Data/subscriber-pipeline/Subscriber Pipeline.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2c8e9936-5f02-498e-a690-25a93c5953fa",
   "metadata": {},
   "source": [
    "# Codecademy Subscriber Pipeline Portfolio Project\n",
    "\n",
    "For this project, we will build a data engineering pipeline to regularly transform a messy database into a clean source for an analytics team.\n",
    "\n",
    "## Scenario\n",
    "\n",
    "We will be working with a mock database of long-term cancelled subscribers for a fictional subscription company. This database is regularly updated from multiple sources, and needs to be routinely cleaned and transformed into usable shape with as little human intervention as possible.\n",
    "\n",
    "## About the Data\n",
    "\n",
    "It’s important to practice working with customer data, especially as a data engineer. But it would be unethical to share actual customer data, so we are using a realistic but entirely fictional dataset. Our version of the dataset is based around a fictional education company called Cademycode.\n",
    "\n",
    "## Project Objectives\n",
    "\n",
    "- Complete a project to add to portfolio\n",
    "- Use Jupyter notebooks to explore and clean a dataset\n",
    "- Use Python to automate data cleaning and transformation using unit tests and error logging\n",
    "- Use Bash scripts to automate file management and run scripts\n",
    "\n",
    "## Prerequisites\n",
    "\n",
    "- Intermediate and Advanced Python 3\n",
    "- Pandas\n",
    "- Bash Scripting\n",
    "\n",
    "## Project Tasks\n",
    "\n",
    "- Set Up\n",
    "- Inspect and Clean Data\n",
    "- Clean the Output CSV\n",
    "- Develop Unit Test and Logs\n",
    "- Create a Bash Script\n",
    "- Create a Readme\n",
    "- Create a Writeup\n",
    "- Save your Project\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d997e93c-6cf7-47a3-b685-901ce99af17e",
   "metadata": {},
   "source": [
    "## Set Up\n",
    "\n",
    "Download the starter kit and set up your working directory to explore the data! When you’re ready, connect to the starter database by loading `dev/cademycode.db` in a Jupyter notebook.\n",
    "\n",
    "- Download the starter kit\n",
    "- Unzip the starter kit\n",
    "- You should get a folder containing a folder `/dev`\n",
    "- Create a Jupyter notebook in the `/dev` folder for your initial exploration\n",
    "- Use the sqlite3 Python package or SQLAlchemy to establish a database connection\n",
    "- Use the database `/cademycode.db` for your development"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "35d7832c-9f15-41ee-9a70-4e45b8db722e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection to database successful.\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "\n",
    "con = sqlite3.connect('dev/cademycode.db')\n",
    "print('Connection to database successful.')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8aece72-3697-4756-a297-f928ee75ca87",
   "metadata": {},
   "source": [
    "## Inspect and Clean the data\n",
    "\n",
    "Import the tables in `cademycode.db` as dataframes. Inspect the tables for missing or invalid data and perform any data cleaning operations you think are necessary.\n",
    "\n",
    "Here are some tips to get started (but remember, there are many different routes to take):\n",
    "\n",
    "- Use a `SELECT` query on `sqlite_master` to determine the names of the tables\n",
    "- Use `pandas.read_sql_query` to read each table in as a DataFrame\n",
    "- Get familiar with the data by using the `.head()` function to explore the first handful of rows in the database.\n",
    "- Look for null values or invalide datatypes by using\n",
    "- `.info()` to display a summary table of each column\n",
    "- `.describe()` to calculate summary statistics for all numerical columns\n",
    "- `.value_counts()` to display each column’s distinct values.\n",
    "\n",
    "Let's begin by listing all tables from database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e542f7be-0980-4535-b274-f23a92485211",
   "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>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cademycode_students</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>cademycode_courses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>cademycode_student_jobs</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      name\n",
       "0      cademycode_students\n",
       "1       cademycode_courses\n",
       "2  cademycode_student_jobs"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tables = pd.read_sql_query(\"SELECT name FROM sqlite_master WHERE type='table';\", con)\n",
    "tables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd98f79c-b1f3-4257-88f2-17042c4a078d",
   "metadata": {},
   "source": [
    "Now let's import these tables as DataFrames and inspect them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a9f54213-eeb2-4659-8cfd-6cdf1733252e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Table: cademycode_students\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>uuid</th>\n",
       "      <th>name</th>\n",
       "      <th>dob</th>\n",
       "      <th>sex</th>\n",
       "      <th>contact_info</th>\n",
       "      <th>job_id</th>\n",
       "      <th>num_course_taken</th>\n",
       "      <th>current_career_path_id</th>\n",
       "      <th>time_spent_hrs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Annabelle Avery</td>\n",
       "      <td>1943-07-03</td>\n",
       "      <td>F</td>\n",
       "      <td>{\"mailing_address\": \"303 N Timber Key, Irondal...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Micah Rubio</td>\n",
       "      <td>1991-02-07</td>\n",
       "      <td>M</td>\n",
       "      <td>{\"mailing_address\": \"767 Crescent Fair, Shoals...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Hosea Dale</td>\n",
       "      <td>1989-12-07</td>\n",
       "      <td>M</td>\n",
       "      <td>{\"mailing_address\": \"P.O. Box 41269, St. Bonav...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Mariann Kirk</td>\n",
       "      <td>1988-07-31</td>\n",
       "      <td>F</td>\n",
       "      <td>{\"mailing_address\": \"517 SE Wintergreen Isle, ...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Lucio Alexander</td>\n",
       "      <td>1963-08-31</td>\n",
       "      <td>M</td>\n",
       "      <td>{\"mailing_address\": \"18 Cinder Cliff, Doyles b...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   uuid             name         dob sex  \\\n",
       "0     1  Annabelle Avery  1943-07-03   F   \n",
       "1     2      Micah Rubio  1991-02-07   M   \n",
       "2     3       Hosea Dale  1989-12-07   M   \n",
       "3     4     Mariann Kirk  1988-07-31   F   \n",
       "4     5  Lucio Alexander  1963-08-31   M   \n",
       "\n",
       "                                        contact_info job_id num_course_taken  \\\n",
       "0  {\"mailing_address\": \"303 N Timber Key, Irondal...    7.0              6.0   \n",
       "1  {\"mailing_address\": \"767 Crescent Fair, Shoals...    7.0              5.0   \n",
       "2  {\"mailing_address\": \"P.O. Box 41269, St. Bonav...    7.0              8.0   \n",
       "3  {\"mailing_address\": \"517 SE Wintergreen Isle, ...    6.0              7.0   \n",
       "4  {\"mailing_address\": \"18 Cinder Cliff, Doyles b...    7.0             14.0   \n",
       "\n",
       "  current_career_path_id time_spent_hrs  \n",
       "0                    1.0           4.99  \n",
       "1                    8.0            4.4  \n",
       "2                    8.0           6.74  \n",
       "3                    9.0          12.31  \n",
       "4                    3.0           5.64  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Info:\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5000 entries, 0 to 4999\n",
      "Data columns (total 9 columns):\n",
      " #   Column                  Non-Null Count  Dtype \n",
      "---  ------                  --------------  ----- \n",
      " 0   uuid                    5000 non-null   int64 \n",
      " 1   name                    5000 non-null   object\n",
      " 2   dob                     5000 non-null   object\n",
      " 3   sex                     5000 non-null   object\n",
      " 4   contact_info            5000 non-null   object\n",
      " 5   job_id                  4995 non-null   object\n",
      " 6   num_course_taken        4749 non-null   object\n",
      " 7   current_career_path_id  4529 non-null   object\n",
      " 8   time_spent_hrs          4529 non-null   object\n",
      "dtypes: int64(1), object(8)\n",
      "memory usage: 351.7+ KB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "None"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Describe:\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>uuid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>5000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2500.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1443.520003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1250.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2500.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>3750.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>5000.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              uuid\n",
       "count  5000.000000\n",
       "mean   2500.500000\n",
       "std    1443.520003\n",
       "min       1.000000\n",
       "25%    1250.750000\n",
       "50%    2500.500000\n",
       "75%    3750.250000\n",
       "max    5000.000000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Value counts for uuid:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "uuid\n",
       "1       1\n",
       "3331    1\n",
       "3338    1\n",
       "3337    1\n",
       "3336    1\n",
       "       ..\n",
       "1667    1\n",
       "1666    1\n",
       "1665    1\n",
       "1664    1\n",
       "5000    1\n",
       "Name: count, Length: 5000, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Value counts for name:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "name\n",
       "Melvin Felt              2\n",
       "Robbie Davies            2\n",
       "Annabelle Avery          1\n",
       "Kelley Munnickhuijsen    1\n",
       "Rachel de Kruijff        1\n",
       "                        ..\n",
       "Cleopatra Singleton      1\n",
       "Linwood Patrick          1\n",
       "Marcia Beeldhouwer       1\n",
       "Arnoldo Rodgers          1\n",
       "Elton Otway              1\n",
       "Name: count, Length: 4998, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Value counts for dob:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "dob\n",
       "1993-08-03    4\n",
       "1955-05-27    4\n",
       "1953-12-05    3\n",
       "1995-06-13    3\n",
       "1956-05-22    3\n",
       "             ..\n",
       "1960-09-21    1\n",
       "1992-05-08    1\n",
       "1979-03-22    1\n",
       "1966-12-30    1\n",
       "1994-12-23    1\n",
       "Name: count, Length: 4492, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Value counts for sex:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "sex\n",
       "M    1995\n",
       "F    1990\n",
       "N    1015\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Value counts for contact_info:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "contact_info\n",
       "{\"mailing_address\": \"303 N Timber Key, Irondale, Wisconsin, 84736\", \"email\": \"annabelle_avery9376@woohoo.com\"}      1\n",
       "{\"mailing_address\": \"768 Silent Skyway, Impact, Idaho, 90270\", \"email\": \"orval_devos4502@coldmail.com\"}             1\n",
       "{\"mailing_address\": \"409 SW First Pike, Leary, North Carolina, 43428\", \"email\": \"rachel886@woohoo.com\"}             1\n",
       "{\"mailing_address\": \"554 Broad Rose, Flagler Beach, Florida, 65799\", \"email\": \"allan8306@inlook.com\"}               1\n",
       "{\"mailing_address\": \"872 Wagon Land, Guthrie Center, Colorado, 86498\", \"email\": \"isidro3025@woohoo.com\"}            1\n",
       "                                                                                                                   ..\n",
       "{\"mailing_address\": \"470 Essex Curve, Copan, Mississippi, 86309\", \"email\": \"cleopatra_singleton7791@inlook.com\"}    1\n",
       "{\"mailing_address\": \"162 Iron Chase, Campbell Station, Oklahoma, 78795\", \"email\": \"patrick6416@inlook.com\"}         1\n",
       "{\"mailing_address\": \"889 Mountain Alley, Quioque, Florida, 22930\", \"email\": \"marcia9217@coldmail.com\"}              1\n",
       "{\"mailing_address\": \"P.O. Box 69948, Hastings borough, Iowa, 13359\", \"email\": \"rodgers8447@hmail.com\"}              1\n",
       "{\"mailing_address\": \"406 Zephyr Port, Oskaloosa, Alabama, 74534\", \"email\": \"elton_otway5644@inlook.com\"}            1\n",
       "Name: count, Length: 5000, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Value counts for job_id:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "job_id\n",
       "2.0    706\n",
       "1.0    693\n",
       "7.0    680\n",
       "3.0    675\n",
       "4.0    671\n",
       "5.0    660\n",
       "6.0    657\n",
       "8.0    253\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Value counts for num_course_taken:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "num_course_taken\n",
       "5.0     341\n",
       "12.0    332\n",
       "2.0     312\n",
       "15.0    309\n",
       "10.0    306\n",
       "7.0     303\n",
       "13.0    297\n",
       "0.0     296\n",
       "8.0     291\n",
       "11.0    289\n",
       "4.0     285\n",
       "6.0     282\n",
       "14.0    280\n",
       "3.0     279\n",
       "1.0     279\n",
       "9.0     268\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Value counts for current_career_path_id:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "current_career_path_id\n",
       "5.0     476\n",
       "3.0     469\n",
       "10.0    460\n",
       "1.0     459\n",
       "6.0     454\n",
       "2.0     450\n",
       "7.0     449\n",
       "9.0     441\n",
       "8.0     437\n",
       "4.0     434\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Value counts for time_spent_hrs:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "time_spent_hrs\n",
       "5.93     8\n",
       "17.47    8\n",
       "11.9     7\n",
       "7.05     7\n",
       "2.91     7\n",
       "        ..\n",
       "27.53    1\n",
       "8.07     1\n",
       "27.51    1\n",
       "29.66    1\n",
       "23.54    1\n",
       "Name: count, Length: 2192, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Table: cademycode_courses\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>career_path_id</th>\n",
       "      <th>career_path_name</th>\n",
       "      <th>hours_to_complete</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>data scientist</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>data engineer</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>data analyst</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>software engineering</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>backend engineer</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   career_path_id      career_path_name  hours_to_complete\n",
       "0               1        data scientist                 20\n",
       "1               2         data engineer                 20\n",
       "2               3          data analyst                 12\n",
       "3               4  software engineering                 25\n",
       "4               5      backend engineer                 18"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Info:\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10 entries, 0 to 9\n",
      "Data columns (total 3 columns):\n",
      " #   Column             Non-Null Count  Dtype \n",
      "---  ------             --------------  ----- \n",
      " 0   career_path_id     10 non-null     int64 \n",
      " 1   career_path_name   10 non-null     object\n",
      " 2   hours_to_complete  10 non-null     int64 \n",
      "dtypes: int64(2), object(1)\n",
      "memory usage: 372.0+ bytes\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "None"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Describe:\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>career_path_id</th>\n",
       "      <th>hours_to_complete</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>10.00000</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>5.50000</td>\n",
       "      <td>21.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>3.02765</td>\n",
       "      <td>6.707376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.00000</td>\n",
       "      <td>12.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>3.25000</td>\n",
       "      <td>18.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>5.50000</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>7.75000</td>\n",
       "      <td>26.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>10.00000</td>\n",
       "      <td>35.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       career_path_id  hours_to_complete\n",
       "count        10.00000          10.000000\n",
       "mean          5.50000          21.900000\n",
       "std           3.02765           6.707376\n",
       "min           1.00000          12.000000\n",
       "25%           3.25000          18.500000\n",
       "50%           5.50000          20.000000\n",
       "75%           7.75000          26.500000\n",
       "max          10.00000          35.000000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Value counts for career_path_id:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "career_path_id\n",
       "1     1\n",
       "2     1\n",
       "3     1\n",
       "4     1\n",
       "5     1\n",
       "6     1\n",
       "7     1\n",
       "8     1\n",
       "9     1\n",
       "10    1\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Value counts for career_path_name:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "career_path_name\n",
       "data scientist               1\n",
       "data engineer                1\n",
       "data analyst                 1\n",
       "software engineering         1\n",
       "backend engineer             1\n",
       "frontend engineer            1\n",
       "iOS developer                1\n",
       "android developer            1\n",
       "machine learning engineer    1\n",
       "ux/ui designer               1\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Value counts for hours_to_complete:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "hours_to_complete\n",
       "20    3\n",
       "27    2\n",
       "12    1\n",
       "25    1\n",
       "18    1\n",
       "35    1\n",
       "15    1\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Table: cademycode_student_jobs\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_id</th>\n",
       "      <th>job_category</th>\n",
       "      <th>avg_salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>analytics</td>\n",
       "      <td>86000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>engineer</td>\n",
       "      <td>101000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>software developer</td>\n",
       "      <td>110000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>creative</td>\n",
       "      <td>66000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>financial services</td>\n",
       "      <td>135000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   job_id        job_category  avg_salary\n",
       "0       1           analytics       86000\n",
       "1       2            engineer      101000\n",
       "2       3  software developer      110000\n",
       "3       4            creative       66000\n",
       "4       5  financial services      135000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Info:\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 13 entries, 0 to 12\n",
      "Data columns (total 3 columns):\n",
      " #   Column        Non-Null Count  Dtype \n",
      "---  ------        --------------  ----- \n",
      " 0   job_id        13 non-null     int64 \n",
      " 1   job_category  13 non-null     object\n",
      " 2   avg_salary    13 non-null     int64 \n",
      "dtypes: int64(2), object(1)\n",
      "memory usage: 444.0+ bytes\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "None"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Describe:\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_id</th>\n",
       "      <th>avg_salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>13.000000</td>\n",
       "      <td>13.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>4.384615</td>\n",
       "      <td>89230.769231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.662657</td>\n",
       "      <td>34727.879881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>66000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>4.000000</td>\n",
       "      <td>86000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>6.000000</td>\n",
       "      <td>110000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>9.000000</td>\n",
       "      <td>135000.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          job_id     avg_salary\n",
       "count  13.000000      13.000000\n",
       "mean    4.384615   89230.769231\n",
       "std     2.662657   34727.879881\n",
       "min     0.000000   10000.000000\n",
       "25%     3.000000   66000.000000\n",
       "50%     4.000000   86000.000000\n",
       "75%     6.000000  110000.000000\n",
       "max     9.000000  135000.000000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Value counts for job_id:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "job_id\n",
       "3    2\n",
       "4    2\n",
       "5    2\n",
       "1    1\n",
       "2    1\n",
       "6    1\n",
       "7    1\n",
       "8    1\n",
       "9    1\n",
       "0    1\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Value counts for job_category:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "job_category\n",
       "software developer    2\n",
       "creative              2\n",
       "financial services    2\n",
       "analytics             1\n",
       "engineer              1\n",
       "education             1\n",
       "HR                    1\n",
       "student               1\n",
       "healthcare            1\n",
       "other                 1\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Value counts for avg_salary:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "avg_salary\n",
       "110000    2\n",
       "66000     2\n",
       "135000    2\n",
       "80000     2\n",
       "86000     1\n",
       "101000    1\n",
       "61000     1\n",
       "10000     1\n",
       "120000    1\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def inspect_dataframes(dataframes):\n",
    "    for table_name, df in dataframes.items():\n",
    "        print(f\"\\nTable: {table_name}\")\n",
    "        display(df.head())\n",
    "        print(\"\\nInfo:\")\n",
    "        display(df.info())\n",
    "        print(\"\\nDescribe:\")\n",
    "        display(df.describe())\n",
    "        for column in df.columns:\n",
    "            print(f\"\\nValue counts for {column}:\")\n",
    "            display(df[column].value_counts())\n",
    "\n",
    "table_names = tables['name'].tolist()\n",
    "df = {table: pd.read_sql_query(f\"SELECT * FROM {table}\", con) for table in table_names}\n",
    "\n",
    "inspect_dataframes(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ea4119c-7f5a-48a0-9261-c0b323fd7a25",
   "metadata": {},
   "source": [
    "Let's take a look at what insights we might get from this data:\n",
    "\n",
    "### Table: `cademycode_students`\n",
    "1. **Data Types**:\n",
    "   - All columns, except `uuid` are `object`type even though they keep different types of data (date, numbers, etc.).\n",
    "\n",
    "2. **Null Data**:\n",
    "   - Columns `job_id`, `num_course_taken`, `current_career_path_id` and `time_spent_hrs` have null data.\n",
    "\n",
    "3. **Doubled data**:\n",
    "   - Two names (`Melvin Felt` and `Robbie Davies`) have been counted twice.\n",
    "\n",
    "4. **Gender Distribution**:\n",
    "   - We have the same proportion of men (`M`) and women (`F`) in the courses, with a significant number of `N` (non specified).\n",
    "\n",
    "5. **Contact Information**:\n",
    "   - Column `contact_info` has a `mailing_address` and an `email`.\n",
    "\n",
    "6. **Job IDs**:\n",
    "   - Distinct values for `job_id`, probably related to `cademycode_student_jobs` table.\n",
    "\n",
    "7. **Number of courses taken**:\n",
    "   - `num_course_taken` indicates the number of courses users took.\n",
    "\n",
    "8. **Time Spent**:\n",
    "   - `time_spent_hrs` shows time spent from users in courses.\n",
    "\n",
    "### Table: `cademycode_courses`\n",
    "1. **Table Data**:\n",
    "   - There are 10 courses added, with the necessarty hours to complete each.\n",
    "\n",
    "2. **Data Integrity**:\n",
    "   - Values are complete and coherent, as there is no apparent issues.\n",
    "\n",
    "### Table: `cademycode_student_jobs`\n",
    "1. **Category and Pay**:\n",
    "   - Table contains job category and average pay for each profession.\n",
    "\n",
    "2. **Pay Statistics**:\n",
    "   - Average pay is 89,230.77, with a standard deviation of 34,727.88, with a minimum of 10,000.00 and a maximum of 135,000.00\n",
    "\n",
    "3. **Doubled Values**:\n",
    "   - There are doubled values for `job_id`.\n",
    "\n",
    "## Next Steps\n",
    "\n",
    "1. **Data Tidying**:\n",
    "   - Correct data type in columns for `cademycode_students`.\n",
    "   - Treat null values for columns `job_id`, `num_course_taken`, `current_career_path_id` and `time_spent_hrs`.\n",
    "   - Solve doubled data in names and job id.\n",
    "\n",
    "2. **Data Transformation**:\n",
    "   - Dividing `contact_info` into separate columns for `mailing_address` and `email`.\n",
    "   - Join related tables (i.e., joining `job_id` to `cademycode_students` and `cademycode_student_jobs`).\n",
    "\n",
    "3. **Final Table Creation**:\n",
    "   - Build one unique final table, with joined data tidy data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0aab51df-7608-4fcd-b048-300bf5019a06",
   "metadata": {},
   "source": [
    "Let's begin by converting columns `dob`, `job_id`, `num_course_taken`, `current_career_path_id` e `time_spent_hrs` to appropriate data types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "aa15c4c2-9299-43c8-8710-ed65046642b8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data Types after Convertion:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "uuid                               int64\n",
       "name                              object\n",
       "dob                       datetime64[ns]\n",
       "sex                               object\n",
       "contact_info                      object\n",
       "job_id                           float64\n",
       "num_course_taken                 float64\n",
       "current_career_path_id           float64\n",
       "time_spent_hrs                   float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "students_df = df['cademycode_students'].copy()\n",
    "\n",
    "students_df['dob'] = pd.to_datetime(students_df['dob'], errors='coerce')\n",
    "students_df['job_id'] = pd.to_numeric(students_df['job_id'], errors='coerce')\n",
    "students_df['num_course_taken'] = pd.to_numeric(students_df['num_course_taken'], errors='coerce')\n",
    "students_df['current_career_path_id'] = pd.to_numeric(students_df['current_career_path_id'], errors='coerce')\n",
    "students_df['time_spent_hrs'] = pd.to_numeric(students_df['time_spent_hrs'], errors='coerce')\n",
    "\n",
    "print('Data Types after Convertion:')\n",
    "students_df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09ff33e9-3fc7-48c2-9c87-ddcde86c9e2b",
   "metadata": {},
   "source": [
    "Now we can treat null values for columns `job_id`, `num_course_taken`, `current_career_path_id` e `time_spent_hrs`.\n",
    "\n",
    "To deal with null values, we can:\n",
    "\n",
    "- Fill nulls with standard values (i.e. 0);\n",
    "- Fill nulls with median values for column;\n",
    "- Remove rows with null values (if applicable).\n",
    "\n",
    "In this case, we will fill `job_id` e `current_career_path_id` with `0` (indicating 'unemployed' and 'not enrolled') and then use median for `num_course_taken` e `time_spent_hrs`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "87a2eead-8c10-485f-8226-ea0dbb8710d2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Null values after filling:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "uuid                      0\n",
       "name                      0\n",
       "dob                       0\n",
       "sex                       0\n",
       "contact_info              0\n",
       "job_id                    0\n",
       "num_course_taken          0\n",
       "current_career_path_id    0\n",
       "time_spent_hrs            0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "students_df.loc[:, 'job_id'] = students_df['job_id'].fillna(0)\n",
    "students_df.loc[:, 'current_career_path_id'] = students_df['current_career_path_id'].fillna(0)\n",
    "\n",
    "students_df.loc[:, 'num_course_taken'] = students_df['num_course_taken'].fillna(students_df['num_course_taken'].median())\n",
    "students_df.loc[:, 'time_spent_hrs'] = students_df['time_spent_hrs'].fillna(students_df['time_spent_hrs'].median())\n",
    "\n",
    "print(\"Null values after filling:\")\n",
    "students_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca8b86a1-19b3-4dfe-9281-aeddcba6cc64",
   "metadata": {},
   "source": [
    "Now let's identify and treat duplication data in `cademycode_students`. First, let's show all rows with the same `name`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f2f32ca9-7bb2-4b89-9233-206626e50559",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Rows with duplicate names:\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>uuid</th>\n",
       "      <th>name</th>\n",
       "      <th>dob</th>\n",
       "      <th>sex</th>\n",
       "      <th>contact_info</th>\n",
       "      <th>job_id</th>\n",
       "      <th>num_course_taken</th>\n",
       "      <th>current_career_path_id</th>\n",
       "      <th>time_spent_hrs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>534</th>\n",
       "      <td>535</td>\n",
       "      <td>Robbie Davies</td>\n",
       "      <td>1965-06-11</td>\n",
       "      <td>F</td>\n",
       "      <td>{\"mailing_address\": \"666 Dusty Land, Pangburn,...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1118</th>\n",
       "      <td>1119</td>\n",
       "      <td>Melvin Felt</td>\n",
       "      <td>1955-07-30</td>\n",
       "      <td>N</td>\n",
       "      <td>{\"mailing_address\": \"804 Rustic Elm, Geneseo v...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2116</th>\n",
       "      <td>2117</td>\n",
       "      <td>Robbie Davies</td>\n",
       "      <td>1942-10-17</td>\n",
       "      <td>M</td>\n",
       "      <td>{\"mailing_address\": \"111 Squaw Alley, Buckeye,...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3583</th>\n",
       "      <td>3584</td>\n",
       "      <td>Melvin Felt</td>\n",
       "      <td>1987-08-25</td>\n",
       "      <td>N</td>\n",
       "      <td>{\"mailing_address\": \"54 Noble Loaf Run, Lakela...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      uuid           name        dob sex  \\\n",
       "534    535  Robbie Davies 1965-06-11   F   \n",
       "1118  1119    Melvin Felt 1955-07-30   N   \n",
       "2116  2117  Robbie Davies 1942-10-17   M   \n",
       "3583  3584    Melvin Felt 1987-08-25   N   \n",
       "\n",
       "                                           contact_info  job_id  \\\n",
       "534   {\"mailing_address\": \"666 Dusty Land, Pangburn,...     5.0   \n",
       "1118  {\"mailing_address\": \"804 Rustic Elm, Geneseo v...     3.0   \n",
       "2116  {\"mailing_address\": \"111 Squaw Alley, Buckeye,...     7.0   \n",
       "3583  {\"mailing_address\": \"54 Noble Loaf Run, Lakela...     3.0   \n",
       "\n",
       "      num_course_taken  current_career_path_id  time_spent_hrs  \n",
       "534               15.0                     3.0            0.53  \n",
       "1118               6.0                    10.0            2.77  \n",
       "2116               0.0                     0.0           10.67  \n",
       "3583              11.0                     1.0            0.47  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "duplicate_names = ['Melvin Felt', 'Robbie Davies']\n",
    "duplicate_rows = students_df[students_df['name'].isin(duplicate_names)]\n",
    "\n",
    "print('Rows with duplicate names:')\n",
    "display(duplicate_rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "202a540c-5955-4605-8cad-9f7e7fb71a7d",
   "metadata": {},
   "source": [
    "This table shows that the names are not duplicated, instead they are from homonymous people. To be sure, let's count duplicated data in the dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "503f9414-194f-4d6c-9b32-afd22fc4cecb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of duplicates found: 0\n",
      "Duplicates found:\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>uuid</th>\n",
       "      <th>name</th>\n",
       "      <th>dob</th>\n",
       "      <th>sex</th>\n",
       "      <th>contact_info</th>\n",
       "      <th>job_id</th>\n",
       "      <th>num_course_taken</th>\n",
       "      <th>current_career_path_id</th>\n",
       "      <th>time_spent_hrs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [uuid, name, dob, sex, contact_info, job_id, num_course_taken, current_career_path_id, time_spent_hrs]\n",
       "Index: []"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "duplicates = students_df.duplicated(subset=['name', 'dob', 'sex', 'contact_info'])\n",
    "print(f\"Number of duplicates found: {duplicates.sum()}\")\n",
    "\n",
    "# Mostrar duplicações\n",
    "print(\"Duplicates found:\")\n",
    "display(students_df[duplicates])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f62cd62-beb3-40fa-a909-b5f34bf422ac",
   "metadata": {},
   "source": [
    "As in fact there is no duplicated rows, there is no need to remove these entries. So let's go ahead and divide `column_info` into `mailing_address` and `email`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "29a22fc2-d48d-43a9-82d0-b9fe16253111",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data after contact_info split:\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>uuid</th>\n",
       "      <th>name</th>\n",
       "      <th>dob</th>\n",
       "      <th>sex</th>\n",
       "      <th>job_id</th>\n",
       "      <th>num_course_taken</th>\n",
       "      <th>current_career_path_id</th>\n",
       "      <th>time_spent_hrs</th>\n",
       "      <th>mailing_address</th>\n",
       "      <th>email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Annabelle Avery</td>\n",
       "      <td>1943-07-03</td>\n",
       "      <td>F</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.99</td>\n",
       "      <td>303 N Timber Key, Irondale, Wisconsin, 84736</td>\n",
       "      <td>annabelle_avery9376@woohoo.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Micah Rubio</td>\n",
       "      <td>1991-02-07</td>\n",
       "      <td>M</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4.40</td>\n",
       "      <td>767 Crescent Fair, Shoals, Indiana, 37439</td>\n",
       "      <td>rubio6772@hmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Hosea Dale</td>\n",
       "      <td>1989-12-07</td>\n",
       "      <td>M</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.74</td>\n",
       "      <td>P.O. Box 41269, St. Bonaventure, Virginia, 83637</td>\n",
       "      <td>hosea_dale8084@coldmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Mariann Kirk</td>\n",
       "      <td>1988-07-31</td>\n",
       "      <td>F</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12.31</td>\n",
       "      <td>517 SE Wintergreen Isle, Lane, Arkansas, 82242</td>\n",
       "      <td>kirk4005@hmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Lucio Alexander</td>\n",
       "      <td>1963-08-31</td>\n",
       "      <td>M</td>\n",
       "      <td>7.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.64</td>\n",
       "      <td>18 Cinder Cliff, Doyles borough, Rhode Island,...</td>\n",
       "      <td>alexander9810@hmail.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   uuid             name        dob sex  job_id  num_course_taken  \\\n",
       "0     1  Annabelle Avery 1943-07-03   F     7.0               6.0   \n",
       "1     2      Micah Rubio 1991-02-07   M     7.0               5.0   \n",
       "2     3       Hosea Dale 1989-12-07   M     7.0               8.0   \n",
       "3     4     Mariann Kirk 1988-07-31   F     6.0               7.0   \n",
       "4     5  Lucio Alexander 1963-08-31   M     7.0              14.0   \n",
       "\n",
       "   current_career_path_id  time_spent_hrs  \\\n",
       "0                     1.0            4.99   \n",
       "1                     8.0            4.40   \n",
       "2                     8.0            6.74   \n",
       "3                     9.0           12.31   \n",
       "4                     3.0            5.64   \n",
       "\n",
       "                                     mailing_address  \\\n",
       "0       303 N Timber Key, Irondale, Wisconsin, 84736   \n",
       "1          767 Crescent Fair, Shoals, Indiana, 37439   \n",
       "2   P.O. Box 41269, St. Bonaventure, Virginia, 83637   \n",
       "3     517 SE Wintergreen Isle, Lane, Arkansas, 82242   \n",
       "4  18 Cinder Cliff, Doyles borough, Rhode Island,...   \n",
       "\n",
       "                            email  \n",
       "0  annabelle_avery9376@woohoo.com  \n",
       "1             rubio6772@hmail.com  \n",
       "2     hosea_dale8084@coldmail.com  \n",
       "3              kirk4005@hmail.com  \n",
       "4         alexander9810@hmail.com  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import json\n",
    "\n",
    "def extract_contact_info(contact_info):\n",
    "    try:\n",
    "        # Here we extracted data as a json object because the data in contact_info is in a json format.\n",
    "        info = json.loads(contact_info.replace(\"'\", '\"'))\n",
    "        return pd.Series([info.get('mailing_address'), info.get('email')])\n",
    "    except json.JSONDecoderError:\n",
    "        return pd.Series([None, None])\n",
    "\n",
    "students_df[['mailing_address', 'email']] = students_df['contact_info'].apply(extract_contact_info)\n",
    "students_df.drop(columns=['contact_info'], inplace=True)\n",
    "\n",
    "print('Data after contact_info split:')\n",
    "display(students_df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a9ace05-0de9-4641-8cfb-cf09f69db313",
   "metadata": {},
   "source": [
    "Now let's check for duplicate data in the other tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "fe1afa90-956c-4eca-b02d-05059c77f2a7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Duplications find in cademycode_courses: 0\n",
      "No duplication found on cademycode_courses.\n",
      "----------\n",
      "Duplications find in cademycode_student_jobs: 3\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_id</th>\n",
       "      <th>job_category</th>\n",
       "      <th>avg_salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3</td>\n",
       "      <td>software developer</td>\n",
       "      <td>110000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>4</td>\n",
       "      <td>creative</td>\n",
       "      <td>66000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>5</td>\n",
       "      <td>financial services</td>\n",
       "      <td>135000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    job_id        job_category  avg_salary\n",
       "10       3  software developer      110000\n",
       "11       4            creative       66000\n",
       "12       5  financial services      135000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "courses_df = df['cademycode_courses'].copy()\n",
    "course_duplicates = courses_df.duplicated()\n",
    "\n",
    "print(f\"Duplications find in cademycode_courses: {course_duplicates.sum()}\")\n",
    "\n",
    "if course_duplicates.sum() > 0 : display(courses_df[course_duplicates])\n",
    "else : print('No duplication found on cademycode_courses.')\n",
    "\n",
    "print('----------')\n",
    "\n",
    "jobs_df = df['cademycode_student_jobs'].copy()\n",
    "jobs_duplicates = jobs_df.duplicated()\n",
    "\n",
    "print(f\"Duplications find in cademycode_student_jobs: {jobs_duplicates.sum()}\")\n",
    "\n",
    "if jobs_duplicates.sum() > 0 : display(jobs_df[jobs_duplicates])\n",
    "else : print('No duplication found on cademycode_student_jobs.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "b6069020-3b8f-4186-b2f8-e03a880f401d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table jobs_df after duplicates removal:\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_id</th>\n",
       "      <th>job_category</th>\n",
       "      <th>avg_salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>analytics</td>\n",
       "      <td>86000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>engineer</td>\n",
       "      <td>101000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>software developer</td>\n",
       "      <td>110000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>creative</td>\n",
       "      <td>66000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>financial services</td>\n",
       "      <td>135000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>education</td>\n",
       "      <td>61000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>HR</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>student</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>healthcare</td>\n",
       "      <td>120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0</td>\n",
       "      <td>other</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   job_id        job_category  avg_salary\n",
       "0       1           analytics       86000\n",
       "1       2            engineer      101000\n",
       "2       3  software developer      110000\n",
       "3       4            creative       66000\n",
       "4       5  financial services      135000\n",
       "5       6           education       61000\n",
       "6       7                  HR       80000\n",
       "7       8             student       10000\n",
       "8       9          healthcare      120000\n",
       "9       0               other       80000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "jobs_df_cleaned = jobs_df.drop_duplicates()\n",
    "\n",
    "print(\"Table jobs_df after duplicates removal:\")\n",
    "display(jobs_df_cleaned)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a80aec27-cf99-4187-be89-128db203356d",
   "metadata": {},
   "source": [
    "Now we can join `students_df`, `jobs_df` and `courses_df` tables to create a final consolidated table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "2f124c29-2faf-4b3c-96ab-ebfbf701c705",
   "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>uuid</th>\n",
       "      <th>name</th>\n",
       "      <th>dob</th>\n",
       "      <th>sex</th>\n",
       "      <th>job_id</th>\n",
       "      <th>num_course_taken</th>\n",
       "      <th>current_career_path_id</th>\n",
       "      <th>time_spent_hrs</th>\n",
       "      <th>mailing_address</th>\n",
       "      <th>email</th>\n",
       "      <th>job_category</th>\n",
       "      <th>avg_salary</th>\n",
       "      <th>career_path_id</th>\n",
       "      <th>career_path_name</th>\n",
       "      <th>hours_to_complete</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Annabelle Avery</td>\n",
       "      <td>1943-07-03</td>\n",
       "      <td>F</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.99</td>\n",
       "      <td>303 N Timber Key, Irondale, Wisconsin, 84736</td>\n",
       "      <td>annabelle_avery9376@woohoo.com</td>\n",
       "      <td>HR</td>\n",
       "      <td>80000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>data scientist</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Micah Rubio</td>\n",
       "      <td>1991-02-07</td>\n",
       "      <td>M</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4.40</td>\n",
       "      <td>767 Crescent Fair, Shoals, Indiana, 37439</td>\n",
       "      <td>rubio6772@hmail.com</td>\n",
       "      <td>HR</td>\n",
       "      <td>80000</td>\n",
       "      <td>8.0</td>\n",
       "      <td>android developer</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Hosea Dale</td>\n",
       "      <td>1989-12-07</td>\n",
       "      <td>M</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.74</td>\n",
       "      <td>P.O. Box 41269, St. Bonaventure, Virginia, 83637</td>\n",
       "      <td>hosea_dale8084@coldmail.com</td>\n",
       "      <td>HR</td>\n",
       "      <td>80000</td>\n",
       "      <td>8.0</td>\n",
       "      <td>android developer</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Mariann Kirk</td>\n",
       "      <td>1988-07-31</td>\n",
       "      <td>F</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12.31</td>\n",
       "      <td>517 SE Wintergreen Isle, Lane, Arkansas, 82242</td>\n",
       "      <td>kirk4005@hmail.com</td>\n",
       "      <td>education</td>\n",
       "      <td>61000</td>\n",
       "      <td>9.0</td>\n",
       "      <td>machine learning engineer</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Lucio Alexander</td>\n",
       "      <td>1963-08-31</td>\n",
       "      <td>M</td>\n",
       "      <td>7.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.64</td>\n",
       "      <td>18 Cinder Cliff, Doyles borough, Rhode Island,...</td>\n",
       "      <td>alexander9810@hmail.com</td>\n",
       "      <td>HR</td>\n",
       "      <td>80000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>data analyst</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   uuid             name        dob sex  job_id  num_course_taken  \\\n",
       "0     1  Annabelle Avery 1943-07-03   F     7.0               6.0   \n",
       "1     2      Micah Rubio 1991-02-07   M     7.0               5.0   \n",
       "2     3       Hosea Dale 1989-12-07   M     7.0               8.0   \n",
       "3     4     Mariann Kirk 1988-07-31   F     6.0               7.0   \n",
       "4     5  Lucio Alexander 1963-08-31   M     7.0              14.0   \n",
       "\n",
       "   current_career_path_id  time_spent_hrs  \\\n",
       "0                     1.0            4.99   \n",
       "1                     8.0            4.40   \n",
       "2                     8.0            6.74   \n",
       "3                     9.0           12.31   \n",
       "4                     3.0            5.64   \n",
       "\n",
       "                                     mailing_address  \\\n",
       "0       303 N Timber Key, Irondale, Wisconsin, 84736   \n",
       "1          767 Crescent Fair, Shoals, Indiana, 37439   \n",
       "2   P.O. Box 41269, St. Bonaventure, Virginia, 83637   \n",
       "3     517 SE Wintergreen Isle, Lane, Arkansas, 82242   \n",
       "4  18 Cinder Cliff, Doyles borough, Rhode Island,...   \n",
       "\n",
       "                            email job_category  avg_salary  career_path_id  \\\n",
       "0  annabelle_avery9376@woohoo.com           HR       80000             1.0   \n",
       "1             rubio6772@hmail.com           HR       80000             8.0   \n",
       "2     hosea_dale8084@coldmail.com           HR       80000             8.0   \n",
       "3              kirk4005@hmail.com    education       61000             9.0   \n",
       "4         alexander9810@hmail.com           HR       80000             3.0   \n",
       "\n",
       "            career_path_name  hours_to_complete  \n",
       "0             data scientist               20.0  \n",
       "1          android developer               27.0  \n",
       "2          android developer               27.0  \n",
       "3  machine learning engineer               35.0  \n",
       "4               data analyst               12.0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "merged_df_cleaned = pd.merge(students_df, jobs_df_cleaned, how='left', left_on='job_id', right_on='job_id')\n",
    "final_df_cleaned = pd.merge(merged_df_cleaned, courses_df, how='left', left_on='current_career_path_id', right_on='career_path_id')\n",
    "\n",
    "display(final_df_cleaned.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77a20765-adce-480b-ba3b-a5ea72774c7c",
   "metadata": {},
   "source": [
    "Here's a summary:\n",
    "\n",
    "1. Merge `students_df` with `jobs_df`:\n",
    "\n",
    "- We use `job_id` as the key to join the two tables.\n",
    "- `how='left'` ensures that all entries from `students_df` are kept, even if there are no matches in `jobs_df`.\n",
    "\n",
    "2. Merge `merged_df` with `courses_df`:\n",
    "\n",
    "- We use `current_career_path_id` as the key to join the two tables.\n",
    "- `how='left'` ensures that all entries from `merged_df` are kept, even if there are no matches in `courses_df`.\n",
    "\n",
    "The final table looks well consolidated and clean, with all the necessary columns and no obvious duplications or null values ​​remaining. Now we can move on to the next task."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7e7689c-7abd-48b7-a72f-905738483e21",
   "metadata": {},
   "source": [
    "## Create the Output CSV\n",
    "\n",
    "Use the cleaned tables to produce an analytics-ready SQLite database and flat CSV file. The final CSV should contain all the data the analysts might need in a single table.\n",
    "\n",
    "- Think about what fields you might want to have as an analyst – are there any you can create as part of this process?\n",
    "- It is easier to update a database than update a CSV file, so create a clean SQLite database first, and then generate the CSV from that database.\n",
    "- Make sure to validate the final table. Improper joins could result in losing rows due to unpaired keys or duplication. You can check for both by calculating the length of your dataframe before and after merges.\n",
    "\n",
    "To start, let's create a new SQLite database to store our final table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f1a4c105-6dc0-49b9-83c6-a82265574262",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Final table saved in SQLite Database \"clean_cademycode.db\".\n"
     ]
    }
   ],
   "source": [
    "clean_db_con = sqlite3.connect('dev/clean_cademycode.db')\n",
    "\n",
    "final_df_cleaned.to_sql('final_table', clean_db_con, if_exists='replace', index=False)\n",
    "print('Final table saved in SQLite Database \"clean_cademycode.db\".')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc70436a-8e43-4e89-9206-046c780fbc4f",
   "metadata": {},
   "source": [
    "Before generating the CSV, we will validate the final table to ensure there is no data loss or duplication due to improper joins."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "baff586c-fa8b-4129-b577-46733b5a82a8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of lines before joining: 5000\n",
      "Number of lines after joining: 5000\n",
      "Validation successful: The number of lines is correct.\n"
     ]
    }
   ],
   "source": [
    "original_length = len(df['cademycode_students'])\n",
    "final_length = len(final_df_cleaned)\n",
    "\n",
    "print(f\"Number of lines before joining: {original_length}\")\n",
    "print(f\"Number of lines after joining: {final_length}\")\n",
    "\n",
    "if original_length == final_length : print('Validation successful: The number of lines is correct.')\n",
    "else: print('Attention: The number of lines differs after joining')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "25dde779-fdfb-411c-a7f2-0060013ade7c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Final table saved to \"final_output.csv\".\n"
     ]
    }
   ],
   "source": [
    "final_df_from_db = pd.read_sql_query(\"SELECT * FROM final_table\", clean_db_con)\n",
    "final_df_from_db.to_csv('dev/final_output.csv', index=False)\n",
    "\n",
    "print('Final table saved to \"final_output.csv\".')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53159aed-e21a-4a10-9c36-12d777af4fb0",
   "metadata": {},
   "source": [
    "## Develop Unit Tests and Logs\n",
    "\n",
    "Turn the Jupyter Notebook into a Python script that can be run with minimal human intervention. The script should:\n",
    "\n",
    "- check for updates to the database and\n",
    "- use unit tests to protect the update process.\n",
    "\n",
    "Any updates made to the final database should be written to a changelog, and any errors from the unit tests should be written to an error log.\n",
    "\n",
    "Some ideas for unit tests include:\n",
    "\n",
    "- checking that the updated database has the same schema as the original\n",
    "- checking if the tables will join properly\n",
    "- checking if there is any new data\n",
    "\n",
    "Your changelog should include details like:\n",
    "\n",
    "- a version number\n",
    "- information about the update such as new row and missing data counts\n",
    "\n",
    "Let's add a function to check the database for new updates before running the data pipeline."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2a55f270-c9ea-4d5f-a575-5cd000510fd6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os, time\n",
    "\n",
    "db_path = 'dev/cademycode.db'\n",
    "last_mod_time = None\n",
    "\n",
    "def is_database_updated():\n",
    "    global last_mod_time\n",
    "    current_mod_time = os.path.getmtime(db_path)\n",
    "    if last_mod_time is None or current_mod_time > last_mod_time:\n",
    "        last_mod_time = current_mod_time\n",
    "        return True\n",
    "    return False"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f2eeab7-9bda-4f05-82a8-d8bac02fddcd",
   "metadata": {},
   "source": [
    "Now let's add some more unit tests to ensure the integrity of the update process."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "09eafca8-cf8e-4f0b-b9a4-1a50e0bfcfbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "import unittest\n",
    "\n",
    "class TestDataCleaning(unittest.TestCase):\n",
    "    def test_no_null_values(self):\n",
    "        self.assertFalse(final_df_cleaned.isnull().values.any(), 'There are null values in final table')\n",
    "\n",
    "    def test_correct_number_of_rows(self):\n",
    "        original_length = len(df['cademycode_students'])\n",
    "        final_length = len(final_df_cleaned)\n",
    "        self.assertEqual(original_length, final_length, 'Number of lines differ after the join')\n",
    "\n",
    "    def test_schema_consistency(self):\n",
    "        original_schema = set(df['cademycode_students'].columns)\n",
    "        final_schema = set(final_df_cleaned.columns)\n",
    "        self.assertEqual(original_schema, final_schema, 'Final table schema differs from original')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44cf3995-0f43-48f4-9f70-79a1965f6d7b",
   "metadata": {},
   "source": [
    "Now let's configure logging to record updates and errors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "87c9ddb7-c9c8-4b1f-8acd-0b1946dbe703",
   "metadata": {},
   "outputs": [],
   "source": [
    "import logging\n",
    "\n",
    "logging.basicConfig(filename='logs/data_pipeline.log', level=logging.INFO, format='%(asctime)s:%(levelname)s:%(message)s')\n",
    "\n",
    "def log_update(message):\n",
    "    logging.info(message)\n",
    "\n",
    "def log_error(message):\n",
    "    logging.error(message)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef59ab82-8fc5-4749-be8a-6e89df1867ea",
   "metadata": {},
   "source": [
    "Finally let's add a changelog that includes details about each update, such as number of new rows and count of missing data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "1f594e51-cd6e-4889-a602-eeace367257c",
   "metadata": {},
   "outputs": [],
   "source": [
    "changelog_path = 'logs/changelog.txt'\n",
    "\n",
    "def write_changelog(version, new_rows_count, missing_data_count):\n",
    "    with open(changelog_path, 'a') as f:\n",
    "        f.write(f\"Version: {version}\\n\")\n",
    "        f.write(f\"New rows added: {new_rows_count}\\n\")\n",
    "        f.write(f\"Missing data count: {missing_data_count}\\n\")\n",
    "        f.write(\"\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9577d358-c267-499d-b355-6c220e3eb7dd",
   "metadata": {},
   "source": [
    "Now we can combine all of these components into a complete Python script."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "bad1665b-bf31-444f-99f7-0ffba1dbeafd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database connection established successfully.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "...\n",
      "----------------------------------------------------------------------\n",
      "Ran 3 tests in 0.005s\n",
      "\n",
      "OK\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "import json\n",
    "import unittest\n",
    "import logging\n",
    "import os\n",
    "\n",
    "logging.basicConfig(filename='logs/data_pipeline.log', level=logging.INFO, \n",
    "                    format='%(asctime)s:%(levelname)s:%(message)s')\n",
    "\n",
    "def log_update(message):\n",
    "    logging.info(message)\n",
    "\n",
    "def log_error(message):\n",
    "    logging.error(message)\n",
    "\n",
    "def extract_contact_info(contact_info):\n",
    "    try:\n",
    "        info = json.loads(contact_info.replace(\"'\", '\"'))\n",
    "        return pd.Series([info.get('mailing_address'), info.get('email')])\n",
    "    except json.JSONDecodeError:\n",
    "        return pd.Series([None, None])\n",
    "\n",
    "def is_database_updated():\n",
    "    global last_mod_time\n",
    "    current_mod_time = os.path.getmtime(db_path)\n",
    "    if last_mod_time is None or current_mod_time > last_mod_time:\n",
    "        last_mod_time = current_mod_time\n",
    "        return True\n",
    "    return False\n",
    "\n",
    "def write_changelog(version, new_rows_count, missing_data_count):\n",
    "    with open(changelog_path, 'a') as f:\n",
    "        f.write(f\"Version: {version}\\n\")\n",
    "        f.write(f\"New rows added: {new_rows_count}\\n\")\n",
    "        f.write(f\"Missing data count: {missing_data_count}\\n\")\n",
    "        f.write(\"\\n\")\n",
    "\n",
    "try:\n",
    "    db_path = 'dev/cademycode.db'\n",
    "    changelog_path = 'logs/changelog.txt'\n",
    "    last_mod_time = None\n",
    "\n",
    "    if is_database_updated():\n",
    "        log_update(\"Updated database. Running the pipeline...\")\n",
    "\n",
    "        con = sqlite3.connect(db_path)\n",
    "        print('Database connection established successfully.')\n",
    "\n",
    "        tables = pd.read_sql_query(\"SELECT name FROM sqlite_master WHERE type='table';\", con)\n",
    "        table_names = tables['name'].tolist()\n",
    "        df = {table: pd.read_sql_query(f\"SELECT * FROM {table}\", con) for table in table_names}\n",
    "\n",
    "        students_df = df['cademycode_students'].copy()\n",
    "        students_df['dob'] = pd.to_datetime(students_df['dob'], errors='coerce')\n",
    "        students_df['job_id'] = pd.to_numeric(students_df['job_id'], errors='coerce')\n",
    "        students_df['num_course_taken'] = pd.to_numeric(students_df['num_course_taken'], errors='coerce')\n",
    "        students_df['current_career_path_id'] = pd.to_numeric(students_df['current_career_path_id'], errors='coerce')\n",
    "        students_df['time_spent_hrs'] = pd.to_numeric(students_df['time_spent_hrs'], errors='coerce')\n",
    "        students_df.loc[:, 'job_id'] = students_df['job_id'].fillna(0)\n",
    "        students_df.loc[:, 'current_career_path_id'] = students_df['current_career_path_id'].fillna(0)\n",
    "        students_df.loc[:, 'num_course_taken'] = students_df['num_course_taken'].fillna(students_df['num_course_taken'].median())\n",
    "        students_df.loc[:, 'time_spent_hrs'] = students_df['time_spent_hrs'].fillna(students_df['time_spent_hrs'].median())\n",
    "        students_df[['mailing_address', 'email']] = students_df['contact_info'].apply(extract_contact_info)\n",
    "        students_df.drop(columns=['contact_info'], inplace=True)\n",
    "\n",
    "        jobs_df_cleaned = df['cademycode_student_jobs'].drop_duplicates()\n",
    "\n",
    "        merged_df_cleaned = pd.merge(students_df, jobs_df_cleaned, how='left', left_on='job_id', right_on='job_id')\n",
    "        final_df_cleaned = pd.merge(merged_df_cleaned, df['cademycode_courses'], how='left', left_on='current_career_path_id', right_on='career_path_id')\n",
    "\n",
    "        final_df_cleaned = final_df_cleaned.assign(\n",
    "            career_path_id=final_df_cleaned['career_path_id'].fillna(0),\n",
    "            career_path_name=final_df_cleaned['career_path_name'].fillna('Unknown'),\n",
    "            hours_to_complete=final_df_cleaned['hours_to_complete'].fillna(0)\n",
    "        )\n",
    "\n",
    "        clean_db_conn = sqlite3.connect('dev/clean_cademycode.db')\n",
    "        final_df_cleaned.to_sql('final_table', clean_db_conn, if_exists='replace', index=False)\n",
    "        final_df_cleaned.to_csv('dev/final_output.csv', index=False)\n",
    "\n",
    "        log_update(\"Pipeline executed successfully.\")\n",
    "\n",
    "        original_length = len(df['cademycode_students'])\n",
    "        final_length = len(final_df_cleaned)\n",
    "\n",
    "        class TestDataCleaning(unittest.TestCase):\n",
    "            def test_no_null_values(self):\n",
    "                self.assertFalse(final_df_cleaned.isnull().values.any(), \"There are null values in the final table\")\n",
    "\n",
    "            def test_correct_number_of_rows(self):\n",
    "                self.assertEqual(original_length, final_length, \"The number of rows differs after the merges\")\n",
    "\n",
    "            def test_schema_consistency(self):\n",
    "                original_schema = set(df['cademycode_students'].columns)\n",
    "                final_schema = set(final_df_cleaned.columns)\n",
    "                original_schema.discard('contact_info')\n",
    "                original_schema.update(['mailing_address', 'email'])\n",
    "                self.assertTrue(original_schema.issubset(final_schema), \"The final table schema does not include all original columns\")\n",
    "\n",
    "        if __name__ == '__main__':\n",
    "            unittest.main(argv=['first-arg-is-ignored'], exit=False)\n",
    "\n",
    "        new_rows_count = len(final_df_cleaned) - original_length\n",
    "        missing_data_count = final_df_cleaned.isnull().sum().sum()\n",
    "        write_changelog(\"1.0.0\", new_rows_count, missing_data_count)\n",
    "\n",
    "    else:\n",
    "        log_update(\"No updates to the database. Pipeline not executed.\")\n",
    "\n",
    "except Exception as e:\n",
    "    log_error(f\"Error running the pipeline: {e}\")\n",
    "    raise"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8cbe1139-8275-4672-8726-e3dcab4661fe",
   "metadata": {},
   "source": [
    "Now that all the tests are ready, let's build our bash script."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb585242-8780-4801-83df-41963058cda1",
   "metadata": {},
   "source": [
    "## Create a Bash Script\n",
    "\n",
    "Create a bash script to handle running the Python script and moving updated files from your working directory in /dev to a production directory. Your bash script should use the logs from the last task to determine if an update occurred.\n",
    "\n",
    "- You can execute python files within bash scripts by calling `python path/to/source/file.py`.\n",
    "- You can either chose to move the file over to the production folder using `mv /path/to/source /path/to/destination` or copy the files to the production folder by using `cp /path/to/source /path/to/destination`\n",
    "- Use version numbers in your changelog to check for updates"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0794bfa-847e-4972-8e53-c4d64606b232",
   "metadata": {},
   "source": [
    "### Step by Step to Create the Bash Script\n",
    "\n",
    "**Step 1: Check the Changelog for Updates**\n",
    "\n",
    "- Check the current version in the changelog before running the Python script.\n",
    "- After running, check the changelog version again to determine if there was an update.\n",
    "\n",
    "**Step 2: Run the Python Script**\n",
    "\n",
    "- Run the Python script that performs the data pipeline.\n",
    "\n",
    "**Step 3: Move Files to the Production Folder**\n",
    "\n",
    "- If an update is detected (based on the changelog version), move the updated files to the production folder.\n",
    "\n",
    "Here's the content for the Script Bash (`run_pipeline.sh`)\n",
    "\n",
    "```bash\n",
    "#!/bin/bash\n",
    "\n",
    "# Path to Python Script\n",
    "PYTHON_SCRIPT=\"path/to/your/script.py\"\n",
    "\n",
    "# Path to production directory\n",
    "PROD_DIR=\"path/to/production\"\n",
    "\n",
    "# Path to changelog\n",
    "CHANGELOG=\"logs/changelog.txt\"\n",
    "\n",
    "# Current version of changelog\n",
    "CURRENT_VERSION=$(grep -oP 'Version: \\K.*' $CHANGELOG | tail -1)\n",
    "\n",
    "# Run Python script\n",
    "python3 $PYTHON_SCRIPT\n",
    "\n",
    "# Check pipeline connection\n",
    "if [ $? -eq 0 ]; then\n",
    "    echo \"Pipeline executed successfully.\"\n",
    "\n",
    "    # New version for changelog\n",
    "    NEW_VERSION=$(grep -oP 'Version: \\K.*' $CHANGELOG | tail -1)\n",
    "\n",
    "    # Check for updates on changelog\n",
    "    if [ \"$CURRENT_VERSION\" != \"$NEW_VERSION\" ]; then\n",
    "        echo \"Update detected. Moving files to production.\"\n",
    "\n",
    "        # Move updated files to production\n",
    "        mv final_output.csv $PROD_DIR/\n",
    "        mv dev/clean_cademycode.db $PROD_DIR/\n",
    "\n",
    "        echo \"Files moved to production.\"\n",
    "    else\n",
    "        echo \"No updates detected. No files moved to production.\"\n",
    "    fi\n",
    "else\n",
    "    echo \"Pipeline execution failed. Check logs for details.\"\n",
    "fi\n",
    "```\n",
    "\n",
    "After creating the Bash script, we are going to make it executable using the `chmod` command.\n",
    "\n",
    "```bash\n",
    "chmod +x run_pipeline.sh\n",
    "```\n",
    "\n",
    "Let's run the Bash script to ensure it works correctly:\n",
    "\n",
    "```bash\n",
    "./run_pipeline.sh\n",
    "```\n",
    "\n",
    "The `run_pipeline.sh` file is in the root of this project. Now that we are done, we can finish writing the Readme and saving the project."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0de781cb-12e9-45ac-9010-789b5062626f",
   "metadata": {},
   "source": [
    "## Wrap-Up\n",
    "\n",
    "### Summary of Tasks\n",
    "\n",
    "Throughout this project, we aimed to build a data engineering pipeline to transform a messy database of long-term canceled subscribers into a clean, analytics-ready dataset. Here's a summary of the key tasks we accomplished:\n",
    "\n",
    "1. **Set Up**: We began by setting up our working directory and ensuring all necessary files and tools were in place.\n",
    "   \n",
    "2. **Inspect and Clean the Data**: We imported the tables from `cademycode.db` into dataframes, inspected them for missing or invalid data, and performed various data cleaning operations. This included handling null values and ensuring data types were correct.\n",
    "\n",
    "3. **Create the Output CSV**: Using the cleaned data, we produced an analytics-ready SQLite database and a flat CSV file. We validated the final table to ensure no data was lost or duplicated during the joins.\n",
    "\n",
    "4. **Develop Unit Tests and Logs**: We converted our Jupyter Notebook into a Python script. The script includes unit tests to check for updates to the database and to protect the update process. It also includes logging to track updates and errors.\n",
    "\n",
    "5. **Create a Bash Script**: We created a Bash script to handle running the Python script and moving updated files from the working directory to a production directory. The script checks the changelog to determine if an update occurred before moving the files.\n",
    "\n",
    "6. **Create a Readme**: We documented the entire update process in a `readme.md` file. This file includes a description of the project structure, instructions on how to run the update process, and details about the version control system and error logging.\n",
    "\n",
    "### Conclusion\n",
    "\n",
    "In conclusion, this project successfully demonstrates how to build a robust data engineering pipeline that automates the transformation of raw data into a clean and usable format. By following a structured approach, we ensured that the pipeline is reliable, maintainable, and easy to understand. The inclusion of unit tests and logging provides additional safeguards and transparency, making it easier to monitor and debug the process.\n",
    "\n",
    "This project not only serves as a valuable addition to our portfolio but also equips us with practical experience in handling real-world data engineering challenges. The skills and methodologies applied here are transferable to a wide range of data engineering tasks, ensuring we are well-prepared for future projects and roles in the field.\n",
    "\n",
    "Thank you for following along with this project. Should you have any questions or require further assistance, please refer to the documentation or reach out to the project maintainer."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}