Removing duplicates python dataframe

Question

I have a dataframe with some duplicates that I need to remove. In the dataframe below, where the month, year and type are all the same it should keep the row with the highest sale. Eg:

df = pd.DataFrame({'month': [1, 1, 7, 10],
                   'year': [2012, 2012, 2013, 2014],
                  'type':['C','C','S','C'],
                  'sale': [55, 40, 84, 31]})

After removing duplicates and keeping the highest value of column 'sale' should look like:

df_2 = pd.DataFrame({'month': [1, 7, 10],
                   'year': [2012, 2013, 2014],
                  'type':['C','S','C'],
                  'sale': [55, 84, 31]})

df.drop_duplicates(subset= ['month', 'year', 'type'], keep= 'first') — Ade_1
– Ade_1, Commented Mar 2, 2021 at 16:38

anky · Accepted Answer · 2021-03-02 16:38:02Z

1

You can use:

(df.sort_values('sale',ascending=False)
   .drop_duplicates(['month','year','type']).sort_index())

   month  year type  sale
0      1  2012    C    55
2      7  2013    S    84
3     10  2014    C    31

answered Mar 2, 2021 at 16:38

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

Comments

igorkf · Accepted Answer · 2021-03-02 16:38:33Z

1

You could groupby and take the max of sale:

df.groupby(['month', 'year', 'type']).max().reset_index()
    month   year    type    sale
0      1    2012      C      55
1      7    2013      S      84
2      10   2014      C      31

If you have another column, like other, than you must specify which column to take the max, in this way:

df.groupby(['month', 'year', 'type'])[['sale']].max().reset_index()
    month   year    type    sale
0      1    2012      C      55
1      7    2013      S      84
2      10   2014      C      31

answered Mar 2, 2021 at 16:38

igorkf

3,6152 gold badges26 silver badges37 bronze badges

Collectives™ on Stack Overflow

Removing duplicates python dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related