🏁 Projet Final — Niveau Intermédiaire : La bataille du signal — reconquérir le marché télécom nigérian, État par État

Téléchargement (bash) :

bash# Le NBS publie le classeur Q4 2023 en accès libre
curl -L -o telecoms_nbs.xlsx \
  "https://nigerianstat.gov.ng/resource/Telecoms_Q4_2023.xlsx"

Chargement et inspection de la structure (Python) :

pythonimport pandas as pd

CHEMIN = "telecoms_nbs.xlsx"

# 1) Lister les onglets sans tout charger en mémoire : ExcelFile ouvre le classeur
#    et expose juste la liste des feuilles.
xls = pd.ExcelFile(CHEMIN)

print(f"Nombre d'onglets : {len(xls.sheet_names)}")
for nom in xls.sheet_names:
    print(repr(nom))   # repr() pour VOIR les espaces cachés en début de nom

⚠️ Deux pièges de nommage à repérer dès maintenant (ils te mordront à l'étape 2) :

• Il existe un onglet " Operators" avec un espace au début — d'où le repr(), qui te rend l'espace visible (' Operators') au lieu de te le cacher.
• Le nommage des onglets trimestriels n'est pas homogène entre les années : les feuilles de 2021 suivent un ordre ("Q2 Voice 2021"), celles de 2022-2023 un autre ("Q1 2022 Voice"). Tu ne pourras donc pas construire le nom d'un onglet avec une seule règle de formatage ; garde ça en tête pour la boucle de chargement de l'étape 2.

python# 2) Charger UN onglet Voice en brut, sans interpréter d'en-tête.
#    header=None => pandas ne devine rien, tu vois les lignes telles quelles.
brut = pd.read_excel(CHEMIN, sheet_name="Q4 2023 Voice", header=None)

print("Dimensions (lignes, colonnes) :", brut.shape)

# Regarder le haut du fichier pour localiser en-têtes et début des données
print(brut.head(9))

Comment lire ce que tu obtiens (sans nettoyer encore) :

• Les 4 premières lignes sont des titres parasites (ligne 3 = un libellé du genre "ACTIVE VOICE SUBSCRIPTIONS…"), pas des données.
• Le vrai en-tête est éclaté sur plusieurs lignes (lignes 5 à 7) ; c'est la ligne 7 qui porte les noms d'opérateurs (MTN, GLO, AIRTEL, EMTS…).
• Les données commencent à la ligne 8 : colonne A = numéro d'ordre, colonne B = l'État en MAJUSCULES, colonnes suivantes = le nombre d'abonnés par opérateur.
• Le format est large (un État par ligne, les opérateurs en colonnes) et il y a des colonnes quasi vides + une ligne parasite OTHERS (UNDEFINED).

Ne corrige rien ici : le but de l'étape 1 est juste de confirmer que le fichier se télécharge, s'ouvre, et de cartographier sa structure. Tout le dépivotage/nettoyage (passer en format long état / opérateur / type / trimestre / nb_abonnes, écarter les colonnes vides et la ligne OTHERS) arrive à l'étape 2.

pythonimport io
import requests
import pandas as pd

# --- Réflexe M19 n°1 : HDX refuse (403) les requêtes "sans navigateur".
#     On envoie un User-Agent de navigateur sur TOUS les appels vers HDX.
HEADERS = {
    "User-Agent": (
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
        "AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0 Safari/537.36"
    )
}

API_URL = "https://data.humdata.org/api/3/action/package_show?id=cod-ps-nga"

# 1) Appel de l'API CKAN
reponse = requests.get(API_URL, headers=HEADERS, timeout=60)

# --- Réflexe M19 n°2 : ne PAS se contenter du statut HTTP.
#     On lève une erreur explicite si le code n'est pas 2xx...
reponse.raise_for_status()

#     ...PUIS on vérifie que le contenu est cohérent (CKAN renvoie toujours
#     un JSON avec une clé booléenne "success"). Si "success" est False,
#     l'API a répondu 200 mais n'a rien trouvé : on s'arrête net.
data = reponse.json()
if not data.get("success"):
    raise RuntimeError("L'API CKAN a répondu, mais success=False (paquet introuvable ?)")

# 2) Parser le JSON : la liste des fichiers est sous result -> resources
ressources = data["result"]["resources"]

# On cherche LE csv admin-1 (un État = un niveau admin-1).
# On filtre sur le format ET sur "adm1" dans le nom, sans coder l'URL en dur.
csv_adm1 = next(
    res for res in ressources
    if res.get("format", "").upper() == "CSV"
    and "adm1" in res.get("name", "").lower()
)

url_csv = csv_adm1["download_url"]   # l'URL est DÉCOUVERTE, pas devinée
print("Fichier repéré :", csv_adm1["name"])

# 3) Télécharger le CSV (toujours avec le User-Agent navigateur)
tele = requests.get(url_csv, headers=HEADERS, timeout=60)
tele.raise_for_status()

# Re-réflexe M19 n°2 : un serveur peut renvoyer 200 + une page HTML d'erreur.
# Avant de parser en CSV, on s'assure que ça ne commence pas par du HTML.
debut = tele.text.lstrip().lower()
if debut.startswith("<!doctype") or debut.startswith("<html"):
    raise RuntimeError("Réponse 200 mais contenu HTML : ce n'est pas le CSV attendu.")

# io.StringIO permet de lire le texte téléchargé comme s'il s'agissait d'un fichier
pop = pd.read_csv(io.StringIO(tele.text))

# 4) Vérifier que les colonnes dont on aura besoin sont bien présentes
colonnes_cles = ["ADM1_EN", "ADM1_PCODE", "T_TL"]
manquantes = [c for c in colonnes_cles if c not in pop.columns]
if manquantes:
    raise KeyError(f"Colonnes attendues absentes : {manquantes}")

print("Colonnes utiles présentes ✔  —  Dimensions :", pop.shape)
print(pop[colonnes_cles].head())

Comment lire ce que tu obtiens :

• ADM1_EN = le nom de l'État (celui qui servira de clé de jointure avec le télécom à l'étape 3), ADM1_PCODE = son code officiel (NG001…NG037), T_TL = la population totale.
• Tu dois retrouver une ligne par État — c'est le signe que tu as bien attrapé le fichier admin-1 (et pas un niveau plus fin ou le total national).
• Si l'un des deux raise se déclenche : soit ton User-Agent manque (403 → réflexe n°1), soit tu as reçu une page d'erreur déguisée en 200 (réflexe n°2). Dans les deux cas, le problème n'est pas ton parsing mais la récupération — c'est précisément le genre de panne que le M19 t'a appris à diagnostiquer avant de blâmer ton code.

🔗 Lien avec le M19 : cet appel package_show + parcours de result.resources est un cas d'école du module « APIs & JSON ». On n'a pas codé le lien du CSV en dur : on interroge l'API, on navigue dans le JSON pour retrouver la ressource par son nom, et on ne fait confiance ni au statut HTTP seul ni au format supposé — on vérifie le contenu. C'est exactement le pipeline robuste que tu voudras en production.

Première réaction, la plus naturelle : lire brut, sans en-tête, et regarder ce qu'il y a dedans.

pythonimport pandas as pd

FICHIER = "telecoms_nbs.xlsx"

# On lit SANS en-tête pour voir la structure réelle
brut = pd.read_excel(FICHIER, sheet_name="Q4 2023 Voice", header=None)
print(brut.head(10))   # observe : lignes de titre, puis 'MTN','GLO'... puis 'ABIA'...

Tu vois alors le schéma : quelques lignes de titre, une ligne avec MTN, GLO, AIRTEL, EMTS, puis les États à partir de ABIA. Tentation : coder skiprows=6, header=[...]. Ne fais pas ça. Si tu inspectes plusieurs onglets, tu découvres que la ligne de l'en-tête change de position d'un trimestre à l'autre (parfois ligne 3, parfois 5, parfois 7), et que sur les onglets Internet de 2023 il y a même une colonne vide en plus au début qui décale tout d'un cran. Un skiprows en dur casserait sur la moitié des onglets.

La bonne approche d'un DA : ne pas coder de position en dur, mais faire repérer les repères par le code. On cherche la ligne où apparaissent MTN ET GLO (c'est la ligne des opérateurs), et la colonne où apparaissent ABIA … ZAMFARA (c'est la colonne des États).

pythonOPERATEURS = ["MTN", "GLO", "AIRTEL", "EMTS"]

def charger_onglet(fichier, onglet):
    """Charge un onglet Voice/Internet en retrouvant seul l'en-tête et la colonne États.
    Renvoie un DataFrame large [etat, MTN, GLO, AIRTEL, EMTS], ou None si l'onglet
    ne contient pas la matrice État × opérateur."""
    brut = pd.read_excel(fichier, sheet_name=onglet, header=None)

    # 1) Ligne des opérateurs = la première où l'on trouve à la fois MTN et GLO
    ligne_op = None
    for r in range(min(15, len(brut))):
        cellules = [str(v).strip().upper() for v in brut.iloc[r].tolist()]
        if "MTN" in cellules and "GLO" in cellules:
            ligne_op = r
            break
    if ligne_op is None:
        return None  # cet onglet n'a pas de découpage par opérateur (on y revient en 2.D)

    entetes = [str(v).strip().upper() for v in brut.iloc[ligne_op].tolist()]
    col_op = {op: entetes.index(op) for op in OPERATEURS if op in entetes}
    if len(col_op) < 4:
        return None  # les 4 opérateurs GSM ne sont pas tous là

    # 2) Colonne des États = celle qui contient ABIA … ZAMFARA
    col_etat = None
    for c in range(min(6, brut.shape[1])):
        valeurs = [str(v).strip().upper() for v in brut.iloc[:, c].tolist()]
        if "ABIA" in valeurs and "ZAMFARA" in valeurs:
            col_etat = c
            break

    # 3) Première ligne de données = celle où la colonne États vaut ABIA
    col_valeurs = [str(v).strip().upper() for v in brut.iloc[:, col_etat].tolist()]
    debut = col_valeurs.index("ABIA")

    df = brut.iloc[debut:, [col_etat] + [col_op[op] for op in OPERATEURS]].copy()
    df.columns = ["etat"] + OPERATEURS
    return df

voice_q4_2023 = charger_onglet(FICHIER, "Q4 2023 Voice")
print(voice_q4_2023.head())

Pourquoi c'est mieux qu'un skiprows en dur : ce chargeur s'adapte tout seul aux décalages du NBS. La même fonction va marcher sur les 20 onglets Voice/Internet sans que tu retouches un seul numéro de ligne. C'est le réflexe à prendre : quand une source est incohérente, on écrit du code qui cherche les ancres au lieu de supposer des positions fixes.

En pandas, l'outil du dépivotage, c'est .melt(). On garde etat comme colonne d'identification (id_vars), et on « fond » les 4 colonnes-opérateurs en deux nouvelles colonnes : une pour le nom (operateur), une pour la valeur (nb_abonnes).

pythonlong = voice_q4_2023.melt(
    id_vars="etat",                 # ce qui reste tel quel, ligne par ligne
    value_vars=OPERATEURS,          # les colonnes à dépivoter
    var_name="operateur",           # nom de la nouvelle colonne "clé"
    value_name="nb_abonnes",        # nom de la nouvelle colonne "valeur"
)
print(long.head())
print(long.shape)   # environ 4 fois plus de lignes que le format large

Retiens le principe : melt prend N colonnes et les empile en 2 colonnes (nom, valeur). C'est l'inverse d'un pivot_table. Chaque État apparaît maintenant 4 fois (une par opérateur), et tu as bien « une observation par ligne ». C'est ce format-là, et pas l'autre, qui ira dans PostgreSQL.

Le piège du débutant, c'est de blacklister les saletés une par une (« si c'est TOTAL, jette ; si c'est OTHERS, jette… »). Fragile : il y aura toujours une saleté que tu n'auras pas prévue. La bonne méthode, c'est l'inverse : on se donne le référentiel des 37 États attendus, on corrige les variantes connues, et on ne garde que ce qui est dans le référentiel. Tout ce qui n'y est pas (TOTAL, OTHERS, ISP, lignes vides…) tombe automatiquement.

pythonimport re

# Référentiel : les 37 États du Nigéria en forme canonique (MAJUSCULES),
# avec FCT déjà écrit comme dans le fichier population, pour préparer la jointure.
ETATS_37 = {
    "ABIA","ADAMAWA","AKWA IBOM","ANAMBRA","BAUCHI","BAYELSA","BENUE","BORNO",
    "CROSS RIVER","DELTA","EBONYI","EDO","EKITI","ENUGU","FEDERAL CAPITAL TERRITORY",
    "GOMBE","IMO","JIGAWA","KADUNA","KANO","KATSINA","KEBBI","KOGI","KWARA","LAGOS",
    "NASARAWA","NIGER","OGUN","ONDO","OSUN","OYO","PLATEAU","RIVERS","SOKOTO",
    "TARABA","YOBE","ZAMFARA",
}

# Table des variantes/coquilles observées -> forme canonique
CORRECTIONS = {
    "AKWA I BOM": "AKWA IBOM",
    "NASSARAWA":  "NASARAWA",           # deux S -> un seul S
    "1IGAWA":     "JIGAWA",             # coquille OCR
    "PIATEAU":    "PLATEAU",            # coquille OCR
    "RIVERS >":   "RIVERS",             # caractère parasite
    "FCT":        "FEDERAL CAPITAL TERRITORY",  # recodage pour la jointure population
}

def canon_etat(valeur):
    """Passe un nom d'État en forme canonique : majuscules, espaces normalisés,
    retours à la ligne supprimés, variantes/coquilles corrigées."""
    s = str(valeur).replace("\n", " ")
    s = re.sub(r"\s+", " ", s).strip().upper()   # 'CROSS\nRIVER' -> 'CROSS RIVER'
    return CORRECTIONS.get(s, s)

def nettoyer(df_long):
    df = df_long.copy()

    # (a) standardiser les noms d'États
    df["etat"] = df["etat"].map(canon_etat)

    # (b) ne garder que les 37 vrais États -> jette TOTAL, OTHERS, ISP, vides...
    df = df[df["etat"].isin(ETATS_37)]

    # (e) forcer le type numérique ; ce qui n'est pas convertible devient NaN puis tombe
    df["nb_abonnes"] = pd.to_numeric(df["nb_abonnes"], errors="coerce")
    df = df.dropna(subset=["nb_abonnes"])

    # (d) EMTS -> 9mobile (nom parlant pour le comité de direction)
    df["operateur"] = df["operateur"].replace({"EMTS": "9mobile"})

    return df

long_propre = nettoyer(long)

# Contrôle qualité : on DOIT retomber sur exactement 37 États et 4 opérateurs
print("États distincts :", long_propre["etat"].nunique())      # doit valoir 37
print("Opérateurs :", sorted(long_propre["operateur"].unique()))
print("Valeurs manquantes :", long_propre["nb_abonnes"].isna().sum())

La leçon : filtrer par un référentiel (garder ce qui est valide) plutôt que par une liste noire (jeter ce qui est invalide). Le premier est robuste — une nouvelle saleté inconnue est éliminée d'office ; le second te lâche au premier cas imprévu. Et toujours finir par un contrôle (.nunique()) : si tu n'obtiens pas 37, c'est qu'une variante d'orthographe t'a échappé et n'est pas dans ta table CORRECTIONS.

Pour parser le nom de l'onglet sans dépendre de l'ordre des mots, on utilise des expressions régulières : on cherche le motif Q1…Q4 où qu'il soit, l'année 20xx où qu'elle soit, et le mot Voice/Internet. Ça marche pour Q2 Voice 2021 comme pour Q1 2022 Voice.

pythonimport re

def parser_onglet(nom):
    """Extrait (type_service, trimestre) du nom d'onglet, quelle que soit la convention.
    'Q2 Voice 2021'  -> ('Voice',    '2021 Q2')
    'Q1 2022 Internet'-> ('Internet', '2022 Q1')"""
    n = nom.strip()
    type_service = "Voice" if "voice" in n.lower() else "Internet"
    trimestre_q = re.search(r"Q[1-4]", n).group(0)     # Q1..Q4, où qu'il soit
    annee       = re.search(r"20\d{2}", n).group(0)    # 20xx, où qu'elle soit
    return type_service, f"{annee} {trimestre_q}"      # '2021 Q2' : triable chronologiquement

On note au passage : en écrivant le trimestre 2021 Q2 (année d'abord), un simple tri alphabétique donne l'ordre chronologique. Petit détail, gros confort pour la suite.

Ensuite, la boucle. On liste les onglets, on écarte Porting et Operators, et pour chaque onglet Voice/Internet on enchaîne chargement → dépivot → nettoyage. Si le chargeur renvoie None (onglet non exploitable), on le note et on passe.

pythonxl = pd.ExcelFile(FICHIER)

# On ne garde que Voice/Internet ; on jette Porting et l'onglet ' Operators' (espace initial !)
onglets = [
    s for s in xl.sheet_names
    if "Porting" not in s and s.strip() != "Operators"
]

morceaux = []
onglets_ignores = []

for onglet in onglets:
    df_large = charger_onglet(FICHIER, onglet)   # fonction de 2.A
    if df_large is None:
        onglets_ignores.append(onglet)           # pas de matrice État × opérateur -> on saute
        continue

    type_service, trimestre = parser_onglet(onglet)

    long = df_large.melt(                         # dépivot (2.B)
        id_vars="etat", value_vars=OPERATEURS,
        var_name="operateur", value_name="nb_abonnes",
    )
    long = nettoyer(long)                         # nettoyage (2.C)
    long["type_service"] = type_service
    long["trimestre"]    = trimestre
    morceaux.append(long)

# On empile tous les trimestres/types en UNE table longue
abonnements = pd.concat(morceaux, ignore_index=True)
abonnements = abonnements[["etat", "operateur", "type_service", "trimestre", "nb_abonnes"]]

print("Table finale :", abonnements.shape)
print("Onglets ignorés :", onglets_ignores)     # regarde ce qui a été écarté, et pose-toi la question !

Le point à ne pas manquer : regarde la liste onglets_ignores. Tu vas constater qu'un trimestre entier a été écarté — non pas à cause d'un bug, mais parce que, pour ce trimestre-là, le NBS n'a pas publié le détail par État et par opérateur (le tableau principal ne contient que des totaux par État). Ton code a eu raison de le sauter automatiquement plutôt que de fabriquer des chiffres faux. C'est une décision d'analyse, pas juste de la technique : tu dois en être conscient et le mentionner dans tes livrables (« la période X est absente du découpage par opérateur, source NBS »). Un DA qui masque un trou dans les données au lieu de le documenter, c'est un DA en qui on ne peut pas avoir confiance.

Dernier réflexe, le contrôle de cohérence avant de passer à la suite :

python# Chaque (trimestre, type, opérateur) doit couvrir exactement 37 États
controle = abonnements.groupby(["trimestre", "type_service", "operateur"])["etat"].nunique()
print("États par groupe -> min :", controle.min(), "| max :", controle.max())  # doit être 37 / 37

# Aucune ligne dupliquée sur la clé métier
cle = ["etat", "operateur", "type_service", "trimestre"]
print("Doublons sur la clé :", abonnements.duplicated(cle).sum())              # doit être 0

Si min et max valent tous les deux 37 et qu'il n'y a aucun doublon, ta table est saine.

rlibrary(readxl)
library(tidyr)
library(dplyr)
library(stringr)

fichier <- "telecoms_nbs.xlsx"
operateurs <- c("MTN", "GLO", "AIRTEL", "EMTS")

etats_37 <- c(
  "ABIA","ADAMAWA","AKWA IBOM","ANAMBRA","BAUCHI","BAYELSA","BENUE","BORNO",
  "CROSS RIVER","DELTA","EBONYI","EDO","EKITI","ENUGU","FEDERAL CAPITAL TERRITORY",
  "GOMBE","IMO","JIGAWA","KADUNA","KANO","KATSINA","KEBBI","KOGI","KWARA","LAGOS",
  "NASARAWA","NIGER","OGUN","ONDO","OSUN","OYO","PLATEAU","RIVERS","SOKOTO",
  "TARABA","YOBE","ZAMFARA"
)

# Lecture d'un onglet : on saute les lignes de titre et on nomme nous-mêmes les colonnes.
# (En R aussi, adapte skip/col_names après avoir INSPECTÉ l'onglet.)
voice <- read_excel(
  fichier, sheet = "Q4 2023 Voice",
  skip = 7, col_names = FALSE            # à ajuster selon l'onglet observé
) |>
  select(etat = 2, MTN = 3, GLO = 4, AIRTEL = 5, EMTS = 6)

# Standardisation des noms d'États (majuscules, espaces, variantes/coquilles)
canon_etat <- function(x) {
  x <- str_replace_all(x, "\\s+", " ")
  x <- str_to_upper(str_trim(x))
  recode(x,
    "AKWA I BOM" = "AKWA IBOM",
    "NASSARAWA"  = "NASARAWA",
    "1IGAWA"     = "JIGAWA",
    "PIATEAU"    = "PLATEAU",
    "RIVERS >"   = "RIVERS",
    "FCT"        = "FEDERAL CAPITAL TERRITORY",
    .default = x
  )
}

long_propre <- voice |>
  # DÉPIVOTAGE : équivalent exact du melt() de pandas
  pivot_longer(
    cols      = all_of(operateurs),
    names_to  = "operateur",
    values_to = "nb_abonnes"
  ) |>
  mutate(
    etat       = canon_etat(etat),
    nb_abonnes = as.numeric(nb_abonnes),
    operateur  = recode(operateur, "EMTS" = "9mobile")
  ) |>
  filter(etat %in% etats_37) |>          # garder ce qui est valide (référentiel)
  filter(!is.na(nb_abonnes))

# Contrôle : doit valoir 37
n_distinct(long_propre$etat)

À retenir : pivot_longer(cols, names_to, values_to) en R ≡ melt(value_vars, var_name, value_name) en pandas. Même geste, même résultat. Que tu sois en Python ou en R, la démarche de nettoyage est la même : inspecter, standardiser contre un référentiel, dépivoter, contrôler.

pythonimport os
from sqlalchemy import create_engine, text

# La chaîne de connexion vit dans une variable d'environnement, JAMAIS en dur
# dans le notebook (sinon elle finit sur GitHub...). Exporte-la avant de lancer
# Jupyter, ou charge-la depuis un .env avec python-dotenv.
#   export DATABASE_URL="postgresql://user:password@host/dbname?sslmode=require"
DATABASE_URL = os.environ["DATABASE_URL"]

# SQLAlchemy : c'est ce moteur que pandas utilisera pour to_sql / read_sql.
engine = create_engine(DATABASE_URL)

# Test de vie : si ça ne lève pas, la connexion est bonne.
with engine.connect() as conn:
    conn.execute(text("SELECT 1"))
print("Connexion Neon OK ✅")

Deux petits points de vigilance Neon :

• sslmode=require est obligatoire, Neon refuse les connexions non chiffrées. Garde-le dans l'URL.
• SQLAlchemy accepte l'URL préfixée postgresql://. Si tu veux forcer le driver psycopg2, écris postgresql+psycopg2://…, c'est le driver par défaut de toute façon.

Si à un moment tu veux la connexion psycopg2 brute (pour un COPY ou un executemany), tu l'ouvres à part avec la même URL :

pythonimport psycopg2

# Connexion bas niveau, utile pour COPY et pour piloter les transactions à la main.
raw_conn = psycopg2.connect(DATABASE_URL)
raw_conn.autocommit = False  # on validera nous-mêmes avec commit()

Le SQL de création (exécuté via psycopg2, ou via engine — au choix) :

sqlDROP TABLE IF EXISTS etats CASCADE;  -- CASCADE : on lâche aussi la FK des faits si elle existe

CREATE TABLE etats (
    etat       TEXT PRIMARY KEY,   -- nom d'État en MAJUSCULES = clé de jointure
    pcode      TEXT,               -- code officiel HDX (NG001 … NG037)
    population BIGINT,             -- population totale (T_TL) — BIGINT par prudence
    zone       TEXT                -- une des 6 zones géopolitiques
);

La construction du DataFrame en Python :

pythonimport pandas as pd

# --- Le mapping des 37 États vers les 6 zones géopolitiques (contrat de données) ---
zones = {
    "North Central": ["Niger", "Kogi", "Benue", "Plateau", "Nasarawa", "Kwara",
                      "Federal Capital Territory"],
    "North East":    ["Bauchi", "Borno", "Taraba", "Adamawa", "Gombe", "Yobe"],
    "North West":    ["Zamfara", "Sokoto", "Kaduna", "Kebbi", "Katsina", "Kano", "Jigawa"],
    "South East":    ["Enugu", "Imo", "Ebonyi", "Abia", "Anambra"],
    "South South":   ["Bayelsa", "Akwa Ibom", "Edo", "Rivers", "Cross River", "Delta"],
    "South West":    ["Oyo", "Ekiti", "Osun", "Ondo", "Lagos", "Ogun"],
}
# On aplatit en dict {ÉTAT_EN_MAJUSCULES: zone} pour un .map() direct.
etat_vers_zone = {
    etat.upper(): zone
    for zone, etats in zones.items()
    for etat in etats
}

# --- Le DataFrame de dimension à partir du CSV HDX ---
pop = pd.read_csv("pop_hdx.csv")   # adapte le chemin

dim_etats = pop[["ADM1_EN", "ADM1_PCODE", "T_TL"]].copy()
dim_etats.columns = ["etat", "pcode", "population"]

# La clé de jointure : nom d'État nettoyé et EN MAJUSCULES, partout pareil.
dim_etats["etat"] = dim_etats["etat"].str.strip().str.upper()

# On rattache la zone. Si un État ne trouve pas de zone -> NaN, ça se verra tout de suite.
dim_etats["zone"] = dim_etats["etat"].map(etat_vers_zone)

# Garde-fou : aucun État ne doit rester sans zone.
assert dim_etats["zone"].notna().all(), "Un État n'a pas de zone : vérifie le mapping !"

Le chargement dans Postgres avec to_sql. On a déjà créé la table (avec sa PK) juste au-dessus, donc on charge en if_exists="append" pour ne pas écraser notre schéma :

pythondim_etats.to_sql(
    "etats",
    engine,
    if_exists="append",   # la table + sa PK existent déjà, on ne fait qu'insérer
    index=False,
)
print(f"{len(dim_etats)} États chargés dans la dimension.")

💡 Pourquoi append et pas if_exists="replace" ? Parce que replace détruit et recrée la table à partir des dtypes pandas — tu perdrais ta PRIMARY KEY et tes types choisis (BIGINT). On garde toujours la main sur le schéma avec un CREATE TABLE explicite, et pandas ne fait qu'insérer les lignes.

Le SQL de création, avec la clé étrangère :

sqlDROP TABLE IF EXISTS abonnements;

CREATE TABLE abonnements (
    etat         TEXT NOT NULL,
    operateur    TEXT NOT NULL,
    type_service TEXT NOT NULL,   -- 'Voice' ou 'Internet'
    trimestre    TEXT NOT NULL,   -- ex. '2023 Q4' (année d'abord : triable chronologiquement)
    nb_abonnes   BIGINT,
    -- La FK : tout etat inséré ici DOIT exister dans la dimension.
    -- Si un nom d'État n'est pas harmonisé, l'INSERT échouera : c'est voulu.
    CONSTRAINT fk_etat FOREIGN KEY (etat) REFERENCES etats (etat)
);

🔒 La contrainte fk_etat est ton filet de sécurité. Si ton DataFrame de faits contient encore FCT (que la dimension ne connaît pas sous ce nom), Postgres refusera l'insertion. Donc on harmonise le nom d'État avant de charger — c'est exactement le recodage annoncé dans le contrat de données.

python# Rappel : dans le CSV télécom, FCT s'écrit "FCT" ; dans HDX (donc dans la dimension),
# c'est "FEDERAL CAPITAL TERRITORY". On aligne les faits sur la dimension AVANT de charger.
faits = faits_long.copy()   # ton DataFrame long de l'Étape 2
faits["etat"] = faits["etat"].str.strip().str.upper()
faits["etat"] = faits["etat"].replace({"FCT": "FEDERAL CAPITAL TERRITORY"})

# On écarte aussi la ligne parasite "OTHERS (UNDEFINED)" si elle traîne encore.
faits = faits[faits["etat"] != "OTHERS (UNDEFINED)"]

Option 1 — chargement simple avec to_sql (parfait pour ce volume) :

pythonfaits.to_sql(
    "abonnements",
    engine,
    if_exists="append",   # la table + la FK existent déjà
    index=False,
    chunksize=5000,       # insère par paquets, plus sûr sur une connexion distante
    method="multi",       # regroupe les lignes par INSERT, nettement plus rapide
)
print(f"{len(faits)} faits chargés.")

Option 2 — chargement par COPY via psycopg2, l'arme lourde quand le volume grimpe (COPY court-circuite le parsing ligne à ligne de INSERT) :

pythonimport io

# On sérialise le DataFrame en CSV en mémoire (pas de fichier sur disque).
buffer = io.StringIO()
faits.to_csv(buffer, index=False, header=False)
buffer.seek(0)

with raw_conn.cursor() as cur:
    cur.copy_expert(
        """
        COPY abonnements (etat, operateur, type_service, trimestre, nb_abonnes)
        FROM STDIN WITH (FORMAT csv)
        """,
        buffer,
    )
raw_conn.commit()   # on valide la transaction
print("COPY terminé.")

⚡ to_sql(method="multi") te suffit largement ici. Retiens le COPY pour le jour où tu chargeras des millions de lignes : c'est l'ordre de chargement le plus rapide de PostgreSQL.

L'idée : un LEFT JOIN des faits vers la dimension, puis on ne garde que les lignes où le côté dimension est NULL (= pas de correspondance).

sqlSELECT COUNT(DISTINCT f.etat) AS etats_non_apparies
FROM abonnements AS f
LEFT JOIN etats AS d
       ON f.etat = d.etat
WHERE d.etat IS NULL;

Et si tu veux voir lesquels posent problème (pour les corriger) plutôt que juste les compter :

sqlSELECT DISTINCT f.etat AS etat_orphelin
FROM abonnements AS f
LEFT JOIN etats AS d
       ON f.etat = d.etat
WHERE d.etat IS NULL
ORDER BY f.etat;

Depuis Python, tu la lances et tu lis le compte :

pythoncontrole = pd.read_sql(
    """
    SELECT COUNT(DISTINCT f.etat) AS etats_non_apparies
    FROM abonnements AS f
    LEFT JOIN etats AS d ON f.etat = d.etat
    WHERE d.etat IS NULL;
    """,
    engine,
)
print(controle)

Règle d'interprétation :

• Si etats_non_apparies = 0 : parfait. Chaque ligne de faits se raccroche à un État de la dimension, aucun abonné ne se perdra dans les jointures analytiques. Ton harmonisation des noms est complète.
• Si etats_non_apparies > 0 : il reste au moins un nom d'État à harmoniser. Lance la seconde requête (celle qui liste les etat_orphelin) pour voir lesquels, puis ajoute le recodage manquant dans le replace({...}) de l'étape C avant de recharger. Le suspect classique dans ce projet : FCT qui n'a pas été renommé en FEDERAL CAPITAL TERRITORY.

🎯 En pratique, comme la contrainte FOREIGN KEY de l'étape C bloque déjà l'insertion de tout État inconnu, cette requête devrait renvoyer 0 dès que le chargement a réussi. Elle reste ta preuve — le contrôle qualité que tu montres au comité pour dire "aucun abonné n'a été perdu au passage en base". C'est ce réflexe (charger puis vérifier l'intégrité) qui sépare un pipeline de production d'un script jetable.

sqlWITH dernier AS (
    -- On isole le trimestre le plus récent, une fois pour toutes
    SELECT MAX(trimestre) AS trimestre
    FROM abonnements
),
base AS (
    -- Abonnés voix par État et par opérateur, sur ce dernier trimestre
    SELECT
        etat,
        operateur,
        SUM(nb_abonnes) AS abonnes
    FROM abonnements
    WHERE type_service = 'Voice'
      AND trimestre = (SELECT trimestre FROM dernier)
    GROUP BY etat, operateur
)
SELECT
    etat,
    operateur,
    abonnes,
    -- Le total de l'État : la fenêtre recalcule la somme pour chaque groupe d'État,
    -- mais SANS écraser le détail par opérateur (contrairement à un GROUP BY)
    SUM(abonnes) OVER (PARTITION BY etat) AS total_etat,
    ROUND(
        100.0 * abonnes / SUM(abonnes) OVER (PARTITION BY etat),
        2
    ) AS part_marche_pct
FROM base
ORDER BY etat, part_marche_pct DESC;

Comment lire le résultat. Pour chaque État, les quatre parts s'additionnent à 100 %. Repère la ligne de notre opérateur : plus part_marche_pct est basse, plus on est faible sur cet État. À l'inverse, un part_marche_pct très élevé sur la ligne MTN signale une place forte adverse. Ne conclus rien sur un seul État isolé : c'est la comparaison entre États qui parle (tâches B et E).

sqlWITH dernier AS (
    SELECT MAX(trimestre) AS trimestre FROM abonnements
),
base AS (
    SELECT
        etat,
        operateur,
        SUM(nb_abonnes) AS abonnes
    FROM abonnements
    WHERE type_service = 'Voice'
      AND trimestre = (SELECT trimestre FROM dernier)
    GROUP BY etat, operateur
)
SELECT
    etat,
    operateur,
    abonnes,
    RANK() OVER (PARTITION BY etat ORDER BY abonnes DESC) AS rang
FROM base
ORDER BY etat, rang;

-- Variante ciblée : seulement les États où NOTRE opérateur est n°1
-- (on enveloppe la requête classée dans une CTE pour pouvoir filtrer sur le rang)
WITH dernier AS (
    SELECT MAX(trimestre) AS trimestre FROM abonnements
),
classement AS (
    SELECT
        etat,
        operateur,
        SUM(nb_abonnes) AS abonnes,
        RANK() OVER (
            PARTITION BY etat
            ORDER BY SUM(nb_abonnes) DESC
        ) AS rang
    FROM abonnements
    WHERE type_service = 'Voice'
      AND trimestre = (SELECT trimestre FROM dernier)
    GROUP BY etat, operateur
)
SELECT etat, operateur, abonnes, rang
FROM classement
WHERE operateur = 'GLO'      -- notre opérateur
ORDER BY rang, etat;

💡 On ne peut pas mettre RANK() dans un WHERE directement (la fonction fenêtre est calculée après le WHERE). D'où l'astuce, très fréquente en M16 : on calcule le rang dans une CTE, puis on filtre le rang dans la requête qui l'entoure.

Comment lire le résultat. Filtre operateur = 'GLO' et regarde la colonne rang : les États où rang = 1 sont tes bastions (à défendre), ceux où rang = 4 sont tes angles morts. Croise avec la version « tous opérateurs » : partout où c'est MTN qui occupe le rang = 1 avec une part très détachée, tu tiens un terrain de bataille. Ce classement ne dit pas encore où investir — un État où tu es 4ᵉ mais déjà saturé n'est pas forcément une cible. C'est la tâche E qui tranchera.

sqlWITH base AS (
    -- Une valeur par (État, opérateur, trimestre) : la série temporelle à analyser
    SELECT
        etat,
        operateur,
        trimestre,
        SUM(nb_abonnes) AS abonnes
    FROM abonnements
    WHERE type_service = 'Voice'
    GROUP BY etat, operateur, trimestre
)
SELECT
    etat,
    operateur,
    trimestre,
    abonnes,
    -- Abonnés du trimestre précédent, DANS LA MÊME série (État + opérateur)
    LAG(abonnes) OVER (
        PARTITION BY etat, operateur
        ORDER BY trimestre
    ) AS abonnes_prec,
    ROUND(
        100.0 * (abonnes - LAG(abonnes) OVER (
                    PARTITION BY etat, operateur ORDER BY trimestre))
        / NULLIF(
            LAG(abonnes) OVER (
                PARTITION BY etat, operateur ORDER BY trimestre),
            0)
    , 2) AS croissance_qoq_pct
FROM base
ORDER BY etat, operateur, trimestre;

⚠️ Le trou dans les données. Les trimestres présents ne sont pas parfaitement consécutifs (il peut manquer un trimestre dans la publication du NBS). LAG regarde la ligne précédente de la série, pas « le trimestre calendaire d'avant ». Quand un trimestre manque, ta variation QoQ compare donc deux trimestres non adjacents : c'est le comportement honnête, mais signale-le dans ton interprétation plutôt que de le maquiller.

Comment lire le résultat. croissance_qoq_pct positive = la série gagne des abonnés ce trimestre-là ; négative = elle en perd ; NULL sur le premier trimestre de chaque série, c'est normal (pas de point de comparaison). Ce qui t'intéresse pour l'acquisition : les États où notre opérateur affiche une croissance QoQ régulièrement positive (une dynamique déjà lancée, à amplifier) versus ceux où MTN accélère fort (on se fait distancer). Une seule variation isolée ne prouve rien — cherche une tendance sur plusieurs trimestres.

sqlCREATE OR REPLACE VIEW vue_penetration AS
WITH abo_etat AS (
    -- Total d'abonnés voix par État et par trimestre
    -- (on agrège les 4 opérateurs : ici on veut la taille du marché, pas le détail)
    SELECT
        etat,
        trimestre,
        SUM(nb_abonnes) AS abonnes_voix
    FROM abonnements
    WHERE type_service = 'Voice'
    GROUP BY etat, trimestre
)
SELECT
    e.etat,
    e.zone,
    e.population,
    ab.trimestre,
    ab.abonnes_voix,
    -- Taux de pénétration : abonnés rapportés à la population de l'État
    -- (peut dépasser 100 % : multi-SIM très répandu au Nigéria — c'est attendu)
    ROUND(
        100.0 * ab.abonnes_voix / NULLIF(e.population, 0),
        2
    ) AS taux_penetration_pct
FROM abo_etat ab
JOIN etats e ON e.etat = ab.etat;

Tu l'interroges ensuite comme une table ordinaire, par exemple sur le dernier trimestre :

sqlSELECT etat, zone, taux_penetration_pct
FROM vue_penetration
WHERE trimestre = (SELECT MAX(trimestre) FROM vue_penetration)
ORDER BY taux_penetration_pct ASC;   -- les moins pénétrés en tête = potentiel brut

Comment lire le résultat. Trie par taux_penetration_pct croissant : les États en tête sont les moins équipés — donc ceux où il reste le plus d'abonnés à conquérir. Ne t'étonne pas si certains taux dépassent 100 % : au Nigéria le multi-SIM (plusieurs cartes par personne) est massif, la pénétration mesure des abonnements actifs, pas des individus. Ce que tu regardes, c'est le classement relatif des États entre eux, pas la valeur absolue. Cette vue est le socle : Power BI la branchera pour ses cartes, Streamlit pour ses filtres, et la tâche E pour trancher.

sqlWITH dernier AS (
    SELECT MAX(trimestre) AS trimestre FROM abonnements
),
abo AS (
    -- Abonnés voix par État/opérateur, dernier trimestre (brique de la tâche A)
    SELECT
        etat,
        operateur,
        SUM(nb_abonnes) AS abonnes
    FROM abonnements
    WHERE type_service = 'Voice'
      AND trimestre = (SELECT trimestre FROM dernier)
    GROUP BY etat, operateur
),
parts AS (
    -- Part de marché par État (fonction fenêtre, comme en A)
    SELECT
        etat,
        operateur,
        100.0 * abonnes / SUM(abonnes) OVER (PARTITION BY etat) AS part_marche_pct
    FROM abo
),
notre_part AS (
    -- On ne garde que la part de NOTRE opérateur
    SELECT etat, part_marche_pct AS part_glo
    FROM parts
    WHERE operateur = 'GLO'
),
penetration AS (
    -- On réutilise la vue de la tâche D (pas de recalcul !)
    SELECT etat, taux_penetration_pct
    FROM vue_penetration
    WHERE trimestre = (SELECT trimestre FROM dernier)
),
seuil_part AS (
    -- Seuil objectif n°1 : la part moyenne de notre opérateur sur tout le pays
    SELECT AVG(part_glo) AS part_glo_moy FROM notre_part
),
seuil_pen AS (
    -- Seuil objectif n°2 : la pénétration moyenne nationale
    SELECT AVG(taux_penetration_pct) AS pen_moy FROM penetration
)
SELECT
    n.etat,
    e.zone,
    ROUND(n.part_glo, 2)              AS part_glo_pct,
    ROUND(p.taux_penetration_pct, 2)  AS penetration_pct
FROM notre_part n
JOIN penetration p ON p.etat = n.etat
JOIN etats e       ON e.etat = n.etat
CROSS JOIN seuil_part sp
CROSS JOIN seuil_pen  spen
WHERE n.part_glo < sp.part_glo_moy            -- (1) on y est faible
  AND p.taux_penetration_pct < spen.pen_moy   -- (2) marché sous-pénétré
ORDER BY p.taux_penetration_pct ASC,          -- le plus de potentiel d'abord
         n.part_glo ASC;

Comment lire le résultat. Chaque ligne de sortie est un État-cible : notre part y est sous la moyenne nationale et la pénétration y est sous la moyenne nationale. L'ordre de tri place en haut les États où l'on est à la fois le plus faible et le marché le moins équipé — c'est la tête de liste du plan d'acquisition. La colonne zone te permet de repérer si ces cibles se concentrent dans une même zone géopolitique (un plan régional cohérent) ou se dispersent. Si la liste ressort vide, ce n'est pas un bug : cela signifie qu'aucun État ne cumule les deux faiblesses sous la moyenne — assouplis alors la règle (par exemple un seuil au 1ᵉʳ quartile plutôt qu'à la moyenne) et documente ce choix. Ne cite jamais un État précis comme « la » réponse sans avoir regardé la dynamique (tâche C) : un État-cible qui, en plus, montre déjà une croissance QoQ positive pour notre opérateur, c'est le dossier en or à porter au comité.

pythonimport pandas as pd
import numpy as np

# --- table longue déjà nettoyée : etat, operateur, type_service, trimestre, nb_abonnes
# --- dimension états déjà jointe : etat, pcode, population, zone
# (voir étapes 2, 3 et 4). On se place sur UN trimestre et le service Voice.

# NB : le trimestre est stocké au format 'AAAA Q#' (année d'abord), tel que produit
# par le nettoyage de l'étape 2 — ici on prend le dernier trimestre disponible.
faits = abonnements.query("type_service == 'Voice' and trimestre == '2023 Q4'")

# Pénétration par État (tous opérateurs confondus)
penet = (
    faits.groupby('etat', as_index=False)['nb_abonnes'].sum()
         .merge(etats[['etat', 'population', 'zone']], on='etat', how='inner')
)
penet['penetration'] = penet['nb_abonnes'] / penet['population']

# Part de marché MTN par État (utile pour la corrélation, test E)
part = (
    faits.pivot_table(index='etat', columns='operateur',
                      values='nb_abonnes', aggfunc='sum', fill_value=0)
)
part['part_mtn'] = part['MTN'] / part[['MTN', 'GLO', 'AIRTEL', 'EMTS']].sum(axis=1)
penet = penet.merge(part[['part_mtn']], on='etat')

rlibrary(dplyr)
library(tidyr)

faits <- abonnements %>%
  filter(type_service == "Voice", trimestre == "2023 Q4")   # format 'AAAA Q#' (cf. étape 2)

# Pénétration par État
penet <- faits %>%
  group_by(etat) %>%
  summarise(nb_abonnes = sum(nb_abonnes), .groups = "drop") %>%
  inner_join(select(etats, etat, population, zone), by = "etat") %>%
  mutate(penetration = nb_abonnes / population)

# Part de marché MTN par État
part <- faits %>%
  group_by(etat, operateur) %>%
  summarise(nb = sum(nb_abonnes), .groups = "drop") %>%
  pivot_wider(names_from = operateur, values_from = nb, values_fill = 0) %>%
  mutate(part_mtn = MTN / (MTN + GLO + AIRTEL + EMTS))

penet <- penet %>%
  inner_join(select(part, etat, part_mtn), by = "etat")

• H0 (hypothèse nulle) : le taux de pénétration moyen est le même dans les 6 zones géopolitiques. Les écarts que tu vois sur la carte ne sont que du bruit d'échantillonnage.
• H1 (hypothèse alternative) : au moins une zone a un taux de pénétration moyen différent des autres.
• Seuil : α = 0,05, fixé avant de regarder les résultats.
• Ce que ça décide :
  • Si on rejette H0 → il existe bien des zones structurellement sous- ou sur-pénétrées ; ça justifie une stratégie d'acquisition différenciée par zone (concentrer l'effort là où la pénétration est faible).
  • Si on ne rejette pas H0 → on n'a pas de preuve d'un effet « zone » ; il faudra plutôt piloter État par État, sans découpage régional.

🎯 Formuler H1 comme « au moins une zone diffère » (et pas « toutes diffèrent »), c'est exactement ce que teste l'ANOVA de la question B. On garde le découpage Nord/Sud pour un test t plus ciblé (question C).

pythonfrom scipy import stats
from statsmodels.stats.multicomp import pairwise_tukeyhsd

# ANOVA : un tableau de valeurs par zone
groupes = [g['penetration'].values for _, g in penet.groupby('zone')]
f_stat, p_value = stats.f_oneway(*groupes)
print("ANOVA — F :", f_stat, "| p-value :", p_value)

# Post-hoc : QUELLES zones diffèrent ?
tukey = pairwise_tukeyhsd(endog=penet['penetration'],
                          groups=penet['zone'],
                          alpha=0.05)
print(tukey.summary())

r# ANOVA
modele <- aov(penetration ~ zone, data = penet)
summary(modele)

# Post-hoc de Tukey : quelles paires de zones diffèrent ?
TukeyHSD(modele, conf.level = 0.95)

pythonfrom scipy import stats
import numpy as np

penet['bloc'] = np.where(penet['zone'].str.startswith('North'), 'Nord', 'Sud')
nord = penet.loc[penet['bloc'] == 'Nord', 'penetration']
sud  = penet.loc[penet['bloc'] == 'Sud',  'penetration']

# 1) Les variances sont-elles comparables ? (Levene)
lev_stat, lev_p = stats.levene(nord, sud)
print("Levene — p-value :", lev_p)

# 2) Test t. equal_var=False => Welch (le choix prudent par défaut)
t_stat, t_p = stats.ttest_ind(nord, sud, equal_var=False)
print("Welch t — t :", t_stat, "| p-value :", t_p)

rpenet$bloc <- ifelse(startsWith(penet$zone, "North"), "Nord", "Sud")

# 1) Égalité des variances (Levene, via car ; sinon var.test pour Fisher)
# install.packages("car")
car::leveneTest(penetration ~ factor(bloc), data = penet)

# 2) Test t de Welch (var.equal = FALSE est le défaut de R)
t.test(penetration ~ bloc, data = penet, var.equal = FALSE)

pythonfrom scipy import stats

# On repart de la table longue Voice + la colonne zone (jointe à etats)
faits_zone = faits.merge(etats[['etat', 'zone']], on='etat', how='inner')

# Tableau de contingence : opérateurs (lignes) x zones (colonnes)
contingence = faits_zone.pivot_table(
    index='operateur', columns='zone',
    values='nb_abonnes', aggfunc='sum', fill_value=0
)

chi2, p_value, dof, attendus = stats.chi2_contingency(contingence)
print("chi2 :", chi2, "| p-value :", p_value, "| ddl :", dof)

# Condition de validité : tous les effectifs attendus >= 5 ?
print("Tous les attendus >= 5 :", (attendus >= 5).all())

rlibrary(dplyr)
library(tidyr)

faits_zone <- faits %>%
  inner_join(select(etats, etat, zone), by = "etat")

contingence <- faits_zone %>%
  group_by(operateur, zone) %>%
  summarise(nb = sum(nb_abonnes), .groups = "drop") %>%
  pivot_wider(names_from = zone, values_from = nb, values_fill = 0) %>%
  tibble::column_to_rownames("operateur") %>%
  as.matrix()

test <- chisq.test(contingence)
test               # statistique + p-value
test$expected      # effectifs attendus : tous >= 5 ?

pythonfrom scipy import stats

r_pearson,  p_pearson  = stats.pearsonr(penet['penetration'],  penet['part_mtn'])
r_spearman, p_spearman = stats.spearmanr(penet['penetration'], penet['part_mtn'])

print("Pearson  — r :", r_pearson,  "| p-value :", p_pearson)
print("Spearman — rho :", r_spearman, "| p-value :", p_spearman)

r# Pearson (lien linéaire)
cor.test(penet$penetration, penet$part_mtn, method = "pearson")

# Spearman (lien monotone, robuste aux outliers)
cor.test(penet$penetration, penet$part_mtn, method = "spearman")

La carte Bing intégrée géocode par nom : « LAGOS », « KANO », etc. passent bien, mais un FCT recodé peut être ambigu. Deux parades :

• Crée une colonne calculée qui remet le nom complet pour l'affichage cartographique :

daxEtat_carte =
IF(vue_penetration[etat] = "FCT", "Federal Capital Territory", vue_penetration[etat])

• Ou, pour un rendu propre et hors-ligne, importe un fond de carte TopoJSON des États du Nigéria dans le visuel Shape map (Format → Shape → Add map). Le nom de chaque forme dans le TopoJSON doit correspondre à ta colonne etat.

Remplace "GLO" par ton opérateur (GLO, AIRTEL ou EMTS/9mobile) :

daxPart de marché GLO =
DIVIDE(
    CALCULATE(SUM(vue_penetration[nb_abonnes]), vue_penetration[operateur] = "GLO"),
    SUM(vue_penetration[nb_abonnes])
)

CALCULATE restreint le numérateur aux abonnés de ton opérateur, le dénominateur reste le total (tous opérateurs confondus) dans le contexte de filtre courant. DIVIDE évite l'erreur de division par zéro si un contexte se retrouve sans abonné. Formate la mesure en pourcentage (Modélisation → Format → %). Pose-la dans une carte à côté du filtre de zone : tu lis alors ta part de marché zone par zone.

pythonimport streamlit as st
import pandas as pd
from sqlalchemy import create_engine, text

st.set_page_config(page_title="Acquisition abonnés — Nigéria", layout="wide")

# --- Connexion (la chaîne vient des secrets, jamais en dur) ---
@st.cache_resource
def get_engine():
    return create_engine(st.secrets["db_url"])

# --- Chargement filtré, mis en cache 10 min pour ménager la base serverless ---
@st.cache_data(ttl=600)
def charger_donnees(trimestre, type_service):
    requete = text("""
        SELECT etat, zone, population, operateur, nb_abonnes, taux_penetration
        FROM vue_penetration
        WHERE trimestre = :trimestre
          AND type_service = :type_service
    """)
    with get_engine().connect() as conn:
        return pd.read_sql(requete, conn, params={
            "trimestre": trimestre,
            "type_service": type_service,
        })

@st.cache_data(ttl=600)
def options():
    with get_engine().connect() as conn:
        trims = pd.read_sql(
            text("SELECT DISTINCT trimestre FROM vue_penetration ORDER BY trimestre"),
            conn)["trimestre"].tolist()
        zones = pd.read_sql(
            text("SELECT DISTINCT zone FROM vue_penetration "
                 "WHERE zone IS NOT NULL ORDER BY zone"),
            conn)["zone"].tolist()
    return trims, zones

st.title("📡 Où concentrer notre acquisition d'abonnés ?")
st.caption("Opérateur : GLO · sources : NBS (télécom) + HDX (population)")

trims, zones = options()

# --- Filtres interactifs (barre latérale) ---
with st.sidebar:
    st.header("Filtres")
    trimestre    = st.selectbox("Trimestre", trims, index=len(trims) - 1)
    type_service = st.selectbox("Type de service", ["Voice", "Internet"])
    operateur    = st.selectbox("Opérateur", ["GLO", "MTN", "AIRTEL", "EMTS"])
    zone         = st.selectbox("Zone géopolitique", ["Toutes"] + zones)

df = charger_donnees(trimestre, type_service)

# --- Garde-fou : une combinaison de filtres peut ne rien renvoyer ---
if df.empty:
    st.warning("Aucune donnée pour cette combinaison. "
               "Change de trimestre ou de type de service.")
    st.stop()

df_op = df[df["operateur"] == operateur].copy()
if zone != "Toutes":
    df_op = df_op[df_op["zone"] == zone]

# --- Indicateurs clés ---
col1, col2 = st.columns(2)
col1.metric("Pénétration moyenne", f"{df_op['taux_penetration'].mean():.1%}")
col2.metric("États couverts", df_op["etat"].nunique())

# --- Classement des États par pénétration (les plus bas = cibles d'acquisition) ---
st.subheader(f"États classés par pénétration — {operateur}")
classement = (df_op[["etat", "taux_penetration"]]
              .sort_values("taux_penetration", ascending=True)
              .set_index("etat"))
st.bar_chart(classement)

# --- Détail État par État ---
st.subheader("Détail par État")
st.dataframe(
    df_op[["etat", "zone", "population", "nb_abonnes", "taux_penetration"]]
        .sort_values("taux_penetration"),
    use_container_width=True,
)

À retenir sur ce code :

• st.selectbox crée chaque filtre ; le tri du bar_chart par pénétration croissante fait remonter en haut les États les moins pénétrés — visuellement, tes cibles d'acquisition.
• La requête est paramétrée (:trimestre, :type_service + params={...}) : jamais de f-string qui concatène une valeur utilisateur dans du SQL (réflexe anti-injection).
• @st.cache_resource garde un seul moteur de connexion pour toute l'app ; @st.cache_data(ttl=600) met en cache les résultats 10 min — utile face au scale-to-zero de Neon, ça évite de réveiller la base à chaque interaction.
• Le garde-fou if df.empty: st.stop() gère proprement le cas réel où un filtre ne renvoie rien.

Si tu ne veux pas exposer ta base (ou éviter les réveils Neon), exporte la vue une fois en CSV et fais lire ce fichier par l'app.

Export (à lancer une fois, en local) :

pythonimport pandas as pd
from sqlalchemy import create_engine

engine = create_engine("postgresql://user:pwd@host/db?sslmode=require")
df = pd.read_sql("SELECT * FROM vue_penetration", engine)
df.to_csv("vue_penetration.csv", index=False)

Puis, dans app.py, remplace la connexion par une simple lecture (le CSV est commité dans le repo à côté de app.py) :

python@st.cache_data
def charger():
    return pd.read_csv("vue_penetration.csv")

df = charger()

Le reste de l'app (filtres, graphique, tableau) ne change pas. C'est plus simple à déployer, au prix de données figées à la date de l'export.

Ajoute à ton .gitignore :

gitignore.streamlit/secrets.toml

Et crée un requirements.txt à la racine du repo, pour que Streamlit Cloud sache quoi installer :

streamlit
pandas
sqlalchemy
psycopg2-binary

(La variante CSV n'a pas besoin de sqlalchemy ni de psycopg2-binary.)

• ModuleNotFoundError sur Streamlit Cloud : ton requirements.txt est incomplet ou absent. En local ça marchait grâce à ton environnement ; sur le cloud, tout ce qui n'est pas listé n'est pas installé. Pour Postgres, n'oublie pas psycopg2-binary.
• La base ne répond pas / timeout au premier chargement : c'est le scale-to-zero de Neon (veille après 5 min). La base se réveille en quelques centaines de millisecondes à quelques secondes ; le cache @st.cache_data(ttl=600) limite la fréquence des réveils. Si c'est trop gênant pour une démo, bascule sur la variante CSV.

Il n'y a pas de « bonne réponse » unique ici : la reco dépend de tes résultats. Ce qui est corrigé, c'est la méthode. Une restitution solide fait ceci :

1. Elle part de la réponse, pas des données. La toute première phrase prononcée est la recommandation. Le comité doit pouvoir décider même s'il n'écoute que 30 secondes. On ne raconte JAMAIS le pipeline (« d'abord j'ai récupéré le XLSX du NBS, puis l'API HDX… ») — ça, c'est ta cuisine, pas leur repas.

2. Chaque argument est adossé à une analyse traçable. Pour chaque point, tu dois pouvoir répondre « d'où sort ce chiffre ? » en une phrase :

• Argument potentiel → ta requête de taux de pénétration nb_abonnes / population par État/zone (schéma étoile : faits abonnements ⋈ dimension etats).
• Argument vulnérabilité MTN → ta requête de parts d'opérateurs par État, + le test statistique qui confirme que l'écart observé n'est pas dû au hasard.
• Argument dynamique → ton analyse de l'évolution sur les 11 trimestres (Q2 2021 → Q4 2023).

3. Elle applique la règle d'interprétation stat honnêtement. Rappel de la règle générique vue au niveau : si p < 0,05, tu rejettes H0 et tu peux affirmer au comité qu'il existe une différence réelle entre les zones/opérateurs comparés ; si p ≥ 0,05, tu ne peux PAS conclure à une différence — et tu ne la présentes pas comme un fait. Ne survends jamais un écart non significatif : un COMEX qui découvre plus tard que ton « argument » ne tenait pas statistiquement ne te refera plus confiance.

4. Elle distingue abonnements et personnes. Point d'honnêteté que l'IA soulèvera (technique 2) : les données sont des abonnements actifs, pas des individus uniques (un même Nigérian peut avoir 2-3 SIM). Ton « taux de pénétration » peut donc dépasser 100 % dans certains États — c'est normal, c'est une intensité d'usage, pas un taux d'équipement. Dis-le au comité avant qu'il ne le remarque : ça renforce ta crédibilité au lieu de la casser.

5. Elle garde les preuves en réserve. Le corps = message + 3 arguments. Le dashboard, l'app Streamlit publiée, les tables détaillées, la méthodo du pipeline = annexes, sorties uniquement en réponse à une question. Structure recommandée de la présentation :

Slide 1 : LA reco (message principal, une phrase)         ← le sommet
Slide 2 : Argument 1 — le potentiel      (1 visuel max)
Slide 3 : Argument 2 — MTN vulnérable    (1 visuel max)
Slide 4 : Argument 3 — la dynamique      (1 visuel max)
Slide 5 : Prochaines étapes / ce qu'on décide aujourd'hui
── ANNEXES (on n'y va que si on te le demande) ──
  A1 : dashboard complet     A2 : app Streamlit (lien live)
  A3 : détail par État       A4 : méthodo & sources (NBS, HDX)

6. Elle a été challengée avant la réunion. Tu es passé par les 2 techniques IA du M20 : tu connais les 3 failles principales de ta reco et les 5-8 questions dures qui vont tomber, et tu as une réponse courte pour chacune. Le comité ne doit pas te surprendre.

Si ta restitution coche ces 6 points, tu as fait le travail d'un Data Analyst — pas celui d'un exécutant qui livre des chiffres, celui d'un partenaire qui livre une décision.