BUG: Importing Pandas-generated excel loses multiindex multicolumn names

[filipthor]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
import numpy as np
import xlsxwriter

# Generate an example dataframe with multiindex index and columns
multi_index = pd.MultiIndex.from_tuples([("r0", "rA"),
                                       ("r1", "rB")],
                                       names=['Courses','Fee'])

cols = pd.MultiIndex.from_tuples([("Gasoline", "Toyoto"), 
                                  ("Gasoline", "Ford"), 
                                  ("Electric", "Tesla"),
                                  ("Electric", "Nio")])

data=[[100,300, 900,400 ], [200,500, 300,600]]

df = pd.DataFrame(data, columns=cols,index=multi_index)

# Export the df (with multiindexes)
writer = pd.ExcelWriter("df_multiindex.xlsx",engine='xlsxwriter')
df.to_excel(writer)
writer.close()

# Reimport the df - this works, aside from the poor formatting of the export. 
df_multiindex_import = pd.read_excel("df_multiindex.xlsx",header=[0,1],index_col=[0,1]) 

# Export the df (with reset index for "better" export formatting) - this generates an empty row, also a problem in itself. # index column is kept against wish) - index = False not implemented for MultiIndex.
writer = pd.ExcelWriter("df_index_reset.xlsx",engine='xlsxwriter')
df.reset_index(col_level=df.columns.nlevels-1).to_excel(writer)
writer.close()

df_index_reset_import = pd.read_excel("df_index_reset.xlsx",header=[0,1],index_col=[1,2]) 

df_index_reset_import.columns # Name of all but last multiindex name is lost

Issue Description

Exporting a multiindex pandas dataframe leaves a blank row due to indexing. The option should exist to allow for no blank row.

This problem can be "solved" by resetting the index and then exporting. This generates an empty row which is mentioned in issue #27772.

Through workarounds this row can be deleted (though this should probably be fixed, or at least given an option to choose.

Reimporting the index-reset excel yields a df which is "corrupt" in that it is not the original df generated.

Issue 1: The "index" column is included in the export. Index=False is not implemented for MultiIndex.
Issue 2: Defining the first columns as indexes drops all but the last name. (see last row of code above)

Expected Behavior

Expected behavior 1:
Exporting multiIndex dataframes should have the option to not leave a blank row due to multiIndexes.

Expected behavior 2:
Resetting the index of a multiIndex dataframe and then exporting should not generate an empty row.

Expected behavior 3:
Resetting the index of a multiIndex dataframe and then exporting should have the functionality to ignore index as mentioned in argument "index=False"

Expected behavior 4:
The column names should not be deleted/lost when reimporting the excel export.

General expectation:
Exporting and importing a df should be a "commutable" action - Generating a df, exporting it, importing, should end up with an identical df.

Installed Versions

Replace this line with the output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit : 8dab54d python : 3.9.10.final.0 python-bits : 64 OS : Windows OS-release : 10 Version : 10.0.19044 machine : AMD64 processor : Intel64 Family 6 Model 142 Stepping 12, GenuineIntel byteorder : little LC_ALL : None LANG : en LOCALE : English_United Kingdom.1252

pandas : 1.5.2
numpy : 1.21.2
pytz : 2021.1
dateutil : 2.8.2
setuptools : 62.0.0
pip : 21.2.4
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : 3.0.7
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : 7.29.0
pandas_datareader: None
bs4 : None
bottleneck : None
brotli : None
fastparquet : None
fsspec : 2021.11.0
gcsfs : None
matplotlib : 3.2.2
numba : None
numexpr : None
odfpy : None
openpyxl : 3.0.9
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : 2021.11.0
scipy : 1.7.1
snappy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
zstandard : None
tzdata : None

[phofl]

Hi, thanks for your report. I think this is a duplicate, could you search the issue tracker again? I am pretty sure that I've seen something very similar

[mroeschke]

Closing as a potential issue duplicate