to_datetime() with errors=coerce and without return different values

[grleblanc]

Code Sample, a copy-pastable example if possible

Without errors='coerce'

>>> d = { 'ts': ['2019-02-02 08:07:13+00', '2019-02-02 08:03:22.54+00'] }
>>> df = pd.DataFrame(data=d)
>>> d
{'ts': ['2019-02-02 08:07:13+00', '2019-02-02 08:03:22.54+00']}
>>> df
                          ts
0     2019-02-02 08:07:13+00
1  2019-02-02 08:03:22.54+00
>>> df.dtypes
ts    object
dtype: object

>>> df['ts'] = pd.to_datetime(df['ts'], infer_datetime_format=True)
>>> df
                                ts
0        2019-02-02 08:07:13+00:00
1 2019-02-02 08:03:22.540000+00:00
>>> df.dtypes
ts    datetime64[ns, UTC]
dtype: object

With errors='coerce'

>>> d = { 'ts': ['2019-02-02 08:07:13+00', '2019-02-02 08:03:22.54+00'] }
>>> df = pd.DataFrame(data=d)
>>> d
{'ts': ['2019-02-02 08:07:13+00', '2019-02-02 08:03:22.54+00']}
>>> df
                          ts
0     2019-02-02 08:07:13+00
1  2019-02-02 08:03:22.54+00
>>> df.dtypes
ts    object
dtype: object
>>> df['ts'] = pd.to_datetime(df['ts'], infer_datetime_format=True, errors='coerce')
>>> df
                   ts
0 2019-02-02 08:07:13
1                 NaT

Problem description

The functionality of to_datetime() with errors='coerce' is different than without. If I understand some of the other issues raised on this topic correctly, the functionality is different in some cases by design. In this case, the dates are very similiar, although different format.

Expected Output

>>> d = { 'ts': ['2019-02-02 08:07:13+00', '2019-02-02 08:03:22.54+00'] }
>>> df = pd.DataFrame(data=d)
>>> d
{'ts': ['2019-02-02 08:07:13+00', '2019-02-02 08:03:22.54+00']}
>>> df
                          ts
0     2019-02-02 08:07:13+00
1  2019-02-02 08:03:22.54+00
>>> df.dtypes
ts    object
dtype: object

>>> df['ts'] = pd.to_datetime(df['ts'], infer_datetime_format=True, errors='coerce')
>>> df
                                ts
0        2019-02-02 08:07:13+00:00
1 2019-02-02 08:03:22.540000+00:00
>>> df.dtypes
ts    datetime64[ns, UTC]
dtype: object

Output of pd.show_versions()

[paste the output of pd.show_versions() here below this line]

INSTALLED VERSIONS

commit: None
python: 3.6.5.final.0
python-bits: 64
OS: Darwin
OS-release: 16.7.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.24.1
pytest: None
pip: 10.0.1
setuptools: 39.0.1
Cython: None
numpy: 1.14.2
scipy: None
pyarrow: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.7.5
pytz: 2018.4
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml.etree: 3.8.0
bs4: None
html5lib: 1.0.1
sqlalchemy: 1.2.7
pymysql: None
psycopg2: 2.7.5 (dt dec pq3 ext lo64)
jinja2: 2.8.1
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

[mroeschke]

Agreed this looks strange. Investigation and PR's welcome!

[mroeschke]

So in to_datetime, we have a lot of sub functions that also attempts error handling while the the logic of when to return a result is inconsistent as well. I have a feeling that pulling out the error handling logic at more of a top level would help make this keyword return a more consistent result.

[saurav2608]

I will have a look at this.

[ngutzmann]

Perhaps this is a bit of useful information. In pandas 0.23.4 the functionality looks to be correct:

>>> import pandas as pd
>>> pd.__version__
'0.23.4'
>>> d = { 'ts': ['2019-02-02 08:07:13+00', '2019-02-02 08:03:22.54+00'] }
>>> df = pd.DataFrame(data=d)
>>> df
                          ts
0     2019-02-02 08:07:13+00
1  2019-02-02 08:03:22.54+00
>>> df['ts'] = pd.to_datetime(df['ts'], infer_datetime_format=True, errors='coerce')
>>> df
                       ts
0 2019-02-02 08:07:13.000
1 2019-02-02 08:03:22.540
>>> df.dtypes
ts    datetime64[ns]
dtype: object

[saurav2608]

I think we need some discussion on what is the correct behavior in some of the corner cases. For example in the below case (on version 0.24.0). I think as errors is 'raise' the second command should raise an error. Currently, we retry parsing on error if 'infer_datetime_format' is True. And reading the code here(

pandas/pandas/core/tools/datetimes.py

Line 296 in 4a20d5b

if result is None:

) makes me think that this behavior is by design.

I think, if errors = 'raise' and anytime we encounter error (even with infer_datetime_format) we should raise and stop.

In [12]: s = pd.Series(np.array(['01/01/2011 00:00:00', 
    ...:                                 '01-02-2011 00:00:00', 
    ...:                                 '2011-01-03T00:00:00']))                                                                                                                 

In [13]: s                                                                                                                                                                        
Out[13]: 
0    01/01/2011 00:00:00
1    01-02-2011 00:00:00
2    2011-01-03T00:00:00
dtype: object
In [19]: pd.to_datetime(s, infer_datetime_format=True, errors='coerce')                                                                                                           
Out[19]: 
0   2011-01-01
1          NaT
2          NaT
dtype: datetime64[ns]

In [20]: pd.to_datetime(s, infer_datetime_format=True, errors='raise')                                                                                                            
Out[20]: 
0   2011-01-01
1   2011-01-02
2   2011-01-03
dtype: datetime64[ns]

[saurav2608]

I think we need some discussion on what is the correct behavior in some of the corner cases. For example in the below case (on version 0.24.0). I think as errors is 'raise' the second command should raise an error. Currently, we retry parsing on error if 'infer_datetime_format' is True. And reading the code here(

pandas/pandas/core/tools/datetimes.py

Line 296 in 4a20d5b

if result is None:
) makes me think that this behavior is by design.
I think, if errors = 'raise' and anytime we encounter error (even with infer_datetime_format) we should raise and stop.

In [12]: s = pd.Series(np.array(['01/01/2011 00:00:00', 
    ...:                                 '01-02-2011 00:00:00', 
    ...:                                 '2011-01-03T00:00:00']))                                                                                                                 

In [13]: s                                                                                                                                                                        
Out[13]: 
0    01/01/2011 00:00:00
1    01-02-2011 00:00:00
2    2011-01-03T00:00:00
dtype: object
In [19]: pd.to_datetime(s, infer_datetime_format=True, errors='coerce')                                                                                                           
Out[19]: 
0   2011-01-01
1          NaT
2          NaT
dtype: datetime64[ns]

In [20]: pd.to_datetime(s, infer_datetime_format=True, errors='raise')                                                                                                            
Out[20]: 
0   2011-01-01
1   2011-01-02
2   2011-01-03
dtype: datetime64[ns]

@mroeschke : any thoughts on this.

[mroeschke]

So the core issue is that our format guessing function is unable to guess %z, UTC offsets.

pandas/pandas/_libs/tslibs/parsing.pyx

Line 635 in 2909b83

def _guess_datetime_format(dt_str, dayfirst=False, dt_str_parse=du_parse,

I agree with your suggestion though; errors='raise' should always raise whenever there's an error.

EDIT: Actually this would fail independently of timezones. Since infer_datetime_format guesses a format based on the first non-null time string, and the 2nd string is the example already is in a different format than the first.

[nicolasdaviaud]

I have a similar issue on 0.24.1, it also seems to be related to different time zones in the same query:

dates = ['2016-05-19T10:27:05', '20/05/2016 11:28:06', '']
print(pd.to_datetime(dates, errors='raise', infer_datetime_format=True, box=False))
print(pd.to_datetime(dates, errors='coerce', infer_datetime_format=True, box=False))

returns

['2016-05-19T10:27:05.000000000' '2016-05-20T11:28:06.000000000' 'NaT']
['2016-05-19T10:27:05.000000000' '2016-05-20T11:28:06.000000000' 'NaT']

but adding a Z to the first date changes the behaviour on the second

dates = ['2016-05-19T10:27:05Z', '20/05/2016 11:28:06', '']
print(pd.to_datetime(dates, errors='raise', infer_datetime_format=True, box=False))
print(pd.to_datetime(dates, errors='coerce', infer_datetime_format=True, box=False))

returns

[datetime.datetime(2016, 5, 19, 10, 27, 5, tzinfo=tzutc()) datetime.datetime(2016, 5, 20, 11, 28, 6) 'NaT']
['2016-05-19T10:27:05.000000000' 'NaT' 'NaT']

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit: None python: 3.7.2.final.0 python-bits: 64 OS: Darwin OS-release: 18.2.0 machine: x86_64 processor: i386 byteorder: little LC_ALL: None LANG: fr_FR.UTF-8 LOCALE: fr_FR.UTF-8

pandas: 0.24.1
pytest: None
pip: 18.1
setuptools: 40.6.3
Cython: None
numpy: 1.16.1
scipy: 1.2.1
pyarrow: None
xarray: None
IPython: 7.2.0
sphinx: None
patsy: 0.5.1
dateutil: 2.8.0
pytz: 2018.9
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 3.0.2
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml.etree: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

[ericman93]

same issue in 1.0.4

args = {
    'cache': True,
    'format': None,
    'infer_datetime_format': True
}
ser = pd.Series(['03-12-2016', '29-07-2017', '07-05-2016'])

pd.to_datetime(ser, errors='raise', **args)

outputs:
0   2016-03-12
1   2017-07-29
2   2016-07-05
dtype: datetime64[ns]

pd.to_datetime(ser, errors='coerce', **args)

outputs:
0   2016-03-12
1          NaT
2   2016-07-05
dtype: datetime64[ns]