13

I've got a multiindex dataframe which I have to save as an excel file. When I use pandas method "to_excel" to do so, I get a nice table which incorporates merged cells. Here is an example of how such a table looks like:

example

Unfortunately, filtering the first column of this table is very problematic in excel since excel does not understand that the merged cells belong together: https://www.extendoffice.com/documents/excel/1955-excel-filter-merged-cells.html

That's why I need the 'to_excel' method to save the dataframe like that:

example2

Is that possible?

By the way, that's the code which I used to produce the first table:

df = pd.DataFrame({"animal": ("horse", "horse", "dog", "dog"), "color of fur": ("black", "white", "grey", "black"), "name": ("Blacky", "Wendy", "Rufus", "Catchy")})

mydf = df.set_index(["animal", "color of fur"])

mydf.to_excel("some_path_here")
John
  • 521
  • 2
  • 5
  • 12
  • Do you need Multiindex? If simply create columns from index - `mydf = mydf.reset_index()` then all working nice... – jezrael Oct 06 '17 at 07:29
  • In principle you are right but the muliindex has the advantage that the columns are bold. But shure, I can live with that. Thank you! – John Oct 06 '17 at 07:33

2 Answers2

21

Use merge_cells=False parameter:

mydf.to_excel("some_path_here", merge_cells=False)

From docs:

merge_cells : boolean, default True

Write MultiIndex and Hierarchical Rows as merged cells.

MaxU - stand with Ukraine
  • 205,989
  • 36
  • 386
  • 419
1

Another possibility would be to insert the dataframe to excel with xlwings (requries excel installation), with the drawback that index and columns are not formatted, so MaxU's answer is superior.

import xlwings as xw
import pandas as pd

df = pd.DataFrame({"animal": ("horse", "horse", "dog", "dog"), "color of fur": ("black", "white", "grey", "black"), "name": ("Blacky", "Wendy", "Rufus", "Catchy")}).set_index(["animal", "color of fur"])

with xw.App(visible=False) as app:
    wb = xw.Book()
    wb.sheets[0].range("A1").value = df
    wb.save(r"test.xlsx")
    wb.close()
mouwsy
  • 1,457
  • 12
  • 20