Convert mixed date formats without silent data loss

Parse mixed dates according to known source formats, retain the original text and record a status for every record. A value such as 01/02/2026 cannot reveal whether its author intended January 2 or February 1 without additional context.

errors="coerce" can help collect failures, but it is not a complete cleaning policy. If coerced values are later dropped without reconciliation, the report silently loses records.

Use source metadata to resolve ambiguity

The pandas quality lab supplies six synthetic date records. Each includes date_text and source_format. The same ambiguous string appears under DMY, MDY and unknown-source labels.

import pandas as pd
from build_and_verify import parse_mixed_dates

dates = parse_mixed_dates().set_index('record_id')
assert dates.loc['D1', 'parsed_date'] == pd.Timestamp('2026-01-31')
assert dates.loc['D2', 'parsed_date'] == pd.Timestamp('2026-02-01')
assert dates.loc['D3', 'parsed_date'] == pd.Timestamp('2026-01-02')
assert pd.isna(dates.loc['D4', 'parsed_date'])
assert dates.loc['D4', 'date_status'] == 'unknown_source_format'
assert dates.loc['D5', 'date_status'] == 'invalid_calendar_or_format'
assert dates.loc['D6', 'date_status'] == 'missing_date'
assert dates['parsed_date'].notna().sum() == 3
print(dates[['date_text', 'source_format', 'parsed_date', 'date_status']])

D2 and D3 legitimately parse to different dates because their source contracts differ. D4 remains unresolved rather than being guessed. D5 is February 30, which is not a valid calendar date. D6 has no date text.

Parse each declared format explicitly

The lab maps iso to %Y-%m-%d, dmy to %d/%m/%Y and mdy to %m/%d/%Y. It applies each format only to that source's rows and stores the result in a separate column.

The to_datetime reference documents explicit formats and coercion behavior. Automatic mixed-format inference can be convenient when a contract permits it, but it cannot resolve an inherently ambiguous day/month string with no source evidence.

Do not use the fact that a parsed date falls inside the desired reporting month as evidence that the interpretation is correct. That chooses the answer based on the result you wanted.

Reconcile outcomes before filtering a month

from build_and_verify import parse_mixed_dates

frame = parse_mixed_dates()
counts = frame['date_status'].value_counts().to_dict()
assert counts == {'accepted': 3, 'unknown_source_format': 1,
                  'invalid_calendar_or_format': 1, 'missing_date': 1}
assert sum(counts.values()) == len(frame) == 6
january = frame.loc[frame['parsed_date'].ge('2026-01-01') & frame['parsed_date'].lt('2026-02-01')]
assert january['record_id'].tolist() == ['D1', 'D3']
print('Two observed January dates; three unresolved or invalid records remain visible')

The January selection contains two accepted records. It does not prove that the complete source contains only two January events, because three records still lack usable dates.

If those unresolved records have material amounts, report their value separately when known. A date-quality problem can create a reporting-coverage problem even when the amount column is valid.

Preserve the repair trail

Keep date_text, source_format, parsed_date and date_status together. If a source owner later confirms D4's convention, record that correction and rerun the parser. Avoid manually replacing a date in the final aggregate, where the change becomes difficult to trace.

For recurring sources, validate the format at ingestion and monitor changes. A system migration can alter date serialization while preserving the column name.

Separate calendar dates from timestamps

These examples are date-only values. Timestamped events additionally require a timezone contract and a decision about the reporting timezone. Parsing a timestamp successfully does not by itself determine the business date.

Exercise: add 13/02/2026 under MDY and DMY source labels. Predict which row should parse, then verify the status counts. Explain why a parser should not silently switch conventions for one row to make it succeed.

NeuraPath's Data Analytics with Generative AI course connects pandas parsing with source accountability. A clean date column is useful when every conversion and unresolved record can be explained.

Continue learning

This article is part of the Pandas wrangling and data checks sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Pandas groupby with missing categories and explicit denominators.
• Continue with Pandas duplicated: choose the business key before the method.