0

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
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.
  • Thanks for your suggestion. The replacement is indeed what I wanted (instead of adding as I did in my question). I Just found out that the reason why this command did not work is because there are ^M characters in one of my files... – mats Sep 4 '15 at 8:43
  • use dos2unix to get rid of ^M – Archemar Sep 4 '15 at 8:44
  • Thanks, I use dos2unix on most files I receive from other people, however one this file it didn't work. I used tr '^M' '\n' < filename to get rid of the ^M characters. – mats Sep 4 '15 at 8:52
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.