spb/firc0001_uqo
Public
1"""N7 - external consistency of 8-K mentions with the 8-K item codes found in the body text (cluster A, proxy).23The archived submissions/<cik>.json files hold only the entity header, so item codes are extracted from the48-K full_body text: regexp 'Item\\s+([1-9]\\.[0-9]{2})' (case-insensitive). Reported as a consistency proxy,5not precision/recall. Read-only.6"""7from __future__ import annotations8import json, sys, math9from pathlib import Path10sys.path.insert(0, str(Path(__file__).resolve().parent))11from common import *1213r = Run("benchmark_8k")14con = connect(SPID_DB)15tmin = con.execute("select min(extracted_at) from extracted_sections").fetchone()[0]16print("extracting item codes from 8-K bodies ...", flush=True)17con.execute(f"""CREATE TEMP TABLE k8 AS18SELECT e.section_id, e.accession_number, e.n_mentions, CASE WHEN e.extracted_at = TIMESTAMP '{tmin}' THEN 'backfilled_june' ELSE 'october' END run,19 f.cik, year(f.filing_date) yr, co.russell_weight_pct, co.in_sp500,20 list_distinct(regexp_extract_all(s.text, '(?i)item\\s+([1-9]\\.[0-9]{{2}})', 1)) items,21 s.word_count, regexp_matches(s.text, '\\b(project|initiative|program|invest|expan|construct|build|launch|develop|deploy|implement|acqui|partner|facilit|capacity|capital|migrat|transform|pipeline|plant|data ?cent|automat|sustainab|renewable|restructur)\\w*', 'i') gate22FROM extracted_sections e JOIN src_sections s USING (section_id) JOIN src_filings f ON f.accession_number = e.accession_number LEFT JOIN src_companies co ON f.cik = co.cik23WHERE f.form_type = '8-K'""")24S = {}25S["sections_8k"] = con.execute("select count(*) from k8").fetchone()[0]26S["sections_with_item_code"] = con.execute("select count(*) from k8 where len(items)>0").fetchone()[0]27S["coverage_pct"] = round(100.0 * S["sections_with_item_code"] / S["sections_8k"], 2)28r.dump("item_code_distribution.csv", con, "SELECT item, count(*) sections FROM (SELECT unnest(items) item FROM k8) GROUP BY 1 ORDER BY 2 DESC")29r.dump("item_code_coverage_by_year.csv", con, "SELECT yr, count(*) sections, count(*) FILTER (WHERE len(items)>0) with_code, round(100.0*count(*) FILTER (WHERE len(items)>0)/count(*),2) pct FROM k8 GROUP BY 1 ORDER BY 1")30con.execute("""CREATE TEMP TABLE mt AS SELECT section_id, bool_or(project_type='ma_integration') ma, bool_or(project_type='cost_reduction') cost, bool_or(project_type='partnership') partn,31 bool_or(project_type IN ('capex_program','plant_construction','manufacturing_expansion','infrastructure','data_center')) capex FROM project_mentions GROUP BY 1""")32con.execute("""CREATE TEMP TABLE j AS SELECT k.*, coalesce(m.ma,FALSE) ma, coalesce(m.cost,FALSE) cost, coalesce(m.partn,FALSE) partn, coalesce(m.capex,FALSE) capex,33 list_contains(items,'2.01') i201, list_contains(items,'1.01') i101, list_contains(items,'2.05') i205, list_contains(items,'8.01') i801, list_contains(items,'5.02') i502, list_contains(items,'2.03') i203, list_contains(items,'7.01') i701,34 list_contains(items,'2.01') OR list_contains(items,'1.01') i201or101,35 CASE WHEN russell_weight_pct IS NULL THEN 'na' ELSE ntile(3) OVER (PARTITION BY russell_weight_pct IS NULL ORDER BY russell_weight_pct) ::varchar END tercile36 FROM k8 k LEFT JOIN mt m USING (section_id)""")37def wilson(k, n, z=1.96):38 if n == 0: return (None, None, None)39 p = k/n; d = 1 + z*z/n; c = (p + z*z/(2*n))/d; h = z*math.sqrt(p*(1-p)/n + z*z/(4*n*n))/d40 return (round(p, 4), round(c-h, 4), round(c+h, 4))41PAIRS = [("ma_integration", "ma", "Item 2.01", "i201"), ("ma_integration", "ma", "Item 2.01 or 1.01", "i201or101"), ("ma_integration", "ma", "Item 1.01", "i101"),42 ("cost_reduction", "cost", "Item 2.05", "i205"), ("partnership", "partn", "Item 1.01", "i101"), ("capex-type", "capex", "Item 1.01", "i101"),43 ("ma_integration", "ma", "Item 8.01 (control)", "i801"), ("ma_integration", "ma", "Item 5.02 (control)", "i502"), ("cost_reduction", "cost", "Item 8.01 (control)", "i801"),44 ("ma_integration", "ma", "Item 2.03 (control)", "i203"), ("partnership", "partn", "Item 8.01 (control)", "i801")]45rows = []46def block(where, label_scope, label_group):47 for tname, tcol, iname, icol in PAIRS:48 n_i, k_ti, n_t, k_it, n_all, k_ti_only, k_i_only, k_both = con.execute(f"""49 SELECT count(*) FILTER (WHERE {icol}), count(*) FILTER (WHERE {icol} AND {tcol}), count(*) FILTER (WHERE {tcol}), count(*) FILTER (WHERE {tcol} AND {icol}), count(*),50 count(*) FILTER (WHERE {tcol} AND NOT {icol}), count(*) FILTER (WHERE {icol} AND NOT {tcol}), count(*) FILTER (WHERE {icol} AND {tcol})51 FROM j WHERE {where}""").fetchone()52 p1 = wilson(k_ti, n_i); p2 = wilson(k_it, n_t)53 base = wilson(n_t, n_all)54 rows.append((label_scope, label_group, tname, iname, n_all, n_i, k_ti, *p1, n_t, k_it, *p2, *base, round((p1[0]/base[0]) if (p1[0] is not None and base[0]) else float('nan'), 3)))55block("TRUE", "all_8k_sections", "all")56block("gate AND word_count >= 30", "calls_only", "all")57block("len(items) > 0", "sections_with_item_code", "all")58for yr in range(2010, 2027):59 block(f"len(items) > 0 AND yr = {yr}", "sections_with_item_code", f"year={yr}")60for t in ("1", "2", "3", "na"):61 block(f"len(items) > 0 AND tercile = '{t}'", "sections_with_item_code", f"weight_tercile={t}")62for rn in ("backfilled_june", "october"):63 block(f"len(items) > 0 AND run = '{rn}'", "sections_with_item_code", f"run={rn}")64r.write_csv("item_vs_type.csv", ["scope", "group", "project_type", "item", "n_sections", "n_item", "n_item_and_type", "p_type_given_item", "ci_low_1", "ci_high_1",65 "n_type", "n_type_and_item", "p_item_given_type", "ci_low_2", "ci_high_2", "base_rate_type", "base_ci_low", "base_ci_high", "lift_type_given_item"], rows)66S["tercile_bounds"] = con.execute("select tercile, min(russell_weight_pct), max(russell_weight_pct), count(distinct cik) from j group by 1 order by 1").fetchall()67r.dump("items_in_ma_sections.csv", con, "SELECT item, count(*) sections FROM (SELECT unnest(items) item FROM j WHERE ma) GROUP BY 1 ORDER BY 2 DESC LIMIT 25")68r.write_json("summary.json", S)69r.finish()70