Eurostat data statistics: total deaths by age group
The objective is to explore total number of deaths data all cases included provided by Eurostat website https://ec.europa.eu/eurostat
and to compare 2020 covid epidemic year with previous years.
​
Data is limited to European countries and the granularity is at country, week , sex , agegroup level.
​
The exercise introduces some new SQL functions like pivot , unpivot for wrangling and visualise data.
In order to load SQL functions evaluate “Main Code” cell from SQL_Operators post
https://community.wolfram.com/groups/-/m/t/2195893
Import data from Eurostat
https://ec.europa.eu/eurostat/web/products-datasets/-/demo_r_mwk_05
In[]:=
tbtotaldeaths=Import["https://ec.europa.eu/eurostat/estat-navtree-portlet-prod/BulkDownloadListing?file=data/demo_r_mwk_05.tsv.gz","TSV"];
The initial structure is in TSV format with week columns transposed “age,sex,unit,geo\\time”,”2021W99”,”2021W08”,”2021W07”,”2021W06”,”2021W05”,”2021W04...”
I do some data transformation to get to a normalise structure like “age, sex, geo, time, value”
tbtotaldeaths[[1]]//Take[#,10]&
Out[]=
{age,sex,unit,geo\time,2021W99,2021W08,2021W07,2021W06,2021W05,2021W04,2021W03,2021W02,2021W01}
(*getthelistoftransposedweekcolumns*)​​colsweeks=tbtotaldeaths[[1]]//Cases[#,x_String/;StringMatchQ[x,RegularExpression["^\\d{4}\\w*"]]"A"<>x]&;​​colsweeks//Take[#,10]&
Out[]=
{A2021W99,A2021W08,A2021W07,A2021W06,A2021W05,A2021W04,A2021W03,A2021W02,A2021W01,A2020W99}
(*maindatatransformationfromsourcestructure{"age,sex,unit,geo\\time","2021W99","2021W06","2021W05","2021W04"....}{age,sex,unit,geo,time,value}*)​​tbeurostatdeaths=​​tableSQL[tbtotaldeaths//Rest,Join[{"fixedcols"},colsweeks]]//​​unpivotSQL[#,Symbol/@colsweeks//Evaluate,{"week","value"}]&//(*unpivotweekcolumnstorowvalues*)​​whereSQL[#,!StringContainsQ[value//ToString,":"]]&//(*filteroutinvalidvalues*)​​rowMapSQL[#,{fixedcols},StringSplit[#,","]&]&//​​(*whereSQL[#,rowNumber<100000]&//*)​​rowTransformSQL[#,Association["age"fixedcols[[1]],​​ "sex"fixedcols[[2]],​​ "unit"fixedcols[[3]],​​ "geo"fixedcols[[4]],​​ "time"(DateObject[{week,{"A","Year","W","Week"}}]),(*convertsweekformattoDate.Thisisverytimeconsuming5minforaprox2Mrows*)​​ "value"ReplaceAll[value,x_StringStringReplace[x,RegularExpression["(\\d+)\\s*p"]"$1",​​ RegularExpression[":"]"0"]//ToExpression]​​ ]]&;//AbsoluteTiming
Out[]=
{493.,Null}
Explore data and display as dataset
tbeurostatdeaths//​​whereSQL[#,sex"T"&&geo"UK"&&age"TOTAL"]&//​​(*orderBySQL[#,{-AbsoluteTime[time]}]&//*)​​tableSQLAsDataset​​
Out[]=
age
sex
unit
geo
time
value
TOTAL
T
NR
UK
Mon 14 Dec
2020 00:0
0:00
14625
TOTAL
T
NR
UK
Mon 7 Dec
2020 00:0
0:00
13915
TOTAL
T
NR
UK
Mon 30 Nov
2020 00:0
0:00
13956
TOTAL
T
NR
UK
Mon 23 Nov
2020 00:0
0:00
14106
TOTAL
T
NR
UK
Mon 16 Nov
2020 00:0
0:00
14276
TOTAL
T
NR
UK
Mon 9 Nov
2020 00:0
0:00
13972
TOTAL
T
NR
UK
Mon 2 Nov
2020 00:0
0:00
13418
TOTAL
T
NR
UK
Mon 26 Oct
2020 00:0
0:00
12501
TOTAL
T
NR
UK
Mon 19 Oct
2020 00:0
0:00
12292
TOTAL
T
NR
UK
Mon 12 Oct
2020 00:0
0:00
11928
rows 1–10 of 312
(*helpertabletogetcountrynamesfromcodes*)​​tbrefgeo=tbeurostatdeaths//​​projectSQL[#,{geo}]&//(*distinctvalues*)​​rowTransformSQL[#,Association["countrycode"geo,​​ "country"Check[CountryData[geo,"Name"],geo]]]&;​​tbrefgeo//showTableSQL
CountryData
:"EL" is not a known entity, class, or tag for CountryData. Use CountryData​[​] for a list of entities.
Total Rows:37 Elapsed:1.×
-7
10
Out[]//TableForm=
countrycode
country
AD
Andorra
AL
Albania
AM
Armenia
AT
Austria
BE
Belgium
BG
Bulgaria
CH
Switzerland
CY
Cyprus
CZ
Czech Republic
DE
Germany
Prepare raw data for reporting (aggregation , timeseries)
(*finalresultstable*)​​tbts=tbeurostatdeaths//​​whereSQL[#,sex"T"&&AbsoluteTime[time]>(DateObject[{2014,1,1}]//AbsoluteTime)]&//​​groupBySQL[#,{geo,age}]&//​​summarySQL[#,Association["ts"TimeSeries[value,{time}]]]&//​​joinSQL[#,tbrefgeo,geo==countrycode]&//(*jointotbrefgeotogetthecountryname*)​​selectSQL[#,{country,age,ts}]&;//AbsoluteTiming​​​​tbts//​​showTableSQL
Out[]=
{15.2285,Null}
Total Rows:769 Elapsed:3.×
-7
10
Out[]//TableForm=
country
age
ts
Andorra
TOTAL
TimeSeries
Time: 06 Jan 2014 to 30 Dec 2019
Data points: 312

Albania
TOTAL
TimeSeries
Time: 29 Dec 2014 to 21 Sep 2020
Data points: 299

Armenia
TOTAL
TimeSeries
Time: 29 Dec 2014 to 21 Dec 2020
Data points: 312

Austria
TOTAL
TimeSeries
Time: 06 Jan 2014 to 01 Feb 2021
Data points: 368

Belgium
TOTAL
TimeSeries
Time: 06 Jan 2014 to 15 Feb 2021
Data points: 371

Bulgaria
TOTAL
TimeSeries
Time: 06 Jan 2014 to 15 Feb 2021
Data points: 371

Switzerland
TOTAL
TimeSeries
Time: 06 Jan 2014 to 15 Feb 2021
Data points: 371

Cyprus
TOTAL
TimeSeries
Time: 29 Dec 2014 to 01 Feb 2021
Data points: 318

Czech Republic
TOTAL
TimeSeries
Time: 06 Jan 2014 to 18 Jan 2021
Data points: 367

Germany
TOTAL
TimeSeries
Time: 04 Jan 2016 to 15 Feb 2021
Data points: 267

Plot by country /age group
In[]:=
(*customfunction*)​​tsTakeLargest[ts_TemporalData,n_Integer]:=Block[{min=Min[TakeLargest[ts//Values,n]]},​​ Cases[ts//Normal,{t_,v_}/;v≥min]//TimeSeries​​ ];​​plotcustomts[ts_TemporalData]:=Block[{tsmax=ts//tsTakeLargest[#,1]&//Cases[#//Normal,{t_,v_}Callout[{t,v},DateString[t,{"Year","MonthNameShort"}],Above]]&},​​ {ts,tsmax}//​​ DateListPlot[#,PlotRangeAll,​​ Joined{True,False},PlotStyle{Automatic,{Red,PointSize[0.02]}},PlotLegends{None,"Max"},​​ ImageSizeMedium]&​​ ];
Build a small interactive app using manipulate
Multiple selections on country/agegroup
Prepare raw data for reporting on multi year plots (aggregate by year , geo, age)
Plot by country/ age group with multi lines for years and one year span on time axes
I define a criteria True/False based on wether the max deaths is before year 2020 for given age group.
Explore the age groups more affected by 2020 covid epidemic