spb/wp2_uqo

1# Author: Simon-Pierre Boucher — contact@spboucher.ai2#3"""Helpers for the 2026-10-17 revision extensions (scripts/07_revision_extensions.py).45Language detection, unit-corrected lot size, floor area, forward sortation6areas (FSA), fixed-effects / clustered OLS and out-of-sample metrics.7"""89import json10import re11import sqlite31213import numpy as np14import pandas as pd15import statsmodels.api as sm1617FR_STOPWORDS = set(18    "le la les des une un et est avec dans pour sur au aux du de à cette ce cet "19    "ses sa son vous nous très plus rez chaussée cuisine chambre salle bain terrain "20    "étage sous-sol près".split()21)22EN_STOPWORDS = set(23    "the and with for this that is are has have from your you in on at of to a an "24    "kitchen bedroom bathroom basement floor lot home house living room large new".split()25)2627SQM_PER_UNIT = {"m2": 1.0, "sqft": 0.09290304, "ac": 4046.8564, "hec": 10000.0}282930def detect_language(text):31    """Return 'fr', 'en' or 'mixed' from stop-word counts (ratio rule 1.5)."""32    words = re.findall(r"[a-zàâçéèêëîïôûùüÿœ'-]+", text.lower())33    n_fr = sum(w in FR_STOPWORDS for w in words)34    n_en = sum(w in EN_STOPWORDS for w in words)35    if n_fr > 1.5 * n_en:36        return "fr"37    if n_en > 1.5 * n_fr:38        return "en"39    return "mixed"404142def _area_sqm(size_str):43    """Convert a Realtor.ca size string ('98900 m2', '5000 sqft', '2 ac') to m²."""44    if not size_str or not isinstance(size_str, str):45        return np.nan46    m = re.match(r"^\s*([\d.,]+)\s*([a-zA-Z0-9]*)", size_str)47    if not m:48        return np.nan49    try:50        value = float(m.group(1).replace(",", ""))51    except ValueError:52        return np.nan53    unit = m.group(2).lower()54    if unit not in SQM_PER_UNIT or value <= 0:55        return np.nan56    return value * SQM_PER_UNIT[unit]575859def load_revision_fields(db_path, ids):60    """Postal code / FSA, municipality, lot area (m²), floor area (m²) for the sample."""61    conn = sqlite3.connect(db_path)62    rows = conn.execute(63        "SELECT id, data FROM properties WHERE category = 'house'"64    ).fetchall()65    conn.close()66    wanted = set(ids)67    recs = {}68    for pid, data_json in rows:69        if pid not in wanted:70            continue71        try:72            d = json.loads(data_json)73        except (json.JSONDecodeError, TypeError):74            continue75        postal = d.get("PostalCode") or ""76        addr = d.get("Property", {}).get("Address", {}).get("AddressText", "")77        city = addr.split("|")[-1].split(",")[0].strip() if "|" in addr else ""78        recs[pid] = {79            "postal_code": postal,80            "fsa": postal[:3].upper(),81            "municipality": city,82            "lot_sqm": _area_sqm(d.get("Land", {}).get("SizeTotal", "")),83            "floor_sqm": _area_sqm(d.get("Building", {}).get("SizeInterior", "")),84            "scraped_at": d.get("scrapedAt"),85        }86    out = pd.DataFrame([recs.get(i, {}) for i in ids])87    out.index = pd.Index(ids)88    return out899091def ols(y, X, cov_type="HC3", groups=None):92    """OLS with HC3 or cluster-robust covariance."""93    if cov_type == "cluster":94        return sm.OLS(y, X).fit(cov_type="cluster", cov_kwds={"groups": groups})95    return sm.OLS(y, X).fit(cov_type=cov_type)969798def zscore(df, columns):99    X = (df[columns] - df[columns].mean()) / df[columns].std(ddof=0)100    return X101102103def within(df, columns, group):104    """Demean columns within groups (fixed-effects transformation)."""105    return df[columns] - df.groupby(group)[columns].transform("mean")106107108def block_f_test(model, variables):109    """Wald F-test that all coefficients in `variables` are zero (robust cov)."""110    names = list(model.params.index)111    R = np.zeros((len(variables), len(names)))112    for i, v in enumerate(variables):113        R[i, names.index(v)] = 1.0114    res = model.f_test(R)115    return float(np.squeeze(res.fvalue)), float(res.pvalue), int(res.df_num)116117118def oos_metrics(y_true_log, y_pred_log):119    """Out-of-sample R² (log scale), RMSE (log), MdAPE (level)."""120    resid = y_true_log - y_pred_log121    ss_res = float(np.sum(resid ** 2))122    ss_tot = float(np.sum((y_true_log - y_true_log.mean()) ** 2))123    price = np.exp(y_true_log)124    mdape = float(np.median(np.abs(np.exp(y_pred_log) - price) / price)) * 100125    return {"r2_oos": 1 - ss_res / ss_tot,126            "rmse_log": float(np.sqrt(np.mean(resid ** 2))),127            "mdape_pct": mdape}128