How to combine data from two file with the header in awk

Question

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

αғsнιη · Accepted Answer · 2021-02-20 06:27:31Z

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

Ed Morton · Accepted Answer · 2021-02-19 22:47:53Z

$ 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

Stack Exchange Network

How to combine data from two file with the header in awk

2 Answers 2

You must log in to answer this question.

Hot Network Questions

How to combine data from two file with the header in awk

2 Answers 2

You must log in to answer this question.

Related

Hot Network Questions