Lesson 37 · Pandas Full Course
resample, shift and rolling in Pandas: Time Series Analysis Made Easy | Pandas Course #37
In this lesson you'll be able to: Turn daily data into monthly or yearly summaries with resample Fill in a finer time grid by upsampling Compare each value…
- CoursePandas Full Course
- Lesson37 of 65
- FormatJupyter notebook · 45 code cells
- Data2 datasets
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- stocks.csv preview148.1 KB
- flights.csv preview2.3 KB
📓 Full notebook
Download .ipynbPandas 37: Resampling, Shifting, and Rolling Windows#
In this lesson you'll be able to:
- Turn daily data into monthly or yearly summaries with resample
- Fill in a finer time grid by upsampling
- Compare each value with the past using shift, diff and pct_change
- Smooth noisy series with rolling windows
- Track running records with expanding windows Data: daily stock prices 2007-2016 and monthly airline passengers
Warm-up: slicing by date#
Lesson 36 put dates in the index and sliced by year and month.
import pandas as pd
stocks = pd.read_csv("data/pandas/stocks.csv", index_col="Date",
parse_dates=True)
print(len(stocks.loc["2008-10"]))
stocks.loc["2008-10", "GSPC"].agg(["min", "max"]).round(1)
stocks.sample(3, random_state=42).round(2)
Downsample with resample#
Problem: 2,306 daily rows are too fine to see the big picture.
monthly = stocks.resample("ME").last()
print(monthly.shape)
monthly.head(3).round(2)
stocks.resample("ME").size().head(3)
sp = stocks["GSPC"]
yearly = sp.resample("YE").agg(["first", "last", "min", "max"])
yearly.round(1)
Quick check: in which years did the S&P 500 finish lower than it started?
yearly["change"] = yearly["last"] / yearly["first"] - 1
yearly["change"].round(3)
fri = sp.loc["2008-09":"2008-10"].resample("W-FRI").last()
fri.round(1)
rules = {"AAPL": "last", "GSPC": "mean"}
stocks.resample("QE").agg(rules).head(3).round(2)
Common mistake: resampling without a date index
raw = pd.read_csv("data/pandas/stocks.csv", parse_dates=["Date"])
raw.resample("ME").last()
by_on = raw.resample("ME", on="Date").last()
print(by_on["GSPC"].iloc[0].round(2))
flights = pd.read_csv("data/pandas/flights.csv")
flights.index = pd.date_range("1949-01-01", periods=144, freq="MS")
p = flights["passengers"]
p.resample("QE").sum().head(4)
p.resample("QS").sum().head(2)
Your turn#
Find Apple's average closing price in each year.
aapl_year = stocks["AAPL"].resample("YE").mean()
aapl_year.round(2)
Upsample to a finer grid#
Problem: line up monthly data with daily data.
daily = p.resample("D").asfreq()
print(len(daily), daily.isna().sum())
daily.loc["1949-01-30":"1949-02-02"]
filled = p.resample("D").ffill()
filled.loc["1949-01-30":"1949-02-02"]
smooth = p.resample("D").interpolate()
smooth.loc["1949-01-30":"1949-02-02"].round(1)
Quick check: which fill is right for a monthly total like passengers?
per_day = p / p.index.days_in_month
print(per_day.head(2).round(2))
Your turn#
How many passengers flew per day in July 1960, the busiest month?
print(per_day.loc["1960-07"].round(2))
print(p.loc["1960-07"].iloc[0] / 31)
Compare with the past: shift, diff and pct_change#
Problem: how did today compare with yesterday, or last year?
prev = sp.shift(1)
pd.DataFrame({"close": sp, "prev": prev}).head(3).round(1)
print(sp.diff().head(3).round(1).tolist())
ret = sp.pct_change()
print(ret.head(3).round(4).tolist())
print(ret.nsmallest(3).round(3))
print(ret.nlargest(3).round(3))
print((ret > 0).mean().round(3))
print(ret.groupby(ret.index.year).std().round(4).head(4))
rets = stocks.pct_change()
rets.corr().round(2)
nxt = ret.shift(-1)
print(ret.corr(nxt).round(3))
growth_1 = (1 + rets).cumprod()
growth_1.iloc[-1].sort_values().round(2)
yoy = p.pct_change(12)
yoy.loc["1950-01":"1950-04"].round(3)
Your turn#
Which single month had the biggest jump in passengers over the month before?
jump = p.diff()
print(jump.idxmax().date(), jump.max())
Smooth with rolling windows#
Problem: daily noise hides the trend.
ma50 = sp.rolling(50).mean()
print(ma50.isna().sum())
print(ma50.iloc[49].round(1))
early = sp.rolling(50, min_periods=10).mean()
print(early.isna().sum())
band = sp.rolling(20).agg(["min", "max"])
band.loc["2008-10-31"].round(1)
m30 = sp.rolling("30D").mean()
print(sp.rolling("30D").count().iloc[-5:].tolist())
Common mistake: a time window on unsorted dates
mixed = sp.sample(frac=1, random_state=42)
mixed.rolling("30D").mean()
fixed = mixed.sort_index().rolling("30D").mean()
print(fixed.equals(m30))
vol = ret.rolling(21).std() * 252 ** 0.5
print(vol.idxmax().date(), vol.max().round(3))
print(vol.loc["2013"].mean().round(3))
ema = sp.ewm(span=50).mean()
print(sp.iloc[-1].round(1), ma50.iloc[-1].round(1),
ema.iloc[-1].round(1))
Your turn#
After the March 2009 bottom, when did the 50-day average first rise above the 200-day?
ma200 = sp.rolling(200).mean()
above = ma50 > ma200
cross = above & ~above.shift(1, fill_value=False)
print(cross.loc["2009-03":].idxmax().date())
import matplotlib.pyplot as plt
ax = sp.plot(figsize=(9, 4), alpha=0.5, label="Daily close")
ma50.plot(ax=ax, label="50-day average")
ax.set_title("S&P 500 with its 50-day moving average")
ax.set_xlabel("Date")
ax.set_ylabel("Index level")
ax.legend()
plt.show()
Running records with expanding windows#
Problem: how far below its best-ever level is the market?
high = sp.expanding().max()
print(high.loc["2008-12-31"].round(1))
drawdown = sp / high - 1
print(drawdown.idxmin().date(), drawdown.min().round(3))
print(sp.expanding().mean().loc["2016-03-01"].round(1))
print(sp.cummax().equals(high))
Quick check: what share of days was the market in a drawdown of 20 percent or more?
print((drawdown <= -0.2).mean().round(3))
Your turn#
On which day did the S&P 500 first set a new record after 2009?
record = sp == high
after_2009 = record[record & (record.index > "2009")]
print(after_2009.index[0].date(), len(after_2009))
Mini-project: did air travel growth slow down?#
Plan: yearly totals, growth per year, a smooth trend, then a finding.
totals = p.resample("YE").sum()
growth = totals.pct_change()
growth.index = growth.index.year
growth.round(3)
trend = p.rolling(12).mean()
ax = p.plot(figsize=(9, 4), alpha=0.5, label="Monthly")
trend.plot(ax=ax, label="12-month average")
ax.set_title("Airline passengers, 1949-1960")
ax.set_xlabel("Month")
ax.set_ylabel("Passengers")
ax.legend()
plt.show()
season = p / p.rolling(12, center=True).mean()
by_month = season.groupby(season.index.month).mean()
print(by_month.round(2).loc[[2, 7, 8, 11]])
first3 = growth.loc[1950:1952].mean()
last3 = growth.loc[1958:1960].mean()
print(f"Growth: {first3:.0%} a year early on, {last3:.0%} by the "
f"end; totals rose {totals.iloc[-1] / totals.iloc[0]:.1f}x")
Recap#
- resample('ME' / 'QE' / 'YE') plus an aggregation downsamples
- Upsampling needs a fill: asfreq, ffill or interpolate
- shift, diff and pct_change compare with earlier rows
- rolling(n) or rolling('30D') smooths; sort the index first
- expanding() tracks running records like the best-ever level
Challenges#
- Find Starbucks's best month, by month-end to month-end return.
- Plot Microsoft with its 200-day moving average.
- Which year had the highest average 12-month passenger growth?
What's next#
Lesson 38: Time-based Grouping and Aggregations Group real orders by month, quarter and weekday, with several summaries at once.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



