[Python] pandas pivot_table: can aggfunc work over a different grouping period from the table?

I have a pandas pivot table that shows payments made to different payees vs date, and I'm using a Grouper to group them into months, e.g.:

payee payee_1 payee_2
date
2019-11-30 amount amount
2019-12-31 amount amount
2020-01-31 amount amount
2020-02-29 amount amount


I can use the standard aggfunc="sum" to show the "amounts" as the sum for each month, but what I want is to show the sum for 12 months up to each month. Is a custom aggregation function a way to do that? If so, how do I get the values to supply to the function?

This is a snippet of raw the data frame:

date payee value
0 2020-10-13 payee_1 -43.74
1 2025-01-26 payee_1 -2.03
2 2022-04-08 payee_2 -9.54
3 2021-06-05 payee_1 -16.06
4 2021-12-08 payee_2 -20



And my pivot table definition:

pd.pivot_table(
df,
values="value",
index=pd.Grouper(
freq="ME",
key="date"
),
columns="payee",
aggfunc="sum",
)

Continue reading...