0

I need to replace the column 13 of file 2, with the column 2 of file 1, when column 1 of file 1 matches with col 2 of file 2.

file 1

AAAA00000687697 HPNMS
AAAA00000693037 GER112
AAAA00000349750 FRA0158

file 2

585     AAAA00000687697 1    +       11868   14409   14409   14409   3       11868,12612,13220,      12227,12721,14409,      0       BBBB00000223972 none    none    -1,-1,-1,
585     AAAA00000693037 1    +       11871   14412   14412   14412   3       11871,12612,13224,      12227,12721,14412,      0       BBBB00000223972 none    none    -1,-1,-1,
585     AAAA00000349750 1    +       11873   14409   14409   14409   4       11873,12594,13402,13660,        12227,12721,13655,14409,        0       BBBB00000223972 none    none    -1,-1,-1,-1,

Output should be like

585     AAAA00000687697 1    +       11868   14409   14409   14409   3       11868,12612,13220,      12227,12721,14409,      0       HPNMS           none    none    -1,-1,-1,
585     AAAA00000693037 1    +       11871   14412   14412   14412   3       11871,12612,13224,      12227,12721,14412,      0       GER112          none    none    -1,-1,-1,
585     AAAA00000349750 1    +       11873   14409   14409   14409   4       11873,12594,13402,13660,        12227,12721,13655,14409,        0       FRA0158 none    none    -1,-1,-1,-1,

I tried the codes below

 awk 'NR==FNR{a[$1]=$2;next} $2 in a {$13=a[$1];print}' FS='\t' OFS='\t' file1.txt file2.txt  > output.txt

It prints the file 2, with an empty $13 th column.

When I tried to print a[$1] alone, it prints an empty file; and when I remove the ;next part in first curly brackets it then can print only the a[$1]. I don't know why it has some troubles saving the array.

  • With $2 in a {$13=a[$1]... you're testing for $2 being an index of a[] and then using $1 as the index in a[] to get the new value from. Think about that for a second. – Ed Morton Sep 3 at 22:14
1

Just replace $13 with the array string a[$2] if available:

awk 'NR==FNR{a[$1]=$2;next}($2 in a){$13=a[$2]}1' file1 file2 
  • Exactly, but the above code still does not work – cookiemonster Sep 4 at 12:34
  • 1
    @cookiemonster What isn't working exactly? Is there data missing? Is it column formatting? Please be more explicit. – oliv Sep 4 at 14:43
  • I cannot be explicit because I don't know what is wrong, there is no data missing or anything. The output file is exactly the same as the input (file 2). When I change the command to { print a[$2] }, it does not print anything maybe why it cannot change the column. – cookiemonster Sep 4 at 15:12
  • *that's why it cannot change the column – cookiemonster Sep 4 at 15:20
  • @cookiemonster What is your version of awk? On what system are you running the command? – oliv Sep 4 at 15:45

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.