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.
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 ageDCIS_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 ageDCIS_MORTALITA1- (1974-latest),FUNZ_BIO="DEATHS"&FUNZ_BIO="PROBDEATH", by age - Methodology Document
There are 3 options:
- take
DCIS_DECESSIbut I will know only age groups and I will miss 2004-2010 data (I will call thisdfdg). - take
DCIS_MORTALITA1\DEATHS * TOT_DEATHS(y) / 100'000(I will call thisdfd2anddfdg2for 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 thisdfd3anddfdg3for grouped ages)
I will make:
- Comparison #1 between
dfdganddfd2(grouped ages) - Comparison #2 between
dfdganddfd3(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.
# 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
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)]
# 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
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()
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.
# 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)]
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)
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)]
# 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
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).
# 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]
dfdg
# 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)
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.
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)]
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
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]
# 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)
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_DECESSIandDCIS_MORTALITA1 - No outliers due to COVID in 2020-2021:
PROBDEATHis not "too" smothed
Follow-up¶
- Which is the best way to predict future deaths in Italy: propagate
PROBDEATHor use the delta in population count?