spb/valoplex Public

1#!/usr/bin/env python32# Auteur : Simon-Pierre Boucher — contact@spboucher.ai3"""Construit la base SQLite de ValoPlex (plex seulement) + stats provinciales plex.45units        : 393 867 plex (CUBF 1000, 2+ logements, rôle 2026) + estimations6               2021-2026, P10/P90 conformes.7transactions : 91 060+ ventes du domaine plex (avec nombre de portes).8market_index : indice mensuel $/m² des ventes de plex.9Sortie       : app/data/valoplex.db + app/src/data/stats.json10"""11import json12import os13import sqlite31415import numpy as np16import pandas as pd1718QC = "/Users/simon-pierreboucher/Desktop/qc_house_eval"19BASE = "/Users/simon-pierreboucher/Desktop/valoplex"20DB = f"{BASE}/app/data/valoplex.db"21os.makedirs(os.path.dirname(DB), exist_ok=True)22if os.path.exists(DB):23    os.remove(DB)2425con = sqlite3.connect(DB)26con.executescript("""27PRAGMA journal_mode=OFF; PRAGMA synchronous=OFF; PRAGMA temp_store=MEMORY;28CREATE TABLE units (29  id_provinc TEXT PRIMARY KEY, adresse TEXT, apt TEXT, municipalite TEXT,30  arrond TEXT, code_mun TEXT, lat REAL, lng REAL, cubf INTEGER,31  cubf_libelle TEXT, type_prop TEXT, annee_construction INTEGER,32  annee_estimee TEXT, aire_etages_m2 REAL, superficie_terrain_m2 REAL,33  front_terrain_m REAL, nb_etages REAL, nb_logements INTEGER,34  nb_locaux_non_resid INTEGER, nb_chambres_locatives INTEGER,35  lien_physique TEXT, genre_construction TEXT,36  matricule TEXT, unite_voisinage TEXT, n_adresses INTEGER,37  dat_cond_marche TEXT,38  valeur_terrain REAL, valeur_batiment REAL, valeur_role REAL,39  valeur_anterieure REAL, adresses_json TEXT,40  est_2021 REAL, est_2022 REAL, est_2023 REAL, est_2024 REAL,41  est_2025 REAL, est_2026 REAL, p10 REAL, p90 REAL, est_hedo REAL42);43CREATE TABLE transactions (44  id TEXT PRIMARY KEY, date TEXT, amount REAL, street TEXT, city TEXT,45  lat REAL, lng REAL, property_type TEXT, year_built INTEGER,46  floor_area REAL, building_type TEXT, id_provinc TEXT, valeur_role REAL,47  land_area REAL, portes INTEGER48);49CREATE TABLE market_index (50  month TEXT, type_prop TEXT, ppm2 REAL, idx REAL, n INTEGER,51  PRIMARY KEY (month, type_prop)52);53CREATE TABLE leads (54  id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT, unit_id TEXT,55  estimate REAL, created_at TEXT DEFAULT (datetime('now'))56);57""")5859# ---- units : plex du rôle 2026 + estimations ----------------------------------60print("units plex...", flush=True)61role = pd.read_parquet(f"{QC}/data/parquet/role_2026.parquet")62cu = pd.to_numeric(role["rl0105a"], errors="coerce")63lg = pd.to_numeric(role["rl0311a"], errors="coerce")64role = role[(cu == 1000) & (lg >= 2)].reset_index(drop=True)6566est = pd.read_parquet(f"{BASE}/data/estimations/plex_estimes_2026.parquet",67                      columns=["id_provinc", "municipalite", "valeur_role",68                               "valeur_estimee", "valeur_estimee_p10",69                               "valeur_estimee_p90"])70df = role.merge(est, on="id_provinc", how="inner")71for y in range(2021, 2026):72    e = pd.read_parquet(f"{BASE}/data/estimations/plex_estimes_{y}.parquet",73                        columns=["id_provinc", "valeur_estimee"]) \74        .rename(columns={"valeur_estimee": f"est_{y}"})75    df = df.merge(e, on="id_provinc", how="left")7677# ---- adresses de chaque porte (table des adresses du rôle) --------------------78print("adresses par porte...", flush=True)79import pyogrio80gen_dom = pyogrio.read_dataframe(81    f"{QC}/data/Role2026_geopackage/Domaines de valeurs/CODE_GENERIQUE_ADRESSE.dbf",82    read_geometry=False)83gen_map = dict(zip(gen_dom.iloc[:, 0].astype(str).str.strip(),84                   gen_dom.iloc[:, 1].astype(str).str.strip().str.title()))85adr = pyogrio.read_dataframe(86    f"{QC}/data/Role2026_geopackage/Role_2026_2.gpkg",87    layer="b05v_adr_unite_evaln_2026",88    columns=["id_provinc", "no_seq_adr", "rl0101a", "rl0101c", "rl0101e",89             "rl0101g", "rl0101h", "rl0101i"],90    read_geometry=False, use_arrow=True)91plex_ids = set(df["id_provinc"])92adr = adr[adr["id_provinc"].isin(plex_ids)].copy()9394def s_(c):95    return adr[c].fillna("").astype(str).str.strip()9697civ = s_("rl0101a").str.lstrip("0")98civ_sup = s_("rl0101c").str.lstrip("0")99num = np.where((civ_sup != "") & (civ_sup != civ), civ + "-" + civ_sup, civ)100generique = s_("rl0101e").map(lambda c: gen_map.get(c, c))101voie = (generique + " " + s_("rl0101g").str.title() + " " + s_("rl0101h")).str.strip()102apt = s_("rl0101i")103full = pd.Series(num, index=adr.index).str.cat(voie, sep=" ").str.strip()104full = np.where(apt != "", full + " app. " + apt, full)105adr["ligne"] = full106adr = adr.sort_values(["id_provinc", "no_seq_adr"])107adr_lists = adr.groupby("id_provinc")["ligne"].apply(108    lambda x: json.dumps(list(dict.fromkeys(x))[:24], ensure_ascii=False))109print(f"  {len(adr_lists)} plex avec adresses détaillées", flush=True)110111lien_map = {"1": "Détaché", "2": "Jumelé", "3": "En rangée 1 côté",112            "4": "En rangée plus de 1 côté", "5": "Intégré"}113genre_map = {"1": "De plain-pied", "2": "À étage mansardé", "3": "À étages entiers",114             "4": "À niveaux décalés", "5": "Unimodulaire"}115116out = pd.DataFrame({117    "id_provinc": df["id_provinc"], "adresse": df["adresse"], "apt": df["apt"],118    "municipalite": df["municipalite"], "arrond": df["arrond"],119    "code_mun": df["code_mun"], "lat": df["lat"], "lng": df["lng"],120    "cubf": pd.to_numeric(df["rl0105a"], errors="coerce"),121    "cubf_libelle": "Logement",122    "type_prop": "plex",123    "annee_construction": pd.to_numeric(df["rl0307a"], errors="coerce"),124    "annee_estimee": df["rl0307b"],125    "aire_etages_m2": pd.to_numeric(df["rl0308a"], errors="coerce"),126    "superficie_terrain_m2": pd.to_numeric(df["rl0302a"], errors="coerce"),127    "front_terrain_m": pd.to_numeric(df["rl0301a"], errors="coerce"),128    "nb_etages": pd.to_numeric(df["rl0306a"], errors="coerce"),129    "nb_logements": pd.to_numeric(df["rl0311a"], errors="coerce"),130    "nb_locaux_non_resid": pd.to_numeric(df["rl0312a"], errors="coerce"),131    "nb_chambres_locatives": pd.to_numeric(df["rl0313a"], errors="coerce"),132    "lien_physique": df["rl0309a"].astype(str).map(lien_map),133    "genre_construction": df["rl0310a"].astype(str).map(genre_map),134    "matricule": df["mat18"],135    "unite_voisinage": df["rl0106a"],136    "n_adresses": pd.to_numeric(df["n_adresses"], errors="coerce"),137    "dat_cond_marche": df["dat_cond_mrche"].astype(str).str[:10],138    "valeur_terrain": pd.to_numeric(df["rl0402a"], errors="coerce"),139    "valeur_batiment": pd.to_numeric(df["rl0403a"], errors="coerce"),140    "valeur_role": df["valeur_role"],141    "valeur_anterieure": pd.to_numeric(df["rl0405a"], errors="coerce"),142    "adresses_json": df["id_provinc"].map(adr_lists),143    "est_2021": df["est_2021"], "est_2022": df["est_2022"],144    "est_2023": df["est_2023"], "est_2024": df["est_2024"],145    "est_2025": df["est_2025"], "est_2026": df["valeur_estimee"],146    "p10": df["valeur_estimee_p10"], "p90": df["valeur_estimee_p90"],147    "est_hedo": None,148})149print(f"écriture de {len(out)} plex...", flush=True)150out.to_sql("units", con, if_exists="append", index=False, chunksize=100_000)151152# ---- transactions du domaine plex ---------------------------------------------153print("transactions plex...", flush=True)154tx = pd.read_parquet(f"{QC}/province_transactions_enrichi.parquet")155lgx = pd.to_numeric(tx["role_nb_logements"], errors="coerce")156cux = pd.to_numeric(tx["role_cubf"], errors="coerce")157good = (cux == 1000) & (lgx >= 2) & tx["role_id_provinc"].notna() & \158    (tx["match_valeur_exacte"] | (tx["match_dist_m"] <= 50))159p = tx[good]160txo = pd.DataFrame({161    "id": p["id"], "date": p["date"], "amount": p["amount"],162    "street": p["street"], "city": p["city"], "lat": p["lat"], "lng": p["lng"],163    "property_type": "plex",164    "year_built": pd.to_numeric(p["yearBuilt"], errors="coerce"),165    "floor_area": pd.to_numeric(p["floorArea"], errors="coerce"),166    "building_type": p["buildingType"],167    "id_provinc": p["role_id_provinc"],168    "valeur_role": pd.to_numeric(p["role_valeur_immeuble"], errors="coerce"),169    "land_area": pd.to_numeric(p["role_superficie_terrain_m2"], errors="coerce"),170    "portes": lgx[good].astype(int),171})172txo.to_sql("transactions", con, if_exists="append", index=False, chunksize=100_000)173174# ---- indice de marché plex -----------------------------------------------------175print("market_index...", flush=True)176t = txo.dropna(subset=["floor_area"])177t = t[(t["floor_area"] > 40) & (t["amount"] > 60_000)]178t = t.assign(month=t["date"].str[:7], ppm2=t["amount"] / t["floor_area"])179gi = t.groupby("month").agg(ppm2=("ppm2", "median"), n=("ppm2", "size")).reset_index()180gi = gi[gi["n"] >= 5].sort_values("month")181gi["ppm2"] = gi["ppm2"].rolling(3, min_periods=1, center=True).median()182gi["idx"] = gi["ppm2"] / gi["ppm2"].iloc[-1]183gi["type_prop"] = "plex"184gi.to_sql("market_index", con, if_exists="append", index=False)185186con.executescript("""187CREATE INDEX idx_units_geo ON units(lat, lng);188CREATE INDEX idx_units_mun ON units(municipalite);189CREATE INDEX idx_tx_geo ON transactions(lat, lng);190CREATE VIRTUAL TABLE units_fts USING fts5(191  adresse, municipalite, content='units', content_rowid='rowid',192  tokenize='unicode61 remove_diacritics 2');193INSERT INTO units_fts(rowid, adresse, municipalite)194  SELECT rowid, adresse, municipalite FROM units;195""")196con.commit()197198# ---- stats plex provinciales ---------------------------------------------------199print("stats.json...", flush=True)200u = pd.read_sql("""SELECT municipalite, nb_logements, valeur_role,201                          aire_etages_m2, superficie_terrain_m2,202                          est_2021, est_2022, est_2023, est_2024, est_2025, est_2026203                   FROM units""", con)204years = [f"est_{y}" for y in range(2021, 2027)]205both = u[["est_2021", "est_2026"]].dropna()206growth = float(both["est_2026"].sum() / both["est_2021"].sum() - 1) * 100207208bands = pd.cut(u["nb_logements"], [1, 2, 3, 5, 11, 10_000],209               labels=["Duplex (2)", "Triplex (3)", "4-5 portes",210                       "6-11 portes", "12+ portes"])211by_band = u.groupby(bands, observed=True).agg(212    n=("est_2026", "count"), total=("est_2026", "sum"),213    mediane=("est_2026", "median")).reset_index()214by_mun = u.groupby("municipalite").agg(215    n=("est_2026", "count"), total=("est_2026", "sum"),216    mediane=("est_2026", "median")).reset_index().dropna() \217    .sort_values("total", ascending=False)218219stats = {220    "generated": "2026-08-09",221    "unites": int(len(u)),222    "valeur_totale_2026": float(u["est_2026"].sum()),223    "valeur_role_totale": float(u["valeur_role"].sum()),224    "valeur_mediane_2026": float(u["est_2026"].median()),225    "croissance_2021_2026_pct": round(growth, 1),226    "logements": int(u["nb_logements"].sum()),227    "aire_etages_km2": round(float(u["aire_etages_m2"].sum()) / 1e6, 1),228    "terrain_km2": round(float(u["superficie_terrain_m2"].sum()) / 1e6, 1),229    "municipalites": int(by_mun["municipalite"].nunique()),230    "totaux_annee": [231        {"year": int(c[4:]), "total": float(u[c].sum()),232         "n": int(u[c].notna().sum())} for c in years233    ],234    "par_type": [235        {"type": str(r.nb_logements), "n": int(r.n), "total": float(r.total),236         "mediane": float(r.mediane) if pd.notna(r.mediane) else None}237        for r in by_band.itertuples()238    ],239    "par_ville": [240        {"ville": r.municipalite, "n": int(r.n), "total": float(r.total),241         "mediane": float(r.mediane) if pd.notna(r.mediane) else None}242        for r in by_mun.head(200).itertuples()243    ],244}245os.makedirs(f"{BASE}/app/src/data", exist_ok=True)246with open(f"{BASE}/app/src/data/stats.json", "w") as f:247    json.dump(stats, f, ensure_ascii=False)248249n_u = con.execute("SELECT COUNT(*) FROM units").fetchone()[0]250n_t = con.execute("SELECT COUNT(*) FROM transactions").fetchone()[0]251print(f"OK — units={n_u}, tx={n_t}")252print(f"VALEUR TOTALE PLEX QC 2026 : {stats['valeur_totale_2026']:,.0f} $ | portes: {stats['logements']:,}")253con.execute("VACUUM")254con.close()255print("DB_DONE")256