1#!/usr/bin/env python32"""WP44 — RQ6 collaboration, institutions, countries (eligible corpus), yearly and by period.3Outputs (results/bibliometrics/): team_size_yearly.csv, countries_yearly.csv (top-N country output + share, with4fractional counting), international_collab_yearly.csv, institution_types_yearly.csv (education/company/government/…),5top_institutions_by_period.csv (full and fractional counts), country_pairs_by_period.csv (edges for the collaboration6network, ≥ 50 joint works), top_sources_by_period.csv (venue concentration inputs), venue_hhi_yearly.csv."""7import argparse, os, duckdb8910def main():11 ap = argparse.ArgumentParser(); ap.add_argument("--root", required=True); a = ap.parse_args(); R = a.root12 out = os.path.join(R, "results/bibliometrics"); os.makedirs(out, exist_ok=True)13 con = duckdb.connect(os.path.join(R, "data/processed/wp44.duckdb"), read_only=True); con.execute("SET threads TO 20; SET memory_limit='" + os.environ.get("WP44_DUCKDB_MEM", "60GB") + "';")14 con.execute("CREATE OR REPLACE TEMP VIEW E AS SELECT * FROM corpus_works WHERE eligible")15 con.execute(f"""COPY (SELECT publication_year AS year, period, count(*) n, round(avg(n_authors),3) mean_authors, quantile_cont(n_authors,0.5) med_authors,16 sum((n_authors=1)::int) single, sum((n_authors>=5)::int) five_plus, sum((n_authors>=10)::int) ten_plus, sum((n_countries>=2)::int) intl,17 sum((n_institutions>=2)::int) multi_inst, sum((n_countries=0)::int) no_country FROM E GROUP BY 1,2 ORDER BY 1) TO '{out}/team_size_yearly.csv' (HEADER)""")18 # country output: full counting (work counted once per country) and fractional (1/n_countries)19 con.execute("""CREATE OR REPLACE TEMP TABLE wc AS SELECT w.id, w.publication_year AS year, w.period, unnest(w.countries) AS country, 1.0/len(w.countries) AS frac FROM E w WHERE len(w.countries) > 0""")20 con.execute(f"""COPY (SELECT year, country, count(*) n_full, round(sum(frac),1) n_frac FROM wc GROUP BY 1,2 ORDER BY 1, 3 DESC) TO '{out}/countries_yearly.csv' (HEADER)""")21 con.execute(f"""COPY (SELECT publication_year AS year, count(*) n, sum((n_countries>=2)::int) n_intl, round(sum((n_countries>=2)::int)*1.0/count(*),4) share_intl FROM E WHERE n_countries>0 GROUP BY 1 ORDER BY 1) TO '{out}/international_collab_yearly.csv' (HEADER)""")22 con.execute("""CREATE OR REPLACE TEMP TABLE wi AS SELECT w.id, w.publication_year AS year, w.period, unnest(w.institution_types) AS itype FROM E w WHERE len(w.institution_types) > 0""")23 con.execute(f"""COPY (SELECT year, itype, count(*) n FROM wi GROUP BY 1,2 ORDER BY 1,3 DESC) TO '{out}/institution_types_yearly.csv' (HEADER)""")24 con.execute(f"""COPY (SELECT year, count(DISTINCT id) n_with_inst, count(DISTINCT CASE WHEN itype='company' THEN id END) n_company,25 round(count(DISTINCT CASE WHEN itype='company' THEN id END)*1.0/count(DISTINCT id),4) share_company FROM wi GROUP BY 1 ORDER BY 1) TO '{out}/industry_share_yearly.csv' (HEADER)""")26 # institutions (from authorships): full and fractional (by distinct institutions per work)27 con.execute("""CREATE OR REPLACE TEMP TABLE winst AS28 SELECT DISTINCT a.work_id, unnest(a.institution_ids) AS inst_id FROM oa_authorships a WHERE a.work_id IN (SELECT id FROM E) AND len(a.institution_ids) > 0""")29 con.execute("""CREATE OR REPLACE TEMP TABLE winst2 AS SELECT w.work_id, w.inst_id, 1.0/count(*) OVER (PARTITION BY w.work_id) AS frac, e.period, e.publication_year AS year FROM winst w JOIN E e ON e.id = w.work_id""")30 con.execute(f"""COPY (SELECT period, inst_id, count(*) n_full, round(sum(frac),1) n_frac FROM winst2 GROUP BY 1,2 HAVING count(*) >= 200 ORDER BY 1, 3 DESC) TO '{out}/top_institutions_by_period.csv' (HEADER)""")31 con.execute(f"""COPY (SELECT a.period, a.country AS c1, b.country AS c2, count(DISTINCT a.id) n FROM wc a JOIN wc b ON a.id = b.id AND a.country < b.country32 GROUP BY 1,2,3 HAVING count(DISTINCT a.id) >= 50 ORDER BY 1, 4 DESC) TO '{out}/country_pairs_by_period.csv' (HEADER)""")33 con.execute(f"""COPY (SELECT period, source_id, any_value(source_name) source_name, any_value(source_type) source_type, count(*) n FROM E WHERE source_id IS NOT NULL34 GROUP BY 1,2 HAVING count(*) >= 100 ORDER BY 1, 5 DESC) TO '{out}/top_sources_by_period.csv' (HEADER)""")35 con.execute(f"""COPY (SELECT year, count(*) n, count(DISTINCT source_id) n_sources, round(sum(s*s),6) hhi, round(max(s),5) top1_share FROM36 (SELECT publication_year AS year, source_id, count(*)*1.0/sum(count(*)) OVER (PARTITION BY publication_year) AS s FROM E WHERE source_id IS NOT NULL GROUP BY 1,2)37 GROUP BY 1 ORDER BY 1) TO '{out}/venue_hhi_yearly.csv' (HEADER)""")38 print(open(f"{out}/team_size_yearly.csv").read()[:800]); print(open(f"{out}/industry_share_yearly.csv").read())394041if __name__ == "__main__":42 main()43