Avoid merged cells in pandas to_excel method

user_6556373 0
Viewed 9963

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")
pandas python
2 Answers

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.

user_57412050

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()
user_139683920
Related