spb/wp11_uqo Public

1# Author: Simon-Pierre Boucher — contact@spboucher.ai2"""Estimation-sample construction for WP11.34Residential arm's-length sales (2021–2026) with a high-confidence roll5match and complete structural descriptors from the assessment roll:6floor area, lot area, building age, dwelling class, physical configuration7and coordinates. Writes ``data/processed/analysis.parquet``.8"""9import numpy as np10import pandas as pd1112from . import config1314LINK_MAP = {"1": "detached", "2": "semi_detached", "3": "row_end",15            "4": "row", "5": "apartment_link"}161718def _property_class(df: pd.DataFrame) -> pd.Series:19    cls = pd.Series("other", index=df.index, dtype="object")20    cls[df["role_cubf"] == "1100"] = "cottage"21    cls[df["role_cubf"] == "1211"] = "mobile"22    is_dwelling = df["role_cubf"] == "1000"23    cls[is_dwelling & (df["propertyType"] == "condo")] = "condo"24    cls[is_dwelling & (df["propertyType"] == "plex")] = "plex"25    cls[is_dwelling & (df["propertyType"] == "unifamilial")] = "single_family"26    untyped = is_dwelling & (cls == "other")27    cls[untyped & (df["role_nb_logements"] == 1)] = "single_family"28    cls[untyped & (df["role_nb_logements"].between(2, 5))] = "plex"29    return cls303132def build(raw: pd.DataFrame | None = None) -> pd.DataFrame:33    df = raw if raw is not None else pd.read_parquet(config.RAW_PARQUET)34    log = [("raw snapshot", len(df))]3536    df = df[df["role_cubf"].isin(config.RESIDENTIAL_CUBF)]37    log.append(("residential CUBF", len(df)))3839    df = df[(df["match_dist_m"] <= config.MATCH_MAX_DIST_M)40            & (df["match_score"] >= config.MATCH_MIN_SCORE)]41    log.append(("high-confidence roll match", len(df)))4243    df = df[(df["amount"] >= config.PRICE_MIN)44            & (df["role_aire_etages_m2"] > 20)45            & (df["role_aire_etages_m2"] < 2_000)]46    log.append(("valid price and floor area", len(df)))4748    df = df.copy()49    df["sale_date"] = pd.to_datetime(df["date"])50    df["sale_year"] = df["tx_year"].astype(int)51    df["month"] = df["sale_date"].dt.to_period("M").astype(str)52    df["quarter"] = df["sale_date"].dt.to_period("Q").astype(str)53    df["t"] = ((df["sale_date"] - pd.Timestamp("2021-01-01")).dt.days54               / 30.44)  # months since Jan 2021, continuous5556    df["price"] = df["amount"].astype(float)57    df["ln_price"] = np.log(df["price"])58    df["area"] = df["role_aire_etages_m2"].astype(float)59    df["ln_area"] = np.log(df["area"])60    df["lot"] = df["role_superficie_terrain_m2"].fillna(0).clip(lower=0)61    df.loc[df["lot"] > df["lot"].quantile(0.995), "lot"] = np.nan62    df["lot"] = df["lot"].fillna(0)63    df["has_lot"] = (df["lot"] > 0).astype(float)64    df["ln_lot"] = np.log(df["lot"].where(df["lot"] > 0, 1.0))6566    year_built = pd.to_numeric(df["role_annee_construction"], errors="coerce")67    df["age"] = (df["sale_year"] - year_built).clip(0, config.AGE_MAX)68    df = df[df["age"].notna()]69    log.append(("known building age", len(df)))7071    df["floors"] = df["role_nb_etages"].clip(1, 6).fillna(1.0)72    df["units"] = df["role_nb_logements"].clip(1, 12).fillna(1.0)73    df["prop_class"] = _property_class(df)74    df["link"] = df["role_lien_physique"].map(LINK_MAP).fillna("unknown")75    df["muni"] = df["role_code_mun"]76    df["grid"] = ((df["lat"] / config.GRID_DEG).round().astype(int).astype(str)77                  + "_" + (df["lng"] / config.GRID_DEG).round().astype(int).astype(str))78    df["grid5"] = ((df["lat"] / config.GRID5_DEG).round().astype(int).astype(str)79                   + "_" + (df["lng"] / config.GRID5_DEG).round().astype(int).astype(str))8081    lo, hi = config.TRIM82    for col in ("price", "area"):83        q = df.groupby("sale_year")[col].quantile([lo, hi]).unstack()84        df = df.join(q.rename(columns={lo: "_qlo", hi: "_qhi"}), on="sale_year")85        df = df[(df[col] >= df["_qlo"]) & (df[col] <= df["_qhi"])]86        df = df.drop(columns=["_qlo", "_qhi"])87    log.append((f"price & area inside [{lo:.0%}, {hi:.0%}] of sale year",88                len(df)))8990    df.attrs["selection_log"] = log91    keep = ["id", "sale_date", "sale_year", "month", "quarter", "t",92            "price", "ln_price", "area", "ln_area", "lot", "ln_lot", "has_lot",93            "age", "floors", "units", "prop_class", "link",94            "muni", "grid", "grid5", "lat", "lng", "city",95            "role_municipalite"]96    return df[keep].reset_index(drop=True)979899def build_and_save() -> pd.DataFrame:100    config.ensure_dirs()101    s = build()102    for step, n in s.attrs["selection_log"]:103        print(f"  {n:>9,}  after: {step}")104    s.to_parquet(config.ANALYSIS_PARQUET, index=False)105    return s106107108def load() -> pd.DataFrame:109    if not config.ANALYSIS_PARQUET.exists():110        return build_and_save()111    return pd.read_parquet(config.ANALYSIS_PARQUET)112