8

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

8

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
| improve this answer | |
  • if you add the -e0 do not need perl :) – LilloX Dec 9 '15 at 17:17
  • @LilloX: True, thanks. I tried but failed (typo). – choroba Dec 9 '15 at 17:23
13

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

| improve this answer | |
  • Good. Could you add a bit of explanation? – Lety Dec 9 '15 at 17:26
  • Sure, Updated :) – LilloX Dec 9 '15 at 18:07
5

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

| improve this answer | |
  • Could you explain the code? – PyariBilli Dec 9 '15 at 17:20
  • Sure, answer updated. – A.B. Dec 9 '15 at 17:29
1

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.

| improve this answer | |
  • 1
    For anyone wondering, q is in the package python3-q-text-as-data (Python 3) and in the package python-q-text-as-data (Python 2). – kos Dec 10 '15 at 1:06
  • Thanks, but where can I get this q package? I don't seem to be able to install either python-q-text-as-data or python3-q-text-as-data. "E: Unable to locate package python3-q-text-as-data". My system already has installed python, python2.7, python3, and python3.4. – Paddy Landau Dec 15 '15 at 13:42
  • Maybe the package is too new and is not available in your distribution release. You can clone it Github: github.com/harelba/q – Vi. Dec 15 '15 at 16:17

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.