Geospatial Schema Standardization & Mapping Automation

Normalizing Mixed Date Formats in Legacy Attribute Tables

03/04/2019 is either the third of April or the fourth of March, and nothing in the value says which. In a municipal survey-date column that has passed through a desktop client, two spreadsheet round-trips and a shapefile export, you will also meet 2019-04-03, the string 19-APR-03, the integer 43558, an empty string, and the text unknown — often in the same column. An inferring parser resolves all of that without complaint, using a locale default for the ambiguous cases, and the dates it invents are indistinguishable from the ones it read. This procedure parses only what has been declared and quarantines the rest, implementing a coercion rule inside field renaming and type coercion rules, part of Automated Attribute Transformation & ETL Workflows.

The steps map to configure (Steps 1–2, profiling and declaration), execute (Step 3, strict parsing), validate (Step 4, ambiguity), and log (Step 5, the matched format). The single rule underneath: a date the pipeline cannot justify is a rejection, not a guess.

Prerequisites checklist

Step 1: Profile the column before writing any parser

python
# etl/date_profile.py — pandas >=2.1 — Python 3.10+
import re
from collections import Counter

SHAPES = [
    (re.compile(r"^\d{4}-\d{2}-\d{2}$"),                  "ISO yyyy-mm-dd"),
    (re.compile(r"^\d{2}/\d{2}/\d{4}$"),                  "nn/nn/yyyy — AMBIGUOUS"),
    (re.compile(r"^\d{2}/\d{2}/\d{2}$"),                  "nn/nn/yy — AMBIGUOUS"),
    (re.compile(r"^\d{1,2}-[A-Za-z]{3}-\d{2,4}$"),        "d-MMM-yy"),
    (re.compile(r"^\d{5}$"),                              "spreadsheet serial"),
    (re.compile(r"^\s*$"),                                "blank"),
]


def profile(values) -> Counter:
    counts: Counter = Counter()
    for value in values:
        text = "" if value is None else str(value).strip()
        for pattern, label in SHAPES:
            if pattern.match(text):
                counts[label] += 1
                break
        else:
            counts[f"other: {text[:20]!r}"] += 1
    return counts
text
ISO yyyy-mm-dd            18,204
nn/nn/yyyy — AMBIGUOUS     4,116
spreadsheet serial           388
d-MMM-yy                     102
blank                         41
other: 'unknown'              12

That profile is the whole design brief. It says the column needs four parsers and a null rule, that a quarter of the values are in a format no parser can disambiguate on its own, and that someone has to answer one question — what convention did this source use for the slash format — before anything can be coerced.

Step 2: Declare an ordered format list per source

yaml
# coercion_manifest.yaml — pandas >=2.1
sources:
  county_a_parcels:
    date_columns:
      last_survey_date:
        formats:                    # MANDATORY: ordered, most specific first
          - "%Y-%m-%d"              # ISO
          - "%d/%m/%Y"              # DECLARED: this source is day-first (confirmed 2026-05)
          - "%d-%b-%y"              # 19-APR-03
        serial_origin: "1899-12-30" # OPTIONAL: enables spreadsheet serial parsing
        null_values: ["", "unknown", "n/a", "0"]   # MANDATORY: explicit, per source
        plausible_range: ["1900-01-01", "today"]   # MANDATORY: rejects serials read as years
        on_ambiguous: quarantine    # MANDATORY: quarantine | first_match

The comment on %d/%m/%Y is not decoration — it is the audit trail for a decision that cannot be derived from the data. Record who confirmed it and when, because in two years someone will ask, and the alternative is re-litigating it from the same ambiguous values.

on_ambiguous: first_match exists for the case where a source is documented and the ambiguity is only theoretical. It should be rare, and choosing it should feel like a decision.

Step 3: Parse strictly, in declared order

python
# etl/dates.py — pandas >=2.1 — Python 3.10+
import logging
from dataclasses import dataclass
from datetime import date, datetime, timedelta

logger = logging.getLogger("etl.dates")


@dataclass(frozen=True)
class Parsed:
    value: date | None
    matched_format: str
    code: str          # "" on success


def parse_one(raw, spec: dict) -> Parsed:
    text = "" if raw is None else str(raw).strip()
    if text.lower() in {v.lower() for v in spec["null_values"]}:
        return Parsed(None, "null", "")

    candidates: list[tuple[date, str]] = []
    for fmt in spec["formats"]:
        try:
            candidates.append((datetime.strptime(text, fmt).date(), fmt))
        except ValueError:
            continue

    if not candidates and spec.get("serial_origin") and text.isdigit():
        origin = datetime.strptime(spec["serial_origin"], "%Y-%m-%d").date()
        candidates.append((origin + timedelta(days=int(text)), "serial"))

    if not candidates:
        return Parsed(None, "", "DATE_UNPARSEABLE")

    distinct = {value for value, _fmt in candidates}
    if len(distinct) > 1 and spec["on_ambiguous"] == "quarantine":
        logger.warning("ambiguous date %r parses as %s", text, sorted(distinct))
        return Parsed(None, "", "DATE_AMBIGUOUS")

    value, fmt = candidates[0]
    low, high = spec["plausible_range"]
    high_date = date.today() if high == "today" else datetime.strptime(high, "%Y-%m-%d").date()
    if not (datetime.strptime(low, "%Y-%m-%d").date() <= value <= high_date):
        return Parsed(None, fmt, "DATE_IMPLAUSIBLE")

    return Parsed(value, fmt, "")

strptime with an explicit format is deliberately unforgiving, and that is the property being bought here. pandas.to_datetime without a format will parse 03/04/2019, April 3 2019 and next Tuesday — the last of which is not a joke, it is what a permissive parser does with free text in a legacy column. Strict parsing means every accepted value matched something a person wrote down.

The plausibility range does double duty. It rejects the sentinel 0 if it slipped past the null list, and it catches a spreadsheet serial misread as a year: 43558 parsed as a four-digit year lands in the year 43558, which no range accepts.

Why an Ambiguous Value Is Quarantined, Not Guessed A single raw value, 03 slash 04 slash 2019, is fed to two declared formats. Under the day-first format it parses as the third of April 2019; under the month-first format it parses as the fourth of March 2019. The two results are drawn on a timeline thirty days apart, and a note observes that in a survey-date column that difference can move a record across a reporting period boundary. Because the two candidates disagree, the value is quarantined with both interpretations recorded, rather than resolved by whichever locale the parser happened to run under. "03/04/2019" one raw value %d/%m/%Y — day first 3 April 2019 %m/%d/%Y — month first 4 March 2019 4 Mar 3 Apr 30 days apart enough to cross a reporting period → quarantined, both candidates recorded

Step 4: Quarantine ambiguity with both interpretations attached

python
# etl/dates.py — Python 3.10+
def rejection_row(feature_id: str, column: str, raw, parsed: Parsed, spec: dict) -> dict:
    candidates = []
    for fmt in spec["formats"]:
        try:
            candidates.append(f"{fmt}→{datetime.strptime(str(raw).strip(), fmt).date()}")
        except ValueError:
            continue
    return {
        "feature_id": feature_id,
        "field": column,
        "code": parsed.code,
        "observed": str(raw)[:40],
        "candidates": "; ".join(candidates),      # what it *would* have been, per format
    }

Recording every candidate interpretation is what makes the quarantine actionable. A steward looking at four thousand rejections all reading %d/%m/%Y→2019-04-03; %m/%d/%Y→2019-03-04 can answer the underlying question once — which convention does this source use — and the whole population resolves. A rejection that says only “ambiguous” sends them back to the raw data to work it out again.

Step 5: Record the format that matched each value

python
# etl/dates.py — pyarrow >=14 — Python 3.10+
import pyarrow as pa

DATE_COERCION_SCHEMA = pa.schema([
    ("feature_id",     pa.string()),
    ("run_id",         pa.string()),
    ("field",          pa.string()),
    ("raw",            pa.string()),
    ("parsed",         pa.date32()),
    ("matched_format", pa.string()),     # "%Y-%m-%d", "serial", "null", ""
    ("code",           pa.string()),
])

The matched_format column is the one that pays for itself. If the day-first declaration turns out to be wrong six months later, the fix is a query — every row where matched_format is %d/%m/%Y and the day is twelve or under is suspect, and everything above twelve was unambiguous anyway. Without it, correcting the mistake means re-reading the source, which by then has been superseded twice. It joins the lineage manifest on run_id and feature_id.

Verification

python
# tests/test_dates.py — pytest >=7
SPEC = load_manifest()["sources"]["county_a_parcels"]["date_columns"]["last_survey_date"]


def test_iso_parses_unambiguously():
    assert parse_one("2019-04-03", SPEC).value == date(2019, 4, 3)


def test_serial_uses_the_declared_origin():
    # 43558 days after 1899-12-30 is 2019-04-03 in the Excel convention.
    assert parse_one("43558", SPEC).value == date(2019, 4, 3)


def test_ambiguous_slash_date_is_quarantined():
    spec = dict(SPEC, formats=["%d/%m/%Y", "%m/%d/%Y"])
    result = parse_one("03/04/2019", spec)
    assert result.value is None and result.code == "DATE_AMBIGUOUS"


def test_free_text_is_never_parsed():
    # Negative control: a permissive parser would happily interpret this.
    assert parse_one("next Tuesday", SPEC).code == "DATE_UNPARSEABLE"

A run over the profiled column logs the distribution of matched formats, which is the health signal to watch between deliveries:

text
INFO etl.dates last_survey_date: 18,204 %Y-%m-%d · 4,116 %d/%m/%Y · 388 serial · 41 null
INFO etl.dates rejected: 12 DATE_UNPARSEABLE · 0 DATE_AMBIGUOUS · 3 DATE_IMPLAUSIBLE

Troubleshooting

Symptom Likely cause Fix
Dates land a day or two off A spreadsheet serial parsed with the wrong origin Excel on Windows uses 1899-12-30 and has a 1900 leap-year bug; older Mac files use 1904
Roughly 40% of dates look wrong, the rest fine Day-first data parsed month-first; values above twelve were unambiguous and survived Correct the declaration and re-run; use matched_format to find affected rows
A column parses cleanly but the counts moved The parser fell back to inference for values the format list missed Remove every fallback; unparseable must mean rejected
DATE_IMPLAUSIBLE on thousands of rows Sentinel 0 or 9999 not in null_values Add the sentinel per source and count it; a changing sentinel rate is a source change
Times appear as midnight everywhere The source carried a time that a date-only target dropped Decide the target type deliberately; a DBF round-trip drops it whatever you decide
Two sources need different conventions for the same field Formats declared globally rather than per source Keep the format list under the source, as in the manifest above