Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'd like to join csv files in Ubuntu.

file_A.csv:
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

file_B.csv:
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

join_AB.csv
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

SYNOPSIS join [OPTION]... FILE1 FILE2

DESCRIPTION For each pair of input lines with identical join fields, write a line to standard output. The default join field is the first, delimited by whitespace. When FILE1 or FILE2 (not both) is -, read standard input.

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.:

file_A.csv
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()
            headers.append(curr)
            try:
                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:
                    try:
                        data[data_key][k] = line[k]
                    except KeyError:
                        pass

    # 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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.