Drop duplicates on one column, breaking ties from another column

user_2797757 0
Viewed 152

I have the below dataframe:

x = pd.DataFrame({
    "item" : ["a", "a", "a", "b", "c", "c"],
    "vote" : [1, 0, 1, 1, 0, 0],
    "timestamp" : ["2020-06-07 11:04:26", "2020-06-07 11:03:37", "2020-06-07 11:09:18", "2020-06-07 11:04:40", "2020-06-07 11:09:11", "2020-06-07 11:09:23"]
})

item   vote   timestamp
a      1      2020-06-07 11:04:26
a      0      2020-06-07 11:03:37
a      1      2020-06-07 11:09:18
b      1      2020-06-07 11:04:40      
c      0      2020-06-07 11:09:11
c      0      2020-06-07 11:09:23

How do I drop_duplicates on item column, and use the timestamp column as a tiebreaker: keep the latest one? The final dataframe should look like this:

item   vote   timestamp
a      1      2020-06-07 11:09:18
b      1      2020-06-07 11:04:40      
c      0      2020-06-07 11:09:23
pandas python-3.x python
2 Answers

You can call sort_values on "item" and "timestamp" before dropping duplicates:

x.sort_values(['item', 'timestamp']).drop_duplicates('item', keep='last')

  item  vote            timestamp
2    a     1  2020-06-07 11:09:18
3    b     1  2020-06-07 11:04:40
5    c     0  2020-06-07 11:09:23

Specifying keep='last' means all but the last row are discarded, which works out because we sorted on timestamp in the previous step.

(x.sort_values(['item', 'timestamp'])
  .drop_duplicates('item', keep='last')
  .reset_index(drop=True))

  item  vote            timestamp
0    a     1  2020-06-07 11:09:18
1    b     1  2020-06-07 11:04:40
2    c     0  2020-06-07 11:09:23
user_49090870

Another way;

  x['timestamp']=pd.to_datetime(x['timestamp'])#Coerce timestamp to datetime
  x.set_index('timestamp', inplace=True)#set timestamp as index
  x2=x.groupby([x.index.date,x['item']])['vote'].agg(vote='last').reset_index()
  x2.columns=['timestamp','item','vote']

enter image description here

user_89869750
Related