3

I have two tab-delimited files(fileA.txt and fileB.txt), I have to compare the first column of fileA.txt with the first column of fileB.txt and also I want to print the values present in the second column of fileB.txt in the output file. Below is my

fileA.txt

id
chr1_45796849_A_T
chr1_45796854_C_T
chr1_45797174_T_A
chr1_45796852_G_C
chr19_9018540_A_G
chr19_9002576_T_C
chr1_45797487_A_G
chr1_45797153_A_T
chr1_45797750_C_T

FileB.txt

chr_pos             freq.var
chr1_45796849_A_T   0.028399811
chr1_45796852_G_C   0.019154034
chr1_45796854_C_T   0.015872901
chr1_45797153_A_T   0.010129176
chr1_45797487_A_G   0.012981216
chr1_45797750_C_T   0.024949931

following is expected outcome

id                  freq.var
chr1_45796849_A_T   0.028399811
chr1_45796854_C_T   0.015872901
chr1_45797174_T_A   
chr1_45796852_G_C   0.019154034
chr19_9018540_A_G   
chr19_9002576_T_C   
chr1_45797487_A_G   0.012981216
chr1_45797153_A_T   0.010129176
chr1_45797750_C_T   0.024949931

I have referred to awk - comparing 2 columns of 2 files and print common lines but it gives only matching entries

5

Read fileB.txt first, make the 1st field a key and the 2nd field its value in an array, skipping the header line with FNR>1 (What are NR and FNR and what does "NR==FNR" imply?).

Then read fileA.txt, print its header for the first line and then print its 1st field followed by the corresponding element in the array, if any.

awk '
    FNR==NR && FNR>1{a[$1]=$2}
    NR!=FNR{
        if(FNR>1){print $1,a[$1]}
        else{print "id", "freq.var"}
    }
' OFS="\t" fileB.txt fileA.txt

OFS="\t" sets the output field separator to tab. Since your file is tab delimited, I assume the output file should be tab delimited too.

You can pipe that into column -t for alignment.

0
4

If you don't need to keep the original sort order, you can use join:

$ join -a1 -j1 -t$'\t' <(tail -n+2 fileA.txt | sort)  <(tail -n+2 fileB.txt | sort)
chr1_45796849_A_T   0.028399811
chr1_45796852_G_C   0.019154034
chr1_45796854_C_T   0.015872901
chr1_45797153_A_T   0.010129176
chr1_45797174_T_A
chr1_45797487_A_G   0.012981216
chr1_45797750_C_T   0.024949931
chr19_9002576_T_C
chr19_9018540_A_G

Or, to keep the headers:

$ ( printf 'id\tfreq.var\n'; join -t$'\t' -a1 -j1 <(tail -n+2 fileA.txt | sort)  <(tail -n+2 fileB.txt | sort))
id  freq.var
chr1_45796849_A_T   0.028399811
chr1_45796852_G_C   0.019154034
chr1_45796854_C_T   0.015872901
chr1_45797153_A_T   0.010129176
chr1_45797174_T_A
chr1_45797487_A_G   0.012981216
chr1_45797750_C_T   0.024949931
chr19_9002576_T_C
chr19_9018540_A_G

And to pretty-print:

$ ( printf '%-20s\t%s\n' "id" "freq_var"; join -t$'\t' -a1 -j1 <(tail -n+2 fileA.txt | sort)  <(tail -n+2 fileB.txt | sort))
id                      freq_var
chr1_45796849_A_T   0.028399811
chr1_45796852_G_C   0.019154034
chr1_45796854_C_T   0.015872901
chr1_45797153_A_T   0.010129176
chr1_45797174_T_A
chr1_45797487_A_G   0.012981216
chr1_45797750_C_T   0.024949931
chr19_9002576_T_C
chr19_9018540_A_G
1
  • For those who always struggle with head/tail, the tail -n+2 in this answer is equivalent to sed 1d – D. Ben Knoble Sep 13 '20 at 17:30
3
$ cat tst.awk
BEGIN { FS=OFS="\t" }
NR==FNR {
    map[(NR>1 ? $1 : "id")] = $2
    next
}
{ print $1, map[$1] }
$ awk -f tst.awk fileB fileA
id  freq.var
chr1_45796849_A_T   0.028399811
chr1_45796854_C_T   0.015872901
chr1_45797174_T_A
chr1_45796852_G_C   0.019154034
chr19_9018540_A_G
chr19_9002576_T_C
chr1_45797487_A_G   0.012981216
chr1_45797153_A_T   0.010129176
chr1_45797750_C_T   0.024949931
0
1

How about not using bash essentials, but something more comfortable like SQL (backed by a tool like q)?

q -H -O -t 'SELECT id, `freq.var` FROM fileA.txt as a LEFT OUTER JOIN fileB.txt as b ON a.id = b.chr_pos'
0

GNU sed with extended regexp mode ON -E and no autoprint -n. Read in the fileB first and store it in hold space. Then for every line of file A compare if it is found in hold.

sed -En '
  1{
    s/^\S+/id/
    :filea
      H;n
    /\t/b filea
    x;s/$/\n/;x
  }
  G
  s/^([^\n]+)\n.*\n(\1\t[^\n]*)\n.*/\2/p;t
  s/\n.*/\t/p
' fileB.txt fileA.txt

Result:

id  freq.var
chr1_45796849_A_T   0.028399811
chr1_45796854_C_T   0.015872901
chr1_45797174_T_A   
chr1_45796852_G_C   0.019154034
chr19_9018540_A_G   
chr19_9002576_T_C   
chr1_45797487_A_G   0.012981216
chr1_45797153_A_T   0.010129176
chr1_45797750_C_T   0.024949931

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.