Menu
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:
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:
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")
712
How can I either get Pandas to understand merged cells, or quickly and easily remove the NaN and group by the appropriate value? (One approach would be to reset the index, step through to find the values and replace NaNs with values, pass in the list of days, then set the index to the column.
DataFrame.to_excel () method in Pandas. Last Updated : 16 Jul, 2020. The to_excel () method is used to export the DataFrame to the excel file. To write a single object to the excel file, we have to specify the target file name. If we want to write to multiple sheets, we need to create an ExcelWriter object with target filename and also need to ...
What happens in many cases is that we need to filter merged cells in excel. But merging imposes a problem while filtering data. For example, look at the dataset below, We are going to try filtering the following table. First, select the table and click the Filter icon from Sort and Filter group in the Data tab.
To casually come back 8 years later, pandas.read_excel () can solve this internally for you with the index_col parameter. Passing index_col as a list will cause pandas to look for a MultiIndex. In the case where there is a list of length one, pandas creates a regular Index filling in the data.
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.
194
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
