Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

Re: Coronavirus - Numbers and Statistics

The home for all non-political Coronavirus (Covid-19) discussions on The Lemon Fool
Forum rules
This is the home for all non-political Coronavirus (Covid-19) discussions on The Lemon Fool
servodude
Lemon Half
Posts: 8271
Joined: November 8th, 2016, 5:56 am
Has thanked: 4435 times
Been thanked: 3564 times

Re: Coronavirus - Numbers and Statistics

#468458

Postby servodude » December 24th, 2021, 11:18 pm

MrFoolish wrote:
dealtn wrote:
MrFoolish wrote:
I've long since forgotten my stats. How do you work it out?


Assuming there is true independence any person is 34/35 probability free of Covid. So ask the question how many times do you need to multiply 34/35 by itself to be less than 0.5. That is the chance of at least 1 person being infected is greater than the chance all are free of Covid.

34/35 to the power of 24 is just below 0.5


Thanks. I tried raising 1/35 to the power of 24 and obviously just ended up with a nonsense tiny number. It didn't occur to me to try something with 34/35. Is there a name for this sort of calculation?


"binomial probably" would be closest I think

jfgw
Lemon Quarter
Posts: 2539
Joined: November 4th, 2016, 3:36 pm
Has thanked: 1097 times
Been thanked: 1146 times

Re: Coronavirus - Numbers and Statistics

#468704

Postby jfgw » December 27th, 2021, 5:41 pm

Cases by age data for England have now been published including specimen date up to 22/12/2021.

All graphs: My graph. Contains public sector information licensed under the Open Government Licence v3.0. : https://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/

Source: https://coronavirus.data.gov.uk/details/cases?areaType=nation&areaName=England and regional data from the same site.

Unlike data for Wales, England new cases data do not include reinfections where people have previously tested positive.

All graphs use the same y-axis scale except for London.

Image

London has already peaked for some age groups,
Image

Other regions seem to be following a similar pattern,
Image
Image
Image
Image
Image
Image
Image
Image


Julian F. G. W.

jfgw
Lemon Quarter
Posts: 2539
Joined: November 4th, 2016, 3:36 pm
Has thanked: 1097 times
Been thanked: 1146 times

Re: Coronavirus - Numbers and Statistics

#468709

Postby jfgw » December 27th, 2021, 6:29 pm

Notes regarding graphs:

An error which crept into my "Cases by Age" spreadsheets has now been corrected. This resulted in absolute cases data being plotted for the 80—84, 85—90 and 90+ age groups instead of the 7-day rolling rate. I suspect that this happened when I had to amend my spreadsheet due to a small change in the way the government reported their data (possibly the inclusion of 0—59 and 60+ age groups).

I am not a programmer and am not familiar with JSON or XML formats. There is a CSV download option (which I use) but the data appear (to me) to be JSON format between square brackets so that they end up in one cell. Row 1 of this CSV file consists of:
areaType, areaName, areaCode, date, newCasesBySpecimenDateAgeDemographics. (I'm not sure what you call this case. Headless Camel?)
The first four columns of the second row consists of (as of today):
nation, England, E92000001, 22/12/2021.
The fifth cell in the second row contains all of this:
[{'age': '00_04', 'cases': 1948, 'rollingSum': 11674, 'rollingRate': 360.4}, {'age': '00_59', 'cases': 111488, 'rollingSum': 599854, 'rollingRate': 1398.6}, {'age': '05_09', 'cases': 4828, 'rollingSum': 32390, 'rollingRate': 915.1}, {'age': '10_14', 'cases': 6497, 'rollingSum': 40992, 'rollingRate': 1193.2}, {'age': '15_19', 'cases': 8067, 'rollingSum': 45424, 'rollingRate': 1457.8}, {'age': '20_24', 'cases': 13571, 'rollingSum': 73386, 'rollingRate': 2113.3}, {'age': '25_29', 'cases': 15494, 'rollingSum': 83075, 'rollingRate': 2202.7}, {'age': '30_34', 'cases': 14654, 'rollingSum': 76684, 'rollingRate': 2005.0}, {'age': '35_39', 'cases': 12616, 'rollingSum': 64773, 'rollingRate': 1732.7}, {'age': '40_44', 'cases': 10993, 'rollingSum': 55873, 'rollingRate': 1607.3}, {'age': '45_49', 'cases': 9159, 'rollingSum': 46573, 'rollingRate': 1280.0}, {'age': '50_54', 'cases': 7572, 'rollingSum': 38522, 'rollingRate': 994.0}, {'age': '55_59', 'cases': 6089, 'rollingSum': 30488, 'rollingRate': 810.5}, {'age': '60+', 'cases': 10005, 'rollingSum': 47496, 'rollingRate': 347.7}, {'age': '60_64', 'cases': 3930, 'rollingSum': 18902, 'rollingRate': 591.3}, {'age': '65_69', 'cases': 2202, 'rollingSum': 10648, 'rollingRate': 382.4}, {'age': '70_74', 'cases': 1660, 'rollingSum': 7508, 'rollingRate': 266.8}, {'age': '75_79', 'cases': 1068, 'rollingSum': 4846, 'rollingRate': 241.1}, {'age': '80_84', 'cases': 568, 'rollingSum': 2689, 'rollingRate': 185.6}, {'age': '85_89', 'cases': 345, 'rollingSum': 1709, 'rollingRate': 193.0}, {'age': '90+', 'cases': 232, 'rollingSum': 1194, 'rollingRate': 229.1}, {'age': 'unassigned', 'cases': 46, 'rollingSum': 245, 'rollingRate': None}]

There may be a simple, straightforward, standard way to extract the data from this, and I would be interested to know if this is the case. Using my limited spreadsheet skills and multiple worksheets, I have managed to plot a graph from the sixth worksheet. Intermediate worksheets were used to locate the data, extract the strings of digits (which vary in length) and convert these into numbers. One good thing is that updating these spreadsheets is now just a copy and paste operation.

The vaccine CSV file is worse. This is just one age group:
[{'age': '12_15', 'VaccineRegisterPopulationByVaccinationDate': 2882623.0, 'cumPeopleVaccinatedCompleteByVaccinationDate': 48650.0, 'newPeopleVaccinatedCompleteByVaccinationDate': 221.0, 'cumPeopleVaccinatedFirstDoseByVaccinationDate': 1379152.0, 'newPeopleVaccinatedFirstDoseByVaccinationDate': 170.0, 'cumPeopleVaccinatedSecondDoseByVaccinationDate': 48650.0, 'newPeopleVaccinatedSecondDoseByVaccinationDate': 221.0, 'cumPeopleVaccinatedThirdInjectionByVaccinationDate': 358.0, 'newPeopleVaccinatedThirdInjectionByVaccinationDate': 0.0, 'cumVaccinationFirstDoseUptakeByVaccinationDatePercentage': 47.8, 'cumVaccinationCompleteCoverageByVaccinationDatePercentage': 1.7, 'cumVaccinationSecondDoseUptakeByVaccinationDatePercentage': 1.7, 'cumVaccinationThirdInjectionUptakeByVaccinationDatePercentage': None},...

The total contents of that one cell consists of 13864 characters. You can download the file for yourself here, https://coronavirus.data.gov.uk/details/vaccinations?areaType=nation&areaName=England

This does not seem, to me, to be the simplest or most efficient way to present data.


Julian F. G. W.

scotia
Lemon Quarter
Posts: 3561
Joined: November 4th, 2016, 8:43 pm
Has thanked: 2371 times
Been thanked: 1943 times

Re: Coronavirus - Numbers and Statistics

#468725

Postby scotia » December 27th, 2021, 9:11 pm

jfgw wrote:This does not seem, to me, to be the simplest or most efficient way to present data.
Julian F. G. W.

Agreed - way back when the data first became online, I tried to use ancient programming skills to extract the numbers I wanted into csv format which reads directly into a spreadsheet. It was (and remains) a very clunky piece of programming - using curl followed by a tortuous string of parameters. This gave me a csv file - gzip encoded, and I used 7-Zip to decode it - then read it into a spreadsheet with Excel. I automated this into a Windows batch file. ServoDude published a more sophisticated piece of software using Python - but with JSON output. If you could let me know which files seem to be returning you JSON format, I'll have a go with my software to see if I can get csv format.

jfgw
Lemon Quarter
Posts: 2539
Joined: November 4th, 2016, 3:36 pm
Has thanked: 1097 times
Been thanked: 1146 times

Re: Coronavirus - Numbers and Statistics

#468729

Postby jfgw » December 27th, 2021, 10:01 pm

If you go here, https://coronavirus.data.gov.uk/details/cases?areaType=nation&areaName=England, scroll down to the heatmap, and click on the "Download" button, you get a choice of CSV, JSON or XML. The CSV file does open in Excel but most of the data are in a single column.
The England (and regional) heatmap data for vaccinations and deaths are of a similar format.

Julian F. G. W.

scotia
Lemon Quarter
Posts: 3561
Joined: November 4th, 2016, 8:43 pm
Has thanked: 2371 times
Been thanked: 1943 times

Re: Coronavirus - Numbers and Statistics

#468737

Postby scotia » December 27th, 2021, 11:06 pm

jfgw wrote:If you go here, https://coronavirus.data.gov.uk/details/cases?areaType=nation&areaName=England, scroll down to the heatmap, and click on the "Download" button, you get a choice of CSV, JSON or XML. The CSV file does open in Excel but most of the data are in a single column.
The England (and regional) heatmap data for vaccinations and deaths are of a similar format.

Julian F. G. W.

OK - I can reproduce the problem. I'll try and crank my ancient brain back into action :) What computing facilities do you use? I'm most familiar with Microsoft Windows.

9873210
Lemon Slice
Posts: 984
Joined: December 9th, 2016, 6:44 am
Has thanked: 226 times
Been thanked: 296 times

Re: Coronavirus - Numbers and Statistics

#468742

Postby 9873210 » December 27th, 2021, 11:22 pm

During the dialogue during the import of the .CSV file you can put "[]{}:" (Notice the double quotes go into the field) in the "other separators" fields.

This does about half the work. It remains to delete the extra columns and create the column headers giving the ages.

Proper programming would be better. I'd use wget and sed if I were doing this more than a half dozen times.

jfgw
Lemon Quarter
Posts: 2539
Joined: November 4th, 2016, 3:36 pm
Has thanked: 1097 times
Been thanked: 1146 times

Re: Coronavirus - Numbers and Statistics

#468833

Postby jfgw » December 28th, 2021, 3:29 pm

I have both LibreOffice and OpenOffice. These let me specify column separators but this does not work. I am guessing that this is due to the square brackets.

I also have Excel. While I prefer this and find it much easier than the above freeware for most things, my Office 2000 version lacks the facility to specify column separators.

9873210 wrote:During the dialogue during the import of the .CSV file you can put "[]{}:" (Notice the double quotes go into the field) in the "other separators" fields.


Maybe if I splash out on a newer version of Excel...


Julian F. G. W.

jfgw
Lemon Quarter
Posts: 2539
Joined: November 4th, 2016, 3:36 pm
Has thanked: 1097 times
Been thanked: 1146 times

Re: Coronavirus - Numbers and Statistics

#468840

Postby jfgw » December 28th, 2021, 4:12 pm

Deaths within 28 days of a first positive test by age.

Image
My graph. Contains public sector information licensed under the Open Government Licence v3.0. : https://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/

Note the gap between the under-65 age groups and the 65+ age groups, suggesting that—once you reach 65—you are more likely to have a red slide sheet tied to the end of your hospital bed.


Julian F. G. W.

9873210
Lemon Slice
Posts: 984
Joined: December 9th, 2016, 6:44 am
Has thanked: 226 times
Been thanked: 296 times

Re: Coronavirus - Numbers and Statistics

#468846

Postby 9873210 » December 28th, 2021, 5:14 pm

jfgw wrote:I have both LibreOffice and OpenOffice. These let me specify column separators but this does not work. I am guessing that this is due to the square brackets.

I also have Excel. While I prefer this and find it much easier than the above freeware for most things, my Office 2000 version lacks the facility to specify column separators.

9873210 wrote:During the dialogue during the import of the .CSV file you can put "[]{}:" (Notice the double quotes go into the field) in the "other separators" fields.


Maybe if I splash out on a newer version of Excel...


Julian F. G. W.


This is a screenshot of the import screen from LibreOffice 7.2.4.1
The bottom shows how the fields will be separated with the displayed settings.
I suspect the magic is in the String Seperators area, particularly the "Other " field. IIRC I started with quote, square bracket got something and added the other characters till I got something useful.

I'm happy to work with you on this, but feel free to drop it if you don't find it a useful line of equiry.

Image

jfgw
Lemon Quarter
Posts: 2539
Joined: November 4th, 2016, 3:36 pm
Has thanked: 1097 times
Been thanked: 1146 times

Re: Coronavirus - Numbers and Statistics

#468868

Postby jfgw » December 28th, 2021, 7:15 pm

It has been a while since I published an admissions by region graph.

Image
My graph. Contains public sector information licensed under the Open Government Licence v3.0. : https://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/


Julian F. G. W.

jfgw
Lemon Quarter
Posts: 2539
Joined: November 4th, 2016, 3:36 pm
Has thanked: 1097 times
Been thanked: 1146 times

Re: Coronavirus - Numbers and Statistics

#468870

Postby jfgw » December 28th, 2021, 7:29 pm

9873210 wrote:This is a screenshot of the import screen from LibreOffice 7.2.4.1
The bottom shows how the fields will be separated with the displayed settings.
I suspect the magic is in the String Seperators area, particularly the "Other " field. IIRC I started with quote, square bracket got something and added the other characters till I got something useful.


Thank you for that. I found that I had to tick the "Merge delimiters" box otherwise it didn't work.

I currently have working spreadsheets. There is no guarantee that they will continue to work, for example, if fourth jabs or lower age groups are reported. I may use LibreOffice to verify my existing spreadsheets.

Thanks again,


Julian F. G. W.

jfgw
Lemon Quarter
Posts: 2539
Joined: November 4th, 2016, 3:36 pm
Has thanked: 1097 times
Been thanked: 1146 times

Re: Coronavirus - Numbers and Statistics

#468873

Postby jfgw » December 28th, 2021, 7:53 pm

Success!
Image
My graph. Contains public sector information licensed under the Open Government Licence v3.0. : https://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/


Julian F. G. W.

scotia
Lemon Quarter
Posts: 3561
Joined: November 4th, 2016, 8:43 pm
Has thanked: 2371 times
Been thanked: 1943 times

Re: Coronavirus - Numbers and Statistics

#468991

Postby scotia » December 29th, 2021, 12:21 pm

jfgw wrote:It has been a while since I published an admissions by region graph.

Julian F. G. W.

Yes - thanks for the data - the admissions are soaring - and I would guess that other regions will soon match the London climb.
On my all-England graphs, the admissions, although delayed over Christmas, are still continuing to climb at the steep gradient as seen last week. But the reported deaths (even when 7 day averaged) are jumping all over place. Holidays seem to create this effect.
The English (as distinct from the rest of the UK) politicians seem to be unconcerned with the admissions rise. Why? And the highly placed medics and scientists seem to be lying low - possibly after having been leant upon by government ministers. I hear government cries of trust the people, not the scientists. On that vein, perhaps its better not to trust me. However I have been thinking about the sharp rise in admissions - and wondering whether or not there is evidence to suggest this is not a "real" rise in patients being admitted because they have a serious Covid infection, but rather these are patients being admitted for other medical reasons, but whom are also found to have Covid. I believe that such patients exist - but are they a significant number - could they be responsible for the significantly rising Covid admissions numbers? Accurate data of admissions, primarily due to Covid, is required.

GrahamPlatt
Lemon Quarter
Posts: 2059
Joined: November 4th, 2016, 9:40 am
Has thanked: 1032 times
Been thanked: 823 times

Re: Coronavirus - Numbers and Statistics

#469655

Postby GrahamPlatt » January 2nd, 2022, 8:19 am

https://coronavirus.data.gov.uk/

Not looking so “mild” now.

nmdhqbc
Lemon Slice
Posts: 634
Joined: March 22nd, 2017, 10:17 am
Has thanked: 112 times
Been thanked: 226 times

Re: Coronavirus - Numbers and Statistics

#469842

Postby nmdhqbc » January 3rd, 2022, 9:05 am

i've been ignoring all news / data on covid for a long while now so apologies if my question is covering old ground. thought i'd have a quick look at it all today and had a question...

just having a look at the data. from 25Dec the "Patients in hospital" seems to go up reasonably sharply but the "Patients in mechanical ventilation beds" seems to stay flat. is there any known reason for this? is there normally a time delay between these?

Hallucigenia
Lemon Quarter
Posts: 2616
Joined: November 5th, 2016, 3:03 am
Has thanked: 166 times
Been thanked: 1718 times

Re: Coronavirus - Numbers and Statistics

#469861

Postby Hallucigenia » January 3rd, 2022, 10:35 am

nmdhqbc wrote:just having a look at the data. from 25Dec the "Patients in hospital" seems to go up reasonably sharply but the "Patients in mechanical ventilation beds" seems to stay flat. is there any known reason for this? is there normally a time delay between these?


You'd expect a lag - if you look at a year ago, those on ventilation peaked 5 days after those in hospital.

But it's been clear for some weeks that the crisis we're facing this year is more about the sheer numbers in general & acute beds, rather than intensive care being overwhelmed like last year. Although 800 Covid cases in intensive care is still around 16% of total ICU capacity, and being infectious means that they take up disproportionately more of capacity due to the need to keep Covid cases away from "ordinary" intensive care patients.

Just generally - things should become clearer this week as the New Year "pulse" works through the system. Last year cases peaked on 4th Jan and admissions on the 12th. There's some encouraging signs that cases in London are starting to go down, but an element of that may just be people in London going back to the provinces for Christmas. It also doesn't help that some of the UK-wide reporting has not been ideal over Christmas - the England data has been pretty good, the other nations have been spotty but England accounts for 90% of the caseload so it's a reasonable proxy.

And whilst I'm here, a hat-tip to Pouria Hadjibagheri, one of the hidden heroes of the pandemic who's just moved on to other things after leading the team on the gov.uk dashboard since April 2020. The numbers are incredible - this blog talks a bit about it :
https://techcommunity.microsoft.com/t5/ ... -p/3036276
Last edited by Hallucigenia on January 3rd, 2022, 10:47 am, edited 1 time in total.

MrFoolish
Lemon Quarter
Posts: 2284
Joined: March 22nd, 2020, 7:27 pm
Has thanked: 553 times
Been thanked: 1115 times

Re: Coronavirus - Numbers and Statistics

#469862

Postby MrFoolish » January 3rd, 2022, 10:41 am

scotia wrote:However I have been thinking about the sharp rise in admissions - and wondering whether or not there is evidence to suggest this is not a "real" rise in patients being admitted because they have a serious Covid infection, but rather these are patients being admitted for other medical reasons, but whom are also found to have Covid. I believe that such patients exist - but are they a significant number - could they be responsible for the significantly rising Covid admissions numbers? Accurate data of admissions, primarily due to Covid, is required.


I don't think they've ever been clear about the reasons for admissions. Maybe it's buried in the stats somewhere, but they certainly don't emphasise it.

I find it most curious the BBC are not trawling the covid wards, dressed like a Salisbury decontamination team, showing us pictures of distressed patients and staff. They did it for the first wave but not now. Answers on a postcard please.

MickR
2 Lemon pips
Posts: 130
Joined: July 29th, 2019, 5:40 pm
Has thanked: 147 times
Been thanked: 31 times

Re: Coronavirus - Numbers and Statistics

#469864

Postby MickR » January 3rd, 2022, 10:47 am

there is a delay between patients admitted, patients on mechanical ventilators and then deaths within 28 days. I seem to remember there is around an 8 day delay between peak admissions and peak ventilator numbers.

The stats on ventilators are the ones I keep my eyes on most, as admissions covers all admissions into hospital with covid, not because of covid, so is picking up people going into hospital with ingrowing toe nails and testing positive on admission. Likewise, the deaths data doesn't define people dying of Covid. However, the ventilator stats give a really good indication of the number of people requiring high levels of care, because of covid

I'm sure the Gov have all of the finer details on deaths and admissions, and assume this is the info they use when deciding on any lockdown measures. Why they don't publish them is beyond me. I reckon its their public relations teams who aren't very good with numbers assume everyone else is the same

Mick

MrFoolish
Lemon Quarter
Posts: 2284
Joined: March 22nd, 2020, 7:27 pm
Has thanked: 553 times
Been thanked: 1115 times

Re: Coronavirus - Numbers and Statistics

#469867

Postby MrFoolish » January 3rd, 2022, 10:57 am

MickR wrote:I'm sure the Gov have all of the finer details on deaths and admissions, and assume this is the info they use when deciding on any lockdown measures. Why they don't publish them is beyond me. I reckon its their public relations teams who aren't very good with numbers assume everyone else is the same


It's because (and I've been saying this for some time) their main concern is staff absenteeism rather than severe disease from omicron. They've been running a "soft" containment policy, largely based on voluntary public compliance through a degree of social distancing. They need to keep us worried or people won't bother.


Return to “Coronavirus Discussions”

Who is online

Users browsing this forum: No registered users and 3 guests