CODEWITHSUNDEEP
pythonpandaspivot-table
Ed J
Ed J

Reputation: 23

Pandas Pivot_Table defined function aggfunc

I am trying to apply a custom aggregation function to a pivot table, but keep receiving KeyError: 'PayoffUPB'. Is this a syntax problem with aggfunc, or do I need to use a lambda function here? Thank you for the help.

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)

Upvotes: 2

Views: 8984

Answers (1)

BENY
BENY

Reputation: 323326

We can using groupby with 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

Upvotes: 2

Related Questions