Lesson 1.1: Colab, the Modern Stack and Tidy Data

Written lesson

Why data architecture matters for causal work

Every method in this course compares outcomes across units and over time: treated versus control, before versus after, above versus below a threshold. Those comparisons are only as good as the table they run on. If a covariate is measured after the treatment, if two sources disagree on how a province is spelled, or if a join silently drops a month, even the most advanced estimator (Double Machine Learning, synthetic control) will return a precise answer to the wrong question.

So the course starts with data engineering, not with econometrics. The goal of Module 1 is one habit: turn any raw source into a tidy panel where each row is one unit at one point in time.

The why

Causal estimators expect a 2-D matrix in which the outcome Y, the treatment D and the covariates X are aligned row by row for each observation. Getting that alignment right is most of the work in applied impact evaluation, and it is where most silent errors come from.

Colab setup and the modern stack

We work in Google Colab: a free, browser-based Python notebook, so nobody has to install anything. Every notebook in this course starts with the same idempotent setup cell. Running it twice does no harm.

Two libraries do the heavy lifting in this module:

Tool	What it is	Why we use it
Polars	A DataFrame library written in Rust	Multi-threaded and lazy, so it handles large administrative files quickly and with little memory
DuckDB	An in-process SQL database	Lets you write readable SQL directly on DataFrames, with no server to set up
pandas	The legacy standard	Still needed by `statsmodels` and many econometrics libraries, so we convert at the end

Python · Colab cell

# Module 1 · Environment setup (safe to re-run)
!pip install -q polars duckdb pandas pyarrow

import polars as pl
import duckdb
import pandas as pd
import numpy as np

pl.Config.set_tbl_rows(8)          # print at most 8 rows of a table
print("Polars", pl.__version__, "| DuckDB", duckdb.__version__)

Tip

In Colab, use Runtime → Restart and run all before you share a notebook. If it runs from top to bottom on a fresh runtime, it is reproducible.

Tidy data: wide vs long

Tidy data follows three rules:

Every variable is a column.
Every observation is a row.
Every type of observational unit is a table.

Statistical agencies rarely publish data this way. A typical table puts one region per column (wide), because it is easy to read in a spreadsheet. For causal work we need long (panel) data, where the unit (province) and the time (month) together identify each row.

We simulate two "raw" sources as they might arrive from Statistics Canada or FRED: inflation in wide format and unemployment in long format with a different column name for the date.

Python · Colab cell

np.random.seed(42)
dates = pd.date_range(start="2020-01-01", periods=48, freq="ME")   # month-ends, 2020-2023

# Source 1: inflation, WIDE (one column per province) – how many agencies publish
inflation_wide = pl.DataFrame({
    "date": dates,
    "inflation_ontario": np.random.normal(0.02, 0.005, 48).cumsum() + 2.0,
    "inflation_quebec":  np.random.normal(0.02, 0.006, 48).cumsum() + 1.8,
})

# Source 2: unemployment, LONG, but the date column has another name
unemployment_long = pl.DataFrame({
    "record_date": np.tile(dates, 2),
    "province": ["Ontario"] * 48 + ["Quebec"] * 48,
    "unemp_rate": np.random.normal(5.5, 0.5, 96),
})

print(inflation_wide.head(4))
print(unemployment_long.head(4))

Look at the two outputs. In the first table the province is hidden inside column names; it is not a variable yet. That is what makes it untidy.

Un-pivoting and cleaning keys with Polars

unpivot (called melt in older code) turns columns into rows. Then we clean the province names so they match the unemployment table exactly: "inflation_ontario" must become "Ontario".

Python · Colab cell

inflation_tidy = inflation_wide.unpivot(
    index="date",
    on=["inflation_ontario", "inflation_quebec"],
    variable_name="province_raw",
    value_name="inflation_rate",
)

inflation_clean = (
    inflation_tidy
    .with_columns(
        pl.col("province_raw")
          .str.replace("inflation_", "")
          .str.to_titlecase()
          .alias("province")
    )
    .drop("province_raw")
    .select("date", "province", "inflation_rate")
)

print(inflation_clean.head(4))
print(inflation_clean.shape)   # 48 months x 2 provinces = 96 rows

The why

A join only matches keys that are identical. "ontario", "Ontario " and "ON" are three different keys to a computer. Cleaning keys before joining is cheaper than discovering missing rows after an estimate looks strange.

Before moving on, always check that each (unit, time) pair appears exactly once. A duplicate key will double-count observations in every regression that follows.

Python · Colab cell

dupes = (
    inflation_clean
    .group_by("province", "date")
    .agg(pl.len().alias("n"))
    .filter(pl.col("n") > 1)
)
print("Duplicated province-month keys:", dupes.height)
assert dupes.height == 0

Exercise: tidy a second messy table

Exercise

A third source reports the policy interest rate with one row per year and one column per month (jan, feb, …, dec). Build it, then reshape it into a tidy table with columns date and policy_rate.

Create rates_wide with a year column (2020–2023) and 12 month columns filled with random rates.
Un-pivot the month columns.
Build a proper month-end date from year and the month name.
Check there are exactly 48 rows and no duplicated dates.

Show a solution

Python · Colab cell

months = ["jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"]
rates_wide = pl.DataFrame(
    {"year": [2020, 2021, 2022, 2023]}
    | {m: np.round(np.random.uniform(0.25, 5.0, 4), 2) for m in months}
)
rates_tidy = (
    rates_wide
    .unpivot(index="year", variable_name="month", value_name="policy_rate")
    .with_columns(
        pl.col("month").replace_strict({m: i + 1 for i, m in enumerate(months)}).alias("m")
    )
    .with_columns(pl.date(pl.col("year"), pl.col("m"), 1).dt.month_end().alias("date"))
    .select("date", "policy_rate")
    .sort("date")
)
print(rates_tidy.head())
assert rates_tidy.height == 48 and rates_tidy["date"].n_unique() == 48

Checklist and recap

My notebook starts with a setup cell and runs top to bottom on a fresh runtime.
I can say which of my tables are wide and which are tidy.
I can un-pivot a wide table and clean the identifier column.
I check for duplicated (unit, time) keys before joining.

Next lesson: joining the tidy tables with SQL in DuckDB, and the join mistakes that quietly bias estimates.