COVID-19

Covid-19 discussion, bring your own statistics
Post Reply
User avatar
lpm
After Pie
Posts: 1820
Joined: Mon Nov 11, 2019 1:05 pm
Location: INJECT HIM WITH BLEACH

Re: COVID-19

Post by lpm » Mon Oct 05, 2020 3:27 pm

France and Spain are currently pretty flat at about 11,000 per day. A solid 10 days or more without any growth and in fact a slight decline in Spain.

It had looked like the UK was levelling off at the 6,000 to 7,000 mark for offiical confirmed cases. Obviously stable at that level is bad, but stable is much nicer word than rising.

In fact the last few days for UK have been in the 10,000 to 12,000 per day area. The doubling time has continued to be about 11-12 days. No evidence at all of any significant deviation from that. We're not going to get to the 50,000 per day level during October at the current pace, but we'll be there in first few days of November.
I'll miss him after he's died in the pandemic

User avatar
Bird on a Fire
Stummy Beige
Posts: 3744
Joined: Fri Oct 11, 2019 5:05 pm
Location: nadir of brie

Re: COVID-19

Post by Bird on a Fire » Mon Oct 05, 2020 3:46 pm

Grumble wrote:
Mon Oct 05, 2020 3:19 pm
El Pollo Diablo wrote:
Mon Oct 05, 2020 3:14 pm
It was excel, it was Excel 2003, it was 65,536 rows, and it was a bunch of dickheads.

https://news.sky.com/story/coronavirus- ... t-12090904
Wow. That’s a well written piece but the story itself is shocking.
It all just seems so unnecessary.

PHE and the Pillar 1 labs were already using a decent database program.

The Pillar 2 labs were submitting data as CSV, which can handle files of any size.

But for some reason, instead of importing the Pillar 2 data directly from CSV they ran it through an Excel template, using an old version of Excel, which truncated some of the data without issuing a warning.

The LibreOffice suggestion might not be so silly - if they'd been able to upgrade to the latest version of their spreadsheet program free of charge this would never have happened.
Ceterum autem censeo humanitatem esse delendam

#ShowYourStripes

User avatar
El Pollo Diablo
Dorkwood
Posts: 1377
Joined: Wed Sep 25, 2019 4:41 pm
Location: FBPE

Re: COVID-19

Post by El Pollo Diablo » Mon Oct 05, 2020 3:57 pm

The government has given £12bn of budget to the track and trace system heretofore. Make no mistake, they had the money to upgrade to a version of Excel that didn't expire in 2009.
Mike Patton wrote:"You overdo it sometimes. There I am, peeing on Axl Rose’s teleprompter." He looks rueful: "I didn’t really have to do that."

User avatar
discovolante
After Pie
Posts: 1853
Joined: Fri Oct 11, 2019 5:10 pm

Re: COVID-19

Post by discovolante » Mon Oct 05, 2020 4:29 pm

I shouldn't be at all shocked by this, but somehow I am. I mean...I don't know much about what the different versions of Excel can and can't do, but I know damn well that if you are sending or receiving data from one place to another you need to be able to make sure that it can be sent/received properly. Depressing.
don't get any big ideas, they're not gonna happen

User avatar
Sciolus
Fuzzable
Posts: 337
Joined: Mon Nov 11, 2019 6:42 pm

Re: COVID-19

Post by Sciolus » Mon Oct 05, 2020 6:00 pm

El Pollo Diablo wrote:
Mon Oct 05, 2020 3:57 pm
The government has given £12bn of budget to the track and trace system heretofore. Make no mistake, they had the money to upgrade to a version of Excel that didn't expire in 2009.
But no-one in the last 13 years thought PHE was important enough to buy them a decent version of Excel.

Excel is an exceptionally good data processing tool, providing you use it properly. There is huge benefit in having the data and the code for processing it in a single file, and having the processing code run automagically whenever the data changes. It makes traceability really easy, for one thing (this data came from this input spreadsheet and was processed using these formulas and gave these results). Whether it was suitable for the amount of data and the type of processing here, I don't know.

The big WTF for me is swapping data between Excel and CSV formats, especially data with dates. That is asking for data corruption, because someone is bound to have them set to mm-dd-yyyy.

User avatar
Sciolus
Fuzzable
Posts: 337
Joined: Mon Nov 11, 2019 6:42 pm

Re: COVID-19

Post by Sciolus » Mon Oct 05, 2020 6:01 pm

Bird on a Fire wrote:
Mon Oct 05, 2020 3:46 pm
PHE and the Pillar 1 labs were already using a decent database program.
Which?

KAJ
Buzzberry
Posts: 40
Joined: Thu Nov 14, 2019 5:05 pm

Re: COVID-19

Post by KAJ » Mon Oct 05, 2020 6:03 pm

lpm wrote:
Mon Oct 05, 2020 3:27 pm
In fact the last few days for UK have been in the 10,000 to 12,000 per day area. The doubling time has continued to be about 11-12 days. No evidence at all of any significant deviation from that. We're not going to get to the 50,000 per day level during October at the current pace, but we'll be there in first few days of November.
Specimen date is more meaningful than reporting date; the latter includes a variable (!) delay. I've been fitting a very simple model
ln(cases) = rectilinear v. date + weekday factor,
  • omit the latest 4 days from calibration (because not all specimens will have been reported)
  • weekday factor estimated over preceding 84 days as ln(cases/7 day average)
  • regression estimated over preceding 35 days
On a linear cases scale it looks like this (pale bars omitted from calibration):
chart.png
chart.png (16 KiB) Viewed 490 times
No real indication of change in slope, which works out as a doubling time of 12.8 days.
discovolante wrote:
Mon Oct 05, 2020 4:29 pm
I shouldn't be at all shocked by this, but somehow I am. I mean...I don't know much about what the different versions of Excel can and can't do, but I know damn well that if you are sending or receiving data from one place to another you need to be able to make sure that it can be sent/received properly. Depressing.
This in spades. Excel isn't the optimum tool for this kind of job, but Excel didn't cause the errors. They were caused by failing to include validation in the data collection system. I'm afraid I'm not very surprised. In the past I've worked with PHE and found a silo mentality, excellent microbiologists and epidemiologists did their own computing and statistics rather than consult specialists. who were available.

User avatar
shpalman
After Pie
Posts: 1962
Joined: Mon Nov 11, 2019 12:53 pm
Location: One step beyond

Re: COVID-19

Post by shpalman » Mon Oct 05, 2020 6:22 pm

Well, after those anomalous numbers of 12,871 new cases on Saturday and 22,961 new cases yesterday, it's good to see the numbers have gone back to normal with only 12,593 today wait what.
molto tricky

KAJ
Buzzberry
Posts: 40
Joined: Thu Nov 14, 2019 5:05 pm

Re: COVID-19

Post by KAJ » Mon Oct 05, 2020 6:25 pm

Sciolus wrote:
Mon Oct 05, 2020 6:00 pm
Excel is an exceptionally good data processing tool, providing you use it properly. There is huge benefit in having the data and the code for processing it in a single file, and having the processing code run automagically whenever the data changes. It makes traceability really easy, for one thing (this data came from this input spreadsheet and was processed using these formulas and gave these results). Whether it was suitable for the amount of data and the type of processing here, I don't know.
Hmmm. Excel is a good spreadsheet. Spreadsheets in general are not straightforward to validate. Anything like a complicated spreadsheet is impractical to satisfactorily validate without tools; it isn't like program code which can be worked through line by line - it isn't practical to check every cell. When I seriously used spreadsheets I had mapping tools which produced a visual formula map and listed:
  • each unique formula with each range where it occurred and each dependent range
  • each data validation with each range where it occurred
  • each conditional format with each range where it occurred
  • each named range.
This allowed the author to document the design and a non-author (important!) to be sure that they had looked at everything important.

I (and my immediate colleagues) realised that systematic, formal, software validation was essential to reliable results, and that this applied to spreadsheets as well as other software (we had similarly stringent procedures for R code). Many others weren't as obsessive :shock:

User avatar
sTeamTraen
Catbabel
Posts: 901
Joined: Mon Nov 11, 2019 4:24 pm

Re: COVID-19

Post by sTeamTraen » Mon Oct 05, 2020 6:49 pm

Sciolus wrote:
Mon Oct 05, 2020 6:00 pm
The big WTF for me is swapping data between Excel and CSV formats, especially data with dates. That is asking for data corruption, because someone is bound to have them set to mm-dd-yyyy.
I wouldn't be too worried about that in the scheme of things. If you import with the wrong MM/DD order, 60% of dates will fail ("month" > 12) and you ought to see carnage in the sheet straight away.
Something something hammer something something nail

KAJ
Buzzberry
Posts: 40
Joined: Thu Nov 14, 2019 5:05 pm

Re: COVID-19

Post by KAJ » Mon Oct 05, 2020 6:55 pm

sTeamTraen wrote:
Mon Oct 05, 2020 6:49 pm
Sciolus wrote:
Mon Oct 05, 2020 6:00 pm
The big WTF for me is swapping data between Excel and CSV formats, especially data with dates. That is asking for data corruption, because someone is bound to have them set to mm-dd-yyyy.
I wouldn't be too worried about that in the scheme of things. If you import with the wrong MM/DD order, 60% of dates will fail ("month" > 12) and you ought to see carnage in the sheet straight away.
And beating my dead hobby horse again, decent software (including spreadsheet!) design should include input data validation. This episode is an exemplar of how inadequate software standards cause real problems.

AMS
Fuzzable
Posts: 346
Joined: Mon Nov 11, 2019 11:14 pm

Re: COVID-19

Post by AMS » Mon Oct 05, 2020 6:58 pm

So if the data comes in from Pillar 2 as a .csv why does it need to pass through Excel to get to the PHE database? It suggests there's a ctrl-A ctrl-C ctrl-V step in there somewhere.

User avatar
El Pollo Diablo
Dorkwood
Posts: 1377
Joined: Wed Sep 25, 2019 4:41 pm
Location: FBPE

Re: COVID-19

Post by El Pollo Diablo » Mon Oct 05, 2020 7:35 pm

I mean, it's a failure on several levels.

It's a failure of data collection - not realising that there would at some point, probably, be a limit on the file size. This stuff has been known about since jesus walked the earth. Not realising that they shouldn't f.cking be f.cking using Excel f.cking 2003, and they should spend 0.0000017% of their budget on a newer version (fair estimate). Not realising that probably they should think of a more robust, longer-term way of doing it. Anything, really. Anything at all - Python, R, macros, writing it on a f.cking whiteboard. Anything.

It's a failure of data organisation - they shouldn't have been doing this in Excel at all. There are f.cking myriad ways they could've done this better.

It's a failure of data validation, as KAJ has said. This stuff should've been checked on entry. There should've been some sort of error check or something.

Mostly, though, it's a failure of management, to make sure the entire end to end process is frankly f.cking sh.t hot, because this stuff is too goddamned important to f.ck up on. People don't (usually) die if I get an Excel process wrong, but they might do here.
Mike Patton wrote:"You overdo it sometimes. There I am, peeing on Axl Rose’s teleprompter." He looks rueful: "I didn’t really have to do that."

User avatar
Bird on a Fire
Stummy Beige
Posts: 3744
Joined: Fri Oct 11, 2019 5:05 pm
Location: nadir of brie

Re: COVID-19

Post by Bird on a Fire » Mon Oct 05, 2020 7:42 pm

AMS wrote:
Mon Oct 05, 2020 6:58 pm
So if the data comes in from Pillar 2 as a .csv why does it need to pass through Excel to get to the PHE database? It suggests there's a ctrl-A ctrl-C ctrl-V step in there somewhere.
That what I'd guess - rather than writing the Pillar 2 import code from scratch, it sounds like they're recycling something else. They really should have had the time and expertise to do better than that, but I don't know how much of their recent funding boost has gone on in-house expertise / consultant wonks, and how much is going straight to Pillar 2 providers etc.
Ceterum autem censeo humanitatem esse delendam

#ShowYourStripes

User avatar
Woodchopper
After Pie
Posts: 1838
Joined: Sat Oct 12, 2019 9:05 am

Re: COVID-19

Post by Woodchopper » Mon Oct 05, 2020 8:49 pm

Bird on a Fire wrote:
Mon Oct 05, 2020 7:42 pm
AMS wrote:
Mon Oct 05, 2020 6:58 pm
So if the data comes in from Pillar 2 as a .csv why does it need to pass through Excel to get to the PHE database? It suggests there's a ctrl-A ctrl-C ctrl-V step in there somewhere.
That what I'd guess - rather than writing the Pillar 2 import code from scratch, it sounds like they're recycling something else. They really should have had the time and expertise to do better than that, but I don't know how much of their recent funding boost has gone on in-house expertise / consultant wonks, and how much is going straight to Pillar 2 providers etc.
I suspect that back in March it was a workaround which was supposed to have been replaced by a better method. Except it wasn’t and no one minded because everything seemed to be working.

As mentioned, if so that would be a failure of management.

User avatar
Vertigowooyay
Clardic Fug
Posts: 232
Joined: Mon Nov 11, 2019 1:25 pm

Re: COVID-19

Post by Vertigowooyay » Mon Oct 05, 2020 9:21 pm

The one danger we’ve all missed is that Dido Harding fails upwards at such a speed that by the end of the month she’ll be our Supreme Galactic Overlord.
Calm yourself Doctor NotTheNineO’ClockNews. We’re men of science. We fear no worldly terrors.

User avatar
Little waster
Catbabel
Posts: 866
Joined: Tue Nov 12, 2019 12:35 am
Location: About 1 inch behind my eyes

Re: COVID-19

Post by Little waster » Mon Oct 05, 2020 9:51 pm

Vertigowooyay wrote:
Mon Oct 05, 2020 9:21 pm
The one danger we’ve all missed is that Dido Harding fails upwards at such a speed that by the end of the month she’ll be our Supreme Galactic Overlord.
That's just silly talk ... Chris Grayling would get there first.

Grayling, yesterday.

Spoiler:
People who use Godwin's Law are worse than Hitler.

User avatar
Vertigowooyay
Clardic Fug
Posts: 232
Joined: Mon Nov 11, 2019 1:25 pm

Re: COVID-19

Post by Vertigowooyay » Tue Oct 06, 2020 9:09 am

Very much for f.ck's sake.
covid bastards.jpg
covid bastards.jpg (211.48 KiB) Viewed 262 times
Calm yourself Doctor NotTheNineO’ClockNews. We’re men of science. We fear no worldly terrors.

User avatar
sTeamTraen
Catbabel
Posts: 901
Joined: Mon Nov 11, 2019 4:24 pm

Re: COVID-19

Post by sTeamTraen » Tue Oct 06, 2020 9:32 am

Vertigowooyay wrote:
Tue Oct 06, 2020 9:09 am
Very much for f.ck's sake.
But on the basis that lockdowns are necessary for public health, doesn't this mean that more people in Tory-voting areas will get sick (or that fewer won't get sick)?

That's presumably how it would be spun by the tabloids if there was a Labour government ("Class War: Evil Health Secretary protects Northern voter base, callously refuses to implement measures to protect people in the shires").

So ISTM this can be interpreted however the reader wants.
Something something hammer something something nail

User avatar
lpm
After Pie
Posts: 1820
Joined: Mon Nov 11, 2019 1:05 pm
Location: INJECT HIM WITH BLEACH

Re: COVID-19

Post by lpm » Tue Oct 06, 2020 9:36 am

I don't see anything wrong with letting the free market do its thing. Like rich people paying poor people to queue up for a couple of days for the new iPhone - both benefit from their choices. Rich people should be allowed to pay poor people to do their quarantining for them.
I'll miss him after he's died in the pandemic

User avatar
TimW
Stargoon
Posts: 135
Joined: Mon Nov 11, 2019 4:27 pm

Re: COVID-19

Post by TimW » Tue Oct 06, 2020 10:36 am

sTeamTraen wrote:
Tue Oct 06, 2020 9:32 am
But on the basis that lockdowns are necessary for public health, doesn't this mean that more people in Tory-voting areas will get sick (or that fewer won't get sick)?
I guess so. But not all people in wealthy Tory-voting areas are wealthy Tories. We can probably guess who's going to get sick and who's going to benefit from the freedom.

User avatar
mediocrity511
Fuzzable
Posts: 255
Joined: Mon Nov 11, 2019 2:16 pm

Re: COVID-19

Post by mediocrity511 » Tue Oct 06, 2020 11:48 am

Is Nottingham going to be the first place to get locked down without being an area of concern first? We seem to have had a massive, massive spike here and have gone from below average to rates higher than a lot of locked down areas.

User avatar
RoMo
Bank Butt
Posts: 23
Joined: Mon Nov 11, 2019 1:57 pm

Re: COVID-19

Post by RoMo » Tue Oct 06, 2020 1:02 pm

Yep, Notts has gone shooting up:

Image

User avatar
sTeamTraen
Catbabel
Posts: 901
Joined: Mon Nov 11, 2019 4:24 pm

Re: COVID-19

Post by sTeamTraen » Tue Oct 06, 2020 2:03 pm

TimW wrote:
Tue Oct 06, 2020 10:36 am
sTeamTraen wrote:
Tue Oct 06, 2020 9:32 am
But on the basis that lockdowns are necessary for public health, doesn't this mean that more people in Tory-voting areas will get sick (or that fewer won't get sick)?
I guess so. But not all people in wealthy Tory-voting areas are wealthy Tories. We can probably guess who's going to get sick and who's going to benefit from the freedom.
But that's just the ecological fallacy. The same holds among the professional v the working classes in Wigan too, but it doesn't tell us anything about whether "posh parts of the country are getting off lightly" or whether any part of the country should "get off lightly".

There are a million reasons to criticise the UK government's response, but the idea (which, as far as I can tell, has been inferred by the author of the piece on the basis that the major lockdowns are in the North and Midlands) that "toffs are being spared the privations of the poor" makes no logical sense here.
Something something hammer something something nail

KAJ
Buzzberry
Posts: 40
Joined: Thu Nov 14, 2019 5:05 pm

Re: COVID-19

Post by KAJ » Tue Oct 06, 2020 3:28 pm

RoMo wrote:
Tue Oct 06, 2020 1:02 pm
Yep, Notts has gone shooting up:

Image
Taking data from the government dashboard link and plotting by specimen date looks bad, but not quite as bad as that.
Screenshot 2020-10-06 at 16.26.30.png
Screenshot 2020-10-06 at 16.26.30.png (72.4 KiB) Viewed 111 times

Post Reply