Menu
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.
210
We can easily insert a total / sum row to our Python pivot table by using the margins and margin_names parameters. The margins parameters insets the summary row and columns. THe margin_names parameters, allows us to rename the pivot table summary columns.
Show Subtotals at Top or BottomSelect a cell in the pivot table, and on the Ribbon, click the Design tab. In the Layout group, click Subtotals, and then click Show All Subtotals at Bottom of Group.
When working with a PivotTable, you can display or hide subtotals for individual column and row fields, display or hide column and row grand totals for the entire report, and calculate the subtotals and grand totals with or without filtered items.
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.
85
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
