CODEWITHSUNDEEP
pythonpandas
Rob
Rob

Reputation: 637

How to find the date of last event in pandas dataframe

I have a Pandas DataFrame as follows:

                    time     event
0    2018-12-30 02:15:00      True
1    2018-12-30 05:33:00     False
2    2018-12-30 08:53:00     False
3    2018-12-30 12:06:00      True
4    2018-12-30 15:23:00      True 
5    2018-12-30 20:18:00     False
6    2018-12-30 22:01:00     False

And I need to calculate a column "time of last event". This needs to return the most recent datetime of a "True" event.

I.e., in the above example, the result should look like this:

                    time      event     time_of_last_event     
0    2018-12-30 02:15:00      True     2018-12-30 02:15:00
1    2018-12-30 05:33:00     False     2018-12-30 02:15:00
2    2018-12-30 08:53:00     False     2018-12-30 02:15:00
3    2018-12-30 12:06:00      True     2018-12-30 12:06:00
4    2018-12-30 15:23:00      True     2018-12-30 15:23:00 
5    2018-12-30 20:18:00     False     2018-12-30 15:23:00
6    2018-12-30 22:01:00     False     2018-12-30 15:23:00

How do I calculate this column?

Upvotes: 1

Views: 399

Answers (2)

BENY
BENY

Reputation: 323306

You can use transform with event cumsum 

df['time_of_last_event']=df.groupby(df.event.cumusm()).time.transform('first')

Upvotes: 3

Quang Hoang
Quang Hoang

Reputation: 150765

You can do where to mask all time with False as nan and ffill() to fill nan with the last valid time:

df['time_of_last_event'] = df['time'].where(df['event']).ffill()

Output:

                  time  event   time_of_last_event
0  2018-12-30 02:15:00   True  2018-12-30 02:15:00
1  2018-12-30 05:33:00  False  2018-12-30 02:15:00
2  2018-12-30 08:53:00  False  2018-12-30 02:15:00
3  2018-12-30 12:06:00   True  2018-12-30 12:06:00
4  2018-12-30 15:23:00   True  2018-12-30 15:23:00
5  2018-12-30 20:18:00  False  2018-12-30 15:23:00
6  2018-12-30 22:01:00  False  2018-12-30 15:23:00

Upvotes: 2

Related Questions