32

OK I have read several threads here on Stack Overflow. I thought this would be fairly easy for me to do but I find that I still do not have a very good grasp of Python. I tried the example located at How to combine 2 csv files with common column value, but both files have different number of lines and that was helpful but I still do not have the results that I was hoping to achieve.

Essentially I have 2 csv files with a common first column. I would like to merge the 2. i.e.

filea.csv

title,stage,jan,feb
darn,3.001,0.421,0.532
ok,2.829,1.036,0.751
three,1.115,1.146,2.921

fileb.csv

title,mar,apr,may,jun,
darn,0.631,1.321,0.951,1.751
ok,1.001,0.247,2.456,0.3216
three,0.285,1.283,0.924,956

output.csv (not the one I am getting but what I want)

title,stage,jan,feb,mar,apr,may,jun
darn,3.001,0.421,0.532,0.631,1.321,0.951,1.751
ok,2.829,1.036,0.751,1.001,0.247,2.456,0.3216
three,1.115,1.146,2.921,0.285,1.283,0.924,956

output.csv (the output that I actually got)

title,feb,may
ok,0.751,2.456
three,2.921,0.924
darn,0.532,0.951

The code I was trying:

'''
testing merging of 2 csv files
'''
import csv
import array
import os

with open('Z:\\Desktop\\test\\filea.csv') as f:
    r = csv.reader(f, delimiter=',')
    dict1 = {row[0]: row[3] for row in r}

with open('Z:\\Desktop\\test\\fileb.csv') as f:
    r = csv.reader(f, delimiter=',')
    #dict2 = {row[0]: row[3] for row in r}
    dict2 = {row[0:3] for row in r}

print str(dict1)
print str(dict2)

keys = set(dict1.keys() + dict2.keys())
with open('Z:\\Desktop\\test\\output.csv', 'wb') as f:
    w = csv.writer(f, delimiter=',')
    w.writerows([[key, dict1.get(key, "''"), dict2.get(key, "''")] for key in keys])

Any help is greatly appreciated.

jww
  • 97,681
  • 90
  • 411
  • 885
Rex
  • 583
  • 2
  • 6
  • 15
  • Can you describe what you want in a simpler way?, maybe telling something like: I want the month columns to be merged, rest columns from fileX – sites Apr 28 '13 at 17:53

2 Answers2

93

When I'm working with csv files, I often use the pandas library. It makes things like this very easy. For example:

import pandas as pd

a = pd.read_csv("filea.csv")
b = pd.read_csv("fileb.csv")
b = b.dropna(axis=1)
merged = a.merge(b, on='title')
merged.to_csv("output.csv", index=False)

Some explanation follows. First, we read in the csv files:

>>> a = pd.read_csv("filea.csv")
>>> b = pd.read_csv("fileb.csv")
>>> a
   title  stage    jan    feb
0   darn  3.001  0.421  0.532
1     ok  2.829  1.036  0.751
2  three  1.115  1.146  2.921
>>> b
   title    mar    apr    may       jun  Unnamed: 5
0   darn  0.631  1.321  0.951    1.7510         NaN
1     ok  1.001  0.247  2.456    0.3216         NaN
2  three  0.285  1.283  0.924  956.0000         NaN

and we see there's an extra column of data (note that the first line of fileb.csv -- title,mar,apr,may,jun, -- has an extra comma at the end). We can get rid of that easily enough:

>>> b = b.dropna(axis=1)
>>> b
   title    mar    apr    may       jun
0   darn  0.631  1.321  0.951    1.7510
1     ok  1.001  0.247  2.456    0.3216
2  three  0.285  1.283  0.924  956.0000

Now we can merge a and b on the title column:

>>> merged = a.merge(b, on='title')
>>> merged
   title  stage    jan    feb    mar    apr    may       jun
0   darn  3.001  0.421  0.532  0.631  1.321  0.951    1.7510
1     ok  2.829  1.036  0.751  1.001  0.247  2.456    0.3216
2  three  1.115  1.146  2.921  0.285  1.283  0.924  956.0000

and finally write this out:

>>> merged.to_csv("output.csv", index=False)

producing:

title,stage,jan,feb,mar,apr,may,jun
darn,3.001,0.421,0.532,0.631,1.321,0.951,1.751
ok,2.829,1.036,0.751,1.001,0.247,2.456,0.3216
three,1.115,1.146,2.921,0.285,1.283,0.924,956.0
DSM
  • 342,061
  • 65
  • 592
  • 494
  • how one can do a .merge based on different name columns ? eg. like aTable on column A merges bTable on column B . – Jorge Vidinha May 21 '14 at 13:28
  • 2
    @JorgeVidinha: if you have a new question, please open a new question -- if you ask it as a comment on a year-old question, no one will see it. – DSM May 21 '14 at 13:30
  • 2
    Elegant solution, even 4 years later. Be wary, however, of the `.dropna()` method without using `how="all"`; otherwise it may drop columns if ANY cells are empty. – WillardSolutions Jul 14 '17 at 19:56
  • Is there a better method for very large csv files? (On the scale of millions of lines.) – hoffee Jul 17 '18 at 14:27
  • great answer DSM how do you save the file in an other place then where the script is saved? – Marine Galantin Oct 28 '19 at 14:50
1

You need to store all of the extra rows in the files in your dictionary, not just one of them:

dict1 = {row[0]: row[1:] for row in r}
...
dict2 = {row[0]: row[1:] for row in r}

Then, since the values in the dictionaries are lists, you need to just concatenate the lists together:

w.writerows([[key] + dict1.get(key, []) + dict2.get(key, []) for key in keys])
Xymostech
  • 9,710
  • 3
  • 34
  • 44