Datetime Handling in pandas

Core Principle: Temporal data in pandas is handled via pd.to_datetime() for parsing, the .dt accessor for timestamp component extraction, and DatetimeIndex for temporal alignment and resampling.

Less central to bioinformatics than the other pandas notes, but it appears constantly in sample metadata, sequencing run tracking, clinical timelines and instrument QC.

Parsing

pd.to_datetime(df["date"])
pd.to_datetime(df["date"], format="%Y-%m-%d")        # ← always specify; faster and safer
pd.to_datetime(df["date"], format="mixed")            # heterogeneous formats
pd.to_datetime(df["date"], errors="coerce")           # unparseable → NaT
pd.to_datetime(df["date"], dayfirst=True)             # 01/02/2026 = 1 Feb
pd.to_datetime(df["epoch"], unit="s")

pd.read_csv(path, parse_dates=["collection_date"])

Always pass format=. Without it pandas infers from the first non-null value and applies that guess to everything, which silently mangles ambiguous dates. 01/02/2026 is 1 February in most of the world and 2 January in the US: and if your file mixes both conventions, inference will pick one and be wrong for half the rows with no warning.

errors="coerce" gives NaT (Not a Time) for failures. Count them:

parsed = pd.to_datetime(df["date"], format="%Y-%m-%d", errors="coerce")
print(f"{parsed.isna().sum()} unparseable dates")

pandas 3.0: microsecond default

Datetimes now default to microsecond resolution rather than nanosecond. The practical consequence is good: the old nanosecond backing gave a representable range of only 1677–2262, so historical dates overflowed. That limit is gone.

You can still request a specific unit:

pd.to_datetime(s).astype("datetime64[ns]")
pd.to_datetime(s).astype("datetime64[s]")

The .dt accessor

s.dt.year; s.dt.month; s.dt.day
s.dt.hour; s.dt.minute
s.dt.dayofweek          # Monday = 0
s.dt.day_name(); s.dt.month_name()
s.dt.quarter
s.dt.dayofyear
s.dt.is_month_end
s.dt.date; s.dt.time
s.dt.to_period("M")     # monthly period
s.dt.strftime("%Y-%m")
s.dt.tz_localize("UTC").dt.tz_convert("America/Los_Angeles")
s.dt.normalize()        # midnight: strip the time component

Arithmetic and durations

df["days_since"] = (pd.Timestamp.today() - df["collection_date"]).dt.days
df["turnaround"] = (df["report_date"] - df["received_date"]).dt.days

df["date"] + pd.Timedelta(days=30)
df["date"] + pd.DateOffset(months=1)      # calendar-aware, unlike Timedelta

pd.date_range("2026-01-01", periods=12, freq="MS")   # month starts
pd.date_range("2026-01-01", "2026-12-31", freq="W")

Timedelta(days=30) is exactly 30 × 24 h. DateOffset(months=1) respects month lengths. For "one month later" you want the offset.

DatetimeIndex superpowers

df = df.set_index("date").sort_index()

df.loc["2026"]                    # whole year: partial string indexing
df.loc["2026-03"]                 # whole month
df.loc["2026-03-01":"2026-03-15"] # inclusive range

df.resample("D").mean()           # downsample to daily
df.resample("W").agg({"reads": "sum", "q30": "mean"})
df.resample("ME").size()          # month-end counts
df.asfreq("D")                    # reindex to a regular frequency, NaN for gaps

Partial-string indexing (df.loc["2026-03"]) only works on a DatetimeIndex, and it is the main reason to set one.

resample is groupby for time: same Split-Apply-Combine shape, with time bins as the groups.

Common frequency aliases

Alias	Meaning
D	calendar day
B	business day
W	weekly (Sunday-ended)
MS / ME	month start / month end
QS / QE	quarter start / end
YS / YE	year start / end
h, min, s	hour, minute, second

pandas 3.0 lowercased several of these (H → h, T → min); old uppercase forms warn or error.

Where this shows up in bioinformatics

# sequencing run QC over time: instrument drift
runs = pd.read_csv("runs.tsv", sep="\t", parse_dates=["run_date"])
monthly = runs.set_index("run_date").resample("ME").agg(
    n_runs=("run_id", "size"),
    mean_q30=("pct_q30", "mean"),
    mean_yield=("total_gb", "mean"),
)
sns.lineplot(data=monthly.reset_index(), x="run_date", y="mean_q30")

# batch effects: is a batch confounded with collection date?
samples.groupby("batch")["collection_date"].agg(["min", "max", "size"])

# sample age at collection (careful: DOB is sensitive data)
samples["days_in_storage"] = (samples["extraction_date"] - samples["collection_date"]).dt.days

# clinical timeline
events["days_from_diagnosis"] = (events["event_date"] - events["diagnosis_date"]).dt.days

# turnaround time distribution
sns.histplot(data=cases, x="turnaround_days", bins=30)

The batch/date check is the useful one. If batch 1 was collected in January and batch 2 in June, any "batch effect" you find is confounded with season, storage time, protocol changes and staff turnover. Plotting collection date against batch is a two-line check that saves a retracted conclusion.

Time zones

s.dt.tz_localize("UTC")                       # naive → aware
s.dt.tz_convert("America/Los_Angeles")        # aware → another zone
s.dt.tz_localize(None)                        # aware → naive

You cannot compare or subtract a tz-aware and a tz-naive datetime: pandas raises. Pick one convention (UTC internally is the usual advice) and apply it at load time.

Common mistakes

• No format=, letting inference pick a convention.
• dayfirst ambiguity in international data.
• Not counting NaT after errors="coerce".
• Comparing tz-aware to tz-naive → TypeError.
• Timedelta(days=30) for "a month". Use DateOffset.
• Resampling without sorting the index first.
• Forgetting that partial-string indexing needs a DatetimeIndex.
• Storing dates of birth in an analysis table. It is identifiable information; store age at event instead.

See also

Series · Index Objects · groupby · Window Functions · pandas dtypes · Applied - Quality Control Plots

Exercise

Write storage_report(df) for a sample sheet with columns sample, collected and extracted. Parse both date columns with the explicit format %Y-%m-%d and errors="coerce", so that a date written in another convention becomes NaT instead of being silently reinterpreted. Report the count of rows where either date failed to parse, then drop those rows.

Return unparseable=<n> first. If no rows survive, return that line followed by no complete rows. Otherwise append one line per calendar month of collection, in ascending month order, formatted <YYYY-MM> n=<rows> mean_days=<mean>, where the mean is the number of days between collection and extraction averaged over that month's rows and written to one decimal place.