BUG: read_excel fails with multirow headers if headers are followed by two empty rows

[neutronleak]

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

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

• (optional) I have confirmed this bug exists on the master branch of pandas.

---

Note: Please read this guide detailing how to provide the necessary information for us to reproduce your bug.

Code Sample, a copy-pastable example

from pathlib import Path
import pandas as pd

excel_file = Path("test_excel_file.xlsx")
table = pd.read_excel(excel_file, 'Taul1', header=[0, 1])

Problem description

Given table

A column	B column	C column
part 1	part 2	part 3
[empty]	[empty]	[empty]
[empty]	[empty]	[empty]
Row 1 Col A	Row 1 Col B	Row 1 Col C
Row 2 Col A	Row 2 Col B	Row 2 Col C

pd.read_excel with the new openpyxl-backend fails with exception

Traceback (most recent call last):
File "pandas_openpyxl_bug\test_pandas_openpyxl.py", line 12, in
table = pd.read_excel(excel_file, 'Taul1', header=[0, 1])
File ".virtualenvs\pandas_openpyxl_bug-3rzOJb6e\lib\site-packages\pandas\util_decorators.py", line 299, in wrapper
return func(*args, **kwargs)
File "virtualenvs\pandas_openpyxl_bug-3rzOJb6e\lib\site-packages\pandas\io\excel_base.py", line 344, in read_excel
data = io.parse(
File ".virtualenvs\pandas_openpyxl_bug-3rzOJb6e\lib\site-packages\pandas\io\excel_base.py", line 1170, in parse
return self._reader.parse(
File ".virtualenvs\pandas_openpyxl_bug-3rzOJb6e\lib\site-packages\pandas\io\excel_base.py", line 566, in parse
output[asheetname] = parser.read(nrows=nrows)
File ".virtualenvs\pandas_openpyxl_bug-3rzOJb6e\lib\site-packages\pandas\io\parsers.py", line 1057, in read
index, columns, col_dict = self._engine.read(nrows)
File ".virtualenvs\pandas_openpyxl_bug-3rzOJb6e\lib\site-packages\pandas\io\parsers.py", line 2503, in read
index, columns = self._make_index(data, alldata, columns, indexnamerow)
File ".virtualenvs\pandas_openpyxl_bug-3rzOJb6e\lib\site-packages\pandas\io\parsers.py", line 1566, in _make_index
index = index.set_names(indexnamerow[:coffset]) # type: ignore[union-attr]
AttributeError: 'NoneType' object has no attribute 'set_names'

Expected Output

A dataframe without a crash

Output of pd.show_versions()

pandas_openpyxl_bug.zip

INSTALLED VERSIONS

commit : f2c8480
python : 3.9.2.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.19041
machine : AMD64
processor : AMD64 Family 23 Model 8 Stepping 2, AuthenticAMD
byteorder : little
LC_ALL : None
LANG : None
LOCALE : Finnish_Finland.1252

pandas : 1.2.3
numpy : 1.20.1
pytz : 2021.1
dateutil : 2.8.1
pip : 21.0.1
setuptools : 53.0.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 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : 3.0.7
pandas_gbq : None
pyarrow : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
numba : None

[ahawryluk]

I can confirm the bug on master, though the last few lines of the traceback are different.

Traceback (most recent call last):
  File "test_pandas_openpyxl.py", line 12, in <module>
    table = pd.read_excel(excel_file, 'Taul1', header=[0, 1])
  File "/home/andrew/pandas-ahawryluk/pandas/util/_decorators.py", line 310, in wrapper
    return func(*args, **kwargs)
  File "/home/andrew/pandas-ahawryluk/pandas/io/excel/_base.py", line 377, in read_excel
    data = io.parse(
  File "/home/andrew/pandas-ahawryluk/pandas/io/excel/_base.py", line 1190, in parse
    return self._reader.parse(
  File "/home/andrew/pandas-ahawryluk/pandas/io/excel/_base.py", line 612, in parse
    output[asheetname] = parser.read(nrows=nrows)
  File "/home/andrew/pandas-ahawryluk/pandas/io/parsers/readers.py", line 998, in read
    index, columns, col_dict = self._engine.read(nrows)
  File "/home/andrew/pandas-ahawryluk/pandas/io/parsers/python_parser.py", line 281, in read
    index, columns = self._make_index(data, alldata, columns, indexnamerow)
  File "/home/andrew/pandas-ahawryluk/pandas/io/parsers/base_parser.py", line 410, in _make_index
    assert index is not None
AssertionError

I suspect there are two different bugs here. The first is the crash (above), but the second is the text parser consuming one of the empty lines in this particular case:

from io import StringIO
data = 'A,A,B\na,b,a\n,,\n,,\n1,2,3\n4,5,6\n7,8,9\n'
pd.read_csv(StringIO(data), header=[0,1])

     A         B
     a    b    a
0  NaN  NaN  NaN
1  1.0  2.0  3.0
2  4.0  5.0  6.0
3  7.0  8.0  9.0

This second bug is probably related to #34188

[rhshadrach]

Thanks @neutronleak. Reproducible example:

df = pd.DataFrame(
    {
        'a': ['A', None, None, '1', '2'],
        'b': ['B', None, None, '3', '4'],
    }
)
df.to_excel('test.xlsx', index=False)
pd.read_excel('test.xlsx', engine='openpyxl', header=[0, 1])

The method OpenpyxlReader.get_sheet_data is returning

 [['a', 'b'], ['A', 'B'], ['', ''], ['', ''], ['1', '3'], ['2', '4']]

which looks right to me, so I think this isn't an openpyxl-specific issue, but rather the parser. I think you're right @ahawryluk, the other issue looks related; thanks for bringing that up.

[ahawryluk]

take