Export to excel for multiindex columns

Hi All,

I’m just using 0.17 for testing exporting multiindex column dataframe. When i’m pivot table dataframe with X column as index (and without “columns” argument), which will not create multiindex column,

pivot table code (without columns argument):

df.pivot_table(index=["X"], columns=[], aggfunc={"Y": len,"Z":np.sum}, fill_value=0)
df.to_excel(FILE_NAME)

the output using to_excel() follow new format:

New format output

But, the question arise when i’m trying to pivot table X column as index (and with “columns” argument), which will create multiindex columns. Then, i’m trying to export it using to_excel() and still follow old format.

pivot table code (with columns argument):

df.pivot_table(index=["X"], columns=["A"], aggfunc={"Y": len,"Z":np.sum}, fill_value=0)
df.to_excel(FILE_NAME)

Old format output

I don’t know if this newest excel export format only applied to single column index, not multicolumn index. Was this case expected or there is something wrong?

Thanks

Issue Analytics

State:
Created 8 years ago
Comments:14 (9 by maintainers)

Top GitHub Comments

1reaction

sakinaljanacommented, Oct 13, 2015

@chris-b1 : Correct, the concern is more to the feature consistency. I assume if new format will be applied to both single column and multi index column. Thanks for clarifying this!!

@jreback : Yeah, it’s perfectly works for doing both write and read from excel. But, it seems more elegant if the index column going up one level, see the following example: reset_index

But then, i found new error (i don’t know if this already fixed or not). When i’m trying to write excel with not including index (index = False), it throw exception.

NotImplementedError: Writing to Excel with MultiIndex columns and no index ('index'=False) is not yet implemented.

The code is like this

df = pd.DataFrame(data={"X":["test1","test2","test3","test4","test5","test5", "test2", "test3"],"Y":[0,1,2,2,1,2,10,3], "Z": [1000,300,400,500,2350,100,100,1000], "A":["category1","category2","category2","category3","category4","category4","category5","category1"]})

# pivot df with column argument
df_with_col_arg = df.pivot_table(index=["X"], columns=["A"], aggfunc={"Y":np.sum,"Z":np.sum}, fill_value=0)

# resetting index
df_with_col_arg.reset_index(inplace=True)

# write to excel with no index
df_with_col_arg.to_excel("excel-with-no-index.xlsx", index=False)

Any idea?

0reactions

hsheikha1429commented, Dec 13, 2018

Had the same error issue and writing here as a reference to tell what fix it in my case:

Having the index=True fixed my case, the export from df to excel showed the multi Indexing in perfect understood position. filename = os.path.join(outputDir, "named_df.xls") # .xls or .xlsx doesn't matter df.to_excel(filename, ### index=True)

Top Results From Across the Web

How to export pandas dataframe with Multi-index columns to ...

One trick is write first level of MultiIndex separately and then skip first row for write another values - DataFrame without first level:...

pandas.DataFrame.to_excel — pandas 0.18.1 documentation

pandas.DataFrame.to_excel¶ ... Column label for index column(s) if desired. If None is given, and header and index are True, then the index names...

Pandas to_excel: Writing DataFrames to Excel Files - Datagy

The easiest way to save a Pandas DataFrame to an Excel file is by passing a path to the .to_excel() method. This will...

How to export pandas dataframe with Multi-index columns to ...

One trick is write first level of MultiIndex separately and then skip first row for write another values - DataFrame without first level:...

How to flatten MultiIndex Columns and Rows in Pandas

1. MultiIndex columns: use get_level_values() · 2. Flatten columns: use to_flat_index() · 3. Flatten columns: join column labels · 4. Flatten rows: flatten...