ETL_Project.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Import Dependencies \n",
    "import os\n",
    "import csv\n",
    "import requests\n",
    "import pandas as pd\n",
    "import sqlalchemy\n",
    "import splinter\n",
    "from splinter import Browser\n",
    "from sqlalchemy import create_engine\n",
    "from bs4 import BeautifulSoup as BS\n",
    "import time\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extracting Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Data Source #1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "world_happiness_file = os.path.join(\"Resources\", \"WHR20_DataForFigure2.1.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Data Source #2 ( Web-Scraped Using Splinter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pass the chrome executable path  and create an instance of the browser\n",
    "executable_path = {'executable_path': 'chromedriver'}\n",
    "browser = Browser('chrome', **executable_path, headless=False)\n",
    "\n",
    "\n",
    "population_url = 'https://www.worldometers.info/'\n",
    "\n",
    "# visit the url\n",
    "browser.visit(population_url)\n",
    "time.sleep(2)\n",
    "\n",
    "\n",
    "# use splinter methods to click on the links\n",
    "browser.click_link_by_text('Population')\n",
    "time.sleep(2)\n",
    "\n",
    "browser.click_link_by_href('/world-population/population-by-country/')\n",
    "time.sleep(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "url1 = browser.url\n",
    "browser.quit()\n",
    "#url1 = 'https://www.worldometers.info/world-population/population-by-country/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Data source #3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "url2 = 'https://www.worldometers.info/demographics/life-expectancy/#countries-ranked-by-life-expectancy'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Function to parse the website using  BeautifulSoup ; fetches tables from Parse trees of Data Source #2 & Data Source #3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def html_df(url):\n",
    "    response = requests.get(url)\n",
    "    soup = BS(response.text,\"html.parser\")\n",
    "    res_table = str(soup.find(\"table\"))\n",
    "    tables = pd.read_html(res_table)[0]\n",
    "    return tables.drop(columns=[\"#\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "world_happiness = pd.read_csv(world_happiness_file)\n",
    "population = html_df(url1)\n",
    "life_expectancy = html_df(url2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transforming Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Data Source #1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Country name', 'Regional indicator', 'Ladder score',\n",
       "       'Standard error of ladder score', 'upperwhisker', 'lowerwhisker',\n",
       "       'Logged GDP per capita', 'Social support', 'Healthy life expectancy',\n",
       "       'Freedom to make life choices', 'Generosity',\n",
       "       'Perceptions of corruption', 'Ladder score in Dystopia',\n",
       "       'Explained by: Log GDP per capita', 'Explained by: Social support',\n",
       "       'Explained by: Healthy life expectancy',\n",
       "       'Explained by: Freedom to make life choices',\n",
       "       'Explained by: Generosity', 'Explained by: Perceptions of corruption',\n",
       "       'Dystopia + residual'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#list the columns\n",
    "world_happiness.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename columns\n",
    "world_happiness = world_happiness.rename(columns={\"Country name\":\"country\",\"Ladder score\": \"happiness_score\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a filtered dataframe from specific columns\n",
    "world_happiness_df = world_happiness[[\"country\", \"happiness_score\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country                                        object\n",
       "Regional indicator                             object\n",
       "happiness_score                               float64\n",
       "Standard error of ladder score                float64\n",
       "upperwhisker                                  float64\n",
       "lowerwhisker                                  float64\n",
       "Logged GDP per capita                         float64\n",
       "Social support                                float64\n",
       "Healthy life expectancy                       float64\n",
       "Freedom to make life choices                  float64\n",
       "Generosity                                    float64\n",
       "Perceptions of corruption                     float64\n",
       "Ladder score in Dystopia                      float64\n",
       "Explained by: Log GDP per capita              float64\n",
       "Explained by: Social support                  float64\n",
       "Explained by: Healthy life expectancy         float64\n",
       "Explained by: Freedom to make life choices    float64\n",
       "Explained by: Generosity                      float64\n",
       "Explained by: Perceptions of corruption       float64\n",
       "Dystopia + residual                           float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check datatypes to maintain consistency across all dataframes\n",
    "world_happiness.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>happiness_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Finland</td>\n",
       "      <td>7.8087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>7.6456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>7.5599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Iceland</td>\n",
       "      <td>7.5045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Norway</td>\n",
       "      <td>7.4880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>148</td>\n",
       "      <td>Central African Republic</td>\n",
       "      <td>3.4759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>149</td>\n",
       "      <td>Rwanda</td>\n",
       "      <td>3.3123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>150</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>3.2992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>151</td>\n",
       "      <td>South Sudan</td>\n",
       "      <td>2.8166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>152</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2.5669</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>153 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      country  happiness_score\n",
       "0                     Finland           7.8087\n",
       "1                     Denmark           7.6456\n",
       "2                 Switzerland           7.5599\n",
       "3                     Iceland           7.5045\n",
       "4                      Norway           7.4880\n",
       "..                        ...              ...\n",
       "148  Central African Republic           3.4759\n",
       "149                    Rwanda           3.3123\n",
       "150                  Zimbabwe           3.2992\n",
       "151               South Sudan           2.8166\n",
       "152               Afghanistan           2.5669\n",
       "\n",
       "[153 rows x 2 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "world_happiness_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Data Source #2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Country (or dependency)', 'Population (2020)', 'Yearly Change',\n",
       "       'Net Change', 'Density (P/Km²)', 'Land Area (Km²)', 'Migrants (net)',\n",
       "       'Fert. Rate', 'Med. Age', 'Urban Pop %', 'World Share'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# list the columns\n",
    "population.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename columns 1 and 2\n",
    "population = population.rename(columns={\"Country (or dependency)\":\"country\",\"Population (2020)\":\"population_2020\",\"Migrants (net)\":\"migrants_net\",\"World Share\":\"world_share_percent\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Create a filtered dataframe from specific columns\n",
    "population_df = population[[\"country\",\"population_2020\",\"migrants_net\",\"world_share_percent\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>population_2020</th>\n",
       "      <th>migrants_net</th>\n",
       "      <th>world_share_percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>China</td>\n",
       "      <td>1439323776</td>\n",
       "      <td>-348399.0</td>\n",
       "      <td>18.47 %</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>India</td>\n",
       "      <td>1380004385</td>\n",
       "      <td>-532687.0</td>\n",
       "      <td>17.70 %</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>United States</td>\n",
       "      <td>331002651</td>\n",
       "      <td>954806.0</td>\n",
       "      <td>4.25 %</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Indonesia</td>\n",
       "      <td>273523615</td>\n",
       "      <td>-98955.0</td>\n",
       "      <td>3.51 %</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>220892340</td>\n",
       "      <td>-233379.0</td>\n",
       "      <td>2.83 %</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>230</td>\n",
       "      <td>Montserrat</td>\n",
       "      <td>4992</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00 %</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>231</td>\n",
       "      <td>Falkland Islands</td>\n",
       "      <td>3480</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00 %</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>232</td>\n",
       "      <td>Niue</td>\n",
       "      <td>1626</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00 %</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>233</td>\n",
       "      <td>Tokelau</td>\n",
       "      <td>1357</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00 %</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>234</td>\n",
       "      <td>Holy See</td>\n",
       "      <td>801</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00 %</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>235 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              country  population_2020  migrants_net world_share_percent\n",
       "0               China       1439323776     -348399.0             18.47 %\n",
       "1               India       1380004385     -532687.0             17.70 %\n",
       "2       United States        331002651      954806.0              4.25 %\n",
       "3           Indonesia        273523615      -98955.0              3.51 %\n",
       "4            Pakistan        220892340     -233379.0              2.83 %\n",
       "..                ...              ...           ...                 ...\n",
       "230        Montserrat             4992           NaN              0.00 %\n",
       "231  Falkland Islands             3480           NaN              0.00 %\n",
       "232              Niue             1626           NaN              0.00 %\n",
       "233           Tokelau             1357           NaN              0.00 %\n",
       "234          Holy See              801           NaN              0.00 %\n",
       "\n",
       "[235 rows x 4 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country                 object\n",
       "population_2020          int64\n",
       "migrants_net           float64\n",
       "world_share_percent     object\n",
       "dtype: object"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check datatypes to maintain consistency across all dataframes\n",
    "population_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert the world_share_percent from object datatype to string\n",
    "population_df = population_df.astype({\"world_share_percent\": str})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# strip the percent symbol from the world share column and store their face value \n",
    "population_df['world_share_percent'] = (population_df['world_share_percent'].str[:-1].astype(float))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country                 object\n",
       "population_2020          int64\n",
       "migrants_net           float64\n",
       "world_share_percent    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#confirm datatypes \n",
    "population_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>population_2020</th>\n",
       "      <th>migrants_net</th>\n",
       "      <th>world_share_percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>China</td>\n",
       "      <td>1439323776</td>\n",
       "      <td>-348399.0</td>\n",
       "      <td>18.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>India</td>\n",
       "      <td>1380004385</td>\n",
       "      <td>-532687.0</td>\n",
       "      <td>17.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>United States</td>\n",
       "      <td>331002651</td>\n",
       "      <td>954806.0</td>\n",
       "      <td>4.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Indonesia</td>\n",
       "      <td>273523615</td>\n",
       "      <td>-98955.0</td>\n",
       "      <td>3.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>220892340</td>\n",
       "      <td>-233379.0</td>\n",
       "      <td>2.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>230</td>\n",
       "      <td>Montserrat</td>\n",
       "      <td>4992</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>231</td>\n",
       "      <td>Falkland Islands</td>\n",
       "      <td>3480</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>232</td>\n",
       "      <td>Niue</td>\n",
       "      <td>1626</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>233</td>\n",
       "      <td>Tokelau</td>\n",
       "      <td>1357</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>234</td>\n",
       "      <td>Holy See</td>\n",
       "      <td>801</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>235 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              country  population_2020  migrants_net  world_share_percent\n",
       "0               China       1439323776     -348399.0                18.47\n",
       "1               India       1380004385     -532687.0                17.70\n",
       "2       United States        331002651      954806.0                 4.25\n",
       "3           Indonesia        273523615      -98955.0                 3.51\n",
       "4            Pakistan        220892340     -233379.0                 2.83\n",
       "..                ...              ...           ...                  ...\n",
       "230        Montserrat             4992           NaN                 0.00\n",
       "231  Falkland Islands             3480           NaN                 0.00\n",
       "232              Niue             1626           NaN                 0.00\n",
       "233           Tokelau             1357           NaN                 0.00\n",
       "234          Holy See              801           NaN                 0.00\n",
       "\n",
       "[235 rows x 4 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Data Source #3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Country', 'Life Expectancy (both sexes)', 'Females Life Expectancy',\n",
       "       'Males Life Expectancy'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#list the columns\n",
    "life_expectancy.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename columns 1 and 2\n",
    "life_expectancy = life_expectancy.rename(columns={\"Country\":\"country\",\"Life Expectancy (both sexes)\": \"life_expectancy_both_sexes\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a filtered dataframe from specific columns\n",
    "life_expectancy_df = life_expectancy[[\"country\",\"life_expectancy_both_sexes\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>life_expectancy_both_sexes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>85.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Japan</td>\n",
       "      <td>85.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Macao</td>\n",
       "      <td>84.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>84.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>84.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>195</td>\n",
       "      <td>Sierra Leone</td>\n",
       "      <td>55.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>196</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>55.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>197</td>\n",
       "      <td>Lesotho</td>\n",
       "      <td>55.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>198</td>\n",
       "      <td>Chad</td>\n",
       "      <td>55.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>199</td>\n",
       "      <td>Central African Republic</td>\n",
       "      <td>54.36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>200 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      country  life_expectancy_both_sexes\n",
       "0                   Hong Kong                       85.29\n",
       "1                       Japan                       85.03\n",
       "2                       Macao                       84.68\n",
       "3                 Switzerland                       84.25\n",
       "4                   Singapore                       84.07\n",
       "..                        ...                         ...\n",
       "195              Sierra Leone                       55.92\n",
       "196                   Nigeria                       55.75\n",
       "197                   Lesotho                       55.65\n",
       "198                      Chad                       55.17\n",
       "199  Central African Republic                       54.36\n",
       "\n",
       "[200 rows x 2 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "life_expectancy_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country                        object\n",
       "life_expectancy_both_sexes    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check datatypes to maintain consistency across all dataframes\n",
    "life_expectancy_df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Loading the Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "######      Connect to local database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_connection_string = \"postgres:jaigurudev@localhost:5432/Project-1-ETL\"\n",
    "engine = create_engine(f'postgresql://{db_connection_string}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "######     Check for tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['happiness', 'life_expectancy', 'population']"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get the mapped table_names\n",
    "engine.table_names()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Use pandas to load converted DataFrame into database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "world_happiness_df.to_sql(name = 'happiness',con = engine , if_exists = 'append',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "population_df.to_sql(name = \"population\",con = engine , if_exists = 'append',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_expectancy_df.to_sql(name = \"life_expectancy\",con = engine , if_exists = 'append',index =False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Confirm data has been added by querying the  tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>happiness_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Finland</td>\n",
       "      <td>7.8087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>7.6456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>7.5599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Iceland</td>\n",
       "      <td>7.5045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Norway</td>\n",
       "      <td>7.4880</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country  happiness_score\n",
       "0      Finland           7.8087\n",
       "1      Denmark           7.6456\n",
       "2  Switzerland           7.5599\n",
       "3      Iceland           7.5045\n",
       "4       Norway           7.4880"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from happiness', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>population_2020</th>\n",
       "      <th>migrants_net</th>\n",
       "      <th>world_share_percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>China</td>\n",
       "      <td>1439323776</td>\n",
       "      <td>-348399.0</td>\n",
       "      <td>18.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>India</td>\n",
       "      <td>1380004385</td>\n",
       "      <td>-532687.0</td>\n",
       "      <td>17.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>United States</td>\n",
       "      <td>331002651</td>\n",
       "      <td>954806.0</td>\n",
       "      <td>4.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Indonesia</td>\n",
       "      <td>273523615</td>\n",
       "      <td>-98955.0</td>\n",
       "      <td>3.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>220892340</td>\n",
       "      <td>-233379.0</td>\n",
       "      <td>2.83</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         country  population_2020  migrants_net  world_share_percent\n",
       "0          China       1439323776     -348399.0                18.47\n",
       "1          India       1380004385     -532687.0                17.70\n",
       "2  United States        331002651      954806.0                 4.25\n",
       "3      Indonesia        273523615      -98955.0                 3.51\n",
       "4       Pakistan        220892340     -233379.0                 2.83"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from population', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>life_expectancy_both_sexes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>85.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Japan</td>\n",
       "      <td>85.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Macao</td>\n",
       "      <td>84.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>84.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>84.07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country  life_expectancy_both_sexes\n",
       "0    Hong Kong                       85.29\n",
       "1        Japan                       85.03\n",
       "2        Macao                       84.68\n",
       "3  Switzerland                       84.25\n",
       "4    Singapore                       84.07"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from life_expectancy', con=engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Confirm if tables can be joined  by querying the  tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>population_2020</th>\n",
       "      <th>net_migrants</th>\n",
       "      <th>world_share_percent</th>\n",
       "      <th>life_expectency_both_sexes</th>\n",
       "      <th>happiness_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Finland</td>\n",
       "      <td>5540720</td>\n",
       "      <td>14000.0</td>\n",
       "      <td>0.07</td>\n",
       "      <td>82.48</td>\n",
       "      <td>7.8087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>5792202</td>\n",
       "      <td>15200.0</td>\n",
       "      <td>0.07</td>\n",
       "      <td>81.40</td>\n",
       "      <td>7.6456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>8654622</td>\n",
       "      <td>52000.0</td>\n",
       "      <td>0.11</td>\n",
       "      <td>84.25</td>\n",
       "      <td>7.5599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Iceland</td>\n",
       "      <td>341243</td>\n",
       "      <td>380.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>83.52</td>\n",
       "      <td>7.5045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Norway</td>\n",
       "      <td>5421241</td>\n",
       "      <td>28000.0</td>\n",
       "      <td>0.07</td>\n",
       "      <td>82.94</td>\n",
       "      <td>7.4880</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country  population_2020  net_migrants  world_share_percent  \\\n",
       "0      Finland          5540720       14000.0                 0.07   \n",
       "1      Denmark          5792202       15200.0                 0.07   \n",
       "2  Switzerland          8654622       52000.0                 0.11   \n",
       "3      Iceland           341243         380.0                 0.00   \n",
       "4       Norway          5421241       28000.0                 0.07   \n",
       "\n",
       "   life_expectency_both_sexes  happiness_score  \n",
       "0                       82.48           7.8087  \n",
       "1                       81.40           7.6456  \n",
       "2                       84.25           7.5599  \n",
       "3                       83.52           7.5045  \n",
       "4                       82.94           7.4880  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# store  the sql query as a string into a variable \n",
    "\n",
    "q = \"\"\"SELECT \n",
    "     a.country AS Country,\n",
    "     b.population_2020 AS Population_2020,\n",
    "\t b.migrants_net AS Net_Migrants,\n",
    "     b.world_share_percent AS World_Share_percent,\n",
    "\t c.life_expectancy_both_sexes AS Life_Expectency_both_sexes,\n",
    "     a.happiness_score AS Happiness_Score \\\n",
    "    \n",
    "FROM \n",
    "    happiness AS a \n",
    "    INNER JOIN population AS b \n",
    "        ON (a.country = b.country) \n",
    "    INNER JOIN life_expectancy AS c \n",
    "        ON (a.country = c.country); \n",
    "\"\"\"\n",
    "\n",
    "# pass the variable \n",
    "pd.read_sql_query(q, con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PythonData",
   "language": "python",
   "name": "pythondata"
  },
  "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
}