1B.Data_Exploration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center> <img style=\"float: center;\" src=\"images/CI_horizontal.png\" width=\"450\">\n",
    "<center>\n",
    "    <span style=\"font-size: 1.5em;\">\n",
    "        <a href='https://www.coleridgeinitiative.org'>Website</a>\n",
    "    </span> \n",
    "    <br>\n",
    "    Alex Gorbunov, Nathan Barrett, Benjamin Feder </center>\n",
    "    <a href=\"https://doi.org/10.5281/zenodo.6407252\"><img src=\"https://zenodo.org/badge/DOI/10.5281/zenodo.6407252.svg\" alt=\"DOI\"></a>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Data Exploration: Tennessee Colleges of Applied Technology (TCAT)**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Introduction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tennessee Colleges of Applied Technology (TCAT) are a key element of postsecondary education in Tennessee with the main mission of workforce development. They are premier providers of technical training for workers to obtain the technical skills and professional training necessary for advancement in the job market. Together with community colleges, TCATs contribute to the governor's education and economic agendas and the Drive to 55 initiative. Awarding over seven thousand certificates and diplomas a year in critical fields, TCATs leave a large footprint in the local and state economy."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using this notebook, we explore the TCAT dataset and start thinking about potential analytical questions to investigate. The table name is `tcat`, and it is accessible in the `ds_tn_tbr` database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Please consult the data dictionary (**Data-dictionary_Education-data_TBR**) and data description (**Education-data_TBR**) on the class website to get a better understanding of the variables in the dataset.\n",
    ">  It is assumed that you work on this notebook after you worked through Notebook 1A on community colleges. For more introductory details, please refer to the community college notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TCATs are public open access postsecondary institutions that are governed by the Tennessee Board of Regents (TBR) together with community colleges. There are 27 TCATs in the system and they are strategically located across the state. Unlike community colleges, TCATs operate on a continuous (year-round) basis; that is, students can enroll at any time during the year into programs of different length. They award certificates and diplomas, and historically they may have awarded some non-preparatory credentials (supplemental certificates and sufficient credentials). You may want to exclude non-preparatory credentials from your analysis of graduates (unless you are interested in comparing labor market value of different credentials)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Types of TCAT credentials:\n",
    "* __Diplomas__ are awarded to students who have demonstrated the competencies required for a program and have been awarded the appropriate diploma upon completion, usually in programs of at least 900 clock hours.\n",
    "* __Certificates__ are awarded to students who have demonstrated the competencies required for a program and have been awarded the appropriate certificate upon completion, usually in programs of less than 900 clock hours.\n",
    "* __Supplemental Certificates__ are non-preparatory awards, usually related to a special industry or special interest.\n",
    "* __Sufficient Credential__ is for students who leave a program without a credential before graduation, but have acquired sufficient competencies for employment in the field of instruction or related field."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the purposes of this training, TCAT data include only students who enroll to prepare for employment in specific occupations. It means that those wishing only to upgrade or update their skills are excluded. TCAT dataset also excludes dual-enrolled students, that is, high school students taking college courses at TCATs."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Learning Objectives"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the notebooks in this class, our general research question is stated as follows:\n",
    "\n",
    "> __What are the employment outcomes of the 2015-16 graduating cohort? How do these outcomes vary by cohort characteristics and employer characteristics?__\n",
    "\n",
    "For this notebook, we will need to explore the TCAT data on this cohort in greater detail before isolating a cohort of 2015-2016 TCAT graduates to investigate this question."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some of you may be more interested in examining outcomes of all students enrolling in TCATs so that you can compare outcomes of completers and non-completers. So we will start with defining a cohort of enrollees before moving on to TCAT graduates."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Notebook 1B Questions and Goals"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook, we will obtain answers to the following questions:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* How many unique students enrolled in TCATs during the academic year 2015-2016?\n",
    "* How many TCAT students graduated with a certificate or diploma in 2015-2016?\n",
    "* How many students enrolled in and graduated from TCATs in 2015-2016 by specific subgroups?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You will also have a chance to choose another academic year and examine your own outputs for that year both for TCAT enrollees and graduates."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Load the Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section, we will use R to read data from a relational database. Please refer to Notebook 1A on community colleges for any additional explanations if necessary."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### R Setup and Server Connection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before you begin, you need to run the code cells below to import the libraries and connect to the proper server. You may disregard the warning messages that you will see."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database interaction imports\n",
    "library(odbc, warn.conflicts=F, quietly=T)\n",
    "\n",
    "# For data manipulation/visualization\n",
    "library(tidyverse, warn.conflicts=F, quietly=T)\n",
    "\n",
    "# For faster date conversions\n",
    "library(lubridate, warn.conflicts=F, quietly=T)\n",
    "\n",
    "# Use percent() function\n",
    "library(scales, warn.conflicts=F, quietly=T)"
   ]
  },
  {
   "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": [
    "### Formulate Data Query and Read in the Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's examine the general structure of the dataset before we pull a specific cohort.\n",
    "You may want to start by testing the query below in DBeaver first. We pull the first 5 rows and add ordering by term sequence and campus ID. If you need more rows, update the query below or in DBeaver."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    SELECT TOP 5 *\n",
    "    FROM ds_tn_tbr.dbo.tcat\n",
    "    ORDER BY TermSeq, CampusID;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's pull the same data in R. Compare the results with the test query you ran in DBeaver. To run the code without saving it to a data frame for later reference, you can simply include `dbGetQuery(con,qry)`, as shown below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create qry as a character object\n",
    "qry <- \"\n",
    "    SELECT TOP 5 *\n",
    "    FROM ds_tn_tbr.dbo.tcat\n",
    "    ORDER BY TermSeq, CampusID;\n",
    "\"\n",
    "# Read in data frame \n",
    "dbGetQuery(con,qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Define a cohort of TCAT enrollees"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's pull a full-year cohort of TCAT students for academic year 2015-2016. \n",
    "These students enrolled in fall 2015, spring 2016, and summer 2016 (note that the trailing summer is used TCAT's' academic year).\n",
    "We will need to use `DISTINCT` to make sure we count each student only once even if they were enrolled in several trimesters (terms) of the academic year."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once again, start by running the queries below in DBeaver before trying them in R. As explained in the data dictionary, a *__RegTerm__* value of __'1'__ stands for fall, __'3'__ for spring, and __'4'__ for summer."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let's see how many observations we will get if we do not account for duplicates on student ID due to enrollment in more than one trimester (term). We use the COUNT() function to return the total student-by-term count instead of the column with all SSN's listed. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# count number of observations with non-missing SSNS\n",
    "qry <- \"\n",
    "SELECT COUNT(SSN)\n",
    "FROM ds_tn_tbr.dbo.tcat\n",
    "WHERE (RegYear = '2015' and RegTerm = '1')\n",
    "    OR (RegYear = '2016' and RegTerm = '3')\n",
    "    OR (RegYear = '2016' and RegTerm = '4');\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's add `DISTINCT` before `SSN` to get a unique (de-duplicated) count of students in that academic year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# count number of unique individuals\n",
    "qry <- \"\n",
    "SELECT COUNT(DISTINCT SSN)\n",
    "FROM ds_tn_tbr.dbo.tcat\n",
    "WHERE (RegYear = '2015' and RegTerm = '1')\n",
    "    OR (RegYear = '2016' and RegTerm = '3')\n",
    "    OR (RegYear = '2016' and RegTerm = '4');\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can simplify the query above if we use *__TermSeq__* to define terms (trimesters). Let's try it now. To do that, we need to know what years and terms *__TermSeq__* corresponds to.\n",
    "Run the following code and examine the output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry <- \"\n",
    "SELECT DISTINCT TermSeq\n",
    "    , RegYear\n",
    "    , RegTerm\n",
    "    , TermDesc\n",
    "FROM ds_tn_tbr.dbo.tcat\n",
    "ORDER BY TermSeq;\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we know that we need 4, 5, and 6 for *__TermSeq__*, let's simplify and re-run our query. Does it produce the same count as before?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use TermSeq instead\n",
    "qry <- \"\n",
    "SELECT COUNT(DISTINCT SSN)\n",
    "FROM ds_tn_tbr.dbo.tcat\n",
    "WHERE TermSeq IN('4','5','6');\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also count distinct students by college. For that, we need to group by institutional code. Let's also add the name of the column with counts using `AS` plus the alias 'StudentCount.'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How do you explain an increase in the total count if we sum up students across colleges? (If we add student count for each college, the new total is REDACTED.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry <- \"\n",
    "SELECT CampusID, COUNT(DISTINCT SSN) AS StudentCount\n",
    "FROM ds_tn_tbr.dbo.tcat\n",
    "WHERE TermSeq IN('4','5','6')\n",
    "GROUP BY CampusID\n",
    "ORDER BY CampusID;\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checkpoint 1: Explore student counts in another academic year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Explore the TCAT dataset to answer the questions below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Refer to the data dictionary on the class website to get a better understanding of the variables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Goal 1: Count unique students enrolling in TCATs in a different academic year\n",
    "# Replace ____ with the table database, table name, and terms of your academic year.\n",
    "\n",
    "qry <- \"\n",
    "SELECT COUNT(DISTINCT SSN)\n",
    "FROM ____.__.____\n",
    "WHERE TermSeq IN('__','__','__');\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Goal 2: Count students enrolling in each TCAT in the same academic year as above removing duplicates on student ID.\n",
    "# Replace ____ with the correct fields names and values of TermSeq.\n",
    "# Create an alias for the column with counts.\n",
    "\n",
    "qry <- \"\n",
    "SELECT __\n",
    "    , COUNT(DISTINCT __)\n",
    "FROM ds_tn_tbr.dbo.tcat\n",
    "WHERE TermSeq IN('__','__','__')\n",
    "GROUP BY __\n",
    "ORDER BY CampusID;\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Goal 3: Pull distinct student count by county of student residence for your academic year of interest.\n",
    "# Examine the dataset and replace ____ with the correct field name (you will need the one with county names as opposed to codes) and values of TermSeq to count students by county.\n",
    "# Note that Tennessee has 95 counties. So you may want to limit the pull to the top 15 in the alphabetical order as implemented below (in 'head(df, 15)').\n",
    "\n",
    "qry <- \"\n",
    "SELECT __\n",
    "    , COUNT(DISTINCT SSN)\n",
    "FROM ds_tn_tbr.dbo.tcat\n",
    "WHERE TermSeq IN('__','__','__')\n",
    "GROUP BY __\n",
    "ORDER BY __;\n",
    "\"\n",
    "\n",
    "# Assign the query results as df\n",
    "df <- dbGetQuery(con, qry)\n",
    "\n",
    "# See the first 15 few rows of df\n",
    "head(df, 15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Examine a cohort of TCAT graduates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the second part of the notebook, we will examine a cohort of graduates in academic year 2015-2016. \n",
    "These are students who completed their program in fall 2015, spring 2016, or summer 2016.\n",
    "We will need to use `DISTINCT` to make sure we count each student once even if they earned multiple credentials during the academic year."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We already know what `TermSeq` values are for academic year 2015-2016. We now want to focus on completers `Completer_c=3` with a certificate or diploma `Award_c IN(1,2)`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Please refer to the data description for additional details on how to identify completers (graduates) and include or exlcude various credentials by award type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# only count number of completers with a certificate or diploma\n",
    "qry <- \"\n",
    "SELECT COUNT(DISTINCT SSN) AS GradCount\n",
    "FROM ds_tn_tbr.dbo.tcat\n",
    "WHERE TermSeq IN('4','5','6')\n",
    "    AND Completer_c = 3\n",
    "    AND Award_c IN(1,2)\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see how many graduates each TCAT had during 2015-2016. The coding approach is the same as the one we used for enrollees above. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Why is the total count after summing up each college's total slightly higher (REDACTED) than the overall count?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# count by college\n",
    "qry <- \"\n",
    "SELECT CampusID, COUNT(DISTINCT SSN) AS GradCount\n",
    "FROM ds_tn_tbr.dbo.tcat\n",
    "WHERE TermSeq IN('4','5','6')\n",
    "    AND Completer_c = 3\n",
    "    AND Award_c IN(1,2)\n",
    "GROUP BY CampusID\n",
    "ORDER BY CampusID;\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The total count after summing up the amount of graduates from each institution is higher than the overall count because there are some instances where an individual graduated from multiple institutions within this time period. Therefore, these individuals are being double-counted when counting the number of graduates by institutions relative to the total number of graduates."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate by race/ethnicity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's do something different. Suppose we want to conduct equity analysis by aggregated race/ethnicity groups. The existing table has separate flags for race/ethnicity, which are not mutually exclusive and are not combined into one field."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's create a new variable `Race` for our future analysis. For cases when multiple race designations are reported for the same student during the academic year, we will let the minority designation take priority. \n",
    "\n",
    "> Using the minority designation as the priority is one way of handling an individual's race. There may be multiple ways to work with messy data, which will be further covered in the Imputation notebook. Is there a certain way your organization would approach this situation?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To attain this goal, we will use the `CASE WHEN` statement, which has the following general structure:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    CASE  \n",
    "        WHEN condition1 THEN result1  \n",
    "        WHEN condition2 THEN result2  \n",
    "        WHEN condition\\# THEN result\\#  \n",
    "        ELSE result_other  \n",
    "    END AS alias;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the `Hisp` field is character (non-numeric) and uses '2' to identify Hispanic students. The other race/ethnicity variables are integer (numeric) and use the value of '1' to flag the respective groups."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run the code below and examine the table. If you scroll all the way to right, you will see that a new field called *__Race__* has been added to the output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a query to add a new Race/ethnicity field to the dataset\n",
    "qry <- \"\n",
    "SELECT *\n",
    "    , CASE WHEN Hisp = '2' THEN 'Hispanic/Latino'\n",
    "           WHEN Asian = 1 THEN 'Asian'\n",
    "           WHEN Black = 1 THEN 'Black or African-American'\n",
    "           WHEN White = 1 THEN 'White'\n",
    "           WHEN RaceUnk = 1 THEN 'NA'\n",
    "           ELSE 'Other race' END AS Race\n",
    "FROM ds_tn_tbr.dbo.tcat\n",
    "WHERE TermSeq IN('4','5','6')\n",
    "    AND Completer_c = 3\n",
    "    AND Award_c IN(1,2)\n",
    "\"\n",
    "\n",
    "# Assign the query results as df\n",
    "df <- dbGetQuery(con, qry)\n",
    "\n",
    "# Examine the first few rows of df\n",
    "head(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Graduates by award type and race/ethnicity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we created a data frame for the graduation year of interest (2015-2016), let's calculate the number of of completers by award type (***AwardType***)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Please note that the code is case-sensitive. So if you type `ssn` instead of `SSN`, you will get an error message."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that duplication on `SSN` is possible if the same student earned two different credentials in the same year. Thus the count in the code cell below is that of unique awards rather than unique graduates. We will unduplicate the count in the next step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Count the number of awards by Award Type\n",
    "df %>%\n",
    "    group_by(AwardType) %>%\n",
    "    summarise(\n",
    "        num_awards = n_distinct(SSN)\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To unduplicate the cohort, let's keep just the highest award for graduates who earned more than one credential during the academic year of interest. We do so by first ordering `AwardType` in the descending order (so that 'Diploma' comes before 'Certificate') and then keeping just a diploma for cases when a student earned both a certificate and a diploma. \n",
    "Let's save the result in a new data frame to use later. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assign the unduplicated count of graduates to a new data frame\n",
    "df_unduped <- df %>%\n",
    "    arrange(SSN, desc(AwardType)) %>%\n",
    "    distinct(SSN, .keep_all = TRUE)\n",
    "\n",
    "head(df_unduped)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compare the new count by award type. How does the unique count of graduates differ from the unique count of awards?\n",
    "df_unduped %>%\n",
    "    group_by(AwardType) %>%\n",
    "    summarise(\n",
    "        num_grads = n_distinct(SSN)\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Count a number of graduates by the new race category.\n",
    "df_unduped %>%\n",
    "    group_by(Race) %>%\n",
    "    summarise(\n",
    "        num_grads = n_distinct(SSN)\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Count a number of graduates by both race/ethnicity and award type.\n",
    "df_unduped %>%\n",
    "    group_by(Race, AwardType) %>%\n",
    "    summarise(\n",
    "        num_grads = n_distinct(SSN)\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " Now let's add percentage within each Race/AwardType category."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Present counts and percentage by race/ethnicity and award type\n",
    "# note: we can use count() because the data frame is unduplicated so the number of rows is the same as the number of individuals\n",
    "df_unduped %>%\n",
    "    count(Race, AwardType) %>%\n",
    "    group_by(AwardType) %>%\n",
    "    mutate(\n",
    "        prop = n/sum(n)\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checkpoint 2: Estimate completer counts in another academic year by college and specific student group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Goal 1: Count unique students graduating from each TCAT in a different academic year.\n",
    "# Replace ____ with the required field names and terms of your academic year.\n",
    "\n",
    "qry <- \"\n",
    "SELECT COUNT(DISTINCT SSN) AS GradCount\n",
    "FROM ds_tn_tbr.dbo.tcat\n",
    "WHERE TermSeq IN('__','__','__')\n",
    "    AND ____ = 3\n",
    "    AND ____ IN(1,2)\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Goal 2: Count in-state Hispanic students who graduated from a TCAT in your academic year of interest.\n",
    "# Replace ____ with two additional filters in the WHERE clause of your query (and terms of your academic year) to focus on this group of graduates.\n",
    "# Examine the dataset to identify the fields that you will need to use as filters in the query below.\n",
    "\n",
    "qry <- \"\n",
    "SELECT COUNT(DISTINCT SSN) AS GradCount\n",
    "FROM ds_tn_tbr.dbo.tcat\n",
    "WHERE TermSeq IN('__','__','__')\n",
    "    AND Completer_c = 3\n",
    "    AND Award_c IN(1,2)\n",
    "    AND ____ = 1\n",
    "    AND ____ = '2'\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Goal 3: Create a flag (variable with 0 and 1 values) for in-state Hispanic graduates in your academic year of interest. Name it 'InstateHisp.'\n",
    "# Use CASE statement with both of the required conditions (a graduate should be both from Tennessee and of Hispanic origin).\n",
    "# HINT: CASE WHEN can take multiple conditions.\n",
    "# Replace ____ with the correct field names (and terms of your academic year) to create a flag for the group of interest.\n",
    "# After running the query, scroll to the right and check whether the flag has been created correctly. Change the number in head(df, #) if you need to examine more observations.\n",
    "\n",
    "qry <- \"\n",
    "SELECT *\n",
    "    , CASE WHEN ____ = 1 AND ____ = '2'\n",
    "           THEN 1 ELSE 0\n",
    "           END AS InstateHisp\n",
    "FROM ds_tn_tbr.dbo.tcat\n",
    "WHERE TermSeq IN('__','__','__')\n",
    "    AND Completer_c = 3\n",
    "    AND Award_c IN(1,2)\n",
    "\"\n",
    "\n",
    "# Assign the query results as df\n",
    "df_new <- dbGetQuery(con, qry)\n",
    "\n",
    "# See the first 15 few rows of df\n",
    "head(df_new, 15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As the final step in our exploration, let's save the graduation cohort as a table in our training database so that we could join it later to the wage data. Please note that the 2015-16 TCAT cohort has been already saved so you do not need to run the code cells below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    qry <- \"use tr_tn_2021;\"\n",
    "    DBI::dbExecute(con, qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    DBI::dbWriteTable(\n",
    "        conn = con,\n",
    "        name = DBI::SQL(\"dbo.tcat_grads_2015_2016\"), \n",
    "        value = df_unduped\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# References\n",
    "\n",
    "Simone, Sean, Barrett, Nathan, & Feder, Benjamin. (2022, March 25). Data Exploration for Cohort Analysis using New Jersey Education to Earnings Data System Tables. Zenodo. https://doi.org/10.5281/zenodo.6385510"
   ]
  },
  {
   "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
}