Data talk:COVID-19 Sweden daily cases hospitalisations deaths.tab

Script to simplify data update (converts downloaded XLSX file into wiki JSON)

Works with Chrome. Doesn’t work with Firefox
Source code of script for converting local XLSX file from FOHM into wiki JSON (js+html)
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)[reply]
updated: 84.47.179.91 18:44, 2 April 2021 (UTC)[reply]