Produce merged files and plots for the yearly SciLifeLab report to KTH. For both unit (facility) and fellows data.
- Python 3
- Openpyxl Python package (read XLSX and XLSM files)
- XlsxWriter Python package (create XLSX files)
- Plotly Python package (create plots)
- kaleido Python package (create PNG of plots)
Tip: Create a virtual Python environment and install the packages using pip
and the requirements.txt
file which can be found in the source code for each year:
$ pip install -r requirements.txt
The source code for each year is in its own subdirectory.
The source code for 2022 is an adjusted copy of 2021 and is used for production.
The source code for 2021 is an adjusted copy of 2020 and is used for production.
The source code for 2020 is new and is used for production. NOTE that the new source code does not use HTML or explicit JavaScript. It is pure Python.
The code for 2019 was written to recreate the merged files for that year. This code did not exist in this form when the actual reports were processed. See https://github.com/senthil10/dc_reporting_scripts instead.
The specifications for the output files A-H are given in the document
Data files for KTH and Infra Reports.xlsx
for each year.
The Reporting Portal https://reporting.scilifelab.se/ is the primary data source. The "Aggregate" feature is used to create XLSX files combining the data from the reports based on the relevant form.
In addition, the Volume Data files attached to each report is also used.
To perform the operations below, you need to be logged in as an admin
or staff account at https://reporting.scilifelab.se/
My location for the input files are ~/Nextcloud/Ă…rsrapport 2022/
-
Go to the form for this year's Infrastructure Unit (Facility) reports in the Reporting Portal.
-
Click on the button "Aggregate".
-
Check the following fields for output.
- Report status filter: Submitted
- Report metadata: identifier, title
- Report history: [none]
- Report owner: [none]
- Report fields: [all]
- Table field: [none]
- File format: Excel (XLSX)
-
Create and download the aggregate file by clicking the button "Aggregate".
-
Check only the "facility" value in the Report fields, and then perform one aggregate operation for each value of Table field; "facility_director", "facility_head", "additional_funding" and "immaterial_property_rights".
-
Move these XLSX files to the subdirectory
Units reports/aggregate_files
for this year's report data.
-
Go to the page listing all reports https://reporting.scilifelab.se/orders
-
Filter by the appropriate form and status "Submitted".
-
Download manually the volume data files for all reports to the subdirectory
volume_data_files
for this year's report data.- Right-click on the report link, to bring up a new tab (keeping the list as is in its tab).
- Click on the link to the Volume Data file; it is visible under "Files" in the top panel, and also further down in the field "Volume data by Excel..." It is the same file.
-
Check and set the parameters for the filepaths and other data in the source code file
facility_data.py
. This file is imported as a module by the other scripts and its variables are used there. -
The script
merge_A.py
produces the fileA_Infrastructure Single Data Reported {year}.xlsx
from the contents of fileorders_Facility_report_{year}.xlsx
. -
The script
merge_B.py
produces the fileB_Infrastructure FD and HF {year}.xlsx
from the contents of the filesorders_Facility_report_{year}_facility_director.xlsx
andorders_Facility_report_{year}_facility_head.xlsx
. -
The script
merge_C.py
produces the fileC_Infrastructure Other Funding {year}.xlsx
from the contents of the fileorders_Facility_report_{year}_additional_funding.xlsx
. -
The script
merge_D.py
produces the fileD_Infrastructure Immaterial Property Rights {year}.xlsx
from the contents of the fileorders_Facility_report_{year}_immaterial_property_rights.xlsx
. -
The script
merge_E.py
produces the fileE_Infrastructure Users {year}.xlsx
from the contents of the sheetA. Users
in all the volume data files.NOTE: Some of the
XLSX
/XLSM
files cause "UserWarning" when read byopenpyxl
. This can be ignored. -
The script
merge_F.py
produces the fileF_Infrastructure Courses {year}.xlsx
from the contents of the sheetB. Courses
in all the volume data files.NOTE: Some of the
XLSX
/XLSM
files cause "UserWarning" when read byopenpyxl
. This can be ignored. -
The script
merge_G.py
produces the fileG_Infrastructure Conferences Symposia Seminars {year}.xlsx
from the contents of the sheetC. Conf, symp, semin
in all the volume data files.NOTE: Some of the
XLSX
/XLSM
files cause "UserWarning" when read byopenpyxl
. This can be ignored. -
The script
merge_H.py
produces the fileH_Infrastructure External Collaborations {year}.xlsx
from the contents of the sheetD. External Collab
(yes, there is a trailing white-space in the name!) in all the volume data files.NOTE: Some of the
XLSX
/XLSM
files cause "UserWarning" when read byopenpyxl
. This can be ignored.
The Python script make_fig5.py
uses the Python library of Plotly and
a few other packages to produce a large PNG file of a scatterplot of
affiliations versus facilities, where the size of each circle shows
the number of unique users.
The input file is that produced by the script merge_E.py
(step 6 above).
(This is an improvement from 2020; no manual cut-and-paste is required now.)
This script also creates a CSV file containing the counts.
The Reporting Portal https://reporting.scilifelab.se/ is the primary data source. The "Aggregate" feature is used to create XLSX files combining the data from the reports based on the relevant form.
In addition, the Volume Data files attached to each report is also used.
To perform the operations below, you need to be logged in as an admin or staff account at https://reporting.scilifelab.se/
-
Go to the form for this year's SciLifeLab Fellow reports in the Reporting Portal.
-
Click on the button "Aggregate".
-
Check the following fields for output.
- Report status filter: Submitted
- Report metadata: identifier, title
- Report history: [none]
- Report owner: [all]
- Report fields: [all except volume_data]
- File format: Excel (XLSX)
-
Create and download the aggregate file by clicking the button "Aggregate".
-
Move this XLSX file to the subdirectory
aggregate_files
for this year's report data.
-
Go to the list of all reports https://reporting.scilifelab.se/orders
-
Filter by the appropriate form, and the status "Submitted".
-
Download manually the volume data files for all reports to the subdirectory
volume_data_files
for this year's report data.- Right-click on the report link, to bring up a new tab (keeping the list as is in its tab).
- Click on the link to the Volume Data file; it is visible under "Files" in the top panel, and also further down in the field "Volume data by Excel..." It is the same file.
-
Check and set the parameters for the filepaths and other data in the source code file
merge_scilifelab_fellows.py
. This file is imported as a module by the other scripts and its variables are used there. -
Run the script
merge_scilifelab_fellows.py
, which produces the filesFellows {year} Teaching.xlsx
,Fellows {year} Grants.xlsx
andFellows {year} Collaborations.xlsx
from the volume data files.NOTE: Some of the
XLSX
/XLSM
files cause "UserWarning" when read byopenpyxl
. This can be ignored.
This procedure was copied 2022 from the SciLifeLab Fellows above.
The Reporting Portal https://reporting.scilifelab.se/ is the primary data source. The "Aggregate" feature is used to create XLSX files combining the data from the reports based on the relevant form.
In addition, the Volume Data files attached to each report is also used.
To perform the operations below, you need to be logged in as an admin or staff account at https://reporting.scilifelab.se/
-
Go to the form for this year's DDLS Fellow reports in the Reporting Portal.
-
Click on the button "Aggregate".
-
Check the following fields for output.
- Report status filter: Submitted
- Report metadata: identifier, title
- Report history: [none]
- Report owner: [all]
- Report fields: [all except volume_data]
- File format: Excel (XLSX)
-
Create and download the aggregate file by clicking the button "Aggregate".
-
Move this XLSX file to the subdirectory
aggregate_files
for this year's report data.
-
Go to the list of all reports.
-
Filter by the appropriate form, and the status "Submitted".
-
Download manually the volume data files for all reports to the subdirectory
volume_data_files
for this year's report data.
-
Check and set the parameters for the filepaths and other data in the source code file
merge_ddls_fellows.py
. -
Run the script
merge_ddls_fellows.py
, which produces the filesDDLS Fellows {year} Teaching.xlsx
,DDLS Fellows {year} Grants.xlsx
andDDLS Fellows {year} Collaborations.xlsx
from the volume data files.NOTE: Some of the
XLSX
/XLSM
files cause "UserWarning" when read byopenpyxl
. This can be ignored.