How to join, to tsv files, examples:


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


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.


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
            a[key] = $2 FS $4
    $1 FS $2 in a {
        split(a[$1 FS $2], ary, /\n/)
        for (idx in ary)
            print ary[idx], $3
' a.tsv b.tsv
  • @glenn_jackman I have not been clear enought. Let me be more specific: there are two files, but their filenames can be fixed in sourcecode :). – Grzegorz Wierzowiecki Oct 24 '11 at 14:59
  • @glenn_jackman: It is skips records with duplicate keys in a.tsv. It only keeps the last-read one in the array... – Peter.O Oct 24 '11 at 18:58
  • @GrzegorzWierzowiecki, answer updated, check it out. – glenn jackman Oct 24 '11 at 20:45
  • @glennjackman , Thanks for pointing out uniqueness problem :). – Grzegorz Wierzowiecki Oct 25 '11 at 8:27

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

  • JOIN is interesting part. Second - cutting, is clear to me. – Grzegorz Wierzowiecki Oct 24 '11 at 12:51
  • 1
    join(1). – sr_ Oct 24 '11 at 12:57
  • 1
    In case I've not stated that clearly enough: There is a UNIX command named join which you may use for that task. – ktf Oct 24 '11 at 12:58
  • Thanks, ktf, I haven't knew before about join command, so I misunderstood "join" with question statement. IMHO you were right, and shown clearly that you mean command with brackets, referencing to man page - it was my misunderstood. Thanks again :D. – Grzegorz Wierzowiecki Oct 28 '11 at 7:40

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 ) 
  • It's good idea to use combination of sort, cut, paste , as long as we could assume, that both sets have same size and all rows have matching ones. Here you can not assume that, there might be some not matching rows. – Grzegorz Wierzowiecki Oct 25 '11 at 7:44
  • 1
    @Grzegorz Wierzowiecki: The method above can deal with non matching rows; it does not output them. join handles that... Re file size(rows); again this isn't a problem. paste must have equal numbers of rows, and it gets them if the input is properly delimited... However, on re-visiting this, I noticed a problem caused by concatenating the keys (tr -d '\t'), eg. — X\tXX will match XX\tX as XXX— Fortunately, tr can cope with null chars.. Becaues the null char is not used as a delimiter, the other tools a quite okay with it (I have ammended the answer) – Peter.O Oct 26 '11 at 15:47
  • +1 for you fered. I've just read man join. Very nice tool, thanks :). – Grzegorz Wierzowiecki Oct 26 '11 at 17:26
  • For general info: To filter out any malformed lines (rows) which don't have the exact number of fields, you can add another filter to each file name. Eg. expecting 4 fields (3 delimiters), this works: <(sed -nr '/^[^\t]*(\t[^\t]*){3}$/p' a.tsv) – Peter.O Oct 27 '11 at 1:29
  • In my domain, if any line is malformed, I rise error that whole input has problem. So your tip is good, just to check opposite task: if all lines are correct. Good for readers you've pointed it out, thanks :). – Grzegorz Wierzowiecki Oct 28 '11 at 7:44

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

awk '
    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


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.