Integration: A Clean, Validated Macro Panel

7.1 Quick Charts with pandas and matplotlib (30 min)

The course notebook uses Plotly for interactive dashboards. For quick checks during data work, pandas' built-in plotting (via matplotlib) is faster. Codespaces has no screen for pop-up windows, so we save charts as PNG files and open them in the VS Code Explorer.

Create day07/quick_charts.py. Copy day06/loaders.py into day07/ first.

Example 1 — A single line chart

import os
import matplotlib
matplotlib.use("Agg")                  # draw to files, not windows
import matplotlib.pyplot as plt
from loaders import load_boc

os.makedirs("day07/charts", exist_ok=True)

overnight = load_boc("data/boc_overnight.csv", "overnight_rate")

ax = overnight.plot(title="BoC Overnight Rate (%)", figsize=(10, 4))
ax.set_ylabel("%")
plt.tight_layout()
plt.savefig("day07/charts/overnight.png", dpi=120)
plt.close()

Example 2 — Several series and a reference line

import pandas as pd
from loaders import load_cpi_long

cpi = load_cpi_long()
cpi["yoy"] = cpi.groupby("component")["value"].pct_change(12) * 100
wide = cpi.pivot(index="date", columns="component", values="yoy").loc["2018":]

ax = wide[["All-items", "Shelter", "Energy"]].plot(
    figsize=(10, 5), title="CPI Inflation by Component (YoY %)")
ax.axhline(2, linestyle="--", color="green", label="2% target")
ax.axhspan(1, 3, color="green", alpha=0.08)       # control range
ax.legend()
plt.tight_layout()
plt.savefig("day07/charts/cpi_components.png", dpi=120)
plt.close()

Example 3 — Recession shading (from the notebook)

RECESSION_BANDS = [          # as defined in the course notebook
    ("2008-10-01", "2009-06-30", "GFC"),
    ("2015-01-01", "2015-09-30", "Oil shock"),
    ("2020-02-01", "2020-06-30", "COVID"),
    ("2025-10-01", "2026-03-31", "Tariff recession"),
]

def add_recession_shading(ax):
    for start, end, label in RECESSION_BANDS:
        ax.axvspan(pd.Timestamp(start), pd.Timestamp(end), color="red", alpha=0.12)

ax = wide["All-items"].plot(figsize=(10, 4), title="Headline CPI (YoY %)")
add_recession_shading(ax)
plt.tight_layout()
plt.savefig("day07/charts/cpi_recessions.png", dpi=120)
plt.close()

Example 4 — Two panels in one figure

fig, axes = plt.subplots(2, 1, figsize=(10, 7), sharex=True)

overnight.loc["2018":].plot(ax=axes[0], title="Overnight rate (%)")
wide["All-items"].plot(ax=axes[1], title="CPI inflation (YoY %)")
for ax in axes:
    add_recession_shading(ax)

plt.tight_layout()
plt.savefig("day07/charts/rate_vs_inflation.png", dpi=120)
plt.close()

Open the charts/ folder in the Explorer and click a PNG to view it.

7.2 Refactoring into a panel.py Module (30 min)

Over three days, your scripts have repeated the same loading and transforming steps. Let's collect them into one tested module, just as Day 3's macrotools.py did for scalar calculations.

Create day07/panel.py:

"""panel.py — build a clean monthly Canadian macro panel from raw files."""
import pandas as pd

from loaders import load_boc, load_fred, load_cpi_long

START = "2006-01-01"


def monthly(series, how="last"):
    """Resample a daily Series to month-start frequency."""
    resampler = series.resample("MS")
    if how == "last":
        return resampler.last()
    if how == "mean":
        return resampler.mean()
    if how == "sum":
        return resampler.sum()
    raise ValueError(f"Unknown aggregation: {how}")


def cpi_yoy_wide(path="data/statcan_cpi.csv"):
    """Wide DataFrame of YoY inflation by component."""
    cpi = load_cpi_long(path)
    cpi["yoy"] = cpi.groupby("component")["value"].pct_change(12) * 100
    wide = cpi.pivot(index="date", columns="component", values="yoy")
    wide.columns = ["cpi_yoy_" + c.lower().replace(" ", "_").replace("-", "_")
                    for c in wide.columns]
    return wide


def build_monthly_panel(start=START):
    """Assemble all monthly indicators and derived series."""
    pieces = [
        monthly(load_boc("data/boc_overnight.csv", "overnight_rate"), "last"),
        monthly(load_boc("data/boc_yield_2yr.csv", "yield_2yr"), "mean"),
        monthly(load_boc("data/boc_yield_10yr.csv", "yield_10yr"), "mean"),
        monthly(load_boc("data/boc_cad_usd.csv", "cad_usd"), "mean"),
        load_fred("data/fred_fedfunds.csv", "fed_funds"),
        monthly(load_fred("data/fred_wti.csv", "wti"), "mean"),
        cpi_yoy_wide(),
    ]
    df = pd.concat(pieces, axis=1).loc[start:]
    df.index.name = "date"

    df["real_rate"] = df["overnight_rate"] - df["cpi_yoy_all_items"]
    df["boc_fed_diff"] = df["overnight_rate"] - df["fed_funds"]
    df["spread_10_2_bp"] = (df["yield_10yr"] - df["yield_2yr"]) * 100
    df["usd_cad"] = 1 / df["cad_usd"]
    return df


def latest_values(df):
    """Latest non-missing value and its date for every column."""
    rows = []
    for col in df.columns:
        s = df[col].dropna()
        if len(s):
            rows.append({"indicator": col, "value": round(s.iloc[-1], 3),
                         "as_of": s.index[-1].strftime("%Y-%m")})
    return pd.DataFrame(rows).set_index("indicator")

Things to notice:

• Each function does one job and can be tested alone.
• raise ValueError(...) stops the program with a clear message instead of silently returning wrong data.
• The column-renaming line turns "All-items" into cpi_yoy_all_items. Clean, lowercase names will save you pain in SQL (Days 11+).

Try it interactively:

from panel import build_monthly_panel, latest_values

panel = build_monthly_panel()
print(panel.shape)
print(latest_values(panel))

7.3 Data-Quality Checks (20 min)

Before anyone uses your panel, it should prove it's sound. Add to panel.py:

def check_panel(df):
    """Raise an error if the panel breaks basic quality rules."""
    problems = []

    # 1. Index rules
    if not df.index.is_unique:
        problems.append("duplicate dates in index")
    if not df.index.is_monotonic_increasing:
        problems.append("index not sorted")
    expected = pd.date_range(df.index.min(), df.index.max(), freq="MS")
    missing = expected.difference(df.index)
    if len(missing):
        problems.append(f"{len(missing)} missing months, e.g. {missing[0]:%Y-%m}")

    # 2. Plausible ranges (economic sanity, not statistics)
    ranges = {
        "overnight_rate": (-1, 25),
        "fed_funds": (-1, 25),
        "cpi_yoy_all_items": (-5, 20),
        "usd_cad": (0.8, 2.0),
        "wti": (0, 250),
    }
    for col, (low, high) in ranges.items():
        s = df[col].dropna()
        bad = s[(s < low) | (s > high)]
        if len(bad):
            problems.append(f"{col}: {len(bad)} values outside [{low}, {high}]")

    # 3. Completeness of core series in recent data
    recent = df.loc["2018":"2024"]
    for col in ["overnight_rate", "fed_funds", "cpi_yoy_all_items"]:
        n_missing = recent[col].isna().sum()
        if n_missing:
            problems.append(f"{col}: {n_missing} missing values in 2018–2024")

    if problems:
        raise ValueError("Panel failed checks:\n  - " + "\n  - ".join(problems))
    print(f"✓ Panel passed all checks ({len(df)} months, {df.shape[1]} columns)")

Why ranges and not just isna()? A unit error (a rate stored as 0.0225 instead of 2.25) produces no missing values but is badly wrong. Range checks catch it. On Days 41–50, Great Expectations formalises exactly these rules: "unemployment between 0 and 100", "dates continuous without gaps".

Try it 7.3

Deliberately break the panel and confirm your checks catch each problem:

broken = panel.copy()
broken.loc["2020-05-01", "overnight_rate"] = 225      # unit error
check_panel(broken)

Then try dropping a row (panel.drop(pd.Timestamp("2019-03-01"))) and duplicating one (pd.concat([panel, panel.iloc[[5]]])).

7.4 Mixing Frequencies: a Quarterly Panel with GDP (20 min)

GDP is quarterly; most of our panel is monthly. The standard approach is to aggregate monthly to quarterly, never to invent monthly GDP.

Add to panel.py:

def load_gdp(path="data/statcan_gdp.csv"):
    """Quarterly real GDP (SAAR, chained 2017 $) with growth rates."""
    df = pd.read_csv(path)
    df["date"] = pd.to_datetime(df["REF_DATE"], format="%Y-%m")
    gdp = df.set_index("date")["VALUE"].sort_index().rename("real_gdp")
    out = gdp.to_frame()
    out["gdp_qoq_ann_simple"] = gdp.pct_change() * 400             # notebook method
    out["gdp_qoq_ann"] = ((gdp / gdp.shift(1)) ** 4 - 1) * 100     # compounded
    out["gdp_yoy"] = gdp.pct_change(4) * 100
    return out


def build_quarterly_panel(monthly_df):
    """Quarterly averages of monthly indicators joined with GDP."""
    q = monthly_df.resample("QS").mean()
    q = q.join(load_gdp(), how="left")
    return q

StatCan labels quarters by their first month (2026-04 = Q2 2026), which matches resample("QS"). The two indexes line up exactly, so .join (a merge on the index) works cleanly.

from panel import build_monthly_panel, build_quarterly_panel

qpanel = build_quarterly_panel(build_monthly_panel())
print(qpanel[["real_gdp", "gdp_qoq_ann", "gdp_qoq_ann_simple",
              "overnight_rate", "real_rate"]].tail(8).round(2))

Compare the simple and compounded annualised growth: the gap is largest in extreme quarters like 2020.

Discussion: is averaging the policy rate over a quarter the right choice? What would last imply instead? There's no universal answer. Document your choice.

7.5 Capstone: build_panel.py (25 min)

Write a single script that anyone on your team can run to reproduce your datasets and charts.

Requirements:

1. Build the monthly panel and run check_panel.
2. Build the quarterly panel.
3. Save both to data/processed/ as Parquet (index included).
4. Save a latest_values table as CSV for quick inspection.
5. Produce at least two charts in day07/charts/: (a) overnight rate vs CPI inflation with the 1–3% band; (b) real GDP growth bars coloured by sign.
6. Print a short text briefing.

"""build_panel.py — reproducible monthly & quarterly macro panels.

Run from the repository root:  python day07/build_panel.py
"""
import os

import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt

from panel import (build_monthly_panel, build_quarterly_panel,
                   check_panel, latest_values)

OUT = "data/processed"
CHARTS = "day07/charts"
os.makedirs(OUT, exist_ok=True)
os.makedirs(CHARTS, exist_ok=True)

# 1–2. Build and validate
monthly = build_monthly_panel()
check_panel(monthly)
quarterly = build_quarterly_panel(monthly)

# 3–4. Save
monthly.to_parquet(f"{OUT}/macro_monthly.parquet")
quarterly.to_parquet(f"{OUT}/macro_quarterly.parquet")
latest = latest_values(monthly)
latest.to_csv(f"{OUT}/latest_values.csv")
print(f"✓ Saved panels: monthly {monthly.shape}, quarterly {quarterly.shape}")

# 5a. Overnight rate vs inflation
recent = monthly.loc["2015":]
ax = recent[["overnight_rate", "cpi_yoy_all_items"]].plot(
    figsize=(10, 4.5), title="BoC Overnight Rate vs CPI Inflation (%)")
ax.axhspan(1, 3, color="green", alpha=0.08, label="1–3% control range")
ax.legend(["Overnight rate", "CPI YoY", "1–3% range"])
plt.tight_layout()
plt.savefig(f"{CHARTS}/rate_vs_cpi.png", dpi=120)
plt.close()

# 5b. GDP growth bars
g = quarterly["gdp_qoq_ann"].dropna().loc["2015":]
colours = ["tab:blue" if v >= 0 else "tab:red" for v in g]
fig, ax = plt.subplots(figsize=(10, 4.5))
ax.bar(g.index, g.values, width=60, color=colours)
ax.axhline(0, color="grey", linewidth=0.8)
ax.set_title("Real GDP Growth (QoQ annualised, %)")
plt.tight_layout()
plt.savefig(f"{CHARTS}/gdp_growth.png", dpi=120)
plt.close()
print(f"✓ Charts saved to {CHARTS}/")

# 6. Briefing
def fmt(name, unit="%"):
    row = latest.loc[name]
    return f"{row['value']:.2f}{unit} ({row['as_of']})"

print("\n=== Canadian Macro Briefing ===")
print(f"Overnight rate:   {fmt('overnight_rate')}")
print(f"CPI inflation:    {fmt('cpi_yoy_all_items')}")
print(f"Real rate:        {fmt('real_rate')}")
print(f"BoC − Fed:        {fmt('boc_fed_diff', ' pp')}")
print(f"10y−2y spread:    {fmt('spread_10_2_bp', ' bp')}")
print(f"USD/CAD:          {fmt('usd_cad', '')}")
gdp_last = quarterly["gdp_qoq_ann"].dropna()
print(f"Real GDP growth:  {gdp_last.iloc[-1]:.1f}% ann. "
      f"({gdp_last.index[-1].year} Q{gdp_last.index[-1].quarter})")

Run it end-to-end:

python day07/build_panel.py

Stretch goals:

• Add a --start option by reading sys.argv so users can choose the start year.
• Add the Energy and Shelter contributions to headline CPI using the notebook's WEIGHTS dictionary.
• Time the script with time python day07/build_panel.py. Which step is slowest?

7.6 Recap and Bridge to Days 8–10 (10 min)

What you built, mapped to the course notebook

Day 1–3 concepts → pandas

Looking ahead

get_course_data.py was a black box. On Days 8–10 you'll open it up: HTTP requests, JSON structures, status codes, error handling, and robust reusable fetchers for the BoC Valet, FRED and StatCan APIs. That gives your panel.py a live, automated data source.

Day 7 checklist

• I can make and save line and bar charts with recession shading
• I refactored repeated code into a documented module
• My panel checks index integrity, plausible ranges and completeness
• I can combine monthly and quarterly data without inventing observations
• One command rebuilds all my datasets and charts

git add .
git commit -m "Day 7: validated macro panel, charts, build script"
git push