Pandas version checks

  • [X] I have checked that this issue has not already been reported.

  • [X] I have confirmed this bug exists on the latest version of pandas.

  • [X] I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd

date_col = [
    '22-May-2015',
    '22-May-2015',
    '01-Jun-2015',
    '09-Jun-2015',
    '06-Jul-2015',
]

Problem:

pd.to_datetime(date_col, dayfirst=True, infer_datetime_format=True, errors='coerce')
DatetimeIndex(['2015-05-22', '2015-05-22', 'NaT', 'NaT', 'NaT'], dtype='datetime64[ns]', freq=None)

pd.to_datetime(date_col, dayfirst=False, infer_datetime_format=True, errors='coerce')
DatetimeIndex(['2015-05-22', '2015-05-22', 'NaT', 'NaT', 'NaT'], dtype='datetime64[ns]', freq=None)

pd.to_datetime(date_col, dayfirst=True, infer_datetime_format=False, errors='coerce')
DatetimeIndex(['2015-05-22', '2015-05-22', '2015-06-01', '2015-06-09',
               '2015-07-06'],
              dtype='datetime64[ns]', freq=None)

No problem but does not fit use case:

pd.to_datetime(date_col, dayfirst=True, infer_datetime_format=True, errors='raise')
DatetimeIndex(['2015-05-22', '2015-05-22', '2015-06-01', '2015-06-09',
               '2015-07-06'],
              dtype='datetime64[ns]', freq=None)

pd.to_datetime(date_col, dayfirst=False, infer_datetime_format=True, errors='raise')
DatetimeIndex(['2015-05-22', '2015-05-22', '2015-06-01', '2015-06-09',
               '2015-07-06'],
              dtype='datetime64[ns]', freq=None)

pd.to_datetime(date_col, dayfirst=True, infer_datetime_format=False, errors='raise')
DatetimeIndex(['2015-05-22', '2015-05-22', '2015-06-01', '2015-06-09',
               '2015-07-06'],
              dtype='datetime64[ns]', freq=None)

Issue Description

pd.to_datetime is being used to convert datetime columns in multiple tables, and dayfirst=True, infer_datetime_format=True and errors='coerce are used This particular column produces unexpected behavior even though the entire column is in the same format. I suspect dayfirst and infer_datetime_format are conflicting and converts the string into an invalid parsing and results in errors='coerce' converting the bottom three rows into NaT.

However, if using the default errors='raise', there does not appear to be an invalid parse, however that does not fit my general use case.

Expected Behavior

DatetimeIndex(['2015-05-22', '2015-05-22', '2015-06-01', '2015-06-09',
               '2015-07-06'],
              dtype='datetime64[ns]', freq=None)

Installed Versions

INSTALLED VERSIONS ------------------ commit : 2e218d10984e9919f0296931d92ea851c6a6faf5 python : 3.8.11.final.0 python-bits : 64 OS : Windows OS-release : 10 Version : 10.0.19045 machine : AMD64 processor : Intel64 Family 6 Model 158 Stepping 10, GenuineIntel byteorder : little LC_ALL : None LANG : None LOCALE : English_United Kingdom.1252 pandas : 1.5.3 numpy : 1.21.2 pytz : 2021.3 dateutil : 2.8.2 setuptools : 58.0.4 pip : 21.0.1 Cython : None pytest : 6.2.5 hypothesis : None sphinx : None blosc : None feather : None xlsxwriter : 3.0.1 lxml.etree : 4.6.2 html5lib : None pymysql : 1.0.2 psycopg2 : None jinja2 : 3.0.1 IPython : 7.27.0 pandas_datareader: None bs4 : None bottleneck : None brotli : None fastparquet : None fsspec : None gcsfs : None matplotlib : None numba : None numexpr : None odfpy : None openpyxl : 3.0.9 pandas_gbq : None pyarrow : 11.0.0 pyreadstat : None pyxlsb : None s3fs : None scipy : None snappy : None sqlalchemy : 1.3.22 tables : None tabulate : None xarray : None xlrd : None xlwt : None zstandard : None tzdata : None

Comment From: MarcoGorelli

thanks @EcoFiendly for the report

This particular column produces unexpected behavior even though the entire column is in the same format.

it's because 'May' could be inferred be both '%B' and '%b'. '%B' is guessed first, and then 'Jun' doesn't fit it

In [13]: guess_datetime_format("22-May-2015")
Out[13]: '%d-%B-%Y'

You need to pass the format explicitly for this to work:

In [12]: pd.to_datetime(date_col, errors='coerce', format='%d-%b-%Y')
Out[12]:
DatetimeIndex(['2015-05-22', '2015-05-22', '2015-06-01', '2015-06-09',
               '2015-07-06'],
              dtype='datetime64[ns]', freq=None)

This could be improved to use more than just the first non-null element to infer the format, but for now, it's working as expected

As an aside, there have been many, many bug fixes to do with datetime parsing recently, I'd strongly encourage you to try out the pandas 2.0.0 release candidate to check that all works as expected

You can install it with either: - mamba install -c conda-forge/label/pandas_rc pandas==2.0.0rc0 - python -m pip install --upgrade --pre pandas==2.0.0rc0

Comment From: EcoFiendly

Thanks @MarcoGorelli for the swift reply. I will try out pandas 2.0.0 release candidate and report back if I encounter any further issues