Take the 2-minute tour ×

I'd like to join csv files in Ubuntu.

ID_a, ID_b, a,  b,  c
key_a, A,   a1, b1, c1
key_a, B,   a2, b2, c2
key_b, A,   a3, b3, c3

ID_a, ID_b, d,  e,  f
key_a, A,   d1, e1, f1
key_a, B,   d2, e2, f2
key_b, A,   d3, e3, f3

ID_a, ID_b, a, b,  c,  d,  e,  f
key_a, A,  a1, b1, c1, d1, e1, f1
key_a, B,  a2, b2, c2, d2, e2, f2
key_b, A,  a3, b3, c3, d3, e3, f3

The input CSV files should be joined on common columns in their header. Is there a stock solution to this, or should I write my own script to do it?

share|improve this question
Duplicate: stackoverflow.com/questions/2619562/… –  jmetz Jul 25 '12 at 16:12

2 Answers 2

Try the join command:

NAME join - join lines of two files on a common field



So you should be able to do:

join file_A.csv file_B.csv > file_AB.csv

You may have to join your first and second fields into one for this to work though - as in essence they can be seen as one field anyway.

I just double checked and it seems to work as long as your files have the format e.g.:

ID_aID_b, a,  b,  c
key_aA,   a1, b1, c1
key_aB,   a2, b2, c2
key_bA,   a3, b3, c3

as I mentioned above.

share|improve this answer
I don't think this'll work for me. I'd need to do scripting to merge then split the ID columns, so is would be just as easy to script the join. –  Andrew Wood Jul 25 '12 at 15:44
@ajwood: That's unfortunate - in that case some amount of scripting will likely be needed. –  jmetz Jul 25 '12 at 16:04
@ajwood - see my comment on the question itself - there is a very similar question already posted on stackoverflow. –  jmetz Jul 25 '12 at 16:17

Here is my solution in Python

import sys
import csv

def main(args):
    # store each header we read
    headers = []

    # Intersect headers to get our keys
    for arg in args:
        with open(arg) as f:
            curr = csv.reader(f).next()
                keys = list( set(keys) & set(curr) )
            except NameError:
                keys = curr

    # New header
    header = list(keys)
    for h in headers:
        header += [ k for k in h if k not in keys ]

    # Join data
    data = {}
    for arg in args:
        with open(arg) as f:
            reader = csv.DictReader(f)
            for line in reader:
                data_key = tuple([ line[k] for k in keys ])
                if not data_key in data: data[data_key] = {}
                for k in header:
                        data[data_key][k] = line[k]
                    except KeyError:

    # Drop keys that are missing data (keys not present in all files)
    for key in data.keys():
        for col in header:
            if key in data and not col in data[key]:
                del( data[key] )

    # Dump data
    print ','.join(header)
    for key in sorted(data):
        row = [ data[key][col] for col in header ]
        print ','.join(row)

if __name__ == '__main__':
    sys.exit( main( sys.argv[1:]) )
share|improve this answer

Your Answer


