Resampling, Grouping, Reshaping and Merging

A small helper we'll reuse all day. Create day06/loaders.py:

"""loaders.py — read the raw course files into clean Series."""
import pandas as pd


def load_boc(path, name):
    """Raw BoC Valet CSV (date, value) → clean daily Series."""
    df = pd.read_csv(path)
    df["date"] = pd.to_datetime(df["date"])
    df["value"] = pd.to_numeric(df["value"], errors="coerce")
    return df.set_index("date")["value"].dropna().sort_index().rename(name)


def load_fred(path, name):
    """Raw FRED CSV → clean Series (first two columns only)."""
    df = pd.read_csv(path).iloc[:, :2]
    df.columns = ["date", name]
    df["date"] = pd.to_datetime(df["date"])
    df[name] = pd.to_numeric(df[name], errors="coerce")
    return df.set_index("date")[name].dropna().sort_index()


def load_cpi_long(path="data/statcan_cpi.csv"):
    """StatCan CPI subset → long DataFrame (date, component, value)."""
    df = pd.read_csv(path).rename(columns={
        "REF_DATE": "date", "Products and product groups": "component",
        "VALUE": "value"})
    df["date"] = pd.to_datetime(df["date"], format="%Y-%m")
    return df[["date", "component", "value"]].dropna().sort_values(["component", "date"])

These are Day 5's cleaning steps wrapped in Day 3-style functions.

6.1 Resampling: Changing Frequency (30 min)

Create day06/resampling.py.

Example 1 — Daily to monthly

from loaders import load_boc

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

monthly_last = overnight.resample("MS").last()    # rate at end of each month
monthly_mean = overnight.resample("MS").mean()    # average rate during the month
print(monthly_last.tail())

resample("MS") groups by calendar month and labels each group with the month start date, matching how StatCan and FRED date monthly data.

Example 2 — Which aggregation is economically right?

cad_usd = load_boc("data/boc_cad_usd.csv", "cad_usd")
cad_monthly = cad_usd.resample("MS").mean()
print(cad_monthly.tail())

The notebook achieves the same with .dt.to_period("M").dt.to_timestamp() followed by groupby(...).mean(). resample is the shortcut.

Example 3 — Other frequencies

quarterly = overnight.resample("QS").last()      # quarter start labels
annual = overnight.resample("YS").mean()         # annual average
print(annual.round(2))

Pick one labelling convention for a project and stick with it. We use start labels (MS, QS) because StatCan does.

Example 4 — Upsampling a step function

Going from monthly to daily requires filling. For a policy rate, forward fill is correct:

m = monthly_last.loc["2025"]
daily = m.resample("D").ffill()
print(daily.head(35))

Try it 6.1

Load 10-year and 2-year yields (data/boc_yield_10yr.csv, data/boc_yield_2yr.csv). Compute monthly averages of each, then the monthly 10yr−2yr spread in bp. Which month since 2022 had the most inverted curve?

y10 = load_boc("data/boc_yield_10yr.csv", "yield_10yr").resample("MS").mean()
y2 = load_boc("data/boc_yield_2yr.csv", "yield_2yr").resample("MS").mean()

spread_bp = (y10 - y2) * 100      # pandas aligns the two series on dates automatically
recent = spread_bp.loc["2022":].dropna()
print(f"Most inverted: {recent.min():.0f} bp in {recent.idxmin():%b %Y}")

6.2 groupby: Statistics by Category and Period (25 min)

Create day06/grouping.py.

Example 1 — Latest value per CPI component

from loaders import load_cpi_long

cpi = load_cpi_long()
print(cpi["component"].value_counts())

latest = cpi.groupby("component")["value"].last()
print(latest)

groupby = split the data into groups → apply a calculation → combine the results.

Example 2 — YoY inflation per component (the notebook's method)

cpi["yoy"] = cpi.groupby("component")["value"].pct_change(12) * 100
print(cpi[cpi["date"] == cpi["date"].max()][["component", "yoy"]])

Without groupby, pct_change(12) would compare Energy with Food at the boundary between components. Grouping keeps each component's calculation separate. This is the exact line in the notebook's Cell 4.

Example 3 — Grouping by time

all_items = cpi[cpi["component"] == "All-items"].set_index("date")["yoy"]

by_year = all_items.groupby(all_items.index.year).mean()
print(by_year.loc[2015:].round(2))

decade = (all_items.index.year // 10) * 10
print(all_items.groupby(decade).mean().round(2))

Example 4 — Several statistics at once with .agg

summary = (cpi.dropna(subset=["yoy"])
              .query("date >= '2021-01-01'")
              .groupby("component")["yoy"]
              .agg(["mean", "min", "max", "std"])
              .round(2)
              .sort_values("max", ascending=False))
print(summary)

Which component drove the post-pandemic inflation surge?

6.3 Long vs Wide: pivot and melt (20 min)

The StatCan file is long: one row per date and component. Charts and regressions usually want wide: one row per date, one column per component.

LONG                                 WIDE
date        component  value         date        Energy  Food  Shelter ...
2026-06-01  Energy     ...           2026-06-01  ...     ...   ...
2026-06-01  Food       ...           2026-07-01  ...     ...   ...
2026-06-01  Shelter    ...

Example 1 — Long to wide with pivot

wide_yoy = cpi.pivot(index="date", columns="component", values="yoy")
print(wide_yoy.loc["2025":].round(1).tail())

If pivot raises "Index contains duplicate entries", your data has more than one row per date-component pair. Investigate with cpi.duplicated(["date", "component"]).sum() before reaching for pivot_table.

Example 2 — Wide to long with melt

long_again = wide_yoy.reset_index().melt(
    id_vars="date", var_name="component", value_name="yoy")
print(long_again.head())

Example 3 — Why wide is handy: comparisons across columns

latest = wide_yoy.dropna().iloc[-1].sort_values(ascending=False)
print(latest.round(1))

gap = wide_yoy["Shelter"] - wide_yoy["All-items"]
print(f"Shelter minus headline, latest: {gap.dropna().iloc[-1]:+.1f} pp")

Rule of thumb: store data long (databases, dbt models, the Parquet files in Days 11+); analyse and plot wide.

6.4 Merging and Concatenating Datasets (30 min)

Create day06/merging.py.

Example 1 — pd.merge on a date column

import pandas as pd
from loaders import load_boc, load_fred

boc_m = load_boc("data/boc_overnight.csv", "overnight_rate").resample("MS").last()
fed_m = load_fred("data/fred_fedfunds.csv", "fed_funds")

rate_diff = pd.merge(boc_m.reset_index(), fed_m.reset_index(), on="date", how="inner")
rate_diff["differential"] = rate_diff["overnight_rate"] - rate_diff["fed_funds"]
print(rate_diff.tail())

reset_index() turns the date index back into a column so merge can match on it. This mirrors the notebook's Cell 5 BoC−Fed differential.

Example 2 — Types of join

a = pd.DataFrame({"date": ["2026-01", "2026-02", "2026-03"], "boc": [2.25, 2.25, 2.25]})
b = pd.DataFrame({"date": ["2026-02", "2026-03", "2026-04"], "fed": [3.6, 3.6, 3.6]})

for how in ["inner", "left", "right", "outer"]:
    print(f"\n--- {how} ---")
    print(pd.merge(a, b, on="date", how=how))

Example 3 — Diagnosing a merge with indicator=True

check = pd.merge(boc_m.reset_index(), fed_m.reset_index(),
                 on="date", how="outer", indicator=True)
print(check["_merge"].value_counts())
print(check[check["_merge"] != "both"].tail())

This shows which dates exist in only one source. It's usually the most recent month, because agencies publish on different schedules.

Example 4 — Protecting against duplicate keys

pd.merge(boc_m.reset_index(), fed_m.reset_index(),
         on="date", how="inner", validate="one_to_one")

If either side had duplicate dates, pandas would raise an error instead of silently multiplying rows.

Example 5 — Combining many series by index with pd.concat

When every piece is a Series with the same kind of DatetimeIndex, concat along columns is the simplest tool:

cad_m = load_boc("data/boc_cad_usd.csv", "cad_usd").resample("MS").mean()
wti_m = load_fred("data/fred_wti.csv", "wti").resample("MS").mean()

panel = pd.concat([boc_m, fed_m, cad_m, wti_m], axis=1)
print(panel.loc["2024":].tail())
print(panel.isna().sum())

axis=1 means "side by side" (columns); axis=0 would stack rows. The notebook builds its boc table with reduce(lambda a, b: pd.merge(a, b, on="date", how="outer"), ...), which gives the same result as an outer concat here.

CAD/USD has many missing values before 2017. The notebook's chart title notes "Post-2017 FX API". Missing values often tell you something about the source, not the economy.

6.5 Mini-Project: Rebuild the Notebook's Derived Series (30 min)

Recreate Cell 5 of Macro_Fall_2026.ipynb using today's tools. Create day06/derived_series.py that builds one monthly DataFrame with:

Then:

1. Keep data from 2006 onward.
2. Print the latest available value of each derived series (hint: series.dropna().iloc[-1]).
3. Save to data/processed/derived_monthly.parquet.
4. Compare your latest real rate and differential with the notebook's printed values (−0.78% and −1.38% at the time the notebook was run).

import os
import pandas as pd
from loaders import load_boc, load_fred, load_cpi_long

overnight = load_boc("data/boc_overnight.csv", "overnight_rate").resample("MS").last()
fed = load_fred("data/fred_fedfunds.csv", "fed_funds")
cad = load_boc("data/boc_cad_usd.csv", "cad_usd").resample("MS").mean()

cpi = load_cpi_long()
all_items = cpi[cpi["component"] == "All-items"].set_index("date")["value"]
cpi_yoy = (all_items.pct_change(12) * 100).rename("cpi_yoy")

df = pd.concat([overnight, fed, cad, cpi_yoy], axis=1).loc["2006":]

df["real_rate"] = df["overnight_rate"] - df["cpi_yoy"]
df["boc_fed_diff"] = df["overnight_rate"] - df["fed_funds"]
df["usd_cad"] = 1 / df["cad_usd"]

for col in ["real_rate", "boc_fed_diff", "usd_cad"]:
    s = df[col].dropna()
    print(f"{col:>13}: {s.iloc[-1]:+.2f}  ({s.index[-1]:%b %Y})")

os.makedirs("data/processed", exist_ok=True)
df.to_parquet("data/processed/derived_monthly.parquet")

Day 6 checklist

• I can resample daily data to monthly/quarterly and choose last, mean or sum on economic grounds
• I can use groupby for per-category and per-period statistics
• I can move between long and wide with pivot and melt
• I can merge datasets, pick the right join, and diagnose mismatches
• I rebuilt the notebook's derived series myself

git add .
git commit -m "Day 6: resample, groupby, pivot, merge"
git push