import pandas as pd
Sample messy dataset
df = pd.DataFrame({
'raw_date': [
'2026-11-14', # ISO format
'14/11/2026', # Slash format
'14th Nov 2026', # Ordinal suffix
'Nov 14, 2026', # Month name
'46340', # Excel serial number (14-11-2026)
'2026.11.14', # Dot separator
'invalid_entry', # Unparseable text
]
})
1. Strip whitespace and remove ordinal suffixes (st, nd, rd, th)
cleaned_str = (
df['raw_date']
.astype(str)
.str.strip()
.str.replace(r'(\d+)(st|nd|rd|th)', r'\1', regex=True)
)
2. Identify 5-digit Excel serial numbers
is_excel = cleaned_str.str.fullmatch(r'\d{5}')
3. Parse standard date strings (dayfirst=True ensures DD/MM/YYYY priority)
parsed_dates = pd.to_datetime(
cleaned_str.where(~is_excel),
format='mixed',
dayfirst=True,
errors='coerce',
)
4. Parse Excel serial numbers and combine them
excel_dates = pd.to_datetime(
pd.to_numeric(cleaned_str.where(is_excel)),
unit='D',
origin='1899-12-30',
)
df['datetime_obj'] = parsed_dates.fillna(excel_dates)
5. Format into 'dd-mm-yyyy' string layout
df['dd_mm_yyyy'] = df['datetime_obj'].dt.strftime('%d-%m-%Y')
print(df[['raw_date', 'dd_mm_yyyy']])
Top comments (0)