""" Extract data from Excel sheets to a json file for creating charts # Run with: # python3 extract_to_json.py Output will be grouped by each page: { "overview_national": { "all": [ { "realised": { "num": ..., "percent": ..., }, "unused": { "num": ..., "percent": ..., }, "blocked": { "num": ..., "percent": ..., }, "year": 2015, }, ... ], "humanities": [...], "medicine": [...], "science_technology": [...], "social_science": [...], }, "overview_uni": { "aau": [ { "realised": {...}, "unused": {...}, "blocked": {...}, "year": 2015, }, ... ], "au": [...], "cbs": [...], "dtu": [...], "itu": [...], "ku": [...], "ruc": [...], "sdu": [...], }, "oa_national": { "all": [ { "uni_repo": { "num": ..., "percent": ..., }, "ext_repo": { "num": ..., "percent": ..., }, "oa_w_apc": { "num": ..., "percent": ..., }, "oa_wo_apc": { "num": ..., "percent": ..., }, "year": 2015, }, ... ], "humanities": [...], "medicine": [...], "science_technology": [...], "social_science": [...], }, "oa_uni": { "aau": [ { "uni_repo": {...}, "ext_repo": {...}, "oa_w_apc": {...}, "oa_wo_apc": {...}, "year": 2015, }, ... ], "au": [...], "cbs": [...], "dtu": [...], "itu": [...], "ku": [...], "ruc": [...], "sdu": [...], } } """ import json from pathlib import Path from openpyxl import Workbook, load_workbook from typing_extensions import Any DATA_DIR = "xlsxs" OUTPUT_DIR = "output" SHEET_NATIONAL = "National" SHEET_MRA = "Main Research Areas" SHEET_UNI = "Universities" YEARS = sorted(list(range(2015, 2027))) # the row number for university info in year 2015-2017 is 1 less than the regular ones # i.e. "aau": 5, "au": 6, ... UNI_TO_ROW = { "aau": 6, "au": 7, "cbs": 8, "dtu": 9, "itu": 10, "ku": 11, "ruc": 12, "sdu": 13, } # the row number for MRA info in year 2015-2017 is 1 less than the regular ones # i.e. "humanities": 5, "medicine": 8, ... MRA_TO_ROW = { "humanities": 6, "medicine": 9, "science_technology": 8, "social_science": 7, } def get_summary_name(year: int) -> str: # there is a 2-year gap between oai and data # i.e. 2025 indicators are run on 2023 data return f"OA-Indicator_{year-2}_eng_summary.xlsx" def read_cell(wb: Workbook, sheet: str, cell: str) -> Any: ws = wb[sheet] return ws[cell].value def format_percent(percent: float | None) -> str | None: """ Format a percentage float value between [0, 1] to a string with 2 digits and % sign. """ if percent is None: return None return f"{round(percent*100, 2)}%" def extract_overview_national() -> dict[str, Any]: stats = { "all": [], "humanities": [], "medicine": [], "science_technology": [], "social_science": [], } for year in YEARS: # open workbook wb = load_workbook(Path(DATA_DIR) / get_summary_name(year), data_only=True) # 2015-2017 sheets have different layouts if 2015 <= year <= 2017: stats["all"].append( { "realised": { "num": read_cell(wb, SHEET_NATIONAL, "B5"), "percent": format_percent(read_cell(wb, SHEET_NATIONAL, "H5")), }, "unused": { "num": read_cell(wb, SHEET_NATIONAL, "C5"), "percent": format_percent(read_cell(wb, SHEET_NATIONAL, "I5")), }, "blocked": { "num": read_cell(wb, SHEET_NATIONAL, "D5"), "percent": format_percent(read_cell(wb, SHEET_NATIONAL, "J5")), }, "year": year, } ) # main research area row number is 1 less for mra in MRA_TO_ROW.keys(): stats[mra].append( { "realised": { "num": read_cell(wb, SHEET_MRA, f"B{MRA_TO_ROW[mra]-1}"), "percent": format_percent( read_cell(wb, SHEET_MRA, f"H{MRA_TO_ROW[mra]-1}") ), }, "unused": { "num": read_cell(wb, SHEET_MRA, f"C{MRA_TO_ROW[mra]-1}"), "percent": format_percent( read_cell(wb, SHEET_MRA, f"I{MRA_TO_ROW[mra]-1}") ), }, "blocked": { "num": read_cell(wb, SHEET_MRA, f"D{MRA_TO_ROW[mra]-1}"), "percent": format_percent( read_cell(wb, SHEET_MRA, f"J{MRA_TO_ROW[mra]-1}") ), }, "year": year, } ) continue stats["all"].append( { "realised": { "num": read_cell(wb, SHEET_NATIONAL, "B6"), "percent": format_percent(read_cell(wb, SHEET_NATIONAL, "L6")), }, "unused": { "num": read_cell(wb, SHEET_NATIONAL, "G6"), "percent": format_percent(read_cell(wb, SHEET_NATIONAL, "Q6")), }, "blocked": { "num": read_cell(wb, SHEET_NATIONAL, "H6"), "percent": format_percent(read_cell(wb, SHEET_NATIONAL, "R6")), }, "year": year, } ) for mra in MRA_TO_ROW.keys(): stats[mra].append( { "realised": { "num": read_cell(wb, SHEET_MRA, f"B{MRA_TO_ROW[mra]}"), "percent": format_percent( read_cell(wb, SHEET_MRA, f"L{MRA_TO_ROW[mra]}") ), }, "unused": { "num": read_cell(wb, SHEET_MRA, f"G{MRA_TO_ROW[mra]}"), "percent": format_percent( read_cell(wb, SHEET_MRA, f"Q{MRA_TO_ROW[mra]}") ), }, "blocked": { "num": read_cell(wb, SHEET_MRA, f"H{MRA_TO_ROW[mra]}"), "percent": format_percent( read_cell(wb, SHEET_MRA, f"R{MRA_TO_ROW[mra]}") ), }, "year": year, } ) return {"overview_national": stats} def extract_overview_university() -> dict[str, Any]: stats = { "aau": [], "au": [], "cbs": [], "dtu": [], "itu": [], "ku": [], "ruc": [], "sdu": [], } for year in YEARS: # open workbook wb = load_workbook(Path(DATA_DIR) / get_summary_name(year), data_only=True) # 2015-2017 sheets have different layouts if 2015 <= year <= 2017: # university row number is 1 less for uni in sorted(UNI_TO_ROW.keys()): stats[uni].append( { "realised": { "num": read_cell(wb, SHEET_UNI, f"B{UNI_TO_ROW[uni]-1}"), "percent": format_percent( read_cell(wb, SHEET_UNI, f"H{UNI_TO_ROW[uni]-1}") ), }, "unused": { "num": read_cell(wb, SHEET_UNI, f"C{UNI_TO_ROW[uni]-1}"), "percent": format_percent( read_cell(wb, SHEET_UNI, f"I{UNI_TO_ROW[uni]-1}") ), }, "blocked": { "num": read_cell(wb, SHEET_UNI, f"D{UNI_TO_ROW[uni]-1}"), "percent": format_percent( read_cell(wb, SHEET_UNI, f"J{UNI_TO_ROW[uni]-1}") ), }, "year": year, } ) continue for uni in sorted(UNI_TO_ROW.keys()): stats[uni].append( { "realised": { "num": read_cell(wb, SHEET_UNI, f"B{UNI_TO_ROW[uni]}"), "percent": format_percent( read_cell(wb, SHEET_UNI, f"L{UNI_TO_ROW[uni]}") ), }, "unused": { "num": read_cell(wb, SHEET_UNI, f"G{UNI_TO_ROW[uni]}"), "percent": format_percent( read_cell(wb, SHEET_UNI, f"Q{UNI_TO_ROW[uni]}") ), }, "blocked": { "num": read_cell(wb, SHEET_UNI, f"H{UNI_TO_ROW[uni]}"), "percent": format_percent( read_cell(wb, SHEET_UNI, f"R{UNI_TO_ROW[uni]}") ), }, "year": year, } ) return {"overview_uni": stats} def extract_oa_types_national() -> dict[str, Any]: stats = { "all": [], "humanities": [], "medicine": [], "science_technology": [], "social_science": [], } for year in YEARS: # don't process 2015 to 2017 if 2015 <= year <= 2017: continue # open workbook wb = load_workbook(Path(DATA_DIR) / get_summary_name(year), data_only=True) stats["all"].append( { "uni_repo": { "num": read_cell(wb, SHEET_NATIONAL, "C6"), "percent": format_percent( read_cell(wb, SHEET_NATIONAL, "C6") / read_cell(wb, SHEET_NATIONAL, "B6") ), }, "ext_repo": { "num": read_cell(wb, SHEET_NATIONAL, "D6"), "percent": format_percent( read_cell(wb, SHEET_NATIONAL, "D6") / read_cell(wb, SHEET_NATIONAL, "B6") ), }, "oa_w_apc": { "num": read_cell(wb, SHEET_NATIONAL, "E6"), "percent": format_percent( read_cell(wb, SHEET_NATIONAL, "E6") / read_cell(wb, SHEET_NATIONAL, "B6") ), }, "oa_wo_apc": { "num": read_cell(wb, SHEET_NATIONAL, "F6"), "percent": format_percent( read_cell(wb, SHEET_NATIONAL, "F6") / read_cell(wb, SHEET_NATIONAL, "B6") ), }, "year": year, } ) for mra in MRA_TO_ROW.keys(): stats[mra].append( { "uni_repo": { "num": read_cell(wb, SHEET_MRA, f"C{MRA_TO_ROW[mra]}"), "percent": format_percent( read_cell(wb, SHEET_MRA, f"C{MRA_TO_ROW[mra]}") / read_cell(wb, SHEET_MRA, f"B{MRA_TO_ROW[mra]}") ), }, "ext_repo": { "num": read_cell(wb, SHEET_MRA, f"D{MRA_TO_ROW[mra]}"), "percent": format_percent( read_cell(wb, SHEET_MRA, f"D{MRA_TO_ROW[mra]}") / read_cell(wb, SHEET_MRA, f"B{MRA_TO_ROW[mra]}") ), }, "oa_w_apc": { "num": read_cell(wb, SHEET_MRA, f"E{MRA_TO_ROW[mra]}"), "percent": format_percent( read_cell(wb, SHEET_MRA, f"E{MRA_TO_ROW[mra]}") / read_cell(wb, SHEET_MRA, f"B{MRA_TO_ROW[mra]}") ), }, "oa_wo_apc": { "num": read_cell(wb, SHEET_MRA, f"F{MRA_TO_ROW[mra]}"), "percent": format_percent( read_cell(wb, SHEET_MRA, f"F{MRA_TO_ROW[mra]}") / read_cell(wb, SHEET_MRA, f"B{MRA_TO_ROW[mra]}") ), }, "year": year, } ) return {"oa_national": stats} def extract_oa_types_university() -> dict[str, Any]: stats = { "aau": [], "au": [], "cbs": [], "dtu": [], "itu": [], "ku": [], "ruc": [], "sdu": [], } for year in YEARS: # don't process year 2015 to 2017 if 2015 <= year <= 2017: continue # open workbook wb = load_workbook(Path(DATA_DIR) / get_summary_name(year), data_only=True) for uni in sorted(UNI_TO_ROW.keys()): stats[uni].append( { "uni_repo": { "num": read_cell(wb, SHEET_UNI, f"C{UNI_TO_ROW[uni]}"), "percent": format_percent( read_cell(wb, SHEET_UNI, f"C{UNI_TO_ROW[uni]}") / read_cell(wb, SHEET_UNI, f"B{UNI_TO_ROW[uni]}") ), }, "ext_repo": { "num": read_cell(wb, SHEET_UNI, f"D{UNI_TO_ROW[uni]}"), "percent": format_percent( read_cell(wb, SHEET_UNI, f"D{UNI_TO_ROW[uni]}") / read_cell(wb, SHEET_UNI, f"B{UNI_TO_ROW[uni]}") ), }, "oa_w_apc": { "num": read_cell(wb, SHEET_UNI, f"E{UNI_TO_ROW[uni]}"), "percent": format_percent( read_cell(wb, SHEET_UNI, f"E{UNI_TO_ROW[uni]}") / read_cell(wb, SHEET_UNI, f"B{UNI_TO_ROW[uni]}") ), }, "oa_wo_apc": { "num": read_cell(wb, SHEET_UNI, f"F{UNI_TO_ROW[uni]}"), "percent": format_percent( read_cell(wb, SHEET_UNI, f"F{UNI_TO_ROW[uni]}") / read_cell(wb, SHEET_UNI, f"B{UNI_TO_ROW[uni]}") ), }, "year": year, } ) return {"oa_uni": stats} def main() -> None: result = {} result.update(extract_overview_national()) result.update(extract_overview_university()) result.update(extract_oa_types_national()) result.update(extract_oa_types_university()) output_path = Path(OUTPUT_DIR) / f"chart_data_{min(YEARS)}-{max(YEARS)}.json" with open(output_path, "w", encoding="utf-8") as f: json.dump(result, f, indent=2) if __name__ == "__main__": main()