spb/firc0001_uqo

1"""Export every number used in the working paper from the two DuckDB databases (run on the master node).23Usage (on M3U96b):  ~/UQO_RESEARCH_DB/.venv/bin/python export_stats.py ~/UQO_RESEARCH_DB <out_dir>4Writes CSV files (one per table) + stats.json. Nothing is hand-edited downstream.5"""6import csv, json, sys7from pathlib import Path8import duckdb910root = Path(sys.argv[1]); out = Path(sys.argv[2]); out.mkdir(parents=True, exist_ok=True)11edgar = duckdb.connect(str(root / "data/duckdb/edgar_research.duckdb"), read_only=True)12spid = duckdb.connect(str(root / "project-db/data/db/spid.duckdb"), read_only=True)13# the src_* views stored in spid.duckdb point to the master node's parquet corpus (valid here)14S = {}1516def dump(name, con, sql):17    cur = con.execute(sql); cols = [d[0] for d in cur.description]; rows = cur.fetchall()18    with open(out / f"{name}.csv", "w", newline="") as f:19        w = csv.writer(f); w.writerow(cols); w.writerows(rows)20    print(f"{name}: {len(rows)} rows"); return rows2122# ---- universe reconciliation -------------------------------------------------23uni = json.load(open(root / "data/metadata/universe.json"))24ru = json.load(open(root / "data/metadata/russell3000_constituents.json"))25sp = json.load(open(root / "data/metadata/sp500_constituents.json"))26S["universe"] = {"built_at": uni["built_at"], "n": uni["count"], "n_sp500": uni["n_sp500"], "n_russell": uni["n_russell3000"],27                 "russell_holdings_as_of": ru["holdings_as_of"], "russell_fetched_at": ru["fetched_at"],28                 "russell_equity_lines_mapped": ru["count"], "russell_unmatched": len(ru["unmatched"]),29                 "russell_unmatched_list": ru["unmatched"], "sp500_fetched_at": sp["fetched_at"], "sp500_rows": sp["count"],30                 "russell_distinct_ciks": len({c["cik"] for c in ru["constituents"]}),31                 "both": sum(1 for c in uni["constituents"] if c["in_sp500"] and c["in_russell3000"]),32                 "sp_only": sum(1 for c in uni["constituents"] if c["in_sp500"] and not c["in_russell3000"]),33                 "ru_only": sum(1 for c in uni["constituents"] if not c["in_sp500"] and c["in_russell3000"])}34S["issuers_with_filings"] = edgar.execute("select count(distinct cik) from filings").fetchone()[0]35S["issuers_without_filings"] = edgar.execute("select count(*) from companies c where not exists (select 1 from filings f where f.cik=c.cik)").fetchone()[0]36dump("issuers_without_filings", edgar, "select ticker, company_name, sector, in_sp500, in_russell3000 from companies c where not exists (select 1 from filings f where f.cik=c.cik) order by ticker")37S["issuers_with_projects"] = spid.execute("select count(distinct cik) from projects").fetchone()[0]3839# ---- corpus -------------------------------------------------------------------40dump("filings_by_form", edgar, "select form_type, count(*) n, min(filing_date) first, max(filing_date) last, round(sum(file_size_bytes)/1e9,1) gb from filings group by 1 order by 1")41dump("filings_by_year_form", edgar, "select extract(year from filing_date)::int yr, form_type, count(*) n, count(distinct cik) issuers from filings group by 1,2 order by 1,2")42dump("issuers_by_year", edgar, "select extract(year from filing_date)::int yr, count(distinct cik) issuers, count(*) filings from filings group by 1 order by 1")43dump("parse_status", edgar, "select form_type, parse_status, count(*) n from filings group by 1,2 order by 1,2")44dump("sections_by_form", edgar, "select f.form_type, count(*) sections, round(avg(s.word_count)) mean_words, round(median(s.word_count)) median_words from filing_sections s join filings f using(accession_number) group by 1 order by 1")45dump("sections_10k_by_item", edgar, "select s.section_name, count(*) n, round(median(s.word_count)) median_words from filing_sections s join filings f using(accession_number) where f.form_type='10-K' group by 1 order by 2 desc")46S["chunks"] = edgar.execute("select count(*), round(avg(word_count),1) from filing_chunks").fetchone()47S["embeddings"] = edgar.execute("select count(*) from embeddings_metadata").fetchone()[0]48S["sections_total"] = edgar.execute("select count(*) from filing_sections").fetchone()[0]49S["filings_total"] = edgar.execute("select count(*) from filings").fetchone()[0]50S["raw_gb"] = edgar.execute("select round(sum(file_size_bytes)/1e9,1) from filings").fetchone()[0]51dump("companies_by_sector", edgar, "select sector, count(*) n, count(*) filter (where in_sp500) sp500, count(*) filter (where in_russell3000) russell from companies group by 1 order by 2 desc")52dump("companies_by_shard", edgar, "select shard_node, count(*) n from companies group by 1 order by 1")53dump("factor_tables", edgar, "select 'chunk_scores' t, count(*) n from chunk_scores union all select 'filing_scores', count(*) from filing_scores union all select 'annual_panel', count(*) from annual_panel union all select 'quarterly_panel', count(*) from quarterly_panel")5455# ---- extraction ledger --------------------------------------------------------56dump("ledger_by_form", spid, "select v.form_type, count(*) sections, count(*) filter (where e.n_mentions>0) with_mentions, sum(e.n_mentions) mentions, count(distinct v.cik) issuers from extracted_sections e join v_sections v using(section_id) group by 1 order by 1")57S["ledger_total"] = spid.execute("select count(*) from extracted_sections").fetchone()[0]58dump("ledger_10k_items", spid, "select v.section_name, count(*) n, sum(e.n_mentions) mentions from extracted_sections e join v_sections v using(section_id) where v.form_type='10-K' group by 1 order by 2 desc")59dump("ledger_10q_items", spid, "select v.section_name, count(*) n, sum(e.n_mentions) mentions from extracted_sections e join v_sections v using(section_id) where v.form_type='10-Q' group by 1 order by 2 desc")6061# ---- mentions / projects ------------------------------------------------------62S["mentions"] = spid.execute("select count(*) from project_mentions").fetchone()[0]63S["projects"] = spid.execute("select count(*) from projects").fetchone()[0]64dump("mentions_by_form", spid, "select form_type, count(*) n, round(avg(confidence),3) mean_conf from project_mentions group by 1 order by 2 desc")65dump("mentions_by_year", spid, "select extract(year from filing_date)::int yr, count(*) mentions, count(distinct cik) issuers from project_mentions group by 1 order by 1")66dump("projects_by_type", spid, "select project_type, count(*) n, round(100.0*count(*)/(select count(*) from projects),1) pct, count(*) filter (where total_amount_usd is not null) with_amount, round(median(total_amount_usd)/1e6,1) median_amount_musd, round(avg(n_mentions),2) mean_mentions from projects group by 1 order by 2 desc")67dump("projects_by_sector", spid, "select coalesce(sector,'(unknown)') sector, count(*) n, count(distinct cik) issuers, round(count(*)*1.0/count(distinct cik),1) per_issuer from projects group by 1 order by 2 desc")68dump("projects_by_status", spid, "select status, count(*) n, round(100.0*count(*)/(select count(*) from projects),1) pct from projects group by 1 order by 2 desc")69dump("projects_by_first_seen_year", spid, "select extract(year from first_seen)::int yr, count(*) n from projects group by 1 order by 1")70dump("mentions_per_project", spid, "select case when n_mentions=1 then '1' when n_mentions=2 then '2' when n_mentions<=5 then '3-5' when n_mentions<=10 then '6-10' else '>10' end bucket, count(*) n from projects group by 1 order by min(n_mentions)")71dump("projects_per_issuer", spid, "select quantile_cont(n, [0.1,0.25,0.5,0.75,0.9,0.99]) q, min(n) mn, max(n) mx, avg(n) mean from (select cik, count(*) n from projects group by 1)")72dump("projects_by_index", spid, "select co.in_sp500, co.in_russell3000, count(*) projects, count(distinct p.cik) issuers from projects p join src_companies co on p.cik=co.cik group by 1,2")73dump("confidence_dist", spid, "select round(confidence,1) conf, count(*) n from project_mentions group by 1 order by 1")74dump("amount_dist", spid, "select quantile_cont(total_amount_usd, [0.1,0.25,0.5,0.75,0.9,0.99]) q, count(*) n, round(sum(total_amount_usd)/1e9,1) sum_gusd from projects where total_amount_usd is not null")75dump("duration", spid, "select case when last_year is null or first_year is null then 'unknown' when last_year=first_year then '0' when last_year-first_year<=2 then '1-2' when last_year-first_year<=5 then '3-5' else '>5' end span_years, count(*) n from projects group by 1 order by 1")76dump("kg", spid, "select 'nodes' k, node_type, count(*) n from kg_nodes group by 1,2 union all select 'edges', rel, count(*) from kg_edges group by 1,2 order by 1,3 desc")77dump("top_issuers", spid, "select ticker, company_name, sector, count(*) projects from projects group by 1,2,3 order by 4 desc limit 15")78dump("example_provenance", spid, "select p.project_id, p.ticker, p.project_name, p.project_type, p.status, p.total_amount_usd, p.n_mentions, p.first_seen, p.last_seen, t.accession_number, t.filing_date, t.form_type, t.status m_status, t.amount_usd, t.confidence, t.snippet from projects p join project_timeline t using(project_id) where p.n_mentions between 4 and 8 and p.total_amount_usd > 5e8 and p.project_type in ('plant_construction','data_center') order by p.n_filings desc, p.project_id, t.filing_date limit 24")79S["mentions_fields"] = spid.execute("select count(*) filter (where amount_usd is not null) amt, count(*) filter (where len(locations)>0) loc, count(*) filter (where len(technologies)>0) tech, count(*) filter (where len(partners)>0) partner, count(*) filter (where len(years)>0) yrs, count(*) from project_mentions").fetchone()80S["projects_fields"] = spid.execute("select count(*) filter (where total_amount_usd is not null), count(*) filter (where canonical_location is not null), count(*) filter (where len(technologies)>0), count(*) from projects").fetchone()81S["spid_runs"] = {"sp500_run": "2026-06 (gpt-4o-mini, 16 workers, 153,894 sections)", "russell_run": "2026-10-27/28 (gpt-4o-mini-2024-07-18, 48 workers, 968,134 sections)"}82S["extracted_at_range"] = [str(x) for x in spid.execute("select min(extracted_at), max(extracted_at) from extracted_sections").fetchone()]83json.dump(S, open(out / "stats.json", "w"), indent=2, default=str)84print(json.dumps(S, indent=1, default=str)[:3000])85