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/03_clean.py7# Purpose : Step 3 — build the flagged clean table, write the full exclusion8#           table and cleaning report. Nothing is silently deleted.9# =============================================================================10"""Cleaning build (Execution Order step 3)."""1112from __future__ import annotations1314import sys15from pathlib import Path1617sys.path.insert(0, str(Path(__file__).resolve().parents[1] / "src"))1819import polars as pl2021from qwhpi.clean import (22    FLOORAREA_HI, FLOORAREA_LO, PRICE_CEILING, RATIO_HI, RATIO_LO,23    build_clean, research_sample,24)25from qwhpi.config import REPORTS_DIR, TABLES_DIR, ensure_dirs26from qwhpi.geography import build_geo_join27from qwhpi.ingest import load_raw2829LINES: list[str] = []303132def note(line: str = "") -> None:33    LINES.append(line)34    print(line)353637def main() -> None:38    ensure_dirs()39    tx = load_raw()40    geo = build_geo_join(tx)41    df = build_clean(tx, geo, force=True)4243    note("## Cleaning report")44    note("")45    note(f"- Input rows: {df.height:,} (all retained; exclusions are flags)")46    note(f"- Screens: ratio band [{RATIO_LO}, {RATIO_HI}] on amount/totalArValue; "47         f"price ceiling ${PRICE_CEILING:,}; duplicate flags; "48         f"floorArea sanitation [{FLOORAREA_LO}, {FLOORAREA_HI}] m².")49    note("- Provider pre-filter verified: no transaction below $50,000 exists in the raw file.")50    note("")5152    # ------------------------------------------------------------------ #53    # Exclusion table54    # ------------------------------------------------------------------ #55    excl = (56        df.filter(pl.col("exclude_reason").is_not_null())57        .group_by("exclude_reason").len()58        .rename({"len": "n"})59        .with_columns(pl.col("n").cast(pl.Int64))60        .with_columns((pl.col("n") / df.height * 100).round(3).alias("pct_of_raw"))61        .sort("n", descending=True)62    )63    total_excl = excl["n"].sum() if excl.height else 064    excl_full = pl.concat([65        excl,66        pl.DataFrame({67            "exclude_reason": ["TOTAL_EXCLUDED", "RETAINED_IN_RESEARCH_SAMPLE"],68            "n": [total_excl, df.height - total_excl],69            "pct_of_raw": [round(total_excl / df.height * 100, 3),70                           round((df.height - total_excl) / df.height * 100, 3)],71        }),72    ])73    excl_full.write_csv(TABLES_DIR / "exclusions.csv")74    note("### Exclusion table (outputs/tables/exclusions.csv)")75    for row in excl_full.iter_rows(named=True):76        note(f"- {row['exclude_reason']}: {row['n']:,} ({row['pct_of_raw']}%)")7778    # Sanitation summary (rows kept, characteristic nulled).79    note("")80    note("### Characteristic sanitation (row retained, value nulled)")81    note(f"- floorArea outside [{FLOORAREA_LO}, {FLOORAREA_HI}] m²: "82         f"{df.filter(pl.col('fa_suspect')).height:,}")83    note(f"- yearBuilt > sale year + 1: {df.filter(pl.col('yb_suspect')).height:,}")8485    # ------------------------------------------------------------------ #86    # Research sample summary87    # ------------------------------------------------------------------ #88    rs = research_sample(df)89    rs_all = research_sample(df, include_undetermined=True)90    note("")91    note("### Research sample")92    note(f"- Clean rows incl. indéterminé: {rs_all.height:,}")93    note(f"- Headline sample (3 determined types): {rs.height:,} "94         f"({rs.height / df.height * 100:.2f}% of raw)")95    by_type = rs.group_by("propertyType").len().sort("len", descending=True)96    for row in by_type.iter_rows(named=True):97        note(f"  - {row['propertyType']}: {row['len']:,}")9899    # Weekly volume of the research sample (continuity check).100    weekly = rs.group_by("week").len().sort("week")101    note(f"- Weeks with >=1 research-sample transaction: {weekly.height} / 291")102    note(f"- Median weekly research-sample volume: {weekly['len'].median():,.0f}")103104    # Missingness within the research sample (for the hedonic step).105    miss = pl.DataFrame({106        "column": ["floorArea", "yearBuilt", "buildingType", "ownerType"],107        "pct_missing": [108            round(rs["floorArea"].is_null().mean() * 100, 2),109            round(rs["yearBuilt"].is_null().mean() * 100, 2),110            round(rs["buildingType"].is_null().mean() * 100, 2),111            round(rs["ownerType"].is_null().mean() * 100, 2),112        ],113    })114    miss.write_csv(TABLES_DIR / "research_sample_missingness.csv")115    note("")116    note("### Missingness in headline research sample")117    for row in miss.iter_rows(named=True):118        note(f"- {row['column']}: {row['pct_missing']}%")119120    report_path = REPORTS_DIR / "cleaning_report.md"121    header = (122        "<!--\n"123        "=============================================================================\n"124        "QWHPI — Quebec Weekly Housing Price Index\n"125        "Author  : Simon-Pierre Boucher\n"126        "Contact : contact@spboucher.ai\n"127        "File    : outputs/reports/cleaning_report.md\n"128        "Purpose : Step 3 cleaning report (generated by engine/scripts/03_clean.py)\n"129        "=============================================================================\n"130        "-->\n\n"131    )132    report_path.write_text(header + "\n".join(LINES) + "\n", encoding="utf-8")133    print(f"\nReport written to {report_path}")134135136if __name__ == "__main__":137    main()138