data_etl.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy import inspect\n",
    "import config"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract CSVs into DataFrame"
   ]
  },
  {
   "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>Job Title</th>\n",
       "      <th>Job Location</th>\n",
       "      <th>Company</th>\n",
       "      <th>Url</th>\n",
       "      <th>Estimate Base Salary</th>\n",
       "      <th>Low Estimate</th>\n",
       "      <th>High Estimate</th>\n",
       "      <th>Company Size</th>\n",
       "      <th>Company Type</th>\n",
       "      <th>Company Sector</th>\n",
       "      <th>...</th>\n",
       "      <th>cassandra_yn</th>\n",
       "      <th>hive_yn</th>\n",
       "      <th>bigml_yn</th>\n",
       "      <th>tableau_yn</th>\n",
       "      <th>powerbi_yn</th>\n",
       "      <th>nlp_yn</th>\n",
       "      <th>pytorch_yn</th>\n",
       "      <th>tensorflow_yn</th>\n",
       "      <th>mathematic_yn</th>\n",
       "      <th>statistic_yn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Analyst</td>\n",
       "      <td>Melbourne</td>\n",
       "      <td>ANZ Banking Group</td>\n",
       "      <td>https://www.glassdoor.com.au/partner/jobListin...</td>\n",
       "      <td>95917</td>\n",
       "      <td>80000</td>\n",
       "      <td>115000</td>\n",
       "      <td>10000+ Employees</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Finance</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>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Clinical Research Associate</td>\n",
       "      <td>Mulgrave</td>\n",
       "      <td>Bristol Myers Squibb</td>\n",
       "      <td>https://www.glassdoor.com.au/partner/jobListin...</td>\n",
       "      <td>96555</td>\n",
       "      <td>79000</td>\n",
       "      <td>118000</td>\n",
       "      <td>10000+ Employees</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Pharmaceutical &amp; Biotechnology</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>2</th>\n",
       "      <td>Clinical Research Associate</td>\n",
       "      <td>Mulgrave</td>\n",
       "      <td>Bristol Myers Squibb</td>\n",
       "      <td>https://www.glassdoor.com.au/partner/jobListin...</td>\n",
       "      <td>96555</td>\n",
       "      <td>79000</td>\n",
       "      <td>118000</td>\n",
       "      <td>10000+ Employees</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Pharmaceutical &amp; Biotechnology</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>3</th>\n",
       "      <td>Clinical Research Associate</td>\n",
       "      <td>Mulgrave</td>\n",
       "      <td>Bristol Myers Squibb</td>\n",
       "      <td>https://www.glassdoor.com.au/partner/jobListin...</td>\n",
       "      <td>96555</td>\n",
       "      <td>79000</td>\n",
       "      <td>118000</td>\n",
       "      <td>10000+ Employees</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Pharmaceutical &amp; Biotechnology</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>4</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Melbourne</td>\n",
       "      <td>ANZ Banking Group</td>\n",
       "      <td>https://www.glassdoor.com.au/partner/jobListin...</td>\n",
       "      <td>115631</td>\n",
       "      <td>94000</td>\n",
       "      <td>143000</td>\n",
       "      <td>10000+ Employees</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Finance</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 53 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Job Title Job Location               Company  \\\n",
       "0                      Analyst    Melbourne     ANZ Banking Group   \n",
       "1  Clinical Research Associate     Mulgrave  Bristol Myers Squibb   \n",
       "2  Clinical Research Associate     Mulgrave  Bristol Myers Squibb   \n",
       "3  Clinical Research Associate     Mulgrave  Bristol Myers Squibb   \n",
       "4               Data Scientist    Melbourne     ANZ Banking Group   \n",
       "\n",
       "                                                 Url  Estimate Base Salary  \\\n",
       "0  https://www.glassdoor.com.au/partner/jobListin...                 95917   \n",
       "1  https://www.glassdoor.com.au/partner/jobListin...                 96555   \n",
       "2  https://www.glassdoor.com.au/partner/jobListin...                 96555   \n",
       "3  https://www.glassdoor.com.au/partner/jobListin...                 96555   \n",
       "4  https://www.glassdoor.com.au/partner/jobListin...                115631   \n",
       "\n",
       "   Low Estimate  High Estimate      Company Size      Company Type  \\\n",
       "0         80000         115000  10000+ Employees  Company - Public   \n",
       "1         79000         118000  10000+ Employees  Company - Public   \n",
       "2         79000         118000  10000+ Employees  Company - Public   \n",
       "3         79000         118000  10000+ Employees  Company - Public   \n",
       "4         94000         143000  10000+ Employees  Company - Public   \n",
       "\n",
       "                   Company Sector  ...  cassandra_yn hive_yn bigml_yn  \\\n",
       "0                         Finance  ...             0       0        0   \n",
       "1  Pharmaceutical & Biotechnology  ...             0       0        0   \n",
       "2  Pharmaceutical & Biotechnology  ...             0       0        0   \n",
       "3  Pharmaceutical & Biotechnology  ...             0       0        0   \n",
       "4                         Finance  ...             0       0        0   \n",
       "\n",
       "  tableau_yn  powerbi_yn  nlp_yn  pytorch_yn  tensorflow_yn  mathematic_yn  \\\n",
       "0          0           0       0           0              0              1   \n",
       "1          0           0       0           0              0              0   \n",
       "2          0           0       0           0              0              0   \n",
       "3          0           0       0           0              0              0   \n",
       "4          0           0       0           0              0              0   \n",
       "\n",
       "   statistic_yn  \n",
       "0             0  \n",
       "1             0  \n",
       "2             0  \n",
       "3             0  \n",
       "4             0  \n",
       "\n",
       "[5 rows x 53 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_science_file = \"Resources/datascience_jobs_aus.csv\"\n",
    "data_science_df = pd.read_csv(data_science_file)\n",
    "data_science_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>jobId</th>\n",
       "      <th>jobTitle</th>\n",
       "      <th>jobClassification</th>\n",
       "      <th>jobSubClassification</th>\n",
       "      <th>advertiserName</th>\n",
       "      <th>advertiserId</th>\n",
       "      <th>companyId</th>\n",
       "      <th>companyName</th>\n",
       "      <th>companyRating</th>\n",
       "      <th>listingDate</th>\n",
       "      <th>...</th>\n",
       "      <th>Haskell</th>\n",
       "      <th>Lisp</th>\n",
       "      <th>Golang</th>\n",
       "      <th>Spark</th>\n",
       "      <th>Javascript</th>\n",
       "      <th>F.</th>\n",
       "      <th>Fortran</th>\n",
       "      <th>first_seen</th>\n",
       "      <th>last_seen</th>\n",
       "      <th>recruiter</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>38098375</td>\n",
       "      <td>Senior Method Development Immunoassay Scientis...</td>\n",
       "      <td>Science &amp; Technology</td>\n",
       "      <td>Biological &amp; Biomedical Sciences</td>\n",
       "      <td>Hobson Prior Australia</td>\n",
       "      <td>30969882</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16/01/2019 12:17</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>6/03/2019</td>\n",
       "      <td>11/03/2019</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>38259053</td>\n",
       "      <td>Pricing Data Scientist - Lower North Shore</td>\n",
       "      <td>Information &amp; Communication Technology</td>\n",
       "      <td>Other</td>\n",
       "      <td>Randstad - Technologies</td>\n",
       "      <td>26537413</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4/02/2019 13:02</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>6/03/2019</td>\n",
       "      <td>6/03/2019</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>38267899</td>\n",
       "      <td>Insights Analyst – Online Video Streaming</td>\n",
       "      <td>Information &amp; Communication Technology</td>\n",
       "      <td>Database Development &amp; Administration</td>\n",
       "      <td>Aurec</td>\n",
       "      <td>39407892</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5/02/2019 11:33</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>6/03/2019</td>\n",
       "      <td>7/03/2019</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38267995</td>\n",
       "      <td>Credit Risk Analyst</td>\n",
       "      <td>Banking &amp; Financial Services</td>\n",
       "      <td>Compliance &amp; Risk</td>\n",
       "      <td>Huxley Associates</td>\n",
       "      <td>26057722</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5/02/2019 11:39</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>6/03/2019</td>\n",
       "      <td>7/03/2019</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>38269206</td>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Information &amp; Communication Technology</td>\n",
       "      <td>Database Development &amp; Administration</td>\n",
       "      <td>FinXL IT Professional Services</td>\n",
       "      <td>33086778</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5/02/2019 12:54</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>6/03/2019</td>\n",
       "      <td>7/03/2019</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 52 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      jobId                                           jobTitle  \\\n",
       "0  38098375  Senior Method Development Immunoassay Scientis...   \n",
       "1  38259053        Pricing Data Scientist - Lower North Shore    \n",
       "2  38267899          Insights Analyst – Online Video Streaming   \n",
       "3  38267995                                Credit Risk Analyst   \n",
       "4  38269206                                     Data Scientist   \n",
       "\n",
       "                        jobClassification  \\\n",
       "0                    Science & Technology   \n",
       "1  Information & Communication Technology   \n",
       "2  Information & Communication Technology   \n",
       "3            Banking & Financial Services   \n",
       "4  Information & Communication Technology   \n",
       "\n",
       "                    jobSubClassification                  advertiserName  \\\n",
       "0       Biological & Biomedical Sciences          Hobson Prior Australia   \n",
       "1                                  Other         Randstad - Technologies   \n",
       "2  Database Development & Administration                           Aurec   \n",
       "3                      Compliance & Risk               Huxley Associates   \n",
       "4  Database Development & Administration  FinXL IT Professional Services   \n",
       "\n",
       "   advertiserId  companyId companyName  companyRating       listingDate  ...  \\\n",
       "0      30969882        NaN         NaN            NaN  16/01/2019 12:17  ...   \n",
       "1      26537413        NaN         NaN            NaN   4/02/2019 13:02  ...   \n",
       "2      39407892        NaN         NaN            NaN   5/02/2019 11:33  ...   \n",
       "3      26057722        NaN         NaN            NaN   5/02/2019 11:39  ...   \n",
       "4      33086778        NaN         NaN            NaN   5/02/2019 12:54  ...   \n",
       "\n",
       "  Haskell Lisp Golang Spark Javascript F. Fortran first_seen   last_seen  \\\n",
       "0       0    0      0     0          0  0       0  6/03/2019  11/03/2019   \n",
       "1       0    0      0     0          0  0       0  6/03/2019   6/03/2019   \n",
       "2       0    0      0     0          0  0       0  6/03/2019   7/03/2019   \n",
       "3       0    0      0     0          0  0       0  6/03/2019   7/03/2019   \n",
       "4       0    0      0     0          0  0       0  6/03/2019   7/03/2019   \n",
       "\n",
       "  recruiter  \n",
       "0         1  \n",
       "1         1  \n",
       "2         1  \n",
       "3         1  \n",
       "4         0  \n",
       "\n",
       "[5 rows x 52 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job_listing_file = \"Resources/datascience_listing.csv\"\n",
    "job_listing_df = pd.read_csv(job_listing_file)\n",
    "job_listing_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transform data_science DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\Users\\User\\anaconda3\\envs\\PythonData\\lib\\site-packages\\pandas\\util\\_decorators.py:311: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  return func(*args, **kwargs)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>job_location</th>\n",
       "      <th>job_title</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Melbourne</td>\n",
       "      <td>Analyst</td>\n",
       "      <td>95917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mulgrave</td>\n",
       "      <td>Clinical Research Associate</td>\n",
       "      <td>96555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Australia</td>\n",
       "      <td>Software Engineer</td>\n",
       "      <td>212000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Dandenong</td>\n",
       "      <td>Quality Manager</td>\n",
       "      <td>90000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Reservoir</td>\n",
       "      <td>Food Technologist</td>\n",
       "      <td>75000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   job_location                    job_title  salary\n",
       "0     Melbourne                      Analyst   95917\n",
       "1      Mulgrave  Clinical Research Associate   96555\n",
       "8     Australia            Software Engineer  212000\n",
       "40    Dandenong              Quality Manager   90000\n",
       "44    Reservoir            Food Technologist   75000"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a filtered dataframe from specific columns\n",
    "data_science_cols = [\"Job Location\",\"Job Title\",\"Estimate Base Salary\"]\n",
    "data_science_transformed = data_science_df[data_science_cols].copy()\n",
    "\n",
    "# Rename the column headers\n",
    "data_science_transformed= data_science_transformed.rename(columns={\n",
    "                                                          \"Job Location\": \"job_location\",\n",
    "                                                          \"Job Title\": \"job_title\",\n",
    "                                                          \"Estimate Base Salary\": \"salary\"\n",
    "                                                          })\n",
    "\n",
    "# Clean the data by dropping duplicates and NA values and setting the index\n",
    "new_data_science_transformed = data_science_transformed.dropna(how=\"any\")\n",
    "new_data_science_transformed.drop_duplicates(\"job_location\", inplace=True)\n",
    "new_data_science_transformed.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transform job_listing DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>job_location</th>\n",
       "      <th>job_class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sydney</td>\n",
       "      <td>Science &amp; Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ACT</td>\n",
       "      <td>Information &amp; Communication Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Melbourne</td>\n",
       "      <td>Information &amp; Communication Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Perth</td>\n",
       "      <td>Information &amp; Communication Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Brisbane</td>\n",
       "      <td>Mining, Resources &amp; Energy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   job_location                               job_class\n",
       "0        Sydney                    Science & Technology\n",
       "1           ACT  Information & Communication Technology\n",
       "8     Melbourne  Information & Communication Technology\n",
       "15        Perth  Information & Communication Technology\n",
       "24     Brisbane              Mining, Resources & Energy"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a filtered dataframe from specific columns\n",
    "data_listing_cols = [\"city\",\"jobClassification\"]\n",
    "data_listing_transformed = job_listing_df[data_listing_cols].copy()\n",
    "\n",
    "# Rename the column headers\n",
    "data_listing_transformed= data_listing_transformed.rename(columns={\"city\": \"job_location\",\n",
    "                                                          \"jobClassification\": \"job_class\"\n",
    "                                                              })\n",
    "\n",
    "# Clean the data by dropping duplicates and NA values and setting the index\n",
    "new_data_listing_transformed = data_listing_transformed.dropna(how=\"any\")\n",
    "new_data_listing_transformed.drop_duplicates(\"job_location\", inplace=True)\n",
    "new_data_listing_transformed.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect to local database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "protocol = 'postgresql'\n",
    "username = config.Username\n",
    "password = config.Password\n",
    "host = 'localhost'\n",
    "port = 5432\n",
    "database_name = 'datascience_db'\n",
    "rds_connection_string = f'{protocol}://{username}:{password}@{host}:{port}/{database_name}'\n",
    "engine = create_engine(rds_connection_string)\n",
    "insp = inspect(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inspect Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['datascience', 'joblisting']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "insp.get_table_names()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_data_science_transformed.to_sql(name='datascience', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_data_listing_transformed.to_sql(name='joblisting', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Confirm data has been added by querying the datascience table"
   ]
  },
  {
   "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>job_location</th>\n",
       "      <th>job_title</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Melbourne</td>\n",
       "      <td>Analyst</td>\n",
       "      <td>95917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mulgrave</td>\n",
       "      <td>Clinical Research Associate</td>\n",
       "      <td>96555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Australia</td>\n",
       "      <td>Software Engineer</td>\n",
       "      <td>212000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Dandenong</td>\n",
       "      <td>Quality Manager</td>\n",
       "      <td>90000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Reservoir</td>\n",
       "      <td>Food Technologist</td>\n",
       "      <td>75000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  job_location                    job_title  salary\n",
       "0    Melbourne                      Analyst   95917\n",
       "1     Mulgrave  Clinical Research Associate   96555\n",
       "2    Australia            Software Engineer  212000\n",
       "3    Dandenong              Quality Manager   90000\n",
       "4    Reservoir            Food Technologist   75000"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from datascience', con=engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Confirm data has been added by querying the job listing table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>job_location</th>\n",
       "      <th>job_class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sydney</td>\n",
       "      <td>Science &amp; Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ACT</td>\n",
       "      <td>Information &amp; Communication Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Melbourne</td>\n",
       "      <td>Information &amp; Communication Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Perth</td>\n",
       "      <td>Information &amp; Communication Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brisbane</td>\n",
       "      <td>Mining, Resources &amp; Energy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  job_location                               job_class\n",
       "0       Sydney                    Science & Technology\n",
       "1          ACT  Information & Communication Technology\n",
       "2    Melbourne  Information & Communication Technology\n",
       "3        Perth  Information & Communication Technology\n",
       "4     Brisbane              Mining, Resources & Energy"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from joblisting', con=engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Join Data from datascience and joblisting table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>job_location</th>\n",
       "      <th>job_title</th>\n",
       "      <th>salary</th>\n",
       "      <th>job_class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sydney</td>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>125000</td>\n",
       "      <td>Science &amp; Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Melbourne</td>\n",
       "      <td>Analyst</td>\n",
       "      <td>95917</td>\n",
       "      <td>Information &amp; Communication Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Perth</td>\n",
       "      <td>Entry Level Media Coordinator</td>\n",
       "      <td>65520</td>\n",
       "      <td>Information &amp; Communication Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Brisbane</td>\n",
       "      <td>Graduate Data Scientist</td>\n",
       "      <td>128589</td>\n",
       "      <td>Mining, Resources &amp; Energy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Darwin</td>\n",
       "      <td>Data Manager</td>\n",
       "      <td>82988</td>\n",
       "      <td>Science &amp; Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Software Engineer</td>\n",
       "      <td>85000</td>\n",
       "      <td>Science &amp; Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Hobart</td>\n",
       "      <td>Biostatistician</td>\n",
       "      <td>106500</td>\n",
       "      <td>Science &amp; Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Gold Coast</td>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>82171</td>\n",
       "      <td>Information &amp; Communication Technology</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  job_location                      job_title  salary  \\\n",
       "0       Sydney                 Data Scientist  125000   \n",
       "1    Melbourne                        Analyst   95917   \n",
       "2        Perth  Entry Level Media Coordinator   65520   \n",
       "3     Brisbane        Graduate Data Scientist  128589   \n",
       "4       Darwin                   Data Manager   82988   \n",
       "5     Adelaide              Software Engineer   85000   \n",
       "6       Hobart                Biostatistician  106500   \n",
       "7   Gold Coast                  Data Engineer   82171   \n",
       "\n",
       "                                job_class  \n",
       "0                    Science & Technology  \n",
       "1  Information & Communication Technology  \n",
       "2  Information & Communication Technology  \n",
       "3              Mining, Resources & Energy  \n",
       "4                    Science & Technology  \n",
       "5                    Science & Technology  \n",
       "6                    Science & Technology  \n",
       "7  Information & Communication Technology  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_join = r\"\"\"SELECT datascience.job_location, datascience.job_title, datascience.salary,joblisting.job_class\n",
    "FROM datascience\n",
    "INNER JOIN joblisting\n",
    "ON datascience.job_location = joblisting.job_location\"\"\"\n",
    "pd.read_sql_query(sql_join, con=engine).head(20)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.13 ('PythonData')",
   "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.13"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "9da66beca27f19bdf81de16e0121a30aa04d4edd5eb88e9a5610932102b72b6f"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}