2.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: Wages</center>\n",
    "<a href=\"https://doi.org/10.5281/zenodo.6385522\"><img src=\"https://zenodo.org/badge/DOI/10.5281/zenodo.6385522.svg\" alt=\"DOI\"></a>\n",
    "\n",
    "<center>Benjamin Feder, Sean Simone, Nathan Barrett</center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **1. Introduction**\n",
    "Few would argue against the commonsense notion that individuals and society benefit from a strong education system. Indeed, the median individual employed full time with a bachelor's degree earns about 80% more than a similar individual with a high school diploma.  More educated populations benefit from increased tax revenue, reduced crime rates and dependence on public assistance programs, and greater civic engagement.  It should come as no surprise then that, with the expectation of future benefits, individuals and governments alike invest a tremendous amount of money into education. In New Jersey, state and local expenditures for higher education alone amounted to over \\\\$7.4 billion in 2017.  Around the same time, 64% of recent New Jersey college graduates had student loan debt averaging about \\$34,000.  \n",
    "\n",
    "There is room for debate, however, about whether the public is fully and fairly realizing the potential benefits of these investments. There is growing concern of a skills gap whereby individuals with postsecondary degrees are unable to fill the available jobs or find consistent employment with the degrees they have. There are also concerns over equity gaps in the workforce outcomes of postsecondary graduates. Accordingly, there is growing interest in developing and using the data systems needed to better understand the education to workforce pipeline. To date, the federal government has allocated over $750 million to states to develop Statewide Longitudinal Data Systems (SLDS) with the goal of making this possible. While not all states have been able to implement such systems, New Jersey has. This notebook will leverage these data to begin to understand the education to workforce pipeline in New Jersey.\n"
   ]
  },
  {
   "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 refine your research question. 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. In the last notebook, you defined a cohort of interest. We will now 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."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Notebook 2 Questions and Goals** \n",
    "In this notebook, we focus on seeking answers to the following questions: \n",
    "- What are the average quarterly earnings of our cohort? Do they vary by major?\n",
    "- What are the stable employment outcomes of our cohort? Do they vary by sex?\n",
    "- What are the most common employment patterns of our cohort?\n",
    "- We also provide code in the Appendix for you to explore additional employment outcomes.\n",
    "\n",
    "After completing this notebook you should be able to perform the following analytical tasks:\n",
    "- Load R libraries and establish a connection to the server\n",
    "- Link an education cohort to the wage data\n",
    "- Identify full quarter employment\n",
    "- Identify wage outcomes by different subgroups"
   ]
  },
  {
   "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",
    "- **Unemployment Insurance (UI) wage records**: Data are collected from businesses and industries in New Jersey that participate, as required by law, in the Unemployment Insurance (UI) program and its trust fund. Wages are reported monthly by industry and compiled quarterly. This information is used for administering the UI program and feeds into mandatory reports to the US Department of Labor."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. SQL and R for Processing Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQL is designed to allow for quick and efficient processing of massive amounts of information, such as the UI wage records file. Although you may not have trouble narrowing down a cohort from the Completions table in R, you will run into memory issues reading larger tables into R prior to significantly limiting their size. Because we will need to link our original cohort to the UI wage records to begin to understand the cohort's employment outcomes, we have saved our resulting analytical file formed at the end of the first data exploration notebook as a table in SQL. This will allow us to easily perform a linkage to their employment outcomes in SQL, as opposed to reading the entire UI wage records table into R to perform the linkage. The SQL code to form this table is explained in a  [supplemental notebook](Supplemental/Supplemental_Table_Creation_in_SQL.ipynb). Once we have our final table of wage outcomes specific to our cohort within a defined time period, we should be able to read this table into R to perform more complex analyses, as it is just a small subset of the original UI wage records file.\n",
    " \n",
    "We *highly recommend* reviewing this notebook to understand the table creation process in SQL, as it will be necessary for any future work with the UI wage records or other files of a similar size. The final section of the supplemental notebook replicates the data manipulation performed in R required to prepare the original cohort and UI wage records for the linkage in SQL.\n",
    "\n",
    "> Note: To save an R data frame as a permanent table in SQL, you can use the following code structure:\n",
    "\n",
    "        qry <- \"use database_with_read_write_access;\"\n",
    "        DBI::dbExecute(con, qry)\n",
    "        DBI::dbWriteTable(conn = con, name = DBI::SQL(\"dbo.TABLENAME\"), value = df_from_R)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Notebook Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we can get started, let's load in the necessary R libraries, connect to the server, and load in the table containing our analytical cohort established in the first data exploration notebook. Again, the supplemental notebook contains information about how the transformations performed in R to create our final data frame in the first notebook were applied in SQL to create the permanent table."
   ]
  },
  {
   "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": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# start with cohort created in notebook 1\n",
    "qry <- \"\n",
    "select * \n",
    "from tr_nj_2021.dbo.nb_cohort\n",
    "\"\n",
    "df_cohort <- dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see cohort\n",
    "head(df_cohort)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Matching Cohort to Wage Records\n",
    "\n",
    "Before we can begin to understand the employment outcomes for the cohort established in the first notebook, we need to be able to link the cohort to the wage records. This section will walk you through the entire linkage procedure."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Understanding the Linkage\n",
    "\n",
    "Before we can begin the linking process, we need to understand how we can link the two tables. Let's take a look at the `ds_nj_dol.dbo.ui_wages` table and see if we can spot any common variables by which we can create a potential linkage."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see ui wage records\n",
    "qry <- \"\n",
    "select top 5 *\n",
    "from ds_nj_dol.dbo.ui_wages\n",
    "where yrq = 20042\n",
    "\"\n",
    "wage_tbl <- dbGetQuery(con, qry)\n",
    "\n",
    "wage_tbl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, the variable `hashed_ssn` is common to each of the two tables. However, we only want to find employment outcomes within a limited analysis time frame."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Checkpoint 1: Time Travel**\n",
    "\n",
    "Given the available variables in `wage_tbl` and `df_cohort`, can you identify potential variables we can use to define a specific time frame (up to three years post-graduation)? Refer to the data dictionaries for complete column definitions.\n",
    "\n",
    "> Note: You don't need to perform the linkage--we will be doing that in a handful of code cells--but please think about potential variables we might be able to use in the future."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# which variables? \n",
    "# hint: you can use this space to explore the two data frames with glimpse(), head(), names(), or str() for example \n",
    "# if you are stuck\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "--------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Manipulation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are a few different ways we can approach linking these two tables so that they satisfy a specific time constraint. Although the solution presented may not line up with your answer to Checkpoint 1, it is one that can be applied to a lot of other scenarios.\n",
    "\n",
    "The general idea is that we will create new variables in each of the tables that represent graduation and job information in terms of calendar dates. From there, we can take advantage of SQL and R's date-specific functions to extract data within a three-year timespan. To create these new variables, we will need to use `new_awarddate` and `awardyearn` from `nb_cohort`, and `yrq` from `ui_wages`.\n",
    "\n",
    "First, let's create the variable tracking job dates in the wage records table. For consistency, we will convert each `yrq` value (year + quarter of employment) to the first day of the given financial quarter (i.e. Q1: January 1, Q2: April 1, etc.)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Wage Records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see yrq\n",
    "wage_tbl %>%\n",
    "    select(yrq)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get the month of the first day of the quarter, you can take each quarter, multiply it by 3, and then subtract 2. Once the date is formatted as mm/dd/yyyy, we can use `lubridate`'s `mdy` function to convert the variable to a date.\n",
    "\n",
    "> Using the `lubridate` package, you could also convert a character type of yyyy/mm/dd to a date with the function `ymd`. Other date formats are supported as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add date\n",
    "# the month needs to be converted from a character type to integer to allow for the multiplication and substraction\n",
    "wage_tbl %>%\n",
    "    mutate(\n",
    "        job_date = mdy(paste(as.integer(substring(yrq, 5, 5))*3-2, '01', substring(yrq, 1, 4), sep=\"/\"))\n",
    "    ) %>%\n",
    "    select(yrq, job_date)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The SQL-translated version of this updated data frame is available in the table `nb_cohort_wages_dated`. For more information about how the table was created in SQL, refer to the supplemental table creation notebook.\n",
    "\n",
    "> Note: `nb_cohort_wages_dated` is further restricted to only include wages from individuals in `nb_cohort_dated`. This is done to limit the time it takes to conduct the linkage process."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Graduates Cohort"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While the data manipulation process for `df_cohort` will follow the same logical approach, it will require one extra step, as there is not an easy trick for getting the graduation months to correspond to the first month within each fiscal quarter. Therefore, we will first need to create a variable corresponding to the first month in each quarter given the graduation month, and then we can use the information at hand to create a rough graduation date. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# new month for new_awarddate\n",
    "df_cohort_mth <- df_cohort %>%\n",
    "    mutate(\n",
    "        matched_awd_mth = case_when(\n",
    "            substring(new_awarddate, 1, 2) %in% c(\"01\", \"02\", \"03\") ~ \"01\",\n",
    "            substring(new_awarddate, 1, 2) %in% c(\"04\", \"05\", \"06\") ~ \"04\",\n",
    "            substring(new_awarddate, 1, 2) %in% c(\"07\", \"08\", \"09\") ~ \"07\",\n",
    "            substring(new_awarddate, 1, 2) %in% c(\"10\", \"11\", \"12\") ~ \"10\",\n",
    "        )\n",
    "    )\n",
    "\n",
    "# see month mapping in practice (now maps to quarters/semesters)\n",
    "df_cohort_mth %>%\n",
    "    select(new_awarddate, matched_awd_mth) %>%\n",
    "    head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have coded months, we can create a new variable `grad_date` using an approach similar to our approach for creating `job_date`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add date\n",
    "df_cohort <- df_cohort_mth %>%\n",
    "    mutate(\n",
    "        grad_date = mdy(paste(matched_awd_mth, '01', awardyearn, sep=\"/\"))\n",
    "    )\n",
    "\n",
    "df_cohort %>%\n",
    "    select(grad_date, new_awarddate) %>%\n",
    "    head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These two steps are combined using a CTE in SQL and written to the table `nb_cohort_dated`. For more information about how the table was created in SQL, refer to the supplemental table creation notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Joining Updated Tables\n",
    "\n",
    "At this point, we have two permanent tables stored in the `tr_nj_2021` database at our disposal to find employment history up to *REDACTED* years after graduation: `nb_cohort_dated` (graduates) and `nb_cohort_dated_wages` (wage records). We can link these two tables based on common `hashed_ssn` values and limit the time frame using SQL's date functions. As a reminder, we would not be able to perform this linkage in R due to memory issues present in storing the wage records as a data frame.\n",
    "\n",
    "We will use a simple `join` statement and add our time constraints to the `where` clause. The time constraint will be implemented by only taking `job_date` values that occur within REDACTED quarters (REDACTED years plus REDACTED quarter) of graduation. \n",
    "\n",
    "The date-specific function we will use in SQL is `dateadd`, as it allows us to add different time intervals to date variables.\n",
    "\n",
    "> Note: We will filter out all wage records with `wage` values of 0, as employers are only required to report non-zero wages paid during the quarter. In this notebook, we will assume that the records with 0 quarterly wages are reflective of HR efforts to prepare to report future earnings instead of employment with 0 wages earned in the quarter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## see linked wages 3 years out\n",
    "qry <- \"\n",
    "select cd.*, w.hashed_ein, w.wage, w.weeks, w.yrq, w.filedate, w.job_date\n",
    "from tr_nj_2021.dbo.nb_cohort_dated cd\n",
    "join tr_nj_2021.dbo.nb_cohort_wages_dated w\n",
    "on cd.hashed_ssn = w.hashed_ssn\n",
    "where w.job_date >= cd.grad_date and dateadd(quarter, 13, cd.grad_date) >= w.job_date and wage > 0\n",
    "\"\n",
    "df_wages <- dbGetQuery(con, qry)\n",
    "\n",
    "head(df_wages)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Checkpoint 2: Time-Keeping**\n",
    "\n",
    "Adjust the query above to only include wage records in the two years (8 quarters) prior to graduation. Return five rows to confirm your results. (Hint: you can re-use the dateadd function by switching the order of the variables and reversing the inequality signs.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# adjust time interval\n",
    "# replace ___ with proper where clause\n",
    "qry <- \"\n",
    "select top 5 cd.*, w.hashed_ein, w.wage, w.weeks, w.yrq, w.filedate, w.job_date\n",
    "from tr_nj_2021.dbo.nb_cohort_dated cd\n",
    "join tr_nj_2021.dbo.nb_cohort_wages_dated w\n",
    "on cd.hashed_ssn = w.hashed_ssn\n",
    "where ___\n",
    "\"\n",
    "dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "--------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Quick Exploration\n",
    "\n",
    "Before we start evaluating different employment measures, we should get a better grasp of the data frame. Let's start by finding the number of individuals, as well as jobs that were linked across the two tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see number of jobs and individuals employed in nj according to ui records\n",
    "df_wages %>%\n",
    "    summarize(\n",
    "        n_ind = n_distinct(hashed_ssn),\n",
    "        n_jobs = n()\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For reference, you can compare the number of individuals who were employed in at least one quarter according to the wage records within their first 13 quarters after graduation with the total number of individuals in the original cohort."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cohort %>%\n",
    "    summarize(\n",
    "        n_ind = n_distinct(hashed_ssn)\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Does this proportion surprise you?\n",
    "\n",
    "Additionally, we assume that there should be one entry for each individual-employer-quarter combination. Let's confirm that assumption by counting the number of entries within each `hashed_ssn`-`hashed_ein`-`yrq` combination."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see if people have multiple filings for same employer and year quarter\n",
    "df_wages %>%\n",
    "    group_by(hashed_ssn, hashed_ein, yrq) %>%\n",
    "    count() %>%\n",
    "    arrange(desc(n)) %>%\n",
    "    head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It turns out that it is possible to have more than one observation within a `hashed_ssn`-`hashed_ein`-`yrq` combination. This occurs because employers are allowed to re-file to correct previously submitted employment records. To only keep the most recent records, we select the most recent `filedate` within this combination."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# unduplicate wages\n",
    "df_wages_undup <- df_wages %>%\n",
    "    arrange(hashed_ssn, hashed_ein, yrq, desc(filedate)) %>%\n",
    "    distinct(hashed_ssn, hashed_ein, yrq, .keep_all=TRUE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see number of jobs and individuals employed in nj according to ui records\n",
    "df_wages_undup %>%\n",
    "    summarize(\n",
    "        n_ind = n_distinct(hashed_ssn),\n",
    "        n_jobs = n()\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For reference, by comparing the `n_jobs` within the code cell above and the one for `df_wages`, you can see that relatively few records contained duplicate entries differing by `filedate`. However, it is important to make sure information is not being potentially duplicated or miscast. Let's confirm that we successfully unduplicated the `df_wages_undup`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see that people can have multiple filings for same employer and year quarter\n",
    "df_wages_undup %>%\n",
    "    group_by(hashed_ssn, hashed_ein, yrq) %>%\n",
    "    count() %>%\n",
    "    arrange(desc(n)) %>%\n",
    "    head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`df_wages_undup` is saved as a table in the `tr_nj_2021` database as `nb_cohort_wages_link`. The code to create `nb_cohort_wages_link` is available in the supplemental notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Employment Outcomes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Connecting education data to employment data is only the first part of understanding the outcomes for New Jersey graduates. There are many ways that one could define and evaluate these outcomes. We present a few here and several more in the Appendix. While working through this section, think through what outcomes would be most relevant for your research question."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will look at the following outcomes:\n",
    "- Average quarterly earnings and the total workers employed by quarter (data: all wages)\n",
    "    - Distribution by major\n",
    "- Average quarterly earnings and the total workers employed by quarter (data: dominant wages)\n",
    "    - Distribution by major\n",
    "- Full quarter employment (data: full quarter wages, non-dominant)\n",
    "    - Distribution by sex\n",
    "- Employment patterns (data: dominant wages)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Average quarterly earnings and number employed by quarter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As mentioned earlier, we plan to focus on the first 12 quarters post-graduation for each individual. However, `df_wages_undup` currently contains employment outcomes in the quarter of graduation, as well as 13 quarters post-graduation. To isolate each quarter post-graduation, we will create a new variable, `quarter_number`, which uses some of R's functionality when it comes to working with date variables. After converting `grad_date` and `job_date` to date objects in R, we can calculate the difference in weeks between the two values (thanks to `difftime`) by dividing by 13 since there are roughly 13 weeks in each quarter and rounding to the nearest whole number."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get quarter from graduation\n",
    "df_wages_undup <- df_wages_undup %>%\n",
    "    mutate(\n",
    "        quarter_number = round(as.double(difftime(as.Date(job_date), as.Date(grad_date), units = \"weeks\")/13), 0)\n",
    "    )\n",
    "\n",
    "# see evidence\n",
    "df_wages_undup %>%\n",
    "    select(grad_date, job_date, quarter_number) %>%\n",
    "    head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With `quarter_number`, we can sum each individual's total earnings by quarter, excluding all observations where `quarter_number` is either 0 or 13."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ignore quarters 0 and 13\n",
    "df_wages_undup <- df_wages_undup %>%\n",
    "    filter(!(quarter_number %in% c(0, 13)))\n",
    "\n",
    "# find quarterly wages\n",
    "df_wages_undup %>%\n",
    "    group_by(hashed_ssn, quarter_number) %>%\n",
    "    summarize(\n",
    "        quarterly_wages = sum(wage),\n",
    "    ) %>%\n",
    "    ungroup() %>%\n",
    "    head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's save these results to the data frame `quarterly_wages`, so we can compute the cohort average quarterly earnings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save as quarterly_wages\n",
    "quarterly_wages <- df_wages_undup %>%\n",
    "    group_by(hashed_ssn, quarter_number) %>%\n",
    "    summarize(\n",
    "        total_wages = sum(wage),\n",
    "    ) %>%\n",
    "    ungroup()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have `quarterly_wages` to capture each individual's total quarterly earnings, we can compute the cohort's average quarterly earnings and the total number of individuals employed, broken down by quarter after graduation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# average wages and number of grads with wages by quarter after graduation\n",
    "avg_and_num <- quarterly_wages %>%\n",
    "    group_by(quarter_number) %>%\n",
    "    summarize(\n",
    "        mean_wage = mean(total_wages),\n",
    "        n_employed = n_distinct(hashed_ssn)\n",
    "    )\n",
    "\n",
    "avg_and_num"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that after about REDACTED quarters post-graduation, the number of individuals employed in New Jersey in our cohort begins to REDACTED, but that over time, the average quarterly earnings REDACTED. Keep in mind that the `mean_wage` encompasses average quarterly earnings, so if an individual had multiple earning sources in a quarter, we are currently taking the sum of them. Let's see if we see similar trends amongst those receiving the most common degrees within the cohort."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### By Major"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Recall the first data exploration notebook, where we identified the most common degrees earned within the cohort. We used the following code to isolate the most common majors:\n",
    "\n",
    "    df_common_major <- df %>%\n",
    "        count(cip_family) %>%\n",
    "        arrange(desc(n)) %>%\n",
    "        mutate(\n",
    "            prop = n/sum(n)\n",
    "        ) %>%\n",
    "        head(10)\n",
    "        \n",
    "Let's see if there are consistent trends relative to the entire cohort of earners for those who received degrees in the two most common fields. Recall that whereas the cohort was saved in the data frame `df` in the first notebook, it is saved as `df_cohort` here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2 most common majors\n",
    "com_majors <- df_cohort %>%\n",
    "    count(cip_family, cip2) %>%\n",
    "    arrange(desc(n)) %>%\n",
    "    mutate(\n",
    "        prop = n/sum(n)\n",
    "    ) %>%\n",
    "    head(2)\n",
    "\n",
    "com_majors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have the two most common majors, we can match this to `df_wages_undup` and evaluate earnings and the number of individuals employed within these two majors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# earnings and number employed for most common majors\n",
    "avg_and_num_major <- df_wages_undup %>%\n",
    "    group_by(hashed_ssn, quarter_number, major) %>%\n",
    "    summarize(\n",
    "        total_wages = sum(wage)\n",
    "    ) %>%\n",
    "    ungroup() %>%\n",
    "    filter(substring(major, 1,2) %in% com_majors$cip2) %>%\n",
    "    group_by(substring(major, 1, 2), quarter_number) %>%\n",
    "    summarize(\n",
    "        mean_wage = mean(total_wages),\n",
    "        n_employed = n_distinct(hashed_ssn)\n",
    "    )\n",
    "\n",
    "avg_and_num_major"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Naturally, we may wonder if we see the same trend amongst `mean_wage` when isolating for dominant earnings within a quarter, the job where the individual had the highest wages per quarter."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Average quarterly earnings and number employed by quarter (Dominant Earnings)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we can perform our analysis, we need to restrict `df_wages_undup` to only include the highest earnings per quarter for each individual. We will take a similar approach to when we unduplicated wage records, by first arranging employment for each individual/quarter combination by descending wages before taking the highest earnings. We will save this resulting data frame as `df_dom_wages`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# identify dominant wages in each quarter\n",
    "df_dom_wages <- df_wages_undup %>%\n",
    "    arrange(hashed_ssn,yrq, desc(wage)) %>%\n",
    "    distinct(hashed_ssn, yrq, .keep_all=TRUE)\n",
    "\n",
    "head(df_dom_wages)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# confirm we have one entry for each hashed_ssn/yrq combination\n",
    "df_dom_wages %>%\n",
    "    count(hashed_ssn, yrq) %>%\n",
    "    arrange(desc(n)) %>%\n",
    "    head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have isolated dominant earnings, we can recycle the same code that we applied to `df_wages_undup`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# average wages and number of grads with wages by quarter after graduation\n",
    "avg_and_num_dom <- df_dom_wages %>%\n",
    "    group_by(quarter_number) %>%\n",
    "    summarize(\n",
    "        mean_wage = mean(wage),\n",
    "        n_employed = n_distinct(hashed_ssn)\n",
    "    )\n",
    "\n",
    "avg_and_num_dom"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Interestingly enough, you can see that while a similar trend appears within `mean_wage`, it does not REDACTED for every REDACTED. Let's see if this trend is similar for graduates of the two most common majors."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### By Major"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# average wages and number of grads with wages by quarter after graduation for common majors\n",
    "avg_and_num_dom_major <- df_dom_wages %>%\n",
    "    group_by(hashed_ssn, quarter_number, major) %>%\n",
    "    summarize(\n",
    "        total_wages = sum(wage)\n",
    "    ) %>%\n",
    "    ungroup() %>%\n",
    "    filter(substring(major, 1,2) %in% com_majors$cip2) %>%\n",
    "    group_by(substring(major, 1, 2), quarter_number) %>%\n",
    "    summarize(\n",
    "        mean_wage = mean(total_wages),\n",
    "        n_employed = n_distinct(hashed_ssn)\n",
    "    )\n",
    "\n",
    "avg_and_num_dom_major"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Full-Quarter Employment\n",
    "Interestingly enough, you can see different REDACTED growth experiences, on average, between graduates of these two majors. However, these two employment measures, using total quarterly and dominant wages, do not include any measure of employment stability, which is often an important job aspect, especially for recent graduates.\n",
    "    \n",
    "There are many ways in which to define stable employment. Sometimes, it may be useful to define stable employment by a consecutive number of quarters worked with the same employer. Other times, stable employment may assume an alternative definition. Here, we will define stable employment as REDACTED employment. REDACTED employment in quarter *t* is indicated by a presence of wages with the same employer in quarters *t-1*, *t*, and *t+1*.\n",
    "\n",
    "**Example: Data Needed to Assess Full-Quarter (FQ) Employment for Person 1/Employer A in Each of the 4 Quarters Post-Graduation**\n",
    "\n",
    "|Person/Employer Combination|High Degree |FQ YearQtr |t-1|t|t+1|\n",
    "|---|---|---|---|---|---|\n",
    "|_Person 1/Employer A_ |_2013 Q3_ |_2013 Q4_ |<font color=green>2013 Q3</font> |**2013 Q4** |2014 Q1 |\n",
    "|_Person 1/Employer A_ |_2013 Q3_ |_2014 Q1_ |2013 Q4 |**2014 Q1** |2014 Q2 |\n",
    "|_Person 1/Employer A_ |_2013 Q3_ |_2014 Q2_ |2014 Q1 |**2014 Q2** |2014 Q3 |\n",
    "|_Person 1/Employer A_ |_2013 Q3_ |_2014 Q3_ |2014 Q2 |**2014 Q3** |<font color=green>2014 Q4</font> |\n",
    "\n",
    "As can be seen in the table above, calculating REDACTED employment for the same four quarter span requires REDACTED additional quarters of wage information. This requires us to extend our data frame to include the employment quarter of graduation as well as one additional quarter after our final quarter of interest. In this example, it means including employment in the quarter prior to quarter REDACTED and the quarter after quarter REDACTED. Now, it should be clear as to why we initially brought in REDACTED quarters worth of data, as we need all REDACTED quarters to calculate REDACTED employment within the REDACTED on which we want to focus.\n",
    "\n",
    "In practice, to isolate instances of REDACTED employment, we can do so by creating three copies of our `nb_cohort_wages_link` table, and matching them based on `hashed_ein` and `hashed_ssn` values while accounting for `job_date` differences amounting to quarters t-1, t, and t+1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get full quarter instances\n",
    "qry <- \"\n",
    "select b.hashed_ssn, b.hashed_ein, b.wage, b.job_date, b.grad_date, b.sex, b.major\n",
    "from tr_nj_2021.dbo.nb_cohort_wages_link a, tr_nj_2021.dbo.nb_cohort_wages_link b, tr_nj_2021.dbo.nb_cohort_wages_link c\n",
    "where a.hashed_ssn = b.hashed_ssn and a.hashed_ein = b.hashed_ein and a.job_date = dateadd(month, 3, b.job_date)\n",
    "and a.hashed_ssn = c.hashed_ssn and a.hashed_ein = c.hashed_ein and b.job_date = dateadd(month, 3, c.job_date)\n",
    "\"\n",
    "full_q_wages <- dbGetQuery(con, qry)\n",
    "\n",
    "head(full_q_wages)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check how many individuals experienced at least one quarter of stable employment, as well as the number of employers by which members of the cohort experienced stable employment and their average wages."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see number with at least one quarter of full quarter, what are their average wages\n",
    "full_q_stats <- full_q_wages %>%\n",
    "    summarize(\n",
    "        num_individuals = n_distinct(hashed_ssn),\n",
    "        num_employers = n_distinct(hashed_ein),\n",
    "        avg_wage = mean(wage)\n",
    "    )\n",
    "\n",
    "full_q_stats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Is there a difference by sex? In our next subsection, we will try to answer this question."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### By Sex\n",
    "\n",
    "At the end of the first data exploration notebook, after exploring the most common majors within the cohort, we also analyzed the sex breakdown of the cohort. Here, we will compare the overall sex breakdown to that of those who experienced at least one quarter of full-quarter employment.\n",
    "\n",
    "Recall the code from the first notebook in the code cell below:      "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sex breakdown\n",
    "df_sex <- df_cohort %>%\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": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see number with at least one quarter of full quarter, what are their average wages\n",
    "full_q_stats_sex <- full_q_wages %>%\n",
    "    group_by(sex) %>%\n",
    "    summarize(\n",
    "        num_individuals = n_distinct(hashed_ssn),\n",
    "        num_employers = n_distinct(hashed_ein),\n",
    "        avg_wage = mean(wage)\n",
    "    ) %>%\n",
    "    mutate(\n",
    "        prop = num_individuals/sum(num_individuals)\n",
    "    )\n",
    "\n",
    "full_q_stats_sex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that roughly the REDACTED proportion exists, and interestingly enough, of those who experienced stable employment, those of REDACTED experienced REDACTED wages, on average."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Checkpoint 3: Stable Employment by Major**\n",
    "\n",
    "Recreate the table above (besides `prop`) for the two most commmon majors. Do the results surprise you?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# replace ___\n",
    "full_q_wages %>%\n",
    "    filter(substring(___, 1, 2) %in% com_majors$cip2) %>%\n",
    "    group_by(___) %>%\n",
    "    summarize(\n",
    "        num_individuals = n_distinct(hashed_ssn),\n",
    "        num_employers = n_distinct(hashed_ein),\n",
    "        avg_wage = mean(wage)\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Employment Patterns\n",
    "\n",
    "At the end of this section, we hope to have found the most common employment patterns for everyone in the original cohort, not just those who matched to the UI wage records. To start, let's get a sense of the amount of individuals that are missing from `df_dom_wages`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see size of original cohort\n",
    "df_cohort %>%\n",
    "    summarize(\n",
    "        num_inds = n_distinct(hashed_ssn)\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see amount of people with employment outcomes\n",
    "df_dom_wages %>%\n",
    "    summarize(n_distinct(hashed_ssn))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we manipulate any existing data frames, let's confirm that if we join `df_dom_wages` to `df_cohort`, the number of individuals where `quarter_number` (or pick any other variable unique to `df_dom_wages`) is equal to the difference in individuals between `df_cohort` and `df_dom_wages`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see that everyone with na quarter is equal to amount who didn't show up in df_dom_wages\n",
    "df_cohort %>%\n",
    "    left_join(df_dom_wages, by = \"hashed_ssn\") %>%\n",
    "    filter(is.na(quarter_number)) %>%\n",
    "    summarize(n_distinct(hashed_ssn))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have confirmed that our join should work as intended, as there are no instances of any observations that may be duplicated, we will join `df_dom_wages` to `df_cohort`. After doing so, we will set all instances where `quarter_number` is `NA` equal to 1, so that we will eventually be able to have REDACTED observations for each individual, one for each potential quarter of employment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set all where quarter is na equal to one so we can use complete\n",
    "full_wages <- df_cohort %>%\n",
    "    left_join(df_dom_wages, by = c(\"hashed_ssn\", \"grad_date\")) %>%\n",
    "    mutate(\n",
    "        quarter_number = ifelse(is.na(quarter_number), 1, quarter_number)\n",
    "    )\n",
    "\n",
    "# see potential quarter numbers\n",
    "full_wages %>%\n",
    "    distinct(quarter_number) %>%\n",
    "    arrange(quarter_number)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have all potential `hashed_ssn` values, as well as instances of all desired `quarter_number` values, we can leverage `complete`, which will add additional rows for any combinations of `hashed_ssn`/`quarter_number` that do not currently exist in `full_wages`. If the combination does not appear in `full_wages`, the resulting `wage` value will be `NA`, signifying the individual was not employed in this quarter. As verification, the number of rows should equal the number of individuals multiplied by REDACTED."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# complete file\n",
    "completed <- full_wages %>%\n",
    "    complete(hashed_ssn, quarter_number, fill=list(wage=NA))\n",
    "\n",
    "# see than n should be a multiple of n_dist\n",
    "completed %>%\n",
    "    summarize(\n",
    "        n = n(),\n",
    "        n_inds = n_distinct(hashed_ssn)\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see completed\n",
    "head(completed)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have created `completed`, we just need to aggregate and manipulate the data frame so that each column is a quarter, and each observation is an individual, with the corresponding columns indicating whether the individual was employed in the given quarter. To start, let's create a variable `wage_ind`, which will be \"yes\" if the individual was employed in the quarter, and \"no\" otherwise. Additionally, for each in column manipulation, we will change each `quarter_number` value from 1, 2, 3,..., 12 to Q1, Q2, Q3,..., Q12 and call this variable `quarter`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create wage_ind and quarter variables\n",
    "patterns <- completed %>%\n",
    "    mutate(\n",
    "        wage_ind = ifelse(is.na(wage), \"no\", \"yes\"),\n",
    "        quarter = paste(\"Q\",quarter_number, sep=\"\")\n",
    "    )\n",
    "\n",
    "head(patterns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we need to figure out how to \"pivot\" the data frame so that each column is a value of `quarter`, with `wage_ind` values for the `hashed_ssn` values. To do so, we will use `pivot_wider`, which allows us to take a tidy data frame (one observation per row) and \"widen\" it so that each column becomes values from what was previously a single column (`quarter`) and the rows are occupied by those from a corresponding column (`wage_ind`). \n",
    "\n",
    "After manipulating the data frame, we can aggregate by the quarter columns, and count the number of observations within each of these patterns to discover the most common employment patterns for the cohort."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# find most common employment patterns\n",
    "patterns <- patterns %>%\n",
    "    select(hashed_ssn, quarter, wage_ind) %>%\n",
    "    pivot_wider(names_from = quarter, values_from = wage_ind) %>%\n",
    "    group_by(Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12) %>%\n",
    "    summarize(cnt = n_distinct(hashed_ssn)) %>%\n",
    "    arrange(desc(cnt)) %>%\n",
    "    ungroup() %>%\n",
    "    mutate(\n",
    "        prop = percent(cnt/sum(cnt), .01)\n",
    "    ) \n",
    "\n",
    "head(patterns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Save as csvs\n",
    "\n",
    "Before we finish the notebook, let's save your work as .csv files so that they can be referenced in the Data Visualization notebook."
   ]
  },
  {
   "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",
    "# average quarterly earnings and number employed by quarter\n",
    "write_csv(avg_and_num, \"U:\\\\..\\\\NJ Training\\\\Results\\\\avg_and_num.csv\")\n",
    "\n",
    "# average quarterly earnings and number employed by quarter (common majors)\n",
    "write_csv(avg_and_num_major, \"U:\\\\..\\\\NJ Training\\\\Results\\\\avg_and_num_major.csv\")\n",
    "\n",
    "# average dominant quarterly earnings and number employed by quarter\n",
    "write_csv(avg_and_num_dom, \"U:\\\\..\\\\NJ Training\\\\Results\\\\avg_and_num_dom.csv\")\n",
    "\n",
    "# average dominant quarterly earnings and number employed by quarter (common majors)\n",
    "write_csv(avg_and_num_dom_major, \"U:\\\\..\\\\NJ Training\\\\Results\\\\avg_and_num_dom_major.csv\")\n",
    "\n",
    "# full quarter info\n",
    "write_csv(full_q_stats, \"U:\\\\..\\\\NJ Training\\\\Results\\\\full_q_stats.csv\")\n",
    "\n",
    "# full quarter info by sex\n",
    "write_csv(full_q_stats_sex, \"U:\\\\..\\\\NJ Training\\\\Results\\\\full_q_stats_sex.csv\")\n",
    "\n",
    "# employment patterns\n",
    "write_csv(patterns, \"U:\\\\..\\\\NJ Training\\\\Results\\\\patterns.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Appendix**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we provide code to look at additional employment outcomes and to develop industry codes. The appendix provides code for the following:\n",
    "- At least four consecutive quarters of full quarter employment (data: full quarter examples, non-dominant)\n",
    "- Wage progression within full quarter employment (data: full quarter wages, dominant)\n",
    "- Industry measures"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Stable Employment: 4 Consecutive quarters of Full-Quarter Employment\n",
    "When introducing full-quarter employment as a proxy for stable employment, we noted that in other situations, stable employment can be defined by a consecutive amount of quarters. In this next example, we will combine these two concepts, defining stable employment as the existence of full-quarter employment across four consecutive quarters, all with the same employer. Here, we will not isolate dominant wages, but rather consider all stable employment instances in this measure.\n",
    "\n",
    "At this point, we have already identified all instances of full-quarter employment and saved the results to `full_q_wages`. Now, we have to track consecutive quarters, or streaks, with employment for the same `hashed_ssn` and `hashed_ein`. To do so, we will first structure the data frame so that `job_date` values are arranged by `hashed_ssn`/ `hashed_ein` combinations. Then, after establishing a baseline of the first date of employment for each `hashed_ssn`/ `hashed_ein` combination, we can calculate the difference in quarters elapsed between the first instance and each future observation. Finally, we consider a streak to \"continue\" if the time elapsed (`interval`) is one quarter greater than the time elapsed of the previous observation (`lag(interval) + 1`) and count consecutive instances where we have consecutive quarters for each `hashed_ein`/`hashed_ssn` combination (`sequence(rle(consecutive)$lengths)`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# track streaks within each employer/individual combination\n",
    "track_streaks <- full_q_wages %>%\n",
    "    group_by(hashed_ssn, hashed_ein) %>%\n",
    "    arrange(hashed_ssn, hashed_ein, job_date) %>%\n",
    "    mutate(\n",
    "        first_job = min(job_date),\n",
    "        interval = round(as.double(difftime(as.Date(job_date), as.Date(first_job), units = \"weeks\")/13), 0),\n",
    "        consecutive = interval == lag(interval) + 1,\n",
    "        consecutive = ifelse(is.na(consecutive), lead(consecutive), consecutive)\n",
    "    ) %>%\n",
    "  mutate(\n",
    "    Streak = ifelse(consecutive == TRUE, sequence(rle(consecutive)$lengths), 0)\n",
    "  ) %>%\n",
    "    ungroup()\n",
    "\n",
    "head(track_streaks)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From here, we can `filter` `track_streaks` for any instances where `Streak` is 4."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see how many had a streak of at least 4\n",
    "track_streaks %>%\n",
    "    filter(Streak == 4) %>%\n",
    "    summarize(\n",
    "        n_distinct(hashed_ssn)\n",
    "    )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We started this section by analyzing wage growth and the number of individuals employed by quarter after graduation. The next example will help you get a better sense of wage progression for those experiencing full-quarter employment."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Full-quarter Employment Wage Growth and Time Elapsed\n",
    "\n",
    "Given the isolation of those experiencing full-quarter employment (`full_q_wages`), we can further our understanding of stable employment experiences by comparing wage progression within this subset. To do so, for each person, we will first identify dominant full-quarter wages within each quarter, taking the highest wages per quarter of full-quarter employment for each individual. After doing so, we will compare earnings between the first instance of full-quarter employment and the last, while also tracking the average time elapsed between these occurrences. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# identify dominant full quarter wages in each quarter\n",
    "full_q_wages_dom <- full_q_wages %>%\n",
    "    arrange(hashed_ssn,job_date, desc(wage)) %>%\n",
    "    distinct(hashed_ssn, job_date, .keep_all=TRUE)\n",
    "\n",
    "head(full_q_wages_dom)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have identified only the dominant full-quarter experiences for each individual, we will track wage growth and time elapsed by first creating two data frames, `first_full` and `last_full`, which track the first and last full-quarter employment experiences per `hashed_ssn`. Afterwards, we can combine these two data frames to create `full_history`, and then perform a simple subtraction between these columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get first quarter of earnings\n",
    "first_full <- full_q_wages_dom %>%\n",
    "    arrange(hashed_ssn, job_date) %>%\n",
    "    distinct(hashed_ssn, .keep_all = TRUE) %>%\n",
    "    rename(\n",
    "        job_date_first = job_date,\n",
    "        wage_first = wage\n",
    "    )\n",
    "\n",
    "# get last quarter of earnings\n",
    "last_full <- full_q_wages_dom %>%\n",
    "    arrange(hashed_ssn, desc(job_date)) %>%\n",
    "    distinct(hashed_ssn, .keep_all = TRUE) %>%\n",
    "    rename(\n",
    "        job_date_last = job_date,\n",
    "        wage_last = wage\n",
    "    ) %>%\n",
    "    select(-c(hashed_ssn, hashed_ein, grad_date, sex))\n",
    "\n",
    "full_history <- cbind(first_full, last_full)\n",
    "\n",
    "head(full_history)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see wage growth and time elapsed between first and last full quarter jobs in this time frame\n",
    "full_history %>% \n",
    "    mutate(\n",
    "        salary_growth = wage_last - wage_first,\n",
    "        quarter_diff = round(as.double(difftime(as.Date(job_date_last), as.Date(job_date_first), units = \"weeks\")/13), 0)\n",
    "    ) %>%\n",
    "    summarize(\n",
    "        mean_salary_growth = mean(salary_growth),\n",
    "        mean_quarter_diff = mean(quarter_diff)\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see there appears to be a REDACTED average salary REDACTED, as well as a REDACTED REDACTED between the REDACTED and REDACTED instances of REDACTED employment."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have demonstrated how to implement various measures of employment outcomes in R, you should have a better sense of the employment outcomes of the cohort. Of course, this is not an exhaustive list of examples, and you are encouraged to implement employment outcome measures that make most sense given your specific research question. For the last section of the wage exploration, we will try to understand common employment patterns over the course of the 12 quarters post-graduation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Developing industry measures\n",
    "\n",
    "Although important, working with industries introduces a separate level of complexity. The `ui_employer` table in the `ds_nj_dol` database contains REDACTED information by `hashed_fein` for the most recent quarter of wage records. Let's start by reading this data at the two-digit level into R as `df_employer`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# take employers, first two digits of naics\n",
    "# include where ind_active = 'Y' so only include active industries\n",
    "qry <- \"\n",
    "SELECT hashed_fein, substring(naics, 1, 2) as naics\n",
    "FROM ds_nj_dol.dbo.ui_employer\n",
    "where substring(naics, 1, 2) is not null and substring(naics, 1, 2) not in ('00', '99') and ind_active = 'Y'\n",
    "\"\n",
    "\n",
    "df_employer <- dbGetQuery(con,qry)\n",
    "\n",
    "head(df_employer)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ideally, each employer would be associated with one industry, or NAICS code. However, you can probably think of an employer that provides services in multiple industries, even when using two-digit NAICS codes. Let's see if there are any `hashed_fein` values with multiple NAICS codes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see max amount of unique naics for each employer\n",
    "df_employer %>%\n",
    "    group_by(hashed_fein) %>%\n",
    "    summarize(\n",
    "        num_naics = n_distinct(naics)\n",
    "    ) %>%\n",
    "    arrange(desc(num_naics)) %>%\n",
    "    head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are certain rules and decisions you may be able to make to choose between NAICS codes for employers, and most employers do not have multiple NAICS codes. However, we will leave it to your group to discuss potential ways to de-duplicate `hashed_fein` values. You can find the most common industries of dominant employers using the following code, where we join `df_employer` to `df_dom_wages` before finding the three most common industries.\n",
    "\n",
    "> Note: There was no industry de-duplication applied in this code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# find most common industries\n",
    "common_inds <- df_dom_wages %>%\n",
    "    left_join(df_employer, by = c(\"hashed_ein\" = \"hashed_fein\")) %>%\n",
    "    filter(!is.na(naics), naics != \"99\") %>%\n",
    "    group_by(naics) %>%\n",
    "    summarize(\n",
    "       n = n_distinct(hashed_ssn)\n",
    "    ) %>%\n",
    "    arrange(desc(n)) %>%\n",
    "    head(3)\n",
    "\n",
    "common_inds"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can find the industries associated with these NAICS codes using the `naics_descriptions` table in the `ds_public_1` database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read in naics descriptions\n",
    "qry <- \"\n",
    "select Code, Title \n",
    "from ds_public_1.dbo.naics_descriptions \n",
    "\"\n",
    "naics_groups <- dbGetQuery(con, qry)\n",
    "\n",
    "head(naics_groups)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get naics descriptions\n",
    "common_inds_desc <- common_inds %>%\n",
    "    left_join(naics_groups, c(\"naics\" = \"Code\"))\n",
    "\n",
    "head(common_inds_desc)"
   ]
  }
 ],
 "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
}