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='60GB';")18 con.execute("""CREATE OR REPLACE TABLE work_citation_windows AS19 WITH E AS (SELECT id, publication_year AS y, type, primary_subfield_id AS sf, cited_by_count, fwci FROM corpus_works WHERE eligible),20 C AS (SELECT c.work_id, sum(CASE WHEN c.year <= e.y + 1 THEN c.cited_by_count ELSE 0 END) AS c12,21 sum(CASE WHEN c.year <= e.y + 2 THEN c.cited_by_count ELSE 0 END) AS c36,22 sum(CASE WHEN c.year <= e.y + 3 THEN c.cited_by_count ELSE 0 END) AS c4823 FROM oa_counts_by_year c JOIN E e ON e.id = c.work_id GROUP BY 1)24 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) c4825 FROM E e LEFT JOIN C c ON c.work_id = e.id""")26 con.execute("""CREATE OR REPLACE TABLE work_citation_windows2 AS27 SELECT w.*, CASE WHEN m12 > 0 THEN c12 / m12 END AS ncs12, CASE WHEN m36 > 0 THEN c36 / m36 END AS ncs3628 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) r29 USING (year, type, sf)""")30 con.execute("DROP TABLE work_citation_windows; ALTER TABLE work_citation_windows2 RENAME TO work_citation_windows")31 con.execute(f"COPY work_citation_windows TO '{R}/data/processed/corpus/work_citation_windows.parquet' (FORMAT PARQUET, COMPRESSION ZSTD)")32 con.execute(f"""COPY (SELECT year, count(*) n, round(avg(c12),3) mean_c12, round(avg(c36),3) mean_c36, quantile_cont(c36,0.5) med_c36,33 round(sum((c36=0)::int)*1.0/count(*),4) share_uncited_36, round(sum((c12=0)::int)*1.0/count(*),4) share_uncited_12,34 quantile_cont(c36,0.99) p99_c36, max(c36) max_c36,35 round((2*sum(c36*rk) / (count(*)*sum(c36)) - (count(*)+1.0)/count(*)),4) gini_c36,36 round(avg(fwci),3) mean_fwci, round(quantile_cont(fwci,0.5),3) med_fwci37 FROM (SELECT *, row_number() OVER (PARTITION BY year ORDER BY c36) rk FROM work_citation_windows) GROUP BY 1 ORDER BY 1) TO '{out}/citation_windows_yearly.csv' (HEADER)""")38 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,39 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)""")40 print(open(f"{out}/citation_windows_yearly.csv").read())414243if __name__ == "__main__":44 main()45