Python Pandas sub-totals and grand total

Question

I have the following table:

How do I either break out the table so that it only has the headers:

Peat-Forming, PBL_AWI, CM, LBH

and the values remain the same as shown? Doing this I believe would make it possible to add sub-totals and grand totals to the data.

Alternatively how do I add Sub-totals to 'Peat-Forming' with a 'Grand Total' at the end?

I tired doing this yet can't get to work because of multi-indexs

Answer 1

How do I either break out the table so that it only has the headers:

Peat-Forming, PBL_AWI, CM, LBH?

df['Shape_Area'].reset_index()

For the rest of your question, please post a copy-pasteable example that generates your dataframe and a mock up of the desired output.

Answer 2

You can add subtotals to the pivot tables directly but it requires a bit of munging. You have to make sure that one of fields to be subtotaled starts in the rows, and the other starts in the columns. You then stack the resultant pivot table. I'm assuming that the table you have pasted was created in pandas already and the name of the column that has the peat data was PeatStatus. If so then you can do the following.

pvt = pd.pivot_table(df, values=['CM', 'LBH'], columns=['NSRCODE'],
                     index=['PeatStatus'], 
                     aggfunc=np.sum, margins=True)

pvt.stack('NSRCODE')

Similar answer is outlined here Pandas Pivot tables row subtotals