Pandas merge 2 csv with a similar column but different header name

Question

I want to merge 2 csv file with a similar column but different header name.

a.csv:

id name country
1 Cyrus MY
2 May US

b.csv:

user_id  gender 
1 female
2 male

What I need is, c.csv:

id name country gender
1 Cyrus MY female
2 May US male

But the result I get when I use the below code

import csv
import pandas as pd

df1 = pd.read_csv('a.csv')
df2 = pd.read_csv('b.csv')

df3 = pd.merge(df1,df2, left_on=['id'],right_on=['user_id'], how='outer')
df3.to_csv('c.csv',index=False)

The result I get:

id name country user_id gender
1 Cyrus MY 1 female
2 May US 2 male

I guess df3.drop('user_id ', inplace=True, axis=1) would solve your problem. — czr
– czr, Commented Mar 16, 2018 at 3:57

Shaido · Accepted Answer · 2018-03-16 04:00:16Z

1

You could rename the user_id column in df2 to id. Since the name is the same, it won't be duplicated.

df2 = pd.read_csv('b.csv').rename(columns={'user_id': 'id'})
df3 = pd.merge(df1, df2, on='id', how='outer')

Otherwise you can drop the user_id column adter the merge.

df3 = df3.drop('user_id', axis=1)

answered Mar 16, 2018 at 4:00

Shaido

28.6k26 gold badges76 silver badges82 bronze badges

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

Comments

BENY · Accepted Answer · 2018-03-16 04:03:44Z

1

You can do with merge

df1.merge(df2,left_on='id',right_on='user_id')
Out[35]: 
   id   name country  user_id  gender
0   1  Cyrus      MY        1  female
1   2    May      US        2    male

Or concat

pd.concat([df1.set_index('id'),df2.set_index('user_id')],1).reset_index()
Out[38]: 
   index   name country  gender
0      1  Cyrus      MY  female
1      2    May      US    male

answered Mar 16, 2018 at 4:03

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Pandas merge 2 csv with a similar column but different header name

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related