option dtype in pandas.read_csv does not work properly for mulilevel columns

[jottbele]

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

---

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

Code Sample, a copy-pastable example

import pandas as pd
df= pd.DataFrame({
('A', 'int16'): pd.Series([1, 2, 3, 4], dtype='int16'),
('A', 'int32'): pd.Series([132, 232, 332, 432], dtype='int32'),
('B', 'float32'): pd.Series([1.01, 1.02, 1.03, 1.04], dtype='float32'),
('B', 'int16'): pd.Series([21, 22, 23, 24], dtype='int16')})
print(df)
df.to_csv('test_df.csv')
print(df.dtypes)

full column name tuples with level 0/1 labels don't work

df_new= pd.read_csv(
'test_df.csv',
header=list(range(2)),
dtype = {
('A', 'int16'): 'int16',
('A', 'int32'): 'int32'
})
print(df_new.dtypes)

See my SO article for more detailed infos:
https://stackoverflow.com/questions/54699527/dtype-is-ignored-when-using-multilevel-columns

Problem description

Although the data types where passed in read_csv, they are not applied. dtype in read_csv only seems to work for column names that contain only one level. For Multilevel columns it generally does not seem to work.

Expected Output

Unnamed: 0_level_0 Unnamed: 0_level_1 int64
A int16 int16
int32 int32
B float32 float64
int16 int64

Output of pd.show_versions()

INSTALLED VERSIONS

commit : 7d32926
python : 3.7.9.final.0
python-bits : 64
OS : Linux
OS-release : 5.4.0-77-generic
Version : #86-Ubuntu SMP Thu Jun 17 02:35:03 UTC 2021
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : de_DE.UTF-8
LOCALE : de_DE.UTF-8

pandas : 1.2.2
numpy : 1.20.1
pytz : 2021.1
dateutil : 2.8.1
pip : 21.0.1
setuptools : 52.0.0.post20210125
Cython : 0.29.23
pytest : 6.2.2
hypothesis : None
sphinx : 4.0.1
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.5.0
html5lib : 1.1
pymysql : None
psycopg2 : 2.8.6 (dt dec pq3 ext lo64)
jinja2 : 2.11.3
IPython : 7.22.0
pandas_datareader: None
bs4 : 4.9.3
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : 3.3.4
numexpr : 2.7.3
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 3.0.0
pyxlsb : None
s3fs : None
scipy : 1.6.2
sqlalchemy : None
tables : 3.6.1
tabulate : None
xarray : None
xlrd : None
xlwt : None
numba : 0.51.2

[rhshadrach]

Thanks for the report - can you add an informative title for this issue.

[jottbele]

Btw. if you pass the string of the very first level of the column names, you can set the datatype, but that datatype would than be applied to all columns with the same name on level 1.
You can veryfy that with the following code (it's from the SO article linked above):

df_new2= pd.read_csv(
'test_df.csv',
header=list(range(2)),
dtype={
'A':'object',
'B': 'float32'
})
print(df_new2.dtypes)

[jottbele]

Just another observation. with engine='python' this seems to work. So it seems, there only is a problem in the c-engine.

[rhshadrach]

Confirmed on master and 1.2.x, investigations and PRs to fix are welcome.

[jmcomie]

take

[jmcomie]

Candidate fix is an update to pandas/_libs/parsers.pyx:cdef class TextReader._get_column_name to make it multi-index aware. The result of this function is used to do the dtype lookup but at present, in the code lit by our test case, it returns only the first header row value.

[jmcomie]

Draft fix below. I'll look for corner cases and applicable helper functions, write test code, then submit the PR.

diff --git a/pandas/_libs/parsers.pyx b/pandas/_libs/parsers.pyx
index e5e61e409c..efa4678579 100644
--- a/pandas/_libs/parsers.pyx
+++ b/pandas/_libs/parsers.pyx
@@ -1281,7 +1281,10 @@ cdef class TextReader:
                 if j >= len(self.header[0]):
                     return j
                 else:
-                    return self.header[0][j]
+                    if len(self.header) == 1:
+                        return self.header[0][j]
+                    else:
+                        return tuple(header_row[j] for header_row in self.header)
             else:
                 return None