TeX 59%
Python 40.8%
1#!/usr/bin/env python32"""Step 01 — Build the sampling frame: house listings (Immo-Ka copy) joined to the3assessment-roll unit (Vrai Prix copy) and the Vrai Prix production evaluation.45Inputs (read-only, never copied into the repository):6 IMMOKA_DB = immoka.db (listings, vp_eval)7 VRAIPRIX_DB = vraiprix.db (units, transactions, market_index)8Outputs:9 data/processed/frame.parquet one row per eligible listing (all exclusion flags kept)10 results/tables/frame_exclusions.csv logged exclusion cascade11 results/raw/frame_summary.json counts by source/region/strata1213Eligibility rules (R0–R8) are logged one by one; nothing is silently dropped.14"""15from __future__ import annotations1617import json18import os19import re20import sqlite321import sys22from pathlib import Path2324import numpy as np25import pandas as pd26import yaml2728ROOT = Path(__file__).resolve().parents[2]29CFG = yaml.safe_load(open(ROOT / "configs" / "data.yaml"))3031IMMOKA_DB = os.environ.get("IMMOKA_DB", CFG["sources"]["immoka_db"])32VRAIPRIX_DB = os.environ.get("VRAIPRIX_DB", CFG["sources"]["vraiprix_db"])3334HOUSE_TYPES = tuple(CFG["frame"]["house_property_types"])35MATCH_M = CFG["frame"]["max_match_m"]3637# Québec administrative regions by municipality code prefix are not in the data; we use the38# listing's `region` when present and otherwise the roll municipality name through a coarse39# lookup of the largest CMAs (good enough for stratification; reported as such).40CMA_HINTS = {41 "montreal": ["Montréal", "Laval", "Longueuil", "Brossard", "Terrebonne", "Repentigny", "Blainville", "Mirabel",42 "Saint-Jérôme", "Boucherville", "Saint-Eustache", "Mascouche", "Châteauguay", "Vaudreuil-Dorion",43 "Sainte-Julie", "Saint-Constant", "Candiac", "La Prairie", "Chambly", "Saint-Bruno-de-Montarville",44 "Varennes", "Sainte-Thérèse", "Boisbriand", "Rosemère", "Lorraine", "Bois-des-Filion", "Deux-Montagnes",45 "Pointe-Claire", "Dollard-Des Ormeaux", "Kirkland", "Beaconsfield", "Dorval", "Mont-Royal", "Westmount",46 "Côte-Saint-Luc", "Hampstead", "Montréal-Ouest", "Sainte-Anne-de-Bellevue", "Baie-D'Urfé", "Senneville",47 "L'Île-Perrot", "Pincourt", "Saint-Lazare", "Hudson", "Les Cèdres", "Mercier", "Beauharnois",48 "Saint-Amable", "Saint-Basile-le-Grand", "McMasterville", "Beloeil", "Mont-Saint-Hilaire", "Otterburn Park",49 "Carignan", "Richelieu", "Saint-Mathias-sur-Richelieu", "Marieville", "L'Assomption", "Saint-Sulpice",50 "Charlemagne", "Lavaltrie", "Saint-Lin--Laurentides", "Sainte-Anne-des-Plaines", "Saint-Colomban",51 "Gore", "Oka", "Pointe-Calumet", "Saint-Joseph-du-Lac", "Sainte-Marthe-sur-le-Lac", "Saint-Placide",52 "Delson", "Saint-Philippe", "Saint-Isidore", "Saint-Mathieu", "Léry", "Kahnawake", "Montréal-Est"],53 "quebec": ["Québec", "Lévis", "L'Ancienne-Lorette", "Saint-Augustin-de-Desmaures", "Boischatel", "Shannon",54 "Sainte-Brigitte-de-Laval", "Stoneham-et-Tewkesbury", "Lac-Beauport", "Fossambault-sur-le-Lac",55 "Sainte-Catherine-de-la-Jacques-Cartier", "Lac-Saint-Joseph", "Château-Richer", "L'Ange-Gardien",56 "Saint-Henri", "Saint-Lambert-de-Lauzon", "Beaumont", "Lac-Delage", "Saint-Gabriel-de-Valcartier",57 "Wendake", "Notre-Dame-des-Anges"],58 "gatineau": ["Gatineau", "Chelsea", "Cantley", "Val-des-Monts", "L'Ange-Gardien", "La Pêche", "Pontiac"],59 "sherbrooke": ["Sherbrooke", "Magog", "Ascot Corner", "Saint-Denis-de-Brompton", "Stoke", "Compton",60 "Waterville", "North Hatley", "Hatley"],61 "trois-rivieres": ["Trois-Rivières", "Bécancour", "Saint-Maurice", "Champlain", "Notre-Dame-du-Mont-Carmel",62 "Yamachiche", "Saint-Étienne-des-Grès"],63 "saguenay": ["Saguenay", "Saint-Honoré", "Saint-Fulgence", "Saint-David-de-Falardeau", "Larouche",64 "Saint-Charles-de-Bourget"],65}666768def cma_of(muni: str) -> str:69 if not isinstance(muni, str):70 return "other"71 m = muni.strip()72 for cma, names in CMA_HINTS.items():73 if m in names:74 return cma75 return "other"767778def parse_details(s: str | None) -> dict:79 if not s:80 return {}81 try:82 d = json.loads(s)83 return d if isinstance(d, dict) else {}84 except Exception:85 return {}868788def parse_images(s: str | None) -> list[str]:89 if not s:90 return []91 try:92 v = json.loads(s)93 except Exception:94 return []95 if isinstance(v, list):96 out = []97 for it in v:98 if isinstance(it, str):99 out.append(it)100 elif isinstance(it, dict) and it.get("url"):101 out.append(it["url"])102 return [u for u in out if u.startswith("http")]103 return []104105106def money(v) -> float | None:107 if v is None:108 return None109 s = re.sub(r"[^\d]", "", str(v))110 return float(s) if s else None111112113# Broker-entered fields we treat as *text-claimed* (not verified) attributes.114DETAIL_FIELDS = {115 "claim_siding": ["Revêtement", "Revêtements", "Revêtement extérieur", "External facing"],116 "claim_roof": ["Revêtement de la toiture", "Toiture", "Roofing"],117 "claim_foundation": ["Fondation"],118 "claim_windows": ["Fenestration", "Fenêtres", "Windows"],119 "claim_basement": ["Sous-sol", "Basement"],120 "claim_garage": ["Garage"],121 "claim_parking": ["Stationnement (total)", "Stationnement", "Nombre de stationnement intérieur"],122 "claim_kitchen": ["Cuisine", "Kitchen"],123 "claim_year_built": ["Année de construction", "Year of construction"],124 "claim_assess_land": ["Évaluation municipale (terrain)", "Évaluation municipale du terrain"],125 "claim_assess_building": ["Évaluation municipale (bâtiment)", "Évaluation municipale du bâtiment"],126 "claim_assess_total": ["Évaluation municipale"],127 "claim_assess_year": ["Évaluation municipale (année)", "Année de l'évaluation municipale"],128 "claim_building_dim": ["Dimensions du bâtiment", "Batisse dim.", "Building dimensions"],129 "claim_style": ["Style"],130 "claim_heating_energy": ["Énergie pour le chauffage"],131 "claim_heating_mode": ["Mode de chauffage", "Système de chauffage"],132 "claim_pool": ["Piscine", "Pool"],133}134135136def main() -> None:137 con = sqlite3.connect(IMMOKA_DB)138 con.execute(f"ATTACH DATABASE '{VRAIPRIX_DB}' AS vp")139 q = f"""140 SELECT l.uid, l.source, l.url, l.address, l.city, l.region, l.property_type, l.price, l.bedrooms, l.bathrooms,141 l.area_sqft, l.lot_sqft, l.year_built AS listing_year_built, l.description, l.features, l.details, l.images,142 l.lat AS listing_lat, l.lng AS listing_lng, l.first_seen, l.last_seen, l.quality_score,143 e.unit_id, e.match_m, e.est AS vp_est, e.low AS vp_low, e.high AS vp_high, e.confidence_pct AS vp_conf,144 e.model_est AS vp_model_est, e.comps_est AS vp_comps_est, e.cost_est AS vp_cost_est, e.role_est AS vp_role_est,145 e.ens_est AS vp_ens_est, e.type_prop AS vp_type_prop, e.municipalite AS vp_municipalite,146 u.id_provinc, u.municipalite AS roll_municipalite, u.code_mun, u.lat AS roll_lat, u.lng AS roll_lng, u.cubf,147 u.type_prop AS roll_type_prop, u.annee_construction AS roll_year_built, u.aire_etages_m2 AS roll_floor_area_m2,148 u.superficie_terrain_m2 AS roll_lot_m2, u.nb_etages AS roll_storeys, u.nb_logements AS roll_dwellings,149 u.valeur_terrain AS roll_land_value, u.valeur_batiment AS roll_building_value, u.valeur_role AS roll_total_value,150 u.dat_cond_marche AS roll_market_date, u.est_2026 AS roll_model_est, u.p10 AS roll_model_p10, u.p90 AS roll_model_p90151 FROM listings l152 JOIN vp_eval e ON e.uid = l.uid153 LEFT JOIN vp.units u ON u.id_provinc = e.unit_id154 WHERE l.property_type IN ({",".join("?" * len(HOUSE_TYPES))})155 """156 df = pd.read_sql_query(q, con, params=HOUSE_TYPES)157 con.close()158 n0 = len(df)159160 casc = []161162 def rule(name: str, keep: pd.Series, desc: str):163 nonlocal df164 removed = int((~keep).sum())165 casc.append({"rule": name, "description": desc, "removed": removed, "remaining": int(keep.sum())})166 df = df[keep].copy()167168 rule("R0", pd.Series(True, index=df.index), f"House listings (property_type in {HOUSE_TYPES}) evaluated by Vrai Prix: {n0}")169 rule("R1", df["vp_type_prop"].eq("unifamilial"), "Vrai Prix classifies the matched roll unit as single-family (unifamilial)")170 rule("R2", df["match_m"].le(MATCH_M), f"Listing geocode within {MATCH_M} m of the roll unit")171 rule("R3", df["roll_type_prop"].eq("unifamilial") & df["roll_dwellings"].fillna(1).le(1), "Roll unit is a single dwelling")172 rule("R4", df["roll_year_built"].gt(1800) & df["roll_floor_area_m2"].gt(20) & df["roll_land_value"].gt(0) & df["roll_building_value"].gt(0),173 "Complete roll fields: year built, floor area, land and building values")174 yb_ok = df["listing_year_built"].isna() | (df["listing_year_built"] - df["roll_year_built"]).abs().le(5)175 rule("R5", yb_ok, "Listing year built (if any) within ±5 years of the roll year built (match sanity)")176 df["n_images"] = df["images"].map(lambda s: len(parse_images(s)))177 rule("R6", df["n_images"].ge(CFG["frame"]["min_images"]), f"At least {CFG['frame']['min_images']} image URLs")178 rule("R7", df["price"].between(CFG["frame"]["price_min"], CFG["frame"]["price_max"]),179 f"Asking price within [{CFG['frame']['price_min']:,}, {CFG['frame']['price_max']:,}]")180 rule("R8", ~df["source"].isin(CFG["frame"]["excluded_sources"]),181 f"Sources with no structured building fields and few photos excluded: {CFG['frame']['excluded_sources']}")182 import urllib.parse183 bad_hosts = set(CFG["frame"]["robots_disallowed_hosts"])184 df["image_urls_tmp"] = df["images"].map(parse_images)185 df["share_disallowed"] = df["image_urls_tmp"].map(lambda l: np.mean([urllib.parse.urlparse(u).netloc in bad_hosts for u in l]) if l else 1.0)186 excluded_robots = df[df["share_disallowed"] > 0.5].copy()187 rule("R10", df["share_disallowed"].le(0.5), "Image host permits automated retrieval (robots.txt): hosts with 'Disallow: /' excluded")188 df = df.drop(columns=["image_urls_tmp", "share_disallowed"])189 # one listing per roll unit (duplicate listings of the same house across brokers): keep the one with most images190 df = df.sort_values(["id_provinc", "n_images"], ascending=[True, False])191 dup = df.duplicated("id_provinc", keep="first")192 rule("R9", ~dup, "One listing per roll unit (duplicates across sources: keep the listing with most images)")193194 # derived fields195 det = df["details"].map(parse_details)196 for col, keys in DETAIL_FIELDS.items():197 df[col] = det.map(lambda d, ks=keys: next((d[k] for k in ks if k in d and d[k] not in (None, "", "Non spécifié")), None)).astype(object)198 for c in ["claim_assess_land", "claim_assess_building", "claim_assess_total"]:199 df[c] = df[c].map(money)200 for col in DETAIL_FIELDS:201 if col not in ("claim_assess_land", "claim_assess_building", "claim_assess_total", "claim_year_built"):202 df[col] = df[col].map(lambda v: None if (v is None or (not isinstance(v, (list, dict)) and pd.isna(v))) else str(v))203 df["claim_year_built"] = pd.to_numeric(df["claim_year_built"].map(lambda v: re.sub(r"[^\d]", "", str(v))[:4] if v else None), errors="coerce")204 df["image_urls"] = df["images"].map(parse_images)205 df["cma"] = df["roll_municipalite"].map(cma_of)206 df["age_2026"] = 2026 - df["roll_year_built"]207 df["age_band"] = pd.cut(df["age_2026"], [-1, 10, 25, 50, 75, 300], labels=["0-10", "11-25", "26-50", "51-75", "76+"])208 df["price_tier"] = pd.qcut(df["price"], 4, labels=["Q1", "Q2", "Q3", "Q4"])209 df["roll_building_share"] = df["roll_building_value"] / df["roll_total_value"]210 df["desc_len"] = df["description"].fillna("").str.len()211212 out = ROOT / "data" / "processed" / "frame.parquet"213 df.drop(columns=["images"]).to_parquet(out, index=False)214 pd.DataFrame(casc).to_csv(ROOT / "results" / "tables" / "frame_exclusions.csv", index=False)215 # balance check: listings excluded for robots reasons vs retained, on roll characteristics216 cols = ["price", "roll_year_built", "roll_floor_area_m2", "roll_land_value", "roll_building_value", "n_images"]217 bal = pd.DataFrame({"excluded_robots_median": excluded_robots[cols].median(), "retained_median": df[cols].median(),218 "excluded_n": len(excluded_robots), "retained_n": len(df)})219 bal.to_csv(ROOT / "results" / "tables" / "robots_exclusion_balance.csv")220 summary = {221 "n_frame": int(len(df)),222 "by_source": df["source"].value_counts().head(25).to_dict(),223 "by_cma": df["cma"].value_counts().to_dict(),224 "by_age_band": df["age_band"].value_counts().sort_index().to_dict(),225 "by_price_tier": df["price_tier"].value_counts().sort_index().to_dict(),226 "price_quartiles": [float(x) for x in df["price"].quantile([0, .25, .5, .75, 1]).values],227 "images_median": float(df["n_images"].median()),228 "claims_coverage": {c: float(df[c].notna().mean()) for c in DETAIL_FIELDS},229 "roll_market_date": df["roll_market_date"].value_counts().to_dict(),230 "vp_cost_est_coverage": float(df["vp_cost_est"].notna().mean()),231 }232 json.dump(summary, open(ROOT / "results" / "raw" / "frame_summary.json", "w"), indent=2, ensure_ascii=False, default=str)233 print(pd.DataFrame(casc).to_string(index=False))234 print(json.dumps({k: v for k, v in summary.items() if k != "by_source"}, indent=1, ensure_ascii=False, default=str))235236237if __name__ == "__main__":238 sys.exit(main())239