The Money lens, end to end¶

This is one of the two lenses the public build exposes (the other is Development). It answers: how much assessed value, and how much municipal tax revenue, does each neighbourhood carry per acre?

How to read this notebook¶

It imports the real src/ modules and runs the same calls, in the same order, as main.run(). It does not reimplement the pipeline — if it drifts from production, that is a bug in this notebook, not a difference of opinion.

Two rules govern everything below, and they exist because the previous walkthrough notebook rotted silently:

  • No number is written by hand. Every figure in the prose is interpolated from the run you are reading. The data refreshes weekly, so any hand-typed count is wrong within days.
  • Invariants are asserted, values are not. dropped == 50 would be a weekly false alarm. aggregation preserves total assessed value must hold for any data, forever — so that is what gets pinned.

Checks accumulate and are reported together at the end, so one failure does not hide the rest.

In [1]:
import json
import logging
import os
import sys
from datetime import datetime, timezone
from pathlib import Path

import pandas as pd

# Resolve the repo root. nbconvert executes with cwd set to the notebook's own
# directory — which is a build directory, not the repo — so the runner passes
# the root explicitly. The cwd search is the fallback for running interactively
# in Jupyter. (`__file__` is not available in a kernel, so it is no use here.)
def _find_root() -> Path:
    env = os.environ.get("EDMONTON_REPO_ROOT")
    if env:
        return Path(env).resolve()
    here = Path.cwd()
    for p in (here, *here.parents):
        if (p / "src" / "join_and_calculate.py").exists():
            return p
    raise RuntimeError(
        "cannot locate the repo root — set EDMONTON_REPO_ROOT or run from inside the repo"
    )


REPO_ROOT = _find_root()
sys.path.insert(0, str(REPO_ROOT))

# Input paths and the rate year come from main.py rather than being restated
# here. Restating them is exactly how a documentation notebook drifts from the
# pipeline it claims to document — it would keep passing while describing files
# production no longer reads. main.py is import-safe and puts src/ on the path.
import main  # noqa: E402

logging.basicConfig(stream=sys.stdout, level=logging.INFO, format="%(name)s: %(message)s")

CHECKS: list[tuple[bool, str, str]] = []


def check(ok: bool, claim: str, detail: str = "") -> None:
    """Record an invariant. Reported together at the end; never stops the run."""
    CHECKS.append((bool(ok), claim, detail))
    print(f"  [{'PASS' if ok else 'FAIL'}] {claim}{'  — ' + detail if detail else ''}")


ASSESSMENT_CSV = main.ASSESSMENT_CSV
BOUNDARIES = main.BOUNDARIES_GEOJSON
MILL_RATES = main.MILL_RATES_JSON
ASSESSMENT_YEAR = main.ASSESSMENT_YEAR

vintage = datetime.fromtimestamp(ASSESSMENT_CSV.stat().st_mtime, timezone.utc)
print(f"repo root:        {REPO_ROOT}")
print(f"assessment roll:  {ASSESSMENT_CSV.name}")
print(f"data vintage:     {vintage:%Y-%m-%d %H:%M UTC}  (file mtime)")
print(f"mill-rate year:   {ASSESSMENT_YEAR}  (from main.ASSESSMENT_YEAR)")
print(f"executed:         {datetime.now(timezone.utc):%Y-%m-%d %H:%M UTC}")

repo root:        /home/runner/work/edmonton-tax-viz/edmonton-tax-viz
assessment roll:  Property_Assessment_Data__Current_Calendar_Year_.csv
data vintage:     2026-10-12 17:22 UTC  (file mtime)
mill-rate year:   2026  (from main.ASSESSMENT_YEAR)
executed:         2026-10-12 17:25 UTC

Step 1 — Load the assessment roll¶

One row per property. load_assessment normalises neighbourhood names so they will match the boundary file later, applies the documented name corrections, drops rows with null or zero assessed value, and flags tax-exempt rows without removing them.

That last distinction is the one worth understanding: exclusion is a policy choice, not a data-cleaning step. Dropping a zero-value row is arithmetic hygiene — it cannot change a ratio. Removing exempt institutional land would change what the map claims, so the pipeline refuses to make that call silently. Watch the log lines below: the drop is reported as a drop, the flag as a flag.

In [2]:
from load_assessment import load_assessment  # noqa: E402

assessment = load_assessment(str(ASSESSMENT_CSV))
print(f"\nrows: {len(assessment):,}   columns: {assessment.shape[1]}")
assessment.head()

load_assessment: Flagged 2 tax-exempt rows (Assessment Class 1 == 'NONRES MUNICIPAL/RES EDUCATION')

load_assessment: Dropping 53 rows with null or zero assessed_value

rows: 439,643   columns: 13
Out[2]:
account_number neighbourhood_name assessed_value is_exempt tax_class assessment_class_1 assessment_class_2 assessment_class_3 assessment_class_pct_1 assessment_class_pct_2 assessment_class_pct_3 latitude longitude
0 11137148 SECORD 4000.0 False Residential RESIDENTIAL NaN NaN 100 NaN NaN 53.527070 -113.707019
1 11140552 ASTER 570000.0 False Residential RESIDENTIAL NaN NaN 100 NaN NaN 53.450942 -113.362159
2 10704838 AMBLESIDE 287000.0 False Residential RESIDENTIAL NaN NaN 100 NaN NaN 53.433782 -113.600287
3 4108601 ALBERTA AVENUE 185500.0 False Residential RESIDENTIAL NaN NaN 100 NaN NaN 53.568288 -113.485282
4 10712364 ALLARD 445500.0 False Residential RESIDENTIAL NaN NaN 100 NaN NaN 53.396268 -113.531481

Step 2 — Apply the mill rates¶

The municipal levy for a property is not one rate times one value. A parcel can be split across up to three assessment classes, each with its own percentage and its own rate:

$$\text{levy} = \text{assessed value} \times \sum_{\text{slot}} \frac{\text{pct}_{\text{slot}}}{100} \times \frac{\text{rate}[\text{class}_{\text{slot}}]}{1000}$$

res_levy and nonres_levy are subsets of levy — a split-class parcel contributes only its residential slice to res_levy. They do not sum to levy, because other rate classes (farmland, machinery) exist. That relationship is the invariant worth pinning.

In [3]:
from apply_tax_rates import apply_tax_rates  # noqa: E402

rates_file = json.loads(MILL_RATES.read_text())
published_years = sorted(rates_file["rates"].keys())
print(f"mill_rates.json publishes: {published_years}")
print(f"pipeline asks for:         {ASSESSMENT_YEAR}\n")

check(
    str(ASSESSMENT_YEAR) in rates_file["rates"],
    "the rate year main.py asks for exists in mill_rates.json",
    f"{ASSESSMENT_YEAR} in {published_years} — the January year-roll breaks this first",
)

assessment = apply_tax_rates(assessment, MILL_RATES, ASSESSMENT_YEAR)

levy_total = assessment["levy"].sum()
res_total = assessment["res_levy"].sum()
nonres_total = assessment["nonres_levy"].sum()

print(f"\ncitywide municipal levy: ${levy_total:,.0f}")
print(f"  residential slices:    ${res_total:,.0f}  ({res_total / levy_total:.1%})")
print(f"  non-residential:       ${nonres_total:,.0f}  ({nonres_total / levy_total:.1%})")
print(f"  other classes:         ${levy_total - res_total - nonres_total:,.0f}")

check(
    res_total + nonres_total <= levy_total * 1.0000001,
    "res + nonres levy never exceeds total levy",
    f"${res_total + nonres_total:,.0f} <= ${levy_total:,.0f}",
)
check(levy_total > 0, "citywide levy is positive")
check(
    (assessment["levy"] < 0).sum() == 0,
    "no property carries a negative levy",
)

mill_rates.json publishes: ['2025', '2026']
pipeline asks for:         2026

  [PASS] the rate year main.py asks for exists in mill_rates.json  — 2026 in ['2025', '2026'] — the January year-roll breaks this first
apply_tax_rates: 80 propert(ies) with class % not summing to 100 — billed as-stated, not normalized

apply_tax_rates: Computed municipal levy for 439643 properties (year 2026); total $2,783,390,245 (residential-class $1,448,013,937 = 52.0%, non-res-rate $1,334,836,783 = 48.0%)

citywide municipal levy: $2,783,390,245
  residential slices:    $1,448,013,937  (52.0%)
  non-residential:       $1,334,836,783  (48.0%)
  other classes:         $539,525
  [PASS] res + nonres levy never exceeds total levy  — $2,782,850,720 <= $2,783,390,245
  [PASS] citywide levy is positive
  [PASS] no property carries a negative levy

Step 3 — Aggregate to neighbourhoods¶

Collapse ~440k property rows to one row per neighbourhood. This is the step that makes the whole project a neighbourhood analysis rather than a parcel one — and it is why docs/PARCEL_LEVEL_OPPORTUNITIES.md exists as a separate backlog.

Aggregation is a pure sum, so it must be value-preserving. If this invariant ever fails, a neighbourhood is being dropped or double-counted.

In [4]:
from aggregate_by_neighbourhood import aggregate_by_neighbourhood  # noqa: E402

aggregated = aggregate_by_neighbourhood(assessment)

value_before = assessment["assessed_value"].sum()
value_after = aggregated["total_assessed_value"].sum()
revenue_after = aggregated["total_revenue"].sum()

print(f"\nneighbourhoods: {len(aggregated):,}")
print(f"assessed value  before ${value_before:,.0f}  after ${value_after:,.0f}")

check(
    abs(value_before - value_after) < 1.0,
    "aggregation preserves total assessed value",
    f"delta ${abs(value_before - value_after):,.4f}",
)
check(
    abs(levy_total - revenue_after) < 1.0,
    "aggregation preserves total levy",
    f"delta ${abs(levy_total - revenue_after):,.4f}",
)
check(
    aggregated["neighbourhood_name"].is_unique,
    "one row per neighbourhood after aggregation",
)

aggregated.sort_values("total_assessed_value", ascending=False).head(10)

aggregate_by_neighbourhood: Aggregated 439643 properties into 407 neighbourhoods

neighbourhoods: 407
assessed value  before $238,662,305,857  after $238,662,305,857
  [PASS] aggregation preserves total assessed value  — delta $0.0000
  [PASS] aggregation preserves total levy  — delta $0.0000
  [PASS] one row per neighbourhood after aggregation
Out[4]:
neighbourhood_name total_assessed_value total_revenue total_res_revenue total_nonres_revenue
109 DOWNTOWN 7.787736e+09 1.463142e+08 2.306416e+07 1.232500e+08
402 WÎHKWÊNTÔWIN 4.198990e+09 4.594036e+07 2.776856e+07 1.817179e+07
397 WINDERMERE 3.708288e+09 3.276745e+07 2.695548e+07 5.804146e+06
82 CHAPPELLE 3.303812e+09 2.983873e+07 2.390248e+07 5.935647e+06
358 SUMMERSIDE 3.251272e+09 3.012595e+07 2.308340e+07 7.042552e+06
194 KESWICK 2.687904e+09 2.297987e+07 1.991700e+07 3.062660e+06
210 LAUREL 2.585409e+09 2.073266e+07 1.981975e+07 9.129046e+05
336 SECORD 2.376065e+09 2.061900e+07 1.759915e+07 3.019845e+06
377 WALKER 2.370254e+09 2.161346e+07 1.695026e+07 4.663196e+06
367 THE ORCHARDS AT ELLERSLIE 2.275520e+09 1.930221e+07 1.693284e+07 2.366926e+06

Step 4 — Citywide revenue share, computed before the boundary join¶

main.run() computes each neighbourhood's share of citywide revenue here, against the full roll — deliberately, and the ordering is load-bearing.

The boundary join in step 6 can drop a neighbourhood the roll knows about. Deriving the share after that join (or in the browser, by summing the served features) would renormalise over whatever survived — so a neighbourhood's "% of city revenue" would silently depend on who else made it onto the map. Computing it here means the denominator is the real city.

In [5]:
city_revenue = aggregated["total_revenue"].sum()
aggregated["pct_of_city_revenue"] = (aggregated["total_revenue"] / city_revenue * 100).round(4)

top = aggregated.nlargest(5, "pct_of_city_revenue")[["neighbourhood_name", "pct_of_city_revenue"]]
print(f"citywide revenue denominator: ${city_revenue:,.0f}")
print(f"shares sum to: {aggregated['pct_of_city_revenue'].sum():.2f}%\n")

check(
    abs(aggregated["pct_of_city_revenue"].sum() - 100.0) < 0.05,
    "revenue shares sum to 100% across the full roll",
    f"{aggregated['pct_of_city_revenue'].sum():.4f}%",
)
top

citywide revenue denominator: $2,783,390,245
shares sum to: 100.00%

  [PASS] revenue shares sum to 100% across the full roll  — 100.0005%
Out[5]:
neighbourhood_name pct_of_city_revenue
109 DOWNTOWN 5.2567
374 UNIVERSITY OF ALBERTA 1.8920
402 WÎHKWÊNTÔWIN 1.6505
252 MISTATIM INDUSTRIAL 1.3892
357 SUMMERLEA 1.3021

Step 5 — Neighbourhood boundaries and area¶

load_boundaries returns geometry plus area_acres. The CRS is set explicitly before any area calculation — projecting after measuring would silently produce wrong acreages, which is why the project rule says to set it up front rather than trusting a file's declared CRS.

In [6]:
from load_boundaries import load_boundaries  # noqa: E402

boundaries = load_boundaries(str(BOUNDARIES))
print(f"boundaries: {len(boundaries):,}   CRS: {boundaries.crs}")
print(f"total area: {boundaries['area_acres'].sum():,.0f} acres")

check(boundaries.crs is not None, "boundaries carry an explicit CRS")
check(
    (boundaries["area_acres"] > 0).all(),
    "every boundary has positive area",
    "a zero would make value_per_acre infinite",
)
boundaries[["neighbourhood_name", "area_acres"]].head()

load_boundaries: Loaded 407 boundary features (input CRS: EPSG:4326)

load_boundaries: area_acres range: 26.6 – 13180.4

boundaries: 407   CRS: EPSG:3400
total area: 193,263 acres
  [PASS] boundaries carry an explicit CRS
  [PASS] every boundary has positive area  — a zero would make value_per_acre infinite
Out[6]:
neighbourhood_name area_acres
0 ABBOTTSFIELD 101.104033
1 ALBANY 208.137756
2 ALBERTA AVENUE 414.762759
3 ALBERTA PARK INDUSTRIAL 164.040076
4 ALCES 952.916247

Step 6 — Join, and account for every unmatched name¶

join_and_calculate left-joins boundaries → assessment and computes value_per_acre and revenue_per_acre. Every optional service layer is left at None here, so this is the Money lens alone; the cost lenses that /full/ adds are a separate notebook.

No silent data drops is the project's core rule, so the point of this step is not that the join succeeds — it is that anything unmatched is named. Watch for the warning about the known straggler documented in data/DATA.md.

In [7]:
from join_and_calculate import join_and_calculate  # noqa: E402

joined = join_and_calculate(aggregated, boundaries)

matched = joined["total_assessed_value"].notna().sum()
print(f"\nfeatures: {len(joined):,}   with assessment: {matched:,}")

roll_names = set(aggregated["neighbourhood_name"])
boundary_names = set(boundaries["neighbourhood_name"])
in_roll_only = sorted(roll_names - boundary_names)
in_boundary_only = sorted(boundary_names - roll_names)

print(f"\nin roll but not on the map ({len(in_roll_only)}): {in_roll_only}")
print(f"on the map but not in the roll ({len(in_boundary_only)}): {len(in_boundary_only)} names")

check(
    len(joined) == len(boundaries),
    "the join is boundary-preserving (left join, no row multiplication)",
    f"{len(joined):,} == {len(boundaries):,}",
)
check(
    joined["neighbourhood_name"].is_unique,
    "no boundary was duplicated by the join",
)
check(
    matched > len(joined) * 0.9,
    "over 90% of boundaries matched an assessment row",
    f"{matched:,}/{len(joined):,} = {matched / len(joined):.1%}",
)

join_and_calculate: 1 assessment neighbourhood(s) with no boundary match (excluded from map):
  OLIVER

join_and_calculate: 1 boundary neighbourhood(s) with no assessment data:
  LEWIS FARMS

join_and_calculate: Joined 407 boundary neighbourhoods; 406 with value_per_acre calculated

features: 407   with assessment: 406

in roll but not on the map (1): ['OLIVER']
on the map but not in the roll (1): 1 names
  [PASS] the join is boundary-preserving (left join, no row multiplication)  — 407 == 407
  [PASS] no boundary was duplicated by the join
  [PASS] over 90% of boundaries matched an assessment row  — 406/407 = 99.8%

Step 7 — The published metric, checked by hand¶

value_per_acre = total_assessed_value / area_acres. Recomputing it independently for the top neighbourhoods is the cheapest possible guard against a units or denominator error — the failure mode this project actually has.

In [8]:
have = joined[joined["value_per_acre"].notna()].copy()
have["recomputed"] = have["total_assessed_value"] / have["area_acres"]
have["delta"] = (have["value_per_acre"] - have["recomputed"]).abs()

worst = have["delta"].max()
check(
    worst < 0.01,
    "value_per_acre equals total_assessed_value / area_acres for every feature",
    f"largest disagreement ${worst:.6f}",
)

if "revenue_per_acre" in have.columns:
    rev = have[have["revenue_per_acre"].notna()].copy()
    rev_delta = (rev["revenue_per_acre"] - rev["total_revenue"] / rev["area_acres"]).abs().max()
    check(
        rev_delta < 0.01,
        "revenue_per_acre equals total_revenue / area_acres for every feature",
        f"largest disagreement ${rev_delta:.6f}",
    )

cols = ["neighbourhood_name", "total_assessed_value", "area_acres", "value_per_acre"]
if "revenue_per_acre" in have.columns:
    cols.append("revenue_per_acre")
have.nlargest(10, "value_per_acre")[cols]

  [PASS] value_per_acre equals total_assessed_value / area_acres for every feature  — largest disagreement $0.000000
  [PASS] revenue_per_acre equals total_revenue / area_acres for every feature  — largest disagreement $0.000000
Out[8]:
neighbourhood_name total_assessed_value area_acres value_per_acre revenue_per_acre
109 DOWNTOWN 7.787736e+09 570.634403 1.364751e+07 256406.120876
402 WÎHKWÊNTÔWIN 4.198990e+09 421.511381 9.961749e+06 108989.597411
142 GARNEAU 1.815364e+09 204.606492 8.872465e+06 86509.699840
374 UNIVERSITY OF ALBERTA 2.242118e+09 295.185819 7.595614e+06 178401.220154
357 SUMMERLEA 1.627544e+09 271.601909 5.992388e+06 133441.298853
53 BOYLE STREET 1.129263e+09 217.675919 5.187818e+06 60422.720560
352 STRATHCONA 1.908900e+09 385.835534 4.947443e+06 50172.543948
398 WINDSOR PARK 8.475745e+08 187.086054 4.530399e+06 36713.297781
222 MAGRATH HEIGHTS 1.281178e+09 303.520292 4.221062e+06 35026.068805
221 MACTAGGART 1.027532e+09 256.557412 4.005074e+06 36634.471214

What this notebook does not cover¶

Stated explicitly so its silence is never mistaken for a clean bill of health:

  • the lot-acre denominator (lot_acres_eligible, parcel_frac) and its cardinality guard — the ineligible_points drift is an open investigation;
  • the Development lens (building permits) — the public build's other lens;
  • every cost lens that /full/ adds: services, transport, temporal, ratio;
  • the web export itself — setback, simplification, and the served column contract, which scripts/check_served_columns.py already guards.
In [9]:
failed = [c for c in CHECKS if not c[0]]
print(f"{len(CHECKS) - len(failed)}/{len(CHECKS)} invariants held\n")
for ok, claim, detail in CHECKS:
    print(f"  [{'PASS' if ok else 'FAIL'}] {claim}{'  — ' + detail if detail else ''}")

if failed:
    raise AssertionError(
        f"{len(failed)} invariant(s) failed:\n"
        + "\n".join(f"  - {claim} {detail}" for _, claim, detail in failed)
    )
print("\nAll invariants held.")

15/15 invariants held

  [PASS] the rate year main.py asks for exists in mill_rates.json  — 2026 in ['2025', '2026'] — the January year-roll breaks this first
  [PASS] res + nonres levy never exceeds total levy  — $2,782,850,720 <= $2,783,390,245
  [PASS] citywide levy is positive
  [PASS] no property carries a negative levy
  [PASS] aggregation preserves total assessed value  — delta $0.0000
  [PASS] aggregation preserves total levy  — delta $0.0000
  [PASS] one row per neighbourhood after aggregation
  [PASS] revenue shares sum to 100% across the full roll  — 100.0005%
  [PASS] boundaries carry an explicit CRS
  [PASS] every boundary has positive area  — a zero would make value_per_acre infinite
  [PASS] the join is boundary-preserving (left join, no row multiplication)  — 407 == 407
  [PASS] no boundary was duplicated by the join
  [PASS] over 90% of boundaries matched an assessment row  — 406/407 = 99.8%
  [PASS] value_per_acre equals total_assessed_value / area_acres for every feature  — largest disagreement $0.000000
  [PASS] revenue_per_acre equals total_revenue / area_acres for every feature  — largest disagreement $0.000000

All invariants held.