spb/qwhpi Public

1#!/usr/bin/env python32# =============================================================================3# QWHPI — Quebec Weekly Housing Price Index4# Author  : Simon-Pierre Boucher5# Contact : contact@spboucher.ai6# File    : engine/scripts/02_geography.py7# Purpose : Step 2 — spatial join of all transactions to official boundaries,8#           validation against the raw city text field, persistence.9# =============================================================================10"""Geography build (Execution Order step 2).1112Runs the spatial join (cached in ``data/processed/geo_join.parquet``),13validates the result against the raw ``city`` text field, and writes14validation tables + a report section.15"""1617from __future__ import annotations1819import sys20import time21import unicodedata22from pathlib import Path2324sys.path.insert(0, str(Path(__file__).resolve().parents[1] / "src"))2526import polars as pl2728from qwhpi.config import REPORTS_DIR, TABLES_DIR, ensure_dirs29from qwhpi.geography import SDA_VERSION, build_geo_join30from qwhpi.ingest import load_raw3132LINES: list[str] = []333435def note(line: str = "") -> None:36    LINES.append(line)37    print(line)383940def norm_name(s: str) -> str:41    """Accent/case/punctuation-insensitive normalization for name comparison."""42    if s is None:43        return ""44    s = unicodedata.normalize("NFKD", s).encode("ascii", "ignore").decode()45    return (46        s.lower().replace("saint-", "st-").replace("sainte-", "ste-")47        .replace("'", "").replace("’", "").replace(".", "").replace(" ", "-")48    )495051def main() -> None:52    ensure_dirs()53    tx = load_raw()5455    t0 = time.time()56    geo = build_geo_join(tx)57    note("## Geography spatial join (SDA 1/20k, version " + SDA_VERSION + ")")58    note("")59    note(f"- Join computed/loaded in {time.time() - t0:.1f}s for {geo.height:,} transactions")6061    method_counts = geo.group_by("join_method").len().sort("len", descending=True)62    for row in method_counts.iter_rows(named=True):63        note(f"- join_method={row['join_method']}: {row['len']:,} "64             f"({row['len'] / geo.height * 100:.3f}%)")6566    n_regions = geo["region"].n_unique()67    n_munic = geo["geo_code"].n_unique()68    note(f"- Distinct regions matched: {n_regions} | municipalities: {n_munic:,}")6970    region_dist = (71        geo.group_by(["region_code", "region"]).len()72        .sort("len", descending=True)73        .rename({"len": "n_transactions"})74    )75    region_dist.write_csv(TABLES_DIR / "geo_region_distribution.csv")76    note("")77    note("Transactions by administrative region:")78    for row in region_dist.iter_rows(named=True):79        note(f"- {row['region_code']} {row['region']}: {row['n_transactions']:,}")8081    # ---------------------------------------------------------------- #82    # Validation vs raw city text field83    # ---------------------------------------------------------------- #84    joined = tx.select("id", "city").join(geo, on="id", how="left")85    comparable = joined.filter(pl.col("municipality").is_not_null())86    comp = comparable.with_columns(87        pl.col("city").map_elements(norm_name, return_dtype=pl.Utf8).alias("city_norm"),88        pl.col("municipality").map_elements(norm_name, return_dtype=pl.Utf8).alias("mun_norm"),89    ).with_columns(90        (91            (pl.col("city_norm") == pl.col("mun_norm"))92            | pl.col("mun_norm").str.contains(pl.col("city_norm"), literal=True)93            | pl.col("city_norm").str.contains(pl.col("mun_norm"), literal=True)94        ).alias("name_match")95    )96    match_rate = comp["name_match"].mean() * 10097    note("")98    note("### Validation: joined municipality vs raw `city` text")99    note(f"- Name agreement (normalized, containment-tolerant): {match_rate:.2f}%")100101    mismatches = (102        comp.filter(~pl.col("name_match"))103        .group_by(["city", "municipality", "region"]).len()104        .sort("len", descending=True)105        .rename({"len": "n"})106    )107    mismatches.head(300).write_csv(TABLES_DIR / "geo_city_mismatch_top300.csv")108    note(f"- Mismatching pairs saved: outputs/tables/geo_city_mismatch_top300.csv "109         f"({mismatches.height:,} distinct pairs, {mismatches['n'].sum():,} transactions)")110    note("")111    note("Top 15 mismatch pairs (city text -> joined municipality):")112    for row in mismatches.head(15).iter_rows(named=True):113        note(f"- '{row['city']}' -> '{row['municipality']}' ({row['region']}): {row['n']:,}")114115    report_path = REPORTS_DIR / "geography_report.md"116    header = (117        "<!--\n"118        "=============================================================================\n"119        "QWHPI — Quebec Weekly Housing Price Index\n"120        "Author  : Simon-Pierre Boucher\n"121        "Contact : contact@spboucher.ai\n"122        "File    : outputs/reports/geography_report.md\n"123        "Purpose : Step 2 geography join + validation report (from 02_geography.py)\n"124        "=============================================================================\n"125        "-->\n\n"126    )127    report_path.write_text(header + "\n".join(LINES) + "\n", encoding="utf-8")128    print(f"\nReport written to {report_path}")129130131if __name__ == "__main__":132    main()133