BUG: pd.to_datetime() produces NaN when first row is a time in the current month

[barenxing]

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
df = pd.DataFrame({
    'log_time': [
        'Fri, 31 May 2024 00:21:22 -0400',
        'Tue, 27 Nov 2018 22:10:55 -0400',
        'Fri, 04 Jun 2021 11:57:28 -0500',
        'Fri, 04 Jun 2021 02:57:28 -0500',
        'Sun, 21 Apr 2024 20:33:25 +0000',
    ]
})

df['log_time'] = pd.to_datetime(df['log_time'], errors='coerce', utc=True).dt.strftime('%Y-%m-%d %H:%M:%S')
print(df)


print("If the first entry is not a date in the month of May, 2024")
print("The pd.to_datetime() works just fine")

df1 = pd.DataFrame({
    'log_time': [
        'Tue, 27 Nov 2018 22:10:55 -0400',
        'Fri, 31 May 2024 00:21:22 -0400',
        'Fri, 04 Jun 2021 11:57:28 -0500',
        'Fri, 04 Jun 2021 02:57:28 -0500',
        'Sun, 21 Apr 2024 20:33:25 +0000',
    ]
})

df1['log_time'] = pd.to_datetime(df1['log_time'], errors='coerce', utc=True).dt.strftime('%Y-%m-%d %H:%M:%S')
print(df1)

Issue Description

using pd.to_datetime() to convert string to time, it fails if the first timestamp in the serial is a date in the current month. After sucessfully process the first row, in all subsequent rows, if the date is in the past it produces NaN.

However, it works fine if the first row contains a date in the past month or earlier.

          log_time

0 2024-05-31 04:21:22
1 NaN
2 NaN
3 NaN
4 NaN

Expected Behavior

The output should be like this.

          log_time

0 2024-05-31 04:21:22
1 2018-11-28 02:10:55
2 2021-06-04 16:57:28
3 2021-06-04 07:57:28
4 2024-04-21 20:33:25

Installed Versions

INSTALLED VERSIONS

commit : bdc79c1
python : 3.12.3.final.0
python-bits : 64
OS : Darwin
OS-release : 23.4.0
Version : Darwin Kernel Version 23.4.0: Fri Mar 15 00:10:42 PDT 2024; root:xnu-10063.101.17~1/RELEASE_ARM64_T6000
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.2.1
numpy : 1.26.4
pytz : 2024.1
dateutil : 2.8.2
setuptools : 68.2.2
pip : 23.3.1
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : 8.20.0
pandas_datareader : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : None
bottleneck : 1.3.7
dataframe-api-compat : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : 3.8.4
numba : None
numexpr : 2.8.7
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyreadstat : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None
None

[Aloqeely]

Thanks for the report! This is caused by the current method of guessing the date format which just guesses the date format for the first item and applies it to the other items, and the problem here is that it cannot actually identify if you want 'full month name' (May) or 'abbreviated month name' (also May).

Currently it just guesses full month name for whenever it sees May, but, there is an effort to fix this in #58328
I recommend passing the format parameter to stop relying on the broken guessing behavior e.g. to_datetime(..., format="%a, %d %b %Y %H:%M:%S %z")

[barenxing]

Thank you, add the format string worked.

[anandratna348]

Your code is work, i check this . Please see the above images.

[Aloqeely]

Closing as duplicate, feel free to continue discussion in the original issue.