Repository Public Research preview
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