Daily numbers, graphs, analysis thread 21

[BigChocFrenzy]

Welcome to thread 21 of the daily updates

Resource links:

Uk dashboard deaths, cases, hospitals, tests - 4 nations, English regions & LAs
R estimates UK & English regions
Imperial UK weekly LAs, cases / 100k, table, map, hotspots
UK School statistics Attendance
Modelling real number of UK infections February to date
NHS England Hospital activity
MSAO Map of English cases
Cases Tracker England Local Government
ONS MSAO Map English deaths
CovidMessenger live update by council district in England
Scot gov Daily data
Scotland TravellingTabby LAs, care homes, hospitals, tests, t&t
PH Wales LAs, tests, ONS deaths
NI Dashboard
Zoe Uk data
UK govt pressers Slides & data
ICNRC Intensive Care National Audit & Research reports
NHS t&t England & UK testing Weekly stats
PHE Surveillance reports & LA Local Watchlist Maps by LSOA
ONS England infection surveillance report each Friday
Datasets for ONS surveillance reports
ONS Roundup deaths, infections & economic reports
ECDC rolling 14-day incidence EEA & UK
Worldometer UK page
Our World in Data GB test positivity etc, DIY country graphs
FT DIY graphs compare deaths, cases, raw / million pop
Alama Personal COVID risk assessment
Local Mobility Reports for countries
UK Highstreet Tracker for cities & large towns Footfall, spend index, workers, visitors, economic recovery

⏭ Our STUDIES Corner ⏮

We welcome factual, data driven and analytical contributions
Please try to keep discussion focused on these
📈 📉 📊 👍

@Frazzled2207

apologies for the daily fail quote but if this really is the problem then it's quite staggeringly basic

"The problem was caused by an Excel spreadsheet reaching its maximum file size, which stopped new names being added in an automated process.
The files have now been split into smaller multiple files to prevent the issue happening again"

That really is basic stuff. Who the hell designed this system?

Sorry I should have added the job title

NHS Test and Trace - Grade 6 - Data Roles Closing Date Extended to 14th October at 23:55

@littleowl1 - tagging littleowl so she can have a look

www.civilservicejobs.service.gov.uk/csr/index.cgi?SID=cGFnZWNsYXNzPUpvYnMmcGFnZWFjdGlvbj12aWV3dmFjYnlqb2JsaXN0JnNlYXJjaF9zbGljZV9jdXJyZW50PTQmam9ibGlzdF92aWV3X3ZhYz0xNjg2ODM1Jm93bmVydHlwZT1mYWlyJm93bmVyPTUwNzAwMDAmY3NvdXJjZT1jc3FzZWFyY2gmdXNlcnNlYXJjaGNvbnRleHQ9MTEwNDE4MzY3JnJlcXNpZz0xNjAxODkwNDM2LThmYTVkYjY4Njg5MzllMjBhNzU2MDdmY2JkYTQyNmE4NTY0NzU2Nzg=

Wave

Files to big is probably code for something like too many rows (it could also be too many columns or cells with too many characters, but these would not easily be fixed by splitting a file into 2) .

Normally if a human is manually doing this, you'd expect to see an error message on your screen about not being able to load all the data. If someone however has created a semi-automated process in a hurry, they may not have done anything to capture any errors, and instead of giving a message 'couldn't load file' it basically produced output containing no rows, or partial rows.

The pre-processing in Excel is probably because the many lab providers are producing test results in slightly different formats and they need to beat them into a shape that matches the government database. If you were doing this in a sensible manner, this kind of batch load and transformation is handled by a piece of custom software that goes through the file and any rows that it doesn't understand are put into a 'bad' file for examination, and an alert raised. Excel would not be my tool of choice for this.

I'm guessing the data would something like a row per test processed, containing data about sample date, process date and the testee. The level of detail about the testee could be simple (name, DOB, address) or could be much more detailed.

Oh and a unique reference for the test so you don't accidentally load the same test twice...

I love the first sentence in that ad
"Sometimes the opportunity comes along to be a part of something really special."

lol.
Intriguingly they seem to be civil service posts not outsourced to serco.

Maybe the serco people have failed so badly they decided they needed to bring it in house. It is a bad sign they are advertising for this now whichever way you look at it.

Really, who could have guessed that outsourcing to for profit companies was a bad idea? (answer - everyone, literally everyone not profiting said this)

kittykarate thanks for that.

@Timeforanotherusername

Wave

It is sounding now as if the Excel spreadsheet ran out of rows.

Just to add to the list of potential things that went bust in the night....
File too big could actually mean that the physical size of the file (not the number of rows or cells) was too big to even start the ingestion process.

Just for thought: "Total number of rows and columns on a worksheet
1,048,576 rows by 16,384 columns"

They lost 22k positive tests. The testing capacity nation-wide is 300k. Assume that there is 1 excel sheet (wrong, but bear with me) for everything. That means that with headings ALL test cases, even if they are swabbed 3 times and are 3 separate rows is about 900k rows.
(assuming that patient is in row and attributes of that tes are in the cols, obv.)

In layman's terms: even if all daily data from all of the UK was in only one huge excel file, the capacity of the excel file would be enough.
This limits the potential fault points to (broadly): 1. input point - not enough storage capacity to move the file that needs to be ingested 2. ingestion point can't handle xxx file size

Both are basic things that should be thought of automatically. They are on every pre-made risk register out there, if nothing else

In layman's terms: even if all daily data from all of the UK was in only one huge excel file, the capacity of the excel file would be enough.

We are assuming that the excel files produced by the labs are just for one day though... maybe they have a really shit process where it just keeps adding days as a new worksheet.

To be honest, none of the detail really matters, it just shows a badly designed and not thought out system.

on a more positive note, tweet from Tim Spector regarding the covid symptom study.

Our CSS U.K. infection survey based on swab tests is holding steady for last few days in England at around 15000 cases per day and looks more reliable than govt data on confirmed cases - see app for full details . Keep logging and sharing

➡️ NEW thread: 📉 📊

https://www.mumsnet.com/Talk/coronavirus/4042501-Daily-numbers-graphs-analysis-thread-22?watched=1

@TheSunIsStillShining

just flagging that we are closing in on 1000 posts...

... Thank you

Yes, details are irrelevant, but I cannot fathom how this could have happened. These are such basics of software design that even a novice can do it from templates.

I wonder if there will be a HoC enquiry about this whole shit and will they have to pay back the money they got.

@Baaaahhhhh

I personally will not engage with track and trace on the basis of how much data is being passed into private hands through it

I assume you engage with healthcare though? No difference. Not engaging with track and trace is not helpful to you or anyone else.

I try not to actually.

I’m miles behind on this thread so apologies if this has already been pointed out. I’ve just Seen Sky News headline article with the revised cases per 100k in the body of the article. Please be aware that these are understated for the date period - they are for the seven days dates up to Oct 1st and all these cases aren’t reported yet. So the figures per 100k will be higher than published

➡️ NEW thread: 📉 📊

https://www.mumsnet.com/Talk/coronavirus/4042501-Daily-numbers-graphs-analysis-thread-22?watched=1

I do wonder who spotted that there were so many entries missing and what their face looked like when the scale of it dawned on them

This might be a stupid question but I'm going to ask anyway. How do they work out the rate per 100,000? Is it confirmed cases in the last 7 days or more complicated than that?

It’s positive cases by specimen (test) date for the last 7 complete days. The most recent few days are disregarded as the results won’t all be known yet. So today’s rates will be based on the 7 days to 2 October

Thanks ceeveebee. I've been puzzling about that for days.