to_excel with MultiIndex adds a blank line

[stefanopassador]

Code Sample

data = [[1, 2, 3, 4, 5, 6], [7, 8, 9, 10, 11, 12]]
multi_index = pd.MultiIndex.from_product([['a', 'b'], ['one', 'two', 'three']])
df = pd.DataFrame(data, columns=multi_index, index=[1, 2])

df.to_excel('test_files/test.xlsx')

Problem description

When exporting an xlsx file from a dataframe with MultiIndex, a blank line is added.

Here a screenshot representing the problem:

This is happening with pandas 0.25.0.

Output of pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.7.3.final.0
python-bits : 64
OS : Windows
OS-release : 10
machine : AMD64
processor : Intel64 Family 6 Model 142 Stepping 9, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.None

pandas : 0.25.0
numpy : 1.16.4
pytz : 2019.1
dateutil : 2.8.0
pip : 19.2
setuptools : 40.8.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 2.10.1
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : 0.2.3
lxml.etree : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : 2.6.2
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
xarray : None
xlrd : 1.2.0
xlwt : None
xlsxwriter : None

[jmcnamara]

Note, there is a typo in the columns argument. The program should be:

import pandas as pd

data = [[1, 2, 3, 4, 5, 6], [7, 8, 9, 10, 11, 12]]
multi_index = pd.MultiIndex.from_product([['a', 'b'], ['one', 'two', 'three']])
df = pd.DataFrame(data, columns=multi_index, index=[1, 2])

df.to_excel('test.xlsx')

And I suppose the expected out should be:

The blank line is probably there to allow for the index label. For example:

 import pandas as pd

data = [[1, 2, 3, 4, 5, 6], [7, 8, 9, 10, 11, 12]]
multi_index = pd.MultiIndex.from_product([['a', 'b'], ['one', 'two', 'three']])
df = pd.DataFrame(data, columns=multi_index, index=[1, 2])

df.to_excel('test.xlsx', index_label="Foo")

Output:

This matches the repl output if the the index name is set:

>>> df.index.name = 'foo'
>>> df
      a             b
    one two three one two three
foo
1     1   2     3   4   5     6
2     7   8     9  10  11    12

It is probably a separate question/feature request on whether it should be something like:

[stefanopassador]

I edited my code snippet to fix the typo.

About my problem, how should I remove that line?
Without MultiIndex I don't encounter the same behavior.

[yuylyp]

@stefanopassador
i encountered the same question, it confused me for some days.
have you got any idea?

[yuylyp]

I have tried to_csv just now, and the blank row disappear by setting index name to None.
while I turned to to_excel, I encountered it again.
It seems to be an unwanted behavior in to_excel.
In other words, it should be an BUG.

[stefanopassador]

@yuylyp I haven't had any luck about it.

[yuylyp]

I might get the reason, I found some code in /pandas/io/formats/excel.py as below.
However I don't know how to fix this issue

# MultiIndex columns require an extra row
# with index names (blank if None) for
# unambiguous round-trip, unless not merging,
# in which case the names all go on one row Issue #11328

[yuylyp]

@stefanopassador
I found a workground, please comment the following code in function _format_regular_rows in /pandas/io/formats/excel.py
but the index name will cover the lowest-level column name while both of them are not none or empty.
so I suggests the developer to add more condition in this if-statement.

if isinstance(self.columns, ABCMultiIndex):
    self.rowcounter += 1

[gergab1129]

I managed to get a workaround to this problem using quite a mix between offsets and writing the DataFrame by segments.

May data be a Dataframe like this:

Extracting the level_0 column names and writing the index you can overcome the blank line problem

index_mes = data.columns.levels[0]

for index_num, value in enumerate(data.index):
        worksheet.write(row_offset + index_num + 3,
                        column_offset, value)`

and then you can write the DataFrame splitting by key:

for mes in index_mes:

        worksheet.merge_range(row_offset + 1, column_offset + 1,
                              row_offset + 1, column_offset + 3,
                              mes)
        data[mes].to_excel(writer, nombre_hoja, startrow=row_offset + 2,
                              startcol=column_offset + 1, header=True,
                              index=False)
         column_offset += 3`

However I found a problem, This is the expected output:

But what I get is this:

You can see that the month are not in order, this problem comes from the line

index_mes = data.columns.levels[0]

This gets you the level_0 columns names in alphabetical order instead of the original order and this I haven't been able to overcome. However if your keys do not depend on orders I see no problem using this approach.

 

[bartkim0426]

@yuylyp
Can you explain more about the specific condition?
I tried to fix this while the sprint in pycon 2019 Korea. While making a test code, I figured out that I can't reproduce this problem with test code because read_excel doesn't have skip_blanklines.
Since I'm not a hard user of pandas, I can't specify the condition for this problem. It will be very helpful to give a clue.

[yuylyp]

@bartkim0426
for example, while the name of 1-level index or names of multiindex is(are) none/ empty or others which indicates the empty string, the statement (self.rowcounter += 1) shouldn't be executed.
while we add those condition, it will works well as we thought.