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

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

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

40A titi,toto,lala
40F 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. When the clade in A.tsvare 20B: - If the corresponding mutation in A.tsv have all the mutation of 40A in B.tsv, print in a new column (after the last of A.tsv) named Conclusion the clade 40A. - It's not a problem if the line 20B in A.tsv contain other mutation then those from 40A in B.tsv. - If the line 20B in A.tsv doesn't contain all the mutation from 40A in B.tsv, don't print anything. The result (store in a new file C.tsv) will look like this:

id  clade   mutation    Conclusion
243 40A titi,xixi,lolo  
254 20B titi,toto,jiji,lala 40A
267 20B lala,jiji,jojo  

I start with that :

awk 'BEGIN{ OFS=FS="\t" }
  NR==FNR{ clade[$1]=$2; next }         
  FNR==1{ print $0, "Conclusion"; next }    
  !($2 in clade){ print; next }         
     split($3 "," clade[$2], tmp, ",")  
     for (i in tmp)                     
       if (++num[tmp[i]] > 1)          

     print $0, count                   
     delete num                      
' B.tsv A.tsv > C.tsv

but I don't know how to do the rest. Do you have an idea? Thanks

  • 1
    How is this different from your previous question or the one before that and why aren't you using either of the answers you accepted there as the starting point for this question instead of the same script you included in your previous questions since it seems extremely similar if not identical?
    – Ed Morton
    Mar 2, 2021 at 20:47
  • It's different on the approach of handling the two files. Here is not a code on the all file but on a specific pattern on each file. That's why I can't use (because I tried) of the previous answer. Sorry if it sounds like that
    – nstatam
    Mar 3, 2021 at 7:31

1 Answer 1

awk 'BEGIN   { FS=OFS="\t" }
     NR==FNR { if($1=="40A") { cladeB=$1; mutB=$2; "nextfile" }; next }
     FNR==1 && mutB{ $4="Conclusion"; }

{ mutbak=mutB; split($3, muts, ","); for(x in muts) gsub(muts[x], "", mutbak) }
{ print $0, (mutbak ~ /^[[:blank:],]*$/ && $2=="20B" )?cladeB:""}' fileB fileA

Unquote the nextfile statement (that for GNU awk then) to read the next file if you won't want continue processing the rest of the fileB; also note that "clade=40A" in fileB should be unique, else it would always return the last one's value.

  • @nstatam I know it will give wrong result if you had titi,totojijilala,lala for example, but to resolve that you can refer to the answers of mine in your previously asked questions. Mar 3, 2021 at 8:53
  • Thanks for your answer ! It works well. I'm trying to understand your code and I have a question. If I have another clade to search (for example 40A from A.tsv / 40F from B.tsv), what part of your code had to be changed ?
    – nstatam
    Mar 4, 2021 at 9:47
  • @nstatam then for that you would need to define a mapping table, and that's require you open a new question and I will take a look if I saw it or you can ping me here and give the link to your question. here you only wanted for one ¬one match. Mar 4, 2021 at 19:13
  • But by just re-run your script with a different example after the first one, I obtain a good result but in the next column. Do you have an idea how to change the last line in your script for the second run in order to print in the same column as the first run ? Thanks
    – nstatam
    Mar 5, 2021 at 8:56
  • It's exactly what I've done (awk '...' fileB fileA.modified1) and in result I have the data for the new assignation in the column after Conclusion . It' probably because in the script we do print $0, ... so when I re-run it , it print in the next column. How to prevents that ?
    – nstatam
    Mar 5, 2021 at 9:19

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.