5A.ML_Feature_Creation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center>\n",
    "<img style=\"float: center;\" src=\"images/CI_horizontal.png\" width=\"400\">\n",
    "</center>\n",
    "<center>\n",
    "    <span style=\"font-size: 1.5em;\">\n",
    "        <a href='https://www.coleridgeinitiative.org'>Website</a>\n",
    "    </span>\n",
    "</center>\n",
    "\n",
    "<a href=\"https://doi.org/10.5281/zenodo.6324207\"><img src=\"https://zenodo.org/badge/DOI/10.5281/zenodo.6324207.svg\" alt=\"DOI\"></a>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center> Joshua Edelmann, Benjamin Feder, Nathan Barrett </center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Machine Learning Part 1: Data Preparation**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Whereas unsupervised machine learning can be used to understand underlying groupings within the data, supervised machine learning focuses on prediction of a certain outcome. With prediction, we will formulate our model based on training data, and then evaluate the results of the model on a separate test set. Similar to unsupervised machine learning, though, before running any algorithm, we must think critically about the features we want to include in our model. The variables we consider as features do not have to be limited to the variables in the provided tables, as there may be features that can be developed by leveraging variables across multiple sources. Additionally, we will want to make sure our input information is in a specific format so that it can be seamlessly used in modeling. This notebook will prepare the training and testing tables and the next notebook will cover prediction algorithms.\n",
    "\n",
    "To this point, we have already tracked a cohort of bachelor's degree earners in Texas and explored their employment outcomes. We created various outcome measures and compared them amongst subgroups such as major and gender. Now, we will focus on one outcome measure, the presence of wages in the fourth quarter after graduation, and create a handful of features as our predictors. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Learning Objectives**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will create two separate tables to predict future employment in the fourth quarter after graduation. Our training set will consist of 2017 calendar year bachelor's degree recipients in Texas, and our testing set will include 2018 recipients. Eventually, both sets will contain the same features as well as the future outcome measure. One row in these tables will correspond to one graduate."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After you finish this notebook, you should know:\n",
    "- How to create the label (outcome variable, Y)\n",
    "- How to create features (predictor variables, X)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Import Packages and Set Up**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database interaction imports\n",
    "library(odbc)\n",
    "\n",
    "# For data manipulation/visualization\n",
    "library(tidyverse)\n",
    "\n",
    "# For faster date conversions\n",
    "library(lubridate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to the server\n",
    "con <- DBI::dbConnect(odbc::odbc(),\n",
    "                     Driver = \"SQL Server\",\n",
    "                     Server = \"msssql01.c7bdq4o2yhxo.us-gov-west-1.rds.amazonaws.com\",\n",
    "                     Trusted_Connection = \"True\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Feature Creation**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We hope to create and/or identify the following features to include in our supervised machine learning example for each individual in both the training and testing sets:\n",
    "\n",
    "- `gradtypi`, the type of institution from which the student graduated\n",
    "- `gradmaj`, the 2-digit CIP code of the student\n",
    "- `instregion`, the region of the institution in Texas\n",
    "- `transfer_ind`, if the student was a transfer student sometime in the 2 years before graduation\n",
    "- `total_sems`, the total number of semesters enrolled in the 2 years before graduation\n",
    "- `total_hours`, the total number of credit hours enrolled in the 2 years before graduation\n",
    "- `employed_count`, the number of individuals in the most recently measured cohort that were employed in their fourth quarter after graduation by `gradmaj` and `instregion`\n",
    "- `employed_prop`, the proportion of individuals in the most recently measured cohort that were employed in their fourth quarter after graduation by `gradmaj` within `instregion`.\n",
    "\n",
    "Notice how these features can be split into two groups:\n",
    "1. Individual-based characteristics\n",
    "2. Information from the past\n",
    "\n",
    "> We will demonstrate how to construct these features for the training set of 2017 calendar year bachelor's degree recipients in Texas. We have already saved a permanent table, `grads17_dated`, with these individuals by slightly modifying the code used to create `grads15_dated` in the Data Exploration: Wages notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **Individual-Based Characteristics**\n",
    "\n",
    "The individual-based characteristics can be identified directly from the individual, and do not rely on either past or future information to fill. Almost all of our desired features are individual-based, as all but `employed_count` and `employed_prop` can satisfy this constraint.\n",
    "\n",
    "We will be able to retrieve this information by starting with the base table of 2017 calendar year bachelor's degree recipients in Texas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see grads17_dated\n",
    "qry <- \"\n",
    "SELECT TOP 5 *\n",
    "FROM tr_tx_2021.dbo.grads17_dated\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Given the desired features, though, we will need to join `grads17_dated` to both the `enrollments` and `institutions` tables to provide more detail regarding the graduating institution and prior enrollment history for this cohort. To limit the enrollment information to just the previous two years before graduation, we will first create a variable that will eventually bound the enrollment information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create previous_2_years cutoff for each individual\n",
    "qry <- \"\n",
    "SELECT TOP 5 *, dateadd(yyyy ,-2, grad_date) as 'previous_2_years'\n",
    "FROM tr_tx_2021.dbo.grads17_dated cohort\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can then build out the query in a common table expression (CTE) to eventually take the final form.\n",
    "\n",
    "> Click [here](#final-query) to skip directly to the complete query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# limit to variables that will help calculate features \n",
    "# left join to enrollments and institutions to preserve all members of the cohort\n",
    "qry <- \"\n",
    "SELECT TOP 5 gradid, gradfice, grad_date, substring(gradmaj, 1, 2) as gradmaj, gradtypi, instregion, stufttr, stutotsch, stusem, stuyear, dateadd(yyyy ,-2, grad_date) as 'previous_2_years'\n",
    "FROM tr_tx_2021.dbo.grads17_dated cohort\n",
    "LEFT JOIN ds_tx_thecb.dbo.institutions inst\n",
    "ON cohort.gradfice = inst.instfice \n",
    "LEFT JOIN ds_tx_thecb.dbo.enrollments enroll\n",
    "ON cohort.gradid = enroll.studentid \n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add in new variable mapping quarters of enrollment to semesters\n",
    "# this will allow us to provide the 2 year cutoff\n",
    "qry <- \"\n",
    "SELECT TOP 5 gradid, gradfice, grad_date, substring(gradmaj, 1, 2) as gradmaj, gradtypi, instregion, stufttr, stutotsch, stusem, stuyear, dateadd(yyyy ,-2, grad_date) as 'previous_2_years',\n",
    "CASE \n",
    "    WHEN stusem = 2 THEN 4 \n",
    "    WHEN stusem = 1 THEN 10 \n",
    "    when stusem = 3 THEN 7 \n",
    "    ELSE 7 \n",
    "    END as new_month \n",
    "FROM tr_tx_2021.dbo.grads17_dated cohort\n",
    "LEFT JOIN ds_tx_thecb.dbo.institutions inst\n",
    "ON cohort.gradfice = inst.instfice \n",
    "LEFT JOIN ds_tx_thecb.dbo.enrollments enroll\n",
    "ON cohort.gradid = enroll.studentid\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# find dates of enrollments\n",
    "# note: in the enrollments file, sometimes the year is null, so the ordinary date conversion won't work\n",
    "qry <- \"\n",
    "with new_table as (\n",
    "    SELECT gradid, gradfice, grad_date, SUBSTRING(gradmaj, 1, 2) as gradmaj, gradtypi, instregion, stufttr, stutotsch, stusem, stuyear, dateadd(yyyy ,-2, grad_date) as 'previous_2_years',\n",
    "    CASE \n",
    "        WHEN stusem = 2 THEN 4 \n",
    "        WHEN stusem = 1 THEN 10 \n",
    "        when stusem = 3 THEN 7 \n",
    "        ELSE 7 \n",
    "        END as new_month \n",
    "    FROM tr_tx_2021.dbo.grads17_dated cohort\n",
    "    LEFT JOIN ds_tx_thecb.dbo.institutions inst\n",
    "    ON cohort.gradfice = inst.instfice \n",
    "    LEFT JOIN ds_tx_thecb.dbo.enrollments enroll\n",
    "    ON cohort.gradid = enroll.studentid \n",
    ")\n",
    "SELECT top 5 gradid, gradfice, gradtypi, grad_date, gradmaj, instregion, stufttr, previous_2_years, stutotsch, case \n",
    "WHEN stuyear is null THEN 0 \n",
    "ELSE CONVERT(datetime, CONCAT((new_month), '/', '01', '/', stuyear)) end as enroll_date\n",
    "FROM new_table\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter for all enrollments in those two years prior to graduation\n",
    "qry <- \"\n",
    "with new_table as (\n",
    "    SELECT gradid, gradfice, grad_date, SUBSTRING(gradmaj, 1, 2) as gradmaj, gradtypi, instregion, stufttr, stutotsch, stusem, stuyear, dateadd(yyyy ,-2, grad_date) as 'previous_2_years',\n",
    "    CASE \n",
    "        WHEN stusem = 2 THEN 4 \n",
    "        WHEN stusem = 1 THEN 10 \n",
    "        when stusem = 3 THEN 7 \n",
    "        ELSE 7 \n",
    "        END as new_month \n",
    "    FROM tr_tx_2021.dbo.grads17_dated cohort\n",
    "    LEFT JOIN ds_tx_thecb.dbo.institutions inst\n",
    "    ON cohort.gradfice = inst.instfice \n",
    "    LEFT JOIN ds_tx_thecb.dbo.enrollments enroll\n",
    "    ON cohort.gradid = enroll.studentid \n",
    "),\n",
    "combined as (\n",
    "    SELECT gradid, gradfice, gradtypi, grad_date, gradmaj, instregion, stufttr, previous_2_years, stutotsch, case \n",
    "    WHEN stuyear is null THEN 0 \n",
    "    ELSE CONVERT(datetime, CONCAT((new_month), '/', '01', '/', stuyear)) end as enroll_date\n",
    "    FROM new_table\n",
    ")\n",
    "SELECT TOP 5 *\n",
    "FROM combined\n",
    "WHERE previous_2_years <= enroll_date and enroll_date <= grad_date\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# within those two years, count the number of semesters and number of total credit hours\n",
    "# also look for the highest transfer indicator (anything above 000001 is a transfer)\n",
    "qry <- \"\n",
    "with new_table as (\n",
    "    SELECT gradid, gradfice, grad_date, SUBSTRING(gradmaj, 1, 2) as gradmaj, gradtypi, instregion, stufttr, stutotsch, stusem, stuyear, dateadd(yyyy ,-2, grad_date) as 'previous_2_years',\n",
    "    CASE \n",
    "        WHEN stusem = 2 THEN 4 \n",
    "        WHEN stusem = 1 THEN 10 \n",
    "        when stusem = 3 THEN 7 \n",
    "        ELSE 7 \n",
    "        END as new_month \n",
    "    FROM tr_tx_2021.dbo.grads17_dated cohort\n",
    "    LEFT JOIN ds_tx_thecb.dbo.institutions inst\n",
    "    ON cohort.gradfice = inst.instfice \n",
    "    LEFT JOIN ds_tx_thecb.dbo.enrollments enroll\n",
    "    ON cohort.gradid = enroll.studentid \n",
    "),\n",
    "combined as (\n",
    "    SELECT gradid, gradfice, gradtypi, grad_date, gradmaj, instregion, stufttr, previous_2_years, stutotsch, case \n",
    "    WHEN stuyear is null THEN 0 \n",
    "    ELSE CONVERT(datetime, CONCAT((new_month), '/', '01', '/', stuyear)) end as enroll_date\n",
    "    FROM new_table\n",
    ")\n",
    "SELECT TOP 5 gradid, gradtypi, gradmaj, instregion, COUNT(DISTINCT(enroll_date)) as total_sems, sum(stutotsch) as total_hours,\n",
    "CASE\n",
    "    WHEN max(stufttr) is null THEN 0\n",
    "    WHEN max(stufttr) = 000000 THEN 0 \n",
    "    WHEN max(stufttr) = 000001 THEN 0\n",
    "    ELSE 1\n",
    "    END as transfer_ind\n",
    "FROM combined\n",
    "WHERE previous_2_years <= enroll_date and enroll_date <= grad_date\n",
    "GROUP BY gradid, instregion, gradtypi, gradmaj\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# those that didn't have enrollment info in this time period are filtered out in agg_filtered\n",
    "# left join agg_filtered results back to combined, which has everyone's info\n",
    "qry <- \"\n",
    "with new_table as (\n",
    "    SELECT gradid, gradfice, grad_date, SUBSTRING(gradmaj, 1, 2) as gradmaj, gradtypi, instregion, stufttr, stutotsch, stusem, stuyear, dateadd(yyyy ,-2, grad_date) as 'previous_2_years',\n",
    "    CASE \n",
    "        WHEN stusem = 2 THEN 4 \n",
    "        WHEN stusem = 1 THEN 10 \n",
    "        when stusem = 3 THEN 7 \n",
    "        ELSE 7 \n",
    "        END as new_month \n",
    "    FROM tr_tx_2021.dbo.grads17_dated cohort\n",
    "    LEFT JOIN ds_tx_thecb.dbo.institutions inst\n",
    "    ON cohort.gradfice = inst.instfice \n",
    "    LEFT JOIN ds_tx_thecb.dbo.enrollments enroll\n",
    "    ON cohort.gradid = enroll.studentid \n",
    "),\n",
    "combined as (\n",
    "    SELECT gradid, gradfice, gradtypi, grad_date, gradmaj, instregion, stufttr, previous_2_years, stutotsch, case \n",
    "    WHEN stuyear is null THEN 0 \n",
    "    ELSE CONVERT(datetime, CONCAT((new_month), '/', '01', '/', stuyear)) end as enroll_date\n",
    "    FROM new_table\n",
    "),\n",
    "agg_filtered as (\n",
    "    SELECT gradid, gradtypi, gradmaj, instregion, COUNT(DISTINCT(enroll_date)) as total_sems, sum(stutotsch) as total_hours,\n",
    "    CASE\n",
    "        WHEN max(stufttr) is null THEN 0\n",
    "        WHEN max(stufttr) = 000000 THEN 0 \n",
    "        WHEN max(stufttr) = 000001 THEN 0\n",
    "    ELSE 1\n",
    "    END as transfer_ind\n",
    "    FROM combined\n",
    "    WHERE previous_2_years <= enroll_date and enroll_date <= grad_date\n",
    "    GROUP BY gradid, instregion, gradtypi, gradmaj\n",
    ")\n",
    "SELECT TOP 5 *\n",
    "FROM combined c\n",
    "LEFT JOIN agg_filtered a\n",
    "ON a.gradid = c.gradid\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for those who were joined back in, set calculated variables in agg_filtered to 0 (total_sems, total_hours, transfer_ind)\n",
    "# also only take one record per person using distinct\n",
    "qry <- \"\n",
    "with new_table as (\n",
    "    SELECT gradid, gradfice, grad_date, SUBSTRING(gradmaj, 1, 2) as gradmaj, gradtypi, instregion, stufttr, stutotsch, stusem, stuyear, dateadd(yyyy ,-2, grad_date) as 'previous_2_years',\n",
    "    CASE \n",
    "        WHEN stusem = 2 THEN 4 \n",
    "        WHEN stusem = 1 THEN 10 \n",
    "        when stusem = 3 THEN 7 \n",
    "        ELSE 7 \n",
    "        END as new_month \n",
    "    FROM tr_tx_2021.dbo.grads17_dated cohort\n",
    "    LEFT JOIN ds_tx_thecb.dbo.institutions inst\n",
    "    ON cohort.gradfice = inst.instfice \n",
    "    LEFT JOIN ds_tx_thecb.dbo.enrollments enroll\n",
    "    ON cohort.gradid = enroll.studentid \n",
    "),\n",
    "combined as (\n",
    "    SELECT gradid, gradfice, gradtypi, grad_date, gradmaj, instregion, stufttr, previous_2_years, stutotsch, case \n",
    "    WHEN stuyear is null THEN 0 \n",
    "    ELSE CONVERT(datetime, CONCAT((new_month), '/', '01', '/', stuyear)) end as enroll_date\n",
    "    FROM new_table\n",
    "),\n",
    "agg_filtered as (\n",
    "    SELECT gradid, gradtypi, gradmaj, instregion, COUNT(DISTINCT(enroll_date)) as total_sems, sum(stutotsch) as total_hours,\n",
    "    CASE\n",
    "        WHEN max(stufttr) is null THEN 0\n",
    "        WHEN max(stufttr) = 000000 THEN 0 \n",
    "        WHEN max(stufttr) = 000001 THEN 0\n",
    "        ELSE 1\n",
    "        END as transfer_ind\n",
    "    FROM combined\n",
    "    WHERE previous_2_years <= enroll_date and enroll_date <= grad_date\n",
    "    GROUP BY gradid, instregion, gradtypi, gradmaj\n",
    ")\n",
    "SELECT DISTINCT TOP 5 c.gradid, c.gradtypi, SUBSTRING(c.gradmaj, 1, 2) as gradmaj, c.instregion,\n",
    "    CASE WHEN a.transfer_ind is null THEN 0 ELSE a.transfer_ind end as transfer_ind,\n",
    "    CASE WHEN a.total_sems is null THEN 0 ELSE a.total_sems end as total_sems,\n",
    "    CASE WHEN a.total_hours is null THEN 0 ELSE a.total_hours end as total_hours\n",
    "FROM combined c\n",
    "LEFT JOIN agg_filtered a\n",
    "ON a.gradid = c.gradid\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='final-query'></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save to cohort_2017 data frame\n",
    "qry <- \"\n",
    "with new_table as (\n",
    "    SELECT gradid, gradfice, grad_date, SUBSTRING(gradmaj, 1, 2) as gradmaj, gradtypi, instregion, stufttr, stutotsch, stusem, stuyear, dateadd(yyyy ,-2, grad_date) as 'previous_2_years',\n",
    "    CASE \n",
    "        WHEN stusem = 2 THEN 4 \n",
    "        WHEN stusem = 1 THEN 10 \n",
    "        when stusem = 3 THEN 7 \n",
    "        ELSE 7 \n",
    "        END as new_month \n",
    "    FROM tr_tx_2021.dbo.grads17_dated cohort\n",
    "    LEFT JOIN ds_tx_thecb.dbo.institutions inst\n",
    "    ON cohort.gradfice = inst.instfice \n",
    "    LEFT JOIN ds_tx_thecb.dbo.enrollments enroll\n",
    "    ON cohort.gradid = enroll.studentid \n",
    "),\n",
    "combined as (\n",
    "    SELECT gradid, gradfice, gradtypi, grad_date, gradmaj, instregion, stufttr, previous_2_years, stutotsch, case \n",
    "    WHEN stuyear is null then 0 \n",
    "    ELSE CONVERT(datetime, CONCAT((new_month), '/', '01', '/', stuyear)) end as enroll_date\n",
    "    FROM new_table\n",
    "),\n",
    "agg_filtered as (\n",
    "    SELECT gradid, gradtypi, gradmaj, instregion, COUNT(DISTINCT(enroll_date)) as total_sems, sum(stutotsch) as total_hours,\n",
    "    CASE\n",
    "        WHEN max(stufttr) is null THEN 0\n",
    "        WHEN max(stufttr) = 000000 THEN 0 \n",
    "        WHEN max(stufttr) = 000001 THEN 0\n",
    "    ELSE 1\n",
    "    END as transfer_ind\n",
    "    FROM combined\n",
    "    WHERE previous_2_years <= enroll_date and enroll_date <= grad_date\n",
    "    GROUP BY gradid, instregion, gradtypi, gradmaj\n",
    ")\n",
    "SELECT DISTINCT c.gradid, c.gradtypi, SUBSTRING(c.gradmaj, 1, 2) as gradmaj, c.instregion,\n",
    "    CASE WHEN a.transfer_ind is null THEN 0 ELSE a.transfer_ind end as transfer_ind,\n",
    "    CASE WHEN a.total_sems is null THEN 0 ELSE a.total_sems end as total_sems,\n",
    "    CASE WHEN a.total_hours is null THEN 0 ELSE a.total_hours end as total_hours\n",
    "FROM combined c\n",
    "LEFT JOIN agg_filtered a\n",
    "ON a.gradid = c.gradid\n",
    "\"\n",
    "cohort_2017 <- dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To confirm we did not double count or ignore any individuals from the original cohort, we can compare the number of individuals in `grads17_dated` with the amount of individuals and rows in `cohort_2017`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# number of individuals in original cohort\n",
    "qry <- \"\n",
    "SELECT COUNT(DISTINCT(gradid)) FROM tr_tx_2021.dbo.grads17_dated\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# number of rows and individuals in indvidual-based variables\n",
    "cohort_2017 %>%\n",
    "    summarize(\n",
    "        number_rows = n(),\n",
    "        number_individuals = n_distinct(gradid)\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Information from the Past\n",
    "\n",
    "Recall that there were 2 more features we wanted to create to help us predict our outcome, `employed_count` and `employed_prop`. If we were to try to predict if each individual in the 2017 cohort would be employed in their fourth quarter after graduation, we could only use information avaialable to us at the time of their graduation. Given that our outcome variable is in the fourth quarter after graduation, we could not analyze employment outcomes from the 2016 cohort, as we would be working with incomplete information (Four quarters after Fall 2016 is Fall 2017 and there are Spring 2017 graduates). \n",
    "\n",
    "Therefore, the most recent information we can utilize would be employment outcomes from the 2015 cohort. We purposely include these two features to begin to understand the different labor market dynamics in the regions of Texas for recent bachelor's degree recipients. \n",
    "\n",
    "Recall that the employment outcomes for the 2015 calendar year graduates are stored in the table `nb_cohort_wages_link`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see nb_cohort_wages_link table\n",
    "qry <- \"\n",
    "SELECT TOP 5 *\n",
    "FROM tr_tx_2021.dbo.nb_cohort_wages_link\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can add in the insitution-level information, as well as restrict employment to the fourth quarter after graduation with following query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get information on graduating institution\n",
    "qry <- \"\n",
    "SELECT TOP 5 w.gradid, w.gradtypi, substring(w.gradmaj,1, 2) as gradmaj, i.instregion, w.wage \n",
    "FROM tr_tx_2021.dbo.nb_cohort_wages_link w \n",
    "LEFT JOIN ds_tx_thecb.dbo.institutions i \n",
    "ON w.gradfice = i.instfice \n",
    "WHERE dateadd(quarter, 4, grad_date) = job_date\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can then aggregate this information in a CTE to find the number of individuals from the 2015 cohort that experienced employment in their fourth quarter after graduation by the region of their graduating institution and their degree."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# aggregate to gradmaj and instregion and count the number of indivdiuals within these combinations\n",
    "qry <- \"\n",
    "with init as (\n",
    "    SELECT w.gradid, w.gradtypi, SUBSTRING(w.gradmaj,1, 2) as gradmaj, i.instregion, w.wage \n",
    "    FROM tr_tx_2021.dbo.nb_cohort_wages_link w \n",
    "    LEFT JOIN ds_tx_thecb.dbo.institutions i \n",
    "    ON w.gradfice = i.instfice \n",
    "    WHERE dateadd(quarter, 4, grad_date) = job_date\n",
    ")\n",
    "SELECT COUNT(DISTINCT(gradid)) as employed_count, gradmaj, instregion \n",
    "FROM init \n",
    "GROUP BY gradmaj, instregion\n",
    "\"\n",
    "additional_features <- dbGetQuery(con, qry)\n",
    "\n",
    "head(additional_features)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add in employed_prop\n",
    "additional_features <- additional_features %>%\n",
    "    group_by(instregion) %>% \n",
    "    mutate(\n",
    "        employed_prop = round(employed_count/sum(employed_count), digits = 4)\n",
    "    ) %>%\n",
    "    ungroup()\n",
    "\n",
    "head(additional_features)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can then join this aggregated information from the 2015 cohort back to our 2017 cohort on the `gradmaj` and `instregion` variables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# join features\n",
    "training_set <- cohort_2017 %>% \n",
    "    left_join(\n",
    "        additional_features, \n",
    "        by=c(\"instregion\" = \"instregion\", \"gradmaj\" = \"gradmaj\")\n",
    "    )\n",
    "\n",
    "training_set %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# some graduates have major/institution region combos that didn't have any employment in 2015 so they will be set to 0\n",
    "training_set <- training_set %>%\n",
    "    mutate(\n",
    "        employed_count = ifelse(is.na(employed_count), 0, employed_count),\n",
    "        employed_prop = ifelse(is.na(employed_prop), 0, employed_prop)\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Outcome Variable**\n",
    "\n",
    "With the features stored in `training_set`, we just need to add one last variable to the data frame before we begin running supervised machine learning algorithms: if they ended up as employed in their fourth quarter after graduation.\n",
    "\n",
    "We have created a de-duplicated, dated, and subset version of the wage records to join to the `grads17_dated` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# link 2017 cohort to wage records in 4th quarter after graduation\n",
    "# only taking distinct so that we don't double-count anyone\n",
    "qry <- \"\n",
    "SELECT DISTINCT cohort.gradid, 1 as wage_ind\n",
    "FROM tr_tx_2021.dbo.grads17_dated cohort\n",
    "JOIN tr_tx_2021.dbo.wage_record17_dated_dedup w\n",
    "ON cohort.gradid  = w.ssn\n",
    "WHERE dateadd(quarter, 4, grad_date) = job_date\n",
    "\"\n",
    "\n",
    "training_wages <- dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see amount of individuals in training_wages\n",
    "# note that this is not everyone in the cohort, just those that did experience employment\n",
    "training_wages %>% \n",
    "    summarize(\n",
    "        number_individuals = n_distinct(gradid)\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# find those that didn't show up in wage records\n",
    "# give them a 0 for wage indicator\n",
    "training_no_wages <- training_set %>%\n",
    "    anti_join(training_wages, by = \"gradid\") %>%\n",
    "    distinct(gradid)  %>% \n",
    "    mutate(\n",
    "        wage_ind = 0\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# combine two data frames with outcomes\n",
    "training_outcome <- training_wages %>%\n",
    "    rbind(training_no_wages)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# join back to training features by gradid\n",
    "full_training_set <- training_set %>% \n",
    "    inner_join(training_outcome, by = (\"gradid\" = \"gradid\")) \n",
    "\n",
    "head(full_training_set)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# make sure we didn't lose anyone in inner_join (aka everyone has an outcome variable)\n",
    "full_training_set %>%\n",
    "    summarize(\n",
    "        number_rows = n(),\n",
    "        number_individuals = n_distinct(gradid)\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We already saved the training set as `nb_training_set` in the `tr_tx_2021` database. The process for creating the testing set follows the same steps, just one year later. You can test your code by recreating `full_training_set` for the 2018 cohort and comparing it to the permanent table `nb_testing_set`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## References\n",
    "\n",
    "Lou, Tian. (2022, March 18). Machine Learning Model Development and Feature Creation Using Illinois Unemployment Insurance Data. Zenodo. https://doi.org/10.5281/zenodo.6368905"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}