subscriber-pipeline/writeup/data_eng_cp_writeup.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "1e6ba182",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "import ast\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eee9e8fa",
   "metadata": {},
   "source": [
    "This file will demostrate the discovery phase/process used before creating the `cleanse_data.py` file which automates the cleaning and update processes."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a3130a4",
   "metadata": {},
   "source": [
    "# Connecting SQLite3 Database\n",
    "\n",
    "First I'll connect to the database to determine which tables are contained in the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b538ddde",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('cademycode_students',), ('cademycode_courses',), ('cademycode_student_jobs',)]\n"
     ]
    }
   ],
   "source": [
    "con = sqlite3.connect('cademycode.db')\n",
    "cur = con.cursor()\n",
    "\n",
    "# read all table names\n",
    "table_list = [a for a in cur.execute(\"SELECT name FROM sqlite_master WHERE type = 'table'\")]\n",
    "print(table_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e07af25",
   "metadata": {},
   "source": [
    "## Extract tables from db file\n",
    "\n",
    "I'll extract the tables using simple query statements and save them to python variables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ab55b933",
   "metadata": {},
   "outputs": [],
   "source": [
    "students = pd.read_sql_query(\"SELECT * FROM cademycode_students\", con)\n",
    "career_paths = pd.read_sql_query(\"SELECT * FROM cademycode_courses\", con)\n",
    "student_jobs = pd.read_sql_query(\"SELECT * FROM cademycode_student_jobs\", con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "af833d27",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "students: 5000\n",
      "career_paths: 10\n",
      "student_jobs: 13\n"
     ]
    }
   ],
   "source": [
    "print('students:', len(students))\n",
    "print('career_paths:', len(career_paths))\n",
    "print('student_jobs:', len(student_jobs))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0efe7abb",
   "metadata": {},
   "source": [
    "# Working with the `Students` table\n",
    "\n",
    "Let's work with the students file first since it is the largest. I'll start by taking a look at the first few rows of data to get a general sense of what the structure looks like."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4780a9b2",
   "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>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  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "students.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d92482c",
   "metadata": {},
   "source": [
    "Next I'll examine the datatypes of each column and check for any null values using the `.info()` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3fdb91f3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<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"
     ]
    }
   ],
   "source": [
    "students.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c205dbad",
   "metadata": {},
   "source": [
    "A couple things I notice when examining the DataFrame so far:\n",
    "- contact_info appears to be a dictionary, which will require additional work to explode it into seperate columns\n",
    "- each row has a UUID, which means one student can only be one row\n",
    "- `dob` seems to be date of birth, will want to make sure it is a datetime object\n",
    "  - calculating age from DoB might be useful as a grouping column\n",
    "- none of the numerical columns are coming in as floats or integers\n",
    "  - `job_id` and `current_career_path_id` should be treated as categorical data since they are IDs\n",
    "- looks like there is missing data in `job_id`, `current_career_path_id`, and `current_career_path_id`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db618d8f",
   "metadata": {},
   "source": [
    "## Calculating Approximate Age\n",
    "\n",
    "I'll convert `dob` into approximate age. This provides a couple benefits over a datetime field:\n",
    "- To an analytics team who's trying to tell a story with numbers, age makes more aesthetic sense. \n",
    "- This creates a new ordinal or even categorical field for analysts to dive into."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f5790e39",
   "metadata": {},
   "outputs": [],
   "source": [
    "now = pd.to_datetime('now')\n",
    "students['age'] = (now - pd.to_datetime(students['dob'])).astype('<m8[Y]')\n",
    "students['age_group'] = np.int64((students['age']/10))*10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "bde0867c",
   "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>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",
       "      <th>age</th>\n",
       "      <th>age_group</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",
       "      <td>79.0</td>\n",
       "      <td>70</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",
       "      <td>31.0</td>\n",
       "      <td>30</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",
       "      <td>32.0</td>\n",
       "      <td>30</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",
       "      <td>33.0</td>\n",
       "      <td>30</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",
       "      <td>58.0</td>\n",
       "      <td>50</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   age  age_group  \n",
       "0                    1.0           4.99  79.0         70  \n",
       "1                    8.0            4.4  31.0         30  \n",
       "2                    8.0           6.74  32.0         30  \n",
       "3                    9.0          12.31  33.0         30  \n",
       "4                    3.0           5.64  58.0         50  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "students.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0039298b",
   "metadata": {},
   "source": [
    "## Explode the dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "aa6e32f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#explode_contact = pd.json_normalize(students['contact_info'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1d7fb50",
   "metadata": {},
   "source": [
    "Interestingly enough, Pandas imported the `contact_info` column as a string type rather than as JSON. This adds an extra step and requires me to convert the strings to dictionaries, and then explode:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "b084239b",
   "metadata": {},
   "outputs": [],
   "source": [
    "students['contact_info'] = students[\"contact_info\"].apply(lambda x: ast.literal_eval(x))\n",
    "explode_contact = pd.json_normalize(students['contact_info'])\n",
    "students = pd.concat([students.drop('contact_info', axis=1), explode_contact], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "52eccd9f",
   "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>age</th>\n",
       "      <th>age_group</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>79.0</td>\n",
       "      <td>70</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.4</td>\n",
       "      <td>31.0</td>\n",
       "      <td>30</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>32.0</td>\n",
       "      <td>30</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>33.0</td>\n",
       "      <td>30</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>58.0</td>\n",
       "      <td>50</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   age  age_group  \\\n",
       "0                    1.0           4.99  79.0         70   \n",
       "1                    8.0            4.4  31.0         30   \n",
       "2                    8.0           6.74  32.0         30   \n",
       "3                    9.0          12.31  33.0         30   \n",
       "4                    3.0           5.64  58.0         50   \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  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "students.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0015208",
   "metadata": {},
   "source": [
    "After exploding the dictionary column, it could be worth spliting the `mailing_address` column into `street`, `city`, `state`, `zip_code` columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "9d86c66f",
   "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>age</th>\n",
       "      <th>age_group</th>\n",
       "      <th>email</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip_code</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>79.0</td>\n",
       "      <td>70</td>\n",
       "      <td>annabelle_avery9376@woohoo.com</td>\n",
       "      <td>303 N Timber Key</td>\n",
       "      <td>Irondale</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>84736</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.4</td>\n",
       "      <td>31.0</td>\n",
       "      <td>30</td>\n",
       "      <td>rubio6772@hmail.com</td>\n",
       "      <td>767 Crescent Fair</td>\n",
       "      <td>Shoals</td>\n",
       "      <td>Indiana</td>\n",
       "      <td>37439</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>32.0</td>\n",
       "      <td>30</td>\n",
       "      <td>hosea_dale8084@coldmail.com</td>\n",
       "      <td>P.O. Box 41269</td>\n",
       "      <td>St. Bonaventure</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>83637</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>33.0</td>\n",
       "      <td>30</td>\n",
       "      <td>kirk4005@hmail.com</td>\n",
       "      <td>517 SE Wintergreen Isle</td>\n",
       "      <td>Lane</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>82242</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>58.0</td>\n",
       "      <td>50</td>\n",
       "      <td>alexander9810@hmail.com</td>\n",
       "      <td>18 Cinder Cliff</td>\n",
       "      <td>Doyles borough</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>73737</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   age  age_group  \\\n",
       "0                    1.0           4.99  79.0         70   \n",
       "1                    8.0            4.4  31.0         30   \n",
       "2                    8.0           6.74  32.0         30   \n",
       "3                    9.0          12.31  33.0         30   \n",
       "4                    3.0           5.64  58.0         50   \n",
       "\n",
       "                            email                   street              city  \\\n",
       "0  annabelle_avery9376@woohoo.com         303 N Timber Key          Irondale   \n",
       "1             rubio6772@hmail.com        767 Crescent Fair            Shoals   \n",
       "2     hosea_dale8084@coldmail.com           P.O. Box 41269   St. Bonaventure   \n",
       "3              kirk4005@hmail.com  517 SE Wintergreen Isle              Lane   \n",
       "4         alexander9810@hmail.com          18 Cinder Cliff    Doyles borough   \n",
       "\n",
       "           state zip_code  \n",
       "0      Wisconsin    84736  \n",
       "1        Indiana    37439  \n",
       "2       Virginia    83637  \n",
       "3       Arkansas    82242  \n",
       "4   Rhode Island    73737  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "split_address = students.mailing_address.str.split(',', expand=True)\n",
    "split_address.columns = ['street', 'city', 'state', 'zip_code']\n",
    "students = pd.concat([students.drop('mailing_address', axis=1), split_address], axis=1)\n",
    "students.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "29fd11de",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5000 entries, 0 to 4999\n",
      "Data columns (total 15 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   job_id                  4995 non-null   object \n",
      " 5   num_course_taken        4749 non-null   object \n",
      " 6   current_career_path_id  4529 non-null   object \n",
      " 7   time_spent_hrs          4529 non-null   object \n",
      " 8   age                     5000 non-null   float64\n",
      " 9   age_group               5000 non-null   int64  \n",
      " 10  email                   5000 non-null   object \n",
      " 11  street                  5000 non-null   object \n",
      " 12  city                    5000 non-null   object \n",
      " 13  state                   5000 non-null   object \n",
      " 14  zip_code                5000 non-null   object \n",
      "dtypes: float64(1), int64(2), object(12)\n",
      "memory usage: 586.1+ KB\n"
     ]
    }
   ],
   "source": [
    "students.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c02205b",
   "metadata": {},
   "source": [
    "## Fixing column datatypes\n",
    "Lets fix the column datatypes first. I know that `num_course_taken` should be integers and `time_spent_hrs` should be floats. Even though `job_id` and `current_career_path_id` look numerical, they should be treated as categorical data since they are IDs. Due to null values, I can't cast `job_id` and `current_career_path_id` into integers, so I'll cast them as floats for now."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2759d73e",
   "metadata": {},
   "outputs": [],
   "source": [
    "students['job_id'] = students['job_id'].astype(float)\n",
    "students['current_career_path_id'] = students['current_career_path_id'].astype(float)\n",
    "students['num_course_taken'] = students['num_course_taken'].astype(float)\n",
    "students['time_spent_hrs'] = students['time_spent_hrs'].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "519e3cdb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5000 entries, 0 to 4999\n",
      "Data columns (total 15 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   job_id                  4995 non-null   float64\n",
      " 5   num_course_taken        4749 non-null   float64\n",
      " 6   current_career_path_id  4529 non-null   float64\n",
      " 7   time_spent_hrs          4529 non-null   float64\n",
      " 8   age                     5000 non-null   float64\n",
      " 9   age_group               5000 non-null   int64  \n",
      " 10  email                   5000 non-null   object \n",
      " 11  street                  5000 non-null   object \n",
      " 12  city                    5000 non-null   object \n",
      " 13  state                   5000 non-null   object \n",
      " 14  zip_code                5000 non-null   object \n",
      "dtypes: float64(5), int64(2), object(8)\n",
      "memory usage: 586.1+ KB\n"
     ]
    }
   ],
   "source": [
    "students.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb0d7042",
   "metadata": {},
   "source": [
    "## Handling Missing Data\n",
    "\n",
    "### `num_course_taken`\n",
    "Let's examine the missing data to determine if the missing data is missing at random, structurally missing, or missing not at random:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ed8300e4",
   "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>age</th>\n",
       "      <th>age_group</th>\n",
       "      <th>email</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>Doug Browning</td>\n",
       "      <td>1970-06-08</td>\n",
       "      <td>M</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.92</td>\n",
       "      <td>52.0</td>\n",
       "      <td>50</td>\n",
       "      <td>doug7761@inlook.com</td>\n",
       "      <td>P.O. Box 15845</td>\n",
       "      <td>Devine</td>\n",
       "      <td>Florida</td>\n",
       "      <td>23097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>27</td>\n",
       "      <td>Damon Schrauwen</td>\n",
       "      <td>1953-10-31</td>\n",
       "      <td>M</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>3.73</td>\n",
       "      <td>68.0</td>\n",
       "      <td>60</td>\n",
       "      <td>damon9864@woohoo.com</td>\n",
       "      <td>P.O. Box 84659</td>\n",
       "      <td>Maben</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>66137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>52</td>\n",
       "      <td>Alisa Neil</td>\n",
       "      <td>1977-05-28</td>\n",
       "      <td>F</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>22.86</td>\n",
       "      <td>45.0</td>\n",
       "      <td>40</td>\n",
       "      <td>alisa9616@inlook.com</td>\n",
       "      <td>16 View Annex</td>\n",
       "      <td>Mosses</td>\n",
       "      <td>North Dakota</td>\n",
       "      <td>25748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>71</td>\n",
       "      <td>Chauncey Hooper</td>\n",
       "      <td>1962-04-07</td>\n",
       "      <td>M</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.97</td>\n",
       "      <td>60.0</td>\n",
       "      <td>60</td>\n",
       "      <td>chauncey6352@woohoo.com</td>\n",
       "      <td>955 Dewy Flat</td>\n",
       "      <td>Slaughterville</td>\n",
       "      <td>South Carolina</td>\n",
       "      <td>22167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>81</td>\n",
       "      <td>Ellyn van Heest</td>\n",
       "      <td>1984-06-28</td>\n",
       "      <td>F</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.39</td>\n",
       "      <td>38.0</td>\n",
       "      <td>30</td>\n",
       "      <td>ellyn_vanheest8375@hmail.com</td>\n",
       "      <td>872 Cider Glade</td>\n",
       "      <td>Chicken</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>42689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4889</th>\n",
       "      <td>4890</td>\n",
       "      <td>Tegan Cochran</td>\n",
       "      <td>1970-11-08</td>\n",
       "      <td>F</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>22.75</td>\n",
       "      <td>51.0</td>\n",
       "      <td>50</td>\n",
       "      <td>tegan130@inlook.com</td>\n",
       "      <td>106 Sunny Nook</td>\n",
       "      <td>Vernal</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>10769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4898</th>\n",
       "      <td>4899</td>\n",
       "      <td>Ruthann Oliver</td>\n",
       "      <td>1998-05-22</td>\n",
       "      <td>F</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>21.27</td>\n",
       "      <td>24.0</td>\n",
       "      <td>20</td>\n",
       "      <td>ruthann1124@woohoo.com</td>\n",
       "      <td>644 Merry Island</td>\n",
       "      <td>Green Valley</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>91273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4914</th>\n",
       "      <td>4915</td>\n",
       "      <td>Ernest Holmes</td>\n",
       "      <td>1995-03-11</td>\n",
       "      <td>M</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>26.50</td>\n",
       "      <td>27.0</td>\n",
       "      <td>20</td>\n",
       "      <td>ernest_holmes505@hmail.com</td>\n",
       "      <td>872 Wintergreen Harbor</td>\n",
       "      <td>Gallitzin borough</td>\n",
       "      <td>Maine</td>\n",
       "      <td>50103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4980</th>\n",
       "      <td>4981</td>\n",
       "      <td>Brice Franklin</td>\n",
       "      <td>1946-12-01</td>\n",
       "      <td>M</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>8.66</td>\n",
       "      <td>75.0</td>\n",
       "      <td>70</td>\n",
       "      <td>brice9741@coldmail.com</td>\n",
       "      <td>947 Panda Way</td>\n",
       "      <td>New Bedford village</td>\n",
       "      <td>Vermont</td>\n",
       "      <td>31232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4985</th>\n",
       "      <td>4986</td>\n",
       "      <td>Russel Vonck</td>\n",
       "      <td>1994-09-07</td>\n",
       "      <td>M</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>16.44</td>\n",
       "      <td>27.0</td>\n",
       "      <td>20</td>\n",
       "      <td>russel_vonck2834@woohoo.com</td>\n",
       "      <td>815 Middle Timber Corner</td>\n",
       "      <td>Clawson</td>\n",
       "      <td>South Dakota</td>\n",
       "      <td>02949</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>251 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      uuid             name         dob sex  job_id  num_course_taken  \\\n",
       "25      26    Doug Browning  1970-06-08   M     7.0               NaN   \n",
       "26      27  Damon Schrauwen  1953-10-31   M     4.0               NaN   \n",
       "51      52       Alisa Neil  1977-05-28   F     5.0               NaN   \n",
       "70      71  Chauncey Hooper  1962-04-07   M     3.0               NaN   \n",
       "80      81  Ellyn van Heest  1984-06-28   F     3.0               NaN   \n",
       "...    ...              ...         ...  ..     ...               ...   \n",
       "4889  4890    Tegan Cochran  1970-11-08   F     5.0               NaN   \n",
       "4898  4899   Ruthann Oliver  1998-05-22   F     3.0               NaN   \n",
       "4914  4915    Ernest Holmes  1995-03-11   M     7.0               NaN   \n",
       "4980  4981   Brice Franklin  1946-12-01   M     4.0               NaN   \n",
       "4985  4986     Russel Vonck  1994-09-07   M     5.0               NaN   \n",
       "\n",
       "      current_career_path_id  time_spent_hrs   age  age_group  \\\n",
       "25                       5.0            1.92  52.0         50   \n",
       "26                      10.0            3.73  68.0         60   \n",
       "51                       8.0           22.86  45.0         40   \n",
       "70                       3.0            3.97  60.0         60   \n",
       "80                      10.0           12.39  38.0         30   \n",
       "...                      ...             ...   ...        ...   \n",
       "4889                     8.0           22.75  51.0         50   \n",
       "4898                     7.0           21.27  24.0         20   \n",
       "4914                     9.0           26.50  27.0         20   \n",
       "4980                     5.0            8.66  75.0         70   \n",
       "4985                     5.0           16.44  27.0         20   \n",
       "\n",
       "                             email                    street  \\\n",
       "25             doug7761@inlook.com            P.O. Box 15845   \n",
       "26            damon9864@woohoo.com            P.O. Box 84659   \n",
       "51            alisa9616@inlook.com             16 View Annex   \n",
       "70         chauncey6352@woohoo.com             955 Dewy Flat   \n",
       "80    ellyn_vanheest8375@hmail.com           872 Cider Glade   \n",
       "...                            ...                       ...   \n",
       "4889           tegan130@inlook.com            106 Sunny Nook   \n",
       "4898        ruthann1124@woohoo.com          644 Merry Island   \n",
       "4914    ernest_holmes505@hmail.com    872 Wintergreen Harbor   \n",
       "4980        brice9741@coldmail.com             947 Panda Way   \n",
       "4985   russel_vonck2834@woohoo.com  815 Middle Timber Corner   \n",
       "\n",
       "                      city            state zip_code  \n",
       "25                  Devine          Florida    23097  \n",
       "26                   Maben          Georgia    66137  \n",
       "51                  Mosses     North Dakota    25748  \n",
       "70          Slaughterville   South Carolina    22167  \n",
       "80                 Chicken         Delaware    42689  \n",
       "...                    ...              ...      ...  \n",
       "4889                Vernal          Georgia    10769  \n",
       "4898          Green Valley          Wyoming    91273  \n",
       "4914     Gallitzin borough            Maine    50103  \n",
       "4980   New Bedford village          Vermont    31232  \n",
       "4985               Clawson     South Dakota    02949  \n",
       "\n",
       "[251 rows x 15 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "missing_course_taken = students[students[['num_course_taken']].isnull().any(axis=1)]\n",
    "display(missing_course_taken)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aaed3ab6",
   "metadata": {},
   "source": [
    "Let's take a look at the distribution of missing and complete data grouped by the categorical data. If the distribution between the missing and non-missing data is similar, we can conclude that the data is MAR if no identifiable pattern is found. Generally, if something is MNAR or structurally missing, it should be visible by graphing the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "b3d0a95a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:xlabel='sex'>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXQAAAEFCAYAAADzHRw3AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjQuMywgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/MnkTPAAAACXBIWXMAAAsTAAALEwEAmpwYAAAS30lEQVR4nO3df4zX9X3A8efLQwqT81aFdCpa6OpE8RDwoC01/CjxR9cwbCWhSjWUdYa2YGH9MROXrV3TbMauTqobYQtlZrRgqFvJZDFzlFqFrYBQ5LQQ4ijecNkNrWAnK6ev/XEnO8477ntwd1/vfc9HQnLf7+d9388rd8mTDx++n883MhNJ0sB3TrUHkCT1DoMuSYUw6JJUCIMuSYUw6JJUCIMuSYUYUq0djxw5MseMGVOt3UvSgLRz587/zsxRnW2rWtDHjBnDjh07qrV7SRqQIuLnXW3zlIskFcKgS1IhDLokFaJq59AlDTwnTpygqamJ48ePV3uU4g0bNozRo0dz7rnnVvw9Bl1SxZqamqitrWXMmDFERLXHKVZmcuTIEZqamhg7dmzF3+cpF0kVO378OBdeeKEx72MRwYUXXtjjfwkZdEk9Ysz7x5n8nA26JHVh4cKFbNiw4bRr1qxZw+HDh/tpotPzHHpf+WpdP+/v1f7dnwSMufuxXn29g3/2sV59vf6wZs0arr76ai6++OJqj+IRuqSB5+GHH2bChAlcc8013H777fz85z9n9uzZTJgwgdmzZ3Po0CGg9Qj7s5/9LLNmzeJ973sfP/rRj1i0aBFXXnklCxcuPPl6I0aM4Itf/CKTJ09m9uzZNDc3v22fO3fuZMaMGVx77bXceOONvPTSS2zYsIEdO3awYMECJk6cyOuvv97puv5i0CUNKI2NjXzjG99g8+bN/PSnP+WBBx5gyZIl3HHHHezZs4cFCxZw1113nVz/yiuvsHnzZu6//37mzJnD8uXLaWxs5Nlnn2X37t0A/PKXv2Ty5Mk888wzzJgxg6997Wun7PPEiRMsXbqUDRs2sHPnThYtWsQ999zDvHnzaGhoYO3atezevZshQ4Z0uq6/eMpF0oCyefNm5s2bx8iRIwG44IIL2LZtG48++igAt99+O1/5yldOrp8zZw4RQX19Pe95z3uor68HYPz48Rw8eJCJEydyzjnnMH/+fAA+9alP8YlPfOKUfe7bt4+9e/dy/fXXA/DGG29w0UUXvW22Stf1FYMuaUDJzG7fAdJ++7ve9S4AzjnnnJNfv/W4paWl2+9/a5/jx49n27Zt3c5Wybq+4ikXSQPK7NmzeeSRRzhy5AgAL7/8MtOmTWPdunUArF27luuuu65Hr/nmm2+efDfLd7/73bd9/xVXXEFzc/PJUJ84cYLGxkYAamtrOXbsWLfr+oNH6JIGlPHjx3PPPfcwY8YMampqmDRpEitWrGDRokXcd999jBo1iu985zs9es3zzjuPxsZGrr32Wurq6li/fv0p24cOHcqGDRu46667ePXVV2lpaWHZsmWMHz+ehQsXsnjxYoYPH862bdu6XNcfIjP7ZUcdNTQ0ZNH3Q/dtiyrQ888/z5VXXlntMXrdiBEjeO2116o9xtt09vOOiJ2Z2dDZek+5SFIhDLqkQe+deHR+Jgy6JBXCoEtSIQy6JBXCoEtSIQy6pAFl2rRp1R7hFGvWrGHJkiWnXbNlyxa2bt3a57N4YZGkM9fb11tUcD1Ff4Sxt23ZsoURI0b0+V9GFR2hR8RNEbEvIg5ExN2nWTclIt6IiHm9N6Ik/b8RI0YArZGcOXMm8+bNY9y4cSxYsIC3LpTcvn0706ZN45prrmHq1KkcO3aM48eP8+lPf5r6+nomTZrED3/4Q6D1CPvmm29mzpw5jB07lgcffJBvfetbTJo0iQ9+8IO8/PLLAMycOZNly5Yxbdo0rr76an7yk5+8bbbm5mZuueUWpkyZwpQpU3j66ac5ePAgK1eu5P7772fixIn8+Mc/7nRdb+j2CD0iaoCHgOuBJmB7RGzMzOc6WXcv8HivTCZJ3di1axeNjY1cfPHFfPjDH+bpp59m6tSpzJ8/n/Xr1zNlyhSOHj3K8OHDeeCBBwB49tln+dnPfsYNN9zA/v37Adi7dy+7du3i+PHjvP/97+fee+9l165dLF++nIcffphly5YBrbfZ3bp1K08++SSLFi1i7969p8zzhS98geXLl3Pddddx6NAhbrzxRp5//nkWL17MiBEj+NKXvgTAbbfd1um6s1XJKZepwIHMfAEgItYBc4HnOqxbCnwfmHLWU0lSBaZOncro0aMBmDhxIgcPHqSuro6LLrqIKVNaU3T++ecD8NRTT7F06VIAxo0bx3vf+96TQZ81axa1tbXU1tZSV1fHnDlzAKivr2fPnj0n93frrbcCMH36dI4ePcovfvGLU+Z54okneO65/0/j0aNHT964q5J1tbW1Z/XzqCTolwAvtnvcBHyg/YKIuAT4OPARDLqkftL+drg1NTW0tLR0eXvd0923quNtddvfcrf9LXY7vm7Hx2+++Sbbtm1j+PDhp5270nU9VUnQO7vxcMefzF8Af5CZb5zuPsURcSdwJ8Bll11W4YhS7392ZXcG4mdbqtW4ceM4fPgw27dvZ8qUKRw7dozhw4czffp01q5dy0c+8hH279/PoUOHuOKKK3jmmWcqfu3169cza9YsnnrqKerq6qirO/U/hW+44QYefPBBvvzlLwOwe/duJk6cSG1tLUePHu123dmqJOhNwKXtHo8GOn7EdQOwri3mI4HfjoiWzPyH9osycxWwClrvtniGM5+Rfg/CsH7dnaQ2Q4cOZf369SxdupTXX3+d4cOH88QTT/C5z32OxYsXU19fz5AhQ1izZs0pR+aVePe73820adM4evQoq1evftv2FStW8PnPf54JEybQ0tLC9OnTWblyJXPmzGHevHn84Ac/4Nvf/naX685Wt7fPjYghwH5gNvAfwHbgtszs9K7tEbEG+MfM3HC61+3v2+f2f9Bv69f9lX77XI/Q3xlKvX1uJWbOnMk3v/lNGho6vXNtn+jp7XO7PULPzJaIWELru1dqgNWZ2RgRi9u2n/1fK5Kks1bRhUWZuQnY1OG5TkOemQvPfixJemfZsmVLtUfolpf+S1IhDLqkHqnWx1YONmfyczbokio2bNgwjhw5YtT7WGZy5MgRhg3r2dvlvDmXpIqNHj2apqYmmpubqz1K8YYNG3byKthKGXRJFTv33HMZO3ZstcdQFzzlIkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVIgh1R5Aekf6al0/7+/V/t2fiuQRuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEqCnpE3BQR+yLiQETc3cn2uRGxJyJ2R8SOiLiu90eVJJ1Ot1eKRkQN8BBwPdAEbI+IjZn5XLtl/wJszMyMiAnAI8C4vhhYktS5So7QpwIHMvOFzPwVsA6Y235BZr6Wmdn28DwgkST1q0qCfgnwYrvHTW3PnSIiPh4RPwMeAxb1zniSpEpVEvTo5Lm3HYFn5t9n5jjgZuDrnb5QxJ1t59h3NDc392hQSdLpVRL0JuDSdo9HA4e7WpyZTwK/GREjO9m2KjMbMrNh1KhRPR5WktS1SoK+Hbg8IsZGxFDgk8DG9gsi4v0REW1fTwaGAkd6e1hJUte6fZdLZrZExBLgcaAGWJ2ZjRGxuG37SuAW4I6IOAG8Dsxv95+kkqR+UNEHXGTmJmBTh+dWtvv6XuDe3h1NktQTXikqSYUw6JJUCIMuSYUw6JJUCIMuSYUw6JJUCIMuSYUw6JJUCIMuSYUw6JJUCIMuSYUw6JJUCIMuSYUw6JJUCIMuSYUw6JJUCIMuSYUw6JJUCIMuSYUw6JJUCIMuSYUw6JJUCIMuSYUw6JJUCIMuSYUw6JJUCIMuSYUYUu0BJKlXfbWun/f3av/u7zQ8QpekQhh0SSqEQZekQhh0SSqEQZekQhh0SSqEQZekQhh0SSqEQZekQhh0SSpERUGPiJsiYl9EHIiIuzvZviAi9rT92RoR1/T+qJKk0+k26BFRAzwEfBS4Crg1Iq7qsOzfgRmZOQH4OrCqtweVJJ1eJTfnmgocyMwXACJiHTAXeO6tBZm5td36fwVG9+aQkgauMXc/1q/7OzisX3f3jlLJKZdLgBfbPW5qe64rvwv809kMJUnquUqO0KOT57LThRGzaA36dV1svxO4E+Cyyy6rcERJUiUqOUJvAi5t93g0cLjjooiYAPwNMDczj3T2Qpm5KjMbMrNh1KhRZzKvJKkLlQR9O3B5RIyNiKHAJ4GN7RdExGXAo8Dtmbm/98eUJHWn21MumdkSEUuAx4EaYHVmNkbE4rbtK4E/Ai4E/jIiAFoys6HvxpYkdVTRR9Bl5iZgU4fnVrb7+jPAZ3p3NElST3ilqCQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEMuiQVwqBLUiEqCnpE3BQR+yLiQETc3cn2cRGxLSL+NyK+1PtjSpK6M6S7BRFRAzwEXA80AdsjYmNmPtdu2cvAXcDNfTGkJKl7lRyhTwUOZOYLmfkrYB0wt/2CzPyvzNwOnOiDGSVJFagk6JcAL7Z73NT2XI9FxJ0RsSMidjQ3N5/JS0iSulBJ0KOT5/JMdpaZqzKzITMbRo0adSYvIUnqQiVBbwIubfd4NHC4b8aRJJ2pSoK+Hbg8IsZGxFDgk8DGvh1LktRT3b7LJTNbImIJ8DhQA6zOzMaIWNy2fWVE/AawAzgfeDMilgFXZebRvhtdktRet0EHyMxNwKYOz61s9/V/0noqRpJUJV4pKkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVAiDLkmFMOiSVIiKgh4RN0XEvog4EBF3d7I9ImJF2/Y9ETG590eVJJ1Ot0GPiBrgIeCjwFXArRFxVYdlHwUub/tzJ/BXvTynJKkblRyhTwUOZOYLmfkrYB0wt8OaucDD2epfgV+PiIt6eVZJ0mkMqWDNJcCL7R43AR+oYM0lwEvtF0XEnbQewQO8FhH7ejTtABIwEvjvftvh16LfdjUY+PsbuAbB7+69XW2oJOidTZtnsIbMXAWsqmCfA15E7MjMhmrPoTPj72/gGsy/u0pOuTQBl7Z7PBo4fAZrJEl9qJKgbwcuj4ixETEU+CSwscOajcAdbe92+SDwama+1PGFJEl9p9tTLpnZEhFLgMeBGmB1ZjZGxOK27SuBTcBvAweA/wE+3XcjDxiD4tRSwfz9DVyD9ncXmW871S1JGoC8UlSSCmHQJakQBl2SCmHQe0FEXFbtGSTJ/xTtBRHxTGZObvv6+5l5S7VnUuUiouPbcE+Rmb/TX7OoZyLij06zOTPz6/02zDtAJVeKqnvtr5R9X9Wm0Jn6EK23rvge8G90fuWz3pl+2clzvwZ8BrgQMOjqseziaw0MvwFcD9wK3AY8BnwvMxurOpW6lZl//tbXEVELfAFYROtNBP+8q+8rladcekFEvEHrkUIAw2m9uIq2x5mZ51drNvVMRLyL1rDfB/xJZn67yiOpGxFxAfD7wALgb4EHMvOV6k5VHR6h94LMrKn2DDo7bSH/GK0xHwOsAB6t5kzqXkTcB3yC1qtD6zPztSqPVFUeoWvQi4i/Ba4G/glYl5l7qzySKhQRbwL/C7Rw6unOQfmvY4OuQa8tCm/959qgj4IGLoMuSYXwwiJJKoRBl6RCGHRJKoRBl6RCGHQNShFxXkQ8FhE/jYi9ETE/Iq6NiB9FxM6IeDwiLoqIuojYFxFXtH3f9yLi96o9v9QZLyzSYHUTcDgzPwYQEXW0vg99bmY2R8R84BuZuajtIxjXRMQDwLsz86+rN7bUNd+2qEEpIn6L1s/JfQT4R+AVYCvwQtuSGuClzLyhbf0q4Bbgmsxs6v+Jpe55hK5BKTP3R8S1tH64+Z8C/ww0ZuaHOq6NiHOAK4HXgQsAg653JM+ha1CKiIuB/8nMvwO+CXwAGBURH2rbfm5EjG9bvhx4ntb7vKyOiHOrMbPUHY/QNVjVA/e1XfZ/AvgsrfcDWdF2Pn0I8BcRcYLWe2tPzcxjEfEk8IfAH1dpbqlLnkOXpEJ4ykWSCmHQJakQBl2SCmHQJakQBl2SCmHQJakQBl2SCmHQJakQ/wfmw0YMtfvKIAAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "sg = (students.groupby('sex').count()['uuid']/len(students)).rename('complete')\n",
    "mg = (missing_course_taken.groupby('sex').count()['uuid']/len(missing_course_taken)).rename('incomplete')\n",
    "df = pd.concat([sg, mg], axis=1)\n",
    "df.plot.bar()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "acaade8b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:xlabel='job_id'>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXoAAAENCAYAAAABh67pAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjQuMywgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/MnkTPAAAACXBIWXMAAAsTAAALEwEAmpwYAAAdEElEQVR4nO3de5RV5Z3m8e9jcVUQbyyDFBHs0CIGRSwrBh0vTbygEjJZrPHuKNPNYOTm6LhMnFkxf/S0WXElAeOymqXIMpJghzYJnVTHxNjEUVGruAiUiEOIgQoYKxrBa6DkN3+cDTkcDpxdcKpOnV3PZ61anr3fd+/9O8fiYfOevd+tiMDMzLLriEoXYGZmnctBb2aWcQ56M7OMc9CbmWWcg97MLON6VbqAYk444YQYPnx4pcswM6saK1as+FNEDC7WliroJV0OzAVqgIcj4r6C9lHAo8A44J6IuD+v7RjgYeCzQABTI2L5wY43fPhwmpub05RmZmaApN8fqK1k0EuqAR4ELgFagSZJSyPi1bxu7wCzgC8V2cVc4BcRMUVSH+DIDtRuZmaHKc0YfT2wMSI2RcROYDEwOb9DRLwVEU3Arvz1ko4GLgAeSfrtjIh3y1G4mZmlkybohwJb8pZbk3VpnAK0AY9KWiXpYUlHdbBGMzM7DGnG6FVkXdp5E3qRG7efGREvSZoL3A387/0OIk0DpgF8+tOfTrl7q1a7du2itbWVjz/+uNKlZF6/fv2ora2ld+/elS7FKiRN0LcCw/KWa4GtKfffCrRGxEvJ8hJyQb+fiJgPzAeoq6vzBDwZ19raysCBAxk+fDhSsXMJK4eI4O2336a1tZURI0ZUuhyrkDRDN03ASEkjki9TrwGWptl5RLwJbJF0arJqAvDqQTaxHuLjjz/m+OOPd8h3Mkkcf/zx/pdTD1fyjD4i2iXNAJ4id3nlgohokTQ9aW+Q9CmgGTga2C1pDjA6InYAM4FFyV8Sm4BbOuetWLVxyHcNf86W6jr6iGgEGgvWNeS9fpPckE6xbVcDdYdeopmZHY5ueWes9TzD7/55Wff3xn1XlnV/h+vmm2/mqquuYsqUKQfss3DhQi699FJOOumkLqzMegIHvWXX1lXp+550VufVkdLChQv57Gc/66C3svOkZtajPfajn3HGF/4LZ555JjfeeCO///3vmTBhAmeccQYTJkxg8+bNQO6M/NZbb+Xiiy/mlFNO4Te/+Q1Tp07ltNNO4+abb967vwEDBnDHHXcwbtw4JkyYQFtb237HXLFiBRdeeCFnn302l112Gdu2bWPJkiU0Nzdz/fXXM3bsWD766KOi/cwOhYPeeqyWDb/lH+c9wjP/8s+88sorzJ07lxkzZnDTTTexZs0arr/+embNmrW3/5///GeeeeYZvvOd7zBp0iRuv/12WlpaWLt2LatXrwbggw8+YNy4caxcuZILL7yQb3zjG/scc9euXcycOZMlS5awYsUKpk6dyj333MOUKVOoq6tj0aJFrF69ml69ehXtZ3YoPHRjPdYzzzcx5coJnHDcsQAcd9xxLF++nCeffBKAG2+8kbvuumtv/0mTJiGJMWPGcOKJJzJmzBgATj/9dN544w3Gjh3LEUccwdVXXw3ADTfcwJe//OV9jrlhwwbWrVvHJZdcAsAnn3zCkCFD9qstbT+zNBz01mNFRMlLD/Pb+/btC8ARRxyx9/We5fb29pLb7znm6aefzvLlB53ANXU/szQ8dGM91oTz6/mXf/sVb7/zLgDvvPMO48ePZ/HixQAsWrSI888/v0P73L17N0uWLAHgBz/4wX7bn3rqqbS1te0N8F27dtHS0gLAwIEDee+990r2M+son9Fbt1CJyyFPP/VvuGfWf+PCKf9ATd+jOOuss5g3bx5Tp07lW9/6FoMHD+bRRx/t0D6POuooWlpaOPvssxk0aBBPPPHEPu19+vRhyZIlzJo1i+3bt9Pe3s6cOXM4/fTTufnmm5k+fTr9+/dn+fLlB+xn1lGK6H7TytTV1YUfPJJt69ev57TTTuvcg1Tg8soBAwbw/vvvl2Vf5dQln7dVlKQVEVH05lQP3ZiZZZyD3qyMuuPZvJmD3sws4xz0ZmYZ56A3M8s4B72ZWcb5OnrrHu4dVOb9bS/ZZfwXb+aFpQvLe9zDsHDhQpqbm/ne9753wD7Lli2jT58+jB8/vgsrs2rnM3rrsbpTyKe1bNkyXnjhhUqXYVXGZ/TWYw0YeR7v/7/nWfZCM/d+73ZOOOEE1q1bx9lnn83jjz+OJJqampg9ezYffPABffv25de//jW9e/fm1ltvpbm5mV69evHtb3+biy++mIULF/KTn/yETz75hHXr1nHHHXewc+dOvv/979O3b18aGxs57rjjuOiiixg7diwvv/wyO3bsYMGCBdTX1+9TW1tbG9OnT987TfJ3v/tdhg4dSkNDAzU1NTz++OM88MADjBo1ar9+5513Xud8YGn/1ZXiX1Odqlrq7EKpgl7S5cBccs+MfTgi7itoHwU8CowD7omI+wvaa8g9U/YPEXFVOQo3K6dVq1bR0tLCSSedxHnnncfzzz9PfX09V199NU888QTnnHMOO3bsoH///sydOxeAtWvX8tprr3HppZfy+uuvA7Bu3TpWrVrFxx9/zGc+8xm++c1vsmrVKm6//XYee+wx5syZA+SmM37hhRd49tlnmTp1KuvWrdunntmzZ3P77bdz/vnns3nzZi677DLWr1/P9OnTGTBgAHfeeScA1113XdF+ZvlKBn0S0g8ClwCtQJOkpRHxal63d4BZwJcOsJvZwHpyDw8363bq6+uprc099njs2LG88cYbDBo0iCFDhnDOOecAcPTRuV/f5557jpkzZwIwatQoTj755L1Bf/HFFzNw4EAGDhzIoEGDmDRpEgBjxoxhzZo1e4937bXXAnDBBRewY8cO3n333X3qefrpp3n11b/+EduxY8feCc/S9Bs4cOBhfR6WLWnO6OuBjRGxCUDSYmAysPe3KyLeAt6StN/MVJJqgSuBfwT+RzmKrjZpn4fa3Z5z2pPkTztcU1NDe3v7AacxPtj8UIXTF+dPbZw/lXHhfguXd+/ezfLly+nfv/9B607bz3q2NF/GDgW25C23JuvS+i5wF7D7YJ0kTZPULKm52OPXzLraqFGj2Lp1K01NTQC89957tLe3c8EFF7Bo0SIAXn/9dTZv3sypp57aoX3vmdXyueeeY9CgQQwatO+48qWXXrrP1Td7nmCVP5XxwfqZ5UtzRl/syQyppryUdBXwVkSskHTRwfpGxHxgPuRmr0yz/8zpyCWGWfsiqRu+nz59+vDEE08wc+ZMPvroI/r378/TTz/NV77yFaZPn86YMWPo1asXCxcu3OdMPo1jjz2W8ePH7/0yttC8efO47bbbOOOMM/b+5dLQ0MCkSZOYMmUKP/3pT3nggQcO2M8sX8lpiiV9Hrg3Ii5Llr8KEBH/VKTvvcD7e76MlfRPwI1AO9CP3Bj9kxFxw8GOmXaa4rRDIlDZYZHUQzf9rku/024YjB2R1WmK07jooou4//77qasrOqNspyjL510tV7NUS51ldrjTFDcBIyWNkNQHuAZYmubAEfHViKiNiOHJds+UCnkzMyuvkkM3EdEuaQbwFLnLKxdERIuk6Ul7g6RPkbt88mhgt6Q5wOiI2NF5pZtVp2XLllW6BOthUl1HHxGNQGPBuoa8128CtSX2sQxY1uEKLbPSPJzbDl93fIqcdS3fGWsV0a9fP95++22OP/54h30nigjefvtt+vXrV+lSDkuHvo+r7rfaKRz0tldXXu9fW1tLa2srnXop7btvpe+7Pbt3k/br12/vzWDWMznorSJ69+7NiBEjDmlbX8Vk1jGevdLMLOMc9GZmGeehG7NOUC0381nP4KA368F8NUvP4KEbM7OMc9CbmWWcg97MLOMc9GZmGecvY80qrYdOq2tdx2f0ZmYZ56A3M8s4B72ZWcY56M3MMs5fxlrH9eSHmJtVIZ/Rm5llXKqgl3S5pA2SNkq6u0j7KEnLJf1F0p1564dJ+g9J6yW1SJpdzuLNzKy0kkM3kmqAB4FLgFagSdLSiHg1r9s7wCzgSwWbtwN3RMRKSQOBFZJ+VbCtmZl1ojRn9PXAxojYFBE7gcXA5PwOEfFWRDQBuwrWb4uIlcnr94D1wNCyVG5mZqmkCfqhwJa85VYOIawlDQfOAl46QPs0Sc2Smjv1OaJmZj1MmqtuVGRddOQgkgYA/wrMiYgdxfpExHxgPkBdXV2H9p+KbzM3sx4qzRl9KzAsb7kW2Jr2AJJ6kwv5RRHxZMfKMzOzw5Um6JuAkZJGSOoDXAMsTbNzSQIeAdZHxLcPvUwzMztUJYduIqJd0gzgKaAGWBARLZKmJ+0Nkj4FNANHA7slzQFGA2cANwJrJa1Odvm1iGgs+zsxM7OiUt0ZmwRzY8G6hrzXb5Ib0in0HMXH+M3MrIv4zlgzs4xz0JuZZZyD3sws4xz0ZmYZ56A3M8s4B72ZWcY56M3MMs5Bb2aWcQ56M7OMc9CbmWWcg97MLOMc9GZmGeegNzPLOAe9mVnGOejNzDLOQW9mlnEOejOzjEsV9JIul7RB0kZJdxdpHyVpuaS/SLqzI9uamVnnKhn0kmqAB4GJ5J4De62k0QXd3gFmAfcfwrZmZtaJ0pzR1wMbI2JTROwEFgOT8ztExFsR0QTs6ui2ZmbWudIE/VBgS95ya7IujcPZ1szMyiBN0KvIuki5/9TbSpomqVlSc1tbW8rdm5lZKWmCvhUYlrdcC2xNuf/U20bE/Iioi4i6wYMHp9y9mZmVkibom4CRkkZI6gNcAyxNuf/D2dbMzMqgV6kOEdEuaQbwFFADLIiIFknTk/YGSZ8CmoGjgd2S5gCjI2JHsW076b2YmVkRJYMeICIagcaCdQ15r98kNyyTalszM+s6vjPWzCzjHPRmZhnnoDczyzgHvZlZxjnozcwyzkFvZpZxDnozs4xz0JuZZZyD3sws4xz0ZmYZ56A3M8s4B72ZWcY56M3MMs5Bb2aWcQ56M7OMc9CbmWWcg97MLOMc9GZmGZcq6CVdLmmDpI2S7i7SLknzkvY1ksbltd0uqUXSOkk/lNSvnG/AzMwOrmTQS6oBHgQmAqOBayWNLug2ERiZ/EwDHkq2HQrMAuoi4rPkHhB+TdmqNzOzktKc0dcDGyNiU0TsBBYDkwv6TAYei5wXgWMkDUnaegH9JfUCjgS2lql2MzNLIU3QDwW25C23JutK9omIPwD3A5uBbcD2iPhlsYNImiapWVJzW1tb2vrNzKyENEGvIusiTR9Jx5I72x8BnAQcJemGYgeJiPkRURcRdYMHD05RlpmZpZEm6FuBYXnLtew//HKgPl8AfhcRbRGxC3gSGH/o5ZqZWUelCfomYKSkEZL6kPsydWlBn6XATcnVN+eSG6LZRm7I5lxJR0oSMAFYX8b6zcyshF6lOkREu6QZwFPkrppZEBEtkqYn7Q1AI3AFsBH4ELglaXtJ0hJgJdAOrALmd8YbMTOz4koGPUBENJIL8/x1DXmvA7jtANt+Hfj6YdRoZmaHwXfGmpllnIPezCzjHPRmZhnnoDczyzgHvZlZxjnozcwyzkFvZpZxDnozs4xz0JuZZZyD3sws4xz0ZmYZ56A3M8s4B72ZWcY56M3MMs5Bb2aWcQ56M7OMc9CbmWWcg97MLONSBb2kyyVtkLRR0t1F2iVpXtK+RtK4vLZjJC2R9Jqk9ZI+X843YGZmB1cy6CXVAA8CE4HRwLWSRhd0mwiMTH6mAQ/ltc0FfhERo4AzgfVlqNvMzFJKc0ZfD2yMiE0RsRNYDEwu6DMZeCxyXgSOkTRE0tHABcAjABGxMyLeLV/5ZmZWSpqgHwpsyVtuTdal6XMK0AY8KmmVpIclHVXsIJKmSWqW1NzW1pb6DZiZ2cGlCXoVWRcp+/QCxgEPRcRZwAfAfmP8ABExPyLqIqJu8ODBKcoyM7M00gR9KzAsb7kW2JqyTyvQGhEvJeuXkAt+MzPrImmCvgkYKWmEpD7ANcDSgj5LgZuSq2/OBbZHxLaIeBPYIunUpN8E4NVyFW9mZqX1KtUhItolzQCeAmqABRHRIml60t4ANAJXABuBD4Fb8nYxE1iU/CWxqaDNzMw6WcmgB4iIRnJhnr+uIe91ALcdYNvVQN2hl2hmZofDd8aamWWcg97MLOMc9GZmGeegNzPLOAe9mVnGOejNzDIu1eWVZmZW2vC7f56q3xv3XdnJlezLZ/RmZhnnoDczyzgHvZlZxnmM3sysq907qAN9tx/24XxGb2aWcQ56M7OMc9CbmWWcg97MLOMc9GZmGeegNzPLuFRBL+lySRskbZR0d5F2SZqXtK+RNK6gvUbSKkk/K1fhZmaWTsmgl1QDPAhMBEYD10oaXdBtIjAy+ZkGPFTQPhtYf9jVmplZh6U5o68HNkbEpojYCSwGJhf0mQw8FjkvAsdIGgIgqRa4Eni4jHWbmVlKaYJ+KLAlb7k1WZe2z3eBu4Ddh1aimZkdjjRBryLrIk0fSVcBb0XEipIHkaZJapbU3NbWlqIsMzNLI03QtwLD8pZrga0p+5wHfFHSG+SGfP5O0uPFDhIR8yOiLiLqBg8enLJ8MzMrJU3QNwEjJY2Q1Ae4Blha0GcpcFNy9c25wPaI2BYRX42I2ogYnmz3TETcUM43YGZmB1dy9sqIaJc0A3gKqAEWRESLpOlJewPQCFwBbAQ+BG7pvJLNzKwjUk1THBGN5MI8f11D3usAbiuxj2XAsg5XaGZmh8V3xpqZZZyD3sws4xz0ZmYZ56A3M8s4B72ZWcY56M3MMs5Bb2aWcQ56M7OMc9CbmWWcg97MLOMc9GZmGeegNzPLOAe9mVnGOejNzDLOQW9mlnEOejOzjHPQm5llnIPezCzjUgW9pMslbZC0UdLdRdolaV7SvkbSuGT9MEn/IWm9pBZJs8v9BszM7OBKBr2kGuBBYCIwGrhW0uiCbhOBkcnPNOChZH07cEdEnAacC9xWZFszM+tEac7o64GNEbEpInYCi4HJBX0mA49FzovAMZKGRMS2iFgJEBHvAeuBoWWs38zMSkgT9EOBLXnLrewf1iX7SBoOnAW8VOwgkqZJapbU3NbWlqIsMzNLI03Qq8i66EgfSQOAfwXmRMSOYgeJiPkRURcRdYMHD05RlpmZpZEm6FuBYXnLtcDWtH0k9SYX8osi4slDL9XMzA5FmqBvAkZKGiGpD3ANsLSgz1LgpuTqm3OB7RGxTZKAR4D1EfHtslZuZmap9CrVISLaJc0AngJqgAUR0SJpetLeADQCVwAbgQ+BW5LNzwNuBNZKWp2s+1pENJb1XZiZ2QGVDHqAJJgbC9Y15L0O4LYi2z1H8fF7MzPrIr4z1sws4xz0ZmYZ56A3M8s4B72ZWcY56M3MMs5Bb2aWcQ56M7OMc9CbmWWcg97MLOMc9GZmGeegNzPLOAe9mVnGOejNzDLOQW9mlnEOejOzjHPQm5llnIPezCzjHPRmZhmXKuglXS5pg6SNku4u0i5J85L2NZLGpd3WzMw6V8mgl1QDPAhMBEYD10oaXdBtIjAy+ZkGPNSBbc3MrBOlOaOvBzZGxKaI2AksBiYX9JkMPBY5LwLHSBqSclszM+tEvVL0GQpsyVtuBT6Xos/QlNsCIGkauX8NALwvaUOK2lITnAD8qWTHb6ich+2Q1DWC60whc3VWsEZwneXUSb+bJx+oIU3QFztKpOyTZtvcyoj5wPwU9RwSSc0RUddZ+y+HaqgRXGe5uc7yqoY6u7rGNEHfCgzLW64Ftqbs0yfFtmZm1onSjNE3ASMljZDUB7gGWFrQZylwU3L1zbnA9ojYlnJbMzPrRCXP6COiXdIM4CmgBlgQES2SpiftDUAjcAWwEfgQuOVg23bKOymt04aFyqgaagTXWW6us7yqoc4urVERRYfMzcwsI3xnrJlZxjnozcwyzkFvZpZxDnozs4xLcx29dSJJJ5K7gziArRHxxwqXVFQ11FkNNe5RDbVWQ43gOlMdO8tX3XTnXwBJY4EGYBDwh2R1LfAu8JWIWFmZyvZVDXVWQ417VEOt1VAjuM4OiYjM/QBjgReB9cDTyc9rybpxla4vqXE18Lki688FXql0fdVUZzXUWE21VkONrrNjP1kdulkI/PeIeCl/ZXLX7qPAmZUoqsBRhfUBRMSLko6qREEHUA11VkONe1RDrdVQI7jO1LIa9BX/YFP4d0k/Bx7jrzN8DgNuAn5Rsar2Vw11VkONe1RDrdVQI7jO1DI5Ri9pHvA3FP9gfxcRMypVWz5JE8nNzz+U3EyfrcDSiGisaGEFqqHOaqhxj2qotRpqBNeZ+vhZDHqo/AdrZtZdZPY6+oj494iYHhGTIuKq5HVVhHzyEJZurxrqrIYa96iGWquhRnCdhTIb9AdSJb8AlX1ET3rVUGc11LhHNdRaDTWC69xHVr+MPZhu8wsgaRS5oaWXIuL9vKbfV6ikoiTVAxERTcnD3S8HXouIf65waQck6bGIuKk71wgg6Xxyz1Ze111qlfQ5YH1E7JDUH7gbGAe8CvyfihaXR9Is4McRsaWwrbt8lgB5z+LYGhFPS7oOGE/u8u8uma44s2P0ByLploh4tBvUMQu4jdz/7LHA7Ij4adK2MiLGVbC8vSR9HZhI7qTgV+Se+bsM+ALwVET8Y+Wqy5FU+DAbARcDzwBExBe7vKgDkPRyRNQnr/+B3O/Aj4FLgX+LiPsqWR+ApBbgzMg9T2I+uWdMLAEmJOu/XNECE5K2Ax8AvwV+CPwoItoqW9X+JC0i9+fnSHI3SQ0AniT3eRIRN3d6EZW+maCrf4DNla4hqWMtMCB5PRxoJhf2AKsqXV9BnTXJL+kO4OhkfX9gTaXrS2pZCTwOXARcmPx3W/L6wkrXV1DrqrzXTcDg5PVRwNpK15fUsj7/sy1oW13p+vI/S3LDz5cCjwBt5C5X/K/AwErXl1fnmuS/vYA/AjXJsrrqz1Amh24krTlQE3BiV9ZyEDWRDNdExBuSLgKWSDqZbjS8BLRHxCfAh5J+GxE7ACLiI0m7K1zbHnXAbOAe4H9GxGpJH0XEbypcVzFHSDqWXEApkjPQiPhAUntlS9trXd6/fF+RVBcRzZL+FthV6eLyRETsBn4J/FJSb3L/+rwWuB8YXMni8hyRDN8cRe6EaRDwDtAX6N0VBWQy6MmF+WXAnwvWC3ih68sp6k1JYyNiNUBEvC/pKmABMKaile1rp6QjI+JD4Ow9KyUNArpF0Cd/2L8j6UfJf/9I9/3dHgSsIPe7GJI+FRFvShpA9/kL/u+BuZL+F/AnYLmkLeTuSfn7ila2r30+r4jYRe6Z1EuT7xa6i0fITcFSQ+5k5EeSNpGbAmFxVxSQyTF6SY8Aj0bEc0XafhAR11WgrMI6asmdLb9ZpO28iHi+AmXtR1LfiPhLkfUnAEMiYm0FyjooSVcC50XE1ypdS1qSjgROjIjfVbqWPSQNBE4h95dma3SjSQEBJP1tRLxe6TrSkHQSQERslXQMue+4NkfEy11y/CwGvZmZ/VWPu47ezKyncdCbmWWcg97MLOMc9NZjSTrgFViSLpL0sw7sqzH5kq1w/b2S7jzEEs3KortegmbW6SJifBn3dUW59mVWbj6jtx5L0vvK+ZakdZLWSro6r8vRkn4s6VVJDZIO+OdF0hvJJadIukfSBklPA6d29vswK8Vn9NbTfZncXENnAicATZKeTdrqgdHkJpn7RdJ3ycF2JulschNYnUXuz9dKcjdImVWMz+itpzsf+GFEfJLcEPQb4Jyk7eWI2JRMAfHDpG8p/4ncjIofJtNFFE64ZtblHPTW0x1s2oHCuwnT3l3ouxCtW3HQW0/3LHC1pBpJg4ELgD23pddLGpGMzV8N7DelxgH2958l9U+mEJjUKVWbdYDH6K0nC3JzwX8eeCVZviuZZGwUsBy4j9wkc88mfQ++w4iVkp4AVpMb2/+/nVO6WXqe68Z6JEnHk5tr/eRK12LW2Tx0Yz1OMpPgcnJzlptlns/ozTpA0kvkHhiR78buOF2z2R4OejOzjPPQjZlZxjnozcwyzkFvZpZxDnozs4z7/20hOqTaZ1msAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "sg = (students.groupby('job_id').count()['uuid']/len(students)).rename('complete')\n",
    "mg = (missing_course_taken.groupby('job_id').count()['uuid']/len(missing_course_taken)).rename('incomplete')\n",
    "df = pd.concat([sg, mg], axis=1)\n",
    "df.plot.bar()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "b3f87be8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:xlabel='age_group'>"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYAAAAEJCAYAAACdePCvAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjQuMywgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/MnkTPAAAACXBIWXMAAAsTAAALEwEAmpwYAAAeXElEQVR4nO3dfZQU9b3n8ffHUUQFCeoEUUwgCRFBZMSBGHQVL+JTlqCJuWgMPpAsIRERT7K5nLh7rvkj92BiwurRlTVXfNjV+EDCDXvD8SEaTFSMDEiAETHERRxB5IIB1BAZ+e4fVUOatpmpZhp6ZurzOqdPV/0eqr7VZ6a/Xb+u+rUiAjMzy5+Dqh2AmZlVhxOAmVlOOQGYmeWUE4CZWU45AZiZ5ZQTgJlZTh1c7QDKccwxx0T//v2rHYaZWaeyZMmS/4iI2uLyTpUA+vfvT0NDQ7XDMDPrVCS9XqrcQ0BmZjnlBGBmllNOAGZmOdWpvgMoZefOnTQ1NbFjx45qh9Llde/enX79+nHIIYdUOxQzq4BOnwCampro2bMn/fv3R1K1w+myIoLNmzfT1NTEgAEDqh2OmVVApx8C2rFjB0cffbTf/PczSRx99NE+0zLrQjp9AgD85n+A+HU261q6RALIi6uvvpq5c+e22ubee+9l/fr1BygiM+vMOv13AMX6z/h1Rbe3duYXKrq9/e3ee+/l5JNP5rjjjqt2KJYXN/Uqo+3W/ReHlc1nABVw//33c8oppzBs2DAmTpzI66+/zpgxYzjllFMYM2YM69atA5JP8N/61rc455xz+NSnPsUzzzzDpEmTOOmkk7j66qt3b69Hjx585zvfYfjw4YwZM4ZNmzZ9ZJ9Llizh7LPP5rTTTuP8889nw4YNzJ07l4aGBq644grq6ur461//WrKdmRk4AbRbY2MjP/zhD3n66af54x//yK233srUqVO58sorWb58OVdccQXTpk3b3f6dd97h6aefZtasWYwbN44bbriBxsZGVqxYwbJlywB47733GD58OEuXLuXss8/mBz/4wR773LlzJ9dddx1z585lyZIlTJo0iRtvvJFLL72U+vp6HnjgAZYtW8bBBx9csp2ZGXTBIaAD7emnn+bSSy/lmGOOAeCoo45i0aJF/PKXvwRg4sSJfO9739vdfty4cUhi6NCh9OnTh6FDhwIwZMgQ1q5dS11dHQcddBATJkwA4Gtf+xpf+tKX9tjn6tWrWblyJWPHjgXgww8/pG/fvh+JLWs7M8snJ4B2iog2r44prD/00EMBOOigg3Yvt6w3Nze32b9ln0OGDGHRokVtxpalnZnlk4eA2mnMmDE88sgjbN68GYAtW7YwatQoHnroIQAeeOABzjzzzLK2uWvXrt1X+zz44IMf6X/iiSeyadOm3W/sO3fupLGxEYCePXuyffv2NtuZmfkMoJ2GDBnCjTfeyNlnn01NTQ2nnnoqt912G5MmTeLHP/4xtbW13HPPPWVt84gjjqCxsZHTTjuNXr168fDDD+9R361bN+bOncu0adPYunUrzc3NTJ8+nSFDhnD11VczZcoUDjvsMBYtWrTXdmZmiohqx5BZfX19FP8ewKpVqzjppJOqFNH+0aNHD959991qh1FSV3y9rZ18GWiHJ2lJRNQXl3sIyMwsp5wAOqCO+unfzLqWTAlA0gWSVktaI2lGifpBkhZJ+puk7xaUnyhpWcFjm6Tpad1Nkt4sqLuoYkdlZmZtavNLYEk1wB3AWKAJWCxpfkS8XNBsCzANuLiwb0SsBuoKtvMmMK+gyayIuKUd8ZuZ2T7KcgYwElgTEa9FxAfAQ8D4wgYR8XZELAZ2trKdMcCfI6LkjxObmdmBlSUBHA+8UbDelJaV6zLg50VlUyUtlzRHUu992KaZme2jLAmg1G2uZV07Kqkb8EXg0YLiO4FPkwwRbQB+spe+kyU1SGooNSlaRzBq1Khqh7CHe++9l6lTp7baZuHChTz//PMHKCIz64iy3AjWBJxQsN4PKHfC+QuBpRGxsaWgcFnSz4B/L9UxIu4C7oLkPoA291TONclZZLhuuTO+kS5cuJAePXp0uORlZgdOljOAxcBASQPST/KXAfPL3M/lFA3/SCqclewSYGWZ2+wwevToASRvqqNHj+bSSy9l0KBBXHHFFbTcaLd48WJGjRrFsGHDGDlyJNu3b2fHjh1cc801DB06lFNPPZXf/va3QPIJ/uKLL2bcuHEMGDCA22+/nZ/+9KeceuqpnH766WzZsgWA0aNHM336dEaNGsXJJ5/Miy+++JHYNm3axJe//GVGjBjBiBEjeO6551i7di2zZ89m1qxZ1NXV8fvf/75kOzPr2to8A4iIZklTgceBGmBORDRKmpLWz5Z0LNAAHAnsSi/1HBwR2yQdTnIF0TeLNv0jSXUkw0lrS9R3Si+99BKNjY0cd9xxnHHGGTz33HOMHDmSCRMm8PDDDzNixAi2bdvGYYcdxq233grAihUreOWVVzjvvPN49dVXAVi5ciUvvfQSO3bs4DOf+Qw333wzL730EjfccAP3338/06dPB5Kpo59//nl+97vfMWnSJFau3DOPXn/99dxwww2ceeaZrFu3jvPPP59Vq1YxZcoUevTowXe/m1y1+9WvfrVkOzPrujLNBRQRC4AFRWWzC5bfIhkaKtX3feDoEuUTy4q0kxg5ciT9+iUvRV1dHWvXrqVXr1707duXESNGAHDkkUcC8Oyzz3LdddcBMGjQID75yU/uTgDnnHMOPXv2pGfPnvTq1Ytx48YBMHToUJYvX757f5dffjkAZ511Ftu2beMvf/nLHvH85je/4eWX/37F7rZt23ZPFpelXc+ePdv1ephZx+XJ4CqscIrnmpoampub9zpldGvzMBVPFV04jXThtNHF2y1e37VrF4sWLeKwww5rNe6s7cys6/BUEAfAoEGDWL9+PYsXLwZg+/btNDc3c9ZZZ/HAAw8A8Oqrr7Ju3TpOPPHEsrbdMlPos88+S69evejVa88vwc877zxuv/323estvzpWOG10a+3MrOtyAjgAunXrxsMPP8x1113HsGHDGDt2LDt27ODb3/42H374IUOHDmXChAnce++9e3zyz6J3796MGjWKKVOmcPfdd3+k/rbbbqOhoYFTTjmFwYMHM3t2MnI3btw45s2bt/tL4L21M7Ouy9NBd2KjR4/mlltuob7+I7O87jd5fr1tLzrLdNBZ4+yCU1Z7OmgzM9uDvwTuxBYuXFjtEMysE3MCMLNOrf+MX2dqt7b7fg6kE+oSQ0Cd6XuMzsyvs1nX0ukTQPfu3dm8ebPfnPaziGDz5s107+6PUWZdRacfAurXrx9NTU101JlCu5Lu3bvvvsvZzDq/Tp8ADjnkEAYMGFDtMMy6HI+td32dfgjIzMz2Tac/AzArW5VvXMr8yXrmFyq+b7NCPgMwM8spJwAzs5zyEFAVZR0KAA8HmFnlOQF0FlWcyMqJyqxr8hCQmVlOOQGYmeVUpiEgSRcAt5L8KPy/RsTMovpBwD3AcODGiLiloG4tsB34EGhumZNa0lHAw0B/kh+F/8eIeKd9h2N55huXzMrT5hmApBrgDuBCYDBwuaTBRc22ANOAWyjtnIioK/pBghnAUxExEHgqXTczswMkyxnASGBNRLwGIOkhYDzwckuDiHgbeFtSOd8AjgdGp8v3AQuBfyqjv3VEOf7VJbPOJst3AMcDbxSsN6VlWQXwhKQlkiYXlPeJiA0A6fPHS3WWNFlSg6QGT/hmZlY5WRKASpSVM/fyGRExnGQI6VpJZ5XRl4i4KyLqI6K+tra2nK5mZtaKLAmgCTihYL0fsD7rDiJiffr8NjCPZEgJYKOkvgDp89tZt2lmZu2XJQEsBgZKGiCpG3AZMD/LxiUdIalnyzJwHrAyrZ4PXJUuXwX8qpzAzcysfdr8EjgimiVNBR4nuQx0TkQ0SpqS1s+WdCzQABwJ7JI0neSKoWOAeZJa9vVgRDyWbnom8IikrwPrgK9U9MjMzKxVme4DiIgFwIKistkFy2+RDA0V2wYM28s2NwNjMkdqZmYV5TuBzcxyygnAzCynnADMzHLKCcDMLKecAMzMcsoJwMwsp5wAzMxyygnAzCynnADMzHLKCcDMLKecAMzMcsoJwMwsp5wAzMxyygnAzCynnADMzHIq0+8BmFkV3NSrjLZb918c1mX5DMDMLKecAMzMcipTApB0gaTVktZImlGifpCkRZL+Jum7BeUnSPqtpFWSGiVdX1B3k6Q3JS1LHxdV5pDMzCyLNr8DkFQD3AGMBZqAxZLmR8TLBc22ANOAi4u6NwPfiYilknoCSyQ9WdB3VkTc0t6DMDOz8mU5AxgJrImI1yLiA+AhYHxhg4h4OyIWAzuLyjdExNJ0eTuwCji+IpGbmVm7ZEkAxwNvFKw3sQ9v4pL6A6cCfygonippuaQ5knqXu00zM9t3WRKASpRFOTuR1AP4BTA9IralxXcCnwbqgA3AT/bSd7KkBkkNmzZtKme3ZmbWiiwJoAk4oWC9H7A+6w4kHULy5v9ARPyypTwiNkbEhxGxC/gZyVDTR0TEXRFRHxH1tbW1WXdrZmZtyJIAFgMDJQ2Q1A24DJifZeOSBNwNrIqInxbV9S1YvQRYmS1kMzOrhDavAoqIZklTgceBGmBORDRKmpLWz5Z0LNAAHAnskjQdGAycAkwEVkhalm7y+xGxAPiRpDqS4aS1wDcreFxmZtaGTFNBpG/YC4rKZhcsv0UyNFTsWUp/h0BETMweppmZVZrvBDYzyyknADOznHICMDPLKScAM7OccgIwM8spJwAzs5xyAjAzyyknADOznHICMDPLKScAM7OccgIwM8spJwAzs5xyAjAzyyknADOznHICMDPLKScAM7OccgIwM8spJwAzs5xyAjAzy6lMCUDSBZJWS1ojaUaJ+kGSFkn6m6TvZukr6ShJT0r6U/rcu/2HY2ZmWbWZACTVAHcAFwKDgcslDS5qtgWYBtxSRt8ZwFMRMRB4Kl03M7MDJMsZwEhgTUS8FhEfAA8B4wsbRMTbEbEY2FlG3/HAfenyfcDF+3YIZma2L7IkgOOBNwrWm9KyLFrr2yciNgCkzx8vtQFJkyU1SGrYtGlTxt2amVlbsiQAlSiLjNtvT9+kccRdEVEfEfW1tbXldDUzs1ZkSQBNwAkF6/2A9Rm331rfjZL6AqTPb2fcppmZVUCWBLAYGChpgKRuwGXA/Izbb63vfOCqdPkq4FfZwzYzs/Y6uK0GEdEsaSrwOFADzImIRklT0vrZko4FGoAjgV2SpgODI2Jbqb7ppmcCj0j6OrAO+EqFj83MzFrRZgIAiIgFwIKistkFy2+RDO9k6puWbwbGlBOsmZlVju8ENjPLKScAM7OccgIwM8spJwAzs5xyAjAzyyknADOznHICMDPLKScAM7OccgIwM8spJwAzs5xyAjAzyyknADOznHICMDPLKScAM7OccgIwM8spJwAzs5xyAjAzyyknADOznHICMDPLqUwJQNIFklZLWiNpRol6SbotrV8uaXhafqKkZQWPbekPxiPpJklvFtRdVNEjMzOzVrX5o/CSaoA7gLFAE7BY0vyIeLmg2YXAwPTxOeBO4HMRsRqoK9jOm8C8gn6zIuKWChyHmZmVKcsZwEhgTUS8FhEfAA8B44vajAfuj8QLwMck9S1qMwb4c0S83u6ozcys3bIkgOOBNwrWm9KycttcBvy8qGxqOmQ0R1LvUjuXNFlSg6SGTZs2ZQjXzMyyyJIAVKIsymkjqRvwReDRgvo7gU+TDBFtAH5SaucRcVdE1EdEfW1tbYZwzcwsiywJoAk4oWC9H7C+zDYXAksjYmNLQURsjIgPI2IX8DOSoSYzMztAsiSAxcBASQPST/KXAfOL2swHrkyvBjod2BoRGwrqL6do+KfoO4JLgJVlR29mZvuszauAIqJZ0lTgcaAGmBMRjZKmpPWzgQXARcAa4H3gmpb+kg4nuYLom0Wb/pGkOpKhorUl6s3MbD9qMwEARMQCkjf5wrLZBcsBXLuXvu8DR5con1hWpGZmVlG+E9jMLKecAMzMcsoJwMwsp5wAzMxyygnAzCynnADMzHLKCcDMLKecAMzMcsoJwMwsp5wAzMxyygnAzCynnADMzHLKCcDMLKecAMzMcsoJwMwsp5wAzMxyygnAzCynnADMzHIqUwKQdIGk1ZLWSJpRol6Sbkvrl0saXlC3VtIKScskNRSUHyXpSUl/Sp97V+aQzMwsizYTgKQa4A7gQmAwcLmkwUXNLgQGpo/JwJ1F9edERF1E1BeUzQCeioiBwFPpupmZHSBZzgBGAmsi4rWI+AB4CBhf1GY8cH8kXgA+JqlvG9sdD9yXLt8HXJw9bDMza68sCeB44I2C9aa0LGubAJ6QtETS5II2fSJiA0D6/PFyAjczs/Y5OEMblSiLMtqcERHrJX0ceFLSKxHxu6wBpkljMsAnPvGJrN3MzKwNWc4AmoATCtb7AeuztomIlue3gXkkQ0oAG1uGidLnt0vtPCLuioj6iKivra3NEK6ZmWWRJQEsBgZKGiCpG3AZML+ozXzgyvRqoNOBrRGxQdIRknoCSDoCOA9YWdDnqnT5KuBX7TwWMzMrQ5tDQBHRLGkq8DhQA8yJiEZJU9L62cAC4CJgDfA+cE3avQ8wT1LLvh6MiMfSupnAI5K+DqwDvlKxozIzszZl+Q6AiFhA8iZfWDa7YDmAa0v0ew0YtpdtbgbGlBOsmZlVju8ENjPLKScAM7OccgIwM8spJwAzs5zK9CVwZ9N/xq8zt1078wv7MRIzs47LZwBmZjnVJc8AynJTr4zttu7fOMzMDjCfAZiZ5ZQTgJlZTjkBmJnllBOAmVlOOQGYmeWUE4CZWU45AZiZ5ZQTgJlZTjkBmJnllBOAmVlOOQGYmeWUE4CZWU5lSgCSLpC0WtIaSTNK1EvSbWn9cknD0/ITJP1W0ipJjZKuL+hzk6Q3JS1LHxdV7rDMzKwtbc4GKqkGuAMYCzQBiyXNj4iXC5pdCAxMH58D7kyfm4HvRMRSST2BJZKeLOg7KyJuqdzhmJlZVlnOAEYCayLitYj4AHgIGF/UZjxwfyReAD4mqW9EbIiIpQARsR1YBRxfwfjNzGwfZUkAxwNvFKw38dE38TbbSOoPnAr8oaB4ajpkNEdS71I7lzRZUoOkhk2bNmUI18zMssiSAFSiLMppI6kH8AtgekRsS4vvBD4N1AEbgJ+U2nlE3BUR9RFRX1tbmyFcMzPLIksCaAJOKFjvB6zP2kbSISRv/g9ExC9bGkTExoj4MCJ2AT8jGWoyM7MDJEsCWAwMlDRAUjfgMmB+UZv5wJXp1UCnA1sjYoMkAXcDqyLip4UdJPUtWL0EWLnPR2FmZmVr8yqgiGiWNBV4HKgB5kREo6Qpaf1sYAFwEbAGeB+4Ju1+BjARWCFpWVr2/YhYAPxIUh3JUNFa4JsVOiYzM8sg04/Cp2/YC4rKZhcsB3BtiX7PUvr7ASJiYlmRmplZRflOYDOznHICMDPLKScAM7OccgIwM8spJwAzs5xyAjAzyyknADOznHICMDPLKScAM7OccgIwM8spJwAzs5xyAjAzy6lMk8GZmdm+6z/j15nbrp35hf0YyZ6cAMzMOpKbemVst7Xdu/IQkJlZTjkBmJnllBOAmVlOOQGYmeWUE4CZWU5lSgCSLpC0WtIaSTNK1EvSbWn9cknD2+or6ShJT0r6U/rcuzKHZGZmWbSZACTVAHcAFwKDgcslDS5qdiEwMH1MBu7M0HcG8FREDASeStfNzOwAyXIGMBJYExGvRcQHwEPA+KI244H7I/EC8DFJfdvoOx64L12+D7i4fYdiZmblyHIj2PHAGwXrTcDnMrQ5vo2+fSJiA0BEbJD08VI7lzSZ5KwC4F1JqzPEnJngGOA/2mz4A1Vyt2VznJWTOUZwnBl0uTi75v/QJ0sVZkkApfYSGdtk6duqiLgLuKucPuWQ1BAR9ftr+5XiOCunM8QIjrPSHOdHZRkCagJOKFjvB6zP2Ka1vhvTYSLS57ezh21mZu2VJQEsBgZKGiCpG3AZML+ozXzgyvRqoNOBrenwTmt95wNXpctXAb9q57GYmVkZ2hwCiohmSVOBx4EaYE5ENEqaktbPBhYAFwFrgPeBa1rrm256JvCIpK8D64CvVPTIsttvw0sV5jgrpzPECI6z0hxnEUWUNSRvZmZdhO8ENjPLKScAM7OccgIwM8spJwAzs5xyAuhgJPWSNFPSK5I2p49VadnHqh1fC8dZWY6zsjpDnB0hxlwlgI7wgmfwCPAOMDoijo6Io4Fz0rJHqxrZnhxnZTnOyuoMcVY9xlxdBirpceBp4L6IeCstO5bkRrRzI2JsNeNL41kdESeWW3egOc7KcpyV1Rni7Agx5uoMAOgfETe3vPkDRMRbEXEz8IkqxlXodUnfk9SnpUBSH0n/xJ4T61Wb46wsx1lZnSHOqseYtwRQ9Rc8gwnA0cAzkt6RtAVYCBwF/GM1AyvSWeN8hyTOo+nYcXaW19Nx7ruqx5i3IaDeJD88Mx5omX56I8m8RDMj4p1qxVZI0iCSifNeiIh3C8oviIjHqhfZniSNBCIiFksaAlwArIqIBVUOrVWS/ndETKx2HK2R9J9Ifk9jRUQ8Ue14Wkj6HPBKRGyVdDjJ/9NwoBH4l4jYWtUAU5KmAfMioqN8sPsIJfOjXQ68GRG/kXQFMAp4GbgrInbu9xjylABaI+maiLinA8QxDbgWWAXUAddHxK/SuqURMbyV7geMpH8m+aW3g4EnSd6sngHOBR6PiB9WMbzdJBVPXAjwDyTfBRERXzywEZUm6cWIGJkuf4Pkb+DfgPOA/xsRM6sY3m6SGoFh6TxfdwHvAb8AxqTlX6pqgClJW0li+zPwIPBoRGT7zYIDRNIDJP8/hwFbgSOAeSSvpSLiqla6V0ZE+JEkwXXVjiGNYwXQI13uDzSQJAGAl6odX1GcNcDhwDbgyLT8MGB5teMriHMp8H+A0cDZ6fOGdPnsasdXEOdLBcuLgdp0+QiSs4Cqx5jGs6rwtS2qW1bt+ApfT5Ih7vOAu4FNwGMkF3z0rHZ8aYzL0+eDSUYiatJ1Haj/oSw/CNNlSFq+tyqgz17qDrSaSId9ImKtpNHAXEmfpPQP7FRLc0R8CLwv6c8RsQ0gIv4qaVeVYytUD1wP3Aj814hYJumvEfFMleMqdlA6RHkQyae/TQAR8Z6k5uqGtoeVBWfLf5RUHxENkj4L7PchizJEROwCngCekHQIyRnr5cAtQG01g0sdlA4DHUHyQaoXsAU4FDjkQASQqwRA8iZ/Psl1toUEPH/gwynpLUl1EbEMICLelfSfgTnA0KpGtqcPJB0eEe8Dp7UUSuoFdJgEkL4JzJL0aPq8kY75d98LWELytxiSjo2ItyT1oGMl/m8At0r6byQ/W7hI0hskF1F8o6qR7WmP1yyS8fT5wHxJh1UnpI+4G3iF5Ez6RuBRSa8Bp5P8fvp+l6vvACTdDdwTEc+WqHswIr5ahbCK4+hH8un6rRJ1Z0TEc1UI6yMkHRoRfytRfgzQNyJWVCGsNkn6AnBGRHy/2rFkkX7R2ici/l+1YykkqSfwKZJk2hQRG6sc0h4kfTYiXq12HG2RdBxARKxXcjPquSTD0S8ekP3nKQGYmdnf5e0+ADMzSzkBmJnllBOA2X6mhP/XrMPxH6XlgqR/k7REUqOkyWnZ1yW9KmmhpJ9Juj0tr5X0C0mL08cZrWy3VtKTkpZK+l+SXpd0jKT+Smaa/Z8k9yKcIOnHklZKWiFpQtp/tKR/L9je7ZKuTpfXSrpZ0ovp4zP78SWyHHICsLyYFBGnkdwXME3S8cB/J7nkbiwwqKDtrcCsiBgBfBn411a2+8/A05HcoT2PPScVPBG4PyJOTfdbBwwjudLjx5L6Zoh7WyR3CN8O/I8M7c0y64jXQ5vtD9MkXZIunwBMBJ6JiC0A6X0Cn03rzwUGS7svJT9SUs+I2F5iu2cClwBExGPpZHMtXo+IFwra/Ty9eW6jpGeAESR3Ubfm5wXPszIcp1lmTgDW5aV3U58LfD4i3pe0EFgNnLSXLgelbf+aZfOt1L2XoV0ze56Jdy+qj70sm7Wbh4AsD3oB76Rv/oNIhn0OB86W1FvSwSRDPS2eAKa2rEiqa2Xbz5JO3SvpPKD3Xtr9DpggqUZSLXAW8CLwOsnZxqHpXdRjivpNKHhe1OaRmpXBZwCWB48BU9K5oFYDLwBvAv8C/AFYTzIFb8tUxtOAO9L2B5O8eU/Zy7Z/APw8/VL3GZKJ5rYDPYrazQM+D/yR5JP89+Lvv0r3CLAc+BPJJGaFDpX0B5IPa5eXfeRmrfCdwJZbknqkcy0dTPIGPSci5pW5jUOBDyOZHvnzwJ0RUVeh+NYC9dHBpjG2rsNnAJZnN0k6l2Tc/QmS+ffL9QngkfQ6/w+A/1K58Mz2L58BmGUg6RqSaaULPRcR11YjHrNKcAIwM8spXwVkZpZTTgBmZjnlBGBmllNOAGZmOeUEYGaWU04AZmY59f8BK7+Y5fq/QcYAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "sg = (students.groupby('age_group').count()['uuid']/len(students)).rename('complete')\n",
    "mg = (missing_course_taken.groupby('age_group').count()['uuid']/len(missing_course_taken)).rename('incomplete')\n",
    "df = pd.concat([sg, mg], axis=1)\n",
    "df.plot.bar()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3816192",
   "metadata": {},
   "source": [
    "Nothing looks out of the ordinary in terms of distribution or from a simple eye-test. Additionally, there are instances of zeroes in the column, so we can conclude that the NaN's aren't just students who haven't enrolled in a course yet - ruling out structural missing data. \n",
    "\n",
    "I think it's safe to call this missing at random (MAR). Since less than 5% of the data is missing and it appears to be MAR, I can remove the missing data with little impact. Instead of completely deleting it, I will store the missing data in a seperate table in the event that my analytics teams wants to try to fill it in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "d4122e4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "missing_data = pd.DataFrame()\n",
    "missing_data = pd.concat([missing_data, missing_course_taken])\n",
    "students = students.dropna(subset=['num_course_taken'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "81b278ad",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4749 entries, 0 to 4999\n",
      "Data columns (total 15 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   uuid                    4749 non-null   int64  \n",
      " 1   name                    4749 non-null   object \n",
      " 2   dob                     4749 non-null   object \n",
      " 3   sex                     4749 non-null   object \n",
      " 4   job_id                  4744 non-null   float64\n",
      " 5   num_course_taken        4749 non-null   float64\n",
      " 6   current_career_path_id  4298 non-null   float64\n",
      " 7   time_spent_hrs          4298 non-null   float64\n",
      " 8   age                     4749 non-null   float64\n",
      " 9   age_group               4749 non-null   int64  \n",
      " 10  email                   4749 non-null   object \n",
      " 11  street                  4749 non-null   object \n",
      " 12  city                    4749 non-null   object \n",
      " 13  state                   4749 non-null   object \n",
      " 14  zip_code                4749 non-null   object \n",
      "dtypes: float64(5), int64(2), object(8)\n",
      "memory usage: 593.6+ KB\n"
     ]
    }
   ],
   "source": [
    "students.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8c42329",
   "metadata": {},
   "source": [
    "### `job_id`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00062e1d",
   "metadata": {},
   "source": [
    "Since this missing data is categorical, we can't impute the data. \n",
    "If this data is MAR, there are only a few other options:\n",
    "- delete the data due to the very low percentage of affect rows\n",
    "- replace with the most frequent category\n",
    "- develop a classification model to predict the missing category"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "aa0063d3",
   "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>age</th>\n",
       "      <th>age_group</th>\n",
       "      <th>email</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>163</td>\n",
       "      <td>Glen Riley</td>\n",
       "      <td>2002-08-22</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.70</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10</td>\n",
       "      <td>glen_riley4484@hmail.com</td>\n",
       "      <td>P.O. Box 37267</td>\n",
       "      <td>Cornlea village</td>\n",
       "      <td>Tennessee</td>\n",
       "      <td>19192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>757</th>\n",
       "      <td>758</td>\n",
       "      <td>Mercedez Vorberg</td>\n",
       "      <td>2002-03-25</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.14</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20</td>\n",
       "      <td>mercedez6297@woohoo.com</td>\n",
       "      <td>284 Cedar Seventh</td>\n",
       "      <td>Virden village</td>\n",
       "      <td>Washington</td>\n",
       "      <td>60489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>854</th>\n",
       "      <td>855</td>\n",
       "      <td>Kurt Ho</td>\n",
       "      <td>2002-05-29</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>23.72</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20</td>\n",
       "      <td>ho6107@inlook.com</td>\n",
       "      <td>P.O. Box 27254</td>\n",
       "      <td>Olin</td>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>60067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1029</th>\n",
       "      <td>1030</td>\n",
       "      <td>Penny Gaines</td>\n",
       "      <td>2002-03-01</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.25</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20</td>\n",
       "      <td>gaines2897@hmail.com</td>\n",
       "      <td>138 Misty Vale</td>\n",
       "      <td>Stockton borough</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>53630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1542</th>\n",
       "      <td>1543</td>\n",
       "      <td>Frederick Reilly</td>\n",
       "      <td>2002-11-13</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>21.32</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10</td>\n",
       "      <td>frederick_reilly6971@woohoo.com</td>\n",
       "      <td>P.O. Box 40769</td>\n",
       "      <td>Quakervillage</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>96218</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      uuid              name         dob sex  job_id  num_course_taken  \\\n",
       "162    163        Glen Riley  2002-08-22   M     NaN               8.0   \n",
       "757    758  Mercedez Vorberg  2002-03-25   F     NaN              15.0   \n",
       "854    855           Kurt Ho  2002-05-29   M     NaN               0.0   \n",
       "1029  1030      Penny Gaines  2002-03-01   N     NaN              15.0   \n",
       "1542  1543  Frederick Reilly  2002-11-13   M     NaN               7.0   \n",
       "\n",
       "      current_career_path_id  time_spent_hrs   age  age_group  \\\n",
       "162                      3.0            5.70  19.0         10   \n",
       "757                      4.0            4.14  20.0         20   \n",
       "854                      8.0           23.72  20.0         20   \n",
       "1029                     4.0           16.25  20.0         20   \n",
       "1542                     9.0           21.32  19.0         10   \n",
       "\n",
       "                                email             street               city  \\\n",
       "162          glen_riley4484@hmail.com     P.O. Box 37267    Cornlea village   \n",
       "757           mercedez6297@woohoo.com  284 Cedar Seventh     Virden village   \n",
       "854                 ho6107@inlook.com     P.O. Box 27254               Olin   \n",
       "1029             gaines2897@hmail.com     138 Misty Vale   Stockton borough   \n",
       "1542  frederick_reilly6971@woohoo.com     P.O. Box 40769      Quakervillage   \n",
       "\n",
       "               state zip_code  \n",
       "162        Tennessee    19192  \n",
       "757       Washington    60489  \n",
       "854    New Hampshire    60067  \n",
       "1029   West Virginia    53630  \n",
       "1542        Maryland    96218  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_job_id = students[students[['job_id']].isnull().any(axis=1)]\n",
    "missing_job_id.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5baf278a",
   "metadata": {},
   "source": [
    "With only 5 rows of missing data and no pattern that I can deduce, this data also appears to be MAR. Taking into account both the amount of resources (time) I have and the impact that fixing 5 rows of data will have, the most reasonable action is to delete this data as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "d4628b96",
   "metadata": {},
   "outputs": [],
   "source": [
    "missing_data = pd.concat([missing_data, missing_job_id])\n",
    "students = students.dropna(subset=['job_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "c2ae86a9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4744 entries, 0 to 4999\n",
      "Data columns (total 15 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   uuid                    4744 non-null   int64  \n",
      " 1   name                    4744 non-null   object \n",
      " 2   dob                     4744 non-null   object \n",
      " 3   sex                     4744 non-null   object \n",
      " 4   job_id                  4744 non-null   float64\n",
      " 5   num_course_taken        4744 non-null   float64\n",
      " 6   current_career_path_id  4293 non-null   float64\n",
      " 7   time_spent_hrs          4293 non-null   float64\n",
      " 8   age                     4744 non-null   float64\n",
      " 9   age_group               4744 non-null   int64  \n",
      " 10  email                   4744 non-null   object \n",
      " 11  street                  4744 non-null   object \n",
      " 12  city                    4744 non-null   object \n",
      " 13  state                   4744 non-null   object \n",
      " 14  zip_code                4744 non-null   object \n",
      "dtypes: float64(5), int64(2), object(8)\n",
      "memory usage: 593.0+ KB\n"
     ]
    }
   ],
   "source": [
    "students.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "111cd020",
   "metadata": {},
   "source": [
    "### `current_career_path_id`\n",
    "`current_career_path_id` is also categorical, so we can apply the same conclusions to the missing data if we can conclude that this is also MAR."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "cbb700c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "missing_path_id = students[students[['current_career_path_id']].isnull().any(axis=1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "4c4cae67",
   "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>age</th>\n",
       "      <th>age_group</th>\n",
       "      <th>email</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>Norene Dalton</td>\n",
       "      <td>1976-04-30</td>\n",
       "      <td>F</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.0</td>\n",
       "      <td>40</td>\n",
       "      <td>norene_dalton9509@hmail.com</td>\n",
       "      <td>130 Wishing Essex</td>\n",
       "      <td>Branch</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>13616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>Sofia van Steenbergen</td>\n",
       "      <td>1990-02-21</td>\n",
       "      <td>N</td>\n",
       "      <td>7.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.0</td>\n",
       "      <td>30</td>\n",
       "      <td>vansteenbergen8482@inlook.com</td>\n",
       "      <td>634 Clear Barn Dell</td>\n",
       "      <td>Beaman</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>33288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>31</td>\n",
       "      <td>Christoper Warner</td>\n",
       "      <td>1989-12-28</td>\n",
       "      <td>M</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.0</td>\n",
       "      <td>30</td>\n",
       "      <td>warner5906@coldmail.com</td>\n",
       "      <td>556 Stony Highlands</td>\n",
       "      <td>Drain</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>01973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>50</td>\n",
       "      <td>Antony Horne</td>\n",
       "      <td>1996-05-29</td>\n",
       "      <td>M</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26.0</td>\n",
       "      <td>20</td>\n",
       "      <td>antony577@coldmail.com</td>\n",
       "      <td>P.O. Box 78685</td>\n",
       "      <td>Lenox</td>\n",
       "      <td>Texas</td>\n",
       "      <td>15516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>55</td>\n",
       "      <td>Omar Bunk</td>\n",
       "      <td>1955-11-08</td>\n",
       "      <td>M</td>\n",
       "      <td>3.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66.0</td>\n",
       "      <td>60</td>\n",
       "      <td>omar1245@coldmail.com</td>\n",
       "      <td>445 Dale Hollow</td>\n",
       "      <td>Vermont village</td>\n",
       "      <td>South Carolina</td>\n",
       "      <td>28329</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    uuid                   name         dob sex  job_id  num_course_taken  \\\n",
       "15    16          Norene Dalton  1976-04-30   F     6.0               0.0   \n",
       "19    20  Sofia van Steenbergen  1990-02-21   N     7.0              13.0   \n",
       "30    31      Christoper Warner  1989-12-28   M     2.0               5.0   \n",
       "49    50           Antony Horne  1996-05-29   M     3.0               2.0   \n",
       "54    55              Omar Bunk  1955-11-08   M     3.0              14.0   \n",
       "\n",
       "    current_career_path_id  time_spent_hrs   age  age_group  \\\n",
       "15                     NaN             NaN  46.0         40   \n",
       "19                     NaN             NaN  32.0         30   \n",
       "30                     NaN             NaN  32.0         30   \n",
       "49                     NaN             NaN  26.0         20   \n",
       "54                     NaN             NaN  66.0         60   \n",
       "\n",
       "                            email               street              city  \\\n",
       "15    norene_dalton9509@hmail.com    130 Wishing Essex            Branch   \n",
       "19  vansteenbergen8482@inlook.com  634 Clear Barn Dell            Beaman   \n",
       "30        warner5906@coldmail.com  556 Stony Highlands             Drain   \n",
       "49         antony577@coldmail.com       P.O. Box 78685             Lenox   \n",
       "54          omar1245@coldmail.com      445 Dale Hollow   Vermont village   \n",
       "\n",
       "              state zip_code  \n",
       "15             Ohio    13616  \n",
       "19          Georgia    33288  \n",
       "30         Illinois    01973  \n",
       "49            Texas    15516  \n",
       "54   South Carolina    28329  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_path_id.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0196574",
   "metadata": {},
   "source": [
    "The most interesting thing to note is that `time_spent_hrs` is also missing with the first 5 rows of missing `current_career_path_id`. This is worth investigating further because it's possible that `current_career_path_id` and  `time_spent_hrs` are structurally missing - the student is not currently taking any classes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "62e7b86c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 451 entries, 15 to 4974\n",
      "Data columns (total 15 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   uuid                    451 non-null    int64  \n",
      " 1   name                    451 non-null    object \n",
      " 2   dob                     451 non-null    object \n",
      " 3   sex                     451 non-null    object \n",
      " 4   job_id                  451 non-null    float64\n",
      " 5   num_course_taken        451 non-null    float64\n",
      " 6   current_career_path_id  0 non-null      float64\n",
      " 7   time_spent_hrs          0 non-null      float64\n",
      " 8   age                     451 non-null    float64\n",
      " 9   age_group               451 non-null    int64  \n",
      " 10  email                   451 non-null    object \n",
      " 11  street                  451 non-null    object \n",
      " 12  city                    451 non-null    object \n",
      " 13  state                   451 non-null    object \n",
      " 14  zip_code                451 non-null    object \n",
      "dtypes: float64(5), int64(2), object(8)\n",
      "memory usage: 56.4+ KB\n"
     ]
    }
   ],
   "source": [
    "missing_path_id.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "642e5dfc",
   "metadata": {},
   "source": [
    "As hypothesized, it seems that `time_spent_hrs` is null wherever `current_career_path_id` is too. This would mean that both are structually missing. To resolve this, I will set `current_career_path_id` to a new id that will indicate no current career path, and I will set `time_spent_hrs` to 0 to indicate no hours spent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "14facb4c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 1.,  8.,  9.,  3.,  6., 10.,  5., nan,  4.,  7.,  2.])"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "students['current_career_path_id'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "ffec07c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "students['current_career_path_id'] = np.where(students['current_career_path_id'].isnull(), 0, students['current_career_path_id'])\n",
    "students['time_spent_hrs'] = np.where(students['time_spent_hrs'].isnull(), 0, students['time_spent_hrs'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "c8cd0bcc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4744 entries, 0 to 4999\n",
      "Data columns (total 15 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   uuid                    4744 non-null   int64  \n",
      " 1   name                    4744 non-null   object \n",
      " 2   dob                     4744 non-null   object \n",
      " 3   sex                     4744 non-null   object \n",
      " 4   job_id                  4744 non-null   float64\n",
      " 5   num_course_taken        4744 non-null   float64\n",
      " 6   current_career_path_id  4744 non-null   float64\n",
      " 7   time_spent_hrs          4744 non-null   float64\n",
      " 8   age                     4744 non-null   float64\n",
      " 9   age_group               4744 non-null   int64  \n",
      " 10  email                   4744 non-null   object \n",
      " 11  street                  4744 non-null   object \n",
      " 12  city                    4744 non-null   object \n",
      " 13  state                   4744 non-null   object \n",
      " 14  zip_code                4744 non-null   object \n",
      "dtypes: float64(5), int64(2), object(8)\n",
      "memory usage: 593.0+ KB\n"
     ]
    }
   ],
   "source": [
    "students.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9012b4b5",
   "metadata": {},
   "source": [
    "# Working with the `career_paths` table\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "6d72b176",
   "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>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",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>frontend engineer</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>iOS developer</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>android developer</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>machine learning engineer</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>ux/ui designer</td>\n",
       "      <td>15</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\n",
       "5               6          frontend engineer                 20\n",
       "6               7              iOS developer                 27\n",
       "7               8          android developer                 27\n",
       "8               9  machine learning engineer                 35\n",
       "9              10             ux/ui designer                 15"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(career_paths)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "70e0bccc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<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: 368.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "career_paths.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e58f5c51",
   "metadata": {},
   "source": [
    "This table is relatively small compared to the `students` table. Taking a look at the full table, and sanity checking with the `.info()` function shows that this table doesn't require much. However, since we added in a new `career_path_id` to account for students who are not taking a career path, we need to add this to the database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "913aeda3",
   "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>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",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>frontend engineer</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>iOS developer</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>android developer</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>machine learning engineer</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>ux/ui designer</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0</td>\n",
       "      <td>not applicable</td>\n",
       "      <td>0</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\n",
       "5                6          frontend engineer                 20\n",
       "6                7              iOS developer                 27\n",
       "7                8          android developer                 27\n",
       "8                9  machine learning engineer                 35\n",
       "9               10             ux/ui designer                 15\n",
       "10               0             not applicable                  0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "not_applicable = {'career_path_id': 0,\n",
    "                  'career_path_name': 'not applicable',\n",
    "                  'hours_to_complete': 0}\n",
    "    \n",
    "career_paths.loc[len(career_paths)] = not_applicable\n",
    "display(career_paths)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21b7fd5e",
   "metadata": {},
   "source": [
    "# Working with `student_jobs` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "fc002fa2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>job_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",
       "    <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",
       "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\n",
       "10       3  software developer      110000\n",
       "11       4            creative       66000\n",
       "12       5  financial services      135000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(student_jobs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "d625b756",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<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: 440.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "student_jobs.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5451107",
   "metadata": {},
   "source": [
    "Similar to the `cademycode_courses` table,  the `cademycode_student_jobs` table is small compared to the students table. Taking a look at the full table, and sanity checking with the .info() function shows that there are duplicates in this table based off of the `job_id` column. This is the only issue that needs to be addressed based off the inspection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "3acd5607",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>job_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": [
    "student_jobs.drop_duplicates(inplace=True)\n",
    "display(student_jobs)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66b8f70c",
   "metadata": {},
   "source": [
    "# Joining tables\n",
    "Before we begin joining - let's convert the ids to integers now that the null values have been resolved."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "881513fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "students['job_id'] = students['job_id'].astype(int)\n",
    "students['current_career_path_id'] = students['current_career_path_id'].astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24130b5f",
   "metadata": {},
   "source": [
    "Now we can join them together:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "32b214bd",
   "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>age</th>\n",
       "      <th>age_group</th>\n",
       "      <th>email</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>career_path_id</th>\n",
       "      <th>career_path_name</th>\n",
       "      <th>hours_to_complete</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>Annabelle Avery</td>\n",
       "      <td>1943-07-03</td>\n",
       "      <td>F</td>\n",
       "      <td>7</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1</td>\n",
       "      <td>4.99</td>\n",
       "      <td>79.0</td>\n",
       "      <td>70</td>\n",
       "      <td>annabelle_avery9376@woohoo.com</td>\n",
       "      <td>303 N Timber Key</td>\n",
       "      <td>Irondale</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>84736</td>\n",
       "      <td>1</td>\n",
       "      <td>data scientist</td>\n",
       "      <td>20</td>\n",
       "      <td>HR</td>\n",
       "      <td>80000</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</td>\n",
       "      <td>5.0</td>\n",
       "      <td>8</td>\n",
       "      <td>4.40</td>\n",
       "      <td>31.0</td>\n",
       "      <td>30</td>\n",
       "      <td>rubio6772@hmail.com</td>\n",
       "      <td>767 Crescent Fair</td>\n",
       "      <td>Shoals</td>\n",
       "      <td>Indiana</td>\n",
       "      <td>37439</td>\n",
       "      <td>8</td>\n",
       "      <td>android developer</td>\n",
       "      <td>27</td>\n",
       "      <td>HR</td>\n",
       "      <td>80000</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</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8</td>\n",
       "      <td>6.74</td>\n",
       "      <td>32.0</td>\n",
       "      <td>30</td>\n",
       "      <td>hosea_dale8084@coldmail.com</td>\n",
       "      <td>P.O. Box 41269</td>\n",
       "      <td>St. Bonaventure</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>83637</td>\n",
       "      <td>8</td>\n",
       "      <td>android developer</td>\n",
       "      <td>27</td>\n",
       "      <td>HR</td>\n",
       "      <td>80000</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</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9</td>\n",
       "      <td>12.31</td>\n",
       "      <td>33.0</td>\n",
       "      <td>30</td>\n",
       "      <td>kirk4005@hmail.com</td>\n",
       "      <td>517 SE Wintergreen Isle</td>\n",
       "      <td>Lane</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>82242</td>\n",
       "      <td>9</td>\n",
       "      <td>machine learning engineer</td>\n",
       "      <td>35</td>\n",
       "      <td>education</td>\n",
       "      <td>61000</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</td>\n",
       "      <td>14.0</td>\n",
       "      <td>3</td>\n",
       "      <td>5.64</td>\n",
       "      <td>58.0</td>\n",
       "      <td>50</td>\n",
       "      <td>alexander9810@hmail.com</td>\n",
       "      <td>18 Cinder Cliff</td>\n",
       "      <td>Doyles borough</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>73737</td>\n",
       "      <td>3</td>\n",
       "      <td>data analyst</td>\n",
       "      <td>12</td>\n",
       "      <td>HR</td>\n",
       "      <td>80000</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               6.0   \n",
       "1     2      Micah Rubio  1991-02-07   M       7               5.0   \n",
       "2     3       Hosea Dale  1989-12-07   M       7               8.0   \n",
       "3     4     Mariann Kirk  1988-07-31   F       6               7.0   \n",
       "4     5  Lucio Alexander  1963-08-31   M       7              14.0   \n",
       "\n",
       "   current_career_path_id  time_spent_hrs   age  age_group  \\\n",
       "0                       1            4.99  79.0         70   \n",
       "1                       8            4.40  31.0         30   \n",
       "2                       8            6.74  32.0         30   \n",
       "3                       9           12.31  33.0         30   \n",
       "4                       3            5.64  58.0         50   \n",
       "\n",
       "                            email                   street              city  \\\n",
       "0  annabelle_avery9376@woohoo.com         303 N Timber Key          Irondale   \n",
       "1             rubio6772@hmail.com        767 Crescent Fair            Shoals   \n",
       "2     hosea_dale8084@coldmail.com           P.O. Box 41269   St. Bonaventure   \n",
       "3              kirk4005@hmail.com  517 SE Wintergreen Isle              Lane   \n",
       "4         alexander9810@hmail.com          18 Cinder Cliff    Doyles borough   \n",
       "\n",
       "           state zip_code  career_path_id           career_path_name  \\\n",
       "0      Wisconsin    84736               1             data scientist   \n",
       "1        Indiana    37439               8          android developer   \n",
       "2       Virginia    83637               8          android developer   \n",
       "3       Arkansas    82242               9  machine learning engineer   \n",
       "4   Rhode Island    73737               3               data analyst   \n",
       "\n",
       "   hours_to_complete job_category  avg_salary  \n",
       "0                 20           HR       80000  \n",
       "1                 27           HR       80000  \n",
       "2                 27           HR       80000  \n",
       "3                 35    education       61000  \n",
       "4                 12           HR       80000  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df = students.merge(career_paths, left_on='current_career_path_id', right_on='career_path_id', how='left')\n",
    "final_df = final_df.merge(student_jobs, on='job_id', how='left')\n",
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "ab7a4366",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4744 entries, 0 to 4743\n",
      "Data columns (total 20 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   uuid                    4744 non-null   int64  \n",
      " 1   name                    4744 non-null   object \n",
      " 2   dob                     4744 non-null   object \n",
      " 3   sex                     4744 non-null   object \n",
      " 4   job_id                  4744 non-null   int64  \n",
      " 5   num_course_taken        4744 non-null   float64\n",
      " 6   current_career_path_id  4744 non-null   int64  \n",
      " 7   time_spent_hrs          4744 non-null   float64\n",
      " 8   age                     4744 non-null   float64\n",
      " 9   age_group               4744 non-null   int64  \n",
      " 10  email                   4744 non-null   object \n",
      " 11  street                  4744 non-null   object \n",
      " 12  city                    4744 non-null   object \n",
      " 13  state                   4744 non-null   object \n",
      " 14  zip_code                4744 non-null   object \n",
      " 15  career_path_id          4744 non-null   int64  \n",
      " 16  career_path_name        4744 non-null   object \n",
      " 17  hours_to_complete       4744 non-null   int64  \n",
      " 18  job_category            4744 non-null   object \n",
      " 19  avg_salary              4744 non-null   int64  \n",
      "dtypes: float64(3), int64(7), object(10)\n",
      "memory usage: 778.3+ KB\n"
     ]
    }
   ],
   "source": [
    "final_df.info()\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33fb3dbd",
   "metadata": {},
   "source": [
    "# Upsert cleansed and missing data tables to a new SQLite DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "013e3edb",
   "metadata": {},
   "outputs": [],
   "source": [
    "sqlite_connection = sqlite3.connect('cademycode_cleansed.db')\n",
    "final_df.to_sql('cademycode_aggregated', sqlite_connection, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "a8323e20",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_df = pd.read_sql_query(\"SELECT * FROM cademycode_aggregated\", sqlite_connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "0a15cee4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4744 entries, 0 to 4743\n",
      "Data columns (total 20 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   uuid                    4744 non-null   int64  \n",
      " 1   name                    4744 non-null   object \n",
      " 2   dob                     4744 non-null   object \n",
      " 3   sex                     4744 non-null   object \n",
      " 4   job_id                  4744 non-null   int64  \n",
      " 5   num_course_taken        4744 non-null   float64\n",
      " 6   current_career_path_id  4744 non-null   int64  \n",
      " 7   time_spent_hrs          4744 non-null   float64\n",
      " 8   age                     4744 non-null   float64\n",
      " 9   age_group               4744 non-null   int64  \n",
      " 10  email                   4744 non-null   object \n",
      " 11  street                  4744 non-null   object \n",
      " 12  city                    4744 non-null   object \n",
      " 13  state                   4744 non-null   object \n",
      " 14  zip_code                4744 non-null   object \n",
      " 15  career_path_id          4744 non-null   int64  \n",
      " 16  career_path_name        4744 non-null   object \n",
      " 17  hours_to_complete       4744 non-null   int64  \n",
      " 18  job_category            4744 non-null   object \n",
      " 19  avg_salary              4744 non-null   int64  \n",
      "dtypes: float64(3), int64(7), object(10)\n",
      "memory usage: 741.4+ KB\n"
     ]
    }
   ],
   "source": [
    "db_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "6d94e3ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "missing_data.to_sql('incomplete_data', sqlite_connection, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "02114952",
   "metadata": {},
   "outputs": [],
   "source": [
    "missing_df = pd.read_sql_query(\"SELECT * FROM incomplete_data\", sqlite_connection)\n",
    "sqlite_connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "443639a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_df.to_csv('cademycode_cleansed.csv')"
   ]
  }
 ],
 "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}