spb/uqo-research-db

1"""Build a shareable `shares/` folder from the EDGAR research DB.23Exports research-ready datasets (parquet + CSV), metadata, diagnostics,4the PDF report and figures, plus a text sample — everything small enough5to share without the 88 GB of raw filings.6"""7from pathlib import Path8import shutil9import json1011import duckdb1213ROOT = Path(__file__).resolve().parent.parent14SHARES = ROOT / "shares"1516PARQUET = ROOT / "data" / "parquet"1718# (source parquet dir, output base name, friendly description)19DATASETS = [20    ("annual_final", "annual_panel_final", "Annual firm-year panel WITH engineered features (deltas, rolling, novelty)"),21    ("quarterly_final", "quarterly_panel_final", "Quarterly firm-quarter panel with sector labels"),22    ("filing_scores", "filing_scores", "One row per individual filing with 8 research-construct scores"),23    ("filings", "filings_index", "Master index of every filing downloaded"),24    ("companies", "companies", "Company reference table: S&P 500 ∪ Russell 3000 (CIK, ticker, sector, SIC, index flags)"),25]262728def reset_dir(p: Path):29    if p.exists():30        shutil.rmtree(p)31    p.mkdir(parents=True, exist_ok=True)323334def main():35    reset_dir(SHARES)36    datasets_dir = SHARES / "datasets"37    meta_dir = SHARES / "metadata"38    diag_dir = SHARES / "diagnostics"39    report_dir = SHARES / "report"40    samples_dir = SHARES / "samples"41    for d in (datasets_dir, meta_dir, diag_dir, report_dir, samples_dir):42        d.mkdir(parents=True, exist_ok=True)4344    con = duckdb.connect()45    manifest = []4647    # 1. Research-ready datasets -> parquet + csv48    for src, out, desc in DATASETS:49        glob = f"{PARQUET / src}/**/*.parquet"50        df = con.execute(f"SELECT * FROM read_parquet('{glob}')").df()51        pq = datasets_dir / f"{out}.parquet"52        csv = datasets_dir / f"{out}.csv"53        df.to_parquet(pq, index=False, compression="zstd")54        df.to_csv(csv, index=False)55        manifest.append({"name": out, "rows": len(df), "cols": df.shape[1], "desc": desc})56        print(f"  [dataset] {out:24s} {len(df):>7,} rows x {df.shape[1]:>3} cols")5758    # 2. Metadata JSON (constituents + cik mapping; skip the noisy download log)59    for fn in ("universe.json", "sp500_constituents.json", "russell3000_constituents.json", "cik_mapping.json"):60        s = ROOT / "data" / "metadata" / fn61        if s.exists():62            shutil.copy2(s, meta_dir / fn)63            print(f"  [metadata] {fn}")6465    # 3. Diagnostics (CSV summaries + coverage report)66    for f in sorted((ROOT / "outputs" / "diagnostics").glob("*")):67        if f.suffix in (".csv", ".json", ".txt"):68            shutil.copy2(f, diag_dir / f.name)69    print(f"  [diagnostics] {len(list(diag_dir.iterdir()))} files")7071    # 4. Report PDFs + figures72    latex = ROOT / "outputs" / "latex"73    for pdf in ("edgar_research_report.pdf", "research_questions.pdf"):74        if (latex / pdf).exists():75            shutil.copy2(latex / pdf, report_dir / pdf)76    fig_src = latex / "figures"77    if fig_src.exists():78        shutil.copytree(fig_src, report_dir / "figures", dirs_exist_ok=True)79    print(f"  [report] 2 PDFs + figures")8081    # 5. Text sample so reviewers see the underlying parsed content (500 rows)82    sec_glob = f"{PARQUET / 'filing_sections'}/**/*.parquet"83    sample = con.execute(84        f"SELECT accession_number, section_name, item_number, word_count, "85        f"LEFT(text, 1500) AS text_preview "86        f"FROM read_parquet('{sec_glob}') USING SAMPLE 500 ROWS"87    ).df()88    sample.to_csv(samples_dir / "filing_sections_sample.csv", index=False)89    print(f"  [sample] filing_sections_sample.csv ({len(sample)} rows)")9091    # write a machine-readable manifest92    (SHARES / "manifest.json").write_text(json.dumps(manifest, indent=2))93    print("\nDone -> shares/")949596if __name__ == "__main__":97    main()98