Historical deaths by UTLA

[ryanjgillies]

Is there a data source that can show historical counts of deaths, broken down by UTLA?

[tomwhite]

Not that I know of. By NHS trust is the closest (England only): https://www.england.nhs.uk/statistics/statistical-work-areas/covid-19-daily-deaths/

[lcgrogers]

It could be useful to have daily numbers of hospital admissions for covid-19 illness.

[Jcamain]

Hmm ... Looks like Public Health England have moved all their data and website to https://coronavirus.data.gov.uk/#local-authorities - but I can't see their data files .. I've emailed the email address they have their to find out if they are still making this publicly available, will report back when I hear.

[lcgrogers]

Thanks for checking!

…

On Apr 14 2020, Julie Main wrote: Hmm ... Looks like Public Health England have moved all their data and website to https://coronavirus.data.gov.uk/#local-authorities - but I can't see their data files .. I've emailed the email address they have their to find out if they are still making this publicly available, will report back when I hear.

-- /////////////////////////////////////////////////////////////////// // Professor L C G Rogers // // Statistical Laboratory // // Wilberforce Road Web: www.statslab.cam.ac.uk/~chris // // Cambridge CB3 0WB, GB // ///////////////////////////////////////////////////////////////////

[Jcamain]

Looks like they are going to start providing the data again today 'Time series data will be available to download from 15/04/2020' - Also, I can do a mapping file from UTLA that plots which hospitals are in which UTLA based on the lat / long, and which polygons they reside in - just need to get the list of hospitals to match up from a naming perspective as I'd imagine the data is a bit dodge as per usual!

[tomwhite]

@lcgrogers there are hospital numbers for Scotland (https://www.gov.scot/publications/coronavirus-covid-19-daily-data-for-scotland/, not in the data in this repo, although it could be added).

@Jcamain thanks - yes, it looks like they are working on CSV judging by the github activity (https://github.com/PublicHealthEngland/coronavirus-dashboard/commits/master). In the meantime I crawled the HTML page to get the UTLA case numbers for England for 14/04/2020.

[Jcamain]

Thanks @tomwhite - can I be really cheeky and see if you can work your magic on this: https://www.england.nhs.uk/statistics/statistical-work-areas/covid-19-daily-deaths/ It's by NHS Trust - I can map this back to UTLA and post an excel of the mapping, can also include links to the various shape files etc. I used (mostly ONS). Just when I think my dashboard is finished, I find more data ;-)

[Shotgunosine]

@Jcamain were you able to get the daily deaths data mapped to UTLA's? Would you be willing to post the data somewhere, or @tomwhite can that be integrated into this dataset?

[Jcamain]

@Shotgunosine Next on my list - I've just finished V1 of my app ( I work for an analytics company so want to use decent analytical capability on the data that is being posted) - I'll post a link when it is up as we are making it centrally available for free. V2 is to bring in the death data - it's by NHS Trust, so I need to map it to UTLA (if I can't find the files, my companies software has geo operations I can use) - I'm going to make a start on this probably about weds - If @tomwhite hasn't got the time to get the daily updates into a central place that's easy to pick up, my boyf finishes his contract at the end of the week, so I'll get him to do it and make it available - here might be the best place as it's a fab source? He's a dev so used to using github - me not so much.

[Shotgunosine]

@Jcamain Sounds great, thanks!

[Shotgunosine]

@Jcamain Just checking in to see if you've been able to bring in deaths data.

Thanks!

[Jcamain]

Fun and games - Death Data on a daily basis is: https://www.england.nhs.uk/statistics/statistical-work-areas/covid-19-daily-deaths/ - but at NHS Trust Level - I've managed to finally do some mapping at STP Level so that I can view deaths that link to UTLA (although it won't go down that far - but does allow you to bring cases to the level), STP's changed last April, so interesting finding the files to use - I found them here: https://digital.nhs.uk/services/organisation-data-service/data-downloads/other-nhs-organisations (bottom of the page) STP Partner Organisations. I also use a function to take the central geopoint of each UTLA and plot within STP Polygons (http://geoportal.statistics.gov.uk/datasets/sustainability-and-transformation-partnerships-april-2019-en-bfc). I haven't automated the storage of the daily data yet - but there is another source here, weekly, which is at Local Government Level, https://www.ons.gov.uk/peoplepopulationandcommunity/healthandsocialcare/causesofdeath/datasets/deathregistrationsandoccurrencesbylocalauthorityandhealthboard I've got a mapping file from LG to UTLA. Need to tidy everything up. Never as easy as you think when Govt and health changes things!

[Shotgunosine]

Hi @Jcamain do you have the data in a format you can share? Even just the mapping file would be a big help. Thanks!

[Jcamain]

@shotgunsosine. No problem - and apols for the delay, we store in proprietary format, so have just exported to csv, so hope it works!! I've also enclosed my data sources, most of these are towards the bottom - some of them I have derived from multiple files. Hope this all makes sense.
UTLAtoSTPMap.xlsx

Clinical_Commissioning_Group_to_STP_and_NHS_England_Region_April_2020_Lookup_in_England.xlsx
Covid19 - Data Sources Locations.xlsx
LLTAtoULTA.xlsx
ONSPopulation.xlsx
STP+Partners+MASTER.xlsx

[Shotgunosine]

Sorry @Jcamain, I don't know that I follow what these files are. If I've got the deaths data (COVID-19-total-announced-deaths-1-May-2020.xlsx) from NHS, what do I need to do to map the codes in that table to UTLAs to figure out which UTLAs are contributing to each of the rows of the deaths data?

If you meant to attach a csv to that last post I don't see it, only Excel files.

[Jcamain]

@Shotgunosine - It's tricky to map directly to UTLA's as the daily death information isn't at that level - it is at NHS Trust level, so instead I've used STP's (Sustainability and Transformation Partnerships), as an NHS Trust sits within an STP and an STP generally links to one or more UTLA's - You can get the UTLA figures (Well the Local Authority Figures) in the weekly report, but that is 11 days out of date. I'm still hunting for better data. I've enclosed the data model, hope that helps, if I get any further will update you.