spb/uqo-research-db
Public
1# ============================================================2# SPID — generate figures & LaTeX tables for the showcase doc3# ../.venv/bin/python docs/make_figures.py4# ============================================================5from __future__ import annotations67import re8import sys9from pathlib import Path1011import matplotlib12matplotlib.use("Agg")13import matplotlib.pyplot as plt14import matplotlib.ticker as mticker15import networkx as nx1617sys.path.insert(0, str(Path(__file__).resolve().parent.parent))18from spid.store import Store # noqa: E4021920HERE = Path(__file__).resolve().parent21FIG = HERE / "figures"22TAB = HERE / "tables"23FIG.mkdir(exist_ok=True); TAB.mkdir(exist_ok=True)2425plt.rcParams.update({26 "font.family": "serif", "font.size": 10, "axes.titlesize": 11,27 "axes.titleweight": "bold", "axes.spines.top": False, "axes.spines.right": False,28 "figure.dpi": 150, "savefig.bbox": "tight", "axes.grid": True,29 "grid.alpha": 0.25, "grid.linewidth": 0.5,30})31NAVY = "#1f3b73"; TEAL = "#2a9d8f"; AMBER = "#e9a23b"; CRIM = "#b5374b"32LABELS = {33 "ma_integration": "M&A / Integration", "plant_construction": "Plant Construction",34 "rd_program": "R&D Program", "product_launch": "Product Launch",35 "manufacturing_expansion": "Mfg. Expansion", "partnership": "Partnership",36 "technology_deployment": "Tech Deployment", "digital_transformation": "Digital Transf.",37 "infrastructure": "Infrastructure", "capex_program": "Capex Program",38 "ai_initiative": "AI Initiative", "geographic_expansion": "Geo. Expansion",39 "cost_reduction": "Cost Reduction", "sustainability": "Sustainability",40 "energy_transition": "Energy Transition", "supply_chain": "Supply Chain",41 "drug_pipeline": "Drug Pipeline", "data_center": "Data Center",42 "cloud_migration": "Cloud Migration", "automation": "Automation",43 "erp_implementation": "ERP Implementation",44}454647def bn(x): # USD -> billions48 return (x or 0) / 1e9495051def fig_by_type(st):52 rows = st.con.execute("""53 SELECT project_type, COUNT(*) n, COALESCE(SUM(total_amount_usd),0) usd54 FROM projects GROUP BY 1 ORDER BY n DESC""").fetchall()55 types = [LABELS.get(r[0], r[0]) for r in rows][::-1]56 n = [r[1] for r in rows][::-1]57 usd = [bn(r[2]) for r in rows][::-1]58 fig, (a1, a2) = plt.subplots(1, 2, figsize=(9.2, 6.2), sharey=True)59 a1.barh(types, n, color=NAVY)60 a1.set_title("Projects by type (count)"); a1.set_xlabel("number of resolved projects")61 for i, v in enumerate(n):62 a1.text(v + max(n) * 0.01, i, str(v), va="center", fontsize=8)63 a2.barh(types, usd, color=TEAL)64 a2.set_title("Associated capital (USD bn)"); a2.set_xlabel("announced USD, billions")65 a2.tick_params(labelleft=False)66 fig.tight_layout(); fig.savefig(FIG / "by_type.pdf"); plt.close(fig)676869def fig_capex_year(st):70 rows = st.con.execute("""71 SELECT EXTRACT(year FROM first_seen)::INT y, COUNT(*) n,72 COALESCE(SUM(total_amount_usd),0) usd73 FROM projects WHERE first_seen IS NOT NULL GROUP BY 1 ORDER BY 1""").fetchall()74 yrs = [r[0] for r in rows]; n = [r[1] for r in rows]; usd = [bn(r[2]) for r in rows]75 fig, ax = plt.subplots(figsize=(8.6, 4.4))76 ax.bar(yrs, usd, color=AMBER, alpha=0.85, label="announced capital (USD bn)")77 ax.set_ylabel("announced capital (USD bn)"); ax.set_xlabel("year of first mention")78 ax.set_title("New projects detected per year and associated capital")79 ax2 = ax.twinx(); ax2.grid(False)80 ax2.plot(yrs, n, color=NAVY, marker="o", lw=2, label="projects started")81 ax2.set_ylabel("projects (count)", color=NAVY)82 ax2.tick_params(axis="y", colors=NAVY)83 ax.legend(loc="upper left", fontsize=8); ax2.legend(loc="upper right", fontsize=8)84 fig.tight_layout(); fig.savefig(FIG / "capex_year.pdf"); plt.close(fig)858687def fig_sector_tech(st):88 sec = st.con.execute("""89 SELECT COALESCE(sector,'(unknown)'), COUNT(*) FROM projects90 GROUP BY 1 ORDER BY 2 DESC LIMIT 9""").fetchall()91 tech = st.con.execute("""92 SELECT t, COUNT(*) FROM (SELECT unnest(technologies) t FROM projects)93 WHERE t<>'' GROUP BY 1 ORDER BY 2 DESC LIMIT 10""").fetchall()94 fig, (a1, a2) = plt.subplots(1, 2, figsize=(9.4, 4.4))95 s_lab = [r[0] for r in sec][::-1]; s_n = [r[1] for r in sec][::-1]96 a1.barh(s_lab, s_n, color=NAVY); a1.set_title("Projects by GICS sector")97 a1.set_xlabel("projects")98 t_lab = [r[0] for r in tech][::-1]; t_n = [r[1] for r in tech][::-1]99 a2.barh(t_lab, t_n, color=CRIM); a2.set_title("Projects by technology tag")100 a2.set_xlabel("projects")101 fig.tight_layout(); fig.savefig(FIG / "sector_tech.pdf"); plt.close(fig)102103104def fig_gantt(st):105 rows = st.con.execute("""106 SELECT ticker, project_name, first_seen, last_seen, n_filings, project_type107 FROM projects WHERE n_filings>=3 AND first_seen IS NOT NULL108 ORDER BY n_filings DESC, total_amount_usd DESC NULLS LAST LIMIT 16""").fetchall()109 rows = rows[::-1]110 fig, ax = plt.subplots(figsize=(9.2, 5.6))111 cmap = plt.get_cmap("tab20")112 for i, (tic, name, fs, ls, nf, pt) in enumerate(rows):113 start = fs.year + (fs.month - 1) / 12114 end = ls.year + (ls.month - 1) / 12115 ax.barh(i, max(end - start, 0.15), left=start, height=0.55,116 color=cmap(i % 20), alpha=0.9)117 ax.text(start - 0.15, i, f"{tic}: {name[:34]}", va="center", ha="right", fontsize=7.2)118 ax.text(end + 0.1, i, f"{nf}f", va="center", fontsize=7, color="#444")119 ax.set_yticks([]); ax.set_xlabel("year")120 ax.set_title("Project lifecycles reconstructed across filings (top multi-filing projects)")121 ax.set_xlim(2009, 2027.5); ax.margins(y=0.02)122 fig.tight_layout(); fig.savefig(FIG / "gantt.pdf"); plt.close(fig)123124125def fig_tesla_timeline(st):126 """Case study: extract GWh/MWh deployments from Tesla energy-storage snippets."""127 pid = st.con.execute("""128 SELECT project_id FROM projects WHERE ticker='TSLA'129 AND lower(project_name) LIKE '%energy storage%'130 ORDER BY n_filings DESC LIMIT 1""").fetchone()131 if not pid:132 return False133 tl = st.con.execute("""134 SELECT filing_date, snippet FROM project_timeline135 WHERE project_id=? ORDER BY filing_date""", [pid[0]]).fetchall()136 pts = []137 for d, snip in tl:138 m = re.search(r"([\d.]+)\s*(GWh|MWh)", snip, re.I)139 if m:140 v = float(m.group(1))141 if m.group(2).lower() == "mwh":142 v /= 1000.0143 pts.append((d, v, snip))144 if len(pts) < 3:145 return False146 fig, ax = plt.subplots(figsize=(8.6, 4.2))147 xs = [p[0] for p in pts]; ys = [p[1] for p in pts]148 ax.plot(xs, ys, color=TEAL, marker="o", lw=2)149 ax.fill_between(xs, ys, color=TEAL, alpha=0.12)150 ax.set_title("Case study — Tesla energy-storage deployments tracked filing-by-filing")151 ax.set_ylabel("energy storage deployed (GWh, as reported)")152 ax.set_xlabel("filing date")153 fig.autofmt_xdate()154 fig.tight_layout(); fig.savefig(FIG / "tesla_timeline.pdf"); plt.close(fig)155 return True156157158def fig_kg(st):159 """Ego knowledge graph for NVDA: company -> projects -> technologies/locations."""160 cik = st.con.execute("SELECT cik FROM projects WHERE ticker='NVDA' LIMIT 1").fetchone()161 if not cik:162 return163 cik = cik[0]164 projs = st.con.execute("""165 SELECT project_id, project_name, project_type, technologies, canonical_location166 FROM projects WHERE cik=? ORDER BY n_filings DESC, total_amount_usd DESC NULLS LAST167 LIMIT 9""", [cik]).fetchall()168 G = nx.Graph()169 comp = "NVIDIA"170 G.add_node(comp, kind="company")171 for pid, name, pt, techs, loc in projs:172 pn = name[:26]173 G.add_node(pn, kind="project"); G.add_edge(comp, pn)174 for t in (techs or [])[:2]:175 G.add_node(t, kind="tech"); G.add_edge(pn, t)176 if loc:177 ln = loc.title()[:18]178 G.add_node(ln, kind="loc"); G.add_edge(pn, ln)179 pos = nx.spring_layout(G, k=0.9, seed=7, iterations=200)180 kinds = nx.get_node_attributes(G, "kind")181 style = {"company": (NAVY, 2400), "project": (TEAL, 950),182 "tech": (CRIM, 600), "loc": (AMBER, 600)}183 fig, ax = plt.subplots(figsize=(9.2, 6.4))184 nx.draw_networkx_edges(G, pos, ax=ax, edge_color="#bbb", width=1.0)185 for kind, (col, size) in style.items():186 nodes = [n for n in G if kinds.get(n) == kind]187 nx.draw_networkx_nodes(G, pos, nodelist=nodes, node_color=col,188 node_size=size, ax=ax, alpha=0.92, edgecolors="white")189 nx.draw_networkx_labels(G, pos, font_size=7, ax=ax)190 ax.set_title("Knowledge-graph slice — NVIDIA: projects, technologies, locations")191 ax.axis("off")192 handles = [plt.Line2D([0], [0], marker="o", color="w", markerfacecolor=c,193 markersize=10, label=l) for l, (c, _) in194 zip(["Company", "Project", "Technology", "Location"], style.values())]195 ax.legend(handles=handles, loc="lower left", fontsize=8, frameon=True)196 fig.tight_layout(); fig.savefig(FIG / "kg_nvda.pdf"); plt.close(fig)197198199# ----------------------------- tables ---------------------------------200def _esc(s):201 return str(s).replace("&", "\\&").replace("%", "\\%").replace("_", "\\_").replace("$", "\\$")202203204def tab_top_ma(st):205 rows = st.con.execute("""206 SELECT ticker, project_name, status, total_amount_usd207 FROM projects WHERE project_type='ma_integration' AND total_amount_usd IS NOT NULL208 ORDER BY total_amount_usd DESC LIMIT 10""").fetchall()209 lines = [r"\begin{tabular}{lllr}", r"\toprule",210 r"Ticker & Project & Status & Value (USD bn) \\", r"\midrule"]211 for tic, name, status, usd in rows:212 lines.append(f"{_esc(tic)} & {_esc(name[:42])} & {_esc(status)} & {bn(usd):.1f} \\\\")213 lines += [r"\bottomrule", r"\end{tabular}"]214 (TAB / "top_ma.tex").write_text("\n".join(lines))215216217def tab_top_companies(st):218 rows = st.con.execute("""219 SELECT ticker, COALESCE(company_name,ticker), sector, COUNT(*) n,220 COALESCE(SUM(total_amount_usd),0) usd221 FROM projects GROUP BY 1,2,3 ORDER BY n DESC LIMIT 12""").fetchall()222 lines = [r"\begin{tabular}{lllrr}", r"\toprule",223 r"Ticker & Company & Sector & Projects & Capital (bn) \\", r"\midrule"]224 for tic, name, sec, n, usd in rows:225 lines.append(f"{_esc(tic)} & {_esc(name[:22])} & {_esc((sec or '')[:20])} & {n} & {bn(usd):.1f} \\\\")226 lines += [r"\bottomrule", r"\end{tabular}"]227 (TAB / "top_companies.tex").write_text("\n".join(lines))228229230def stats(st):231 o = {232 "projects": st.count("projects"),233 "mentions": st.count("project_mentions"),234 "companies": st.con.execute("SELECT COUNT(DISTINCT cik) FROM projects").fetchone()[0],235 "capex_bn": bn(st.con.execute("SELECT COALESCE(SUM(total_amount_usd),0) FROM projects").fetchone()[0]),236 "kg_nodes": st.count("kg_nodes"), "kg_edges": st.count("kg_edges"),237 "timeline": st.count("project_timeline"),238 "types": st.con.execute("SELECT COUNT(DISTINCT project_type) FROM projects").fetchone()[0],239 }240 macro = "\n".join([241 f"\\newcommand{{\\Nprojects}}{{{o['projects']}}}",242 f"\\newcommand{{\\Nmentions}}{{{o['mentions']}}}",243 f"\\newcommand{{\\Ncompanies}}{{{o['companies']}}}",244 f"\\newcommand{{\\Ncapex}}{{{o['capex_bn']:,.0f}}}",245 f"\\newcommand{{\\Nkgnodes}}{{{o['kg_nodes']}}}",246 f"\\newcommand{{\\Nkgedges}}{{{o['kg_edges']}}}",247 f"\\newcommand{{\\Ntimeline}}{{{o['timeline']}}}",248 f"\\newcommand{{\\Ntypes}}{{{o['types']}}}",249 ])250 (TAB / "stats.tex").write_text(macro)251 print("stats:", o)252253254def main():255 st = Store(read_only=True)256 try:257 fig_by_type(st); print("✓ by_type")258 fig_capex_year(st); print("✓ capex_year")259 fig_sector_tech(st); print("✓ sector_tech")260 fig_gantt(st); print("✓ gantt")261 ok = fig_tesla_timeline(st); print("✓ tesla_timeline" if ok else "… tesla skipped")262 fig_kg(st); print("✓ kg_nvda")263 tab_top_ma(st); tab_top_companies(st); print("✓ tables")264 stats(st)265 finally:266 st.close()267 print("Figures in", FIG)268269270if __name__ == "__main__":271 main()272