Replace values on the basis of matching ID variable

Question

I have two files. file1 has 6 columns, file2 has 2 columns. I want to replace the values in the 6th column of file1 by the values in column 2 of file2, based on matching ID variables (column 1 in both files).

Note: file2 contains more subjects than file1. Therefore the command below did not work unfortunately:

awk 'NR==FNR{tmp[$1]=$2;next}{print $0,tmp[$1]}' file2 file1

file1 looks like:

HG00096 HG00096 0 0 0 -9
HG00097 HG00097 0 0 0 -9
HG00099 HG00099 0 0 0 -9
HG00100 HG00100 0 0 0 -9
HG00102 HG00102 0 0 0 -9

file2 looks like:

HG00096 2
HG00097 5
HG00098 5
HG00099 3
HG00100 3
HG00101 5
HG00102 3

I would like the resulting file to look as follows:

HG00096 HG00096 0 0 0 2
HG00097 HG00097 0 0 0 5
HG00099 HG00099 0 0 0 3
HG00101 HG00100 0 0 0 5
HG00102 HG00102 0 0 0 3

Answer 1

You almost make it.

awk 'NR==FNR{tmp[$1]=$2;next};{$6=tmp[$1] ; print }' file2.txt file1.txt

Where:

• $6 = tmp[$1] will replace 6th field.

Answer 2

Use join:

join -j 1 -o 0,1.2,1.3,1.4,1.5,2.2 file1 file2

Where:

• -j defines the join field of both files
• -o formats the output as desired