← Home

Compute italian migration

AIM: get a migration balance by subracting deaths from delta-residents. I need it to have the migration trend till today, and use it for my model to predict the future.

In [1]:
import sys
from pathlib import Path
sys.path.insert(0, str(Path("../scripts").resolve()))

import warnings 
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
from plotly.subplots import make_subplots
from istat_source import istat_data


pio.renderers.default = 'plotly_mimetype+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
In [2]:
dfp=pd.read_csv("../data/pop_by_age_year.csv", index_col=0).rename(columns=int) # From Notebook#14
dfpc=pd.read_csv("../data/cc_by_age_year.csv", index_col=0).rename(columns=int) # From Notebook#14
dfdp=pd.read_csv("../data/deathprob_by_age_year.csv", index_col=0).rename(columns=int) # From Notebook#30
In [3]:
px.line(
    data_frame=dfpc,
    x=dfpc.index,
    y=dfpc.columns,
    color_discrete_sequence=get_colors(n=dfpc.columns.size),
    title="Population Change by age, on each year of observation",
).update_layout(
    xaxis_title="Age",
    yaxis_title="Population Change",
    legend_title_text="Year of observation",
    width=1000, 
).show()
In [4]:
# Compute the deaths from DEATHPROB*POPULATION and subract these from the change in population
# keep only 2002-today, and ages under 90
MAX_AGE = 80 # Assume there are no migrants above this age (conveniently, because there are numberical problems with the oldest ages)
last_year = dfp.columns[-1]
# Destination age a in year y+1 comes from age a-1 in year y.
common_years = sorted(set(dfpc.columns) & set(dfdp.columns) & set(dfp.columns))
common_years = [y for y in common_years if y >= 2002]
dfd = (dfp * dfdp).shift(1).loc[1:MAX_AGE, common_years]
dfpm = dfd + dfpc.loc[1:MAX_AGE, common_years]

# get some average values, making a couple of adjustements: remove 2020-2021 (adjsutment in statistics), fix high ages
sspm = dfpm.clip(lower=0).drop(columns=[2020, 2021]).mean(axis=1).astype(int)
sspm.to_csv("../data/average_migration_balance.csv")

fig = go.Figure(   
).add_trace(go.Scatter(
    x=sspm.index,
    y=sspm,
    mode='lines',
    line=dict(color="black", width=5),
    name="Average",
    zorder=1000
)).add_traces([go.Scatter(
    x=dfpm.index, 
    y=dfpm[year],
    mode='lines',
    line=dict(color=color),
    name=str(year)
    ) for year, color in zip(dfpm.columns, get_colors(n=dfpm.columns.size))]
).update_layout(
    xaxis_title="Age",
    yaxis_title="Computed Migrants",
    legend_title_text="Year",
    margin=dict(l=10, r=10, t=20, b=10),
    width=780,
    height=320,
)
print("Computed Migrants by age and year of observations by excluding deaths from population change.")
fig.write_html("../images_output/migrants_by_age.html")
fig.show()
display(dfpm)
Computed Migrants by age and year of observations by excluding deaths from population change.
2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 ... 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025
AGE
1 5827.491725 4542.780780 5287.008655 2538.845685 3596.170049 4944.160943 4078.216979 1000.003841 1174.343228 245.571638 ... 1428.746916 1825.752556 2007.959949 6977.901938 9387.307012 3652.725042 4565.740396 4241.968355 4636.184851 3647.322220
2 4039.941542 4599.940126 2972.557368 3013.950059 1408.792265 3396.846325 2733.285264 1415.895322 -777.478189 -628.674281 ... -512.185594 -67.163754 496.996977 1663.013926 6869.290532 1990.844487 3684.205507 3400.591679 3346.499105 2951.843593
3 2824.118978 4485.887209 4602.919412 3003.613231 2418.021865 2746.583552 3139.097778 1988.267334 995.061516 -838.257055 ... -1472.706731 -383.490752 205.992966 954.365835 6990.185538 1126.686914 3321.347711 3390.854898 2855.569545 3294.648930
4 3776.529113 3300.512850 4901.771414 4661.924215 2090.399404 4018.585478 2199.936650 1776.167538 1098.910596 473.572369 ... -1058.927629 -1162.496660 -349.443360 877.853007 8830.726183 560.897424 2777.951595 3092.526337 2784.875107 3303.730330
5 3985.018611 4343.664847 3687.157108 5020.832123 3830.844242 4203.555732 3267.626403 1341.343437 1221.561055 930.249849 ... -809.590095 -923.295180 -188.622437 948.537674 9591.402353 -1753.225056 2422.274753 3102.545982 2982.721730 3122.809373
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
76 1300.969133 1197.945288 338.766763 -94.595308 -276.162575 -416.391209 140.657777 -209.649728 -300.091625 -129.827851 ... -625.736898 -791.121803 -590.650810 -419.978142 380.751458 -1927.844154 -218.488540 -289.931992 98.166663 -1146.229355
77 1025.980250 1001.752473 1488.311731 68.997467 -142.597803 -263.011274 -669.605057 -482.867164 -206.541763 -234.896890 ... -940.842196 -920.647832 -885.242017 -118.806523 103.226185 -1328.442311 -666.742064 -609.750856 -734.252349 -439.523043
78 1697.497402 594.170109 616.928713 820.382087 110.737135 52.190697 -287.650653 -817.925303 -786.462681 -113.050860 ... -1425.992964 -1174.962982 -714.700335 -30.546994 577.127576 -1410.624301 -200.437099 -590.719700 -1136.433414 -871.124118
79 253.570577 1023.953866 725.902930 581.197783 892.166681 -88.744340 -301.194408 -734.170149 -894.650357 -311.456422 ... -1546.720595 -1777.764084 -1328.718372 -440.894421 870.452779 -996.129577 -608.833449 -274.709617 -924.330528 -1628.650892
80 1035.572001 -134.009152 -59.460955 -257.302750 525.643801 411.906609 -518.665789 -407.584018 -1356.600592 -363.448839 ... -1511.352686 -1902.058098 -1825.975669 -553.266948 35.894214 -759.085927 -274.066565 -664.495970 -1000.851321 -1300.957211

80 rows × 24 columns

Get raw data for migration

I can not get the same resolution per age, but I can compare with the total migration balance with the sum of the values I computed

In [5]:
df6 = istat_data(resource_id="28_185", key={'FREQ': 'A', 'REF_AREA': ['IT', 'ITTOT'], 'CHANGE_OF_RESIDENCE': 'FREIGN', 'CITIZENSHIP': ['IT', 'FOR', 'TOTAL'], 'AGE': [], 'SEX': '9', 'TERRITORY_NEXT_RESID': ['IT', 'ITTOT'], 'COUNTRY_PREV_RESID': 'X1033', 'COUNTRY_NEXT_RESID': 'X1033'})
df6
Out[5]:
FREQ REF_AREA DATA_TYPE CHANGE_OF_RESIDENCE CITIZENSHIP SEX AGE TERRITORY_NEXT_RESID COUNTRY_PREV_RESID COUNTRY_NEXT_RESID TIME_PERIOD value
0 A IT TDEREG FREIGN FOR 9 TOTAL ITTOT X1033 X1033 2002 7700.0
1 A IT TDEREG FREIGN FOR 9 TOTAL ITTOT X1033 X1033 2003 8840.0
2 A IT TDEREG FREIGN FOR 9 TOTAL ITTOT X1033 X1033 2004 10755.0
3 A IT TDEREG FREIGN FOR 9 TOTAL ITTOT X1033 X1033 2005 11940.0
4 A IT TDEREG FREIGN FOR 9 TOTAL ITTOT X1033 X1033 2006 12099.0
... ... ... ... ... ... ... ... ... ... ... ... ...
1075 A ITTOT TREG FREIGN TOTAL 9 Y40-64 ITTOT X1033 X1033 2021 77851.0
1076 A ITTOT TREG FREIGN TOTAL 9 Y40-64 ITTOT X1033 X1033 2022 104327.0
1077 A ITTOT TREG FREIGN TOTAL 9 Y40-64 ITTOT X1033 X1033 2023 100615.0
1078 A ITTOT TREG FREIGN TOTAL 9 Y40-64 ITTOT X1033 X1033 2024 98752.0
1079 A ITTOT TREG FREIGN TOTAL 9 Y40-64 ITTOT X1033 X1033 2025 97499.0

1080 rows × 12 columns

In [6]:
# Create a table with data the way I like it
replace_eta_num = {"Y_UN17": "Y00-17", "Y18-39": "Y18-39", "Y40-64": "Y40-64", "Y_GE65": "Y65+"} # more convenient for sorting
replace_paese_cittad = {"IT": "ITALIANS", "FOR": "FOREIGNERS"}
df_mig = pd.concat([
    ( # Immigrations: TOTAL, and by age
        df6
        .query("(TERRITORY_NEXT_RESID=='IT') & (REF_AREA=='ITTOT') & (CITIZENSHIP=='TOTAL')")
        .replace({"AGE": replace_eta_num})
        .assign(year= lambda x: x["TIME_PERIOD"].astype(int))
        .pivot(index="AGE", columns="year", values="value")
        .rename(index=lambda x: f"in_{x}")
    ),
    ( # Immigration: by nationality
        df6
        .query("(TERRITORY_NEXT_RESID=='IT') & (REF_AREA=='ITTOT') & (AGE=='TOTAL') &(CITIZENSHIP.isin(['FOR','IT']))")
        .replace({"CITIZENSHIP": replace_paese_cittad})
        .assign(year= lambda x: x["TIME_PERIOD"].astype(int))
        .pivot(index="CITIZENSHIP", columns="year", values="value")
        .rename(index=lambda x: f"in_{x}")
    ),
    ( # Emigrations: TOTAL, and by age
        df6
        .query("(TERRITORY_NEXT_RESID=='ITTOT') & (REF_AREA=='IT') & (CITIZENSHIP=='TOTAL')")
        .replace({"AGE": replace_eta_num})
        .assign(year= lambda x: x["TIME_PERIOD"].astype(int))
        .pivot(index="AGE", columns="year", values="value")
        .rename(index=lambda x: f"out_{x}")
    ),   
    ( # Emigrations: by nationality
        df6
        .query("(TERRITORY_NEXT_RESID=='ITTOT') & (REF_AREA=='IT') & (AGE=='TOTAL') &(CITIZENSHIP.isin(['FOR','IT']))")
        .replace({"CITIZENSHIP": replace_paese_cittad})
        .assign(year= lambda x: x["TIME_PERIOD"].astype(int))
        .pivot(index="CITIZENSHIP", columns="year", values="value")
        .rename(index=lambda x: f"out_{x}")
    ),
    

])
# add a row named "diff_TOTAL" with the difference between immigrations and emigrations
df_mig = pd.concat([
    df_mig, 
    df_mig.loc[["out_TOTAL", "in_TOTAL"], :].diff(axis=0).loc[["in_TOTAL"]].rename(index={"in_TOTAL":"diff_TOTAL"}),
    df_mig.loc[["out_ITALIANS", "in_ITALIANS"], :].diff(axis=0).loc[["in_ITALIANS"]].rename(index={"in_ITALIANS":"diff_ITALIANS"}),
    df_mig.loc[["out_FOREIGNERS", "in_FOREIGNERS"], :].diff(axis=0).loc[["in_FOREIGNERS"]].rename(index={"in_FOREIGNERS": "diff_FOREIGNERS"}),
])
assert all(df_mig.loc[[ "in_" + v for v in replace_eta_num.values()], :].sum(axis=0) == df_mig.loc["in_TOTAL", :]), "For immigrations, the sum of age groups should be equal to the total."
assert all(df_mig.loc[[ "in_" + v for v in replace_paese_cittad.values()], :].sum(axis=0) == df_mig.loc["in_TOTAL", :]), "For immigrations, the sum of nationality groups should be equal to the total."
assert all(df_mig.loc[[ "out_" + v for v in replace_eta_num.values()], :].sum(axis=0) == df_mig.loc["out_TOTAL", :]), "For emigrations, the sum of age groups should be equal to the total."
assert all(df_mig.loc[[ "out_" + v for v in replace_paese_cittad.values()], :].sum(axis=0) == df_mig.loc["out_TOTAL", :]), "For emigrations, the sum of nationality groups should be equal to the total."
print("All checks comparing totals,  passed.")

display(df_mig)
All checks comparing totals,  passed.
year 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 ... 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025
in_TOTAL 213202.0 440301.0 414880.0 304960.0 279714.0 527123.0 494394.0 421859.0 447744.0 385793.0 ... 300823.0 343440.0 332324.0 332778.0 247526.0 318366.0 410985.0 439658.0 451583.0 439916.0
in_Y00-17 48654.0 52481.0 61224.0 72540.0 61726.0 86285.0 84546.0 74882.0 69928.0 65272.0 ... 48806.0 54787.0 57329.0 63315.0 47166.0 57273.0 79184.0 83982.0 83699.0 79858.0
in_Y18-39 120233.0 285813.0 259676.0 170008.0 157596.0 325505.0 290318.0 242082.0 261735.0 224695.0 ... 180306.0 211033.0 195265.0 169142.0 128725.0 168227.0 208131.0 234378.0 248764.0 242164.0
in_Y40-64 36564.0 93231.0 84461.0 53684.0 51873.0 106145.0 106120.0 92977.0 106038.0 85769.0 ... 59633.0 64341.0 65817.0 82647.0 60216.0 77851.0 104327.0 100615.0 98752.0 97499.0
in_Y65+ 7751.0 8776.0 9519.0 8728.0 8519.0 9188.0 13410.0 11918.0 10043.0 10057.0 ... 12078.0 13279.0 13913.0 17674.0 11419.0 15015.0 19343.0 20683.0 20368.0 20395.0
in_FOREIGNERS 168726.0 392771.0 373086.0 267634.0 242048.0 490430.0 462276.0 392529.0 419552.0 354327.0 ... 262929.0 301071.0 285500.0 264571.0 191766.0 243607.0 336495.0 378372.0 393115.0 383465.0
in_ITALIANS 44476.0 47530.0 41794.0 37326.0 37666.0 36693.0 32118.0 29330.0 28192.0 31466.0 ... 37894.0 42369.0 46824.0 68207.0 55760.0 74759.0 74490.0 61286.0 58468.0 56451.0
out_TOTAL 41756.0 48706.0 49910.0 53931.0 58407.0 51113.0 61671.0 64921.0 67501.0 82461.0 ... 157065.0 155110.0 156960.0 179505.0 159884.0 158312.0 150189.0 158438.0 188903.0 144157.0
out_Y00-17 6778.0 8020.0 7149.0 8826.0 9597.0 8473.0 10033.0 10959.0 12564.0 14957.0 ... 29345.0 27826.0 27265.0 27633.0 28195.0 26540.0 22125.0 20937.0 23926.0 16840.0
out_Y18-39 21786.0 23692.0 25170.0 25920.0 27127.0 26158.0 32096.0 33503.0 33126.0 40074.0 ... 79036.0 78609.0 80273.0 94457.0 81168.0 78550.0 76996.0 84011.0 103463.0 75518.0
out_Y40-64 10358.0 12922.0 13412.0 14486.0 15948.0 13059.0 15888.0 16938.0 18125.0 22544.0 ... 40851.0 40310.0 41088.0 48295.0 41134.0 42263.0 39947.0 40133.0 47785.0 37348.0
out_Y65+ 2834.0 4072.0 4179.0 4699.0 5735.0 3423.0 3654.0 3521.0 3686.0 4886.0 ... 7833.0 8365.0 8334.0 9120.0 9387.0 10959.0 11121.0 13357.0 13729.0 14451.0
out_FOREIGNERS 7700.0 8840.0 10755.0 11940.0 12099.0 14814.0 22135.0 25897.0 27956.0 32404.0 ... 42553.0 40551.0 40228.0 57485.0 38934.0 64093.0 50679.0 44381.0 47847.0 35153.0
out_ITALIANS 34056.0 39866.0 39155.0 41991.0 46308.0 36299.0 39536.0 39024.0 39545.0 50057.0 ... 114512.0 114559.0 116732.0 122020.0 120950.0 94219.0 99510.0 114057.0 141056.0 109004.0
diff_TOTAL 171446.0 391595.0 364970.0 251029.0 221307.0 476010.0 432723.0 356938.0 380243.0 303332.0 ... 143758.0 188330.0 175364.0 153273.0 87642.0 160054.0 260796.0 281220.0 262680.0 295759.0
diff_ITALIANS 10420.0 7664.0 2639.0 -4665.0 -8642.0 394.0 -7418.0 -9694.0 -11353.0 -18591.0 ... -76618.0 -72190.0 -69908.0 -53813.0 -65190.0 -19460.0 -25020.0 -52771.0 -82588.0 -52553.0
diff_FOREIGNERS 161026.0 383931.0 362331.0 255694.0 229949.0 475616.0 440141.0 366632.0 391596.0 321923.0 ... 220376.0 260520.0 245272.0 207086.0 152832.0 179514.0 285816.0 333991.0 345268.0 348312.0

17 rows × 24 columns

In [7]:
colors_age = ["#E377C2", "#2CA02C", "#BCBD22", "#7F7F7F"]
colors_cittad = ["#1F77B4", "#FF7F0F"]
max_yrange = 550000

fig = make_subplots(rows=5, cols=1, vertical_spacing=0.03)
for i, group in enumerate(replace_eta_num.values()):
    fig.add_trace(
        go.Bar(name=f"in_{group}", x=df_mig.columns, y=df_mig.loc[f"in_{group}"], marker_color=colors_age[i], marker_line_width=0), 
        row=1, col=1)
for i, group in enumerate(replace_paese_cittad.values()):
    fig.add_trace(
        go.Bar(name=f"in_{group}", x=df_mig.columns, y=df_mig.loc[f"in_{group}"], marker_color=colors_cittad[i], marker_line_width=0), 
        row=3, col=1)
for i, group in enumerate(replace_eta_num.values()):
    fig.add_trace(
        go.Bar(name=f"out_{group}", x=df_mig.columns, y=-df_mig.loc[f"out_{group}"], marker_color=colors_age[i], marker_line_width=0), 
        row=2, col=1)
for i, group in enumerate(replace_paese_cittad.values()):
    fig.add_trace(
        go.Bar(name=f"out_{group}", x=df_mig.columns, y=-df_mig.loc[f"out_{group}"], marker_color=colors_cittad[i], marker_line_width=0), 
        row=4, col=1)
fig.add_trace(go.Bar(name="diff_TOTAL", x=df_mig.columns, y=df_mig.loc["diff_TOTAL"], marker_color="black", marker_line_width=0), 
    row=5, col=1)
fig.update_layout(
    barmode="stack", 
    #title="Migration Balance by age groups and citizenship", 
    margin=dict(l=10, r=10, t=20, b=10),
    width=780,
    height=570,
    legend_traceorder="normal",
    )

fig.update_yaxes(title="Incoming<br>(by age)", range=[0, max_yrange], row=1, col=1)
fig.update_yaxes(title="Incoming<br>(by citznshp)", range=[0, max_yrange], row=3, col=1)
fig.update_yaxes(title="Outcoming<br>(by age)", range=[-max_yrange, 0], row=2, col=1)
fig.update_yaxes(title="Outcoming<br>(by citznshp)", range=[-max_yrange, 0], row=4, col=1)
fig.update_yaxes(title="Net Balance", range=[0, max_yrange], row=5, col=1)

fig.write_html("../images_output/migrants_by_agegroups_citizenship.html")
fig.show()
In [8]:
ratio_italians_out_in = df_mig.loc["out_ITALIANS", :]/df_mig.loc["in_ITALIANS", :]
fig = go.Figure(
).add_trace(go.Scatter(x=df_mig.columns, y=ratio_italians_out_in, mode='lines+markers', marker_color="#1F77B4"), 
).add_hline(y=1, line_dash="dot", line_color="black", line_width=1
).update_layout(
    yaxis_title="Ratio Italians OUT/IN",
    margin=dict(l=10, r=10, t=20, b=10),
    width=620,
    height=170,
)
fig.write_html("../images_output/migrants_italian_citizenship.html")

print("Italians OUT over Italians IN by citizenship (parity=1.0)")
fig.show()
Italians OUT over Italians IN by citizenship (parity=1.0)

Finally compare raw and computed data

  • Calculations (blue bars): obtained by subtracting estimated deaths from the change or residents
  • Raw Data (red line): obtained directly from DCIS_MIGRAZIONI (immigrations minus emigrations)
  • Average: obtained by my calculations (2002-latest) excluding 2020, 2021, and excluding artifacts (negative values for high age groups)
In [9]:
# are these total number reasonable?
fig = go.Figure()
fig.add_trace(go.Bar(name="From calculations", x=dfpm.columns, y=dfpm.sum(axis=0)))
fig.add_trace(go.Scatter(name="ISTAT data", x=df_mig.columns, y=df_mig.loc["diff_TOTAL"], mode='lines+markers'))
fig.add_trace(go.Scatter(
    name="Average (calculations)", 
    x=dfpm.columns, 
    y=[sspm.sum()]*len(dfpm.columns),
    mode='lines',
    line=dict(color='black', dash='dot')
))
fig.update_layout(
    margin=dict(l=10, r=10, t=20, b=10),
    width=780,
    height=320,
)
print("Migration balance: Calculated vs. Observed")
fig.write_html("../images_output/migrants_total.html")
fig.show()
Migration balance: Calculated vs. Observed

Conclusions

  • According to total migration balance: raw data collected by ISTAT and my calculations from the change in residents has some significan discrepanciesm, expecially in 2015-2022.
  • In 2015 the number of italians emigrating was 3x those incoming
  • The net balance of migrants is oscillating between 130-480 thousands per year
  • Saved the average 2001-latest migration balance as average_migration_balance.csv: this is for ages 1-80, I can assume the other are 0 (newborns are counted as births, >80 y.o. are only less than 1000)

Follow-up

  • Understand why there is a discrepancy between calculated and raw data expecially in 2015-2021
  • Use the average number of migrants I compute to build scenarios for the future
← Home