Merge files using a common column

Question

I have two files out of which I want to create a third which contains all the information.

file 1:

a 111 
b 222 
c 333 
d 666 
e 777 

file 2:

111 x1  
222 x2
333 x3
444 x4 
555 x5 
666 x6 
777 x7 
888 x8

I would like to combine them as following:

111  x1  a
222  x2  b
333  x3  c
444  x4  0
555  x5  0
666  x6  d
777  x7  e
888  x8  0

Note:

The second column of file 1 is a subset of the first column of file 2

Answer 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

Answer 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

Answer 3

A little bit awk magic:

awk 'FNR==NR{a[$2]=$1;next}{if(a[$1]==""){a[$1]=0}; \
    printf "%s%s%s%s%s\n",$1,FS,$2,FS,a[$1]}' \
    file1 file2

or

awk 'FNR==NR{a[$2]=$1;next}{if(a[$1]==""){a[$1]=0};
    print $1,$2,a[$1]}' file1 file2

---

Output

111 x1 a
222 x2 b
333 x3 c
444 x4 0
555 x5 0
666 x6 d
777 x7 e
888 x8 0

---

Explanation

• FNR==NR{a[$2]=$1;next}

  Runs over file1 (FNR==NR) and creates a key-value structure. The key is the second column ($2) of file1, the value is the first column ($1) of file1

• {if(a[$1]==""){a[$1]=0};print $1,$2,a[$1]}

  Runs over file2 and

  • if(a[$1]==""){a[$1]=0}

    If the key in the first column ($1) in file2 doesn't exist in file1, we need a 0

  • print $1,$2,a[$1]

    Print (using print) the first and the second column of file2 and the value of the key-value structure with the key of the first column ($1) of file2

    or

  • printf "%s%s%s%s%s\n",$1,FS,$2,FS,a[$1]}'

    Print (using printf) the first and the second column of file2 and the value of the key-value structure with the key of the first column ($1) of file2.

    • FS is the separator between the columns, taken from the input file

    • "%s%s%s%s%s\n"

      is the formatting for the output

      • %s - String

      • \n – Newline

Answer 4

Using q:

$ q "select f2.c1, f2.c2, ifnull(f1.c1,0) from file_2.txt f2 LEFT JOIN file_1.txt f1 on f1.c2 = f2.c1 "
111 x1 a
222 x2 b
333 x3 c
444 x4 0
555 x5 0
666 x6 d
777 x7 e
888 x8 0

It may be more readable this way sometimes.