2

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"

4
  • 2
    You can straightforwardly modify {print $1, a[$1]$2} to {print $1, a[$1] OFS $2} or indeed {print $1, a[$1] "any string here" $2} Nov 13, 2020 at 15:02
  • What should the output be if 004 D also existed in file2 - 004 D or 004 D D? Does the order of output lines matter and, if so, what should it be based on?
    – Ed Morton
    Nov 13, 2020 at 22:43
  • Hi Ed, I have pre-processed the files so that the column order is always as expected. as a newbie, I have to break the overall pipeline into little chunks :)
    – steff2j
    Nov 14, 2020 at 14:06
  • @steff2j thanks, and how about how to handle duplicate column values? Btw if you don't put @EdMorton in the comment then I don't get notified that you've posted anything for me to read.
    – Ed Morton
    Nov 14, 2020 at 14:56

3 Answers 3

4

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)
2
  • super elegant, can't believe I couldn't get that. can this work for more than two files. the lists are pre-sorted, so that is not an issue.
    – steff2j
    Nov 13, 2020 at 18:57
  • 1
    @steff2j According to Join's manual, only two files can be given, so not in a single command. You could create a simple shell loop to handle various files, but in that case I think I'd go for Awk, because shell loops are slow.
    – Quasímodo
    Nov 13, 2020 at 23:19
3

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.

1
  • amazing - thank you! This works well for combining multiple files as well.
    – steff2j
    Nov 13, 2020 at 18:56
1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .