Data talk:COVID-19 Sweden daily cases hospitalisations deaths.tab
Jump to navigation
Jump to search
Script to simplify data update (converts downloaded XLSX file into wiki JSON)
[edit]Works with Chrome. Doesn’t work with Firefox
file fohm-to-wiki.html
<html> <head></head> <body> <div> download and apply <a href="https://www.arcgis.com/sharing/rest/content/items/b5e7488e117749c19881cce45db13f7e/data"> Folkhalsomyndigheten_Covid19.xlsx </a> (<a href="https://www.folkhalsomyndigheten.se/smittskydd-beredskap/utbrott/aktuella-utbrott/covid-19/statistik-och-analyser/bekraftade-fall-i-sverige/#:~:text=Data%20som%20statistiken%20ovan%20bygger%20p%C3%A5%20kan%20laddas%20ner%20h%C3%A4r"> [1] </a>) </div> <input type="file" id="xlsxFileIn"> <button id="xlsxFilePraseBt">parse</button><br/> <textarea id="wikiJsonOut" style="width:90%;height:150px"></textarea> <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"></script> <script> var assert = console.assert; var readExcelWb = async (file) => ( XLSX.read( new Uint8Array(await file.arrayBuffer()), {type: 'array'} ) ); var time1900_01_01Z = new Date("1900-01-01Z").getTime(); var dayMilis = (1000 * 60 * 60 * 24); var excelDateNumberToDate = (dateNum) => ( new Date(time1900_01_01Z + (dateNum-2) * dayMilis) ); var isoDayText = (date) => (date && date.toJSON() && date.toJSON().slice(0,10)); var ad1d = (date, n) => (n=Number.isFinite(n)?n:1, new Date(date.getTime()+n*dayMilis)); var cellText = (cell) => ((cell && cell.w) || XLSX.utils.format_cell(cell)) var cellNum = (cell) => { assert(cell && cell.t == "n", cell); return (parseFloat((cell && cell.v) || 0)); }; var cellDate = (cell) => { assert(cell && cell.t == "n" && !Number.isNaN(cell.v), cell); return (excelDateNumberToDate(parseInt((cell && cell.v) || 1))); }; var rnd2 = (v) => (Math.round(v*100)/100); var rnd4 = (v) => (Math.round(v*10000)/10000); var rollAvg = (arr, pos, win) => ( arr = arr.slice(Math.max(0, pos+1 - win), pos+1), rnd2(arr.reduce((a, b) => (a + b), 0) / Math.min(win, Math.max(1, arr.length))) ); var compressJson = (jsonText) => (jsonText.replace( /\[[^\[\]\{\}]*\]|\{[^\[\]\{\}]*\}/g, (part) => ( part.replace(/(?<=(^|[^a-zA-Z])[\,\{\[])\s+|\s+(?=[\]\}])/g, "")) ) ); xlsxFilePraseBt.onclick = async () => { var files = xlsxFileIn.files; if (!files.length) return; wikiJsonOut.value = await parseAndRenderWikiJson(files[0]); } var parseAndRenderWikiJson = async (file) => { var workbook = window.workbook = await readExcelWb(file); console.log("SheetNames", [...workbook.SheetNames]); var wbData = parseFohmWb(workbook); var wikiJson = fromatWikiDataJson(wbData); return compressJson(JSON.stringify(wikiJson , null, 2)); } var fromatWikiDataJson = (data) => ( { "license": "CC0-1.0", "description": { "en": "COVID-19 Swedish daily cases, hospitalisations, deaths" }, "sources": `[https://www.arcgis.com/sharing/rest/content/items/b5e7488e117749c19881cce45db13f7e/data Folkhalsomyndigheten_Covid19.xlsx] by [[w:Public Health Agency of Sweden|FOHM]], referenced from [https://www.folkhalsomyndigheten.se/smittskydd-beredskap/utbrott/aktuella-utbrott/covid-19/statistik-och-analyser/bekraftade-fall-i-sverige/#:~:text=Data%20som%20statistiken%20ovan%20bygger%20p%C3%A5%20kan%20laddas%20ner%20h%C3%A4r Bekräftade fall i Sverige – daglig uppdatering] via link <b>Data som statistiken ovan bygger på kan laddas ner här (Excel)</b><br/> cases: sheet "Antal per dag region", column "B"<br/> ICUs: sheet "Antal intensivvårdade per dag", column "B"<br/> deaths : sheet "Antal avlidna per dag", column "B"<br/>`.replace(/\s+/g, " "), "schema": { "fields": [ {"name": "days_back", "type": "number", "title": {"en": "Days Back"}}, {"name": "date", "type": "string", "title": {"en": "Date"}}, {"name": "cases", "type": "number", "title": {"en": "Cases"}}, {"name": "cases_7d_avg", "type": "number", "title": {"en": "Cases (7d avg)"}}, {"name": "cases_total", "type": "number", "title": {"en": "Cases Total"}}, {"name": "hospitalisations_icu", "type": "number", "title": {"en": "Hospitalisations - ICU"}}, {"name": "hospitalisations_icu_7d_avg", "type": "number", "title": {"en": "Hospitalisations - ICU (7d avg)"}}, {"name": "hospitalisations_icu_total", "type": "number", "title": {"en": "Hospitalisations - ICU Total"}}, {"name": "deaths", "type": "number", "title": {"en": "Deaths"}}, {"name": "deaths_7d_avg", "type": "number", "title": {"en": "Deaths (7d avg)"}}, {"name": "deaths_total", "type": "number", "title": {"en": "Deaths Total"}}, {"name": "deaths_per_1K_cases_total", "type": "number", "title": {"en": "Deaths Total / Cases Total * 1000"}} ] }, "data": data } ); var parseFohmWb = (wb) => { assert(wb, "wb", wb); // cases var shCases = wb.Sheets["Antal per dag region"]; assert(shCases, "sheet 'Antal per dag region' missing", workbook.SheetNames); var firstCaseRowDate = new Date("2020-02-04Z"); assert(cellText(shCases["A1"]) == "Statistikdatum", "A1 != 'Statistikdatum'", shCases["A1"]); assert(cellText(shCases["B1"]) == "Totalt_antal_fall", "B1 != 'Totalt_antal_fall'", shCases["B1"]); var {values: casesVals, totals: casesTotals, unknownDateValue: casesUnk} = parseFohmSh(shCases, firstCaseRowDate); assert(casesVals.length > 23, "casesVals.length", casesVals.length); assert(casesUnk == null, "casesUnk", casesUnk); // ICUs var shIcus = wb.Sheets["Antal intensivvårdade per dag"]; assert(shCases, "sheet 'Antal intensivvårdade per dag' missing", workbook.SheetNames); var firstIcuDate = new Date("2020-03-06Z"); assert(cellText(shIcus["A1"]) == "Datum_vårdstart", "A1 != 'Datum_vårdstart'", shIcus["A1"]); assert(cellText(shIcus["B1"]) == "Antal_intensivvårdade", "B1 != 'Antal_intensivvårdade'", shIcus["B1"]); var {values: icuVals, totals: icuTotals, unknownDateValue: icuUnk} = parseFohmSh(shIcus, firstIcuDate); assert(icuUnk == null, "icuUnk", icuUnk); // mortality var shMort = wb.Sheets["Antal avlidna per dag"]; assert(shMort, "sheet 'Antal avlidna per dag' missing", workbook.SheetNames); var firstMortRowDate = new Date("2020-03-11Z"); assert(cellText(shMort["A1"]) == "Datum_avliden", "A1 != 'Datum_avliden'", shMort["A1"]); assert(cellText(shMort["B1"]) == "Antal_avlidna", "B1 != 'Antal_avlidna'", shMort["B1"]); var {values: mortVals, totals: mortTotals, unknownDateValue: mortUnk} = parseFohmSh(shMort, firstMortRowDate); assert(mortUnk != null, "mortUnk", mortUnk); // align arrays var icusDateGap = 31; assert(ad1d(firstCaseRowDate, icusDateGap).toJSON() == firstIcuDate.toJSON(), ad1d(firstCaseRowDate, icusDateGap).toJSON(), "!=", firstIcuDate.toJSON() ) var icusGapFillArr = Array.from({length: icusDateGap}).fill(0); var mortDateGap = 36; assert(ad1d(firstCaseRowDate, mortDateGap).toJSON() == firstMortRowDate.toJSON(), ad1d(firstCaseRowDate, mortDateGap).toJSON(), "!=", firstMortRowDate.toJSON() ) var mortGapFillArr = Array.from({length: mortDateGap}).fill(0); icuVals = [...icusGapFillArr, ...icuVals]; icuTotals = [...icusGapFillArr, ...icuTotals]; mortVals = [...mortGapFillArr, ...mortVals]; mortTotals = [...mortGapFillArr, ...mortTotals]; assert(casesVals.length == icuVals.length, casesVals.length, "!=", icuVals.length); assert(casesVals.length == mortVals.length, casesVals.length, "!=", mortVals.length); // 7d avg var casesAvg7d = casesVals.map((v, i, arrVals) => (rollAvg(arrVals,i,7))); var icuAvg7d = icuVals.map((v, i, arrVals) => (rollAvg(arrVals,i,7))); var mortAvg7d = mortVals.map((v, i, arrVals) => (rollAvg(arrVals,i,7))); var mortRatioTotals = casesTotals.map((_, i) => (rnd4( mortTotals[i]/casesTotals[i]*1000 ))); // zip arrays var daysN = casesVals.length; var data = Array.from({length: daysN}).map((_, ri)=>([ daysN-1-ri, isoDayText(ad1d(firstCaseRowDate, ri)), casesVals[ri], casesAvg7d[ri], casesTotals[ri], icuVals[ri], icuAvg7d[ri], icuTotals[ri], mortVals[ri], mortAvg7d[ri], mortTotals[ri], mortRatioTotals[ri] ])); // remove range ("2020-02-05" ... "2020-02-24") data.splice(1, 20) // result console.log("data", data); return data; } var parseFohmSh = (sh, firstRowDate) => { assert(sh && firstRowDate, "sh", sh, "firstRowDate", firstRowDate); var vals = [], unknownDateVal = null; for (var i = 0; i < 2000; ++i) { var ri = i+2 var rowDateBox = sh["A"+ri], rowValBox = sh["B"+ri]; if (!rowDateBox) break; if ((cellText(rowDateBox) || "").toLowerCase().includes("uppgift saknas")) { unknownDateVal = cellNum(rowValBox); break; } assert(rowValBox, "rowValBox", rowValBox); var rowDate = cellDate(rowDateBox), rowVal = cellNum(rowValBox); var expectDate = ad1d(firstRowDate, i); assert(isoDayText(expectDate) == isoDayText(rowDate), "expectDate", expectDate, "rowDate", rowDate); vals.push(rowVal); } assert(vals.length > 0, "vals.length", vals.length); var totalVals = [], curTotal = 0; for (var curVal of vals) { totalVals.push(curTotal += curVal); } // add 'unknownDateVal' value to last item total if (unknownDateVal) totalVals[totalVals.length-1] += unknownDateVal; return {values: vals, totals: totalVals, unknownDateValue: unknownDateVal}; } </script> </body></html> |
created: 84.47.179.91 22:15, 27 February 2021 (UTC)
updated: 84.47.179.91 18:44, 2 April 2021 (UTC)
Charts examples
[edit]See or edit raw graph data.
See or edit source data.
See or edit raw graph data.
See or edit raw graph data.
See or edit source data.
See or edit raw graph data.
See or edit raw graph data.
See or edit source data.
See or edit raw graph data.
See or edit raw graph data.
updated: 84.47.179.91 19:11, 5 July 2021 (UTC)