python - pandas: restructuring DataFrames -

i have following pandas dataframe , baseline value:

df = pd.dataframe(data=[     {'yr': 2010, 'month': 0, 'name': 'johnny', 'total': 50},,     {'yr': 2010, 'month': 0, 'name': 'johnny', 'total': 50},     {'yr': 2010, 'month': 1, 'name': 'johnny', 'total': 105},     {'yr': 2010, 'month': 0, 'name': 'zack', 'total': 90} ]) baseline_value = 100 

i'm grouping , aggregating data based on year, month , name. i'm calculating net sum relative baseline value:

pt = pd.pivot_table(data=df, index=['yr', 'month', 'name'], values='total', aggfunc=np.sum) pt['net'] = pt['total'] - baseline_value print(pt)                     total    net yr    month  name 2010  0      johnny   100      0              zack      90    -10       1      johnny   105      5 

how can restructure dataframe output looks this:

                             value yr    month   name    type 2010  0       johnny  total    100                       net        0               zack    total     90                       net      -10       1       johnny  total    105                       net        5 

option 1: reshaping yout pivot dataframe: pt

use stack, rename, , to_frame:

pt.stack().rename('value').to_frame() 

output:

                         value yr   month name                2010 0     johnny total    100                   net        0            zack   total     90                   net      -10      1     johnny total    105                   net        5 

option 2 using set_index , sum original df

here approach starting source df, using set_index , sum level parameter, reshape stack:

baseline_value = 100 (df.set_index(['yr','month','name'])   .sum(level=[0,1,2])   .eval('net = @baseline_value - total',inplace=false)   .stack()   .to_frame(name='value')) 

output:

                         value yr   month name                2010 0     johnny total    100                   net        0            zack   total     90                   net       10      1     johnny total    105                   net       -5