I want to replace the gene names in file 1 with gene annotation information from file 2.

File 1 looks like this: an OG identifier in the first column and gene names in the 2nd to the Xnd column):

OG0000008,aly11306X1, aly15270X1, aly1603X45, aly1603X46
OG0000009,Tni22G01870, Tni22G01880, Tni22G01890, Tni22G01900, Tni22G01910, Tni22G01920
OG0000010,BANYX1.2.t00767, BANYX1.2.t00769, BANYX1.2.t00774, BANYX1.2.t00775, BANYX1.2.t00861

All the gene names of the 2nd to the Xnd column needs to be replaced by annotation info from File 2 that looks like this: A gene identifier (can occur multiple times!) in the first column followed by multiple annotations in column 2 to 5:

    aly11306X1  Pfam    PF16207 IPR032443   
    aly15270X1  Pfam    PF13923
    aly1603X45  Pfam    PF01509 IPR002501   GO:0006396
    aly1603X46  Pfam    PF04845 IPR006628   GO:0000977|GO:0032422
    Tni22G01870 Pfam    PF02779 IPR005475   
    Tni22G01880 Pfam    PF02780 IPR033248   
    Tni22G01890 Pfam    PF00456 IPR005474   
    Tni22G01900 Pfam    PF04949 IPR007033   
    Tni22G01910 Pfam    PF00250 IPR001766   GO:0003700|GO:0006355|GO:0043565
    Tni22G01920 Pfam    PF00379 IPR000618   GO:0042302
    BANYX1.2.t00767 Pfam    PF00400 IPR001680   GO:0005515
    BANYX1.2.t00769 Pfam    PF00400 IPR001680   GO:0005515
    BANYX1.2.t00774 Pfam    PF00400 IPR001680   GO:0005515
    BANYX1.2.t00775 Pfam    PF00400 IPR001680   GO:0005515
    BANYX1.2.t00861 Pfam    PF04949 IPR007033   
    BANYX1.2.t00861 Pfam    PF08704 IPR014816   GO:0016429|GO:0030488|GO:0031515

In the outputfile I want to retrieve the OG identifier in column 1 from File 1, followed by the gene annotation of column 3 from File 2. In other words, the gene names in File 1 should be replaced by the gene PF number of File 2. I do not really care about the delimiter here.


OG0000008   PF16207 PF13923 PF01509 PF04845
OG0000009   PF02779 PF02780 PF00456 PF04949 PF00250 PF00379
OG0000010   PF00400 PF04949 PF08704

I tried to solve this issue with grep, but in reality these files are 1000s of records long, so that didn't work. I decided to go fo AWK, but unfortunately my awk knowledge is limited but I would really want to get better. Are you able to help me here?

I've tried to start simple, by only searching within the 2nd column, but it prints the whole file instead:

awk -F "," 'NR==FNR{a[$2];next} $2 in a {print $1, $3}' File1 File2

Can someone help me out?

  • 1
    You should include a line that has both Tni22G01900 and BANYX1.2.t00861 in the sample input as they both map to the same annotation PF04949 and so it'd be a good test of a potential solution to see if it could produce that annotation just 1 time in the associated output line.
    – Ed Morton
    Sep 16, 2021 at 18:17
  • I just noticed you have two lines in file2 for BANYX1.2.t00861. How should those be treated? Should we only take the domains from the second (last) entry or should we concatenate both? Should BANYX1.2.t00861 have PF04949 or PF08704 or PF04949 PF08704?
    – terdon
    Sep 16, 2021 at 18:39
  • @EdMorton it should probably have it twice, not once. These are gene names and the PF* are "domains" (regions) in the protein encoded by this gene. Proteins can have multiple domains and multiple, unrelated proteins can have the same one.
    – terdon
    Sep 16, 2021 at 18:40
  • @terdon could be, it's all just rows and columns to me. Hopefully the OP will provide some clarification and an updated example.
    – Ed Morton
    Sep 16, 2021 at 18:59
  • You might want to accept Ed's answer instead of mine. His is better, and actually produces the output you asked for (unlike mine).
    – terdon
    Sep 16, 2021 at 19:26

1 Answer 1


Using GNU awk for arrays of arrays:

$ cat tst.awk
    delete annots
    for ( i=2; i<=NF; i++ ) {
        gene = $i
        if ( gene in genes_annots ) {
            for ( annot in genes_annots[gene] ) {
    printf "%s", $1
    for (annot in annots) {
        printf "%s%s", OFS, annot
    print ""

$ awk -f tst.awk file2 FS='[, ]+' file1
OG0000008 PF01509 PF16207 PF13923 PF04845
OG0000009 PF00250 PF02779 PF00379 PF04949 PF00456 PF02780
OG0000010 PF08704 PF04949 PF00400

The above will even work if you have 2 separate genes with the same annotation, e.g. genes Tni22G01900 and BANYX1.2.t00861 both have annotation PF04949:

$ cat file3
OG0000099,Tni22G01900, BANYX1.2.t00861

$ awk -f tst.awk file2 FS='[, ]+' file3
OG0000099 PF08704 PF04949

I'm assuming above that the order the annotations appear in each line of output doesn't matter as long as they are all present and unique within the line.

  • 1
    I indeed completely overlooked the fact that the script of terdon misses a few PF identifiers per line (not for all but a few). I cannot find the logic why it is skipping a few now and than. Therefor I have flagged this answer as the one answering my question. Thank you both for your help!
    – TUnix
    Sep 18, 2021 at 8:21

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.