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
B.tsv
have a header line? You mentionedtheir common column (clade)
. What doesthe 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:09EDIT 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