Pandas Pivot tables row subtotals

Question

I'm using Pandas 0.10.1

Considering this Dataframe:

Date       State   City    SalesToday  SalesMTD  SalesYTD 20130320     stA    ctA            20       400      1000 20130320     stA    ctB            30       500      1100 20130320     stB    ctC            10       500       900 20130320     stB    ctD            40       200      1300 20130320     stC    ctF            30       300       800 

How can i group subtotals per state?

State   City  SalesToday  SalesMTD  SalesYTD   stA    ALL          50       900      2100   stA    ctA          20       400      1000   stA    ctB          30       500      1100 

I tried with a pivot table but i only can have subtotals in columns

table = pivot_table(df, values=['SalesToday', 'SalesMTD','SalesYTD'],\                      rows=['State','City'], aggfunc=np.sum, margins=True) 

I can achieve this on excel, with a pivot table.

Answer 1

If you put State and City not both in the rows, you'll get separate margins. Reshape and you get the table you're after:

In [10]: table = pivot_table(df, values=['SalesToday', 'SalesMTD','SalesYTD'],\                      rows=['State'], cols=['City'], aggfunc=np.sum, margins=True)   In [11]: table.stack('City') Out[11]:              SalesMTD  SalesToday  SalesYTD State City                                 stA   All        900          50      2100       ctA        400          20      1000       ctB        500          30      1100 stB   All        700          50      2200       ctC        500          10       900       ctD        200          40      1300 stC   All        300          30       800       ctF        300          30       800 All   All       1900         130      5100       ctA        400          20      1000       ctB        500          30      1100       ctC        500          10       900       ctD        200          40      1300       ctF        300          30       800 

I admit this isn't totally obvious.