UNIX for Dummies Questions & Answers

Hi,

I have two text files that I would like to merge/join. I would like to join them if the first columns of both text files match and the second column of the first text file matches the third column of the second text file.

Example input:
First file:

Code:

1334 10 0 0 1 5.2
1334 12 0 0 1 4.5
1340 9 0 0 1 5.6
1340 11 0 0 1 3.5

Second file

Code:

1334 14 10 0 2 5.5
1341 2 13 14 2 5.6
1341 13 0 0 1 5.5
1340 8 11 0 1 5.6
1340 10 0 0 2 5.5

Output file:

Code:

1334 10 0 0 1 5.2 1334 14 10 0 2 5.5
1340 11 0 0 1 3.5 1340 8 11 0 1 5.6

Thanks!

To keep the forums high quality for all users, please take the time to format your posts correctly.

First of all, use Code Tags when you post any code or data samples so others can easily read your code. You can easily do this by highlighting your code and then clicking on the # in the editing menu. (You can also type code tags [code] and [/code] by hand.)

Second, avoid adding color or different fonts and font size to your posts. Selective use of color to highlight a single word or phrase can be useful at times, but using color, in general, makes the forums harder to read, especially bright colors like red.

Third, be careful when you cut-and-paste, edit any odd characters and make sure all links are working property.

Thank You.

The UNIX and Linux Forums

Code:

$ awk 'NR==FNR{D[$1]=$0}; (NR!=FNR) && D[$1] && (!S[$1]++) { print D[$1], $0 }' file1 file2

1334 12 0 0 1 4.5 1334 14 10 0 2 5.5
1340 11 0 0 1 3.5 1340 8 11 0 1 5.6

$

The output is not exactly what I want. I want to the first column of the first file to match the first column of the second file, as well as the second column of the first file to match the third column of the second file. Thanks!

Code:

$ awk 'NR==FNR { C[$1]++; L[$1, C[$1]]=$0; D[$1, C[$1]]=$2 ; next }
C[$1] { for(N=1; N<=C[$1]; N++) if(D[$1, N]==$3) print L[$1, N], $0 }' file1 file2

1334 10 0 0 1 5.2 1334 14 10 0 2 5.5
1340 11 0 0 1 3.5 1340 8 11 0 1 5.6

$