Inconsistent date parsing of to_datetime
State: closed

- [x] xref #12585
- [x] tests added / passed
- [ ] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing.html#code-standards) for how to run them
- [ ] whatsnew entry

carries on from https://github.com/pandas-dev/pandas/pull/35428

Labels:
name: Datetime
description: Datetime data dtype
name: IO CSV
description: read_csv, to_csv

Comments:
@MarcoGorelli this looks neat. prob need to troll all of the issues and add every test you can find :->
Sure, added, here's the docstring:

![image](https://user-images.githubusercontent.com/33491632/128625774-b9f8816c-323d-4be6-8a18-dfccddeb3f49.png)

Unfortunately, this doesn't solve the case of datetime strings, just date strings. E.g.:
```python
In [2]: pd.to_datetime(['31-12-2021'])
<ipython-input-2-a6d9926683b2>:1: UserWarning: Parsing '31-12-2021' in DD/MM/YYYY format. Provide format or specify infer_datetime_format=True for consistent parsing.
  pd.to_datetime(['31-12-2021'])
Out[2]: DatetimeIndex(['2021-12-31'], dtype='datetime64[ns]', freq=None)

In [3]: pd.to_datetime(['31-12-2021 12:00:31'])
Out[3]: DatetimeIndex(['2021-12-31 12:00:31'], dtype='datetime64[ns]', freq=None)
```
because those cases go straight to dateutil:

https://github.com/pandas-dev/pandas/blob/08d296f1278e08b407448c95086589e1d10285f9/pandas/_libs/tslibs/parsing.pyx#L238-L244

and this PR only addresses what reaches `_parse_delimited_date`.

> Just needs a whatsnew note for 1.4 (probably good to have a dedicated section demoing this behavior),

As in, in the "notable bug fixes" section? If so, I've added it there, here's how it looks:

![image](https://user-images.githubusercontent.com/33491632/128626108-339ab67c-bd0e-47b1-be9f-86c0b12a995e.png)

https://github.com/pandas-dev/pandas/blob/08d296f1278e08b407448c95086589e1d10285f9/pandas/_libs/tslibs/parsing.pyx#L238-L244

yeah would be nice to warn in this as well 
> yeah would be nice to warn in this as well

Agreed, just not sure how to do it - all I can think of is

```python
        dt = du_parse(date_string, default=_DEFAULT_DATETIME,
                      dayfirst=dayfirst, yearfirst=yearfirst, **kwargs)
        if dayfirst and not re.search(rf'(?<!\d){dt.day}(?!\d)', date_string).start() < re.search(rf'(?<!\d){dt.month}(?!\d)', date_string).start():
            warnings.warn(
                PARSING_WARNING_MSG.format(
                    date_string=date_string,
                    format='MM/DD/YYYY'
                ),
                stacklevel=4,
            )
        elif not dayfirst and re.search(rf'(?<!\d){dt.day}(?!\d)', date_string).start() < re.search(rf'(?<!\d){dt.month}(?!\d)', date_string).start():
            warnings.warn(
                PARSING_WARNING_MSG.format(
                    date_string=date_string,
                    format='DD/MM/YYYY'
                ),
                stacklevel=4,
            )
```
would that be OK?

As in, parse `date_string`, and check for the first occurrence of the parsed day and month properties

EDIT
----

The above wouldn't work, as it's possible to specify a date as both `01` and `1` in the date string.

----

So, I can't think (at the moment) how to do this - any suggestions?
well the bigger problem here is that we *really* should warn anytime this is called `du_parse` (if we are not guessing a format) and actually parsing, e.g. this is the fallback case.

I am happy to merge this and to do followups, but ideally want to warn if at all possible that something is amiss (even with false positives).
does this show any warnings during csv parsing? (i think it should in the same cases). ideally create a followon issue with checkboxes for the cases.
@MarcoGorelli looks pretty good if you can rebase & address comments.
> does this show any warnings during csv parsing? (i think it should in the same cases).

yup, I've added the same suite of tests for that

> well the bigger problem here is that we really should warn anytime this is called du_parse (if we are not guessing a format) and actually parsing, e.g. this is the fallback case.

I gave that a go but it's a lot more complicated and there's a ton more warnings to catch. I've pushed that work to a branch (MarcoGorelli:warn-on-du-parse), but for now I've kept this PR to just the delimited date case
Thanks @MarcoGorelli. Happy to have a follow up with warnings around `du_parse`
@MarcoGorelli did this also close #43164 , where the `pandas.options` seem ignored in this regard?
I just tried that and got the same output you got (that say, I haven't looked into `pandas.options` much and don't know what that option's meant to do, I hadn't seen it before)