spb/firc0001_uqo

1"""Build every LaTeX table and figure of the paper from data/*.csv (no hand-typed numbers)."""2import csv, json3from pathlib import Path4import matplotlib5matplotlib.use("Agg")6import matplotlib.pyplot as plt78ROOT = Path(__file__).resolve().parent.parent9D, T, F = ROOT / "data", ROOT / "paper" / "tables", ROOT / "paper" / "figures"10T.mkdir(exist_ok=True); F.mkdir(exist_ok=True)11S = json.load(open(D / "stats.json"))1213def rows(name):14    with open(D / f"{name}.csv") as f:15        return list(csv.DictReader(f))1617def n(x, d=0):18    x = float(x)19    return f"{x:,.{d}f}".replace(",", "{,}")2021def w(name, body):22    (T / name).write_text(body); print("table", name)2324LAB = {"ma_integration": "M\\&A / integration", "capex_program": "Capital expenditure program", "partnership": "Strategic partnership",25       "geographic_expansion": "Geographic expansion", "plant_construction": "Plant / site construction", "cost_reduction": "Cost reduction / restructuring",26       "drug_pipeline": "Drug / clinical pipeline", "product_launch": "New product / launch", "rd_program": "R\\&D program",27       "infrastructure": "Infrastructure investment", "manufacturing_expansion": "Manufacturing / capacity expansion",28       "technology_deployment": "Technology deployment", "digital_transformation": "Digital transformation", "erp_implementation": "ERP / systems implementation",29       "sustainability": "Sustainability / ESG", "supply_chain": "Supply-chain initiative", "data_center": "Data center", "energy_transition": "Energy transition / renewables",30       "ai_initiative": "AI / ML initiative", "automation": "Automation / robotics", "cloud_migration": "Cloud migration"}3132# ---- Table: universe reconciliation ----33U = S["universe"]34w("tab_reconciliation.tex", f"""\\begin{{tabular}}{{lr}}35\\toprule36Step & Count \\\\37\\midrule38iShares IWV holdings file, equity lines with a ticker (as of {U['russell_holdings_as_of']}) & 2{{,}}587 \\\\39\\quad of which mapped to an SEC CIK (CVRs, escrow lines and FDIC-only banks excluded) & {n(U['russell_equity_lines_mapped'])} \\\\40\\quad distinct CIKs (share classes collapsed) & {n(U['russell_distinct_ciks'])} \\\\41S\\&P~500 constituents, cached snapshot of {U['sp500_fetched_at'][:10]} (500 CIKs) & {n(U['sp500_rows'])} \\\\42\\midrule43Merged issuer universe (distinct CIKs) & {n(U['n'])} \\\\44\\quad in both lists & {n(U['both'])} \\\\45\\quad S\\&P~500 only & {n(U['sp_only'])} \\\\46\\quad Russell proxy only & {n(U['ru_only'])} \\\\47\\midrule48Issuers with at least one 10-K, 10-Q or 8-K since 2010 & {n(S['issuers_with_filings'])} \\\\49Issuers without any such filing (foreign private issuers filing 20-F/40-F, FDIC-only banks) & {n(S['issuers_without_filings'])} \\\\50Issuers with at least one resolved project & {n(S['issuers_with_projects'])} \\\\51\\bottomrule52\\end{{tabular}}""")5354# ---- Table: filings by form ----55fb = rows("filings_by_form"); ps = rows("parse_status")56err = {}57for r in ps:58    err.setdefault(r["form_type"], {})[r["parse_status"]] = int(r["n"])59body = "\\begin{tabular}{lrrrrr}\n\\toprule\nForm & Filings & First filing & Last filing & Raw HTML (GB) & Not parsed \\\\\n\\midrule\n"60for r in fb:61    e = err.get(r["form_type"], {}); notp = e.get("error", 0) + e.get("missing", 0)62    body += f"{r['form_type']} & {n(r['n'])} & {r['first']} & {r['last']} & {float(r['gb']):.1f} & {notp} \\\\\n"63body += f"\\midrule\nTotal & {n(S['filings_total'])} & 2010-01-04 & 2026-10-27 & {S['raw_gb']:.1f} & {sum(e.get('error',0)+e.get('missing',0) for e in err.values())} \\\\\n\\bottomrule\n\\end{{tabular}}"64w("tab_filings.tex", body)6566# ---- Table: coverage by year ----67iy = rows("issuers_by_year"); fy = rows("filings_by_year_form"); py = {r["yr"]: r for r in rows("projects_by_first_seen_year")}; my = {r["yr"]: r for r in rows("mentions_by_year")}68byf = {}69for r in fy: byf.setdefault(r["yr"], {})[r["form_type"]] = r["n"]70body = "\\begin{tabular}{lrrrrrrr}\n\\toprule\nYear & Issuers & 10-K & 10-Q & 8-K & Mentions & Issuers w/ mentions & Projects first seen \\\\\n\\midrule\n"71for r in iy:72    y = r["yr"]; b = byf[y]73    body += f"{y} & {n(r['issuers'])} & {n(b['10-K'])} & {n(b['10-Q'])} & {n(b['8-K'])} & {n(my[y]['mentions'])} & {n(my[y]['issuers'])} & {n(py[y]['n'])} \\\\\n"74body += "\\bottomrule\n\\end{tabular}"75w("tab_coverage_year.tex", body)7677# ---- Table: sections and extraction ledger ----78sb = {r["form_type"]: r for r in rows("sections_by_form")}; lb = rows("ledger_by_form")79body = "\\begin{tabular}{lrrrrrr}\n\\toprule\nForm & Parsed sections & Median words & Sections sent to the extractor & Sections with $\\geq 1$ mention & Mentions & Issuers \\\\\n\\midrule\n"80tot = [0, 0, 0, 0]81for r in lb:82    s = sb[r["form_type"]]83    body += f"{r['form_type']} & {n(s['sections'])} & {n(s['median_words'])} & {n(r['sections'])} & {n(r['with_mentions'])} & {n(r['mentions'])} & {n(r['issuers'])} \\\\\n"84    tot[0] += int(s["sections"]); tot[1] += int(r["sections"]); tot[2] += int(r["with_mentions"]); tot[3] += int(r["mentions"])85body += f"\\midrule\nTotal & {n(tot[0])} & & {n(tot[1])} & {n(tot[2])} & {n(tot[3])} & {n(S['issuers_with_filings'])} \\\\\n\\bottomrule\n\\end{{tabular}}"86w("tab_sections.tex", body)8788# ---- Table: 10-K / 10-Q items in scope ----89body = "\\begin{tabular}{llrr}\n\\toprule\nForm & Section (item) & Sections extracted & Mentions \\\\\n\\midrule\n"90for r in rows("ledger_10k_items"): body += f"10-K & \\texttt{{{r['section_name'].replace('_','\\_')}}} & {n(r['n'])} & {n(r['mentions'])} \\\\\n"91body += "\\midrule\n"92for r in rows("ledger_10q_items"): body += f"10-Q & \\texttt{{{r['section_name'].replace('_','\\_')}}} & {n(r['n'])} & {n(r['mentions'])} \\\\\n"93body += "\\bottomrule\n\\end{tabular}"94w("tab_items.tex", body)9596# ---- Table: projects by type ----97pt = rows("projects_by_type")98body = "\\begin{tabular}{llrrrrr}\n\\toprule\nCode & Category & Projects & Share (\\%) & With amount & Median amount (M USD) & Mentions / project \\\\\n\\midrule\n"99for r in pt:100    body += f"\\texttt{{{r['project_type'].replace('_','\\_')}}} & {LAB[r['project_type']]} & {n(r['n'])} & {r['pct']} & {n(r['with_amount'])} & {n(r['median_amount_musd'],1)} & {float(r['mean_mentions']):.2f} \\\\\n"101body += f"\\midrule\n & Total & {n(S['projects'])} & 100.0 & {n(S['projects_fields'][0])} & 128.5 & {S['mentions']/S['projects']:.2f} \\\\\n\\bottomrule\n\\end{{tabular}}"102w("tab_types.tex", body)103104# ---- Table: by sector ----105cs = {r["sector"]: r for r in rows("companies_by_sector")}106body = "\\begin{tabular}{lrrrrr}\n\\toprule\nGICS sector (iShares) & Issuers in universe & of which S\\&P~500 & Issuers with projects & Projects & Projects per issuer \\\\\n\\midrule\n"107for r in rows("projects_by_sector"):108    c = cs.get(r["sector"], {"n": "", "sp500": ""})109    body += f"{r['sector']} & {n(c['n']) if c['n'] else ''} & {n(c['sp500']) if c['sp500'] else ''} & {n(r['issuers'])} & {n(r['n'])} & {float(r['per_issuer']):.1f} \\\\\n"110body += "\\bottomrule\n\\end{tabular}"111w("tab_sectors.tex", body)112113# ---- Table: status, mention buckets, duration, confidence ----114st = rows("projects_by_status"); mb = rows("mentions_per_project"); du = rows("duration"); cf = rows("confidence_dist")115body = "\\begin{tabular}{lr@{\\hspace{2em}}lr@{\\hspace{2em}}lr}\n\\toprule\nLatest status & Projects & Mentions per project & Projects & Span of cited years & Projects \\\\\n\\midrule\n"116du_order = ["0", "1-2", "3-5", ">5", "unknown"]; dus = {r["span_years"]: r["n"] for r in du}117rowsn = max(len(st), len(mb), len(du_order))118for i in range(rowsn):119    a = f"\\texttt{{{st[i]['status'].replace('_','\\_')}}} & {n(st[i]['n'])}" if i < len(st) else "&"120    b = f"{mb[i]['bucket']} & {n(mb[i]['n'])}" if i < len(mb) else "&"121    c = f"{du_order[i].replace('>','$>$')} years & {n(dus[du_order[i]])}" if i < len(du_order) else "&"122    body += f"{a} & {b} & {c} \\\\\n"123body += "\\bottomrule\n\\end{tabular}"124w("tab_status.tex", body)125body = "\\begin{tabular}{lr}\n\\toprule\nSelf-reported confidence (rounded) & Mentions \\\\\n\\midrule\n"126for r in cf: body += f"{r['conf']} & {n(r['n'])} \\\\\n"127body += "\\bottomrule\n\\end{tabular}"128w("tab_confidence.tex", body)129130# ---- Table: amounts ----131ad = rows("amount_dist")[0]; q = json.loads(ad["q"]); ppi = json.loads(rows("projects_per_issuer")[0]["q"])132body = f"""\\begin{{tabular}}{{lrrrrrr}}133\\toprule134 & P10 & P25 & Median & P75 & P90 & P99 \\\\135\\midrule136Largest amount cited per project, M USD ($N$ = {n(ad['n'])}) & {q[0]/1e6:,.1f} & {q[1]/1e6:,.0f} & {q[2]/1e6:,.1f} & {q[3]/1e6:,.0f} & {q[4]/1e6:,.0f} & {q[5]/1e6:,.0f} \\\\137Projects per issuer ($N$ = {n(S['issuers_with_projects'])}) & {ppi[0]:.0f} & {ppi[1]:.0f} & {ppi[2]:.0f} & {ppi[3]:.0f} & {ppi[4]:.0f} & {ppi[5]:.0f} \\\\138\\bottomrule139\\end{{tabular}}""".replace(",", "{,}")140w("tab_amounts.tex", body)141142# ---- Table: knowledge graph ----143kg = rows("kg")144body = "\\begin{tabular}{llr}\n\\toprule\n & Type & Count \\\\\n\\midrule\n"145for r in kg: body += f"{r['k'].capitalize()} & \\texttt{{{r['node_type'].replace('_','\\_')}}} & {n(r['n'])} \\\\\n"146body += "\\bottomrule\n\\end{tabular}"147w("tab_graph.tex", body)148149# ---- Table: top issuers ----150body = "\\begin{tabular}{llr}\n\\toprule\nTicker & Issuer & Resolved projects \\\\\n\\midrule\n"151for r in rows("top_issuers")[:12]: body += f"{r['ticker']} & {r['company_name'].title().replace('&','\\&')} & {n(r['projects'])} \\\\\n"152body += "\\bottomrule\n\\end{tabular}"153w("tab_top_issuers.tex", body)154155# ---- Table: provenance example (Cheniere Corpus Christi) ----156ex = [r for r in rows("example_provenance") if r["ticker"] == "LNG"]157body = "\\begin{tabular}{llllp{6.2cm}}\n\\toprule\nAccession & Filed & Form & Status / amount & Evidence snippet (model-generated description grounded in the section) \\\\\n\\midrule\n"158for r in ex[:8]:159    amt = f"{float(r['amount_usd'])/1e9:.1f} B USD" if r["amount_usd"] else "--"160    snip = r["snippet"].replace("&", "\\&").replace("%", "\\%").replace("$", "\\$")[:170]161    body += f"\\texttt{{{r['accession_number']}}} & {r['filing_date']} & {r['form_type']} & {r['m_status'].replace('_','\\_')} / {amt} & {snip} \\\\\n"162body += "\\bottomrule\n\\end{tabular}"163w("tab_provenance.tex", body)164S["example_project"] = {k: ex[0][k] for k in ("project_id", "ticker", "project_name", "project_type", "status", "total_amount_usd", "n_mentions", "first_seen", "last_seen")} if ex else {}165166# ---- Figures ----167BLUE, GOLD, GREY = "#16365c", "#c9a227", "#8a8a8a"168yrs = [int(r["yr"]) for r in iy]169fig, ax1 = plt.subplots(figsize=(7.2, 3.4))170ax1.bar(yrs, [int(r["filings"]) for r in iy], color=BLUE, alpha=.85, label="Filings (10-K, 10-Q, 8-K)")171ax1.set_ylabel("Filings per year"); ax1.set_xlabel("Filing year")172ax2 = ax1.twinx(); ax2.plot(yrs, [int(r["issuers"]) for r in iy], color=GOLD, marker="o", lw=2, label="Issuers with $\\geq$1 filing")173ax2.set_ylabel("Issuers"); ax2.set_ylim(0, 2700)174h1, l1 = ax1.get_legend_handles_labels(); h2, l2 = ax2.get_legend_handles_labels(); ax1.legend(h1 + h2, l1 + l2, loc="upper left", frameon=False, fontsize=8)175for a in (ax1, ax2): a.spines[["top"]].set_visible(False)176fig.tight_layout(); fig.savefig(F / "fig_coverage.pdf"); plt.close(fig)177178fig, ax = plt.subplots(figsize=(7.2, 5.2))179labs = [LAB[r["project_type"]].replace("\\&", "&") for r in pt][::-1]; vals = [int(r["n"]) for r in pt][::-1]180ax.barh(labs, vals, color=BLUE); ax.set_xlabel("Resolved projects"); ax.spines[["top", "right"]].set_visible(False)181for i, v in enumerate(vals): ax.text(v + 300, i, f"{v:,}", va="center", fontsize=7)182fig.tight_layout(); fig.savefig(F / "fig_types.pdf"); plt.close(fig)183184fig, ax = plt.subplots(figsize=(7.2, 3.2))185ax.plot(yrs, [int(my[str(y)]["mentions"]) for y in yrs], color=BLUE, marker="o", lw=2, label="Project mentions")186ax.plot(yrs, [int(py[str(y)]["n"]) for y in yrs], color=GOLD, marker="s", lw=2, label="Projects first observed")187ax.set_xlabel("Filing year"); ax.set_ylabel("Count"); ax.legend(frameon=False, fontsize=8); ax.spines[["top", "right"]].set_visible(False)188fig.tight_layout(); fig.savefig(F / "fig_mentions_year.pdf"); plt.close(fig)189190fig, ax = plt.subplots(figsize=(7.2, 3.0))191ax.bar([r["conf"] for r in cf], [int(r["n"]) for r in cf], width=0.08, color=BLUE)192ax.set_xlabel("Self-reported extraction confidence"); ax.set_ylabel("Mentions"); ax.spines[["top", "right"]].set_visible(False)193fig.tight_layout(); fig.savefig(F / "fig_confidence.pdf"); plt.close(fig)194print("figures ok")195json.dump(S, open(D / "stats.json", "w"), indent=2, default=str)196