How to Clean and Standardise Date Columns in Spatial Data
On this page
Problem statement
The records have a date column that looks fine until you try to use it. This page works on 3,858 real hedgehog sightings from Scotland, published to GBIF by three recorders: The Wildlife Information Centre's hedgehog survey, the Fife Nature Records Centre, and iNaturalist. Each sighting is a point, and each carries the date exactly as the recorder wrote it, in GBIF's verbatimEventDate:
print(gdf["survey_date"].dtype, "|", len(gdf), "rows")
print(gdf.groupby("dataset")["survey_date"].first().to_string())
print(gdf["survey_date"].sample(8, random_state=4).tolist())str | 3858 rows
dataset
Fife 19/05/2025
TWIC 06/06/2024
iNaturalist 2026-03-27 23:36:19
['02/10/2018', '28/06/2018', '2025-08-12 20:59:14+01:00', '01/08/2012', '1991 - 1997', '2022-05-06 22:47:30+01:00', '06/07/2022', '13/05/2021']
Day-first dates, months with no day, bare years, date ranges, iNaturalist timestamps with offsets, zone abbreviations and whatever a phone's JavaScript produced — all in one text column. Sorting it sorts text, filtering by year does not work, and a spatial layer written from it stores the whole mess as strings.
Dates in GIS data are unusually messy because the data has usually been through a form, a spreadsheet or an app, and at least one locale. In this column the causes are:
- day-first dates that pandas reads month-first unless told otherwise
- partial dates — a month, a year, a season — that a parser silently turns into the 1st of the month or of January
- ranges written as two dates, for records that were only ever known to fall in a window
- a placeholder year (1200) meaning "earliest date unknown"
- timestamps with numeric offsets, zone abbreviations (
BST,PDT) and browser output such asGMT+0100 (GMT+1)
Quick answer
Look at the value shapes, then parse each shape with an explicit format and keep what the value can actually tell you — a day, a month, a year or a window:
raw = gdf["survey_date"].astype("string").str.strip()
truth = pd.to_datetime(gdf["gbif_date"].str[:10], format="%Y-%m-%d", errors="coerce")
guessed = pd.to_datetime(raw, errors="coerce") # let pandas guess
right = (guessed.dt.normalize() == truth).sum()
print(f"guessing: {guessed.notna().sum()} parsed, {right} on the right day")
day_first = pd.to_datetime(raw, format="%d/%m/%Y", errors="coerce") # one shape, one rule
print(f"%d/%m/%Y: {day_first.notna().sum()} parsed, {(day_first == truth).sum()} on the right day")guessing: 1180 parsed, 79 on the right day
%d/%m/%Y: 3061 parsed, 3061 on the right day
Letting pandas guess is the worst of both worlds: it took its format from the first row, 06/06/2024, decided that was month-first, and then parsed only the rows that also happen to read as a valid month-first date — putting most of those on the wrong day — and turned everything else into NaT without a warning. One explicit format gets every day-first row right, and leaves the other shapes for formats of their own. clean_dates(), built step by step below, reads every shape in this column except four seasons.
- inventory the raw values — mask the digits and count the shapes
- settle day-first against month-first by counting components over 12
- parse each shape with an explicit
format=anderrors="coerce" - convert timestamps to one zone before taking the calendar date
- keep partial dates and ranges as a start, an end and a precision
- replace placeholder dates with
NaT, then check against an independent parse
Where this date column goes wrong
Inventory the formats, then parse each one explicitly
Inventory the raw values first
Never parse a column you have not looked at.
shape = raw.str.replace(r"\d", "9", regex=True)
print("nulls:", raw.isna().sum(), "| distinct values:", raw.nunique(), "| distinct shapes:", shape.nunique())
print(shape.value_counts().head(12).to_string())nulls: 0 | distinct values: 2452 | distinct shapes: 84
survey_date
99/99/9999 3061
9999-99 231
9999-99-99 99:99:99 181
9999-99-99 99:99:99+99:99 72
9999 68
9999/99/99 9:99 PM 22
9999 - 9999 19
99/99/9999 - 99/99/9999 17
July 9999 16
99/99/9999-99/99/9999 14
June 9999 14
9999/99/99 99:99 PM 13
Masking digits turns 2,452 distinct values into 84 shapes, and each shape maps to one parsing rule. The long tail matters as much as the head: the browser-style strings, the zone abbreviations and the seasons each appear only a handful of times, and each needs its own decision.
Settle day-first against month-first by counting
03/04/2019 is 3 April or 4 March, and no amount of code can tell you which for that one row. What code can do is measure whether the column as a whole is decidable:
slashed = raw.where(raw.str.fullmatch(r"\d{2}/\d{2}/\d{4}"))
first = pd.to_numeric(slashed.str[:2], errors="coerce")
second = pd.to_numeric(slashed.str[3:5], errors="coerce")
print("dd/mm/yyyy-shaped rows :", slashed.notna().sum())
print("first part > 12 (day-first) :", (first > 12).sum())
print("second part > 12 (month-first) :", (second > 12).sum())
print("both 12 or under (undecidable) :", ((first <= 12) & (second <= 12)).sum())dd/mm/yyyy-shaped rows : 3061
first part > 12 (day-first) : 1881
second part > 12 (month-first) : 0
both 12 or under (undecidable) : 1180
Every row that can prove anything proves day-first, and none proves month-first, so the 1,180 undecidable rows are read day-first with a clear conscience. If both counts had been non-zero, the column would hold two conventions and no single parse would be right — you would need the source system's locale, or a second field, to split it.
What the defaults do with a column like this
for label, kwargs in [("no arguments", {}),
("dayfirst=True", {"dayfirst": True}),
('format="mixed"', {"format": "mixed"}),
('format="mixed", dayfirst, utc', {"format": "mixed", "dayfirst": True, "utc": True})]:
try:
attempt = pd.to_datetime(raw, errors="coerce", **kwargs)
except ValueError as exc:
print(f"{label:30} ValueError: {str(exc)[:60]}")
continue
local = attempt.dt.tz_convert("Europe/London").dt.tz_localize(None) if kwargs.get("utc") else attempt
ok = (local.dt.normalize() == truth).sum()
print(f"{label:30} {attempt.notna().sum():5} parsed, {ok:5} on the right day")
iso = raw.str.fullmatch(r"\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}") & raw.str[8:10].le("12")
swapped = pd.to_datetime(raw[iso], format="mixed", dayfirst=True)
print("mixed + dayfirst on ISO rows:", raw[iso].iloc[0], "->", swapped.iloc[0].date())no arguments 1180 parsed, 79 on the right day
dayfirst=True 3061 parsed, 3061 on the right day
format="mixed" ValueError: Mixed timezones detected. Pass utc=True in to_datetime or tz
format="mixed", dayfirst, utc 3731 parsed, 3269 on the right day
mixed + dayfirst on ISO rows: 2026-04-01 23:10:38 -> 2026-01-04
Each default fails differently. With no arguments pandas guesses one format from the first row. dayfirst=True reads the dominant shape correctly and leaves the rest as NaT. format="mixed" parses row by row, but refuses outright — even with errors="coerce" — once it meets two different offsets. Adding utc=True gets it through, and dayfirst=True is then applied to the ISO timestamps too, so 2026-04-01 becomes 4 January. Only explicit formats remove the guessing.
Parse each shape with an explicit format
parse_part() tries one explicit format at a time and only fills rows that are still empty, recording what each value could tell it:
def parse_part(text):
"""Parse one date or timestamp per row into a start date, an end date and a precision."""
start = pd.Series(pd.NaT, index=text.index, dtype="datetime64[us]")
end = start.copy()
precision = pd.Series(pd.NA, index=text.index, dtype="string")
def take(values, prec, span=None):
todo = start.isna() & values.notna()
start[todo] = values[todo]
end[todo] = values[todo] if span is None else values[todo] + span
precision[todo] = prec
take(pd.to_datetime(text, format="%d/%m/%Y", errors="coerce"), "day")
take(pd.to_datetime(text, format="%Y-%m-%d", errors="coerce"), "day")
for fmt in ("%Y-%m", "%B %Y"):
take(pd.to_datetime(text, format=fmt, errors="coerce"), "month", pd.offsets.MonthEnd(0))
take(pd.to_datetime(text, format="%Y", errors="coerce"), "year", pd.offsets.YearEnd(0))
# timestamps with no offset: the observer's local time
for fmt in ("%Y-%m-%d %H:%M:%S", "%Y-%m-%dT%H:%M", "%Y-%m-%d %H:%M", "%Y/%m/%d %I:%M %p",
"%Y/%m/%d %H:%M %p", "%Y/%m/%d %H:%M", "%Y/%m/%d"):
ts = pd.to_datetime(text, format=fmt, errors="coerce")
take(ts.dt.normalize(), "day")
# timestamps with an offset or a zone name: to UTC, then to the local calendar date
aware = text.replace(ZONES, regex=True)
for fmt in ("%Y-%m-%d %H:%M:%S%z", "%Y-%m-%dT%H:%M:%S%z", "%Y-%m-%d %I:%M:%S %p%z",
"%Y/%m/%d %I:%M %p%z", "%b %d %Y %H:%M:%S%z"):
ts = pd.to_datetime(aware, format=fmt, errors="coerce", utc=True)
take(ts.dt.tz_convert("Europe/London").dt.tz_localize(None).dt.normalize(), "day")
return start, end, precisionThe order is part of the rule: the strict day-first format runs first, so the dominant shape is decided by the rule you chose, and a looser format only ever sees the rows the stricter ones left. A value that matches nothing stays NaT and is counted, not guessed.
Put timestamps in one zone before taking the date
Recorders' apps wrote offsets in four different ways. Two of them defeat the parser:
for text in ["2023-07-30 3:36:23 PM GMT+01:00", "2021/04/17 9:37 PM BST"]:
try:
print(f"{text:34} -> {pd.to_datetime(text)}")
except ValueError as exc:
print(f"{text:34} -> {type(exc).__name__}: {str(exc)[:55]}")2023-07-30 3:36:23 PM GMT+01:00 -> 2023-07-30 15:36:23-01:00
2021/04/17 9:37 PM BST -> ValueError: Parsed string "2021/04/17 9:37 PM BST" included an un-r
The first one is worse than an error. The recorder meant one hour ahead of GMT; dateutil, which pandas falls back to, follows the POSIX convention in which GMT+1 means one hour behind, and returns -01:00 — two hours out. The second simply fails, because a zone abbreviation is not an offset (BST is also Bangladesh Standard Time). So the text is rewritten first, into offsets that mean one thing:
ZONES = {r"\s*\(.*\)$": "", # "(GMT+1)" browser label
r"^[A-Z][a-z]{2} (?=[A-Z][a-z]{2} \d)": "", # "Sun " weekday
r"\s*GMT\s*(?=[+-]\d)": "", # "GMT+01:00" -> "+01:00"
r" GMT$": "+00:00", r" BST$": "+01:00", r" EDT$": "-04:00",
r" CDT$": "-05:00", r" MDT$": "-06:00", r" PDT$": "-07:00"}
examples = pd.Series(["2023-07-30 3:36:23 PM GMT+01:00", "2021/04/17 9:37 PM BST",
"Sat Aug 07 2021 15:11:04 GMT -0700 (PDT)"], dtype="string")
print(examples.replace(ZONES, regex=True).tolist())
start, end, precision = parse_part(examples)
print(start.dt.date.tolist(), precision.tolist())['2023-07-30 3:36:23 PM+01:00', '2021/04/17 9:37 PM+01:00', 'Aug 07 2021 15:11:04-0700']
[datetime.date(2023, 7, 30), datetime.date(2021, 4, 17), datetime.date(2021, 8, 7)] ['day', 'day', 'day']
Converting to Europe/London before taking the date matters for the late-evening sightings — hedgehogs are nocturnal, and a phone set to Pacific time logged 15:11, which was 23:11 in Scotland. Timestamps with no offset at all are iNaturalist's local times and are taken as written.
Keep partial dates and ranges as a window
print(pd.to_datetime("2019-07"), "|", pd.to_datetime("July 2019"))2019-07-01 00:00:00 | 2019-07-01 00:00:00
A parser that is handed a month returns the first of that month, and nothing downstream can tell it apart from a sighting on 1 July. So a partial value keeps a start, an end and a precision, and a range is split into two parts that are each parsed the same way:
def clean_dates(raw):
start, end, precision = parse_part(raw)
halves = raw.where(start.isna()).str.extract(r"^(.+?)\s*-\s*(\S.*)$")
first, _, _ = parse_part(halves[0])
_, last, _ = parse_part(halves[1])
is_range = first.notna() & last.notna()
start[is_range], end[is_range], precision[is_range] = first[is_range], last[is_range], "range"
placeholder = start.dt.year < 1900 # "1200 - 1995": earliest date unknown
start[placeholder] = pd.NaT
return start, end, precision
start, end, precision = clean_dates(raw)
print(precision.value_counts(dropna=False).to_string())
print("unparsed:", raw[precision.isna()].tolist())day 3457
month 278
year 68
range 51
<NA> 4
unparsed: ['Summer 2013', 'Spring 1998', 'Summer 1995', 'Autumn 1994']
Replace placeholder dates, then check the range
print("placeholder starts:", raw[raw.str.startswith(("1200", "01/01/1200"))].tolist())
lo, hi = pd.Timestamp("1900-01-01"), pd.Timestamp.today().normalize()
print("earliest start:", start.min().date(), "| latest end:", end.max().date())
print("outside 1900..today:", int(((start < lo) | (end > hi)).sum()))placeholder starts: ['01/01/1200 - 06/04/2004', '01/01/1200 - 06/04/2004', '1200 - 1995', '1200 - 1996']
earliest start: 1960-01-01 | latest end: 2026-09-28
outside 1900..today: 0
Four records start in the year 1200. That is not a date, it is the recorder's way of writing "some time before": the ends of those windows are real and are kept, while the start becomes NaT, which is what it meant. Nothing parsed lands after today or before 1900, but this is the check that would catch a 9999-12-31, or the 1899-12-30 that an empty Excel cell becomes, if a different source used one.
Look for Excel serial numbers
numeric = pd.to_numeric(raw, errors="coerce")
print("numbers in the column:", numeric.notna().sum(), "| in the Excel serial range:", numeric.between(20000, 60000).sum())
from openpyxl.utils.datetime import from_excel
for serial in (45447, 59, 61):
print(serial, pd.to_datetime(serial, unit="D", origin="1899-12-30").date(), from_excel(serial).date())numbers in the column: 68 | in the Excel serial range: 0
45447 2024-06-04 2024-06-04
59 1900-02-27 1900-02-28
61 1900-03-01 1900-03-01
This column has none — its only numbers are four-digit years — but a column that went through a spreadsheet often does. Counting days from 1899-12-30 is the usual conversion, and it agrees with openpyxl from 1 March 1900 onwards; below serial 61 the two disagree by a day, because Excel counts a 29 February 1900 that never existed.
Check against an independent parse
GBIF parses every record's date itself when it indexes it, and publishes the result as eventDate. That is a second opinion worth having:
day = precision.eq("day") & truth.notna()
print(f"day-precision rows GBIF also dated: {day.sum()}, agree {(start[day] == truth[day]).sum()}")
odd = day & (start != truth)
print(pd.DataFrame({"raw": raw[odd], "ours": start[odd].dt.date, "gbif": gdf["gbif_date"][odd]}).to_string(index=False))
print("GBIF gave no date to", gdf["gbif_date"].isna().sum(), "records; our precision for them:",
precision[gdf["gbif_date"].isna()].value_counts().to_dict())day-precision rows GBIF also dated: 3457, agree 3456
raw ours gbif
2022-04-12 23:50:34+01:00 2022-04-12 2022-04-13T00:50:34
GBIF gave no date to 25 records; our precision for them: {'range': 25}
The one disagreement is GBIF's: a sighting at 23:50 with an offset of +01:00 is 23:50 in Scotland, and GBIF's value is an hour later, on the next day. The records GBIF could not date at all are the multi-year windows, which a single timestamp cannot represent and a start–end pair can.
Store one canonical form
gdf["date_start"], gdf["date_end"], gdf["date_precision"] = start, end, precision
Path("data/clean").mkdir(parents=True, exist_ok=True)
gdf.to_file("data/clean/hedgehogs.gpkg", driver="GPKG")
# one record, plus the Pacific-time sighting's timestamp in Scotland's zone
sample = gdf.head(1).assign(seen_at=pd.Timestamp("2021-08-07 15:11:04-07:00").tz_convert("Europe/London"))
for path in ("data/clean/sample.shp", "data/clean/sample.gpkg", "data/clean/sample.geojson", "data/clean/sample.parquet"):
if path.endswith(".parquet"):
sample.to_parquet(path)
back = gpd.read_parquet(path)
else:
sample.to_file(path)
back = gpd.read_file(path)
print(f"{path.rsplit('.', 1)[1]:8} date_start {str(back['date_start'].dtype):15} "
f"seen_at {str(back['seen_at'].dtype):31} {back['seen_at'].iloc[0]}")shp date_start str seen_at str 2021/08/07 23:11:04+01
gpkg date_start datetime64[ms] seen_at datetime64[ms, UTC+01:00] 2021-08-07 23:11:04+01:00
geojson date_start datetime64[ms] seen_at datetime64[ms, UTC+01:00] 2021-08-07 23:11:04+01:00
parquet date_start datetime64[us] seen_at datetime64[us, Europe/London] 2021-08-07 23:11:04+01:00
Only GeoParquet brings the zone back: GeoPackage and GeoJSON keep the instant and its offset but not the name Europe/London, so arithmetic across a clock change on the read-back column is done in a fixed +01:00. The shapefile returns text — GDAL writes a datetime to a .dbf as a string such as 2021/08/07 23:11:04+01, and reading it back does not turn it into a date again. If a shapefile is required, write an ISO date string column yourself so its contents are at least unambiguous.
Add the derived fields your analysis needs once, rather than re-parsing downstream:
gdf["year"] = gdf["date_start"].dt.year.astype("Int64")
gdf["month"] = gdf["date_start"].where(gdf["date_precision"].eq("day")).dt.month.astype("Int64")
print(gdf[["survey_date", "date_start", "date_end", "date_precision", "year", "month"]].sample(4, random_state=2).to_string(index=False))survey_date date_start date_end date_precision year month
24/04/2011 2011-04-24 2011-04-24 day 2011 4
09/05/1993 1993-05-09 1993-05-09 day 1993 5
26/06/1994 1994-06-26 1994-06-26 day 1994 6
10/06/2011 2011-06-10 2011-06-10 day 2011 6
month is only filled where the precision is a day: a record known only to fall in 2019 has a year but no month, and giving it January would put a hibernating animal in the wrong season.
Cleaning hedgehog sighting dates from GBIF
The data used on this page
Hedgehog (Erinaceus europaeus, GBIF taxon 5219616) occurrences in Scotland from three datasets on GBIF: the TWIC Hedgehog Public Survey (CC BY), the Fife Nature Records Centre Combined Dataset (CC BY) and iNaturalist Research-grade Observations (CC BY-NC). They are requested from the GBIF occurrence API and cached; survey_date is the recorder's verbatimEventDate, gbif_date is GBIF's own parse of it.
import hashlib
import io
import json
import zipfile
from pathlib import Path
import geopandas as gpd
import pandas as pd
import requests
# GBIF adds and revises records daily. So that the numbers below reproduce, every GBIF request on
# this page is answered from the reply it got on 9 October 2026 (one file per request, named by a
# SHA-1 of its URL); any other request, or all of them once these lines are deleted, goes live.
SAVED = zipfile.ZipFile(io.BytesIO(requests.get(
"https://data.spatialworkflow.io/gbif-api-2026-10-09.zip", timeout=300).content))
live_get = requests.get
def saved_get(url, params=None, **kwargs):
prepared = requests.Request("GET", url, params=params).prepare()
try:
body = SAVED.read(hashlib.sha1(prepared.url.encode()).hexdigest()[:16] + ".json")
except KeyError:
return live_get(url, params=params, **kwargs)
reply = requests.models.Response()
reply.status_code, reply._content, reply.url = 200, body, prepared.url
reply.headers["Content-Type"] = "application/json"
return reply
requests.get = saved_get
DATASETS = {"52922205-6477-4999-9d4a-d38999aa32eb": "TWIC",
"c358edb6-9b46-45eb-a173-b854910500a9": "Fife",
"50c9509d-22c7-4a22-a47d-8c48425ef4a7": "iNaturalist"}
cache = Path("data/hedgehogs.json")
if not cache.exists():
cache.parent.mkdir(exist_ok=True)
rows = []
for key, name in DATASETS.items():
offset = 0
while True:
page = requests.get("https://api.gbif.org/v1/occurrence/search", timeout=120, params={
"taxonKey": 5219616, "gadmGid": "GBR.3_1", "hasCoordinate": "true",
"datasetKey": key, "limit": 300, "offset": offset}).json()
rows += [{"gbifID": r["key"], "dataset": name, "verbatimEventDate": r.get("verbatimEventDate"),
"eventDate": r.get("eventDate"), "lon": r["decimalLongitude"], "lat": r["decimalLatitude"]}
for r in page["results"]]
offset += 300
if page["endOfRecords"]:
break
cache.write_text(json.dumps(rows))
records = pd.DataFrame(json.loads(cache.read_text()))
gdf = gpd.GeoDataFrame(
records[["gbifID", "dataset"]].assign(survey_date=records["verbatimEventDate"],
gbif_date=records["eventDate"]),
geometry=gpd.points_from_xy(records["lon"], records["lat"]), crs=4326)
print(len(gdf), "records:", gdf["dataset"].value_counts().to_dict())3858 records: {'TWIC': 2589, 'Fife': 873, 'iNaturalist': 396}
Example 1: a reusable date cleaner with a report
parse_part(), ZONES and clean_dates() above are the whole cleaner. Wrapped with a report, it becomes something a pipeline can log:
def date_report(raw, start, end, precision):
return {"rows": len(raw), "blank": int(raw.isna().sum()),
**{f"precision_{k}": int(v) for k, v in precision.value_counts().items()},
"unparsed": int((precision.isna() & raw.notna()).sum()),
"open_start": int((precision.eq("range") & start.isna()).sum()),
"earliest": str(start.min().date()), "latest": str(end.max().date())}
start, end, precision = clean_dates(gdf["survey_date"].astype("string").str.strip())
for key, value in date_report(raw, start, end, precision).items():
print(f"{key:16} {value}")rows 3858
blank 0
precision_day 3457
precision_month 278
precision_year 68
precision_range 51
unparsed 4
open_start 4
earliest 1960-01-01
latest 2026-09-28
Example 2: quarantine what will not parse
unparsed = gdf.loc[precision.isna() & raw.notna(), ["gbifID", "dataset", "survey_date", "geometry"]]
Path("data/out").mkdir(parents=True, exist_ok=True)
unparsed.to_file("data/out/unparsed_dates.gpkg", driver="GPKG")
print(f"{len(unparsed)} rows quarantined:")
print(unparsed.drop(columns="geometry").to_string(index=False))4 rows quarantined:
gbifID dataset survey_date
1887123317 Fife Summer 2013
6067543483 Fife Spring 1998
6068176419 Fife Summer 1995
6067572888 Fife Autumn 1994
Writing the failures to their own layer keeps the pipeline moving while making sure nobody discovers the gap months later. "Summer 1995" could become a June-to-August window if the project decides what a season means; that is a decision for a person, not a parser.
Example 3: filter by date without inventing precision
year = pd.Timestamp("2019-01-01"), pd.Timestamp("2019-12-31")
exact = gdf["date_precision"].isin(["day", "month"]) & gdf["date_start"].between(*year)
inside = gdf["date_start"].ge(year[0]) & gdf["date_end"].le(year[1])
overlaps = gdf["date_start"].fillna(pd.Timestamp.min).le(year[1]) & gdf["date_end"].ge(year[0])
print(f"2019, day or month precision: {exact.sum()} | whole window inside 2019: {inside.sum()} | window overlaps 2019: {overlaps.sum()}")
by_month = gdf.loc[gdf["date_precision"].eq("day"), "month"].value_counts().sort_index()
print(by_month.to_string())2019, day or month precision: 113 | whole window inside 2019: 117 | window overlaps 2019: 119
month
1 25
2 7
3 100
4 419
5 590
6 489
7 557
8 512
9 398
10 238
11 97
12 25
The three counts answer three different questions, and the difference between them is exactly the records whose date is a window. The month profile is only possible because month-level and year-level records were kept out of it: it shows the winter lull while hedgehogs hibernate — 25, 7 and 25 sightings in January, February and December, against 590 in May.
Example 4: assert date quality in the pipeline
def assert_dates_clean(frame, max_unparsed_pct=1.0):
for col in ("date_start", "date_end"):
if not pd.api.types.is_datetime64_any_dtype(frame[col]):
raise TypeError(f"{col} is {frame[col].dtype}, expected datetime64")
unparsed = frame["date_precision"].isna().mean() * 100
if unparsed > max_unparsed_pct:
raise ValueError(f"{unparsed:.1f}% of dates unparsed (limit {max_unparsed_pct}%)")
backwards = frame["date_start"] > frame["date_end"]
if backwards.any():
raise ValueError(f"{backwards.sum()} windows end before they start")
if (frame["date_end"] > pd.Timestamp.today()).any():
raise ValueError("dates in the future")
print(f"dates ok: {frame['date_precision'].notna().sum()} parsed, "
f"{frame['date_start'].min().date()} to {frame['date_end'].max().date()}")
assert_dates_clean(gdf)dates ok: 3854 parsed, 1960-01-01 to 2026-09-28
A date is a window, not a string
A date is not a string; it is a point or a window on a calendar that can be written in dozens of ways. Every messy value in a raw column is a rendering choice made by some earlier system — a survey form, a phone app, a browser, a records-centre export — and cleaning means recovering the underlying value and then choosing one rendering of your own.
Pandas will happily infer formats, and that convenience is the source of most date bugs. In pandas 2 and 3 inference takes one format from the first value and applies it to the whole column, so the same code gives different results depending on which row happens to come first; format="mixed" switches to per-value inference, which fails in other ways. 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. Counting first and second components over 12 takes three lines and turns an invisible problem into either a clear answer, as here, or a specific question for whoever supplied the file.
Precision is the part most cleaning code throws away. A month, a year and a five-year window are all real information, and each is different from a day. Storing a start, an end and a precision keeps it; turning every value into one timestamp invents it. Darwin Core, the standard GBIF's records follow, does the same thing by allowing an interval such as 2019-06/2019-08 in eventDate.
Placeholders are the other quiet corruption. A year of 1200, or a 1900-01-01, parses perfectly, so it survives every technical check and then distorts every summary: minimum dates, ranges, "years since recorded", time-series plots. It means "unknown", and the honest representation of unknown is NaT.
Finally, storage. A shapefile's .dbf has a Date field with no time, and from GeoPandas a datetime does not even reach it — it is written as text. GeoPackage has Date and DateTime fields and GeoJSON has no date type at all, but GDAL reads ISO 8601 strings in both back as datetimes with their offset; only GeoParquet keeps the pandas dtype, zone name included. Choosing a format with the right ceiling — and writing an ISO string alongside when you must use an older one — keeps the value intact all the way to the consumer.
Excel serials, two-digit years and silent failures
- 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 gone: Older pandas returned the input unchanged, leaving an object column that looked parsed. pandas 3 removed it; on 3.0.6, passing it fails with a bareAssertionError. Usecoerceand count theNaTs.- Sorting text dates is meaningless:
'14/05/1967'sorts after'06/06/2024'because1comes after0. Always convert before sorting or filtering. - Year 1200 needs a wide timestamp: pandas 3 parses it into a microsecond-resolution column, but adding a nanosecond
Timedeltato that column raisesOutOfBoundsDatetime; that is whyparse_part()adds a span only for months and years, as a calendar offset. NaTpropagates: Arithmetic withNaTyieldsNaT, which is correct but means aggregations needdropna()orskipna.- Timezones and shapefiles: Shapefiles cannot store an offset. Convert to one zone and write an ISO string if the time matters.
GMT+1means two things: In a phone's display it is one hour ahead of GMT; in POSIX zone names such asEtc/GMT+1it is one hour behind. Rewrite it to a numeric offset before parsing.- Locale-dependent month names:
%bparsesMarin English but notMär. Pass the values through a mapping first when the source is non-English.
Internal links
- How to Clean and Normalise Attribute Columns in a GeoDataFrame
- How to Handle Missing and Null Values in Spatial Datasets in Python
- How to Validate a GeoDataFrame Against a Schema Before Analysis
- GeoPandas to_file() Fails on a Column Type: How to Fix It
- The Python GIS Data Cleaning Checklist: From Raw Download to Analysis-Ready
- How to Build a Repeatable Data-Cleaning Report in GeoPandas
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?
The dBase Date field has no time component, and from GeoPandas a datetime column does not reach it anyway: GDAL writes it as text such as 2024/06/06 00:00:00, which reads back as a string. Write an ISO 8601 string column yourself so the text is at least unambiguous, or use GeoPackage, which reads back real datetimes.
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.