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/01_profile.py7# Purpose : Step 1 audit — schema, descriptives, missingness, outlier scan,8#           coverage, duplicates, and weekly liquidity matrices. No index yet.9# =============================================================================10"""Full dataset audit (Execution Order step 1).1112Idempotent: re-running overwrites its own outputs. Writes tables to13``outputs/tables/`` and a human-readable report to14``outputs/reports/audit_report.md``. The raw CSV is never modified.15"""1617from __future__ import annotations1819import sys20from pathlib import Path2122sys.path.insert(0, str(Path(__file__).resolve().parents[1] / "src"))2324import polars as pl2526from qwhpi.config import REPORTS_DIR, TABLES_DIR, ensure_dirs27from qwhpi.ingest import load_raw2829pl.Config.set_tbl_rows(50)3031REPORT_LINES: list[str] = []323334def note(line: str = "") -> None:35    REPORT_LINES.append(line)36    print(line)373839def save(df: pl.DataFrame, name: str) -> None:40    path = TABLES_DIR / f"{name}.csv"41    df.write_csv(path)42    note(f"  -> table saved: outputs/tables/{name}.csv ({df.height} rows)")434445def main() -> None:46    ensure_dirs()47    df = load_raw()4849    note("# QWHPI Data Audit Report")50    note("")51    note("Author: Simon-Pierre Boucher — contact@spboucher.ai")52    note("")5354    # ------------------------------------------------------------------ #55    # 1. Schema & basic shape56    # ------------------------------------------------------------------ #57    note("## 1. Schema and shape")58    note("")59    note(f"- Rows: {df.height:,}")60    note(f"- Columns: {df.width}")61    note(f"- Date range: {df['date'].min()} -> {df['date'].max()}")62    n_weeks = df["week"].n_unique()63    note(f"- Distinct Monday-labeled weeks with >=1 transaction: {n_weeks}")64    note(f"- Distinct cities (raw text field): {df['city'].n_unique():,}")65    note(f"- Distinct ids: {df['id'].n_unique():,} (duplicated ids: {df.height - df['id'].n_unique():,})")66    note("")67    schema_tbl = pl.DataFrame(68        {"column": df.columns, "dtype": [str(t) for t in df.dtypes]}69    )70    save(schema_tbl, "audit_schema")7172    # ------------------------------------------------------------------ #73    # 2. Missingness74    # ------------------------------------------------------------------ #75    note("")76    note("## 2. Missingness")77    note("")78    nulls = df.null_count().transpose(include_header=True,79                                      header_name="column",80                                      column_names=["n_missing"])81    nulls = nulls.with_columns(82        (pl.col("n_missing") / df.height * 100).round(2).alias("pct_missing")83    ).sort("n_missing", descending=True)84    save(nulls, "audit_missingness")85    for row in nulls.filter(pl.col("n_missing") > 0).iter_rows(named=True):86        note(f"- `{row['column']}`: {row['n_missing']:,} missing ({row['pct_missing']}%)")8788    # Sentinel-style missingness that null counts miss.89    note("")90    note("Sentinel / degenerate values (not encoded as null):")91    sentinels = pl.DataFrame({92        "check": [93            "amount <= 0", "amount in (1, 2)", "amount < 5000",94            "floorArea <= 0", "floorArea > 2000 m2",95            "yearBuilt < 1600", "yearBuilt > 2026", "yearBuilt == 0",96            "previousValue <= 0", "totalArValue <= 0",97            "lat/lng outside Quebec bbox (44.9-62.7N, -79.8--57W)",98        ],99        "n": [100            df.filter(pl.col("amount") <= 0).height,101            df.filter(pl.col("amount").is_in([1, 2])).height,102            df.filter(pl.col("amount") < 5000).height,103            df.filter(pl.col("floorArea") <= 0).height,104            df.filter(pl.col("floorArea") > 2000).height,105            df.filter(pl.col("yearBuilt") < 1600).height,106            df.filter(pl.col("yearBuilt") > 2026).height,107            df.filter(pl.col("yearBuilt") == 0).height,108            df.filter(pl.col("previousValue") <= 0).height,109            df.filter(pl.col("totalArValue") <= 0).height,110            df.filter(111                (pl.col("lat") < 44.9) | (pl.col("lat") > 62.7)112                | (pl.col("lng") < -79.8) | (pl.col("lng") > -57.0)113            ).height,114        ],115    })116    save(sentinels, "audit_sentinel_values")117    for row in sentinels.iter_rows(named=True):118        note(f"- {row['check']}: {row['n']:,}")119120    # ------------------------------------------------------------------ #121    # 3. Categorical distributions122    # ------------------------------------------------------------------ #123    note("")124    note("## 3. Categorical distributions")125    for col in ("propertyType", "buildingType", "ownerType"):126        note("")127        note(f"### {col}")128        dist = (129            df.group_by(col).len().sort("len", descending=True)130            .with_columns((pl.col("len") / df.height * 100).round(2).alias("pct"))131        )132        save(dist, f"audit_dist_{col}")133        for row in dist.head(15).iter_rows(named=True):134            note(f"- {row[col]}: {row['len']:,} ({row['pct']}%)")135136    # ------------------------------------------------------------------ #137    # 4. Price descriptives (by property type)138    # ------------------------------------------------------------------ #139    note("")140    note("## 4. Amount descriptives by propertyType (amount > 0)")141    note("")142    pos = df.filter(pl.col("amount") > 0)143    desc = (144        pos.group_by("propertyType")145        .agg(146            n=pl.len(),147            p01=pl.col("amount").quantile(0.01),148            p05=pl.col("amount").quantile(0.05),149            p25=pl.col("amount").quantile(0.25),150            median=pl.col("amount").median(),151            p75=pl.col("amount").quantile(0.75),152            p95=pl.col("amount").quantile(0.95),153            p99=pl.col("amount").quantile(0.99),154            max=pl.col("amount").max(),155            mean=pl.col("amount").mean().round(0),156        )157        .sort("n", descending=True)158    )159    save(desc, "audit_amount_by_type")160    for row in desc.iter_rows(named=True):161        note(162            f"- {row['propertyType']}: n={row['n']:,} | p01=${row['p01']:,.0f} "163            f"| median=${row['median']:,.0f} | p99=${row['p99']:,.0f} | max=${row['max']:,.0f}"164        )165166    # Same for hedonic characteristics.167    char_desc = (168        df.group_by("propertyType")169        .agg(170            n=pl.len(),171            floorArea_med=pl.col("floorArea").median(),172            floorArea_p99=pl.col("floorArea").quantile(0.99),173            yearBuilt_med=pl.col("yearBuilt").median(),174            yearBuilt_min=pl.col("yearBuilt").min(),175            floorArea_miss_pct=(pl.col("floorArea").is_null().mean() * 100).round(2),176            yearBuilt_miss_pct=(pl.col("yearBuilt").is_null().mean() * 100).round(2),177            buildingType_miss_pct=(pl.col("buildingType").is_null().mean() * 100).round(2),178        )179        .sort("n", descending=True)180    )181    save(char_desc, "audit_characteristics_by_type")182183    # ------------------------------------------------------------------ #184    # 5. Duplicates185    # ------------------------------------------------------------------ #186    note("")187    note("## 5. Duplicate scan")188    note("")189    full_dupes = df.height - df.unique(subset=[c for c in df.columns if c not in ("id",)]).height190    addr_date = df.height - df.unique(subset=["street", "city", "date"]).height191    addr_date_amt = df.height - df.unique(subset=["street", "city", "date", "amount"]).height192    note(f"- Exact duplicates ignoring id: {full_dupes:,}")193    note(f"- Same street+city+date (candidate multi-unit or true dupes): {addr_date:,}")194    note(f"- Same street+city+date+amount (strong duplicate candidates): {addr_date_amt:,}")195196    # Repeat-sales potential: same street+city appearing on different dates.197    repeat = (198        df.group_by(["street", "city"]).agg(n_dates=pl.col("date").n_unique())199        .filter(pl.col("n_dates") >= 2)200    )201    note(f"- Addresses (street+city) with >=2 distinct sale dates: {repeat.height:,}")202203    # ------------------------------------------------------------------ #204    # 6. Weekly coverage & liquidity205    # ------------------------------------------------------------------ #206    note("")207    note("## 6. Weekly coverage and liquidity")208    note("")209    weekly = df.group_by("week").len().sort("week")210    first, last = weekly["week"].min(), weekly["week"].max()211    grid = pl.DataFrame({"week": pl.date_range(first, last, "1w", eager=True)})212    weekly_full = grid.join(weekly, on="week", how="left").fill_null(0)213    zero_weeks = weekly_full.filter(pl.col("len") == 0)214    note(f"- Continuous Monday grid: {grid.height} weeks ({first} -> {last})")215    note(f"- Zero-transaction weeks on the grid: {zero_weeks.height}")216    note(f"- Median tx/week (province): {weekly_full['len'].median():,.0f}")217    note(f"- Min tx/week: {weekly_full['len'].min():,} | Max: {weekly_full['len'].max():,}")218    tail = weekly_full.tail(6)219    note(f"- Last 6 weeks (partial-week check): "220         + ", ".join(f"{r['week']}={r['len']}" for r in tail.iter_rows(named=True)))221    save(weekly_full.rename({"len": "transactions"}), "audit_weekly_volume_province")222223    # Liquidity matrix: median weekly tx for top cities x property type.224    top_cities = (225        df.group_by("city").len().sort("len", descending=True).head(25)["city"].to_list()226    )227    liq = (228        df.filter(pl.col("city").is_in(top_cities))229        .group_by(["city", "propertyType", "week"]).len()230        .group_by(["city", "propertyType"])231        .agg(232            median_wk=pl.col("len").median(),233            mean_wk=pl.col("len").mean().round(1),234            weeks_present=pl.len(),235        )236        .with_columns(237            pl.col("weeks_present").truediv(grid.height).mul(100).round(1).alias("pct_weeks_present")238        )239        .sort(["city", "propertyType"])240    )241    save(liq, "audit_weekly_liquidity_topcities")242243    pivot = (244        liq.pivot(values="median_wk", index="city", on="propertyType")245        .sort("unifamilial", descending=True, nulls_last=True)246    )247    save(pivot, "audit_liquidity_matrix")248    note("")249    note("Median weekly transactions, top cities (rows) x type (cols): see audit_liquidity_matrix.csv")250    for row in pivot.head(12).iter_rows(named=True):251        note(f"- {row}")252253    # City name hygiene: how many city strings, top mass share.254    city_dist = df.group_by("city").len().sort("len", descending=True)255    top50_share = city_dist.head(50)["len"].sum() / df.height * 100256    note("")257    note(f"- Top 50 city strings cover {top50_share:.1f}% of transactions")258    save(city_dist.head(200), "audit_city_top200")259260    # ------------------------------------------------------------------ #261    # 7. indéterminé investigation (preliminary)262    # ------------------------------------------------------------------ #263    note("")264    note("## 7. `indéterminé` property type — preliminary look")265    note("")266    ind = df.filter(pl.col("propertyType") == "indéterminé")267    note(f"- Count: {ind.height:,} ({ind.height / df.height * 100:.1f}%)")268    if ind.height:269        note(f"- Median amount: ${ind['amount'].median():,.0f} "270             f"(vs unifamilial ${df.filter(pl.col('propertyType') == 'unifamilial')['amount'].median():,.0f})")271        note(f"- floorArea missing: {ind['floorArea'].is_null().mean() * 100:.1f}% | "272             f"yearBuilt missing: {ind['yearBuilt'].is_null().mean() * 100:.1f}% | "273             f"buildingType missing: {ind['buildingType'].is_null().mean() * 100:.1f}%")274        bt = ind.group_by("buildingType").len().sort("len", descending=True).head(8)275        note("- Top buildingType values within indéterminé: "276             + ", ".join(f"{r['buildingType']}={r['len']:,}" for r in bt.iter_rows(named=True)))277        ot = ind.group_by("ownerType").len().sort("len", descending=True).head(5)278        note("- ownerType within indéterminé: "279             + ", ".join(f"{r['ownerType']}={r['len']:,}" for r in ot.iter_rows(named=True)))280281    # ------------------------------------------------------------------ #282    # Write report283    # ------------------------------------------------------------------ #284    report_path = REPORTS_DIR / "audit_report.md"285    header = (286        "<!--\n"287        "=============================================================================\n"288        "QWHPI — Quebec Weekly Housing Price Index\n"289        "Author  : Simon-Pierre Boucher\n"290        "Contact : contact@spboucher.ai\n"291        "File    : outputs/reports/audit_report.md\n"292        "Purpose : Step 1 data audit report (generated by engine/scripts/01_profile.py)\n"293        "=============================================================================\n"294        "-->\n\n"295    )296    report_path.write_text(header + "\n".join(REPORT_LINES) + "\n", encoding="utf-8")297    print(f"\nReport written to {report_path}")298299300if __name__ == "__main__":301    main()302