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.

[lordgrenville]

Since mixed date formats within one series is unusual, why not raise a warning every time there are ambiguous values?

[jorisvandenbossche]

Since mixed date formats within one series is unusual, why not raise a warning every time there are ambiguous values?

I am not sure there is way to get from dateutil how (assuming which format) it parsed the date, so then it will also be difficult to warn for different formats.

[erpda]

I am new to Pandas and github - please be gentle.

If I understand the thread correctly - this is an unaddressed issue for 3.5 years? Don't you think that explicitly coding how a date should be read is an unreasonable solution? It's like importing integers and coding "randomly_scramble_my_number=False". This error is enough to ban use of Pandas in some large organisations :(

[jreback]

I am new to Pandas and github - please be gentle.

If I understand the thread correctly - this is an unaddressed issue for 3.5 years? Don't you think that explicitly coding how a date should be read is an unreasonable solution? It's like importing integers and coding "randomly_scramble_my_number=False". This error is enough to ban use of Pandas in some large organisations :(

@erpda

There are 3000+ open issues in pandas. How should the handful of mostly volunteer folks prioritize? if large orgs want to just take and not help fix then I am not sure what to say.

open source is about finding, reporting, AND helping to fix. there are a many orgs and many many individual contributors to pandas and open source generally that have built an amazing eco system.

Would love for large orgs to step up and seriously fund open source. just taking and not giving back at all IMHO is terrible.

[ahmetanildindar]

Dear all,

It seems to_datetime is causing significant problem.
Here are the codes I've ran. When compare the EARTHQUAKEDATE fields of the original and copied and to_datetime applied dataframes, I see the values are unfortunately different.

Original is in DD/MM/YYYY hh:mm:ss format
EARTHQUAKEDATE 05/10/1977 05:34:43

But modified is in YYYY/DD/MM hh:mm:ss format
EARTHQUAKEDATE 1977-05-10 05:34:43

I'd would expect the result as YYYY-MM-DD hh:mm:ss.

I'd like to do anything I can for the solution.

[Craskermasker]

I've stumbled across this issue in working with our scientific dataset. Specifying the dayfirst argument to read_csv sorted us out. I would agree with the suggestion of printing a warning when inconsistent datetime parsing may be occurring. Can this be done after parsing by checking for inconsistent mapping from the string input to the datetime output across the whole series? Would this introduce a performance penalty?

I'm keen to contribute a fix but I'm a newbie to the pandas project.

[jreback]

@Craskermasker happy to have a contribution towards this

[paalge]

I barely caught this error when trying to process a datasett, and I feel that it should be addressed.
My first thought was to make dayfirst True as default, but this does not work as if you input 10.20.2020 it converts it to 2020-10-20 without throwing an error 😮.

The only solution I see for solving this is to force the use of format, including making it a mandatory argument when using parse_dates in read_csv/read_tsv

In read_csv there should be a check that all dates are converted using the same format.From digging through the source code I would expect it to be this needs to be implemented in

pandas/pandas/_libs/tslibs/parsing.pyx

Line 591 in 2990c1d

def try_parse_date_and_time(

In the mean time I have reverted to using numpy.genfromtxt

[MarcoGorelli]

Just to give an update on this, the example from this issue will now show a warning:

In [1]: import pandas as pd

In [2]: pd.to_datetime(["31/12/2014", "10/03/2011"])
<ipython-input-2-9e192223af3c>:1: UserWarning: Parsing '31/12/2014' in DD/MM/YYYY format. Provide format or specify infer_datetime_format=True for consistent parsing.
  pd.to_datetime(["31/12/2014", "10/03/2011"])
Out[2]: DatetimeIndex(['2014-12-31', '2011-10-03'], dtype='datetime64[ns]', freq=None)

What's missing is to show a warning when the date string isn't a delimited date string (e.g. "31/12/2014 00:00:00") - this would involve adding a warning to when du_parse is called, and would result in many false positives, but maybe that's OK

[matthew-brett]

Just to say, for others on this thread- the warning happens for all date-time conversions, not just for those with the problem in question - where the parser changes its mind mid-conversion about what the days and months are.

In [4]: pd.to_datetime(["31/12/2014"])
<ipython-input-4-9e280f89aa98>:1: UserWarning: Parsing '31/12/2014' in DD/MM/YYYY format. Provide format or specify infer_datetime_format=True for consistent parsing.
  pd.to_datetime(["31/12/2014"])

This is helpful - thank you - because it reminds users to specify the format or add the keyword argument, for all date-time parsing. But, because it is not specific to the change-mind case, and appears for all date-time parsing, I suspect a lot of users will get used to ignoring the warning, and will not realize what has happened in the mid-parsing switch.

[matthew-brett]

Just another tiny example to complement the original:

[ins] In [5]: pd.to_datetime(['01-06-20', '13-06-20', '01-07-20', '13-07-20'])                                                         
Out[5]: DatetimeIndex(['2020-01-06', '2020-06-13', '2020-01-07', '2020-07-13'], dtype='datetime64[ns]', freq=None)

Because the parser does not have a rule to keep it consistent, it thinks that the month comes before the day for the first and third dates, and the day comes before the month for the second and fourth.

[MarcoGorelli]

From the latest dev meeting, it was agreed that it would be good to aim for pd.to_datetime to be strict and to not allow mixed formats. Question is - how to do this?

Background: pandas uses its own parsing when the date string format is standard (e.g. yyyy-mm-dd), and defers to dateutil when pandas can't guess. dateutil is extremely flexible, though isn't strict, and it doesn't return the format it guessed (dateutil/dateutil#1138).

When parsing dates using pandas' own parsers, pandas could already be strict - however, that'd be inconsistent with the non-strict parsing by dateutil, and users wouldn't know which one was being used nor what to expect. A couple of solutions could then be:

1. strict pandas parsers, emit UserWarning if dateutil is used, OR
2. strict pandas parsers, error if pandas parser can't guess format - remove dateutil

Approach 1: I looked into this in #47828. However, there are parts of the code where dateutil is hit as part of a try-expect block, where the warning would be a false-positive. Example:

https://github.com/pandas-dev/pandas/blob/main/pandas/_libs/tslibs/period.pyx#L2581-L2588

Either this code would need refactoring, or the warning could be filtered in the try block and only emitted if the else block is reached. I'd be -1 on this

Approach 2: I'm leaning towards this at the moment. If users have non-standard dates they need to parse, they could use dateutil themselves and accept the guesswork, or explicitly pass format=

---

EDIT: there's a 3rd approach, which I've outlined here, which would obliviate the need to remove dateutil or to emit a warning from it

[MarcoGorelli]

I've put together a PDEP for this: #48621

In addition to the core and triage teams, I'd encourage others who have commented here (@lordgrenville, @garfieldthecat, @paalge, @Craskermasker, @ahmetanildindar , @matthew-brett) to have a look - if you have concerns about this proposal, I'd appreciate your input