1-data-cleaning-population.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating an additional population table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Importing population data from the FAO population table in case this data is required for the analysis, cleaning the data and exporting it as a SQL table. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import packages\n",
    "import pandas as pd\n",
    "import numpy as np \n",
    "\n",
    "\n",
    "import _functions_sql as fs\n",
    "import _functions_data_files as fdf\n",
    "\n",
    "# Specify source directory and file (required for the fdf function to work as expected)\n",
    "source_dir = 'fao_population'\n",
    "source_file = 'Population_E_All_Data_(Normalized).csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import raw data into a pandas dataframe\n",
    "population = pd.read_csv(\n",
    "      fdf.get_path(source_file, source_dir)\n",
    "    , encoding='latin-1'\n",
    "    , converters={'Note': str} # handle DtypeWarning without 'low_memory=False' \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Year\n",
       "2025    1343\n",
       "2032    1343\n",
       "2030    1343\n",
       "2029    1343\n",
       "2028    1343\n",
       "        ... \n",
       "2070     807\n",
       "2071     807\n",
       "2072     807\n",
       "2073     807\n",
       "2100     807\n",
       "Name: count, Length: 151, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the date range of the table\n",
    "population['Year'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    169142\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for full duplicates\n",
    "population.duplicated().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['1000 No'], dtype=object)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Getting a list of unique values in the unit column\n",
    "population['Unit'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Total Population - Both sexes', 'Total Population - Male',\n",
       "       'Total Population - Female', 'Rural population',\n",
       "       'Urban population'], dtype=object)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Getting a list of unique values in the element column\n",
    "population['Element'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop unneeded columns\n",
    "population.drop(columns = ['Year Code', 'Area Code (M49)', 'Area Code', 'Item Code', 'Item', 'Element Code', 'Unit', 'Flag', 'Note'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group the population data by area and year\n",
    "population = population.pivot_table(index=['Area','Year'],\n",
    "                    columns='Element', values='Value', aggfunc='sum').reset_index()"
   ]
  },
  {
   "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>Element</th>\n",
       "      <th>Area</th>\n",
       "      <th>Year</th>\n",
       "      <th>Rural population</th>\n",
       "      <th>Total Population - Both sexes</th>\n",
       "      <th>Total Population - Female</th>\n",
       "      <th>Total Population - Male</th>\n",
       "      <th>Urban population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>1950</td>\n",
       "      <td>7286.991</td>\n",
       "      <td>7480.461</td>\n",
       "      <td>3518.202</td>\n",
       "      <td>3962.260</td>\n",
       "      <td>465.127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>1951</td>\n",
       "      <td>7352.856</td>\n",
       "      <td>7571.537</td>\n",
       "      <td>3569.936</td>\n",
       "      <td>4001.601</td>\n",
       "      <td>486.654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>1952</td>\n",
       "      <td>7425.363</td>\n",
       "      <td>7667.533</td>\n",
       "      <td>3624.224</td>\n",
       "      <td>4043.308</td>\n",
       "      <td>509.617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>1953</td>\n",
       "      <td>7504.561</td>\n",
       "      <td>7764.546</td>\n",
       "      <td>3679.045</td>\n",
       "      <td>4085.501</td>\n",
       "      <td>534.035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>1954</td>\n",
       "      <td>7590.370</td>\n",
       "      <td>7864.285</td>\n",
       "      <td>3735.084</td>\n",
       "      <td>4129.202</td>\n",
       "      <td>560.077</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Element         Area  Year  Rural population  Total Population - Both sexes  \\\n",
       "0        Afghanistan  1950          7286.991                       7480.461   \n",
       "1        Afghanistan  1951          7352.856                       7571.537   \n",
       "2        Afghanistan  1952          7425.363                       7667.533   \n",
       "3        Afghanistan  1953          7504.561                       7764.546   \n",
       "4        Afghanistan  1954          7590.370                       7864.285   \n",
       "\n",
       "Element  Total Population - Female  Total Population - Male  Urban population  \n",
       "0                         3518.202                 3962.260           465.127  \n",
       "1                         3569.936                 4001.601           486.654  \n",
       "2                         3624.224                 4043.308           509.617  \n",
       "3                         3679.045                 4085.501           534.035  \n",
       "4                         3735.084                 4129.202           560.077  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop unneeded columns - we do not require the split into female & male\n",
    "population.drop(columns = ['Total Population - Female', 'Total Population - Male'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename columns\n",
    "population.rename(columns = {'Area':'area', 'Year':'year', 'Rural population':'rural_population', 'Total Population - Both sexes':'total_population', 'Urban population': 'urban_population'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Removing the 'Element' name from the index column\n",
    "population = population.rename_axis(None, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    39301\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking for duplicates \n",
    "population[['area', 'year']].duplicated().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Multiplying the population columns since the original values are in 1000s\n",
    "population.loc[:, ['rural_population', 'urban_population', 'total_population']] *= 1000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "population = population.reindex(columns=['area', 'year', 'rural_population', 'urban_population', 'total_population'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "population[['rural_population', 'urban_population', 'total_population']] = population[['rural_population', 'urban_population', 'total_population']].round().astype('Int64')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+ table written: fao_population\n"
     ]
    }
   ],
   "source": [
    "# Export the dataframe\n",
    "fs.write_dataframe(population, 'fao_population')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "capstone",
   "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.9.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}