raise NotImplemented for date parsing args in read_excel #11544
State: closed

Fixes #11544

The `parse_dates` and `date_parser` args are passed to `TextReader` and then to `TextFileReader` where they don't seem to have an effect. It was decided to raise the exception at the `_parse_excel` level however, following suit with the handling of `chunksize` args.


Labels:
name: Error Reporting
description: Incorrect or improved errors from pandas
name: IO Excel
description: read_excel, to_excel

Comments:
lgtm. minor comments. ping when updated & green.

Good to go. I also updated the `NotImplemented` message for the `chunksize` keyword.

thanks!

AFAIK, the `parse_dates` keyword for `read_excel` _does_ work. 
It only works a bit in unexpected ways sometimes, as it is a keyword to specify whether to parse _strings_ to a datetime. And most of the time, if you have dates in an excel file, the column is formatted as dates, the engine knows this and the column will be of datetime dtype regardless of what you specify with `parse_dates`. Hence the confusion. 
But if you have a column that is formatted as text in excel, `parse_dates` works perfectly to parse these strings into a datetime64 column.

So I think the more correct fix is to clarify the documentation. And maybe also warn on `parse_dates=True` when you didn't specify `index_col`

cc @TomAugspurger I just saw you giving that comment on SO: http://stackoverflow.com/questions/34403682/why-does-pandas-parse-dates-when-parse-dates-false

As a small example it actually does something:

```
In [43]: dti = pd.date_range('2014-1-1', periods=10)

In [44]: df = pd.DataFrame({'dates':dti, 'strings':pd.Series(dti).astype(str)})

In [46]: df.to_excel('test.xlsx', index=False)

In [47]: pd.read_excel('test.xlsx').dtypes
Out[47]:
dates      datetime64[ns]
strings            object
dtype: object

In [48]: pd.read_excel('test.xlsx', parse_dates=['strings']).dtypes
Out[48]:
dates      datetime64[ns]
strings    datetime64[ns]
dtype: object
```

Thanks, I misunderstood how read_excel handled parse_dates. 

> On Dec 22, 2015, at 5:14 AM, Joris Van den Bossche notifications@github.com wrote:
> 
> AFAIK, the parse_dates keyword for read_excel does work. 
> It only works a bit in unexpected ways sometimes, as it is a keyword to specify whether to parse strings to a datetime. And most of the time, if you have dates in an excel file, the column is formatted as dates, the engine knows this and the column will be of datetime dtype regardless of what you specify with parse_dates. Hence the confusion. 
> But if you have a column that is formatted as text in excel, parse_dates works perfectly to parse these strings into a datetime64 column.
> 
> So I think the more correct fix is to clarify the documentation. And maybe also warn on parse_dates=True when you didn't specify index_col
> 
> cc @TomAugspurger I just saw you giving that comment on SO: http://stackoverflow.com/questions/34403682/why-does-pandas-parse-dates-when-parse-dates-false
> 
> —
> Reply to this email directly or view it on GitHub.

Thanks for the clarification. I'll submit another PR with doc fixes and a warning on `parse_dates=True` and no `index_col`. 

See PR #12051