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
A.txt (as you already did in your code example).
for (i=1;i<=n_tmp[$1];i++) mut_tmp[$1][i]=buff[i]
# Find missing
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
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=""
- 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
- 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
- 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
If you don't have GNU
awk available, you can make it work with most other
awk implementations by replacing
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
267 40B lala,sisi,sojo huhu sisi
Please note that your example desired output is inconsistent with your description of the requirements.
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
- all occurences of
- all occurences of