<html><head></head>
<body>
<div>
download and apply
<a href="https://www.socialstyrelsen.se/globalassets/1-globalt/covid-19-statistik/statistik-relaterad-till-covid-19/antal-doda--per-lan-och-vecka-2015-v1-2020.xlsx">
antal-doda--per-lan-och-vecka-2015-v1-2020.xlsx
</a>
(<a href="https://www.socialstyrelsen.se/statistik-och-data/statistik/statistik-om-covid-19/statistik-relaterad-till-covid-19/">
[1]
</a>)
</div>
<input type="file" id="xlsxFileIn">
<button id="xlsxFilePraseBt">parse</button>
addDateUnknown:<input type="checkbox" id="addDateUnknownCb">
use4WeeksRollAvg:<input type="checkbox" id="use4wRollAvgCb"><br/>
<textarea id="chartsMdOut" style="width:90%;height:150px"></textarea>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<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>
xlsxFilePraseBt.onclick = async () => {
var files = xlsxFileIn.files;
if (!files.length) return;
var cfg = {
addDateUnknown: addDateUnknownCb.checked, use4wRollAvg: use4wRollAvgCb.checked,
chartYAxisTitle: "Deaths per week"
};
chartsMdOut.value = (await parseExcelAndRenderMd(files[0], cfg)).join("\n");
}
var parseExcelAndRenderMd = async (file, cfg) => {
var workbook = window.workbook = await readExcelWb(file);
workbook.SheetNames.forEach((sheetName) => {
console.log(sheetName);
});
var regionsAllData = parseStatistikenPerLanWb(workbook);
return formatChartsMd(regionsAllData, cfg);
}
var assert = console.assert;
var readExcelWb = async (file) => (
XLSX.read(
new Uint8Array(await file.arrayBuffer()),
{type: 'array'}
)
);
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 parseStatistikenPerLanWb = (wb) => {
assert(wb.SheetNames.length == 23, wb.SheetNames.length);
assert(wb.SheetNames[0] == "Om statistiken", wb.SheetNames[0]);
var regions = wb.SheetNames.slice(1);
return regions.map((region) => {
var sheet = wb.Sheets[region];
assert(cellText(sheet.A5) == "Vecka", sheet.A5);
assert(cellText(sheet.A59).startsWith("ej fastställt"), sheet.A59);
var years = [];
var yearColId = ["B","C","D","E","F","G", "H"];
for (var c = 0; c <= 6; ++c) {
var year = 2015 + c, cId = yearColId[c];
assert(cellText(sheet[cId+"5"]) == year, sheet[cId+"5"], year);
var weeks = [];
for (var r = 0; r < 53; ++r) {
var rId = 6 + r;
assert(cellNum(sheet["A"+rId]) == r+1, sheet["A"+rId], r+1);
var cell = sheet[cId + rId];
if (cell == null) break;
weeks.push(cellNum(cell));
}
assert(year == 2021 || weeks.length >= 52);
var dateUnknown = cellNum(sheet[cId+59]);
var errPerWeek = dateUnknown / weeks.length;
var unknownPerWeek = weeks.map((_,w)=>(
Math.round(errPerWeek*(w+1)) - Math.round(errPerWeek*(w))
));
years.push({
id: cellText(sheet[cId+"5"]),
weeks: weeks,
dateUnknown: dateUnknown,
unknownPerWeek: unknownPerWeek
});
}
return {
name: region,
years: years
};
});
};
var ad7d = (date, n) => (n=Number.isFinite(n)?n:1, new Date(date.getTime()+n*7*24*60*60*1000));
//var weekKey = (w0date, n) => (`w${n+1} ${ad7d(w0date, n).toJSON().slice(0,10)}`);
var weekKey = (w0date, n) => (ad7d(w0date, n).toJSON().slice(0,10).replace(/-/g,"/"));
var formatChartMd = (regionsAllDataArr, regionsNames, cfg) => {
var {addDateUnknown, use4wRollAvg, useStackedArea, chartColors, chartYAxisTitle} = cfg;
var addErr = !!addDateUnknown;
var useRollAvg = !!use4wRollAvg;
var chartType = useStackedArea ? "stackedarea" : "line";
var rollAvgTitleSufix = "(4-weeks rolling average)";
var chartYAxisTitle = useRollAvg ? `${chartYAxisTitle} ${rollAvgTitleSufix}` : chartYAxisTitle;
var regionsAllData = Object.fromEntries(regionsAllDataArr.map((r) => ([r.name, r])));
var yearW0 = [
new Date("2014-12-29"), new Date("2016-01-04"), new Date("2017-01-02"),
new Date("2018-01-01"), new Date("2018-12-31"), new Date("2019-12-30"),
new Date("2021-01-04")
];
var regionsData = regionsNames.map((regName)=>(regionsAllData[regName]));
var yearsWeeksK = [];
var yearsWeeksReg = regionsData.map(()=>([]));
for (var yId = 0; yId <= 6; ++yId) {
var year = 2015 + yId;
var regYear = regionsData[0].years[yId];
assert(regYear.id == year, regYear.id, year);
var weeksK = regYear.weeks.map((_,i)=>(weekKey(yearW0[yId], i+1)));
yearsWeeksK.push(weeksK);
}
var yearsWeeksReg = regionsData.map((region)=>(
region.years.flatMap((y)=>(y.weeks.map(
(v,i) => (!addErr? v : v + y.unknownPerWeek[i])
)))
));
if (useRollAvg) {
var rnd4 = (v) => (Math.round(v*10000)/10000);
var rollAvg = (arr, pos, win) => (
arr = arr.slice(Math.max(0, pos+1 - win), pos+1),
rnd4(arr.reduce((a, b) => (a + b), 0) / Math.min(win, Math.max(1, arr.length)))
);
yearsWeeksReg = yearsWeeksReg.map((regWeeks) => (
regWeeks.map((v, i) => (rollAvg(regWeeks,i,4)))
));
}
var regTitles = regionsData.map((region, regIndex)=>(
` | y${regIndex+1}Title=${region.name}`
));
var mdXValues = ` | x=${yearsWeeksK.flat().join()}`;
var regMdYValues = yearsWeeksReg.map((regWeeks, regIndex)=>(
` | y${regIndex+1}=${regWeeks.join()}`
));
var chartMd =
`
{{Graph:Chart
| width = 1000
| height = 250
| type = ${chartType}
| xType = date
| yAxisMin = 0
| yAxisTitle = ${chartYAxisTitle}
${chartColors &&
` | colors=${chartColors}` || ""
}
| legend=Regions
${[...regTitles, mdXValues, ...regMdYValues].join("\n")}
}}
`;
return chartMd;
};
var formatChartsMd = (regionsAllDataArr, cfg) => {
var {addDateUnknown, use4wRollAvg} = cfg;
console.log("addDateUnknown", !!addDateUnknown, "use4wRollAvg", !!use4wRollAvg);
var regionsNamesGr0 = [
["Stockholm","Västra Götaland","Skåne"],
["Gävleborg","Halland","Jönköping","Norrbotten","Uppsala","Värmland",
"Västernorrland","Örebro","Östergötland"],
["Dalarna","Kalmar","Södermanland","Västerbotten","Västmanland"],
["Blekinge","Jämtland","Kronoberg"],
["Gotland"]
];
var regionsNamesGr = [
["Skåne","Stockholm","Västra Götaland"],
["Södermanland","Västmanland"],
["Dalarna","Jönköping"],
["Gävleborg","Norrbotten"],
["Uppsala","Värmland"],
["Gotland","Jämtland","Västernorrland","Östergötland"],
["Blekinge","Västerbotten","Örebro"],
["Halland","Kalmar","Kronoberg"]
];
var overallChartMd = formatChartMd(
regionsAllDataArr, regionsNamesGr0.flat(), {...cfg, useStackedArea: true,
chartColors: "#ff7f0e,#ffbb78,#1f77b4,#aec7e8,#2ca02c,#98df8a,#d62728,#ff9896,#9467bd,#c5b0d5,#8c564b,#c49c94,#e377c2,#f7b6d2,#7f7f7f,#c7c7c7,#bcbd22,#dbdb8d,#17becf,#9edae5"
}
);
var chartsMd = [overallChartMd, ...regionsNamesGr.map((regionsNames) => (
formatChartMd(regionsAllDataArr, regionsNames, cfg)
))];
console.log(chartsMd.join("\n"))
return chartsMd;
};
</script>
</body></html>
|