Alternative To Groupby For Generating A Summary Table From Tidy Pandas DataFrame

I want to generate a summary table from a tidy pandas DataFrame. I now use groupby and two for loops, which does not seem efficient. Seems stacking and unstacking would get me ther

Solution 1:

A simple solution from crosstab

pd.crosstab(index=df.Cat,columns=df.Stage,values=df.Score,aggfunc='sum', margins = True, margins_name = 'Total').iloc[:-1,:]
Out[342]: 
Stage      EOL      FUEL        OP     Total
Cat                                         
CC         NaN  1.521572  1.211599  2.733171
HT         NaN  0.632220  1.294327  1.926547
PM     0.53231  0.868605       NaN  1.400915

Solution 2:

I was wondering if not a simpler solution than using pd.crosstab is to use pd.pivot:

pd.pivot_table(df_tidy, index=['Cat'], columns=["Stage"], margins=True, margins_name='Total', aggfunc=np.sum).iloc[:-1,:]