8

I have two tab-separated files which look as follows:

file1:

NC_008146.1     WP_011558474.1  1155234 1156286 44173
NC_008146.1     WP_011558475.1  1156298 1156807 12
NC_008146.1     WP_011558476.1  1156804 1157820 -3
NC_008705.1     WP_011558474.1  1159543 1160595 42748
NC_008705.1     WP_011558475.1  1160607 1161116 12
NC_008705.1     WP_011558476.1  1161113 1162129 -3
NC_009077.1     WP_011559727.1  2481079 2481633 8
NC_009077.1     WP_011854835.1  1163068 1164120 42559
NC_009077.1     WP_011854836.1  1164127 1164636 7

file2:

NC_008146.1     GCF_000014165.1_ASM1416v1_protein.faa
NC_008705.1     GCF_000015405.1_ASM1540v1_protein.faa
NC_009077.1     GCF_000016005.1_ASM1600v1_protein.faa

I want to match column 1 of file1 to file2 and replace itself with the respective column 2 entry of file 2. The output would look like this:

GCF_000014165.1_ASM1416v1_protein.faa     WP_011558474.1  1155234 1156286 44173
GCF_000014165.1_ASM1416v1_protein.faa     WP_011558475.1  1156298 1156807 12
GCF_000014165.1_ASM1416v1_protein.faa     WP_011558476.1  1156804 1157820 -3
GCF_000015405.1_ASM1540v1_protein.faa     WP_011558474.1  1159543 1160595 42748
GCF_000015405.1_ASM1540v1_protein.faa     WP_011558475.1  1160607 1161116 12
GCF_000015405.1_ASM1540v1_protein.faa     WP_011558476.1  1161113 1162129 -3
GCF_000016005.1_ASM1600v1_protein.faa     WP_011559727.1  2481079 2481633 8
GCF_000016005.1_ASM1600v1_protein.faa     WP_011854835.1  1163068 1164120 42559
GCF_000016005.1_ASM1600v1_protein.faa     WP_011854836.1  1164127 1164636 7
14

You can do this very easily with awk:

$ awk 'NR==FNR{a[$1]=$2; next}{$1=a[$1]; print}' file2 file1
GCF_000014165.1_ASM1416v1_protein.faa WP_011558474.1 1155234 1156286 44173
GCF_000014165.1_ASM1416v1_protein.faa WP_011558475.1 1156298 1156807 12
GCF_000014165.1_ASM1416v1_protein.faa WP_011558476.1 1156804 1157820 -3
GCF_000015405.1_ASM1540v1_protein.faa WP_011558474.1 1159543 1160595 42748
GCF_000015405.1_ASM1540v1_protein.faa WP_011558475.1 1160607 1161116 12
GCF_000015405.1_ASM1540v1_protein.faa WP_011558476.1 1161113 1162129 -3
GCF_000016005.1_ASM1600v1_protein.faa WP_011559727.1 2481079 2481633 8
GCF_000016005.1_ASM1600v1_protein.faa WP_011854835.1 1163068 1164120 42559
GCF_000016005.1_ASM1600v1_protein.faa WP_011854836.1 1164127 1164636 7

Or, since that looks like a tab-separated file:

$ awk -vOFS="\t" 'NR==FNR{a[$1]=$2; next}{$1=a[$1]; print}' file2 file1
GCF_000014165.1_ASM1416v1_protein.faa   WP_011558474.1  1155234 1156286 44173
GCF_000014165.1_ASM1416v1_protein.faa   WP_011558475.1  1156298 1156807 12
GCF_000014165.1_ASM1416v1_protein.faa   WP_011558476.1  1156804 1157820 -3
GCF_000015405.1_ASM1540v1_protein.faa   WP_011558474.1  1159543 1160595 42748
GCF_000015405.1_ASM1540v1_protein.faa   WP_011558475.1  1160607 1161116 12
GCF_000015405.1_ASM1540v1_protein.faa   WP_011558476.1  1161113 1162129 -3
GCF_000016005.1_ASM1600v1_protein.faa   WP_011559727.1  2481079 2481633 8
GCF_000016005.1_ASM1600v1_protein.faa   WP_011854835.1  1163068 1164120 42559
GCF_000016005.1_ASM1600v1_protein.faa   WP_011854836.1  1164127 1164636 7

This assumes that every RefSeq (NC_*) id in file1 has a corresponding entry in file2.

Explanation

  • NR==FNR : NR is the current line number, FNR is the line number of the current file. The two will be identical only while the 1st file (here, file2) is being read.
  • a[$1]=$2; next: if this is the first file (see above), save the 2nd field in an array whose key is the 1st field. Then, move on to the next line. This ensures the next block isn't executed for the 1st file.
  • {$1=a[$1]; print} : now, in the second file, set the 1st field to whatever value was saved in the array a for the 1st field (so, the associated value from file2) and print the resulting line.
  • 1
    NR == FNR doesn't work correctly when the first file is empty. See this and the associated answer for a workaround – iruvar Apr 5 at 12:44
  • 3
    @iruvar nothing will work well if the first file is empty, so I don't really see why that's relevant. The entire point here is to combine the data from the two files. If either file is empty, the whole exercise is pointless. – terdon Apr 5 at 12:45
  • sorry I should have said in this particular case file2 and not file1 is empty. Sane behaviour when file2 is empty is to report the contents of file1. The problem with NR == FNR is that code associated with it executes on the contents of file1 when file2 is empty – iruvar Apr 5 at 12:51
  • 3
    @iruvar there is no sane behavior here if either file is empty. That's what I'm saying :) So trying to make it deal with that case gracefully is pointless. And, in any case, when either file is empty here, nothing is printed. Which actually seems like the sanest approach, I'd rather get no data than wrong data. – terdon Apr 5 at 12:54
0

Tested with below command and worked fine

for i in `awk '{print $1}' f2`; do k=`awk -v i="$i" '$1==i {print $2}' f2`;sed  "/$i/s/$i/$k/g" f1 >f3;done

output

for i in `awk '{print $1}' f2`; do k=`awk -v i="$i" '$1==i {print $2}' f2`;sed  "/$i/s/$i/$k/g" f1 >f3;done


GCF_000014165.1_ASM1416v1_protein.faa     WP_011558474.1  1155234 1156286 44173
GCF_000014165.1_ASM1416v1_protein.faa     WP_011558475.1  1156298 1156807 12
GCF_000014165.1_ASM1416v1_protein.faa     WP_011558476.1  1156804 1157820 -3
GCF_000015405.1_ASM1540v1_protein.faa     WP_011558474.1  1159543 1160595 42748
GCF_000015405.1_ASM1540v1_protein.faa     WP_011558475.1  1160607 1161116 12
GCF_000015405.1_ASM1540v1_protein.faa     WP_011558476.1  1161113 1162129 -3
GCF_000016005.1_ASM1600v1_protein.faa     WP_011559727.1  2481079 2481633 8
GCF_000016005.1_ASM1600v1_protein.faa     WP_011854835.1  1163068 1164120 42559
GCF_000016005.1_ASM1600v1_protein.faa     WP_011854836.1  1164127 1164636 7
17

No need for awk, assuming the files are sorted, you can use coreutils join:

join -o '2.2 1.2 1.3 1.4 1.5' file1 file2

Output:

GCF_000014165.1_ASM1416v1_protein.faa     WP_011558474.1  1155234 1156286 44173
GCF_000014165.1_ASM1416v1_protein.faa     WP_011558475.1  1156298 1156807 12
GCF_000014165.1_ASM1416v1_protein.faa     WP_011558476.1  1156804 1157820 -3
GCF_000015405.1_ASM1540v1_protein.faa     WP_011558474.1  1159543 1160595 42748
GCF_000015405.1_ASM1540v1_protein.faa     WP_011558475.1  1160607 1161116 12
GCF_000015405.1_ASM1540v1_protein.faa     WP_011558476.1  1161113 1162129 -3
GCF_000016005.1_ASM1600v1_protein.faa     WP_011559727.1  2481079 2481633 8
GCF_000016005.1_ASM1600v1_protein.faa     WP_011854835.1  1163068 1164120 42559
GCF_000016005.1_ASM1600v1_protein.faa     WP_011854836.1  1164127 1164636 7

If your files aren't, sorted, you can either sort them first (sort file1 > file1.sorted; sort file2 > file2.sorted) and then use the command above, or, if your shell supports the <() construct (bash does), you can do:

join -o '2.2 1.2 1.3 1.4 1.5' <(sort file1) <(sort file2)

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.