life_table.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# импортирую библиотеки\n",
    "import pandas as pd\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>birth_date</th>\n",
       "      <th>sex</th>\n",
       "      <th>nationality</th>\n",
       "      <th>citizenship</th>\n",
       "      <th>death_date</th>\n",
       "      <th>life_status</th>\n",
       "      <th>birth_country</th>\n",
       "      <th>birth_oblast</th>\n",
       "      <th>birth_rayon</th>\n",
       "      <th>birth_town</th>\n",
       "      <th>address_country</th>\n",
       "      <th>address_oblast</th>\n",
       "      <th>address_rayon</th>\n",
       "      <th>address_town</th>\n",
       "      <th>address_temp_country</th>\n",
       "      <th>address_temp_oblast</th>\n",
       "      <th>address_temp_rayon</th>\n",
       "      <th>address_temp_town</th>\n",
       "      <th>work_status</th>\n",
       "      <th>lifetime</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>iin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>0</th>\n",
       "      <td>1937-07-17</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-12-06</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>30458.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1958-09-18</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2002-02-18</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>15859.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1955-01-20</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-05-16</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>24223.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004-06-18</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>NaT</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1927-02-18</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1992-02-14</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>23737.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499995</th>\n",
       "      <td>1990-01-19</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499996</th>\n",
       "      <td>1921-06-22</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1949-10-02</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>15</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>15</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>15</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>10329.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499997</th>\n",
       "      <td>1960-10-28</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010-03-22</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>19</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>19</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>19</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>18042.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499998</th>\n",
       "      <td>1966-10-18</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499999</th>\n",
       "      <td>1931-02-07</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1977-04-27</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>16881.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>500000 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       birth_date  sex  nationality  citizenship death_date  life_status  \\\n",
       "iin                                                                        \n",
       "0      1937-07-17    2            3            1 2020-12-06            2   \n",
       "1      1958-09-18    1            1            1 2002-02-18            2   \n",
       "2      1955-01-20    1            1            1 2021-05-16            2   \n",
       "3      2004-06-18    1            1            3        NaT            1   \n",
       "4      1927-02-18    1            1            1 1992-02-14            2   \n",
       "...           ...  ...          ...          ...        ...          ...   \n",
       "499995 1990-01-19    2            1            1        NaT            1   \n",
       "499996 1921-06-22    1            2            3 1949-10-02            2   \n",
       "499997 1960-10-28    1            1            1 2010-03-22            2   \n",
       "499998 1966-10-18    1            2            1        NaT            1   \n",
       "499999 1931-02-07    2            1            1 1977-04-27            2   \n",
       "\n",
       "        birth_country  birth_oblast  birth_rayon  birth_town  address_country  \\\n",
       "iin                                                                             \n",
       "0                   1            12           15           3                1   \n",
       "1                   1             5            9           1                1   \n",
       "2                   1            13            6           6                1   \n",
       "3                   1             3            7           8                1   \n",
       "4                   3             6           13           2                3   \n",
       "...               ...           ...          ...         ...              ...   \n",
       "499995              1             7           18           6                1   \n",
       "499996              1            11           15           9                1   \n",
       "499997              1             7           19           6                1   \n",
       "499998              1             3            3           0                1   \n",
       "499999              2             5           16           5                2   \n",
       "\n",
       "        address_oblast  address_rayon  address_town  address_temp_country  \\\n",
       "iin                                                                         \n",
       "0                   12             15             3                     1   \n",
       "1                    5              9             1                     1   \n",
       "2                   13              6             6                     1   \n",
       "3                    3              7             8                     1   \n",
       "4                    6             13             2                     3   \n",
       "...                ...            ...           ...                   ...   \n",
       "499995               7             18             6                     1   \n",
       "499996              11             15             9                     1   \n",
       "499997               7             19             6                     1   \n",
       "499998               3              3             0                     1   \n",
       "499999               5             16             5                     2   \n",
       "\n",
       "        address_temp_oblast  address_temp_rayon  address_temp_town  \\\n",
       "iin                                                                  \n",
       "0                        12                  15                  3   \n",
       "1                         5                   9                  1   \n",
       "2                        13                   6                  6   \n",
       "3                         3                   7                  8   \n",
       "4                         6                  13                  2   \n",
       "...                     ...                 ...                ...   \n",
       "499995                    7                  18                  6   \n",
       "499996                   11                  15                  9   \n",
       "499997                    7                  19                  6   \n",
       "499998                    3                   3                  0   \n",
       "499999                    5                  16                  5   \n",
       "\n",
       "        work_status  lifetime  \n",
       "iin                            \n",
       "0                 1   30458.0  \n",
       "1                 1   15859.0  \n",
       "2                 2   24223.0  \n",
       "3                 2       NaN  \n",
       "4                 2   23737.0  \n",
       "...             ...       ...  \n",
       "499995            2       NaN  \n",
       "499996            1   10329.0  \n",
       "499997            2   18042.0  \n",
       "499998            1       NaN  \n",
       "499999            1   16881.0  \n",
       "\n",
       "[500000 rows x 20 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# загружаю ГБД ФЛ в датафрейм\n",
    "df = pd.read_csv('data/inter/gbd_fl.csv', index_col=0, parse_dates=['birth_date', 'death_date'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ожидаемая продолжительность жизни\n",
    "\n",
    "Показатели таблицы смертности представляют систему упорядоченных по возрасту и взаимосвязанных между собой рядов чисел, организованных как описание процесса уменьшения с возрастом под действием смертности некоторого теоретического поколения с фиксированной начальной численностью. Таблицы смертности строятся для оценки показателя «Ожидаемая продолжительность жизни».\n",
    "\n",
    "Различают полные и краткие таблицы смертности. В полных таблицах смертности возраст принимает все целые значения, шаг изменения возраста - 1 год от 0 до 100 лет. В кратких таблицах смертности возраст меняется с шагом 5, с выделением первых пяти лет жизни погодно: 0, 1, 2, 3, 4, 5-9, 10-14, ..., 80-84, 85 лет и старше или 0, 1-4, 5-9, 10-14, ..., 80-84, 85 лет и старше. \n",
    "\n",
    "В качестве исходных статистических данных для расчета таблиц смертности служат возрастные коэффициенты смертности, которые рассчитываются как отношение числа умерших в данном возрасте в течение календарного года к среднегодовой численности лиц данного возраста:\n",
    "\n",
    "$$m_{x, n} = \\frac{M_{x, n}}{P_{x, n}},$$\n",
    "\n",
    "где:\n",
    "- $m_{x, n}$ – коэффициент смертности в интервале возрастов $[x, x+n)$;\n",
    "- $M_{x, n}$ – число умерших в интервале возрастов $[x, x+n)$;\n",
    "- $P_{x, n}$ – средняя численность населения в интервале возрастов $[x, x+n)$.\n",
    "\n",
    "Далее считаются вероятности смертей:\n",
    "\n",
    "$$q_{x, n} = \\frac{n \\cdot m_{x, n}}{1 + (n - a_{x, n}) \\cdot m_{x, n}},$$\n",
    "\n",
    "где:\n",
    "- $q_{x, n}$ – вероятность смерти в интервале возрастов $[x, x+n)$;\n",
    "- $n$ – интервал возраста;\n",
    "- $m_{x, n}$ – коэффициент смертности в интервале возрастов $[x, x+n)$;\n",
    "- $a_{x, n}$ – среднее число человеко-лет, прожитых в интервале возрастов $[x, x+n)$.\n",
    "\n",
    "Каждое $q_{x, n}$ представляет собой вероятность того, что человек, достигший точного возраста $x$ лет, не доживет до возраста $x+n$ лет. На основе полученных вероятностей рассчитываются все остальные показатели таблицы смертности.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def death_probability(df, x, n, year):\n",
    "    \"\"\"\n",
    "    Входные данные:\n",
    "        df: датафрейм с физлицами\n",
    "        x: начало интервала возраста\n",
    "        n: длина интервала возраста\n",
    "        year: год для которого рассчитывается статистика\n",
    "    Выходные данные:\n",
    "        q: вероятность смерти в интервале возрастов [x; x+n)\n",
    "        m: коэффициент смертности в интервале возрастов [x; x+n)\n",
    "    \"\"\"\n",
    "    \n",
    "    def population_by_age(month, day):\n",
    "        \"\"\"\n",
    "        Входные данные:\n",
    "            month, day: месяц и день\n",
    "        Выходные данные:\n",
    "            H: число людей возраст которых был в интервале [x, x+n)\n",
    "            в момент времени year-month-day\n",
    "        \"\"\"\n",
    "        H = df[(df.birth_date <= pd.Timestamp(year - x, month, day)) &\n",
    "               (df.birth_date >  pd.Timestamp(year - x - n, month, day)) &\n",
    "              ((df.death_date >  pd.Timestamp(year, month, day)) | \n",
    "               (df.life_status == 1))].shape[0]\n",
    "        return H\n",
    "\n",
    "    # средняя численность населения в интервале возрастов [x; x+n)\n",
    "    P = (population_by_age(1, 1) + \\\n",
    "         population_by_age(12, 31)) / 2\n",
    "    \n",
    "    if P == 0: return 1, 1\n",
    "    \n",
    "    # люди умершие в год year\n",
    "    df_died = df[df.death_date.dt.year == year]\n",
    "    \n",
    "    # люди умершие в интервале [x; x+n) в год year\n",
    "    df_died_int = df_died[\n",
    "        ((df_died.lifetime >= int(x * 365.25))) &\n",
    "        ((df_died.lifetime < int((x + n) * 365.25)))\n",
    "    ]\n",
    "    \n",
    "    # число умерших в интервале [x; x+n) в год year\n",
    "    M = df_died_int.shape[0]\n",
    "    if M == 0: return 0, 1\n",
    "    \n",
    "    # коэффициент смертности в интервале возрастов [x; x+n)\n",
    "    m = M / P\n",
    "    \n",
    "    # среднее число человеко-лет, прожитых в интервале возрастов [x; x+n)\n",
    "    a = df_died_int.lifetime.mean() / 365.25 - x\n",
    "    \n",
    "    # вероятность смерти в интервале возрастов [x; x+n)\n",
    "    q = (n * m) / (1 + (n - a) * m)\n",
    "    \n",
    "    return q, m"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Посчитаем число доживших и умерших в наших интервалах возрастов. Первоначальной численностью поколения (корень таблицы) $l_0$ принимается за $100, 000$.\n",
    "\n",
    "$$l_{x + n} = l_x \\cdot (1 - q_{x, n})= l_x - d_{x, n},$$\n",
    "\n",
    "$$d_{x, n} = l_x - l_{x+n} = l_x \\cdot q_{x, n},$$\n",
    "\n",
    "$$L_{x, n} = \\frac{d_{x, n}}{m_{x, n}},$$\n",
    "\n",
    "где:\n",
    "- $l_x$ – число доживших до точного возраста $x$ из начальной численности когорты;\n",
    "- $q_{x, n}$ – вероятность смерти в интервале возрастов $[x; x+n)$;\n",
    "- $d_{x, n}$ – число умерших в интервале возрастов $[x; x+n)$;\n",
    "- $L_{x, n}$ – число живущих в интервале возрастов от $[x; x+n)$.\n",
    "\n",
    "Далее считаем число человеко-лет жизни:\n",
    "\n",
    "$$T_{x, n} = \\sum_{x=0}^{\\mathrm{end\\ of\\ table}} L_{x, n},$$\n",
    "где:\n",
    "- $T_x$ – число человеко-лет жизни в возрастах $x$ лет и старше.\n",
    "\n",
    "Ожидаемая продолжительность жизни, достигших возраста $х$ лет представляет число лет, которое предстоит прожить достигшим данного возраста при сохранении в каждом следующем возрасте современного уровня смертности.\n",
    "\n",
    "$$e_x^0 = \\frac{T_x}{l_x},$$\n",
    "где:\n",
    "- $e_x^0$ – ожидаемая продолжительность жизни, достигших возраста $х$ лет.\n",
    "\n",
    "Ожидаемая продолжительность жизни при рождении ($e_0^0$) представляет число лет, которое в среднем предстоит прожить одному человеку из поколения родившихся при условии, что на протяжении всей жизни этого поколения уровень смертности в каждом возрасте останется таким, как в год для которых вычислен показатель."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>m</th>\n",
       "      <th>q</th>\n",
       "      <th>l</th>\n",
       "      <th>d</th>\n",
       "      <th>L</th>\n",
       "      <th>T</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>int</th>\n",
       "      <th></th>\n",
       "      <th></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>&lt;1</th>\n",
       "      <td>0.040519</td>\n",
       "      <td>0.039546</td>\n",
       "      <td>100000</td>\n",
       "      <td>3955</td>\n",
       "      <td>97608</td>\n",
       "      <td>5657832</td>\n",
       "      <td>56.578320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.014774</td>\n",
       "      <td>0.014651</td>\n",
       "      <td>96045</td>\n",
       "      <td>1408</td>\n",
       "      <td>95305</td>\n",
       "      <td>5560224</td>\n",
       "      <td>57.891863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.003022</td>\n",
       "      <td>0.003019</td>\n",
       "      <td>94637</td>\n",
       "      <td>286</td>\n",
       "      <td>94635</td>\n",
       "      <td>5464919</td>\n",
       "      <td>57.746114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.003688</td>\n",
       "      <td>0.003682</td>\n",
       "      <td>94351</td>\n",
       "      <td>348</td>\n",
       "      <td>94359</td>\n",
       "      <td>5370284</td>\n",
       "      <td>56.918146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.005457</td>\n",
       "      <td>0.005445</td>\n",
       "      <td>94003</td>\n",
       "      <td>512</td>\n",
       "      <td>93829</td>\n",
       "      <td>5275925</td>\n",
       "      <td>56.125070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5-10</th>\n",
       "      <td>0.003418</td>\n",
       "      <td>0.016948</td>\n",
       "      <td>93491</td>\n",
       "      <td>1585</td>\n",
       "      <td>463715</td>\n",
       "      <td>5182096</td>\n",
       "      <td>55.428822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10-15</th>\n",
       "      <td>0.004057</td>\n",
       "      <td>0.020101</td>\n",
       "      <td>91906</td>\n",
       "      <td>1848</td>\n",
       "      <td>455490</td>\n",
       "      <td>4718381</td>\n",
       "      <td>51.339205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15-20</th>\n",
       "      <td>0.004495</td>\n",
       "      <td>0.022250</td>\n",
       "      <td>90058</td>\n",
       "      <td>2004</td>\n",
       "      <td>445829</td>\n",
       "      <td>4262891</td>\n",
       "      <td>47.334951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20-25</th>\n",
       "      <td>0.004025</td>\n",
       "      <td>0.019924</td>\n",
       "      <td>88054</td>\n",
       "      <td>1755</td>\n",
       "      <td>436045</td>\n",
       "      <td>3817062</td>\n",
       "      <td>43.349104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25-30</th>\n",
       "      <td>0.005454</td>\n",
       "      <td>0.026911</td>\n",
       "      <td>86299</td>\n",
       "      <td>2323</td>\n",
       "      <td>425941</td>\n",
       "      <td>3381017</td>\n",
       "      <td>39.177939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30-35</th>\n",
       "      <td>0.004054</td>\n",
       "      <td>0.020080</td>\n",
       "      <td>83976</td>\n",
       "      <td>1687</td>\n",
       "      <td>416082</td>\n",
       "      <td>2955076</td>\n",
       "      <td>35.189530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-40</th>\n",
       "      <td>0.006326</td>\n",
       "      <td>0.031158</td>\n",
       "      <td>82289</td>\n",
       "      <td>2564</td>\n",
       "      <td>405327</td>\n",
       "      <td>2538994</td>\n",
       "      <td>30.854598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40-45</th>\n",
       "      <td>0.020388</td>\n",
       "      <td>0.097169</td>\n",
       "      <td>79725</td>\n",
       "      <td>7747</td>\n",
       "      <td>379971</td>\n",
       "      <td>2133667</td>\n",
       "      <td>26.762835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45-50</th>\n",
       "      <td>0.020304</td>\n",
       "      <td>0.096665</td>\n",
       "      <td>71978</td>\n",
       "      <td>6958</td>\n",
       "      <td>342691</td>\n",
       "      <td>1753696</td>\n",
       "      <td>24.364334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50-55</th>\n",
       "      <td>0.023699</td>\n",
       "      <td>0.112255</td>\n",
       "      <td>65020</td>\n",
       "      <td>7299</td>\n",
       "      <td>307982</td>\n",
       "      <td>1411005</td>\n",
       "      <td>21.701092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55-60</th>\n",
       "      <td>0.028337</td>\n",
       "      <td>0.132396</td>\n",
       "      <td>57721</td>\n",
       "      <td>7643</td>\n",
       "      <td>269715</td>\n",
       "      <td>1103023</td>\n",
       "      <td>19.109562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60-65</th>\n",
       "      <td>0.032719</td>\n",
       "      <td>0.151694</td>\n",
       "      <td>50078</td>\n",
       "      <td>7597</td>\n",
       "      <td>232188</td>\n",
       "      <td>833308</td>\n",
       "      <td>16.640201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65-70</th>\n",
       "      <td>0.040806</td>\n",
       "      <td>0.184888</td>\n",
       "      <td>42481</td>\n",
       "      <td>7855</td>\n",
       "      <td>192498</td>\n",
       "      <td>601120</td>\n",
       "      <td>14.150326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70-75</th>\n",
       "      <td>0.049404</td>\n",
       "      <td>0.219253</td>\n",
       "      <td>34626</td>\n",
       "      <td>7592</td>\n",
       "      <td>153671</td>\n",
       "      <td>408622</td>\n",
       "      <td>11.801017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75-80</th>\n",
       "      <td>0.064205</td>\n",
       "      <td>0.276425</td>\n",
       "      <td>27034</td>\n",
       "      <td>7473</td>\n",
       "      <td>116392</td>\n",
       "      <td>254951</td>\n",
       "      <td>9.430754</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80-85</th>\n",
       "      <td>0.103489</td>\n",
       "      <td>0.414040</td>\n",
       "      <td>19561</td>\n",
       "      <td>8100</td>\n",
       "      <td>78269</td>\n",
       "      <td>138559</td>\n",
       "      <td>7.083431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85-90</th>\n",
       "      <td>0.172309</td>\n",
       "      <td>0.587907</td>\n",
       "      <td>11461</td>\n",
       "      <td>6739</td>\n",
       "      <td>39109</td>\n",
       "      <td>60290</td>\n",
       "      <td>5.260448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90-95</th>\n",
       "      <td>0.072000</td>\n",
       "      <td>0.270360</td>\n",
       "      <td>4722</td>\n",
       "      <td>1277</td>\n",
       "      <td>17736</td>\n",
       "      <td>21181</td>\n",
       "      <td>4.485599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95-100</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3445</td>\n",
       "      <td>3445</td>\n",
       "      <td>3445</td>\n",
       "      <td>3445</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100+</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               m         q       l     d       L        T          e\n",
       "int                                                                 \n",
       "<1      0.040519  0.039546  100000  3955   97608  5657832  56.578320\n",
       "1       0.014774  0.014651   96045  1408   95305  5560224  57.891863\n",
       "2       0.003022  0.003019   94637   286   94635  5464919  57.746114\n",
       "3       0.003688  0.003682   94351   348   94359  5370284  56.918146\n",
       "4       0.005457  0.005445   94003   512   93829  5275925  56.125070\n",
       "5-10    0.003418  0.016948   93491  1585  463715  5182096  55.428822\n",
       "10-15   0.004057  0.020101   91906  1848  455490  4718381  51.339205\n",
       "15-20   0.004495  0.022250   90058  2004  445829  4262891  47.334951\n",
       "20-25   0.004025  0.019924   88054  1755  436045  3817062  43.349104\n",
       "25-30   0.005454  0.026911   86299  2323  425941  3381017  39.177939\n",
       "30-35   0.004054  0.020080   83976  1687  416082  2955076  35.189530\n",
       "35-40   0.006326  0.031158   82289  2564  405327  2538994  30.854598\n",
       "40-45   0.020388  0.097169   79725  7747  379971  2133667  26.762835\n",
       "45-50   0.020304  0.096665   71978  6958  342691  1753696  24.364334\n",
       "50-55   0.023699  0.112255   65020  7299  307982  1411005  21.701092\n",
       "55-60   0.028337  0.132396   57721  7643  269715  1103023  19.109562\n",
       "60-65   0.032719  0.151694   50078  7597  232188   833308  16.640201\n",
       "65-70   0.040806  0.184888   42481  7855  192498   601120  14.150326\n",
       "70-75   0.049404  0.219253   34626  7592  153671   408622  11.801017\n",
       "75-80   0.064205  0.276425   27034  7473  116392   254951   9.430754\n",
       "80-85   0.103489  0.414040   19561  8100   78269   138559   7.083431\n",
       "85-90   0.172309  0.587907   11461  6739   39109    60290   5.260448\n",
       "90-95   0.072000  0.270360    4722  1277   17736    21181   4.485599\n",
       "95-100  1.000000  1.000000    3445  3445    3445     3445   1.000000\n",
       "100+    1.000000  1.000000       0     0       0        0   0.000000"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def life_table(df, year, short=True):\n",
    "    \"\"\"\n",
    "    Входные данные:\n",
    "        df: датафрейм с людьми\n",
    "        year: год для счета таблицы смертности\n",
    "        short: выдать краткую или длинную таблицу\n",
    "    Выходные данные:\n",
    "        df_table: датафрейм с таблицей смертности\n",
    "    \"\"\"\n",
    "    table = {\n",
    "        'int':  [], 'm':    [],\n",
    "        'q':    [], 'l':    [int(1e5)],\n",
    "        'd':    [], 'L':    [],\n",
    "        'T':    [], 'e':    [],\n",
    "    }\n",
    "    \n",
    "    def helper(table, start_age, max_age, n):\n",
    "        for x in range(start_age, max_age, n):\n",
    "            q, m = death_probability(df, x, n, year)\n",
    "            if 0 < x < 100:\n",
    "                interval = f'{x}-{x + n}' if n > 1 else f'{x}'\n",
    "            else:\n",
    "                interval = '<1' if x == 0 else '100+'\n",
    "            table['int'].append(interval)\n",
    "            table['m'].append(m)\n",
    "            table['q'].append(q)\n",
    "            table['l'].append(int(table['l'][-1] * (1 - q)))\n",
    "            table['d'].append(table['l'][-2] - table['l'][-1])\n",
    "            table['L'].append(int(table['d'][-1] / m))\n",
    "        return table\n",
    "    \n",
    "    if short:\n",
    "        table = helper(table, 0, 5, 1)\n",
    "        table = helper(table, 5, 100, 5)\n",
    "    else:\n",
    "        table = helper(table, 0, 100, 1)\n",
    "    table = helper(table, 100, 200, 100)\n",
    "    \n",
    "    table['l'].pop(-1)\n",
    "    \n",
    "    table['T'] = [sum(table['L'])]\n",
    "    for l in table['L']:\n",
    "        table['T'].append(table['T'][-1] - l)\n",
    "    table['T'].pop(-1)\n",
    "    \n",
    "    table['e'] = [T / max(l, 1) for T, l in zip(table['T'], table['l'])]\n",
    "    \n",
    "    df_table = pd.DataFrame.from_dict(table) \\\n",
    "                 .set_index('int')\n",
    "    \n",
    "    return df_table\n",
    "\n",
    "life_table(df, 2010)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_life_tables(df, start_year, end_year):\n",
    "    years = range(start_year, end_year)\n",
    "    writer_short = pd.ExcelWriter(\n",
    "        f'results/life_tables/short.xlsx', engine='xlsxwriter'\n",
    "    )\n",
    "    writer_long = pd.ExcelWriter(\n",
    "        f'results/life_tables/long.xlsx', engine='xlsxwriter'\n",
    "    )\n",
    "    for year in tqdm(years):\n",
    "        # сохраняю краткую таблицу смертности в формате .xlsx\n",
    "        life_table(df, year).to_excel(writer_short, sheet_name=f'{year}')\n",
    "        # сохраняю длинную таблицу смертности в формате .xlsx\n",
    "        life_table(df, year, short=False).to_excel(writer_long, sheet_name=f'{year}')\n",
    "    writer_short.save(); writer_long.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 71/71 [06:05<00:00,  5.15s/it]\n"
     ]
    }
   ],
   "source": [
    "save_life_tables(df, 1950, 2021)"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "b38e66fadc0fcffc649cbdb5f7cd876385cffb3a29ce07f11308631a3c7cbd95"
  },
  "kernelspec": {
   "display_name": "Python 3.8.8 64-bit ('base': conda)",
   "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.8.8"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}