compare four columns between two files

Question

I want to compare tow files with their two corresponding columns $1 and $2. If $1 and $2 of file1 match with column $1 and $2 of file2, print line from file1 and file2, else print line from file1 and NA NA NA

file1

a 12
b 13
c 14
d 15
e 16

file2

b 13 p1
c 14 p2
e 10 k3
e 16 k4

OUTput

a 12 NA NA NA
b 13 b 13 p1
c 14 c 14 p2
d 15 NA NA NA
e 16 e 16 k4

I have written this program but it can compare with only column $1,

 awk 'BEGIN{FS=OFS="\t"} FNR==NR {a[$1]=$0; next} 
     { t=($1 in a)?a[$1]:"NA"OFS"NA"OFS"NA"OFS"NA"; print t,$0}' file1 file2

Could you kindly correct my code and it would be nice if you can explain it. Thanks a lot.

glenn jackman glenn jackman · Accepted Answer · 2014-04-23T16:56:14

Since you don't care about lines in file2 that aren't in file1 (like e 10 k3), process the files the other way around:

awk '
    NR==FNR {key[$1,$2]=$0; next}
    {
        if (($1,$2) in key) print $0, key[$1,$2]
        else                print $0, "NA","NA","NA"
    }
' file2 file1

compare four columns between two files

1 Answers