spb/uqo-research-db

1"""Descriptive statistics, summary tables, and exploratory figures."""2from __future__ import annotations34import numpy as np5import pandas as pd67from common import DATA, set_style, savefig, write_table, save_results, esc8import matplotlib.pyplot as plt910set_style()1112proj = pd.read_parquet(DATA / "projects.parquet")13men = pd.read_parquet(DATA / "mentions.parquet")14panel = pd.read_parquet(DATA / "panel.parquet")1516TYPE_LABEL = {17    "ma_integration": "M&A integration", "capex_program": "Capex program",18    "plant_construction": "Plant construction", "partnership": "Partnership",19    "cost_reduction": "Cost reduction", "geographic_expansion": "Geographic expansion",20    "infrastructure": "Infrastructure", "product_launch": "Product launch",21    "technology_deployment": "Technology deployment", "manufacturing_expansion": "Mfg. expansion",22    "rd_program": "R&D program", "ai_initiative": "AI initiative",23    "digital_transformation": "Digital transformation", "sustainability": "Sustainability",24    "supply_chain": "Supply chain", "data_center": "Data center",25    "energy_transition": "Energy transition", "cloud_migration": "Cloud migration",26    "automation": "Automation", "drug_pipeline": "Drug pipeline",27    "erp_implementation": "ERP implementation",28}2930# ---- Figure 1: mentions per year by form ----------------------------------31mp = (men[(men.year >= 2010) & (men.year <= 2026)]32      .groupby(["year", "form_type"]).size().unstack(fill_value=0))33fig, ax = plt.subplots(figsize=(7.2, 4.0))34mp.plot(ax=ax, marker="o", ms=3.5, lw=1.6)35ax.set_xlabel("Filing year"); ax.set_ylabel("Project mentions")36ax.set_title("Project mentions extracted per year, by filing form")37ax.legend(title="Form")38savefig(fig, "fig_mentions_by_year.pdf")3940# ---- Figure 2: project type distribution + announced capital --------------41bt = (proj.groupby("project_type")42      .agg(projects=("project_id", "count"),43           capex=("total_amount_usd", lambda s: np.nansum(s.values)))44      .sort_values("projects", ascending=True))45bt.index = [TYPE_LABEL.get(i, i) for i in bt.index]46fig, ax = plt.subplots(figsize=(7.4, 6.2))47ax.barh(bt.index, bt["projects"], color="#1b3a5b")48ax.set_xlabel("Number of resolved projects")49ax.set_title("Distribution of projects across the 21-category taxonomy")50for y, (n, cap) in enumerate(zip(bt["projects"], bt["capex"])):51    ax.text(n + 60, y, f"{n:,}", va="center", fontsize=8)52savefig(fig, "fig_type_distribution.pdf")5354# ---- Figure 3: sector x type heatmap (row-normalised share) ---------------55top_types = proj["project_type"].value_counts().head(12).index56ct = (proj[proj.project_type.isin(top_types)]57      .pivot_table(index="sector", columns="project_type", values="project_id",58                   aggfunc="count", fill_value=0))59ct = ct.div(ct.sum(axis=1), axis=0)60ct = ct.rename(columns=TYPE_LABEL)61fig, ax = plt.subplots(figsize=(9.0, 5.2))62im = ax.imshow(ct.values, cmap="magma_r", aspect="auto")63ax.set_xticks(range(len(ct.columns))); ax.set_xticklabels(ct.columns, rotation=45, ha="right", fontsize=8)64ax.set_yticks(range(len(ct.index))); ax.set_yticklabels(ct.index, fontsize=8)65ax.set_title("Project-type composition by GICS sector (row-normalised share)")66fig.colorbar(im, ax=ax, shrink=0.8, label="Within-sector share")67ax.grid(False)68savefig(fig, "fig_sector_type_heatmap.pdf")6970# ---- Figure 4: announced capex by year (by selected type) -----------------71pa = proj[(proj.ann_year >= 2010) & (proj.ann_year <= 2025)].copy()72pa["ann_year"] = pa["ann_year"].astype(int)73cap_year = (pa.groupby("ann_year")["total_amount_usd"]74            .apply(lambda s: np.nansum(s.values) / 1e9))75n_year = pa.groupby("ann_year")["project_id"].count()76fig, ax = plt.subplots(figsize=(7.2, 4.0))77ax.bar(cap_year.index, cap_year.values, color="#2e8b57", alpha=0.85, label="Announced capital ($B)")78ax.set_xlabel("Announcement year"); ax.set_ylabel("Announced capital (USD bn)")79ax2 = ax.twinx(); ax2.plot(n_year.index, n_year.values, color="#c44e52", marker="o", ms=3, label="# projects")80ax2.set_ylabel("Number of projects"); ax2.grid(False)81ax.set_title("Announced project capital and project count by year")82savefig(fig, "fig_capex_by_year.pdf")8384# ---- Figure 5: top locations ----------------------------------------------85loc = proj["canonical_location"].replace("", np.nan).dropna().value_counts().head(15)[::-1]86fig, ax = plt.subplots(figsize=(7.0, 4.6))87ax.barh(loc.index, loc.values, color="#8172b3")88ax.set_xlabel("Number of projects"); ax.set_title("Top 15 project locations")89savefig(fig, "fig_top_locations.pdf")9091# ---- Table 1: summary statistics ------------------------------------------92def stat_row(name, s):93    s = pd.to_numeric(s, errors="coerce").dropna()94    return (name, len(s), s.mean(), s.std(), s.quantile(.5), s.min(), s.max())9596rows = [97    stat_row("Projects per firm", proj.groupby("cik").size()),98    stat_row("Mentions per project", proj["n_mentions"]),99    stat_row("Filings per project", proj["n_filings"]),100    stat_row("Technologies per project", proj["n_tech"]),101    stat_row("Announced amount (USD m)", proj.loc[proj.has_amount, "total_amount_usd"] / 1e6),102    stat_row("Project duration (days)", proj.loc[proj.duration_days > 0, "duration_days"]),103    stat_row("Avg. confidence", proj["avg_confidence"]),104    stat_row("Projects/firm-year (panel)", panel["n_projects"]),105    stat_row("AI projects/firm-year", panel["n_ai"]),106]107lt = [r"\begin{tabular}{lrrrrrr}", r"\toprule",108      r"Variable & N & Mean & SD & Median & Min & Max \\", r"\midrule"]109for nm, n, mu, sd, md, mn, mx in rows:110    lt.append(f"{esc(nm)} & {n:,} & {mu:,.2f} & {sd:,.2f} & {md:,.2f} & {mn:,.2f} & {mx:,.2f} \\\\")111lt += [r"\bottomrule", r"\end{tabular}"]112write_table("tab_summary_stats", "\n".join(lt))113114# ---- Table 2: projects by type --------------------------------------------115bt2 = (proj.groupby("project_type")116       .agg(n=("project_id", "count"),117            cap=("total_amount_usd", lambda s: np.nansum(s.values) / 1e9),118            ai=("is_ai", "mean"),119            conf=("avg_confidence", "mean"))120       .sort_values("n", ascending=False))121lt = [r"\begin{tabular}{lrrrr}", r"\toprule",122      r"Project type & Projects & Capital (\$bn) & AI share & Mean conf. \\", r"\midrule"]123for t, r in bt2.iterrows():124    lt.append(f"{esc(TYPE_LABEL.get(t,t))} & {int(r.n):,} & {r.cap:,.1f} & {r.ai:.2f} & {r.conf:.2f} \\\\")125lt += [r"\midrule",126       f"\\textbf{{Total}} & {len(proj):,} & {np.nansum(proj['total_amount_usd'].values)/1e9:,.1f} & "127       f"{proj['is_ai'].mean():.2f} & {proj['avg_confidence'].mean():.2f} \\\\",128       r"\bottomrule", r"\end{tabular}"]129write_table("tab_by_type", "\n".join(lt))130131# ---- Table 3: sector breakdown --------------------------------------------132bs = (proj.groupby("sector")133      .agg(firms=("cik", "nunique"), n=("project_id", "count"),134           cap=("total_amount_usd", lambda s: np.nansum(s.values)/1e9),135           ai=("is_ai", "mean"))136      .sort_values("n", ascending=False))137lt = [r"\begin{tabular}{lrrrr}", r"\toprule",138      r"GICS sector & Firms & Projects & Capital (\$bn) & AI share \\", r"\midrule"]139for sct, r in bs.iterrows():140    lt.append(f"{esc(sct)} & {int(r.firms)} & {int(r.n):,} & {r.cap:,.1f} & {r.ai:.2f} \\\\")141lt += [r"\bottomrule", r"\end{tabular}"]142write_table("tab_by_sector", "\n".join(lt))143144save_results("descriptives", {145    "projects_per_firm_mean": float(proj.groupby("cik").size().mean()),146    "median_amount_musd": float((proj.loc[proj.has_amount, "total_amount_usd"]/1e6).median()),147    "share_with_amount": float(proj["has_amount"].mean()),148    "top_type": bt2.index[0], "top_type_n": int(bt2.iloc[0]["n"]),149    "ai_share_overall": float(proj["is_ai"].mean()),150    "mention_cagr_2010_2025": float((men[men.year==2025].shape[0] / men[men.year==2010].shape[0]) ** (1/15) - 1),151})152print("descriptives done")153