Merge two files and plus the second column using AWK

Question

I would like to merge two files and plus the second column from two files.

file1.

001    A
002    B
003    C
004    D

file2.

002    D
003    D
005    E
006    F

should be merged into file 3 as below.

001    A
002    BD
003    CD
004    D
005    E
006    F

I run the following command but output was not correct

$ awk 'FNR==NR{a[$1]=$2; next}{print $1, a[$1]$2}' file1 file2
002    BD
003    CD
005    E
006    F

Please help how I can merge use awk or sed.

Answer 1

Left/Right Outer Join vs. Full Outer Join

The issue with the command that you are using is that it requires that all entries in file2 are in file1. The print section of the statement is only evaluating the entries that are in file2.

FNR and NR are frequently used in this manner to JOIN based on a commonality - picking all records from one dataset and only related records from the other data set. What you have implemented is specifically a 'RIGHT OUTER JOIN', as it will pull in all entries from the 'right' side - file2, and matching members of the left side, file1.

Instead, you want to conduct a 'FULL OUTER JOIN'. All records in both files with a merging of records based on column 1.

FNR==NR means that the 'FILE' number of records (FNR) is equal to the Total Number of Records (NR). NR will increment for each line from any file is processed, while FNR will reset to 0 when starting a new file. So FNR==NR is only true when importing 2+ files while reading in the first file. When awk switches to the next file, FNR will reset to 0 while NR will continue to grow.

To illustrate this I inserted a print statement to provide the state of these variables as awk processes the input:

$> awk 'FNR==NR{a[$1]=$2;printf("File: %s, NR: %s, FNR: %s, $1: %s, $2: %s, a[$1]: %s\n",FILENAME,NR,FNR,$1,$2,a[$1]); next}  {printf("File: %s, NR: %s, FNR: %s, $1: %s, $2: %s, a[$1]$2: %s\n",FILENAME,NR,FNR,$1,$2,a[$1]$2); }' file1 file2

File: file1, NR: 1, FNR: 1, $1: 001, $2: A, a[$1]: A
File: file1, NR: 2, FNR: 2, $1: 002, $2: B, a[$1]: B
File: file1, NR: 3, FNR: 3, $1: 003, $2: C, a[$1]: C
File: file1, NR: 4, FNR: 4, $1: 004, $2: D, a[$1]: D
File: file2, NR: 5, FNR: 1, $1: 002, $2: D, a[$1]$2: BD
File: file2, NR: 6, FNR: 2, $1: 003, $2: D, a[$1]$2: CD
File: file2, NR: 7, FNR: 3, $1: 005, $2: E, a[$1]$2: E
File: file2, NR: 8, FNR: 4, $1: 006, $2: F, a[$1]$2: F

Solution

To fix it, all you need to do is continue to add entries to the array while processing file2, and only output the results after processing all input files.

So in this case we don't actually care about NR or FNR at all.

• For each line of text from all input files, use it's column one value $1 as an index into an array a[$1]

• Assign the value of column 2, $2, to the array at that index, but append the value so we don't overwrite a value that may already exist: a[$1]=a[$1]$2

• Wait until all records/lines have been processed before printing out the array:

  for (i in a) { printf("%s\t%s\n", i, a[i]) }

The only downside to this is that awk uses associative arrays which use string based indices, not integer (which is why this works at all), but a side effect of that is that the order of entries in the array may not be as intended; in this case printing out the contents is not in numerical order (by index) so piping the output to sort is required:

$> awk '{ a[$1]=a[$1]$2; next } END { for (i in a) { printf("%s\t%s\n", i, a[i]) } }' file1 file2 | sort -n 

001     A
002     BD
003     CD
004     D
005     E
006     F

Alternate Approach

You can also use the join command to do this but I don't know how to get it to combine the fields - they remain space delimited, so an additional stage of processing is required:

$> join  -o 0,1.2,2.2 -a1 -a2 file1 file2 | awk '{printf("%s\t%s%s\n", $1, $2, $3)}'
001     A
002     BD
003     CD
004     D
005     E
006     F

TODO

This does nothing to reject duplicate entries - which may or may not be desired. Currently if you have duplicate records in seperate input files, they will be combined:

file1: 001 A
and
file2: 001 A
will result in an output record of
001 AA

Answer 2

Now that I've seen a research effort from you, I give you my solution:

awk '{ z[$1]=z[$1]$2 } END { for (i in z) print i, z[i] }' file1  file2

The output is:

002 BD
003 CD
004 D
005 E
006 F 
001 A

If you want to do a numeric sort on the values of the first column, you can pass the output of the previous command to sort:

awk '{ z[$1]=z[$1]$2 } END { for (i in z) print i, z[i] }' file1  file2 \
| sort -n -k1