Repository Public Research preview
1"""n1_03_keyword_baseline.py — regex AI-lexicon baseline vs the embedding scores (AI_C-07, EDIT-05, THEO-14).23Section level (1,342,283 sections, the pipeline's text unit): CORE and EXT keyword counts (see n1_common),4filing level: hits per 1,000 words (plain and section-weighted with the pipeline's operational weights),5firm-year level: mean over filings (mirrors eq. annual of the paper) and pooled rate -> keyword_firm_year.csv.6Chunk level (10,720,890 chunks): CORE hit counts -> chunk_kw.parquet, then agreement with score_ai_adoption /7score_ai_infrastructure: hit share by score decile, AUC, Spearman, mean score by hit bucket.8Firm-year agreement with annual_final (ai_adoption_wmean, ai_infrastructure_wmean, composite): Spearman / Pearson9overall and by year, quintile transition, top-quintile overlap, share of zero-hit firm-years.10Outputs: results/keyword_baseline.json, keyword_firm_year.csv, keyword_chunk_deciles.csv, keyword_corr_by_year.csv,11 results/sec_kw.parquet, results/chunk_kw.parquet (node only, inputs for n1_04).12"""13import os, sys, json14import numpy as np, pandas as pd15from scipy.stats import spearmanr, pearsonr16sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))17from n1_common import * # noqa1819c = connect(); views(c)20out = {"lexicon": {"core_terms": CORE_TERMS, "ext_extra_terms": EXT_EXTRA_TERMS, "acronym_case_sensitive": ACRONYM_RE,21 "note": "reviewer-assembled lexicon; CORE = unambiguous AI terms common to published AI dictionaries (Babina, Fedyk, He & Hodson 2024 JFE; Eisfeldt, Schubert & Zhang 2023); EXT adds the automation / analytics / data-driven terms that the original anchor paragraphs also contain. Applied with RE2 via DuckDB regexp_extract_all on lower-cased text; the bare acronym AI / A.I. is matched case-sensitively on the raw text."}}22Q = lambda s: "$q$" + s + "$q$"2324with Timer("section keyword counts"):25 c.execute(f"""CREATE OR REPLACE TABLE sec_kw AS26 SELECT s.section_id, s.accession_number, s.section_name, s.word_count,27 len(regexp_extract_all(lower(s.text), {Q(CORE_RE)})) + len(regexp_extract_all(s.text, {Q(ACRONYM_RE)})) AS n_core,28 len(regexp_extract_all(lower(s.text), {Q(EXT_RE)})) + len(regexp_extract_all(s.text, {Q(ACRONYM_RE)})) AS n_ext,29 len(regexp_extract_all(s.text, {Q(ACRONYM_RE)})) AS n_acronym30 FROM sections s""")31 c.execute(f"COPY sec_kw TO '{RES}/sec_kw.parquet' (FORMAT PARQUET, COMPRESSION ZSTD)")32 out["sections"] = c.execute("""SELECT count(*) n, sum(n_core) core_hits, sum(n_ext) ext_hits, sum(n_acronym) acronym_hits, sum(word_count) words,33 avg(CASE WHEN n_core>0 THEN 1.0 ELSE 0 END) share_sections_with_core, 1000.0*sum(n_core)/sum(word_count) core_per_1000w FROM sec_kw""").df().iloc[0].to_dict()34 out["sections_by_name"] = c.execute("""SELECT section_name, count(*) n, 1000.0*sum(n_core)/sum(word_count) core_per_1000w,35 avg(CASE WHEN n_core>0 THEN 1.0 ELSE 0 END) share_with_core FROM sec_kw GROUP BY 1 ORDER BY 1""").df().to_dict("records")3637with Timer("filing / firm-year keyword rates"):38 W = weight_case("operational_measures", "k.section_name")39 c.execute(f"""CREATE OR REPLACE TABLE filing_kw AS40 SELECT k.accession_number, f.cik, f.ticker, f.form_type, f.filing_date, EXTRACT(YEAR FROM f.filing_date)::INT AS fiscal_year,41 sum(k.n_core) n_core, sum(k.n_ext) n_ext, sum(k.word_count) words,42 1000.0*sum(k.n_core)/NULLIF(sum(k.word_count),0) AS core_per_1000w,43 1000.0*sum(k.n_ext)/NULLIF(sum(k.word_count),0) AS ext_per_1000w,44 1000.0*sum({W}*k.n_core)/NULLIF(sum({W}*k.word_count),0) AS core_w_per_1000w45 FROM sec_kw k JOIN filings f USING (accession_number) GROUP BY 1,2,3,4,5,6""")46 out["filings_by_form"] = c.execute("""SELECT form_type, count(*) n, avg(core_per_1000w) mean_core_per_1000w, median(core_per_1000w) med,47 avg(CASE WHEN n_core>0 THEN 1.0 ELSE 0 END) share_any_core, 1000.0*sum(n_core)/sum(words) pooled_core_per_1000w FROM filing_kw GROUP BY 1 ORDER BY 1""").df().to_dict("records")48 out["filings_by_year"] = c.execute("""SELECT fiscal_year, count(*) n, 1000.0*sum(n_core)/sum(words) pooled_core_per_1000w, avg(CASE WHEN n_core>0 THEN 1.0 ELSE 0 END) share_any_core49 FROM filing_kw GROUP BY 1 ORDER BY 1""").df().to_dict("records")50 fy = c.execute("""SELECT cik, ANY_VALUE(ticker) ticker, fiscal_year, count(*) n_filings, sum(words) words, sum(n_core) n_core, sum(n_ext) n_ext,51 avg(core_per_1000w) kw_core_mean, avg(core_w_per_1000w) kw_core_wmean, avg(ext_per_1000w) kw_ext_mean,52 1000.0*sum(n_core)/NULLIF(sum(words),0) kw_core_pooled, avg(CASE WHEN n_core>0 THEN 1.0 ELSE 0 END) any_core_share,53 avg(CASE WHEN form_type='10-K' THEN core_per_1000w END) kw_core_10k, avg(CASE WHEN form_type='10-Q' THEN core_per_1000w END) kw_core_10q,54 avg(CASE WHEN form_type='8-K' THEN core_per_1000w END) kw_core_8k55 FROM filing_kw GROUP BY cik, fiscal_year""").df()56 af = c.execute("SELECT cik, ticker, fiscal_year, sector, filing_count, total_chunks, ai_adoption_wmean, ai_infrastructure_wmean, ai_disruption_risk_wmean, ai_adoption_mean, ai_adoption_top5pct FROM annual_final").df()57 m = af.merge(fy.drop(columns=["ticker"]), on=["cik", "fiscal_year"], how="left")58 out["firm_years_annual_final"] = int(len(af)); out["firm_years_matched_kw"] = int(m.kw_core_mean.notna().sum())59 m = m[m.kw_core_mean.notna() & (m.fiscal_year.between(2010, 2024))].copy()60 m.to_csv(f"{RES}/keyword_firm_year.csv", index=False, float_format="%.6g")6162def z(g): return (g - g.mean()) / g.std(ddof=0)63for col in ["ai_adoption_wmean", "ai_infrastructure_wmean", "kw_core_mean", "kw_core_wmean", "kw_ext_mean", "kw_core_pooled", "kw_core_10k"]:64 m[f"z_{col}"] = m.groupby("fiscal_year")[col].transform(z)65m["z_compos"] = m[["z_ai_adoption_wmean", "z_ai_infrastructure_wmean"]].mean(axis=1)66m["n_fy"] = 16768def agreement(d, a, b):69 d = d[[a, b, "fiscal_year"]].dropna()70 res = {"n": int(len(d)), "spearman": float(spearmanr(d[a], d[b]).correlation), "pearson": float(pearsonr(d[a], d[b])[0]),71 "share_zero_b": float((d[b] == 0).mean())}72 # within-year quintiles (ties broken by average rank -> qcut on rank with method first for a, 'average' for b to keep zeros together)73 qa = d.groupby("fiscal_year")[a].transform(lambda s: pd.qcut(s.rank(method="first"), 5, labels=False) + 1)74 qb = d.groupby("fiscal_year")[b].transform(lambda s: pd.qcut(s.rank(method="first"), 5, labels=False) + 1)75 res["share_same_quintile"] = float((qa == qb).mean())76 res["share_a_Q5_in_b_Q5"] = float((qb[qa == 5] == 5).mean())77 res["share_a_Q5_in_b_Q45"] = float((qb[qa == 5] >= 4).mean())78 res["share_a_Q1_in_b_Q1"] = float((qb[qa == 1] == 1).mean())79 res["mean_b_by_a_quintile"] = d.groupby(qa)[b].mean().round(4).to_dict()80 res["kendall_tau"] = float(d[[a, b]].corr(method="kendall").iloc[0, 1])81 return res8283with Timer("firm-year agreement"):84 out["firm_year"] = {85 "adopt_vs_kw_core_mean": agreement(m, "ai_adoption_wmean", "kw_core_mean"),86 "adopt_vs_kw_core_wmean": agreement(m, "ai_adoption_wmean", "kw_core_wmean"),87 "adopt_vs_kw_ext_mean": agreement(m, "ai_adoption_wmean", "kw_ext_mean"),88 "infra_vs_kw_core_mean": agreement(m, "ai_infrastructure_wmean", "kw_core_mean"),89 "compos_vs_kw_core_mean": agreement(m, "z_compos", "z_kw_core_mean"),90 "compos_vs_kw_core_pooled": agreement(m, "z_compos", "z_kw_core_pooled"),91 "compos_vs_kw_ext_mean": agreement(m, "z_compos", "z_kw_ext_mean"),92 "adopt_top5pct_vs_kw_core_mean": agreement(m, "ai_adoption_top5pct", "kw_core_mean"),93 "adopt_10k_vs_kw_core_10k": agreement(m[m.kw_core_10k.notna()], "ai_adoption_wmean", "kw_core_10k"),94 }95 by = []96 for y, g in m.groupby("fiscal_year"):97 by.append({"fiscal_year": int(y), "n": int(len(g)), "share_zero_kw": float((g.kw_core_mean == 0).mean()),98 "mean_kw_core_per_1000w": float(g.kw_core_mean.mean()), "mean_adopt_wmean": float(g.ai_adoption_wmean.mean()),99 "spearman_adopt_kw": float(spearmanr(g.ai_adoption_wmean, g.kw_core_mean).correlation),100 "spearman_infra_kw": float(spearmanr(g.ai_infrastructure_wmean, g.kw_core_mean).correlation),101 "spearman_compos_kw": float(spearmanr(g.z_compos, g.kw_core_mean).correlation),102 "spearman_compos_kwext": float(spearmanr(g.z_compos, g.kw_ext_mean).correlation),103 "pearson_compos_kw": float(pearsonr(g.z_compos, g.kw_core_mean)[0])})104 pd.DataFrame(by).to_csv(f"{RES}/keyword_corr_by_year.csv", index=False, float_format="%.4f")105 # sector view: mean composite and keyword rate, and the Utilities / Energy anomaly106 sec = m.groupby("sector").agg(n=("n_fy", "sum"), mean_z_compos=("z_compos", "mean"), mean_kw_core=("kw_core_mean", "mean"),107 share_zero_kw=("kw_core_mean", lambda s: float((s == 0).mean())),108 spearman=("z_compos", lambda s: float(spearmanr(s, m.loc[s.index, "kw_core_mean"]).correlation))).reset_index()109 out["by_sector"] = sec.round(4).to_dict("records")110 # firms ranked by composite (>= 5 years) and their keyword rank111 tf = m.groupby(["ticker", "sector"]).agg(z=("z_compos", "mean"), kw=("kw_core_mean", "mean"), yrs=("fiscal_year", "count")).reset_index()112 tf = tf[tf.yrs >= 5].copy(); tf["rank_z"] = tf.z.rank(ascending=False); tf["rank_kw"] = tf.kw.rank(ascending=False)113 top30 = tf.nsmallest(30, "rank_z")114 out["top30_firms_by_composite"] = top30[["ticker", "sector", "z", "kw", "rank_z", "rank_kw"]].round(3).to_dict("records")115 out["top30_firms_kw_rank_median"] = float(top30.rank_kw.median()); out["n_firms_ranked"] = int(len(tf))116 out["firm_level_spearman_z_vs_kw"] = float(spearmanr(tf.z, tf.kw).correlation)117 out["top30_by_kw"] = tf.nsmallest(30, "rank_kw")[["ticker", "sector", "z", "kw", "rank_z", "rank_kw"]].round(3).to_dict("records")118119with Timer("chunk keyword counts (10.7 M chunks)"):120 c.execute(f"""CREATE OR REPLACE TABLE chunk_kw AS121 SELECT chunk_id, len(regexp_extract_all(lower(chunk_text), {Q(CORE_RE)})) + len(regexp_extract_all(chunk_text, {Q(ACRONYM_RE)})) AS n_core,122 len(regexp_extract_all(lower(chunk_text), {Q(EXT_RE)})) + len(regexp_extract_all(chunk_text, {Q(ACRONYM_RE)})) AS n_ext123 FROM chunks""")124 c.execute(f"COPY chunk_kw TO '{RES}/chunk_kw.parquet' (FORMAT PARQUET, COMPRESSION ZSTD)")125 out["chunks"] = c.execute("SELECT count(*) n, avg(CASE WHEN n_core>0 THEN 1.0 ELSE 0 END) share_core, avg(CASE WHEN n_ext>0 THEN 1.0 ELSE 0 END) share_ext, sum(n_core) core_hits FROM chunk_kw").df().iloc[0].to_dict()126127with Timer("chunk-level agreement"):128 c.execute("""CREATE OR REPLACE TABLE cj AS129 SELECT cs.chunk_id, cs.form_type, cs.fiscal_year, cs.section_name, cs.score_ai_adoption a, cs.score_ai_infrastructure i,130 (cs.score_ai_adoption + cs.score_ai_infrastructure)/2 comp, k.n_core, k.n_ext,131 ntile(10) OVER (ORDER BY cs.score_ai_adoption) dec_a, ntile(10) OVER (ORDER BY cs.score_ai_infrastructure) dec_i,132 ntile(100) OVER (ORDER BY (cs.score_ai_adoption + cs.score_ai_infrastructure)/2) pct_comp133 FROM chunk_scores cs JOIN chunk_kw k USING (chunk_id)""")134 dec = c.execute("""SELECT dec_a AS decile, count(*) n, avg(CASE WHEN n_core>0 THEN 1.0 ELSE 0 END) share_core_hit, avg(CASE WHEN n_ext>0 THEN 1.0 ELSE 0 END) share_ext_hit,135 avg(n_core) mean_core_hits, min(a) min_score, max(a) max_score FROM cj GROUP BY 1 ORDER BY 1""").df()136 dec.to_csv(f"{RES}/keyword_chunk_deciles.csv", index=False, float_format="%.5f")137 out["chunk_adopt_deciles"] = dec.round(5).to_dict("records")138 out["chunk_infra_deciles"] = c.execute("""SELECT dec_i AS decile, count(*) n, avg(CASE WHEN n_core>0 THEN 1.0 ELSE 0 END) share_core_hit FROM cj GROUP BY 1 ORDER BY 1""").df().round(5).to_dict("records")139 out["chunk_comp_top_percentiles"] = c.execute("""SELECT pct_comp AS percentile, count(*) n, avg(CASE WHEN n_core>0 THEN 1.0 ELSE 0 END) share_core_hit, avg(CASE WHEN n_ext>0 THEN 1.0 ELSE 0 END) share_ext_hit FROM cj WHERE pct_comp >= 90 GROUP BY 1 ORDER BY 1""").df().round(5).to_dict("records")140 out["chunk_score_by_hit_bucket"] = c.execute("""SELECT CASE WHEN n_core=0 THEN '0' WHEN n_core=1 THEN '1' WHEN n_core<=4 THEN '2-4' ELSE '5+' END bucket, count(*) n,141 avg(a) mean_adopt, median(a) med_adopt, quantile_cont(a, 0.9) p90_adopt, avg(i) mean_infra, median(i) med_infra FROM cj GROUP BY 1 ORDER BY 1""").df().round(5).to_dict("records")142 # AUC (Mann-Whitney) of the score for hit vs no-hit chunks, and Spearman via ranks143 out["chunk_auc_adopt_core_hit"] = c.execute("""WITH r AS (SELECT n_core>0 h, rank() OVER (ORDER BY a) rk FROM cj)144 SELECT (sum(CASE WHEN h THEN rk END) - (sum(CASE WHEN h THEN 1 END)*(sum(CASE WHEN h THEN 1 END)+1))/2.0) / (sum(CASE WHEN h THEN 1 END)*sum(CASE WHEN NOT h THEN 1 END)) FROM r""").fetchone()[0]145 out["chunk_auc_infra_core_hit"] = c.execute("""WITH r AS (SELECT n_core>0 h, rank() OVER (ORDER BY i) rk FROM cj)146 SELECT (sum(CASE WHEN h THEN rk END) - (sum(CASE WHEN h THEN 1 END)*(sum(CASE WHEN h THEN 1 END)+1))/2.0) / (sum(CASE WHEN h THEN 1 END)*sum(CASE WHEN NOT h THEN 1 END)) FROM r""").fetchone()[0]147 out["chunk_spearman_adopt_ncore"] = c.execute("WITH r AS (SELECT rank() OVER (ORDER BY a) ra, rank() OVER (ORDER BY n_core) rk FROM cj) SELECT corr(ra, rk) FROM r").fetchone()[0]148 out["chunk_spearman_adopt_next"] = c.execute("WITH r AS (SELECT rank() OVER (ORDER BY a) ra, rank() OVER (ORDER BY n_ext) rk FROM cj) SELECT corr(ra, rk) FROM r").fetchone()[0]149 out["chunk_pearson_adopt_ncore"] = c.execute("SELECT corr(a, n_core) FROM cj").fetchone()[0]150 out["chunk_by_form_section_top_decile_hit_share"] = c.execute("""SELECT form_type, section_name, count(*) n, avg(CASE WHEN n_core>0 THEN 1.0 ELSE 0 END) share_core_hit151 FROM cj WHERE dec_a = 10 GROUP BY 1,2 ORDER BY 1,2""").df().round(4).to_dict("records")152 out["chunk_top_decile_hit_share_by_year"] = c.execute("""SELECT fiscal_year, count(*) n, avg(CASE WHEN n_core>0 THEN 1.0 ELSE 0 END) share_core_hit153 FROM cj WHERE dec_a = 10 GROUP BY 1 ORDER BY 1""").df().round(4).to_dict("records")154155dump_json(out, f"{RES}/keyword_baseline.json")156print(json.dumps({k: out["firm_year"][k] for k in ["adopt_vs_kw_core_mean", "compos_vs_kw_core_mean", "compos_vs_kw_ext_mean"]}, indent=1, default=str))157print("chunk AUC adopt", out["chunk_auc_adopt_core_hit"], "spearman", out["chunk_spearman_adopt_ncore"])158print("deciles", dec.to_string())159