pandas - pivot || create custom column for ratio/percentage-CodePudding

import numpy as np
import pandas as pd

data = {'experiment_name': ['exp1', 'exp1', 'exp1', 'exp1', 'exp1', 'exp1'], 
'variant': ['A', 'B', 'A','B','A','B'],'sessions_with_orders':[1,2,6,0,23,12],
'total_sessions':[10,23,56,22,89,12]}  
  
# Create DataFrame  
df = pd.DataFrame(data)    
df.pivot_table(index='variant',columns='experiment_name',values=['total_sessions','sessions_with_orders'],aggfunc=np.sum)

I have some data frame where I pivot it using aggregate functions.

The output I get is desired. However, I would like to create ratio sessions_with_orders/total_sessions. How do I do this? That is doable on excel but I am unable to think over pandas-data frame.

I do not understand lambda, cross_tab or how to implement them.
I am on python 3.9.8. np-version 1.22.3 and pd-version 1.3.4

CodePudding user response：

IIUC, you can use assign:

(df
.pivot_table(index='variant',columns='experiment_name',values=['total_sessions','sessions_with_orders'],aggfunc=np.sum)
.assign(ratio=lambda d: d['sessions_with_orders']/d['total_sessions'])
)

output:

                sessions_with_orders total_sessions     ratio
experiment_name                 exp1           exp1          
variant                                                      
A                30                   155            0.193548
B                14                   57             0.245614

If you have several experiments, however, better use join (I changed here the last experiment to "exp2" for the demo):

df2 = df.pivot_table(index='variant',columns='experiment_name',
                     values=['total_sessions','sessions_with_orders'],
                     aggfunc=np.sum)

df2.join(pd.concat({'ratio': df2['sessions_with_orders'].div(df2['total_sessions'])},
          axis=1))

output:

                sessions_with_orders       total_sessions           ratio     
experiment_name                 exp1  exp2           exp1  exp2      exp1 exp2
variant                                                                       
A                               30.0   NaN          155.0   NaN  0.193548  NaN
B                                2.0  12.0           45.0  12.0  0.044444  1.0