Pandas DataFrame日期格式统一问题：将乱序日期转为dd/mm/yyyy

Hey there! Dealing with inconsistent, messy date columns in pandas is a super common pain point—let’s break down exactly how to standardize everything to your desired dd/mm/yyyy format.

Step 1: Parse Messy Dates into Datetime Objects #

First, we need to convert unstructured date strings into pandas’ native datetime type. The pd.to_datetime() function is smart enough to auto-detect most common date formats, even if they’re mixed up in the same column.

import pandas as pd

# Convert the messy 'Date' column to datetime type
# Use errors='coerce' to turn unparseable values into NaT (Not a Time) instead of crashing
df['Date'] = pd.to_datetime(df['Date'], errors='coerce')

Pro Tip for Ambiguous Formats:

If your column mixes styles like mm/dd/yyyy and dd/mm/yyyy, add the dayfirst=True parameter to prioritize interpreting the first value as the day (critical for avoiding mix-ups between US and European date conventions):

df['Date'] = pd.to_datetime(df['Date'], errors='coerce', dayfirst=True)

Step 2: Convert Datetime Objects to dd/mm/yyyy String Format #

Once your dates are properly parsed as datetime objects, you can easily format them into the exact string format you want using dt.strftime():

# Format the datetime column to dd/mm/yyyy
df['Date'] = df['Date'].dt.strftime('%d/%m/%Y')

Handling Unparseable Dates #

After using errors='coerce', any dates that couldn’t be parsed will show up as NaN in the final column. You can handle these by:

• Dropping rows with missing dates: df = df.dropna(subset=['Date'])
• Filling them with a default value: df['Date'] = df['Date'].fillna('Unknown Date')

Full Example #

Putting it all together with a sample DataFrame:

import pandas as pd

# Sample messy data
data = {'Date': ['2023-10-05', '06/11/2023', 'Nov 7, 2023', 'invalid-date']}
df = pd.DataFrame(data)

# Parse dates
df['Date'] = pd.to_datetime(df['Date'], errors='coerce', dayfirst=True)

# Format to dd/mm/yyyy
df['Date'] = df['Date'].dt.strftime('%d/%m/%Y')

print(df)
# Output:
#          Date
# 0  05/10/2023
# 1  06/11/2023
# 2  07/11/2023
# 3         NaN

内容的提问来源于stack exchange，提问作者Sourabh Maharajpet