Python Doc & Data Automation

Normalize Inconsistent Date Formats in CSV

One order_date column, four sources, four formats:

order_date
01/02/2026
2026-02-01
1 Feb 26
45689
02-01-2026 00:00:00

pd.to_datetime either raises ValueError: time data "1 Feb 26" doesn't match format "%d/%m/%Y", or — with format="mixed" — succeeds and silently reads 01/02/2026 as 1 February in one file and 2 January in another. Monthly totals move between January and February, and nobody notices until a quarter closes.

Root Cause

A CSV stores dates as text, and text like 01/02/2026 is genuinely ambiguous: British systems mean 1 February, American systems mean 2 January, and nothing in the file says which. pandas resolves ambiguity per call, not per value: dayfirst=True applies to the whole column, so a column mixing both conventions cannot be parsed correctly in one pass. Inference adds a second hazard — with format="mixed" pandas infers per element, so two rows in the same column can be interpreted with different conventions. Excel exports contribute serial numbers (45689 is a day count from 1899-12-30) that look like integers, and systems that export "datetimes" add midnight times to date-only values. Parsing has to start from where each row came from, not from the string alone.

Minimal Diagnostic

Classify every value in the column by shape, and flag the values that are ambiguous — those where both day-first and month-first readings are valid dates.

# pip install "pandas>=2.2"
import re
from collections import Counter
from pathlib import Path
import pandas as pd

SOURCE = Path("in/orders-mixed.csv")
COLUMN = "order_date"

SHAPES = [
    ("iso", re.compile(r"^\d{4}-\d{2}-\d{2}(?:[ T].*)?$")),
    ("slash-4y", re.compile(r"^\d{1,2}/\d{1,2}/\d{4}(?:[ T].*)?$")),
    ("slash-2y", re.compile(r"^\d{1,2}/\d{1,2}/\d{2}$")),
    ("dash-4y", re.compile(r"^\d{1,2}-\d{1,2}-\d{4}(?:[ T].*)?$")),
    ("month-name", re.compile(r"^\d{1,2}[ -][A-Za-z]{3,9}[ -]\d{2,4}$")),
    ("serial", re.compile(r"^\d{5}(?:\.\d+)?$")),
]

def classify(value: str) -> str:
    text = str(value).strip()
    for name, pattern in SHAPES:
        if pattern.match(text):
            return name
    return "unrecognised"

def profile(path: Path, column: str) -> None:
    frame = pd.read_csv(path, dtype={column: "string"})
    shapes = Counter(classify(v) for v in frame[column].dropna())
    print(shapes.most_common())
    ambiguous = 0
    for value in frame[column].dropna():
        m = re.match(r"^(\d{1,2})[/-](\d{1,2})[/-]", str(value).strip())
        if m and 1 <= int(m.group(1)) <= 12 and 1 <= int(m.group(2)) <= 12:
            ambiguous += 1
    print(f"ambiguous day/month values: {ambiguous} of {frame[column].notna().sum()}")
    if "source_system" in frame.columns:
        print(pd.crosstab(frame["source_system"], frame[column].map(classify)).to_string())

if __name__ == "__main__":
    profile(SOURCE, COLUMN)

[('slash-4y', 4210), ('iso', 2880), ('serial', 611), ('month-name', 240), ('dash-4y', 122), ('unrecognised', 7)]
ambiguous day/month values: 1408 of 8063
shape       dash-4y  iso  month-name  serial  slash-4y
source_system
crm-uk            0    0         240       0      4210
erp-de          122    0           0       0         0
webshop           0 2880           0       0         0
legacy-xls        0    0           0     611         0

The cross-tab is the answer: each source system uses one format consistently. 1,408 values are ambiguous in isolation but not once the source is known — the UK CRM is day-first, the German ERP uses DD-MM-YYYY, the webshop is ISO, and the legacy export produces Excel serials.

Format per source system The UK CRM produces day slash month slash four-digit year, parsed with the format percent d slash percent m slash percent Y, and is ambiguous without the source. The German ERP produces day dash month dash year, parsed with an explicit dash format. The webshop produces ISO dates, unambiguous. The legacy Excel export produces serial numbers counted from 30 December 1899, which are not dates at all until converted. Source Shape Parse rule Ambiguous alone crm-uk 01/02/2026 %d/%m/%Y yes erp-de 01-02-2026 %d-%m-%Y yes webshop 2026-02-01 ISO no legacy-xls 45689 Excel serial no

Fix: Parse per Source with Explicit Formats

Parse each source's rows with its own explicit format and never let pandas infer. Rows whose source is unknown fall back to unambiguous shapes only, and anything left is reported rather than guessed. Changed lines carry comments.

# pip install "pandas>=2.2"
from pathlib import Path
import pandas as pd

SOURCE_FORMATS = {                                        # changed: one explicit format per source
    "crm-uk": "%d/%m/%Y",
    "erp-de": "%d-%m-%Y",
    "webshop": "ISO8601",
    "legacy-xls": "EXCEL_SERIAL",
}
EXCEL_EPOCH = pd.Timestamp("1899-12-30")                   # changed: Excel's day zero

def parse_column(frame: pd.DataFrame, column: str, source_col: str = "source_system") -> pd.Series:
    text = frame[column].astype("string").str.strip()
    out = pd.Series(pd.NaT, index=frame.index, dtype="datetime64[ns]")
    for source, fmt in SOURCE_FORMATS.items():
        mask = frame[source_col].eq(source) & text.notna()
        if not mask.any():
            continue
        values = text[mask]
        if fmt == "EXCEL_SERIAL":
            days = pd.to_numeric(values, errors="coerce")
            out.loc[mask] = EXCEL_EPOCH + pd.to_timedelta(days.round(), unit="D")   # changed: serial -> date
        elif fmt == "ISO8601":
            out.loc[mask] = pd.to_datetime(values, format="ISO8601", errors="coerce")
        else:
            out.loc[mask] = pd.to_datetime(values, format=fmt, errors="coerce")     # changed: exact format
    unknown = ~frame[source_col].isin(SOURCE_FORMATS) & text.notna()
    if unknown.any():
        out.loc[unknown] = pd.to_datetime(text[unknown], format="ISO8601", errors="coerce")  # unambiguous only
    return out.dt.normalize()                                                        # changed: drop times

if __name__ == "__main__":
    orders = pd.read_csv(Path("in/orders-mixed.csv"), dtype={"order_date": "string"})
    orders["order_date_parsed"] = parse_column(orders, "order_date")
    failed = orders[orders["order_date_parsed"].isna() & orders["order_date"].notna()]
    print(f"parsed {orders['order_date_parsed'].notna().sum()} of {orders['order_date'].notna().sum()}")
    print(failed[["source_system", "order_date"]].head().to_string(index=False))

errors="coerce" turns unparseable values into NaT instead of stopping the job, which is right only because the failures are counted and reported afterwards. An exact format also acts as a validation: if the UK CRM ever starts emitting ISO dates, every row fails loudly rather than being reinterpreted silently. dt.normalize() removes the midnight times that some exports add, so grouping by date behaves.

Parsing route per row Rows are split by source system. Rows from the UK CRM are parsed with day slash month slash year. Rows from the German ERP use day dash month dash year. Webshop rows are parsed as ISO 8601. Legacy Excel rows have their serial numbers converted from the 1899-12-30 epoch. Rows from unknown sources are parsed only if they are ISO. Everything that fails is collected into a report instead of being guessed. Split by source source_system column Explicit formats %d/%m/%Y, %d-%m-%Y ISO rows format='ISO8601' Excel serials epoch + days Unknown source ISO only Report failures never guess

Variant Fix 1: No Source Column — Infer the Convention per File

Files often arrive without a source column, but each file is internally consistent. Infer the convention from the whole file: if any value has a first component above 12, the file is day-first; if any has a second component above 12, it is month-first.

# pip install "pandas>=2.2"
import re
import pandas as pd

PAIR = re.compile(r"^(\d{1,2})[/-](\d{1,2})[/-](\d{2,4})")

def infer_convention(values: pd.Series) -> str:
    first_over_12 = second_over_12 = 0
    for value in values.dropna().astype(str):
        m = PAIR.match(value.strip())
        if not m:
            continue
        a, b = int(m.group(1)), int(m.group(2))
        first_over_12 += a > 12
        second_over_12 += b > 12
    if first_over_12 and not second_over_12:
        return "dayfirst"
    if second_over_12 and not first_over_12:
        return "monthfirst"
    if first_over_12 and second_over_12:
        return "mixed"                     # the file itself contains both conventions
    return "undetermined"                  # every value ambiguous, e.g. only dates before the 13th

Act on the result rather than defaulting: dayfirst and monthfirst parse with the matching format, mixed means the file must be split by another signal, and undetermined means the file cannot be parsed safely — ask the supplier, or use a neighbouring column such as a sequential order number to infer the ordering. Recording the inferred convention per file in the job log is what makes a later "these dates look wrong" question answerable.

Variant Fix 2: Two-Digit Years and Month Names

Two-digit years need a century rule, and month names depend on locale. %y maps 69–99 to 1969–1999 and 00–68 to 2000–2068, which is wrong for historical data; clamp explicitly. Month names in other languages do not parse at all with the default C locale:

# pip install "pandas>=2.2"
import pandas as pd

GERMAN_MONTHS = {"jan": "Jan", "feb": "Feb", "mär": "Mar", "mrz": "Mar", "apr": "Apr", "mai": "May",
                 "jun": "Jun", "jul": "Jul", "aug": "Aug", "sep": "Sep", "okt": "Oct",
                 "nov": "Nov", "dez": "Dec"}

def parse_month_names(values: pd.Series, pivot_year: int = 2035) -> pd.Series:
    text = values.astype("string").str.strip().str.lower()
    for source, target in GERMAN_MONTHS.items():
        text = text.str.replace(rf"\b{source}[a-zä]*\b", target, regex=True)     # localise to English
    parsed = pd.to_datetime(text, format="%d %b %y", errors="coerce")
    too_late = parsed.dt.year > pivot_year
    return parsed.mask(too_late, parsed - pd.DateOffset(years=100))              # 2068 -> 1968

Replacing month names by rule keeps the job independent of the server's locale, which is safer than locale.setlocale — locale settings are process-global and can change the behaviour of unrelated code. Pick the pivot year from the data's domain: order dates cannot be in the future, birth dates cannot be in the next year.

The same value under two conventions Read as day first, 01/02/2026 is 1 February 2026 and the order lands in the February total. Read as month first it is 2 January 2026 and the order lands in January. For a file of four thousand UK orders, choosing the wrong convention moved 311 orders and 42,800 of revenue between the two months, with no error raised anywhere in the pipeline. dayfirst=True (correct here) 01/02/2026 -> 1 Feb 2026 Jan total: 128,400 Feb total: 171,200 matches the CRM report monthfirst (wrong here) 01/02/2026 -> 2 Jan 2026 Jan total: 171,200 Feb total: 128,400 311 orders in the wrong month

Verification

Assert that everything parsed, that no date falls outside a plausible window, and — the check that matters most — that the parsed dates agree with an independent signal such as a sequential order number or a period stated in the file.

# pip install "pandas>=2.2"
import pandas as pd

def verify_dates(frame: pd.DataFrame, raw_col: str, parsed_col: str,
                 window: tuple[str, str] = ("2015-01-01", "2027-12-31")) -> None:
    unparsed = frame[frame[parsed_col].isna() & frame[raw_col].notna()]
    assert unparsed.empty, f"{len(unparsed)} unparsed value(s), e.g. {unparsed[raw_col].head(3).tolist()}"
    lo, hi = pd.Timestamp(window[0]), pd.Timestamp(window[1])
    out_of_range = frame[(frame[parsed_col] < lo) | (frame[parsed_col] > hi)]
    assert out_of_range.empty, f"{len(out_of_range)} date(s) outside {window}"
    if "order_id" in frame.columns:                     # sequential ids should rise with dates
        ordered = frame.dropna(subset=[parsed_col]).sort_values("order_id")
        inversions = (ordered[parsed_col].diff().dt.days < -3).sum()
        assert inversions <= 0.01 * len(ordered), f"{inversions} date inversions against order_id order"
    day_counts = frame[parsed_col].dt.day.value_counts(normalize=True)
    assert day_counts.head(12).sum() < 0.6, "suspicious clustering in days 1-12: convention may be flipped"
    print(f"{frame[parsed_col].notna().sum()} dates parsed, range "
          f"{frame[parsed_col].min():%Y-%m-%d} to {frame[parsed_col].max():%Y-%m-%d}")

The clustering check is the practical guard against a silent convention flip: when day-first data is read month-first, every day above 12 becomes impossible, so all parsed days fall in 1–12 and the distribution collapses. Real order data spreads roughly evenly across the month.

FAQ

Is dayfirst=True enough? Only for a column that is entirely day-first. It is a hint, not a format — pandas may still fall back to inference for values that do not fit.

What about format="mixed"? It infers per value, which is exactly what makes ambiguous values inconsistent. Use it only when every value is unambiguous, such as ISO with and without times.

How do I keep times when I need them? Skip dt.normalize() and keep the parsed datetime; add timezone handling explicitly, as in fix Excel does not support timezones error.

How do I handle a column where some rows are dates and others are free text such as "on delivery"? Parse into a separate column and keep the original. Rows that fail parsing keep their text, which the validation step can classify as expected values or as errors. Overwriting the source column loses the only evidence of what the supplier actually sent.

Excel serials with times? The fractional part is the time of day: EXCEL_EPOCH + pd.to_timedelta(days, unit="D") without rounding keeps it.

Part of Cleaning Messy CSV Data with pandas.