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

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

Answer 2

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 3

I am unable to comment (no privilege) - but would like to ask an additional question. I want to do the exact same thing as the first person asked. In addition i want the remaining lines in file 2 that don't have a match in file 1 to be "-9". many thanks. Happy to post a separate question but didn't want to create more posts unnecessarily.

---

file 1

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

---

file 2

---

HG00096 2
HG00097 5
HG00099 3
HG00101 5
HG00102 3

file 3

---

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

---