BUG: read_excel throws generic IndexError if header argument specifies non-existent rows

[MrCreosote]

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

It doesn't seem to be based on a search for read_excel and indexerror

• 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

Given an excel file with one tab that contains the text header in cell A1 and nothing else:

In [1]: import pandas

In [2]: pandas.read_excel("~/temp/test_pandas/headers.xlsx", header=[0, 1])
---------------------------------------------------------------------------
IndexError                                Traceback (most recent call last)

*snip - see comment below for full traceback*

    564                     if index_col is not None:

IndexError: list index out of range

Problem description

The code throws a (presumably unintended) IndexError, which is difficult to respond to as the actual cause of the error is not specified.

Expected Output

A ParserError (or other appropriate error type) with an error message that makes the cause of the problem clear.

Output of pd.show_versions()

INSTALLED VERSIONS

commit : 5f648bf
python : 3.9.6.final.0
python-bits : 64
OS : Linux
OS-release : 4.15.0-154-generic
Version : #161-Ubuntu SMP Fri Jul 30 13:04:17 UTC 2021
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.3.2
numpy : 1.21.2
pytz : 2021.1
dateutil : 2.8.2
pip : 20.3.3
setuptools : 51.3.3
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 : 7.26.0
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 : 2.0.1
xlwt : None
numba : None

[MrCreosote]

Full stacktrace:

IndexError                                Traceback (most recent call last)
<ipython-input-2-c7e91d4b5ed4> in <module>
----> 1 pandas.read_excel("~/temp/test_pandas/headers.xlsx", header=[0, 1])

~/.local/share/virtualenvs/py3.9playground--W4ZB8cD/lib/python3.9/site-packages/pandas/util/_decorators.py in wrapper(*args, **kwargs)
    309                     stacklevel=stacklevel,
    310                 )
--> 311             return func(*args, **kwargs)
    312 
    313         return wrapper

~/.local/share/virtualenvs/py3.9playground--W4ZB8cD/lib/python3.9/site-packages/pandas/io/excel/_base.py in read_excel(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, storage_options)
    370 
    371     try:
--> 372         data = io.parse(
    373             sheet_name=sheet_name,
    374             header=header,

~/.local/share/virtualenvs/py3.9playground--W4ZB8cD/lib/python3.9/site-packages/pandas/io/excel/_base.py in parse(self, sheet_name, header, names, index_col, usecols, squeeze, converters, true_values, false_values, skiprows, nrows, na_values, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, **kwds)
   1270             DataFrame from the passed in Excel file.
   1271         """
-> 1272         return self._reader.parse(
   1273             sheet_name=sheet_name,
   1274             header=header,

~/.local/share/virtualenvs/py3.9playground--W4ZB8cD/lib/python3.9/site-packages/pandas/io/excel/_base.py in parse(self, sheet_name, header, names, index_col, usecols, squeeze, dtype, true_values, false_values, skiprows, nrows, na_values, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, **kwds)
    560                         row += skiprows
    561 
--> 562                     data[row], control_row = fill_mi_header(data[row], control_row)
    563 
    564                     if index_col is not None:

IndexError: list index out of range

[kurchi1205]

I am new to OpenSource Projects . Can I work on this bug ?

[attack68]

@kurchi1205 this hasn't been triaged. You are better looking at any existing issues marked "good first issue" and "contributions welcome"

[phofl]

Is it possible to create a code only example without an external file?

[MrCreosote]

@phofl here you go:

In [1]: import pandas

In [2]: from io import BytesIO

In [3]: df = pandas.DataFrame(["header"])

In [4]: b = BytesIO()

In [5]: df.to_excel(b, header=False, index=False)

In [6]: b.seek(0)
Out[6]: 0

In [7]: pandas.read_excel(b, header=[0,1])
---------------------------------------------------------------------------
IndexError                                Traceback (most recent call last)

[simonjayhawkins]

@Tanu1201 see #43143 (comment)

[phofl]

Thanks @MrCreosote. Looks similar to the read_csv bug