BUG: to_datetime dayfirst, infer_datetime_format and coerce produces unexpected error

[EcoFiendly]

Pandas version checks

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

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

• 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 : 2e218d1 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

[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

[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