如何在Pandas中按公司和年份生成月度累计求和透视表？

问题背景 #

我有一份包含不同公司年度各品类保费的周期表，对应的DataFrame如下：

我想要生成一张表格，查看按Company和Year分组的累计保费，即每个月显示从年初到该月的累计保费。

常规求和透视表的实现 #

常规的月度保费求和透视表可以正常运行：

data.pivot_table(columns = 'Company', index = 'Month', values = 'Premium', aggfunc= np.sum)

运行结果如下：

尝试累计求和遇到的问题 #

但将aggfunc改为np.cumsum时，结果只返回一个Series，无法得到按月份分组的累计透视表。我需要的是按年份生成累计求和透视表，将每月值累加到之前的结果中，预期输出如下：

使用的原始数据 #

原始数据的字典形式如下：

{'Company': {0: 'AgeSA',1: 'Türkiye',2: 'Türkiye',3: 'AgeSA',4: 'AgeSA',5: 'Türkiye',6: 'AgeSA',7: 'Türkiye',8: 'Türkiye',9: 'AgeSA',10: 'Türkiye',11: 'Türkiye',12: 'AgeSA',13: 'Türkiye',14: 'Türkiye',15: 'AgeSA',16: 'AgeSA',17: 'Türkiye',18: 'AgeSA',19: 'Türkiye',20: 'Türkiye',21: 'AgeSA',22: 'Türkiye',23: 'Türkiye'}, 'Type': {0: 'Birikimli Hayat',1: 'Birikimli Hayat',2: 'Sadece Yaşam Teminatlı',3: 'Karma Sigorta',4: 'Yıllık Vefat',5: 'Yıllık Vefat',6: 'Uzun Süreli Vefat',7: 'Uzun Süreli Vefat',8: 'Birikimli Hayat',9: 'Yıllık Vefat',10: 'Yıllık Vefat',11: 'Uzun Süreli Vefat',12: 'Birikimli Hayat',13: 'Birikimli Hayat',14: 'Sadece Yaşam Teminatlı',15: 'Karma Sigorta',16: 'Yıllık Vefat',17: 'Yıllık Vefat',18: 'Uzun Süreli Vefat',19: 'Uzun Süreli Vefat',20: 'Birikimli Hayat',21: 'Yıllık Vefat',22: 'Yıllık Vefat',23: 'Uzun Süreli Vefat'}, 'Month': {0: 1,1: 1,2: 1,3: 1,4: 1,5: 1,6: 1,7: 1,8: 1,9: 1,10: 1,11: 1,12: 2,13: 2,14: 2,15: 2,16: 2,17: 2,18: 2,19: 2,20: 2,21: 2,22: 2,23: 2}, 'Year': {0: 2022,1: 2022,2: 2022,3: 2022,4: 2022,5: 2022,6: 2022,7: 2022,8: 2022,9: 2022,10: 2022,11: 2022,12: 2022,13: 2022,14: 2022,15: 2022,16: 2022,17: 2022,18: 2022,19: 2022,20: 2022,21: 2022,22: 2022,23: 2022}, 'Ferdi Grup': {0: 'Ferdi',1: 'Ferdi',2: 'Ferdi',3: 'Ferdi',4: 'Ferdi',5: 'Ferdi',6: 'Ferdi',7: 'Ferdi',8: 'Grup',9: 'Grup',10: 'Grup',11: 'Grup',12: 'Ferdi',13: 'Ferdi',14: 'Ferdi',15: 'Ferdi',16: 'Ferdi',17: 'Ferdi',18: 'Ferdi',19: 'Ferdi',20: 'Grup',21: 'Grup',22: 'Grup',23: 'Grup'}, 'Premium': {0: 936622.43,1: 14655.67,2: 8496.0,3: 124768619.29,4: 6651019.24,5: 11055383.530005993,6: 54273212.457471885,7: 22163192.66,8: 81000.95,9: 9338009.52,10: 251790130.54997802,11: 140949274.79999998,12: 910808.77,13: 8754.71,14: 7128.0,15: 129753498.31,16: 8015974.454128993,17: 16776490.000003006,18: 67607915.34000003,19: 24683694.700000003,20: 60887.56,21: 1497105.2458709963,22: 195019190.297756,23: 167424048.43}, 'cumsum': {0: 936622.43,1: 14655.67,2: 23151.67,3: 125705241.72000001,4: 132356260.96000001,5: 11078535.200005993,6: 186629473.4174719,7: 33241727.860005993,8: 33322728.810005993,9: 195967482.9374719,10: 285112859.35998404,11: 426062134.159984,12: 196878291.7074719,13: 426070888.869984,14: 426078016.869984,15: 326631790.0174719,16: 334647764.4716009,17: 442854506.869987,18: 402255679.8116009,19: 467538201.569987,20: 467599089.129987,21: 403752785.05747193,22: 662618279.427743,23: 830042327.857743}}

当前实现方法 #

我尝试了以下代码添加累计列：

df_2 = data.copy()
df_2['cumsum'] = df_2.groupby(['Company', 'Year'])[['Premium']].cumsum()
df_2.sort_values(['Company', 'Year', 'cumsum']).reset_index(drop = True)

但结果里每行都从上一行获取了累计值，不是按月份汇总后的累计结果。于是我又做了一次透视表，对每组取最大值来得到月度累计值：

df_2.pivot_table(index = ['Year', 'Month'], values = ['Premium', 'cumsum'],
columns = 'Company',
aggfunc = {'Premium': 'sum', 'cumsum': 'max'})

最终得到了目标结果，但总觉得这个方法太繁琐。

疑问 #

在Pandas中生成累计求和表真的这么复杂吗？还是我用了复杂的方法？

内容的提问来源于stack exchange，提问作者Mehmet Soydam