← Home

Investigating the best way to get DEATH numbers for Italy

NO NEED TO UPDATE THIS NOTEBOOK, as it is just for investigation purposes

AIM: for each AGE and YEAR_OF_OBSERVATION, we want the most reliable estimate of the number of deaths in Italy.

Live retrieval through legacy istatapi is disabled by the shared guard. The saved outputs are retained; migrate retrieval to the bounded istat_source client before enabling new queries.

In [1]:
import sys
from pathlib import Path
sys.path.insert(0, str(Path("../scripts").resolve()))
from istat_source import install_istat_guard
install_istat_guard()  # legacy ISTAT requests are disabled; use the cached maintained runner

import numpy as np
import pandas as pd
import requests
import matplotlib
import plotly.express as px
import plotly.graph_objects as go
import plotly.io as pio
import warnings 
from istatapi import discovery, retrieval

pio.renderers.default = 'vscode+notebook'
warnings.filterwarnings('ignore')
requests.urllib3.disable_warnings() # avoid "InsecureRequestWarning: Unverified HTTPS request is being made to host 'sdmx.istat.it'. Adding certificate verification is strongly advised"

def get_colors(n, cmap_name="rainbow"):
    """Get colors for px colors_discrete argument, given the number of colors needed, n."""
    cmap = matplotlib.colormaps[cmap_name]
    colors = [cmap(i) for i in np.linspace(0, 1, n)]  # Generate colors
    colors_str = [f"rgba({int(color[0]*250)}, {int(color[1]*250)}, {int(color[2]*250)}, 1.0)" for color in colors]
    return colors_str

Compare deaths data from DCIS_DECESSI and DCIS_MORTALITA1

Remember that for population count I used:

  • DCIS_RICPOPRES2011 (2011-2018), TIPO_INDDEM="JAN" (also "DEATH" available), by age
  • DCIS_POPRES1 (2019-latest), TIPO_INDDEM="JAN" (only data available, i.e., "DEATH" is NOT available), by age

So, I don't have recent data about DEATHS.

Data about deaths:

  • DCIS_DECESSI - (2011-latest), TIPO_DATO="DEATH", only age groups (5years)
  • DCIS_POPORESBIL1 - (2011-2018), TIPO_INDDEM="DEATH", by age
  • DCIS_MORTALITA1 - (1974-latest), FUNZ_BIO="DEATHS" & FUNZ_BIO="PROBDEATH", by age - Methodology Document

There are 3 options:

  • take DCIS_DECESSI but I will know only age groups and I will miss 2004-2010 data (I will call this dfdg).
  • take DCIS_MORTALITA1\DEATHS * TOT_DEATHS(y) / 100'000 (I will call this dfd2 and dfdg2 for grouped ages) - THIS IS WRONG, beacause the 100'000 sample is some "stationary" population, not the total population.
  • take DCIS_MORTALITA1\PROBDEATH * POPULATION(a, y) / 1'000 (I will call this dfd3 and dfdg3 for grouped ages)

I will make:

  • Comparison #1 between dfdg and dfd2 (grouped ages)
  • Comparison #2 between dfdg and dfd3 (grouped ages)

And show that the first comparison is wrong but the second is coherent, but not identical, because PROBDEATH is computed with some averaging and smoothing.

In [2]:
# Load the popolation data and split by age group coherent with DCIS_DECESSI

dfp=pd.read_csv("../data/pop_by_age_year.csv", index_col=0).rename(columns=int)
dfp_ag = dfp.copy()
dfp_ag['age_group'] = pd.cut(
    dfp.index,
    bins=[-1, 4, 9, 14, 19, 24, 29, 34, 39, 44, 49, 54, 59, 64, 69, 74, 79, 84, 89, 94, 100],
    labels=[ # Use the conventional labels from ISTAT
        'Y_UN4', 'Y5-9', 'Y10-14', 'Y15-19', 'Y20-24', 'Y25-29', 'Y30-34', 'Y35-39', 'Y40-44', 'Y45-49',
        'Y50-54', 'Y55-59', 'Y60-64', 'Y65-69', 'Y70-74', 'Y75-79', 'Y80-84', 'Y85-89', 'Y90-94', 'Y_GE95'
    ]
)
age_group_labels = {k: v for k, v in zip( # Rename to more conventional labels for sorting
    ['Y_UN4', 'Y5-9', 'Y10-14', 'Y15-19', 'Y20-24', 'Y25-29', 'Y30-34', 'Y35-39', 'Y40-44', 'Y45-49',
     'Y50-54', 'Y55-59', 'Y60-64', 'Y65-69', 'Y70-74', 'Y75-79', 'Y80-84', 'Y85-89', 'Y90-94', 'Y_GE95'],
    ['Y00-04', 'Y05-09', 'Y10-14', 'Y15-19', 'Y20-24', 'Y25-29', 'Y30-34', 'Y35-39', 'Y40-44', 'Y45-49',
     'Y50-54', 'Y55-59', 'Y60-64', 'Y65-69', 'Y70-74', 'Y75-79', 'Y80-84', 'Y85-89', 'Y90-94', 'Y95+']
)}
dfp_ag = dfp_ag.groupby('age_group').sum()

dfp_ag
Out[2]:
2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 ... 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025
age_group
Y_UN4 2615118 2648690 2682957 2722830 2742580 2766691 2794628 2820772 2822037 2814758 ... 2563779 2491284 2414284 2338752 2264538 2216510 2142834 2085244 2031476 1982728
Y5-9 2670883 2642383 2642001 2665027 2698540 2719674 2753946 2783266 2807843 2809995 ... 2826949 2793205 2752385 2691273 2627956 2578340 2523317 2464447 2409672 2342876
Y10-14 2811136 2841184 2830446 2816816 2793342 2773113 2758757 2767693 2785425 2815128 ... 2832275 2830996 2836003 2841862 2835060 2841695 2823644 2794408 2745119 2693561
Y15-19 2951444 2877206 2856180 2850135 2866629 2896122 2943031 2947221 2941153 2917378 ... 2863240 2870747 2879427 2873676 2871056 2857013 2866117 2895108 2921261 2922469
Y20-24 3403172 3297760 3232237 3170428 3101283 3033697 3019868 3043117 3051364 3075031 ... 3011433 2982184 2961062 2962307 2955888 2957340 2923341 2938344 2950359 2988760
Y25-29 4226129 4106073 4033696 3905064 3743185 3596111 3531963 3460079 3396597 3357372 ... 3218786 3215851 3216640 3175599 3128494 3045579 3001345 2995670 3012211 3051798
Y30-34 4538190 4533168 4568378 4580466 4555980 4468611 4396162 4288646 4114496 3954616 ... 3480793 3412512 3346272 3320500 3282441 3226146 3211649 3234123 3213337 3203692
Y35-39 4634717 4693056 4752934 4765505 4738533 4715429 4737619 4738681 4718939 4689885 ... 4019133 3872779 3758255 3654733 3572191 3483252 3412872 3366663 3357101 3342755
Y40-44 4093260 4203385 4361165 4533812 4659852 4759077 4848120 4900029 4891294 4870390 ... 4725830 4628648 4504958 4372031 4187464 4008570 3868084 3773564 3680853 3615932
Y45-49 3745904 3802030 3887195 3975013 4064767 4168756 4298401 4444872 4606933 4738991 ... 4883298 4850613 4818286 4785280 4749765 4692501 4609870 4503977 4380844 4207289
Y50-54 3834841 3772816 3708736 3710698 3720210 3773144 3853175 3936508 4020325 4115911 ... 4720066 4808923 4870982 4900974 4876704 4817675 4807237 4791465 4770527 4740799
Y55-59 3350758 3474812 3622927 3747671 3883444 3816058 3756135 3689385 3690217 3710147 ... 4071859 4166061 4263608 4387417 4537491 4623965 4735135 4810601 4848988 4826781
Y60-64 3454797 3439915 3375198 3282309 3164100 3288694 3419345 3568056 3689905 3824379 ... 3625089 3680986 3750459 3819054 3893350 3946096 4053284 4162290 4292746 4441564
Y65-69 3077349 3104112 3168211 3243092 3310754 3329227 3311281 3250377 3163420 3051944 ... 3670374 3599731 3534776 3468709 3471014 3474333 3527442 3601512 3672071 3741720
Y70-74 2809168 2824266 2842188 2867483 2859310 2858191 2893614 2963888 3037941 3106471 ... 2848705 2965634 3083893 3215420 3324360 3441171 3368919 3312180 3254150 3254996
Y75-79 2277940 2308649 2332858 2362331 2425270 2461103 2474434 2497809 2525832 2528778 ... 2775042 2790572 2767716 2712798 2644013 2547210 2649517 2760935 2887393 2984910
Y80-84 1268161 1440220 1607590 1734099 1764351 1791707 1823036 1848769 1875153 1931624 ... 2050515 2063678 2098937 2162715 2231536 2277963 2283923 2263224 2223335 2169220
Y85-89 828433 748082 666574 651320 727040 831266 947603 1054936 1129987 1156291 ... 1299278 1330825 1343948 1367800 1396624 1396102 1401252 1424476 1481572 1543258
Y90-94 333301 355771 364019 376455 379181 365848 334245 304103 302254 352534 ... 554313 571057 583270 599445 616360 629658 640339 639782 654100 681422
Y_GE95 68569 72800 76500 83814 90645 98206 106406 112385 119201 126874 ... 122955 140448 152608 166328 175183 175094 180012 179188 184115 197647

20 rows × 24 columns

In [3]:
ds = discovery.DataSet(dataflow_identifier="DCIS_DECESSI") 
ds.set_filters(
    freq="A", 
    #eta="TOTAL", 
    paese_cittad="TOTAL", itter107="IT", 
    paese_nasc="X1033", # mondo
    sesso="9", stato_civ="99", tipo_dato="DEATH", titolo_studio="99"
)
df6 = retrieval.get_data(ds)
df6.loc[:, lambda dfx: (~dfx.isna()).any(axis=0)]
Out[3]:
DATAFLOW FREQ ETA PAESE_CITTAD ITTER107 PAESE_NASC SESSO STATO_CIV TIPO_DATO TITOLO_STUDIO TIME_PERIOD OBS_VALUE
0 IT1:26_29(1.1) A TOTAL TOTAL IT X1033 9 99 DEATH 99 2011-01-01 593427
221 IT1:26_29(1.1) A Y75-79 TOTAL IT X1033 9 99 DEATH 99 2011-01-01 78610
208 IT1:26_29(1.1) A Y70-74 TOTAL IT X1033 9 99 DEATH 99 2011-01-01 53831
65 IT1:26_29(1.1) A Y20-24 TOTAL IT X1033 9 99 DEATH 99 2011-01-01 1154
195 IT1:26_29(1.1) A Y65-69 TOTAL IT X1033 9 99 DEATH 99 2011-01-01 33930
... ... ... ... ... ... ... ... ... ... ... ... ...
181 IT1:26_29(1.1) A Y55-59 TOTAL IT X1033 9 99 DEATH 99 2023-01-01 17786
25 IT1:26_29(1.1) A Y_GE95 TOTAL IT X1033 9 99 DEATH 99 2023-01-01 60432
246 IT1:26_29(1.1) A Y80-84 TOTAL IT X1033 9 99 DEATH 99 2023-01-01 114422
259 IT1:26_29(1.1) A Y85-89 TOTAL IT X1033 9 99 DEATH 99 2023-01-01 142287
272 IT1:26_29(1.1) A Y90-94 TOTAL IT X1033 9 99 DEATH 99 2023-01-01 120585

273 rows × 12 columns

In [4]:
# convert to table: year x age_group
dfdg = (
    df6
    .query("ETA!='TOTAL'")
    .assign(age_group= lambda x: x["ETA"].map(age_group_labels)) # rename to my convenient age group labels (better for sorting)
    .assign(year= lambda x: x["TIME_PERIOD"].dt.year)
    .groupby(["age_group", "year"], as_index=False)["OBS_VALUE"].sum()
    .rename(columns={"OBS_VALUE": "deaths"})
    .pivot(index="age_group", columns="year", values="deaths")
)
dfdg
Out[4]:
year 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023
age_group
Y00-04 2004 1911 1815 1717 1678 1563 1483 1487 1239 1135 1101 1079 1159
Y05-09 242 202 207 206 181 219 192 198 149 149 150 165 194
Y10-14 282 250 241 241 232 262 261 247 210 218 227 211 256
Y15-19 730 648 610 565 648 569 561 596 550 489 573 589 574
Y20-24 1154 1019 959 877 901 874 872 840 814 737 769 821 837
Y25-29 1270 1181 1149 1069 1047 1024 1001 1062 995 896 1012 959 985
Y30-34 1764 1619 1560 1410 1452 1365 1324 1342 1231 1227 1316 1311 1291
Y35-39 2864 2877 2659 2538 2350 2237 2055 2121 1915 1957 2017 1893 1918
Y40-44 4765 4725 4555 4304 4308 4153 4084 3952 3743 3661 3642 3501 3302
Y45-49 7983 7984 7767 7387 7662 7103 7167 6911 6675 6962 6931 6592 6296
Y50-54 11239 11315 11343 11379 11926 11646 11480 11598 11105 12039 11930 11174 10883
Y55-59 16046 16096 15924 15781 16525 16173 16282 16435 16499 18372 18683 18189 17786
Y60-64 25861 25313 24067 23198 23995 22398 23211 23140 22919 26380 26299 25264 24618
Y65-69 33930 34985 35060 35631 37615 36488 35704 34076 32725 37688 37415 35754 34577
Y70-74 53831 54122 52089 49874 49951 47379 49737 49587 50459 61973 59726 56056 52107
Y75-79 78610 78818 76831 75696 80580 76350 79492 75479 72840 83204 78321 79310 76556
Y80-84 112457 115208 110882 109415 116284 107560 112299 108840 109518 131356 122810 123404 114422
Y85-89 126291 131093 127394 125989 139013 132045 140863 135177 136600 158488 146985 151705 142287
Y90-94 70261 82190 89084 97406 109970 103969 112295 109421 110500 130669 121974 131257 120585
Y95+ 41843 41327 36548 33681 41253 41884 48698 50624 53731 62717 59465 65843 60432
In [5]:
px.line(
    data_frame=dfdg,
    x=dfdg.index,
    y=dfdg.columns,
    color_discrete_sequence=get_colors(n=dfdg.columns.size),
    title="Deaths by Age Group from DCIS_DECESSI",
).update_layout(
    xaxis_title="Age Group",
    yaxis_title="Deaths",
).show()
In [6]:
tot_births = dfp.iloc[0]
tot_deaths = dfdg.sum()
fig = go.Figure()
fig.add_trace(go.Scatter(x=tot_births.index, y=tot_births, mode='lines+markers', name='Births'))
fig.add_trace(go.Scatter(x=tot_deaths.index, y=tot_deaths, mode='lines+markers', name='Deaths'))
fig.update_layout(title="Total births and deaths in Italy by year", xaxis_title="Year", yaxis_title="Count")

Now compute the same from DCIS_MORTALITA1/DEATHS*TOTAL_DEATHS/100'000, to be compared to data from DCIS_DECESSI.

In [7]:
# First I need to get total deaths by year
ds = discovery.DataSet(dataflow_identifier="DCIS_RICPOPRES2011")
ds.set_filters(freq="A", sesso="9", tipo_dato="DEATH", classe_eta="TOTAL", cittadinanza="TOTAL", itter107="IT")
df8 = retrieval.get_data(ds)

ds = discovery.DataSet(dataflow_identifier="DCIS_POPORESBIL1") 
ds.set_filters(freq="A", itter107="IT", sesso="9", tipo_inddem="DEATH")
df9 = retrieval.get_data(ds)

df89 = pd.concat([df8, df9.rename(columns={"TIPO_INDDEM": "TIPO_DATO"})])
df89.loc[:, lambda dfx: (~dfx.isna()).any(axis=0)]
Out[7]:
DATAFLOW FREQ SESSO TIPO_DATO CLASSE_ETA CITTADINANZA ITTER107 TIME_PERIOD OBS_VALUE OBS_STATUS
0 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2001-01-01 103213 NaN
1 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2002-01-01 557393 NaN
2 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2003-01-01 586468 NaN
3 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2004-01-01 546658 NaN
4 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2005-01-01 567304 NaN
5 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2006-01-01 557892 NaN
6 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2007-01-01 570801 NaN
7 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2008-01-01 585126 NaN
8 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2009-01-01 591663 NaN
9 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2010-01-01 587488 NaN
10 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2011-01-01 593402 NaN
11 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2012-01-01 612883 NaN
12 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2013-01-01 600744 NaN
13 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2014-01-01 598364 NaN
14 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2015-01-01 647571 NaN
15 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2016-01-01 615261 NaN
16 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2017-01-01 649061 NaN
17 IT1:164_164(1.1) A 9 DEATH TOTAL TOTAL IT 2018-01-01 633133 NaN
0 IT1:22_315(2.1) A 9 DEATH NaN NaN IT 2019-01-01 634417 NaN
1 IT1:22_315(2.1) A 9 DEATH NaN NaN IT 2020-01-01 740317 NaN
2 IT1:22_315(2.1) A 9 DEATH NaN NaN IT 2021-01-01 701346 NaN
3 IT1:22_315(2.1) A 9 DEATH NaN NaN IT 2022-01-01 715077 NaN
4 IT1:22_315(2.1) A 9 DEATH NaN NaN IT 2023-01-01 671065 NaN
5 IT1:22_315(2.1) A 9 DEATH NaN NaN IT 2024-01-01 650587 p
In [8]:
tot_deaths = (
    df89
    .iloc[1:] # remove 2001, because weird value
    .assign(year= lambda x: x["TIME_PERIOD"].dt.year)
    [["year", "OBS_VALUE"]]
    .set_index("year") 
    .to_dict()["OBS_VALUE"]
)
print(tot_deaths)
{2002: 557393, 2003: 586468, 2004: 546658, 2005: 567304, 2006: 557892, 2007: 570801, 2008: 585126, 2009: 591663, 2010: 587488, 2011: 593402, 2012: 612883, 2013: 600744, 2014: 598364, 2015: 647571, 2016: 615261, 2017: 649061, 2018: 633133, 2019: 634417, 2020: 740317, 2021: 701346, 2022: 715077, 2023: 671065, 2024: 650587}
In [9]:
ds = discovery.DataSet(dataflow_identifier="DCIS_MORTALITA1") 
ds.set_filters(
    freq="A", 
    #eta="TOTAL", 
    itter107="IT", sesso="9", funz_bio="DEATHS"
)
df7 = retrieval.get_data(ds)
df7.loc[:, lambda dfx: (~dfx.isna()).any(axis=0)]
Out[9]:
DATAFLOW FREQ ETA_CLASSI_ETA ITTER107 SESSO FUNZ_BIO TIME_PERIOD OBS_VALUE OBS_STATUS
0 IT1:26_295(1.1) A Y_UN4 IT 9 DEATHS 1974-01-01 2735 NaN
510 IT1:26_295(1.1) A Y104 IT 9 DEATHS 1974-01-01 4 NaN
5304 IT1:26_295(1.1) A Y69 IT 9 DEATHS 1974-01-01 2077 NaN
5253 IT1:26_295(1.1) A Y68 IT 9 DEATHS 1974-01-01 1916 NaN
5202 IT1:26_295(1.1) A Y67 IT 9 DEATHS 1974-01-01 1824 NaN
... ... ... ... ... ... ... ... ... ...
4742 IT1:26_295(1.1) A Y6 IT 9 DEATHS 2024-01-01 9 e
4691 IT1:26_295(1.1) A Y59 IT 9 DEATHS 2024-01-01 406 e
4640 IT1:26_295(1.1) A Y58 IT 9 DEATHS 2024-01-01 374 e
4946 IT1:26_295(1.1) A Y62 IT 9 DEATHS 2024-01-01 524 e
7343 IT1:26_295(1.1) A Y99 IT 9 DEATHS 2024-01-01 1231 e

7344 rows × 9 columns

In [10]:
# convert to table: year x age_group
dfd = (
    df7
    .rename(columns={"ETA_CLASSI_ETA": "ETA"})
    .query("ETA!='TOTAL'")
    .query("~ETA.str.contains('-|_')")
    .assign(age= lambda x: [ min(100, int(a.split("Y")[-1])) for a in x["ETA"] ])
    .groupby(["age", "TIME_PERIOD"], as_index=False)["OBS_VALUE"].sum()
    .assign(year= lambda x: x["TIME_PERIOD"].dt.year)
    .rename(columns={"OBS_VALUE": "deaths"})
    .pivot(index="age", columns="year", values="deaths")
    .sort_index()
    .filter(tot_deaths.keys())
    .apply(lambda x: x/100000*tot_deaths[x.name])
    .astype(int)
)
dfd
Out[10]:
2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 ... 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024
age
0 2407 2386 2028 2002 1874 1843 1831 1893 1885 1869 ... 1929 1821 1888 1848 1763 1947 1690 1680 1650 1672
1 156 146 136 124 128 125 128 124 123 130 ... 135 116 129 126 133 140 126 121 134 136
2 111 117 103 102 100 97 99 94 93 94 ... 103 92 90 88 95 103 91 92 93 91
3 89 93 81 79 78 74 76 76 76 71 ... 77 67 71 69 69 81 70 64 67 71
4 72 76 65 68 66 62 64 59 58 59 ... 64 55 51 56 57 66 56 57 53 58
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
96 8516 8269 9309 9230 9567 9697 9724 10111 10968 12016 ... 12718 13135 13026 14619 15371 14169 15022 15245 16051 16323
97 6727 6333 7445 7227 7687 7682 7501 7851 8453 9073 ... 10730 11185 10469 11143 12028 10771 11915 11648 12669 13252
98 5222 4703 5805 5457 6008 5919 5669 6011 6432 6735 ... 8703 9241 8256 8363 9186 7921 9159 8566 9643 10467
99 3963 3407 4433 3988 4535 4412 4207 4502 4840 5037 ... 6333 7167 6295 6407 6991 5693 6824 6085 7133 8008
100 8277 6269 9571 7431 9071 8493 8168 8857 9940 10877 ... 10276 14329 12890 15644 15651 10379 13367 10525 14052 16902

101 rows × 23 columns

Comparison #1: deaths from DCIS_DECESSI and DCIS_MORTALITA1/DEATHS

This is not correct (I explained the reason why, at the beginning of the notebook).

In [11]:
# Aggregate deaths by age group and compare with dfdg
dfdg2 = dfd.copy()
dfdg2["age_group"] = pd.cut(
    dfd.index,
    bins=[-1, 4, 9, 14, 19, 24, 29, 34, 39, 44, 49, 54, 59, 64, 69, 74, 79, 84, 89, 94, 100],
    labels=[
        'Y00-04', 'Y05-09', 'Y10-14', 'Y15-19', 'Y20-24', 'Y25-29', 'Y30-34', 'Y35-39', 'Y40-44', 'Y45-49',
        'Y50-54', 'Y55-59', 'Y60-64', 'Y65-69', 'Y70-74', 'Y75-79', 'Y80-84', 'Y85-89', 'Y90-94', 'Y95+'
    ]
)
dfdg2 = dfdg2.groupby("age_group", as_index=False).sum().set_index("age_group")
dfdg2[dfdg.columns]
Out[11]:
2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023
age_group
Y00-04 2223 2204 2168 2133 2308 2151 2229 2187 2117 2337 2033 2014 1997
Y05-09 241 236 234 217 237 227 243 238 232 241 238 234 251
Y10-14 301 299 282 271 282 269 294 294 276 301 273 292 292
Y15-19 804 739 696 637 690 643 660 642 618 709 658 684 668
Y20-24 1083 1016 984 925 981 920 952 915 892 1003 938 941 930
Y25-29 1161 1118 1098 1039 1092 1013 1048 1016 1020 1182 1113 1120 1091
Y30-34 1433 1401 1344 1279 1370 1259 1322 1270 1253 1448 1386 1413 1380
Y35-39 1867 1898 1860 1870 1998 1886 1925 1860 1793 2107 2009 2050 1950
Y40-44 2988 2971 2868 2804 2997 2808 2977 2916 2935 3432 3302 3380 3177
Y45-49 4727 4789 4684 4598 4996 4643 4858 4646 4572 5371 5138 5295 5057
Y50-54 7682 7702 7567 7328 8065 7332 7564 7348 7090 9022 8539 8214 7621
Y55-59 12168 12211 11850 11528 12807 11761 12070 11647 11283 14353 13429 13047 12002
Y60-64 18820 19414 18795 18278 20169 17939 19048 18277 17768 23347 21725 20641 18613
Y65-69 29021 29305 27992 27564 30258 27991 29680 28393 27537 36436 33823 32389 29303
Y70-74 42954 44505 43372 42554 47348 43078 45639 42392 41946 56277 51722 50976 46381
Y75-79 69277 70756 66422 64280 71665 64827 70692 67579 67030 88228 79705 80414 70789
Y80-84 104021 108583 102683 100386 112468 102013 109337 102368 101409 127696 115137 119407 109790
Y85-89 128499 135298 130070 128826 143563 133336 144206 138805 139290 163852 152568 160103 144759
Y90-94 104988 107191 108083 111408 120341 120133 126473 126086 127616 136431 132768 141339 136217
Y95+ 59101 61193 67695 70389 63867 70949 67766 74182 77669 66478 74774 71097 78747
In [12]:
dfdg
Out[12]:
year 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023
age_group
Y00-04 2004 1911 1815 1717 1678 1563 1483 1487 1239 1135 1101 1079 1159
Y05-09 242 202 207 206 181 219 192 198 149 149 150 165 194
Y10-14 282 250 241 241 232 262 261 247 210 218 227 211 256
Y15-19 730 648 610 565 648 569 561 596 550 489 573 589 574
Y20-24 1154 1019 959 877 901 874 872 840 814 737 769 821 837
Y25-29 1270 1181 1149 1069 1047 1024 1001 1062 995 896 1012 959 985
Y30-34 1764 1619 1560 1410 1452 1365 1324 1342 1231 1227 1316 1311 1291
Y35-39 2864 2877 2659 2538 2350 2237 2055 2121 1915 1957 2017 1893 1918
Y40-44 4765 4725 4555 4304 4308 4153 4084 3952 3743 3661 3642 3501 3302
Y45-49 7983 7984 7767 7387 7662 7103 7167 6911 6675 6962 6931 6592 6296
Y50-54 11239 11315 11343 11379 11926 11646 11480 11598 11105 12039 11930 11174 10883
Y55-59 16046 16096 15924 15781 16525 16173 16282 16435 16499 18372 18683 18189 17786
Y60-64 25861 25313 24067 23198 23995 22398 23211 23140 22919 26380 26299 25264 24618
Y65-69 33930 34985 35060 35631 37615 36488 35704 34076 32725 37688 37415 35754 34577
Y70-74 53831 54122 52089 49874 49951 47379 49737 49587 50459 61973 59726 56056 52107
Y75-79 78610 78818 76831 75696 80580 76350 79492 75479 72840 83204 78321 79310 76556
Y80-84 112457 115208 110882 109415 116284 107560 112299 108840 109518 131356 122810 123404 114422
Y85-89 126291 131093 127394 125989 139013 132045 140863 135177 136600 158488 146985 151705 142287
Y90-94 70261 82190 89084 97406 109970 103969 112295 109421 110500 130669 121974 131257 120585
Y95+ 41843 41327 36548 33681 41253 41884 48698 50624 53731 62717 59465 65843 60432
In [13]:
# get percentage deviation of dfdg and dfdg2
with pd.option_context('display.float_format', '{:.1f}%'.format):
    display((dfdg2[dfdg.columns].div(dfdg, axis=1) - 1)*100)
2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023
age_group
Y00-04 10.9% 15.3% 19.4% 24.2% 37.5% 37.6% 50.3% 47.1% 70.9% 105.9% 84.7% 86.7% 72.3%
Y05-09 -0.4% 16.8% 13.0% 5.3% 30.9% 3.7% 26.6% 20.2% 55.7% 61.7% 58.7% 41.8% 29.4%
Y10-14 6.7% 19.6% 17.0% 12.4% 21.6% 2.7% 12.6% 19.0% 31.4% 38.1% 20.3% 38.4% 14.1%
Y15-19 10.1% 14.0% 14.1% 12.7% 6.5% 13.0% 17.6% 7.7% 12.4% 45.0% 14.8% 16.1% 16.4%
Y20-24 -6.2% -0.3% 2.6% 5.5% 8.9% 5.3% 9.2% 8.9% 9.6% 36.1% 22.0% 14.6% 11.1%
Y25-29 -8.6% -5.3% -4.4% -2.8% 4.3% -1.1% 4.7% -4.3% 2.5% 31.9% 10.0% 16.8% 10.8%
Y30-34 -18.8% -13.5% -13.8% -9.3% -5.6% -7.8% -0.2% -5.4% 1.8% 18.0% 5.3% 7.8% 6.9%
Y35-39 -34.8% -34.0% -30.0% -26.3% -15.0% -15.7% -6.3% -12.3% -6.4% 7.7% -0.4% 8.3% 1.7%
Y40-44 -37.3% -37.1% -37.0% -34.9% -30.4% -32.4% -27.1% -26.2% -21.6% -6.3% -9.3% -3.5% -3.8%
Y45-49 -40.8% -40.0% -39.7% -37.8% -34.8% -34.6% -32.2% -32.8% -31.5% -22.9% -25.9% -19.7% -19.7%
Y50-54 -31.6% -31.9% -33.3% -35.6% -32.4% -37.0% -34.1% -36.6% -36.2% -25.1% -28.4% -26.5% -30.0%
Y55-59 -24.2% -24.1% -25.6% -27.0% -22.5% -27.3% -25.9% -29.1% -31.6% -21.9% -28.1% -28.3% -32.5%
Y60-64 -27.2% -23.3% -21.9% -21.2% -15.9% -19.9% -17.9% -21.0% -22.5% -11.5% -17.4% -18.3% -24.4%
Y65-69 -14.5% -16.2% -20.2% -22.6% -19.6% -23.3% -16.9% -16.7% -15.9% -3.3% -9.6% -9.4% -15.3%
Y70-74 -20.2% -17.8% -16.7% -14.7% -5.2% -9.1% -8.2% -14.5% -16.9% -9.2% -13.4% -9.1% -11.0%
Y75-79 -11.9% -10.2% -13.5% -15.1% -11.1% -15.1% -11.1% -10.5% -8.0% 6.0% 1.8% 1.4% -7.5%
Y80-84 -7.5% -5.8% -7.4% -8.3% -3.3% -5.2% -2.6% -5.9% -7.4% -2.8% -6.2% -3.2% -4.0%
Y85-89 1.7% 3.2% 2.1% 2.3% 3.3% 1.0% 2.4% 2.7% 2.0% 3.4% 3.8% 5.5% 1.7%
Y90-94 49.4% 30.4% 21.3% 14.4% 9.4% 15.5% 12.6% 15.2% 15.5% 4.4% 8.8% 7.7% 13.0%
Y95+ 41.2% 48.1% 85.2% 109.0% 54.8% 69.4% 39.2% 46.5% 44.6% 6.0% 25.7% 8.0% 30.3%

This last table show that there are significan discrepancies between the two sources!!!

Comparison #2: deaths from DCIS_DECESSI and DCIS_MORTALITA1/PROBDEATH*population

I should instead make match DEATH from DCIS_DECESSI with the population each year of observation, multiplied by PROBDEATH.

In [14]:
ds = discovery.DataSet(dataflow_identifier="DCIS_MORTALITA1") 
ds.set_filters(
    freq="A", 
    #eta="TOTAL", 
    itter107="IT", sesso="9", funz_bio="PROBDEATH"
)
df5 = retrieval.get_data(ds)
df5.loc[:, lambda dfx: (~dfx.isna()).any(axis=0)]
Out[14]:
DATAFLOW FREQ ETA_CLASSI_ETA ITTER107 SESSO FUNZ_BIO TIME_PERIOD OBS_VALUE OBS_STATUS
0 IT1:26_295(1.1) A Y_UN4 IT 9 PROBDEATH 1974-01-01 27.34902 NaN
510 IT1:26_295(1.1) A Y104 IT 9 PROBDEATH 1974-01-01 602.90046 NaN
5304 IT1:26_295(1.1) A Y69 IT 9 PROBDEATH 1974-01-01 29.21105 NaN
5253 IT1:26_295(1.1) A Y68 IT 9 PROBDEATH 1974-01-01 26.23550 NaN
5202 IT1:26_295(1.1) A Y67 IT 9 PROBDEATH 1974-01-01 24.37493 NaN
... ... ... ... ... ... ... ... ... ...
4742 IT1:26_295(1.1) A Y6 IT 9 PROBDEATH 2024-01-01 0.08638 e
4691 IT1:26_295(1.1) A Y59 IT 9 PROBDEATH 2024-01-01 4.27162 e
4640 IT1:26_295(1.1) A Y58 IT 9 PROBDEATH 2024-01-01 3.91415 e
4946 IT1:26_295(1.1) A Y62 IT 9 PROBDEATH 2024-01-01 5.58787 e
7343 IT1:26_295(1.1) A Y99 IT 9 PROBDEATH 2024-01-01 321.53069 e

7344 rows × 9 columns

In [15]:
df_dp = (
    df5
    .rename(columns={"ETA_CLASSI_ETA": "ETA"})
    .query("ETA!='TOTAL'")
    .query("~ETA.str.contains('-|_')")
    .assign(age= lambda x: [ int(a.split("Y")[-1]) for a in x["ETA"] ])
    .groupby(["age", "TIME_PERIOD"], as_index=False)["OBS_VALUE"].sum()
    .assign(year= lambda x: x["TIME_PERIOD"].dt.year)
    .rename(columns={"OBS_VALUE": "deaths"})
    .pivot(index="age", columns="year", values="deaths")
    .sort_index()
    .div(1000) # PROBDEATH is per 1000 people
)
df_dp
Out[15]:
year 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 ... 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024
age
0 0.024431 0.023538 0.021786 0.020453 0.018872 0.017614 0.016245 0.015062 0.013304 0.012488 ... 0.002978 0.002962 0.002915 0.002918 0.002784 0.002632 0.002407 0.002346 0.002460 0.002572
1 0.001161 0.001034 0.000992 0.000951 0.000931 0.000891 0.000880 0.000842 0.000818 0.000769 ... 0.000211 0.000190 0.000203 0.000204 0.000208 0.000191 0.000182 0.000175 0.000199 0.000209
2 0.000803 0.000729 0.000687 0.000662 0.000643 0.000619 0.000617 0.000583 0.000570 0.000541 ... 0.000162 0.000148 0.000145 0.000145 0.000152 0.000143 0.000135 0.000125 0.000138 0.000144
3 0.000580 0.000540 0.000499 0.000480 0.000460 0.000443 0.000448 0.000422 0.000415 0.000394 ... 0.000124 0.000115 0.000107 0.000109 0.000114 0.000110 0.000101 0.000094 0.000102 0.000107
4 0.000450 0.000430 0.000392 0.000377 0.000353 0.000338 0.000347 0.000329 0.000326 0.000305 ... 0.000098 0.000092 0.000084 0.000089 0.000089 0.000088 0.000079 0.000076 0.000082 0.000088
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
115 0.830542 0.842068 0.840793 0.832899 0.820353 0.821158 0.830536 0.812013 0.827645 0.844510 ... 0.812349 0.783121 0.790757 0.785699 0.789470 0.825086 0.801922 0.827020 0.812427 0.798995
116 0.840824 0.851811 0.851071 0.844183 0.832275 0.833640 0.842781 0.824728 0.840211 0.855815 ... 0.831453 0.805091 0.811518 0.807117 0.810286 0.843005 0.821794 0.844665 0.832323 0.820611
117 0.849532 0.860006 0.859822 0.854056 0.842874 0.844825 0.853700 0.836179 0.851242 0.865447 ... 0.848947 0.825683 0.830815 0.825405 0.829654 0.859216 0.840126 0.860587 0.850653 0.840784
118 0.856800 0.866782 0.867174 0.862612 0.852221 0.854774 0.863353 0.846417 0.860838 0.873540 ... 0.864874 0.844921 0.848679 0.843982 0.847608 0.873775 0.856952 0.874836 0.867463 0.859555
119 0.862763 0.872263 0.873250 0.869934 0.860379 0.863539 0.871795 0.855488 0.869114 0.880248 ... 0.879288 0.862830 0.865155 0.861231 0.864192 0.886750 0.872318 0.887476 0.882806 0.876970

120 rows × 51 columns

In [16]:
dfd_from_probdeath = dfp*df_dp
dfdg3 = dfd_from_probdeath.copy()
dfdg3["age_group"] = pd.cut(
    dfdg3.index,
    bins=[-1, 4, 9, 14, 19, 24, 29, 34, 39, 44, 49, 54, 59, 64, 69, 74, 79, 84, 89, 94, 120],
    labels=[
        'Y00-04', 'Y05-09', 'Y10-14', 'Y15-19', 'Y20-24', 'Y25-29', 'Y30-34', 'Y35-39', 'Y40-44', 'Y45-49',
        'Y50-54', 'Y55-59', 'Y60-64', 'Y65-69', 'Y70-74', 'Y75-79', 'Y80-84', 'Y85-89', 'Y90-94', 'Y95+'
    ]
)
dfdg3 = dfdg3.groupby("age_group", as_index=False).sum().set_index("age_group").astype(int)
dfdg3[dfdg.columns]
Out[16]:
2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023
age_group
Y00-04 2080 1951 1936 1849 1804 1711 1639 1593 1465 1334 1195 1141 1186
Y05-09 232 226 225 215 213 214 212 215 196 176 170 172 186
Y10-14 295 273 265 258 250 257 260 271 248 233 230 229 246
Y15-19 798 704 663 616 607 600 595 590 573 552 539 552 584
Y20-24 1126 1038 1029 961 937 909 887 865 847 806 793 774 822
Y25-29 1323 1220 1198 1149 1109 1064 1040 1048 1036 1005 982 947 982
Y30-34 1949 1779 1691 1570 1520 1452 1406 1366 1330 1308 1295 1293 1345
Y35-39 2999 2891 2832 2790 2641 2509 2335 2242 2102 2068 2019 1985 1988
Y40-44 4998 4785 4699 4592 4494 4405 4339 4244 4141 3975 3866 3738 3660
Y45-49 7699 7713 7827 7789 7763 7550 7429 7229 7052 7048 7037 6992 6971
Y50-54 10979 10903 11213 11208 11778 11537 11505 11645 11297 12256 12088 11373 11228
Y55-59 15946 15714 15882 15796 16495 16236 16126 16311 16230 18291 18411 17975 17963
Y60-64 26263 25732 24965 23797 24182 22595 23057 23099 22804 26247 26082 24892 24534
Y65-69 33712 33883 34127 35007 36938 37331 36813 35440 33461 38201 37304 35352 34747
Y70-74 53739 54449 53887 51877 52032 47475 49037 48147 49284 60079 60742 57076 54213
Y75-79 79188 78260 75646 74711 79481 76342 80120 77715 75020 85418 77496 77761 74818
Y80-84 111372 113932 109491 107656 115249 106925 110214 105809 106319 126399 120223 121919 116696
Y85-89 122401 126643 121914 120490 132155 125776 135012 130128 130464 151419 140781 143693 134517
Y90-94 59464 69149 75277 84478 100324 95050 103226 99939 100822 117443 110473 119495 110890
Y95+ 36214 39217 34305 30052 32735 32550 38529 40333 44531 53006 49887 56022 51208
In [17]:
# get percentage deviation of dfdg and dfdg3
with pd.option_context('display.float_format', '{:.1f}%'.format):
    display((dfdg3[dfdg.columns].div(dfdg, axis=1) - 1)*100)
2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023
age_group
Y00-04 3.8% 2.1% 6.7% 7.7% 7.5% 9.5% 10.5% 7.1% 18.2% 17.5% 8.5% 5.7% 2.3%
Y05-09 -4.1% 11.9% 8.7% 4.4% 17.7% -2.3% 10.4% 8.6% 31.5% 18.1% 13.3% 4.2% -4.1%
Y10-14 4.6% 9.2% 10.0% 7.1% 7.8% -1.9% -0.4% 9.7% 18.1% 6.9% 1.3% 8.5% -3.9%
Y15-19 9.3% 8.6% 8.7% 9.0% -6.3% 5.4% 6.1% -1.0% 4.2% 12.9% -5.9% -6.3% 1.7%
Y20-24 -2.4% 1.9% 7.3% 9.6% 4.0% 4.0% 1.7% 3.0% 4.1% 9.4% 3.1% -5.7% -1.8%
Y25-29 4.2% 3.3% 4.3% 7.5% 5.9% 3.9% 3.9% -1.3% 4.1% 12.2% -3.0% -1.3% -0.3%
Y30-34 10.5% 9.9% 8.4% 11.3% 4.7% 6.4% 6.2% 1.8% 8.0% 6.6% -1.6% -1.4% 4.2%
Y35-39 4.7% 0.5% 6.5% 9.9% 12.4% 12.2% 13.6% 5.7% 9.8% 5.7% 0.1% 4.9% 3.6%
Y40-44 4.9% 1.3% 3.2% 6.7% 4.3% 6.1% 6.2% 7.4% 10.6% 8.6% 6.2% 6.8% 10.8%
Y45-49 -3.6% -3.4% 0.8% 5.4% 1.3% 6.3% 3.7% 4.6% 5.6% 1.2% 1.5% 6.1% 10.7%
Y50-54 -2.3% -3.6% -1.1% -1.5% -1.2% -0.9% 0.2% 0.4% 1.7% 1.8% 1.3% 1.8% 3.2%
Y55-59 -0.6% -2.4% -0.3% 0.1% -0.2% 0.4% -1.0% -0.8% -1.6% -0.4% -1.5% -1.2% 1.0%
Y60-64 1.6% 1.7% 3.7% 2.6% 0.8% 0.9% -0.7% -0.2% -0.5% -0.5% -0.8% -1.5% -0.3%
Y65-69 -0.6% -3.1% -2.7% -1.8% -1.8% 2.3% 3.1% 4.0% 2.2% 1.4% -0.3% -1.1% 0.5%
Y70-74 -0.2% 0.6% 3.5% 4.0% 4.2% 0.2% -1.4% -2.9% -2.3% -3.1% 1.7% 1.8% 4.0%
Y75-79 0.7% -0.7% -1.5% -1.3% -1.4% -0.0% 0.8% 3.0% 3.0% 2.7% -1.1% -2.0% -2.3%
Y80-84 -1.0% -1.1% -1.3% -1.6% -0.9% -0.6% -1.9% -2.8% -2.9% -3.8% -2.1% -1.2% 2.0%
Y85-89 -3.1% -3.4% -4.3% -4.4% -4.9% -4.7% -4.2% -3.7% -4.5% -4.5% -4.2% -5.3% -5.5%
Y90-94 -15.4% -15.9% -15.5% -13.3% -8.8% -8.6% -8.1% -8.7% -8.8% -10.1% -9.4% -9.0% -8.0%
Y95+ -13.5% -5.1% -6.1% -10.8% -20.6% -22.3% -20.9% -20.3% -17.1% -15.5% -16.1% -14.9% -15.3%

This time the error is <10%, which makes sense when considering the smoothing in PROBDEATH.

Conclusions

  • This notebook helps to understand how to reconcile data from DCIS_DECESSI and DCIS_MORTALITA1
  • No outliers due to COVID in 2020-2021: PROBDEATH is not "too" smothed

Follow-up

  • Which is the best way to predict future deaths in Italy: propagate PROBDEATH or use the delta in population count?
← Home