Dates and Time-Series Indexing

5.1 Why Dates Are Special (8 min)

Create day05/dates_basics.py.

import pandas as pd

# Text dates can't do date arithmetic
print("2024-06-05" > "2024-01-01")     # works only by luck of ISO ordering
# "2024-06-05" - "2024-01-01"          # TypeError

# Real dates can
first_cut = pd.to_datetime("2024-06-05")
peak = pd.to_datetime("2023-07-12")

print(type(first_cut))                  # Timestamp
print(first_cut - peak)                 # 329 days
print(first_cut.year, first_cut.month, first_cut.quarter)
print(first_cut + pd.DateOffset(months=3))
print(first_cut.strftime("%B %Y"))      # "June 2024"

Generating date sequences

monthly = pd.date_range("2026-01-01", "2026-06-01", freq="MS")   # month starts
quarterly = pd.date_range("2025-01-01", periods=4, freq="QS")    # quarter starts
print(monthly)
print(quarterly)

The notebook's synth_dates helper uses exactly this.

5.2 Date Columns and the .dt Accessor (15 min)

ff = pd.read_csv("data/fred_fedfunds.csv").iloc[:, :2]
ff.columns = ["date", "fed_funds"]
ff["date"] = pd.to_datetime(ff["date"])
ff["fed_funds"] = pd.to_numeric(ff["fed_funds"], errors="coerce")

print(ff.dtypes)        # date is now datetime64

ff["year"] = ff["date"].dt.year
ff["quarter"] = ff["date"].dt.quarter
ff["month_name"] = ff["date"].dt.month_name()
print(ff.tail())

.dt gives access to date parts of a whole column, just as .str does for text.

Parsing dates explicitly

StatCan monthly dates look like "2026-07" (no day). Tell pandas the format so it never guesses wrong:

print(pd.to_datetime("2026-07", format="%Y-%m"))    # 2026-07-01

5.3 The DatetimeIndex: Slicing by Date (28 min)

Putting dates in the index unlocks pandas' time-series superpowers. Create day05/time_index.py.

Example 1 — Setting the index

import pandas as pd

ff = pd.read_csv("data/fred_fedfunds.csv").iloc[:, :2]
ff.columns = ["date", "fed_funds"]
ff["date"] = pd.to_datetime(ff["date"])
ff["fed_funds"] = pd.to_numeric(ff["fed_funds"], errors="coerce")

ff = ff.set_index("date").sort_index()
print(ff.head())
print(ff.index)          # DatetimeIndex

Example 2 — Selecting by partial date strings

print(ff.loc["2023"])                       # all of 2023
print(ff.loc["2022-03":"2023-07"])          # a date range (both ends included)
print(ff.loc["2020-04-01"])                 # one exact month
print(ff.loc["2019":])                      # from 2019 to the end

Compare with Day 4, where we filtered on text. This is shorter, safer and faster.

Example 3 — Working with a single series

rate = ff["fed_funds"]                      # Series with a DatetimeIndex

print("First date:", rate.index.min())
print("Last date:", rate.index.max())
print("Peak:", rate.max(), "on", rate.idxmax().date())
print("Average 2010s:", rate.loc["2010":"2019"].mean().round(2))

idxmax() returns the date of the maximum. That's hard to do with plain lists!

Example 4 — Sanity checks on the index

print(rate.index.is_monotonic_increasing)   # sorted?
print(rate.index.is_unique)                 # no duplicate dates?

Try it 5.3

Using the Fed funds series: (a) average rate in 2008, (b) the lowest rate since 2022 and its date, (c) how many months between 2020-01 and 2021-12 the rate was below 0.25%.

print(rate.loc["2008"].mean())

recent = rate.loc["2022":]
print(recent.min(), recent.idxmin().date())

covid = rate.loc["2020-01":"2021-12"]
print((covid < 0.25).sum())

5.4 Missing Values and Cleaning BoC Data (20 min)

Create day05/clean_boc.py.

Example 1 — Spotting missing values

import numpy as np
import pandas as pd

s = pd.Series([2.25, np.nan, 2.25, np.nan, 2.50])
print(s.isna())             # True where missing
print(s.isna().sum())       # how many → 2
print(s.dropna())           # remove them
print(s.ffill())            # carry the last value forward

numpy (np) was installed alongside pandas. np.nan is how we type a missing value.

Example 2 — Cleaning the raw BoC file (replaces Day 3's clean_observations)

raw = pd.read_csv("data/boc_overnight.csv")
print(raw.head())
print(raw["value"].dtype)

overnight = pd.to_numeric(raw["value"], errors="coerce")
print("Unreadable or missing values:", overnight.isna().sum())

boc = pd.DataFrame({
    "date": pd.to_datetime(raw["date"]),
    "overnight_rate": overnight,
}).dropna().set_index("date").sort_index()

print(boc.tail())

Example 3 — When to use ffill

The overnight rate is a step function: it stays at its level until the next decision. Carrying it forward is economically correct. The course notebook does boc.sort_values("date").ffill() after merging daily series.

But carrying forward GDP or CPI would invent data. Always ask: does the last value genuinely still apply?

Rule: dropna when a value is unknown; ffill only when the previous value is still the truth (policy rates, prime rate, tariff status).

5.5 shift, diff, pct_change: Growth Rates and Inflation (28 min)

Create day05/inflation.py. We use real StatCan CPI data.

Step 1 — Load All-items CPI as a monthly series

import pandas as pd

cpi_raw = pd.read_csv("data/statcan_cpi.csv")
cpi_raw = cpi_raw.rename(columns={
    "REF_DATE": "date",
    "Products and product groups": "component",
    "VALUE": "value",
})
cpi_raw["date"] = pd.to_datetime(cpi_raw["date"], format="%Y-%m")

all_items = (cpi_raw[cpi_raw["component"] == "All-items"]
             .set_index("date")["value"]
             .sort_index())
print(all_items.tail())

The parentheses let you split a long chain of methods across lines.

Step 2 — shift: looking back in time

print(pd.DataFrame({
    "cpi": all_items,
    "cpi_last_month": all_items.shift(1),
    "cpi_last_year": all_items.shift(12),
}).tail())

shift(12) moves the series down 12 rows, so each month sits beside the same month last year.

Step 3 — Year-over-year inflation, three ways

# (a) Manual formula with shift
yoy_manual = (all_items / all_items.shift(12) - 1) * 100

# (b) pct_change: the notebook's method
yoy = all_items.pct_change(12) * 100

# (c) Check they match
print((yoy_manual - yoy).abs().max())     # ~0

print(f"Latest CPI inflation: {yoy.iloc[-1]:.1f}% "
      f"({yoy.index[-1]:%B %Y})")

Your Day 3 yoy_changes(levels, lag=12) did the same thing with a loop.

Step 4 — Other growth measures

mom = all_items.pct_change(1) * 100                         # month-over-month
ann_3m = ((all_items / all_items.shift(3)) ** 4 - 1) * 100  # 3-month annualised
change_pts = all_items.diff(12)                             # change in index points

Economist's warning: table 18-10-0004 is not seasonally adjusted. Year-over-year comparisons cancel seasonality; month-over-month and 3-month annualised rates don't. Interpret short-horizon measures from this table with care.

Step 5 — Watch out for gaps

pct_change(12) compares with 12 rows back, not 12 months back. If a month is missing, every later YoY is wrong. Check before trusting it:

expected = pd.date_range(all_items.index.min(), all_items.index.max(), freq="MS")
missing = expected.difference(all_items.index)
print("Missing months:", len(missing))

This exact check becomes a formal data-quality rule in Great Expectations (Days 48–50).

5.6 Rolling and Expanding Windows (10 min)

yoy_recent = yoy.loc["2019":]

smoothed = yoy_recent.rolling(3).mean()          # 3-month moving average
print(smoothed.tail())

peak_so_far = yoy_recent.expanding().max()       # running maximum
print(peak_so_far.tail())

above_3 = (yoy_recent > 3).rolling(12).sum()    # months above 3% in the last 12
print(above_3.tail())

• rolling(n) = your Day 3 moving_average
• expanding().max() = your Day 2 running-peak accumulator

The notebook uses .rolling(4, min_periods=1).mean() for housing starts, where min_periods=1 allows averages before 4 observations exist.

5.7 Periods: Normalising Dates (5 min)

Daily data has dates like 2026-08-14; monthly data uses 2026-08-01. To line them up, the notebook converts dates to periods and back:

d = pd.Series(pd.to_datetime(["2026-08-03", "2026-08-14", "2026-09-02"]))

print(d.dt.to_period("M"))                        # 2026-08, 2026-08, 2026-09
print(d.dt.to_period("M").dt.to_timestamp())      # 2026-08-01, 2026-08-01, 2026-09-01
print(d.dt.to_period("Q"))                        # 2026Q3, ...

Tomorrow you'll meet resample, a cleaner way to achieve the same result.

5.8 Mini-Project: CPI Inflation Fact Sheet (23 min)

Create day05/cpi_factsheet.py that prints:

1. Latest CPI YoY and its month.
2. Peak YoY since 2020 and its month.
3. Number of months since January 2021 with inflation above the 3% top of the BoC control range.
4. Average YoY in 2015–2019 versus 2023–present.
5. The most recent month inflation was below 2%.
6. Whether any months are missing from the series.

import pandas as pd

cpi_raw = pd.read_csv("data/statcan_cpi.csv").rename(columns={
    "REF_DATE": "date", "Products and product groups": "component", "VALUE": "value"})
cpi_raw["date"] = pd.to_datetime(cpi_raw["date"], format="%Y-%m")

cpi = (cpi_raw[cpi_raw["component"] == "All-items"]
       .set_index("date")["value"].sort_index())
yoy = (cpi.pct_change(12) * 100).dropna()

print("=== CPI Inflation Fact Sheet ===")
print(f"Latest: {yoy.iloc[-1]:.1f}% ({yoy.index[-1]:%b %Y})")

since_2020 = yoy.loc["2020":]
print(f"Peak since 2020: {since_2020.max():.1f}% ({since_2020.idxmax():%b %Y})")

print(f"Months above 3% since 2021: {(yoy.loc['2021':] > 3).sum()}")

print(f"Avg 2015–2019: {yoy.loc['2015':'2019'].mean():.2f}%")
print(f"Avg 2023–now:  {yoy.loc['2023':].mean():.2f}%")

below_2 = yoy[yoy < 2]
print(f"Last month below 2%: {below_2.index[-1]:%b %Y}")

expected = pd.date_range(cpi.index.min(), cpi.index.max(), freq="MS")
print(f"Missing months: {len(expected.difference(cpi.index))}")

Day 5 checklist

• I can convert text to dates and use .dt to extract date parts
• I can set a DatetimeIndex and slice with .loc["2022":"2023"]
• I can clean messy values with to_numeric(errors="coerce") and choose between dropna and ffill
• I can compute MoM, YoY and annualised rates with shift and pct_change
• I check for missing periods before trusting pct_change

git add .
git commit -m "Day 5: dates, time-series index, inflation"
git push