awk compare 2 columns in 2 unsorted files and return specific columns in 2nd file after match

Question

I wish to compare col1 and col3 in file1 with col1 and col2 in file2. If there is a match, I want to print col1 and col3 of file1 and col5 and col6 of file2. file 1 has no header but file 2 has a header. I also want to keep the order based on ordering in file1

Example:

file1:   
1 3 6 
1 3 7
3 3 5
2 4 9
0 5 3

file2:
A B C D E F G
2 9 D 0 5 X 9
1 7 A 8 6 6 G
1 6 D 5 5 T 1
3 5 M 2 2 R 9
7 9 X 3 1 1 4

Expected result:
    1 6 5 T
    1 7 6 6
    3 5 2 R
    2 9 5 X

I tried this:

awk -F ' ' 'FNR=NR{arr[$1$3]++;next};arr[$1$2]{print $5,$6,arr[$1,$3];}' file1 file2

It only prints col5 and col6 of file2. How can I get the result. I want it in awk but any other language is still okay

You can't create a unique string by concatenating 2 strings since a bc -> abc and ab c -> abc, you need to provide some kind of separator between them. So, as a start, arr[$1$3] should be arr[$1,$3] or arr[$1 FS $3] or similar and do the same for the other array index you're currently creating by concatenation. — Ed Morton

anubhava anubhava · Accepted Answer · 2020-06-03T21:13:06

You may use this awk:

awk 'FNR == NR {map[$1,$2] = $5 OFS $6; next}
($1,$3) in map {print $1, $3, map[$1,$3]}' file2 file1

awk compare 2 columns in 2 unsorted files and return specific columns in 2nd file after match

1 Answers