0

I have two files, A.tsv and B.tsv:

A.tsv (field separator = \t):

Sample ID   Internal Control    Result  Consensus   
4686427 Pass    Not Detected    Not Available
4666275 Pass    Detected    Not Available
4666295 Pass    Detected    Available
4644444 Pass    Detected    Available

B.tsv (field separator = \t):

seqName clade   substitutions   deletions
4666295 A8A yes no
4666275 18A no  yes
4686427 161 no  yes

And I want to combine those two file into a new file like this :

Sample ID   Internal Control    Result  Consensus   clade   substitutions   deletions   
4686427 Pass    Not Detected    Not Available   161 no  yes
4666275 Pass    Detected    Not Available   18A no  yes
4666295 Pass    Detected    Available   A8A yes no
4644444 Pass    Detected    Available

I've write this but it does not print the header and print also the first line of the second file :

awk -F '\t' -v OFS="\t" 'NR==FNR{a[$1]=$0;next}{print $0,a[$1]}' B.tsv A.tsv > C.tsv

So how to do it properly ? Thanks

PS: I've subsample the file, the real one are bigger in term of line and column

0
2

Try:

awk 'BEGIN        { FS=OFS="\t" }
     NR==FNR      { seq=$1; sub(/[^\t]*\t/,""); if(NR==1)hdr=$0; hold[seq]=$0; next }
     FNR==1       { print $0, hdr; next }
     ($1 in hold) { print $0, hold[$1]; next }
                  { print }' fileB fileA >fileC
0
1
$ cat tst.awk
BEGIN { FS=OFS="\t" }
{ key = (FNR>1 ? $1 : RS) }
NR == FNR {
    $1 = ""
    map[key] = $0
    next
}
{ print $0 map[key] }

$ awk -f tst.awk B.tsv A.tsv
Sample  ID      Internal Control        Result Consensus        clade   substitutions   deletions
4686427 Pass    Not Detected    Not Available   161     no      yes
4666275 Pass    Detected        Not Available   18A     no      yes
4666295 Pass    Detected        Available       A8A     yes     no
4644444 Pass    Detected        Available

$ awk -f tst.awk B.tsv A.tsv | column -s$'\t' -t
Sample   ID    Internal Control  Result Consensus  clade  substitutions  deletions
4686427  Pass  Not Detected      Not Available     161    no             yes
4666275  Pass  Detected          Not Available     18A    no             yes
4666295  Pass  Detected          Available         A8A    yes            no
4644444  Pass  Detected          Available

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.