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 n_sec_10k = q("select count(*) from spid_sections")[0][0]87 n_sec = 153_894 # sections éligibles (>=30 mots) : 103 926 8-K + 27 164 10-K + 22 804 10-Q (journal d'extraction)88 n_men = q("select count(*) from project_mentions")[0][0]89 n_proj = q("select count(*) from projects")[0][0]90 n_fil = q("select count(distinct accession_number) from project_mentions")[0][0]91 stats.update(n_sections=n_sec, n_sections_10k=n_sec_10k, n_mentions=n_men, n_projects=n_proj,92 n_filings_with_mentions=n_fil)93 labels = ["Sections de\nfilings analysées", "Filings avec au\nmoins un projet", "Mentions de\nprojets extraites",94 "Projets\nrésolus"]95 vals = [n_sec, n_fil, n_men, n_proj]96 fig, ax = plt.subplots(figsize=(8, 3.6))97 bars = ax.barh(labels[::-1], vals[::-1], color=[OR, TEAL, BLEUC, BLEU])98 for b, v in zip(bars, vals[::-1]):99 ax.text(b.get_width() + n_sec * 0.01, b.get_y() + b.get_height() / 2, f"{v:,}".replace(",", " "),100 va="center", fontsize=10, fontweight="bold", color=GRIS)101 ax.set_xlim(0, n_sec * 1.18)102 ax.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"{int(x/1000)} k"))103 ax.set_xlabel("nombre d'unités")104 ax.set_title("De la section de filing au projet résolu")105 save(fig, "fig_entonnoir")106107108# ------------------------------------------------------------------ 2. mentions par année / formulaire109def fig_mentions_annee():110 rows = q("""select year(filing_date) y, form_type, count(*) from project_mentions111 where filing_date between '2010-01-01' and '2026-12-31' group by 1,2 order by 1,2""")112 years = sorted({r[0] for r in rows})113 forms = ["10-K", "10-Q", "8-K"]114 data = {f: [0] * len(years) for f in forms}115 for y, f, n in rows:116 data[f][years.index(y)] = n117 fig, ax = plt.subplots(figsize=(8.5, 3.8))118 bottom = np.zeros(len(years))119 for f, c in zip(forms, [BLEU, OR, TEAL]):120 ax.bar(years, data[f], bottom=bottom, color=c, label=f, width=0.78)121 bottom += np.array(data[f])122 for x, t in zip(years, bottom):123 ax.text(x, t + 30, f"{int(t):,}".replace(",", " "), ha="center", fontsize=7, color=GRIS)124 ax.set_xticks(years); ax.tick_params(axis="x", labelrotation=45)125 ax.set_ylabel("mentions de projets"); ax.legend(title="Formulaire", ncol=3, loc="upper left")126 ax.set_title("Mentions de projets par année de dépôt et par formulaire")127 save(fig, "fig_mentions_annee")128 stats["mentions_by_form"] = {f: sum(v) for f, v in data.items()}129130131# ------------------------------------------------------------------ 3. types132def fig_types():133 rows = q("""select project_type, count(*), coalesce(sum(total_amount_usd),0), median(total_amount_usd)134 from projects group by 1 order by 2 desc""")135 lab = [LABELS[r[0]] for r in rows][::-1]136 n = [r[1] for r in rows][::-1]137 usd = [r[2] / 1e9 for r in rows][::-1]138 fig, (a1, a2) = plt.subplots(1, 2, figsize=(9.6, 6), sharey=True)139 a1.barh(lab, n, color=BLEU)140 for i, v in enumerate(n):141 a1.text(v + 40, i, f"{v:,}".replace(",", " "), va="center", fontsize=7.5, color=GRIS)142 a1.set_title("Nombre de projets"); a1.set_xlabel("projets résolus"); a1.set_xlim(0, max(n) * 1.15)143 a2.barh(lab, usd, color=OR)144 for i, v in enumerate(usd):145 a2.text(v + 100, i, f"{v:,.0f}".replace(",", " "), va="center", fontsize=7.5, color=GRIS)146 a2.set_title("Capital annoncé (G$ US)"); a2.set_xlabel("somme des montants divulgués"); a2.set_xlim(0, max(usd) * 1.18)147 fig.suptitle("Les 21 types de projets de la taxonomie SPID", fontweight="bold", y=1.0)148 save(fig, "fig_types")149 with open(TAB / "tab_types.tex", "w") as f:150 for r in rows:151 med = f"{r[3]/1e6:,.0f}".replace(",", "\\,") if r[3] else "---"152 f.write(f"\\code{{{r[0].replace('_', '\\_')}}} & {T(LABELS[r[0]])} & {fr(r[1])} & {fr(r[2]/1e9,1)} & {med} \\\\\n")153154155# ------------------------------------------------------------------ 4. secteurs156def fig_secteurs():157 rows = q("""select sector, count(*), coalesce(sum(total_amount_usd),0), count(distinct cik),158 avg(total_amount_usd) from projects where sector is not null group by 1 order by 2 desc""")159 lab = [SECT.get(r[0], r[0]) for r in rows][::-1]160 n = [r[1] for r in rows][::-1]; usd = [r[2] / 1e9 for r in rows][::-1]161 fig, (a1, a2) = plt.subplots(1, 2, figsize=(9.6, 4.4), sharey=True)162 a1.barh(lab, n, color=BLEUC); a1.set_title("Projets par secteur GICS"); a1.set_xlabel("projets")163 for i, v in enumerate(n):164 a1.text(v + 30, i, f"{v:,}".replace(",", " "), va="center", fontsize=8, color=GRIS)165 a1.set_xlim(0, max(n) * 1.15)166 a2.barh(lab, usd, color=OR); a2.set_title("Capital annoncé par secteur (G$ US)"); a2.set_xlabel("G$ US")167 for i, v in enumerate(usd):168 a2.text(v + 50, i, f"{v:,.0f}".replace(",", " "), va="center", fontsize=8, color=GRIS)169 a2.set_xlim(0, max(usd) * 1.18)170 save(fig, "fig_secteurs")171 with open(TAB / "tab_secteurs.tex", "w") as f:172 for r in rows:173 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")174175176# ------------------------------------------------------------------ 5. heatmap secteur x type177def fig_heatmap():178 rows = q("select sector, project_type, count(*) from projects where sector is not null group by 1,2")179 sectors = [r[0] for r in q("select sector from projects where sector is not null group by 1 order by count(*) desc")]180 types = [r[0] for r in q("select project_type from projects group by 1 order by count(*) desc")]181 M = np.zeros((len(sectors), len(types)))182 for s, t, n in rows:183 M[sectors.index(s), types.index(t)] = n184 share = M / M.sum(axis=1, keepdims=True) * 100185 fig, ax = plt.subplots(figsize=(10.5, 5.2))186 im = ax.imshow(share, cmap="Blues", aspect="auto")187 ax.set_xticks(range(len(types))); ax.set_xticklabels([LABELS[t] for t in types], rotation=55, ha="right", fontsize=8)188 ax.set_yticks(range(len(sectors))); ax.set_yticklabels([SECT.get(s, s) for s in sectors], fontsize=8.5)189 for i in range(len(sectors)):190 for j in range(len(types)):191 v = share[i, j]192 if v >= 2:193 ax.text(j, i, f"{v:.0f}", ha="center", va="center", fontsize=6.5,194 color="white" if v > share.max() * 0.55 else GRIS)195 ax.grid(False)196 cb = fig.colorbar(im, ax=ax, fraction=0.025, pad=0.02); cb.set_label("% des projets du secteur")197 ax.set_title("Composition du portefeuille de projets par secteur (parts en % par ligne)")198 save(fig, "fig_heatmap_secteur_type")199200201# ------------------------------------------------------------------ 6. statut202def fig_statut():203 rows = q("select status, count(*) from projects group by 1 order by 2 desc")204 rows2 = q("""select project_type, status, count(*) from projects group by 1,2""")205 types = [r[0] for r in q("select project_type from projects group by 1 order by count(*) desc limit 12")]206 fig, (a1, a2) = plt.subplots(1, 2, figsize=(10, 4.2), gridspec_kw={"width_ratios": [1, 1.6]})207 vals = [r[1] for r in rows]; labs = [f"{STATUT[r[0]]}\n{r[1]:,}".replace(",", " ") for r in rows]208 a1.pie(vals, labels=labs, colors=[BLEU, VERT, OR, GRIS], startangle=90, counterclock=False,209 labeldistance=1.18, wedgeprops=dict(width=0.42, edgecolor="white"), textprops=dict(fontsize=8.5))210 a1.set_title("Statut des projets résolus"); a1.grid(False)211 order = ["planned", "in_progress", "completed", "mentioned"]212 cols = {"planned": OR, "in_progress": BLEU, "completed": VERT, "mentioned": GRIS}213 D = {t: {s: 0 for s in order} for t in types}214 for t, s, n in rows2:215 if t in D:216 D[t][s] = n217 left = np.zeros(len(types))218 for s in order:219 v = np.array([D[t][s] / sum(D[t].values()) * 100 for t in types])220 a2.barh([LABELS[t] for t in types], v, left=left, color=cols[s], label=STATUT[s])221 left += v222 a2.invert_yaxis(); a2.set_xlim(0, 100); a2.set_xlabel("% des projets du type")223 a2.legend(ncol=4, loc="lower center", bbox_to_anchor=(0.5, -0.32), fontsize=8)224 a2.set_title("Statut selon le type (12 types principaux)")225 save(fig, "fig_statut")226 stats["status"] = {r[0]: r[1] for r in rows}227228229# ------------------------------------------------------------------ 7. montants230def fig_montants():231 amts = np.array([r[0] for r in q("select total_amount_usd from projects where total_amount_usd > 0")])232 stats.update(n_with_amount=int(len(amts)), amount_median_musd=float(np.median(amts) / 1e6),233 amount_mean_musd=float(amts.mean() / 1e6), amount_p90_musd=float(np.percentile(amts, 90) / 1e6),234 amount_total_gusd=float(amts.sum() / 1e9), amount_max_gusd=float(amts.max() / 1e9))235 rows = q("""select project_type, quantile_cont(total_amount_usd, [0.25,0.5,0.75]) from projects236 where total_amount_usd>0 group by 1 order by median(total_amount_usd) desc""")237 fig, (a1, a2) = plt.subplots(1, 2, figsize=(10, 4.2), gridspec_kw={"width_ratios": [1, 1.3]})238 a1.hist(np.log10(amts), bins=45, color=BLEU, alpha=0.9)239 med = np.log10(np.median(amts))240 a1.axvline(med, color=OR, lw=2, label=f"médiane {np.median(amts)/1e6:,.0f} M$".replace(",", " "))241 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)242 a1.set_xlabel("montant divulgué (US$, échelle log)"); a1.set_ylabel("projets"); a1.legend()243 a1.set_title(f"Distribution des montants ({len(amts):,} projets)".replace(",", " "))244 lab = [LABELS[r[0]] for r in rows]245 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]) / 1e6246 y = np.arange(len(lab))247 a2.hlines(y, q1, q3, color=BLEUC, lw=5, alpha=0.5, label="intervalle interquartile")248 a2.plot(q2, y, "o", color=BLEU, label="médiane")249 a2.set_yticks(y); a2.set_yticklabels(lab, fontsize=7.5); a2.invert_yaxis()250 a2.set_xscale("log"); a2.set_xlabel("montant divulgué par projet (M$ US, log)")251 a2.legend(loc="lower right", fontsize=8); a2.set_title("Montant médian et IQR par type")252 save(fig, "fig_montants")253254255# ------------------------------------------------------------------ 8. entreprises256def fig_entreprises():257 rows = q("""select ticker, company_name, sector, count(*), coalesce(sum(total_amount_usd),0)258 from projects group by 1,2,3 order by 4 desc limit 20""")259 fig, ax = plt.subplots(figsize=(8.5, 5.6))260 lab = [f"{r[1]} ({r[0]})" for r in rows][::-1]; n = [r[3] for r in rows][::-1]261 secs = [r[2] for r in rows][::-1]262 uniq = list(dict.fromkeys(secs)); cmap = {s: PAL[i % len(PAL)] for i, s in enumerate(uniq)}263 ax.barh(lab, n, color=[cmap[s] for s in secs])264 for i, v in enumerate(n):265 ax.text(v + 1, i, str(v), va="center", fontsize=8, color=GRIS)266 from matplotlib.patches import Patch267 ax.legend(handles=[Patch(color=cmap[s], label=SECT.get(s, s)) for s in uniq], fontsize=7.5, loc="lower right")268 ax.set_xlabel("projets résolus"); ax.set_title("Les 20 entreprises aux portefeuilles de projets les plus fournis")269 save(fig, "fig_entreprises")270 with open(TAB / "tab_entreprises.tex", "w") as f:271 for r in rows[:15]:272 f.write(f"{r[0]} & {T(r[1])} & {SECT.get(r[2], r[2])} & {fr(r[3])} & {fr(r[4]/1e9,1)} \\\\\n")273 dist = q("""with c as (select cik, count(*) n from projects group by 1)274 select min(n), median(n), avg(n), max(n) from c""")[0]275 stats["projects_per_firm"] = dict(min=dist[0], median=dist[1], mean=dist[2], max=dist[3])276277278# ------------------------------------------------------------------ 9. technologies + localisations279def fig_tech_loc():280 tech = q("""select lower(trim(t)) as t, count(*) from projects, unnest(technologies) as u(t)281 where t is not null and t <> '' group by 1 order by 2 desc limit 20""")282 loc = q("""select canonical_location, count(*) from projects283 where canonical_location is not null and canonical_location <> '' group by 1 order by 2 desc limit 20""")284 fig, (a1, a2) = plt.subplots(1, 2, figsize=(10.5, 5.4))285 a1.barh([r[0].capitalize() for r in tech][::-1], [r[1] for r in tech][::-1], color=TEAL)286 a1.set_title("20 technologies les plus citées"); a1.set_xlabel("projets")287 a2.barh([r[0] for r in loc][::-1], [r[1] for r in loc][::-1], color=ORANGE)288 a2.set_title("20 localisations canoniques les plus fréquentes"); a2.set_xlabel("projets")289 save(fig, "fig_tech_localisations")290 stats["n_tech_distinct"] = q("select count(distinct lower(trim(t))) from projects, unnest(technologies) as u(t)")[0][0]291 stats["n_loc_distinct"] = q("select count(distinct canonical_location) from projects where canonical_location<>''")[0][0]292 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]293 stats["share_with_tech"] = q("select avg(case when len(technologies)>0 then 1 else 0 end) from projects")[0][0]294295296# ------------------------------------------------------------------ 10. nouveaux projets par année + thèmes émergents297def fig_dynamique():298 rows = q("""select year(first_seen) y, count(*) from projects where first_seen between '2010-01-01' and '2026-12-31'299 group by 1 order by 1""")300 years = [r[0] for r in rows]; n = [r[1] for r in rows]301 themes = ["ai_initiative", "data_center", "cloud_migration", "digital_transformation", "sustainability", "energy_transition"]302 rows2 = q(f"""select year(filing_date) y, project_type, count(*) from project_mentions303 where project_type in ({','.join(repr(t) for t in themes)}) and filing_date between '2010-01-01' and '2026-12-31'304 group by 1,2 order by 1""")305 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"))306 fig, (a1, a2) = plt.subplots(1, 2, figsize=(10.5, 4))307 a1.bar(years, n, color=BLEU, width=0.78)308 for x, v in zip(years, n):309 a1.text(x, v - 40, f"{v:,}".replace(",", " "), ha="center", va="top", fontsize=6.5, color="white", rotation=90)310 a1.set_xticks(years); a1.tick_params(axis="x", labelrotation=45); a1.set_ylabel("projets")311 a1.set_title("Nouveaux projets par année de première mention")312 ys = sorted(tot)313 for t, c in zip(themes, [ROUGE, VIOLET, BLEUC, TEAL, VERT, OR]):314 d = {y: 0 for y in ys}315 for y, tt, k in rows2:316 if tt == t:317 d[y] = k318 a2.plot(ys, [d[y] / tot[y] * 100 for y in ys], marker="o", ms=3, color=c, label=LABELS[t])319 a2.set_ylabel("% des mentions de l'année"); a2.set_xticks(ys); a2.tick_params(axis="x", labelrotation=45)320 a2.legend(fontsize=7.5, ncol=2); a2.set_title("Thèmes émergents : part des mentions annuelles")321 save(fig, "fig_dynamique")322323324# ------------------------------------------------------------------ 11. durée de vie + mentions par projet + confiance325def fig_cycle():326 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")])327 nm = np.array([r[0] for r in q("select n_mentions from projects")])328 conf = np.array([r[0] for r in q("select confidence from project_mentions where confidence is not null")])329 stats.update(dur_median=float(np.median(dur)), dur_mean=float(dur.mean()),330 share_single_year=float((dur == 0).mean()), share_5y_plus=float((dur >= 5).mean()),331 share_single_mention=float((nm == 1).mean()), conf_mean=float(conf.mean()),332 conf_median=float(np.median(conf)), share_conf_ge_08=float((conf >= 0.8).mean()))333 fig, (a1, a2, a3) = plt.subplots(1, 3, figsize=(11, 3.4))334 b = np.bincount(np.clip(dur, 0, 15))335 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)])336 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")337 b2 = np.bincount(np.clip(nm, 1, 12))[1:]338 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)339 a2.set_xlabel("mentions fusionnées dans le projet"); a2.set_title("Mentions par projet"); a2.set_yscale("log")340 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")341 a3.set_xlabel("confiance attribuée par le modèle"); a3.set_title("Confiance des mentions"); a3.legend(fontsize=8)342 save(fig, "fig_cycle")343344345# ------------------------------------------------------------------ 12. sections 10-K ingérées346def fig_sections():347 rows = q("select section_name, count(*), avg(word_count), median(word_count) from spid_sections group by 1 order by 2 desc")348 noms = {"risk_factors": "Item 1A — Facteurs de risque", "business": "Item 1 — Activités", "md_and_a": "Item 7 — Rapport de gestion (MD&A)",349 "properties": "Item 2 — Propriétés", "quantitative_disclosures": "Item 7A — Risques de marché", "full_body": "Corps complet (repli)"}350 fig, (a1, a2) = plt.subplots(1, 2, figsize=(10, 3.6), sharey=True)351 lab = [noms.get(r[0], r[0]) for r in rows][::-1]352 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")353 a2.barh(lab, [r[3] for r in rows][::-1], color=OR); a2.set_title("Longueur médiane (mots)"); a2.set_xlabel("mots")354 a2.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"{int(x):,}".replace(",", " ")))355 save(fig, "fig_sections_10k")356 with open(TAB / "tab_sections.tex", "w") as f:357 for r in rows:358 f.write(f"\\code{{{r[0].replace('_','\\_')}}} & {T(noms.get(r[0], r[0]))} & {fr(r[1])} & {fr(r[2])} & {fr(r[3])} \\\\\n")359 s = q("select count(distinct accession_number), sum(word_count) from spid_sections")[0]360 stats.update(n_10k_filings_ingested=s[0], words_10k=s[1])361362363# ------------------------------------------------------------------ 13. graphe364def fig_graphe():365 nodes = q("select node_type, count(*) from kg_nodes group by 1 order by 2 desc")366 edges = q("select rel, count(*) from kg_edges group by 1 order by 2 desc")367 deg = q("""select d, count(*) from (select dst, count(*) d from kg_edges where rel in ('located_in','uses') group by 1)368 group by 1 order by 1""")369 fig, (a1, a2) = plt.subplots(1, 2, figsize=(10, 3.8), gridspec_kw={"width_ratios": [1, 1.1]})370 nl = {"Project": "Projet", "Partner": "Partenaire", "Location": "Localisation", "Technology": "Technologie", "Company": "Entreprise"}371 a1.barh([nl[r[0]] for r in nodes][::-1], [r[1] for r in nodes][::-1], color=[VIOLET, TEAL, ORANGE, OR, BLEU])372 for i, r in enumerate(nodes[::-1]):373 a1.text(r[1] + 200, i, f"{r[1]:,}".replace(",", " "), va="center", fontsize=8, color=GRIS)374 a1.set_xlim(0, nodes[0][1] * 1.2); a1.set_title("Nœuds du graphe par type")375 x = np.array([r[0] for r in deg]); y = np.array([r[1] for r in deg])376 a2.loglog(x, y, "o", ms=4, color=BLEU, alpha=0.8)377 a2.set_xlabel("degré entrant (projets rattachés)"); a2.set_ylabel("nombre de nœuds")378 a2.set_title("Distribution des degrés — localisations et technologies")379 save(fig, "fig_graphe")380 stats["kg_nodes"] = {r[0]: r[1] for r in nodes}; stats["kg_edges"] = {r[0]: r[1] for r in edges}381382383# ------------------------------------------------------------------ 14. tableau des plus grands projets384def tab_top_projets():385 rows = q("""select ticker, project_name, project_type, total_amount_usd, first_year, last_year, n_mentions386 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""")387 with open(TAB / "tab_top_projets.tex", "w") as f:388 for r in rows:389 name = r[1].replace("&", "\\&").replace("%", "\\%").replace("$", "\\$").replace("_", "\\_")390 if len(name) > 52:391 name = name[:50] + "…"392 f.write(f"{r[0]} & {name} & {T(LABELS[r[2]])} & {fr(r[3]/1e9,1)} & {r[4]}--{r[5]} & {r[6]} \\\\\n")393 rows = q("""select ticker, project_name, project_type, status, first_year, last_year, n_mentions, n_filings394 from projects order by n_mentions desc limit 10""")395 with open(TAB / "tab_plus_suivis.tex", "w") as f:396 for r in rows:397 name = r[1].replace("&", "\\&").replace("%", "\\%").replace("$", "\\$").replace("_", "\\_")398 if len(name) > 48:399 name = name[:46] + "…"400 f.write(f"{r[0]} & {name} & {T(LABELS[r[2]])} & {STATUT[r[3]]} & {r[4]}--{r[5]} & {r[6]} & {r[7]} \\\\\n")401402403# ------------------------------------------------------------------ 15. exemples de mentions404def tab_exemples():405 rows = q("""select ticker, form_type, filing_date, project_type, project_name, description, amount_usd, confidence406 from project_mentions where confidence>=0.9 and amount_usd>1e8 and len(description)<230407 and project_type in ('data_center','ai_initiative','plant_construction','energy_transition','drug_pipeline','erp_implementation')408 order by hash(mention_id) limit 6""")409 esc = lambda s: s.replace("&", "\\&").replace("%", "\\%").replace("$", "\\$").replace("_", "\\_").replace("#", "\\#")410 with open(TAB / "tab_exemples.tex", "w") as f:411 for r in rows:412 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")413414415def globals_stats():416 stats["n_ciks"] = q("select count(distinct cik) from projects")[0][0]417 stats["n_sectors"] = q("select count(distinct sector) from projects where sector is not null")[0][0]418 d = q("select min(filing_date), max(filing_date), count(distinct accession_number) from project_mentions")[0]419 stats.update(date_min=str(d[0]), date_max=str(d[1]), n_filings=d[2])420 stats["n_partners"] = q("select count(distinct lower(trim(p))) from project_mentions, unnest(partners) as u(p) where p<>''")[0][0]421 stats["embeddings"] = q("select model, dim, count(*) from project_embeddings group by 1,2")[0]422 # macros LaTeX423 with open(TAB / "macros.tex", "w") as f:424 f.write(f"\\newcommand{{\\nSections}}{{{fr(stats['n_sections'])}}}\n")425 f.write(f"\\newcommand{{\\nMentions}}{{{fr(stats['n_mentions'])}}}\n")426 f.write(f"\\newcommand{{\\nProjets}}{{{fr(stats['n_projects'])}}}\n")427 f.write(f"\\newcommand{{\\nFilings}}{{{fr(stats['n_filings'])}}}\n")428 f.write(f"\\newcommand{{\\nCiks}}{{{fr(stats['n_ciks'])}}}\n")429 f.write(f"\\newcommand{{\\nAvecMontant}}{{{fr(stats['n_with_amount'])}}}\n")430 f.write(f"\\newcommand{{\\pctAvecMontant}}{{{fr(100*stats['n_with_amount']/stats['n_projects'],1)}}}\n")431 f.write(f"\\newcommand{{\\medianeMontant}}{{{fr(stats['amount_median_musd'])}}}\n")432 f.write(f"\\newcommand{{\\moyenneMontant}}{{{fr(stats['amount_mean_musd'])}}}\n")433 f.write(f"\\newcommand{{\\totalMontant}}{{{fr(stats['amount_total_gusd'])}}}\n")434 f.write(f"\\newcommand{{\\nLoc}}{{{fr(stats['n_loc_distinct'])}}}\n")435 f.write(f"\\newcommand{{\\nTech}}{{{fr(stats['n_tech_distinct'])}}}\n")436 f.write(f"\\newcommand{{\\nPartenaires}}{{{fr(stats['n_partners'])}}}\n")437 f.write(f"\\newcommand{{\\nDixK}}{{{fr(stats['n_10k_filings_ingested'])}}}\n")438 f.write(f"\\newcommand{{\\nSectionsDixK}}{{{fr(stats['n_sections_10k'])}}}\n")439 f.write(f"\\newcommand{{\\motsDixK}}{{{fr(stats['words_10k']/1e6,0)}}}\n")440 f.write(f"\\newcommand{{\\confMoy}}{{{fr(stats['conf_mean'],2)}}}\n")441 f.write(f"\\newcommand{{\\pctConfHuit}}{{{fr(100*stats['share_conf_ge_08'],1)}}}\n")442 f.write(f"\\newcommand{{\\pctMonoMention}}{{{fr(100*stats['share_single_mention'],1)}}}\n")443 f.write(f"\\newcommand{{\\pctMonoAnnee}}{{{fr(100*stats['share_single_year'],1)}}}\n")444 f.write(f"\\newcommand{{\\pctCinqAns}}{{{fr(100*stats['share_5y_plus'],1)}}}\n")445 f.write(f"\\newcommand{{\\dureeMoy}}{{{fr(stats['dur_mean'],1)}}}\n")446 f.write(f"\\newcommand{{\\pctAvecLoc}}{{{fr(100*stats['share_with_location'],1)}}}\n")447 f.write(f"\\newcommand{{\\pctAvecTech}}{{{fr(100*stats['share_with_tech'],1)}}}\n")448 f.write(f"\\newcommand{{\\projParFirmeMed}}{{{fr(stats['projects_per_firm']['median'])}}}\n")449 f.write(f"\\newcommand{{\\projParFirmeMoy}}{{{fr(stats['projects_per_firm']['mean'],1)}}}\n")450 f.write(f"\\newcommand{{\\projParFirmeMax}}{{{fr(stats['projects_per_firm']['max'])}}}\n")451 f.write(f"\\newcommand{{\\nMentionsHuitK}}{{{fr(stats['mentions_by_form']['8-K'])}}}\n")452 f.write(f"\\newcommand{{\\nMentionsDixK}}{{{fr(stats['mentions_by_form']['10-K'])}}}\n")453 f.write(f"\\newcommand{{\\nMentionsDixQ}}{{{fr(stats['mentions_by_form']['10-Q'])}}}\n")454455456if __name__ == "__main__":457 print("DB :", DB)458 fig_entonnoir(); fig_mentions_annee(); fig_types(); fig_secteurs(); fig_heatmap(); fig_statut()459 fig_montants(); fig_entreprises(); fig_tech_loc(); fig_dynamique(); fig_cycle(); fig_sections(); fig_graphe()460 tab_top_projets(); tab_exemples(); globals_stats()461 (HERE / "stats.json").write_text(json.dumps(stats, indent=2, default=str, ensure_ascii=False))462 print(json.dumps(stats, indent=2, default=str, ensure_ascii=False))463