Repository: pandas-dev/pandas
Pull request: BUG Avoid IndexError on writing empty (row) MI df to excel (GH19543)
- [x] closes #19543 
- [x] Tests added and passed
- [x] All code checks passed
- [x] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.


Problem statement:
BUG: to_excel to throw IndexError for dataframe with empty (row) MultiIndex
```python
mi = pd.MultiIndex.from_arrays([
        np.array(['a', 'a', 'b', 'b']), 
        np.array(['1', '2', '2', '3']), 
        np.array(['alpha', 'beta', 'alpha', 'beta']),
    ], names=['one', 'two', 'three'])
df = pd.DataFrame(np.random.rand(4, 3), index=mi)
df2 = df.loc[('b', '1', slice(None)), :]
print(df2.index)
```
Output:
```
MultiIndex(levels=[['a', 'b'], ['1', '2', '3'], ['alpha', 'beta']],
           labels=[[], [], []],
           names=['one', 'two', 'three'])
```
The next line causes an `IndexError`:
```python
df2.to_excel('test.xlsx')
```
Notice that the 'empty' `MultiIndex` does not give any indication of its presence when the entire dataframe is printed, looking like an ordinary empty dataframe instead. 
```
Empty DataFrame
Columns: [0, 1, 2]
Index: []
```
#### Problem description

I'm not quite sure which behaviour is the problematic one here: the fact that the first block of code creates an 'empty' `MultiIndex` that has values in `levels`, or that `to_excel` doesn't write a `DataFrame` with such an index to file properly. The 'empty' `MultiIndex` behaviour is a little bit anomalous by itself, because a similar block of code (below) will cause a `KeyError`. The `KeyError` is 'suppressed' by the fact that there is a third level in the `MultiIndex`.

```python
mi = pd.MultiIndex.from_arrays([
        np.array(['a', 'a', 'b', 'b']), 
        np.array(['1', '2', '2', '3']), 
    ], names=['one', 'two'])
df = pd.DataFrame(np.random.rand(4, 3), index=mi)
df2 = df.loc[('b', '1'), :]
```



I initially ran into this behaviour (the IndexError) when using `xs`, but since `xs` seems to be headed for the deprecation guillotine, I figured I'd rewrite this with `loc` instead.

#### Output of ``pd.show_versions()``

<details>

INSTALLED VERSIONS
------------------
commit: None
python: 3.6.1.final.0
python-bits: 64
OS: Linux
OS-release: 4.4.0-43-Microsoft
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.22.0
pytest: None
pip: 9.0.1
setuptools: 27.2.0
Cython: None
numpy: 1.14.0
scipy: 1.0.0
pyarrow: None
xarray: None
IPython: 6.2.1
sphinx: None
patsy: None
dateutil: 2.6.1
pytz: 2017.3
blosc: None
bottleneck: None
tables: 3.4.2
numexpr: 2.6.4
feather: None
matplotlib: None
openpyxl: 2.4.9
xlrd: 1.1.0
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 1.0b10
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

</details>

Hints from issue discussion:
> I'm not quite sure which behaviour is the problematic one here: the fact that the first block of code creates an 'empty' MultiIndex that has values in levels,

This is normal and expected behaviour. When taking a subset of a MultiIndex, the `levels` are not re-computed but kept intact (the fact that it is empty is expressed in its empty `labels`).

> or that to_excel doesn't write a DataFrame with such an index to file properly. 

This could be considered a bug, given that for an empty dataframe without multiindex, this works. Or at least it should give a better error message.

The fact that `df.loc[('b', '1', slice(None)), :]` does not raise an error while `df.loc[('b', '1'), :]` does, I am not fully sure about the explanation. But I suppose it is because of having the `slice(None)` makes this seen as "indexing an MI with multiple indexers (one for each level)", while in the other case you are selecting only single labels (up to a certain level). But I agree this is a confusing case. 
If we want to discuss this further, let's open a separate issue for it.

Alright, so this issue should be focused on `to_excel` throwing `IndexError`. I'll open a separate issue for the `MultiIndex` slicing behaviour.
OK, I edited the title to reflect this. Probably the most sensible thing to do is to be consistent with empty dataframes in general (writing only the column headers to excel)

PR welcome!
Hello,
Not a lot of added value ... but the code below helps to easy reproduce this:
``pd.DataFrame([[],[],[]]).T.set_index([0, 1]).to_excel('test.xlsx') ``
I encountered this error on 1.2.4 having produced such an empty-multiindexed DataFrame via `DataFrame.compare` of two dataframes having no differences, where the index on each DataFrame happened to be a MultiIndex; the end result is an empty DataFrame with an empty and unlabeled MultiIndex for columns, and an empty-but-labeled MultiIndex for rows. Without an existing MultiIndex, this could still arise from comparison-with-no-differences followed by transposition (converting the automatically-generated MultiIndex columns into rows). I would also like to note that no error arises from an empty DataFrame with MultiIndex columns but single-level rows.

Simple sample of such, both as an example of this problem naturally arising from `compare` and so that the actual error message (`index 0 is out of bounds for axis 0 with size 0`) beyond that it's an IndexError shows up on this issue for ease of searching:
```
Python 3.7.4 (default, Apr 28 2021, 11:52:34)
[GCC 10.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas as pd
>>> df = pd.DataFrame([[1,2,3],[4,5,6]])
>>> df.compare(df)
Empty DataFrame
Columns: []
Index: []
>>> df.compare(df).T.index
MultiIndex([], )
>>> df.compare(df).T.index.levels
FrozenList([[], ['self', 'other']])
>>> df.compare(df).to_excel("test.xlsx")  # no error
>>> df.compare(df).T.to_excel("test.xlsx")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/qwertystop/.pyenv/versions/testenv/lib/python3.7/site-packages/pandas
/core/generic.py", line 2196, in to_excel
    storage_options=storage_options,
  File "/home/qwertystop/.pyenv/versions/testenv/lib/python3.7/site-packages/pandas
/io/formats/excel.py", line 826, in write
    freeze_panes=freeze_panes,
  File "/home/qwertystop/.pyenv/versions/testenv/lib/python3.7/site-packages/pandas
/io/excel/_openpyxl.py", line 410, in write_cells
    for cell in cells:
  File "/home/qwertystop/.pyenv/versions/testenv/lib/python3.7/site-packages/pandas
/io/formats/excel.py", line 757, in get_formatted_cells
    for cell in itertools.chain(self._format_header(), self._format_body()):
  File "/home/qwertystop/.pyenv/versions/testenv/lib/python3.7/site-packages/pandas
/io/formats/excel.py", line 576, in _format_header_regular
    coloffset = len(self.df.index[0])
  File "/home/qwertystop/.pyenv/versions/testenv/lib/python3.7/site-packages/pandas
/core/indexes/multi.py", line 2028, in __getitem__
    if level_codes[key] == -1:
IndexError: index 0 is out of bounds for axis 0 with size 0
```
If I see this correctly, it would suffice to reference `self.df.index.names` instead of `self.df.index[0]` in https://github.com/pandas-dev/pandas/blob/46bcf3740b38339f62b94e66ec29537a28a17140/pandas/io/formats/excel.py#L637

I will try to construct a fix.

Commit message: BUG Avoid IndexError on writing empty (row) MI df to excel (GH19543)

re-add gh ref

Patch to review:
diff --git a/doc/source/whatsnew/v1.5.0.rst b/doc/source/whatsnew/v1.5.0.rst
index 5891eeea98cbb..13cf92f530f5e 100644
--- a/doc/source/whatsnew/v1.5.0.rst
+++ b/doc/source/whatsnew/v1.5.0.rst
@@ -872,6 +872,7 @@ I/O
 - Bug in :func:`read_sas` returned ``None`` rather than an empty DataFrame for SAS7BDAT files with zero rows (:issue:`18198`)
 - Bug in :class:`StataWriter` where value labels were always written with default encoding (:issue:`46750`)
 - Bug in :class:`StataWriterUTF8` where some valid characters were removed from variable names (:issue:`47276`)
+- Bug in :meth:`to_excel` when writing an empty dataframe with :class:`MultiIndex` (:issue:`19543`)
 
 Period
 ^^^^^^
diff --git a/pandas/io/formats/excel.py b/pandas/io/formats/excel.py
index d0fea32cafe26..effb0652aa2d3 100644
--- a/pandas/io/formats/excel.py
+++ b/pandas/io/formats/excel.py
@@ -634,7 +634,7 @@ def _format_header_regular(self) -> Iterable[ExcelCell]:
             if self.index:
                 coloffset = 1
                 if isinstance(self.df.index, MultiIndex):
-                    coloffset = len(self.df.index[0])
+                    coloffset = len(self.df.index.names)
 
             colnames = self.columns
             if self._has_aliases:
diff --git a/pandas/tests/io/excel/test_writers.py b/pandas/tests/io/excel/test_writers.py
index 42483645d9fc3..ba6366b71d854 100644
--- a/pandas/tests/io/excel/test_writers.py
+++ b/pandas/tests/io/excel/test_writers.py
@@ -838,6 +838,19 @@ def test_to_excel_multiindex_no_write_index(self, path):
         # Test that it is the same as the initial frame.
         tm.assert_frame_equal(frame1, frame3)
 
+    def test_to_excel_empty_multiindex(self, path):
+        # GH 19543.
+        expected = DataFrame([], columns=[0, 1, 2])
+
+        df = DataFrame([], index=MultiIndex.from_tuples([], names=[0, 1]), columns=[2])
+        df.to_excel(path, "test1")
+
+        with ExcelFile(path) as reader:
+            result = pd.read_excel(reader, sheet_name="test1")
+        tm.assert_frame_equal(
+            result, expected, check_index_type=False, check_dtype=False
+        )
+
     def test_to_excel_float_format(self, path):
         df = DataFrame(
             [[0.123456, 0.234567, 0.567567], [12.32112, 123123.2, 321321.2]],


Code review comments:
diff_hunk: @@ -872,6 +872,7 @@ I/O
 - Bug in :func:`read_sas` returned ``None`` rather than an empty DataFrame for SAS7BDAT files with zero rows (:issue:`18198`)
 - Bug in :class:`StataWriter` where value labels were always written with default encoding (:issue:`46750`)
 - Bug in :class:`StataWriterUTF8` where some valid characters were removed from variable names (:issue:`47276`)
+- Bug in :meth:`to_excel` when writing an empty dataframe with :class:`MultiIndex` (:issue:`19543`)
path: doc/source/whatsnew/v1.5.0.rst
text: @user1:
DataFrame.to_excel please

Please don't resolve my remarks when I haven't checked them over. Normally the author of the remarks clicks resolve

@author:
🙈 done