BUG: read_csv with index_col option on pyarrow dtype cause error.

[ProFatXuanAll]

Pandas version checks

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

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

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

Suppose that we have a file call test.csv, which contains the following lines:

col1,col2
a,1
b,2

When calling the following lines, we get error:

import pandas as pd
import pyarrow as pa

df = pd.read_csv(
  'test.csv',
  dtype={
    'col1': pd.ArrowDtype(pa.string()),  # this cause problem
    'col2': pd.ArrowDtype(pa.uint8()),   # this cause problem
  },
  engine='pyarrow',
  dtype_backend='pyarrow',
  index_col='col1',               # without this is okay
)

Error message:

KeyError: "Only a column name can be used for the key in a dtype mappings argument. 'col1' not found in columns."

However, when using pandas built-in dtypes is okay.

import pandas as pd
import pyarrow as pa

df = pd.read_csv(
  'test.csv',
  dtype={
    'col1': 'string',  # this is okay
    'col2': 'uint8',   # this is okay
  },
  engine='pyarrow',
  dtype_backend='pyarrow',
  index_col='col1',
)

Issue Description

A weird scenario where read_csv failed with mysterious error.
When passing dtype option with pyarrow dtypes along with index_col option, we get a mysterious KeyError.
But replacing pyarrow dtypes with pandas bulit-in dtype is okay.

Expected Behavior

No error should happen.
In particular, index_col should be able to work with pyarrow dtypes.

Installed Versions

INSTALLED VERSIONS

commit : 37ea63d
python : 3.9.16.final.0
python-bits : 64
OS : Darwin
OS-release : 22.3.0
Version : Darwin Kernel Version 22.3.0: Mon Jan 30 20:39:35 PST 2023; root:xnu-8792.81.3~2/RELEASE_ARM64_T8103
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.0.1
numpy : 1.23.5
pytz : 2023.3
dateutil : 2.8.2
setuptools : 67.7.2
pip : None
Cython : 0.29.34
pytest : 7.3.1
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : 3.1.0
lxml.etree : 4.9.2
html5lib : 1.1
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : 8.13.1
pandas_datareader: None
bs4 : 4.12.2
bottleneck : 1.3.7
brotli :
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : 3.7.1
numba : 0.56.4
numexpr : 2.8.4
odfpy : None
openpyxl : 3.1.2
pandas_gbq : None
pyarrow : 12.0.0
pyreadstat : 1.2.1
pyxlsb : 1.0.10
s3fs : None
scipy : 1.9.3
snappy :
sqlalchemy : None
tables : 3.8.0
tabulate : None
xarray : 2023.4.2
xlrd : 2.0.1
zstandard : 0.21.0
tzdata : 2023.3
qtpy : None
pyqt5 : None

[lithomas1]

Hi, thanks for the report. I can reproduce this on main.

I turned your example into a copy-pastable one. I get an error even if using pandas types however.

from io import StringIO
test="""
col1,col2
a,1
b,2
"""
f=StringIO(test)
import pandas as pd
import pyarrow as pa
df = pd.read_csv(
  f,
  dtype={
    'col1': 'string',  # this is okay
    'col2': 'uint8',   # this is okay
  },
  engine='pyarrow',
  dtype_backend='pyarrow',
  index_col='col1',
)

The traceback is

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/thomasli/pandas/pandas/io/parsers/readers.py", line 935, in read_csv
    return _read(filepath_or_buffer, kwds)
  File "/Users/thomasli/pandas/pandas/io/parsers/readers.py", line 606, in _read
    return parser.read(nrows)
  File "/Users/thomasli/pandas/pandas/io/parsers/readers.py", line 1718, in read
    df = self._engine.read()  # type: ignore[attr-defined]
  File "/Users/thomasli/pandas/pandas/io/parsers/arrow_parser_wrapper.py", line 189, in read
    return self._finalize_pandas_output(frame)
  File "/Users/thomasli/pandas/pandas/io/parsers/arrow_parser_wrapper.py", line 136, in _finalize_pandas_output
    frame = frame.astype(self.kwds.get("dtype"))
  File "/Users/thomasli/pandas/pandas/core/generic.py", line 6400, in astype
    raise KeyError(
KeyError: "Only a column name can be used for the key in a dtype mappings argument. 'col1' not found in columns."

which should be the same as you.

I think the issue is that we are applying the dtype conversion too late in the process (after the index column has been dropped and moved to the index).

I'll try to have a look this week.

[ProFatXuanAll]

FYI, the same error happens when using usecols option instead of index_col.

from io import StringIO

import pandas as pd

test = '''
col1,col2,col3
a,1,x
b,2,y
'''
f = StringIO(test)

df = pd.read_csv(
  f,
  dtype={
    'col1': 'string',
    'col2': 'uint8',
    'col3': 'string',
  },
  engine='pyarrow',  # These cause problems.
  usecols=['col1', 'col2'],
)

The traceback is

Traceback (most recent call last):
  File "/Users/username/project/mwe.py", line 12, in <module>
    df = pd.read_csv(
  File "/Users/username/project/.venv/lib/python3.9/site-packages/pandas/io/parsers/readers.py", line 912, in read_csv
    return _read(filepath_or_buffer, kwds)
  File "/Users/username/project/.venv/lib/python3.9/site-packages/pandas/io/parsers/readers.py", line 583, in _read
    return parser.read(nrows)
  File "/Users/username/project/.venv/lib/python3.9/site-packages/pandas/io/parsers/readers.py", line 1692, in read
    df = self._engine.read()  # type: ignore[attr-defined]
  File "/Users/username/project/.venv/lib/python3.9/site-packages/pandas/io/parsers/arrow_parser_wrapper.py", line 164, in read
    return self._finalize_pandas_output(frame)
  File "/Users/username/project/.venv/lib/python3.9/site-packages/pandas/io/parsers/arrow_parser_wrapper.py", line 132, in _finalize_pandas_output
    frame = frame.astype(self.kwds.get("dtype"))
  File "/Users/username/project/.venv/lib/python3.9/site-packages/pandas/core/generic.py", line 6290, in astype
    raise KeyError(
KeyError: "Only a column name can be used for the key in a dtype mappings argument. 'col3' not found in columns."

As the above MWE suggested, it seems to be the line engine='pyarrow' causing the error.
By removing the line the code works fine.

from io import StringIO

import pandas as pd

test = '''
col1,col2,col3
a,1,x
b,2,y
'''
f = StringIO(test)

df = pd.read_csv(
  f,
  dtype={
    'col1': 'string',
    'col2': 'uint8',
    'col3': 'string',
  },
  # engine='pyarrow',  # These cause problems.
  usecols=['col1', 'col2'],
)

[kanaryayi]

I have the same issue with usecols+pyarrow which I reported here on Stackoverflow. Looking forward for the merge

https://stackoverflow.com/questions/76348125/how-can-i-filter-data-from-a-large-txt-file-with-pyarrow-engine-and-usecols-para

[dss010101]

Does this also fix the issue where when reading using pyarrow engine..the dtype arguement is ignore. for example it would read 20200101 float even if i pass in a dtype dictionary indicating that column should be read as string