How to compare two column of two file and print the number of match with awk

Question

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

id  clade   mutation
243 40A titi,toto,lala
254     
267 40B lala,jiji,jojo

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

40A lala,toto,xixi,xaxa
40B xaxa,jojo,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 and indicate the number of match that it found in a new column in a new file (C.tsv) like this:

id  clade   mutation    number
243 40A titi,toto,lala  2
254     
267 40B lala,jiji,jojo  1

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 count the match. Do you have an idea?

A SECOND QUESTION:

I'm wondering how to make a new column with only the information on how many mutations are present in B.tsv.  Example for the column total_mut in C.tsv:

id  clade   mutation    number  total_mut
243 40A titi,toto,lala  2   4
254     
267 40B lala,jiji,jojo  1   3

Answer 1

The method is to make an array indexed by clade and mutation, from the B file. Then iterate the mutations from the A file.

Somewhat tricky to deal with a tab-separated file, especially keeping the number of columns where there is no clade.

We define the necessary column numbers for the A file as cClade and cMut, and changed these to match the full data format.

For the follow-up question, we save nMut (number of mutations), which split() already returns, and add it to the prints (header and detail). Tested this version too.

#! /bin/bash

Match () {  #:: (data, template)

    Awk='
BEGIN { FS = "\t"; Sep = ","; cClade = 20; cMut = 41; }
F == "B" {
    nMut[$1] = split ($2, V, Sep);
    for (j in V) Mut[$1 Sep V[j]];
    next;
}
! $2 { printf ("%s%s%s\n", $0, FS, FS); next; }
FNR == 1 { printf ("%s%s%s%s%s\n", $0, FS, "number", FS, "total_mut"); next; }
{
    n = 0;
    split ($cMut, V, Sep);
    for (j in V) if (($cClade Sep V[j]) in Mut) ++n;
    printf ("%s%s%s%s%s\n", $0, FS, n, FS, nMut[$cClade]);
}
'
    awk -f <( printf '%s' "${Awk}" ) F="B" "${2}" F="A" "${1}"
}

    Match useTemplate.A.tsv useTemplate.B.tsv > useTemplate.C.tsv

Answer 2

With GNU awk for the \< and \> (Word-boundaries anchors):

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

{
    split($3, muts, "," );
    for(x in muts) { tmp=mutations[$2]; c+=sub( "\\<"muts[x]"\\>", "", tmp) }
}

FNR==1 { c="number" }
{ print $0, (c?c:""); c=0 }' fileB  fileA

Output:

id      clade   mutation        number
243     40A     titi,toto,lala  2
254
267     40B     lala,jiji,jojo  1

---

Updated answer for the second requirement:

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

{
    split($3, muts, "," );
    for(x in muts) { tmp=mutations[$2]; c+=sub( "\\<"muts[x]"\\>", "", tmp) }
    m=1+gsub(",", "", tmp) 
}

FNR==1 { c="number"; m="total_mut" }
{ print $0, (c?c:""), (m>1?m:""); c=m=0 }' fileB  fileA

Output:

id      clade   mutation        number  total_mut
243     40A     titi,toto,lala  2       4
254
267     40B     lala,jiji,jojo  1       3

Answer 3

awk 'BEGIN{ OFS=FS="\t" }
  NR==FNR{ clade[$1]=$2; next }         # save clade, mutation of B.tsv in array
  FNR==1{ print $0, "number"; next }    # print header
  !($2 in clade){ print; next }         # no match -> print record
  {                                     # else...
     split($3 "," clade[$2], tmp, ",")  # split mutations into tmp array
     for (i in tmp)                     # for all mutations
       if (++num[tmp[i]] > 1)           # if same mutation occurs more than once
         ++count                        # increment counter

     print $0, count                    # print record and count
     delete num                         # reset temporary array
     count=0                            # reset counter
  }
' B.tsv A.tsv > C.tsv

---

Second answer:

Replace line 3 with:

FNR==1{ print $0, "number", "total_mut"; next }

Replace the last print with:

print $0, count, split(clade[$2], tmp, ",")

Answer 4

This uses GNU awk for multi-dimensional arrays:

gawk '
    BEGIN {
        FS = "[\t,]"
        OFS = "\t"
    }
    FILENAME == ARGV[1] {
        for (i = 2; i <= NF; i++)
            B[$1][$i] = 1
        next
    }
    FNR == 1 {
        print $0, "number", "total_mut"
        next
    }
    !($2 in B) {
        print
        next
    }
    {
        count = 0
        for (i = 3; i <= NF; i++)
            if ($i in B[$2])
                count++
        print $0, count, length(B[$2])
    }
' {B,A}.tsv

id      clade   mutation        number  total_mut
243     40A     titi,toto,lala  2       4
254
267     40B     lala,jiji,jojo  1       3