Pandas Time Series: resample, rolling, time zones & DatetimeIndex

Q: What's the difference between resample and groupby?

resample is a time-based groupby on a DatetimeIndex (or a datetime column via on=): it bins by regular time intervals, and supports upsampling. groupby with pd.Grouper(freq=…) achieves similar downsampling, and can combine time bins with other keys.

Q: What's the difference between tz_localize and tz_convert?

tz_localize attaches a time zone to naive timestamps, declaring what zone they already represent, without changing the clock time. tz_convert changes aware timestamps to another zone, adjusting the clock time while keeping the same instant.

Q: How do I compute a moving average?

Use rolling on a sorted series: s.rolling(7).mean() for 7 rows, or s.rolling("7D").mean() for a 7-day time window on a DatetimeIndex. Use min_periods to control how many observations are required.

Q: How do I handle missing dates in a time series?

Reindex to a complete frequency (asfreq("D"), or reindex(pd.date_range(...))) to expose gaps, then fill appropriately: zeros for counts, forward fill for states, interpolation for continuous measures, or leave NaN when the value is genuinely unknown.

Pandas Time Series

A huge share of real-world data is timestamped: orders, page views, sensor readings, stock prices, logs, and metrics. Pandas was originally built for financial time series, and it remains one of the best tools for them: flexible datetime parsing, DatetimeIndex slicing like df.loc["2026-09"], time zone handling, resampling from events to daily or monthly aggregates, rolling windows for moving averages, and calendar-aware date offsets.

Time series work has classic traps: naive versus time-zone-aware timestamps, daylight saving transitions, gaps and irregular sampling, and look-ahead bias when computing features. Getting the fundamentals right avoids subtle, costly errors in reports and models.

TL;DR

Parse with pd.to_datetime (or parse_dates on load), and set a DatetimeIndex for time-based operations.
Slice by date strings: df.loc["2026-09-01":"2026-09-30"], df.loc["2026"].
Store in UTC, convert for display: tz_localize (attach a zone) vs tz_convert (change zone).
resample("D") changes frequency: aggregate to coarser periods, or upsample and fill to finer ones.
rolling("7D") / rolling(7) for moving windows; expanding() for cumulative stats; ewm for exponential smoothing.
shift, diff, and pct_change create lags and changes. Avoid look-ahead in features.

Quick Example

Core Concepts

Datetime Types

pd.to_datetime parses strings, epochs (unit="s"), and mixed formats. Pass format= for speed and strictness, and errors="coerce" to turn bad values into NaT. The .dt accessor exposes components (.dt.date, .dt.hour, .dt.dayofweek, .dt.month_name()) and operations (.dt.floor("h"), .dt.tz_convert).

DatetimeIndex and Slicing

With a sorted DatetimeIndex, partial string indexing selects periods naturally: df.loc["2026"], df.loc["2026-09"], df.loc["2026-09-01":"2026-09-15"] (inclusive). Also useful are between_time("09:00", "17:00") for intraday filters and at_time. Keep the index sorted, or slicing becomes slow or raises errors.

Time Zones

Naive timestamps have no zone, while aware ones do. Don't mix them in comparisons, since pandas raises.
tz_localize("UTC") declares what zone naive timestamps are in. tz_convert("America/New_York") changes the representation of aware timestamps.
Store and compute in UTC; convert to local zones for business-calendar aggregation ("sales per local day") and display.
Daylight saving time creates non-existent and ambiguous local times. tz_localize has nonexistent= and ambiguous= options for them.

Resampling

resample(rule) groups by time bins, like a time-based groupby:

Downsampling (fine to coarse): resample("h").mean(), resample("W-MON").sum(), resample("MS").agg(...). Common rules: min, h, D, W, MS/ME (month start or end), QS, YS.
Upsampling (coarse to fine): resample("15min").ffill(), or .interpolate(), to fill new slots.
label and closed control bin edges and naming, and origin and offset align bins (for example days starting at 06:00).
asfreq changes frequency without aggregating, which reveals gaps as NaN.

Rolling, Expanding, and EWM Windows

rolling(window): a fixed number of rows (rolling(7)) or a time-based window (rolling("7D")), which handles irregular timestamps correctly. min_periods controls early values, and center=True centers the window.
expanding(): cumulative statistics from the start (running max, running mean).
ewm(span=…) / ewm(halflife="3D", times=…): exponentially weighted averages that emphasize recent data.

These power moving averages, volatility, anomaly thresholds, and smoothing in dashboards. They're the pandas counterpart to SQL window functions.

Shifts, Lags, and Changes

shift(n) moves values down by n periods (lags); shift(-n) looks forward.
shift(freq="1D") shifts the index by time rather than moving values.
diff() gives the difference from the previous value; pct_change() gives relative change.

Date Offsets and Business Calendars

pd.offsets.MonthEnd(), BusinessDay(), CustomBusinessDay(holidays=…), and Week(weekday=0) do calendar-aware arithmetic: ts + pd.offsets.BMonthEnd() moves to the last business day of the month. They're used for financial reporting, billing cycles, and scheduling.

Gaps and Irregular Data

Real data has missing periods and uneven sampling. Detect gaps with asfreq or resample().count(), then decide how to fill them based on meaning: zero for "no sales that day", forward fill for "last known state", interpolation for continuous measurements, or leave NaN when unknown. Filling without thought biases analyses.

Best Practices

Normalize Time Zones at Ingestion

Convert all timestamps to UTC-aware values when loading data, and document it. Mixed or naive timestamps from different sources are a leading cause of off-by-hours bugs.

Use Time-Based Windows for Irregular Data

rolling("7D") respects actual time spans, while rolling(7) counts rows, which may span weeks when data is sparse.

Prevent Look-Ahead Bias

For forecasting and ML features, compute rolling statistics on past data only (shift(1) before rolling), and split train and test sets by time, not randomly. See feature engineering and model evaluation.

Choose the Right Store for Large Time Series

For billions of points, or continuous ingestion, time-series databases (TimescaleDB, ClickHouse, Prometheus for metrics) or Parquet partitioned by date, queried with DuckDB or Polars, scale better than in-memory pandas.

Common Mistakes

Resampling Local Business Days in UTC

Aggregating daily sales for a US store in UTC shifts evening orders into the next day. Convert to the business's time zone before resampling by day.

Unsorted Index

Slicing or resampling an unsorted DatetimeIndex gives wrong results, or errors. Call sort_index() after loading or concatenating.

Filling Gaps With the Wrong Method

Forward-filling revenue invents sales on days that had none. Filling sensor data with zeros creates fake drops. Choose fill methods by semantics.

FAQ

What's the difference between resample and groupby?

resample is a time-based groupby on a DatetimeIndex (or a datetime column via on=): it bins by regular time intervals, and supports upsampling. groupby with pd.Grouper(freq=…) achieves similar downsampling, and can combine time bins with other keys.

What's the difference between tz_localize and tz_convert?

tz_localize attaches a time zone to naive timestamps, declaring what zone they already represent, without changing the clock time. tz_convert changes aware timestamps to another zone, adjusting the clock time while keeping the same instant.

How do I compute a moving average?

Use rolling on a sorted series: s.rolling(7).mean() for 7 rows, or s.rolling("7D").mean() for a 7-day time window on a DatetimeIndex. Use min_periods to control how many observations are required.

How do I handle missing dates in a time series?

Reindex to a complete frequency (asfreq("D"), or reindex(pd.date_range(...))) to expose gaps, then fill appropriately: zeros for counts, forward fill for states, interpolation for continuous measures, or leave NaN when the value is genuinely unknown.