BUG/API: multiple headers and index_col != range(...) in parsers

[mzeitlin11]

• 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.

---

I was looking into #34765 which traced back to the parsers in general and isn't excel specific. For both the c and python engines, using a multiple line header and index_col which doesn't include only the leftmost columns, results are unexpected:

Code Sample, a copy-pastable example

import pandas as pd
import io

s = """
a,b,c,d
e,f,g,h
x,y,1,2
"""

df = pd.read_csv(io.StringIO(s), header=[0, 1], index_col=1)
print(df)
print(df.columns)

   1  (c, g)  (d, h)
y  x       1       2
Index([1, ('c', 'g'), ('d', 'h')], dtype='object')

or

print(pd.read_csv(io.StringIO(s), header=[0, 1], index_col=[1, 2]))

gives

     1  (d, h)
y 1  x       2
Index([1, ('d', 'h')], dtype='object')

Problem description

I'd expect in the first example the output to match what happens with index_col = 0:

df = pd.read_csv(io.StringIO(s), header=[0, 1], index_col=0)
print(df)
print(df.columns)

a  b  c  d
e  f  g  h
x  y  1  2
MultiIndex([('b', 'f'),
            ('c', 'g'),
            ('d', 'h')],
           names=['a', 'e'])

Expected Output

So I'd expect

b  a  c  d
f  e  g  h
y  x  1  2
MultiIndex([('a', 'e'),
            ('c', 'g'),
            ('d', 'h')],
           names=['b', 'f'])

The other possibility is that maybe this behavior just shouldn't be supported and if multiple headers lines are specified, passing an index_col != range(n) just raises.

Output of pd.show_versions()

INSTALLED VERSIONS

commit : d4b6233
python : 3.8.6.final.0
python-bits : 64
OS : Darwin
OS-release : 19.6.0
Version : Darwin Kernel Version 19.6.0: Thu Oct 29 22:56:45 PDT 2020; root:xnu-6153.141.2.2~1/RELEASE_X86_64
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.UTF-8

pandas : 1.3.0.dev0+90.gd4b623361.dirty
numpy : 1.19.4
pytz : 2020.4
dateutil : 2.8.1
pip : 20.3.1
setuptools : 49.6.0.post20201009
Cython : 0.29.21
pytest : 6.2.0
hypothesis : 5.43.3
sphinx : 3.3.1
blosc : None
feather : None
xlsxwriter : 1.3.7
lxml.etree : 4.6.2
html5lib : 1.1
pymysql : None
psycopg2 : None
jinja2 : 2.11.2
IPython : 7.19.0
pandas_datareader: None
bs4 : 4.9.3
bottleneck : 1.3.2
fsspec : 0.8.4
fastparquet : 0.4.1
gcsfs : 0.7.1
matplotlib : 3.3.3
numexpr : 2.7.1
odfpy : None
openpyxl : 3.0.5
pandas_gbq : None
pyarrow : 2.0.0
pyxlsb : None
s3fs : 0.4.2
scipy : 1.5.3
sqlalchemy : 1.3.20
tables : 3.6.1
tabulate : 0.8.7
xarray : 0.16.2
xlrd : 1.2.0
xlwt : 1.3.0
numba : 0.52.0

[mzeitlin11]

This issue looks to be caused by index_col being handled differently for multiline headers because of code in

pandas/pandas/io/parsers.py

Lines 1445 to 1447 in 21b57fa

	def _extract_multi_indexer_columns(
	self, header, index_names, col_names, passed_names=False
	):

Modifying this function to follow how index_col is treated in the non-multiline header case fixes these issues, but changes the following behavior:

s = """
a,a
b,b
"""

df = pd.read_csv(io.StringIO(s), header=[0, 1], index_col=0)
print(df.columns)

gives

MultiIndex([('a', 'b.1')],
           names=['a', 'b'])

when current master gives

MultiIndex([('a', 'b')],
           names=['a', 'b'])

The output change is caused by name mangling, just like in master for a single header case:

s = """
a,a
"""

df = pd.read_csv(io.StringIO(s), header=0, index_col=0)
print(df.columns)

gives

Index(['a.1'], dtype='object')

The behavior of the first example where names in

s = """
a,a
b,b
"""

df = pd.read_csv(io.StringIO(s), header=[0, 1], index_col=0)

are not mangled is only tested in a single test (but it does not look to be the purpose of the test):

tests/io/excel/test_readers.py::TestExcelFileRead::test_read_datetime_multiindex

Does this behavior change make sense?

[phofl]

I would say the output on master for MultiIndex is correct, while

Index(['a.1'], dtype='object')

seems fishy. Would expect a here.