CODEWITHSUNDEEP
pandas
L1meta
L1meta

Reputation: 373

Pandas: Days since last event per id

I want to build a column for my dataframe df['days_since_last'] that shows the days since the last match for each player_id for each event_id and nan if the row is the first match for the player in the dataset.

Example of my data:

   event_id  player_id  match_date
0   1470993     227485  2015-11-29
1   1492031     227485  2016-07-23
2   1489240     227485  2016-06-19
3   1495581     227485  2016-09-02
4   1490222     227485  2016-07-03
5   1469624     227485  2015-11-14
6   1493822     227485  2016-08-13
7   1428946     313444  2014-08-10
8   1483245     313444  2016-05-21
9   1472260     313444  2015-12-13

I tried the code in Find days since last event pandas dataframe but got nonsensical results.

Upvotes: 1

Views: 1167

Answers (2)

jezrael
jezrael

Reputation: 862921

It seems you need sort first:

df['days_since_last_event'] = (df.sort_values(['player_id','match_date'])
                                 .groupby('player_id')['match_date'].diff()
                                 .dt.days)
print (df)
   event_id  player_id match_date  days_since_last_event
0   1470993     227485 2015-11-29                   15.0
1   1492031     227485 2016-07-23                   20.0
2   1489240     227485 2016-06-19                  203.0
3   1495581     227485 2016-09-02                   20.0
4   1490222     227485 2016-07-03                   14.0
5   1469624     227485 2015-11-14                    NaN
6   1493822     227485 2016-08-13                   21.0
7   1428946     313444 2014-08-10                    NaN
8   1483245     313444 2016-05-21                  160.0
9   1472260     313444 2015-12-13                  490.0

Upvotes: 3

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210872

Demo:

In [174]: df['days_since_last'] =  (df.groupby('player_id')['match_date']
                                      .transform(lambda x: (x.max()-x).dt.days))

In [175]: df
Out[175]:
   event_id  player_id match_date  days_since_last
0   1470993     227485 2015-11-29              278
1   1492031     227485 2016-07-23               41
2   1489240     227485 2016-06-19               75
3   1495581     227485 2016-09-02                0
4   1490222     227485 2016-07-03               61
5   1469624     227485 2015-11-14              293
6   1493822     227485 2016-08-13               20
7   1428946     313444 2014-08-10              650
8   1483245     313444 2016-05-21                0
9   1472260     313444 2015-12-13              160

Upvotes: 1

Related Questions