Click here to Skip to main content
15,795,793 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I wrote a function that converts all the dates of the specified date column to the specified format. Any missing or invalid date is replaced with a value specified by the user.

The issue is, all the dates are being converted to the same default format '%Y/%m/%d', regardless of the format specified.

What I have tried:

# Date Formatting
from dateutil.parser import parse
import pandas as pd

def date_fun(dfd, date_inputs):
        col_name = date_inputs["DateColumn"]
        replace_date = date_inputs["ReplaceValue"]

        date_col = dfd[col_name]

        formatted_dates = pd.to_datetime(date_col, errors='coerce')
      #   dayfirst=True   
      #   formatted_dates = pd.to_datetime(date_col, errors='coerce', dayfirst=True)
      #   formatted_dates = pd.to_datetime(date_col, format='%m/%d/%Y', errors='coerce')
      #   formatted_dates = pd.to_datetime(date_col, format='%Y/%m/%d', errors='coerce')
      #   formatted_dates = pd.to_datetime(date_col, format='%d/%m/%Y', errors='coerce')

        if replace_date is not None:
            formatted_dates = formatted_dates.fillna(replace_date)
        dfd[col_name] = formatted_dates

        result_dict = dfd.to_dict(orient='records')
        for record in result_dict:
            for key, value in record.items():
                if isinstance(value, float):
                    record[key] = str(value)
        return result_dict
    except Exception as e:
        logging.error(f"An error occurred while performing the operation: {e}")

Column Data





23-01-1992, 7:45

My Output






Why is the conversion of dates to the specified format not happening?

NOTE: I kept the different formats that I tried in comments.
Updated 23-Apr-23 22:51pm

The to_datetime function converts a date and time string from text to a DateTime object, not the other way round. So your format string must match the format of the date strings you are converting. See pandas.to_datetime — pandas 2.0.0 documentation[^].
Share this answer
Because datetime objects don't have a format: they are stored as a number of ticks since a specified point in time and only display as any format when they are converted to a string for presentation to the user.
Since you fill your column with datetime objects, the table applies the current style from the system it is executing on and converts the datetime to a string at that point.

If you want your column to have a specific date format, you need to apply that to the column itself, not the data!
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900