Dates, times and time series

to_datetime, DatetimeIndex, resample, rolling, shift, asfreq, time zones and business calendars - working with data that moves through time.

Getting real datetimes

import pandas as pd

df["ts"] = pd.to_datetime(df["ts"], errors="coerce", utc=True)
df = df.dropna(subset=["ts"]).set_index("ts").sort_index()

df.index.year, df.index.month, df.index.day_name()   # accessors on the index
df.index.tz                                          # UTC
local = df.tz_convert("Europe/Berlin")               # same instants, local clock
naive = df.tz_localize(None)                         # drop the zone, keep wall time

pd.date_range("2026-01-01", periods=5, freq="D")
pd.bdate_range("2026-01-01", periods=5)              # business days only
pd.to_datetime("2026-03-29 02:30", utc=True)         # an instant, not a wall time

• Parse once, at load time: string dates compare as strings, which is why > "2026-1-2" style filters give nonsense later.
• errors="coerce" turns unparsable values into NaT so you can count them instead of losing the whole load.
• Store UTC and convert only for display. Mixing a tz-aware index with a naive timestamp raises, and the error message rarely points at the real cause.

resample, rolling and shift

daily = df["amount"].resample("D").sum()                 # calendar days
monthly = df["amount"].resample("ME").agg(["sum", "mean", "size"])
weekly = df.resample("W-MON", closed="left", label="left").sum()

sparse = df["amount"].resample("D").sum().asfreq("D")    # explicit grid
gap_filled = sparse.ffill(limit=3)                       # forward fill up to 3 days
zeroed = sparse.fillna(0)

df["ma7"]  = df["amount"].rolling(7, min_periods=1).mean()
df["vol"]  = df["amount"].rolling("7D").std()             # time-based window
df["prev"] = df["amount"].shift(1)
df["delta"] = df["amount"] - df["prev"]
df["next"] = df["amount"].shift(-1)

Worked example: daily signups with a clean week

signups = (
    df[df["event"] == "signup"]
    .set_index("ts")
    .resample("D")["user_id"]
    .count()
    .asfreq("D", fill_value=0)          # days with no events are zero, not missing
)

report = pd.DataFrame({"signups": signups})
report["dow"]  = report.index.day_name()
report["ma7"]  = report["signups"].rolling("7D").mean()
report["wow"]  = report["signups"].pct_change(7).round(3)   # week over week

business = report.asfreq("B").dropna()                      # working days only

The order matters: count first, then reindex onto a regular grid, then compute window statistics. Doing it the other way - filling gaps before counting - invents events that never happened and inflates every average.

FAQ

Related

The index in depth: MultiIndex, set_index and reindex Plotting and quick exploratory charts

Last refreshed 2026-09-18.