Why can't you format the date?-CodePudding

I have a dataframe with different dates with the following format 15 January 2012 and want to pass it with a format 15/01/2012

My code

data['Last Date'] = pd.to_datetime(data['Last Date'], format="%d/%B/%Y")
print(data.info())

but I get an error.

TypeError                                 Traceback (most recent call last)
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\core\tools\datetimes.py:510, 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))

File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\_libs\tslibs\conversion.pyx:360, 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)
c:\Users\Ivan\Programacion\Hacthon atrazeneca\exceltosql.ipynb Cell 4' in <module>
----> 1 data['Last Refreshed on'] = pd.to_datetime(data['Last Refreshed on'], format="%d/%B/%Y")
      2 print(data.info())

File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\core\tools\datetimes.py:1047, in to_datetime(arg, errors, dayfirst, yearfirst, utc, format, exact, unit, infer_datetime_format, origin, cache)
   1045             result = arg.tz_localize(tz)
   1046 elif isinstance(arg, ABCSeries):
-> 1047     cache_array = _maybe_cache(arg, format, cache, convert_listlike)
   1048     if not cache_array.empty:
   1049         result = arg.map(cache_array)
...
    439         return _return_parsed_timezone_results(result, timezones, tz, name)

File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\_libs\tslibs\strptime.pyx:150, in pandas._libs.tslibs.strptime.array_strptime()

ValueError: time data '19 February 2015' does not match format '%d/%B/%Y' (match)

CodePudding user response：

Convert the column to datetime in the parsed format and then convert it to the format you want


date = pd.to_datetime(data["Last Date"], format="%d %B %Y")
data['Last Date'] = date.dt.strftime("%d/%m/%Y")

Example:

import pandas as pd


time = [item.strftime("%d %B %Y") for item in pd.date_range("2021-05-12", "2021-06-02")]
df = pd.DataFrame({"Last Date": time})


date = pd.to_datetime(df["Last Date"], format="%d %B %Y")
df["Last Date"] = date.dt.strftime("%d/%m/%Y")
print(df)