Inconsistent date parsing of to_datetime

[jorisvandenbossche]

As this comes up regularly:

• BUG: Unexpected pd.to_datetime result #12583
• pd.to_datetime/pd.DatetimeIndex unexpected behavior #12501
• Bug: inconsistent date parsing by to_datetime #7348
• Determine datetime_format from more than one element #15075
• pandas.io.tests.test_parsers.Test*.test_yy_format fails in 0.18.0 #12611 deal with yearfirst/datefirst in read_csv
• COMPAT: dateutil 2.5.2 broke sometests #12730, compat with dateutil 2.5.2 dayfirst=True, yearfirst=True fixes
• Greedy date inference in read_csv leads to inconsistent data #14301 raise if inconsistency in read_csv with dayfirst/yearfirst
• performance read_csv : using day first 23x to 35x slower than setting the format explicitly #25848
  The problem

Depending on the string formats, you can get inconsistent parsing (meaning: different interpretation of parts of the strings as days vs months between different values in the passed list/array of strings).
For example:

In [22]: pd.to_datetime(["31/12/2014", "10/03/2011"])
Out[22]: DatetimeIndex(['2014-12-31', '2011-10-03'])

where the first is parsed as 'dayfirst' and the second not. Above particular case can be solved by passing dayfirst=True, but in any case, the default behaviour of to_datetime results in inconsistent parsing here.
Another typical case if where you do provide dayfirst=True but have a malformed date, then it will fall back to 'not dayfirst' (#3341).

The reason

The reason is the flexibility of dateutil.parser.parse. This is in the first place also a feature, as it lets you parse messy datetime colums. But when it comes to flexibility regarding dayfirst/yearfirst, this gives unexpected results.
So dateutil will 'try' to use dayfirst=True, but when this does not give a valid date, it will ignore dayfirst=True and parse the string anyway:

In [3]: dateutil.parser.parse('1/12/2012', dayfirst=True)
Out[3]: datetime.datetime(2012, 12, 1, 0, 0)

In [4]: dateutil.parser.parse('1/13/2012', dayfirst=True)
Out[4]: datetime.datetime(2012, 1, 13, 0, 0)

The same is true for dayfirst=False (the default) as this will also try with 'day first' if the other way around failed.
The issue for the lack of strictness of dayfirst is actually #3341.

Possible solutions

• In some cases passing dayfirst=True is sufficient (if you don't have messy or malformed dates).
• The typical answer is saying: "if you want to be sure to have strict and consistent parsing, provide a format argument.
  We should probably stress this more in the docstring of to_datetime

But, regardless of the two points above, fact is that the default behaviour of to_datetime (without passing any arguments) can do inconsistent parsing and so give unexpected results.
Possible ways to have better default behaviour:

• Have the possibility to specify that dateutil should be strict about dayfirst. I raised an issue for that here: Possibility to have a strict dayfirst dateutil/dateutil#214
• Changing the default of infer_datetime_format to True would solve it in some cases (where the first datetime has the correct format you want), but certainly not all.
• The idea of raising a warning when inconsistent parsing happened, is AFAIK not possible as pandas does not know how dateutil parsed the dates. And always warning when no format is passed or could be inferred (so in practice, when dateutil is used), is maybe to drastically, as the flexible parsing is also a feature.

[jreback]

So we ONLY hit the dateutil path (and hence use dayfirst/yearfirst settings) IF we fail to parse ISO8601 (IOW an exception is raised).

So easiest thing to do is track in array_to_datetime when/if this happens.

• If we don't trigger the exception at all, then all dates are consistent (as ISO 8601, meaning dayfirst=False, yearfirst=False).
• If we have at least 1 time we hit the exception (but not all), then we have a potential ambiguity (meaning
  some dates were parsed as ISO 8601 and some maybe were not)
• If we hit the exception EVERY time, then we have NO ISO8601 dates and I think the dayfirst/yearfirst flags would make this unambiguous (somewhat depends on dateutil).

So this is a relatively easy first step, we could easily raise an ambiguity error and force the user to figure it out (unless coerceing of course).

We could also (and maybe wait for an ambiguity error to occur), try several formats to see what parses.

These options take into account all dates, to make this more performance we might want to take a sample (or a sample of the uniques is best).

[dolaameng]

The dayfirst parameter to the reading functions such as read_csv is even more confusing, as multiple datetime columns with different formats might co-exist in the same dataset.