Duplicates in Pandas Dataframe after Self Merge

Question

import pandas as pd

I have a Dataframe Table

d1 = pd.DataFrame({'ID_A':[1, 2, 3], 'name':['Micha', 'Micha', 'Lea']})

-----------------
- ID_A   - name  
-----------------
- 1      - Micha 
- 2      - Micha 
- 3      - Lea   
-----------------

I wanna do a self join to get the following

---------------------------
- ID_A_x - name  - ID_A_y -
---------------------------
- 1      - Micha - 1      -
- 1      - Micha - 2      -
- 3      - Lea   - 3      -
---------------------------

But with

pd.merge(d1, d1, left_on='name', right_on='name', how='left')

i get duplicate pairs that are the same for me, how to avoid them? This result is not what i want:

---------------------------
- ID_A_x - name  - ID_A_y -
---------------------------
- 1      - Micha - 1      -
- 1      - Micha - 2      -
- 2      - Micha - 2      -
- 2      - Micha - 1      -
- 3      - Lea   - 3      -
---------------------------

Please help.

Wald · Accepted Answer · 2018-07-19 11:45:45Z

2

I am not sure that I understood you correctly. But one possible solution (which I think is what you want) could be:

import pandas as pd
d1 = pd.DataFrame({'ID_A':[1, 2, 3], 'name':['Micha', 'Micha', 'Lea']})
pd.merge(d1.drop_duplicates(subset='name'), d1, on='name', how='left')

Output:

   ID_A_x   name  ID_A_y
0       1  Micha       1
1       1  Micha       2
2       3    Lea       3

answered Jul 19, 2018 at 11:45

Wald

3112 silver badges12 bronze badges

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

3 Comments

steff9488 Over a year ago

Works, Thank you.

ColoradoGranite Over a year ago

I seem to be having this same issue. Dropping duplicates might work, but it concerns me that it produces them in the first place. Is there a better solution?

Wald Over a year ago

@ColoradoGranite The reason for the duplicates is that we have non-unique keys. We merge on 'name' and Micha is two times in that column. The only way to avoid duplicates would be to have unique keys, I think. So to answer your question, I can't find anything better with the above data.

loegare · Accepted Answer · 2018-07-19 11:46:08Z

1

Imgur

pd.DataFrame({'ID_A_x':[1,1,2,2,3],
             'name':['Mi','Mi','Mi','Mi','Lea'],
             'ID_A_y':[1,2,2,1,3]}).drop_duplicates(['ID_A_y','name'])

answered Jul 19, 2018 at 11:46

loegare

1521 silver badge11 bronze badges

3 Comments

steff9488 Over a year ago

This is perfect for cleaning it up afterwards. Thank you.

Rean Over a year ago

hi @loegare, may i have lib name for showing "executed in ..ms, finished...."

loegare Over a year ago

@rean its a jupyter notebook extension, dont remember the exact name

Collectives™ on Stack Overflow

Duplicates in Pandas Dataframe after Self Merge

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related