Calculation based off of previous row (Pandas)

user_11331385 0
Viewed 129

I'm trying to create an event counter and days since first event counter for some log data. The DataFrame below tracks whether or not an event happened for a group each day. For each group, i need to calculate the number of events that have happened prior and on that date for any date. I also need to calculate the number of days since the first event happened in each group

Starting DF

group        date  event
    A  2020-07-16       0
    A  2020-07-17       1
    A  2020-07-18       0
    A  2020-07-19       1
    A  2020-07-20       0
    A  2020-07-21       0
    A  2020-07-22       1
    B  2020-07-16       1
    B  2020-07-17       1
    B  2020-07-18       0
    B  2020-07-19       1
    B  2020-07-20       0
    B  2020-07-21       1
    B  2020-07-22       1

Code to generate DF

import pandas as pd
import datetime as datetime
base = datetime.datetime.today()
numdays = 7
date_list = [(base - datetime.timedelta(days=x)).date() for x in range(numdays)]

df = pd.DataFrame(columns=['group', 'date'])
for group in ['A', 'B']:
    tmp = pd.DataFrame({'group': group, 'date': date_list})
    df = df.append(tmp)
df = df.sort_values(['group', 'date'])

groupA_events = [0, 1, 0, 1, 0, 0, 1]
groupB_events = [1, 1, 0, 1, 0, 1, 1]
events = groupA_events + groupB_events
df['event'] = events

Ending DF

group        date  event  counter  since_first
    A  2020-07-16      0         0            0
    A  2020-07-17      1         1            0
    A  2020-07-18      0         1            1
    A  2020-07-19      1         2            2
    A  2020-07-20      0         2            3
    A  2020-07-21      0         2            4
    A  2020-07-22      1         3            5
    B  2020-07-16      1         1            0
    B  2020-07-17      1         2            1
    B  2020-07-18      0         2            2
    B  2020-07-19      1         3            3
    B  2020-07-20      0         3            4
    B  2020-07-21      1         4            5
    B  2020-07-22      1         5            6

My data is roughly 800k rows (and growing). I found a solution that works (kind of) but has an extremely long execution time.

pandas logging time python
2 Answers

You can do groupby with cumsum + cumcount

df['counter']=df.groupby('group').event.cumsum()
df['since_first']=df[df['counter'].ne(0)].groupby('group')['counter'].cumcount()
df['since_first'].fillna(0, inplace=True)
user_79645270

Use a cumsum to get the counter. The days since can be obtained by masking then transforming to the first day where there is an event within each group. This is useful as if your dates aren't continuous it will still properly calculate the time difference. (clip so anything before is considered 0)

df['counter'] = df.groupby('group').agg(counter=('event', 'cumsum'))

df['date'] = pd.to_datetime(df['date'])
s_first = df['date'].where(df['event'].eq(1)).groupby(df['group']).transform('first')

df['days_since'] = (df['date'] - s_first).dt.days.clip(lower=0)

  group       date  event  counter  days_since
6     A 2020-07-16      0        0           0
5     A 2020-07-17      1        1           0
4     A 2020-07-18      0        1           1
3     A 2020-07-19      1        2           2
2     A 2020-07-20      0        2           3
1     A 2020-07-21      0        2           4
0     A 2020-07-22      1        3           5
6     B 2020-07-16      1        1           0
5     B 2020-07-17      1        2           1
4     B 2020-07-18      0        2           2
3     B 2020-07-19      1        3           3
2     B 2020-07-20      0        3           4
1     B 2020-07-21      1        4           5
0     B 2020-07-22      1        5           6
user_43333590
Related