BUG: read_csv with engine pyarrow doesn't handle missing values
State: closed

- [x] closes #47950 (Replace xxxx with the Github issue number)
- [x] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [x] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [x] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [ ] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.


Labels:
name: IO CSV
description: read_csv, to_csv
name: Arrow
description: pyarrow functionality

Comments:
@mroeschke I'm not really familiar with pyarrow, any thoughts on this solution? Tagging you as I've seen you in lot's of arrow-related PRs

---


PS. I'm aware I still need a whatsnew note, will add that later
Is pyarrow reading this as a date or something else? 



Currently on main, `None` gets read as `'None'`, and the whole column gets parsed as `object`. By keeping it as `None`, it gets parsed as `NaT`. Will update with the code paths it goes down later
Not super familiar with our implementation, but I wonder if we should be passing along the `parse_dates` fields to the underlying pyarrow implementation. Something like this

```python
>>> import pyarrow as pa
>>> import io
>>> data = """idx,date
... 2,2000-01-01
... ,
... """
>>> pa.csv.read_csv(io.BytesIO(data.encode()), convert_options=csv.ConvertOptions(column_types={"date": pa.timestamp(unit="ns")}))
pyarrow.Table
idx: int64
date: timestamp[ns]
----
idx: [[2,null]]
date: [[2000-01-01 00:00:00.000000000,null]]
```

Might simplify code paths compared to reading in as an object and converting on our end
Here's what currently happens:

in 

https://github.com/pandas-dev/pandas/blob/7214f857e45965fe7a0cfbba1d2dc35abb0fd7f4/pandas/io/parsers/base_parser.py#L1061

, `date_cols` is `(array([datetime.date(2000, 1, 1), None], dtype=object),)`.

This gets converted to `array(['2000-01-01', 'None'], dtype=object)`, which can't be converted to a date.

In this branch, on the other hand, it gets converted to `array(['2000-01-01', None], dtype=object)`. So then, when we get to

https://github.com/pandas-dev/pandas/blob/7214f857e45965fe7a0cfbba1d2dc35abb0fd7f4/pandas/core/tools/datetimes.py#L434-L442

which goes to

https://github.com/pandas-dev/pandas/blob/7214f857e45965fe7a0cfbba1d2dc35abb0fd7f4/pandas/core/arrays/datetimes.py#L2159-L2167

which converts `array(['2000-01-01', None], dtype=object)` to `(array(['2000-01-01T00:00:00.000000000',                           'NaT'], dtype='datetime64[ns]'), None)`
I was thinking more that we would pass the `parse_dates` options to `convert_options` here:

https://github.com/pandas-dev/pandas/blob/7214f857e45965fe7a0cfbba1d2dc35abb0fd7f4/pandas/io/parsers/arrow_parser_wrapper.py#L147

Which might have that off to arrow and reduce the layers / complexity we have in the wrapper?
Thanks @WillAyd for weighing in - indeed, with that option, then `table.to_pandas()` would have `NaT` rather than `None`.

However, we run into another issue: when we get to

https://github.com/pandas-dev/pandas/blob/85738b3106fe06fcca0cb5f56f9a689f1f3cd1b7/pandas/_libs/tslibs/parsing.pyx#L1155-L1160

then `array` is `array ['2000-01-01T00:00:00.000000000'                           'NaT']`, and the first `item` is `946684800000000000`.

But then, we get to

https://github.com/pandas-dev/pandas/blob/85738b3106fe06fcca0cb5f56f9a689f1f3cd1b7/pandas/_libs/tslibs/parsing.pyx#L1096

which throws

```
OverflowError: value too large to convert to int
```

So like this, it's the first element (`2000-01-01T00:00:00.000000000`) that ends up throwing an error, rather than the second (missing) one
Do you get that while also commenting out line 111 in that module? 
> Do you get that while also commenting out line 111 in that module?

Ah gotcha, thanks!

Yes, that works, but then
```
data = """idx,date
2,19990127 19:00:00
"""

df = pd.read_csv(io.StringIO(data), parse_dates=['date'], engine='pyarrow')
```
fails:
```
  File "/home/megorelli/pandas-dev/pandas/io/parsers/arrow_parser_wrapper.py", line 148, in read
    table = pyarrow_csv.read_csv(
  File "pyarrow/_csv.pyx", line 1217, in pyarrow._csv.read_csv
  File "pyarrow/_csv.pyx", line 1226, in pyarrow._csv.read_csv
  File "pyarrow/error.pxi", line 144, in pyarrow.lib.pyarrow_internal_check_status
  File "pyarrow/error.pxi", line 100, in pyarrow.lib.check_status
pyarrow.lib.ArrowInvalid: In CSV column #1: CSV conversion error to timestamp[ns]: invalid value '19990127 19:00:00'
```

, whereas on this branch it would output:
```
   idx                date
0    2 1999-01-27 19:00:00
```
Agreed with @WillAyd general suggestion to dispatch this to Arrow's read_csv as much as possible.

With the above error, might be worth reporting to the Arrow Jira board, but I can see how this is one of those tricky parsing situations that Arrow does not (may not) support.
Agreed with @mroeschke   I think Arrow should handle the date conversions if the arrow engine is chosen. If that doesn’t work for users they can use another engine, but picking and choosing read_csv arguments to dispatch across engines is pretty confusing
Thanks for your comments

I may have misunderstood, but are you suggesting to accept this error? Wouldn't that introduce a regression? Because currently, with the latest pandas release,
```
import pandas as pd
import io

data = """idx,date
2,19990127 19:00:00
"""

df = pd.read_csv(io.StringIO(data), parse_dates=['date'], engine='pyarrow')

print(pd.read_csv(io.StringIO(data), parse_dates=['date'], engine='pyarrow'))
```
works fine, it outputs
```
idx                date
0    2 1999-01-27 19:00:00
```
Yea I think that behavior should be deprecated if it’s not the arrow engine doing the date parsing
sounds good to me - I've opened https://github.com/pandas-dev/pandas/issues/48017 - closing this PR then