How can you generate running resampled cumulative to date output for pandas timeseries data?

Question

I am trying to generate the cumulative sum (or other cumulative function) of data in a pandas timeseries that is resampled. This is different from generating simply the cumulative sum of the data at each resample point in that I also want to output the cumulative sum of the data up to an including each resample point (before it resets and starts again).

Here is an example to illustrate:

import numpy as np
import pandas as pd
import datetime as dt

index = pd.date_range(dt.datetime(2021,1,1),dt.datetime(2021,12,1),freq='MS')
data = np.repeat(1,12)
df = pd.DataFrame(data=data,columns=['value'],index=index)

df >>>
         value
2021-01-01  1
2021-02-01  1
2021-03-01  1
2021-04-01  1
2021-05-01  1
2021-06-01  1
2021-07-01  1
2021-08-01  1
2021-09-01  1
2021-10-01  1
2021-11-01  1
2021-12-01  1

The desired output is:

>>>
         cum_resampled_value
2021-01-01  1
2021-02-01  2
2021-03-01  3
2021-04-01  1
2021-05-01  2
2021-06-01  3
2021-07-01  1
2021-08-01  2
2021-09-01  3
2021-10-01  1
2021-11-01  2
2021-12-01  3

This represents the cumulative sum of the value up to and including each quarter.

This is instead of applying the following and only producing the data points at the end of each quarter:

df.resample('Q').agg(lambda x: np.sum(x))

>>>
         value
2021-03-31  3
2021-06-30  3
2021-09-30  3
2021-12-31  3

I have tried to deploy expanding and resample methods in tandem to achieve this. It does work on an initial subset of the dataframe:

df[:3].expanding(1).apply(lambda x: x.resample('Q').agg(lambda x: np.sum(x)))

>>>

         value
2021-01-01  1.0
2021-02-01  2.0
2021-03-01  3.0

However, if I try this approach on the original dataframe:

df.expanding(1).apply(lambda x: x.resample('Q').agg(lambda x: np.sum(x)))

It returns:

TypeError: cannot convert the series to <class 'float'>

It seems like this is the right kind of approach, but I am not entirely sure why the error is emerging other than where it seems to emerge. If anyone could tweak this approach to get it working or suggest another solution that does not involve explicitly generating multiple other columns and working with those, then that would be great. Thanks!

score 1 · Accepted Answer · answered Jul 28 '22 at 20:37

It looks like you rather need a groupby using the quarter period as grouper:

df['value2'] = df.groupby(df.index.to_period('Q'))['value'].cumsum()

Output:

            value  value2
2021-01-01      1       1
2021-02-01      1       2
2021-03-01      1       3
2021-04-01      1       1
2021-05-01      1       2
2021-06-01      1       3
2021-07-01      1       1
2021-08-01      1       2
2021-09-01      1       3
2021-10-01      1       1
2021-11-01      1       2
2021-12-01      1       3

How can you generate running resampled cumulative to date output for pandas timeseries data?

1 Answers1