1.Data_Exploration_Cross-section_Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center> <img style=\"float: center;\" src=\"images/CI_horizontal.png\" width=\"450\">\n",
    "<center>\n",
    "    <span style=\"font-size: 1.5em;\">\n",
    "        <a href='https://www.coleridgeinitiative.org'>Website</a>\n",
    "    </span> \n",
    "\n",
    "# **<center> Data Exploration: Cross-sectional Analysis </center>**\n",
    "\n",
    "<a href=\"https://doi.org/10.5281/zenodo.4588936\"><img src=\"https://zenodo.org/badge/DOI/10.5281/zenodo.4588936.svg\" alt=\"DOI\"></a>\n",
    "\n",
    "Tian Lou, Dave McQuown"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **1. Introduction**\n",
    "In the spring of 2020, many states enacted stay-at-home orders and extensive business shutdowns to slow the spread of COVID-19. Within a few months, an unprecedented number of workers filed Unemployment Insurance (UI) claims, placing heavy demand on state governments' financial obligations. Using the state of Illinois for the month of April as an example, about half a million workers filed first time UI claims. The state government paid more than 700 million dollars to compensate 1.9 million claims.[<sup>1</sup>](#fn1) <a id = \"1\"> </a> Even as the state gradually began to open its economy, the number of unemployed workers remained high with many continuing to rely on UI benefits. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How can local workforce boards help these unemployed workers get back to work quickly and reduce governments' financial burdens? What information is the most useful for them to allocate resources and  plan strategically? How is UI claimants' reemployment affected by economic shocks? Throughout this project, we will explore Illinois UI claims data and use advanced data analytical methods to answer these questions. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook focuses on understanding the Illinois UI claims data (i.e., PROMIS file) and using it to construct two measures: weekly certified UI claimant counts and average weekly payments. We will start 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 the PROMIS file. We will create a cross-sectional sample and investigate the trends in Illinois UI claimant counts, their weekly total payments during the COVID recession, and how these two measures varied across industry. At the end of this notebook, we will save the summary statistics in csv files and use them in the visualization notebooks. As you work through the notebook, we will have checkpoints for you to practice on the code. As you work through the material presented in the notebook, think about how you might apply the techniques and code to your project. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **2. Learning Objectives**\n",
    "\n",
    "Throughout this series of notebooks, our overarching questions are: How can states best produce information that can be used by local workforce boards to help inform resource allocation for unemployed workers? What information is the most useful for workforce boards for strategic resource planning and efficient resource allocation? What measures are most useful to local workforce boards and how do we characterize variation in those measures? We will use Illinois administrative data, Bureau of Labor Statistics (BLS) workforce data, and Opportunity Insignts Economic Tracker data as an example and show you step by step how we develop a project, including: exploring the dataset, selecting a sample, defining outcome measures, conducting subgroup analyses, creating visualizations, and building a prediction machine learning model. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook, our focus is the **cross-sectional analysis**. After you finish this notebook, you should understand:\n",
    "- how to load R libraries and how to estabilish a connection to the Database\n",
    "- how to create a cross-sectional sample by using the PROMIS file\n",
    "- how to calculate weekly claimant counts and average weekly total pay\n",
    "- how to conduct subgroup analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Research Questions** \n",
    "In this notebook, we focus on seeking answers to the following questions: \n",
    "- What are the trends of Illinois certified UI claimant counts during the COVID-19 recession?\n",
    "- How do Illinois certified UI claimant counts vary by industry? Which industries had the most job losses during the COVID-19 recession?\n",
    "- What are the average weekly payments received by Illinois certified UI claimants?\n",
    "- How do Illinois certified UI claimants' weekly payments vary by industry?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Datasets** ####\n",
    "We will explore and understand the Illinois PROMIS file in this notebook:\n",
    "- **2020 Illinois PROMIS certified claims file**: weekly UI claims data. Each record represents a certified claim in a certain week. The data has a claimant's demographics, education level, prior industry, occupation, and locations. It also contains detailed information about the claim, such as program type, claim type, certification status, benefit starting date, and benefit amount. Federal Pandemic Unemployment Compensation (FPUC, $600/week) and dependent benefits are included in the total amount paid.\n",
    "\n",
    "The full certified claims data has more than 26 million rows. Using the full dataset for data exploration is time-consuming. Therefore, **we will use a 1% random sample of the certified claims data in all notebooks. You should also use the 1% random sample when walking through all notebooks and when identifying the scope of your project.** After you decide your research questions and analysis scope, only pull the data and the variables you need from the full dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Analytical Methods** ####\n",
    "You will work through various techniques of how to use SQL and R to explore the datasets in the ADRF and better understand what you are working with. 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. We will provide an introduction and examples for:\n",
    "\n",
    "- How to estabilish a connection to the database in R\n",
    "- How to create new tables from the larger tables in a database (sometimes called the \"analytical frame\")\n",
    "- How to explore different variables of interest\n",
    "- How to clean data\n",
    "- How to create aggregate metrics\n",
    "- How to generate descriptive statistics to describe a specific sample"
   ]
  },
  {
   "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": [
    "#### **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 eaiser 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: ETA Training\n",
    "- In the \"ETA 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-eta\\ETA Class Notebooks\\xwalks\"**.  At the end of this notebook, **we save summary statistics to \"U:\\\\FirstName.LastName.UserID\\ETA 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": {},
   "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 Database**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we are ready to connect to the database `tr_dol_eta`. We will create the database connection using the `DBI`  and `ODBC` libraries. \n",
    "\n",
    "> **Loading R functions** and **establishing database 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 database\n",
    "con <- DBI::dbConnect(odbc::odbc(),\n",
    "                     Driver = \"SQL Server\",\n",
    "                     Server = \"msssql01.c7bdq4o2yhxo.us-gov-west-1.rds.amazonaws.com\",\n",
    "                     Database = \"tr_dol_eta\",\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 20 rows of the Illinois PROMIS data, which is stored in the `il_des_promis_1pct` table inside the `tr_dol_eta` schema.\n",
    "\n",
    "First, we create a query as a `character` string object in R."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create query character string\n",
    "# Database name: tr_dol_eta\n",
    "# Schema name: dbo\n",
    "# Table name: il_des_promis_1pct\n",
    "query <- '\n",
    "SELECT TOP 20 *\n",
    "FROM tr_dol_eta.dbo.il_des_promis_1pct;\n",
    "'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use `TOP` to read in only the first 20 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 `query` as inputs to `dbGetQuery()` to read the data into an R data frame. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in data frame and save it in df\n",
    "df <- dbGetQuery(con,query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# See first few rows of df\n",
    "head(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Checkpoint 1: Explore Columns** \n",
    "\n",
    "Take a look at the columns in the table `il_des_promis_1pct` table. Which variables might be useful for your project?\n",
    "\n",
    "> Refer to the data dictionary on the class website to understand what the different variables mean."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace ____ with the table name\n",
    "query <- '\n",
    "SELECT TOP 20 *\n",
    "FROM tr_dol_eta.dbo.____;\n",
    "'\n",
    "\n",
    "# Read in data frame and save it in df\n",
    "df <- dbGetQuery(con,query)\n",
    "\n",
    "# Write code to explore the columns of df\n",
    "# Hint: there is more than one method. You can use head(), glimpse(), names(), etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **4. Create the Cross-sectional Sample**\n",
    "\n",
    "In this section, we will use the Illinois PROMIS certified claims file to create a sample of all certified claimants who received benefits since the week ending March 7th, 2020. Each week, PROMIS files are generated for both initial claims and certified claims. For the purpose of this class, **the `il_des_promis_1pct` table in `tr_dol_eta.dbo` only contains certified claims**.\n",
    "\n",
    "> The full dataset is in the table `il_des_promis`. It contains more than 26 million rows. `il_des_promis_1pct` is a 1% random sample. Using the full dataset for data exploration is time-consuming. Therefore, **we will only use the table `il_des_promis_1pct` in all notebooks. You should also only use the 1% random sample when walking through all notebooks and when identifying the scope of your analysis.** After you decide your research questions and analysis scope, only pull the data and the variables you need from the full dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Recall that an **initial claim** refers to a claim filed by a recently unemployed worker with state unemployment agencies to request a determination of basic eligibility for UI benefits. In Illinois, after filing the initial claim, the person will wait about a week to receive a letter which notify him/her when to **ceritify the claim**. During the certification process, the person will answer questions such as whether they have worked and whether they have actively looked for jobs in the past two weeks. If the person meets the eligibility requirements, he/she can receive UI benefits.[<sup>2</sup>](#fn2) <a id = \"2\"> </a>**In Illinois, regular UI claims need to be certified every two weeks.**[<sup>3</sup>](#fn3) <a id = \"3\"> </a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We usually analyze UI claimants' weekly outcomes, because they collect their benefits on a **calendar week** basis. A calendar week starts on Sunday and ends on Saturday. The PROMIS file has a variable, `week_end_date`, that indicates the last day of the calendar week. *It is always a Saturday.* We can use it to limit the data to weeks or after the first week of March 2020 (`2020-03-07`). In sections 5 and 6, we will also use `week_end_date` to aggregate the data to weekly level.  \n",
    "\n",
    "Moreover, there are different types of UI programs. In this class, we will focus on the **Regular state UI program**. Thus, we also restrict the sample to `program_type=1` and `sub_program_type=1`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select PROMIS certified claimant records from the database to a dataframe\n",
    "\n",
    "# Store SQL query to a variable\n",
    "query <- \"\n",
    "SELECT ssn_id,\n",
    "    week_end_date,\n",
    "    byr_start_week,\n",
    "    sub_program_type,\n",
    "    program_type,\n",
    "    claim_type,\n",
    "    birth_date,\n",
    "    gender,\n",
    "    race,\n",
    "    ethnicity,\n",
    "    disability,\n",
    "    education,\n",
    "    county_fips_code,\n",
    "    naics_code,\n",
    "    occupation_code,\n",
    "    total_pay\n",
    "FROM tr_dol_eta.dbo.il_des_promis_1pct\n",
    "WHERE sub_program_type = 1\n",
    "AND program_type = 1\n",
    "AND week_end_date >= '2020-03-07'\n",
    "\"\n",
    "\n",
    "# Execute query\n",
    "df_claimants <-dbGetQuery(con,query)\n",
    "\n",
    "# R interprets dates as character when pulling from the database, must convert with ymd()\n",
    "df_claimants <- df_claimants %>%\n",
    "    mutate(week_end_date=ymd(week_end_date),\n",
    "          byr_start_week=ymd(byr_start_week),\n",
    "          birth_date=ymd(birth_date))\n",
    "\n",
    "# See top records in the dataframe\n",
    "head(df_claimants)\n",
    "\n",
    "# Close the database connection\n",
    "dbDisconnect(con)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to join `df_claimants` with the county-region crosswalk, `reopening_regions.csv`, on `county_fips_code` to get region. In Checkpoint 2, you will need to use `region` to define your regional sample. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join/merge the DataFrame with the county-region crosswalk\n",
    "\n",
    "# Load Restore Illinois Health Regions\n",
    "region_dict <- read_csv('P:\\\\tr-dol-eta\\\\ETA Class Notebooks\\\\xwalks\\\\reopening_regions.csv', col_types = \"ic\") %>%\n",
    "    mutate(county_fips_code = substr(fips,3,5)) %>%\n",
    "    select(county_fips_code, region)\n",
    "\n",
    "#Left join region to certified claimants data\n",
    "df_claimants <- left_join(df_claimants, region_dict, by.y = \"county_fips_code\")\n",
    "\n",
    "# See top records in the dataframe\n",
    "head(df_claimants)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Checkpoint 2: Create Your Sample**\n",
    "In the class, we have asked you to identify a **region of interest**. Now use the DataFrame `df_claimants` we have created in the example to select your regional sample. \n",
    "\n",
    "> Values of `region`: Central, Cook County, North-Central, Northeast, Southern\n",
    "\n",
    "> Note that we use the string `reg` in object names to indicate dataframes and variables based on the regional subset used in the checkpoints. This includes all objects with names beginning with `df_reg`,`cs_reg`, or `reg`. Objects without `reg` in the name reflect the full State and are primarily used in the exmples rather than the checkpoints."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Subset the dataframe to a specific region\n",
    "# Replace ___ with your selected region\n",
    "df_reg_claimants <- df_claimants %>% filter(region == '___')\n",
    "\n",
    "# See top records in the dataframe\n",
    "head(df_reg_claimants)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **5. Weekly UI Claimant Count**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can aggregate `df_claimants` on `week_end_date` to get weekly claimant counts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aggregate cross-sectional dataset by benefit week ending date\n",
    "# and confirm that each person has one record each benefit week\n",
    "cs_counts <- df_claimants %>% \n",
    "    group_by(week_end_date) %>%\n",
    "    summarize(claimant_count_dist=n_distinct(ssn_id), claimant_count=n()) %>% # Count records and distinct SSNs\n",
    "    mutate(uniq_check = (claimant_count_dist == claimant_count)) # Compare the values calculated in the previous line\n",
    "\n",
    "# If the there are no weeks with a value of FALSE, there is only one record per claimant/week\n",
    "table(cs_counts$uniq_check)\n",
    "\n",
    "# Assuming no FALSE values, clean up the dataframe to keep only week_end_date and claimant_count\n",
    "cs_counts <- cs_counts %>% select(week_end_date, claimant_count)\n",
    "\n",
    "# See the top 15 records in the dataframe\n",
    "head(cs_counts, 15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that the number of certified claimants spiked in the REDACTED as people REDACTED as a result of the COVID-19 pandemic. The total number of claimants peaked the week ending REDACTED."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The COVID recession has hit a few industries especially hard, such as Accommodation and Food Services, Manufacturing, and Retail Trade. For example, compared to March, the national employment of the Accommodation and Food Services industry in April reduced by nearly 50%.[<sup>4</sup>](#fn4) <a id = \"4\"> </a> Next, we will break down Illinois weekly claimant counts by industry and investigate which industries have the most job losses and how many workers in these industries remain unemployed over time."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The variable `naics_code` in the `il_des_promis_1pct` table tells us a claimant's separation employer's industry. In the database, we have 6-digit NAICS codes. However, we will not directly use the 6-digit codes in the analysis, because it is too granular and will result in many small counts. These results will have very limited generalizability. **Mostly importantly, you will not be able to export any results based on less than 10 individuals from ADRF.** \n",
    "\n",
    "> You can use `select distinct(naics_code) from tr_dol_eta.dbo.il_des_promis_1pct;` in DBeaver to check the unique values of `naics_code` and use `select count(distinct(naics_code)) from tr_dol_eta.dbo.il_des_promis_1pct;` to check how many unique 6-digit NAICS codes are in the data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Therefore, we need to convert the 6-digit NAICS codes to 2-digit NAICS codes so that we have fewer industry groups. However, even if we use 2-digit NAICS codes, some industries still have very small number of UI claimants in some weeks, such as REDACTED. You can either suppress counts of these industries or combine them. In this notebook, we will combine the small industries so that we get consistent results with the Certified UI Claimants by Industry in the portal. See `naics_groups.csv` in the `P:\\tr-dol-eta\\ETA Class Notebooks\\xwalks` folder for how we combine small industries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert 6-digit NAICS codes to 2-digit NAICS codes by keeping only the first two characters\n",
    "df_claimants <- df_claimants %>% mutate(naics_maj_code = substr(naics_code,1,2))\n",
    "\n",
    "# See the top records in the dataframe\n",
    "head(df_claimants)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check the value of NAICS code to make sure there aren't any unknown values.\n",
    "# 2-digit NAICS codes list: 11,21,22,23,31,32,33,42,44,45,48,49,51,52,53,54,55,56,61,62,71,72,81,92\n",
    "table(df_claimants$naics_maj_code)\n",
    "\n",
    "# Remove anything not in the list of known NAICS major codes\n",
    "df_claimants <- df_claimants %>% filter(!(naics_maj_code %in% c('0','00','99')))\n",
    "\n",
    "# Check that it worked\n",
    "table(df_claimants$naics_maj_code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Combine NAICS major codes based on grouping used for UI dashboard \n",
    "# See `naics_groups.csv` in the P:\\tr-dol-eta\\ETA Class Notebooks\\xwalks folder\n",
    "naics_groups <- read_csv('P:\\\\tr-dol-eta\\\\ETA Class Notebooks\\\\xwalks\\\\naics_groups.csv', col_types = \"ccc\")\n",
    "\n",
    "# See the top records in the dataframe\n",
    "head(naics_groups)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join NAICS groupings to claimant dataset\n",
    "df_claimants <- left_join(df_claimants, naics_groups, by = 'naics_maj_code')\n",
    "\n",
    "# Break down the data by benefit week and NAICS code grouping and save it as a dataframe.\n",
    "cs_ind_counts <- df_claimants %>% \n",
    "    group_by(week_end_date, naics_maj_code_rv, naics_maj_desc_rv) %>%\n",
    "    summarize(claimant_count=n_distinct(ssn_id))\n",
    "    \n",
    "# Show top records in the dataframe\n",
    "head(cs_ind_counts)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What were the top 3 industries at the peak week of REDACTED?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show counts for all industries in descending order\n",
    "cs_ind_counts %>%\n",
    "    filter(week_end_date == ymd(\"REDACTED\")) %>%\n",
    "    arrange(desc(claimant_count))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that the industries with the most certified claimants during the benefit week ending REDACTED are REDACTED, REDACTED, and REDACTED. How many claimants are there from these industries four weeks later, the week ending REDACTED?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show counts of certified claimants in the REDACTED \n",
    "# industries the week ending REDACTED\n",
    "cs_ind_counts %>%\n",
    "    filter(week_end_date == ymd(\"REDACTED\")) %>%\n",
    "    filter(naics_maj_code_rv %in% c(REDACTED))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compared to the week ending REDACTED, the number of certified claimants in the week ending REDACTED was about the same for REDACTED but had declined slightly for REDACTED and REDACTED."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Checkpoint 3: Calculate Weekly UI Claimant Counts and Break Down Your Sample by Variables of Interest**\n",
    "\n",
    "First, calculate weekly UI claimant counts by week using the regional sample you created in Checkpoint 2. During which week did the number of certified claimants in your region peak? Is it the same as the state level trend (i.e., peaked during the week ending REDACTED)?\n",
    "\n",
    "> Note that we use the string `reg` in object names to indicate dataframes and variables based on the regional subset used in the checkpoints. This includes all objects with names beginning with `df_reg`,`cs_reg`, or `reg`. Objects without `reg` in the name reflect the full State and are primarily used in the exmples rather than the checkpoints."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aggregate regional cross-sectional dataset by benefit week ending date\n",
    "# and confirm that each person has one record each benefit week\n",
    "# Which variable should you fill in ___ ?\n",
    "cs_reg_counts <- df_reg_claimants %>% \n",
    "    group_by(___) %>%\n",
    "    summarize(claimant_count_dist=n_distinct(ssn_id), claimant_count=n()) %>% # Count records and distinct SSNs\n",
    "    mutate(uniq_check = (claimant_count_dist == claimant_count)) # Compare the values calculated in the previous line"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# If the there are no weeks with a value of FALSE, there is only one record per claimant/week\n",
    "# Fill in ___ with the appropriate column\n",
    "table(cs_reg_counts$___)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assuming no FALSE values, clean up the dataframe to keep only week_end_date and claimant_count\n",
    "# Fill in ___ with the appropriate columns\n",
    "cs_reg_counts <- cs_reg_counts %>% select(___, ___)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Second, in addition to separation industry, the PROMIS file contains detailed information about a claimant's demographics, occupation and educational attainment. Choose one dimension to conduct your subgroup analysis. What are the top demographic groups/industries/education groups during the peak week? What are the trends in these top groups in the subsequent weeks?\n",
    "\n",
    "Similar to industry, some of these variables are at very granular level. Here are some suggested methods of how to regroup these variables to avoid small counts.\n",
    "\n",
    "- **Age Group**: 14-24, 25-34, 35-44, 45-54, 55-64, 64-99; remove anyone younger than 14 or older than 99\n",
    "- **Race**: white, African American, other race, race unknown\n",
    "- **Education Level**: less than 12 years, high school graduate, Associate's degree, Bachelor's degree, Master degree or higher, education unknown\n",
    "- **Occupation**: use 2-digit occupation codes and combine small categories if needed\n",
    "\n",
    "> <font color='red'>**Note that if you want to break down your regional sample by industry, you need to create the industry category by following the code showed in the example.**</font> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example of recoding education level into the groups above\n",
    "# You may replace this with a dimension and grouping scheme of your choosing\n",
    "\n",
    "df_reg_claimants <- df_reg_claimants %>%\n",
    "    mutate(educ_recode = case_when (education >= 1 & education <= 13 ~ \"Less than HS graduate\",\n",
    "                                    education >= 14 & education <= 18 ~ \"HS graduate to some college\",\n",
    "                                    education >= 19 & education <= 20 ~ \"Associate's degree\",\n",
    "                                    education >= 21 & education <= 22 ~ \"Bachelor's degree\",\n",
    "                                    education >= 23 ~ \"Master's degree or higher\",\n",
    "                                    TRUE ~ \"Other\"))\n",
    "\n",
    "# Show top records in the dataframe\n",
    "head(df_reg_claimants)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aggregate by benefit week and subgroup\n",
    "# Replace ___ with the subgroup variable\n",
    "\n",
    "cs_reg_sub_counts <- df_reg_claimants %>% \n",
    "    group_by(week_end_date, ___) %>%\n",
    "    summarize(claimant_count=n_distinct(ssn_id))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **6. Weekly Total Pay**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another outcome measure we are interested in is the amount of benefits received by certified UI claimants. We will use the field `total_pay`, which is the total amount paid to the claimant for the week. This includes regular UI benefits, dependent allowances, and supplements such as Federal Pandemic Unemployment Compensation. It excludes deductions for part time wages earned and tax withholding."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the state-level average total pay amount by week\n",
    "cs_amounts <- df_claimants %>% \n",
    "    group_by(week_end_date) %>%\n",
    "    summarize(avg_total_pay = mean(total_pay))\n",
    "\n",
    "# Show top 25 records in the dataframe\n",
    "head(cs_amounts, 25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The average weekly amount paid was strongly affected by the Fededral Pandemic Unemployment Compensation (FPUC) program, which granted each claimant an additional $600 per week in benefits. We can see that the average weekly amount paid REDACTED times the week ending REDACTED when the program began, and REDACTED the week ending REDACTED. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, let's see how the average weekly total pay varies by industry. We will focus on the top 3 industries with the most claimants during the peak week."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate state-level average total pay by industry for the 3 industries with the most claimants the overall peak week\n",
    "cs_ind_amounts <- df_claimants %>% \n",
    "    group_by(week_end_date, naics_maj_code_rv) %>%\n",
    "    summarize(avg_total_pay = mean(total_pay)) %>%\n",
    "    filter(naics_maj_code_rv %in% c(REDACTED))\n",
    "\n",
    "# Show top records in the dataframe\n",
    "head(cs_ind_amounts, 25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similar to the overall state figures, average weekly total pay by industry is heavily impacted by FPUC and experiences REDACTED when that program begins and ends. Each of these industries has a REDACTED average weekly total paid than the state of Illinois overall. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Checkpoint 4: Calculate the Average Weekly Total Pay for Your Regional Sample**\n",
    "Now calculate the average weekly total pay of claimants in your region of interest. Then calculate average weekly total pay by your subgroup of interest for claimants in your region. Compare your results with the state-level average weekly benefits. Do claimants in your region of interest and subgroup of interest receive higher or lower benefits on average?\n",
    "\n",
    "> Note that we use the string `reg` in object names to indicate dataframes and variables based on the regional subset used in the checkpoints. This includes all objects with names beginning with `df_reg`,`cs_reg`, or `reg`. Objects without `reg` in the name reflect the full State and are primarily used in the exmples rather than the checkpoints."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate regional total pay amount by week, \n",
    "# using the DataFrame df_reg_claimants, which includes regional claims data and the subgroup variable you created in Checkpoint 3\n",
    "# Replace ___ with the DataFrame, the benefit week, and the total pay field\n",
    "cs_reg_amounts <- ___ %>% \n",
    "    group_by(___) %>%\n",
    "    summarize(avg_total_pay = mean(___))\n",
    "\n",
    "head(cs_reg_amounts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aggregate by benefit week and a subgroup of your choice.\n",
    "# Replace ___ with the appropriate values\n",
    "cs_reg_sub_amounts <- ___ %>% \n",
    "    group_by(week_end_date, ___) %>%\n",
    "    summarize(avg_total_pay = mean(___))\n",
    "\n",
    "head(cs_reg_sub_amounts)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **7. Export Results to .csv Files**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now you have successfully finished your cross-sectional analysis! The last step is to save your results in .csv files. We will use these files in the Data Visualization notebook and show you how to create various graphs."
   ]
  },
  {
   "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",
    "#weekly UI claimant counts\n",
    "write.csv(cs_counts, \"U:\\\\..\\\\ETA Training\\\\Results\\\\cs_counts.csv\", row.names=F)\n",
    "\n",
    "#weekly UI claimant counts by industry\n",
    "write.csv(cs_ind_counts, \"U:\\\\..\\\\ETA Training\\\\Results\\\\cs_ind_counts.csv\", row.names=F)\n",
    "\n",
    "#weekly UI average benefit amounts\n",
    "write.csv(cs_amounts, \"U:\\\\..\\\\ETA Training\\\\Results\\\\cs_amounts.csv\", row.names=F)\n",
    "\n",
    "#weekly UI benefit amounts by industry\n",
    "write.csv(cs_ind_amounts, \"U:\\\\..\\\\ETA Training\\\\Results\\\\cs_ind_amounts.csv\", row.names=F)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Checkpoint 5: Save Your Results**\n",
    "Save the results you get in Checkpoints 3 and 4 in .csv files. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the outputs of your checkpoint work as CSV\n",
    "\n",
    "#Counts by week for your region of interest\n",
    "write.csv(cs_reg_counts, \"U:\\\\..\\\\ETA Training\\\\Results\\\\cs_reg_counts.csv\", row.names=F)\n",
    "\n",
    "# Counts by week for your selected region and dimension grouping\n",
    "write.csv(cs_reg_sub_counts, \"U:\\\\..\\\\ETA Training\\\\Results\\\\cs_reg_sub_counts.csv\", row.names=F)\n",
    "\n",
    "# Average total pay by week for your region of interest\n",
    "write.csv(cs_reg_amounts, \"U:\\\\..\\\\ETA Training\\\\Results\\\\cs_reg_amounts.csv\", row.names=F)\n",
    "\n",
    "# Average amounts by week for your selected region and dimension grouping\n",
    "write.csv(cs_reg_sub_amounts, \"U:\\\\..\\\\ETA Training\\\\Results\\\\cs_reg_sub_amounts.csv\", row.names=F)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **Footnotes:**\n",
    "<span id=\"fn1\"> 1. Data Source: <a href='https://www.oui.doleta.gov/unemploy/claimssum.asp'>Department of Labor, Monthly Benefit and Claims data</a> </span>     \n",
    "[[Go back]](#1)\n",
    "\n",
    "<span id=\"fn2\"> 2. <a href='https://www2.illinois.gov/ides/IDES%20Forms%20and%20Publications/CLI105L.pdf'>Illinois Unemployment Insurance benefit Handbook</a> </span>        \n",
    "[[Go back]](#2)\n",
    "\n",
    "<span id=\"fn3\"> 3. <a href='https://www2.illinois.gov/ides/IDES%20Forms%20and%20Publications/Reg_UI_Certification_Timeline.pdf'>Illinois Regular UI Certification Timeline</a> </span>       \n",
    "[[Go back]](#3)\n",
    "\n",
    "<span id=\"fn4\"> 4. Data Source: <a href='https://www.bls.gov/iag/tgs/iag72.htm'>Bureau of Labor Statistics</a> </span>       \n",
    "[[Go back]](#4)\n",
    "\n",
    "> Note that the above links don't work inside of the ADRF since you don't have internet access.\n",
    "\n",
    "> Click [Go back] to go back to where you were."
   ]
  }
 ],
 "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": "4.0.3"
  },
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 4
}