Analysis of school funding in Wales http://leveltheplayingfield.wales/
Due to Covid-19, some data sources were not updated in 2020 (see notes below). Therefore some charts and tables cannot be updated for the latest school year.
The majority of the data is from Stats Wales.
CSV files in the data directory were downloaded from the Stats Wales website, and are named after the final part of the Stats Wales URL.
- https://statswales.gov.wales/Catalogue/Population-and-Migration/Population/Estimates/Local-Authority/populationestimates-by-localauthority-year
- Note: Make sure Wales is the only expanded country. Click on Year dropdown, and Show All to get all years. Also one export per age group too.
- https://statswales.gov.wales/Catalogue/Local-Government/Finance/Revenue/Delegated-School-Budgets/delegatedschoolbudgetsperpupil-by-sector (Individual schools budget per pupil, and Number of pupils)
- https://statswales.gov.wales/Catalogue/Local-Government/Finance/Revenue/Delegated-School-Outturn/levelofreservescarriedforward-by-sector
- https://statswales.gov.wales/Catalogue/Local-Government/Finance/Revenue/Delegated-School-Budgets/delegatedschoolbudgetsperpupil-by-school (Individual schools budget, Individual schools budget per pupil, and Number of pupils)
- Note: Make sure Caerphilly Middle schools are expanded.
- From 2020-21. This data was previously entered into Google Sheets
- https://statswales.gov.wales/Catalogue/Local-Government/Finance/Revenue/Delegated-School-Outturn/levelofreservescarriedforward-by-school
- Note: Make sure Middle schools are expanded.
- From 2020-21. This data was previously entered into Google Sheets
Data in the data/geo directory is from https://gov.wales/address-list-schools. Download the latest ODS file, and export the "Maintained" sheet to CSV (using OpenOffice). Postcode to lat/long conversion was carried out using https://www.doogal.co.uk/BatchGeocoding.php.
Data in the data/sheets directory is data that was retrieved via FOI requests (see the website for more information) and combined with data from the Stats Wales site to give a per-school view.
This data is stored in Google Sheets, but a local copy is provided in data/sheets to make it possible to reproduce all the analyses.
Starting from 2019, the school support category data was downloaded from gov.wales in XLSX format and saved as CSV files in the data/support-category directory.
- Not updated in 2020, and will not be assessed for 5 years.
In RStudio, run batch.R to generate all the reports in the docs directory. These are hosted on the main website at http://leveltheplayingfield.wales.
There are custom analyses in the posts directory for generating reports for the blog posts. These are written to the docs/posts directory.
There are experiments and ad hoc analyses in adhoc.R. These do not create output for the website.
If you wish to run your own analyses, then run load_data.R in RStudio, which will load all the data
into several dataframes (all_schools, population_with_age, etc).
To update the reports every year:
- Download new data as listed in "Data Sources" above.
- Download the new list of schools. (TODO: document how to update school list - see qa.R)
- Update the "Delegation rates %" spreadsheet in Google Sheets.
- Add FSM numbers from My Local School to the spreadsheets in Google Sheets. (This data is not available on Stats Wales)
- Run download_data.R.
- Run batch.R.
- Run
python3 make_site.py, and compare the local site to the live one. - Upload the new site.