r/Glasgow Tools

zak75

Reddit URLhttps://www.reddit.com/user/zak75
Last 12 monthsTotalDeletedRemoved
r/Glasgow posts200
r/glasgow comments1500

Interests:

  • Here's the [**latest public data for Glasgow City neighbourhoods in May**](https://i.imgur.com/epvefgE.png) [*(click here for notes and sources)*](https://www.reddit.com/user/zak75/comments/kgxb6w/notes_and_sources_for_covid19_data_and/) which further confirms that the outbreak is centred around Pollokshields West and East. Glasgow currently has a rate of 100.3 per 100,000 of population and remarkably Pollokshields West and East account for 21.6 of that rate on their own! The [*postcodes*](https://en.wikipedia.org/wiki/G_postcode_area) in the tweet do mostly match where the highest number of cases are, at least in Glasgow City. by zak75 (Mon 17th May 2021 5:32pm)
  • > I work in BI. I approve of this viz. Oh man, is it as bad as that?! ;) by zak75 (Tue 18th May 2021 10:17am)
  • Oh, I forgot to put that explanatory note on this time! Where there is an asterisk the data has been suppressed for privacy reasons. Neighbourhood data is suppressed if there are fewer than 3 cases in a 7-day period, so where there is an asterisk the total cases will range from 0-2. by zak75 (Fri 21st May 2021 4:38pm)
  • Thanks! The table was created by me using data from the [*Public Health Scotland Seven day trends by Neighbourhood dataset*](https://www.opendata.nhs.scot/dataset/covid-19-in-scotland/resource/8906de12-f413-4b3f-95a0-11ed15e61773). If you'd like more info [**click here for my notes and sources post.**](https://www.reddit.com/user/zak75/comments/kgxb6w/notes_and_sources_for_covid19_data_and/) The datasets generally get updated daily just after 2PM. I should possibly warn that the neighbourhood dataset is currently just under half a million rows and tomorrow it'll be over because 1,279 rows are added to it most days, so it's rather large and for most folk not ideal. An easier option might be the [**Public Health Scotland COVID-19 dashboard on tableau**](https://public.tableau.com/profile/phs.covid.19#!/vizhome/COVID-19DailyDashboard_15960160643010/Overview) but it's rather sluggish and not very good for making easy comparisons between neighbourhoods or over time. by zak75 (Fri 21st May 2021 8:32pm)
  • Thanks, that's really nice to hear! :) Here's another one based on today's data: * [**May 22: latest stats for all neighbourhoods in East Ren and Glasgow City**](https://i.imgur.com/cmwm9Q6.png) The main headline would be Pollokshaws can now be considered as part of the current outbreak, after jumping into 7th place from 29 the day before. **Latest council rates:** * East Renfrewshire: 108.9 per 100,000 (-7.3) * Glasgow City: 126.7 per 100,000 (+4.1) by zak75 (Sat 22nd May 2021 3:22pm)
  • Looks like some folk don't like the idea of anyone finding the covid stats useful! Here's some more based on today's data: * [**May 23: latest stats for all neighbourhoods in East Ren and Glasgow City**](https://i.imgur.com/t8sjmsS.png) by zak75 (Sun 23rd May 2021 5:34pm)
  • Here's the latest neighbourhood case data for East Renfrewshire and Glasgow City: * [**latest stats for all the neighbourhoods in East Ren and Glasgow**](https://i.imgur.com/t8sjmsS.png) * [**chart of the 10 worst neighbourhoods in ER&GC (by recent 7-day case rates) over last fortnight**](https://i.imgur.com/42w5ySw.png) * [**chart of the 10 worst neighbourhoods in ER&GC (by recent 7-day case totals) over last fortnight**](https://i.imgur.com/Jkhsrjc.png) Although the local stats might not look great they're quite encouraging. The main outbreak has remained relatively localised and the worst outbreaks haven't continued to grow exponentially to the crazy levels we saw in the Wigtownshire new variant outbreak at Christmas and Ne'erday. Along with all the other data it underlines how much things have changed with a significant chunk of the population vaccinated. [*Click here for notes and sources*](https://www.reddit.com/user/zak75/comments/kgxb6w/notes_and_sources_for_covid19_data_and/) by zak75 (Sun 23rd May 2021 5:45pm)
  • Here's the latest Covid-19 stats for councils: * [**Latest Covid-19 stats for all council areas in Scotland on May 28th**](https://i.imgur.com/K7rfU8M.png) The linked visualisations were created by me from the following Public Health Scotland datasets: [*Seven day trends by Neighbourhood*](https://www.opendata.nhs.scot/dataset/covid-19-in-scotland/resource/8906de12-f413-4b3f-95a0-11ed15e61773) and the [*Daily Case Trends By Local Authority*](https://www.opendata.nhs.scot/dataset/covid-19-in-scotland/resource/427f9a25-db22-4014-a3bc-893b68243055). by zak75 (Fri 28th May 2021 3:13pm)
  • Here's the latest Covid-19 stats for councils: * [**Covid-19 stats for all council areas in Scotland on May 28th**](https://i.imgur.com/K7rfU8M.png) The linked visualisations were created by me from the following Public Health Scotland datasets: [*Seven day trends by Neighbourhood*](https://www.opendata.nhs.scot/dataset/covid-19-in-scotland/resource/8906de12-f413-4b3f-95a0-11ed15e61773) and the [*Daily Case Trends By Local Authority*](https://www.opendata.nhs.scot/dataset/covid-19-in-scotland/resource/427f9a25-db22-4014-a3bc-893b68243055). by zak75 (Fri 28th May 2021 4:24pm)
  • Haha, how can anyone not?! :) For stuff like this though, I only really use Excel (or LibreOffice) to prettify the output. All the number crunching and everything else is done in [Jupyter](https://jupyter.org/) notebooks. by zak75 (Fri 28th May 2021 8:08pm)
  • Thankfully for Glasgow things aren't looking quite as bad as the stats might suggest, in fact they're still pretty encouraging. As expected the outbreak has spread to more neighbourhoods but so far none of them have been anywhere near as bad as the main outbreak in Pollokshields West and East. Here's a chart of some of the neighbourhoods with the highest rates in Glasgow City over the last couple of weeks: * [**Glasgow City outbreak selected neighbourhoods chart by rates (data on May 28th)**](https://i.imgur.com/akLEWzR.png) As you'll see things are looking much better than they were a fortnight ago and it looks like the interventions are working. We'll have a better idea in a few days but there's more than a few grounds for optimism, at least for Glasgow City. by zak75 (Fri 28th May 2021 8:43pm)
  • On a related note, in case folk would like to see the raw number of new cases each day, here's a chart of that: * [**Daily number of new cases in Glasgow City over the last month (data: May 28th)**](https://i.imgur.com/OWTJ11M.png) As folk'll see, daily cases in Glasgow City are often more than 5 times what they were a month ago! by zak75 (Fri 28th May 2021 9:10pm)
  • Sure, no problem. Reddit is very image focused but, like you say, that has plenty of downsides such as finding specific info or limiting how much data it's practical to show. Taking advantage of not being an image, here's the outbreak data going back to May 1st in JSON format: * [**JSON of cases rates for all the neighbourhoods in May (so far)**](https://pastebin.com/4Ke4A6Yy) * [**JSON of 7-day cases totals for all the neighbourhoods in May (so far)**](https://pastebin.com/4Ke4A6Yy) by zak75 (Sat 29th May 2021 11:14am)
  • **On the latest data available right now** (today's data gets updated around 2PM) **no council has higher 7-day rates or 7-day running average test positivity than Glasgow City**. Some are rising fast though, so that may change soon, if not today. Here's the full table: | council | new cases 7d_tot | new cases rate 7dper100k | new deaths 7dper100k | tests 7dper100k | tests positive % 7d_ra | |---------------------|:----------------:|:---------------------:|:----------------------:|:---------------:|:------------------------:| | Aberdeen City | 34 | 14.9 | 0.0 | 2,657.5 | 0.6 | | Aberdeenshire | 26 | 10.0 | 0.0 | 2,459.3 | 0.4 | | Angus | 23 | 19.8 | 0.0 | 2,797.8 | 0.7 | | Argyll & Bute | 9 | 10.5 | 0.0 | 1,972.7 | 0.5 | | City of Edinburgh | 274 | 52.2 | 0.0 | 2,479.2 | 2.2 | | Clackmannanshire | 65 | 126.1 | 0.0 | 6,379.5 | 2.2 | | Dumfries & Galloway | 5 | 3.4 | 0.0 | 2,243.7 | 0.2 | | Dundee City | 113 | 75.7 | 0.0 | 2,445.8 | 3.1 | | East Ayrshire | 44 | 36.1 | 0.0 | 2,701.4 | 1.5 | | East Dunbartonshire | 77 | 70.9 | 0.0 | 2,937.2 | 2.7 | | East Lothian | 14 | 13.1 | 0.0 | 2,454.9 | 0.5 | | East Renfrewshire | 102 | 106.8 | 0.0 | 3,141.4 | 3.6 | | Falkirk | 51 | 31.7 | 0.6 | 2,987.1 | 1.3 | | Fife | 109 | 29.2 | 0.0 | 2,561.1 | 1.2 | | Glasgow City | 858 | 135.5 | 0.2 | 3,588.7 | 4.2 | | Highland | 18 | 7.6 | 0.0 | 2,047.2 | 0.4 | | Inverclyde | 12 | 15.4 | 0.0 | 2,750.6 | 0.6 | | Midlothian | 79 | 85.4 | 0.0 | 3,074.8 | 3.1 | | Moray | 14 | 14.6 | 0.0 | 2,314.8 | 0.8 | | Na h-Eileanan Siar | 0 | 0.0 | 0.0 | 2,638.5 | 0.0 | | North Ayrshire | 81 | 60.1 | 0.0 | 2,984.3 | 2.1 | | North Lanarkshire | 143 | 41.9 | 0.0 | 2,760.1 | 1.7 | | Orkney Islands | 0 | 0.0 | 0.0 | 1,508.8 | 0.0 | | Perth & Kinross | 43 | 28.3 | 0.0 | 2,704.8 | 1.3 | | Renfrewshire | 207 | 115.6 | 0.0 | 3,565.0 | 3.4 | | Scottish Borders | 5 | 4.3 | 0.0 | 1,610.3 | 0.3 | | Shetland Islands | 0 | 0.0 | 0.0 | 2,194.6 | 0.0 | | South Ayrshire | 39 | 34.6 | 0.0 | 2,322.2 | 1.5 | | South Lanarkshire | 202 | 63.0 | 0.0 | 3,046.5 | 2.3 | | Stirling | 44 | 46.7 | 0.0 | 2,580.4 | 2.1 | | West Dunbartonshire | 23 | 25.9 | 0.0 | 2,945.0 | 0.9 | | West Lothian | 40 | 21.8 | 0.0 | 2,066.1 | 1.1 | source: the Public Health Scotland [*Daily Case Trends By Local Authority*](https://www.opendata.nhs.scot/dataset/covid-19-in-scotland/resource/427f9a25-db22-4014-a3bc-893b68243055) dataset. by zak75 (Sat 29th May 2021 12:15pm)
  • For the raw neighbourhood data: [**Public Health Scotland: Seven day trends by Neighbourhood dataset**](https://www.opendata.nhs.scot/dataset/covid-19-in-scotland/resource/8906de12-f413-4b3f-95a0-11ed15e61773). When you click the link you'll see that you have a variety of options, from downloading a CSV to querying the Data API via SQL. At first the only way to get neighbourhood data was extracting it bit by bit from the Tableau dashboard, I can confirm this is a vastly better option! :) If you're interested in analysing the data be sure to check out the other PHS datasets available there. by zak75 (Sat 29th May 2021 12:46pm)