Skip to content

BUG: Error when exporting empty DataFrame with MultiIndex on both axes to xlsx file #57696

Closed
@speleo3

Description

@speleo3

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
df = pd.DataFrame(
    data=[],
    index=pd.MultiIndex.from_tuples([], names=[0, 1]),
    columns=pd.MultiIndex.from_tuples([("A", "B")]),
)
df.to_excel("tmp.xlsx")

Issue Description

The given example tries to write an empty dataframe with multiindex rows and columns to an Excel xlsx file.

Observed exception:

Traceback (most recent call last):
  File "example.py", line 7, in <module>
    df.to_excel("tmp.xlsx")
  File ".../lib/python3.10/site-packages/pandas/core/generic.py", line 2258, in to_excel
    formatter.write(
  File ".../lib/python3.10/site-packages/pandas/io/formats/excel.py", line 944, in write
    writer._write_cells(
  File ".../lib/python3.10/site-packages/pandas/io/excel/_xlsxwriter.py", line 261, in _write_cells
    for cell in cells:
  File ".../lib/python3.10/site-packages/pandas/io/formats/excel.py", line 881, in get_formatted_cells
    for cell in itertools.chain(self._format_header(), self._format_body()):
  File ".../lib/python3.10/site-packages/pandas/io/formats/excel.py", line 626, in _format_header_mi
    coloffset = len(self.df.index[0]) - 1
  File ".../lib/python3.10/site-packages/pandas/core/indexes/multi.py", line 2140, in __getitem__
    if level_codes[key] == -1:
IndexError: index 0 is out of bounds for axis 0 with size 0

This bug is almost identical to #46873 but for the case with multiindex columns.

Expected Behavior

Excel file written. No exception.

Installed Versions

INSTALLED VERSIONS
------------------
commit                : ab2980ba52ae24db72765b3c4ae1d33ac8fad3f5
python                : 3.10.13.final.0
python-bits           : 64
OS                    : Linux
OS-release            : 6.7.6-arch1-1
Version               : #1 SMP PREEMPT_DYNAMIC Fri, 23 Feb 2024 16:31:48 +0000
machine               : x86_64
processor             : 
byteorder             : little
LC_ALL                : None
LANG                  : en_US.UTF-8
LOCALE                : en_US.UTF-8

pandas : 3.0.0.dev0+451.gab2980ba52.dirty
numpy : 1.26.4
pytz : 2024.1
dateutil : 2.9.0
setuptools : 69.1.1
pip : 24.0
Cython : 3.0.8
pytest : 8.0.2
hypothesis : 6.98.15
sphinx : 7.2.6
blosc : None
feather : None
xlsxwriter : 3.1.9
lxml.etree : 5.1.0
html5lib : 1.1
pymysql : 1.4.6
psycopg2 : 2.9.9
jinja2 : 3.1.3
IPython : 8.22.1
pandas_datareader : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.12.3
bottleneck : 1.3.8
fastparquet : 2024.2.0
fsspec : 2024.2.0
gcsfs : 2024.2.0
matplotlib : 3.8.3
numba : 0.59.0
numexpr : 2.9.0
odfpy : None
openpyxl : 3.1.2
pyarrow : 15.0.0
pyreadstat : 1.2.6
python-calamine : None
pyxlsb : 1.0.10
s3fs : 2024.2.0
scipy : 1.12.0
sqlalchemy : 2.0.27
tables : 3.9.2
tabulate : 0.9.0
xarray : 2024.2.0
xlrd : 2.0.1
zstandard : 0.22.0
tzdata : 2024.1
qtpy : None
pyqt5 : None

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions