Joining multiple fields in text files on Unix

Question

How can I do it?

File1 looks like this:

foo 1 scaf 3 
bar 2 scaf 3.3

File2 looks like this:

foo 1 scaf 4.5
foo 1 boo 2.3
bar 2 scaf 1.00

What I want to do is to find lines that co-occur in File1 and File2 when fields 1,2, and 3 are the same.

Is there a way to do it?

Answer 1

Here is the correct answer (in terms of using standard GNU coreutils tools, and not writing custom script in perl/awk you name it).

$ join -j1 -o1.2,1.3,1.4,1.5,2.5 <(<file1 awk '{print $1"-"$2"-"$3" "$0}' | sort -k1,1) <(<file2 awk '{print $1"-"$2"-"$3" "$0}' | sort -k1,1)
bar 2 scaf 3.3 1.00
foo 1 scaf 3 4.5

OK, how does it work:

1. First of all we will use a great tool join which can merge two lines. join has two requirements:

   • We can join only by a single field.
   • Both files must be sorted by key column!

2. We need to generate keys in input files and for that we use a simple awk script:

   $ cat file1
   foo 1 scaf 3
   bar 2 scaf 3.3    
   
   $ <file1 awk '{print $1"-"$2"-"$3" "$0}'
   foo-1-scaf foo 1 scaf 3
   bar-2-scaf bar 2 scaf 3.3
   

   You see, we added 1st column with some key like "foo-1-scaf". We do the same with file2. BTW. <file awk, is just fancy way of writing awk file, or cat file | awk.

   We also should sort our files by the key, in our case this is column 1, so we add to the end of the command the | sort -k1,1 (sort by text from column 1 to column 1)

3. At this point we could just generate files file1.with.key and file2.with.key and join them, but suppose those file are huge, we don't want to copy them over filesystem. Instead we can use something called bash process substitution to generate output into named pipe (this will avoid any unnecessary intermediate file creation). For more info please read the provided link.

   Our target syntax is: join <( some command ) <(some other command)

4. The last thing is to explain fancy join arguments: -j1 -o1.2,1.3,1.4,1.5,2.5

   • -j1 - join by key in 1st column (in both files)

   • -o - output only those fields 1.2 (1st file field2), 1.3 (1st file column 3), etc.

     This way we joined lines, but join outputs only the necessary columns.

The lessons learned from this post should be:

• you should master the coreutils package, those tools are very powerful when combined and you almost never need to write custom program to deal with such cases,
• core utils tools are also blazing fast and heavily tested, so they are always best choice.