1#!/usr/bin/env python32"""WP44 — RQ3/RQ4 citation impact with explicit citation-window control.34From oa_counts_by_year (citations received per calendar year) we build fixed-window counts:5 c12 = citations received in the publication year and the following year (≈ 0–24 months), c36 = pub year + 2 years.6Normalisation: NCS_k = c_k / mean(c_k | same publication year, same type class, same primary subfield) over the eligible7corpus (field-normalised within OpenAlex subfield; category-level normalisation is added in 05_category_impact.py).8Outputs: results/bibliometrics/citation_windows_yearly.csv (means, medians, shares uncited, Gini of c36 by year),9work_citation_windows.parquet (work-level c12/c36/NCS for downstream scripts), top_cited_by_year.csv (top 50 per year by c36),10fwci_summary_yearly.csv (OpenAlex FWCI as a secondary indicator)."""11import argparse, os, duckdb121314def main():15 ap = argparse.ArgumentParser(); ap.add_argument("--root", required=True); a = ap.parse_args(); R = a.root16 out = os.path.join(R, "results/bibliometrics"); os.makedirs(out, exist_ok=True)17 con = duckdb.connect(os.path.join(R, "data/processed/wp44.duckdb")); con.execute("SET threads TO 20; SET memory_limit='" + os.environ.get("WP44_DUCKDB_MEM", "60GB") + "';")18 con.execute("""CREATE OR REPLACE TABLE work_citation_windows AS19 WITH E AS (SELECT id, preprint_work_id, publication_year AS y, coalesce(type, 'unknown') AS type, coalesce(primary_subfield_id, -1) AS sf, cited_by_count, fwci20 FROM corpus_works WHERE eligible AND NOT e6_retracted),21 -- citations received by the canonical node include those of its merged preprint version (E5)22 CY AS (SELECT e.id AS work_id, c.year, c.cited_by_count FROM oa_counts_by_year c JOIN E e ON e.id = c.work_id23 UNION ALL SELECT e.id, c.year, c.cited_by_count FROM oa_counts_by_year c JOIN E e ON e.preprint_work_id = c.work_id),24 C AS (SELECT c.work_id, sum(CASE WHEN c.year <= e.y + 1 THEN c.cited_by_count ELSE 0 END) AS c12,25 sum(CASE WHEN c.year <= e.y + 2 THEN c.cited_by_count ELSE 0 END) AS c36,26 sum(CASE WHEN c.year <= e.y + 3 THEN c.cited_by_count ELSE 0 END) AS c4827 FROM CY c JOIN E e ON e.id = c.work_id GROUP BY 1)28 SELECT e.id AS work_id, e.y AS year, e.type, e.sf, e.cited_by_count, e.fwci, coalesce(c.c12,0) c12, coalesce(c.c36,0) c36, coalesce(c.c48,0) c48,29 (e.y + 1 <= 2025) AS c12_complete, (e.y + 2 <= 2025) AS c36_complete, (e.y + 3 <= 2025) AS c48_complete30 FROM E e LEFT JOIN C c ON c.work_id = e.id""")31 con.execute("""CREATE OR REPLACE TABLE work_citation_windows2 AS32 SELECT w.*, CASE WHEN m12 > 0 THEN c12 / m12 END AS ncs12, CASE WHEN m36 > 0 THEN c36 / m36 END AS ncs3633 FROM work_citation_windows w JOIN (SELECT year, type, sf, avg(c12) m12, avg(c36) m36, count(*) n_ref FROM work_citation_windows GROUP BY 1,2,3) r34 USING (year, type, sf)""")35 con.execute("DROP TABLE work_citation_windows; ALTER TABLE work_citation_windows2 RENAME TO work_citation_windows")36 con.execute(f"COPY work_citation_windows TO '{R}/data/processed/corpus/work_citation_windows.parquet' (FORMAT PARQUET, COMPRESSION ZSTD)")37 con.execute(f"""COPY (SELECT year, count(*) n, any_value(c12_complete) c12_complete, any_value(c36_complete) c36_complete, round(avg(c12),3) mean_c12, round(avg(c36),3) mean_c36, quantile_cont(c36,0.5) med_c36,38 round(sum((c36=0)::int)*1.0/count(*),4) share_uncited_36, round(sum((c12=0)::int)*1.0/count(*),4) share_uncited_12,39 quantile_cont(c36,0.99) p99_c36, max(c36) max_c36,40 round((2*sum(c36*rk) / (count(*)*sum(c36)) - (count(*)+1.0)/count(*)),4) gini_c36,41 round((2*sum(c12*rk12) / (count(*)*sum(c12)) - (count(*)+1.0)/count(*)),4) gini_c12,42 round(avg(fwci),3) mean_fwci, round(quantile_cont(fwci,0.5),3) med_fwci43 FROM (SELECT *, row_number() OVER (PARTITION BY year ORDER BY c36, work_id) rk, row_number() OVER (PARTITION BY year ORDER BY c12, work_id) rk12 FROM work_citation_windows) GROUP BY 1 ORDER BY 1) TO '{out}/citation_windows_yearly.csv' (HEADER)""")44 con.execute(f"""COPY (SELECT * FROM (SELECT w.year, w.work_id, c.title, c.source_name, c.type, w.c12, w.c36, w.cited_by_count, w.fwci, w.ncs36,45 row_number() OVER (PARTITION BY w.year ORDER BY w.c36 DESC, w.cited_by_count DESC) rk FROM work_citation_windows w JOIN corpus_works c ON c.id = w.work_id) WHERE rk <= 50 ORDER BY year, rk) TO '{out}/top_cited_by_year.csv' (HEADER)""")46 print(open(f"{out}/citation_windows_yearly.csv").read())474849if __name__ == "__main__":50 main()51