2. Estimating population density for catchments - preliminary analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c5bfffd1",
   "metadata": {},
   "source": [
    "The SALURBAL data for Latin American cities is provided as both count and rate per 10 million people, and contains recent mortality estimates.  While the population of the cities is not explicitly provided, the intuition is that this could be derived by relating the absolute count to the relative rate.   \n",
    "\n",
    "A rate per 10 million (r) is calculated as count (c) divided by (population (p) divided by 10^7). \n",
    "\n",
    "r = c / (p/10^7)\n",
    "\n",
    "Therefore,\n",
    "\n",
    "r * (p/10^7) = c , and\n",
    "p/10^7 = c/r, and so\n",
    "p = 10^7*c/r\n",
    "\n",
    "If the count for Buenos Aires in Argentina were 56,553, and the count per 10 million population were 35,437, that implies that the overall population is 10^7*56,533/35,437, or 15,953,100.\n",
    "\n",
    "According to [Wikipedia](https://en.wikipedia.org/wiki/Buenos_Aires), the 2021 population of Buenos Aires metropolitan region is \t15,624,000 --- which roughly corresponds to the above 2022 statistic (slightly less, which is as expected).\n",
    "\n",
    "So, we can now apply this formula with some confidence for any city without a rate of zero (which would result in a division by zero error)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "94e41858",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "783722c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('./covid-data/SALURBAL - L1 level covid19  - Death count and rate per 10m persons for cities - 2022-09-06.csv', index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "27922fba",
   "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>Outcome</th>\n",
       "      <th>Level</th>\n",
       "      <th>Type</th>\n",
       "      <th>Country</th>\n",
       "      <th>Location</th>\n",
       "      <th>Date</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>Argentina Non-salurbal</td>\n",
       "      <td>2020-03-22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>Argentina Non-salurbal</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>Argentina Non-salurbal</td>\n",
       "      <td>2020-04-05</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>Argentina Non-salurbal</td>\n",
       "      <td>2020-04-12</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>Argentina Non-salurbal</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>62</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82364</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count per 10 M</td>\n",
       "      <td>Peru</td>\n",
       "      <td>Tumbes</td>\n",
       "      <td>2022-05-09</td>\n",
       "      <td>75765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82365</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count per 10 M</td>\n",
       "      <td>Peru</td>\n",
       "      <td>Tumbes</td>\n",
       "      <td>2022-05-16</td>\n",
       "      <td>75765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82366</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count per 10 M</td>\n",
       "      <td>Peru</td>\n",
       "      <td>Tumbes</td>\n",
       "      <td>2022-05-23</td>\n",
       "      <td>75765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82367</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count per 10 M</td>\n",
       "      <td>Peru</td>\n",
       "      <td>Tumbes</td>\n",
       "      <td>2022-05-30</td>\n",
       "      <td>75765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82368</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count per 10 M</td>\n",
       "      <td>Peru</td>\n",
       "      <td>Tumbes</td>\n",
       "      <td>2022-06-06</td>\n",
       "      <td>75765</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>82368 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Outcome Level            Type    Country                Location  \\\n",
       "1      deaths    L1           count  Argentina  Argentina Non-salurbal   \n",
       "2      deaths    L1           count  Argentina  Argentina Non-salurbal   \n",
       "3      deaths    L1           count  Argentina  Argentina Non-salurbal   \n",
       "4      deaths    L1           count  Argentina  Argentina Non-salurbal   \n",
       "5      deaths    L1           count  Argentina  Argentina Non-salurbal   \n",
       "...       ...   ...             ...        ...                     ...   \n",
       "82364  deaths    L1  count per 10 M       Peru                  Tumbes   \n",
       "82365  deaths    L1  count per 10 M       Peru                  Tumbes   \n",
       "82366  deaths    L1  count per 10 M       Peru                  Tumbes   \n",
       "82367  deaths    L1  count per 10 M       Peru                  Tumbes   \n",
       "82368  deaths    L1  count per 10 M       Peru                  Tumbes   \n",
       "\n",
       "             Date  Value  \n",
       "1      2020-03-22      0  \n",
       "2      2020-03-29     14  \n",
       "3      2020-04-05     34  \n",
       "4      2020-04-12     47  \n",
       "5      2020-04-19     62  \n",
       "...           ...    ...  \n",
       "82364  2022-05-09  75765  \n",
       "82365  2022-05-16  75765  \n",
       "82366  2022-05-23  75765  \n",
       "82367  2022-05-30  75765  \n",
       "82368  2022-06-06  75765  \n",
       "\n",
       "[82368 rows x 7 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "90fdcdaf",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# retain only most recent record for each city\n",
    "df = df.sort_values('Date').drop_duplicates(['Location','Type'],keep='last').sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "df0310a9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "To exclude:['Argentina Non-salurbal' 'Brazil Non-salurbal' 'Chile Non-salurbal'\n",
      " 'Colombia Non-salurbal' 'Guatemala Non-salurbal' 'Mexico Non-salurbal'\n",
      " 'Peru Non-salurbal']\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>Outcome</th>\n",
       "      <th>Level</th>\n",
       "      <th>Type</th>\n",
       "      <th>Country</th>\n",
       "      <th>Location</th>\n",
       "      <th>Date</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>345</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>Bahia Blanca</td>\n",
       "      <td>2022-05-29</td>\n",
       "      <td>916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>460</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count per 10 M</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>Bahia Blanca</td>\n",
       "      <td>2022-05-29</td>\n",
       "      <td>29701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>575</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>Buenos Aires</td>\n",
       "      <td>2022-05-29</td>\n",
       "      <td>56553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>690</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count per 10 M</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>Buenos Aires</td>\n",
       "      <td>2022-05-29</td>\n",
       "      <td>35437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>805</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>Comodoro Rivadavia</td>\n",
       "      <td>2022-05-29</td>\n",
       "      <td>667</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81904</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count per 10 M</td>\n",
       "      <td>Peru</td>\n",
       "      <td>Tarapoto</td>\n",
       "      <td>2022-06-06</td>\n",
       "      <td>64460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82020</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count</td>\n",
       "      <td>Peru</td>\n",
       "      <td>Trujillo</td>\n",
       "      <td>2022-06-06</td>\n",
       "      <td>6703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82136</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count per 10 M</td>\n",
       "      <td>Peru</td>\n",
       "      <td>Trujillo</td>\n",
       "      <td>2022-06-06</td>\n",
       "      <td>70595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82252</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count</td>\n",
       "      <td>Peru</td>\n",
       "      <td>Tumbes</td>\n",
       "      <td>2022-06-06</td>\n",
       "      <td>1027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82368</th>\n",
       "      <td>deaths</td>\n",
       "      <td>L1</td>\n",
       "      <td>count per 10 M</td>\n",
       "      <td>Peru</td>\n",
       "      <td>Tumbes</td>\n",
       "      <td>2022-06-06</td>\n",
       "      <td>75765</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>714 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Outcome Level            Type    Country            Location  \\\n",
       "345    deaths    L1           count  Argentina        Bahia Blanca   \n",
       "460    deaths    L1  count per 10 M  Argentina        Bahia Blanca   \n",
       "575    deaths    L1           count  Argentina        Buenos Aires   \n",
       "690    deaths    L1  count per 10 M  Argentina        Buenos Aires   \n",
       "805    deaths    L1           count  Argentina  Comodoro Rivadavia   \n",
       "...       ...   ...             ...        ...                 ...   \n",
       "81904  deaths    L1  count per 10 M       Peru            Tarapoto   \n",
       "82020  deaths    L1           count       Peru            Trujillo   \n",
       "82136  deaths    L1  count per 10 M       Peru            Trujillo   \n",
       "82252  deaths    L1           count       Peru              Tumbes   \n",
       "82368  deaths    L1  count per 10 M       Peru              Tumbes   \n",
       "\n",
       "             Date  Value  \n",
       "345    2022-05-29    916  \n",
       "460    2022-05-29  29701  \n",
       "575    2022-05-29  56553  \n",
       "690    2022-05-29  35437  \n",
       "805    2022-05-29    667  \n",
       "...           ...    ...  \n",
       "81904  2022-06-06  64460  \n",
       "82020  2022-06-06   6703  \n",
       "82136  2022-06-06  70595  \n",
       "82252  2022-06-06   1027  \n",
       "82368  2022-06-06  75765  \n",
       "\n",
       "[714 rows x 7 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# note records with name 'salurbal' in location\n",
    "print(f\"To exclude:{df[df['Location'].str.contains('salurbal')]['Location'].unique()}\")\n",
    "# drop records with name 'salurbal' in location\n",
    "df = df[~df['Location'].str.contains('salurbal')]\n",
    "df.sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1a2ef6ba",
   "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>Type</th>\n",
       "      <th>count</th>\n",
       "      <th>count per 10 M</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th>Location</th>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Argentina</th>\n",
       "      <th>Bahia Blanca</th>\n",
       "      <th>2022-05-29</th>\n",
       "      <td>916</td>\n",
       "      <td>29701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Buenos Aires</th>\n",
       "      <th>2022-05-29</th>\n",
       "      <td>56553</td>\n",
       "      <td>35437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Comodoro Rivadavia</th>\n",
       "      <th>2022-05-29</th>\n",
       "      <td>667</td>\n",
       "      <td>30235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Concordia</th>\n",
       "      <th>2022-05-29</th>\n",
       "      <td>339</td>\n",
       "      <td>18266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Corrientes</th>\n",
       "      <th>2022-05-29</th>\n",
       "      <td>791</td>\n",
       "      <td>19866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Peru</th>\n",
       "      <th>Sullana</th>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>1917</td>\n",
       "      <td>74000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tacna</th>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>1739</td>\n",
       "      <td>57385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tarapoto</th>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>946</td>\n",
       "      <td>64460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Trujillo</th>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>6703</td>\n",
       "      <td>70595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tumbes</th>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>1027</td>\n",
       "      <td>75765</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>357 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Type                                     count  count per 10 M\n",
       "Country   Location           Date                             \n",
       "Argentina Bahia Blanca       2022-05-29    916           29701\n",
       "          Buenos Aires       2022-05-29  56553           35437\n",
       "          Comodoro Rivadavia 2022-05-29    667           30235\n",
       "          Concordia          2022-05-29    339           18266\n",
       "          Corrientes         2022-05-29    791           19866\n",
       "...                                        ...             ...\n",
       "Peru      Sullana            2022-06-06   1917           74000\n",
       "          Tacna              2022-06-06   1739           57385\n",
       "          Tarapoto           2022-06-06    946           64460\n",
       "          Trujillo           2022-06-06   6703           70595\n",
       "          Tumbes             2022-06-06   1027           75765\n",
       "\n",
       "[357 rows x 2 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# transform long to wide\n",
    "df = df.pivot(\n",
    "    index=[\"Country\", \"Location\", \"Date\"],\n",
    "    columns=\"Type\",\n",
    "    values=\"Value\",\n",
    ")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6fd305b4",
   "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></th>\n",
       "      <th>Type</th>\n",
       "      <th>count</th>\n",
       "      <th>count per 10 M</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th>Location</th>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Argentina</th>\n",
       "      <th>Bahia Blanca</th>\n",
       "      <th>2022-05-29</th>\n",
       "      <td>916</td>\n",
       "      <td>29701</td>\n",
       "      <td>308407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Buenos Aires</th>\n",
       "      <th>2022-05-29</th>\n",
       "      <td>56553</td>\n",
       "      <td>35437</td>\n",
       "      <td>15958743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Comodoro Rivadavia</th>\n",
       "      <th>2022-05-29</th>\n",
       "      <td>667</td>\n",
       "      <td>30235</td>\n",
       "      <td>220605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Concordia</th>\n",
       "      <th>2022-05-29</th>\n",
       "      <td>339</td>\n",
       "      <td>18266</td>\n",
       "      <td>185590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Corrientes</th>\n",
       "      <th>2022-05-29</th>\n",
       "      <td>791</td>\n",
       "      <td>19866</td>\n",
       "      <td>398167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Peru</th>\n",
       "      <th>Sullana</th>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>1917</td>\n",
       "      <td>74000</td>\n",
       "      <td>259054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tacna</th>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>1739</td>\n",
       "      <td>57385</td>\n",
       "      <td>303040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tarapoto</th>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>946</td>\n",
       "      <td>64460</td>\n",
       "      <td>146757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Trujillo</th>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>6703</td>\n",
       "      <td>70595</td>\n",
       "      <td>949500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tumbes</th>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>1027</td>\n",
       "      <td>75765</td>\n",
       "      <td>135550</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>355 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Type                                     count  count per 10 M  population\n",
       "Country   Location           Date                                         \n",
       "Argentina Bahia Blanca       2022-05-29    916           29701      308407\n",
       "          Buenos Aires       2022-05-29  56553           35437    15958743\n",
       "          Comodoro Rivadavia 2022-05-29    667           30235      220605\n",
       "          Concordia          2022-05-29    339           18266      185590\n",
       "          Corrientes         2022-05-29    791           19866      398167\n",
       "...                                        ...             ...         ...\n",
       "Peru      Sullana            2022-06-06   1917           74000      259054\n",
       "          Tacna              2022-06-06   1739           57385      303040\n",
       "          Tarapoto           2022-06-06    946           64460      146757\n",
       "          Trujillo           2022-06-06   6703           70595      949500\n",
       "          Tumbes             2022-06-06   1027           75765      135550\n",
       "\n",
       "[355 rows x 3 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# estimate population as p = 10^7 * count / rate, dropping any nulls\n",
    "df['population'] = (10**7) * (df['count'] / df['count per 10 M'])\n",
    "df = df[~df['population'].isna()].copy()\n",
    "df['population'] = (df['population']).astype('int32')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b9e7748c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "355"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many cities with statistics? (355)\n",
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "09715c2e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many countries? (7)\n",
    "len(df.reset_index()['Country'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "b9139f9f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Argentina', 'Brazil', 'Chile', 'Colombia', 'Guatemala', 'Mexico',\n",
       "       'Peru'], dtype=object)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Which countries\n",
    "df.reset_index()['Country'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "01b47d7d",
   "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></th>\n",
       "      <th>Type</th>\n",
       "      <th>count</th>\n",
       "      <th>count per 10 M</th>\n",
       "      <th>population</th>\n",
       "      <th>deaths_per_100k</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th>Location</th>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Argentina</th>\n",
       "      <th>Bahia Blanca</th>\n",
       "      <th>2022-05-29</th>\n",
       "      <td>916</td>\n",
       "      <td>29701</td>\n",
       "      <td>308407</td>\n",
       "      <td>297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Buenos Aires</th>\n",
       "      <th>2022-05-29</th>\n",
       "      <td>56553</td>\n",
       "      <td>35437</td>\n",
       "      <td>15958743</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Comodoro Rivadavia</th>\n",
       "      <th>2022-05-29</th>\n",
       "      <td>667</td>\n",
       "      <td>30235</td>\n",
       "      <td>220605</td>\n",
       "      <td>302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Concordia</th>\n",
       "      <th>2022-05-29</th>\n",
       "      <td>339</td>\n",
       "      <td>18266</td>\n",
       "      <td>185590</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Corrientes</th>\n",
       "      <th>2022-05-29</th>\n",
       "      <td>791</td>\n",
       "      <td>19866</td>\n",
       "      <td>398167</td>\n",
       "      <td>198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Peru</th>\n",
       "      <th>Sullana</th>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>1917</td>\n",
       "      <td>74000</td>\n",
       "      <td>259054</td>\n",
       "      <td>740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tacna</th>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>1739</td>\n",
       "      <td>57385</td>\n",
       "      <td>303040</td>\n",
       "      <td>573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tarapoto</th>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>946</td>\n",
       "      <td>64460</td>\n",
       "      <td>146757</td>\n",
       "      <td>644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Trujillo</th>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>6703</td>\n",
       "      <td>70595</td>\n",
       "      <td>949500</td>\n",
       "      <td>705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tumbes</th>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>1027</td>\n",
       "      <td>75765</td>\n",
       "      <td>135550</td>\n",
       "      <td>757</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>355 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Type                                     count  count per 10 M  population  \\\n",
       "Country   Location           Date                                            \n",
       "Argentina Bahia Blanca       2022-05-29    916           29701      308407   \n",
       "          Buenos Aires       2022-05-29  56553           35437    15958743   \n",
       "          Comodoro Rivadavia 2022-05-29    667           30235      220605   \n",
       "          Concordia          2022-05-29    339           18266      185590   \n",
       "          Corrientes         2022-05-29    791           19866      398167   \n",
       "...                                        ...             ...         ...   \n",
       "Peru      Sullana            2022-06-06   1917           74000      259054   \n",
       "          Tacna              2022-06-06   1739           57385      303040   \n",
       "          Tarapoto           2022-06-06    946           64460      146757   \n",
       "          Trujillo           2022-06-06   6703           70595      949500   \n",
       "          Tumbes             2022-06-06   1027           75765      135550   \n",
       "\n",
       "Type                                     deaths_per_100k  \n",
       "Country   Location           Date                         \n",
       "Argentina Bahia Blanca       2022-05-29              297  \n",
       "          Buenos Aires       2022-05-29              354  \n",
       "          Comodoro Rivadavia 2022-05-29              302  \n",
       "          Concordia          2022-05-29              182  \n",
       "          Corrientes         2022-05-29              198  \n",
       "...                                                  ...  \n",
       "Peru      Sullana            2022-06-06              740  \n",
       "          Tacna              2022-06-06              573  \n",
       "          Tarapoto           2022-06-06              644  \n",
       "          Trujillo           2022-06-06              705  \n",
       "          Tumbes             2022-06-06              757  \n",
       "\n",
       "[355 rows x 4 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get deaths per 100k population\n",
    "df['deaths_per_100k'] = df['count']/(df['population']/100000)\n",
    "df['deaths_per_100k'] = df['deaths_per_100k'].astype('int32')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f94833f",
   "metadata": {},
   "source": [
    "The statistic of 354 deaths per 100,000 population is much higher than the 7.65 reported by Adlakha and Sallis for Buenos Aires...  \n",
    "```\n",
    "Earlier:   221/( 2890000/100000) =   7.64\n",
    "    Now: 56553/(15958743/100000) = 354.37\n",
    "```\n",
    "\n",
    "The Adlakha and Sallis statistic was based on pop. estimate of 2,890,000 --- which roughly corresponds to the urban not metroplitan city area, according to Wikipedia.  The SALURBAL data corresponds to the metropolitan area, hence the far greater population.   The Adlakha and Sallis article was also submitted for publication in May 2020 -- very early in the pandemic --- hence the extreme difference in death count is likely explained by Greater Buenos Aires lock down period from 19 March (a week and a half after reporting of the first case) to 17 July."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6f752f5",
   "metadata": {},
   "source": [
    "# Population density\n",
    "Wikipedia could be a convenient single source for metro area statistics, under assumption these are ball park accurate and current.\n",
    "\n",
    "Buenos Aires metropolitan area is recorded as 4,758 persons per sqkm.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e89f3d9a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3354.0863808322824"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[('Argentina','Buenos Aires')]['population'][0]/4758"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc0336b1",
   "metadata": {},
   "source": [
    "The estimated statistic for Buenos Aires population density of 3354 persons per sqkm, is lower than that in Adlkha and Sallis (13,680).   The Adlakha and Sallis statistic was based on pop. estimate of 2,890,000  --- which roughly corresponds to the urban not metroplitan city area, according to Wikipedia.  Wikipedia gives the urban area at 203 sqkm."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "971080b4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14236.453201970444"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "2890000/203"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88658c25",
   "metadata": {},
   "source": [
    "Above we see that the Buenos Aires density estimates in Adlakha and Sallis likely corresponds to urban not metro area for Buenos Aires.\n",
    "\n",
    "However, the 2022 death statistic we have is for Metro area, empirically determined by back-calculating the population and identifying that this corresponded to the city's metropolitan statistic.\n",
    "\n",
    "So --- in general i think for Buenos Aires, the way to represent this is the Greater Buenos Aires conurbation https://en.wikipedia.org/wiki/Greater_Buenos_Aires.\n",
    "\n",
    "But ... can we determine this for every city, or do we just assume these are all 'Metro'?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26071f59",
   "metadata": {},
   "source": [
    "# Mexico city"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fb888dc",
   "metadata": {},
   "source": [
    "In Adlkha and Sallis, the mid-2020 statistics for Mexico City were:\n",
    "\n",
    "|                  |Statistic        |\n",
    "|------------------|-----------------|\n",
    "|Population density|9800             |\n",
    "|Population        |missing, but from 8.81 to 8.92 million, depending on derivation using case or death rates|\n",
    "|Deaths            |2203             |\n",
    "|Deaths per 100k pop|25              |\n",
    "\n",
    "Using the 2022 SALURBAL data, for Mexico City, we have"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "754a7f59",
   "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>Date</th>\n",
       "      <th>2022-06-06</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Type</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>population</th>\n",
       "      <td>21190532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>53718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>deaths_per_100k</th>\n",
       "      <td>253</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Date             2022-06-06\n",
       "Type                       \n",
       "population         21190532\n",
       "count                 53718\n",
       "deaths_per_100k         253"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[('Mexico', 'Ciudad de Mexico')][['population','count','deaths_per_100k']].transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c24be252",
   "metadata": {},
   "source": [
    "As with Buenos Aires, for [Mexico City](https://en.wikipedia.org/wiki/Mexico_City) according to Wikipedia, the Adlakha and Sallis article present the urban population (9.2 million) while SALURBAL data roughly corresponds to the Metro population (21.8 million).  Death count and rate is higher, mostly as a function of time and time-related contexts; but the area catchment (population and density) is broader and not directly comparable.\n",
    "\n",
    "If the [Greater Mexico City metro area](https://en.wikipedia.org/wiki/Greater_Mexico_City) is taken as 7,866.1 km2, then this gives a marginal density of 21190532/7866=2693 persons per sqkm approximately."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1da1e92c",
   "metadata": {},
   "source": [
    "# Population and density comparisons with the GHSL-derived data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5ae2a48",
   "metadata": {},
   "source": [
    "The GHSL derived data had a Buenos Aires population estimate of 13,906,506 (2015); that corresponds to the metropolitan area (15.9 million in 2022).  Likewise for Mexico City, the population estimate of 19.6 million (2015) corresponds to the greater metropolitan area (21.8, 2020). We calculated population density as population relative to the built up area, not the overall area using the GHSL data (so more like net density, rather than gross density; an idiosyncracy we could amend if we thought appropriate).  For Buenos Aires, this was 10,230 persons per sqkm (lower than Adlakha and Sallis); and Mexico City, 15,066 persons per sqkm (higher than Adlakha and Sallis).  \n",
    "\n",
    "If we calculated gross density (which may be more appropriate), for Buenos Aires we would have 13906506/1967 = 7,069 persons per sqkm (lower still than Adlakha and Sallis, but about double that of the SALURBAN reference), and for Mexico City 19559564/2114= 9,252 (almost 4 times that of SALURBAN refernce)\n",
    "\n",
    "This may be explained by the GHSL method for identifying urban centres. Rather than administrative boundaries, it uses satellite imagery, census data and other sources to identify urban agglomerations; effectively like an empircal greater metropolitan area (which may effectively be several cities combined).  So, it includes peri-urban areas to an extent which may differ (be lesser than or greater than) would be the case for administrative boundaries.   If analysis were restricted more to inner city areas, as was the case for Buenos Aires and Mexico City in Adlakha and Sallis' paper, this would result in a higher density estimate than would be achieved were population considered relative to the greater metropolitan expanse which includes areas towards the urban fringe which would be expected to be more sparse population-wise.\n",
    "\n",
    "The much lower density estimates for the SALURBAN data would likely relate to the use of administrative boundaries that capture regional areas with very low population that both the GHSL method and even moreso the use of inner city areas would exclude.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed8a6f7a",
   "metadata": {},
   "source": [
    "# So what to do?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c74023d4",
   "metadata": {},
   "source": [
    "The best bet for a comparable analysis would be to source population and density statistics for metro areas, perhaps using Wikipedia as a convenience source with broad recognition.  It's city pages routinely contain these statistics, although some smaller cities may not be listed; but its perhaps a do-able source in the first instance (and may have link to official source).\n",
    "\n",
    "If we were to do any spatial analyses (eg connectivity, as we discussed), we would require actual boundaries.  We could use GHSL for that, but I think it might be best to avoid doing this and keep things simple.  I think manually going through each city's country's statistical agency for digital boundaries and statistics will be too much effort (even just for Aus and NZ it was too much -- must be more difficult once we move outside high income English speaking countries!).\n",
    "\n",
    "For the Latin American cities we can use the recorded count and rate statistics to derive population which can be used as a reference with recorded populations from Wikipedia to ensure that at least for these cities the catchments for density and death statistics correspond."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "7ec55037",
   "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>Type</th>\n",
       "      <th>population</th>\n",
       "      <th>count</th>\n",
       "      <th>deaths_per_100k</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-06-06</th>\n",
       "      <td>9922528</td>\n",
       "      <td>9734</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Type        population  count  deaths_per_100k\n",
       "Date                                          \n",
       "2022-06-06     9922528   9734               98"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[('Peru','Lima')][['population','count','deaths_per_100k']]"
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}