SyntaxPilot
Courses/Data Analytics with Python/Lesson 28

Data Analytics with Python · Lesson 28 of 90 · 16 min

Time series in pandas: resample, rolling and shift

DatetimeIndexdate_rangefrequency aliases (D, W, ME, QE)resampleasfrequpsampling vs downsamplingrolling windowrolling meanexpandingshiftlagdiffpct_changeewm (exponentially weighted mean)

These are the notes for Data Analytics with Python, episode 28. Almost every business number comes with a date: sales per day, users per hour, temperature per minute. Here you invent the tools that answer the questions every manager asks about such numbers: what was the weekly total?, what is the trend under the noise? and are we up or down compared with yesterday and last week? You meet the DatetimeIndex, date_range, frequency aliases (D, W, ME, QE), resample for downsampling and upsampling (with asfreq), rolling windows, the rolling mean, expanding, shift and lags, diff, pct_change and the exponentially weighted mean (ewm). Every term is defined the first time it appears and collected in the Glossary. Every program below was run for real (Python 3.13, pandas 3.0.5, NumPy 2.5.3); outputs are unedited.

The problem: a daily log

Ramesh writes down how many cups of chai his stall sells every day: 60 days, all of January and February 2026 (the numbers are generated by code, made up for teaching). The stall owner asks three questions:

  1. What was the total each week and each month?
  2. Are we really growing, or was last Saturday just lucky?
  3. How did today compare with yesterday and with the same day last week?

Each question groups or compares days by time. A time series is simply a list of values in time order, each with a timestamp: one number per day, per hour or per minute.

The data used in every example (the first lines of each program):

import sys; sys.tracebacklimit = 0
import pandas as pd, numpy as np
rng = np.random.default_rng(7)
days = pd.date_range("2026-01-01", periods=60, freq="D")
cups = 200 + 2 * np.arange(60) + np.where(days.dayofweek >= 5, 80, 0) + rng.integers(-25, 26, 60)
s = pd.Series(cups, index=days, name="cups")
  • pd.date_range makes 60 dates, one per day (explained below).
  • The cups grow by 2 per day (2 * np.arange(60)), weekends (dayofweek >= 5 means Saturday or Sunday; Monday is 0) sell 80 extra, and rng.integers(-25, 26, 60) adds random noise between -25 and +25 (the seeded random generator of episode 10, so you get the same numbers).
  • The Series uses the dates as its index.
print(s.head(10))
print(len(s), s.index.dtype)
2026-01-01    223
2026-01-02    208
2026-01-03    293
2026-01-04    306
2026-01-05    212
2026-01-06    224
2026-01-07    229
2026-01-08    200
2026-01-09    193
2026-01-10    288
Freq: D, Name: cups, dtype: int64
60 datetime64[us]

The weekend jump is visible (3 and 4 January, a Saturday and Sunday). The index type is datetime64[us]: pandas stores each date as a real moment in time (counted in microseconds, us), not as text. Freq: D says the rows are one day apart.

DatetimeIndex: telling pandas a column is time

Real data usually arrives as text: a CSV has a date column full of strings. The obvious attempt is to call resample (the tool for grouping by time, below) on that table:

df = pd.DataFrame({"date": ["2026-01-01", "2026-01-02", "2026-01-03"],
                   "cups": [210, 190, 290]})
print(df.resample("W")["cups"].sum())
TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'RangeIndex'

Why it fails: resample reads the time from the index, and this table's index is a RangeIndex (just 0, 1, 2). The dates are only text in a column, so pandas cannot know that "2026-01-02" comes one day after "2026-01-01".

The fix: convert the text to timestamps with pd.to_datetime (episode 22) and make that column the index with set_index:

df = pd.DataFrame({"date": ["2026-01-01", "2026-01-02", "2026-01-03"],
                   "cups": [210, 190, 290]})
df["date"] = pd.to_datetime(df["date"])
df = df.set_index("date")
print(df.index)
DatetimeIndex(['2026-01-01', '2026-01-02', '2026-01-03'], dtype='datetime64[us]', name='date', freq=None)

A DatetimeIndex is an index whose labels are timestamps. It unlocks every time-series tool in pandas: resample, time-based slicing, rolling("7D") and more. freq=None means pandas has not been told the spacing between rows (it can still work with irregular dates).

Shortcut: resample also accepts the column directly, without changing the index: df.resample("W", on="date") (see practice question 4).

Frequency aliases and date_range

A frequency alias is a short code for a spacing in time:

Alias Meaning Label placed on
D calendar day the day
W (= W-SUN) week ending Sunday the Sunday
ME month end last day of the month
MS month start first day of the month
QE quarter end (3 months) last day of the quarter
YE year end 31 December
h hour the hour
min minute the minute

pd.date_range(start, periods=n, freq=...) builds a DatetimeIndex of n evenly spaced timestamps (you can also give end= instead of periods=):

print(pd.date_range("2026-01-01", periods=4, freq="D"))
print(pd.date_range("2026-01-01", periods=3, freq="W"))
print(pd.date_range("2026-01-01", periods=3, freq="ME"))
DatetimeIndex(['2026-01-01', '2026-01-02', '2026-01-03', '2026-01-04'], dtype='datetime64[us]', freq='D')
DatetimeIndex(['2026-01-04', '2026-01-11', '2026-01-18'], dtype='datetime64[us]', freq='W-SUN')
DatetimeIndex(['2026-01-31', '2026-02-28', '2026-03-31'], dtype='datetime64[us]', freq='ME')

The weekly range jumps to 4 January because a W period ends on Sunday and 1 January 2026 is a Thursday. In pandas 3 the old aliases M, Q and Y no longer work; use ME, QE, YE (error shown below).

resample: downsampling days into weeks and months

Real-life picture: Ramesh drops one coin a day into a row (a daily series). Then he puts out one piggy bank per week and drops each coin into the bank for its week (resample("W") puts each row into a time bucket based on its date). Finally he counts each bank (.sum(), one total per week).

resample is groupby for time: it splits rows into time periods, applies an aggregation, and combines the results. Going from many small periods to fewer big ones (day → week) is called downsampling.

print(s.resample("W").sum().head(4))
print(s.resample("ME").sum())
2026-01-04    1030
2026-01-11    1635
2026-01-18    1783
2026-01-25    1831
Freq: W-SUN, Name: cups, dtype: int64
2026-01-31    7901
2026-02-28    8824
2026-03-31     399
Freq: ME, Name: cups, dtype: int64

The partial-period trap. Did sales crash in March? No: the data ends on 1 March, so the March bucket holds one day. Likewise week 1 has only four days (Thursday 1 January to Sunday 4 January). The first and last buckets of a range are often partial periods. Check how many rows each bucket really has, for example with s.resample("ME").count(), before comparing totals.

The old alias fails in pandas 3:

print(s.resample("M").sum())
ValueError: 'M' is no longer supported for offsets. Please use 'ME' instead.

During handling of the above exception, another exception occurred:

ValueError: Invalid frequency: M. Failed to parse with error message: ValueError("'M' is no longer supported for offsets. Please use 'ME' instead.")

resample works with agg like groupby (episode 24), giving several summaries at once:

print(s.resample("ME").agg(["sum", "mean", "max"]))
             sum        mean  max
2026-01-31  7901  254.870968  344
2026-02-28  8824  315.142857  408
2026-03-31   399  399.000000  399

An average February day sold 315 cups versus 255 in January. When periods have different lengths (months have 28 to 31 days), compare means, not totals.

Upsampling: months into days

Going the other way, from big periods to smaller ones (month → day), is upsampling. The owner sets monthly targets (7,500 cups for January, 8,400 for February) and Ramesh wants a target for every day.

target = pd.Series([7500, 8400], name="target",
                   index=pd.to_datetime(["2026-01-01", "2026-02-01"]))
print(target.resample("D").asfreq().head(4))
print(target.resample("D").ffill().head(4))
2026-01-01    7500.0
2026-01-02       NaN
2026-01-03       NaN
2026-01-04       NaN
Freq: D, Name: target, dtype: float64
2026-01-01    7500
2026-01-02    7500
2026-01-03    7500
2026-01-04    7500
Freq: D, Name: target, dtype: int64
  • asfreq() creates the new rows at the new frequency and leaves them empty (NaN, "not a number", pandas' marker for a missing value). It never invents data.
  • ffill() (forward fill, episode 19) copies the last known value forward, so every January day gets 7,500.
  • Other options: bfill() (copy the next value backwards) or interpolate() (draw a straight line between known values).

Note that the result ends on 1 February: upsampling only fills between the first and last timestamps you have.

Rolling windows: smoothing noisy days

Daily numbers are noisy: weekends spike, weekdays dip, and random noise sits on top, so comparing a Saturday with a Monday says nothing about growth. Idea: for each day, take the average of the last 7 days. Every 7-day window contains exactly one weekend, so the weekend effect cancels out. Then slide the window forward one day and repeat. This is a moving average (also called a rolling mean).

A rolling window is a fixed-size group of consecutive rows that slides along the series one row at a time. s.rolling(7) builds it; .mean() (or .sum(), .max(), .std()…) summarises each window.

r = s.rolling(7).mean()
print(pd.DataFrame({"cups": s, "avg7": r.round(1)}).head(9))
            cups   avg7
2026-01-01   223    NaN
2026-01-02   208    NaN
2026-01-03   293    NaN
2026-01-04   306    NaN
2026-01-05   212    NaN
2026-01-06   224    NaN
2026-01-07   229  242.1
2026-01-08   200  238.9
2026-01-09   193  236.7

The first 6 values are NaN because a 7-row window needs 7 values. From 7 January, each value is the mean of that day and the 6 days before it: (223 + 208 + 293 + 306 + 212 + 224 + 229) / 7 = 242.1.

  • min_periods=1 lets the window start with fewer rows, so you get a value from day one.
  • expanding() is a window that never slides: it grows from the first row to the current row. expanding().max() is the best day so far (a running record); expanding().mean() is the running average.
print(s.rolling(7, min_periods=1).mean().round(1).head(3))
print(s.expanding().max().head(5))
2026-01-01    223.0
2026-01-02    215.5
2026-01-03    241.3
Freq: D, Name: cups, dtype: float64
2026-01-01    223.0
2026-01-02    223.0
2026-01-03    293.0
2026-01-04    306.0
2026-01-05    306.0
Freq: D, Name: cups, dtype: float64

With a DatetimeIndex you can also use a time-based window: s.rolling("7D").mean() uses "the last 7 calendar days", which handles missing dates correctly (it starts giving values from day one, like min_periods=1).

The picture (the code that drew it is below; charts start properly in episode 30):

Daily cups, 7-day rolling mean and ewm

import pandas as pd, numpy as np
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
rng = np.random.default_rng(7)
days = pd.date_range("2026-01-01", periods=60, freq="D")
cups = 200 + 2 * np.arange(60) + np.where(days.dayofweek >= 5, 80, 0) + rng.integers(-25, 26, 60)
s = pd.Series(cups, index=days, name="cups")
plt.rcParams["font.size"] = 15
fig, ax = plt.subplots(figsize=(13, 5.2))
ax.plot(s.index, s, color="0.65", lw=1.5, label="daily cups")
ax.plot(s.index, s.rolling(7).mean(), color="tab:blue", lw=3, label="rolling(7).mean()")
ax.plot(s.index, s.ewm(span=7).mean(), color="tab:orange", lw=3, label="ewm(span=7).mean()")
import matplotlib.dates as mdates
ax.xaxis.set_major_formatter(mdates.DateFormatter("%d %b"))
ax.set_xlabel("date"); ax.set_ylabel("cups sold"); ax.legend(loc="upper left"); ax.grid(alpha=0.3)
plt.savefig("smooth.png", dpi=110, bbox_inches="tight")
print("saved smooth.png")
saved smooth.png

The grey daily line spikes every weekend; the blue rolling mean removes the spikes and shows a steady climb: the stall is growing.

shift, lags, diff and pct_change

Question: how did today compare with yesterday? The obvious attempt is "the series from day 2 on, minus the series up to the second-last day":

print((s[1:] - s[:-1]).head(4))
2026-01-01    NaN
2026-01-02    0.0
2026-01-03    0.0
2026-01-04    0.0
Freq: D, Name: cups, dtype: float64

Why it fails: index alignment (episode 11). pandas subtracts values with the same label, not the same position. 2 January is subtracted from 2 January, so every difference is 0, and the two ends become NaN. Slicing moved the rows but not their labels.

The fix: move the values down instead.

  • shift(1) pushes every value down one row, so each date holds the previous day's value. A past value placed next to the present value is called a lag (a lag of 1 day here).
  • diff() = value minus the previous value (s - s.shift(1)).
  • pct_change() = (value − previous) / previous: the percentage change as a fraction (multiply by 100 for %).
d = pd.DataFrame({"cups": s})
d["yesterday"] = d["cups"].shift(1)
d["change"] = d["cups"].diff()
d["pct"] = (d["cups"].pct_change() * 100).round(1)
print(d.head(4))
            cups  yesterday  change   pct
2026-01-01   223        NaN     NaN   NaN
2026-01-02   208      223.0   -15.0  -6.7
2026-01-03   293      208.0    85.0  40.9
2026-01-04   306      293.0    13.0   4.4

2 January: 208 vs 223 is a change of −15, or −6.7 %. 3 January (a Saturday) is +85, +40.9 %. The first row is NaN because there is no day before the first day. shift(-1) moves values up (a lead: tomorrow's value).

Week over week (WoW). Comparing a Saturday with a Friday mixes in the weekend effect. The fair comparison is the same weekday last week: shift(7).

d = pd.DataFrame({"cups": s})
d["last_week"] = d["cups"].shift(7)
d["wow"] = d["cups"] - d["last_week"]
print(d.iloc[7:11])
            cups  last_week   wow
2026-01-08   200      223.0 -23.0
2026-01-09   193      208.0 -15.0
2026-01-10   288      293.0  -5.0
2026-01-11   289      306.0 -17.0

Saturday 10 January sold 288 cups versus 293 the Saturday before: −5. Year over year (YoY) uses the same idea with a longer lag, for example shift(12) on monthly data or pct_change(12).

ewm: recent days count more

A rolling mean gives each of the last 7 days the same weight (1/7), and a day drops out completely when it leaves the window. Real-life picture: a teacher who grades you on your last 7 tests with equal weight reacts slowly when you suddenly improve; a teacher who gives recent tests more weight, and lets older ones fade gradually, reacts faster.

The exponentially weighted mean (ewm, short for exponentially weighted moving) gives every past value a weight that shrinks by the same factor with each step back in time. span=7 sets the speed of fading so it behaves roughly like a 7-day window (the smoothing factor is α = 2 / (span + 1) = 0.25: with each step back in time, a value's weight is multiplied by 1 − α = 0.75).

print(pd.DataFrame({"cups": s,
                    "roll7": s.rolling(7).mean().round(1),
                    "ewm7": s.ewm(span=7).mean().round(1)}).tail(4))
            cups  roll7   ewm7
2026-02-26   328  337.1  328.5
2026-02-27   321  342.7  326.6
2026-02-28   393  344.1  343.2
2026-03-01   399  342.9  357.2

On 1 March (a strong Sunday at 399) the ewm jumps to 357.2 while the rolling mean barely moves (342.9). Analysts use ewm for trend lines and alerts that must react quickly; the plain rolling mean is easier to explain to a manager ("the average of the last 7 days").

Bonus: selecting by date text

With a DatetimeIndex, loc understands date strings: a month string selects the whole month (partial string indexing), and a date slice includes both ends.

print(s.loc["2026-02"].sum())
print(s.loc["2026-01-10":"2026-01-12"].tolist())
8824
[288, 289, 241]

Mini project: weekly_report()

A function that turns any daily series into a weekly report: totals, week-over-week growth in %, and a 4-week rolling average for the trend.

def weekly_report(daily):
    w = daily.resample("W").sum()
    return pd.DataFrame({"cups": w,
                         "growth_%": (w.pct_change() * 100).round(1),
                         "avg4": w.rolling(4).mean().round(0)})
print(weekly_report(s))
            cups  growth_%    avg4
2026-01-04  1030       NaN     NaN
2026-01-11  1635      58.7     NaN
2026-01-18  1783       9.1     NaN
2026-01-25  1831       2.7  1570.0
2026-02-01  1967       7.4  1804.0
2026-02-08  2114       7.5  1924.0
2026-02-15  2181       3.2  2023.0
2026-02-22  2183       0.1  2111.0
2026-03-01  2400       9.9  2220.0

Week 2 shows +58.7 % growth, but that is the partial-period trap: week 1 had only 4 days. After that, growth is a steady 0 to 10 % a week and the 4-week average climbs from 1,570 to 2,220. In a real report, drop or flag partial weeks.

Common mistakes

Code / habit Real error / symptom Why Fix
df.resample("W") with dates as text in a column TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'RangeIndex' resample reads time from the index pd.to_datetime + set_index, or resample("W", on="date")
s.resample("M") ValueError: Invalid frequency: M. Failed to parse with error message: ValueError("'M' is no longer supported for offsets. Please use 'ME' instead.") old alias removed in pandas 3 "ME" (and "QE", "YE")
s[1:] - s[:-1] no error; every change is 0 pandas aligns on labels s.diff() or s - s.shift(1)
comparing a partial first/last week or month no error; fake crash or fake growth the bucket has fewer days compare means, check .count(), drop partial periods
rolling(7) and expecting values from day 1 first 6 values are NaN the window needs 7 values min_periods=1, or accept the NaNs
comparing Saturday with Friday no error; weekend effect looks like growth different weekdays shift(7) (week over week) or a 7-day rolling mean
summing an upsampled target totals multiplied by ~30 ffill copied the monthly value to every day divide by days in the month, or don't sum upsampled values

Practice

All questions use this daily website-visitor series (14 days from Monday 2 March 2026):

import sys; sys.tracebacklimit = 0
import pandas as pd
v = pd.Series([120, 135, 128, 150, 210, 260, 240, 130, 142, 139, 165, 230, 275, 255],
              index=pd.date_range("2026-03-02", periods=14, freq="D"), name="visitors")

1. Weekly totals.

Solution
print(v.resample("W").sum())
2026-03-08    1243
2026-03-15    1336
Freq: W-SUN, Name: visitors, dtype: int64

Both weeks are complete (Monday to Sunday), so the totals are comparable.

2. A 3-day rolling mean, rounded to 1 decimal (first 5 rows).

Solution
print(v.rolling(3).mean().round(1).head(5))
2026-03-02      NaN
2026-03-03      NaN
2026-03-04    127.7
2026-03-05    137.7
2026-03-06    162.7
Freq: D, Name: visitors, dtype: float64

3. Day-over-day % change, and the day with the biggest % increase.

Solution
pc = (v.pct_change() * 100).round(1)
print(pc.head(4))
print(pc.idxmax(), pc.max())
2026-03-02     NaN
2026-03-03    12.5
2026-03-04    -5.2
2026-03-05    17.2
Freq: D, Name: visitors, dtype: float64
2026-03-06 00:00:00 40.0

idxmax returns the label (here a date) of the largest value: Friday 6 March, +40 %.

4. Weekly totals from a table whose dates are a text column, without changing the index.

Solution
df = pd.DataFrame({"date": ["2026-03-02", "2026-03-03", "2026-03-09"], "visitors": [120, 135, 130]})
df["date"] = pd.to_datetime(df["date"])
print(df.resample("W", on="date")["visitors"].sum())
date
2026-03-08    255
2026-03-15    130
Freq: W-SUN, Name: visitors, dtype: int64

on="date" tells resample which column holds the time; it still has to be a datetime column.

5. Week-over-week change of the weekly totals, two ways.

Solution
w = v.resample("W").sum()
print((w - w.shift(1)).tolist(), w.diff().tolist())
[nan, 93.0] [nan, 93.0]

Quick quiz

s = pd.Series([10, 20, 30, 40])
print(s.rolling(3).mean().tolist())

A. [10.0, 15.0, 20.0, 30.0] B. [nan, nan, 20.0, 30.0] C. [20.0, 30.0] D. [nan, 15.0, 20.0, 30.0]

Answer

B. A 3-row window needs 3 values, so the first two are NaN; then (10+20+30)/3 = 20 and (20+30+40)/3 = 30. The result keeps the original length so it lines up with the original index.

Interview questions

  1. What is a DatetimeIndex and why do you need one? An index of timestamps; it enables resample, date-string slicing and time-based rolling windows.
  2. resample vs groupby? resample is a groupby on time periods of the index (or an on= column), with calendar-aware buckets like weeks and month ends.
  3. Downsampling vs upsampling? Downsampling goes to a coarser frequency and needs an aggregation (sum, mean); upsampling goes to a finer frequency and creates empty rows to fill (asfreq, ffill, interpolate).
  4. Why is the first value of rolling(7).mean() NaN? The window needs 7 values; use min_periods to allow fewer.
  5. rolling vs expanding vs ewm? Fixed-size sliding window; window from the start to now; all history with exponentially fading weights.
  6. shift vs diff vs pct_change? shift moves values (creates lags/leads); diff = value − lagged value; pct_change = that difference divided by the lagged value.
  7. Why can't you write s[1:] - s[:-1] for daily change? pandas aligns on index labels, so each date is subtracted from itself.
  8. How do you compute week-over-week change on daily data? s - s.shift(7) (or s.pct_change(7)), comparing the same weekday.
  9. What is the partial-period problem? The first/last bucket of a resample covers fewer days, which creates fake drops or growth; check counts or compare means.
  10. What replaced the M alias in pandas 3? ME (month end); likewise QE, YE; MS is month start.

Glossary

Term Meaning Example
Time series Values in time order, each with a timestamp cups sold per day
Timestamp One specific moment in time 2026-01-01 00:00:00
datetime64 pandas/NumPy type that stores moments in time datetime64[us]
DatetimeIndex An index whose labels are timestamps s.index here
RangeIndex The default index 0, 1, 2, … a freshly read CSV
pd.to_datetime Converts text to timestamps "2026-01-02" → Timestamp
set_index Makes a column the index df.set_index("date")
date_range Builds evenly spaced timestamps 60 days from 1 Jan
Frequency The spacing between timestamps daily, weekly
Frequency alias Short code for a frequency D, W, ME, QE, YE, h, min
W-SUN Weeks ending on Sunday (the default W) label = the Sunday
ME / MS Month end / month start 31 Jan / 1 Jan
QE Quarter end (every 3 months) 31 Mar
resample Group rows into time buckets, then aggregate s.resample("W").sum()
Downsampling Going to fewer, bigger periods days → weeks
Upsampling Going to more, smaller periods months → days
asfreq Change frequency without filling (NaN for new rows) monthly target → daily NaNs
Forward fill (ffill) Copy the last known value forward 7,500 every January day
Partial period A bucket that covers fewer days than normal March with 1 day
NaN "Not a number": pandas' missing-value marker first rows of a rolling mean
Rolling window A fixed number of consecutive rows sliding along 7 days
Rolling mean / moving average Mean of each rolling window s.rolling(7).mean()
min_periods Minimum rows a window needs to give a value min_periods=1
expanding A window from the first row to the current row running max
Lag A past value placed next to the present yesterday's cups
Lead A future value placed next to the present shift(-1)
shift Move values down (or up) by n rows shift(1), shift(7)
diff Value minus the previous value +85 cups
pct_change Change divided by the previous value 0.409 = +40.9 %
Week over week (WoW) Change vs the same day/week one week earlier s - s.shift(7)
Year over year (YoY) Change vs the same period one year earlier pct_change(12) on months
Exponentially weighted mean (ewm) Average where older values' weights fade by a constant factor s.ewm(span=7).mean()
span Sets how fast ewm weights fade (α = 2/(span+1)) span=7 → α = 0.25
Index alignment pandas matches values by label in arithmetic s[1:] - s[:-1] = 0s
Partial string indexing Selecting by a date string like "2026-02" s.loc["2026-02"]
idxmax Label of the largest value the best day

Summary

  • A time series needs a DatetimeIndex: pd.to_datetime + set_index (or resample(..., on=)).
  • Frequency aliases describe spacing: D, W, ME (not M in pandas 3), QE, YE.
  • resample groups by time: downsample with an aggregation, upsample with asfreq + ffill/interpolate. Watch for partial periods.
  • rolling(n).mean() smooths noise (first n−1 values NaN unless min_periods); expanding grows from the start.
  • shift creates lags; diff and pct_change compare with the past; shift(7) gives week over week.
  • ewm weights recent values more and reacts faster than a rolling mean.
  • Next episode: working with bigger data: memory, speed and Parquet.