1

pd.to_datetime("20/01/2023", format="DD/MM/YYYY") returns an unexpected value error: ValueError: time data '20-01-2023' does not match format 'DD/MM/YYYY' (match)

This is how I got to that error:

pd.to_datetime("20/01/2023") works, but returns a fair warning:

<ipython-input-19-b333b85550fc>:1: UserWarning: Parsing '20/01/2023' in DD/MM/YYYY format. Provide format or specify infer_datetime_format=True for consistent parsing.

However, by using the (suggested) correct date format: pd.to_datetime("20-01-2023", format="DD/MM/YYYY")

I get a weird value error:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
~\anaconda3\lib\site-packages\pandas\core\tools\datetimes.py in _to_datetime_with_format(arg, orig_arg, name, tz, fmt, exact, errors, infer_datetime_format)
    509         try:
--> 510             values, tz = conversion.datetime_to_datetime64(arg)
    511             dta = DatetimeArray(values, dtype=tz_to_dtype(tz))

~\anaconda3\lib\site-packages\pandas\_libs\tslibs\conversion.pyx in pandas._libs.tslibs.conversion.datetime_to_datetime64()

TypeError: Unrecognized value type: <class 'str'>

During handling of the above exception, another exception occurred:

ValueError                                Traceback (most recent call last)
<ipython-input-29-79a8b4ee5337> in <module>
----> 1 pd.to_datetime("20/01/2023", format="DD/MM/YYYY")

~\anaconda3\lib\site-packages\pandas\core\tools\datetimes.py in to_datetime(arg, errors, dayfirst, yearfirst, utc, format, exact, unit, infer_datetime_format, origin, cache)
   1076             result = convert_listlike(arg, format)
   1077     else:
-> 1078         result = convert_listlike(np.array([arg]), format)[0]
   1079 
   1080     #  error: Incompatible return value type (got "Union[Timestamp, NaTType,

~\anaconda3\lib\site-packages\pandas\core\tools\datetimes.py in _convert_listlike_datetimes(arg, format, name, tz, unit, errors, infer_datetime_format, dayfirst, yearfirst, exact)
    392 
    393     if format is not None:
--> 394         res = _to_datetime_with_format(
    395             arg, orig_arg, name, tz, format, exact, errors, infer_datetime_format
    396         )

~\anaconda3\lib\site-packages\pandas\core\tools\datetimes.py in _to_datetime_with_format(arg, orig_arg, name, tz, fmt, exact, errors, infer_datetime_format)
    512             return DatetimeIndex._simple_new(dta, name=name)
    513         except (ValueError, TypeError):
--> 514             raise err
    515 
    516 

~\anaconda3\lib\site-packages\pandas\core\tools\datetimes.py in _to_datetime_with_format(arg, orig_arg, name, tz, fmt, exact, errors, infer_datetime_format)
    499 
    500         # fallback
--> 501         res = _array_strptime_with_fallback(
    502             arg, name, tz, fmt, exact, errors, infer_datetime_format
    503         )

~\anaconda3\lib\site-packages\pandas\core\tools\datetimes.py in _array_strptime_with_fallback(arg, name, tz, fmt, exact, errors, infer_datetime_format)
    435 
    436     try:
--> 437         result, timezones = array_strptime(arg, fmt, exact=exact, errors=errors)
    438         if "%Z" in fmt or "%z" in fmt:
    439             return _return_parsed_timezone_results(result, timezones, tz, name)

~\anaconda3\lib\site-packages\pandas\_libs\tslibs\strptime.pyx in pandas._libs.tslibs.strptime.array_strptime()

ValueError: time data '20/01/2023' does not match format 'DD/MM/YYYY' (match)

As you see, pandas tells me time data '20/01/2023' does not match format 'DD/MM/YYYY' (match), but '20/01/2023' does match the format. What's going on here? How can I create this datetime parsing the format then?

FObersteiner
  • 22,500
  • 8
  • 42
  • 72
Jota92
  • 21
  • 1
  • 4
  • `pd.to_datetime("20/01/2023", format="%d/%m/%Y")` works, so was the suggested "DD/MM/YYYY" format that I used wrong? – Jota92 Jan 31 '23 at 08:59
  • 1
    Who suggested that format? That simply is not a valid parsing directive in Python. – FObersteiner Jan 31 '23 at 09:32
  • 1
    @FObersteiner, if you check the beginning of the post, you'll see I toke the initial warning text as a suggested format string. That is, `pd.to_datetime("20/01/2023")` worked, but returned the warning: `:1: UserWarning: Parsing '20/01/2023' in DD/MM/YYYY format. Provide format or specify infer_datetime_format=True for consistent parsing.` And that's where I picked the (now I realise wrong) date string format of "DD/MM/YYYY". – Jota92 Jan 31 '23 at 10:36
  • right, that's what happens when you take warnings seriously - seriously: that warning is indeed misleading. – FObersteiner Jan 31 '23 at 10:49

1 Answers1

2

The correct way to specify the format is

pd.to_datetime("20/01/2023", format="%d/%m/%Y")

Check out the documentation at https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.to_datetime.html where it links to the strptime codes

576i
  • 7,579
  • 12
  • 55
  • 92