pandas.to_datetime() does not respect exact format string

[ssche]

Code Sample, a copy-pastable example if possible

import pandas as pd
s = pd.Series(['2012-01-01 10:00', '2012-02-10 23:20', '2012-03-22 12:40', '2012-05-02 02:00', '2012-06-11 15:20', '2012-07-22 04:40','2012-08-31 18:00', '2012-10-11 07:20', '2012-11-20 20:40', '2012-12-31 10:00'])
print s
0    2012-01-01 10:00
1    2012-02-10 23:20
2    2012-03-22 12:40
3    2012-05-02 02:00
4    2012-06-11 15:20
5    2012-07-22 04:40
6    2012-08-31 18:00
7    2012-10-11 07:20
8    2012-11-20 20:40
9    2012-12-31 10:00
dtype: object
print pd.to_datetime(s, format='%Y-%m-%d', errors='raise', infer_datetime_format=False, exact=True)
0   2012-01-01 10:00:00
1   2012-02-10 23:20:00
2   2012-03-22 12:40:00
3   2012-05-02 02:00:00
4   2012-06-11 15:20:00
5   2012-07-22 04:40:00
6   2012-08-31 18:00:00
7   2012-10-11 07:20:00
8   2012-11-20 20:40:00
9   2012-12-31 10:00:00
dtype: datetime64[ns]

Expected Output

A ValueError exception because string '2012-01-01 10:00' (and all others of this series) does not comply with format string '%Y-%m-%d' and exact conversion is required.

output of pd.show_versions()

INSTALLED VERSIONS

commit: None python: 2.7.10.final.0 python-bits: 64 OS: Darwin OS-release: 15.3.0 machine: x86_64 processor: i386 byteorder: little LC_ALL: None

pandas: 0.17.1 nose: 1.3.6 pip: 8.0.3 setuptools: 18.0 Cython: 0.23.4 numpy: 1.10.4 scipy: 0.16.0 statsmodels: None IPython: 3.0.0 sphinx: None patsy: None dateutil: 2.5.0 pytz: 2015.7 blosc: None bottleneck: 1.0.0 tables: 3.2.2 numexpr: 2.4.6 matplotlib: 1.4.3 openpyxl: 2.2.2 xlrd: 0.9.3 xlwt: 0.8.0 xlsxwriter: 0.7.3 lxml: 3.4.4 bs4: None html5lib: None httplib2: 0.9.1 apiclient: None sqlalchemy: 1.0.9 pymysql: None psycopg2: 2.6.1 (dt dec pq3 ext lo64) Jinja2: None

[ssche]

The problem is this part in pd.to_datetime():

       if format is not None:
            # There is a special fast-path for iso8601 formatted
            # datetime strings, so in those cases don't use the inferred
            # format because this path makes process slower in this
            # special case
            format_is_iso8601 = _format_is_iso(format)
            print 'format_is_iso8601', format_is_iso8601
            if format_is_iso8601:
                require_iso8601 = not infer_datetime_format
                format = None

When I change the condition to if format is not None and infer_datetime_format, it works as expected. Is this something that could be changed in Pandas. I find it quite confusing that despite explicitly setting infer_datetime_format=False there's still some internal magic happening in this function. Others may feel the same, not sure...

[jreback]

nothing to do with that (well you can disable if you set infer_datetime_format=False). Its here: https://docs.python.org/3/library/re.html#search-vs-match

if format is specified and exact=True (the default) its a .match like search, meaning it must match at the beginning (as opposed to anywhere). So this is a valid match.

I suppose we could be more stricted on exact, e.g. maybe make it exact='match|search|False or something.

but why is this a problem? (as an aside you have an ISO8601 string, so you don't need to do anything to parse it).

[ssche]

I'm trying out a few date formats and want to determine the correct one. If I try '%Y-%m-%d' and '%Y-%m-%d %H:%M:%S', they both match which isn't quite true.

The format parameter is definitely set to None in the code I posted above, so I think it has something to do with the snippet... I find this counter-intuitive given the parameters I passed into this function.

[jreback]

hmm, actually I take that back. it IS removing the format BECAUSE its ISO8601. I am not sure it makes sense to change this as there is a perf penalty for parsing ISO strings with an actual format string (via regex). And we expect (and users expect) that the perf will be the same whether they pass a compat format or not.

Further if it actually does match ISO how is this a problem?

[ssche]

In my case, I can easily fix this in the application by not checking for multiple iso format strings. I am trying to detect date format ambiguities, i.e. more than one date format matching which is a generalisation of discerning the %m/%d vs. %d/%m case.

However, I was surprised to get this kind of behaviour from this function as I thought I disabled all auto-magic (infer_datetime_format=False, exact=True) behaviour. I wasn't concerned with possible performance hits.

Perhaps a comment in the documentation would suffice to clarify this behaviour if it cannot be turned off through parameters.

[ssche]

nothing to do with that (well you can disable if you set infer_datetime_format=False). Its here: https://docs.python.org/3/library/re.html#search-vs-match

Your intuition seemed to have been the same as mine... But infer_datetime_format=False doesn't work like this for this iso date case.

[jreback]

@ssche I don't see any ambiguity in an ISO8601 string. That's the point, it cannot be anything but dayfirst=False, yearfirst=False by-definition.

[jreback]

        if format is not None:
            # There is a special fast-path for iso8601 formatted
            # datetime strings, so in those cases don't use the inferred
            # format because this path makes process slower in this
            # special case
            format_is_iso8601 = _format_is_iso(format)
            if format_is_iso8601:
                require_iso8601 = not infer_datetime_format
                format = None

the code says it all. If you would like to update the doc-string, by all means.

[ssche]

The ambiguity detection is something I do in my application. I just provided this bit of information as background info because you asked. The point here is that infer_datetime_format=False, exact=True do some auto-magic behaviour which I find unexpected.

[jreback]

@ssche its not unexpected, you gave it a format string which is ISO8601. I don't see any problem here. If you give it a non-ISO8601 string then it would indeed raise.

[jreback]

In [16]: pd.to_datetime(s, format='%Y-%m-%d%H', infer_datetime_format=False, exact=True, errors='raise')
ValueError: time data '2012-01-01 10:00' does not match format '%Y-%m-%d%H' (match)

# you don't need any arguments actually, the default will be to raise if it is not a matched format.
In [17]: pd.to_datetime(s, format='%Y-%m-%d%H')
ValueError: time data '2012-01-01 10:00' does not match format '%Y-%m-%d%H' (match)

[ssche]

So you don't find it confusing that the behaviour of this function is date format-specific, i.e. given another date format an exception is raised?

[jreback]

@ssche I am not sure what you mean. Of course its date-format specific. Its just special cased to ISO8601 to match the general format.

[ssche]

In the iso-format case, the date format doesn't need to be exact, but for all other formats an exception will be raised when the format doesn't match. That's what I find confusing.

[jreback]

and so? again why is this a problem. by definition they are ISO8601 which matches a variety of non-ambiguous formats (where the time is optional), and separators can be optional (pandas actually allows a lot more separators than the standard).

So we tend to parse more easily lots of formats. I still don't understand why this is a problem. It parses correctly as it should, even though you gave an underspecified format.

[ssche]

So we tend to parse more easily lots of formats. I still don't understand why this is a problem. It parses correctly as it should, even though you gave an underspecified format.

That's exactly my problem. I have a very specific use case which requires to raise an exception when the date format doesn't match. Trying '%Y-%m-%d' and '%Y-%m-%d %H:%M:%S' both won't raise an exception for parsing '2012-01-01 10:00', so the application is unable to detect the proper (=exact, =non underspecified, =non overspecified) date format (that's all I care about).

I tried to make this more strict through the parameters I quoted a number of times already, but this didn't make the date conversion more strict. I don't know how else I can make this function more strict to only accept date formats that I specify and raise an exception if they don't.

[jreback]

well you can try to make a change to the code to see if u can make it very strict but I suspect this will be quite tricky (maybe add exact='strict') or something

alternately in your code it's easy enough to check for lengths of the input strings

[ssche]

So the if format is not None and infer_datetime_format "fix" I proposed wouldn't be acceptable?

[jreback]

no that defeats the purpose of iso format detection

you could allow infer_datetime_format='strict' for maybe exact='striict') which when s format is specified could bypass the inference check (and then would fail because it's not an exact match)

[rsheftel]

Just adding my two cents because I was running into the same exact problem. My use case is that I have a program that uses read_csv to load a csv file where the index is either just a date, or date and time. If it has a time I want to make it UTC time zone, if not then no time zone. The pythonic way to do it I thought would be try to read_csv with a parser using to_datetime that would try to match one format, and if that raised an exception try the other format. But by not raising the exception if there is not an exact match that approach does not work.

I would be in favor of an exact='strict' or similar to truly enforce the exact match.

[fersarr]

this breaks a lot of tests for us as well. Very similar use case as @rsheftel. The strict mode that raises exceptions would work for us too.

[philipCrunchboards]

We're having the same problem. I'd like to be able to add some argument(s) to the following code to make it either raise or return NaT:

> to_datetime(0.0, format="%Y-%m-%d", exact=True)
Timestamp('1970-01-01 00:00:00')

I like the sound of exact="strict".

[jo47011]

We ran into the same problem. I already raised a question at stackoverflow: Pandas to_datetime not setting NaT on wrong format Is there a decision yet on how to proceed with this issue?

[jrmistry]

Hello all,

Not sure if my issue is the same but posting here for suggestions.

Core problem: the pd.to_datetime function does not respect original values even if I set errors = 'ignore'

See example below. The column of values is stored in a UTC timestamp in milliseconds. When I try to convert it to a datetime object, pandas automatically fills in the NaN values with NaT even though I specifically told it to ignore errors. Any suggestions on how to fix this? Thanks

[IngErnestoAlvarez]

I think that's not a problem, to_datetime changes the dtype of the list to datetime, and NaT is the equivalent of NaN on datetime dtype. When u convert NaN to datetime it will change to NaT and that's not an error.

[rsheftel]

The PR #35741 seems to say that in the future the date_parser in read_csv will be deprecated. Does anyone know what the right way to invoke a custom date parser will be in the future?

[brandon-leapyear]

:sparkles: This is an old work account. Please reference @brandonchinn178 for all future communication :sparkles:

---

We're also coming up against this. As a minimal example, this is very confusing behavior for me:

>>> pd.to_datetime('2022/02/01', format="%Y-%m-%d")
Timestamp('2022-02-01 00:00:00')
>>> pd.to_datetime('2022/02/01', format="%Y-%m")
Timestamp('2022-02-01 00:00:00')

We're trying to require the input to EXACTLY be %Y-%m-%d; all other formats should error. The logic mentioned earlier in this thread is very odd behavior: if the string passed to format is a prefix of any ISO formats, format is implicitly converted to allow any ISO string. IMO this "any ISO format" logic should be under a completely different flag, like iso_format=True, and format should be an exact match

https://github.com/pandas-dev/pandas/blob/3cf30739dbd1162c9c81728ed2f8da949b6e367a/pandas/_libs/tslibs/parsing.pyx#L856

EDIT: as a hacky workaround, you can do:

# some string of characters unlikely to be in the column
# if they are in the column, the error message could get messed up;
# you might want to explicitly parse the error message with regex
PREFIX = "!&*@&!#"

try:
    pd.to_datetime(PREFIX + df["col"], format=PREFIX + "%Y-%m-%d")
except ValueError as e:
    # fix error message to remove the prefix
    raise ValueError(e.args[0].replace(PREFIX, ""))

[aflag]

We have also been bitten by this. Not only does it match a longer date format, but it parses integer types too. For instance:

 pd.to_datetime([1], format="%Y-%m-%d %H:%M")

doesn't raise any errors and parses the date to be 1970-01-01 00:00:00.000000001 however, by just changing the separator it works in a very different (and, imho, less surprising) way:

 pd.to_datetime([1], format="%Y|%m|%d %H:%M")

raises ValueError: time data '1' does not match format '%Y|%m|%d %H:%M' (match).

I agree with @brandon-leapyear, a new flag should be created to enable this behaviour and format should always match exactly.

[ssche]

xref #12585, #48621

[MarcoGorelli]

I think it should be possible to make the fast iso8601 path respect exact, that'd make it unnecessary to add an extra argument - I'm giving this a go (will require https://github.com/pandas-dev/pandas/pull/49024 to be in first though)

Right, I think this can be done, there's just two issues to solve here:

1. that

https://github.com/pandas-dev/pandas/blob/009c4c622c739941ae3dfde4bc4117eb25172500/pandas/_libs/tslibs/src/datetime/np_datetime_strings.c#L69-L72

isn't strict with respect to exact, it just keeps parsing the string. The format could be preserved and passed to it.

2. that if an element isn't a string, then it won't even reach the iso8601 function, it'll just go to

https://github.com/pandas-dev/pandas/blob/307c69a37b7f652450e2a3ba7578fdaa7b8d427d/pandas/_libs/tslib.pyx#L570

So, we should only let it get there if format is None