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.5215721.2115992.733171
HT         NaN  0.6322201.2943271.926547
PM     0.532310.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,:]