spb/wp10_uqo Public

1# Author: Simon-Pierre Boucher — contact@spboucher.ai2"""Estimation-sample construction for WP10.34Reads the raw matched transaction–roll snapshot, restricts to residential5arm's-length sales with a high-confidence roll match, builds the assessment6ratio and every derived regressor, applies the trims, and writes7``data/processed/analysis.parquet``.89Key variable definitions10------------------------11ratio        AV / SP where AV = ``role_valeur_immeuble`` (total assessed value12             on the roll in force at the sale date) and SP = ``amount``.13ln_ratio     log(ratio).14cell         municipality × roll vintage × sale year — the market-timing cell15             inside which assessed values share a common reference date, so16             the roll lag is constant and ratio comparisons are clean.17lag_months   months elapsed between the roll's market-condition reference18             date (July 1, by statute) and the sale date.19land_share   assessed land value / total assessed value.20age          sale year minus year built (from the roll where available).21"""22import numpy as np23import pandas as pd2425from . import config262728def _property_class(df: pd.DataFrame) -> pd.Series:29    """Harmonised dwelling class from the listing type, CUBF code and unit count."""30    cls = pd.Series("other", index=df.index, dtype="object")31    cls[df["role_cubf"] == "1100"] = "cottage"32    cls[df["role_cubf"] == "1211"] = "mobile"33    is_dwelling = df["role_cubf"] == "1000"34    cls[is_dwelling & (df["propertyType"] == "condo")] = "condo"35    cls[is_dwelling & (df["propertyType"] == "plex")] = "plex"36    cls[is_dwelling & (df["propertyType"] == "unifamilial")] = "single_family"37    # dwellings the feed left untyped: use the roll's unit count38    untyped = is_dwelling & (cls == "other")39    cls[untyped & (df["role_nb_logements"] == 1)] = "single_family"40    cls[untyped & (df["role_nb_logements"].between(2, 5))] = "plex"41    return cls424344def build(raw: pd.DataFrame | None = None) -> pd.DataFrame:45    """Apply every sample restriction and return the analysis DataFrame."""46    df = raw if raw is not None else pd.read_parquet(config.RAW_PARQUET)47    n0 = len(df)48    log = [("raw snapshot", n0)]4950    # -------------------------------------------------- residential use codes51    df = df[df["role_cubf"].isin(config.RESIDENTIAL_CUBF)]52    log.append(("residential CUBF (1000/1100/1211/1990)", len(df)))5354    # -------------------------------------------------- match confidence55    df = df[(df["match_dist_m"] <= config.MATCH_MAX_DIST_M)56            & (df["match_score"] >= config.MATCH_MIN_SCORE)]57    log.append((f"match dist ≤ {config.MATCH_MAX_DIST_M:.0f} m & score ≥ "58                f"{config.MATCH_MIN_SCORE:.0f}", len(df)))5960    # -------------------------------------------------- valid AV and SP61    df = df[(df["amount"] >= config.PRICE_MIN)62            & (df["role_valeur_immeuble"] > 5_000)63            & df["role_date_cond_marche"].notna()]64    log.append(("positive AV, SP ≥ 50k, dated roll", len(df)))6566    # -------------------------------------------------- derived variables67    df = df.copy()68    df["sale_date"] = pd.to_datetime(df["date"])69    df["sale_year"] = df["tx_year"].astype(int)70    df["ratio"] = df["role_valeur_immeuble"] / df["amount"]71    df["ln_ratio"] = np.log(df["ratio"])72    df["ln_price"] = np.log(df["amount"].astype(float))73    df["ln_av"] = np.log(df["role_valeur_immeuble"])74    df["lag_months"] = ((df["sale_date"] - df["role_date_cond_marche"]).dt.days75                        / 30.44)76    df["land_share"] = (df["role_valeur_terrain"]77                        / df["role_valeur_immeuble"]).clip(0, 1)78    df["age"] = (df["sale_year"]79                 - pd.to_numeric(df["role_annee_construction"], errors="coerce"))80    df.loc[(df["age"] < 0) | (df["age"] > 300), "age"] = np.nan81    df["prop_class"] = _property_class(df)82    df["muni"] = df["role_code_mun"]83    df["roll"] = df["role_anrole"]84    df["cell"] = (df["muni"] + "_" + df["roll"] + "_"85                  + df["sale_year"].astype(str))8687    # -------------------------------------------------- ratio trim (by roll vintage,88    # so the mechanical drift of ratios across reference dates is not trimmed away)89    lo, hi = config.RATIO_TRIM90    q = df.groupby("roll")["ratio"].quantile([lo, hi]).unstack()91    df = df.join(q.rename(columns={lo: "_qlo", hi: "_qhi"}), on="roll")92    df = df[(df["ratio"] >= df["_qlo"]) & (df["ratio"] <= df["_qhi"])]93    df = df.drop(columns=["_qlo", "_qhi"])94    log.append((f"ratio inside [{lo:.0%}, {hi:.0%}] of its roll vintage", len(df)))9596    # -------------------------------------------------- market-timing cells97    counts = df.groupby("cell")["cell"].transform("size")98    df = df[counts >= config.CELL_MIN_OBS]99    log.append((f"cells (muni × roll × year) with ≥ {config.CELL_MIN_OBS} sales",100                len(df)))101102    df.attrs["selection_log"] = log103    keep = ["id", "sale_date", "sale_year", "amount", "ln_price",104            "role_valeur_immeuble", "role_valeur_terrain", "role_valeur_batiment",105            "totalArValue", "previousValue", "ln_av", "ratio", "ln_ratio",106            "lag_months", "land_share", "age", "prop_class",107            "muni", "roll", "cell", "city", "role_municipalite",108            "lat", "lng", "role_superficie_terrain_m2", "role_aire_etages_m2",109            "role_nb_logements", "match_dist_m", "match_score",110            "match_valeur_exacte", "ownerType", "role_cubf"]111    return df[keep].reset_index(drop=True)112113114def build_and_save() -> pd.DataFrame:115    """Build the sample, print the selection log, persist to parquet."""116    config.ensure_dirs()117    s = build()118    for step, n in s.attrs["selection_log"]:119        print(f"  {n:>9,}  after: {step}")120    s.to_parquet(config.ANALYSIS_PARQUET, index=False)121    return s122123124def load() -> pd.DataFrame:125    """Load the processed analysis sample (build it first if missing)."""126    if not config.ANALYSIS_PARQUET.exists():127        return build_and_save()128    return pd.read_parquet(config.ANALYSIS_PARQUET)129