How to Clean and Standardise Date Columns in Spatial Data

Problem statement

The survey layer has a date column that looks fine until you try to use it:

>>> gdf["survey_date"].head(8).tolist()
['2026-03-14', '14/03/2026', '03/14/2026', '14-Mar-26', '20260314', '', '1/1/1900', 45000]
>>> gdf["survey_date"].dtype
dtype('O')

Six formats, an empty string, a sentinel date, and an Excel serial number — all in one column of type object. Sorting puts '03/14/2026' before '14/03/2026', filtering by year fails, and writing to a shapefile stores the whole mess as text.

Dates in GIS data are unusually messy because the data has usually been through a spreadsheet, an export, and at least one locale.

Common causes:

day-first and month-first formats mixed in one column, sometimes ambiguously
Excel serial numbers where a date was expected
sentinel values for "unknown": 1900-01-01, 9999-12-31, 0, NULL, -1
shapefiles storing dates as text, or dropping the time component
timezone-aware timestamps mixed with naive ones
two-digit years, where 26 may mean 1926 or 2026

Quick answer

Parse explicitly, quarantine what will not parse, and store a single canonical type:

inventory the raw values before parsing — count formats and lengths
parse with an explicit format= where you can; dayfirst= where you must infer
use errors="coerce" so failures become NaT instead of stopping the run
replace sentinel dates with NaT and validate against a plausible range
store one canonical form: datetime64[ns] in memory, ISO 8601 or a real date field on disk

import pandas as pd
import geopandas as gpd

gdf = gpd.read_file("data/raw/surveys.gpkg")
raw = gdf["survey_date"].astype("string").str.strip()

parsed = pd.to_datetime(raw, format="%Y-%m-%d", errors="coerce")
missing = parsed.isna() & raw.notna() & raw.ne("")
parsed[missing] = pd.to_datetime(raw[missing], dayfirst=True, errors="coerce")

SENTINELS = {"1900-01-01", "1899-12-30", "9999-12-31", "1970-01-01"}
parsed[parsed.dt.strftime("%Y-%m-%d").isin(SENTINELS)] = pd.NaT

gdf["survey_date"] = parsed
print(f"{parsed.notna().sum()} parsed, {parsed.isna().sum()} unparsed or unknown")
print(gdf.loc[parsed.isna() & raw.notna(), "survey_date"].head())

Trying the strict format first and only falling back for the remainder means the well-formed majority is parsed deterministically, and only the odd rows are subject to inference.

Where a date column goes wrong

Triage table of date column problems and their fixes. — Six failure modes — and only one of them is a genuine parsing ambiguity.

Step-by-step solution

Vertical steps: inventory, strict parse, fallback parse, sentinel removal, range validation, canonical storage. — Six stages — the first one decides everything that follows.

Inventory the raw values first

Never parse a column you have not looked at.

import pandas as pd

raw = gdf["survey_date"].astype("string").str.strip()

print("dtype        :", gdf["survey_date"].dtype)
print("nulls        :", raw.isna().sum())
print("blank strings:", raw.eq("").sum())
print("\nvalue shapes:")
shapes = raw.str.replace(r"\d", "9", regex=True).value_counts()
print(shapes.head(10))
print("\nexamples per shape:")
for shape in shapes.head(5).index:
    print(f"  {shape}: {raw[raw.str.replace(r'\d', '9', regex=True) == shape].head(3).tolist()}")

Masking digits turns thousands of distinct values into a handful of shapes — 9999-99-99, 99/99/9999, 99999 — and each shape maps to one parsing rule.

Parse the dominant format strictly

FORMATS = ["%Y-%m-%d", "%d/%m/%Y", "%d-%b-%Y", "%Y%m%d", "%d.%m.%Y"]

parsed = pd.Series(pd.NaT, index=raw.index, dtype="datetime64[ns]")
for fmt in FORMATS:
    todo = parsed.isna() & raw.notna() & raw.ne("")
    if not todo.any():
        break
    attempt = pd.to_datetime(raw[todo], format=fmt, errors="coerce")
    parsed[todo] = attempt
    print(f"{fmt:12} parsed {attempt.notna().sum():5d} more")

print(f"unparsed after formats: {(parsed.isna() & raw.notna() & raw.ne('')).sum()}")

Ordering matters: put the format you believe is dominant first, so ambiguous values like 03/04/2026 are interpreted by the rule you chose rather than by pandas' inference.

Deal with the day-first / month-first ambiguity honestly

03/04/2026 is 3 April or 4 March, and no amount of code can tell you which. What code can do is measure whether the column is decidable.

first = pd.to_numeric(raw.str.extract(r"^(\d{1,2})[/-]")[0], errors="coerce")
second = pd.to_numeric(raw.str.extract(r"^\d{1,2}[/-](\d{1,2})")[0], errors="coerce")

print("first component > 12 (proves day-first) :", (first > 12).sum())
print("second component > 12 (proves month-first):", (second > 12).sum())
ambiguous = (first <= 12) & (second <= 12)
print("undecidable rows:", ambiguous.sum())

If both counts are non-zero, the column contains both conventions and cannot be parsed as one — you need the source system's locale, or a second field, to split it. Record the finding rather than papering over it.

Handle Excel serial numbers

Numbers where dates belong are almost always Excel serials, counted from 1899-12-30 on Windows.

import pandas as pd

numeric = pd.to_numeric(raw, errors="coerce")
serials = numeric.between(20000, 60000)          # ~1954 to ~2064
print(f"{serials.sum()} Excel serial values")

parsed[serials] = pd.to_datetime(numeric[serials], unit="D", origin="1899-12-30")

Check the resulting years look sensible: an off-by-two error caused by the 1900 leap-year bug shows up immediately as dates two days out.

Replace sentinel dates with NaT

SENTINEL_DATES = pd.to_datetime(
    ["1900-01-01", "1899-12-30", "1970-01-01", "9999-12-31", "2099-12-31"]
)
sentinel_hits = parsed.isin(SENTINEL_DATES)
print("sentinel values:", sentinel_hits.sum())
parsed[sentinel_hits] = pd.NaT

# and the ones that are simply implausible
MIN_DATE, MAX_DATE = pd.Timestamp("1990-01-01"), pd.Timestamp.today() + pd.Timedelta(days=1)
out_of_range = parsed.notna() & (~parsed.between(MIN_DATE, MAX_DATE))
print("out of plausible range:", out_of_range.sum())
print(parsed[out_of_range].dt.year.value_counts().head())

A survey date in 1900 or 2099 is not a date, it is a placeholder. Converting it to NaT means "unknown", which is what it actually meant.

Normalise timezones

import pandas as pd

ts = pd.to_datetime(gdf["recorded_at"], errors="coerce", utc=True)   # everything → UTC
gdf["recorded_at_utc"] = ts
gdf["recorded_at_local"] = ts.dt.tz_convert("Europe/London")
gdf["recorded_date"] = ts.dt.tz_convert("Europe/London").dt.date     # local calendar date

utc=True is the only way to combine offsets safely — mixing naive and aware timestamps raises, and mixing offsets silently produces an object column. Derive local calendar dates from the UTC instant, never the other way around.

Store one canonical form

# in memory: real datetimes
gdf["survey_date"] = parsed

# GeoPackage: a real Date/DateTime field
gdf.to_file("data/clean/surveys.gpkg", driver="GPKG")

# Shapefile: Date only — write an ISO string too, so nothing is lost
export = gdf.copy()
export["survey_iso"] = export["survey_date"].dt.strftime("%Y-%m-%d")
export["survey_date"] = export["survey_date"].dt.date
export.to_file("data/clean/surveys.shp", driver="ESRI Shapefile")

Add the derived fields your analysis actually needs, rather than re-parsing downstream:

gdf["year"] = gdf["survey_date"].dt.year
gdf["month"] = gdf["survey_date"].dt.month
gdf["quarter"] = gdf["survey_date"].dt.to_period("Q").astype("string")
gdf["age_days"] = (pd.Timestamp.today().normalize() - gdf["survey_date"]).dt.days

Code examples

Example 1: a reusable date cleaner with a report

import pandas as pd

DEFAULT_FORMATS = ["%Y-%m-%d", "%d/%m/%Y", "%m/%d/%Y", "%d-%b-%Y", "%Y%m%d", "%d.%m.%Y"]
SENTINELS = pd.to_datetime(["1899-12-30", "1900-01-01", "1970-01-01", "9999-12-31"])

def clean_dates(series: pd.Series, formats=DEFAULT_FORMATS,
                min_date="1990-01-01", max_date=None):
    raw = series.astype("string").str.strip().replace({"": pd.NA, "NULL": pd.NA, "null": pd.NA})
    out = pd.Series(pd.NaT, index=raw.index, dtype="datetime64[ns]")
    report = {"rows": len(raw), "blank": int(raw.isna().sum()), "by_format": {}}

    for fmt in formats:
        todo = out.isna() & raw.notna()
        if not todo.any():
            break
        attempt = pd.to_datetime(raw[todo], format=fmt, errors="coerce")
        n = int(attempt.notna().sum())
        if n:
            out[todo] = attempt
            report["by_format"][fmt] = n

    numeric = pd.to_numeric(raw, errors="coerce")
    serials = out.isna() & numeric.between(20000, 60000)
    if serials.any():
        out[serials] = pd.to_datetime(numeric[serials], unit="D", origin="1899-12-30")
        report["excel_serials"] = int(serials.sum())

    hit = out.isin(SENTINELS)
    report["sentinels"] = int(hit.sum())
    out[hit] = pd.NaT

    lo = pd.Timestamp(min_date)
    hi = pd.Timestamp(max_date) if max_date else pd.Timestamp.today().normalize()
    bad_range = out.notna() & ~out.between(lo, hi)
    report["out_of_range"] = int(bad_range.sum())
    out[bad_range] = pd.NaT

    report["parsed"] = int(out.notna().sum())
    report["unparsed"] = int((out.isna() & raw.notna()).sum())
    return out, report

gdf["survey_date"], report = clean_dates(gdf["survey_date"])
for k, v in report.items():
    print(f"{k:16} {v}")

Example 2: quarantine what will not parse

raw = gdf["survey_date_raw"].astype("string")
unparsed = gdf.loc[gdf["survey_date"].isna() & raw.notna()]

if len(unparsed):
    print(f"{len(unparsed)} rows could not be parsed:")
    print(unparsed["survey_date_raw"].value_counts().head(10))
    unparsed.to_file("data/out/unparsed_dates.gpkg", driver="GPKG")

Writing the failures to their own layer keeps the pipeline moving while making sure nobody discovers the gap months later.

Example 3: date-aware spatial filtering

import geopandas as gpd
import pandas as pd

recent = gdf[gdf["survey_date"] >= pd.Timestamp.today() - pd.DateOffset(years=1)]
print(f"{len(recent)} surveys in the last 12 months")

by_quarter = (
    gdf.dropna(subset=["survey_date"])
       .assign(quarter=lambda d: d["survey_date"].dt.to_period("Q").astype("string"))
       .groupby("quarter")
       .agg(n=("survey_date", "size"),
            area_km2=("geometry", lambda g: g.to_crs(3857).area.sum() / 1e6))
)
print(by_quarter.tail())

Example 4: assert date quality in the pipeline

def assert_dates_clean(gdf, col="survey_date", max_missing_pct=5.0):
    s = gdf[col]
    if not pd.api.types.is_datetime64_any_dtype(s):
        raise TypeError(f"{col} is {s.dtype}, expected datetime64")

    missing_pct = s.isna().mean() * 100
    if missing_pct > max_missing_pct:
        raise ValueError(f"{col}: {missing_pct:.1f}% missing (limit {max_missing_pct}%)")

    future = s > pd.Timestamp.today().normalize()
    if future.any():
        raise ValueError(f"{col}: {future.sum()} dates in the future")

    print(f"{col}: ok — {s.notna().sum()} dates, {s.min().date()} to {s.max().date()}")

assert_dates_clean(gdf)

Explanation

A date is not a string; it is a point on a calendar that can be written in dozens of ways. Every one of the messy values in a raw column is a rendering choice made by some earlier system — a locale, a spreadsheet, an export dialogue — and cleaning means recovering the underlying value and then choosing one rendering of your own.

Grid of date storage options across shapefile, GeoPackage, GeoJSON and Parquet. — Where the value ends up matters as much as how you parsed it.

Pandas will happily infer formats, and that convenience is the source of most date bugs. Inference is applied per value in older versions and per column in newer ones, so a column of 03/04/2026-style values can be interpreted one way in your notebook and another way on a colleague's machine with a different pandas version. Passing an explicit format= removes the ambiguity for the values that match it, and errors="coerce" turns the rest into NaT — visible, countable failures rather than silent misreadings.

The genuinely undecidable case deserves attention rather than a default. If a column contains both 13/04/2026 (proving day-first) and 04/13/2026 (proving month-first), it holds two conventions and no single parse is correct. Detecting that by counting components above 12 takes three lines, and it converts an invisible data-quality disaster into a specific question for whoever supplied the file.

Sentinels are the other quiet corruption. 1900-01-01 and 9999-12-31 parse perfectly, so they survive every technical check and then distort every summary: minimum dates, ranges, "days since survey", time-series plots. They mean "unknown", and the honest representation of unknown is NaT.

Finally, storage. Shapefiles have a Date field with no time component, so timestamps are silently truncated. GeoPackage has a proper DateTime. GeoJSON has no date type at all, so ISO 8601 strings are the convention. GeoParquet preserves the pandas dtype, including timezone. Choosing the format with the right ceiling — and writing an ISO string alongside when you must use an older format — keeps the value intact all the way to the consumer.

Edge cases or notes

Excel's leap-year bug: Serial numbers assume 1900 was a leap year. Using origin="1899-12-30" compensates; Mac-origin files may use 1904 instead.
Two-digit years: Pandas maps 69–99 to 1969–1999 and 00–68 to 2000–2068. If your data spans 1920s records, that default is wrong.
errors="ignore" is a trap: It returns the input unchanged, leaving an object column that looks parsed. Use coerce and count the NaTs.
Sorting object columns is meaningless: '2026-03-14' < '14/03/2026' compares text. Always convert before sorting or filtering.
NaT propagates: Arithmetic with NaT yields NaT, which is correct but means aggregations need dropna() or skipna.
Timezones and shapefiles: Shapefiles cannot store an offset. Convert to UTC and write an ISO string if the offset matters.
Locale-dependent month names: %b parses Mar in English but not Mär. Pass the values through a mapping first when the source is non-English.

Internal links

FAQ

Why does pandas parse the same column differently on two machines?

Because format inference has changed across pandas versions and can depend on the first values it sees. Pass an explicit format= (or dayfirst=) so the result is deterministic everywhere.

How do I handle a column with both day-first and month-first dates?

Detect it by counting values whose first component exceeds 12 and whose second component exceeds 12. If both exist, the column genuinely holds two conventions — split it by source or ask the supplier; no parser can resolve it.

What are the numbers in my date column?

Excel serial dates, counted in days from 1899-12-30 on Windows. Convert with pd.to_datetime(values, unit="D", origin="1899-12-30") and sanity-check the resulting years.

Should I use `errors="coerce"` or let parsing fail?

Use coerce in a batch context so one bad row cannot stop the run, then count and quarantine the resulting NaT values. Failing loudly is right only when any bad date should abort the job.

How do I store dates in a shapefile?

You cannot store a full timestamp — the dBase Date field has no time component. Write the date, and add an ISO 8601 string column if the time matters. GeoPackage avoids the problem entirely.

What should I do with dates like 1900-01-01?

Treat them as sentinels for "unknown" and convert to NaT. Left in place they parse cleanly and then corrupt every minimum, range and duration you compute.

How do I keep timezone information through the pipeline?

Parse with utc=True to get a uniform tz-aware column, derive local dates for display, and store either UTC ISO strings or GeoParquet, which preserves the timezone-aware dtype exactly.

How to Clean and Standardise Date Columns in Spatial Data #

Problem statement #

Quick answer #

Where a date column goes wrong #

Step-by-step solution #

Inventory the raw values first #

Parse the dominant format strictly #

Deal with the day-first / month-first ambiguity honestly #

Handle Excel serial numbers #

Replace sentinel dates with NaT #

Normalise timezones #

Store one canonical form #

Code examples #

Example 1: a reusable date cleaner with a report #

Example 2: quarantine what will not parse #

Example 3: date-aware spatial filtering #

Example 4: assert date quality in the pipeline #

Explanation #

Edge cases or notes #

Internal links #

FAQ #

Why does pandas parse the same column differently on two machines? #

How do I handle a column with both day-first and month-first dates? #

What are the numbers in my date column? #

Should I use errors="coerce" or let parsing fail? #

How do I store dates in a shapefile? #

What should I do with dates like 1900-01-01? #

How do I keep timezone information through the pipeline? #