Merging two txt files based on a common column with different row numbers

user_15403347 0
Viewed 73

I would like to merge two whitespace-delimited files without sorting them first based on the "phenotype" column. File 1 contains the same phenotype several times, while file 2 has each phenotype only once. I need to match "phenotype" from file 1 to "category" in file 2.

File 1:

chr pos pval_EAS phenotype FDR
1 1902906 0.234 biomarkers-30600-both_sexes-irnt.tsv.gz 1
2 1475898 0.221 biomarkers-30600-both_sexes-irnt.tsv.gz 1
2 568899 0.433 continuous-4566-both_sexes-irnt.tsv.gz 1
2 2435478 0.113 continuous-4566-both_sexes-irnt.tsv.gz 1
4 1223446 0.112 phecode-554-both_sexes-irnt.tsv.gz 0.345
4 3456573 0.0003 phecode-554-both_sexes-irnt.tsv.gz 0.989

File 2:

phenotype Category
biomarkers-30600-both_sexes-irnt.tsv.bgz Metabolic
continuous-4566-both_sexes-irnt.tsv.gz Neoplasms
phecode-554-both_sexes-irnt.tsv.gz Immunological

I tried the following, but I don't get the desired output:

awk -F' ' 'FNR==NR{a[$1]=$4; next} {print $0 a[$6]}' file2 file1 > file3
merge awk linux
1 Answers

With your shown samples, please try following.

awk 'FNR==NR{arr[$1]=$2;next} ($4 in arr){print $0,arr[$4]}' file2 file1

Explanation: Adding detailed explanation for above.

awk '                ##Starting awk program from here.
FNR==NR{             ##Checking condition which will be TRUE when file2 is being read.
  arr[$1]=$2         ##Creating array arr with index of $1 and value is $2.
  next               ##next will skip all further statements from here.
}
($4 in arr){         ##Checking condition if 4th field is in arr then do following.
  print $0,arr[$4]   ##Printing current line along with value of arr with 4th field as index number.
}
' file2 file1        ##Mentioning Input_file names here.

Bonus solution: In case you want to print those lines which are not matching values and want to print with N/A then do following.

awk 'FNR==NR{arr[$1]=$2;next} {print $0,(($4 in arr)?arr[$4]:"N/A")}' file2 file1
user_58665800
Related