Skip to content

tomwhite/covid-19-uk-data

Repository files navigation

COVID-19 UK Historical Data

⚠️ Update: 1 August 2020. This repository is deprecated and is no longer updated. Users are encouraged to move to official upstream data sources which are listed below ⚠️

Data on numbers of tests, confirmed cases, and deaths for coronavirus (COVID-19) in the UK is published by the government, but it is fragmented and not always provided in consistent or machine-friendly formats. Also, in many cases only the latest numbers are available so it's not possible to look at changes over time.

This site collates the historical data and provides it in an easily consumable format (CSV), in both wide and tidy data forms.

Ideally the data publishers will start doing this so this site becomes redundant.

Data files

The following CSV files are available (note they are no longer updated):

  • data/covid-19-cases-uk.csv: daily counts of confirmed cases for (upper tier) local authorities in England, health boards in Scotland and Wales, and local government district for Northern Ireland.
    • Note that prior to 18 March 2020 Wales data was broken down by local authority, not heath board, and prior to 27 March 2020 there were no breakdowns by area for Northern Ireland.
  • data/covid-19-totals-uk.csv: daily counts of tests, confirmed cases, deaths for the whole of the UK
  • data/covid-19-totals-england.csv: daily counts of tests, confirmed cases, deaths for England
  • data/covid-19-totals-northern-ireland.csv: daily counts of tests, confirmed cases, deaths for Northern Ireland
  • data/covid-19-totals-scotland.csv: daily counts of tests, confirmed cases, deaths for Scotland
  • data/covid-19-totals-wales.csv: daily counts of tests, confirmed cases, deaths for Wales
  • data/covid-19-indicators-uk.csv: daily counts of tests, confirmed cases, deaths for the whole of the UK and individual countries in the UK (England, Scotland, Wales, Northern Ireland). This is a tidy-data version of covid-19-totals-*.csv combined into one file.
  • data/daily/*.csv: daily counts, with a separate file for each date and country.

Interpreting the numbers (more information on this DHSC/PHE page, and the PHE dashboard about page)

  • "Tests" are the number of people tested, not the number of samples tested.
  • "Confirmed cases" are the number of people with a positive test.
  • "Deaths" are hospital deaths, so they don't include deaths of people with COVID-19 who died at home for example. (Although this changed in England on 29 April 2020.)

Note that the totals for the UK don't necessarily equal the sum of the totals of the four nations (England, Scotland, Wales, Northern Ireland), due to differences in date reported.

You can use these files without reading the rest of this document.

There is an experimental Datasette instance hosting the data. This is useful for running simple SQL on the data, or exporting in JSON format.

News

  • 1 August 2020. Retired this repo. See discussion here.
  • 2 July 2020. PHE started including Pillar 2 data in England confirmed case numbers. This data is now being included in this repository.
  • 1 July 2020. England UTLA confirmed case data is no longer being included since it doesn't have Pillar 2 tests, which make up the vast majority of tests.
  • 1 July 2020. NI data is no longer being included since the (undocumented) backend API changed again, and the NI Department of Health does not provide a machine-readable alternative. (See 2 June 2020 entry below.)
  • 30 June 2020. With the new Leicester lockdown, media attention around the lack of Pillar 2 data in England has increased. I have added a prominent warning to the top of this README.
  • 2 June 2020. I received a reply from the NI Department of Health to my enquiry about making machine readable downloads available. For this reason I may stop collating NI data in this repository, since the JSON API the code uses is undocumented and changes from time-to-time. See #63.

Mr White

Thank you for your query. Currently, the information on which the dashboard statistics are based is being drawn from live systems and the data is continually being revised. This means that we do not at this time feel it would be appropriate to provide data that is still volatile and is subject to both revision and change.

Regards

Information and Analysis Directorate

  • 28 May 2020. DHSC is now providing a timeseries of testing data, linked to from this DHSC/PHE page.
  • 23 May 2020. DHSC is no longer reporting the number of people tested (daily or cumulative) in Pillar 2, hence it is not possible to give an overall total.
  • 12 May 2020. The PHW dashboard data download link is no longer static - it changes every day, and there is no easy way to retrieve it, since it is dynamically generated in Tableau.
  • 1 May 2020. The NI Department of Health dashboard has been re-instated.
  • 28 April 2020. The NI Department of Health is no longer reporting the number of people tested, just the number of tests.
  • 21 April 2020. The PHA NI dashboard was suspended since it was reporting incorrect data. Test and total confirmed case numbers are being announced on Twitter by @healthdpt. Area breakdowns are no longer being provided.
  • 21 April 2020. The PHW dashboard now has a link to download the data in XLSX format. The URL is dynamically generated however, so it's still not easy to automate the download.
  • 20 April 2020. The PHE dashboard now has stable URLs for its CSV downloads.
  • 18 April 2020. PHA NI launched a dashboard to replace the daily surveillance reports.
  • 15 April 2020. A new dashboard for UK and England was launched, replacing the ArcGIS one. As a part of this change the XLSX/CSV files for daily indicators, and case counts by region and UTLA (in England) are no longer being produced. They have been replaced by CSV files, or - for programmatic access - a JSON feed.
  • 14 April 2020. No per-area case numbers produced for NI, even though it is a weekday (Tuesday). Yesterday was a bank holiday, and no case numbers were produced either.
  • 9 April 2020. The reporting period for case numbers in Wales changed. "For operational reasons, we are moving the point at which we count new cases of Novel Coronavirus (Covid-19) back from 7pm to 1pm. Case numbers on Thursday [9 April] will therefore be lower than usual, and will return to normal on Friday [10 April]."
  • 8 April 2020. Scotland started publishing numbers for people in hospital and intensive care, by health board. They also started reporting numbers that were less 5 as "*".
  • 6 April 2020. Wales published a new interactive dashboard, which gives data for confirmed cases, and testing episodes, broken down by local authority and health board. There is historical data too. Unfortunately there is currently no way of exporting the raw data from the dashboard.
  • 2 April 2020. Scotland reported a more timely process for counting deaths.
  • 29 March 2020. There's a new spreadsheet that includes historical data for the dashboard. This includes cases (by country, English UTLA, English NHS region), deaths (by country), and recovered patients (although this isn't being updated at the time of writing).
  • 27 March 2020. UK daily indicators now include number of deaths for UK, England, Scotland, Wales, and Northern Ireland.
  • 26 March 2020. Northern Ireland's Public Health Agency (PHA) started publishing confirmed cases by Local Government District (LGD) on weekdays.
  • 25 March 2020. The reporting period for number of deaths changed. Previously it was for the 24 hour period starting and ending at 9am. The new period starts and ends at 5pm, and is reported the following afternoon at 2pm. (So the number of deaths reported on 25 March (cumulative total 463) represents the period 9am to 5pm on 24 March.) The testing and case numbers continue to be the 9am period.
  • 24 March 2020. Northern Ireland's Public Health Agency (PHA) started producing a Daily COVID-19 Surveillance Bulletin in PDF form. It contains test numbers (also broken down by Health and Social Care Trust), and case numbers but only on a choropleth map (and broken down by age and gender).
  • 21 March 2020. PHW is back to health board (not LA) breakdowns again, this time it looks permanent.
  • 20 March 2020. PHW is providing LA area breakdowns again, after not doing so for two days.
  • 18 March 2020. PHW is no longer providing LA area breakdowns. "Novel Coronavirus (COVID-19) is now circulating in every part of Wales. For this reason, we will not be reporting cases by local authority area from today. From tomorrow, we will update daily at 12 noon the case numbers by health board of residence."

Data sources

The following sources may include more data than described here. This summary includes only Tests, Confirmed cases and Deaths.

UK

  • Source: UK testing time series (CSV)
    • Tests: number of people tested (Pillar 1 only) by day in UK, England, Scotland, NI; (Pillar 1 and 2) Wales
    • Confirmed cases: number of confirmed cases (Pillar 1 and 2) by day in UK, England, Scotland, Wales, NI
  • Source: UK daily deaths time series (CSV)
    • Deaths: number of deaths by day in UK
  • Source: UK dashboard deaths (CSV) (JSON)
    • Deaths: number of deaths by day in UK, England, Scotland, Wales, NI
  • Charts available on the PHE dashboard
  • Twitter updates: @DHSCgovuk

England

  • Source: UK dashboard cases (CSV) (JSON)
    • Confirmed cases: number of confirmed cases (Pillar 1 and 2) by day in England, regions, UTLAs, LTLAs
  • Charts available on the PHE dashboard
  • Twitter updates: @PHE_uk

Scotland

  • Source: Trends in daily COVID-19 data (XLSX) (CSVs)
    • Tests: number of people tested (Pillar 1, and Pillar 2 since 15 June) by day in Scotland (CSV)
    • Confirmed cases: number of confirmed cases (Pillar 1, and Pillar 2 since 15 June) by day in Scotland (CSV)
    • Deaths: number of deaths by day in Scotland (CSV)
  • Source: COVID-19 data by NHS Board (XLSX) (CSV)
    • Confirmed cases: number of confirmed cases (Pillar 1, and Pillar 2 since 15 June) by day by health board
  • See also statistics.gov.scot
  • Charts available on the PHS dashboard
  • Twitter updates: @scotgov

Wales

  • Source: Data download (XLSX)
    • Tests: number of people tested (Pillars 1 and 2) by day by local authority
    • Confirmed cases: number of confirmed cases (Pillars 1 and 2) by day by local authority
    • Deaths: number of deaths by day in Wales; number of cumulative deaths by health board
  • More information and charts available on the PHW dashboard
  • Twitter updates: @PublicHealthW

Northern Ireland

  • Source: No machine-readable dataset available
  • Charts available on the Department of Health dashboard
    • Includes number of people tested and confirmed cases for Pillar 1, and Pillar 2 since 24 June.
  • Twitter updates: @healthdpt

Local Authority and Health Board metadata

Related projects/datasets

Wishlist

Here are my suggestions for how to improve the data being published by public bodies.

The short version: publish everything in CSV format, and include historical data!

  • Public Health Agency, Northern Ireland: Provide a machine readable version of the historical data on the dashboard.

The reporting systems have changed a lot since the outbreak began, and overall they have improved, both in the amount of information being published, and the ease of access of machine-readable datasets. (Public Health Scotland provides all their data in XLSX and CSV format, including historical data. Public Health Wales provides a XLSX spreadsheet with historical data.)

Tools

There are command line tools for downloading, parsing, and processing the data. They rely on Python 3.

To install the tools, create a virtual environment, activate it, then install the required packages:

python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt

Daily workflow

A sqlite DB is now used to store and aggregate intermediate data. The CSV files remain the point of record.

The crawl tool will see if the reseouce (webpage, date file) has already been downloaded, and if it hasn't download it if it's available for the specified date (today). (If not available the tool will exit.) If available, the tool will then extract the relevant information from it and update the sqlite database. This means that you can just run crawl until it finds new updates.

The convert_sqlite_to_csvs tool will extract the data from sqlite and update the CSV files.

The updates tool runs crawl then convert_sqlite_to_csvs, and issues interactive prompts for if you want to commit the changes to git.

There is also a crawl_all tool (and corresponding update_all tool) that uses machine-readable sources to update all historical data for that source. This is not available for all sources yet.

./tools/update_all.sh phw
./tools/update_all.sh phs
./tools/update.sh NI
./tools/update.sh UK
./tools/update_all.sh phe

The equivalent done manually (just for Wales):

DATE=$(date +'%Y-%m-%d')
./tools/crawl.py $DATE Wales
./tools/convert_sqlite_to_csvs.py
git add data/; git commit -am "Update for $DATE for Wales"

NI updates are being done manually since there are currently no machine-readable sources.

# edit covid-19-totals-northern-ireland.csv and add tests/cases/deaths
./tools/convert_totals_to_indicators.py
csvs-to-sqlite --replace-tables -t indicators -pk Date -pk Country -pk Indicator data/covid-19-indicators-uk.csv data/covid-19-uk.db
./tools/convert_sqlite_to_csvs.py
git commit -a # "Update for xxx for NI from https://twitter.com/healthdpt"

Updates are not always made at a consistent time of day, so the following command can be run continuously in a terminal to check for updates every 10 minutes. The -b option makes it beep if there is a new update.

watch -n 600 -b ./tools/crawl.py

Check data consistency

./tools/check_indicators.py
./tools/check_totals.py

Manual overrides

Sometimes it's necessary to fix data by hand. In this case the following tools are useful:

Repopulate the sqlite database from the CSV files:

rm data/covid-19-uk.db
csvs-to-sqlite --replace-tables -t indicators -pk Date -pk Country -pk Indicator data/covid-19-indicators-uk.csv data/covid-19-uk.db
csvs-to-sqlite --replace-tables -t cases -pk Date -pk Country -pk AreaCode -pk Area data/covid-19-cases-uk.csv data/covid-19-uk.db