Lesson 1.3: Aggregation, Windows and the Analysis-Ready Panel

Written lesson

Rebuild the panel

Python · Colab cell

!pip install -q polars duckdb pandas pyarrow statsmodels
import polars as pl, duckdb, pandas as pd, numpy as np

np.random.seed(7)
dates = pd.date_range("2018-01-01", periods=72, freq="ME")
provinces = ["Ontario", "Quebec", "Alberta", "Manitoba"]

rows = []
for p_i, p in enumerate(provinces):
    level = 5 + p_i * 0.4
    for t, d in enumerate(dates):
        # A commodity slump hits every province from 2021 onwards
        shock = 1.5 if d.year in (2021, 2022) else (0.8 if d.year == 2023 else 0.0)
        # Alberta's hiring subsidy (from January 2021) lowers its unemployment by 0.6 points
        subsidy = -0.6 if (p == "Alberta" and d.year >= 2021) else 0.0
        rows.append((d, p, level + shock + subsidy + np.random.normal(0, 0.3),
                     2 + 0.03 * t + np.random.normal(0, 0.2)))
panel = pl.DataFrame(rows, schema=["date", "province", "unemp_rate", "inflation_rate"], orient="row")
print(panel.head())

Grouped aggregation with expressions

Polars works with expressions: small recipes such as pl.col("x").mean() that the engine optimises and runs in parallel. group_by(...).agg([...]) applies a list of them to each group.

Python · Colab cell

summary = (
    panel.group_by("province")
    .agg(
        pl.col("unemp_rate").mean().round(2).alias("avg_unemp"),
        pl.col("unemp_rate").max().round(2).alias("peak_unemp"),
        pl.col("inflation_rate").std().round(3).alias("inflation_volatility"),
        pl.len().alias("months"),
    )
    .sort("avg_unemp", descending=True)
)
print(summary)

Rolling windows inside each unit

High-frequency series are noisy. A 3-month rolling mean smooths them, but it must be computed within each province, never across the boundary between two provinces.

Python · Colab cell

panel = panel.sort("province", "date").with_columns(
    pl.col("unemp_rate").rolling_mean(window_size=3).over("province").alias("unemp_ma3"),
    (pl.col("inflation_rate") - pl.col("inflation_rate").shift(12).over("province"))
        .alias("inflation_yoy_change"),
)
print(panel.filter(pl.col("province") == "Quebec").select("date", "unemp_rate", "unemp_ma3").head(5))

The first two values of unemp_ma3 are null for every province: there are not yet three months to average. That is correct behaviour. Filling them with something would invent data.

Pre/post baselines: a first naive comparison

Many evaluations start by comparing an outcome before and after an event. Here, suppose Alberta introduced a hiring subsidy in January 2021. A naive before/after comparison:

Python · Colab cell

POLICY = pd.Timestamp("2021-01-31")
before_after = (
    panel.with_columns((pl.col("date") >= POLICY).alias("post"))
    .group_by("province", "post")
    .agg(pl.col("unemp_rate").mean().round(2).alias("mean_unemp"))
    .pivot(on="post", index="province", values="mean_unemp")
    .rename({"false": "pre", "true": "post"})
    .with_columns((pl.col("post") - pl.col("pre")).round(2).alias("change"))
    .select("province", "pre", "post", "change")
    .sort("province")
)
print(before_after)

Unemployment rose everywhere, including in Alberta, even though we built a subsidy that lowers Alberta's unemployment by 0.6 points. A naive before/after reading would conclude the subsidy failed. Alberta's change mixes the policy with the slump that hit every province at the same time; notice that Alberta rose by less than the others.

The why

This table is the seed of the whole course. A before/after difference is not a causal effect: it needs a counterfactual. Comparing Alberta's change with the other provinces' change is the idea behind difference-in-differences (Module 11); building a weighted "synthetic Alberta" from the others is the idea behind synthetic control (Module 12).

Lazy queries, Parquet and the hand-off to pandas

For large files, use Polars' lazy API: you describe the whole pipeline, Polars optimises it (for example, reading only the columns you need), and nothing runs until .collect().

Python · Colab cell

panel.write_parquet("macro_panel.parquet")          # columnar, typed, compressed

result = (
    pl.scan_parquet("macro_panel.parquet")           # lazy: nothing is read yet
    .filter(pl.col("date") >= pd.Timestamp("2019-01-01"))
    .group_by("province")
    .agg(pl.col("unemp_rate").mean().alias("avg_unemp"))
    .sort("province")
)
print(result.explain())                                # the optimised plan
print(result.collect())

Econometrics libraries such as statsmodels still expect pandas. Convert at the very end, once the data are clean:

Python · Colab cell

import statsmodels.formula.api as smf
df = panel.drop_nulls("unemp_ma3").to_pandas()
model = smf.ols("unemp_rate ~ inflation_rate + C(province)", data=df).fit()
print(model.params.round(3))

Exercise and checklist

Exercise

Using a window function, compute for each province the deviation of unemployment from that province's pre-2020 average. Then report the average deviation in 2021 by province. Which province looks "most affected", and why can't this table alone tell you whether the Alberta subsidy worked?

Show a solution

Python · Colab cell

dev = panel.with_columns(
    (pl.col("unemp_rate")
     - pl.col("unemp_rate").filter(pl.col("date").dt.year() < 2020).mean().over("province"))
    .alias("deviation")
)
print(
    dev.filter(pl.col("date").dt.year() == 2021)
       .group_by("province").agg(pl.col("deviation").mean().round(2))
       .sort("province")
)
# All provinces share the same shock, so differences reflect the shared slump, the subsidy and noise all at once.
# Only by comparing with a credible counterfactual can we isolate the subsidy. That is what Modules 4-12 provide.

I use expressions and group_by().agg() for summaries.
Rolling statistics and lags are computed with .over(unit).
I can explain why a before/after difference is not a causal effect.
My panel is saved to Parquet and converted to pandas only for estimation.