Python 68.8%
TypeScript 18.6%
CSS 8.7%
JavaScript 3.3%
HTML 0.6%
1# -----------------------------------------------------------------------------2# Rent-Ka — Rental listings aggregator (Canada, outside Québec)3# Author: Simon-Pierre Boucher — contact@spboucher.ai4# statsfiche.py : panneaux « enrichissements de la fiche » de l'onglet5# Statistiques — tout ce que Rent-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 ks = _n1(con, "SELECT COUNT(*) FROM listings l JOIN kascores k"63 " ON l.coord_key=k.coord_key WHERE l.active=1"64 " AND l.published=1 AND l.dup_of IS NULL")65 fv = _n1(con, "SELECT COUNT(*) FROM fairvalue f JOIN listings l ON f.uid=l.uid "66 "WHERE l.active=1 AND l.published=1 AND l.dup_of IS NULL")67 bld = _n1(con, f"SELECT COUNT(*) FROM listings WHERE {ACTIVE} "68 "AND building_key IS NOT NULL AND building_key<>''")69 imgok = _n1(con, f"SELECT COUNT(*) FROM listings WHERE {ACTIVE} AND images_ok=1")70 compl = con.execute(71 f"SELECT ROUND(AVG(completeness),1) FROM listings WHERE {ACTIVE}"72 ).fetchone()[0]7374 kpis = [75 {"id": "enr_tot", "label": "Fiches actives publiées", "value": tot},76 {"id": "enr_compl", "label": "Complétude moyenne", "value": compl, "unit": "%"},77 {"id": "enr_bld", "label": "Immeubles au passeport",78 "value": _n1(con, "SELECT COUNT(*) FROM buildings")},79 {"id": "enr_img", "label": "Photos vérifiées (cumul)",80 "value": _n1(con, "SELECT COUNT(*) FROM image_checks")},81 ]82 bars = [83 {"label": "Geolocation", "value": _pctof(geo, tot)},84 {"label": "KA Scores", "value": _pctof(ks, tot)},85 {"label": "Fair value", "value": _pctof(fv, tot)},86 {"label": "Building attached", "value": _pctof(bld, tot)},87 {"label": "Photos audited OK", "value": _pctof(imgok, tot)},88 ]8990 # couches de données branchées sur la fiche (chaque ligne est optionnelle)91 layers: list[list] = []9293 def layer(nom: str, count_fn, desc: str) -> None:94 try:95 n = count_fn()96 if n:97 layers.append([nom, _fr(n), desc])98 except Exception:99 pass100101 layer("KA Scores", lambda: _n1(con, "SELECT COUNT(*) FROM kascores"),102 "emplacements notés (marche, transit, vélo, calme, services)")103 layer("Juste valeur", lambda: _n1(con, "SELECT COUNT(*) FROM fairvalue"),104 "évaluations de loyer (modèle comparables Rent-Ka)")105 layer("Historique des prix", lambda: _n1(con, "SELECT COUNT(*) FROM price_log"),106 "points de prix consignés depuis la première capture")107 layer("Immeubles", lambda: _n1(con, "SELECT COUNT(*) FROM buildings"),108 "passeports d'immeuble (historique par adresse)")109110 table = {"id": "enr_couches",111 "title": "Data layers attached to every listing",112 "columns": ["Layer", "Volume", "Description"],113 "rows": layers}114115 return {116 "id": "enrichissement",117 "title": "Listing enrichment — what Rent-Ka adds to the raw listing",118 "subtitle": "Every aggregated listing is enriched automatically: "119 "geocoding, KA Scores, fair value, building passport and "120 "photo audit.",121 "kpis": [k for k in kpis if k.get("value") is not None],122 "breakdowns": [{"id": "enr_couv",123 "title": "Enrichment coverage (% of active listings)",124 "kind": "bar", "items": bars}],125 "tables": [table] if layers else [],126 }127128129# --- Panneau : KA Scores -------------------------------------------------------130def _panel_kascores(con) -> dict | None:131 rows = con.execute(132 "SELECT k.walk, k.transit, k.bike, k.calme, k.services, k.global, l.city"133 " FROM listings l JOIN kascores k ON l.coord_key=k.coord_key"134 " WHERE l.active=1 AND l.published=1 AND l.dup_of IS NULL"135 " AND k.global IS NOT NULL").fetchall()136 if len(rows) < 100:137 return None138 glob = [r["global"] for r in rows]139 kpis = [140 {"id": "ks_n", "label": "Fiches avec KA Scores", "value": len(rows)},141 {"id": "ks_med", "label": "Score global médian",142 "value": round(median(glob), 1), "unit": "/100"},143 {"id": "ks_70", "label": "Score ≥ 70 (excellents secteurs)",144 "value": _pctof(sum(1 for g in glob if g >= 70), len(glob)), "unit": "%"},145 {"id": "ks_loc", "label": "Emplacements notés (cumul)",146 "value": _n1(con, "SELECT COUNT(*) FROM kascores")},147 ]148 piliers = [("walk", "Marche"), ("transit", "Transport"), ("bike", "Vélo"),149 ("calme", "Calme"), ("services", "Services")]150 bars = []151 for col, label in piliers:152 vals = [r[col] for r in rows if r[col] is not None]153 if vals:154 bars.append({"label": label, "value": round(median(vals), 1)})155156 bins = []157 for lo in range(0, 100, 10):158 n = sum(1 for g in glob if lo <= g < lo + 10 or (lo == 90 and g == 100))159 bins.append({"label": str(lo), "value": n})160161 byc: dict[str, list[float]] = {}162 for r in rows:163 if r["city"]:164 byc.setdefault(r["city"], []).append(r["global"])165 top = sorted(((c, v) for c, v in byc.items() if len(v) >= 30),166 key=lambda kv: -median(kv[1]))[:12]167 table = {"id": "ks_villes",168 "title": "Meilleurs scores globaux par ville (min. 30 fiches)",169 "columns": ["Ville", "Fiches notées", "Score global médian"],170 "rows": [[c, len(v), f"{median(v):.1f}"] for c, v in top]}171172 return {173 "id": "kascores",174 "title": "KA Scores — la qualité du secteur, chiffrée",175 "subtitle": "Cinq piliers calculés sur données ouvertes (OSM, GTFS…) "176 "pour chaque emplacement : marche, transport, vélo, calme "177 "et services.",178 "kpis": kpis,179 "breakdowns": [{"id": "ks_piliers",180 "title": "Score médian par pilier (fiches actives)",181 "kind": "bar", "items": bars}] if bars else [],182 "distributions": [{"id": "ks_hist",183 "title": "Distribution du score global",184 "unit": "pts", "bins": bins}],185 "tables": [table] if top else [],186 }187188189# --- Panneau : juste valeur ----------------------------------------------------190def _panel_fairvalue(con) -> dict | None:191 rows = con.execute(192 "SELECT f.deviation, f.verdict, l.city FROM fairvalue f"193 " JOIN listings l ON f.uid=l.uid"194 " WHERE l.active=1 AND l.published=1 AND l.dup_of IS NULL"195 " AND f.deviation IS NOT NULL").fetchall()196 if len(rows) < 100:197 return None198 devs = [r["deviation"] for r in rows]199 # heuristique d'unité : fraction (0.08) vs pourcentage (8.0)200 scale = 100.0 if median(abs(d) for d in devs) < 1.5 else 1.0201 devs_pct = [d * scale for d in devs]202203 labels = {"marche": "Prix du marché", "sous": "Sous le marché",204 "sur": "Au-dessus du marché"}205 counts: dict[str, int] = {}206 for r in rows:207 if r["verdict"]:208 counts[labels.get(r["verdict"], r["verdict"])] = \209 counts.get(labels.get(r["verdict"], r["verdict"]), 0) + 1210211 kpis = [212 {"id": "fv_n", "label": "Loyers évalués (fiches actives)", "value": len(rows)},213 {"id": "fv_med", "label": "Écart médian au loyer estimé",214 "value": round(median(devs_pct), 1), "unit": "%"},215 {"id": "fv_sur", "label": "Au-dessus du marché",216 "value": _pctof(counts.get("Au-dessus du marché", 0), len(rows)), "unit": "%"},217 {"id": "fv_sous", "label": "Sous le marché (aubaines)",218 "value": _pctof(counts.get("Sous le marché", 0), len(rows)), "unit": "%"},219 ]220221 bins = []222 lo = -40223 while lo < 60:224 n = sum(1 for d in devs_pct if lo <= d < lo + 10)225 bins.append({"label": f"{lo}", "value": n})226 lo += 10227228 byc: dict[str, list[float]] = {}229 for r, d in zip(rows, devs_pct):230 if r["city"]:231 byc.setdefault(r["city"], []).append(d)232 top = sorted(((c, v) for c, v in byc.items() if len(v) >= 30),233 key=lambda kv: -median(kv[1]))[:12]234 table = {"id": "fv_villes",235 "title": "Villes où les loyers affichés dépassent le plus la juste "236 "valeur (min. 30 fiches)",237 "columns": ["Ville", "Fiches évaluées", "Écart médian"],238 "rows": [[c, len(v), f"{median(v):+.1f} %"] for c, v in top]}239240 return {241 "id": "juste_valeur",242 "title": "Juste valeur — le loyer affiché est-il le bon prix ?",243 "subtitle": "Chaque fiche est comparée aux logements semblables du "244 "même secteur (modèle de comparables Rent-Ka).",245 "kpis": kpis,246 "breakdowns": [{"id": "fv_verdicts", "title": "Verdicts de juste valeur",247 "kind": "donut",248 "items": [{"label": k, "value": v}249 for k, v in sorted(counts.items(),250 key=lambda kv: -kv[1])]}]251 if counts else [],252 "distributions": [{"id": "fv_hist",253 "title": "Distribution des écarts au loyer estimé (%)",254 "unit": "%", "bins": bins}],255 "tables": [table] if top else [],256 }257258259# --- Panneau : historique des prix & vie des annonces --------------------------260def _panel_historique(con) -> dict | None:261 rows = con.execute(262 "SELECT uid, ts, price FROM price_log ORDER BY uid, ts").fetchall()263 if len(rows) < 200:264 return None265 npts = len(rows)266 drops: list[float] = []267 hikes: list[float] = []268 changed: set[str] = set()269 prev_uid, prev_price = None, None270 for r in rows:271 if r["uid"] == prev_uid and prev_price and r["price"] and r["price"] != prev_price:272 pct = (r["price"] - prev_price) / prev_price * 100.0273 if -60.0 <= pct <= 120.0:274 (drops if pct < 0 else hikes).append(pct)275 changed.add(r["uid"])276 prev_uid, prev_price = r["uid"], r["price"]277278 kpis = [279 {"id": "px_pts", "label": "Points de prix consignés", "value": npts},280 {"id": "px_chg", "label": "Annonces avec changement de prix",281 "value": len(changed)},282 {"id": "px_drop", "label": "Baisses de loyer détectées", "value": len(drops)},283 {"id": "px_dmed", "label": "Baisse médiane",284 "value": round(median(drops), 1) if drops else None, "unit": "%"},285 ]286 donut = [{"label": "Baisses", "value": len(drops)},287 {"label": "Hausses", "value": len(hikes)}]288289 ev_labels = {"disparition": "Disparition", "reapparition": "Réapparition",290 "photos": "Photos modifiées", "description": "Description modifiée",291 "dispo": "Disponibilité modifiée", "inclusions": "Inclusions modifiées",292 "superficie": "Superficie modifiée"}293 evs = [{"label": ev_labels.get(r["event"], r["event"]), "value": r["n"]}294 for r in con.execute(295 "SELECT event, COUNT(*) n FROM listing_events GROUP BY event"296 " ORDER BY n DESC")]297298 return {299 "id": "historique_prix",300 "title": "Historique des prix — chaque loyer est suivi dans le temps",301 "subtitle": "Rent-Ka consigne le loyer de chaque annonce à chaque "302 "synchronisation : baisses, hausses et événements de vie "303 "de l'annonce apparaissent sur la fiche.",304 "kpis": [k for k in kpis if k.get("value") is not None],305 "breakdowns": ([{"id": "px_sens", "title": "Changements de loyer détectés",306 "kind": "donut", "items": donut}]307 + ([{"id": "px_events",308 "title": "Événements de vie des annonces (photos, "309 "description, disponibilité…)",310 "kind": "bar", "items": evs}] if evs else [])),311 }312313314315# --- Panneau : gestionnaires (masqué tant que < 10 fiches Google notées) --------316def _panel_gestionnaires(con) -> dict | None:317 rows = con.execute(318 "SELECT name, gmaps_rating r, gmaps_reviews nrev FROM managers"319 " WHERE gmaps_rating IS NOT NULL").fetchall()320 if len(rows) < 10:321 return None322 notes = [r["r"] for r in rows]323 kpis = [324 {"id": "mg_n", "label": "Gestionnaires répertoriés",325 "value": _n1(con, "SELECT COUNT(*) FROM managers")},326 {"id": "mg_fiche", "label": "Avec fiche Google notée", "value": len(rows)},327 {"id": "mg_med", "label": "Note Google médiane",328 "value": round(median(notes), 2), "unit": "/5"},329 {"id": "mg_avis", "label": "Avis cumulés",330 "value": sum(r["nrev"] or 0 for r in rows)},331 ]332 top = sorted((r for r in rows if (r["nrev"] or 0) >= 20),333 key=lambda r: -r["r"])[:12]334 table = {"id": "mg_top",335 "title": "Gestionnaires les mieux notés (min. 20 avis)",336 "columns": ["Gestionnaire", "Note Google", "Avis"],337 "rows": [[r["name"], f"{r['r']:.1f}", r["nrev"]] for r in top]}338 return {339 "id": "gestionnaires",340 "title": "Gestionnaires immobiliers — réputation Google",341 "subtitle": "Chaque gestionnaire agrégé est rapproché de sa fiche "342 "Google Maps : note et avis apparaissent sur ses annonces.",343 "kpis": kpis,344 "tables": [table] if top else [],345 }346347348def panels(con: sqlite3.Connection) -> list[dict]:349 """Panneaux « enrichissements de la fiche » (cache 30 min)."""350 hit = _CACHE.get("panels")351 if hit and time.time() - hit[0] < _TTL:352 return hit[1]353 out = []354 for fn in (lambda: _panel_enrichissement(con),355 lambda: _panel_kascores(con),356 lambda: _panel_fairvalue(con),357 lambda: _panel_historique(con),358 lambda: _panel_gestionnaires(con)):359 try:360 p = fn()361 if p and (p.get("kpis") or p.get("tables") or p.get("breakdowns")):362 out.append(p)363 except Exception:364 continue365 _CACHE["panels"] = (time.time(), out)366 return out367