python pandas merging two dataframe and dealing with duplicates?

Question

When I merge two data frame based on 'Name' column in both daraframe, I get duplicated rows, and I want to keep the first one. How can I deal with it?

df1

     Name   Address
0    Amy    Nan
1    Breen  Nan
2    Carly  Nan

df2

     ID    Name   Address
0   1102   Amy    899 South
1   1105   Amy    700 Tower
2   1342   Breen  930 Tower
3   4674   Breen  555 Hall
4   5644   Breen  345 Young
5   2340   Carly  8988 House

after merging:

df1

    Name   Address
0   Amy    899 South
1   Amy    700 Tower
2   Breen  930 Tower
3   Breen  555 Hall
4   Breen  345 Young
5   Carly  8988 House

I want to make like this:

df1

    Name   Address
0   Amy    899 South
1   Breen  930 Tower
2   Carly  8988 House

Rayhane Mama · Accepted Answer · 2017-06-29 23:34:46Z

2

This can be done easily once you already merged your dataframes.

I Personally merged your two dataframes like follows:

df1 = pd.merge(df1,df2,how='right')  # 'right' because we don't want Nan values do we ?

then all you want to do is to drop duplicates as follows:

df1.drop_duplicates(subset=['Name'],keep='first',inplace=True)
print(df1)

output:

      Address   Name
0   899 South    Amy
2   930 Tower  Breen
5  8988 House  Carly

I hope this was helpful. Happy Coding.

answered Jun 29, 2017 at 23:34

Rayhane Mama

2,42413 silver badges20 bronze badges

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

Comments

Vaishali · Accepted Answer · 2017-06-29 23:44:25Z

2

You can do this with map and without merge as you don't need the other columns from df2. Just need to handle the duplicates in df2 first.

df2_unique = df2.drop_duplicates('Name',keep='first')
df1['Address'] = df1['Name'].map(df2_unique.set_index('Name')['Address'])

You get

    Name    Address
0   Amy     899 South
1   Breen   930 Tower
2   Carly   8988 House

answered Jun 29, 2017 at 23:44

Vaishali

38.5k5 gold badges62 silver badges88 bronze badges

Comments

Sagar Waghmode · Accepted Answer · 2017-06-29 22:30:59Z

0

I am not sure if you need to do merge here:

df2[['Name', 'Address']].drop_duplicates(subset=['Address'])

should take care of it.

answered Jun 29, 2017 at 22:30

Sagar Waghmode

7775 silver badges16 bronze badges

Collectives™ on Stack Overflow

python pandas merging two dataframe and dealing with duplicates?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related