Pandas Pivot tables row subtotals

by

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.

More Related Contents:

  1. Pivoting a Pandas Dataframe containing strings – ‘No numeric types to aggregate’ error
  2. How to aggregate unique count with pandas pivot_table
  3. Pivot table subtotals in Pandas
  4. Pivot Tables or Group By for Pandas?
  5. Pivot Tables of Counts in Pandas DataFrame
  6. Difference between pivot and pivot_table. Why is only pivot_table working?
  7. Pandas groupby with bin counts
  8. How to write this code without using pandas?
  9. When should I (not) want to use pandas apply() in my code?
  10. How can I plot separate Pandas DataFrames as subplots?
  11. In pandas, is inplace = True considered harmful, or not?
  12. How do I convert a pandas Series or index to a Numpy array? [duplicate]
  13. How to reversibly store and load a Pandas dataframe to/from disk
  14. Format y axis as percent
  15. How to access pandas groupby dataframe by key
  16. Replacing Pandas or Numpy Nan with a None to use with MysqlDB
  17. Cumsum reset at NaN
  18. Add column with number of days between dates in DataFrame pandas
  19. Matplotlib – fixing x axis scale and autoscale y axis
  20. Plotting pandas timedelta
  21. Set value of one Pandas column based on value in another column
  22. Ranking order per group in Pandas
  23. Convert list into a pandas dataframe
  24. Converting Pandas dataframe into Spark dataframe error
  25. Change main plot legend label text
  26. Remove NaN/NULL columns in a Pandas dataframe?
  27. Group DataFrame in 5-minute intervals
  28. How to update values in a specific row in a Python Pandas DataFrame?
  29. Pandas: Modify a particular level of Multiindex
  30. Get column by number in Pandas

Leave a Comment