Python: Remove duplicates from DataFrame based on another column value

Question

I have the following table with fictive data:

I want to remove any duplicate rows and keep only the row which contains a positive value in "Won Turnover". Hence, the two rows marked with red should be removed in this case

Moreover, if there are duplicate rows with only Lost Turnover, then the row with the highest turnover should be kept (The bottom two rows).

zipa · Accepted Answer · 2019-10-07 08:19:47Z

3

Maybe this can do it:

df.sort_values(['Won Turnover', 'Lost Turnover'], ascending=False).drop_duplicates('Supplier')

edited Oct 7, 2019 at 8:19

answered Oct 7, 2019 at 7:55

zipa

28k6 gold badges45 silver badges62 bronze badges

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

3 Comments

Franco Piccolo Over a year ago

This doesn't account for the fact that there may not be any Won Turnover and yes multiple Lost Turnover.

zipa Over a year ago

@FrancoPiccolo I think this edit should cover all the cases.

mober Over a year ago

Thank you for your answer. This solution gave me the right answer. However, I had to replace 'Supplier' with 'Matching Key' in the drop_duplicates statement

jezrael · Accepted Answer · 2019-10-07 08:43:39Z

First test Won Turnover if only missing value per groups with GroupBy.all and test only max value per Lost Turnover. Chain by & for bitwise AND and add new condition for return all not missing rows per Won Turnover with | for bitwise OR:

m1 = (df.assign(new = df['Won Turnover'].isna())
        .groupby(['Date','Supplier','Customer'])['new'].transform('all'))
m2 = (df.groupby(['Date','Supplier','Customer'])['Lost Turnover'].transform('max')
        .eq(df['Lost Turnover']))
df = df[(m1 & m2) | df['Won Turnover'].notna()]
print (df)

         Date Supplier   Customer  Won Turnover  Lost Turnover
1  25.06.2019     Nike      Pepsi       25000.0            NaN
2  25.06.2019     Nike  McDonalds       10000.0            NaN
3  25.06.2019   Adidas  Coca Cola       12000.0            NaN
5  25.06.2019   Adidas  McDonalds       35000.0            NaN
6  25.06.2019   Adidas      Pepsi           NaN        15000.0

Collectives™ on Stack Overflow

Python: Remove duplicates from DataFrame based on another column value

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related