Search code examples
unixawkcygwinpaste

Add column to file from another file where ID's match

I have a file like this:-

123,Bob
456,Joe
789,Jane

And a file like this

456,abc,Red
789,def,Yellow

I want to add column 3 from file 2 to file 1 but only where the first column matches, ending up with something like this:-

123,Bob
456,Joe,Red
789,Jane,Yellow

I have any tools available at the Cygwin commandline to do this, e.g. awk, paste etc.

Solution

  • If you have paste, you should also have join which is designed for this sort of thing. Assuming the input files are sorted on the key you can say:

    join -t, in1 in2

    Output:

    456,Joe,Red
789,Jane,Yellow

    Edit to accommodate new requirements

    You can use the -o switch:

    join -t, -o 1.1,1.2,2.3 in1 in2

    You seem to want to keep unpaired lines from the first file, in that case use -a1:

    join -t, -o 1.1,1.2,2.3 -a1 in1 in2

    Output:

    123,Bob,
456,Joe,Red
789,Jane,Yellow

    This leaves an extraneous comma at the end of unpaired lines, but that can be removed with | sed 's/,$//'.