WIP BUG: Inconsistent date parsing of to_datetime
State: closed

- [x] closes #12585
- [x] tests added / passed
- [ ] documentation updated 
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [ ] whatsnew entry (pending 1.2)

This PR is an attempt to address concerns about datetime parsing. I'll update the docs if people approve this change.

#### Original problem
From #12585:
``` python
In [3]: pd.to_datetime(["31/12/2014", "10/03/2011"]) 
   ...:                                                                                                               
Out[3]: DatetimeIndex(['2014-12-31', '2011-03-10'], dtype='datetime64[ns]', freq=None)
```
The issue is that the first item is parsed as `DD\MM\YYYY` whereas for the second the format is `MM\DD\YYYY`, and there is no message to alert the user of inconsistency in the output.

The problem lies with the `dayfirst` argument that selects between `DD\MM\YYYY` and `MM\DD\YYYY`. In the parser:
https://github.com/pandas-dev/pandas/blob/04e9e0afd476b1b8bed930e47bf60ee20fa78f38/pandas/_libs/tslibs/parsing.pyx#L94-L163
we try  `DD\MM\YYYY` or `MM\DD\YYYY` first depending on `dayfirst` but if the result is invalid we try the other. 

#### Proposed change
I'd like to raise warnings whenever we parse in contradiction to the directive of `dayfirst`. The OP, where `dayfirst` defaults to `False`, now raises a Warning:
``` python
In [4]: pd.to_datetime(["31/12/2014", "10/03/2011"], dayfirst=False) 
   ...:                                                                                                               
/workspaces/pandas-arw2019/pandas/core/arrays/datetimes.py:2044: UserWarning: Parsing 31/12/2014 DD/MM format.
  result, tz_parsed = tslib.array_to_datetime(
Out[4]: DatetimeIndex(['2014-12-31', '2011-10-03'], dtype='datetime64[ns]', freq=None)
```
A downside of this change is that sometimes a consistent output produces unnecessary warnings (due to mismatch with the default value of `dayfirst`):
``` python
In [11]: pd.to_datetime(["31/12/2014", "30/04/2011", "31/05/2016"] )                                                 
/workspaces/pandas-arw2019/pandas/core/arrays/datetimes.py:2044: UserWarning: Parsing 31/12/2014 DD/MM format.
  result, tz_parsed = tslib.array_to_datetime(
/workspaces/pandas-arw2019/pandas/core/arrays/datetimes.py:2044: UserWarning: Parsing 30/04/2011 DD/MM format.
  result, tz_parsed = tslib.array_to_datetime(
/workspaces/pandas-arw2019/pandas/core/arrays/datetimes.py:2044: UserWarning: Parsing 31/05/2016 DD/MM format.
  result, tz_parsed = tslib.array_to_datetime(
Out[11]: DatetimeIndex(['2014-12-31', '2011-04-30', '2016-05-31'], dtype='datetime64[ns]', freq=None)
```
but these can be silenced with `infer_datetime_format`:
``` python
In [12]: pd.to_datetime(["31/12/2014", "30/04/2011", "31/05/2016"], infer_datetime_format=True )                      
Out[12]: DatetimeIndex(['2014-12-31', '2011-04-30', '2016-05-31'], dtype='datetime64[ns]', freq=None)
```
For invalid input (if it cannot be parsed according to a single format) we will *always* raise a warning, even with `infer_datetime_format=True`:
``` python
In [13]: pd.to_datetime(["31/12/2014", "03/31/2011"], infer_datetime_format=True)                                     
/workspaces/pandas-arw2019/pandas/core/arrays/datetimes.py:2044: UserWarning: Parsing 31/12/2014 DD/MM format.
  result, tz_parsed = tslib.array_to_datetime(
Out[13]: DatetimeIndex(['2014-12-31', '2011-03-31'], dtype='datetime64[ns]', freq=None)
```

#### Remaining issues
Personally I can see a case for raising an error with 
``` python
In [13]: pd.to_datetime(["31/12/2014", "03/31/2011"], infer_datetime_format=True)                                     
```
since there's no valid way to process that. I also think that 
``` python
In [11]: pd.to_datetime(["31/12/2014", "30/04/2011", "31/05/2016"])                                                 
```
shouldn't be raising warnings, but I feel much less strongly about that. That said, changing this behaviour will require more heavy-handed alterations to the codebase (and maybe performance implications - not sure).

I think that the warnings I added here will allow users to figure out the origin of these problems when they turn up and that should do away with most of the headaches.

Labels:
name: Bug
name: Datetime
description: Datetime data dtype

Comments:
> Might be preferable to the user if they just get 1 warning along the lines of "Parsed as DD/MM/YYY and as MM/DD/YYY. Provide `format` or specify infer_datetime_format=True for consistent parsing"

Sounds good. I'll keep working on this 
@arw2019 doc build failing

```
>>>-------------------------------------------------------------------------
Warning in /home/runner/work/pandas/pandas/doc/source/user_guide/timeseries.rst at block ending on line 222
Specify :okwarning: as an option in the ipython:: block to suppress this message
----------------------------------------------------------------------------
/home/runner/work/pandas/pandas/pandas/core/arrays/datetimes.py:2044: UserWarning: Parsing '01-14-2012' in MM/DD/YYYY format.
  result, tz_parsed = tslib.array_to_datetime(
<<<-------------------------------------------------------------------------
```
@arw2019 are you still working on this?
@simonjayhawkins yes, will push what I have shortly. Sorry about keeping this idle
Closing this for now. I'll circle back when I'm ready for review.