spb/qc-election

1# QC Élection Forecast — Plateforme de prévision électorale du Québec 20262# Auteur : Simon-Pierre Boucher3# Contact : contact@spboucher.ai4# https://www.qc-election.com5"""Feature store démographique des 127 circonscriptions (Phase D).67SOURCE TIER 1 IDÉALE : Élections Québec publie, pour chacune des 1278circonscriptions 2026, un « Portrait socioéconomique » — produits personnalisés9du Recensement 2021 de Statistique Canada calculés SUR LES LIMITES 202610(aucune interpolation d'aires de diffusion requise) :11âge × genre, langue parlée à la maison, diplomation, revenu des ménages,12immigration, identité autochtone, minorités visibles, type de logement.1314Sorties :15  * data/population/riding_2026_demographics.csv — 1 ligne / circonscription16    (variables clés + z-scores standardisés sur les 127) avec provenance;17  * data/population/riding_similarity.json — top 10 de circonscriptions18    SEMBLABLES par distance cosinus sur les features standardisées (§29 :19    partial pooling, transferts du soir d'élection, comparaisons);20  * data/population/portraits/{slug}.json — détail complet par circonscription21    (fiches locales, cellules MRP marginales âge×genre×langue).22"""23from __future__ import annotations2425import json26import logging27import re28import unicodedata29from concurrent.futures import ThreadPoolExecutor30from datetime import date3132import numpy as np33from sqlalchemy.orm import Session3435from ..config import DATA_DIR, settings36from .. import models as Mo3738log = logging.getLogger("eq-socio")3940POP_DIR = DATA_DIR / "population"41PORTRAIT_URL = ("https://www.electionsquebec.qc.ca/cartes-electorales/"42                "circonscriptions-provinciales/portrait-socioeconomique/{slug}/")4344AGE_GROUPS = ["0 à 9", "10 à 19", "20 à 29", "30 à 39", "40 à 49",45              "50 à 59", "60 à 69", "70 à 79", "80 ans et plus"]4647# variables retenues pour la standardisation / similarité48Z_FEATURES = ["pct_60_plus", "pct_locataires", "pct_francais", "pct_anglais",49              "pct_universitaire", "pct_revenu_100k_plus", "pct_immigrants",50              "pct_autochtone", "pct_appartements"]515253def slug_of(name: str) -> str:54    s = re.sub(r"[–—]", "-", name)          # tirets cadratins AVANT l'ascii-fold55    s = unicodedata.normalize("NFKD", s).encode("ascii", "ignore").decode()56    s = s.replace("'", "").replace("’", "")57    s = re.sub(r"[\s-]+", "-", s).lower()58    return re.sub(r"[^a-z0-9-]", "", s)596061def _table_after(md: str, header_kw: str, span: int = 2600) -> dict[str, float]:62    """Première table markdown après un titre contenant `header_kw` →63    {libellé: valeur de la circonscription (1re colonne de données)}."""64    i = md.lower().find(header_kw.lower())65    if i < 0:66        return {}67    window = md[i:i + span]68    out = {}69    for m in re.finditer(r"^\|\s*([^|]{2,80}?)\s*\|\s*([\d\s ,.$%]+?)\s*\|", window, re.M):70        label = m.group(1).strip().strip("*")71        raw = m.group(2).replace("%", "").replace("$", "")72        raw = re.sub(r"[\s ]", "", raw).replace(",", ".")73        try:74            v = float(raw)75        except ValueError:76            continue77        # les tables successives réutilisent les mêmes libellés (âge en %,78        # puis effectifs Hommes/Femmes, puis Province) : la PREMIÈRE table79        # après le titre fait foi — jamais d'écrasement.80        if label not in out:81            out[label] = v82    return out838485def _riding_population(md: str, name: str) -> float | None:86    """Population = total Hommes + Femmes du bloc de LA circonscription87    (le bloc « Province » suit — on s'arrête avant)."""88    i = md.find("Répartition des femmes et des hommes selon l'âge")89    if i < 0:90        return None91    block = md[i:i + 2600]92    j = block.find("**Province**")93    if j > 0:94        block = block[:j]95    m = re.search(r"\|\s*\*\*Total\*\*\s*\|\s*([\d\s , ]+)\|\s*([\d\s , ]+)\|",96                  block)97    if not m:98        return None99    def n(s):100        return float(re.sub(r"[^\d]", "", s) or 0)101    return n(m.group(1)) + n(m.group(2))102103104def parse_portrait(md: str, name: str = "") -> dict | None:105    """Markdown du portrait → variables clés (avec les tables brutes utiles)."""106    ages = _table_after(md, "Population totale selon les groupes d'âge", 1000)107    ages = {k: v for k, v in ages.items()108            if any(k.startswith(g) for g in AGE_GROUPS) and v <= 100}109    if len(ages) < 7:110        return None111    pop_total = _riding_population(md, name)112    langue = _table_after(md, "langue la plus souvent parlée", 900)113    diplo = _table_after(md, "plus haut diplôme ou grade", 2200)114    revenu = _table_after(md, "Revenu des ménages", 2600)115    immig = _table_after(md, "Citoyenneté et immigration", 1600)116    autoch = _table_after(md, "identité autochtone", 700)117    logement = _table_after(md, "type de construction résidentielle", 1800)118    minor = _table_after(md, "Minorités visibles", 900)119120    def find(d, *kws):121        for k, v in d.items():122            if all(kw.lower() in k.lower() for kw in kws):123                return v124        return None125126    g60 = sum(v for k, v in ages.items()127              if k.startswith(("60 à 69", "70 à 79", "80 ans")))128    g2039 = sum(v for k, v in ages.items() if k.startswith(("20 à 29", "30 à 39")))129    uni = find(diplo, "universitaire") or find(diplo, "baccalauréat")130    rev100 = sum(v for k, v in revenu.items()131                 if re.match(r"(100 000|125 000|150 000|200 000)", k))132    return {133        "population": pop_total,134        "age_groups_pct": ages,135        "pct_60_plus": round(g60, 1),136        "pct_20_39": round(g2039, 1),137        "pct_francais": find(langue, "français"),138        "pct_anglais": find(langue, "anglais"),139        "pct_universitaire": uni,140        "pct_revenu_100k_plus": round(rev100, 1) if revenu else None,141        "pct_immigrants": find(immig, "immigrant"),142        "pct_autochtone": find(autoch, "autochtone"),143        "pct_minorites_visibles": find(minor, "minorit") or find(minor, "total"),144        "pct_appartements": find(logement, "appartement") or145                            find(logement, "cinq étages"),146        "tables": {"langue": langue, "revenu": revenu, "diplome": diplo,147                   "logement": logement},148    }149150151XLS_URL = ("https://docs.electionsquebec.qc.ca/PRO/6a58edde4eab9/"152           "statistiques-recensement-2021-CEP2026.xls")153XLS_PATH = POP_DIR / "statistiques-recensement-2021-CEP2026.xls"154155# Codes de variables stables du classeur officiel (feuille « 127 CEP 2026  »)156XLS_CODES = {157    "population": "TAB1CH_0",158    "pct_65_plus": "TAB1CH_20b",159    "pct_revenu_100k_plus": "TAB2C2247_REV",160    "pct_autochtone": "TAB1CH_lan_N_9",161    "pct_locataires": "TAB2C2515a",162    "n_immigrants": "TAB2C18",163    "n_immigration_total": "TAB2C16",164    "n_uni_bacc_plus": "TAB3C706",165    "pct_fr_maison": "TAB1CH_580a",   # langue la plus parlée à la MAISON166    "pct_en_maison": "TAB1CH_579a",167    "n_app_duplex": "TAB1CH_209",168    "n_app_moins5": "TAB1CH_210",169    "n_app_5plus": "TAB1CH_211",170    "n_logements_occ": "TAB1CH_0b",171}172173174def build_from_xls(db: Session) -> dict:175    """SOURCE PRINCIPALE : le classeur officiel d'Élections Québec176    (Recensement 2021, 2 917 variables × 127 circonscriptions 2026, codes177    stables) — déterministe, complet, sans scraping."""178    import httpx179    import pandas as pd180    POP_DIR.mkdir(parents=True, exist_ok=True)181    if not XLS_PATH.exists():182        r = httpx.get(XLS_URL, timeout=180, follow_redirects=True)183        r.raise_for_status()184        XLS_PATH.write_bytes(r.content)185    raw = pd.ExcelFile(XLS_PATH).parse("127 CEP 2026 ", header=None)186    # noms de circonscription sur deux lignes (L1 + L2)187    names = []188    for c in range(3, raw.shape[1]):189        l1 = str(raw.iat[1, c]) if pd.notna(raw.iat[1, c]) else ""190        l2 = str(raw.iat[2, c]) if pd.notna(raw.iat[2, c]) else ""191        names.append((l1 + l2).strip())192    code_row = {str(raw.iat[i, 0]).strip(): i for i in range(raw.shape[0])193                if pd.notna(raw.iat[i, 0])}194    # % minorités visibles : repérée par libellé (code variable selon éditions)195    minor_row = next((i for i in range(raw.shape[0])196                      if pd.notna(raw.iat[i, 1]) and "total des minorités visibles"197                      in str(raw.iat[i, 1]).lower()198                      and "pourcentage" in str(raw.iat[i, 1]).lower()), None)199    uni_denom_row = next((i for i in range(raw.shape[0])200                          if pd.notna(raw.iat[i, 1]) and201                          "15 ans et plus dans les ménages privés selon le plus haut"202                          in str(raw.iat[i, 1])), None)203204    def val(code_or_row, col):205        i = code_row.get(code_or_row) if isinstance(code_or_row, str) else code_or_row206        if i is None:207            return None208        v = raw.iat[i, col]209        try:210            return float(v)211        except (TypeError, ValueError):212            return None213214    # alignement noms XLS ↔ noms de la base (accents/tirets)215    db_names = {slug_of(d.name): d.name for d in db.query(Mo.District).all()}216    rows = []217    for k, xls_name in enumerate(names):218        col = 3 + k219        name = db_names.get(slug_of(xls_name))220        if name is None:221            log.warning("circonscription XLS non appariée: %s", xls_name)222            continue223        cv = lambda k: val(XLS_CODES[k], col)224        pop = cv("population")225        apps = sum((cv(k2) or 0.0)226                   for k2 in ("n_app_duplex", "n_app_moins5", "n_app_5plus"))227        log_tot = cv("n_logements_occ") or 1.0228        imm_tot = cv("n_immigration_total") or 1.0229        uni_den = val(uni_denom_row, col) or 1.0230        pct = lambda x: round(x * 100, 1) if x is not None else None231        rows.append({232            "district": name, "population": pop,233            "pct_60_plus": pct(cv("pct_65_plus")),   # 65+ (libellé officiel)234            "pct_francais": pct(cv("pct_fr_maison")),235            "pct_anglais": pct(cv("pct_en_maison")),236            "pct_universitaire": round((cv("n_uni_bacc_plus") or 0)237                                       / uni_den * 100, 1),238            "pct_revenu_100k_plus": pct(cv("pct_revenu_100k_plus")),239            "pct_immigrants": round((cv("n_immigrants") or 0)240                                    / imm_tot * 100, 1),241            "pct_autochtone": pct(cv("pct_autochtone")),242            "pct_locataires": pct(cv("pct_locataires")),243            "pct_appartements": round(apps / log_tot * 100, 1),244            "pct_minorites_visibles": pct(val(minor_row, col)),245            "source_url": XLS_URL,246            "source": ("Élections Québec — Statistiques du Recensement 2021 "247                       "par circonscription 2026 (classeur officiel)"),248            "harvested": date.today().isoformat(),249        })250    (POP_DIR / "portraits").mkdir(parents=True, exist_ok=True)251    for r in rows:252        (POP_DIR / "portraits" / f"{slug_of(r['district'])}.json").write_text(253            json.dumps(r, ensure_ascii=False))254    out = build_feature_store(db)255    return {"circonscriptions_xls": len(rows), **out}256257258def harvest(db: Session, max_workers: int = 4,259            refresh: bool = False) -> dict:260    """Moissonne les 127 portraits (idempotent : saute les JSON déjà présents)."""261    from .firecrawl_watch import _enabled, _post262    if not _enabled():263        return {"skipped": "Firecrawl requis"}264    (POP_DIR / "portraits").mkdir(parents=True, exist_ok=True)265    names = [d.name for d in db.query(Mo.District).all()]266267    def one(name: str) -> tuple[str, str]:268        slug = slug_of(name)269        f = POP_DIR / "portraits" / f"{slug}.json"270        if f.exists() and not refresh:271            return name, "cache"272        data = _post("/scrape", {"url": PORTRAIT_URL.format(slug=slug),273                                 "formats": ["markdown"]}, timeout=90.0)274        md = ((data or {}).get("data") or {}).get("markdown") or ""275        parsed = parse_portrait(md, name) if md else None276        if parsed is None:277            return name, "échec"278        parsed["markdown"] = md[:60000]   # conservé pour re-parse sans réseau279        parsed["district"] = name280        parsed["source_url"] = PORTRAIT_URL.format(slug=slug)281        parsed["source"] = ("Élections Québec — portrait socioéconomique "282                            "(Recensement 2021, limites 2026)")283        parsed["harvested"] = date.today().isoformat()284        f.write_text(json.dumps(parsed, ensure_ascii=False))285        return name, "ok"286287    results: dict[str, int] = {"ok": 0, "cache": 0, "échec": 0}288    failed = []289    with ThreadPoolExecutor(max_workers=max_workers) as ex:290        for name, status in ex.map(one, names):291            results[status] += 1292            if status == "échec":293                failed.append(name)294    build_feature_store(db)295    return {**results, "échecs": failed[:10]}296297298def build_feature_store(db: Session) -> dict:299    """Portraits JSON → CSV standardisé + graphe de similarité (top 10)."""300    import pandas as pd301    rows = []302    for f in sorted((POP_DIR / "portraits").glob("*.json")):303        d = json.loads(f.read_text())304        rows.append({k: d.get(k) for k in305                     ["district", "population", *Z_FEATURES,306                      "pct_minorites_visibles", "source_url", "harvested"]})307    if len(rows) < 60:308        return {"skipped": f"seulement {len(rows)} portraits"}309    df = pd.DataFrame(rows)310    for c in Z_FEATURES:311        col = pd.to_numeric(df[c], errors="coerce")312        mu, sd = col.mean(), col.std() or 1.0313        df[f"z_{c}"] = ((col - mu) / sd).round(3)314    POP_DIR.mkdir(parents=True, exist_ok=True)315    df.to_csv(POP_DIR / "riding_2026_demographics.csv", index=False)316    # similarité cosinus sur les z-features (valeurs manquantes → 0)317    Z = df[[f"z_{c}" for c in Z_FEATURES]].fillna(0.0).to_numpy()318    norms = np.linalg.norm(Z, axis=1, keepdims=True)319    norms[norms == 0] = 1.0320    S = (Z / norms) @ (Z / norms).T321    sim = {}322    names = df["district"].tolist()323    for i, n in enumerate(names):324        order = np.argsort(-S[i])325        sim[n] = [{"district": names[j], "similarity": round(float(S[i, j]), 3)}326                  for j in order[1:11]]327    (POP_DIR / "riding_similarity.json").write_text(328        json.dumps(sim, ensure_ascii=False))329    return {"circonscriptions": len(df)}330