combining arrays into matrix - adding delimiters between cells

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    B D
003    C D
004    D
005    E
006    F

I tried this command, suggested by andreatsh here: Merge two files and plus the second column using AWK:

$ awk '{ z[$1]=z[$1]$2 } END { for (i in z) print i, z[i] }' file1  file2
002 BD
003 CD
004 D

etc...

That's very close, but how could I add a space between the letters?

e.g. instead of "BD" in the first line, rather "B D"

Answer 1

Yes, you can do it with Awk, but Join was born for this task:

join -a1 -a2 file1 file2

Output from given samples:

001 A
002 B D
003 C D
004 D
005 E
006 F

Note that the files need to be sorted before running join. If your shell supports it, you can do it in one step using process substitution:

join -a1 -a2 <(sort file1) <(sort file2)

Answer 2

The right tool for this is indeed join, as Quasimodo showed you, but you can also use the same awk command with a very minor modification:

$  awk '{ z[$1]=z[$1]" "$2 } END { for (i in z) print i, z[i] }' file1  file2
002  B D
003  C D
004  D
005  E
006  F
001  A

I only changed z[$1]=z[$1]$2 to z[$1]=z[$1]" "$2.

Answer 3

I added 004 D to file2 so we'd have a non-obvious case to test with. Given that, either this:

$ sort -k1,1 -s file1 file2 |
awk '
    $1 != prev { if (NR>1) print ""; printf "%s", $1; prev=$1 }
    { printf " %s", $2 }
    END { print "" }
'
001 A
002 B D
003 C D
004 D D
005 E
006 F

or this:

$ sort -k1,1 -s file1 file2 |
awk '
    $1 != prev{if (NR>1) print ""; printf "%s", $1; prev=$1; delete seen }
    !seen[$2]++ { printf " %s", $2 }
    END { print "" }
'
001 A
002 B D
003 C D
004 D
005 E
006 F

depending on how you want duplicate values for the same key handled. Just list as many files as you like on the sort line. The above assumes GNU sort for -s to retain input order for the same keys, if you don't have that and actually need it there are simple alternatives. You can also trivially tweak that to always get the A B C, etc. to be in alphabetic order on each output line rather than the order they occur in each input file if you like, e.g.:

$ head file*
==> file1 <==
001    A
002    E
003    F
004    D

==> file2 <==
002    D
003    D
004    D
005    E
006    F

==> file3 <==
001    A
002    E
003    C
004    D

$ sort -s file* | awk '$1 != prev{if (NR>1) print ""; prev=$1; delete seen; printf "%s", $1} !seen[$2]++{printf " %s", $2} END{print ""}'
001 A
002 D E
003 C D F
004 D
005 E
006 F