spb/uqo-research-db
Public
1# ============================================================2# Rapport UQO — SPID : génération des figures et tableaux LaTeX3# python3 docs/rapport_uqo/make_figures.py4# Lit uniquement les tables physiques de data/db/spid.duckdb5# (les vues src_* pointent vers un ancien chemin et sont ignorées).6# ============================================================7from __future__ import annotations89import json10from pathlib import Path1112import duckdb13import matplotlib14matplotlib.use("Agg")15import matplotlib.pyplot as plt16import matplotlib.ticker as mticker17import numpy as np1819HERE = Path(__file__).resolve().parent20ROOT = HERE.parent.parent21DB = ROOT / "data" / "db" / "spid.duckdb"22FIG = HERE / "figures"23TAB = HERE / "tables"24FIG.mkdir(exist_ok=True)25TAB.mkdir(exist_ok=True)2627plt.rcParams.update({28 "font.family": "sans-serif",29 "font.sans-serif": ["Fira Sans", "Helvetica", "Arial", "DejaVu Sans"],30 "font.size": 9.5, "axes.titlesize": 11, "axes.titleweight": "bold",31 "axes.spines.top": False, "axes.spines.right": False,32 "figure.dpi": 160, "savefig.bbox": "tight", "axes.grid": True,33 "grid.alpha": 0.25, "grid.linewidth": 0.5, "legend.frameon": False,34})35BLEU = "#003E7E"; BLEUC = "#0066B3"; OR = "#C6A300"; GRIS = "#58595B"36VERT = "#008046"; ROUGE = "#B42318"; ORANGE = "#D67A00"; VIOLET = "#5E35B1"; TEAL = "#007979"37PAL = [BLEU, OR, TEAL, ORANGE, VERT, VIOLET, ROUGE, BLEUC, GRIS, "#8DA9C4", "#E0C36B"]3839LABELS = {40 "ma_integration": "Intégration M&A", "plant_construction": "Construction d'usine",41 "rd_program": "Programme de R&D", "product_launch": "Lancement de produit",42 "manufacturing_expansion": "Expansion manufacturière", "partnership": "Partenariat",43 "technology_deployment": "Déploiement technologique", "digital_transformation": "Transformation numérique",44 "infrastructure": "Infrastructure", "capex_program": "Programme de capex",45 "ai_initiative": "Initiative IA", "geographic_expansion": "Expansion géographique",46 "cost_reduction": "Réduction des coûts", "sustainability": "Durabilité",47 "energy_transition": "Transition énergétique", "supply_chain": "Chaîne d'approvisionnement",48 "drug_pipeline": "Pipeline de médicaments", "data_center": "Centre de données",49 "cloud_migration": "Migration infonuagique", "automation": "Automatisation",50 "erp_implementation": "Implantation ERP",51}52SECT = {53 "Industrials": "Industrie", "Health Care": "Santé", "Information Technology": "Technologies de l'information",54 "Consumer Discretionary": "Consommation discrétionnaire", "Utilities": "Services publics",55 "Financials": "Finance", "Consumer Staples": "Consommation de base", "Real Estate": "Immobilier",56 "Energy": "Énergie", "Materials": "Matériaux", "Communication Services": "Services de communication",57}58STATUT = {"in_progress": "En cours", "completed": "Terminé", "planned": "Planifié", "mentioned": "Mentionné"}5960con = duckdb.connect(str(DB), read_only=True)61q = lambda sql: con.execute(sql).fetchall()62stats: dict = {}636465T = lambda s: s.replace("&", "\\&")666768def fr(n: float, dec: int = 0) -> str:69 """Format nombre à la française pour LaTeX : 19\\,227 ; 4\\,602{,}6."""70 if dec == 0:71 s = f"{int(round(n)):,}".replace(",", "\\,")72 else:73 s = f"{n:,.{dec}f}".replace(",", "\\,").replace(".", "{,}")74 return s757677def save(fig, name):78 fig.savefig(FIG / f"{name}.pdf")79 fig.savefig(FIG / f"{name}.png")80 plt.close(fig)81 print(" fig", name)828384# ------------------------------------------------------------------ 1. entonnoir85def fig_entonnoir():86 # sections éligibles (>= 30 mots) réellement passées à l'extracteur : registre extracted_sections87 n_sec = q("select count(*) from extracted_sections")[0][0]88 n_sec_10k = q("select count(*) from extracted_sections e join v_sections v using(section_id) where v.form_type = '10-K'")[0][0]89 n_men = q("select count(*) from project_mentions")[0][0]90 n_proj = q("select count(*) from projects")[0][0]91 n_fil = q("select count(distinct accession_number) from project_mentions")[0][0]92 stats.update(n_sections=n_sec, n_sections_10k=n_sec_10k, n_mentions=n_men, n_projects=n_proj,93 n_filings_with_mentions=n_fil)94 labels = ["Sections de\nfilings analysées", "Filings avec au\nmoins un projet", "Mentions de\nprojets extraites",95 "Projets\nrésolus"]96 vals = [n_sec, n_fil, n_men, n_proj]97 fig, ax = plt.subplots(figsize=(8, 3.6))98 bars = ax.barh(labels[::-1], vals[::-1], color=[OR, TEAL, BLEUC, BLEU])99 for b, v in zip(bars, vals[::-1]):100 ax.text(b.get_width() + n_sec * 0.01, b.get_y() + b.get_height() / 2, f"{v:,}".replace(",", " "),101 va="center", fontsize=10, fontweight="bold", color=GRIS)102 ax.set_xlim(0, n_sec * 1.18)103 ax.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"{int(x/1000)} k"))104 ax.set_xlabel("nombre d'unités")105 ax.set_title("De la section de filing au projet résolu")106 save(fig, "fig_entonnoir")107108109# ------------------------------------------------------------------ 2. mentions par année / formulaire110def fig_mentions_annee():111 rows = q("""select year(filing_date) y, form_type, count(*) from project_mentions112 where filing_date between '2010-01-01' and '2026-12-31' group by 1,2 order by 1,2""")113 years = sorted({r[0] for r in rows})114 forms = ["10-K", "10-Q", "8-K"]115 data = {f: [0] * len(years) for f in forms}116 for y, f, n in rows:117 data[f][years.index(y)] = n118 fig, ax = plt.subplots(figsize=(8.5, 3.8))119 bottom = np.zeros(len(years))120 for f, c in zip(forms, [BLEU, OR, TEAL]):121 ax.bar(years, data[f], bottom=bottom, color=c, label=f, width=0.78)122 bottom += np.array(data[f])123 for x, t in zip(years, bottom):124 ax.text(x, t + 30, f"{int(t):,}".replace(",", " "), ha="center", fontsize=7, color=GRIS)125 ax.set_xticks(years); ax.tick_params(axis="x", labelrotation=45)126 ax.set_ylabel("mentions de projets"); ax.legend(title="Formulaire", ncol=3, loc="upper left")127 ax.set_title("Mentions de projets par année de dépôt et par formulaire")128 save(fig, "fig_mentions_annee")129 stats["mentions_by_form"] = {f: sum(v) for f, v in data.items()}130131132# ------------------------------------------------------------------ 3. types133def fig_types():134 rows = q("""select project_type, count(*), coalesce(sum(total_amount_usd),0), median(total_amount_usd)135 from projects group by 1 order by 2 desc""")136 lab = [LABELS[r[0]] for r in rows][::-1]137 n = [r[1] for r in rows][::-1]138 usd = [r[2] / 1e9 for r in rows][::-1]139 fig, (a1, a2) = plt.subplots(1, 2, figsize=(9.6, 6), sharey=True)140 a1.barh(lab, n, color=BLEU)141 for i, v in enumerate(n):142 a1.text(v + 40, i, f"{v:,}".replace(",", " "), va="center", fontsize=7.5, color=GRIS)143 a1.set_title("Nombre de projets"); a1.set_xlabel("projets résolus"); a1.set_xlim(0, max(n) * 1.15)144 a2.barh(lab, usd, color=OR)145 for i, v in enumerate(usd):146 a2.text(v + 100, i, f"{v:,.0f}".replace(",", " "), va="center", fontsize=7.5, color=GRIS)147 a2.set_title("Capital annoncé (G$ US)"); a2.set_xlabel("somme des montants divulgués"); a2.set_xlim(0, max(usd) * 1.18)148 fig.suptitle("Les 21 types de projets de la taxonomie SPID", fontweight="bold", y=1.0)149 save(fig, "fig_types")150 with open(TAB / "tab_types.tex", "w") as f:151 for r in rows:152 med = f"{r[3]/1e6:,.0f}".replace(",", "\\,") if r[3] else "---"153 f.write(f"\\code{{{r[0].replace('_', '\\_')}}} & {T(LABELS[r[0]])} & {fr(r[1])} & {fr(r[2]/1e9,1)} & {med} \\\\\n")154155156# ------------------------------------------------------------------ 4. secteurs157def fig_secteurs():158 rows = q("""select sector, count(*), coalesce(sum(total_amount_usd),0), count(distinct cik),159 avg(total_amount_usd) from projects where sector is not null group by 1 order by 2 desc""")160 lab = [SECT.get(r[0], r[0]) for r in rows][::-1]161 n = [r[1] for r in rows][::-1]; usd = [r[2] / 1e9 for r in rows][::-1]162 fig, (a1, a2) = plt.subplots(1, 2, figsize=(9.6, 4.4), sharey=True)163 a1.barh(lab, n, color=BLEUC); a1.set_title("Projets par secteur GICS"); a1.set_xlabel("projets")164 for i, v in enumerate(n):165 a1.text(v + 30, i, f"{v:,}".replace(",", " "), va="center", fontsize=8, color=GRIS)166 a1.set_xlim(0, max(n) * 1.15)167 a2.barh(lab, usd, color=OR); a2.set_title("Capital annoncé par secteur (G$ US)"); a2.set_xlabel("G$ US")168 for i, v in enumerate(usd):169 a2.text(v + 50, i, f"{v:,.0f}".replace(",", " "), va="center", fontsize=8, color=GRIS)170 a2.set_xlim(0, max(usd) * 1.18)171 save(fig, "fig_secteurs")172 with open(TAB / "tab_secteurs.tex", "w") as f:173 for r in rows:174 f.write(f"{SECT.get(r[0], r[0])} & {fr(r[3])} & {fr(r[1])} & {fr(r[1]/r[3],1)} & {fr(r[2]/1e9,1)} \\\\\n")175176177# ------------------------------------------------------------------ 5. heatmap secteur x type178def fig_heatmap():179 rows = q("select sector, project_type, count(*) from projects where sector is not null group by 1,2")180 sectors = [r[0] for r in q("select sector from projects where sector is not null group by 1 order by count(*) desc")]181 types = [r[0] for r in q("select project_type from projects group by 1 order by count(*) desc")]182 M = np.zeros((len(sectors), len(types)))183 for s, t, n in rows:184 M[sectors.index(s), types.index(t)] = n185 share = M / M.sum(axis=1, keepdims=True) * 100186 fig, ax = plt.subplots(figsize=(10.5, 5.2))187 im = ax.imshow(share, cmap="Blues", aspect="auto")188 ax.set_xticks(range(len(types))); ax.set_xticklabels([LABELS[t] for t in types], rotation=55, ha="right", fontsize=8)189 ax.set_yticks(range(len(sectors))); ax.set_yticklabels([SECT.get(s, s) for s in sectors], fontsize=8.5)190 for i in range(len(sectors)):191 for j in range(len(types)):192 v = share[i, j]193 if v >= 2:194 ax.text(j, i, f"{v:.0f}", ha="center", va="center", fontsize=6.5,195 color="white" if v > share.max() * 0.55 else GRIS)196 ax.grid(False)197 cb = fig.colorbar(im, ax=ax, fraction=0.025, pad=0.02); cb.set_label("% des projets du secteur")198 ax.set_title("Composition du portefeuille de projets par secteur (parts en % par ligne)")199 save(fig, "fig_heatmap_secteur_type")200201202# ------------------------------------------------------------------ 6. statut203def fig_statut():204 rows = q("select status, count(*) from projects group by 1 order by 2 desc")205 rows2 = q("""select project_type, status, count(*) from projects group by 1,2""")206 types = [r[0] for r in q("select project_type from projects group by 1 order by count(*) desc limit 12")]207 fig, (a1, a2) = plt.subplots(1, 2, figsize=(10, 4.2), gridspec_kw={"width_ratios": [1, 1.6]})208 vals = [r[1] for r in rows]; labs = [f"{STATUT[r[0]]}\n{r[1]:,}".replace(",", " ") for r in rows]209 a1.pie(vals, labels=labs, colors=[BLEU, VERT, OR, GRIS], startangle=90, counterclock=False,210 labeldistance=1.18, wedgeprops=dict(width=0.42, edgecolor="white"), textprops=dict(fontsize=8.5))211 a1.set_title("Statut des projets résolus"); a1.grid(False)212 order = ["planned", "in_progress", "completed", "mentioned"]213 cols = {"planned": OR, "in_progress": BLEU, "completed": VERT, "mentioned": GRIS}214 D = {t: {s: 0 for s in order} for t in types}215 for t, s, n in rows2:216 if t in D:217 D[t][s] = n218 left = np.zeros(len(types))219 for s in order:220 v = np.array([D[t][s] / sum(D[t].values()) * 100 for t in types])221 a2.barh([LABELS[t] for t in types], v, left=left, color=cols[s], label=STATUT[s])222 left += v223 a2.invert_yaxis(); a2.set_xlim(0, 100); a2.set_xlabel("% des projets du type")224 a2.legend(ncol=4, loc="lower center", bbox_to_anchor=(0.5, -0.32), fontsize=8)225 a2.set_title("Statut selon le type (12 types principaux)")226 save(fig, "fig_statut")227 stats["status"] = {r[0]: r[1] for r in rows}228229230# ------------------------------------------------------------------ 7. montants231def fig_montants():232 amts = np.array([r[0] for r in q("select total_amount_usd from projects where total_amount_usd > 0")])233 stats.update(n_with_amount=int(len(amts)), amount_median_musd=float(np.median(amts) / 1e6),234 amount_mean_musd=float(amts.mean() / 1e6), amount_p90_musd=float(np.percentile(amts, 90) / 1e6),235 amount_total_gusd=float(amts.sum() / 1e9), amount_max_gusd=float(amts.max() / 1e9))236 rows = q("""select project_type, quantile_cont(total_amount_usd, [0.25,0.5,0.75]) from projects237 where total_amount_usd>0 group by 1 order by median(total_amount_usd) desc""")238 fig, (a1, a2) = plt.subplots(1, 2, figsize=(10, 4.2), gridspec_kw={"width_ratios": [1, 1.3]})239 a1.hist(np.log10(amts), bins=45, color=BLEU, alpha=0.9)240 med = np.log10(np.median(amts))241 a1.axvline(med, color=OR, lw=2, label=f"médiane {np.median(amts)/1e6:,.0f} M$".replace(",", " "))242 a1.set_xticks(range(3, 12)); a1.set_xticklabels(["1 k", "10 k", "100 k", "1 M", "10 M", "100 M", "1 G", "10 G", "100 G"], fontsize=8)243 a1.set_xlabel("montant divulgué (US$, échelle log)"); a1.set_ylabel("projets"); a1.legend()244 a1.set_title(f"Distribution des montants ({len(amts):,} projets)".replace(",", " "))245 lab = [LABELS[r[0]] for r in rows]246 q1 = np.array([r[1][0] for r in rows]) / 1e6; q2 = np.array([r[1][1] for r in rows]) / 1e6; q3 = np.array([r[1][2] for r in rows]) / 1e6247 y = np.arange(len(lab))248 a2.hlines(y, q1, q3, color=BLEUC, lw=5, alpha=0.5, label="intervalle interquartile")249 a2.plot(q2, y, "o", color=BLEU, label="médiane")250 a2.set_yticks(y); a2.set_yticklabels(lab, fontsize=7.5); a2.invert_yaxis()251 a2.set_xscale("log"); a2.set_xlabel("montant divulgué par projet (M$ US, log)")252 a2.legend(loc="lower right", fontsize=8); a2.set_title("Montant médian et IQR par type")253 save(fig, "fig_montants")254255256# ------------------------------------------------------------------ 8. entreprises257def fig_entreprises():258 rows = q("""select ticker, company_name, sector, count(*), coalesce(sum(total_amount_usd),0)259 from projects group by 1,2,3 order by 4 desc limit 20""")260 fig, ax = plt.subplots(figsize=(8.5, 5.6))261 lab = [f"{r[1]} ({r[0]})" for r in rows][::-1]; n = [r[3] for r in rows][::-1]262 secs = [r[2] for r in rows][::-1]263 uniq = list(dict.fromkeys(secs)); cmap = {s: PAL[i % len(PAL)] for i, s in enumerate(uniq)}264 ax.barh(lab, n, color=[cmap[s] for s in secs])265 for i, v in enumerate(n):266 ax.text(v + 1, i, str(v), va="center", fontsize=8, color=GRIS)267 from matplotlib.patches import Patch268 ax.legend(handles=[Patch(color=cmap[s], label=SECT.get(s, s)) for s in uniq], fontsize=7.5, loc="lower right")269 ax.set_xlabel("projets résolus"); ax.set_title("Les 20 entreprises aux portefeuilles de projets les plus fournis")270 save(fig, "fig_entreprises")271 with open(TAB / "tab_entreprises.tex", "w") as f:272 for r in rows[:15]:273 f.write(f"{r[0]} & {T(r[1])} & {SECT.get(r[2], r[2])} & {fr(r[3])} & {fr(r[4]/1e9,1)} \\\\\n")274 dist = q("""with c as (select cik, count(*) n from projects group by 1)275 select min(n), median(n), avg(n), max(n) from c""")[0]276 stats["projects_per_firm"] = dict(min=dist[0], median=dist[1], mean=dist[2], max=dist[3])277278279# ------------------------------------------------------------------ 9. technologies + localisations280def fig_tech_loc():281 tech = q("""select lower(trim(t)) as t, count(*) from projects, unnest(technologies) as u(t)282 where t is not null and t <> '' group by 1 order by 2 desc limit 20""")283 loc = q("""select canonical_location, count(*) from projects284 where canonical_location is not null and canonical_location <> '' group by 1 order by 2 desc limit 20""")285 fig, (a1, a2) = plt.subplots(1, 2, figsize=(10.5, 5.4))286 a1.barh([r[0].capitalize() for r in tech][::-1], [r[1] for r in tech][::-1], color=TEAL)287 a1.set_title("20 technologies les plus citées"); a1.set_xlabel("projets")288 a2.barh([r[0] for r in loc][::-1], [r[1] for r in loc][::-1], color=ORANGE)289 a2.set_title("20 localisations canoniques les plus fréquentes"); a2.set_xlabel("projets")290 save(fig, "fig_tech_localisations")291 stats["n_tech_distinct"] = q("select count(distinct lower(trim(t))) from projects, unnest(technologies) as u(t)")[0][0]292 stats["n_loc_distinct"] = q("select count(distinct canonical_location) from projects where canonical_location<>''")[0][0]293 stats["share_with_location"] = q("select avg(case when canonical_location is not null and canonical_location<>'' then 1 else 0 end) from projects")[0][0]294 stats["share_with_tech"] = q("select avg(case when len(technologies)>0 then 1 else 0 end) from projects")[0][0]295296297# ------------------------------------------------------------------ 10. nouveaux projets par année + thèmes émergents298def fig_dynamique():299 rows = q("""select year(first_seen) y, count(*) from projects where first_seen between '2010-01-01' and '2026-12-31'300 group by 1 order by 1""")301 years = [r[0] for r in rows]; n = [r[1] for r in rows]302 themes = ["ai_initiative", "data_center", "cloud_migration", "digital_transformation", "sustainability", "energy_transition"]303 rows2 = q(f"""select year(filing_date) y, project_type, count(*) from project_mentions304 where project_type in ({','.join(repr(t) for t in themes)}) and filing_date between '2010-01-01' and '2026-12-31'305 group by 1,2 order by 1""")306 tot = dict(q("select year(filing_date), count(*) from project_mentions where filing_date between '2010-01-01' and '2026-12-31' group by 1"))307 fig, (a1, a2) = plt.subplots(1, 2, figsize=(10.5, 4))308 a1.bar(years, n, color=BLEU, width=0.78)309 for x, v in zip(years, n):310 a1.text(x, v - 40, f"{v:,}".replace(",", " "), ha="center", va="top", fontsize=6.5, color="white", rotation=90)311 a1.set_xticks(years); a1.tick_params(axis="x", labelrotation=45); a1.set_ylabel("projets")312 a1.set_title("Nouveaux projets par année de première mention")313 ys = sorted(tot)314 for t, c in zip(themes, [ROUGE, VIOLET, BLEUC, TEAL, VERT, OR]):315 d = {y: 0 for y in ys}316 for y, tt, k in rows2:317 if tt == t:318 d[y] = k319 a2.plot(ys, [d[y] / tot[y] * 100 for y in ys], marker="o", ms=3, color=c, label=LABELS[t])320 a2.set_ylabel("% des mentions de l'année"); a2.set_xticks(ys); a2.tick_params(axis="x", labelrotation=45)321 a2.legend(fontsize=7.5, ncol=2); a2.set_title("Thèmes émergents : part des mentions annuelles")322 save(fig, "fig_dynamique")323324325# ------------------------------------------------------------------ 11. durée de vie + mentions par projet + confiance326def fig_cycle():327 dur = np.array([r[0] for r in q("select last_year-first_year from projects where first_year is not null and last_year is not null and last_year>=first_year")])328 nm = np.array([r[0] for r in q("select n_mentions from projects")])329 conf = np.array([r[0] for r in q("select confidence from project_mentions where confidence is not null")])330 stats.update(dur_median=float(np.median(dur)), dur_mean=float(dur.mean()),331 share_single_year=float((dur == 0).mean()), share_5y_plus=float((dur >= 5).mean()),332 share_single_mention=float((nm == 1).mean()), conf_mean=float(conf.mean()),333 conf_median=float(np.median(conf)), share_conf_ge_08=float((conf >= 0.8).mean()))334 fig, (a1, a2, a3) = plt.subplots(1, 3, figsize=(11, 3.4))335 b = np.bincount(np.clip(dur, 0, 15))336 a1.bar(range(len(b)), b, color=BLEU); a1.set_xticks(range(0, 16, 3)); a1.set_xticklabels([str(i) if i < 15 else "15+" for i in range(0, 16, 3)])337 a1.set_xlabel("années entre première et dernière mention"); a1.set_ylabel("projets"); a1.set_title("Durée de suivi des projets")338 b2 = np.bincount(np.clip(nm, 1, 12))[1:]339 a2.bar(range(1, 13), b2, color=TEAL); a2.set_xticks(range(1, 13)); a2.set_xticklabels([str(i) if i < 12 else "12+" for i in range(1, 13)], fontsize=8)340 a2.set_xlabel("mentions fusionnées dans le projet"); a2.set_title("Mentions par projet"); a2.set_yscale("log")341 a3.hist(conf, bins=22, range=(0.45, 1.0), color=OR); a3.axvline(0.45, color=ROUGE, ls="--", lw=1, label="seuil 0,45")342 a3.set_xlabel("confiance attribuée par le modèle"); a3.set_title("Confiance des mentions"); a3.legend(fontsize=8)343 save(fig, "fig_cycle")344345346# ------------------------------------------------------------------ 12. sections 10-K ingérées347def fig_sections():348 rows = q("select v.section_name, count(*), avg(v.word_count), median(v.word_count) from extracted_sections e "349 "join v_sections v using(section_id) where v.form_type = '10-K' group by 1 order by 2 desc")350 noms = {"risk_factors": "Item 1A — Facteurs de risque", "business": "Item 1 — Activités", "md_and_a": "Item 7 — Rapport de gestion (MD&A)",351 "properties": "Item 2 — Propriétés", "quantitative_disclosures": "Item 7A — Risques de marché", "full_body": "Corps complet (repli)"}352 fig, (a1, a2) = plt.subplots(1, 2, figsize=(10, 3.6), sharey=True)353 lab = [noms.get(r[0], r[0]) for r in rows][::-1]354 a1.barh(lab, [r[1] for r in rows][::-1], color=BLEU); a1.set_title("Sections 10-K ingérées par SPID"); a1.set_xlabel("sections")355 a2.barh(lab, [r[3] for r in rows][::-1], color=OR); a2.set_title("Longueur médiane (mots)"); a2.set_xlabel("mots")356 a2.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"{int(x):,}".replace(",", " ")))357 save(fig, "fig_sections_10k")358 with open(TAB / "tab_sections.tex", "w") as f:359 for r in rows:360 f.write(f"\\code{{{r[0].replace('_','\\_')}}} & {T(noms.get(r[0], r[0]))} & {fr(r[1])} & {fr(r[2])} & {fr(r[3])} \\\\\n")361 s = q("select count(distinct v.accession_number), sum(v.word_count) from extracted_sections e "362 "join v_sections v using(section_id) where v.form_type = '10-K'")[0]363 stats.update(n_10k_filings_ingested=s[0], words_10k=s[1])364365366# ------------------------------------------------------------------ 13. graphe367def fig_graphe():368 nodes = q("select node_type, count(*) from kg_nodes group by 1 order by 2 desc")369 edges = q("select rel, count(*) from kg_edges group by 1 order by 2 desc")370 deg = q("""select d, count(*) from (select dst, count(*) d from kg_edges where rel in ('located_in','uses') group by 1)371 group by 1 order by 1""")372 fig, (a1, a2) = plt.subplots(1, 2, figsize=(10, 3.8), gridspec_kw={"width_ratios": [1, 1.1]})373 nl = {"Project": "Projet", "Partner": "Partenaire", "Location": "Localisation", "Technology": "Technologie", "Company": "Entreprise"}374 a1.barh([nl[r[0]] for r in nodes][::-1], [r[1] for r in nodes][::-1], color=[VIOLET, TEAL, ORANGE, OR, BLEU])375 for i, r in enumerate(nodes[::-1]):376 a1.text(r[1] + 200, i, f"{r[1]:,}".replace(",", " "), va="center", fontsize=8, color=GRIS)377 a1.set_xlim(0, nodes[0][1] * 1.2); a1.set_title("Nœuds du graphe par type")378 x = np.array([r[0] for r in deg]); y = np.array([r[1] for r in deg])379 a2.loglog(x, y, "o", ms=4, color=BLEU, alpha=0.8)380 a2.set_xlabel("degré entrant (projets rattachés)"); a2.set_ylabel("nombre de nœuds")381 a2.set_title("Distribution des degrés — localisations et technologies")382 save(fig, "fig_graphe")383 stats["kg_nodes"] = {r[0]: r[1] for r in nodes}; stats["kg_edges"] = {r[0]: r[1] for r in edges}384385386# ------------------------------------------------------------------ 14. tableau des plus grands projets387def tab_top_projets():388 rows = q("""select ticker, project_name, project_type, total_amount_usd, first_year, last_year, n_mentions389 from projects where total_amount_usd>0 and total_amount_usd < 3e11 and n_mentions>=3 and first_year is not null and project_type<>'capex_program' order by total_amount_usd desc limit 15""")390 with open(TAB / "tab_top_projets.tex", "w") as f:391 for r in rows:392 name = r[1].replace("&", "\\&").replace("%", "\\%").replace("$", "\\$").replace("_", "\\_")393 if len(name) > 52:394 name = name[:50] + "…"395 f.write(f"{r[0]} & {name} & {T(LABELS[r[2]])} & {fr(r[3]/1e9,1)} & {r[4]}--{r[5]} & {r[6]} \\\\\n")396 rows = q("""select ticker, project_name, project_type, status, first_year, last_year, n_mentions, n_filings397 from projects order by n_mentions desc limit 10""")398 with open(TAB / "tab_plus_suivis.tex", "w") as f:399 for r in rows:400 name = r[1].replace("&", "\\&").replace("%", "\\%").replace("$", "\\$").replace("_", "\\_")401 if len(name) > 48:402 name = name[:46] + "…"403 f.write(f"{r[0]} & {name} & {T(LABELS[r[2]])} & {STATUT[r[3]]} & {r[4]}--{r[5]} & {r[6]} & {r[7]} \\\\\n")404405406# ------------------------------------------------------------------ 15. exemples de mentions407def tab_exemples():408 rows = q("""select ticker, form_type, filing_date, project_type, project_name, description, amount_usd, confidence409 from project_mentions where confidence>=0.9 and amount_usd>1e8 and len(description)<230410 and project_type in ('data_center','ai_initiative','plant_construction','energy_transition','drug_pipeline','erp_implementation')411 order by hash(mention_id) limit 6""")412 esc = lambda s: s.replace("&", "\\&").replace("%", "\\%").replace("$", "\\$").replace("_", "\\_").replace("#", "\\#")413 with open(TAB / "tab_exemples.tex", "w") as f:414 for r in rows:415 f.write(f"{r[0]} & {r[1]} \\newline {r[2]} & {T(LABELS[r[3]])} & \\textbf{{{esc(r[4])}}} \\newline {esc(r[5])} & {fr(r[6]/1e6)} & {r[7]:.2f} \\\\\n")416417418def globals_stats():419 stats["n_ciks"] = q("select count(distinct cik) from projects")[0][0]420 stats["n_sectors"] = q("select count(distinct sector) from projects where sector is not null")[0][0]421 d = q("select min(filing_date), max(filing_date), count(distinct accession_number) from project_mentions")[0]422 stats.update(date_min=str(d[0]), date_max=str(d[1]), n_filings=d[2])423 stats["n_partners"] = q("select count(distinct lower(trim(p))) from project_mentions, unnest(partners) as u(p) where p<>''")[0][0]424 stats["embeddings"] = q("select model, dim, count(*) from project_embeddings group by 1,2")[0]425 # macros LaTeX426 with open(TAB / "macros.tex", "w") as f:427 f.write(f"\\newcommand{{\\nSections}}{{{fr(stats['n_sections'])}}}\n")428 f.write(f"\\newcommand{{\\nMentions}}{{{fr(stats['n_mentions'])}}}\n")429 f.write(f"\\newcommand{{\\nProjets}}{{{fr(stats['n_projects'])}}}\n")430 f.write(f"\\newcommand{{\\nFilings}}{{{fr(stats['n_filings'])}}}\n")431 f.write(f"\\newcommand{{\\nCiks}}{{{fr(stats['n_ciks'])}}}\n")432 f.write(f"\\newcommand{{\\nAvecMontant}}{{{fr(stats['n_with_amount'])}}}\n")433 f.write(f"\\newcommand{{\\pctAvecMontant}}{{{fr(100*stats['n_with_amount']/stats['n_projects'],1)}}}\n")434 f.write(f"\\newcommand{{\\medianeMontant}}{{{fr(stats['amount_median_musd'])}}}\n")435 f.write(f"\\newcommand{{\\moyenneMontant}}{{{fr(stats['amount_mean_musd'])}}}\n")436 f.write(f"\\newcommand{{\\totalMontant}}{{{fr(stats['amount_total_gusd'])}}}\n")437 f.write(f"\\newcommand{{\\nLoc}}{{{fr(stats['n_loc_distinct'])}}}\n")438 f.write(f"\\newcommand{{\\nTech}}{{{fr(stats['n_tech_distinct'])}}}\n")439 f.write(f"\\newcommand{{\\nPartenaires}}{{{fr(stats['n_partners'])}}}\n")440 f.write(f"\\newcommand{{\\nDixK}}{{{fr(stats['n_10k_filings_ingested'])}}}\n")441 f.write(f"\\newcommand{{\\nSectionsDixK}}{{{fr(stats['n_sections_10k'])}}}\n")442 f.write(f"\\newcommand{{\\motsDixK}}{{{fr(stats['words_10k']/1e6,0)}}}\n")443 f.write(f"\\newcommand{{\\confMoy}}{{{fr(stats['conf_mean'],2)}}}\n")444 f.write(f"\\newcommand{{\\pctConfHuit}}{{{fr(100*stats['share_conf_ge_08'],1)}}}\n")445 f.write(f"\\newcommand{{\\pctMonoMention}}{{{fr(100*stats['share_single_mention'],1)}}}\n")446 f.write(f"\\newcommand{{\\pctMonoAnnee}}{{{fr(100*stats['share_single_year'],1)}}}\n")447 f.write(f"\\newcommand{{\\pctCinqAns}}{{{fr(100*stats['share_5y_plus'],1)}}}\n")448 f.write(f"\\newcommand{{\\dureeMoy}}{{{fr(stats['dur_mean'],1)}}}\n")449 f.write(f"\\newcommand{{\\pctAvecLoc}}{{{fr(100*stats['share_with_location'],1)}}}\n")450 f.write(f"\\newcommand{{\\pctAvecTech}}{{{fr(100*stats['share_with_tech'],1)}}}\n")451 f.write(f"\\newcommand{{\\projParFirmeMed}}{{{fr(stats['projects_per_firm']['median'])}}}\n")452 f.write(f"\\newcommand{{\\projParFirmeMoy}}{{{fr(stats['projects_per_firm']['mean'],1)}}}\n")453 f.write(f"\\newcommand{{\\projParFirmeMax}}{{{fr(stats['projects_per_firm']['max'])}}}\n")454 f.write(f"\\newcommand{{\\nMentionsHuitK}}{{{fr(stats['mentions_by_form']['8-K'])}}}\n")455 f.write(f"\\newcommand{{\\nMentionsDixK}}{{{fr(stats['mentions_by_form']['10-K'])}}}\n")456 f.write(f"\\newcommand{{\\nMentionsDixQ}}{{{fr(stats['mentions_by_form']['10-Q'])}}}\n")457458459if __name__ == "__main__":460 print("DB :", DB)461 fig_entonnoir(); fig_mentions_annee(); fig_types(); fig_secteurs(); fig_heatmap(); fig_statut()462 fig_montants(); fig_entreprises(); fig_tech_loc(); fig_dynamique(); fig_cycle(); fig_sections(); fig_graphe()463 tab_top_projets(); tab_exemples(); globals_stats()464 (HERE / "stats.json").write_text(json.dumps(stats, indent=2, default=str, ensure_ascii=False))465 print(json.dumps(stats, indent=2, default=str, ensure_ascii=False))466