Joining columns from files if they contain a match in another column

Question

I have two files:

File1.txt

30    40    A    T    match1    string1
45    65    G    R    match2    string2
50    78    C    Y    match3    string3

File2.txt

match1    60    add1    50    add2
match2    15    add1    60    add2
match3    20    add1    45    add2

and I want to obtain an output that looks like so:

30    40    A    T    match1    string1    60    add1
45    65    G    R    match2    string2    15    add1
50    78    C    Y    match3    string3    20    add1

I want to append column 2 and column 3 from file2.txt to the end of file1.txt if there is a match in column 5 from file1.txt.

I've tried to use this join command:

join -1 5 -2 1 -a 1 -o 1.1 -o 1.2 -o 1.3 -o 1.4 -o 1.5 -o 1.6 -o 2.2 -o 2.3 file1.txt fil2.txt

However, this only seems to print the columns from the first file. Is there any other solutions other than join to tackle this problem?

Answer 1

You can do:

join -1 5 -2 1 -o 1.1,1.2,1.3,1.4,1.5,1.6,2.2,2.3 file1.txt file2.txt

Example:

% cat f1.txt 
30    40    A    T    match1    string1
45    65    G    R    match2    string2
50    78    C    Y    match3    string3

% cat f2.txt 
match1    60    add1    50    add2
match2    15    add1    60    add2
match3    20    add1    45    add2

% join -1 5 -2 1 -o 1.1,1.2,1.3,1.4,1.5,1.6,2.2,2.3 f1.txt f2.txt
30 40 A T match1 string1 60 add1
45 65 G R match2 string2 15 add1
50 78 C Y match3 string3 20 add1

Answer 2

I found a solution:

awk -F "\t" 'FNR==NR {a[$1] = $2 "\t" $3;next} $5 in a{print $0 "\t" a[$5]}' file2.txt file1.txt > outing.txt