Python Dataframe - Removing Duplicate Values?

Question

I have a program that runs completely through but unfortunately is returning duplicates because of the way the base data is structured. Results looks like this:

   Date      Amount   Source   Type
  7/16/2019  10        A       B
  7/17/2019  10        A       B
  7/15/2019  10        A       B
  7/15/2019  10        B       B

I'd like to return:
   Date      Amount   Source   Type
  7/17/2019   10        A       B
  7/15/2019   10        B       B

7/17/2019 is chosen because its the last date we received 10 from source A and Type B.

I've tried:

df.drop_duplicates(subset='a','b','date', keep="last")

but its not quite working. Is there a better way to do this?

This worked

df[df.Date.eq(df.groupby(['Source','Type'])['Date'].transform('max'))]

df[df.Date.eq(df.groupby(['Source','Type'])['Date'].transform('max'))] — anky
– anky, Commented Jul 16, 2019 at 14:29
Both throw this error which I haven't dealt with all that much: valueError: cannot reindex from a duplicate axis — user2679225
– user2679225, Commented Jul 16, 2019 at 14:33
@user2679225 df[df.Date.eq(df.groupby(['Source','Type'])['Date'].transform('max').values)] — BENY
– BENY, Commented Jul 16, 2019 at 14:34

Daniel Fonnegra García · Accepted Answer · 2019-07-16 14:38:59Z

1

As stated in This post:

non_duplicate_index = ~df.index.duplicated(keep='first')
result = df.loc[non_duplicate_index]

df.index.duplicated(keep='first') Returns an index containing True/False values. True if the value is duplicated, and False otherwise. Then, ~df.index.duplicated(keep='first') returns True where the value is not duplicated.

Finally df.loc[non_duplicate_index] is an slicing pandas method which returns the df rows where non_duplicate_index is True.

answered Jul 16, 2019 at 14:38

Daniel Fonnegra García

4022 silver badges11 bronze badges

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

Comments

BENY · Accepted Answer · 2019-07-16 14:39:00Z

1

drop_duplicates will work as well

df.sort_values('Date').drop_duplicates(subset=['Source','Type'], keep="last") 
Out[566]: 
        Date  Amount Source Type
3 2019-07-15      10      B    B
1 2019-07-17      10      A    B

answered Jul 16, 2019 at 14:39

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Python Dataframe - Removing Duplicate Values?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related