1"""r3k_22_spid_validation_sample.py — Stage D (FIRC-0002, package L8, cluster I: ADVE-05, AI_C-04, EDIT-09, ECON-13, THEO-13, AI_C-13, ADVE-16).2Sampling frame for the author's human precision check of the SPID project mentions (AR-FIRC0002-3 not granted: NO labels and NO3LLM rater are produced here; the two columns `label_project_present` and `label_ai_related` are left empty for the readers).4Source: the laptop copy of the WP 0001 SPID database (~/Desktop/UQO/Recherche/UQO_RESEARCH_DB/project-db/data/db/spid.duckdb,5mtime 2026-10-28 12:21, identical to the M3U96b copy), opened READ-ONLY with duckdb 2.0.0. Universe: mentions of the panel's CIKs6with filing years 2010-2024. Stratification: project_type x form_type, 200 mentions, proportional with a floor of 6 per stratum,7seed 20261105. Evidence: the LLM-extracted fields (name, type, description, objective, technologies, confidence) plus a <= 2,000-char8excerpt of the SOURCE section text centred on the first occurrence of the project name (or the section start if not found), so the9reader judges the extraction against the filing text. Writes results/r3k_v2/validation/spid_sample.csv and spid_sample_meta.json.10Provenance of the extractor (for Appendix D) is read from the FIRC-0001 pipeline snapshot if present.11Run: nice -n 10 .venv-review/bin/python -I scripts/r3k_22_spid_validation_sample.py12"""13import os, json, re, glob, numpy as np, pandas as pd, duckdb14SEED = 20261105; DB = os.path.expanduser("~/Desktop/UQO/Recherche/UQO_RESEARCH_DB/project-db/data/db/spid.duckdb"); T = "results/r3k_v2/validation"15con = duckdb.connect(DB, read_only=True)16cols = {t: con.sql(f"SELECT column_name FROM information_schema.columns WHERE table_name='{t}'").df().column_name.tolist() for t in ["project_mentions", "spid_sections", "src_sections", "v_sections", "extracted_sections"]}17print({k: v[:12] for k, v in cols.items()})18panel = pd.read_parquet("data/processed_r3k_v2/analysis_panel_v2.parquet", columns=["cik", "fiscal_year"]) if os.path.exists("data/processed_r3k_v2/analysis_panel_v2.parquet") else pd.read_parquet("data/processed_r3k_v2/merged_panel_v2.parquet", columns=["cik", "fiscal_year"])19ciks = sorted(panel.cik.astype(str).str.zfill(10).unique()); con.register("panel_ciks", pd.DataFrame({"cik": ciks}))20pm = con.sql("""SELECT m.mention_id, m.accession_number, m.cik, m.ticker, m.section_id, m.section_name, m.project_name, m.project_type, m.description, m.objective, m.amount_raw, m.technologies, m.status, m.confidence, m.backend, m.form_type, m.filing_date, m.fiscal_year, m.sector21 FROM project_mentions m JOIN panel_ciks p ON lpad(m.cik, 10, '0') = p.cik WHERE m.fiscal_year BETWEEN 2010 AND 2024""").df()22print("mentions in universe:", len(pm), "| by type:", pm.project_type.value_counts().to_dict(), "| by form:", pm.form_type.value_counts().to_dict())23# stratified sample: project_type x form_type, proportional with floor 6, total 20024rng = np.random.default_rng(SEED); strata = pm.groupby(["project_type", "form_type"]).size(); strata = strata[strata > 0]25alloc = (strata / strata.sum() * 200).round().astype(int).clip(lower=6); 26while alloc.sum() > 200: alloc[alloc.idxmax()] -= 127while alloc.sum() < 200: alloc[alloc.idxmax()] += 128parts = []29for (pt, ft), n in alloc.items():30 g = pm[(pm.project_type == pt) & (pm.form_type == ft)]; parts.append(g.sample(n=min(n, len(g)), random_state=int(rng.integers(0, 2**31))))31smp = pd.concat(parts).sample(frac=1, random_state=SEED).reset_index(drop=True)32# source text excerpt: find the section text table that carries `text`33text_tbl = next((t for t in ["spid_sections", "src_sections", "v_sections", "extracted_sections"] if "text" in cols[t] and "section_id" in cols[t]), None); print("text table:", text_tbl)34# the laptop spid.duckdb holds the texts of only 7,368 filings (src_sections is a view on the node parquet): the sampled section texts35# were read from the N1 copy of filing_sections on M4M64a (pyarrow filter on the 200 section_ids, read-only) into this local parquet36LOCAL_TX = f"{V_}/spid_sample_sections_L8.parquet" if False else "data/processed_r3k_v2/spid_sample_sections_L8.parquet"37if os.path.exists(LOCAL_TX): text_tbl = "local:" + LOCAL_TX38exc = []39if text_tbl:40 if text_tbl.startswith("local:"): tx = pd.read_parquet(text_tbl[6:]).drop_duplicates("section_id").set_index("section_id")["text"].to_dict()41 else: con.register("smp_ids", smp[["section_id"]].drop_duplicates()); tx = con.sql(f"SELECT s.section_id, s.text FROM {text_tbl} s JOIN smp_ids i ON s.section_id = i.section_id").df().drop_duplicates("section_id").set_index("section_id")["text"].to_dict()42 for r in smp.itertuples():43 t = tx.get(r.section_id) or ""; name = (r.project_name or "").strip(); pos = -144 if name:45 m = re.search(re.escape(name[:40]), t, flags=re.I); pos = m.start() if m else -146 if pos < 0 and name:47 for w in [w for w in re.findall(r"[A-Za-z]{5,}", name)][:3]:48 m = re.search(re.escape(w), t, flags=re.I)49 if m: pos = m.start(); break50 start = max(0, pos - 900) if pos >= 0 else 0; exc.append({"evidence_found": pos >= 0, "evidence_excerpt": t[start:start + 2000].replace("\n", " ")})51else:52 exc = [{"evidence_found": False, "evidence_excerpt": ""}] * len(smp)53smp = pd.concat([smp, pd.DataFrame(exc)], axis=1); smp["technologies"] = smp["technologies"].apply(lambda x: "; ".join(x) if isinstance(x, (list, np.ndarray)) else (x or ""))54smp.insert(0, "sample_no", range(1, len(smp) + 1)); smp["label_project_present"] = ""; smp["label_ai_related"] = ""; smp["reader"] = ""; smp["notes"] = ""55smp.to_csv(f"{T}/spid_sample.csv", index=False)56prov = {}57for f in glob.glob("/Users/simon-pierreboucher/Desktop/UQO/Recherche/Recherche-UQO/UQO-WP-REVIEW/manuscripts/FIRC-0001/pipeline_code_*/project-db/spid/extract/llm.py"):58 s = open(f).read(); prov["file"] = f; prov["model"] = re.findall(r"gpt-[\w\.-]+", s)[:3]; prov["temperature"] = re.findall(r"temperature\s*[=:]\s*([0-9.]+)", s)[:2]59meta = {"seed": SEED, "db": DB, "db_mtime": "2026-10-28 12:21", "n_universe_mentions": int(len(pm)), "n_sample": int(len(smp)), "strata_allocation": {f"{k[0]}|{k[1]}": int(v) for k, v in alloc.items()}, "share_evidence_found": round(float(smp.evidence_found.mean()), 3), "text_table": text_tbl, "backend_counts": pm.backend.value_counts().to_dict(), "confidence_quantiles": {q: float(pm.confidence.quantile(q)) for q in [.1, .5, .9]} if pm.confidence.notna().any() else None, "extractor_provenance_from_code_snapshot": prov, "labels_produced": False, "note": "human precision check reduced to this frame (AR-FIRC0002-3 not granted); readers fill label_project_present / label_ai_related"}60json.dump(meta, open(f"{T}/spid_sample_meta.json", "w"), indent=2); print(json.dumps({k: v for k, v in meta.items() if k != "strata_allocation"}, indent=1))61