python - Pandas Pivot_Table 定义函数 aggfunc

Question

我正在尝试将自定义聚合函数应用于数据透视表，但不断收到 KeyError:“PayoffUPB”。这是 aggfunc 的语法问题吗，还是我需要在这里使用 lambda 函数？谢谢您的帮助。

import pandas as pd

df = pd.DataFrame([201801,201801,201801,201802,201802,201802,201803,201803,201803], columns=['Month'])
df['Program'] = ['a','b','c','a','b','c','a','b','c']
df['UPB'] = [1000000,1200000,1500000,1300000,1400000,1400000,1000000,1600000,1250000]
df['PayoffUPB'] = [50000,60000,30000,35000,40000,50000,65000,45000,25000]

print(df)

def CPR(x):
    result = 100*(1-(1-x['PayoffUPB'].sum()/x['UPB'].sum())**12)
    return result

df.pivot_table(index='Month',columns='Program',aggfunc=CPR)

Answer 1

我们可以将groupby与unstack结合使用

df.groupby(['Month','Program']).apply(CPR).unstack()
Out[310]: 
Program          a          b          c
Month                                   
201801   45.963991  45.963991  21.528328
201802   27.928082  29.379551  35.364845
201803   55.358443  28.989114  21.528328