Merge files using a common column

Solution 1:

Using join:

join -1 1 -2 2 -a1 -e0 -o'0,1.2,2.1' file2 file1

The join command joins the lines of two files which share a common field of data. In this case: Join the file2 and the file1 using the field 1 ( -1 1) of the file2 and the field 2 ( -2 2) of the file1.

The output will be: "joined field, field 2 of file2, field 1 of file1" (-o'0,1.2,2.1'), if there is a missing field put 0 (-e0)

If one of the two files have more records then add them (in this case file2) (-a1)

Please refer to the manpage of the command join

Solution 2:

The join command does almost what you need, if the files are sorted as in your samples:

join -12 -a2 file1 file2 -o2.1,2.2,1.1

You just need to add the zeroes to the lines with no match. You can use the -e switch for that:

join -12 -a2 file1 file2  -o2.1,2.2,1.1 -e0