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.
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.
Re: COVID-19
Posted: Mon Oct 05, 2020 3:57 pm
by El Pollo Diablo
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.
Re: COVID-19
Posted: Mon Oct 05, 2020 4:29 pm
by discovolante
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.
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.
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 (16 KiB) Viewed 2951 times
No real indication of change in slope, which works out as a doubling time of 12.8 days.
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.
Re: COVID-19
Posted: Mon Oct 05, 2020 6:22 pm
by shpalman
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.
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
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.
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.
Re: COVID-19
Posted: Mon Oct 05, 2020 6:58 pm
by AMS
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.
Re: COVID-19
Posted: Mon Oct 05, 2020 7:35 pm
by El Pollo Diablo
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.
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.
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.
Re: COVID-19
Posted: Mon Oct 05, 2020 9:21 pm
by Vertigowooyay
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.
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.
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.
Re: COVID-19
Posted: Tue Oct 06, 2020 9:36 am
by lpm
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.
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.
Re: COVID-19
Posted: Tue Oct 06, 2020 11:48 am
by mediocrity511
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.
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.
