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.

The problem with your question is that you haven't specified the operation other than 'mapping/merging'. Do you simply search CSV2 for the first(?) occurrence of a line that also has "m1.medium"? Or for the last? Is the first field in CSV2 unique? Etc. I suppose specifying the 'mapping/merging' operation to /yourself/ is at least 50% of the job done. — user2849202
– user2849202, Commented May 6, 2020 at 21:43
What is the issue, exactly? Please be more specific. See How to Ask, help center. — AMC
– AMC, Commented May 6, 2020 at 21:53
@Javier do you want the CSVs joined on the Name/Flavor columns or just merge all the columns into one file keeping the rows where they are? — user3064538
– user3064538, Commented May 6, 2020 at 21:56

anubhava · Accepted Answer · 2020-05-06 21:53:22Z

2

You may use this awk:

awk -v hdr='"ID","Name","Flavor","RAM","Disk","VCPUs"' 'BEGIN {
   FS=OFS=","
   print hdr
}
NR == FNR {
   a[$1] = $2 FS $3 FS $4
   next
}
$3 in a {
   print $0, a[$3]
}' file2.csv file1.csv

"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

answered May 6, 2020 at 21:53

anubhava

790k67 gold badges603 silver badges671 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chash · Accepted Answer · 2020-05-06 21:51:26Z

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

user3064538 · Accepted Answer · 2020-05-06 22:02:52Z

If I understood your question correctly and you want to match up rows in the first file based on the string in the Flavor column with the rows that have that value in the Name column in the second csv, then this is quite easy to do with xsv (which you'll likely need to install first):

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

You'll have to also remove the duplicate Name column, which you can do with xsv again:

$ xsv join "Flavor" file1.csv "Name" file2.csv | xsv select ID,Name,Flavor,RAM,Disk,VCPUs
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

Collectives™ on Stack Overflow

Merge two CSV files using bash/python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related