1.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> "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **<center> Data Exploration: Create a Cohort </center>**\n",
    "\n",
    "<a href=\"https://doi.org/10.5281/zenodo.6385510\"><img src=\"https://zenodo.org/badge/DOI/10.5281/zenodo.6385510.svg\" alt=\"DOI\"></a>\n",
    "\n",
    "\n",
    "<center> Sean Simone, Nathan Barrett, Benjamin Feder <center>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **1. Introduction**\n",
    "Historically, state agencies used their own administrative data to administer programs and complete required reporting. These data were siloed and were not leveraged to evaluate or inform policy development within or across agencies. Legal, technological, and human resource barriers prevented using data to assist policymakers in making decisions. Through a concerted effort supported by the Statewide Longitudinal Data System (SLDS) Grant Program, states have gradually come together to address this limitation. In this course, you will learn how to review, understand, and link data from different agencies. You will also uncover real-life problems in using the data (such as missing data, errors in the data, and complex data structures) and learn how to address them. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook introduces you to the concept of creating a group or a \"cohort\" that will be used for future analysis. We will construct measures to understand who we are including and excluding (coverage) from the cohort and walk you through the decisions that need to be made when constructing the cohort using filters such as institution governance and/or level (in terms of highest degree offered), award level, major, and others. We begin with introducing you to the data analytical tools to load the data, including connecting R to the database and using SQL queries to pull the data. We then use these tools to explore completions files from the NJ Office of the Secretary of Higher Education. We will create a dataset (called a \"data frame\") and investigate the trends in New Jersey postsecondary graduates. At the end of this notebook, we will save the summary statistics in csv files that will be used in subsequent notebooks."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **A Note About COVID-19**\n",
    "\n",
    "This course will not address the COVID-19 crisis that most states are facing. While some states have access to weekly claims data, in most states it takes time for data to work through business processes before data are available to reserachers. For New Jersey, outcomes data (unemployment claims and wage data), aren't posted to the data system until six months after a quarter ends. Higher education completions data aren't posted until eight months after academic year ends. These correspond to the data collection windows for reporting to the Federal government. Additionally, when someone graduates with a credential from a postsecondary institution, there is a time delay before an analyst can see an outcome (2 to 4 quarters after graduation). As such, analysts must consider these limitations when designing an analysis. Statistics and data visualizations from this course can serve as a baseline for future analysis on COVID-19. Once designed, the same analysis can be repeated over time to show changes pre- and post-COVID-19."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **2. Learning Objectives**\n",
    "\n",
    "The Applied Data Analytics training uses a project-based approach to develop your analytic skills. You will begin by working with your team to develop and refine a research question. A crucial part of this is data exploration. You will implement techniques using SQL and R to explore and better understand the data that are available to you and if addressing your question is feasible. This will form the basis of all the other types of analyses you will do in this class and is a crucial first step for any data analysis workflow. As you work through the notebook, we will have checkpoints for you to practice writing code by making small adjustments, but you can also think about how you might apply any of the techniques and code presented with other datasets to address your research question. \n",
    "\n",
    "The guiding research questions we will use for the notebooks are quite general: \n",
    "\n",
    ">**What are the employment outcomes of the 2012-13 graduating cohort? How do these outcomes vary by cohort characteristics and employer characteristics?** \n",
    "\n",
    "This will allow the code we use to have the most versatility. We will analyze these questions through a variety of different lenses, but will start by defining a specific cohort of New Jersey graduates in the 2012-2013 academic year. We will then track their earnings and employment outcomes over time. The exploration of the supply side of the labor market will later be supplemented by an analysis of the demand side to enhance our understanding of the overall labor market.\n",
    "\n",
    "We are going to show just a portion of what you might be interested in investigating to answer these overarching questions, so don't feel restricted by the questions we've decided to try to answer.\n",
    "\n",
    ">**When defining your research question(s), recall that one key benefit of working with New Jersey administrative records in the ADRF is the ability to integrate higher education and employment data.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Notebook 1 Questions and Goals** \n",
    "In this notebook, we focus on seeking answers to the following questions: \n",
    "- How many students graduated from New Jersey public postsecondary institutions in the 2012-13 academic year?\n",
    "- What filters can be used to define the cohort (e.g., demographics, institutions, enrollment type, etc.)?\n",
    "- How many students graduated from New Jersey public postsecondary institutions by subgroup (e.g. demographics, institutions, enrollment type, etc.)?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After completing this notebook you should be able to perform the following analytical tasks:\n",
    "- load R libraries and establish a connection to the Database\n",
    "- create a cohort sample by using the OSHE Completions file\n",
    "- calculate descriptive statistics to understand who is in the population\n",
    "- create new tables from the larger tables in a database (sometimes called the \"analytical frame\")\n",
    "- explore different variables of interest\n",
    "- clean data\n",
    "- create aggregate metrics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The specific techniques include but not limited to:\n",
    "- **SQL statements/keywords**:\n",
    " - `SELECT ... FROM`: select data from a table in the database\n",
    " - `WHERE`: select subset of tables from the database\n",
    " - `GROUP BY`: aggregate data over the variables of interest\n",
    " - `ORDER BY`: sort data based on the variables of interest\n",
    " - `DISTINCT`: look at distinct values of a variable\n",
    " - `JOIN ... ON`: join tables\n",
    "- **R code**:\n",
    " - `group_by` and `summarize` to find group-based measures\n",
    " - `mutate` to create new variables\n",
    " - `arrange` and `desc` to sort values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Datasets** ####\n",
    "We will explore and understand the New Jersey Education to Earnings Data System (NJEEDS) tables in this notebook:\n",
    "- **Higher Education (OSHE) Completions**: The completions table comes from the Office of the Secretary of Higher Education's (OSHE) Student Unit Record data system (SURE). The data include completions at all levels that are reported to the U.S. Department of Education's Integrated Postsecondary Education Data System (IPEDS) Completions Survey.\n",
    "- **Higher Education (OSHE) Supplemental Tables**:  Multiple supplemental tables are available to append contextual information to the completions table. The `supplements_cip` table connects major names to the file. The `supplements_instcode` table connect institution characteristics to the file.\n",
    "\n",
    "> **NOTE:** Not all colleges and universities within New Jersey report data into the SURE system. Only public institutions reliably report data over time. For private nonprofit institutions, analysts should use caution as to which institutions reports data and which do not.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Directory Structure**\n",
    "\n",
    "We will constantly read and write csv files to load crosswalks and to save results in all the notebooks. Let's create a few folders in your U drive first so it is easier for you to organize all the files. \n",
    "\n",
    "- Open a Windows File Explorer\n",
    "- On the left hand side, find U drive (U:) and click into it\n",
    "- On the right hand side, open your user folder: FirstName.LastName.UserID\n",
    "- In your user folder, create a new folder: NJ Training\n",
    "- In the \"NJ Training\" folder, create three subfolders: \"Notebooks\", \"Results\", \"Output\"\n",
    "- You can copy and paste the class notebooks to the \"Notebook\" folder, save summary statistics to the \"Results\" folder, and save visualizations (in the third notebook) to the \"Output\" folder.\n",
    "\n",
    "For example, we read all the crosswalks from **\"P:\\tr-dol-nj\\NJ Class Notebooks\\xwalks\"**.  At the end of this notebook, **we save summary statistics to \"U:\\\\FirstName.LastName.UserID\\NJ Training\\Results\\filename.csv\"**.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **3. Load the Data**\n",
    "\n",
    "In this section, we will demonstrate how to use R to read data from a relational database. First, we need to load packages in R."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **R Setup**\n",
    "\n",
    "We will use several R functions that are not immediately available in base R. Therefore, we need to load them using the built-in function `library()`. For example, running `library(tidyverse)` loads the `tidyverse` suite of packages. It is a collection of packages designed for data science.\n",
    "\n",
    "> When you run the following code cell, don't worry about the warning message below."
   ]
  },
  {
   "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": "markdown",
   "metadata": {},
   "source": [
    "__When in doubt, full documentation for a method can be printed with `?<package/function_name>`, e.g. `?tidyverse/ggplot` or `?sprintf`.__ Do not worry about memorizing the information in the help documentation - you can always run this command when you are unsure of how to use a function.\n",
    "\n",
    "> Certain functions exist across multiple packages (e.g. the function `lag` exists in both the `dplyr` and `stats` package - also noted in the message yielded from `library(tidyverse)`. When calling a function, you can put the package name first to ensure that you are using the right one. For example, `dplyr::lag` or `stats::lag` calls the `lag` function from `dplyr` or `stats`, respectively. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# See help documentation for head:\n",
    "# a function we will use frequently to check the content of a table\n",
    "# It returns the first few rows of a table\n",
    "?head"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Establish a Connection to the Server**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we are ready to connect to the server. We will create the connection using the `DBI`  and `ODBC` libraries. \n",
    "\n",
    "> **Loading R libraries** and **establishing connection** should always be the first step in your Jupyter Notebooks. Make sure you copy these code chunks when you create a new notebook."
   ]
  },
  {
   "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**\n",
    "\n",
    "Next, we need to dictate what we want to pull in from the database. This part is similar to writing a SQL query in DBeaver. In this example, we will pull in 5 rows of the New Jersey higher education graduates, which is stored in the `completions` table inside the `ds_nj_oshe` schema. Before running the code below, test the inital query you will use to bring in your first data frame to make sure it successfully runs in DBeaver:\n",
    "\n",
    "    SELECT TOP 5 *\n",
    "    FROM ds_nj_oshe.dbo.completions ;\n",
    "\n",
    "Next, we create the same query as a `character` object in R."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Create qry character object\n",
    "# Database name: ds_nj_oshe\n",
    "# Schema name: dbo\n",
    "# Table name: completions\n",
    "qry <- \"\n",
    "SELECT TOP 5 *\n",
    "FROM ds_nj_oshe.dbo.completions;\n",
    "\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use `TOP` to read in only the first 5 rows because we're just looking to preview the data and we don't want to eat up memory by reading a huge data frame into R. \n",
    "\n",
    "> `TOP` provides one simple way to get a \"sample\" of data. You may get different samples of data from others using just the `TOP` clause. However, it is not because you get a random sample by using `TOP`. It is because the database returns the results that can be pulled the fastest."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Read in the Data** \n",
    "\n",
    "Now we can use `con` and `qry` as inputs to `dbGetQuery()` to read the data into R. Compare the results below with the test query you made 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": [
    "# Read in data frame \n",
    "dbGetQuery(con,qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# See first few rows\n",
    "dbGetQuery(con, qry) %>%\n",
    "    head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Note: There are other methods you can use to explore the data. Two of these functions are `glimpse` and `names`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Checkpoint 1: Explore Columns** \n",
    "\n",
    "Take a look at the columns in the `completions` table. Which variables might be useful for your project?  Let's explore another table.  Try to query another higher education data table. Explore the `supplements_cip` lookup table in the `ds_nj_oshe` database.\n",
    "\n",
    "> 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": [
    "# Replace ____ with the table database and table name\n",
    "qry <- \"\n",
    "SELECT TOP 5 *\n",
    "FROM ___.___.__;\n",
    "\"\n",
    "\n",
    "# Read in data frame\n",
    "dbGetQuery(con,qry)\n",
    "\n",
    "# Can write code to explore the data frame\n",
    "dbGetQuery(con, qry) %>%\n",
    "    ____()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **4. Explore the table and understand the data**\n",
    "\n",
    "Before building a cohort, it is important to understand the quality of the data. As we will be creating a cohort of graduates, it is important to note that the `completions` table lists awards, not people. Each row represents a person-degree/credential-major. The data are not always clean. Before creating the cohort it is useful to understand missing values, changes in trends, and inconsistent data. \n",
    "\n",
    "Since we hope to create a cohort of graduates that graduated within a specific time period (2012-13 academic year), let's take a look at the distribution of the number of graduates by year, or `awardyearn` in the data.\n",
    "\n",
    "Try running the following query in DBeaver to understand the kinds of information you will be bringing into your data frame:\n",
    "\n",
    "    SELECT awardyearn, count(DISTINCT(hashed_ssn)) as num_individuals\n",
    "    FROM ds_nj_oshe.dbo.completions\n",
    "    GROUP BY awardyearn\n",
    "    ORDER BY awardyearn desc;\n",
    "\n",
    "Now run the query in R and review the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Exploration query on award year earned\n",
    "qry <- \"\n",
    "SELECT awardyearn, count(DISTINCT(hashed_ssn)) as num_individuals\n",
    "FROM ds_nj_oshe.dbo.completions\n",
    "GROUP BY awardyearn\n",
    "ORDER BY awardyearn desc;\n",
    "\"\n",
    "\n",
    "# Read in data frame and but don't save the results\n",
    "dbGetQuery(con,qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, we have quite a large number of potential graduates to pull from. However, the academic year of 2012-2013 includes some graduates from 2012, and others from 2013. To get a better sense of our potential sample size, let's look at the number of individuals that graduated by month in 2012 and 2013 using the following query:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    SELECT awarddate, count(DISTINCT(hashed_ssn)) as num_individuals\n",
    "    FROM ds_nj_oshe.dbo.completions\n",
    "    WHERE awardyearn = 2012 OR awardyearn = 2013\n",
    "    GROUP BY awarddate\n",
    "    ORDER BY awarddate ;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Exploration query on awarddate\n",
    "qry <- \"\n",
    "SELECT awarddate, count(DISTINCT(hashed_ssn)) as num_individuals\n",
    "FROM ds_nj_oshe.dbo.completions\n",
    "WHERE awardyearn = 2012 OR awardyearn = 2013\n",
    "GROUP BY awarddate\n",
    "ORDER BY awarddate ;\n",
    "\"\n",
    "\n",
    "# Read in data frame but don't save the results\n",
    "dbGetQuery(con,qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that there are cases where leading zeros are missing.  This can be corrected in SQL code or in R depending on if the data frame has been created or not.  In SQL, the following code will add leading zeros to ensure that each `awarddate` is 6 digits:\n",
    "\n",
    "    RIGHT('000000'+ISNULL(awarddate,''),6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Exploration query on awarddate\n",
    "qry <- \"\n",
    "SELECT right('000000'+ awarddate, 6) as new_awarddate, count(DISTINCT(hashed_ssn)) as num_individuals\n",
    "FROM ds_nj_oshe.dbo.completions\n",
    "WHERE awardyearn = 2012 OR awardyearn = 2013\n",
    "GROUP BY right('000000'+ awarddate, 6)\n",
    "ORDER BY right('000000'+ awarddate, 6) ;\n",
    "\"\n",
    "\n",
    "# Read in data frame but don't save the results\n",
    "dbGetQuery(con,qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have created a new column, `new_awarddate`, to adjust for the inconsistency of leading zeros, let's read the data into R and save the resulting data frame as `df`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read table into r and assign as df\n",
    "qry <- \"\n",
    "select *, right('000000' + awarddate, 6) as new_awarddate\n",
    "from ds_nj_oshe.dbo.completions \n",
    "where awardyearn in (2012, 2013)\n",
    "\"\n",
    "df<-dbGetQuery(con, qry)\n",
    "\n",
    "# see first few rows of df\n",
    "head(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Checkpoint 2: Explore the Data** \n",
    "\n",
    "Run the following queries in the notebook to better understand some key variables of interest:\n",
    "\n",
    "    SELECT awardyearn, race_single, count(DISTINCT(hashed_ssn)) as num_individuals\n",
    "    FROM ds_nj_oshe.dbo.completions\n",
    "    GROUP BY awardyearn, race_single\n",
    "    ORDER BY awardyearn ;\n",
    "\n",
    "    SELECT awardyearn, citizenship, count(DISTINCT(hashed_ssn)) as num_individuals\n",
    "    FROM ds_nj_oshe.dbo.completions\n",
    "    GROUP BY awardyearn, citizenship\n",
    "    ORDER BY awardyearn ;\n",
    "\n",
    "Try running the code in the box below using different columns.  After reviewing this information, try to answer the following questions:\n",
    "1. Do you see any obvious problems in the data? \n",
    "2. Are there null values in the data you are seeing?  Do you see any trends or patterns?\n",
    "3. Are there years where you need to use different column to represent the same or similar construct?\n",
    "4. Are there cases where some values aren't as clean as you would have hoped? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace ___ in query to understand race/citizenship and year\n",
    "qry <- \"\n",
    "SELECT awardyearn, ____, count(DISTINCT(hashed_ssn)) as num_individuals\n",
    "FROM ds_nj_oshe.dbo.completions\n",
    "GROUP BY awardyearn, ____\n",
    "ORDER BY awardyearn ;\n",
    "\"\n",
    "\n",
    "# Read in data frame but don't save the results\n",
    "dbGetQuery(con,qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **5. Create the Cohort**\n",
    "\n",
    "In this section, we will use the New Jersey `completions` table to create a sample of all students in NJ SURE participating institutions who earned a bachelor's degree during the 2012-13 academic year. This is not as easy as it looks. Even though we created a new variable to consistently track graduations, we still need to further limit the cohort from our original query that yielded `df` because the academic year is not the same as the calendar year.\n",
    "\n",
    "In addition to establishing a time period, it is common to narrow your population down.  Some research questions require you to select only certain graduates.  Some questions focus on degree level (bachelors degree recipients, for example), or major (health sciences, for example). When establishing your cohort, it is helpful to build an initial query iteraively, checking each restriction before adding others. To recall, our initial query is\n",
    "\n",
    "    select *, right('000000' + awarddate, 6) as new_awarddate\n",
    "    from ds_nj_oshe.dbo.completions \n",
    "    where awardyearn in (2012, 2013)\n",
    "    \n",
    "Let's keep track of the number of individuals we currently have in `df`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see number of individuals in df\n",
    "df %>%\n",
    "    summarize(\n",
    "        num_inds = n_distinct(hashed_ssn)\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Academic Year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As previously mentioned, we have not yet limited `df` to just include graduates from the 2012-13 *academic* year, which is defined by the last REDACTED months of 2012 and first REDACTED months of 2013.\n",
    "\n",
    "To isolate these graduates, we can `filter` `df` based on these requirements, as the month of graduate is now consistent in `new_awarddate`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# isolate 2012-2013 academic year grads\n",
    "# substring(variable, 1, 2) will isolate the first two characters of the variable\n",
    "# | represents \"or\"\n",
    "df <- df %>%\n",
    "    filter(\n",
    "        (awardyearn == 2012 & between(substring(new_awarddate, 1, 2), '07', '12')) |\n",
    "        (awardyearn == 2013 & between(substring(new_awarddate, 1, 2), '01', '06'))\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see our breakdown of graduates by month now to confirm we properly filtered `df`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see count of grads by month\n",
    "df %>%\n",
    "    group_by(new_awarddate) %>%\n",
    "    summarize(\n",
    "        num_inds = n_distinct(hashed_ssn)\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Bachelor's degree earners"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have isolated all graduates in the 2012-13 academic year, let's turn our attention to bachelor's degree earners. According to the data documentation, a bachelor's degree is assigned codes from 300-399 inclusive for the `awardtype` variable. Before further subsetting `df`, let's take a look at `awardtype`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# count number of graduates by awardtype\n",
    "df %>%\n",
    "    group_by(awardtype) %>%\n",
    "    summarize(\n",
    "        num_inds = n_distinct(hashed_ssn)\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There do not appear to be any issues in terms of potential lengths within `awardtype`, so let's go ahead and `filter` for bachelor's degree recipients."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter for bachelor's degree recipients\n",
    "df <- df %>%\n",
    "    filter(between(awardtype, '300', '399')) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Checkpoint 3: Create Your Sample**\n",
    "Starting with the `completions` table, create a sample of graduates of a separate academic year and award level. Name the data frame `df_checkpoint`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace ____ \n",
    "qry <- \"\n",
    "select *, right('000000' + awarddate, 6) as new_awarddate\n",
    "from ds_nj_oshe.dbo.completions \n",
    "where awardyearn in (___, ___)\n",
    "\"\n",
    "\n",
    "# Read in data frame and save it as df_checkpoint\n",
    "df_checkpoint <- dbGetQuery(con,qry)\n",
    "\n",
    "df_checkpoint <- df_checkpoint %>%\n",
    "    filter(___)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **6. Link data across tables**\n",
    "\n",
    "Now that we have identified a cohort, it is important to link that cohort to other tables to gain further insights. In this example, we will link our data frame in the classification of instructional program (CIP) codes to the code names. First, we will write a query to read a cip crosswalk into R, so that we can understand the CIP code - subject meaning a bit more.  We don't need all the data in the CIP table because we are using the most recent CIP codes. As a result, the query below only includes specific columns in the data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query to bring in speific columns in the cip_2010 table\n",
    "qry <- \"\n",
    "SELECT code_2010 as major, title_2010 as major_title, cip2, cip_family\n",
    "FROM ds_nj_oshe.dbo.supplements_cipcode;\n",
    "\"\n",
    "\n",
    "# Read in data frame and save it as df_cip\n",
    "df_cip <- dbGetQuery(con,qry)\n",
    "\n",
    "# see df_cip\n",
    "glimpse(df_cip)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we merge the columns from `df_cip` for all records in `df` as long as they have the same CIP code, as designated by the `major` column across the two data frames.  The joining statement in R is as follows:\n",
    "\n",
    "    df <- df %>% \n",
    "        left_join(df_cip, by.y = \"major\")\n",
    "    \n",
    "A left join is used because we would like to retain all records in the left data frame (`df`) and are only bringing in matched records from the right data frame (`df_cip`).  Conversely, a right join  starts with all the records from the right table and only brings in matched records from the left table, and an inner join only includes records for which there is a match between both tables.  Notice that if the the same column name is used to match the data frames together, you only need to specify the one name for both tables after \"by\".  If the column names are different, you need to declare the column names for the two data frames.\n",
    "\n",
    "> **NOTE:** In this example, we are going to use R to join data frames. As you will see in the next notebook, with larger tables, it is inefficient or at times not possible to bring extremely large tables into R.  As a result, the joins have to be done in SQL prior to bringing the data frame into R."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Left join cohort to cip code xwalk data\n",
    "df <- df %>% \n",
    "    left_join(df_cip, by.y = \"major\")\n",
    "\n",
    "# See top records in the dataframe\n",
    "head(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that the data frame is finalized, you can manipulate it further based on major.  If, for example, your working group was only interested in those graduates in New Jersey graduating in the cip code of \"110101\", the data frame can be filtered and saved with those records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Subset the dataframe to specific major\n",
    "df_compgrads <- df %>% filter(major == '110101')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then you can see the major title associated with the CIP code. Of course, you can work the other way, where you `filter` for a specific `major_title` or `cip_family`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see major title\n",
    "df_compgrads %>%\n",
    "    count(major_title)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Checkpoint 4: Add columns\n",
    "\n",
    "Using the cohort that you created in the previous checkpoint, try to join data from the `supplements_cip2010` table and further subset your data frame to a specific major. Save the resulting data frame as `df_checkpoint_major`. If the data you selected was from before 2010, then use CIP 2000 codes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add in cip xwalk\n",
    "df_checkpoint <- df_checkpoint %>%\n",
    "    left_join(df_cip, by.y = \"major\")\n",
    "\n",
    "# Replace ___ with a major. See https://nces.ed.gov/ipeds/cipcode outside of the ADRF to select a cipcode.\n",
    "df_checkpoint_major <- df_checkpoint %>%\n",
    "    filter(___)\n",
    "\n",
    "# See top records in the dataframe\n",
    "head(df_checkpoint_major)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **7. Higher Education Graduate Cohort Count and Descriptive Statistics**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we will run some statistics to understand how data are structured in the cohort data frame. Recall that you had already made a data frame on your own, `df`. Here, we will use the same data frame to further examine the data elements. Recall that that each record in our data frame does not represent a person. Each record represents an award or credential (degree or certificate). You can see this by comparing the number of rows, or awards, with the number of graduates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# compare number of rows to grads\n",
    "df %>% \n",
    "    summarize(\n",
    "        awards=n(), \n",
    "        graduates=n_distinct(hashed_ssn)\n",
    "    ) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The difference between awards and graduates is because a subset of students earn two degrees, a certificate and a degree, or multiple short-cycle certificates in an academic year. When framing your higher education cohort, one needs to ask if you want to de-duplicate the file (by selecting the highest award for example) or if you want to focus on one level of degree. Workforce outcomes are going to be very different for certificate holders without a degree compared to those with associates degrees. Those with bachelors or graduate degrees are expected to have higher incomes. Recall that in this example, we have restricted our cohort to bachelor's degree holders. Three decisions are required to accurately form the cohort:\n",
    "\n",
    "1. What time period are you using to define the cohort (one academic year or multiple academic years)?\n",
    "2. What degree level or levels will you focus on?\n",
    "3. What will you do with duplicate records?\n",
    "\n",
    "In this example, we will review the duplicates to help inform decisions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Duplicates code\n",
    "\n",
    "The series of commands below help identify duplicates, create a data frame of duplicates, and list the results. Before we de-duplicate the files, let's save a degree-level (not person-level) file for further analysis.  We will name this data frame `df_awards`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# copy df as df_awards\n",
    "df_awards <- df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we can start to explore the duplicates. First, we will identify a case of duplication, which we can isolate by counting the number of occurrences of each `hashed_ssn` in `df`, and then finding the `hashed_ssn` with the highest number of occurrences."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# find duplicate example\n",
    "dup_ex <- df %>%\n",
    "    count(hashed_ssn) %>%\n",
    "    arrange(desc(n)) %>%\n",
    "    head(1)\n",
    "\n",
    "# see example\n",
    "dup_ex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From here, we can find all rows in `df` with the `hashed_ssn` in `dup_ex` so we can further explore a duplicated example. We will select certain variables to highlight the duplication."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see all duplicated rows in example\n",
    "df %>%\n",
    "    filter(hashed_ssn == dup_ex$hashed_ssn) %>%\n",
    "    select(hashed_ssn, new_awarddate, instcode, major, major_title, cip2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes, the two-digit family CIP code is nearly the same for all duplicates, and that the double or triple degrees are nearly equivalent (for example Accounting and Finance OR Buisness and Marketing) in addition to other true duplicates, based on the columns we selected. We will assume that most degrees are in similar two-digit CIP families, thus de-duplicating and taking the most recent record (or one of the most recent if there are multiple). To do so, we will first sort `df`, so that for each `hashed_ssn`, the first row is at least one of the most recent degrees. From there, we can use `distinct` to isolate the first row within each `hashed_ssn`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# unduplicate cohort\n",
    "df <- df %>%\n",
    "    arrange(hashed_ssn, desc(awardyearn), desc(new_awarddate)) %>%\n",
    "    distinct(hashed_ssn, .keep_all = TRUE)\n",
    "\n",
    "# compare number of rows to grads\n",
    "df %>% \n",
    "    summarize(\n",
    "        awards=n(), \n",
    "        graduates=n_distinct(hashed_ssn)\n",
    "    ) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Checkpoint 5: Explore duplicates and remove for your cohort**\n",
    "\n",
    "For your data frame `df_checkpoint`, explore and come up with a strategy for removing all duplicates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# replace ___ with code\n",
    "dup_ex <- __ %>%\n",
    "    count(hashed_ssn) %>%\n",
    "    arrange(desc(n)) %>%\n",
    "    head(1)\n",
    "\n",
    "# see example\n",
    "dup_ex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see all duplicated rows in example\n",
    "___ %>%\n",
    "    filter(hashed_ssn == dup_ex$___) %>%\n",
    "    select(___)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# unduplicate cohort\n",
    "___ <- ___ %>%\n",
    "    arrange(hashed_ssn, ___) %>%\n",
    "    distinct(hashed_ssn, .keep_all = TRUE)\n",
    "\n",
    "# compare number of rows to grads\n",
    "___ %>% \n",
    "    summarize(\n",
    "        awards=n(), \n",
    "        graduates=n_distinct(hashed_ssn)\n",
    "    ) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **8. Exploratory Analysis of the Cohort**\n",
    "\n",
    "In this section we will find out more about our 2012-13 graduating cohort. We will begin by isolating the top 10 majors using the two digit CIP code. \n",
    "\n",
    "From there we will look to see if there are differences by sex. Understanding these patterns are an important part of understanding potential disparities in employment outcomes.  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Up to this point, we have identified our cohort (`df`), and removed all duplicates so that they are person-level files.  Let's start by looking at the difference in `major_title` compared to `cip_family`, based on the granularity we desire in major groups."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Major Groupings\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## see difference in number of award types between major title and cip family\n",
    "df %>%\n",
    "    summarize(\n",
    "        num_cip_fam = n_distinct(cip_family),\n",
    "        num_major_title = n_distinct(major_title)\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the sake of this analysis, we will use `cip_family`, or the two-digit CIP codes as we continue our analysis by major. Let's find the 10 most common majors in the cohort."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 10 most common majors\n",
    "df %>%\n",
    "    count(cip_family) %>%\n",
    "    arrange(desc(n)) %>%\n",
    "    head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Does this list surprise you? For perspective, we will add in another column tracking the proportion of graduates by major."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 10 most common majors with proportion\n",
    "df %>%\n",
    "    count(cip_family) %>%\n",
    "    arrange(desc(n)) %>%\n",
    "    mutate(\n",
    "        prop = n/sum(n)\n",
    "    ) %>%\n",
    "    head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because we hope to build off of this cursory subgroup analysis in later notebooks, let's save the resulting data frame to `df_common_major`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 10 most common majors with proportion\n",
    "df_common_major <- df %>%\n",
    "    count(cip_family) %>%\n",
    "    arrange(desc(n)) %>%\n",
    "    mutate(\n",
    "        prop = n/sum(n)\n",
    "    ) %>%\n",
    "    head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sex\n",
    "\n",
    "Additionally, we can look at the sex breakdown within the cohort using the `sex` variable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sex breakdown\n",
    "df_sex <- df %>%\n",
    "    count(sex) %>%\n",
    "    arrange(desc(n)) %>%\n",
    "    mutate(\n",
    "        prop = n/sum(n)\n",
    "    )\n",
    "\n",
    "# see df_sex\n",
    "df_sex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top Majors by Sex\n",
    "\n",
    "Let's intersect the major breakdown by sex—do the most common majors differ amongst sex groups? Since we are looking at proportions and counts within multiple combinations of subgroups (`cip_family` and `sex`), we need to adjust the code from above a bit. First, we need to calculate the proportion of observations within each `sex`, hence the `group_by`, and we replace `head` with `slice` to retrieve the top 10 majors within each `sex` value, instead of returning the top 10 rows as ordered by `sex`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# major/sex breakdown\n",
    "df_major_sex <- df %>%\n",
    "    count(cip_family, sex) %>%\n",
    "    arrange(desc(n)) %>%\n",
    "    group_by(sex) %>%\n",
    "    mutate(\n",
    "        prop = n/sum(n)\n",
    "    ) %>%\n",
    "    arrange(sex, desc(n)) %>%\n",
    "    slice(1:10)\n",
    "\n",
    "df_major_sex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Checkpoint 5: Common Majors and Sex**\n",
    "\n",
    "Using your own data frame, `df_checkpoint`, identify the 10 most common majors overall and by sex. Save these results to `df_checkpoint_common_major` and `df_checkpoint_major_sex`, respectively.\n",
    "\n",
    "Do your results vary drastically from those derived from `df`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# find common major\n",
    "df_checkpoint_common_major <- df_checkpoint %>%\n",
    "    ___\n",
    "\n",
    "df_checkpoint_common_major"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# find most common majors by sex\n",
    "df_checkpoint_major_sex <- df_checkpoint %>%\n",
    "    ___\n",
    "\n",
    "df_checkpoint_major_sex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **9. Export Results to .csv Files**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now you have successfully finished defining a cohort and a quick subgroup analysis! The last step is to save your results in .csv files so that we can re-use these results in future notebooks. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color=red> Note that you need to change the directory in write.csv() statements below. Replace \". .\" with your username.</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save dataframes to CSV to use in later notebook\n",
    "\n",
    "# most common majors\n",
    "write_csv(df_common_major, \"U:\\\\..\\\\NJ Training\\\\Results\\\\common_major.csv\")\n",
    "\n",
    "# sex breakdown\n",
    "write_csv(df_sex, \"U:\\\\..\\\\NJ Training\\\\Results\\\\common_sex.csv\")\n",
    "\n",
    "# most common majors by sex\n",
    "write_csv(df_major_sex, \"U:\\\\..\\\\NJ Training\\\\Results\\\\common_major_sex.csv\")"
   ]
  }
 ],
 "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"
  },
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 4
}