CODEWITHSUNDEEP
awk
Madza Farias-Virgens
Madza Farias-Virgens

Reputation: 1071

Print lines in both file when 2 different columns match

I have 2 tab delim files

file 1 

B T 4 tab -
1 C 5 - cab
5 A 2 - ttt
D T 18 1111 -

file 2

K A 3 0.1 
T B 4 0.3 
P 1 5 0.5 
P 5 2 0.11

I need to merge the two based on file 1 col1 and 3 and file2 col2 and 3, and print lines in both files. I'm expecting the following output:

B T 4 tab - T B 4 0.3 
1 C 5 - cab P 1 5 0.5 
5 A 2 - ttt P 5 2 0.11

I tried adapting from a similar question I had in the past: 

awk 'NR==FNR {a[$1,$3] = $2"\t"$4"\t"$5; next} $2,$3 in a {print a[$1,$3],$0}' file1 file2

but no success, the output I get looks like this, which is similar to file2:

 K  A   3   0.1 
 T  B   4   0.3 
 P  1   5   0.5 
 P  5   2   0.11

Upvotes: 1

Views: 109

Answers (1)

hek2mgl
hek2mgl

Reputation: 158200

There are two small problems in your code:

awk 'NR==FNR{a[$1,$3]=$0; next} ($2,$3) in a {print a[$2,$3], $0}' file1 file2
#                  parentheses -^ ----^
#                                            $2,$3  ----^

Upvotes: 2

Related Questions