Merge two CSV files using bash/python

Question

I have two CSV files that I need help mapping/merging:

CSV File1:

"ID","Name","Flavor"
"45fc754d-6a9b-4bde-b7ad-be91ae60f582","test1","m1.medium"
"83dbc739-e436-4c9f-a561-c5b40a3a6da5","test2","m1.tiny"
"ef68fcf3-f624-416d-a59b-bb8f1aa2a769","test3","m1.medium"

CSV File2:

"Name","RAM","Disk","VCPUs"
"m1.medium",4096,40,2
"m1.xlarge",16384,160,8
"m1.tiny",128,1,1

The ideal output would be:

"ID","Name","Flavor","RAM","Disk","VCPUs"
"45fc754d-6a9b-4bde-b7ad-be91ae60f582","test1","m1.medium",4096,40,2
"83dbc739-e436-4c9f-a561-c5b40a3a6da5","test2","m1.tiny",128,1,1
"ef68fcf3-f624-416d-a59b-bb8f1aa2a769","test3","m1.medium",4096,40,2

Note that Flavor in CSV File 1 and Name in CSV File 2 are the same. The difference in name is a result of the different tool being used to pull the info.

Also note, CSV File2 has a flavor/name m1.xlarge. As detailed above, if the m1.xlarge flavor/name is not found in CSV File1 it should be discarded from the consolidated output.

I've been at this all day with mixed results. Any ideas would be appreciated.

Answer 1

Something like this, but you'll have to play around with the quoting options to see what you like.

#!/usr/bin/env python3

import csv

by_name = {}
with open('b.csv') as b:
    for row in csv.DictReader(b):
        name = row.pop('Name')
        by_name[name] = row

with open('c.csv', 'w') as c:
    w = csv.DictWriter(c, ['ID', 'Name', 'Flavor', 'RAM', 'Disk', 'VCPUs'])
    w.writeheader()

    with open('a.csv') as a:
        for row in csv.DictReader(a):
            try:
                match = by_name[row['Flavor']]
            except KeyError:
                continue

            row.update(match)

            w.writerow(row)

Output:

ID,Name,Flavor,RAM,Disk,VCPUs
45fc754d-6a9b-4bde-b7ad-be91ae60f582,test1,m1.medium,4096,40,2
83dbc739-e436-4c9f-a561-c5b40a3a6da5,test2,m1.tiny,128,1,1
ef68fcf3-f624-416d-a59b-bb8f1aa2a769,test3,m1.medium,4096,40,2