BUG: UserWarning about lacking infer_datetime_format with pd.to_datetime

[moojen]

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

df['date'] = pd.to_datetime(df['date'], infer_datetime_format=True, utc=True, errors='ignore')

Issue Description

When I run this code, I get the following warning:

"UserWarning: Parsing '15/09/1979' in DD/MM/YYYY format. Provide format or specify infer_datetime_format=True for consistent parsing."

Which is strange, because I already have set infer_datetime_format=True.

Expected Behavior

You would expect no warning at all, since "infer_datetime_format=True" is already provided as an argument.

Installed Versions

pymysql : None html5lib : None lxml.etree : 4.8.0 xlsxwriter : None feather : None blosc : None sphinx : None hypothesis : None pytest : 6.2.5 Cython : None setuptools : 58.1.0 pip : 22.0.3 dateutil : 2.8.2 pytz : 2021.3 numpy : 1.22.2 pandas : 1.4.1

[rhshadrach]

Can you provide a small DataFrame that reproduces this warning? I attempted with

df = pd.DataFrame({'date': ['15/09/1979']})

on main and did not get a warning.

[MarcoGorelli]

closing for now, will reopen if you provide a reproducible example @moojen

[benoit9126]

@MarcoGorelli Here is a small example to reproduce this warning:

pd.to_datetime(['01/01/2000','31/05/2000','31/05/2001', '01/02/2000'], infer_datetime_format=True)

It leads to this output

<input>:1: UserWarning: Parsing '31/05/2000' in DD/MM/YYYY format. Provide format or specify infer_datetime_format=True for consistent parsing.
<input>:1: UserWarning: Parsing '31/05/2001' in DD/MM/YYYY format. Provide format or specify infer_datetime_format=True for consistent parsing.
DatetimeIndex(['2000-01-01', '2000-05-31', '2001-05-31', '2000-01-02'], dtype='datetime64[ns]', freq=None)

The problem is the following: the infer_datetime_format option takes the first not NaN object in the array and uses it to guess the format of the date in the provided array. Here, with "01/01/2000", the guessed format is MM/DD/YYYY.

Later in the conversion function, there is an internal error because '31/05/2000' can not be converted using the guessed format and the format is locally changed (per object, not for the entire array) into the good one DD/MM/YYYY. It leads to a warning per unique date in the array, where the inversion of month and day is required to convert the date.

As you can see in my example, the last date in the array "01/02/2000" is converted using the guessed format into '2000-01-02' (without warning) while I hoped "01-02-2000"...

The user warning is annoying because it is emitted per object in the array. With a large array, it leads to thousands of warning lines: one per value when there is no other option than to change the initial guess to parse the date.

This issue seems to be related to #12585

[rhshadrach]

Agreed that the warning should only be emitted once; and the message saying to pass infer_datetime_format=True is confusing. The trickier issues about how to infer better seems to be well captured by #12585, so I'd recommend scoping this issue on just the warning message and count.