gawk join two TSV by columns (a'la sql join)

Question

How to join, to tsv files, examples:

a.tsv

c   7   r   z
d   6   s   w
f   1   f   f
b   8   p   y
a   9   q   x

b.tsv

a   q   a
c   r   ccc
b   p   bb
0   0   0
d   s   dddd

Here I'd like to "join" them by columns, where a$1,a$3==b$1,b$2 and display rest (a$2,a$4,b$3) :

6   w   dddd
9   x   a
8   y   bb
7   z   ccc

Question is: how would you do this in gawk ?

Order of rows does not metter (in output. In input, order of rows is not defined and can be different in a.tsv and b.tsv - like rows in relational db, they have no order).

Uniqueness note: Originally, I assumed "uniqueness of key={a$1,a$3}. As glenn jackman noticed - it can not be assumed from original problem statement, as it allows not unique rows according to any key - thanks glenn.

Answer 1

It appears the join command can only join on one field [1,2], so:

awk '
    BEGIN {FS=OFS="\t"}
    NR==FNR {a[$1 FS $3] = $2 FS $4; next}
    $1 FS $2 in a {print a[$1 FS $2], $3}
' a.tsv b.tsv

Update due to comment: since the given key is not unique, here's a technique to build up multiple entries from "a.tsv"

awk '
    BEGIN {FS=OFS="\t"}
    NR==FNR {
        key = $1 FS $3
        if (key in a)
            a[key] = a[key] "\n" $2 FS $4
        else
            a[key] = $2 FS $4
        next
    }
    $1 FS $2 in a {
        split(a[$1 FS $2], ary, /\n/)
        for (idx in ary)
            print ary[idx], $3
    }
' a.tsv b.tsv

Answer 2

I'd split the task to two different programs:

1. Use join(1) to join the two files

2. awk(1) or cut(1) to strip unwanted columns

Answer 3

I don't know awk very well, but it was designed specifically for handling fields in text files, so I presume it can do the job quite well, but because you seemed(?) to express interest in join (in ktf's comments), here is a solution using standard unix tools: join and cut and paste and sort -- lots of "ands", but it works and it might serve as an example of why awk is better :)... I've thown it in mainly for then comparison-of-methods factor.

join -t $'\t' -o 1.2 1.3  2.2  \
 <(paste <(paste <(cut -f1 a.tsv) \
                 <(cut -f3 a.tsv) \
                 | tr '\t' '\0' ) \
         <(cut -f2 a.tsv) \
         <(cut -f4 a.tsv) \
         | sort ) \
 <(paste <(paste <(cut -f1 b.tsv) \
                 <(cut -f2 b.tsv) \
                 | tr '\t' '\0' ) \
         <(cut -f3 b.tsv) \
         | sort ) 

Answer 4

Finally I've managed to do that. So I share my solution :

awk '
BEGIN {
    FS=OFS="\t"
    while ((getline < "a.tsv") > 0){
        a2[$1,$3] = $2; a4[$1,$3] = $4
    }
}
($1,$2) in a2 { print a2[$1,$2] FS a4[$1,$2] FS $3 }' < b.tsv

produces:

9   x   a
7   z   ccc
8   y   bb
6   w   dddd

This solution :

• does not assume order of input rows
• works, when some rows have no matching in other file
• assume rows in a.tsv are unique according to key = {a$1,a$3}

For those interested in right join , you just need to delete if( ($1,$2) in a2) statement. For those interested in left join, just do "right join" version and swap a.tsv with b.tsv (and change code accordingly).

Uniqueness note : As glenn jackman noticed, that a.tsv's rows might not be unique according to key={a$1,a$3} , you might like to check out his solution.