Comparing first two columns of one file with first two columns of another file and print the values from second file in output

Question

I have two tab-delimited files, file A and file B, I have to compare the first two columns of file A with the first two columns of file B and print the values from the file B in output, and if entry is not available print NA. following is my

fileA.txt

id  freq.var
chr12_56487682_56487682_T_A 0
chr3_52436344_52436344_C_G  0
chr19_9091288_9091288_G_T   0
chr12_44172075_44172075_A_C 0.05739626
chr19_9048431_9048431_A_G   0.033832938
chr12_56487678_56487678_T_G 0
chr17_37881646_37881646_T_G 0
chr19_9002576_9002576_T_C   0.0342902
chr15_63991054_63991054_T_G 0.024456501
chr17_29554589_29554589_C_T 0.095445774

fileB.txt

id  freq.var
chr12_56487682_56487682_T_A 0.0035266
chr19_9048431_9048431_A_G   0.033832938
chr3_52436344_52436344_C_G  0.002500443
chr12_44172075_44172075_A_C 0.05739626
chr19_9002576_9002576_T_C   0.0342902
chr17_29554589_29554589_C_T 0.095445774
chr15_63991054_63991054_T_G 0.024456501

following is the expected outcome

id  freq.var
chr12_56487682_56487682_T_A 0.0035266
chr3_52436344_52436344_C_G  0.002500443
chr19_9091288_9091288_G_T   NA
chr12_44172075_44172075_A_C 0.05739626
chr19_9048431_9048431_A_G   0.033832938
chr12_56487678_56487678_T_G NA
chr17_37881646_37881646_T_G NA
chr19_9002576_9002576_T_C   0.0342902
chr15_63991054_63991054_T_G 0.024456501
chr17_29554589_29554589_C_T 0.095445774

an earlier similar type of question I have posted Comparing two tab delimited files on the basis of first column of the both the files and print matching and non matching entries in output file

Answer 1

This a standard usage of awk, parsing two files, the actions for the first file are after the condition FNR==NR, store the the values into a hash array and the next statement means go to next line without executing any following actions. For the second file, the following part is executed, we use a ternary, either get the stored value if exists or set it to "NA". 1 alone is print. Pipe to column for tab-like look with spaces.

awk 'NR==FNR{a[$1]=$2; next} {$2=($1 in a)? a[$1]: "NA"} 1' fileB.txt fileA.txt \
    | column -t

Output:

id                           freq.var
chr12_56487682_56487682_T_A  0.0035266
chr3_52436344_52436344_C_G   0.002500443
chr19_9091288_9091288_G_T    NA
chr12_44172075_44172075_A_C  0.05739626
chr19_9048431_9048431_A_G    0.033832938
chr12_56487678_56487678_T_G  NA
chr17_37881646_37881646_T_G  NA
chr19_9002576_9002576_T_C    0.0342902
chr15_63991054_63991054_T_G  0.024456501
chr17_29554589_29554589_C_T  0.095445774