Pandas merge two dataframes without duplicating column

Question

My question is similar to Pandas Merge - How to avoid duplicating columns but I cannot find a solution for the specific example below.

I have DateFrame df:

Customer    Address
J. Smith    10 Sunny Rd Timbuktu

and Dataframe emails:

Name        Email
J. Smith    [email protected]

I want to merge the two dataframes to produce:

Customer    Address                 Email
J. Smith    10 Sunny Rd Timbuktu    [email protected]

I am using the following code:

data_names = {'Name':data_col[1], ...}
mapped_name = data_names['Name']
df = df.merge(emails, how='inner', left_on='Customer', right_on=mapped_name)

The result is:

Customer    Address                 Email                 Name
J. Smith    10 Sunny Rd Timbuktu    [email protected]   J. Smith

While I could just delete the column named mapped_name, there is the possibility that the mapped_name could be 'Customer' and in that case I dont want to remove both Customer columns.

Any ideas?

You can even drop unwanted column using df.drop(mapped_name, axis=1, inplace=True) — Akshay Kandul
– Akshay Kandul, Commented Jun 23, 2017 at 5:25

jezrael · Accepted Answer · 2017-06-23 05:32:05Z

2

I think you can rename first column in email dataframe to Customer, how='inner' can be omit because default value:

emails.columns = ['Customer'] + emails.columns[1:].tolist()

df = df.merge(emails, on='Customer')
print (df)
   Customer               Address                Email
0  J. Smith  10 Sunny Rd Timbuktu  [email protected]

And similar solution as another answer - is possible rename first column selected by [0]:

df = df.merge(emails.rename(columns={emails.columns[0]:'Customer'}), on='Customer')
print (df)
   Customer               Address                Email
0  J. Smith  10 Sunny Rd Timbuktu  [email protected]

edited Jun 23, 2017 at 5:32

answered Jun 23, 2017 at 5:20

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Comments

Allen Qin · Accepted Answer · 2017-06-23 05:27:38Z

1

You can just rename your email name column to 'Customer' and then merge. This way, you don't need to worry about dropping the column at all.

df.merge(emails.rename(columns={mapped_name:'Customer'}), how='inner', on='Customer')
Out[53]: 
   Customer               Address                Email
0  J. Smith  10 Sunny Rd Timbuktu  [email protected]

answered Jun 23, 2017 at 5:27

Allen Qin

20k9 gold badges55 silver badges68 bronze badges

Collectives™ on Stack Overflow

Pandas merge two dataframes without duplicating column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related