I'm trying to find the count of days that a user has been within a past due status. The data is arranged as follows:
| customer_id | subscription_event | subscription_event_timestamp |
|---|---|---|
| A | charged_successfully | 2020-09-18 13:21:10 |
| A | charged_successfully | 2020-09-18 13:21:10 |
| A | subscription_went_past_due | 2020-10-18 13:22:00 |
| A | subscription_past_due | 2020-11-18 13:22:00 |
| A | charged_successfully | 2020-11-30 14:18:34 |
| B | charged_successfully | 2021-12-01 13:01:53 |
| B | subscription_went_past_due | 2021-01-01 15:26:33 |
| B | charged_successfully | 2021-01-15 12:01:12 |
Currently, I have an expanding window that can count the events partitioned by each user, but I would rather accurately tell the time delta between each timestamp (i.e. total duration the customer was past due.)
Here is the code for the current expanding window:
churnsubset['days_in_status'] = churnsubset.groupby(
['customerid', 'subscription_event_timestamp']
)[['subscription_event_timestamp']].transform(lambda x: x.expanding().count())
churnsubset.sort_values(by=['subscription_event_timestamp'])
What I expect to be the output is:
| customer_id | subscription_event | subscription_event_timestamp | days_past_due |
|---|---|---|---|
| A | charged_successfully | 2020-09-18 13:21:10 | 0 |
| A | charged_successfully | 2020-09-18 13:21:10 | 0 |
| A | subscription_went_past_due | 2020-10-18 13:22:00 | 0 |
| A | subscription_past_due | 2020-11-18 13:22:00 | 31.0 |
| A | charged_successfully | 2020-11-19 14:18:34 | 0 |
| B | charged_successfully | 2021-12-01 13:01:53 | 13.0 |
| B | subscription_went_past_due | 2021-01-01 15:26:33 | 0 |
| B | subscription_past_due | 2021-01-15 11:02:00 | 14.0 |
| B | charged_successfully | 2021-01-15 12:01:12 | 0 |
Can someone point me in the right direction either in pandas or likely a loop of some kind?