world-population/data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Population Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import data\n",
    "pop_total = pd.read_csv('data-sources/population_total.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_years(df, start_year=2000):\n",
    "    # remove all year columns from df that are before start_year\n",
    "    \n",
    "    df_copy = df.copy()\n",
    "    \n",
    "    # collect column headers to be removed\n",
    "    cols = []\n",
    "    for col in df_copy:\n",
    "        if (col.isdigit() and int(col) < start_year):\n",
    "            cols.append(col)\n",
    "\n",
    "    # drop columns\n",
    "    df_copy = df_copy.drop(columns=cols, axis=1)\n",
    "    \n",
    "    return df_copy\n",
    "    \n",
    "pop_total_short = remove_years(pop_total, start_year=2000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove columns otherwise not required\n",
    "pop_cols = ['Indicator Name', '2018', 'Indicator Code', 'Country Code']\n",
    "pop_total_short = pop_total_short.drop(columns=pop_cols, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# transpose dataframe\n",
    "pop_total_short = pop_total_short.transpose()\n",
    "\n",
    "# change column headers to country names & drop duplicate column\n",
    "pop_total_short.columns = list(pop_total_short.loc['Country Name' , : ])\n",
    "pop_total_short = pop_total_short.drop(['Country Name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove unions\n",
    "unions = [\n",
    "    \"Central Europe and the Baltics\", \"Caribbean small states\", \"East Asia & Pacific (excluding high income)\", \n",
    "    \"Early-demographic dividend\", \"East Asia & Pacific\", \"Europe & Central Asia (excluding high income)\", \n",
    "    \"Europe & Central Asia\", \"Euro area\", \"European Union\", \"Fragile and conflict affected situations\",\n",
    "    \"High income\", \"Heavily indebted poor countries (HIPC)\", \"IBRD only\", \"IDA & IBRD total\", \"IDA total\",\n",
    "    \"IDA blend\", \"IDA only\", \"Latin America & Caribbean\", \"Not classified\" ,\"Latin America & Caribbean (excluding high income)\", \n",
    "    \"Least developed countries: UN classification\", \"Low income\", \"Lower middle income\", \"Low & middle income\", \n",
    "    \"Late-demographic dividend\", \"Middle East & North Africa\", \"Middle income\", \n",
    "    \"Middle East & North Africa (excluding high income)\", \"OECD members\", \"Other small states\", \n",
    "    \"Pre-demographic dividend\", \"Pacific island small states\", \"Post-demographic dividend\", \n",
    "    \"Sub-Saharan Africa (excluding high income)\", \"Sub-Saharan Africa\", \"Small states\", \n",
    "    \"East Asia & Pacific (IDA & IBRD countries)\", \"Europe & Central Asia (IDA & IBRD countries)\", \n",
    "    \"Latin America & the Caribbean (IDA & IBRD countries)\", \"Middle East & North Africa (IDA & IBRD countries)\",\n",
    "    \"South Asia (IDA & IBRD)\", \"Sub-Saharan Africa (IDA & IBRD countries)\", \"South Asia\", \"Upper middle income\", \n",
    "    \"World\", \"Arab World\", \"North America\"\n",
    "]\n",
    "pop_total_short = pop_total_short.drop(columns=unions, axis=1)\n",
    "\n",
    "# remove countries with less than 10M inhabitants in 2000\n",
    "small_countries = []\n",
    "for col in pop_total_short:\n",
    "    if pop_total_short[col]['2000'] <= 10000000:\n",
    "        small_countries.append(col)\n",
    "\n",
    "pop_total_short = pop_total_short.drop(columns=small_countries, axis=1)\n",
    "\n",
    "# drop columns with more than two missing values\n",
    "pop_total_short = pop_total_short.dropna(axis='columns', thresh=2)\n",
    "\n",
    "# order by max population in descending order\n",
    "pop_total_short = pop_total_short.sort_values(by='2017', axis=1, ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>China</th>\n",
       "      <th>India</th>\n",
       "      <th>United States</th>\n",
       "      <th>Indonesia</th>\n",
       "      <th>Brazil</th>\n",
       "      <th>Pakistan</th>\n",
       "      <th>Nigeria</th>\n",
       "      <th>Bangladesh</th>\n",
       "      <th>Russian Federation</th>\n",
       "      <th>Mexico</th>\n",
       "      <th>...</th>\n",
       "      <th>Guatemala</th>\n",
       "      <th>Ecuador</th>\n",
       "      <th>Zimbabwe</th>\n",
       "      <th>Cambodia</th>\n",
       "      <th>Cuba</th>\n",
       "      <th>Belgium</th>\n",
       "      <th>Greece</th>\n",
       "      <th>Czech Republic</th>\n",
       "      <th>Portugal</th>\n",
       "      <th>Hungary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001</th>\n",
       "      <td>0.00729025</td>\n",
       "      <td>0.0174986</td>\n",
       "      <td>0.00994656</td>\n",
       "      <td>0.0140213</td>\n",
       "      <td>0.0140517</td>\n",
       "      <td>0.0222212</td>\n",
       "      <td>0.0254301</td>\n",
       "      <td>0.0191966</td>\n",
       "      <td>-0.00423253</td>\n",
       "      <td>0.0132462</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0235352</td>\n",
       "      <td>0.0177501</td>\n",
       "      <td>0.0117748</td>\n",
       "      <td>0.0205819</td>\n",
       "      <td>0.00321109</td>\n",
       "      <td>0.00344543</td>\n",
       "      <td>0.00521238</td>\n",
       "      <td>-0.00375015</td>\n",
       "      <td>0.00707723</td>\n",
       "      <td>-0.00229116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002</th>\n",
       "      <td>0.0140618</td>\n",
       "      <td>0.0349045</td>\n",
       "      <td>0.0193604</td>\n",
       "      <td>0.0282104</td>\n",
       "      <td>0.0277455</td>\n",
       "      <td>0.0442587</td>\n",
       "      <td>0.0516109</td>\n",
       "      <td>0.038147</td>\n",
       "      <td>-0.00880315</td>\n",
       "      <td>0.0259137</td>\n",
       "      <td>...</td>\n",
       "      <td>0.047903</td>\n",
       "      <td>0.0351159</td>\n",
       "      <td>0.0227678</td>\n",
       "      <td>0.039694</td>\n",
       "      <td>0.00603207</td>\n",
       "      <td>0.00795366</td>\n",
       "      <td>0.00890392</td>\n",
       "      <td>-0.00567008</td>\n",
       "      <td>0.0126078</td>\n",
       "      <td>-0.00512811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>0.0203977</td>\n",
       "      <td>0.0522073</td>\n",
       "      <td>0.0281594</td>\n",
       "      <td>0.0425677</td>\n",
       "      <td>0.0410443</td>\n",
       "      <td>0.0662713</td>\n",
       "      <td>0.0786299</td>\n",
       "      <td>0.056526</td>\n",
       "      <td>-0.0132902</td>\n",
       "      <td>0.038545</td>\n",
       "      <td>...</td>\n",
       "      <td>0.072934</td>\n",
       "      <td>0.0523419</td>\n",
       "      <td>0.03368</td>\n",
       "      <td>0.0576654</td>\n",
       "      <td>0.0084433</td>\n",
       "      <td>0.0121822</td>\n",
       "      <td>0.0113145</td>\n",
       "      <td>-0.00595462</td>\n",
       "      <td>0.0164164</td>\n",
       "      <td>-0.00797368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>0.0264762</td>\n",
       "      <td>0.069403</td>\n",
       "      <td>0.037719</td>\n",
       "      <td>0.0570776</td>\n",
       "      <td>0.0539164</td>\n",
       "      <td>0.0884834</td>\n",
       "      <td>0.106591</td>\n",
       "      <td>0.0739182</td>\n",
       "      <td>-0.0172549</td>\n",
       "      <td>0.0518672</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0983784</td>\n",
       "      <td>0.0697663</td>\n",
       "      <td>0.0454303</td>\n",
       "      <td>0.0749668</td>\n",
       "      <td>0.0104213</td>\n",
       "      <td>0.0165723</td>\n",
       "      <td>0.0138197</td>\n",
       "      <td>-0.00565204</td>\n",
       "      <td>0.0188498</td>\n",
       "      <td>-0.010168</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 76 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           China      India United States  Indonesia     Brazil   Pakistan  \\\n",
       "2000           0          0             0          0          0          0   \n",
       "2001  0.00729025  0.0174986    0.00994656  0.0140213  0.0140517  0.0222212   \n",
       "2002   0.0140618  0.0349045     0.0193604  0.0282104  0.0277455  0.0442587   \n",
       "2003   0.0203977  0.0522073     0.0281594  0.0425677  0.0410443  0.0662713   \n",
       "2004   0.0264762   0.069403      0.037719  0.0570776  0.0539164  0.0884834   \n",
       "\n",
       "        Nigeria Bangladesh Russian Federation     Mexico  ...  Guatemala  \\\n",
       "2000          0          0                  0          0  ...          0   \n",
       "2001  0.0254301  0.0191966        -0.00423253  0.0132462  ...  0.0235352   \n",
       "2002  0.0516109   0.038147        -0.00880315  0.0259137  ...   0.047903   \n",
       "2003  0.0786299   0.056526         -0.0132902   0.038545  ...   0.072934   \n",
       "2004   0.106591  0.0739182         -0.0172549  0.0518672  ...  0.0983784   \n",
       "\n",
       "        Ecuador   Zimbabwe   Cambodia        Cuba     Belgium      Greece  \\\n",
       "2000          0          0          0           0           0           0   \n",
       "2001  0.0177501  0.0117748  0.0205819  0.00321109  0.00344543  0.00521238   \n",
       "2002  0.0351159  0.0227678   0.039694  0.00603207  0.00795366  0.00890392   \n",
       "2003  0.0523419    0.03368  0.0576654   0.0084433   0.0121822   0.0113145   \n",
       "2004  0.0697663  0.0454303  0.0749668   0.0104213   0.0165723   0.0138197   \n",
       "\n",
       "     Czech Republic    Portugal     Hungary  \n",
       "2000              0           0           0  \n",
       "2001    -0.00375015  0.00707723 -0.00229116  \n",
       "2002    -0.00567008   0.0126078 -0.00512811  \n",
       "2003    -0.00595462   0.0164164 -0.00797368  \n",
       "2004    -0.00565204   0.0188498   -0.010168  \n",
       "\n",
       "[5 rows x 76 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate population percentage change\n",
    "pop_perc = pop_total_short.copy()\n",
    "\n",
    "for col in pop_total_short:\n",
    "    base_val = pop_total_short[col].iloc[0]\n",
    "    pop_perc[col] = pop_perc[col]/base_val - 1\n",
    "    \n",
    "pop_perc.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# adjust index column\n",
    "pop_perc = pop_perc.reset_index()\n",
    "pop_perc = pop_perc.rename(columns={'index': 'Year'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[0.07277341022216513, 0.037541859894346594, 0.003014940573084428]\n"
     ]
    }
   ],
   "source": [
    "# add columns for bottom/top lines with distance representing total population\n",
    "min_population = min(list(pop_total_short.min()))\n",
    "max_population = float(max(list(pop_total_short.max())))\n",
    "\n",
    "# scale linearly between min./max. thickness\n",
    "max_thickness = 0.10\n",
    "min_thickness = 0.003\n",
    "\n",
    "slope = (max_thickness - min_thickness)/(max_population - min_population)\n",
    "intercept = max_thickness - slope*max_population\n",
    "\n",
    "def thicknessScale(pop, m=slope, n=intercept):\n",
    "    return m*pop + n\n",
    "\n",
    "for col in pop_total_short:\n",
    "    thickness = thicknessScale(pd.to_numeric(pop_total_short[col]))    \n",
    "    thickness.index = range(18)\n",
    "    \n",
    "    # lower limit\n",
    "    pop_perc[col + '_low'] = pop_perc[col] - thickness/2\n",
    "    \n",
    "    # upper limit\n",
    "    pop_perc[col + '_up'] = pop_perc[col] + thickness/2\n",
    "    \n",
    "# thickness scale for d3 legend creation\n",
    "legend_dims = [thicknessScale(x) for x in [1000000000, 500000000, 10000000]]\n",
    "\n",
    "print(legend_dims)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Year</th>\n",
       "      <th>China</th>\n",
       "      <th>India</th>\n",
       "      <th>United States</th>\n",
       "      <th>Indonesia</th>\n",
       "      <th>Brazil</th>\n",
       "      <th>Pakistan</th>\n",
       "      <th>Nigeria</th>\n",
       "      <th>Bangladesh</th>\n",
       "      <th>Russian Federation</th>\n",
       "      <th>...</th>\n",
       "      <th>Belgium_low</th>\n",
       "      <th>Belgium_up</th>\n",
       "      <th>Greece_low</th>\n",
       "      <th>Greece_up</th>\n",
       "      <th>Czech Republic_low</th>\n",
       "      <th>Czech Republic_up</th>\n",
       "      <th>Portugal_low</th>\n",
       "      <th>Portugal_up</th>\n",
       "      <th>Hungary_low</th>\n",
       "      <th>Hungary_up</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.00151632</td>\n",
       "      <td>0.00151632</td>\n",
       "      <td>-0.00153586</td>\n",
       "      <td>0.00153586</td>\n",
       "      <td>-0.00151646</td>\n",
       "      <td>0.00151646</td>\n",
       "      <td>-0.00151768</td>\n",
       "      <td>0.00151768</td>\n",
       "      <td>-0.0015149</td>\n",
       "      <td>0.0015149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001</td>\n",
       "      <td>0.00729025</td>\n",
       "      <td>0.0174986</td>\n",
       "      <td>0.00994656</td>\n",
       "      <td>0.0140213</td>\n",
       "      <td>0.0140517</td>\n",
       "      <td>0.0222212</td>\n",
       "      <td>0.0254301</td>\n",
       "      <td>0.0191966</td>\n",
       "      <td>-0.00423253</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00192787</td>\n",
       "      <td>0.004963</td>\n",
       "      <td>0.00367454</td>\n",
       "      <td>0.00675023</td>\n",
       "      <td>-0.00526525</td>\n",
       "      <td>-0.00223505</td>\n",
       "      <td>0.00555698</td>\n",
       "      <td>0.00859748</td>\n",
       "      <td>-0.00380524</td>\n",
       "      <td>-0.000777084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2002</td>\n",
       "      <td>0.0140618</td>\n",
       "      <td>0.0349045</td>\n",
       "      <td>0.0193604</td>\n",
       "      <td>0.0282104</td>\n",
       "      <td>0.0277455</td>\n",
       "      <td>0.0442587</td>\n",
       "      <td>0.0516109</td>\n",
       "      <td>0.038147</td>\n",
       "      <td>-0.00880315</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00643447</td>\n",
       "      <td>0.00947286</td>\n",
       "      <td>0.00736467</td>\n",
       "      <td>0.0104432</td>\n",
       "      <td>-0.00718449</td>\n",
       "      <td>-0.00415567</td>\n",
       "      <td>0.0110855</td>\n",
       "      <td>0.0141301</td>\n",
       "      <td>-0.00664117</td>\n",
       "      <td>-0.00361505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2003</td>\n",
       "      <td>0.0203977</td>\n",
       "      <td>0.0522073</td>\n",
       "      <td>0.0281594</td>\n",
       "      <td>0.0425677</td>\n",
       "      <td>0.0410443</td>\n",
       "      <td>0.0662713</td>\n",
       "      <td>0.0786299</td>\n",
       "      <td>0.056526</td>\n",
       "      <td>-0.0132902</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0106615</td>\n",
       "      <td>0.0137029</td>\n",
       "      <td>0.0097743</td>\n",
       "      <td>0.0128546</td>\n",
       "      <td>-0.00746892</td>\n",
       "      <td>-0.00444031</td>\n",
       "      <td>0.0148928</td>\n",
       "      <td>0.01794</td>\n",
       "      <td>-0.00948571</td>\n",
       "      <td>-0.00646164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004</td>\n",
       "      <td>0.0264762</td>\n",
       "      <td>0.069403</td>\n",
       "      <td>0.037719</td>\n",
       "      <td>0.0570776</td>\n",
       "      <td>0.0539164</td>\n",
       "      <td>0.0884834</td>\n",
       "      <td>0.106591</td>\n",
       "      <td>0.0739182</td>\n",
       "      <td>-0.0172549</td>\n",
       "      <td>...</td>\n",
       "      <td>0.01505</td>\n",
       "      <td>0.0180946</td>\n",
       "      <td>0.0122786</td>\n",
       "      <td>0.0153608</td>\n",
       "      <td>-0.00716645</td>\n",
       "      <td>-0.00413762</td>\n",
       "      <td>0.0173253</td>\n",
       "      <td>0.0203744</td>\n",
       "      <td>-0.0116792</td>\n",
       "      <td>-0.00865674</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 229 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year       China      India United States  Indonesia     Brazil   Pakistan  \\\n",
       "0  2000           0          0             0          0          0          0   \n",
       "1  2001  0.00729025  0.0174986    0.00994656  0.0140213  0.0140517  0.0222212   \n",
       "2  2002   0.0140618  0.0349045     0.0193604  0.0282104  0.0277455  0.0442587   \n",
       "3  2003   0.0203977  0.0522073     0.0281594  0.0425677  0.0410443  0.0662713   \n",
       "4  2004   0.0264762   0.069403      0.037719  0.0570776  0.0539164  0.0884834   \n",
       "\n",
       "     Nigeria Bangladesh Russian Federation  ... Belgium_low  Belgium_up  \\\n",
       "0          0          0                  0  ... -0.00151632  0.00151632   \n",
       "1  0.0254301  0.0191966        -0.00423253  ...  0.00192787    0.004963   \n",
       "2  0.0516109   0.038147        -0.00880315  ...  0.00643447  0.00947286   \n",
       "3  0.0786299   0.056526         -0.0132902  ...   0.0106615   0.0137029   \n",
       "4   0.106591  0.0739182         -0.0172549  ...     0.01505   0.0180946   \n",
       "\n",
       "   Greece_low   Greece_up Czech Republic_low Czech Republic_up Portugal_low  \\\n",
       "0 -0.00153586  0.00153586        -0.00151646        0.00151646  -0.00151768   \n",
       "1  0.00367454  0.00675023        -0.00526525       -0.00223505   0.00555698   \n",
       "2  0.00736467   0.0104432        -0.00718449       -0.00415567    0.0110855   \n",
       "3   0.0097743   0.0128546        -0.00746892       -0.00444031    0.0148928   \n",
       "4   0.0122786   0.0153608        -0.00716645       -0.00413762    0.0173253   \n",
       "\n",
       "  Portugal_up Hungary_low   Hungary_up  \n",
       "0  0.00151768  -0.0015149    0.0015149  \n",
       "1  0.00859748 -0.00380524 -0.000777084  \n",
       "2   0.0141301 -0.00664117  -0.00361505  \n",
       "3     0.01794 -0.00948571  -0.00646164  \n",
       "4   0.0203744  -0.0116792  -0.00865674  \n",
       "\n",
       "[5 rows x 229 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# store data\n",
    "pop_perc.to_csv('pop_total.csv', index=False)\n",
    "pop_perc.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Year                        1.111222e+70\n",
       "China                       5.063269e-02\n",
       "India                       1.412239e-01\n",
       "United States               7.933280e-02\n",
       "Indonesia                   1.241092e-01\n",
       "Brazil                      1.032062e-01\n",
       "Pakistan                    2.010849e-01\n",
       "Nigeria                     2.585130e-01\n",
       "Bangladesh                  1.338163e-01\n",
       "Russian Federation         -1.817862e-02\n",
       "Mexico                      1.294307e-01\n",
       "Japan                       5.542419e-03\n",
       "Ethiopia                    2.754389e-01\n",
       "Philippines                 1.735445e-01\n",
       "Egypt, Arab Rep.            1.820945e-01\n",
       "Vietnam                     9.007332e-02\n",
       "Germany                    -3.889144e-03\n",
       "Congo, Dem. Rep.            3.261579e-01\n",
       "Iran, Islamic Rep.          1.114287e-01\n",
       "Turkey                      1.291748e-01\n",
       "Thailand                    5.599990e-02\n",
       "France                      5.600241e-02\n",
       "United Kingdom              5.548841e-02\n",
       "Italy                       3.376151e-02\n",
       "Tanzania                    3.054022e-01\n",
       "South Africa                1.148795e-01\n",
       "Myanmar                     7.876456e-02\n",
       "Korea, Rep.                 4.740778e-02\n",
       "Kenya                       2.727168e-01\n",
       "Colombia                    1.136755e-01\n",
       "                                ...     \n",
       "Syrian Arab Republic_low    1.499623e-01\n",
       "Syrian Arab Republic_up     1.536045e-01\n",
       "Chile_low                   9.320554e-02\n",
       "Chile_up                    9.669343e-02\n",
       "Kazakhstan_low              7.925217e-02\n",
       "Kazakhstan_up               8.269615e-02\n",
       "Netherlands_low             3.603048e-02\n",
       "Netherlands_up              3.950533e-02\n",
       "Zambia_low                  2.787163e-01\n",
       "Zambia_up                   2.819767e-01\n",
       "Guatemala_low               2.177058e-01\n",
       "Guatemala_up                2.210171e-01\n",
       "Ecuador_low                 1.537787e-01\n",
       "Ecuador_up                  1.571171e-01\n",
       "Zimbabwe_low                1.409470e-01\n",
       "Zimbabwe_up                 1.442414e-01\n",
       "Cambodia_low                1.535928e-01\n",
       "Cambodia_up                 1.568924e-01\n",
       "Cuba_low                    1.443974e-02\n",
       "Cuba_up                     1.754833e-02\n",
       "Belgium_low                 5.104025e-02\n",
       "Belgium_up                  5.411087e-02\n",
       "Greece_low                  1.218043e-02\n",
       "Greece_up                   1.526259e-02\n",
       "Czech Republic_low          1.079695e-02\n",
       "Czech Republic_up           1.383877e-02\n",
       "Portugal_low                1.456273e-02\n",
       "Portugal_up                 1.760976e-02\n",
       "Hungary_low                -2.126809e-02\n",
       "Hungary_up                 -1.825250e-02\n",
       "Length: 229, dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_perc.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Metadata Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import data\n",
    "metadata = pd.read_csv('data-sources/country_metadata.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove columns otherwise not required\n",
    "cols = ['Country Code', 'SpecialNotes']\n",
    "metadata = metadata.drop(columns=cols, axis=1)\n",
    "\n",
    "# rename \"TableName\" column\n",
    "metadata.columns.values[-1] = 'Country'\n",
    "\n",
    "# remove all rows with missing valuesp\n",
    "metadata = metadata.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sort by income group\n",
    "metadata['IncomeGroup'] = pd.Categorical(metadata['IncomeGroup'], \n",
    "                                         [\"High income\", \"Upper middle income\", \"Lower middle income\", \n",
    "                                          \"Low income\"])\n",
    "metadata = metadata.sort_values(by=['IncomeGroup'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Region</th>\n",
       "      <th>IncomeGroup</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Latin America &amp; Caribbean</td>\n",
       "      <td>High income</td>\n",
       "      <td>Aruba</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>Europe &amp; Central Asia</td>\n",
       "      <td>High income</td>\n",
       "      <td>Netherlands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Europe &amp; Central Asia</td>\n",
       "      <td>High income</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>Europe &amp; Central Asia</td>\n",
       "      <td>High income</td>\n",
       "      <td>Norway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>Europe &amp; Central Asia</td>\n",
       "      <td>High income</td>\n",
       "      <td>Faroe Islands</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Region  IncomeGroup         Country\n",
       "0    Latin America & Caribbean  High income           Aruba\n",
       "173      Europe & Central Asia  High income     Netherlands\n",
       "79       Europe & Central Asia  High income  United Kingdom\n",
       "174      Europe & Central Asia  High income          Norway\n",
       "76       Europe & Central Asia  High income   Faroe Islands"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# store data\n",
    "metadata.to_csv('metadata.csv', index=False)\n",
    "metadata.head()"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}