Search and replace with AWK but in file with multiple columns and separators

Question

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.

Output:

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?

Answer 1

Using GNU awk for arrays of arrays:

$ cat tst.awk
NR==FNR {
    genes_annots[$1][$3]
    next
}
{
    delete annots
    for ( i=2; i<=NF; i++ ) {
        gene = $i
        if ( gene in genes_annots ) {
            for ( annot in genes_annots[gene] ) {
                annots[annot]
            }
        }
    }
    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.