BUG/API: multiple headers and index_col != range(...) in parsers · Issue #38549 · pandas-dev/pandas (original) (raw)

• 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