resample, rolling and shift - the three operations that need the rows to be in time order.
Overview
The index does the work
Time series analysis in pandas depends almost entirely on having a DatetimeIndex. Without one, none of the time-aware methods work. resample raises. Rolling windows with time-based spans refuse to run. Date-based selection returns nothing useful.
Setting up the DatetimeIndex is a two-step process. First, parse the date column with pd.to_datetime. Second, set it as the index with set_index. People skip the second step more often than the first, because the frame already has a datetime column and seems ready to use. But resample and time-based loc selections operate on the index, not on an arbitrary column.
Once the index is a timeline, pandas can do things that would otherwise require substantial code. Select all rows in March 2024 with a string slice. Aggregate daily data to monthly totals. Compute a seven-day rolling average. Shift values forward by one business day. Each of these is one line.
resample is the time-series equivalent of group-by. The groups are calendar periods rather than column values. Downsampling aggregates, like monthly totals from daily data. Upsampling creates new periods and fills them, like daily values from monthly observations.
Rolling windows compute statistics over a sliding window of time. A 30-day moving average, a 7-day maximum, a rolling standard deviation. The window can be specified as a number of rows or as a time span. Time-based windows handle irregular data correctly because they measure actual calendar time, not row count.
shift moves values forward or backward by a number of periods. It is how you compute period-over-period changes. The difference between this month and last month is a subtraction of the original and the shifted version. pct_change wraps that pattern into a single method.
This module covers setting up the DatetimeIndex, resample for aggregation and upsampling, rolling for moving statistics, and shift for period comparisons. The examples use daily and monthly data to show the mechanics clearly.
all in this module depends on the frame having a DatetimeIndex. resample raises without one. Time-based windows have nothing to measure against.
df.set_index("date") after pd.to_datetime is the usual setup. It's the step people skip before wondering why resample won't run.
Once the index is a timeline, pandas can do things that would else need a lot of math. Select a month by name, aggregate by period, window by duration rather than by row count.
Worth knowing
resample and time-based rolling require a DatetimeIndex and raise without one.
resample("ME").sum() is group-by for time — downsampling aggregates, and takes agg like any group-by.
Upsampling creates empty rows: ffill asserts the value held, interpolate asserts it moved smoothly. Different claims.
rolling(n) gives NaN until the window fills; min_periods=1 starts immediately, and rolling("3D") windows by time.
shift, diff and pct_change compare each row with its past — the basis of lagged features.
All of these assume the rows are in time order, and pandas does not check. sort_index() first.
Time Series
resample, rolling and shift.
A DatetimeIndex is the prerequisite
resample and rolling-by-time both need one.
example_01.pypandas
import pandas as pd
import numpy as np
s = pd.Series(np.arange(10),
index=pd.date_range("2024-01-01", periods=10, freq="D"))
print(s.head(3))
print()
print("index type:", type(s.index).__name__)
print("frequency :", s.index.freq)
print()
plain = pd.Series(np.arange(10))
try:
plain.resample("D").sum()
except TypeError as e:
print("resample without a DatetimeIndex ->", type(e).__name__)
print(" ", str(e)[:56])
import pandas as pd
s = pd.Series([10, 20],
index=pd.to_datetime(["2024-01-01", "2024-01-04"]))
print("original:"); print(s)
print()
up = s.resample("D").asfreq()
print("daily, unfilled:"); print(up)
print()
print("forward filled:"); print(s.resample("D").ffill())
print()
print("interpolated:"); print(s.resample("D").interpolate())
print()
print("Choose deliberately - ffill asserts the value held, interpolate")
print("asserts it moved smoothly. They are different claims.")
Output
rolling windows
A moving statistic over the last n rows, or the last n days.
example_04.pypandas
import pandas as pd
import numpy as np
s = pd.Series([1, 2, 3, 4, 5],
index=pd.date_range("2024-01-01", periods=5, freq="D"))
print("3-row rolling mean:")
print(s.rolling(3).mean())
print(" the first two are NaN - the window is not full yet")
print()
print("min_periods=1 starts immediately:")
print(list(s.rolling(3, min_periods=1).mean()))
print()
print("a time-based window handles irregular gaps:")
print(list(s.rolling("3D").sum()))
print()
print("expanding() is a window that grows from the start:")
print(list(s.expanding().sum()))
Output
shift compares a row with its past
The basis of differences, growth rates and lagged features.
example_05.pypandas
import pandas as pd
s = pd.Series([100, 110, 121],
index=pd.date_range("2024-01-01", periods=3, freq="D"))
print("original:", list(s))
print("shift(1) :", list(s.shift(1)), "<- everything moved down one")
print()
print("difference from yesterday:", list(s.diff()))
print("percent change :", [round(v, 4) if pd.notna(v) else None
for v in s.pct_change()])
print()
print("shift is what makes a lagged feature:")
df = pd.DataFrame({"value": s})
df["yesterday"] = df["value"].shift(1)
print(df)
Output
Order matters, and pandas will not check it
Every operation here assumes the rows are sorted in time.
example_06.pypandas
import pandas as pd
idx = pd.to_datetime(["2024-01-03", "2024-01-01", "2024-01-02"])
s = pd.Series([30, 10, 20], index=idx)
print("out of order:"); print(s)
print()
print("diff() on unsorted data is meaningless:")
print(list(s.diff()))
print()
print("sorted first:")
print(list(s.sort_index().diff()))
print()
print("pandas did not warn either time. sort_index() before any")
print("shift, diff, rolling or resample on data you did not sort.")
Output
resample
resample is group-by where the groups are time periods.
Downsampling — daily to monthly — aggregates. Takes the same methods as groupby: sum(), mean(), agg(["sum", "mean"]).
The rate string is the arg that matters. D daily, W weekly, ME month end, MS month start, QE quarter end, YE year end, h hourly, min minutely.
Note that pandas 2.2 renamed a few of these. M became ME, H became h, T became min. Older code and older tutorials use the old spellings. This Now warn or fail based on version. If a rate string doesn't work, that rename is the likely reason.
The label of each output row is the end of the period for ME. The start for MS. label= and closed= control which boundary is used and which side is inclusive. They matter when periods must line up with a thing external.
Upsampling invents rows
Going to a finer rate creates rows that didn't exist, and they start as NaN.
asfreq() leaves them empty. This Is honest and rarely handy on its own.
ffill() carries the last known value forward. That asserts the value held constant until the next finding. Correct for a price, a status, a setting.
interpolate() fits between the known points. That asserts the value moved smoothly. Correct for a temp, a spot, anything smooth.
These are other claims about the world. Choosing between them is a modelling choice rather than a formatting one. Both fabricate data. The question is which fabrication is less wrong for what the number means.
rolling
s.rolling(3).mean() gives a moving mean over the last three rows.
The first two results are NaN. The window isn't yet full. That is deliberate. A three-row mean found from one row isn't a three-row mean. min_periods=1 replaces it and starts finding at once. This Is convenient and slightly dishonest at the edges.
s.rolling("3D") windows by time rather than row count. This is the one to use when findings are irregularly spaced. Three rows might span three days or three months. Only the time-based window means what you said.
center=True puts the window around each point rather than behind it. It's right for smoothing a curve for display and wrong for anything predictive. It uses future values.
expanding() is a window that grows from the start. A running total or a cumulative mean.
ewm() gives fast weighted statistics. Here Recent findings count for more.
shift, diff, pct_change
shift(1) moves each value down one row. Each row can see the previous one. shift(-1) looks forward.
diff() is s - s.shift(1). pct_change() is the scaled version.
These are how you build lagged features, growth rates and change spotting. The first row is always NaN. It has no predecessor.
Within groups, use the group-by versions. df.groupby("id")["v"].diff(). Or the shift crosses from one entity into another and makes a gap between unrelated rows. That is a common and quiet error in panel data.
Order is assumed, not checked
each step here — shift, diff, rolling, resample — assumes the rows are in time order.
pandas does not verify it. diff() on unsorted data returns numbers, and they're meaningless.
sort_index() before any of these on data you didn't sort yourself. It's one line. The failure it blocks makes plausible output rather than an error.
The same applies to duplicated timestamps: two rows for the same instant make diff and rolling ambiguous. Are often a sign of a data problem worth looking at before aggregating over it.
Frequency strings
The alias is the arg that decides what resample and date_range do. Pandas 2.2 renamed a few of them.
D day, B business day, W week (ending Sunday by default, W-MON to change it), MS month start, ME month end, QS/QE quarter, YS/YE year, h hour, min minute, s second.
The renames: M to ME, H to h, T to min, S to s. Older code and most tutorials use the old spellings. This Now warn or fail. When a rate string doesn't work, this is the first thing to check.
Multiples work: "15min", "2W", "3ME".
Anchored offsets pin the boundary: "W-FRI" for weeks ending Friday, "QE-JAN" for quarters ending in January. This Matters for fiscal years that don't start in January.
Which label a period gets
resample("ME") labels each group with the end of the period. "MS" with the start.
label="left" or "right" replaces which boundary names the group. closed= decides which end is inclusive.
These matter when the output must line up with a thing made elsewhere. A monthly total labelled 2024-01-31 and one labelled 2024-01-01 are the same number with other index labels, and joining them gives nothing.
Settling on a rule early. Often period start — saves reconciliation later.
Grouping by time and something else
resample groups by time only. To group by time and a group, pd.Grouper mixes with plain keys:
key= names the date column. So This works without setting an index. This Is what makes Grouper more handy than resample .
The result is a MultiIndex, and unstack("city") gives the wide table.
Rolling in more depth
min_periods decides how many findings a window needs before making a value. The default for a fixed-size window is the window size. For a time-based window it's 1.
closed= controls whether the window includes its endpoints. This Matters for time-based windows where an finding may sit just on the boundary.
win_type gives weighted windows — Gaussian, tri — for smoothing.
rolling(...).apply(func, raw=True) runs a custom function per window. raw=True passes a NumPy array rather than a Series and is much faster.
Within groups, df.groupby("id")["v"].rolling(3).mean() windows inside each group and returns a MultiIndexed result. This Often needs droplevel(0) before it can be assigned back.
Gaps, and what they hide
A rolling window over rows assumes the rows are evenly spaced. Real time series have gaps. Weekends, outages, missing readings. And a three-row window may span three days or three weeks.
Two fixes:
Window by time: rolling("3D") means three days whatever the row spacing.
Regularise first: resample("D").mean() produces a row per day, with NaN where there was no data, after which row-based windows mean what they say.
Which is right depends on whether an absent finding should count as missing or simply not exist. That is a modelling question. resample forces you to answer it. This Is an arg for doing it early.
A checklist for time-series work
Parse the dates and check the dtype.
Set a DatetimeIndex, or plan to use pd.Grouper(key=...).
sort_index().
Check for duplicated timestamps.
Decide on a time zone, or commit to naive throughout.
Decide whether gaps are missing or absent, and smooth if they're missing.
Then resample, rolling and shift mean what they appear to mean.
Aligning two series with different frequencies
A common task: daily data and monthly targets, joined for compare.
The wrong approach is a merge on dates. This Matches almost nothing.
The right approach is to bring both to the same rate first:
Or, going the other way, upsample the monthly figure and forward-fill it so each day carries its month's target.
Which direction is right depends on the question. Aggregating up loses detail and is often correct for reporting. Spreading down invents precision and is often correct for per-row compare. Either way, the alignment step is clear rather than implied by a join.
MS versus ME matters here: two monthly series labelled at opposite ends of the month won't join at all.
Lags and leads for modelling
shift(1) gives the previous value. A lag, safe to use as a predictor.
shift(-1) gives the next value. A lead. This Is the target in a forecasting problem and must never be a feature.
Using a lead as a feature is data leakage. It makes models that score beautifully and fail in production. Because both are one method with a sign, the mistake is easy to make and invisible in the code.
Rolling features have the same hazard: rolling(3, center=True) uses future values. For anything predictive, windows must look backwards only. This Is the default without center.
Within groups, all of these need the group-wise form, or the lag crosses from one entity to the next.
Missing periods
A gap in a time series is either "no finding" or "no event". The two need other handling.
resample("D").sum() gives 0 for days with no rows. Correct if the series counts events.
resample("D").mean() gives NaN — correct if the series measures a thing that existed but wasn't recorded.
Choosing the wrong one makes a series that looks full and is wrong at just the notable points.
asfreq() makes the gaps clear without aggregating. This Is the honest first step when you aren't yet sure.
A summary
A DatetimeIndex is the prerequisite for resample and time-based windows.
rate aliases were renamed in pandas 2.2 — ME, h, min.
Downsampling aggregates. Upsampling invents rows that need filling on purpose.
ffill asserts a value held. interpolate asserts it moved smoothly.
rolling("3D") for irregular spacing. min_periods for the edges.
shift(-1) is a lead and belongs only in a target, never a feature.
Use the group-wise forms on panel data.
sort_index() first, always, because none of these check.
A closing note on Time Series
Time series work rewards setting things up properly and punishes assumptions.
The setup is short: parse the dates with an clear format, check the dtype, decide on a time zone, set a DatetimeIndex if you need period pick. Sort. each step in this module assumes that order and none of them check it. diff on unsorted rows returns numbers that mean nothing.
The recurring choice is what a gap means. A day with no rows is either zero events or an unrecorded reading. resample("D").sum() and .mean() encode those two other answers. Choosing without noticing gives a series that looks full and is wrong just where the notable things happen.
And on panel data — a few entities stacked in one frame — shift, diff and rolling must be done within groups, or the compare runs across the boundary from one entity into the next and makes a plausible number from unrelated rows.
Questions people ask about Time Series
How do I change the frequency of a time series? Use resample, for example df.resample('M').sum() for monthly totals. It needs a DatetimeIndex or an on= column.
What is the difference between resample and groupby? resample groups by time intervals and fills in intervals with no data, while groupby only produces groups that exist. resample is a groupby specialised for dates.
How do I compute a moving average? Use df['x'].rolling(window=7).mean(). The first window-1 values are NaN unless you set min_periods.
What does shift do? It moves the values down by n rows, which gives the previous period's value. Combine it with subtraction, or use diff() and pct_change(), for changes over time.
Why do I get NaN or surprising results after resampling? Periods with no data are empty, so sums give 0 and means give NaN. Also check that the index is sorted and that you know which edge labels the bins.
Do I need to sort by date first? Yes. rolling, shift, diff and slicing assume the rows are in time order and do not check it.
One more thing
resample accepts origin= and offset=. This Control where the period boundaries fall. That matters for data that should be bucketed on a thing other than midnight or the first of the month. A business day starting at 6am, or weeks aligned to a fiscal calendar.
Check yourself
0 of 4
Answer without scrolling back up.
What does `resample` require?
df.set_index('date') after pd.to_datetime is the usual setup, and the step people skip before wondering why resample will not run.
What is the difference between `ffill` and `interpolate` when upsampling?
Different claims about the world. Both fabricate data - the question is which fabrication is less wrong for what the number means.
Why does `rolling(3).mean()` start with NaN?
min_periods=1 overrides it, which is convenient and slightly dishonest at the edges.
What does pandas do if you call `diff()` on rows that are not in time order?
Order is assumed, not checked. sort_index() before any shift, diff, rolling or resample on data you did not sort yourself.
Cheat sheet
Time Series
Resample timestamps into intervals, compute rolling averages, and shift rows forward or back to compare periods side by side.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.