SPB Git

Repository Public Research preview

n1_01_corpus_counts.py 9.0 KB 111 lines python

1"""n1_01_corpus_counts.py — corpus counts by form / year (DATA-10, T1 macros).23Reproduces (or documents the discrepancy with) the manuscript's 10,720,890 chunks /41,342,283 sections / 527,548 filings from the parquet copy, and emits LaTeX macros.5Outputs: results/corpus_counts.json, corpus_counts_by_form_year.csv, corpus_macros.tex6"""7import os, sys8import pandas as pd9sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))10from n1_common import *  # noqa1112c = connect(); views(c)13out = {"targets": {"chunks": TARGET_CHUNKS, "sections": TARGET_SECTIONS, "filings": TARGET_FILINGS}}1415with Timer("filings"):16    out["filings_total"] = c.execute("SELECT count(*) FROM filings").fetchone()[0]17    out["filings_distinct_accession"] = c.execute("SELECT count(DISTINCT accession_number) FROM filings").fetchone()[0]18    out["issuers_cik"] = c.execute("SELECT count(DISTINCT cik) FROM filings").fetchone()[0]19    out["issuers_ticker"] = c.execute("SELECT count(DISTINCT ticker) FROM filings").fetchone()[0]20    out["companies_rows"] = c.execute("SELECT count(*) FROM companies").fetchone()[0]21    out["filings_by_form"] = c.execute("""SELECT form_type, count(*) n, count(DISTINCT cik) issuers,22        sum(CASE WHEN parse_status='complete' THEN 1 ELSE 0 END) parsed,23        min(filing_date)::VARCHAR dmin, max(filing_date)::VARCHAR dmax FROM filings GROUP BY 1 ORDER BY 1""").df().to_dict("records")24    out["parse_status"] = c.execute("SELECT form_type, parse_status, count(*) n FROM filings GROUP BY 1,2 ORDER BY 1,2").df().to_dict("records")25    out["download_status"] = c.execute("SELECT download_status, count(*) n FROM filings GROUP BY 1 ORDER BY 1").df().to_dict("records")2627with Timer("sections"):28    out["sections_total"] = c.execute("SELECT count(*) FROM sections").fetchone()[0]29    out["sections_distinct_id"] = c.execute("SELECT count(DISTINCT section_id) FROM sections").fetchone()[0]30    out["sections_filings_with_section"] = c.execute("SELECT count(DISTINCT accession_number) FROM sections").fetchone()[0]31    out["sections_by_form_name"] = c.execute("""SELECT f.form_type, s.section_name, count(*) n,32        median(s.word_count) med_words, avg(s.word_count) mean_words, sum(s.word_count) words33        FROM sections s JOIN filings f USING (accession_number) GROUP BY 1,2 ORDER BY 1,2""").df().to_dict("records")34    out["sections_words_total"] = c.execute("SELECT sum(word_count) FROM sections").fetchone()[0]35    out["sections_orphan_no_filing"] = c.execute("SELECT count(*) FROM sections s LEFT JOIN filings f USING (accession_number) WHERE f.accession_number IS NULL").fetchone()[0]3637with Timer("chunks"):38    out["chunks_total"] = c.execute("SELECT count(*) FROM chunks").fetchone()[0]39    out["chunks_distinct_id"] = c.execute("SELECT count(DISTINCT chunk_id) FROM chunks").fetchone()[0]40    out["chunks_filings"] = c.execute("SELECT count(DISTINCT accession_number) FROM chunks").fetchone()[0]41    out["chunks_null_section"] = c.execute("SELECT count(*) FROM chunks WHERE section_id IS NULL").fetchone()[0]42    out["chunks_orphan_no_section"] = c.execute("SELECT count(*) FROM chunks ch LEFT JOIN sections s USING (section_id) WHERE ch.section_id IS NOT NULL AND s.section_id IS NULL").fetchone()[0]43    wc = c.execute("""SELECT count(*) n, min(word_count) mn, quantile_cont(word_count, 0.05) p05, quantile_cont(word_count, 0.25) p25,44        median(word_count) med, avg(word_count) mean, quantile_cont(word_count, 0.75) p75, quantile_cont(word_count, 0.95) p95, max(word_count) mx,45        avg(CASE WHEN word_count = 512 THEN 1.0 ELSE 0 END) share_eq512, avg(CASE WHEN word_count > 512 THEN 1.0 ELSE 0 END) share_gt512,46        avg(CASE WHEN word_count < 512 THEN 1.0 ELSE 0 END) share_lt512, avg(char_count) mean_chars, median(char_count) med_chars, sum(word_count) words47        FROM chunks""").df().iloc[0].to_dict()48    out["chunk_word_count"] = wc49    out["chunks_per_filing"] = c.execute("""WITH t AS (SELECT accession_number, count(*) n FROM chunks GROUP BY 1)50        SELECT f.form_type, count(*) filings, avg(n) mean_chunks, median(n) med_chunks, min(n) mn, max(n) mx, sum(n) chunks51        FROM t JOIN filings f USING (accession_number) GROUP BY 1 ORDER BY 1""").df().to_dict("records")52    byfy = c.execute("""SELECT f.form_type, EXTRACT(YEAR FROM f.filing_date)::INT AS year, count(*) chunks,53        count(DISTINCT ch.accession_number) filings, count(DISTINCT f.cik) issuers, sum(ch.word_count) words54        FROM chunks ch JOIN filings f USING (accession_number) GROUP BY 1,2 ORDER BY 1,2""").df()55    byfy.to_csv(f"{RES}/corpus_counts_by_form_year.csv", index=False)56    out["chunks_by_form"] = byfy.groupby("form_type")[["chunks", "filings", "words"]].sum().reset_index().to_dict("records")57    out["chunks_by_year"] = byfy.groupby("year")[["chunks", "filings"]].sum().reset_index().to_dict("records")5859with Timer("chunk_scores / embeddings"):60    out["chunk_scores_total"] = c.execute("SELECT count(*) FROM chunk_scores").fetchone()[0]61    out["chunk_scores_by_form"] = c.execute("SELECT form_type, count(*) n, count(DISTINCT accession_number) filings, count(DISTINCT cik) issuers FROM chunk_scores GROUP BY 1 ORDER BY 1").df().to_dict("records")62    out["chunk_scores_by_section"] = c.execute("SELECT form_type, section_name, count(*) n, avg(score_ai_adoption) mean_adopt, avg(score_ai_infrastructure) mean_infra FROM chunk_scores GROUP BY 1,2 ORDER BY 1,2").df().to_dict("records")63    out["chunks_without_score"] = c.execute("SELECT count(*) FROM chunks ch LEFT JOIN chunk_scores cs USING (chunk_id) WHERE cs.chunk_id IS NULL").fetchone()[0]64    out["scores_without_chunk"] = c.execute("SELECT count(*) FROM chunk_scores cs LEFT JOIN chunks ch USING (chunk_id) WHERE ch.chunk_id IS NULL").fetchone()[0]65    import pyarrow.parquet as pq, glob as _glob66    _files = sorted(_glob.glob(f"{DATA}/embeddings/*/*.parquet"))67    _rows = [pq.ParquetFile(f).metadata.num_rows for f in _files]   # metadata only, one file at a time68    out["embeddings_total"] = int(sum(_rows)); out["embeddings_files"] = len(_files)69    out["embeddings_rows_per_file"] = {"min": int(min(_rows)), "max": int(max(_rows)), "mean": float(sum(_rows) / len(_rows))}70    out["embeddings_by_model"] = [{"model_name": os.path.basename(os.path.dirname(_files[0])).split("=")[-1], "n": int(sum(_rows))}]71    out["filing_scores_total"] = c.execute("SELECT count(*) FROM filing_scores").fetchone()[0]72    out["filing_scores_by_form"] = c.execute("SELECT form_type, count(*) n, sum(chunk_count) chunks FROM filing_scores GROUP BY 1 ORDER BY 1").df().to_dict("records")73    out["annual_final_rows"] = c.execute("SELECT count(*) FROM annual_final").fetchone()[0]74    out["annual_final_years"] = c.execute("SELECT fiscal_year, count(*) n, count(DISTINCT cik) ciks FROM annual_final GROUP BY 1 ORDER BY 1").df().to_dict("records")7576out["acceptance"] = {77    "chunks_match": out["chunks_total"] == TARGET_CHUNKS, "chunks_diff": out["chunks_total"] - TARGET_CHUNKS,78    "sections_match": out["sections_total"] == TARGET_SECTIONS, "sections_diff": out["sections_total"] - TARGET_SECTIONS,79    "filings_match": out["filings_total"] == TARGET_FILINGS, "filings_diff": out["filings_total"] - TARGET_FILINGS,80    "embeddings_vs_chunks_diff": out["embeddings_total"] - out["chunks_total"],81    "chunk_scores_vs_chunks_diff": out["chunk_scores_total"] - out["chunks_total"],82}83dump_json(out, f"{RES}/corpus_counts.json")848586def fmt(n): return f"{int(n):,}".replace(",", "{,}")87forms = {r["form_type"]: r for r in out["filings_by_form"]}88cf = {r["form_type"]: r for r in out["chunks_by_form"]}89macros = [90    "% corpus macros generated by code/n1/n1_01_corpus_counts.py (FIRC-0002 Stage D, N1) from the parquet copy of M3U96b",91    f"\\newcommand{{\\mnChunksCorpus}}{{{fmt(out['chunks_total'])}}}",92    f"\\newcommand{{\\mnSectionsCorpus}}{{{fmt(out['sections_total'])}}}",93    f"\\newcommand{{\\mnFilingsCorpusN}}{{{fmt(out['filings_total'])}}}",94    f"\\newcommand{{\\mnIssuersCorpusN}}{{{fmt(out['issuers_cik'])}}}",95    f"\\newcommand{{\\mnEmbeddingsCorpus}}{{{fmt(out['embeddings_total'])}}}",96    f"\\newcommand{{\\mnChunkScoresCorpus}}{{{fmt(out['chunk_scores_total'])}}}",97    f"\\newcommand{{\\mChunkWordsMedian}}{{{out['chunk_word_count']['med']:.0f}}}",98    f"\\newcommand{{\\mChunkWordsMean}}{{{out['chunk_word_count']['mean']:.0f}}}",99    f"\\newcommand{{\\mChunkShareFull}}{{{100*out['chunk_word_count']['share_eq512']:.1f}}}",100]101for ft in ["10-K", "10-Q", "8-K"]:102    key = {"10-K": "TenK", "10-Q": "TenQ", "8-K": "EightK"}[ft]   # letter-only macro names (LaTeX control sequences cannot contain digits)103    if ft in forms:104        macros.append(f"\\newcommand{{\\mnFilings{key}}}{{{fmt(forms[ft]['n'])}}}")105        macros.append(f"\\newcommand{{\\mnParsed{key}}}{{{fmt(forms[ft]['parsed'])}}}")106    if ft in cf:107        macros.append(f"\\newcommand{{\\mnChunks{key}}}{{{fmt(cf[ft]['chunks'])}}}")108open(f"{RES}/corpus_macros.tex", "w").write("\n".join(macros) + "\n")109print(json.dumps(out["acceptance"], indent=1))110print("chunks", out["chunks_total"], "sections", out["sections_total"], "filings", out["filings_total"], "embeddings", out["embeddings_total"], "scores", out["chunk_scores_total"])111
Files main
Menu

Theme