Stack/Unstack Multi-index pivot table in Python Pandas


I have the follow Python Pandas Table:

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/lggqy.jpg" data-original="https://i.stack.imgur.com/lggqy.jpg" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

I'm trying to get it to look like this:

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/vVA0R.jpg" data-original="https://i.stack.imgur.com/vVA0R.jpg" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

How do I stack/unstack the "Peat-Forming" to have "PBL_AWI" and "Description" underneath?

Like this:

-Peat-Forming -PBL_AWI Values... -Description Values...


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


  • writing colored output into CSV file in Python
  • Pandas to Excel (Merged Header Column)
  • Excel cannot open the file created by XLSXWriter
  • Python Pandas: Convert “.value_counts” output to dataframe
  • TypeError: (“sort_values() got multiple values for argument 'axis'”, 'occurred at ind
  • Pandas Modify Dataframe
  • how to merge two dataframes and sum the values of columns
  • python XlsxWriter text wrapping and links styling
  • Writing a multi-index dataframe to Excel file
  • Pandas dataframe transpose with original row and column values
  • how to convert a data frame with a list in the value to a big data frame with the each level as a si
  • How to rearrange table in pandas in a format suitable for analysis in R?
  • Converting long table to wide and creating columns according to the rows
  • Pandas Dataframe find intervals and count occurances
  • merging multiple columns into one columns in pandas
  • How to mock local variables in java? [duplicate]
  • How do you get the value from a TVML textField?
  • How can I read in multiple excel files in SPSS using a macro?
  • Formula in Excel that references another Excel file based on cell reference
  • Excel Range in C# using interop.Excel. Range both empty and not empty?
  • How to send image as base64 string in JSON using HTTP POST in Android?
  • networkstream “cannot access a disposed object” when using newly created networkstream
  • Complicated COUNT query in MySQL
  • Purescript types for buildQueryString function
  • How read between delimiters in php DOM of a XML file?
  • Floating-point number in Excel
  • How to best manage SMTP clients
  • Pandas groupby to to_csv
  • Swift iOS don't recognize GCM library imports?
  • NHibernate manually control fetching
  • How to make JSON.NET deserialize to Microsoft Date Time?
  • Declaring variable dynamically in VB.net
  • Yii2: Config params vs. const/define
  • Ajax Loaded meta Tags
  • Exchange data b/w iOS devices using Bluetooth 4.0
  • Deserializing XML into class C#
  • Function pointer “assignment from incompatible pointer type” only when using vararg ellipsis
  • KeystoneJS: Relationships in Admin UI not updating
  • python draw pie shapes with colour filled
  • How to Embed XSL into XML