Tjyylli

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?

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.

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:]) )