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).

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.

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

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.

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 :(

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.

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.

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.

@Craskermasker happy to have a contribution towards this

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

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

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

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.

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.

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

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