BUG: to_excel inserts invalid data if a level in a MultiIndex is None

musshorn commented 1 year ago

Pandas version checks

[X] I have checked that this issue has not already been reported.
[X] I have confirmed this bug exists on the latest version of pandas.
[X] I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
import numpy as np

Cols = pd.MultiIndex.from_tuples([("Item", "Type 1", "Class A"), ("Item", "Type 1", "Class B"), ("Item", "Type 2", "Class A"), ("Item", "Type 2", "Class B"), ("Item", "Type 3", None)])

df = pd.DataFrame(np.random.randn(10, 5), columns=Cols)
print(df)
df.to_excel("Test.xlsx")

Issue Description

When a level in a MultiIndex is None, the resulting dataframe when exported to excel has an invalid level inserted replacing the None. See Image. PandasIssue

Expected Behavior

I would expect the result from to_excel should reflect the levels in the MultiIndex correctly.

Installed Versions

>>> pd.show_versions() INSTALLED VERSIONS ------------------ commit : 2e218d10984e9919f0296931d92ea851c6a6faf5 python : 3.10.0.final.0 python-bits : 64 OS : Windows OS-release : 10 Version : 10.0.19044 machine : AMD64 processor : Intel64 Family 6 Model 79 Stepping 1, GenuineIntel byteorder : little LC_ALL : None LANG : en_US.UTF-8 LOCALE : English_Australia.1252 pandas : 1.5.3 numpy : 1.22.4 pytz : 2022.2.1 dateutil : 2.8.2 setuptools : 57.4.0 pip : 23.0 Cython : 0.29.32 pytest : 7.2.0 hypothesis : None sphinx : 4.5.0 blosc : None feather : None xlsxwriter : 3.0.1 lxml.etree : 4.9.1 html5lib : None pymysql : None psycopg2 : None jinja2 : 3.1.2 IPython : 8.1.1 pandas_datareader: None bs4 : 4.10.0 bottleneck : None brotli : 1.0.9 fastparquet : None fsspec : None gcsfs : None matplotlib : 3.5.1 numba : None numexpr : 2.8.4 odfpy : None openpyxl : 3.1.0 pandas_gbq : None pyarrow : None pyreadstat : None pyxlsb : None s3fs : None scipy : 1.8.0 snappy : None sqlalchemy : None tables : 3.7.0 tabulate : 0.8.9 xarray : None xlrd : None xlwt : 1.3.0 zstandard : 0.17.0 tzdata : None

phofl commented 1 year ago

I guess this is related to forward filling for round tripping purposes (see read_excel index_col documentation). But investigations welcome

musshorn commented 1 year ago

I think https://github.com/pandas-dev/pandas/blob/13db83ad3f1245107151e6a626537a8ea7f72df1/pandas/io/formats/excel.py#L641-L660 is the offending loop. When it reaches the last level, levels.take(level_codes) gets passed [0, 1, 0, 1, -1] which sets values to ['Class A', 'Class B', 'Class A', 'Class B', 'Class B'] and that's ultimately the row that gets written to Excel.