Stack/Unstack Multi-index Pivot Table In Python Pandas

I have the follow Python Pandas Table: I'm trying to get it to look like this: How do I stack/unstack the 'Peat-Forming' to have 'PBL_AWI' and 'Description' underneath? Like this

Solution 1:

Unstacking would create another level in the columns level it would make it wide. Looking into xlsxwriter is something I recommend but you could probably try using this. (Kind of hacky though)

writer = ExcelWriter('output.xlsx')
non_peatlands = df.loc['Non-Peatlands']
peat = df.loc['Peatlands']

peat.reset_index().to_excel(writer,'1',startrow=no_peatlands.shape[0])#not sure if you need to add +1 or -1 to start or not so header is overwritten play around with it
no_peatlands.reset_index().to_excel(writer,'1') #this comes second to overwrite the columns of peat frame
writer.save()

For the totals you. calculate and append to dataframes peat and non_peatlands. You might have to play around with the MultiIndex to get it to merge. eg peat.index = pd.MultiIndex('set the correct index probably should use from tuples') tuple of the Total peatlands looks like this ("Total Peatlands","") to get the cells to merge properly.

As you can see my answer is pretty hacky (but possible) with just the pandas implementation. I would recommend using xlsxwriter like @user765015 said