Python how to merge two csv files

user_5233403 0
Viewed 831

Im trying to merge two csv files (a and b) into one (c). The csv files look like this:

--- CSV A ---

Field_1, Key
a0     , k0
a1     , k1
a2     , k2
a3     , k0
a4     , k2
a5     , k0

--- CSV B ---

Key, Field_2, Field_3
k0 , b0     , c0
k1 , b1     , c1
k2 , b2     , c2
k3 , b3     , c3

--- Expected CSV C (merged csv) ---

Field_1, Key, Field_2, Field_3
a0     , k0 , b0     , c0
a1     , k1 , b1     , c1
a2     , k2 , b2     , c2
a3     , k0 , b0     , c0
a4     , k2 , b2     , c2
a5     , k0 , b0     , c0

So basically the fields from the csv b that matches the key of csv a should be joined to get csv c. But instead Im getting the next merged fields empty

--- Actual CSV C ---

Field_1, Key, Field_2, Field_3
a0     , k0 ,        , 
a1     , k1 ,        , 
a2     , k2 ,        , 
a3     , k0 ,        , 
a4     , k2 ,        , 
a5     , k0 ,        ,

This is the code Im trying to use to merge this fields. But as I said, I am unable to get the data from the csv b merged, I only get the headers.

a = pd.read_csv("a.csv")
b = pd.read_csv("b.csv").rename(columns={'Key': ' Key'})
result = a.merge(b, on=" Key", how="left")
result.to_csv("c.csv", index=False)

So how would I do so I get the data from csv b merged correctly? Thanks.

pandas csv merge python-3.x python
3 Answers

Just change how property to outer.

result = a.merge(b, on=" Key", how = "outer")

left use only keys from left frame, similar to a SQL left outer join; preserve key order

user_65831400

The merged data is empty because of incorrect key. As per your data keys in b.csv is different from keys in a.csv as it contains an extra space.

Your code will work for these data

a.csv

Field_1,Key a0, k0 a1, k1 a2, k2 a3, k0 a4, k2 a5, k0

b.csv

Key, Field_2, Field_3 k0, b0, c0 k1, b1, c1 k2, b2, c2 k3, b3, c3

user_54600180

The problem is that you have spaces(_) in your key field. In a.csv you have "_key" (e.g. "k0") and in b.csv you have "key" (e.g. "k0_") so the keys does not match. If you remove the blank spaces in the csv file this code works:

import pandas as pd

a = pd.read_csv("a.csv",sep=",")
b = pd.read_csv("b.csv",sep=",")

pd.merge(a,b,on="Key",how="left")

You can use skipinitialspace=True during import for a.csv as the whitespace is in front like so :

a = pd.read_csv("a.csv",sep=",",skipinitialspace=True)

or you define a function that strips any whitespace and apply it at the import: 

def trim(dataset):

    trim = lambda x: x.strip() if type(x) is str else x #Stripping whitespaces in values

    dataset = dataset.rename(columns=lambda x: x.strip()) #Stripping whitespaces in colnames

    return dataset.applymap(trim)

a = trim(pd.read_csv("a.csv",sep=","))
b = trim(pd.read_csv("b.csv",sep=","))
user_94835590
Related