Find duplicates in dataframe by compound criteria?

user_808946 0
Viewed 44

I have a dataframe which has data like:

col1    col2    col3
1       3       bob
2       1       alice
3       3       bob
4       3       rose

And what I want to do is keep duplicate rows of col2 and discard duplicates with greater than 1 instance of col3's value. Or put another way, duplicates of col2 but only where col3's values are different. So in the above example, what I would end up with is:

col1    col2    col3
1       3       bob
4       3       rose

Alice wouldn't be in the output because obviously there's no second value of col2's '1' - it isn't duplicate. The second entry of Bob (3 3 bob) wouldn't be in the output because while col2's '3' is a duplicate, col3's 'bob' is already in the result set (1 3 bob). (I am aware of the keep= parameter to change the behaviour of keeping first or last, but ignoring it for simplicity.)

Any thoughts? Thank you.

pandas duplicates python
2 Answers

Use a combination of .duplicated(), .drop_duplicates() and the loc accessor

df.loc[df[df['col2'].duplicated(False)].col3.drop_duplicates(keep='first').index,:]



 col1  col2  col3
0     1     3   bob
3     4     3  rose

How it works

#Filter all duplicated in col2 using duplicated(False)

df[df['col2'].duplicated(False)]



#Drop duplicates in col3 but retaining first using .drop_duplicates(keep='first')

df[df['col2'].duplicated(False)].col3.drop_duplicates(keep='first')



#Extract index

df[df['col2'].duplicated(False)].col3.drop_duplicates(keep='first').index



#Finally filter using loc accessor
     df.loc[index,all columns]
user_89869750

Try:

df.loc[df.drop_duplicates(['col2', 'col3'])
         .duplicated(['col2'], keep=False).loc[lambda x: x].index]

Output:

   col1  col2  col3
0     1     3   bob
3     4     3  rose

Details:

Inside df.loc find indexes using

  • first drop_duplicates to get rid of duplicate records of col2 and col3

  • use duplicated with keep = False return True for all records with duplicate 'col2'

  • lastly, use loc with lambda to boolean select only those True indexes

user_63615310
Related