Combining CSV's with Different Columns using Pandas [with key column]

Question

I'm trying to combine two CSV files in Python, each CSV file has unique columns but both CSV files share a common key column.

I've been looking around StackOverflow/Google/Pandas documentation but didn't find exactly what I was looking for. The examples provided on the Pandas documentation pages for merge and concat are different from what I'm trying to achieve so I'm not sure if what I'm asking is possible with Pandas.

I've read in selected columns from both CSV files into separate dataframes, what I would like to do now is combine the two dataframes into a single dataframe based on the key column.

Example

CSV 1:
Key   Make   Model
501   Audi   A3
502   Audi   A4
503   Audi   A5

CSV 2:
Key   Engine
501   2.0T
502   2.0T
503   2.0T

Combined Expected Result:
Key   Make   Model   Engine
501   Audi   A3      2.0T
502   Audi   A4      2.0T
503   Audi   A5      2.0T

kajnwunda · Accepted Answer · 2019-04-11 18:47:44Z

3

You need to read your csvs into 2 separate data frames and then join them on 'Key' column.

import pandas as pd
df1 = pd.read_csv('csv1.csv')
df2 = pd.read_csv('csv2.csv')
df_final = df1.merge(df2, left_on = 'Key', right_on = 'Key')

answered Apr 11, 2019 at 18:47

kajnwunda

752 silver badges7 bronze badges

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

1 Comment

mvx Over a year ago

does this also work for more than 2 csv files as well?

Angel Roman · Accepted Answer · 2019-04-11 21:51:48Z

3

Kacper Sobociński answer is correct, you can use pandas merge.

import pandas as pd

data1 = {'Key': [501,502,503], 
        'Make': ['Audi','Audi','Audi'],
        'Model': ['A3','A4','A5']}

data2 = {'Key':[501,502,503],
         'Engine': ['2.0T', '2.0T','2.0T']}

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)


df = pd.merge(df1,df2, how = 'inner', on = 'Key')

print(df)

   Key  Make Model Engine
0  501  Audi    A3   2.0T
1  502  Audi    A4   2.0T
2  503  Audi    A5   2.0T

answered Apr 11, 2019 at 21:51

Angel Roman

6381 gold badge4 silver badges14 bronze badges

2 Comments

skatingrocker17 Over a year ago

Thanks, that's what I was looking for.

Angel Roman Over a year ago

Happy to help, and welcome to Stack Overflow. If this answer or any other one solved your issue, please mark it as accepted

Collectives™ on Stack Overflow

Combining CSV's with Different Columns using Pandas [with key column]

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related