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

3 Answers 3

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
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.
3
  • 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, 2015 at 8:43
  • use dos2unix to get rid of ^M
    – Archemar
    Sep 4, 2015 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, 2015 at 8:52
0

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

2

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.