hacking_part_2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "# Table of Contents\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is the second part of the Statistical Hacking for Economists notebook. In this part we look at the following topics. We start by creating our own dataset by getting it directly from the [Eurostat website](https://ec.europa.eu/eurostat/data/database) into the notebook. Then we use `pandas` to get the data into the shape that we want and merge it with data on other variables.\n",
    "\n",
    "We use this data to estimate a Bayesian model that is equivalent to an OLS regression using `pymc3`. We explain what the advantages are of using a Bayesian analysis and give a short introduction to Bayesian statistics. Since we have data on more than 20 countries, it is hard to distinguish the different countries in a graph. We explain how you can create an interactive (html) plot using `altair`.\n",
    "\n",
    "One of the advantages of Bayesian statistics is a consistent way to deal with missing data which is better than both deleting observations with missing values and interpolating missing values. We explain how missing values can be coded using `pymc3`.\n",
    "\n",
    "Then we consider modeling timeseries using `pymc3` by using a Gaussian Process.\n",
    "\n",
    "A Bayesian estimation of a neural network allows us to get a better idea of what the uncertainties are surrounding the weights and predictions of the neural network.\n",
    "\n",
    "We finish by simulating 95% confidence intervals to explain what they are and what they are not.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preamble\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import the relevant libraries.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: theano in /Users/annelouisedeboer/anaconda3/lib/python3.11/site-packages (1.0.5)\n",
      "Requirement already satisfied: numpy>=1.9.1 in /Users/annelouisedeboer/anaconda3/lib/python3.11/site-packages (from theano) (1.24.3)\n",
      "Requirement already satisfied: scipy>=0.14 in /Users/annelouisedeboer/anaconda3/lib/python3.11/site-packages (from theano) (1.10.1)\n",
      "Requirement already satisfied: six>=1.9.0 in /Users/annelouisedeboer/anaconda3/lib/python3.11/site-packages (from theano) (1.16.0)\n"
     ]
    }
   ],
   "source": [
    "!pip install theano"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "metadata": {},
   "outputs": [],
   "source": [
    "from __future__ import absolute_import, division, print_function, unicode_literals\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from scipy import optimize\n",
    "import statsmodels.api as sm\n",
    "import statsmodels.formula.api as smf\n",
    "import matplotlib.pyplot as plt\n",
    "import tensorflow as tf\n",
    "import altair as alt\n",
    "from linearmodels.iv import IV2SLS\n",
    "from tensorflow.keras import datasets, layers, models\n",
    "from tensorflow import keras\n",
    "# import theano\n",
    "# import pymc3 as pm\n",
    "import arviz as az\n",
    "import seaborn as sn\n",
    "\n",
    "\n",
    "# import covid19pandas as cod\n",
    "from country_codes import eurostat_dictionary\n",
    "import eurostat\n",
    "\n",
    "from warnings import filterwarnings\n",
    "filterwarnings('ignore')\n",
    "from sklearn import datasets\n",
    "from sklearn.preprocessing import scale\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.datasets import make_moons"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dealing with data\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section we work with the API of Eurostat to get data directly into the notebook.\n",
    "\n",
    "We look at the data for each variable, decide on the shape we want to have for the dataframe and then merge a number of data sets together. For this we will be using pandas. Further, we use the python file `country_codes.py` which should be in the same folder as this notebook.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Countries\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We focus on the following countries when looking at the data. For the Eurostat data this does not matter so much. But if you want to combine Eurostat data with OECD data, this selection can be useful.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {},
   "outputs": [],
   "source": [
    "EU_countries = ['Belgium', 'Bulgaria', 'Czechia', 'Denmark',\n",
    "'Germany (until 1990 former territory of the FRG)', 'Germany','Estonia',\n",
    "'Ireland', 'Greece', 'Spain', 'France', 'Croatia', 'Italy',\n",
    "'Cyprus', 'Latvia', 'Lithuania', 'Luxembourg', 'Hungary', 'Malta',\n",
    "'Netherlands', 'Austria', 'Poland', 'Portugal', 'Romania',\n",
    "'Slovenia', 'Slovakia', 'Finland', 'Sweden', 'United Kingdom',\n",
    "'Iceland', 'Liechtenstein', 'Norway', 'Switzerland',\n",
    "'Bosnia and Herzegovina']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Mortality\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The [Eurostat website](https://ec.europa.eu/eurostat/data/database?node_code=hlth_cd_apr) has a browser where you can look for data. Here we are looking for data on mortality. You can click on the link to the data browser to see the [details of the variable](https://ec.europa.eu/eurostat/databrowser/view/hlth_cd_apr/default/table?lang=en). At the top-left of the screen you can see the name of the variable in the line &ldquo;`online data code: HLTH_CD_APR`&rdquo;. The name of this variable we use below in the `get_data_df` method.\n",
    "\n",
    "So we call this method and collect the information in the dataframe `df`. Then we check what `df` looks like:\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>freq</th>\n",
       "      <th>mortalit</th>\n",
       "      <th>sex</th>\n",
       "      <th>icd10</th>\n",
       "      <th>unit</th>\n",
       "      <th>geo\\TIME_PERIOD</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>F</td>\n",
       "      <td>A00-A09</td>\n",
       "      <td>NR</td>\n",
       "      <td>AT</td>\n",
       "      <td>7.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>F</td>\n",
       "      <td>A00-A09</td>\n",
       "      <td>NR</td>\n",
       "      <td>BE</td>\n",
       "      <td>25.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>F</td>\n",
       "      <td>A00-A09</td>\n",
       "      <td>NR</td>\n",
       "      <td>BG</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>F</td>\n",
       "      <td>A00-A09</td>\n",
       "      <td>NR</td>\n",
       "      <td>CH</td>\n",
       "      <td>14.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>F</td>\n",
       "      <td>A00-A09</td>\n",
       "      <td>NR</td>\n",
       "      <td>CY</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  freq mortalit sex    icd10 unit geo\\TIME_PERIOD  2011  2012  2013  2014  \\\n",
       "0    A     PRVT   F  A00-A09   NR              AT   7.0  12.0   9.0   6.0   \n",
       "1    A     PRVT   F  A00-A09   NR              BE  25.0  11.0  20.0  26.0   \n",
       "2    A     PRVT   F  A00-A09   NR              BG   5.0   4.0  10.0  11.0   \n",
       "3    A     PRVT   F  A00-A09   NR              CH  14.0  14.0   9.0   9.0   \n",
       "4    A     PRVT   F  A00-A09   NR              CY   6.0   1.0   1.0   0.0   \n",
       "\n",
       "   2015  2016  2017  2018  2019  2020  \n",
       "0   5.0  15.0  10.0   8.0   6.0  13.0  \n",
       "1  26.0  27.0  45.0  26.0  44.0  39.0  \n",
       "2  13.0   7.0   4.0   3.0   4.0   5.0  \n",
       "3   5.0  13.0  13.0  11.0  14.0  10.0  \n",
       "4   0.0   0.0   2.0   2.0   3.0   3.0  "
      ]
     },
     "execution_count": 239,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = eurostat.get_data_df('hlth_cd_apr')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So we have a number of columns with abbreviations in them and then we have data for the years 2011-2017. Use the website of the variable to figure out what the abbreviations mean. To illustrate, the column `mortalit` gives three measures of mortality:\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['PRVT', 'TOTAL', 'TRT'], dtype=object)"
      ]
     },
     "execution_count": 240,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.mortalit.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will be interested in preventable &rsquo;PRVT&rsquo; and treatable &rsquo;TRT&rsquo; mortality.\n",
    "\n",
    "First, let&rsquo;s change the country column &rsquo;geo\\time&rsquo; and use country names instead of abbreviations. We need to &ldquo;escape&rdquo; the &rsquo;\\\\&rsquo; symbol to make sure pandas reads &rsquo;\\\\&rsquo; literally (not as a symbol). That is why we have &rsquo;\\\\\\\\&rsquo; in the code below. We use the `eurostat_dictionary` to turn the country abbreviations into country names.\n",
    "\n",
    "Note that to change the column name we use `.rename`; to change values in a row, we use `.replace`. The replacements are provided using a python dictionary: `{'old_name':'new_name'}`.\n",
    "\n",
    "If you are wondering why we use `inplace=True`, just run the code block without this to see the difference.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>freq</th>\n",
       "      <th>mortalit</th>\n",
       "      <th>sex</th>\n",
       "      <th>icd10</th>\n",
       "      <th>unit</th>\n",
       "      <th>geo</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>F</td>\n",
       "      <td>A00-A09</td>\n",
       "      <td>NR</td>\n",
       "      <td>AT</td>\n",
       "      <td>7.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>Austria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>F</td>\n",
       "      <td>A00-A09</td>\n",
       "      <td>NR</td>\n",
       "      <td>BE</td>\n",
       "      <td>25.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>Belgium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>F</td>\n",
       "      <td>A00-A09</td>\n",
       "      <td>NR</td>\n",
       "      <td>BG</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Bulgaria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>F</td>\n",
       "      <td>A00-A09</td>\n",
       "      <td>NR</td>\n",
       "      <td>CH</td>\n",
       "      <td>14.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Switzerland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>F</td>\n",
       "      <td>A00-A09</td>\n",
       "      <td>NR</td>\n",
       "      <td>CY</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Cyprus</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  freq mortalit sex    icd10 unit geo  2011  2012  2013  2014  2015  2016  \\\n",
       "0    A     PRVT   F  A00-A09   NR  AT   7.0  12.0   9.0   6.0   5.0  15.0   \n",
       "1    A     PRVT   F  A00-A09   NR  BE  25.0  11.0  20.0  26.0  26.0  27.0   \n",
       "2    A     PRVT   F  A00-A09   NR  BG   5.0   4.0  10.0  11.0  13.0   7.0   \n",
       "3    A     PRVT   F  A00-A09   NR  CH  14.0  14.0   9.0   9.0   5.0  13.0   \n",
       "4    A     PRVT   F  A00-A09   NR  CY   6.0   1.0   1.0   0.0   0.0   0.0   \n",
       "\n",
       "   2017  2018  2019  2020      country  \n",
       "0  10.0   8.0   6.0  13.0      Austria  \n",
       "1  45.0  26.0  44.0  39.0      Belgium  \n",
       "2   4.0   3.0   4.0   5.0     Bulgaria  \n",
       "3  13.0  11.0  14.0  10.0  Switzerland  \n",
       "4   2.0   2.0   3.0   3.0       Cyprus  "
      ]
     },
     "execution_count": 241,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.rename({'geo\\TIME_PERIOD':'geo'},inplace=True,axis=1)\n",
    "df['country'] = df['geo'].replace(eurostat_dictionary)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we will select the values that we are interested in: only EU countries, both males and females, both preventable and treatable mortality, unit of measurement rate &rsquo;RT&rsquo; (not number &rsquo;NR&rsquo;) and all diseases (e.g. not the subset &ldquo; [A00-A09] Intestinal infectious diseases&rdquo;).\n",
    "\n",
    "For selection, we can use `==` or `.isin()`. With numbers we can also use smaller/greater than `<,>` etc.\n",
    "\n",
    "After this selection, we can drop some columns to make the dataframe a bit easier to handle.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>freq</th>\n",
       "      <th>mortalit</th>\n",
       "      <th>sex</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6300</th>\n",
       "      <td>A</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>F</td>\n",
       "      <td>95.29</td>\n",
       "      <td>96.16</td>\n",
       "      <td>93.22</td>\n",
       "      <td>93.95</td>\n",
       "      <td>97.52</td>\n",
       "      <td>96.39</td>\n",
       "      <td>92.94</td>\n",
       "      <td>93.14</td>\n",
       "      <td>94.81</td>\n",
       "      <td>99.09</td>\n",
       "      <td>Austria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6301</th>\n",
       "      <td>A</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>F</td>\n",
       "      <td>101.35</td>\n",
       "      <td>99.97</td>\n",
       "      <td>100.34</td>\n",
       "      <td>95.40</td>\n",
       "      <td>99.71</td>\n",
       "      <td>94.13</td>\n",
       "      <td>93.42</td>\n",
       "      <td>92.83</td>\n",
       "      <td>92.39</td>\n",
       "      <td>113.63</td>\n",
       "      <td>Belgium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6302</th>\n",
       "      <td>A</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>F</td>\n",
       "      <td>119.32</td>\n",
       "      <td>126.17</td>\n",
       "      <td>119.29</td>\n",
       "      <td>129.04</td>\n",
       "      <td>128.32</td>\n",
       "      <td>120.14</td>\n",
       "      <td>121.87</td>\n",
       "      <td>119.05</td>\n",
       "      <td>120.05</td>\n",
       "      <td>177.73</td>\n",
       "      <td>Bulgaria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6303</th>\n",
       "      <td>A</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>F</td>\n",
       "      <td>73.75</td>\n",
       "      <td>74.02</td>\n",
       "      <td>73.85</td>\n",
       "      <td>72.70</td>\n",
       "      <td>69.37</td>\n",
       "      <td>68.91</td>\n",
       "      <td>68.45</td>\n",
       "      <td>71.15</td>\n",
       "      <td>67.12</td>\n",
       "      <td>72.59</td>\n",
       "      <td>Switzerland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6304</th>\n",
       "      <td>A</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>F</td>\n",
       "      <td>57.31</td>\n",
       "      <td>49.38</td>\n",
       "      <td>47.75</td>\n",
       "      <td>54.65</td>\n",
       "      <td>55.67</td>\n",
       "      <td>49.98</td>\n",
       "      <td>46.69</td>\n",
       "      <td>50.87</td>\n",
       "      <td>49.03</td>\n",
       "      <td>54.90</td>\n",
       "      <td>Cyprus</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     freq mortalit sex    2011    2012    2013    2014    2015    2016  \\\n",
       "6300    A     PRVT   F   95.29   96.16   93.22   93.95   97.52   96.39   \n",
       "6301    A     PRVT   F  101.35   99.97  100.34   95.40   99.71   94.13   \n",
       "6302    A     PRVT   F  119.32  126.17  119.29  129.04  128.32  120.14   \n",
       "6303    A     PRVT   F   73.75   74.02   73.85   72.70   69.37   68.91   \n",
       "6304    A     PRVT   F   57.31   49.38   47.75   54.65   55.67   49.98   \n",
       "\n",
       "        2017    2018    2019    2020      country  \n",
       "6300   92.94   93.14   94.81   99.09      Austria  \n",
       "6301   93.42   92.83   92.39  113.63      Belgium  \n",
       "6302  121.87  119.05  120.05  177.73     Bulgaria  \n",
       "6303   68.45   71.15   67.12   72.59  Switzerland  \n",
       "6304   46.69   50.87   49.03   54.90       Cyprus  "
      ]
     },
     "execution_count": 242,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[df.country.isin(EU_countries) & (df.sex.isin([\"M\",\"F\"]) ) & (df.mortalit.isin([\"PRVT\",\"TRT\"])) \\\n",
    "        & (df.unit == \"RT\") & (df.icd10 == \"TOTAL\")]\n",
    "df.drop([\"unit\",\"icd10\",\"geo\"],axis=1,inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>sex</th>\n",
       "      <th>mortalit</th>\n",
       "      <th>year</th>\n",
       "      <th>rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Austria</td>\n",
       "      <td>F</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>2011</td>\n",
       "      <td>95.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>F</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>2011</td>\n",
       "      <td>101.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bulgaria</td>\n",
       "      <td>F</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>2011</td>\n",
       "      <td>119.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>F</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>2011</td>\n",
       "      <td>73.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cyprus</td>\n",
       "      <td>F</td>\n",
       "      <td>PRVT</td>\n",
       "      <td>2011</td>\n",
       "      <td>57.31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country sex mortalit  year    rate\n",
       "0      Austria   F     PRVT  2011   95.29\n",
       "1      Belgium   F     PRVT  2011  101.35\n",
       "2     Bulgaria   F     PRVT  2011  119.32\n",
       "3  Switzerland   F     PRVT  2011   73.75\n",
       "4       Cyprus   F     PRVT  2011   57.31"
      ]
     },
     "execution_count": 243,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.melt(df,id_vars=['country','sex','mortalit'],\n",
    "                        value_vars= df.columns[2:-1],\n",
    "                        var_name='year',\n",
    "                        value_name='rate')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Instead of one column `rate` we want to have two columns (i.e. two variables); one corresponds to PRVT, the other to TRT. For this we use [unstack](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.unstack.html). We put the identifying columns in an order such that the last column refers to `mortalit` which contains the two values PRVT and TRT. These columns become the index of the dataframe. Then we unstack the dataframe on the last column of the index, which is the default value of `unstack()`. This pivots the column `mortalit` into two separate columns PRVT and TRT.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>mortalit</th>\n",
       "      <th>PRVT</th>\n",
       "      <th>TRT</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Austria</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">2011</th>\n",
       "      <th>F</th>\n",
       "      <td>95.29</td>\n",
       "      <td>67.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>248.50</td>\n",
       "      <td>96.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2012</th>\n",
       "      <th>F</th>\n",
       "      <td>96.16</td>\n",
       "      <td>69.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>252.28</td>\n",
       "      <td>91.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <th>F</th>\n",
       "      <td>93.22</td>\n",
       "      <td>66.84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    rate       \n",
       "mortalit            PRVT    TRT\n",
       "country year sex               \n",
       "Austria 2011 F     95.29  67.41\n",
       "             M    248.50  96.86\n",
       "        2012 F     96.16  69.72\n",
       "             M    252.28  91.45\n",
       "        2013 F     93.22  66.84"
      ]
     },
     "execution_count": 244,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_index(['country','year','sex','mortalit'],inplace=True)\n",
    "df = df.unstack()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we reset the index (such that it no longer features the hierarchy &#x2013;with &rsquo;rate&rsquo; and &rsquo;mortalit&rsquo;&#x2013; shown above) and rename the columns to make them easier to read/understand.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>sex</th>\n",
       "      <th>Preventable mortality</th>\n",
       "      <th>Treatable mortality</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2011</td>\n",
       "      <td>F</td>\n",
       "      <td>95.29</td>\n",
       "      <td>67.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2011</td>\n",
       "      <td>M</td>\n",
       "      <td>248.50</td>\n",
       "      <td>96.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2012</td>\n",
       "      <td>F</td>\n",
       "      <td>96.16</td>\n",
       "      <td>69.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2012</td>\n",
       "      <td>M</td>\n",
       "      <td>252.28</td>\n",
       "      <td>91.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2013</td>\n",
       "      <td>F</td>\n",
       "      <td>93.22</td>\n",
       "      <td>66.84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   country  year sex  Preventable mortality  Treatable mortality\n",
       "0  Austria  2011   F                  95.29                67.41\n",
       "1  Austria  2011   M                 248.50                96.86\n",
       "2  Austria  2012   F                  96.16                69.72\n",
       "3  Austria  2012   M                 252.28                91.45\n",
       "4  Austria  2013   F                  93.22                66.84"
      ]
     },
     "execution_count": 245,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.reset_index(inplace=True)\n",
    "df.columns = [' '.join(col).strip() for col in df.columns.values]\n",
    "df.rename({'rate PRVT':'Preventable mortality', 'rate TRT':'Treatable mortality'},inplace=True,axis=1)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we consider a number of other variables. There you can practice the steps above to get these dataframes into the right shape. Finally, we merge each dataframe with the one previously created.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### GDP per capita\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Go to this [page](https://ec.europa.eu/eurostat/data/database?node_code=nama_10_pc) to find the variable name for &ldquo;Main GDP aggregates per capita&rdquo;; fill in the &ldquo;dots&rdquo; in the following code:\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>freq</th>\n",
       "      <th>unit</th>\n",
       "      <th>na_item</th>\n",
       "      <th>geo\\TIME_PERIOD</th>\n",
       "      <th>1975</th>\n",
       "      <th>1976</th>\n",
       "      <th>1977</th>\n",
       "      <th>1978</th>\n",
       "      <th>1979</th>\n",
       "      <th>1980</th>\n",
       "      <th>...</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>2021</th>\n",
       "      <th>2022</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>CLV10_EUR_HAB</td>\n",
       "      <td>B1GQ</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>3260.0</td>\n",
       "      <td>3330.0</td>\n",
       "      <td>3410.0</td>\n",
       "      <td>3530.0</td>\n",
       "      <td>3670.0</td>\n",
       "      <td>3830.0</td>\n",
       "      <td>3920.0</td>\n",
       "      <td>3810.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>CLV10_EUR_HAB</td>\n",
       "      <td>B1GQ</td>\n",
       "      <td>AT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>36180.0</td>\n",
       "      <td>36130.0</td>\n",
       "      <td>36140.0</td>\n",
       "      <td>36390.0</td>\n",
       "      <td>36980.0</td>\n",
       "      <td>37690.0</td>\n",
       "      <td>38070.0</td>\n",
       "      <td>35390.0</td>\n",
       "      <td>36740.0</td>\n",
       "      <td>38080.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>CLV10_EUR_HAB</td>\n",
       "      <td>B1GQ</td>\n",
       "      <td>BE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>33490.0</td>\n",
       "      <td>33870.0</td>\n",
       "      <td>34360.0</td>\n",
       "      <td>34620.0</td>\n",
       "      <td>35040.0</td>\n",
       "      <td>35510.0</td>\n",
       "      <td>36110.0</td>\n",
       "      <td>34050.0</td>\n",
       "      <td>36230.0</td>\n",
       "      <td>37040.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>CLV10_EUR_HAB</td>\n",
       "      <td>B1GQ</td>\n",
       "      <td>BG</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>5390.0</td>\n",
       "      <td>5470.0</td>\n",
       "      <td>5700.0</td>\n",
       "      <td>5910.0</td>\n",
       "      <td>6120.0</td>\n",
       "      <td>6330.0</td>\n",
       "      <td>6630.0</td>\n",
       "      <td>6400.0</td>\n",
       "      <td>6950.0</td>\n",
       "      <td>7680.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>CLV10_EUR_HAB</td>\n",
       "      <td>B1GQ</td>\n",
       "      <td>CH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>58650.0</td>\n",
       "      <td>59300.0</td>\n",
       "      <td>59600.0</td>\n",
       "      <td>60170.0</td>\n",
       "      <td>60420.0</td>\n",
       "      <td>61690.0</td>\n",
       "      <td>61950.0</td>\n",
       "      <td>60190.0</td>\n",
       "      <td>62950.0</td>\n",
       "      <td>64000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 52 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  freq           unit na_item geo\\TIME_PERIOD  1975  1976  1977  1978  1979  \\\n",
       "0    A  CLV10_EUR_HAB    B1GQ              AL   NaN   NaN   NaN   NaN   NaN   \n",
       "1    A  CLV10_EUR_HAB    B1GQ              AT   NaN   NaN   NaN   NaN   NaN   \n",
       "2    A  CLV10_EUR_HAB    B1GQ              BE   NaN   NaN   NaN   NaN   NaN   \n",
       "3    A  CLV10_EUR_HAB    B1GQ              BG   NaN   NaN   NaN   NaN   NaN   \n",
       "4    A  CLV10_EUR_HAB    B1GQ              CH   NaN   NaN   NaN   NaN   NaN   \n",
       "\n",
       "   1980  ...     2013     2014     2015     2016     2017     2018     2019  \\\n",
       "0   NaN  ...   3260.0   3330.0   3410.0   3530.0   3670.0   3830.0   3920.0   \n",
       "1   NaN  ...  36180.0  36130.0  36140.0  36390.0  36980.0  37690.0  38070.0   \n",
       "2   NaN  ...  33490.0  33870.0  34360.0  34620.0  35040.0  35510.0  36110.0   \n",
       "3   NaN  ...   5390.0   5470.0   5700.0   5910.0   6120.0   6330.0   6630.0   \n",
       "4   NaN  ...  58650.0  59300.0  59600.0  60170.0  60420.0  61690.0  61950.0   \n",
       "\n",
       "      2020     2021     2022  \n",
       "0   3810.0      NaN      NaN  \n",
       "1  35390.0  36740.0  38080.0  \n",
       "2  34050.0  36230.0  37040.0  \n",
       "3   6400.0   6950.0   7680.0  \n",
       "4  60190.0  62950.0  64000.0  \n",
       "\n",
       "[5 rows x 52 columns]"
      ]
     },
     "execution_count": 246,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_n = eurostat.get_data_df('nama_10_pc')\n",
    "df_n.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Question: make the following selections:\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-   from the column `country` select EU countries\n",
    "-   from `na_item` select &rsquo;B1GQ&rsquo;\n",
    "-   from `unit` select &rsquo;CP<sub>EUR</sub><sub>HAB</sub>&rsquo;\n",
    "-   then drop the columns `unit, na_item, geo`\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The melt statement\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The melt statement is easier now. Together with `year`, a column is uniquely identified by `country` only. Since we only have one variable here (GDP), we do not need to `unstack`.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>freq</th>\n",
       "      <th>unit</th>\n",
       "      <th>na_item</th>\n",
       "      <th>geo</th>\n",
       "      <th>1975</th>\n",
       "      <th>1976</th>\n",
       "      <th>1977</th>\n",
       "      <th>1978</th>\n",
       "      <th>1979</th>\n",
       "      <th>1980</th>\n",
       "      <th>...</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>2021</th>\n",
       "      <th>2022</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>CLV10_EUR_HAB</td>\n",
       "      <td>B1GQ</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>3330.0</td>\n",
       "      <td>3410.0</td>\n",
       "      <td>3530.0</td>\n",
       "      <td>3670.0</td>\n",
       "      <td>3830.0</td>\n",
       "      <td>3920.0</td>\n",
       "      <td>3810.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>CLV10_EUR_HAB</td>\n",
       "      <td>B1GQ</td>\n",
       "      <td>AT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>36130.0</td>\n",
       "      <td>36140.0</td>\n",
       "      <td>36390.0</td>\n",
       "      <td>36980.0</td>\n",
       "      <td>37690.0</td>\n",
       "      <td>38070.0</td>\n",
       "      <td>35390.0</td>\n",
       "      <td>36740.0</td>\n",
       "      <td>38080.0</td>\n",
       "      <td>Austria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>CLV10_EUR_HAB</td>\n",
       "      <td>B1GQ</td>\n",
       "      <td>BE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>33870.0</td>\n",
       "      <td>34360.0</td>\n",
       "      <td>34620.0</td>\n",
       "      <td>35040.0</td>\n",
       "      <td>35510.0</td>\n",
       "      <td>36110.0</td>\n",
       "      <td>34050.0</td>\n",
       "      <td>36230.0</td>\n",
       "      <td>37040.0</td>\n",
       "      <td>Belgium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>CLV10_EUR_HAB</td>\n",
       "      <td>B1GQ</td>\n",
       "      <td>BG</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>5470.0</td>\n",
       "      <td>5700.0</td>\n",
       "      <td>5910.0</td>\n",
       "      <td>6120.0</td>\n",
       "      <td>6330.0</td>\n",
       "      <td>6630.0</td>\n",
       "      <td>6400.0</td>\n",
       "      <td>6950.0</td>\n",
       "      <td>7680.0</td>\n",
       "      <td>Bulgaria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>CLV10_EUR_HAB</td>\n",
       "      <td>B1GQ</td>\n",
       "      <td>CH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>59300.0</td>\n",
       "      <td>59600.0</td>\n",
       "      <td>60170.0</td>\n",
       "      <td>60420.0</td>\n",
       "      <td>61690.0</td>\n",
       "      <td>61950.0</td>\n",
       "      <td>60190.0</td>\n",
       "      <td>62950.0</td>\n",
       "      <td>64000.0</td>\n",
       "      <td>Switzerland</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 53 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  freq           unit na_item geo  1975  1976  1977  1978  1979  1980  ...  \\\n",
       "0    A  CLV10_EUR_HAB    B1GQ  AL   NaN   NaN   NaN   NaN   NaN   NaN  ...   \n",
       "1    A  CLV10_EUR_HAB    B1GQ  AT   NaN   NaN   NaN   NaN   NaN   NaN  ...   \n",
       "2    A  CLV10_EUR_HAB    B1GQ  BE   NaN   NaN   NaN   NaN   NaN   NaN  ...   \n",
       "3    A  CLV10_EUR_HAB    B1GQ  BG   NaN   NaN   NaN   NaN   NaN   NaN  ...   \n",
       "4    A  CLV10_EUR_HAB    B1GQ  CH   NaN   NaN   NaN   NaN   NaN   NaN  ...   \n",
       "\n",
       "      2014     2015     2016     2017     2018     2019     2020     2021  \\\n",
       "0   3330.0   3410.0   3530.0   3670.0   3830.0   3920.0   3810.0      NaN   \n",
       "1  36130.0  36140.0  36390.0  36980.0  37690.0  38070.0  35390.0  36740.0   \n",
       "2  33870.0  34360.0  34620.0  35040.0  35510.0  36110.0  34050.0  36230.0   \n",
       "3   5470.0   5700.0   5910.0   6120.0   6330.0   6630.0   6400.0   6950.0   \n",
       "4  59300.0  59600.0  60170.0  60420.0  61690.0  61950.0  60190.0  62950.0   \n",
       "\n",
       "      2022      country  \n",
       "0      NaN      Albania  \n",
       "1  38080.0      Austria  \n",
       "2  37040.0      Belgium  \n",
       "3   7680.0     Bulgaria  \n",
       "4  64000.0  Switzerland  \n",
       "\n",
       "[5 rows x 53 columns]"
      ]
     },
     "execution_count": 247,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_n.rename({'geo\\TIME_PERIOD':'geo'},inplace=True,axis=1)\n",
    "\n",
    "df_n['country'] = df_n['geo'].replace(eurostat_dictionary)\n",
    "# drop columns that contain all nan values\n",
    "# Dropping columns with all NaN values\n",
    "df_n.dropna(axis=1, how='all', inplace=True)\n",
    "\n",
    "df_n.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>sex</th>\n",
       "      <th>Preventable mortality</th>\n",
       "      <th>Treatable mortality</th>\n",
       "      <th>unit</th>\n",
       "      <th>gdp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2011</td>\n",
       "      <td>F</td>\n",
       "      <td>95.29</td>\n",
       "      <td>67.41</td>\n",
       "      <td>CLV10_EUR_HAB</td>\n",
       "      <td>36300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2011</td>\n",
       "      <td>F</td>\n",
       "      <td>95.29</td>\n",
       "      <td>67.41</td>\n",
       "      <td>CLV10_EUR_HAB</td>\n",
       "      <td>26360.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2011</td>\n",
       "      <td>F</td>\n",
       "      <td>95.29</td>\n",
       "      <td>67.41</td>\n",
       "      <td>CLV10_EUR_HAB</td>\n",
       "      <td>4410.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2011</td>\n",
       "      <td>F</td>\n",
       "      <td>95.29</td>\n",
       "      <td>67.41</td>\n",
       "      <td>CLV10_EUR_HAB</td>\n",
       "      <td>18430.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2011</td>\n",
       "      <td>F</td>\n",
       "      <td>95.29</td>\n",
       "      <td>67.41</td>\n",
       "      <td>CLV10_EUR_HAB</td>\n",
       "      <td>19130.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   country  year sex  Preventable mortality  Treatable mortality  \\\n",
       "0  Austria  2011   F                  95.29                67.41   \n",
       "1  Austria  2011   F                  95.29                67.41   \n",
       "2  Austria  2011   F                  95.29                67.41   \n",
       "3  Austria  2011   F                  95.29                67.41   \n",
       "4  Austria  2011   F                  95.29                67.41   \n",
       "\n",
       "            unit      gdp  \n",
       "0  CLV10_EUR_HAB  36300.0  \n",
       "1  CLV10_EUR_HAB  26360.0  \n",
       "2  CLV10_EUR_HAB   4410.0  \n",
       "3  CLV10_EUR_HAB  18430.0  \n",
       "4  CLV10_EUR_HAB  19130.0  "
      ]
     },
     "execution_count": 249,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge on 'country' and 'year' columns\n",
    "df_n = pd.melt(df_n,id_vars=['country','unit'],\n",
    "                        value_vars= df_n.columns[4:-1],\n",
    "                        var_name='year',\n",
    "                        value_name='gdp')\n",
    "\n",
    "merged_df = df.merge(df_n, on=['country', 'year'], how='left') #\n",
    "merged_df.head()\n",
    "\n",
    "# dataframe with both mortalilty rates and GDP"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Income quantiles\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use [this page](https://ec.europa.eu/eurostat/databrowser/view/icw_res_02/default/table?lang=en) to get the variable name for &ldquo;Mean and median economic resources of households by income, consumption and wealth quantiles - experimental statistics&rdquo;.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We drop the year 2010 as it does not lie in the period for which we have the other data that we download.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 304,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"5\" halign=\"left\">mean/median income</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>quant_inc</th>\n",
       "      <th>QU1</th>\n",
       "      <th>QU2</th>\n",
       "      <th>QU3</th>\n",
       "      <th>QU4</th>\n",
       "      <th>QU5</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Austria</th>\n",
       "      <th>2015</th>\n",
       "      <td>16542.1</td>\n",
       "      <td>27955.8</td>\n",
       "      <td>36687.8</td>\n",
       "      <td>48214.5</td>\n",
       "      <td>74519.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>17597.6</td>\n",
       "      <td>32409.5</td>\n",
       "      <td>41893.2</td>\n",
       "      <td>52793.7</td>\n",
       "      <td>83297.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Belgium</th>\n",
       "      <th>2015</th>\n",
       "      <td>15834.1</td>\n",
       "      <td>23310.6</td>\n",
       "      <td>33248.2</td>\n",
       "      <td>44849.9</td>\n",
       "      <td>68233.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>18600.4</td>\n",
       "      <td>27357.2</td>\n",
       "      <td>39019.0</td>\n",
       "      <td>52166.4</td>\n",
       "      <td>76766.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bulgaria</th>\n",
       "      <th>2015</th>\n",
       "      <td>1847.7</td>\n",
       "      <td>3270.1</td>\n",
       "      <td>5115.9</td>\n",
       "      <td>7451.6</td>\n",
       "      <td>14354.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              mean/median income                                    \n",
       "quant_inc                    QU1      QU2      QU3      QU4      QU5\n",
       "country  year                                                       \n",
       "Austria  2015            16542.1  27955.8  36687.8  48214.5  74519.8\n",
       "         2020            17597.6  32409.5  41893.2  52793.7  83297.4\n",
       "Belgium  2015            15834.1  23310.6  33248.2  44849.9  68233.2\n",
       "         2020            18600.4  27357.2  39019.0  52166.4  76766.2\n",
       "Bulgaria 2015             1847.7   3270.1   5115.9   7451.6  14354.6"
      ]
     },
     "execution_count": 304,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_n = eurostat.get_data_df('icw_res_02') #icw_res_02\n",
    "df_n.rename({'geo\\TIME_PERIOD':'geo'},inplace=True,axis=1)\n",
    "\n",
    "df_n = df_n[(df_n.indic_il=='INC_DISP')&(df_n.statinfo=='AVG')&(df_n.quant_inc.isin(['QU1', 'QU2', 'QU3', 'QU4', 'QU5']))&(df_n.quant_expn=='TOTAL')&(df_n.quant_wlth=='TOTAL')]\n",
    "\n",
    "df_n.drop(['unit','quant_expn','quant_wlth','indic_il','statinfo', '2010'],axis=1,inplace=True)\n",
    "df_n['country'] = df_n['geo'].replace(eurostat_dictionary)\n",
    "\n",
    "df_n.drop(['geo'],axis=1,inplace=True)\n",
    "\n",
    "df_n = pd.melt(df_n,id_vars=['country','quant_inc'],\n",
    "                        value_vars= df_n.columns[1:-1],\n",
    "                        var_name='year',\n",
    "                        value_name='mean/median income')\n",
    "\n",
    "df_n.set_index(['country', 'year', 'quant_inc'], inplace=True)\n",
    "\n",
    "df_n = df_n[df_n.index.duplicated(keep='last')] # removing duplicate indices\n",
    "\n",
    "df_n.index.unique()\n",
    "\n",
    "df_n = df_n.unstack(level='quant_inc')\n",
    "\n",
    "df_n.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 307,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>sex</th>\n",
       "      <th>Preventable mortality</th>\n",
       "      <th>Treatable mortality</th>\n",
       "      <th>(mean/median income, QU1)</th>\n",
       "      <th>(mean/median income, QU2)</th>\n",
       "      <th>(mean/median income, QU3)</th>\n",
       "      <th>(mean/median income, QU4)</th>\n",
       "      <th>(mean/median income, QU5)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2011</td>\n",
       "      <td>F</td>\n",
       "      <td>95.29</td>\n",
       "      <td>67.41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2011</td>\n",
       "      <td>M</td>\n",
       "      <td>248.50</td>\n",
       "      <td>96.86</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2012</td>\n",
       "      <td>F</td>\n",
       "      <td>96.16</td>\n",
       "      <td>69.72</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2012</td>\n",
       "      <td>M</td>\n",
       "      <td>252.28</td>\n",
       "      <td>91.45</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2013</td>\n",
       "      <td>F</td>\n",
       "      <td>93.22</td>\n",
       "      <td>66.84</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   country  year sex  Preventable mortality  Treatable mortality  \\\n",
       "0  Austria  2011   F                  95.29                67.41   \n",
       "1  Austria  2011   M                 248.50                96.86   \n",
       "2  Austria  2012   F                  96.16                69.72   \n",
       "3  Austria  2012   M                 252.28                91.45   \n",
       "4  Austria  2013   F                  93.22                66.84   \n",
       "\n",
       "   (mean/median income, QU1)  (mean/median income, QU2)  \\\n",
       "0                        NaN                        NaN   \n",
       "1                        NaN                        NaN   \n",
       "2                        NaN                        NaN   \n",
       "3                        NaN                        NaN   \n",
       "4                        NaN                        NaN   \n",
       "\n",
       "   (mean/median income, QU3)  (mean/median income, QU4)  \\\n",
       "0                        NaN                        NaN   \n",
       "1                        NaN                        NaN   \n",
       "2                        NaN                        NaN   \n",
       "3                        NaN                        NaN   \n",
       "4                        NaN                        NaN   \n",
       "\n",
       "   (mean/median income, QU5)  \n",
       "0                        NaN  \n",
       "1                        NaN  \n",
       "2                        NaN  \n",
       "3                        NaN  \n",
       "4                        NaN  "
      ]
     },
     "execution_count": 307,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df = df.merge(df_n.reset_index(), on=['country', 'year'], how='left')\n",
    "merged_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>freq</th>\n",
       "      <th>unit</th>\n",
       "      <th>frequenc</th>\n",
       "      <th>isced11</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>2014</th>\n",
       "      <th>2019</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>PC</td>\n",
       "      <td>GE1W</td>\n",
       "      <td>ED0-2</td>\n",
       "      <td>F</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0.7</td>\n",
       "      <td>0.9</td>\n",
       "      <td>Austria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>PC</td>\n",
       "      <td>GE1W</td>\n",
       "      <td>ED0-2</td>\n",
       "      <td>F</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>2.3</td>\n",
       "      <td>2.9</td>\n",
       "      <td>Belgium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>PC</td>\n",
       "      <td>GE1W</td>\n",
       "      <td>ED0-2</td>\n",
       "      <td>F</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.4</td>\n",
       "      <td>Bulgaria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>PC</td>\n",
       "      <td>GE1W</td>\n",
       "      <td>ED0-2</td>\n",
       "      <td>F</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Cyprus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>PC</td>\n",
       "      <td>GE1W</td>\n",
       "      <td>ED0-2</td>\n",
       "      <td>F</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>1.1</td>\n",
       "      <td>0.3</td>\n",
       "      <td>Czechia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  freq unit frequenc isced11 sex    age  2014  2019   country\n",
       "0    A   PC     GE1W   ED0-2   F  TOTAL   0.7   0.9   Austria\n",
       "1    A   PC     GE1W   ED0-2   F  TOTAL   2.3   2.9   Belgium\n",
       "2    A   PC     GE1W   ED0-2   F  TOTAL   0.4   0.4  Bulgaria\n",
       "3    A   PC     GE1W   ED0-2   F  TOTAL   0.0   0.0    Cyprus\n",
       "4    A   PC     GE1W   ED0-2   F  TOTAL   1.1   0.3   Czechia"
      ]
     },
     "execution_count": 309,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df_n = eurostat.get_data_df('hlth_ehis_al3e') # ALCOHOL DATA\n",
    "df_n.rename({'geo\\TIME_PERIOD':'geo'},inplace=True,axis=1)\n",
    "\n",
    "# Frequency of heavy episodic drinking by sex, age and educational attainment level\n",
    "\n",
    "# df_n.drop(['unit','quant_expn','quant_wlth','indic_il','statinfo', '2010'],axis=1,inplace=True)\n",
    "df_n['country'] = df_n['geo'].replace(eurostat_dictionary)\n",
    "\n",
    "df_n.drop(['geo'],axis=1,inplace=True)\n",
    "\n",
    "# df_n = pd.melt(df_n,id_vars=['country','quant_inc'],\n",
    "                        # value_vars= df_n.columns[1:-1],\n",
    "                        # var_name='year',\n",
    "                        # value_name='mean/median income')\n",
    "\n",
    "# df_n.set_index(['country', 'year', 'quant_inc'], inplace=True)\n",
    "\n",
    "# df_n = df_n[df_n.index.duplicated(keep='last')] # removing duplicate indices\n",
    "\n",
    "# df_n.index.unique()\n",
    "\n",
    "# df_n = df_n.unstack(level='quant_inc')\n",
    "\n",
    "df_n.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Life style variables\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  },
  "org": null
 },
 "nbformat": 4,
 "nbformat_minor": 4
}