CODEWITHSUNDEEP
pythoncsvmerge
Gerard
Gerard

Reputation: 518

Merging two csv files with a common column but uneven lengths

I have two csv files: csv file 1 contains the following:

California,C1,G1,K1,Dine-In,B,25
California,C2,G2,K1,Dine-In,A,8
Hawaii,H1,J1,L1,Dine-In,A,22
Hawaii,H2,J2,L2,Dine-In,A,20

csv file 2 contains:

Hawaii,10
California,20

I wanted my output to be:

California,C1,G1,K1,Dine-In,B,25,20
California,C2,G2,K1,Dine-In,A,8,20
Hawaii,H1,J1,L1,Dine-In,A,22,10
Hawaii,H2,J2,L2,Dine-In,A,20,10

I have done my code:

with open(r'file 1.csv', 'r') as f:
    r = csv.reader(f)
    dict2 = {row[0]: row[1:] for row in r}

with open(r'file 2.csv','r') as f:
    r = csv.reader(f)
    dict1 = OrderedDict((row[0], row[1:]) for row in r)

result = OrderedDict()
for d in (dict1, dict2):
    for key, value in d.iteritems():
        result.setdefault(key, []).extend(value)

with open('combined data.csv', 'wb') as f:
    w = csv.writer(f)
    for key, value in result.iteritems():
        w.writerow([key] + value)

but it gives me an output of:

California,C1,G1,K1,Dine-In,B,25
California,C2,G2,K1,Dine-In,A,8
Hawaii,H1,J1,L1,Dine-In,A,22
Hawaii,H2,J2,L2,Dine-In,A,20
Hawaii,10
California,20

got any ideas on this?

Upvotes: 0

Views: 1280

Answers (2)

Mike Müller
Mike Müller

Reputation: 85512

A pandas solution

import pandas pd

df1 = pd.read_csv('file1.csv', header=None)
df2 = pd.read_csv('file2.csv', header=None)
res = pd.merge(df1, df2, on=0)
res.to_csv('combined.csv', header=None, index=False)

combined.csv: 

California,C1,G1,K1,Dine-In,B,25,20
California,C2,G2,K1,Dine-In,A,8,20
Hawaii,H1,J1,L1,Dine-In,A,22,10
Hawaii,H2,J2,L2,Dine-In,A,20,10

In Steps

Read the first file into a data frame:

df1 = pd.read_csv('file1.csv', header=None)

It looks like this:

            0   1   2   3        4  5   6
0  California  C1  G1  K1  Dine-In  B  25
1  California  C2  G2  K1  Dine-In  A   8
2      Hawaii  H1  J1  L1  Dine-In  A  22
3      Hawaii  H2  J2  L2  Dine-In  A  20

Do the same for the second file:

df2 = pd.read_csv('file2.csv', header=None)

Results in:

            0   1
0      Hawaii  10
1  California  20

Merge at column 0: 

res = pd.merge(df1, df2, on=0)

Now, res looks like this:

            0 1_x   2   3        4  5   6  1_y
0  California  C1  G1  K1  Dine-In  B  25   20
1  California  C2  G2  K1  Dine-In  A   8   20
2      Hawaii  H1  J1  L1  Dine-In  A  22   10
3      Hawaii  H2  J2  L2  Dine-In  A  20   10

Finally, write to a csv file without the header and the index:

res.to_csv('combined.csv', header=None, index=False)

Upvotes: 2

Martin Evans
Martin Evans

Reputation: 46779

You only need to load file 2.csv in as a dictionary, and then append this to each row when reading file 1.csv as follows:

import csv

with open(r'file 2.csv','rb') as f_file2:
    dict2 = {row[0]: row[1:] for row in csv.reader(f_file2)}

with open(r'file 1.csv', 'rb') as f_file1, open('combined data.csv', 'wb') as f_output:
    csv_output = csv.writer(f_output)

    for row in csv.reader(f_file1):
        csv_output.writerow(row + dict2[row[0]])

Giving you:

California,C1,G1,K1,Dine-In,B,25,20
California,C2,G2,K1,Dine-In,A,8,20
Hawaii,H1,J1,L1,Dine-In,A,22,10
Hawaii,H2,J2,L2,Dine-In,A,20,10

Upvotes: 3

Related Questions