Lou·Ka — tous les logements à louer du Québec, un seul endroit.
HTML 98.9%
Python 0.6%
1# -----------------------------------------------------------------------------2# Lou-Ka — Agrégateur de logements à louer (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# statsfiche.py : panneaux « enrichissements de la fiche » de l'onglet5# Statistiques — tout ce que Lou-Ka ajoute par-dessus l'annonce brute :6# · couverture des enrichissements (géocodage, quartier, KA Scores,7# juste valeur, immeuble, photos) + couches de données branchées ;8# · KA Scores (piliers, distribution, villes) ;9# · Juste valeur (verdicts, écarts, villes surchauffées) ;10# · Historique des prix (baisses/hausses) + vie des annonces ;11# · Court terme (chalets et hébergements, CITQ, notes) ;12# · Gestionnaires (fiches Google, notes) — masqué tant que < 10 notés ;13# · Annuaire des déménageurs du Québec.14# Même contrat de rendu que statsextra.py (kpis/breakdowns/distributions/15# tables, rendu générique du kit stats) — un panneau absent si sa source16# ne répond pas. Cache 30 min.17# -----------------------------------------------------------------------------18from __future__ import annotations1920import json21import sqlite322import time23from pathlib import Path24from statistics import median2526ROOT = Path(__file__).resolve().parent.parent27DATA = ROOT / "data"28_CACHE: dict[str, tuple[float, list]] = {}29_TTL = 18003031ACTIVE = "active=1 AND published=1 AND dup_of IS NULL"323334def _ro(name: str) -> sqlite3.Connection | None:35 p = DATA / name36 if not p.exists():37 return None38 con = sqlite3.connect(f"file:{p}?mode=ro", uri=True)39 con.row_factory = sqlite3.Row40 return con414243def _n1(con, sql: str, args: tuple = ()) -> int:44 return con.execute(sql, args).fetchone()[0] or 0454647def _fr(n: float) -> str:48 return f"{round(n):,}".replace(",", " ")495051def _pctof(part: int, tot: int) -> float:52 return round(100.0 * part / tot, 1) if tot else 0.0535455# --- Panneau : couverture des enrichissements ---------------------------------56def _panel_enrichissement(con) -> dict | None:57 tot = _n1(con, f"SELECT COUNT(*) FROM listings WHERE {ACTIVE}")58 if tot < 100:59 return None60 geo = _n1(con, f"SELECT COUNT(*) FROM listings WHERE {ACTIVE} "61 "AND lat IS NOT NULL AND lng IS NOT NULL")62 quart = _n1(con, f"SELECT COUNT(*) FROM listings WHERE {ACTIVE} "63 "AND dauid IS NOT NULL AND dauid<>''")64 ks = _n1(con, "SELECT COUNT(*) FROM listings l JOIN kascores k"65 " ON l.coord_key=k.coord_key WHERE l.active=1"66 " AND l.published=1 AND l.dup_of IS NULL")67 fv = _n1(con, "SELECT COUNT(*) FROM fairvalue f JOIN listings l ON f.uid=l.uid "68 "WHERE l.active=1 AND l.published=1 AND l.dup_of IS NULL")69 bld = _n1(con, f"SELECT COUNT(*) FROM listings WHERE {ACTIVE} "70 "AND building_key IS NOT NULL AND building_key<>''")71 imgok = _n1(con, f"SELECT COUNT(*) FROM listings WHERE {ACTIVE} AND images_ok=1")72 compl = con.execute(73 f"SELECT ROUND(AVG(completeness),1) FROM listings WHERE {ACTIVE}"74 ).fetchone()[0]7576 kpis = [77 {"id": "enr_tot", "label": "Fiches actives publiées", "value": tot},78 {"id": "enr_compl", "label": "Complétude moyenne", "value": compl, "unit": "%"},79 {"id": "enr_bld", "label": "Immeubles au passeport",80 "value": _n1(con, "SELECT COUNT(*) FROM buildings")},81 {"id": "enr_img", "label": "Photos vérifiées (cumul)",82 "value": _n1(con, "SELECT COUNT(*) FROM image_checks")},83 ]84 bars = [85 {"label": "Géolocalisation", "value": _pctof(geo, tot)},86 {"label": "Quartier (recensement)", "value": _pctof(quart, tot)},87 {"label": "KA Scores", "value": _pctof(ks, tot)},88 {"label": "Juste valeur", "value": _pctof(fv, tot)},89 {"label": "Immeuble rattaché", "value": _pctof(bld, tot)},90 {"label": "Photos auditées OK", "value": _pctof(imgok, tot)},91 ]9293 # couches de données branchées sur la fiche (chaque ligne est optionnelle)94 layers: list[list] = []9596 def layer(nom: str, count_fn, desc: str) -> None:97 try:98 n = count_fn()99 if n:100 layers.append([nom, _fr(n), desc])101 except Exception:102 pass103104 layer("KA Scores", lambda: _n1(con, "SELECT COUNT(*) FROM kascores"),105 "emplacements notés (marche, transit, vélo, calme, services)")106 layer("Juste valeur", lambda: _n1(con, "SELECT COUNT(*) FROM fairvalue"),107 "évaluations de loyer (modèle comparables Lou-Ka)")108 layer("Historique des prix", lambda: _n1(con, "SELECT COUNT(*) FROM price_log"),109 "points de prix consignés depuis la première capture")110 layer("Immeubles", lambda: _n1(con, "SELECT COUNT(*) FROM buildings"),111 "passeports d'immeuble (historique par adresse)")112113 def _side(db: str, sql: str) -> int:114 c = _ro(db)115 if c is None:116 return 0117 try:118 return c.execute(sql).fetchone()[0] or 0119 finally:120 c.close()121122 layer("Qualité de l'air", lambda: _side("air.db", "SELECT COUNT(DISTINCT station) FROM air_stats"),123 "stations de mesure (RSQAQ) rattachées aux fiches")124 layer("Zones inondables", lambda: _side("inondation.db", "SELECT COUNT(*) FROM zi"),125 "polygones officiels vérifiés au survol de chaque fiche")126 layer("Commerces à proximité", lambda: _side("commerces.db", "SELECT COUNT(*) FROM commerces_cache"),127 "cellules de commerces essentiels en cache")128 layer("Hydro-Québec", lambda: _side("hydro.db", "SELECT COUNT(*) FROM hydro_cache"),129 "estimations de coût d'électricité par adresse")130 layer("Dossiers TAL", lambda: _side("tal.db", "SELECT COUNT(*) FROM tal_lookup"),131 "adresses vérifiées au Tribunal administratif du logement")132 layer("Registre des loyers", lambda: _side("rdl.db", "SELECT COUNT(*) FROM rdl_housings"),133 "loyers réellement déclarés (Vivre en ville)")134135 table = {"id": "enr_couches",136 "title": "Couches de données branchées sur chaque fiche",137 "columns": ["Couche", "Volume", "Description"],138 "rows": layers}139140 return {141 "id": "enrichissement",142 "title": "Enrichissement des fiches — ce que Lou-Ka ajoute à l'annonce",143 "subtitle": "Chaque annonce agrégée est enrichie automatiquement : "144 "géocodage, quartier de recensement, KA Scores, juste "145 "valeur, passeport d'immeuble, audit des photos et couches "146 "du territoire.",147 "kpis": [k for k in kpis if k.get("value") is not None],148 "breakdowns": [{"id": "enr_couv",149 "title": "Couverture des enrichissements (% des fiches actives)",150 "kind": "bar", "items": bars}],151 "tables": [table] if layers else [],152 }153154155# --- Panneau : KA Scores -------------------------------------------------------156def _panel_kascores(con) -> dict | None:157 rows = con.execute(158 "SELECT k.walk, k.transit, k.bike, k.calme, k.services, k.global, l.city"159 " FROM listings l JOIN kascores k ON l.coord_key=k.coord_key"160 " WHERE l.active=1 AND l.published=1 AND l.dup_of IS NULL"161 " AND k.global IS NOT NULL").fetchall()162 if len(rows) < 100:163 return None164 glob = [r["global"] for r in rows]165 kpis = [166 {"id": "ks_n", "label": "Fiches avec KA Scores", "value": len(rows)},167 {"id": "ks_med", "label": "Score global médian",168 "value": round(median(glob), 1), "unit": "/100"},169 {"id": "ks_70", "label": "Score ≥ 70 (excellents secteurs)",170 "value": _pctof(sum(1 for g in glob if g >= 70), len(glob)), "unit": "%"},171 {"id": "ks_loc", "label": "Emplacements notés (cumul)",172 "value": _n1(con, "SELECT COUNT(*) FROM kascores")},173 ]174 piliers = [("walk", "Marche"), ("transit", "Transport"), ("bike", "Vélo"),175 ("calme", "Calme"), ("services", "Services")]176 bars = []177 for col, label in piliers:178 vals = [r[col] for r in rows if r[col] is not None]179 if vals:180 bars.append({"label": label, "value": round(median(vals), 1)})181182 bins = []183 for lo in range(0, 100, 10):184 n = sum(1 for g in glob if lo <= g < lo + 10 or (lo == 90 and g == 100))185 bins.append({"label": str(lo), "value": n})186187 byc: dict[str, list[float]] = {}188 for r in rows:189 if r["city"]:190 byc.setdefault(r["city"], []).append(r["global"])191 top = sorted(((c, v) for c, v in byc.items() if len(v) >= 30),192 key=lambda kv: -median(kv[1]))[:12]193 table = {"id": "ks_villes",194 "title": "Meilleurs scores globaux par ville (min. 30 fiches)",195 "columns": ["Ville", "Fiches notées", "Score global médian"],196 "rows": [[c, len(v), f"{median(v):.1f}"] for c, v in top]}197198 return {199 "id": "kascores",200 "title": "KA Scores — la qualité du secteur, chiffrée",201 "subtitle": "Cinq piliers calculés sur données ouvertes (OSM, GTFS…) "202 "pour chaque emplacement : marche, transport, vélo, calme "203 "et services.",204 "kpis": kpis,205 "breakdowns": [{"id": "ks_piliers",206 "title": "Score médian par pilier (fiches actives)",207 "kind": "bar", "items": bars}] if bars else [],208 "distributions": [{"id": "ks_hist",209 "title": "Distribution du score global",210 "unit": "pts", "bins": bins}],211 "tables": [table] if top else [],212 }213214215# --- Panneau : juste valeur ----------------------------------------------------216def _panel_fairvalue(con) -> dict | None:217 rows = con.execute(218 "SELECT f.deviation, f.verdict, l.city FROM fairvalue f"219 " JOIN listings l ON f.uid=l.uid"220 " WHERE l.active=1 AND l.published=1 AND l.dup_of IS NULL"221 " AND f.deviation IS NOT NULL").fetchall()222 if len(rows) < 100:223 return None224 devs = [r["deviation"] for r in rows]225 # heuristique d'unité : fraction (0.08) vs pourcentage (8.0)226 scale = 100.0 if median(abs(d) for d in devs) < 1.5 else 1.0227 devs_pct = [d * scale for d in devs]228229 labels = {"marche": "Prix du marché", "sous": "Sous le marché",230 "sur": "Au-dessus du marché"}231 counts: dict[str, int] = {}232 for r in rows:233 if r["verdict"]:234 counts[labels.get(r["verdict"], r["verdict"])] = \235 counts.get(labels.get(r["verdict"], r["verdict"]), 0) + 1236237 kpis = [238 {"id": "fv_n", "label": "Loyers évalués (fiches actives)", "value": len(rows)},239 {"id": "fv_med", "label": "Écart médian au loyer estimé",240 "value": round(median(devs_pct), 1), "unit": "%"},241 {"id": "fv_sur", "label": "Au-dessus du marché",242 "value": _pctof(counts.get("Au-dessus du marché", 0), len(rows)), "unit": "%"},243 {"id": "fv_sous", "label": "Sous le marché (aubaines)",244 "value": _pctof(counts.get("Sous le marché", 0), len(rows)), "unit": "%"},245 ]246247 bins = []248 lo = -40249 while lo < 60:250 n = sum(1 for d in devs_pct if lo <= d < lo + 10)251 bins.append({"label": f"{lo}", "value": n})252 lo += 10253254 byc: dict[str, list[float]] = {}255 for r, d in zip(rows, devs_pct):256 if r["city"]:257 byc.setdefault(r["city"], []).append(d)258 top = sorted(((c, v) for c, v in byc.items() if len(v) >= 30),259 key=lambda kv: -median(kv[1]))[:12]260 table = {"id": "fv_villes",261 "title": "Villes où les loyers affichés dépassent le plus la juste "262 "valeur (min. 30 fiches)",263 "columns": ["Ville", "Fiches évaluées", "Écart médian"],264 "rows": [[c, len(v), f"{median(v):+.1f} %"] for c, v in top]}265266 return {267 "id": "juste_valeur",268 "title": "Juste valeur — le loyer affiché est-il le bon prix ?",269 "subtitle": "Chaque fiche est comparée aux logements semblables du "270 "même secteur (modèle de comparables Lou-Ka).",271 "kpis": kpis,272 "breakdowns": [{"id": "fv_verdicts", "title": "Verdicts de juste valeur",273 "kind": "donut",274 "items": [{"label": k, "value": v}275 for k, v in sorted(counts.items(),276 key=lambda kv: -kv[1])]}]277 if counts else [],278 "distributions": [{"id": "fv_hist",279 "title": "Distribution des écarts au loyer estimé (%)",280 "unit": "%", "bins": bins}],281 "tables": [table] if top else [],282 }283284285# --- Panneau : historique des prix & vie des annonces --------------------------286def _panel_historique(con) -> dict | None:287 rows = con.execute(288 "SELECT uid, ts, price FROM price_log ORDER BY uid, ts").fetchall()289 if len(rows) < 200:290 return None291 npts = len(rows)292 drops: list[float] = []293 hikes: list[float] = []294 changed: set[str] = set()295 prev_uid, prev_price = None, None296 for r in rows:297 if r["uid"] == prev_uid and prev_price and r["price"] and r["price"] != prev_price:298 pct = (r["price"] - prev_price) / prev_price * 100.0299 if -60.0 <= pct <= 120.0:300 (drops if pct < 0 else hikes).append(pct)301 changed.add(r["uid"])302 prev_uid, prev_price = r["uid"], r["price"]303304 kpis = [305 {"id": "px_pts", "label": "Points de prix consignés", "value": npts},306 {"id": "px_chg", "label": "Annonces avec changement de prix",307 "value": len(changed)},308 {"id": "px_drop", "label": "Baisses de loyer détectées", "value": len(drops)},309 {"id": "px_dmed", "label": "Baisse médiane",310 "value": round(median(drops), 1) if drops else None, "unit": "%"},311 ]312 donut = [{"label": "Baisses", "value": len(drops)},313 {"label": "Hausses", "value": len(hikes)}]314315 ev_labels = {"disparition": "Disparition", "reapparition": "Réapparition",316 "photos": "Photos modifiées", "description": "Description modifiée",317 "dispo": "Disponibilité modifiée", "inclusions": "Inclusions modifiées",318 "superficie": "Superficie modifiée"}319 evs = [{"label": ev_labels.get(r["event"], r["event"]), "value": r["n"]}320 for r in con.execute(321 "SELECT event, COUNT(*) n FROM listing_events GROUP BY event"322 " ORDER BY n DESC")]323324 return {325 "id": "historique_prix",326 "title": "Historique des prix — chaque loyer est suivi dans le temps",327 "subtitle": "Lou-Ka consigne le loyer de chaque annonce à chaque "328 "synchronisation : baisses, hausses et événements de vie "329 "de l'annonce apparaissent sur la fiche.",330 "kpis": [k for k in kpis if k.get("value") is not None],331 "breakdowns": ([{"id": "px_sens", "title": "Changements de loyer détectés",332 "kind": "donut", "items": donut}]333 + ([{"id": "px_events",334 "title": "Événements de vie des annonces (photos, "335 "description, disponibilité…)",336 "kind": "bar", "items": evs}] if evs else [])),337 }338339340# --- Panneau : court terme ------------------------------------------------------341def _panel_court_terme() -> dict | None:342 con = _ro("louka_ct.db")343 if con is None:344 return None345 try:346 tot, nsrc = con.execute(347 "SELECT COUNT(*), COUNT(DISTINCT source) FROM st_listings"348 " WHERE active=1").fetchone()349 if not tot or tot < 100:350 return None351 prices = [r[0] for r in con.execute(352 "SELECT price_night FROM st_listings WHERE active=1"353 " AND price_night > 20 AND price_night < 10000")]354 citq = con.execute("SELECT COUNT(*) FROM st_listings WHERE active=1"355 " AND citq IS NOT NULL AND citq<>''").fetchone()[0]356 rated = [r[0] for r in con.execute(357 "SELECT rating FROM st_listings WHERE active=1 AND rating IS NOT NULL")]358 byreg = {}359 for r in con.execute(360 "SELECT region, price_night FROM st_listings WHERE active=1"361 " AND region IS NOT NULL AND region<>'' AND price_night > 20"362 " AND price_night < 10000"):363 byreg.setdefault(r[0], []).append(r[1])364 finally:365 con.close()366367 bars = sorted(([{"label": k, "value": round(median(v))}368 for k, v in byreg.items() if len(v) >= 100]),369 key=lambda x: -x["value"])[:12]370 kpis = [371 {"id": "ct_n", "label": "Hébergements court terme actifs", "value": tot},372 {"id": "ct_src", "label": "Plateformes agrégées", "value": nsrc},373 {"id": "ct_px", "label": "Prix médian par nuit",374 "value": round(median(prices)) if prices else None, "unit": "$"},375 {"id": "ct_citq", "label": "Avec n° d'enregistrement CITQ",376 "value": _pctof(citq, tot), "unit": "%"},377 {"id": "ct_note", "label": "Note médiane des voyageurs",378 "value": round(median(rated), 2) if len(rated) >= 50 else None, "unit": "/5"},379 ]380 return {381 "id": "court_terme",382 "title": "Court terme — chalets et hébergements à la nuit",383 "subtitle": "La section Court terme agrège les plateformes de location "384 "de chalets et d'hébergements partout au Québec.",385 "kpis": [k for k in kpis if k.get("value") is not None],386 "breakdowns": [{"id": "ct_reg",387 "title": "Prix médian par nuit selon la région "388 "(min. 100 annonces)",389 "kind": "bar", "items": bars}] if bars else [],390 }391392393# --- Panneau : gestionnaires (masqué tant que < 10 fiches Google notées) --------394def _panel_gestionnaires(con) -> dict | None:395 rows = con.execute(396 "SELECT name, gmaps_rating r, gmaps_reviews nrev FROM managers"397 " WHERE gmaps_rating IS NOT NULL").fetchall()398 if len(rows) < 10:399 return None400 notes = [r["r"] for r in rows]401 kpis = [402 {"id": "mg_n", "label": "Gestionnaires répertoriés",403 "value": _n1(con, "SELECT COUNT(*) FROM managers")},404 {"id": "mg_fiche", "label": "Avec fiche Google notée", "value": len(rows)},405 {"id": "mg_med", "label": "Note Google médiane",406 "value": round(median(notes), 2), "unit": "/5"},407 {"id": "mg_avis", "label": "Avis cumulés",408 "value": sum(r["nrev"] or 0 for r in rows)},409 ]410 top = sorted((r for r in rows if (r["nrev"] or 0) >= 20),411 key=lambda r: -r["r"])[:12]412 table = {"id": "mg_top",413 "title": "Gestionnaires les mieux notés (min. 20 avis)",414 "columns": ["Gestionnaire", "Note Google", "Avis"],415 "rows": [[r["name"], f"{r['r']:.1f}", r["nrev"]] for r in top]}416 return {417 "id": "gestionnaires",418 "title": "Gestionnaires immobiliers — réputation Google",419 "subtitle": "Chaque gestionnaire agrégé est rapproché de sa fiche "420 "Google Maps : note et avis apparaissent sur ses annonces.",421 "kpis": kpis,422 "tables": [table] if top else [],423 }424425426# --- Panneau : annuaire des déménageurs -----------------------------------------427def _panel_demenageurs() -> dict | None:428 p = DATA / "demenageurs.json"429 if not p.exists():430 return None431 doc = json.loads(p.read_text(encoding="utf-8"))432 movers = doc.get("movers", [])433 if len(movers) < 50:434 return None435 rated = [m["rating"] for m in movers if m.get("rating")]436 byreg: dict[str, int] = {}437 for m in movers:438 byreg[m["region"]] = byreg.get(m["region"], 0) + 1439 bars = sorted(({"label": k, "value": v} for k, v in byreg.items()),440 key=lambda x: -x["value"])[:12]441 kpis = [442 {"id": "dem_n", "label": "Déménageurs répertoriés", "value": len(movers)},443 {"id": "dem_reg", "label": "Régions couvertes", "value": len(byreg)},444 {"id": "dem_web", "label": "Avec site web",445 "value": _pctof(sum(1 for m in movers if m.get("website")), len(movers)),446 "unit": "%"},447 {"id": "dem_note", "label": "Note Google médiane",448 "value": round(median(rated), 1) if len(rated) >= 30 else None,449 "unit": "/5"},450 ]451 return {452 "id": "annuaire_demenageurs",453 "title": "Annuaire des déménageurs du Québec",454 "subtitle": "L'annuaire complet des entreprises de déménagement de la "455 "province, présenté sur /demenageurs.",456 "kpis": [k for k in kpis if k.get("value") is not None],457 "breakdowns": [{"id": "dem_reg_bar",458 "title": "Déménageurs par région (top 12)",459 "kind": "bar", "items": bars}],460 }461462463def panels(con: sqlite3.Connection) -> list[dict]:464 """Panneaux « enrichissements de la fiche » (cache 30 min)."""465 hit = _CACHE.get("panels")466 if hit and time.time() - hit[0] < _TTL:467 return hit[1]468 out = []469 for fn in (lambda: _panel_enrichissement(con),470 lambda: _panel_kascores(con),471 lambda: _panel_fairvalue(con),472 lambda: _panel_historique(con),473 _panel_court_terme,474 lambda: _panel_gestionnaires(con),475 _panel_demenageurs):476 try:477 p = fn()478 if p and (p.get("kpis") or p.get("tables") or p.get("breakdowns")):479 out.append(p)480 except Exception:481 continue482 _CACHE["panels"] = (time.time(), out)483 return out484