src/BD_w11_ExcessDeathAndCovidDeathPhase1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import random\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import math\n",
    "import json\n",
    "import matplotlib.pyplot as plt\n",
    "from pandas import Timestamp\n",
    "from datetime import datetime\n",
    "from time import time\n",
    "from os import getcwd\n",
    "from os.path import join\n",
    "%matplotlib inline\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Motivation\n",
    "-------\n",
    "The reporting of COVID deaths may be be accurate for the following reason:\n",
    "- Most of the COVID deaths are associated to other sickness.  It is hard to say it is really casused by COVID.\n",
    "- Not all the deaths are diagonized for the real cause of the death.\n",
    "    - Some families of the deaths do not want to do the test.\n",
    "    - Test requires some additional cost.  This cost is not always justified, esp. in the situation of the shortage of the medical resources when there is serious pandemic or where medical resources is very poor.\n",
    "    \n",
    "Another way to determine COVID deaths is to find the excess deaths over the regular deaths in the time before COVID pandemic.   Therefore, we need some kind of data covering a few years before COVID and years in COVID.  The data is available in https://github.com/akarlinsky/world_mortality/blob/main/world_mortality.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Understanding of the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  iso3c  country  year  time time_unit  deaths\n",
      "0   ALB  Albania  2015     1   monthly  2490.0\n",
      "1   ALB  Albania  2015     2   monthly  2139.0\n",
      "2   ALB  Albania  2015     3   monthly  2051.0\n",
      "3   ALB  Albania  2015     4   monthly  1906.0\n",
      "4   ALB  Albania  2015     5   monthly  1709.0\n",
      "5   ALB  Albania  2015     6   monthly  1561.0\n",
      "6   ALB  Albania  2015     7   monthly  2008.0\n",
      "7   ALB  Albania  2015     8   monthly  1687.0\n",
      "8   ALB  Albania  2015     9   monthly  1569.0\n",
      "9   ALB  Albania  2015    10   monthly  1560.0\n"
     ]
    }
   ],
   "source": [
    "# https://github.com/akarlinsky/world_mortality/blob/main/world_mortality.csv\n",
    "# https://github.com/akarlinsky/world_mortality\n",
    "path = join(getcwd().rstrip('src'), 'data/world_mortality.csv').replace('\\\\', '/')\n",
    "DF = pd.read_csv(path)\n",
    "# DF = pd.read_csv('~/AI/DATA/BigData/DeathBirthRate/world_mortality2015-20220214.csv')\n",
    "DF.rename(columns = {'country_name':'country'}, inplace=True)\n",
    "print(DF.head(10))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1 find out how many countries are included"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# of countries included in the data is  122\n"
     ]
    }
   ],
   "source": [
    "AllCountries = set(DF.country)\n",
    "print(\"# of countries included in the data is \", len(AllCountries))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2015 2022\n"
     ]
    }
   ],
   "source": [
    "print(DF.year.min(), DF.year.max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "116"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# # of countries included in 2015\n",
    "len(set(DF[DF.year == 2015].country))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "116"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# # of countries included in 2015\n",
    "len(set(DF[DF.year == 2015].country))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "91"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# # of countries included in 2022\n",
    "len(set(DF[DF.year == 2022].country))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "111"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# # of countries included in 2021\n",
    "len(set(DF[DF.year == 2021].country))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "122"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# # of countries included in 2020\n",
    "len(set(DF[DF.year == 2020].country))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2 Find out how many different time_unit\n",
    "---\n",
    "We have seen \"monthly\" time_unit.  Let us find out if there is any other time_unit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'monthly', 'weekly'}"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# # of countries included in 2020\n",
    "set(DF.time_unit)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.3 We have seen only two time_unit.  Let us find out if the values are complete for each country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{8, 12, 53}"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(DF[DF.year==2020].groupby(['country', 'time_unit']).year.count())\n",
    "# https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.count.html\n",
    "# can distinguish nan and non-nan"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Since the above return 8, 12 and 53 in the year of 2020.  It is probably that some countries with monthly time_unit do not have complete vaules for all months in 2020."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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></th>\n",
       "      <th>iso3c</th>\n",
       "      <th>year</th>\n",
       "      <th>time</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>time_unit</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Albania</th>\n",
       "      <th>monthly</th>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Algeria</th>\n",
       "      <th>monthly</th>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andorra</th>\n",
       "      <th>monthly</th>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Antigua and Barbuda</th>\n",
       "      <th>monthly</th>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Argentina</th>\n",
       "      <th>monthly</th>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               iso3c  year  time  deaths\n",
       "country             time_unit                           \n",
       "Albania             monthly       12    12    12      12\n",
       "Algeria             monthly       12    12    12      12\n",
       "Andorra             monthly       12    12    12      12\n",
       "Antigua and Barbuda monthly       12    12    12      12\n",
       "Argentina           monthly       12    12    12      12"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DF[DF.year==2020].groupby(['country', 'time_unit']).count().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>time_unit</th>\n",
       "      <th>iso3c</th>\n",
       "      <th>year</th>\n",
       "      <th>time</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>monthly</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>monthly</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>monthly</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>monthly</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>monthly</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               country time_unit  iso3c  year  time  deaths\n",
       "0              Albania   monthly     12    12    12      12\n",
       "1              Algeria   monthly     12    12    12      12\n",
       "2              Andorra   monthly     12    12    12      12\n",
       "3  Antigua and Barbuda   monthly     12    12    12      12\n",
       "4            Argentina   monthly     12    12    12      12"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp = DF[DF.year==2020].groupby(['country', 'time_unit']).count()\n",
    "tmp = tmp.reset_index()\n",
    "tmp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>time_unit</th>\n",
       "      <th>iso3c</th>\n",
       "      <th>year</th>\n",
       "      <th>time</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>El Salvador</td>\n",
       "      <td>monthly</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        country time_unit  iso3c  year  time  deaths\n",
       "34  El Salvador   monthly      8     8     8       8"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The following shows that there is only one country does not have complete list for the year of 2020\n",
    "tmp[tmp.year == 8]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Find average annual excess deaths for the years of 2020 and 2021\n",
    "------\n",
    "Since 2022 is not finished yet and there is only 90 countries have values in 2022 (even though it is not complete yet), let us focus on the average annual excess deaths for 2020 and 2021.\n",
    "The simplest way to find the excess deaths is:\n",
    "1. find the average annual deaths before 2020, i.e. years from 2015 to 2019.  This is the regualr deaths.\n",
    "- find the average annual deaths for 2020 and 2021. This is the deaths from all causes.\n",
    "- The difference between the deaths from all causes and the regular deaths is the excess deaths.\n",
    "- If there is no major event for large scale deaths, then excess deaths should be COVID deaths\n",
    "- Examples of large scale deaths are big natural disasters like earthquake, tsunami, pandemic, or pandemic and war.  There are no such incidents in 2020 and 2021 other than COVID.   Therefore, this excess deaths must be caused by COVID pandemic.  Ukraine war occurred in 2022."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_2015_2019 = DF[DF.year < 2020]\n",
    "DF_2020_2021 = DF[(DF.year == 2020) | (DF.year==2021)]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                country         year       time         deaths\n",
      "0               Albania  2017.000000   6.500000    1829.650000\n",
      "1               Algeria  2018.500000   6.500000   14940.804167\n",
      "2               Andorra  2017.000000   6.500000      25.850000\n",
      "3   Antigua and Barbuda  2017.000000   6.500000      47.550000\n",
      "4             Argentina  2017.000000   6.500000   28449.050000\n",
      "5               Armenia  2017.000000   6.500000    2253.950000\n",
      "6                 Aruba  2017.000000   6.500000      59.150000\n",
      "7             Australia  2016.992337  26.601533    3084.199234\n",
      "8               Austria  2016.992337  26.601533    1558.122605\n",
      "9            Azerbaijan  2017.000000   6.500000    4702.566667\n",
      "10              Bahamas  2017.000000   6.500000     202.900000\n",
      "11             Barbados  2017.000000   6.500000     220.216667\n",
      "12              Belarus  2017.000000   6.500000    9994.283333\n",
      "13              Belgium  2016.992337  26.601533    2099.601533\n",
      "14               Belize  2017.000000   6.500000     158.000000\n",
      "15              Bermuda  2017.000000   6.500000      41.983333\n",
      "16              Bolivia  2017.000000   6.500000    4217.733333\n",
      "17               Bosnia  2017.000000   6.500000    3074.866667\n",
      "18               Brazil  2017.000000   6.500000  109218.866667\n",
      "19               Brunei  2017.000000   6.500000     137.400000\n",
      "20             Bulgaria  2016.992337  26.601533    2086.164751\n",
      "21           Cabo Verde  2017.000000   6.500000     224.266667\n",
      "22               Canada  2016.992337  26.601533    5295.440613\n",
      "23                Chile  2017.500000  26.500000    2044.355769\n",
      "24             Colombia  2016.992337  26.601533    4378.076628\n",
      "25           Costa Rica  2017.000000   6.500000    1915.650000\n",
      "26              Croatia  2016.992337  26.601533    1010.865900\n",
      "27                 Cuba  2017.000000   6.500000    8688.550000\n",
      "28               Cyprus  2016.992337  26.601533     112.858238\n",
      "29              Czechia  2016.992337  26.601533    2130.505747\n"
     ]
    }
   ],
   "source": [
    "Regular = DF_2015_2019.groupby('country').mean().reset_index()\n",
    "print(Regular.head(30))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "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>iso3c</th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>time</th>\n",
       "      <th>time_unit</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>531</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2925.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>532</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2772.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>533</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2015</td>\n",
       "      <td>3</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2770.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>534</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2015</td>\n",
       "      <td>4</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2768.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>535</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2015</td>\n",
       "      <td>5</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2680.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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>787</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2019</td>\n",
       "      <td>48</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2986.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>788</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2019</td>\n",
       "      <td>49</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2958.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>789</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2019</td>\n",
       "      <td>50</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2994.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>790</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2019</td>\n",
       "      <td>51</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2950.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>791</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2019</td>\n",
       "      <td>52</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2881.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>261 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    iso3c    country  year  time time_unit  deaths\n",
       "531   AUS  Australia  2015     1    weekly  2925.0\n",
       "532   AUS  Australia  2015     2    weekly  2772.0\n",
       "533   AUS  Australia  2015     3    weekly  2770.0\n",
       "534   AUS  Australia  2015     4    weekly  2768.0\n",
       "535   AUS  Australia  2015     5    weekly  2680.0\n",
       "..    ...        ...   ...   ...       ...     ...\n",
       "787   AUS  Australia  2019    48    weekly  2986.0\n",
       "788   AUS  Australia  2019    49    weekly  2958.0\n",
       "789   AUS  Australia  2019    50    weekly  2994.0\n",
       "790   AUS  Australia  2019    51    weekly  2950.0\n",
       "791   AUS  Australia  2019    52    weekly  2881.0\n",
       "\n",
       "[261 rows x 6 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DF[(DF.country == 'Australia') & (DF.year < 2020)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": true
   },
   "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>iso3c</th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>time</th>\n",
       "      <th>time_unit</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>582</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2015</td>\n",
       "      <td>52</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2715.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>583</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2015</td>\n",
       "      <td>53</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2819.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>635</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2016</td>\n",
       "      <td>52</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2919.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>687</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2017</td>\n",
       "      <td>52</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2775.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>739</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2018</td>\n",
       "      <td>52</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2945.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>791</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2019</td>\n",
       "      <td>52</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2881.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>843</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2020</td>\n",
       "      <td>52</td>\n",
       "      <td>weekly</td>\n",
       "      <td>3067.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>844</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2020</td>\n",
       "      <td>53</td>\n",
       "      <td>weekly</td>\n",
       "      <td>2987.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>896</th>\n",
       "      <td>AUS</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2021</td>\n",
       "      <td>52</td>\n",
       "      <td>weekly</td>\n",
       "      <td>3165.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    iso3c    country  year  time time_unit  deaths\n",
       "582   AUS  Australia  2015    52    weekly  2715.0\n",
       "583   AUS  Australia  2015    53    weekly  2819.0\n",
       "635   AUS  Australia  2016    52    weekly  2919.0\n",
       "687   AUS  Australia  2017    52    weekly  2775.0\n",
       "739   AUS  Australia  2018    52    weekly  2945.0\n",
       "791   AUS  Australia  2019    52    weekly  2881.0\n",
       "843   AUS  Australia  2020    52    weekly  3067.0\n",
       "844   AUS  Australia  2020    53    weekly  2987.0\n",
       "896   AUS  Australia  2021    52    weekly  3165.0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DF[(DF.country == 'Australia') & (DF.year < 2022) & (DF.time >= 52)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### It looks like that:\n",
    "- when the average of time is 6.5 means that it has complete year from month 1 to month 12 so that the average is 6.5.   The annual average deaths should be 12X of this average.\n",
    "- when the average of time is 26.60 means that it has complete year from week 1 to week 52 or 53 so that the average is 26.60.  If all year has 52 weeks, this value should be 26.5.  Let us assume there is alway 52 weeks for simplicity.  The annual average deaths should be very close to 52X of this average.\n",
    "\n",
    "Let us add one column \"AverageAnnualUnitCount\" to represent the actual number of values in the data for the years we are working on.  The value can help us to know how many values of time_unit we have in the data.   Even though the annual average can be obtained by just the average in the specified time_unit multiplied by 12 or 52 even if some countries do not have complete values for a whole year.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "Regular['AverageAnnualUnitCount'] = round(Regular.time * 2 - 1, 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>time</th>\n",
       "      <th>deaths</th>\n",
       "      <th>AverageAnnualUnitCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>1829.650000</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2018.5</td>\n",
       "      <td>6.5</td>\n",
       "      <td>14940.804167</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>25.850000</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>47.550000</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>28449.050000</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               country    year  time        deaths  AverageAnnualUnitCount\n",
       "0              Albania  2017.0   6.5   1829.650000                    12.0\n",
       "1              Algeria  2018.5   6.5  14940.804167                    12.0\n",
       "2              Andorra  2017.0   6.5     25.850000                    12.0\n",
       "3  Antigua and Barbuda  2017.0   6.5     47.550000                    12.0\n",
       "4            Argentina  2017.0   6.5  28449.050000                    12.0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Regular.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The above Regular DataFrame does not have \"time_unit\"  because it is not numerical and is deleted when we obtain mean or count.  However, it is still better to know what kind of time_unit each country is reporting.   This time_unit can be insert back to Regular by merge method in Pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>iso3c</th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>time</th>\n",
       "      <th>time_unit</th>\n",
       "      <th>deaths</th>\n",
       "      <th>AnnualUnitCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALB</td>\n",
       "      <td>Albania</td>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>monthly</td>\n",
       "      <td>2490.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALB</td>\n",
       "      <td>Albania</td>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>monthly</td>\n",
       "      <td>2139.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ALB</td>\n",
       "      <td>Albania</td>\n",
       "      <td>2015</td>\n",
       "      <td>3</td>\n",
       "      <td>monthly</td>\n",
       "      <td>2051.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ALB</td>\n",
       "      <td>Albania</td>\n",
       "      <td>2015</td>\n",
       "      <td>4</td>\n",
       "      <td>monthly</td>\n",
       "      <td>1906.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ALB</td>\n",
       "      <td>Albania</td>\n",
       "      <td>2015</td>\n",
       "      <td>5</td>\n",
       "      <td>monthly</td>\n",
       "      <td>1709.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  iso3c  country  year  time time_unit  deaths  AnnualUnitCount\n",
       "0   ALB  Albania  2015     1   monthly  2490.0               12\n",
       "1   ALB  Albania  2015     2   monthly  2139.0               12\n",
       "2   ALB  Albania  2015     3   monthly  2051.0               12\n",
       "3   ALB  Albania  2015     4   monthly  1906.0               12\n",
       "4   ALB  Albania  2015     5   monthly  1709.0               12"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# obtain time_unit for each country.  We can obtain this value just once by specifying \n",
    "\n",
    "import copy as copy \n",
    "tmp = copy.copy(DF)\n",
    "# tmp['time_unit'] = [ 12 for x in tmp.time_unit if x == 'monthly' else 52 ]\n",
    "time_unit_dict = {'monthly': 12, 'weekly': 52}\n",
    "tmp['AnnualUnitCount'] = [time_unit_dict[x] for x in tmp.time_unit ]\n",
    "tmp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>AnnualUnitCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               country  AnnualUnitCount\n",
       "0              Albania             12.0\n",
       "1              Algeria             12.0\n",
       "2              Andorra             12.0\n",
       "3  Antigua and Barbuda             12.0\n",
       "4            Argentina             12.0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We only need one value of AnnualUnitCount for each country.  A quick way to get it is \n",
    "tmp = tmp.groupby('country').mean().reset_index()[['country', 'AnnualUnitCount']]\n",
    "tmp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>time</th>\n",
       "      <th>deaths</th>\n",
       "      <th>AverageAnnualUnitCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>1829.650000</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2018.5</td>\n",
       "      <td>6.5</td>\n",
       "      <td>14940.804167</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>25.850000</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>47.550000</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>28449.050000</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               country    year  time        deaths  AverageAnnualUnitCount\n",
       "0              Albania  2017.0   6.5   1829.650000                    12.0\n",
       "1              Algeria  2018.5   6.5  14940.804167                    12.0\n",
       "2              Andorra  2017.0   6.5     25.850000                    12.0\n",
       "3  Antigua and Barbuda  2017.0   6.5     47.550000                    12.0\n",
       "4            Argentina  2017.0   6.5  28449.050000                    12.0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Regular.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": false
   },
   "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>time</th>\n",
       "      <th>deaths</th>\n",
       "      <th>AverageAnnualUnitCount</th>\n",
       "      <th>AnnualUnitCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>1829.650000</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2018.5</td>\n",
       "      <td>6.5</td>\n",
       "      <td>14940.804167</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>25.850000</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>47.550000</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>28449.050000</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               country    year  time        deaths  AverageAnnualUnitCount  \\\n",
       "0              Albania  2017.0   6.5   1829.650000                    12.0   \n",
       "1              Algeria  2018.5   6.5  14940.804167                    12.0   \n",
       "2              Andorra  2017.0   6.5     25.850000                    12.0   \n",
       "3  Antigua and Barbuda  2017.0   6.5     47.550000                    12.0   \n",
       "4            Argentina  2017.0   6.5  28449.050000                    12.0   \n",
       "\n",
       "   AnnualUnitCount  \n",
       "0             12.0  \n",
       "1             12.0  \n",
       "2             12.0  \n",
       "3             12.0  \n",
       "4             12.0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Regular = pd.merge(Regular, tmp, on='country')\n",
    "Regular.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>time</th>\n",
       "      <th>deaths</th>\n",
       "      <th>AverageAnnualUnitCount</th>\n",
       "      <th>AnnualUnitCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>21955.80</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2018.5</td>\n",
       "      <td>6.5</td>\n",
       "      <td>179289.65</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>310.20</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>570.60</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>341388.60</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               country    year  time     deaths  AverageAnnualUnitCount  \\\n",
       "0              Albania  2017.0   6.5   21955.80                    12.0   \n",
       "1              Algeria  2018.5   6.5  179289.65                    12.0   \n",
       "2              Andorra  2017.0   6.5     310.20                    12.0   \n",
       "3  Antigua and Barbuda  2017.0   6.5     570.60                    12.0   \n",
       "4            Argentina  2017.0   6.5  341388.60                    12.0   \n",
       "\n",
       "   AnnualUnitCount  \n",
       "0             12.0  \n",
       "1             12.0  \n",
       "2             12.0  \n",
       "3             12.0  \n",
       "4             12.0  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# change deaths to annual deaths\n",
    "Regular['deaths'] = Regular.deaths * Regular.AnnualUnitCount\n",
    "Regular.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['country', 'year', 'time', 'deaths', 'AverageAnnualUnitCount',\n",
       "       'AnnualUnitCount'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Regular.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we don't need everything.  Just select some columns\n",
    "Regular = Regular[['country', 'deaths', 'AverageAnnualUnitCount']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>deaths</th>\n",
       "      <th>AverageAnnualUnitCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>21955.80</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>179289.65</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>310.20</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>570.60</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>341388.60</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               country     deaths  AverageAnnualUnitCount\n",
       "0              Albania   21955.80                    12.0\n",
       "1              Algeria  179289.65                    12.0\n",
       "2              Andorra     310.20                    12.0\n",
       "3  Antigua and Barbuda     570.60                    12.0\n",
       "4            Argentina  341388.60                    12.0"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Regular.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4. Now let us do the same process on DF_2020_2021  for AllCauses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": true
   },
   "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>deaths</th>\n",
       "      <th>AverageAnnualUnitCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>29092.5</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>235628.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>419.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>611.5</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>376221.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               country    deaths  AverageAnnualUnitCount\n",
       "0              Albania   29092.5                    12.0\n",
       "1              Algeria  235628.0                    12.0\n",
       "2              Andorra     419.0                    12.0\n",
       "3  Antigua and Barbuda     611.5                    12.0\n",
       "4            Argentina  376221.0                    12.0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Irregular = DF_2020_2021.groupby('country').mean().reset_index()\n",
    "Irregular['AverageAnnualUnitCount'] = round(Irregular.time * 2 - 1, 0)\n",
    "Irregular = pd.merge(Irregular, tmp, on='country')\n",
    "Irregular['deaths'] = Irregular.deaths * Irregular.AnnualUnitCount\n",
    "Irregular = Irregular[['country', 'deaths', 'AverageAnnualUnitCount']]\n",
    "Irregular.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "# 5. Combine Regular and AllCauses\n",
    "### The first deaths and AverageAnnualUnitCount are for Regular, the 2nd is for AllCauses\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>RegularDeaths</th>\n",
       "      <th>RegularAverageAnnualUnitCount</th>\n",
       "      <th>IrregularDeaths</th>\n",
       "      <th>IrregularAverageAnnualUnitCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>21955.80</td>\n",
       "      <td>12.0</td>\n",
       "      <td>29092.5</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>179289.65</td>\n",
       "      <td>12.0</td>\n",
       "      <td>235628.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>310.20</td>\n",
       "      <td>12.0</td>\n",
       "      <td>419.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>570.60</td>\n",
       "      <td>12.0</td>\n",
       "      <td>611.5</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>341388.60</td>\n",
       "      <td>12.0</td>\n",
       "      <td>376221.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               country  RegularDeaths  RegularAverageAnnualUnitCount  \\\n",
       "0              Albania       21955.80                           12.0   \n",
       "1              Algeria      179289.65                           12.0   \n",
       "2              Andorra         310.20                           12.0   \n",
       "3  Antigua and Barbuda         570.60                           12.0   \n",
       "4            Argentina      341388.60                           12.0   \n",
       "\n",
       "   IrregularDeaths  IrregularAverageAnnualUnitCount  \n",
       "0          29092.5                             12.0  \n",
       "1         235628.0                             12.0  \n",
       "2            419.0                             12.0  \n",
       "3            611.5                             12.0  \n",
       "4         376221.0                             12.0  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newDF = pd.concat([Regular, Irregular[['deaths', 'AverageAnnualUnitCount']]], axis=1)\n",
    "newDF.columns = ['country', 'RegularDeaths', 'RegularAverageAnnualUnitCount', 'IrregularDeaths', 'IrregularAverageAnnualUnitCount']\n",
    "newDF.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 6. Get excess deaths\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>RegularDeaths</th>\n",
       "      <th>RegularAverageAnnualUnitCount</th>\n",
       "      <th>IrregularDeaths</th>\n",
       "      <th>IrregularAverageAnnualUnitCount</th>\n",
       "      <th>ExcessDeaths</th>\n",
       "      <th>ExcessDeathRate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>21955.80</td>\n",
       "      <td>12.0</td>\n",
       "      <td>29092.5</td>\n",
       "      <td>12.0</td>\n",
       "      <td>7136.70</td>\n",
       "      <td>0.325049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>179289.65</td>\n",
       "      <td>12.0</td>\n",
       "      <td>235628.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>56338.35</td>\n",
       "      <td>0.314231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>310.20</td>\n",
       "      <td>12.0</td>\n",
       "      <td>419.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>108.80</td>\n",
       "      <td>0.350741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>570.60</td>\n",
       "      <td>12.0</td>\n",
       "      <td>611.5</td>\n",
       "      <td>12.0</td>\n",
       "      <td>40.90</td>\n",
       "      <td>0.071679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>341388.60</td>\n",
       "      <td>12.0</td>\n",
       "      <td>376221.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>34832.40</td>\n",
       "      <td>0.102032</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               country  RegularDeaths  RegularAverageAnnualUnitCount  \\\n",
       "0              Albania       21955.80                           12.0   \n",
       "1              Algeria      179289.65                           12.0   \n",
       "2              Andorra         310.20                           12.0   \n",
       "3  Antigua and Barbuda         570.60                           12.0   \n",
       "4            Argentina      341388.60                           12.0   \n",
       "\n",
       "   IrregularDeaths  IrregularAverageAnnualUnitCount  ExcessDeaths  \\\n",
       "0          29092.5                             12.0       7136.70   \n",
       "1         235628.0                             12.0      56338.35   \n",
       "2            419.0                             12.0        108.80   \n",
       "3            611.5                             12.0         40.90   \n",
       "4         376221.0                             12.0      34832.40   \n",
       "\n",
       "   ExcessDeathRate  \n",
       "0         0.325049  \n",
       "1         0.314231  \n",
       "2         0.350741  \n",
       "3         0.071679  \n",
       "4         0.102032  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newDF = pd.concat([newDF, (newDF.IrregularDeaths - newDF.RegularDeaths)], axis=1)\n",
    "newDF = pd.concat([newDF, (newDF.IrregularDeaths - newDF.RegularDeaths) / newDF.RegularDeaths], axis=1)\n",
    "newDF.columns = ['country', 'RegularDeaths', 'RegularAverageAnnualUnitCount', 'IrregularDeaths', 'IrregularAverageAnnualUnitCount', 'ExcessDeaths', 'ExcessDeathRate']\n",
    "newDF.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 7. Get population so that we can calculate deaths per million population from OWID dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = join(getcwd().rstrip('src'),\n",
    "            'data/owid-covid-data.csv').replace('\\\\', '/')\n",
    "data = pd.read_csv(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>40099462.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>40099462.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>40099462.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>40099462.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>40099462.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country  population\n",
       "0  Afghanistan  40099462.0\n",
       "1  Afghanistan  40099462.0\n",
       "2  Afghanistan  40099462.0\n",
       "3  Afghanistan  40099462.0\n",
       "4  Afghanistan  40099462.0"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = data[['location', 'population']]\n",
    "data.rename(columns = {'location':'country'}, inplace=True)\n",
    "data.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "244\n"
     ]
    },
    {
     "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>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>4.009946e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Africa</td>\n",
       "      <td>1.392394e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2.854710e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>4.417797e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>7.903400e+04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country    population\n",
       "0  Afghanistan  4.009946e+07\n",
       "1       Africa  1.392394e+09\n",
       "2      Albania  2.854710e+06\n",
       "3      Algeria  4.417797e+07\n",
       "4      Andorra  7.903400e+04"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.groupby('country').count().sort_values(by='population', ascending=False)\n",
    "data.drop_duplicates(subset=['country'], inplace=True)\n",
    "data.reset_index(drop=True, inplace=True)\n",
    "print(len(data))\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 8. merge population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>RegularDeaths</th>\n",
       "      <th>RegularAverageAnnualUnitCount</th>\n",
       "      <th>IrregularDeaths</th>\n",
       "      <th>IrregularAverageAnnualUnitCount</th>\n",
       "      <th>ExcessDeaths</th>\n",
       "      <th>ExcessDeathRate</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>21955.80</td>\n",
       "      <td>12.0</td>\n",
       "      <td>29092.5</td>\n",
       "      <td>12.0</td>\n",
       "      <td>7136.70</td>\n",
       "      <td>0.325049</td>\n",
       "      <td>2854710.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>179289.65</td>\n",
       "      <td>12.0</td>\n",
       "      <td>235628.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>56338.35</td>\n",
       "      <td>0.314231</td>\n",
       "      <td>44177969.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>310.20</td>\n",
       "      <td>12.0</td>\n",
       "      <td>419.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>108.80</td>\n",
       "      <td>0.350741</td>\n",
       "      <td>79034.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>570.60</td>\n",
       "      <td>12.0</td>\n",
       "      <td>611.5</td>\n",
       "      <td>12.0</td>\n",
       "      <td>40.90</td>\n",
       "      <td>0.071679</td>\n",
       "      <td>93220.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>341388.60</td>\n",
       "      <td>12.0</td>\n",
       "      <td>376221.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>34832.40</td>\n",
       "      <td>0.102032</td>\n",
       "      <td>45276780.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               country  RegularDeaths  RegularAverageAnnualUnitCount  \\\n",
       "0              Albania       21955.80                           12.0   \n",
       "1              Algeria      179289.65                           12.0   \n",
       "2              Andorra         310.20                           12.0   \n",
       "3  Antigua and Barbuda         570.60                           12.0   \n",
       "4            Argentina      341388.60                           12.0   \n",
       "\n",
       "   IrregularDeaths  IrregularAverageAnnualUnitCount  ExcessDeaths  \\\n",
       "0          29092.5                             12.0       7136.70   \n",
       "1         235628.0                             12.0      56338.35   \n",
       "2            419.0                             12.0        108.80   \n",
       "3            611.5                             12.0         40.90   \n",
       "4         376221.0                             12.0      34832.40   \n",
       "\n",
       "   ExcessDeathRate  population  \n",
       "0         0.325049   2854710.0  \n",
       "1         0.314231  44177969.0  \n",
       "2         0.350741     79034.0  \n",
       "3         0.071679     93220.0  \n",
       "4         0.102032  45276780.0  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con1 = list(data.country)\n",
    "con2 = list(newDF.country)\n",
    "pop = []\n",
    "for c in con2:\n",
    "    if c in con1:\n",
    "        pop.append(data[data.country == c].population.values[0])\n",
    "    else:\n",
    "        pop.append(0)\n",
    "pop = pd.Series(pop, name='population')\n",
    "newDF = pd.concat([newDF, pop], axis=1)\n",
    "newDF.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 9. calculate deaths per million population and sort by it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>RegularDeaths</th>\n",
       "      <th>RegularAverageAnnualUnitCount</th>\n",
       "      <th>IrregularDeaths</th>\n",
       "      <th>IrregularAverageAnnualUnitCount</th>\n",
       "      <th>ExcessDeaths</th>\n",
       "      <th>ExcessDeathRate</th>\n",
       "      <th>population</th>\n",
       "      <th>RDPM</th>\n",
       "      <th>IRDPM</th>\n",
       "      <th>EDPM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>21955.80</td>\n",
       "      <td>12.0</td>\n",
       "      <td>29092.5</td>\n",
       "      <td>12.0</td>\n",
       "      <td>7136.70</td>\n",
       "      <td>0.325049</td>\n",
       "      <td>2854710.0</td>\n",
       "      <td>0.007691</td>\n",
       "      <td>0.010191</td>\n",
       "      <td>0.002500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>179289.65</td>\n",
       "      <td>12.0</td>\n",
       "      <td>235628.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>56338.35</td>\n",
       "      <td>0.314231</td>\n",
       "      <td>44177969.0</td>\n",
       "      <td>0.004058</td>\n",
       "      <td>0.005334</td>\n",
       "      <td>0.001275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>310.20</td>\n",
       "      <td>12.0</td>\n",
       "      <td>419.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>108.80</td>\n",
       "      <td>0.350741</td>\n",
       "      <td>79034.0</td>\n",
       "      <td>0.003925</td>\n",
       "      <td>0.005302</td>\n",
       "      <td>0.001377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>570.60</td>\n",
       "      <td>12.0</td>\n",
       "      <td>611.5</td>\n",
       "      <td>12.0</td>\n",
       "      <td>40.90</td>\n",
       "      <td>0.071679</td>\n",
       "      <td>93220.0</td>\n",
       "      <td>0.006121</td>\n",
       "      <td>0.006560</td>\n",
       "      <td>0.000439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>341388.60</td>\n",
       "      <td>12.0</td>\n",
       "      <td>376221.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>34832.40</td>\n",
       "      <td>0.102032</td>\n",
       "      <td>45276780.0</td>\n",
       "      <td>0.007540</td>\n",
       "      <td>0.008309</td>\n",
       "      <td>0.000769</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               country  RegularDeaths  RegularAverageAnnualUnitCount  \\\n",
       "0              Albania       21955.80                           12.0   \n",
       "1              Algeria      179289.65                           12.0   \n",
       "2              Andorra         310.20                           12.0   \n",
       "3  Antigua and Barbuda         570.60                           12.0   \n",
       "4            Argentina      341388.60                           12.0   \n",
       "\n",
       "   IrregularDeaths  IrregularAverageAnnualUnitCount  ExcessDeaths  \\\n",
       "0          29092.5                             12.0       7136.70   \n",
       "1         235628.0                             12.0      56338.35   \n",
       "2            419.0                             12.0        108.80   \n",
       "3            611.5                             12.0         40.90   \n",
       "4         376221.0                             12.0      34832.40   \n",
       "\n",
       "   ExcessDeathRate  population      RDPM     IRDPM      EDPM  \n",
       "0         0.325049   2854710.0  0.007691  0.010191  0.002500  \n",
       "1         0.314231  44177969.0  0.004058  0.005334  0.001275  \n",
       "2         0.350741     79034.0  0.003925  0.005302  0.001377  \n",
       "3         0.071679     93220.0  0.006121  0.006560  0.000439  \n",
       "4         0.102032  45276780.0  0.007540  0.008309  0.000769  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rdp = newDF.RegularDeaths / newDF.population\n",
    "rdp.name = 'RDPM' # RDPM = Regular Deaths Per Million\n",
    "irdp = newDF.IrregularDeaths / newDF.population\n",
    "irdp.name = 'IRDPM' # IRDPM = Irregular Deaths Per Million\n",
    "edp = newDF.ExcessDeaths / newDF.population\n",
    "edp.name = 'EDPM' # EDPM = Excess Deaths Per Million\n",
    "newDF = pd.concat([newDF, rdp, irdp, edp], axis=1)\n",
    "newDF.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>RD</th>\n",
       "      <th>RAAUC</th>\n",
       "      <th>IRD</th>\n",
       "      <th>IRAAUC</th>\n",
       "      <th>ED</th>\n",
       "      <th>EDR</th>\n",
       "      <th>Population</th>\n",
       "      <th>RDPM</th>\n",
       "      <th>IRDPM</th>\n",
       "      <th>EDPM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Cabo Verde</td>\n",
       "      <td>2691.200000</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2959.000000</td>\n",
       "      <td>12.0</td>\n",
       "      <td>267.800000</td>\n",
       "      <td>0.099510</td>\n",
       "      <td>0.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>French Guiana</td>\n",
       "      <td>890.375479</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1144.495238</td>\n",
       "      <td>53.0</td>\n",
       "      <td>254.119759</td>\n",
       "      <td>0.285407</td>\n",
       "      <td>0.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Réunion</td>\n",
       "      <td>4808.704981</td>\n",
       "      <td>52.0</td>\n",
       "      <td>5467.923810</td>\n",
       "      <td>53.0</td>\n",
       "      <td>659.218829</td>\n",
       "      <td>0.137089</td>\n",
       "      <td>0.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mayotte</td>\n",
       "      <td>692.735632</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1008.304762</td>\n",
       "      <td>53.0</td>\n",
       "      <td>315.569130</td>\n",
       "      <td>0.455540</td>\n",
       "      <td>0.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Martinique</td>\n",
       "      <td>3271.616858</td>\n",
       "      <td>52.0</td>\n",
       "      <td>4084.228571</td>\n",
       "      <td>53.0</td>\n",
       "      <td>812.611713</td>\n",
       "      <td>0.248382</td>\n",
       "      <td>0.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Country           RD  RAAUC          IRD  IRAAUC          ED  \\\n",
       "0     Cabo Verde  2691.200000   12.0  2959.000000    12.0  267.800000   \n",
       "1  French Guiana   890.375479   52.0  1144.495238    53.0  254.119759   \n",
       "2        Réunion  4808.704981   52.0  5467.923810    53.0  659.218829   \n",
       "3        Mayotte   692.735632   52.0  1008.304762    53.0  315.569130   \n",
       "4     Martinique  3271.616858   52.0  4084.228571    53.0  812.611713   \n",
       "\n",
       "        EDR  Population  RDPM  IRDPM  EDPM  \n",
       "0  0.099510         0.0   inf    inf   inf  \n",
       "1  0.285407         0.0   inf    inf   inf  \n",
       "2  0.137089         0.0   inf    inf   inf  \n",
       "3  0.455540         0.0   inf    inf   inf  \n",
       "4  0.248382         0.0   inf    inf   inf  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newDF.sort_values(by='EDPM', ascending=False, inplace=True)\n",
    "newDF.reset_index(drop=True, inplace=True)\n",
    "newDF.columns = ['Country', 'RD', 'RAAUC', 'IRD', 'IRAAUC', 'ED', 'EDR', 'Population', 'RDPM', 'IRDPM', 'EDPM']\n",
    "newDF.head()\n",
    "# RD = Regular Deaths\n",
    "# RAAUC = Regular Average Annual Unit Count\n",
    "# IRD = Irregular Deaths\n",
    "# IRAAUC = Irregular Average Annual Unit Count\n",
    "# ED = Excess Deaths\n",
    "# EDR = Excess Death Rate\n",
    "# RDPM = Regular Deaths Per Million\n",
    "# IRDPM = Irregular Deaths Per Million\n",
    "# EDPM = Excess Deaths Per Million"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.8"
  },
  "vscode": {
   "interpreter": {
    "hash": "26de051ba29f2982a8de78e945f0abaf191376122a1563185a90213a26c5da77"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}