spb/firc0001_uqo
Public
1"""Build every LaTeX table, figure and inline-number macro of the paper from the exported data.23Inputs (read-only):4 data/*.csv, data/stats.json exports of code/export_stats.py (node, DuckDB read-only)5 data/documented_inputs.json the few values that no query can reproduce (each with its source)6 data/datamodel_audit.csv rows of the data-model audit table (Appendix C)7 code/audit/spid_snapshot/{llm,rules,taxonomy}.py frozen pipeline snapshot (prompt, gate regex, taxonomy)8 results/**/*.csv Stage D audit outputs (optional; tables are skipped when absent)9Outputs:10 paper/tables/*.tex, paper/figures/*.pdf, paper/tables/numbers.tex (SPID... macros for inline numbers),11 results/inline_numbers.json (macro -> value, source, formula), data/example_project.json.12No numeric literal is typed in this file; stats.json is never rewritten.13Usage: python3 -I code/make_tables.py [--out SCRATCH_DIR] (--out is used by tests/)14"""15import argparse, ast, csv, json, math, re, sys16from pathlib import Path17import matplotlib18matplotlib.use("Agg")19import matplotlib.pyplot as plt20from matplotlib.ticker import MaxNLocator2122ROOT = Path(__file__).resolve().parent.parent23ap = argparse.ArgumentParser()24ap.add_argument("--out", default=None, help="scratch root; tables/, figures/, results/ are created under it (tests)")25ARGS = ap.parse_args()26D = ROOT / "data"27R = ROOT / "results"28SNAP = ROOT / "code" / "audit" / "spid_snapshot"29OUT = Path(ARGS.out) if ARGS.out else ROOT / "paper"30T, F = OUT / "tables", OUT / "figures"31RES_OUT = (Path(ARGS.out) / "results") if ARGS.out else R32DATA_OUT = (Path(ARGS.out) / "data") if ARGS.out else D33for p in (T, F, RES_OUT, DATA_OUT):34 p.mkdir(parents=True, exist_ok=True)3536S = json.load(open(D / "stats.json")) # read-only37DI = json.load(open(D / "documented_inputs.json")) if (D / "documented_inputs.json").exists() else {}38INLINE = {} # macro -> {value, source, formula}394041def rows(name, base=D):42 with open(base / f"{name}.csv", newline="") as f:43 return list(csv.DictReader(f))444546def n(x, d=0):47 x = float(x)48 return f"{x:,.{d}f}".replace(",", "{,}")495051def w(name, body):52 (T / name).write_text(body if body.endswith("\n") else body + "\n")53 print("table", name)545556def esc(s):57 return (str(s).replace("\\", "\\textbackslash{}").replace("&", "\\&").replace("%", "\\%")58 .replace("$", "\\$").replace("_", "\\_").replace("#", "\\#"))596061_DIGITS = {"0": "Zero", "1": "One", "2": "Two", "3": "Three", "4": "Four", "5": "Five", "6": "Six", "7": "Seven", "8": "Eight", "9": "Nine"}626364def macro(name, value, source, formula):65 """Register an inline number: \\SPID<name> in paper/tables/numbers.tex (control sequences allow letters only)."""66 name = "".join(_DIGITS.get(ch, ch) for ch in name if ch.isalnum())67 INLINE[name] = {"value": value, "source": source, "formula": formula}686970def di(key):71 """Documented input (value + source) or None."""72 e = DI.get(key)73 return None if e is None else e.get("value")747576def di_source(key):77 e = DI.get(key)78 return "" if e is None else e.get("source", "")798081LAB = {"ma_integration": "M\\&A / integration", "capex_program": "Capital expenditure program", "partnership": "Strategic partnership",82 "geographic_expansion": "Geographic expansion", "plant_construction": "Plant / site construction", "cost_reduction": "Cost reduction / restructuring",83 "drug_pipeline": "Drug / clinical pipeline", "product_launch": "New product / launch", "rd_program": "R\\&D program",84 "infrastructure": "Infrastructure investment", "manufacturing_expansion": "Manufacturing / capacity expansion",85 "technology_deployment": "Technology deployment", "digital_transformation": "Digital transformation", "erp_implementation": "ERP / systems implementation",86 "sustainability": "Sustainability / ESG", "supply_chain": "Supply-chain initiative", "data_center": "Data center", "energy_transition": "Energy transition / renewables",87 "ai_initiative": "AI / ML initiative", "automation": "Automation / robotics", "cloud_migration": "Cloud migration"}8889# ---- Table 1: universe reconciliation ----------------------------------------90U = S["universe"]91iwv_lines = S.get("iwv_equity_lines") # exported by export_stats.py when the holdings file is archived92iwv_src = "stats.json:iwv_equity_lines (export_stats.py)"93if iwv_lines is None:94 iwv_lines = di("iwv_equity_lines_reported"); iwv_src = di_source("iwv_equity_lines_reported")95if iwv_lines is not None:96 row1_label = (f"iShares IWV holdings file, equity lines with a ticker (as of {U['russell_holdings_as_of']}); "97 "count reported by the build log, source file not archived" if "not archived" in iwv_src98 else f"iShares IWV holdings file, equity lines with a ticker (as of {U['russell_holdings_as_of']})")99 row1_val = n(iwv_lines)100else:101 row1_label = f"iShares IWV holdings file, equity lines with a ticker (as of {U['russell_holdings_as_of']}); source file not archived"102 row1_val = "--"103macro("iwvEquityLines", row1_val, iwv_src, "documented input or stats.json")104sp500_ciks = S.get("sp500_ciks", U.get("n_sp500"))105w("tab_reconciliation.tex", f"""\\begin{{tabular}}{{lr}}106\\toprule107Step & Count \\\\108\\midrule109{row1_label} & {row1_val} \\\\110\\quad of which mapped to an SEC CIK (CVRs, escrow lines and FDIC-only banks excluded) & {n(U['russell_equity_lines_mapped'])} \\\\111\\quad distinct CIKs (share classes collapsed) & {n(U['russell_distinct_ciks'])} \\\\112S\\&P~500 constituents, cached snapshot of {U['sp500_fetched_at'][:10]} ({n(sp500_ciks)} CIKs) & {n(U['sp500_rows'])} \\\\113\\midrule114Merged issuer universe (distinct CIKs) & {n(U['n'])} \\\\115\\quad in both lists & {n(U['both'])} \\\\116\\quad S\\&P~500 only & {n(U['sp_only'])} \\\\117\\quad Russell proxy only & {n(U['ru_only'])} \\\\118\\midrule119Issuers with at least one 10-K, 10-Q or 8-K since 2010 & {n(S['issuers_with_filings'])} \\\\120Issuers without any such filing (foreign private issuers filing 20-F/40-F, FDIC-only banks) & {n(S['issuers_without_filings'])} \\\\121Issuers with at least one resolved project & {n(S['issuers_with_projects'])} \\\\122\\bottomrule123\\end{{tabular}}""")124macro("universeN", n(U["n"]), "stats.json:universe.n", "count")125macro("russellDistinctCiks", n(U["russell_distinct_ciks"]), "stats.json:universe.russell_distinct_ciks", "count")126macro("russellMappedLines", n(U["russell_equity_lines_mapped"]), "stats.json:universe.russell_equity_lines_mapped", "count")127macro("russellUnmatched", n(U["russell_unmatched"]), "stats.json:universe.russell_unmatched", "len(unmatched)")128# Russell 3000 coverage of the proxy (L5): only if results/russell_coverage.json holds a retrieved constituent count129rc_path = R / "russell_coverage.json"130if rc_path.exists():131 rc = json.load(open(rc_path))132 if rc.get("index_constituents") is not None:133 cov = 100.0 * U["russell_distinct_ciks"] / rc["index_constituents"]134 macro("russellIndexConstituents", n(rc["index_constituents"]), "results/russell_coverage.json:index_constituents", rc.get("source_url", ""))135 macro("russellProxyCoveragePct", f"{cov:.1f}", "results/russell_coverage.json", "100 * russell_distinct_ciks / index_constituents")136 if rc.get("fund_holdings_count") is not None:137 macro("iwvHoldingsCount", n(rc["fund_holdings_count"]), "results/russell_coverage.json:fund_holdings_count", rc.get("fund_source_url", ""))138 if rc.get("replication_method"):139 macro("iwvReplicationMethod", esc(rc["replication_method"]), "results/russell_coverage.json:replication_method", rc.get("fund_source_url", ""))140141# ---- Table 2: filings by form --------------------------------------------------142fb = rows("filings_by_form"); ps = rows("parse_status")143err = {}144for r in ps:145 err.setdefault(r["form_type"], {})[r["parse_status"]] = int(r["n"])146fdr = S.get("filing_date_range") or [min(r["first"] for r in fb), max(r["last"] for r in fb)]147macro("filingDateFirst", fdr[0], "stats.json:filing_date_range or min(filings_by_form.first)", "min")148macro("filingDateLast", fdr[1], "stats.json:filing_date_range or max(filings_by_form.last)", "max")149body = "\\begin{tabular}{lrrrrr}\n\\toprule\nForm & Filings & First filing & Last filing & Raw HTML (GB) & Not parsed \\\\\n\\midrule\n"150for r in fb:151 e = err.get(r["form_type"], {}); notp = e.get("error", 0) + e.get("missing", 0)152 body += f"{r['form_type']} & {n(r['n'])} & {r['first']} & {r['last']} & {float(r['gb']):.1f} & {notp} \\\\\n"153not_parsed = sum(e.get("error", 0) + e.get("missing", 0) for e in err.values())154missing = sum(e.get("missing", 0) for e in err.values())155body += f"\\midrule\nTotal & {n(S['filings_total'])} & {fdr[0]} & {fdr[1]} & {S['raw_gb']:.1f} & {not_parsed} \\\\\n\\bottomrule\n\\end{{tabular}}"156w("tab_filings.tex", body)157macro("filingsTotal", n(S["filings_total"]), "stats.json:filings_total", "count")158macro("filingsNotParsed", str(not_parsed), "data/parse_status.csv", "sum(error)+sum(missing)")159macro("filingsMissingOnDisk", str(missing), "data/parse_status.csv", "sum(missing)")160macro("rawGb", f"{S['raw_gb']:.1f}", "stats.json:raw_gb", "sum(file_size_bytes)/1e9")161162# ---- Table: coverage by year ----------------------------------------------------163iy = rows("issuers_by_year"); fy = rows("filings_by_year_form")164py = {r["yr"]: r for r in rows("projects_by_first_seen_year")}; my = {r["yr"]: r for r in rows("mentions_by_year")}165byf = {}166for r in fy: byf.setdefault(r["yr"], {})[r["form_type"]] = r["n"]167body = "\\begin{tabular}{lrrrrrrrr}\n\\toprule\nYear & Issuers & 10-K & 10-Q & 8-K & Mentions & Issuers w/ mentions & Mentions per issuer & Projects first seen \\\\\n\\midrule\n"168for r in iy:169 y = r["yr"]; b = byf[y]; mpi = int(my[y]["mentions"]) / int(my[y]["issuers"])170 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'])} & {mpi:.1f} & {n(py[y]['n'])} \\\\\n"171body += "\\bottomrule\n\\end{tabular}"172w("tab_coverage_year.tex", body)173yrs_all = sorted(int(r["yr"]) for r in iy)174y_first, y_last = yrs_all[0], yrs_all[-1]175inner = [y for y in yrs_all if y_first < y < y_last] # exclude the left-censored first year and the partial last year176fs_inner = [int(py[str(y)]["n"]) for y in inner]177macro("firstSeenYearMin", str(inner[0]), "data/projects_by_first_seen_year.csv", "first full year after the left-censored start")178macro("firstSeenYearMax", str(inner[-1]), "data/projects_by_first_seen_year.csv", "last full year before the partial final year")179macro("firstSeenRangeInner", f"{n(min(fs_inner))}--{n(max(fs_inner))}", "data/projects_by_first_seen_year.csv", f"min/max of projects first seen, {inner[0]}--{inner[-1]}")180macro("firstSeenFirstYear", n(py[str(y_first)]["n"]), "data/projects_by_first_seen_year.csv", f"projects first seen in {y_first} (left-censored)")181macro("firstSeenInnerMean", n(sum(fs_inner) / len(fs_inner)), "data/projects_by_first_seen_year.csv", f"mean {inner[0]}--{inner[-1]}")182macro("mentionsFirstYear", n(my[str(y_first)]["mentions"]), "data/mentions_by_year.csv", f"mentions in {y_first}")183macro("mentionsLastFullYear", n(my[str(inner[-1])]["mentions"]), "data/mentions_by_year.csv", f"mentions in {inner[-1]}")184macro("issuersWithMentionsFirstYear", n(my[str(y_first)]["issuers"]), "data/mentions_by_year.csv", f"issuers with mentions in {y_first}")185macro("issuersWithMentionsLastFullYear", n(my[str(inner[-1])]["issuers"]), "data/mentions_by_year.csv", f"issuers with mentions in {inner[-1]}")186macro("mentionsPerIssuerFirstYear", f"{int(my[str(y_first)]['mentions'])/int(my[str(y_first)]['issuers']):.1f}", "data/mentions_by_year.csv", "mentions/issuers")187macro("mentionsPerIssuerLastFullYear", f"{int(my[str(inner[-1])]['mentions'])/int(my[str(inner[-1])]['issuers']):.1f}", "data/mentions_by_year.csv", "mentions/issuers")188macro("lastYear", str(y_last), "data/issuers_by_year.csv", "max year (partial)")189190# ---- Table 3: sections and extraction ledger -----------------------------------191sb = {r["form_type"]: r for r in rows("sections_by_form")}; lb = rows("ledger_by_form")192mf = {r["form_type"]: int(r["n"]) for r in rows("mentions_by_form")}193calls = {}194t3 = R / "ledger" / "table3_ledger_vs_stored.csv"195if t3.exists():196 calls = {r["form_type"]: int(float(r["calls"])) for r in rows(t3.stem, t3.parent) if r.get("calls")}197cc = " & Gate-passing calls" if calls else ""198body = ("\\begin{tabular}{lrrrr" + ("r" if calls else "") + "rrr}\n\\toprule\nForm & Parsed sections & Median words & Ledgered sections ($\\geq$ 30 words)" + cc + " & "199 "Sections with $\\geq 1$ mention & Mentions returned by the model (ledger) & Mentions stored & Issuers \\\\\n\\midrule\n")200tot = [0, 0, 0, 0, 0]; gap_by_form = {}201for r in lb:202 s = sb[r["form_type"]]; stored = mf[r["form_type"]]203 gap_by_form[r["form_type"]] = int(r["mentions"]) - stored204 cv = f" & {n(calls[r['form_type']])}" if calls else ""205 body += (f"{r['form_type']} & {n(s['sections'])} & {n(s['median_words'])} & {n(r['sections'])}{cv} & {n(r['with_mentions'])} & "206 f"{n(r['mentions'])} & {n(stored)} & {n(r['issuers'])} \\\\\n")207 tot[0] += int(s["sections"]); tot[1] += int(r["sections"]); tot[2] += int(r["with_mentions"]); tot[3] += int(r["mentions"]); tot[4] += stored208ctot = f" & {n(sum(calls.values()))}" if calls else ""209body += (f"\\midrule\nTotal & {n(tot[0])} & & {n(tot[1])}{ctot} & {n(tot[2])} & {n(tot[3])} & {n(tot[4])} & {n(S['issuers_with_filings'])}$^{{\\dagger}}$ \\\\\n"210 "\\bottomrule\n\\end{tabular}")211if calls:212 for ft, v in calls.items():213 macro(f"calls{ft}", n(v), "results/ledger/table3_ledger_vs_stored.csv", f"calls where form_type={ft}")214w("tab_sections.tex", body)215ledger_gap = tot[3] - tot[4]216gap_txt = ", ".join(f"{k} {v}" for k, v in gap_by_form.items())217coll_sections = None218for cand in (R / "ledger" / "ledger_collisions.csv", D / "ledger_collisions.csv"):219 if cand.exists():220 with open(cand, newline="") as f:221 coll_sections = sum(1 for _ in csv.DictReader(f))222 break223note = (f"\\item ``Ledgered sections'' counts rows of the \\texttt{{extracted\\_sections}} ledger (sections with at least 30 words; "224 f"for 10-Ks restricted to the items of Table~\\ref{{tab:items}}); the ledger does not record whether the candidate gate "225 f"passed or whether the API call succeeded, so this is not a count of calls"226 + (f"; ``Gate-passing calls'' recomputes the candidate gate of Appendix~\\ref{{app:prompt}} over the ledgered text ({n(sum(calls.values()))} sections reached the model)" if calls else "")227 + ". ``Mentions returned by the model'' is "228 f"$\\sum$\\texttt{{n\\_mentions}} over the ledger ({n(tot[3])}); ``Mentions stored'' is the row count of "229 f"\\texttt{{project\\_mentions}} ({n(tot[4])}). The difference of {n(ledger_gap)} ({gap_txt})"230 + (f", spread over {n(coll_sections)} sections," if coll_sections else "")231 + " is the number of mentions overwritten by mention-identifier collisions before storage (Section~\\ref{sec:construct}). "232 f"$^{{\\dagger}}$Issuers with at least one filing of any form, not a column sum.\n")233w("tab_sections_note.tex", note)234macro("ledgerTotal", n(S["ledger_total"]), "stats.json:ledger_total", "count(extracted_sections)")235macro("ledgerMentionsReturned", n(tot[3]), "data/ledger_by_form.csv", "sum(mentions)")236macro("ledgerGap", n(ledger_gap), "data/ledger_by_form.csv, data/mentions_by_form.csv", "sum(ledger mentions) - sum(stored mentions)")237macro("ledgerGapByForm", gap_txt, "data/ledger_by_form.csv, data/mentions_by_form.csv", "per form")238for ft in ("8-K", "10-K", "10-Q"):239 key = ft.replace("-", "")240 macro(f"mentionsStored{key}", n(mf[ft]), "data/mentions_by_form.csv", f"n where form_type={ft}")241 lr = next(r for r in lb if r["form_type"] == ft)242 macro(f"mentionsLedger{key}", n(lr["mentions"]), "data/ledger_by_form.csv", f"mentions where form_type={ft}")243 macro(f"ledgerSections{key}", n(lr["sections"]), "data/ledger_by_form.csv", f"sections where form_type={ft}")244productive = tot[2]245macro("productiveSections", n(productive), "data/ledger_by_form.csv", "sum(with_mentions)")246macro("productiveShareLedger", f"{100*productive/S['ledger_total']:.1f}", "data/ledger_by_form.csv, stats.json", "100*productive/ledger_total")247macro("mentionsPerLedgerSection", f"{S['mentions']/S['ledger_total']:.2f}", "stats.json", "mentions/ledger_total")248macro("mentionsPerProductiveSection", f"{S['mentions']/productive:.2f}", "stats.json, data/ledger_by_form.csv", "mentions/productive")249gate_total = S.get("gate_pass_total")250gt_path = R / "ledger" / "gate_pass_totals.json"251if not gate_total and gt_path.exists():252 gt = json.load(open(gt_path))253 gate_total = next((gt[k] for k in ("calls", "gate_pass", "calls_total", "gate_pass_total") if isinstance(gt.get(k), (int, float))), None)254if gate_total:255 g = gate_total256 S = dict(S); S["gate_pass_total"] = g # local copy only; stats.json is never written257 macro("gatePassTotal", n(g), "stats.json:gate_pass_total", "regexp_matches(_PROJECT_GATE) over the ledger")258 macro("productiveShareCalls", f"{100*productive/g:.1f}", "stats.json:gate_pass_total", "100*productive/gate_pass_total")259 macro("mentionsPerCall", f"{S['mentions']/g:.2f}", "stats.json:gate_pass_total", "mentions/gate_pass_total")260261# ---- Table 4: 10-K / 10-Q items in scope ----------------------------------------262gp = None263for cand in (R / "ledger" / "gate_pass_by_form_item.csv",):264 if cand.exists():265 gp = {}266 for r in rows(cand.stem, cand.parent):267 fk = next((r[c] for c in r if c in ("form", "form_type")), None)268 ik = next((r[c] for c in r if c in ("item", "section_name")), None)269 gk = r.get("calls", next((r[c] for c in r if "gate_pass" in c.lower()), None))270 if fk and ik and gk not in (None, ""):271 gp[(fk, ik)] = gp.get((fk, ik), 0) + int(float(gk)) # summed over runs (backfilled + October)272cols = "llrr" + ("r" if gp else "")273body = f"\\begin{{tabular}}{{{cols}}}\n\\toprule\nForm & Section (item) & Ledgered sections & Mentions" + (" & Gate passed (calls)" if gp else "") + " \\\\\n\\midrule\n"274for form, name in (("10-K", "ledger_10k_items"), ("10-Q", "ledger_10q_items")):275 for r in rows(name):276 extra = f" & {n(gp[(form, r['section_name'])])}" if gp and (form, r["section_name"]) in gp else (" & --" if gp else "")277 body += f"{form} & \\texttt{{{r['section_name'].replace('_', '\\_')}}} & {n(r['n'])} & {n(r['mentions'])}{extra} \\\\\n"278 if form == "10-K": body += "\\midrule\n"279body += "\\bottomrule\n\\end{tabular}"280w("tab_items.tex", body)281k10 = {r["section_name"]: r for r in rows("ledger_10k_items")}; q10 = {r["section_name"]: r for r in rows("ledger_10q_items")}282for key, src in (("tenKmdnaMentions", k10.get("md_and_a")), ("tenQmdnaMentions", q10.get("md_and_a")), ("tenKbusinessMentions", k10.get("business")),283 ("tenQfinancialStatementsMentions", q10.get("financial_statements"))):284 if src: macro(key, n(src["mentions"]), "data/ledger_10k_items.csv / ledger_10q_items.csv", "mentions by item")285if k10.get("full_body"): macro("tenKfullBodyRows", n(k10["full_body"]["n"]), "data/ledger_10k_items.csv", "n where section_name=full_body")286287# ---- Table 5: projects by type ---------------------------------------------------288pt = rows("projects_by_type")289ci = {}290ci_path = D / "projects_by_type_ci.csv"291if ci_path.exists():292 for r in rows("projects_by_type_ci"):293 lo = next((r[c] for c in r if c.lower() in ("ci_low", "lo", "ci_lo", "median_ci_low")), None)294 hi = next((r[c] for c in r if c.lower() in ("ci_high", "hi", "ci_hi", "median_ci_high")), None)295 if lo not in (None, "") and hi not in (None, ""):296 ci[r["project_type"]] = (float(lo), float(hi))297small_n = di("small_n_threshold") or 50298# the threshold itself is a documented presentation rule (data/documented_inputs.json), not a measurement299ad = rows("amount_dist")[0]; q = json.loads(ad["q"])300total_median = S.get("amount_total_median_musd", q[2] / 1e6)301om = {}302om_path = R / "resolver" / "overmerge_by_type.csv"303if om_path.exists():304 om = {r["project_type"]: r for r in rows(om_path.stem, om_path.parent)}305oc = " & $\\geq 3$ distinct names (\\%)" if om else ""306body = ("\\begin{tabular}{llrrrrrr" + ("r" if om else "") + "}\n\\toprule\nCode & Category & Projects & Share (\\%) & With amount ($N$) & Median amount (M USD) & "307 "95\\% CI of the median & Mentions / project" + oc + " \\\\\n\\midrule\n")308pct_sum = 0.0; greyed = []309for r in pt:310 pct_sum += float(r["pct"]); N = int(r["with_amount"])311 med = n(r["median_amount_musd"], 1)312 if N < small_n:313 med = f"\\textcolor{{gray}}{{{med}}}"; greyed.append(r["project_type"])314 cic = f"{ci[r['project_type']][0]/1e6:,.1f}--{ci[r['project_type']][1]/1e6:,.1f}".replace(",", "{,}") if r["project_type"] in ci else "--"315 ov = f" & {float(om[r['project_type']]['pct_ge3_names']):.1f}" if om and r["project_type"] in om else (" & --" if om else "")316 body += (f"\\texttt{{{r['project_type'].replace('_', '\\_')}}} & {LAB[r['project_type']]} & {n(r['n'])} & {r['pct']} & {n(N)} & {med} & {cic} & "317 f"{float(r['mean_mentions']):.2f}{ov} \\\\\n")318body += (f"\\midrule\n & Total & {n(S['projects'])} & {pct_sum:.1f} & {n(S['projects_fields'][0])} & {n(total_median, 1)} & -- & "319 f"{S['mentions']/S['projects']:.2f}" + (" & " if om else "") + f" \\\\\n\\bottomrule\n\\end{{tabular}}")320w("tab_types.tex", body)321note = (f"\\item Shares are rounded to one decimal and sum to {pct_sum:.1f}. ``With amount'' counts projects for which at least one mention carries a USD "322 f"figure; ``median amount'' is the median across those projects of the largest amount cited (Section~\\ref{{sec:amounts}} on why amounts are never summed). "323 f"Medians printed in grey rest on fewer than {small_n} projects with an amount ({', '.join('\\texttt{' + g.replace('_', '\\_') + '}' for g in greyed)}) "324 "and should not be quoted without their $N$. "325 + ("The 95\\% interval of the median is a percentile bootstrap (\\texttt{data/projects\\_by\\_type\\_ci.csv}). " if ci326 else "The 95\\% interval column is filled when \\texttt{data/projects\\_by\\_type\\_ci.csv} (bootstrap on the node) is present. ")327 + ("``$\\geq 3$ distinct names'' is the share of projects of the category whose mentions carry three or more distinct project names "328 "(over-merge indicator of the resolution key, \\texttt{results/resolver/overmerge\\_by\\_type.csv}).\n" if om else "\n"))329w("tab_types_note.tex", note)330macro("typesPctSum", f"{pct_sum:.1f}", "data/projects_by_type.csv", "sum(pct)")331macro("amountMedianMusd", n(total_median, 1), "stats.json:amount_total_median_musd or data/amount_dist.csv", "median(total_amount_usd)/1e6")332macro("smallNThreshold", str(small_n), "data/documented_inputs.json:small_n_threshold", "presentation rule")333macro("smallNCategories", ", ".join(greyed), "data/projects_by_type.csv", f"with_amount < {small_n}")334ptd = {r["project_type"]: r for r in pt}335for code in ("data_center", "ai_initiative", "cloud_migration", "automation", "ma_integration", "capex_program", "partnership", "geographic_expansion", "plant_construction"):336 if code in ptd:337 cam = "".join(p.capitalize() for p in code.split("_"))338 macro(f"share{cam}", ptd[code]["pct"], "data/projects_by_type.csv", f"pct where project_type={code}")339 macro(f"n{cam}", n(ptd[code]["n"]), "data/projects_by_type.csv", f"n where project_type={code}")340 macro(f"withAmount{cam}", n(ptd[code]["with_amount"]), "data/projects_by_type.csv", f"with_amount where project_type={code}")341 macro(f"medianAmount{cam}", n(ptd[code]["median_amount_musd"], 1), "data/projects_by_type.csv", f"median_amount_musd where project_type={code}")342tech = [c for c in ("data_center", "ai_initiative", "cloud_migration", "automation") if c in ptd]343macro("techCategoriesCountRange", f"{n(min(int(ptd[c]['n']) for c in tech))}--{n(max(int(ptd[c]['n']) for c in tech))}", "data/projects_by_type.csv", "min/max n over " + ", ".join(tech))344345# ---- Table 6: by sector -------------------------------------------------------------346cs = {r["sector"]: r for r in rows("companies_by_sector")}347sec = rows("projects_by_sector")348body = "\\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"349for r in sec:350 c = cs.get(r["sector"], {"n": "", "sp500": ""})351 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"352body += "\\bottomrule\n\\end{tabular}"353w("tab_sectors.tex", body)354secd = {r["sector"]: r for r in sec}355top4 = sorted(sec, key=lambda r: -float(r["per_issuer"]))[:4]356vals = [float(r["per_issuer"]) for r in top4]357macro("sectorTopFourPerIssuerRange", f"{math.floor(min(vals))}--{math.ceil(max(vals))}", "data/projects_by_sector.csv", "floor(min)/ceil(max) of per_issuer over the four highest sectors: " + ", ".join(r["sector"] for r in top4))358macro("sectorTopFourNames", ", ".join(r["sector"].lower() for r in top4), "data/projects_by_sector.csv", "four highest per_issuer")359if "Financials" in secd: macro("sectorFinancialsPerIssuer", f"{round(float(secd['Financials']['per_issuer']))}", "data/projects_by_sector.csv", "round(per_issuer) for Financials")360for name in ("Health Care", "Industrials"):361 if name in secd:362 cam = name.replace(" ", "")363 macro(f"sectorProjects{cam}", n(secd[name]["n"]), "data/projects_by_sector.csv", f"n for {name}")364 macro(f"sectorIssuers{cam}", n(secd[name]["issuers"]), "data/projects_by_sector.csv", f"issuers for {name}")365366# ---- Table 7: status, mention buckets, duration ----------------------------------------367st = rows("projects_by_status"); mb = rows("mentions_per_project"); du = rows("duration"); cf = rows("confidence_dist")368body = "\\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"369du_order = ["0", "1-2", "3-5", ">5", "unknown"]; dus = {r["span_years"]: int(r["n"]) for r in du}370rowsn = max(len(st), len(mb), len(du_order))371for i in range(rowsn):372 a = f"\\texttt{{{st[i]['status'].replace('_', '\\_')}}} & {n(st[i]['n'])}" if i < len(st) else "&"373 b = f"{mb[i]['bucket']} & {n(mb[i]['n'])}" if i < len(mb) else "&"374 c = f"{du_order[i].replace('>', '$>$')} years & {n(dus[du_order[i]])}" if i < len(du_order) else "&"375 body += f"{a} & {b} & {c} \\\\\n"376body += "\\bottomrule\n\\end{tabular}"377w("tab_status.tex", body)378P = S["projects"]; known = P - dus["unknown"]379macro("spanKnownN", n(known), "data/duration.csv", "projects - unknown")380macro("spanUnknownN", n(dus["unknown"]), "data/duration.csv", "n where span_years=unknown")381macro("spanUnknownShareAll", f"{100*dus['unknown']/P:.1f}", "data/duration.csv", "100*unknown/projects")382macro("spanZeroShareAll", f"{100*dus['0']/P:.1f}", "data/duration.csv", "100*n(0)/projects")383macro("spanZeroShareKnown", f"{100*dus['0']/known:.1f}", "data/duration.csv", "100*n(0)/(projects-unknown)")384macro("spanOverFiveShareAll", f"{100*dus['>5']/P:.1f}", "data/duration.csv", "100*n(>5)/projects")385macro("spanOverFiveShareKnown", f"{100*dus['>5']/known:.1f}", "data/duration.csv", "100*n(>5)/(projects-unknown)")386for r in st:387 macro("statusShare" + "".join(p.capitalize() for p in r["status"].split("_")), r["pct"], "data/projects_by_status.csv", f"pct where status={r['status']}")388mbd = {r["bucket"]: int(r["n"]) for r in mb}389macro("singleMentionShare", f"{100*mbd['1']/P:.1f}", "data/mentions_per_project.csv", "100*n(bucket=1)/projects")390macro("overTenMentionShare", f"{100*mbd['>10']/P:.1f}", "data/mentions_per_project.csv", "100*n(bucket=>10)/projects")391392# ---- confidence ---------------------------------------------------------------------393body = "\\begin{tabular}{lr}\n\\toprule\nSelf-reported confidence (rounded) & Mentions \\\\\n\\midrule\n"394for r in cf: body += f"{r['conf']} & {n(r['n'])} \\\\\n"395body += "\\bottomrule\n\\end{tabular}"396w("tab_confidence.tex", body)397M = S["mentions"]; cfd = {float(r["conf"]): int(r["n"]) for r in cf}398macro("confShareOne", f"{100*cfd.get(1.0, 0)/M:.1f}", "data/confidence_dist.csv", "100*n(conf=1.0)/mentions")399macro("confShareNine", f"{100*cfd.get(0.9, 0)/M:.1f}", "data/confidence_dist.csv", "100*n(conf=0.9)/mentions")400macro("confShareLeSix", f"{100*sum(v for k, v in cfd.items() if k <= 0.6)/M:.1f}", "data/confidence_dist.csv", "100*n(conf<=0.6)/mentions")401if S.get("conf_exact_default_count") is not None:402 macro("confExactDefaultCount", n(S["conf_exact_default_count"]), "stats.json:conf_exact_default_count", "count(round(confidence,3)=0.600)")403404# ---- Table 8: amounts ----------------------------------------------------------------405ppi = json.loads(rows("projects_per_issuer")[0]["q"])406body = f"""\\begin{{tabular}}{{lrrrrrr}}407\\toprule408 & P10 & P25 & Median & P75 & P90 & P99 \\\\409\\midrule410Largest 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} \\\\411Projects 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} \\\\412\\bottomrule413\\end{{tabular}}""".replace(",", "{,}")414w("tab_amounts.tex", body)415macro("amountMentions", n(S["mentions_fields"][0]), "stats.json:mentions_fields[0]", "count(amount_usd is not null)")416macro("amountMentionsShare", f"{100*S['mentions_fields'][0]/M:.1f}", "stats.json", "100*mentions_fields[0]/mentions")417macro("amountProjects", n(S["projects_fields"][0]), "stats.json:projects_fields[0]", "count(total_amount_usd is not null)")418macro("amountProjectsShare", f"{100*S['projects_fields'][0]/P:.1f}", "stats.json", "100*projects_fields[0]/projects")419macro("amountP25Musd", f"{q[1]/1e6:,.0f}".replace(",", "{,}"), "data/amount_dist.csv", "q[1]/1e6")420macro("amountP75Musd", f"{q[3]/1e6:,.0f}".replace(",", "{,}"), "data/amount_dist.csv", "q[3]/1e6")421macro("amountP99Busd", f"{q[5]/1e9:,.1f}", "data/amount_dist.csv", "q[5]/1e9")422macro("amountSumTusd", f"{float(ad['sum_gusd'])/1e3:,.1f}", "data/amount_dist.csv", "sum_gusd/1e3")423amt_note = ("\\item Amounts are the largest USD figure cited across the mentions of a project; they mix transaction values, budgets, contract values "424 "and estimates and are never summed in the text. ")425if S.get("amount_max") is not None:426 macro("amountMaxBusd", f"{S['amount_max']/1e9:,.1f}".replace(",", "{,}"), "stats.json:amount_max", "max(total_amount_usd)/1e9")427 amt_note += f"The maximum is {S['amount_max']/1e9:,.1f}~B USD. ".replace(",", "{,}")428if S.get("amount_ge_50b") is not None:429 macro("amountGeFiftyB", n(S["amount_ge_50b"]), "stats.json:amount_ge_50b", "count(total_amount_usd >= 50e9)")430 amt_note += f"{n(S['amount_ge_50b'])} projects carry an amount of 50~B USD or more. "431if S.get("amount_le_zero") is not None:432 macro("amountLeZero", n(S["amount_le_zero"]), "stats.json:amount_le_zero", "count(total_amount_usd <= 0)")433w("tab_amounts_note.tex", amt_note + "\n")434435# ---- knowledge graph --------------------------------------------------------------------436kg = rows("kg")437body = "\\begin{tabular}{llr}\n\\toprule\n & Type & Count \\\\\n\\midrule\n"438for r in kg: body += f"{r['k'].capitalize()} & \\texttt{{{r['node_type'].replace('_', '\\_')}}} & {n(r['n'])} \\\\\n"439body += "\\bottomrule\n\\end{tabular}"440w("tab_graph.tex", body)441macro("kgNodes", n(sum(int(r["n"]) for r in kg if r["k"] == "nodes")), "data/kg.csv", "sum(n) where k=nodes")442macro("kgEdges", n(sum(int(r["n"]) for r in kg if r["k"] == "edges")), "data/kg.csv", "sum(n) where k=edges")443for r in kg:444 if r["k"] == "nodes": macro("kgNodes" + r["node_type"].capitalize(), n(r["n"]), "data/kg.csv", f"n where node_type={r['node_type']}")445446# ---- top issuers ---------------------------------------------------------------------------447body = "\\begin{tabular}{llr}\n\\toprule\nTicker & Issuer & Resolved projects \\\\\n\\midrule\n"448for r in rows("top_issuers")[:12]: body += f"{r['ticker']} & {r['company_name'].title().replace('&', '\\&')} & {n(r['projects'])} \\\\\n"449body += "\\bottomrule\n\\end{tabular}"450w("tab_top_issuers.tex", body)451452# ---- Table 9: provenance example (Cheniere Corpus Christi) ------------------------------------453ex = [r for r in rows("example_provenance") if r["ticker"] == "LNG"]454body = "\\begin{tabular}{llllp{6.2cm}}\n\\toprule\nAccession & Filed & Form & Status / amount & Evidence snippet (model-generated description grounded in the section) \\\\\n\\midrule\n"455for r in ex[:8]:456 amt = f"{float(r['amount_usd'])/1e9:.1f} B USD" if r["amount_usd"] else "--"457 snip = r["snippet"].replace("&", "\\&").replace("%", "\\%").replace("$", "\\$")[:DI["snippet_chars"]["value"]]458 body += f"\\texttt{{{r['accession_number']}}} & {r['filing_date']} & {r['form_type']} & {r['m_status'].replace('_', '\\_')} / {amt} & {snip} \\\\\n"459body += "\\bottomrule\n\\end{tabular}"460w("tab_provenance.tex", body)461if ex:462 forms = sorted({r["form_type"] for r in ex[:8]})463 macro("provenanceForms", " and ".join(forms), "data/example_provenance.csv", "distinct form_type of the printed rows")464 macro("provenanceN", str(len(ex[:8])), "data/example_provenance.csv", "rows printed")465 macro("provenanceFirst", ex[0]["filing_date"], "data/example_provenance.csv", "min filing_date")466 macro("provenanceLast", ex[:8][-1]["filing_date"], "data/example_provenance.csv", "max filing_date")467 macro("provenanceMaxAmountBusd", f"{max(float(r['amount_usd']) for r in ex if r['amount_usd'])/1e9:.1f}", "data/example_provenance.csv", "max(amount_usd)/1e9")468 macro("provenanceNameYear", ex[:8][-1]["filing_date"][:4], "data/example_provenance.csv", "filing year of the last mention (representative name source)")469 json.dump({k: ex[0][k] for k in ("project_id", "ticker", "project_name", "project_type", "status", "total_amount_usd", "n_mentions", "first_seen", "last_seen")},470 open(DATA_OUT / "example_project.json", "w"), indent=2)471472# ---- Table 12: data-model audit (Appendix C) as a longtable -------------------------------------473if (D / "datamodel_audit.csv").exists():474 body = ("\\begin{longtable}{p{3.1cm}p{8.6cm}p{4.3cm}}\n\\caption{Tables required by the research design, current implementation and status}"475 "\\label{tab:datamodel}\\\\\n\\toprule\nRequired table & Current implementation (database, table, key fields) & Status \\\\\n\\midrule\n\\endfirsthead\n"476 "\\toprule\nRequired table & Current implementation (database, table, key fields) & Status \\\\\n\\midrule\n\\endhead\n\\bottomrule\n\\endfoot\n")477 for r in rows("datamodel_audit"):478 body += f"\\texttt{{{r['required_table'].replace('_', '\\_')}}} & {r['implementation']} & {r['status']} \\\\\n"479 body += "\\end{longtable}"480 w("tab_datamodel.tex", body)481482# ---- Appendix B: candidate gate regex and system prompt, read from the frozen snapshot ----------483def _snapshot(name):484 for base in (SNAP, ROOT.parent.parent / "manuscripts" / "FIRC-0001" / "pipeline_code_2324b58" / "project-db" / "spid"):485 for p in (base / name, base / "extract" / name):486 if p.exists(): return p487 return None488489rules_py = _snapshot("rules.py")490if rules_py:491 tree = ast.parse(rules_py.read_text())492 pieces, flags = None, ""493 for node in ast.walk(tree):494 if isinstance(node, ast.Assign) and any(isinstance(t, ast.Name) and t.id == "_PROJECT_GATE" for t in node.targets):495 call = node.value; arg = call.args[0]496 # the pattern is written as adjacent raw-string literals; keep the author's line breaks497 seg = rules_py.read_text().splitlines()[arg.lineno - 1: arg.end_lineno]498 pieces = [re.match(r'\s*r"(.*)"\s*,?\s*$', s).group(1) for s in seg]499 flags = ", ".join(ast.unparse(a) for a in call.args[1:])500 if pieces:501 full = "".join(pieces)502 lines = "\n".join(pieces)503 w("gate_regex.tex", "\\begin{verbatim}\n" + lines + "\n\\end{verbatim}\n")504 macro("gateRegexFlags", esc(flags), str(rules_py.relative_to(ROOT)) if rules_py.is_relative_to(ROOT) else str(rules_py), "re.compile flags")505 macro("gateRegexLength", str(len(full)), "rules.py:_PROJECT_GATE", "len(pattern)")506 macro("gateRegexAlternatives", str(full.count("|") + 1), "rules.py:_PROJECT_GATE", "number of alternatives")507508llm_py, tax_py = _snapshot("llm.py"), _snapshot("taxonomy.py")509if llm_py and tax_py:510 ttree = ast.parse(tax_py.read_text()); types = None511 for node in ast.walk(ttree):512 if isinstance(node, (ast.Assign, ast.AnnAssign)):513 tg = node.targets[0] if isinstance(node, ast.Assign) else node.target514 if isinstance(tg, ast.Name) and tg.id == "PROJECT_TYPES":515 types = ast.literal_eval(node.value)516 ltree = ast.parse(llm_py.read_text()); tmpl = None517 for node in ast.walk(ltree):518 if isinstance(node, ast.Assign) and any(isinstance(t, ast.Name) and t.id == "_SYSTEM" for t in node.targets):519 v = node.value520 if isinstance(v, ast.BinOp) and isinstance(v.left, ast.Constant): tmpl = v.left.value521 elif isinstance(v, ast.Constant): tmpl = v.value522 if types and tmpl and "%s" in tmpl:523 labels = "\n".join(f" - {k}: {v['label']}" for k, v in types.items())524 prompt = tmpl % labels525 w("prompt_verbatim.tex", "\\begin{verbatim}\n" + prompt + "\n\\end{verbatim}\n")526 macro("taxonomyN", str(len(types)), "taxonomy.py:PROJECT_TYPES", "len")527 body = "\\begin{tabular}{ll}\n\\toprule\nCode & Label \\\\\n\\midrule\n"528 for k, v in types.items(): body += f"\\texttt{{{k.replace('_', '\\_')}}} & {esc(v['label'])} \\\\\n"529 body += "\\bottomrule\n\\end{tabular}"530 w("tab_taxonomy.tex", body)531532# ---- Stage D audit tables from results/ (generic CSV -> tabular; skipped when absent) ---------------533def csv_table(texname, path, caption_cols=None, max_rows=60):534 if not path.exists():535 print("skip", texname, "(missing", path.relative_to(ROOT), ")"); return536 with open(path, newline="") as f:537 rd = csv.DictReader(f); cols = rd.fieldnames; data = list(rd)538 def fmt(v):539 try:540 x = float(v)541 if x.is_integer() and abs(x) >= 1e3: return n(x)542 return v if x.is_integer() else f"{x:,.3f}".rstrip("0").rstrip(".").replace(",", "{,}")543 except ValueError:544 return esc(v)545 spec = "l" + "r" * (len(cols) - 1)546 body = f"\\begin{{tabular}}{{{spec}}}\n\\toprule\n" + " & ".join(esc(c) for c in cols) + " \\\\\n\\midrule\n"547 for r in data[:max_rows]: body += " & ".join(fmt(r[c]) for c in cols) + " \\\\\n"548 if len(data) > max_rows: body += f"\\multicolumn{{{len(cols)}}}{{l}}{{\\ldots {len(data) - max_rows} more rows in \\texttt{{{esc(str(path.relative_to(ROOT)))}}}}} \\\\\n"549 body += "\\bottomrule\n\\end{tabular}"550 w(texname, body)551552csv_table("tab_spec_curve.tex", R / "resolver" / "spec_curve.csv")553csv_table("tab_grounding.tex", R / "grounding" / "grounding_by_field_form_item.csv")554csv_table("tab_focus.tex", R / "focus" / "focus_coverage_by_form_item.csv")555csv_table("tab_cohort.tex", R / "runs" / "cohort_by_run.csv" if (R / "runs" / "cohort_by_run.csv").exists() else D / "cohort_by_run.csv")556csv_table("tab_fp_classes.tex", R / "fp_classes" / "financing_regex_by_item_type.csv")557csv_table("tab_timeseries_cohort.tex", R / "timeseries" / "first_seen_fixed_cohort.csv")558csv_table("tab_benchmark_8k.tex", R / "benchmark_8k" / "item_vs_type.csv")559csv_table("tab_rules_agreement.tex", R / "rules" / "agreement_by_form_item.csv")560561# ---- macros from the Stage D audit results (results/**.json; skipped when absent) ------------------562def jload(rel):563 p = R / rel564 return json.load(open(p)) if p.exists() else {}565566def jmacro(name, obj, key, src, fmt=None):567 v = obj.get(key) if isinstance(obj, dict) else None568 if v is None or isinstance(v, (list, dict)): return569 macro(name, fmt(v) if fmt else (n(v) if isinstance(v, (int, float)) and float(v).is_integer() else str(v)), src, key)570571gt = jload("ledger/gate_pass_totals.json")572for k, mname, fmt in (("calls", "callsTotal", None), ("gate_pass", "gatePassSections", None), ("gate_rejected_zero", "gateRejectedSections", None),573 ("lt30_words", "ledgerUnderThirtyWords", None), ("productive_pct_calls", "productiveShareCalls", lambda v: f"{v:.1f}"),574 ("mentions_per_call", "mentionsPerCall", lambda v: f"{v:.2f}"), ("usd_per_call_paper_total_775", "usdPerCall", lambda v: f"{v:.4f}"),575 ("october_calls", "octoberCalls", None), ("october_rows", "octoberRows", None)):576 jmacro(mname, gt, k, "results/ledger/gate_pass_totals.json", fmt)577tp = jload("ledger/throughput.json")578for k, mname, fmt in (("wall_clock_hours_first_to_last_flush", "octoberWallClockHours", lambda v: f"{v:.1f}"), ("largest_gap_hours", "outageHours", lambda v: f"{v:.1f}"),579 ("effective_hours_excluding_largest_gap", "octoberEffectiveHours", lambda v: f"{v:.1f}"),580 ("rows_per_second_effective", "octoberRowsPerSecondEffective", lambda v: f"{v:.1f}"), ("rows_per_second_wall_clock", "octoberRowsPerSecondWallClock", lambda v: f"{v:.1f}"),581 ("calls_per_second_effective", "octoberCallsPerSecondEffective", lambda v: f"{v:.1f}"), ("october_first_flush", "octoberFirstFlush", lambda v: str(v)[:16]),582 ("october_last_flush", "octoberLastFlush", lambda v: str(v)[:16])):583 jmacro(mname, tp, k, "results/ledger/throughput.json", fmt)584bb = jload("ledger/backfill_block.json")585for k, mname in (("rows_at_min_timestamp", "backfilledRows"), ("june_log_sections", "juneLogSections"), ("block_lt30_words", "backfilledUnderThirtyWords"),586 ("block_stored_mentions", "backfilledMentions"), ("block_ciks", "backfilledCiks"), ("block_gate_fail", "backfilledGateFail"), ("rows_in_outage_window", "rowsInOutageWindow")):587 jmacro(mname, bb, k, "results/ledger/backfill_block.json")588if isinstance(bb.get("outage_window"), list) and len(bb["outage_window"]) == 2:589 macro("outageWindowStart", str(bb["outage_window"][0])[:16], "results/ledger/backfill_block.json", "outage_window[0]")590 macro("outageWindowEnd", str(bb["outage_window"][1])[:16], "results/ledger/backfill_block.json", "outage_window[1]")591ls_ = jload("ledger/log_summary.json")592oc_ = ls_.get("october", {}) if isinstance(ls_.get("october"), dict) else {}593for k, mname in (("llm_call_failed_total", "llmCallFailedTotal"), ("llm_call_failed_inside_window", "llmCallFailedInsideOutage"), ("llm_call_failed_outside_window", "llmCallFailedOutsideOutage")):594 jmacro(mname, oc_, k, "results/ledger/log_summary.json:october")595if isinstance(oc_.get("llm_call_failed_by_reason"), dict):596 for k, v in oc_["llm_call_failed_by_reason"].items(): macro("llmFailed" + "".join(p.capitalize() for p in k.split("_")), n(v), "results/ledger/log_summary.json:october.llm_call_failed_by_reason", k)597jn_ = ls_.get("june", {}) if isinstance(ls_.get("june"), dict) else {}598jmacro("juneLlmCallFailed", jn_, "llm_call_failed_total", "results/ledger/log_summary.json:june")599co = jload("ledger/collisions.json")600for k, mname in (("sections_ledger_gt_stored", "collisionSections"), ("excess_mentions", "collisionMentions")):601 jmacro(mname, co, k, "results/ledger/collisions.json")602cp = jload("resolver/composition.json")603for k, mname in (("projects_keyed_general", "projectsKeyedGeneral"), ("mentions_keyed_general", "mentionsKeyedGeneral")):604 jmacro(mname, cp, k, "results/resolver/composition.json")605if isinstance(cp.get("projects_by_key_branch"), list):606 for br, v in cp["projects_by_key_branch"]:607 macro("projectsKeyed" + "".join(p.capitalize() for p in str(br).split("_")), n(v), "results/resolver/composition.json", f"projects_by_key_branch[{br}]")608 macro("projectsKeyed" + "".join(p.capitalize() for p in str(br).split("_")) + "Pct", f"{100*v/P:.1f}", "results/resolver/composition.json", f"100*projects_by_key_branch[{br}]/projects")609ti = jload("resolver/ties_idempotence.json")610for k, mname in (("projects_with_tie_at_max_confidence", "projectsTieMaxConfidence"), ("projects_with_tie_and_distinct_names", "projectsTieDistinctNames"),611 ("projects_with_status_tie_on_last_seen", "projectsStatusTie"), ("mentions_at_confidence_1", "mentionsAtConfidenceOne")):612 jmacro(mname, ti, k, "results/resolver/ties_idempotence.json")613sc_path = R / "resolver" / "spec_curve.csv"614if sc_path.exists():615 sc = rows(sc_path.stem, sc_path.parent)616 thr = min(sc, key=lambda r: float(r["threshold"]))["threshold"]617 at = [r for r in sc if r["threshold"] == thr]618 pr = [int(float(r["projects"])) for r in at]619 macro("projectCountRangeKeys", f"{n(min(pr))}--{n(max(pr))}", "results/resolver/spec_curve.csv", f"min/max projects over keys at threshold {thr}")620 macro("projectCountRangeKeysN", str(len(at)), "results/resolver/spec_curve.csv", f"number of keys at threshold {thr}")621 pall = [int(float(r["projects"])) for r in sc]622 macro("projectCountRangeAll", f"{n(min(pall))}--{n(max(pall))}", "results/resolver/spec_curve.csv", "min/max projects over all keys x thresholds")623 cur = [r for r in at if "current" in r["key"].lower()]624 if cur: macro("projectCountCurrentKey", n(cur[0]["projects"]), "results/resolver/spec_curve.csv", f"key={cur[0]['key']} at {thr}")625tn_path = R / "resolver" / "top_name_tokens.csv"626if tn_path.exists():627 tn = rows(tn_path.stem, tn_path.parent)[:10]628 macro("topNameTokens", ", ".join(f"\\texttt{{{esc(r['name_tok'])}}} ({float(r['pct_of_tokenkeyed_projects']):.1f}\\%)" for r in tn), "results/resolver/top_name_tokens.csv", "top-10 tokens, pct of token-keyed projects")629 macro("topNameTokensProjectsPct", f"{sum(float(r['pct_of_tokenkeyed_projects']) for r in tn):.1f}", "results/resolver/top_name_tokens.csv", "sum of pct_of_tokenkeyed_projects over the top 10")630csv_table("tab_top_name_tokens.tex", R / "resolver" / "top_name_tokens.csv", max_rows=20)631csv_table("tab_overmerge.tex", R / "resolver" / "overmerge_by_type.csv")632csv_table("tab_june_october.tex", R / "resolver" / "june_october_reconciliation.csv")633csv_table("tab_table3_calls.tex", R / "ledger" / "table3_ledger_vs_stored.csv")634635# ---- headline macros ---------------------------------------------------------------------------------636macro("projects", n(P), "stats.json:projects", "count(projects)")637macro("mentions", n(M), "stats.json:mentions", "count(project_mentions)")638macro("issuersWithProjects", n(S["issuers_with_projects"]), "stats.json:issuers_with_projects", "count(distinct cik)")639macro("issuersWithFilings", n(S["issuers_with_filings"]), "stats.json:issuers_with_filings", "count(distinct cik) in filings")640macro("sectionsTotal", n(S["sections_total"]), "stats.json:sections_total", "count(filing_sections)")641macro("chunks", n(S["chunks"][0]), "stats.json:chunks[0]", "count(filing_chunks)")642macro("projectsWithLocation", n(S["projects_fields"][1]), "stats.json:projects_fields[1]", "count(canonical_location is not null)")643macro("projectsTokenKeyedShare", f"{100*(P - S['projects_fields'][1])/P:.1f}", "stats.json:projects_fields", "100*(projects - with location)/projects")644macro("projectsPerIssuerMean", f"{float(rows('projects_per_issuer')[0]['mean']):.1f}", "data/projects_per_issuer.csv", "mean")645macro("projectsPerIssuerMedian", f"{ppi[2]:.0f}", "data/projects_per_issuer.csv", "q[2]")646macro("extractedAtFirst", S["extracted_at_range"][0][:19], "stats.json:extracted_at_range[0]", "min(extracted_at)")647macro("extractedAtLast", S["extracted_at_range"][1][:19], "stats.json:extracted_at_range[1]", "max(extracted_at)")648for key, mname in (("ledger_10k_mdna_median_words", "tenKmdnaMedianWords"), ("annual_panel_firms", "annualPanelFirms"), ("sp500_ciks", "spFiveHundredCiks"),649 ("backfilled_rows", "backfilledRows"), ("backfilled_mentions", "backfilledMentions"), ("outage_rows", "outageRows"),650 ("llm_failed_outside_outage", "llmFailedOutsideOutage"), ("llm_failed_inside_outage", "llmFailedInsideOutage"), ("june_failed_calls", "juneFailedCalls")):651 if S.get(key) is not None: macro(mname, n(S[key]), f"stats.json:{key}", "export_stats.py")652if S.get("spid_runs"):653 for k, v in S["spid_runs"].items():654 if isinstance(v, dict):655 for kk, vv in v.items():656 if vv is not None: macro("run" + "".join(p.capitalize() for p in k.split("_")) + "".join(p.capitalize() for p in kk.split("_")), esc(vv), f"stats.json:spid_runs.{k}.{kk}", "export_stats.py")657if di("brief_definition"): macro("brief", di("brief_definition"), "data/documented_inputs.json:brief_definition", "documented input (author to confirm)")658659# ---- Figures ---------------------------------------------------------------------------------------------660BLUE, GOLD, GREY = "#16365c", "#c9a227", "#8a8a8a"661662663def save(fig, name):664 """PDF for the paper and PNG (same figure) for the Word export; no other script produces the PNGs."""665 fig.savefig(F / f"{name}.pdf", metadata={"CreationDate": None})666 fig.savefig(F / f"{name}.png", dpi=int(DI["png_dpi"]["value"]))667 plt.close(fig)668yrs = [int(r["yr"]) for r in iy]669fig, ax1 = plt.subplots(figsize=(7.2, 3.4))670ax1.bar(yrs, [int(r["filings"]) for r in iy], color=BLUE, alpha=.85, label="Filings (10-K, 10-Q, 8-K)")671ax1.set_ylabel("Filings per year"); ax1.set_xlabel("Filing year")672ax1.xaxis.set_major_locator(MaxNLocator(integer=True))673ax2 = ax1.twinx(); ax2.plot(yrs, [int(r["issuers"]) for r in iy], color=GOLD, marker="o", lw=2, label="Issuers with $\\geq$1 filing")674ax2.set_ylabel("Issuers"); ax2.set_ylim(0, U["n"] * 1.05)675h1, 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)676for a in (ax1, ax2): a.spines[["top"]].set_visible(False)677fig.tight_layout(); save(fig, "fig_coverage")678679fig, ax = plt.subplots(figsize=(7.2, 5.2))680labs = [LAB[r["project_type"]].replace("\\&", "&") for r in pt][::-1]; vals = [int(r["n"]) for r in pt][::-1]681ax.barh(labs, vals, color=BLUE); ax.set_xlabel("Resolved projects"); ax.spines[["top", "right"]].set_visible(False)682for i, v in enumerate(vals): ax.text(v + max(vals) * 0.007, i, f"{v:,}", va="center", fontsize=7)683ax.xaxis.set_major_locator(MaxNLocator(integer=True))684fig.tight_layout(); save(fig, "fig_types")685686fig, (ax, axb) = plt.subplots(2, 1, figsize=(7.2, 5.6), sharex=True, gridspec_kw={"height_ratios": [3, 2]})687ax.plot(yrs, [int(my[str(y)]["mentions"]) for y in yrs], color=BLUE, marker="o", lw=2, label="Project mentions")688ax.plot(yrs, [int(py[str(y)]["n"]) for y in yrs], color=GOLD, marker="s", lw=2, label="Projects first observed")689ax.set_ylabel("Count"); ax.set_ylim(0, None); ax.legend(frameon=False, fontsize=8); ax.spines[["top", "right"]].set_visible(False)690ax.axvspan(yrs[0] - 0.5, yrs[0] + 0.5, color=GREY, alpha=.15); ax.axvspan(yrs[-1] - 0.5, yrs[-1] + 0.5, color=GREY, alpha=.15)691ax.text(yrs[0], ax.get_ylim()[1] * 0.97, "left-censored", fontsize=7, ha="center", va="top", color=GREY)692ax.text(yrs[-1], ax.get_ylim()[1] * 0.97, "partial year", fontsize=7, ha="center", va="top", color=GREY)693axb.plot(yrs, [int(my[str(y)]["mentions"]) / int(my[str(y)]["issuers"]) for y in yrs], color=BLUE, marker="o", lw=2, label="Mentions per issuer with $\\geq$1 mention")694axb.plot(yrs, [int(py[str(y)]["n"]) / int(my[str(y)]["issuers"]) for y in yrs], color=GOLD, marker="s", lw=2, label="Projects first observed per issuer with $\\geq$1 mention")695axb.set_xlabel("Filing year"); axb.set_ylabel("Per issuer"); axb.set_ylim(0, None); axb.legend(frameon=False, fontsize=8); axb.spines[["top", "right"]].set_visible(False)696axb.xaxis.set_major_locator(MaxNLocator(integer=True))697fig.tight_layout(); save(fig, "fig_mentions_year")698macro("newProjectsPerIssuerFirstYear", f"{int(py[str(y_first)]['n'])/int(my[str(y_first)]['issuers']):.1f}", "data/projects_by_first_seen_year.csv, mentions_by_year.csv", "first seen / issuers with mentions")699macro("newProjectsPerIssuerLastFullYear", f"{int(py[str(inner[-1])]['n'])/int(my[str(inner[-1])]['issuers']):.1f}", "data/projects_by_first_seen_year.csv, mentions_by_year.csv", "first seen / issuers with mentions")700701fig, ax = plt.subplots(figsize=(7.2, 3.0))702ax.bar([float(r["conf"]) for r in cf], [int(r["n"]) for r in cf], width=0.08, color=BLUE)703ax.set_xlabel("Self-reported extraction confidence"); ax.set_ylabel("Mentions"); ax.spines[["top", "right"]].set_visible(False)704fig.tight_layout(); save(fig, "fig_confidence")705print("figures ok")706707# ---- inline-number macros ---------------------------------------------------------------------------------708lines = ["% Generated by code/make_tables.py — inline numbers of the paper. Do not edit; use \\SPID<name> in the text.",709 "% Source and formula of every macro: results/inline_numbers.json"]710for k, v in INLINE.items():711 lines.append(f"\\newcommand{{\\SPID{k}}}{{{v['value']}}}")712(T / "numbers.tex").write_text("\n".join(lines) + "\n")713json.dump(INLINE, open(RES_OUT / "inline_numbers.json", "w"), indent=1, ensure_ascii=False)714print(f"numbers.tex: {len(INLINE)} macros; stats.json untouched")715