1

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
3
  • Should B.tsv have a header line? You mentioned their common column (clade). What does the number of match that it found mean as I can't figure it out from the example? Is it the line number, a count of matched key values, or something else? – Ed Morton Feb 22 at 15:09
  • 2
    With respect to EDIT FOR A SECOND QUESTION : - no. Ask one question at a time and especially don't add requirements after people have already posted answers. See How to Ask. – Ed Morton Feb 22 at 15:23
  • @EdMorton No problem, I understand that – nstatam Feb 22 at 15:31
0

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
4
  • Thanks for you answer. The template B.tsv has the same number of column then the orignal but the file A.tsv is a subsampling. In my original A.tsvy column with the clade are $20 and my column with the mutation are $41. What part of your code have I to change ? – nstatam Feb 22 at 13:54
  • @nstatam Changed in the code, but not tested again. For wide data, naming the column indexes is a good idea. I have had files where the columns moved around with every data delivery, and used a look-up on the CSV headers to generalise the code. – Paul_Pedant Feb 22 at 14:34
  • Thanks, it works well ! – nstatam Feb 22 at 15:16
  • I edited a subsidiary question. Can you take a look if you have a moment please ? – nstatam Feb 22 at 15:23
1

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
1
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, ",")
0

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
2
  • 1
    nbd but since you're using GNU awk you could use ARGIND == 1 instead of FILENAME == ARGV[1] – Ed Morton Feb 23 at 4:51
  • 1
    Thanks for the reminder Ed. I'm trying to wean myself off the NR == FNR idiom. – glenn jackman Feb 23 at 14:18

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.