Pandas
Intermediate 16 min read Pandas

Time Series in Pandas

Time-aware indexing and windows

DatetimeIndex powers resampling (daily→weekly), timezone handling, rolling windows and lag features — the backbone of forecasting and trend analytics.

datetimeresamplerollingshifttimezoneDatetimeIndex
Prerequisites: Indexing and Selection
Before we start

What you will learn

  • Build a DatetimeIndex and parse timestamps
  • Resample with rule and aggfunc
  • Compute rolling means and lag features
Introduction

What is this?

Once your index is a DatetimeIndex, pandas unlocks time-native operations: label slicing by date range, .resample('W') to group by a frequency, .rolling(window).mean() for trailing averages, and .shift(k) to create lagged features. Timezones and calendar-folds are first-class — the essentials of forecasting features.

Motivation

Why do you need it?

  • Sales, servers and users produce time-series data — the majority of business data.
  • Resampling(from minutely to daily) is how noisy telemetry becomes stable metrics.
  • Rolling means smooth outliers; lags (shift) are the raw material of forecasting models.
  • Timezone mishandling quietly shifts aggregates by hours — an invisible data-quality bug.
Think about it

Mental model

Imagine

A calendar you can chop into weeks, add running averages to recent pages, and flip backward to compare this week vs last

Think of

DatetimeIndex, rolling, shift

The index IS the calendar; resample groups by its pages, rolling smears a window, and shift hands you last week's page beside today's.

Mechanics

How it works

  • pd.to_datetime(col) converts values; setting it as index gives df.index a DatetimeIndex.
  • df['2026-01':] slices by label ranges; .loc['2026'] even selects one year.
  • df.resample('W').sum() aggregates to weekly (W,MON alignment to Monday etc).
  • df['x'].rolling(7, min_periods=1).mean() computes trailing 7-window means.
  • df['x_lag1'] = df['x'].shift(1) creates yesterday's value (first row NaN).

Time ops applied.

DatetimeIndex
Slicing
Resample
Rolling
Shifts
Hands-on

Examples

A mini forecast table

A mini forecast table
import pandas as pd
idx = pd.date_range("2026-01-01", periods=14, freq="D")
s = pd.Series(range(14), index=idx)
s7 = s.rolling(7).mean()          # trailing weekly mean (first 6 NaN)
lag = s.shift(1)                   # yesterday
weekly = s.resample("W").sum()     # weekly totals
print(s7.dropna().head(2))
print(lag.head(3).to_list())
print(weekly.head(2).to_list())

One Series, four views: raw daily, smoothed by a 7-day window, yesterday-lagged, and weekly-totaled. These four shapes are what a forecasting dataset needs before any model sees it.

Real world

In production

  • An SRE dashboard resamples per-minute CPU to per-day and rolling 7-day means for trend alerts.
  • A demand forecast builds lags at 1,7,14 days and calendar features (weekday, month), then feeds a gradient boosting model.
  • A billing team converts event timestamps to UTC for consistent daily revenue cuts across timezones.
Guidelines

Best practices

  • Parse to datetime at ingest; keep the raw string for diagnosis.
  • Always align timezones before grouping by day — naive vs aware mixing errors silently.
  • Use min_periods in rolling to control NaN edges.
  • Build features from PREVIOUS data only (shift) to avoid look-ahead leakage.
Watch out

Common mistakes

  • Grouping by string date → alphabetical ordering breaks chronology.
  • Mixing naive and aware datetimes, creating hour-shifted cuts.
  • Using the same-day value as a rolling window feature — look-ahead leakage.
  • resample on a non-datetime index → TypeError.
When things break

Debugging tips

  • df.index.tz and df.index.freq reveal timezone and frequency quality.
  • Verified a weekly resample against a hand-counted week boundary.
  • For lag leakage, assert the strongest feature correlates 0 with train-time future values.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

To create a feature equal to 'yesterday's revenue', use:

2

Rolling windows computed over the full series are safe to use as features.

Go deeper

Official documentation

Time series / date functionality
docs
Resampling API
docs