How to compare two column of two file and print not matching pattern with awk

Question

I have a data file A.tsv (field separator = \t) :

id  clade   mutation
243 40A siti,toto,mumu
254     
267 40B lala,sisi,sojo

and a template file B.tsv (field separator = \t) :

40A toto,xixi,saxa
40B lala,sojo,huhu
40C sasa,sisi,lala

Based on their common column (clade), I want to compare the mutation of A.tsv from the template B.tsv. I have two questions:

1. How to indicate in a new column named "missing_mutation" the name of the mutation in B.tsv that aren't present in A.tsv.
2. How to indicate in a new column named "remaining_mutation" the name of the mutation that are present in A.tsv (and that start with the letter s, case-insensitive) but not present in B.tsv.

C.tsv looks like this:

id  clade   mutation    missing_mutation    remaining_mutation
243 40A siti,toto,mumu  xixi,saxa   siti
254     
267 40B lala,sisi,sojo  huhu    sisi

I know how to compare two files like this:

awk -F"," -vOFS="," '    
    NR==FNR {
     a[$2]=$3;
     next
    }
    
    { print $0,a[$2] }
' B.tsv A.tsv > C.tsv

but I don't know how to print those who don't match. Do you have an idea?

Answer 1

The following awk program, which uses GNU awk for convenience (in form of "true double-index arrays"), should do the trick. It is intended to be called with your two files as argument in the order B.txt A.txt (as you already did in your code example).

#!/bin/awk -f

BEGIN{FS=OFS="\t"}

NR==FNR {
    n_tmp[$1]=split($2,buff,/,/)
    for (i=1;i<=n_tmp[$1];i++) mut_tmp[$1][i]=buff[i]
    next
}

FNR==1 {
    print $0,"missing_mutation","remaining_mutation"
}

FNR>1 {
    n_curr=split($3,mutations,/,/)
    mut_miss=0
    mut_rem=0

    # Find missing
    for (j=1;j<=n_tmp[$2];j++)
    {
        for (i=1;i<=n_curr;i++)
        {
            if (mutations[i]==mut_tmp[$2][j]) break
        }
        if (i>n_curr) mut_miss=mut_miss ? mut_miss "," mut_tmp[$2][j] : mut_tmp[$2][j]
    }

    # Find remaining
    for (i=1;i<=n_curr;i++)
    {
        for (j=1;j<=n_tmp[$2];j++)
        {
            if (mutations[i]==mut_tmp[$2][j]) break;
        }
        if (j>n_tmp[$2] && mutations[i]~/^[Ss]/) mut_rem=mut_rem ? mut_rem "," mutations[i] : mutations[i]
    }
    
    if (!mut_miss) mut_miss=""
    if (!mut_rem) mut_rem=""

    print $0,mut_miss,mut_rem
}

• This will first create an associative table of clade nr. vs. mutation list while parsing your template B.tsv, where the template mutation list is explicitly split into an array of individual mutations. Note that for convenience, I use a "double-indexed" array mut_tmp, where the first index is the clade nr. and the second is the index number of the mutation. The number of mutations is also stored in an array n_tmp.
• While parsing A.tsv, it will first print the new header.
• Then, for every line, it will first split the list of mutations in field 3 into an array mutations.
• Afterwards, it will compare each entry in the list of template mutations for the current clade (mut_tmp[$2]) against the mutations present (mutations) to fill the "missing mutations" field.
• Next, it will compare each entry in the list of current mutations against the template mutations for the current clade to fill the "remaining mutations" field, where in addition it is checked that the mutation starts with s or S.

If you don't have GNU awk available, you can make it work with most other awk implementations by replacing

mut_tmp[a][b]

with

mut_tmp[a,b]

This will work because the clade numbers and mutation names do not seem to contain the special character \034 which is by default used to concatenate the individual components to a single-string array index (look for the SUBSEP internal variable for more information).

The output for your input example is:

~$ awk -f compare.awk B.tsv A.tsv 
id  clade   mutation    missing_mutation    remaining_mutation
243 40A siti,toto,mumu  xixi,saxa   siti
254             
267 40B lala,sisi,sojo  huhu    sisi

Please note that your example desired output is inconsistent with your description of the requirements.

Edit

Since you stated in a comment that in the actual A.tsv the clade is in column 22 and the mutations in column 41, change

• split($3,mutations,/,/) to split($41,mutations,/,/)
• all occurences of n_tmp[$2] to n_tmp[$22]
• all occurences of mut_tmp[$2][j] (or [$2,j]) to mut_tmp[$22][j] (or [$22,j], respectively)

Answer 2

Alternative awk:

awk 'BEGIN{ FS=OFS="\t" }
NR==FNR{ mutations[$1] =$2; next }

FNR>1  {
         split($3, muts, "," );
         for(x in muts) { 
             if (gsub( ",?"muts[x]",?", "", mutations[$2])>0) delete muts[x] }
       }

FNR==1 { $4="missing_mutation"; $5="remaining_mutation" }

{ printf ("%s", $0 OFS mutations[$2] OFS );
  for(r in muts) {
      if(muts[r] ~/^[Ss]/) printf("%s", sep muts[r]); sep="," }
  print ""; sep=""
}' fileB  fileA