Change object format to datetime pandas

Question

I tried to change column type from object to datetime format, when the date was with this shape dd/mm/yy hh:mm:ss ex: 3/4/2023 4:02:55 PM the type changed well. But when the shape was with this shape yy-mm-dd-hh.mm.ss ex: 2023-03-04-15.22.31.000000 the type changed to datetime but the values become null

I used the code

df['date'] = pd.to_datetime(df['date'], errors='coerce')

or

df['date'] = pd.to_datetime(df['date'], format='%Y-%m-%d %H:%M:%S', errors='coerce')

output was the same result

What's your question exactly? What do you need help with? Isn't it obvious that the data doesn't match the format? If you removed `errors='coerce'`, you'd get an error that tells you that. Please read [ask]. You might also want to read [How to make good reproducible pandas examples](/q/20109391/4518341). — wjandrea, Apr 13 '23 at 02:48

score 0 · Answer 1 · answered Apr 13 '23 at 00:20

The issue is that the format you provided does not match the format of the second date type you mentioned. You should update the format string to match the actual format of the dates in the column. Following is the updated code to handle both date formats:

import pandas as pd

# Sample data
data = {'date': ['3/4/2023 4:02:55 PM', '2023-03-04-15.22.31.000000']}
df = pd.DataFrame(data)

# Custom parser function
def custom_parser(date_str):
    for fmt in ['%d/%m/%Y %I:%M:%S %p', '%Y-%m-%d-%H.%M.%S.%f']:
        try:
            return pd.to_datetime(date_str, format=fmt)
        except ValueError:
            pass
    return pd.NaT

df['date'] = df['date'].apply(custom_parser)
print(df)

In this code, I created a custom parser function custom_parser that tries to parse the date string with multiple formats. If none of the formats work, it returns pd.NaT (Not a Time) to represent a missing value. Then, I used the apply function to apply the custom parser to each date string in the 'date' column.

Now the 'date' column should have the correct datetime values for both formats without any null values.

unfortunately, the column type changed but the dates become null, as df.isnull().sum(axis=0) --> the output 'date' column has all values with null — asmaa mahmoud, Apr 13 '23 at 08:33

Change object format to datetime pandas

1 Answers1