Mathew K Analytics

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…

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.

📓 Full notebook

Download .ipynb

Pandas 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)
23
min     848.9
max    1161.1
Name: GSPC, dtype: float64
stocks.sample(3, random_state=42).round(2)
MSFT IBM SBUX AAPL GSPC
Date
2013-07-11 33.09 178.58 33.41 57.74 1675.02
2009-01-09 16.16 72.34 4.48 11.98 890.35
2014-05-22 38.28 175.74 34.87 84.05 1892.49

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)
(111, 5)
MSFT IBM SBUX AAPL GSPC
Date
2007-01-31 24.75 82.07 16.01 11.34 1438.24
2007-02-28 22.67 77.17 14.16 11.19 1406.82
2007-03-31 22.43 78.26 14.37 12.29 1420.86
stocks.resample("ME").size().head(3)
Date
2007-01-31    20
2007-02-28    19
2007-03-31    22
Freq: ME, dtype: int64
sp = stocks["GSPC"]
yearly = sp.resample("YE").agg(["first", "last", "min", "max"])
yearly.round(1)
first last min max
Date
2007-12-31 1416.6 1468.4 1374.1 1565.2
2008-12-31 1447.2 903.2 752.4 1447.2
2009-12-31 931.8 1115.1 676.5 1127.8
2010-12-31 1133.0 1257.6 1022.6 1259.8
2011-12-31 1271.9 1257.6 1099.2 1363.6
2012-12-31 1277.1 1426.2 1277.1 1465.8
2013-12-31 1462.4 1848.4 1457.2 1848.4
2014-12-31 1832.0 2058.9 1741.9 2090.6
2015-12-31 2058.2 2043.9 1867.6 2130.8
2016-12-31 2012.7 1978.3 1829.1 2016.7

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)
Date
2007-12-31    0.037
2008-12-31   -0.376
2009-12-31    0.197
2010-12-31    0.110
2011-12-31   -0.011
2012-12-31    0.117
2013-12-31    0.264
2014-12-31    0.124
2015-12-31   -0.007
2016-12-31   -0.017
Freq: YE-DEC, Name: change, dtype: float64
fri = sp.loc["2008-09":"2008-10"].resample("W-FRI").last()
fri.round(1)
Date
2008-09-05    1242.3
2008-09-12    1251.7
2008-09-19    1255.1
2008-09-26    1213.3
2008-10-03    1099.2
2008-10-10     899.2
2008-10-17     940.5
2008-10-24     876.8
2008-10-31     968.8
Freq: W-FRI, Name: GSPC, dtype: float64
rules = {"AAPL": "last", "GSPC": "mean"}
stocks.resample("QE").agg(rules).head(3).round(2)
AAPL GSPC
Date
2007-03-31 12.29 1424.38
2007-06-30 16.15 1497.18
2007-09-30 20.30 1489.46

Common mistake: resampling without a date index

raw = pd.read_csv("data/pandas/stocks.csv", parse_dates=["Date"])
raw.resample("ME").last()
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[10], line 2
      1 raw = pd.read_csv("data/pandas/stocks.csv", parse_dates=["Date"])
----> 2 raw.resample("ME").last()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py:9411, in NDFrame.resample(self, rule, closed, label, convention, on, level, origin, offset, group_keys)
   9056 """
   9057 Resample time-series data.
   9058 
   (...)
   9407 Freq: 17min, dtype: int64
   9408 """
   9409 from pandas.core.resample import get_resampler
-> 9411 return get_resampler(
   9412     cast("Series | DataFrame", self),
   9413     freq=rule,
   9414     label=label,
   9415     closed=closed,
   9416     convention=convention,
   9417     key=on,
   9418     level=level,
   9419     origin=origin,
   9420     offset=offset,
   9421     group_keys=group_keys,
   9422 )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\resample.py:2335, in get_resampler(obj, **kwds)
   2331 """
   2332 Create a TimeGrouper and return our resampler.
   2333 """
   2334 tg = TimeGrouper(obj, **kwds)  # type: ignore[arg-type]
-> 2335 return tg._get_resampler(obj)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\resample.py:2541, in TimeGrouper._get_resampler(self, obj)
   2533 elif isinstance(ax, TimedeltaIndex):
   2534     return TimedeltaIndexResampler(
   2535         obj,
   2536         timegrouper=self,
   2537         group_keys=self.group_keys,
   2538         gpr_index=ax,
   2539     )
-> 2541 raise TypeError(
   2542     "Only valid with DatetimeIndex, "
   2543     "TimedeltaIndex or PeriodIndex, "
   2544     f"but got an instance of '{type(ax).__name__}'"
   2545 )

TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'RangeIndex'
by_on = raw.resample("ME", on="Date").last()
print(by_on["GSPC"].iloc[0].round(2))
1438.24
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)
1949-03-31    362
1949-06-30    385
1949-09-30    432
1949-12-31    341
Freq: QE-DEC, Name: passengers, dtype: int64
p.resample("QS").sum().head(2)
1949-01-01    362
1949-04-01    385
Freq: QS-JAN, Name: passengers, dtype: int64

Your turn#

Find Apple's average closing price in each year.

aapl_year = stocks["AAPL"].resample("YE").mean()
aapl_year.round(2)
Date
2007-12-31     16.97
2008-12-31     18.78
2009-12-31     19.42
2010-12-31     34.38
2011-12-31     48.16
2012-12-31     76.40
2013-12-31     63.94
2014-12-31     89.49
2015-12-31    118.42
2016-12-31     96.85
Freq: YE-DEC, Name: AAPL, dtype: float64

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"]
4353 4209
1949-01-30      NaN
1949-01-31      NaN
1949-02-01    118.0
1949-02-02      NaN
Freq: D, Name: passengers, dtype: float64
filled = p.resample("D").ffill()
filled.loc["1949-01-30":"1949-02-02"]
1949-01-30    112
1949-01-31    112
1949-02-01    118
1949-02-02    118
Freq: D, Name: passengers, dtype: int64
smooth = p.resample("D").interpolate()
smooth.loc["1949-01-30":"1949-02-02"].round(1)
1949-01-30    117.6
1949-01-31    117.8
1949-02-01    118.0
1949-02-02    118.5
Freq: D, Name: passengers, dtype: float64

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))
1949-01-01    3.61
1949-02-01    4.21
Freq: MS, dtype: float64

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)
1960-07-01    20.06
Freq: MS, dtype: float64
20.06451612903226

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)
close prev
Date
2007-01-03 1416.6 NaN
2007-01-04 1418.3 1416.6
2007-01-05 1409.7 1418.3
print(sp.diff().head(3).round(1).tolist())
ret = sp.pct_change()
print(ret.head(3).round(4).tolist())
[nan, 1.7, -8.6]
[nan, 0.0012, -0.0061]
print(ret.nsmallest(3).round(3))
print(ret.nlargest(3).round(3))
Date
2008-10-15   -0.090
2008-12-01   -0.089
2008-09-29   -0.088
Name: GSPC, dtype: float64
Date
2008-10-13    0.116
2008-10-28    0.108
2009-03-23    0.071
Name: GSPC, dtype: float64
print((ret > 0).mean().round(3))
print(ret.groupby(ret.index.year).std().round(4).head(4))
0.54
Date
2007    0.0101
2008    0.0258
2009    0.0172
2010    0.0114
Name: GSPC, dtype: float64
rets = stocks.pct_change()
rets.corr().round(2)
MSFT IBM SBUX AAPL GSPC
MSFT 1.00 0.55 0.48 0.47 0.71
IBM 0.55 1.00 0.49 0.49 0.71
SBUX 0.48 0.49 1.00 0.43 0.68
AAPL 0.47 0.49 0.43 1.00 0.61
GSPC 0.71 0.71 0.68 0.61 1.00
nxt = ret.shift(-1)
print(ret.corr(nxt).round(3))
-0.107
growth_1 = (1 + rets).cumprod()
growth_1.iloc[-1].sort_values().round(2)
GSPC    1.40
IBM     1.67
MSFT    2.20
SBUX    3.72
AAPL    9.07
Name: 2016-03-01 00:00:00, dtype: float64
yoy = p.pct_change(12)
yoy.loc["1950-01":"1950-04"].round(3)
1950-01-01    0.027
1950-02-01    0.068
1950-03-01    0.068
1950-04-01    0.047
Freq: MS, Name: passengers, dtype: float64

Your turn#

Which single month had the biggest jump in passengers over the month before?

jump = p.diff()
print(jump.idxmax().date(), jump.max())
1960-07-01 87.0

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))
49
1425.1
early = sp.rolling(50, min_periods=10).mean()
print(early.isna().sum())
9
band = sp.rolling(20).agg(["min", "max"])
band.loc["2008-10-31"].round(1)
min     848.9
max    1056.9
Name: 2008-10-31 00:00:00, dtype: float64
m30 = sp.rolling("30D").mean()
print(sp.rolling("30D").count().iloc[-5:].tolist())
[21.0, 21.0, 21.0, 20.0, 21.0]

Common mistake: a time window on unsorted dates

mixed = sp.sample(frac=1, random_state=42)
mixed.rolling("30D").mean()
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[33], line 2
      1 mixed = sp.sample(frac=1, random_state=42)
----> 2 mixed.rolling("30D").mean()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py:12196, in NDFrame.rolling(self, window, min_periods, center, win_type, on, closed, step, method)
  12183 if win_type is not None:
  12184     return Window(
  12185         self,
  12186         window=window,
   (...)
  12193         method=method,
  12194     )
> 12196 return Rolling(
  12197     self,
  12198     window=window,
  12199     min_periods=min_periods,
  12200     center=center,
  12201     win_type=win_type,
  12202     on=on,
  12203     closed=closed,
  12204     step=step,
  12205     method=method,
  12206 )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\window\rolling.py:160, in BaseWindow.__init__(self, obj, window, min_periods, center, win_type, on, closed, step, method, selection)
    154     raise ValueError(
    155         f"invalid on specified as {self.on}, "
    156         "must be a column (of DataFrame), an Index or None"
    157     )
    159 self._selection = selection
--> 160 self._validate()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\window\rolling.py:1984, in Rolling._validate(self)
   1978 # we allow rolling on a datetimelike index
   1979 if (
   1980     self.obj.empty
   1981     or isinstance(self._on, (DatetimeIndex, TimedeltaIndex, PeriodIndex))
   1982     or (isinstance(self._on.dtype, ArrowDtype) and self._on.dtype.kind in "mM")
   1983 ) and isinstance(self.window, (str, BaseOffset, timedelta)):
-> 1984     self._validate_datetimelike_monotonic()
   1986     # this will raise ValueError on non-fixed freqs
   1987     try:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\window\rolling.py:2032, in Rolling._validate_datetimelike_monotonic(self)
   2030     self._raise_monotonic_error("values must not have NaT")
   2031 if not (self._on.is_monotonic_increasing or self._on.is_monotonic_decreasing):
-> 2032     self._raise_monotonic_error("values must be monotonic")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\window\rolling.py:2038, in Rolling._raise_monotonic_error(self, msg)
   2036 if on is None:
   2037     on = "index"
-> 2038 raise ValueError(f"{on} {msg}")

ValueError: index values must be monotonic
fixed = mixed.sort_index().rolling("30D").mean()
print(fixed.equals(m30))
True
vol = ret.rolling(21).std() * 252 ** 0.5
print(vol.idxmax().date(), vol.max().round(3))
print(vol.loc["2013"].mean().round(3))
2008-10-28 0.863
0.11
ema = sp.ewm(span=50).mean()
print(sp.iloc[-1].round(1), ma50.iloc[-1].round(1),
      ema.iloc[-1].round(1))
1978.3 1939.9 1941.2

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())
2009-06-23
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()
No description has been provided for this image

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))
1565.2
2009-03-09 -0.568
print(sp.expanding().mean().loc["2016-03-01"].round(1))
print(sp.cummax().equals(high))
1460.0
True

Quick check: what share of days was the market in a drawdown of 20 percent or more?

print((drawdown <= -0.2).mean().round(3))
0.29

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))
2013-03-28 108

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)
1949      NaN
1950    0.103
1951    0.218
1952    0.158
1953    0.142
1954    0.062
1955    0.189
1956    0.156
1957    0.122
1958    0.034
1959    0.124
1960    0.112
Name: passengers, dtype: float64
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()
No description has been provided for this image
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]])
2     0.89
7     1.24
8     1.22
11    0.80
Name: passengers, dtype: float64
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")
Growth: 16% a year early on, 9% by the end; totals rose 3.8x

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#

  1. Find Starbucks's best month, by month-end to month-end return.
  2. Plot Microsoft with its 200-day moving average.
  3. 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.