""" Analyse Sports - Croisement communes / intercommunalités / licenciés / clubs / équipements """ import openpyxl from pathlib import Path import sys DATA_DIR = Path(__file__).parent # ───────────────────────────────────────────── # UTILITAIRES # ───────────────────────────────────────────── def normaliser_code_commune(val): """Normalise un code commune en chaîne 5 caractères (ex: 1001 -> '01001', '2B366' reste tel quel).""" s = str(val).strip().split(".")[0] # retire éventuel .0 float if s.isdigit(): return s.zfill(5) return s # codes Corse ou DOM : 2A, 2B, 97x... def lire_excel(chemin, desc=""): """Lit un fichier Excel en mode lecture seule et retourne (headers, liste_de_dicts).""" chemin = Path(chemin) print(f" Lecture {desc or chemin.name}...") wb = openpyxl.load_workbook(chemin, read_only=True, data_only=True) ws = wb.active rows_iter = ws.iter_rows(values_only=True) headers = list(next(rows_iter)) data = [dict(zip(headers, row)) for row in rows_iter] wb.close() print(f" → {len(data):,} lignes, {len(headers)} colonnes") return headers, data # ───────────────────────────────────────────── # 1. CHARGEMENT # ───────────────────────────────────────────── def load_population(): _, data = lire_excel(DATA_DIR / "population municipale.xlsx", "population municipale") result = {} for row in data: code = normaliser_code_commune(row.get("codgeo", "")) result[code] = { "code_commune": code, "commune": row.get("libgeo", ""), "dep": str(row.get("dep", "")).strip(), "reg": str(row.get("reg", "")).strip(), "population": int(row.get("p21_pop") or 0), } return result # dict code_commune -> info def load_clubs(): _, data = lire_excel(DATA_DIR / "clubs-data-2023.xlsx", "clubs 2023") # Agrégation par commune par_commune = {} # code -> {nb_clubs, federations: set} par_commune_fed = {} # (code, fed) -> nb_clubs for row in data: code = normaliser_code_commune(row.get("Code Commune", "")) fed = str(row.get("Fédération", "") or "").strip() total = int(row.get("Total_actifs") or 0) if code not in par_commune: par_commune[code] = {"nb_clubs": 0, "nb_feds": set()} par_commune[code]["nb_clubs"] += total if total > 0: par_commune[code]["nb_feds"].add(fed) par_commune_fed[(code, fed)] = par_commune_fed.get((code, fed), 0) + total # Convertir set en count for code in par_commune: par_commune[code]["nb_feds"] = len(par_commune[code]["nb_feds"]) return par_commune, par_commune_fed def load_licencies(): _, data = lire_excel(DATA_DIR / "lic-data-2023.xlsx", "licenciés 2023 (volumineux)") par_commune = {} par_commune_fed = {} for row in data: code = normaliser_code_commune(row.get("Code Commune", "")) fed = str(row.get("Fédération", "") or "").strip() total = int(row.get("Total") or 0) par_commune[code] = par_commune.get(code, 0) + total par_commune_fed[(code, fed)] = par_commune_fed.get((code, fed), 0) + total return par_commune, par_commune_fed def load_equipements(): _, data = lire_excel(DATA_DIR / "data-es.xlsx", "équipements sportifs (volumineux)") par_commune = {} # code -> nb_equip par_famille = {} # (code, famille) -> nb_equip epci_map = {} # code_commune -> (epci_code, epci_nom) commune_nom_map = {} # code -> nom commune (depuis inst_nom / new_name) for row in data: code = normaliser_code_commune(row.get("new_code", "") or "") if not code or code == "00000": code = normaliser_code_commune(row.get("inst_cp", "") or "") famille = str(row.get("equip_type_famille", "") or "Inconnu").strip() equip_id = row.get("equip_numero") par_commune[code] = par_commune.get(code, 0) + 1 key = (code, famille) par_famille[key] = par_famille.get(key, 0) + 1 if code not in epci_map: epci_c = str(row.get("epci_code", "") or "").strip() epci_n = str(row.get("epci_nom", "") or "").strip() if epci_c: epci_map[code] = (epci_c, epci_n) # Familles uniques familles = sorted(set(f for (_, f) in par_famille.keys())) return par_commune, par_famille, epci_map, familles # ───────────────────────────────────────────── # 2. CONSTRUCTION DU TABLEAU CROISÉ # ───────────────────────────────────────────── def build_table(): print("\nChargement des fichiers sources...") pop = load_population() clubs_comm, clubs_fed = load_clubs() lic_comm, lic_fed = load_licencies() equip_comm, equip_famille, epci_map, familles = load_equipements() print("\nFusion des données...") rows = [] all_codes = set(pop.keys()) | set(clubs_comm.keys()) | set(lic_comm.keys()) | set(equip_comm.keys()) for code in all_codes: if not code or code == "00000": continue info = pop.get(code, {}) epci = epci_map.get(code, ("", "")) row = { "code_commune": code, "commune": info.get("commune", ""), "dep": info.get("dep", ""), "reg": info.get("reg", ""), "population": info.get("population", 0), "epci_code": epci[0], "epci_nom": epci[1], "nb_clubs": clubs_comm.get(code, {}).get("nb_clubs", 0) if isinstance(clubs_comm.get(code), dict) else 0, "nb_licencies": lic_comm.get(code, 0), "nb_equipements": equip_comm.get(code, 0), } pop_val = row["population"] row["licencies_pour_1000_hab"] = round(row["nb_licencies"] / pop_val * 1000, 1) if pop_val else None row["clubs_pour_1000_hab"] = round(row["nb_clubs"] / pop_val * 1000, 2) if pop_val else None row["equip_pour_1000_hab"] = round(row["nb_equipements"] / pop_val * 1000, 2) if pop_val else None # Équipements par famille for fam in familles: row[f"equip_{fam}"] = equip_famille.get((code, fam), 0) rows.append(row) # Tri par département puis population décroissante rows.sort(key=lambda r: (r["dep"], -r["population"])) print(f"Tableau construit : {len(rows):,} communes") return rows, familles, clubs_fed, lic_fed # ───────────────────────────────────────────── # 3. VUE EPCI # ───────────────────────────────────────────── def build_epci_view(rows): epci_agg = {} for row in rows: epci_code = row.get("epci_code", "") epci_nom = row.get("epci_nom", "") if not epci_code: continue key = (epci_code, epci_nom) if key not in epci_agg: epci_agg[key] = {"epci_code": epci_code, "epci_nom": epci_nom, "population": 0, "nb_clubs": 0, "nb_licencies": 0, "nb_equipements": 0, "nb_communes": 0} d = epci_agg[key] d["population"] += row["population"] d["nb_clubs"] += row["nb_clubs"] d["nb_licencies"] += row["nb_licencies"] d["nb_equipements"] += row["nb_equipements"] d["nb_communes"] += 1 result = list(epci_agg.values()) for r in result: p = r["population"] r["licencies_pour_1000_hab"] = round(r["nb_licencies"] / p * 1000, 1) if p else None r["clubs_pour_1000_hab"] = round(r["nb_clubs"] / p * 1000, 2) if p else None r["equip_pour_1000_hab"] = round(r["nb_equipements"] / p * 1000, 2) if p else None result.sort(key=lambda r: -r["population"]) return result # ───────────────────────────────────────────── # 4. EXPORT EXCEL # ───────────────────────────────────────────── def export_excel(rows, familles): try: import openpyxl from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter except ImportError: print("openpyxl manquant.") return output = DATA_DIR / "synthese_sports.xlsx" print(f"\nExport Excel → {output}") wb = openpyxl.Workbook() # ── Onglet Communes ── ws = wb.active ws.title = "Communes" base_cols = ["code_commune", "commune", "dep", "reg", "population", "epci_code", "epci_nom", "nb_clubs", "nb_licencies", "nb_equipements", "licencies_pour_1000_hab", "clubs_pour_1000_hab", "equip_pour_1000_hab"] fam_cols = [f"equip_{f}" for f in familles] all_cols = base_cols + fam_cols # En-têtes header_fill = PatternFill("solid", fgColor="1F4E79") header_font = Font(color="FFFFFF", bold=True) for col_idx, col_name in enumerate(all_cols, 1): cell = ws.cell(row=1, column=col_idx, value=col_name) cell.fill = header_fill cell.font = header_font cell.alignment = Alignment(horizontal="center") # Données for row_idx, row in enumerate(rows, 2): for col_idx, col_name in enumerate(all_cols, 1): ws.cell(row=row_idx, column=col_idx, value=row.get(col_name)) # Largeurs auto for col_idx, col_name in enumerate(all_cols, 1): ws.column_dimensions[get_column_letter(col_idx)].width = max(12, len(col_name) + 2) ws.freeze_panes = "A2" ws.auto_filter.ref = ws.dimensions # ── Onglet EPCI ── epci_rows = build_epci_view(rows) ws2 = wb.create_sheet("EPCI") epci_cols = ["epci_code", "epci_nom", "nb_communes", "population", "nb_clubs", "nb_licencies", "nb_equipements", "licencies_pour_1000_hab", "clubs_pour_1000_hab", "equip_pour_1000_hab"] for col_idx, col_name in enumerate(epci_cols, 1): cell = ws2.cell(row=1, column=col_idx, value=col_name) cell.fill = header_fill cell.font = header_font cell.alignment = Alignment(horizontal="center") for row_idx, row in enumerate(epci_rows, 2): for col_idx, col_name in enumerate(epci_cols, 1): ws2.cell(row=row_idx, column=col_idx, value=row.get(col_name)) for col_idx, col_name in enumerate(epci_cols, 1): ws2.column_dimensions[get_column_letter(col_idx)].width = max(14, len(col_name) + 2) ws2.freeze_panes = "A2" ws2.auto_filter.ref = ws2.dimensions # ── Onglet Equip par famille ── ws3 = wb.create_sheet("Equip_par_famille") fam_base = ["code_commune", "commune", "dep", "population", "nb_equipements"] fam_all = fam_base + fam_cols for col_idx, col_name in enumerate(fam_all, 1): cell = ws3.cell(row=1, column=col_idx, value=col_name) cell.fill = header_fill cell.font = header_font cell.alignment = Alignment(horizontal="center") for row_idx, row in enumerate(rows, 2): for col_idx, col_name in enumerate(fam_all, 1): ws3.cell(row=row_idx, column=col_idx, value=row.get(col_name)) for col_idx, col_name in enumerate(fam_all, 1): ws3.column_dimensions[get_column_letter(col_idx)].width = max(12, min(30, len(col_name) + 2)) ws3.freeze_panes = "A2" ws3.auto_filter.ref = ws3.dimensions wb.save(output) print(f" Excel sauvegardé ({output.stat().st_size // 1024} Ko)") return output # ───────────────────────────────────────────── # 5. EXPORT CSV # ───────────────────────────────────────────── def export_csv(rows, familles): import csv output = DATA_DIR / "synthese_sports.csv" base_cols = ["code_commune", "commune", "dep", "reg", "population", "epci_code", "epci_nom", "nb_clubs", "nb_licencies", "nb_equipements", "licencies_pour_1000_hab", "clubs_pour_1000_hab", "equip_pour_1000_hab"] fam_cols = [f"equip_{f}" for f in familles] all_cols = base_cols + fam_cols with open(output, "w", newline="", encoding="utf-8-sig") as f: writer = csv.DictWriter(f, fieldnames=all_cols, extrasaction="ignore") writer.writeheader() writer.writerows(rows) print(f" CSV sauvegardé ({output.stat().st_size // 1024} Ko)") return output # ───────────────────────────────────────────── # 6. INTERFACE TERMINAL # ───────────────────────────────────────────── def afficher_stats(rows): pop_total = sum(r["population"] for r in rows) lic_total = sum(r["nb_licencies"] for r in rows) clubs_total = sum(r["nb_clubs"] for r in rows) equip_total = sum(r["nb_equipements"] for r in rows) nb_epci = len(set(r["epci_nom"] for r in rows if r.get("epci_nom"))) print("\n" + "="*60) print(" STATISTIQUES GÉNÉRALES") print("="*60) print(f" Communes : {len(rows):>10,}") print(f" Population : {pop_total:>10,}") print(f" Licenciés : {lic_total:>10,}") print(f" Clubs : {clubs_total:>10,}") print(f" Équipements : {equip_total:>10,}") print(f" EPCI identifiés : {nb_epci:>10,}") print("="*60) def fmt_row(r, large=False): epci = (r.get("epci_nom") or "")[:30] pop = f"{r['population']:,}" if r['population'] else "-" lic = f"{r['nb_licencies']:,}" if r['nb_licencies'] else "0" clubs = str(r['nb_clubs']) equip = str(r['nb_equipements']) taux = str(r.get('licencies_pour_1000_hab') or "-") if large: return (f" {r['code_commune']:<7} {r['commune'][:28]:<28} {r['dep']:<4}" f" {pop:>9} {epci:<32} {clubs:>6} {lic:>9} {equip:>7} {taux:>8}") return (f" {r['code_commune']:<7} {r['commune'][:28]:<28} {r['dep']:<4}" f" {pop:>9} {clubs:>6} {lic:>9} {equip:>7} {taux:>8}") def print_header(large=False): if large: print(f" {'Code':<7} {'Commune':<28} {'Dep':<4} {'Pop':>9} {'EPCI':<32}" f" {'Clubs':>6} {'Licenc.':>9} {'Equip.':>7} {'Lic/1k':>8}") else: print(f" {'Code':<7} {'Commune':<28} {'Dep':<4} {'Pop':>9}" f" {'Clubs':>6} {'Licenc.':>9} {'Equip.':>7} {'Lic/1k':>8}") print(" " + "-"*80) def rechercher_commune(rows, nom): nom_up = nom.upper() result = [r for r in rows if nom_up in r["commune"].upper()] if not result: print(f" Aucune commune trouvée pour '{nom}'") return print(f"\n {len(result)} résultat(s) pour '{nom}':") print_header(large=True) for r in result[:50]: print(fmt_row(r, large=True)) if len(result) > 50: print(f" ... {len(result)-50} résultats supplémentaires. Affinez la recherche.") def rechercher_epci(rows, nom): nom_up = nom.upper() result = [r for r in rows if nom_up in (r.get("epci_nom") or "").upper()] if not result: print(f" Aucun EPCI trouvé pour '{nom}'") return # Vue agrégée EPCI epci_rows = build_epci_view(result) print(f"\n EPCI correspondant ({len(epci_rows)}) :") for e in epci_rows[:20]: print(f" {e['epci_nom'][:40]:<40} Pop:{e['population']:>10,} " f"Clubs:{e['nb_clubs']:>5} Lic:{e['nb_licencies']:>8,} Equip:{e['nb_equipements']:>6}") print(f"\n Communes dans cet EPCI ({len(result)}) :") print_header() for r in result[:100]: print(fmt_row(r)) def top_communes(rows, critere, n): valides = ["nb_licencies", "nb_clubs", "nb_equipements", "licencies_pour_1000_hab", "clubs_pour_1000_hab", "equip_pour_1000_hab"] if critere not in valides: print(f" Critère invalide. Choisir parmi : {', '.join(valides)}") return sorted_rows = sorted( [r for r in rows if r.get(critere) is not None], key=lambda r: -(r[critere] or 0) ) print(f"\n Top {n} communes par {critere}:") print_header(large=True) for r in sorted_rows[:n]: print(fmt_row(r, large=True)) def rechercher_departement(rows, dep): result = [r for r in rows if str(r["dep"]).zfill(2) == str(dep).zfill(2) or str(r["dep"]).upper() == str(dep).upper()] if not result: print(f" Aucune commune trouvée pour département '{dep}'") return pop = sum(r["population"] for r in result) lic = sum(r["nb_licencies"] for r in result) clubs = sum(r["nb_clubs"] for r in result) equip = sum(r["nb_equipements"] for r in result) print(f"\n Département {dep} — {len(result)} communes") print(f" Population : {pop:,} | Clubs : {clubs:,} | Licenciés : {lic:,} | Équipements : {equip:,}") print(f" Taux lic/1000 hab : {round(lic/pop*1000,1) if pop else '-'}") print_header() for r in result[:100]: print(fmt_row(r)) if len(result) > 100: print(f" ... {len(result)-100} communes supplémentaires.") def menu(rows, familles): afficher_stats(rows) while True: print("\n" + "─"*50) print(" MENU PRINCIPAL") print("─"*50) print(" 1. Rechercher une commune") print(" 2. Rechercher un EPCI / intercommunalité") print(" 3. Rechercher par département") print(" 4. Top N communes par critère") print(" 5. Statistiques générales") print(" 6. Exporter Excel (synthese_sports.xlsx)") print(" 7. Exporter CSV (synthese_sports.csv)") print(" 0. Quitter") choix = input("\n Votre choix : ").strip() if choix == "1": nom = input(" Nom commune (partiel) : ").strip() if nom: rechercher_commune(rows, nom) elif choix == "2": nom = input(" Nom EPCI (partiel) : ").strip() if nom: rechercher_epci(rows, nom) elif choix == "3": dep = input(" Code département (ex: 75, 2A, 971) : ").strip() if dep: rechercher_departement(rows, dep) elif choix == "4": print(" Critères : nb_licencies, nb_clubs, nb_equipements,") print(" licencies_pour_1000_hab, clubs_pour_1000_hab, equip_pour_1000_hab") critere = input(" Critère [nb_licencies] : ").strip() or "nb_licencies" n_str = input(" Nombre de résultats [20] : ").strip() n = int(n_str) if n_str.isdigit() else 20 top_communes(rows, critere, n) elif choix == "5": afficher_stats(rows) elif choix == "6": export_excel(rows, familles) elif choix == "7": export_csv(rows, familles) elif choix == "0": print("\n Au revoir.") break else: print(" Choix invalide.") # ───────────────────────────────────────────── # MAIN # ───────────────────────────────────────────── if __name__ == "__main__": print("="*60) print(" OBSERVATOIRE SPORTIF — Croisement territorial") print(" Communes | EPCI | Population | Clubs | Licenciés | Équipements") print("="*60) rows, familles, clubs_fed, lic_fed = build_table() # Export automatique au premier lancement print("\nGénération des exports au démarrage...") export_excel(rows, familles) export_csv(rows, familles) menu(rows, familles)