[FIXED] How to add calculated fields in pandas pivot table

Issue

Suppose I have a pivot like this:

import pandas as pd

d = {'Col_A': [1,2,3,3,3,4,9,9,10,11], 
     'Col_B': ['A','K','E','E','H','A','J','A','L','A'],
     'Value1':[648,654,234,873,248,45,67,94,180,120],
     'Value2':[180,120,35,654,789,34,567,21,235,83],
     'Value3':[567,21,235,83,248,45,67,94,180,120]
    }

df = pd.DataFrame(data=d)
df_pvt = pd.pivot_table(df,values=['Value1'], index='Col_A', columns='Col_B', aggfunc=np.sum).fillna(0)
df_pvt

I want to add a calculated field at the right side of the pivot using "Value2/Value3". This calculated field should also display with Col_B categories. One way to do it is to add Value2, and Value3 in the pivot and do the division afterwards. Then, I could just drop those Value 2 and Value 3 sections in the pivot. However, I'm wondering if there's any easier way to achieve this. I've tried the following, but didn't work:

pd.pivot_table(df,values=['Value1','Value2'/'Value3'], index='Col_A', columns=['Col_B','val2/val3'], aggfunc=np.sum).fillna(0)

Solution

Apply these transformations before the pivot:

df = df.groupby(['Col_A', 'Col_B']).sum()
df = df.eval('V23 = Value2 / Value3')[['Value1', 'V23']]

Then apply the pivot and clean-up:

df.reset_index().pivot(index='Col_A', columns='Col_B').fillna(0)

UPDATE: In fact, you can replace the last line with just:

df.unstack(fill_value=0)

Answered By - rudolfovic