pop_scrub.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Notebook to create dataset from United Nations Population Data (Country, Pop. by Year from 2009 to 2020) and Country_Centroids Data (Country, Lat, Lon) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies and Setup\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# File to Load (Remember to Change These)\n",
    "population_data_to_load = \"Data/WPP2019_POP_F01_1_TOTAL_POPULATION_BOTH_SEXES.xlsx\"\n",
    "\n",
    "# Read Population Data from \"Estimates\" sheet\n",
    "population_data = pd.read_excel(population_data_to_load, \"ESTIMATES\", encoding=\"ISO-8859-1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Unnamed: 8</th>\n",
       "      <th>Unnamed: 9</th>\n",
       "      <th>...</th>\n",
       "      <th>Unnamed: 68</th>\n",
       "      <th>Unnamed: 69</th>\n",
       "      <th>Unnamed: 70</th>\n",
       "      <th>Unnamed: 71</th>\n",
       "      <th>Unnamed: 72</th>\n",
       "      <th>Unnamed: 73</th>\n",
       "      <th>Unnamed: 74</th>\n",
       "      <th>Unnamed: 75</th>\n",
       "      <th>Unnamed: 76</th>\n",
       "      <th>Unnamed: 77</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>United Nations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Population Division</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Department of Economic and Social Affairs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>World Population Prospects 2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>File POP/1-1: Total population (both sexes com...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Estimates, 1950 - 2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>POP/DB/WPP/Rev.2019/POP/F01-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>© August 2019 by United Nations, made availabl...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Suggested citation: United Nations, Department...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Total population, both sexes combined, as of 1...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Index</td>\n",
       "      <td>Variant</td>\n",
       "      <td>Region, subregion, country or area *</td>\n",
       "      <td>Notes</td>\n",
       "      <td>Country code</td>\n",
       "      <td>Type</td>\n",
       "      <td>Parent code</td>\n",
       "      <td>1950</td>\n",
       "      <td>1951</td>\n",
       "      <td>1952</td>\n",
       "      <td>...</td>\n",
       "      <td>2011</td>\n",
       "      <td>2012</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>2015</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2019</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1</td>\n",
       "      <td>Estimates</td>\n",
       "      <td>WORLD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>900</td>\n",
       "      <td>World</td>\n",
       "      <td>0</td>\n",
       "      <td>2.53643e+06</td>\n",
       "      <td>2.58403e+06</td>\n",
       "      <td>2.63086e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>7.04119e+06</td>\n",
       "      <td>7.12583e+06</td>\n",
       "      <td>7.21058e+06</td>\n",
       "      <td>7.29529e+06</td>\n",
       "      <td>7.3798e+06</td>\n",
       "      <td>7.46402e+06</td>\n",
       "      <td>7.54786e+06</td>\n",
       "      <td>7.63109e+06</td>\n",
       "      <td>7.71347e+06</td>\n",
       "      <td>7.7948e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2</td>\n",
       "      <td>Estimates</td>\n",
       "      <td>UN development groups</td>\n",
       "      <td>a</td>\n",
       "      <td>1803</td>\n",
       "      <td>Label/Separator</td>\n",
       "      <td>900</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>3</td>\n",
       "      <td>Estimates</td>\n",
       "      <td>More developed regions</td>\n",
       "      <td>b</td>\n",
       "      <td>901</td>\n",
       "      <td>Development Group</td>\n",
       "      <td>1803</td>\n",
       "      <td>814819</td>\n",
       "      <td>824004</td>\n",
       "      <td>833720</td>\n",
       "      <td>...</td>\n",
       "      <td>1.23956e+06</td>\n",
       "      <td>1.24411e+06</td>\n",
       "      <td>1.24845e+06</td>\n",
       "      <td>1.25262e+06</td>\n",
       "      <td>1.25662e+06</td>\n",
       "      <td>1.26048e+06</td>\n",
       "      <td>1.26415e+06</td>\n",
       "      <td>1.26756e+06</td>\n",
       "      <td>1.27063e+06</td>\n",
       "      <td>1.2733e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>4</td>\n",
       "      <td>Estimates</td>\n",
       "      <td>Less developed regions</td>\n",
       "      <td>c</td>\n",
       "      <td>902</td>\n",
       "      <td>Development Group</td>\n",
       "      <td>1803</td>\n",
       "      <td>1.72161e+06</td>\n",
       "      <td>1.76003e+06</td>\n",
       "      <td>1.79714e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>5.80164e+06</td>\n",
       "      <td>5.88171e+06</td>\n",
       "      <td>5.96213e+06</td>\n",
       "      <td>6.04268e+06</td>\n",
       "      <td>6.12317e+06</td>\n",
       "      <td>6.20354e+06</td>\n",
       "      <td>6.28371e+06</td>\n",
       "      <td>6.36353e+06</td>\n",
       "      <td>6.44284e+06</td>\n",
       "      <td>6.52149e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 78 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           Unnamed: 0 Unnamed: 1  \\\n",
       "0                                                 NaN        NaN   \n",
       "1                                                 NaN        NaN   \n",
       "2                                                 NaN        NaN   \n",
       "3                                      United Nations        NaN   \n",
       "4                                 Population Division        NaN   \n",
       "5           Department of Economic and Social Affairs        NaN   \n",
       "6                                                 NaN        NaN   \n",
       "7                     World Population Prospects 2019        NaN   \n",
       "8   File POP/1-1: Total population (both sexes com...        NaN   \n",
       "9                              Estimates, 1950 - 2020        NaN   \n",
       "10                      POP/DB/WPP/Rev.2019/POP/F01-1        NaN   \n",
       "11  © August 2019 by United Nations, made availabl...        NaN   \n",
       "12  Suggested citation: United Nations, Department...        NaN   \n",
       "13                                                NaN        NaN   \n",
       "14                                                NaN        NaN   \n",
       "15                                              Index    Variant   \n",
       "16                                                  1  Estimates   \n",
       "17                                                  2  Estimates   \n",
       "18                                                  3  Estimates   \n",
       "19                                                  4  Estimates   \n",
       "\n",
       "                              Unnamed: 2 Unnamed: 3    Unnamed: 4  \\\n",
       "0                                    NaN        NaN           NaN   \n",
       "1                                    NaN        NaN           NaN   \n",
       "2                                    NaN        NaN           NaN   \n",
       "3                                    NaN        NaN           NaN   \n",
       "4                                    NaN        NaN           NaN   \n",
       "5                                    NaN        NaN           NaN   \n",
       "6                                    NaN        NaN           NaN   \n",
       "7                                    NaN        NaN           NaN   \n",
       "8                                    NaN        NaN           NaN   \n",
       "9                                    NaN        NaN           NaN   \n",
       "10                                   NaN        NaN           NaN   \n",
       "11                                   NaN        NaN           NaN   \n",
       "12                                   NaN        NaN           NaN   \n",
       "13                                   NaN        NaN           NaN   \n",
       "14                                   NaN        NaN           NaN   \n",
       "15  Region, subregion, country or area *      Notes  Country code   \n",
       "16                                 WORLD        NaN           900   \n",
       "17                 UN development groups          a          1803   \n",
       "18                More developed regions          b           901   \n",
       "19                Less developed regions          c           902   \n",
       "\n",
       "           Unnamed: 5   Unnamed: 6  \\\n",
       "0                 NaN          NaN   \n",
       "1                 NaN          NaN   \n",
       "2                 NaN          NaN   \n",
       "3                 NaN          NaN   \n",
       "4                 NaN          NaN   \n",
       "5                 NaN          NaN   \n",
       "6                 NaN          NaN   \n",
       "7                 NaN          NaN   \n",
       "8                 NaN          NaN   \n",
       "9                 NaN          NaN   \n",
       "10                NaN          NaN   \n",
       "11                NaN          NaN   \n",
       "12                NaN          NaN   \n",
       "13                NaN          NaN   \n",
       "14                NaN          NaN   \n",
       "15               Type  Parent code   \n",
       "16              World            0   \n",
       "17    Label/Separator          900   \n",
       "18  Development Group         1803   \n",
       "19  Development Group         1803   \n",
       "\n",
       "                                           Unnamed: 7   Unnamed: 8  \\\n",
       "0                                                 NaN          NaN   \n",
       "1                                                 NaN          NaN   \n",
       "2                                                 NaN          NaN   \n",
       "3                                                 NaN          NaN   \n",
       "4                                                 NaN          NaN   \n",
       "5                                                 NaN          NaN   \n",
       "6                                                 NaN          NaN   \n",
       "7                                                 NaN          NaN   \n",
       "8                                                 NaN          NaN   \n",
       "9                                                 NaN          NaN   \n",
       "10                                                NaN          NaN   \n",
       "11                                                NaN          NaN   \n",
       "12                                                NaN          NaN   \n",
       "13                                                NaN          NaN   \n",
       "14  Total population, both sexes combined, as of 1...          NaN   \n",
       "15                                               1950         1951   \n",
       "16                                        2.53643e+06  2.58403e+06   \n",
       "17                                                ...          ...   \n",
       "18                                             814819       824004   \n",
       "19                                        1.72161e+06  1.76003e+06   \n",
       "\n",
       "     Unnamed: 9  ...  Unnamed: 68  Unnamed: 69  Unnamed: 70  Unnamed: 71  \\\n",
       "0           NaN  ...          NaN          NaN          NaN          NaN   \n",
       "1           NaN  ...          NaN          NaN          NaN          NaN   \n",
       "2           NaN  ...          NaN          NaN          NaN          NaN   \n",
       "3           NaN  ...          NaN          NaN          NaN          NaN   \n",
       "4           NaN  ...          NaN          NaN          NaN          NaN   \n",
       "5           NaN  ...          NaN          NaN          NaN          NaN   \n",
       "6           NaN  ...          NaN          NaN          NaN          NaN   \n",
       "7           NaN  ...          NaN          NaN          NaN          NaN   \n",
       "8           NaN  ...          NaN          NaN          NaN          NaN   \n",
       "9           NaN  ...          NaN          NaN          NaN          NaN   \n",
       "10          NaN  ...          NaN          NaN          NaN          NaN   \n",
       "11          NaN  ...          NaN          NaN          NaN          NaN   \n",
       "12          NaN  ...          NaN          NaN          NaN          NaN   \n",
       "13          NaN  ...          NaN          NaN          NaN          NaN   \n",
       "14          NaN  ...          NaN          NaN          NaN          NaN   \n",
       "15         1952  ...         2011         2012         2013         2014   \n",
       "16  2.63086e+06  ...  7.04119e+06  7.12583e+06  7.21058e+06  7.29529e+06   \n",
       "17          ...  ...          ...          ...          ...          ...   \n",
       "18       833720  ...  1.23956e+06  1.24411e+06  1.24845e+06  1.25262e+06   \n",
       "19  1.79714e+06  ...  5.80164e+06  5.88171e+06  5.96213e+06  6.04268e+06   \n",
       "\n",
       "    Unnamed: 72  Unnamed: 73  Unnamed: 74  Unnamed: 75  Unnamed: 76  \\\n",
       "0           NaN          NaN          NaN          NaN          NaN   \n",
       "1           NaN          NaN          NaN          NaN          NaN   \n",
       "2           NaN          NaN          NaN          NaN          NaN   \n",
       "3           NaN          NaN          NaN          NaN          NaN   \n",
       "4           NaN          NaN          NaN          NaN          NaN   \n",
       "5           NaN          NaN          NaN          NaN          NaN   \n",
       "6           NaN          NaN          NaN          NaN          NaN   \n",
       "7           NaN          NaN          NaN          NaN          NaN   \n",
       "8           NaN          NaN          NaN          NaN          NaN   \n",
       "9           NaN          NaN          NaN          NaN          NaN   \n",
       "10          NaN          NaN          NaN          NaN          NaN   \n",
       "11          NaN          NaN          NaN          NaN          NaN   \n",
       "12          NaN          NaN          NaN          NaN          NaN   \n",
       "13          NaN          NaN          NaN          NaN          NaN   \n",
       "14          NaN          NaN          NaN          NaN          NaN   \n",
       "15         2015         2016         2017         2018         2019   \n",
       "16   7.3798e+06  7.46402e+06  7.54786e+06  7.63109e+06  7.71347e+06   \n",
       "17          ...          ...          ...          ...          ...   \n",
       "18  1.25662e+06  1.26048e+06  1.26415e+06  1.26756e+06  1.27063e+06   \n",
       "19  6.12317e+06  6.20354e+06  6.28371e+06  6.36353e+06  6.44284e+06   \n",
       "\n",
       "    Unnamed: 77  \n",
       "0           NaN  \n",
       "1           NaN  \n",
       "2           NaN  \n",
       "3           NaN  \n",
       "4           NaN  \n",
       "5           NaN  \n",
       "6           NaN  \n",
       "7           NaN  \n",
       "8           NaN  \n",
       "9           NaN  \n",
       "10          NaN  \n",
       "11          NaN  \n",
       "12          NaN  \n",
       "13          NaN  \n",
       "14          NaN  \n",
       "15         2020  \n",
       "16   7.7948e+06  \n",
       "17          ...  \n",
       "18   1.2733e+06  \n",
       "19  6.52149e+06  \n",
       "\n",
       "[20 rows x 78 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population_data.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Unnamed: 8</th>\n",
       "      <th>Unnamed: 9</th>\n",
       "      <th>...</th>\n",
       "      <th>Unnamed: 68</th>\n",
       "      <th>Unnamed: 69</th>\n",
       "      <th>Unnamed: 70</th>\n",
       "      <th>Unnamed: 71</th>\n",
       "      <th>Unnamed: 72</th>\n",
       "      <th>Unnamed: 73</th>\n",
       "      <th>Unnamed: 74</th>\n",
       "      <th>Unnamed: 75</th>\n",
       "      <th>Unnamed: 76</th>\n",
       "      <th>Unnamed: 77</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Index</td>\n",
       "      <td>Variant</td>\n",
       "      <td>Region, subregion, country or area *</td>\n",
       "      <td>Notes</td>\n",
       "      <td>Country code</td>\n",
       "      <td>Type</td>\n",
       "      <td>Parent code</td>\n",
       "      <td>1950</td>\n",
       "      <td>1951</td>\n",
       "      <td>1952</td>\n",
       "      <td>...</td>\n",
       "      <td>2011</td>\n",
       "      <td>2012</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>2015</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2019</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1</td>\n",
       "      <td>Estimates</td>\n",
       "      <td>WORLD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>900</td>\n",
       "      <td>World</td>\n",
       "      <td>0</td>\n",
       "      <td>2.53643e+06</td>\n",
       "      <td>2.58403e+06</td>\n",
       "      <td>2.63086e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>7.04119e+06</td>\n",
       "      <td>7.12583e+06</td>\n",
       "      <td>7.21058e+06</td>\n",
       "      <td>7.29529e+06</td>\n",
       "      <td>7.3798e+06</td>\n",
       "      <td>7.46402e+06</td>\n",
       "      <td>7.54786e+06</td>\n",
       "      <td>7.63109e+06</td>\n",
       "      <td>7.71347e+06</td>\n",
       "      <td>7.7948e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2</td>\n",
       "      <td>Estimates</td>\n",
       "      <td>UN development groups</td>\n",
       "      <td>a</td>\n",
       "      <td>1803</td>\n",
       "      <td>Label/Separator</td>\n",
       "      <td>900</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>3</td>\n",
       "      <td>Estimates</td>\n",
       "      <td>More developed regions</td>\n",
       "      <td>b</td>\n",
       "      <td>901</td>\n",
       "      <td>Development Group</td>\n",
       "      <td>1803</td>\n",
       "      <td>814819</td>\n",
       "      <td>824004</td>\n",
       "      <td>833720</td>\n",
       "      <td>...</td>\n",
       "      <td>1.23956e+06</td>\n",
       "      <td>1.24411e+06</td>\n",
       "      <td>1.24845e+06</td>\n",
       "      <td>1.25262e+06</td>\n",
       "      <td>1.25662e+06</td>\n",
       "      <td>1.26048e+06</td>\n",
       "      <td>1.26415e+06</td>\n",
       "      <td>1.26756e+06</td>\n",
       "      <td>1.27063e+06</td>\n",
       "      <td>1.2733e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>4</td>\n",
       "      <td>Estimates</td>\n",
       "      <td>Less developed regions</td>\n",
       "      <td>c</td>\n",
       "      <td>902</td>\n",
       "      <td>Development Group</td>\n",
       "      <td>1803</td>\n",
       "      <td>1.72161e+06</td>\n",
       "      <td>1.76003e+06</td>\n",
       "      <td>1.79714e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>5.80164e+06</td>\n",
       "      <td>5.88171e+06</td>\n",
       "      <td>5.96213e+06</td>\n",
       "      <td>6.04268e+06</td>\n",
       "      <td>6.12317e+06</td>\n",
       "      <td>6.20354e+06</td>\n",
       "      <td>6.28371e+06</td>\n",
       "      <td>6.36353e+06</td>\n",
       "      <td>6.44284e+06</td>\n",
       "      <td>6.52149e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300</th>\n",
       "      <td>285</td>\n",
       "      <td>Estimates</td>\n",
       "      <td>Bermuda</td>\n",
       "      <td>14</td>\n",
       "      <td>60</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>918</td>\n",
       "      <td>37.256</td>\n",
       "      <td>37.8</td>\n",
       "      <td>38.437</td>\n",
       "      <td>...</td>\n",
       "      <td>65.076</td>\n",
       "      <td>64.737</td>\n",
       "      <td>64.381</td>\n",
       "      <td>64.038</td>\n",
       "      <td>63.695</td>\n",
       "      <td>63.36</td>\n",
       "      <td>63.04</td>\n",
       "      <td>62.763</td>\n",
       "      <td>62.508</td>\n",
       "      <td>62.273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301</th>\n",
       "      <td>286</td>\n",
       "      <td>Estimates</td>\n",
       "      <td>Canada</td>\n",
       "      <td>NaN</td>\n",
       "      <td>124</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>918</td>\n",
       "      <td>13733.4</td>\n",
       "      <td>14078.4</td>\n",
       "      <td>14445.5</td>\n",
       "      <td>...</td>\n",
       "      <td>34539.2</td>\n",
       "      <td>34922</td>\n",
       "      <td>35296.5</td>\n",
       "      <td>35664.3</td>\n",
       "      <td>36026.7</td>\n",
       "      <td>36382.9</td>\n",
       "      <td>36732.1</td>\n",
       "      <td>37074.6</td>\n",
       "      <td>37411</td>\n",
       "      <td>37742.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>302</th>\n",
       "      <td>287</td>\n",
       "      <td>Estimates</td>\n",
       "      <td>Greenland</td>\n",
       "      <td>26</td>\n",
       "      <td>304</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>918</td>\n",
       "      <td>22.993</td>\n",
       "      <td>23.466</td>\n",
       "      <td>23.936</td>\n",
       "      <td>...</td>\n",
       "      <td>56.555</td>\n",
       "      <td>56.477</td>\n",
       "      <td>56.412</td>\n",
       "      <td>56.383</td>\n",
       "      <td>56.378</td>\n",
       "      <td>56.408</td>\n",
       "      <td>56.473</td>\n",
       "      <td>56.565</td>\n",
       "      <td>56.66</td>\n",
       "      <td>56.772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>288</td>\n",
       "      <td>Estimates</td>\n",
       "      <td>Saint Pierre and Miquelon</td>\n",
       "      <td>2</td>\n",
       "      <td>666</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>918</td>\n",
       "      <td>4.567</td>\n",
       "      <td>4.609</td>\n",
       "      <td>4.648</td>\n",
       "      <td>...</td>\n",
       "      <td>6.323</td>\n",
       "      <td>6.251</td>\n",
       "      <td>6.168</td>\n",
       "      <td>6.073</td>\n",
       "      <td>5.992</td>\n",
       "      <td>5.933</td>\n",
       "      <td>5.885</td>\n",
       "      <td>5.845</td>\n",
       "      <td>5.821</td>\n",
       "      <td>5.795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304</th>\n",
       "      <td>289</td>\n",
       "      <td>Estimates</td>\n",
       "      <td>United States of America</td>\n",
       "      <td>35</td>\n",
       "      <td>840</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>918</td>\n",
       "      <td>158804</td>\n",
       "      <td>160872</td>\n",
       "      <td>163266</td>\n",
       "      <td>...</td>\n",
       "      <td>311584</td>\n",
       "      <td>314044</td>\n",
       "      <td>316401</td>\n",
       "      <td>318673</td>\n",
       "      <td>320878</td>\n",
       "      <td>323016</td>\n",
       "      <td>325085</td>\n",
       "      <td>327096</td>\n",
       "      <td>329065</td>\n",
       "      <td>331003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>290 rows × 78 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0 Unnamed: 1                            Unnamed: 2 Unnamed: 3  \\\n",
       "15       Index    Variant  Region, subregion, country or area *      Notes   \n",
       "16           1  Estimates                                 WORLD        NaN   \n",
       "17           2  Estimates                 UN development groups          a   \n",
       "18           3  Estimates                More developed regions          b   \n",
       "19           4  Estimates                Less developed regions          c   \n",
       "..         ...        ...                                   ...        ...   \n",
       "300        285  Estimates                               Bermuda         14   \n",
       "301        286  Estimates                                Canada        NaN   \n",
       "302        287  Estimates                             Greenland         26   \n",
       "303        288  Estimates             Saint Pierre and Miquelon          2   \n",
       "304        289  Estimates              United States of America         35   \n",
       "\n",
       "       Unnamed: 4         Unnamed: 5   Unnamed: 6   Unnamed: 7   Unnamed: 8  \\\n",
       "15   Country code               Type  Parent code         1950         1951   \n",
       "16            900              World            0  2.53643e+06  2.58403e+06   \n",
       "17           1803    Label/Separator          900          ...          ...   \n",
       "18            901  Development Group         1803       814819       824004   \n",
       "19            902  Development Group         1803  1.72161e+06  1.76003e+06   \n",
       "..            ...                ...          ...          ...          ...   \n",
       "300            60       Country/Area          918       37.256         37.8   \n",
       "301           124       Country/Area          918      13733.4      14078.4   \n",
       "302           304       Country/Area          918       22.993       23.466   \n",
       "303           666       Country/Area          918        4.567        4.609   \n",
       "304           840       Country/Area          918       158804       160872   \n",
       "\n",
       "      Unnamed: 9  ...  Unnamed: 68  Unnamed: 69  Unnamed: 70  Unnamed: 71  \\\n",
       "15          1952  ...         2011         2012         2013         2014   \n",
       "16   2.63086e+06  ...  7.04119e+06  7.12583e+06  7.21058e+06  7.29529e+06   \n",
       "17           ...  ...          ...          ...          ...          ...   \n",
       "18        833720  ...  1.23956e+06  1.24411e+06  1.24845e+06  1.25262e+06   \n",
       "19   1.79714e+06  ...  5.80164e+06  5.88171e+06  5.96213e+06  6.04268e+06   \n",
       "..           ...  ...          ...          ...          ...          ...   \n",
       "300       38.437  ...       65.076       64.737       64.381       64.038   \n",
       "301      14445.5  ...      34539.2        34922      35296.5      35664.3   \n",
       "302       23.936  ...       56.555       56.477       56.412       56.383   \n",
       "303        4.648  ...        6.323        6.251        6.168        6.073   \n",
       "304       163266  ...       311584       314044       316401       318673   \n",
       "\n",
       "     Unnamed: 72  Unnamed: 73  Unnamed: 74  Unnamed: 75  Unnamed: 76  \\\n",
       "15          2015         2016         2017         2018         2019   \n",
       "16    7.3798e+06  7.46402e+06  7.54786e+06  7.63109e+06  7.71347e+06   \n",
       "17           ...          ...          ...          ...          ...   \n",
       "18   1.25662e+06  1.26048e+06  1.26415e+06  1.26756e+06  1.27063e+06   \n",
       "19   6.12317e+06  6.20354e+06  6.28371e+06  6.36353e+06  6.44284e+06   \n",
       "..           ...          ...          ...          ...          ...   \n",
       "300       63.695        63.36        63.04       62.763       62.508   \n",
       "301      36026.7      36382.9      36732.1      37074.6        37411   \n",
       "302       56.378       56.408       56.473       56.565        56.66   \n",
       "303        5.992        5.933        5.885        5.845        5.821   \n",
       "304       320878       323016       325085       327096       329065   \n",
       "\n",
       "     Unnamed: 77  \n",
       "15          2020  \n",
       "16    7.7948e+06  \n",
       "17           ...  \n",
       "18    1.2733e+06  \n",
       "19   6.52149e+06  \n",
       "..           ...  \n",
       "300       62.273  \n",
       "301      37742.2  \n",
       "302       56.772  \n",
       "303        5.795  \n",
       "304       331003  \n",
       "\n",
       "[290 rows x 78 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remove rows with \"NaN\" in Unnamed: 2\n",
    "pop_data_1 = population_data[pd.notnull(population_data[\"Unnamed: 2\"])]\n",
    "pop_data_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select columns we want to keep\n",
    "pop_data_2 = pd.DataFrame(pop_data_1, columns = ['Unnamed: 2', 'Unnamed: 5', 'Unnamed: 66', \n",
    "                                                 'Unnamed: 67', 'Unnamed: 68', 'Unnamed: 69',\n",
    "                                                 'Unnamed: 70','Unnamed: 71', 'Unnamed: 72',\n",
    "                                                 'Unnamed: 73', 'Unnamed: 74','Unnamed: 75', \n",
    "                                                 'Unnamed: 76','Unnamed: 77',]).copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Type</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Region, subregion, country or area *</td>\n",
       "      <td>Type</td>\n",
       "      <td>2009</td>\n",
       "      <td>2010</td>\n",
       "      <td>2011</td>\n",
       "      <td>2012</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>2015</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2019</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>WORLD</td>\n",
       "      <td>World</td>\n",
       "      <td>6.87277e+06</td>\n",
       "      <td>6.95682e+06</td>\n",
       "      <td>7.04119e+06</td>\n",
       "      <td>7.12583e+06</td>\n",
       "      <td>7.21058e+06</td>\n",
       "      <td>7.29529e+06</td>\n",
       "      <td>7.3798e+06</td>\n",
       "      <td>7.46402e+06</td>\n",
       "      <td>7.54786e+06</td>\n",
       "      <td>7.63109e+06</td>\n",
       "      <td>7.71347e+06</td>\n",
       "      <td>7.7948e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>UN development groups</td>\n",
       "      <td>Label/Separator</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>More developed regions</td>\n",
       "      <td>Development Group</td>\n",
       "      <td>1.22974e+06</td>\n",
       "      <td>1.23477e+06</td>\n",
       "      <td>1.23956e+06</td>\n",
       "      <td>1.24411e+06</td>\n",
       "      <td>1.24845e+06</td>\n",
       "      <td>1.25262e+06</td>\n",
       "      <td>1.25662e+06</td>\n",
       "      <td>1.26048e+06</td>\n",
       "      <td>1.26415e+06</td>\n",
       "      <td>1.26756e+06</td>\n",
       "      <td>1.27063e+06</td>\n",
       "      <td>1.2733e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Less developed regions</td>\n",
       "      <td>Development Group</td>\n",
       "      <td>5.64303e+06</td>\n",
       "      <td>5.72206e+06</td>\n",
       "      <td>5.80164e+06</td>\n",
       "      <td>5.88171e+06</td>\n",
       "      <td>5.96213e+06</td>\n",
       "      <td>6.04268e+06</td>\n",
       "      <td>6.12317e+06</td>\n",
       "      <td>6.20354e+06</td>\n",
       "      <td>6.28371e+06</td>\n",
       "      <td>6.36353e+06</td>\n",
       "      <td>6.44284e+06</td>\n",
       "      <td>6.52149e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300</th>\n",
       "      <td>Bermuda</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>65.675</td>\n",
       "      <td>65.388</td>\n",
       "      <td>65.076</td>\n",
       "      <td>64.737</td>\n",
       "      <td>64.381</td>\n",
       "      <td>64.038</td>\n",
       "      <td>63.695</td>\n",
       "      <td>63.36</td>\n",
       "      <td>63.04</td>\n",
       "      <td>62.763</td>\n",
       "      <td>62.508</td>\n",
       "      <td>62.273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301</th>\n",
       "      <td>Canada</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>33746.1</td>\n",
       "      <td>34147.6</td>\n",
       "      <td>34539.2</td>\n",
       "      <td>34922</td>\n",
       "      <td>35296.5</td>\n",
       "      <td>35664.3</td>\n",
       "      <td>36026.7</td>\n",
       "      <td>36382.9</td>\n",
       "      <td>36732.1</td>\n",
       "      <td>37074.6</td>\n",
       "      <td>37411</td>\n",
       "      <td>37742.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>302</th>\n",
       "      <td>Greenland</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>56.729</td>\n",
       "      <td>56.638</td>\n",
       "      <td>56.555</td>\n",
       "      <td>56.477</td>\n",
       "      <td>56.412</td>\n",
       "      <td>56.383</td>\n",
       "      <td>56.378</td>\n",
       "      <td>56.408</td>\n",
       "      <td>56.473</td>\n",
       "      <td>56.565</td>\n",
       "      <td>56.66</td>\n",
       "      <td>56.772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>Saint Pierre and Miquelon</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>6.343</td>\n",
       "      <td>6.361</td>\n",
       "      <td>6.323</td>\n",
       "      <td>6.251</td>\n",
       "      <td>6.168</td>\n",
       "      <td>6.073</td>\n",
       "      <td>5.992</td>\n",
       "      <td>5.933</td>\n",
       "      <td>5.885</td>\n",
       "      <td>5.845</td>\n",
       "      <td>5.821</td>\n",
       "      <td>5.795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>306308</td>\n",
       "      <td>309011</td>\n",
       "      <td>311584</td>\n",
       "      <td>314044</td>\n",
       "      <td>316401</td>\n",
       "      <td>318673</td>\n",
       "      <td>320878</td>\n",
       "      <td>323016</td>\n",
       "      <td>325085</td>\n",
       "      <td>327096</td>\n",
       "      <td>329065</td>\n",
       "      <td>331003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>290 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  Country               Type         2009  \\\n",
       "15   Region, subregion, country or area *               Type         2009   \n",
       "16                                  WORLD              World  6.87277e+06   \n",
       "17                  UN development groups    Label/Separator          ...   \n",
       "18                 More developed regions  Development Group  1.22974e+06   \n",
       "19                 Less developed regions  Development Group  5.64303e+06   \n",
       "..                                    ...                ...          ...   \n",
       "300                               Bermuda       Country/Area       65.675   \n",
       "301                                Canada       Country/Area      33746.1   \n",
       "302                             Greenland       Country/Area       56.729   \n",
       "303             Saint Pierre and Miquelon       Country/Area        6.343   \n",
       "304              United States of America       Country/Area       306308   \n",
       "\n",
       "            2010         2011         2012         2013         2014  \\\n",
       "15          2010         2011         2012         2013         2014   \n",
       "16   6.95682e+06  7.04119e+06  7.12583e+06  7.21058e+06  7.29529e+06   \n",
       "17           ...          ...          ...          ...          ...   \n",
       "18   1.23477e+06  1.23956e+06  1.24411e+06  1.24845e+06  1.25262e+06   \n",
       "19   5.72206e+06  5.80164e+06  5.88171e+06  5.96213e+06  6.04268e+06   \n",
       "..           ...          ...          ...          ...          ...   \n",
       "300       65.388       65.076       64.737       64.381       64.038   \n",
       "301      34147.6      34539.2        34922      35296.5      35664.3   \n",
       "302       56.638       56.555       56.477       56.412       56.383   \n",
       "303        6.361        6.323        6.251        6.168        6.073   \n",
       "304       309011       311584       314044       316401       318673   \n",
       "\n",
       "            2015         2016         2017         2018         2019  \\\n",
       "15          2015         2016         2017         2018         2019   \n",
       "16    7.3798e+06  7.46402e+06  7.54786e+06  7.63109e+06  7.71347e+06   \n",
       "17           ...          ...          ...          ...          ...   \n",
       "18   1.25662e+06  1.26048e+06  1.26415e+06  1.26756e+06  1.27063e+06   \n",
       "19   6.12317e+06  6.20354e+06  6.28371e+06  6.36353e+06  6.44284e+06   \n",
       "..           ...          ...          ...          ...          ...   \n",
       "300       63.695        63.36        63.04       62.763       62.508   \n",
       "301      36026.7      36382.9      36732.1      37074.6        37411   \n",
       "302       56.378       56.408       56.473       56.565        56.66   \n",
       "303        5.992        5.933        5.885        5.845        5.821   \n",
       "304       320878       323016       325085       327096       329065   \n",
       "\n",
       "            2020  \n",
       "15          2020  \n",
       "16    7.7948e+06  \n",
       "17           ...  \n",
       "18    1.2733e+06  \n",
       "19   6.52149e+06  \n",
       "..           ...  \n",
       "300       62.273  \n",
       "301      37742.2  \n",
       "302       56.772  \n",
       "303        5.795  \n",
       "304       331003  \n",
       "\n",
       "[290 rows x 14 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_data_3 = pop_data_2.rename(columns={\"Unnamed: 2\": \"Country\", \"Unnamed: 5\": \"Type\", \"Unnamed: 66\": \"2009\", \n",
    "                                       'Unnamed: 67': \"2010\", 'Unnamed: 68': \"2011\", 'Unnamed: 69': \"2012\",\n",
    "                                       'Unnamed: 70': \"2013\", 'Unnamed: 71': \"2014\", 'Unnamed: 72': \"2015\",\n",
    "                                       'Unnamed: 73': \"2016\", 'Unnamed: 74': \"2017\", 'Unnamed: 75': \"2018\",\n",
    "                                       'Unnamed: 76': \"2019\", 'Unnamed: 77': \"2020\"})\n",
    "pop_data_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>Type</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>WORLD</td>\n",
       "      <td>World</td>\n",
       "      <td>6.87277e+06</td>\n",
       "      <td>6.95682e+06</td>\n",
       "      <td>7.04119e+06</td>\n",
       "      <td>7.12583e+06</td>\n",
       "      <td>7.21058e+06</td>\n",
       "      <td>7.29529e+06</td>\n",
       "      <td>7.3798e+06</td>\n",
       "      <td>7.46402e+06</td>\n",
       "      <td>7.54786e+06</td>\n",
       "      <td>7.63109e+06</td>\n",
       "      <td>7.71347e+06</td>\n",
       "      <td>7.7948e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Burundi</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>8397.66</td>\n",
       "      <td>8675.61</td>\n",
       "      <td>8958.41</td>\n",
       "      <td>9245.99</td>\n",
       "      <td>9540.3</td>\n",
       "      <td>9844.3</td>\n",
       "      <td>10160</td>\n",
       "      <td>10488</td>\n",
       "      <td>10827</td>\n",
       "      <td>11175.4</td>\n",
       "      <td>11530.6</td>\n",
       "      <td>11890.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Comoros</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>673.251</td>\n",
       "      <td>689.696</td>\n",
       "      <td>706.578</td>\n",
       "      <td>723.865</td>\n",
       "      <td>741.511</td>\n",
       "      <td>759.39</td>\n",
       "      <td>777.435</td>\n",
       "      <td>795.597</td>\n",
       "      <td>813.89</td>\n",
       "      <td>832.322</td>\n",
       "      <td>850.891</td>\n",
       "      <td>869.595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Djibouti</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>827.82</td>\n",
       "      <td>840.194</td>\n",
       "      <td>853.671</td>\n",
       "      <td>868.136</td>\n",
       "      <td>883.296</td>\n",
       "      <td>898.707</td>\n",
       "      <td>913.998</td>\n",
       "      <td>929.117</td>\n",
       "      <td>944.1</td>\n",
       "      <td>958.923</td>\n",
       "      <td>973.557</td>\n",
       "      <td>988.002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Eritrea</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>3119.92</td>\n",
       "      <td>3170.44</td>\n",
       "      <td>3213.97</td>\n",
       "      <td>3250.1</td>\n",
       "      <td>3281.45</td>\n",
       "      <td>3311.44</td>\n",
       "      <td>3342.82</td>\n",
       "      <td>3376.56</td>\n",
       "      <td>3412.89</td>\n",
       "      <td>3452.8</td>\n",
       "      <td>3497.12</td>\n",
       "      <td>3546.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300</th>\n",
       "      <td>Bermuda</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>65.675</td>\n",
       "      <td>65.388</td>\n",
       "      <td>65.076</td>\n",
       "      <td>64.737</td>\n",
       "      <td>64.381</td>\n",
       "      <td>64.038</td>\n",
       "      <td>63.695</td>\n",
       "      <td>63.36</td>\n",
       "      <td>63.04</td>\n",
       "      <td>62.763</td>\n",
       "      <td>62.508</td>\n",
       "      <td>62.273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301</th>\n",
       "      <td>Canada</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>33746.1</td>\n",
       "      <td>34147.6</td>\n",
       "      <td>34539.2</td>\n",
       "      <td>34922</td>\n",
       "      <td>35296.5</td>\n",
       "      <td>35664.3</td>\n",
       "      <td>36026.7</td>\n",
       "      <td>36382.9</td>\n",
       "      <td>36732.1</td>\n",
       "      <td>37074.6</td>\n",
       "      <td>37411</td>\n",
       "      <td>37742.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>302</th>\n",
       "      <td>Greenland</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>56.729</td>\n",
       "      <td>56.638</td>\n",
       "      <td>56.555</td>\n",
       "      <td>56.477</td>\n",
       "      <td>56.412</td>\n",
       "      <td>56.383</td>\n",
       "      <td>56.378</td>\n",
       "      <td>56.408</td>\n",
       "      <td>56.473</td>\n",
       "      <td>56.565</td>\n",
       "      <td>56.66</td>\n",
       "      <td>56.772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>Saint Pierre and Miquelon</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>6.343</td>\n",
       "      <td>6.361</td>\n",
       "      <td>6.323</td>\n",
       "      <td>6.251</td>\n",
       "      <td>6.168</td>\n",
       "      <td>6.073</td>\n",
       "      <td>5.992</td>\n",
       "      <td>5.933</td>\n",
       "      <td>5.885</td>\n",
       "      <td>5.845</td>\n",
       "      <td>5.821</td>\n",
       "      <td>5.795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>Country/Area</td>\n",
       "      <td>306308</td>\n",
       "      <td>309011</td>\n",
       "      <td>311584</td>\n",
       "      <td>314044</td>\n",
       "      <td>316401</td>\n",
       "      <td>318673</td>\n",
       "      <td>320878</td>\n",
       "      <td>323016</td>\n",
       "      <td>325085</td>\n",
       "      <td>327096</td>\n",
       "      <td>329065</td>\n",
       "      <td>331003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>236 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       Country          Type         2009         2010  \\\n",
       "16                       WORLD         World  6.87277e+06  6.95682e+06   \n",
       "42                     Burundi  Country/Area      8397.66      8675.61   \n",
       "43                     Comoros  Country/Area      673.251      689.696   \n",
       "44                    Djibouti  Country/Area       827.82      840.194   \n",
       "45                     Eritrea  Country/Area      3119.92      3170.44   \n",
       "..                         ...           ...          ...          ...   \n",
       "300                    Bermuda  Country/Area       65.675       65.388   \n",
       "301                     Canada  Country/Area      33746.1      34147.6   \n",
       "302                  Greenland  Country/Area       56.729       56.638   \n",
       "303  Saint Pierre and Miquelon  Country/Area        6.343        6.361   \n",
       "304   United States of America  Country/Area       306308       309011   \n",
       "\n",
       "            2011         2012         2013         2014        2015  \\\n",
       "16   7.04119e+06  7.12583e+06  7.21058e+06  7.29529e+06  7.3798e+06   \n",
       "42       8958.41      9245.99       9540.3       9844.3       10160   \n",
       "43       706.578      723.865      741.511       759.39     777.435   \n",
       "44       853.671      868.136      883.296      898.707     913.998   \n",
       "45       3213.97       3250.1      3281.45      3311.44     3342.82   \n",
       "..           ...          ...          ...          ...         ...   \n",
       "300       65.076       64.737       64.381       64.038      63.695   \n",
       "301      34539.2        34922      35296.5      35664.3     36026.7   \n",
       "302       56.555       56.477       56.412       56.383      56.378   \n",
       "303        6.323        6.251        6.168        6.073       5.992   \n",
       "304       311584       314044       316401       318673      320878   \n",
       "\n",
       "            2016         2017         2018         2019        2020  \n",
       "16   7.46402e+06  7.54786e+06  7.63109e+06  7.71347e+06  7.7948e+06  \n",
       "42         10488        10827      11175.4      11530.6     11890.8  \n",
       "43       795.597       813.89      832.322      850.891     869.595  \n",
       "44       929.117        944.1      958.923      973.557     988.002  \n",
       "45       3376.56      3412.89       3452.8      3497.12     3546.43  \n",
       "..           ...          ...          ...          ...         ...  \n",
       "300        63.36        63.04       62.763       62.508      62.273  \n",
       "301      36382.9      36732.1      37074.6        37411     37742.2  \n",
       "302       56.408       56.473       56.565        56.66      56.772  \n",
       "303        5.933        5.885        5.845        5.821       5.795  \n",
       "304       323016       325085       327096       329065      331003  \n",
       "\n",
       "[236 rows x 14 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Keep only world and country rows\n",
    "pop_data_4 = pop_data_3.loc[pop_data_3['Type'].isin(['Country/Area','World'])]\n",
    "pop_data_4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save scrubbed pop file to .csv\n",
    "pop_data_4.to_csv(r'Data/pop_data_2009-2019.csv', index = False, header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}