Time Series: Filtering, Resampling & Rolling
Use a DatetimeIndex to slice data by date strings, aggregate to weekly or monthly totals with resample(), and smooth data with rolling windows.
Data that is recorded over time — runs per match, ticket sales per day, wickets per over — is a time series. Questions about it are time questions: runs in April only, total runs per week, average of the last 3 matches (current form). When the dates are in the index, pandas makes these one-liners: date-string slicing, resample() and rolling().
Definition
A time series in pandas is a Series or DataFrame whose index is a
DatetimeIndex.resample(rule)groups rows into regular time bins (day, week, month…) and aggregates each bin — a groupby on time.rolling(window)computes a statistic over a moving window of the last n rows (or the last n days), giving one value per row.
- DatetimeIndex — set it with
df.set_index("date")(afterpd.to_datetime). - Regular time bins —
"W"= weekly bins ending on Sunday,"ME"= calendar months; bins with no data still appear. - Moving window — the window slides one row at a time: rows 1–3, then 2–4, then 3–5…
Syntax
python
df = df.set_index("date").sort_index()
df.loc["2024-04"] # all rows in April 2024
df.loc["2024-04-01":"2024-04-15"] # date range (both ends included)
df.resample(rule).sum() # or .mean(), .count(), .max(), .agg([...])
s.rolling(window, min_periods=None).mean()
s.shift(periods=1)| Parameter | Meaning | Default |
|---|---|---|
rule (resample) | bin size: "D", "W", "ME", "QE", "YE", "h", "2W" … | required |
on (resample) | resample by a datetime column instead of the index | None |
window (rolling) | number of rows (3) or a time span ("7D", needs a DatetimeIndex) | required |
min_periods (rolling) | minimum values needed in a window to produce a result (otherwise NaN) | = window for integer windows |
periods (shift) | how many rows to move the values down (negative = up) | 1 |
Example — make a DatetimeIndex
python
virat = pd.DataFrame({
"date": pd.to_datetime(["2024-03-22", "2024-03-25", "2024-03-29", "2024-04-02",
"2024-04-06", "2024-04-11", "2024-04-15", "2024-04-21",
"2024-04-25", "2024-05-12"]),
"runs": [21, 77, 83, 22, 113, 3, 6, 18, 51, 42],
})
virat = virat.set_index("date").sort_index()
print(virat.index)text
DatetimeIndex(['2024-03-22', '2024-03-25', '2024-03-29', '2024-04-02',
'2024-04-06', '2024-04-11', '2024-04-15', '2024-04-21',
'2024-04-25', '2024-05-12'],
dtype='datetime64[us]', name='date', freq=None)Example — slicing by date strings
python
print(virat.loc["2024-04"])
print(virat.loc["2024-03-25":"2024-04-06"])
print(virat[virat.index.day_name() == "Saturday"])text
runs
date
2024-04-02 22
2024-04-06 113
2024-04-11 3
2024-04-15 6
2024-04-21 18
2024-04-25 51
runs
date
2024-03-25 77
2024-03-29 83
2024-04-02 22
2024-04-06 113
runs
date
2024-04-06 113How it works: with a DatetimeIndex, a partial date string like "2024-04" selects the whole month (partial string indexing). A slice of date strings includes both ends (it is label-based, like all .loc slices). The index itself has .day_name(), .month etc.
Example — resample: weekly and monthly totals
python
print(virat.resample("ME")["runs"].agg(["count", "sum", "mean"]))
print(virat.resample("W")["runs"].sum())text
count sum mean
date
2024-03-31 3 181 60.333333
2024-04-30 6 213 35.500000
2024-05-31 1 42 42.000000
date
2024-03-24 21
2024-03-31 160
2024-04-07 135
2024-04-14 3
2024-04-21 24
2024-04-28 51
2024-05-05 0
2024-05-12 42
Freq: W-SUN, Name: runs, dtype: int64How it works: resample("ME") puts every row into its calendar month and labels the bin with the month-end date; agg computes several statistics per bin. Weekly bins ("W") end on Sundays — and the week ending 5 May, when no match was played, still appears with a sum of 0. That is exactly what you want for a timeline (a groupby on week numbers would silently skip it).
text
dates: 22/3 25/3 29/3 | 2/4 6/4 11/4 15/4 21/4 25/4 | 12/5
bins: ───── March ─────|──────────── April ────────────|─ May ─
sum: 181 | 213 | 42Example — resample on a column with on=
python
sales = pd.DataFrame({
"time": pd.date_range("2024-03-22 18:00", periods=6, freq="30min"),
"tickets": [120, 340, 560, 410, 220, 90],
})
print(sales.resample("h", on="time")["tickets"].sum())text
time
2024-03-22 18:00:00 460
2024-03-22 19:00:00 970
2024-03-22 20:00:00 310
Freq: h, Name: tickets, dtype: int64on="time" resamples by a column, so you don't need set_index. Here the 30-minute ticket counts are summed into hourly bins.
Example — rolling windows: current form
python
form = virat["runs"].to_frame()
form["last3_avg"] = virat["runs"].rolling(3).mean().round(1)
form["last3_avg_min1"] = virat["runs"].rolling(3, min_periods=1).mean().round(1)
form["prev_match"] = virat["runs"].shift(1)
print(form)text
runs last3_avg last3_avg_min1 prev_match
date
2024-03-22 21 NaN 21.0 NaN
2024-03-25 77 NaN 49.0 21.0
2024-03-29 83 60.3 60.3 77.0
2024-04-02 22 60.7 60.7 83.0
2024-04-06 113 72.7 72.7 22.0
2024-04-11 3 46.0 46.0 113.0
2024-04-15 6 40.7 40.7 3.0
2024-04-21 18 9.0 9.0 6.0
2024-04-25 51 25.0 25.0 18.0
2024-05-12 42 37.0 37.0 51.0How it works:
rolling(3).mean()averages the current row and the 2 before it. The first two rows have fewer than 3 values →NaN.min_periods=1produces a value as soon as at least 1 value is available.shift(1)moves values down one row — "previous match" — handy for change calculations likeruns - runs.shift(1).
Example — time-based window
python
print(virat["runs"].rolling("7D").sum())text
date
2024-03-22 21.0
2024-03-25 98.0
2024-03-29 160.0
2024-04-02 105.0
2024-04-06 135.0
2024-04-11 116.0
2024-04-15 9.0
2024-04-21 24.0
2024-04-25 69.0
2024-05-12 42.0
Name: runs, dtype: float64"7D" means "all matches in the last 7 days up to this date", however many that is — useful when matches are irregularly spaced. (Time windows need a sorted DatetimeIndex.)
Comparison — groupby vs resample vs rolling
| Tool | Output size | Question it answers |
|---|---|---|
groupby("team") | one row per group | totals per category |
resample("ME") | one row per time bin | totals per week/month (with empty bins) |
rolling(3) | same as input | moving average / recent form |
shift(1) | same as input | previous value, change since last |
Common mistakes
- ❌
df.resample("ME")when the date is a normal column →TypeError: Only valid with DatetimeIndex…. ✅set_index("date")or useon="date". - ❌ Slicing an unsorted DatetimeIndex. ✅
sort_index()first. - ❌
resample("M")in pandas 3 →ValueError. ✅"ME". - ❌ Forgetting the aggregation:
df.resample("W")alone is just a resampler object. ✅ Add.sum(),.mean(), etc. - ❌ Surprise at leading
NaNs fromrolling(3). ✅ That is by design; usemin_periods.
Interview tip
Q: What is the difference between resample and rolling?
A: resample changes the frequency — it groups rows into fixed time bins and returns one row per bin. rolling keeps the original rows and computes a statistic over a sliding window ending at each row.
Key points to remember
- Set a DatetimeIndex with set_index('date') (after to_datetime) and sort_index().
- Partial date strings select ranges: df.loc['2024-04'] is all of April; slices include both ends.
- resample('W'/'ME'/'h') groups rows into time bins; always follow it with an aggregation like .sum() or .mean().
- Empty bins still appear in resample results (sum 0 / mean NaN) — good for timelines.
- resample(..., on='col') works on a datetime column without setting the index.
- rolling(3).mean() is a moving average of the last 3 rows; rolling('7D') uses a time window.
- shift(1) gives the previous row's value; diff() gives the change.
Try it yourself
Slice Virat's season by month and dates
Monthly and weekly totals with resample
Practice: rolling 3-match form
Revision Flashcards
Tap a card to flip it and see the answer.