3.Data_Visualization.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 Visualization</center>\n",
    "<a href=\"https://doi.org/10.5281/zenodo.6399326\"><img src=\"https://zenodo.org/badge/DOI/10.5281/zenodo.6399326.svg\" alt=\"DOI\"></a>\n",
    "\n",
    "\n",
    "<center>Benjamin Feder, Nathan Barrett, Sean Simone</center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **1. Introduction**\n",
    "In the two data exploration notebooks, we formed an analytical cohort of graduates and explored the cohort's characteristics and various employment outcomes. Sometimes, it is hard to see trends and to communicate with your audience by only looking at summary statistics. In this notebook, we will import the csv files we saved in the previous two notebooks and learn how to turn them into informative visualizations. We will also discuss what type of visualization to choose for different types of analyses and how to use labels and plot adjustments to better present your results. As you work through the notebook, we will have checkpoints for you to practice using the code. You can think about how you might apply any of the techniques and code presented in this notebook to your project."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **2. Learning Objectives**\n",
    "\n",
    "After you finish this notebook, you should know:\n",
    "\n",
    "- how to use the R package `ggplot2` to create line plots, bar charts, and heatmaps, sometimes creating side-by-side visuals broken down by subgroup\n",
    "- how to choose visualization types for your analyses\n",
    "- how to improve your visualizations with informative titles, labels, and plot settings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Research Questions** \n",
    "We will create various visualizations by using the summary statistics we created in the two data exploration notebooks. The questions we seek to answer are:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- What are the average quarterly and number of individuals employed by quarter in our cohort? Do they vary by major?\n",
    "- What were the most common major types within the cohort? Do they vary by sex?\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?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Datasets** ####\n",
    "The summary statistics we created in the two data exploration notebooks are based on the following New Jersey Education to Earnings Data System (NJEEDS) tables:\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": [
    "#### **Methods**\n",
    "We will cover the following visualizations in this notebook:\n",
    "- **Line Plot**: is typically used for time series data to show how a variable changes over time\n",
    "- **Bar Plot**: visualizes relationships between numerical and categorical variables\n",
    "- **Small multiples**: compares information by different groups using a series of mini-graphs\n",
    "- **Heat Map**: adds highlights to your data with color-coding"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use R package `gglot2` to create all visualizations. Here is a brief introduction of **the syntax of `ggplot2`**:\n",
    "\n",
    "- start with `ggplot()` <br>\n",
    "- then, supply a dataset and aesthetic mapping with `x` pertaining to the variable on the x-axis, and so on, for example: `ggplot(dataset, aes(x = ..., y = ...)` <br>\n",
    "- from there, provide a geometric object represented by `geom_` to convey the desired type of visualization <br>\n",
    "- finally, add additional layers if necessary using `+` <br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For example, we can use the code blow to create a line plot (geometric object).\n",
    "\n",
    "    ggplot(data, aes(x = ... , y = ...) + \n",
    "        geom_line()\n",
    "   \n",
    "We can add an additional layer `labs()` to create a line plot with a title.\n",
    "\n",
    "    ggplot(data, aes(x = ... , y = ...) + \n",
    "        geom_histogram() + \n",
    "        labs(title = 'My plot title')\n",
    "        \n",
    "The `aes()` call can contain additional arguments outside of `x` and `y` to potentially match the `fill`, `color`, `linetype`, and additional specifications of specific variables in a dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Notebook Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we can get started, let's load in the necessary R libraries and connect to the proper server."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "ERROR",
     "evalue": "Error in library(odbc, warn.conflicts = F, quietly = T): there is no package called ‘odbc’\n",
     "output_type": "error",
     "traceback": [
      "Error in library(odbc, warn.conflicts = F, quietly = T): there is no package called ‘odbc’\nTraceback:\n",
      "1. library(odbc, warn.conflicts = F, quietly = T)"
     ]
    }
   ],
   "source": [
    "# Database interaction imports\n",
    "library(odbc, warn.conflicts=F, quietly=T)\n",
    "\n",
    "# For data manipulation/visualization\n",
    "library(tidyverse, warn.conflicts=F, quietly=T)\n",
    "\n",
    "# For faster date conversions\n",
    "library(lubridate, warn.conflicts=F, quietly=T)\n",
    "\n",
    "# Use percent() function\n",
    "library(scales, warn.conflicts=F, quietly=T)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to the server\n",
    "con <- DBI::dbConnect(odbc::odbc(),\n",
    "                     Driver = \"SQL Server\",\n",
    "                     Server = \"msssql01.c7bdq4o2yhxo.us-gov-west-1.rds.amazonaws.com\",\n",
    "                     Trusted_Connection = \"True\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have established a connection to our server, let's read in the csvs containing the information to answer the first Research Question, \"What are the average quarterly and number of individuals employed by quarter in our cohort? Do they vary by major?\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is a reminder of what summary statistics each file contains:\n",
    "- **`avg_and_num.csv`**: average quarterly earnings and number employed by quarter\n",
    "- **`avg_and_num_major.csv`**: average quarterly earnings and number employed by quarter for most common majors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color=red> Before you run the cell below, make sure you have run through both data exploration notebooks and have saved the csv files in your \"U:\\\\..\\\\NJ Training\\\\Results\" directory. You also need to change the directory in the `read_csv()` statements below. Replace \"..\" with your username.</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# average quarterly earnings and number employed by quarter\n",
    "avg_and_num <- read_csv(\"U:\\\\..\\\\NJ Training\\\\Results\\\\avg_and_num.csv\")\n",
    "\n",
    "# average quarterly earnings and number employed by quarter (common majors)\n",
    "avg_and_num_major <- read_csv(\"U:\\\\..\\\\NJ Training\\\\Results\\\\avg_and_num_major.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Average quarterly earnings and number employed by quarter (lineplot)\n",
    "\n",
    "Previously, by looking at the counts in data frame `avg_and_num`, we identified that after about 4 quarters post-graduation, the number of individuals employed in New Jersey in our cohort begins to decline, while the average quarterly earnings rise. However, when presenting your results, a list of numbers may not be an effective way for your audience to understand and absorb the information. In this example, we will focus on the time trend of the number of individuals employed per quarter, using a line plot with quarter after graduation as the x-axis and number of individuals employed as the y-axis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Example ggplot2 syntax to make a simple visualization of trends in number of people employed\n",
    "\n",
    "#Specify source dataset and x and y variables\n",
    "ggplot(avg_and_num, aes(x = quarter_number, y = n_employed)) + \n",
    "\n",
    "#Plots a line on the graph\n",
    "geom_line()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the graph, we can see a similar trend relative to the table, in that the number of individuals in the cohort employed in New Jersey REDACTED up until the REDACTED quarter after graduation, before steadily REDACTED through the REDACTED quarter cutoff point. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Although we can see the trends from the above graph quickly and clearly, there are several things we need to improve so that the graph provides the audience some context and delivers information more clearly.\n",
    "\n",
    "1. **Graph Title**: The title of the visualization should convey the major takeaway(s) of the plot and should answer the original question. In this case, depending on the information you want to address, you may have different titles. For example, if you want to describe the REDACTED in the number of graduates employed in New Jersey up until REDACTED quarters after graduation, the title could be \"Employment of NJ graduates in NJ REDACTED in first REDACTED quarters after graduation.\" However, if you want to describe the more recent trend, the title could be \"Employment of NJ graduates in NJ steadily REDACTED in years REDACTED and REDACTED following graduation\".\n",
    "2. **X-axis and Y-axis Labels**: The labels in our current graph are variable names. We need to change them to short and easy-to-understand descriptions, such as \"quarter after graduation\" and \"number employed\".\n",
    "3. **Data Source**: Providing clear reference and source of the underlying data used for the visualization can increase the credibility and enable the reproducibility of your results. The reference can be the data agency or the name of the dataset. In this analysis, we use \"NJEEDS data\" as our major data source. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> We can add a `labs()` layer to the visualization to change the title, axes' names, and caption of the plot. We simply use `+` to add this layer to the code we showed earlier. \n",
    "\n",
    "    labs(\n",
    "        title = \"YOUR TITLE\",\n",
    "        x = \"X-AXIS LABEL\",\n",
    "        y = \"Y-AXIS LABEL\",\n",
    "        caption = \"DATA SOURCE OR NOTES\"\n",
    "        )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4. **X-axis and Y-axis Tick Mark**: On our current graph, x-axis only shows quarters on a 2.5 month interval. It is hard for the audience to see key time points, such as when the number of individuals employed in NJ peaked. Therefore, we want to adjust the x-axis tick so that it shows the time points more frequently. In this case, we use `scale_x_continuous()` and `scale_y_continuous()` to specify a continuous data type and to adjust tick marks and axis extent. In both cases, `breaks=` statement controls the minimum and maximum values of the axis labels as well as the tick mark intervals between them."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5. **Key Time Points**: In this analysis, labelling key timepoints, such as when the cohort had its peak number of individuals employed in NJ, helps the audience understand the context. However, it is not necessary to add key timepoints to every graph. We can add vertical lines to the graph to represent significant events using the `geom_vline()` statement and control labels for these lines using the `annotate()` statement. Within `geom_vline()` we must specify where to place the line using the `xintercept=` command. In our case, we assign the peak quarter to a variable and then pass this variable to `xintercept=`. When we use the `annotate()` statement, we must specify the location of the labels using the `x=` and `y=` statements. Keep in mind you will likely need to adjust these based on specific details of your graph. In the example below, we use a height of REDACTED, which places the label nearly at the top."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Code adjusting overall graph attributes\n",
    "\n",
    "# For easier reading, increase base font size\n",
    "theme_set(theme_gray(base_size = 16))\n",
    "# Adjust repr.plot.width and repr.plot.height to change the size of graphs\n",
    "options(repr.plot.width = 12, repr.plot.height = 8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First, assigning the overall peak week to a variable.\n",
    "# We will use this to plot a vertical line on the graph representing this quarter\n",
    "\n",
    "# Overall peak\n",
    "peak_overall <- 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Example ggplot2 syntax to make a simple visualization of trends in number of people employed\n",
    "\n",
    "#Specify source dataset and x and y variables\n",
    "num_employed_plot <- ggplot(avg_and_num, aes(x = quarter_number, y = n_employed)) + \n",
    "\n",
    "#Plots a line on the graph\n",
    "geom_line() +\n",
    "\n",
    "# Add a blue vertical dashed line at the peak week for certified claimants overall\n",
    "# with a label at the top explaining what it is\n",
    "geom_vline(xintercept = peak_overall,\n",
    "    color = \"blue\", linetype = \"dashed\") +\n",
    "annotate(\"text\", x = peak_overall, y = 21500, color = \"blue\",\n",
    "    hjust = -0.1, label = \"Peak\") +\n",
    "\n",
    "# Adjust the y scale to assign start and end points as well\n",
    "# as the interval for tick marks\n",
    "scale_y_continuous(\n",
    "    labels = scales::comma,\n",
    "    breaks = seq(20000, 21500, 250),\n",
    "    limits = c(20000,21500)) + \n",
    "\n",
    "# Adjust the x scale to assign start and end points as well\n",
    "# as the interval for tick marks\n",
    "scale_x_continuous(\n",
    "    breaks = seq(1, 12, 1),\n",
    "    limits = c(1,12)) + \n",
    "\n",
    "#Add a title, labels for the x and y axes, and data source\n",
    "labs(title = \"Employment of NJ graduates in NJ REDACTED in REDACTED REDACTED quarters after graduation\",\n",
    "    x = \"Quarter after Graduation\", y = \"Number Employed\",\n",
    "    caption = \"Data Source: NJEEDS Data\") +\n",
    "\n",
    "# shift labels over slightly to clean up appearance\n",
    "theme(axis.text.x = element_text(vjust=.5))\n",
    "\n",
    "#Display the graph that we just created\n",
    "print(num_employed_plot)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Checkpoint 1: Create A Line Plot**\n",
    "Create a line plot to show the trend of average quarterly wages for the cohort in `avg_and_num`. Remember that your plot should include an informative title, axes' labels, data source, and key timepoints. Adjust axes' tick marks and graph sizes to make it easy to read.  \n",
    "\n",
    "Comment on the trend of average quarterly wages. During which quarter did the average quarterly wages peak?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Example ggplot2 syntax to make a simple visualization of trends in average quarterly wages\n",
    "\n",
    "#Specify source dataset and x and y variables\n",
    "ggplot(___, aes(x = ___, y = ___)) + \n",
    "\n",
    "#Plots a line on the graph\n",
    "geom_line()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Note that this visualization may have different peak week\n",
    "#Set that here if you want to show a vertical line for the peak\n",
    "\n",
    "# Overall peak\n",
    "peak_overall <- ___"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Example ggplot2 syntax to make a simple visualization of trends in number of people employed\n",
    "\n",
    "#Specify source dataset and x and y variables\n",
    "avg_wages_plot <- ggplot(___, aes(x = ___r, y = ___)) + \n",
    "\n",
    "#Plots a line on the graph\n",
    "geom_line() +\n",
    "\n",
    "# Add a blue vertical dashed line at the peak week for certified claimants overall\n",
    "# with a label at the top explaining what it is\n",
    "geom_vline(xintercept = peak_overall,\n",
    "    color = \"blue\", linetype = \"dashed\") +\n",
    "annotate(\"text\", x = peak_overall, y = ___, color = \"blue\",\n",
    "    hjust = -0.1, label = \"Peak\") +\n",
    "\n",
    "# Adjust the y scale to assign start and end points as well\n",
    "# as the interval for tick marks\n",
    "scale_y_continuous(\n",
    "    labels = scales::comma,\n",
    "    breaks = seq(___, ___, ___),\n",
    "    limits = c(___, ___)) + \n",
    "\n",
    "# Adjust the x scale to assign start and end points as well\n",
    "# as the interval for tick marks\n",
    "scale_x_continuous(\n",
    "    breaks = seq(___, ___, ___),\n",
    "    limits = c(___, ___)) + \n",
    "\n",
    "#Add a title, labels for the x and y axes, and data source\n",
    "labs(title = \"___\",\n",
    "    x = \"___\", y = \"___\",\n",
    "    caption = \"___\") +\n",
    "\n",
    "# shift labels over slightly to clean up appearance\n",
    "theme(axis.text.x = element_text(vjust=.5))\n",
    "\n",
    "#Display the graph that we just created\n",
    "print(avg_wages_plot)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### By Major (Line Plot, Multiple Lines)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the data exploration wage analysis notebook, we then followed by looking at these trends within the two most common degree fields. By adding to the `aes()` call, we can visualize the differences amongst subgroups using separate lines. \n",
    "\n",
    "In the following example, we will visualize the trends in average quarterly wages by quarter for the two most common majors in the cohort. First, though, let's find the associated titles of the CIP codes. \n",
    "\n",
    "To do so, we will need to first rename the `substring(major, 1, 2)` column and change it to a character type before using the CIP code crosswalk available in the projects drive to find the associated title."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename and change type of cip code variable\n",
    "avg_and_num_major <- avg_and_num_major %>%\n",
    "    rename(cip = `substring(major, 1, 2)`) %>%\n",
    "    mutate(\n",
    "        cip = as.character(cip)\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read in cip code lookup\n",
    "cip_xwalk <- read_csv(\"P:\\\\tr-nj-2021\\\\NJ Class Notebooks\\\\xwalks\\\\oshe_cip_xwalk.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# match to avg_and_num_major and keep desired columns\n",
    "avg_and_num_major <- avg_and_num_major %>%\n",
    "    left_join(cip_xwalk, by = c(\"cip\" = \"CIPCode-text\")) %>%\n",
    "    select(CIPTitle, quarter_number, mean_wage, n_employed)\n",
    "\n",
    "head(avg_and_num_major)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have the associated titles, we can create our visualization. Here, we will add in a `color` aesthetic to designate the differences in majors, as the color of the line will correspond to the specific major. \n",
    "\n",
    "> You can manually insert colors using `scale_color_manual()`. Before exporting, we recommend that you choose contrasting colors that satisfy ADA requirements. Examples can be found on color brewer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Example ggplot2 syntax to make a simple visualization of trends in quarterly wages by major\n",
    "\n",
    "#Specify source dataset and x, y, and color variables\n",
    "# only take first word in title because it makes graph harder to read\n",
    "avg_wages_major_plot <- ggplot(avg_and_num_major, aes(x = quarter_number, y = mean_wage, \n",
    "                                                      color = word(CIPTitle, 1))) + \n",
    "\n",
    "#Plots a line on the graph\n",
    "geom_line() +\n",
    "\n",
    "# Adjust the y scale to assign start and end points as well\n",
    "# as the interval for tick marks\n",
    "scale_y_continuous(\n",
    "    labels = scales::comma,\n",
    "    breaks = seq(4000, 14000, 2000),\n",
    "    limits = c(4000,14000)) + \n",
    "\n",
    "# Adjust the x scale to assign start and end points as well\n",
    "# as the interval for tick marks\n",
    "scale_x_continuous(\n",
    "    breaks = seq(1, 12, 1),\n",
    "    limits = c(1,12)) + \n",
    "\n",
    "#Add a title, labels for the x and y axes, color legend, and data source\n",
    "labs(title = \"NJ Graduates majoring in REDACTED experienced REDACTED earnings after graduation on average\",\n",
    "    x = \"Quarter after Graduation\", y = \"Average Quarterly Wages\",\n",
    "    caption = \"Data Source: NJEEDS Data\",\n",
    "    color = \"Major\") +\n",
    "\n",
    "# shift labels over slightly to clean up appearance\n",
    "theme(axis.text.x = element_text(vjust=.5)) \n",
    "\n",
    "#Display the graph that we just created\n",
    "print(avg_wages_major_plot)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Earnings and Number Employed Together (Line Plot, Small Multiples)\n",
    "\n",
    "Perhaps if we wanted to discuss the relationship between average quarterly wages and number of individuals employed in New Jersey (or lack thereof), we might consider creating a visualization with multiple y-axes to designate the difference between the two variables. Since the two variables are on entirely different scales, as `scale_y_continuous()` values did not even overlap in the the visualizations, it may not be advisable to do so. \n",
    "\n",
    "However, it is possible to leverage a small multiples visualization, where we use a combination of mini-graphs visualize the relationship of these two variables, relative to the quarter after graduation using `ggplot2`. This will require some data manipulation, as `facet_grid()` and `facet_wrap()` work by displaying subgroups within specific variables. Therefore, we will need to create a new variable, with its subgroups corresponding to either `mean_wage` or `n_employed` for each `quarter_number` value, to use this method.\n",
    "\n",
    "To do so, we can use `pivot_longer`, where we will take all of the column names except `quarter_number`, and have them become subgroups within one overarching column, while tracking the values for these columns in a separate variable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# adjust data frame so variables on y axis are in the same column \"variable\"\n",
    "# -c(quarter_number) is used so that pivot_longer ignores the column instead of including it as a subgroup in variable\n",
    "avg_and_num_long <- avg_and_num %>%\n",
    "    pivot_longer(names_to = \"variable\", values_to = \"value\", -c(quarter_number))\n",
    "\n",
    "head(avg_and_num_long)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that the data frame is suitable for displaying small grids, we can include the `facet_grid()` call inside of our plot to stack the two graphs on top of each other. With this plot, keep in mind that the scale changes between the two panels."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Example ggplot2 syntax to make a simple visualization of trends in quarterly wages and number of people employed \n",
    "\n",
    "#Specify source dataset and x and y variables\n",
    "both_vars_plot <- ggplot(data = avg_and_num_long, mapping = aes(x = quarter_number, y = value)) +\n",
    "\n",
    "#Plots a line on the graph\n",
    "geom_line() +\n",
    "\n",
    "# creates separate panels for the subgroups in variable\n",
    "# scale is set so that the y-axis can have differing scales\n",
    "facet_grid(variable~., scale = \"free_y\") +\n",
    "\n",
    "# Adjust the x scale to assign start and end points as well\n",
    "# as the interval for tick marks\n",
    "scale_x_continuous(\n",
    "    breaks = seq(1, 12, 1),\n",
    "    limits = c(1,12)) + \n",
    "\n",
    "#Add a title, labels for the x and y axes and data source\n",
    "labs(title = \"NJ Graduates experience REDACTED trends in REDACTED and number employed REDACTED to quarters \\n after graduation\",\n",
    "    x = \"Quarter after Graduation\", y = \"Value\",\n",
    "    caption = \"Data Source: NJEEDS Data\") +\n",
    "\n",
    "# shift labels over slightly to clean up appearance\n",
    "theme(axis.text.x = element_text(vjust=.5)) \n",
    "\n",
    "#Display the graph that we just created\n",
    "print(both_vars_plot)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Checkpoint 2: Reproduce for Dominant Earnings\n",
    "\n",
    "Import the dominant earnings csv files you saved in the data exploration notebook. Then create a line plot to show the trend of average quarterly earnings by quarter after graduation. Remember that your plot should include an informative title, axes labels, data source, and key timepoints. Adjust axes tick marks and graph sizes to make it easy to read.  \n",
    "\n",
    "Comment on the trend of average quarterly dominant earnings for the two major groups. Do they follow similar trends relative to non-dominant earnings?\n",
    "\n",
    "Recall that we saved the following file:\n",
    "- **`avg_and_num_dom_major.csv`**: average dominant quarterly earnings and number employed by quarter for the most common majors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color=red> You need to change the directory in the read_csv() statements below. Replace \"..\" with your username.</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# average dominant quarterly earnings and number employed by quarter (common majors)\n",
    "avg_and_num_dom_major <- read_csv(\"U:\\\\..\\\\NJ Training\\\\Results\\\\avg_and_num_dom_major.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename and change type of cip code variable\n",
    "avg_and_num_dom_major <- ___ %>%\n",
    "    rename(cip = `substring(major, 1, 2)`) %>%\n",
    "    mutate(\n",
    "        cip = as.character(cip)\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# match to avg_and_num_major_dom and keep desired columns\n",
    "avg_and_num_major_dom <- __ %>%\n",
    "    left_join(cip_xwalk, by = __) %>%\n",
    "    select(___, ___, ___, ___)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Example ggplot2 syntax to make a simple visualization of trends in quarterly wages by major\n",
    "\n",
    "#Specify source dataset and x, y, and color variables\n",
    "# only take first word in title because it makes graph harder to read\n",
    "avg_wages_major_plot <- ggplot(___, aes(x = ___, y = ___, \n",
    "                                                      color = ___)) + \n",
    "\n",
    "#Plots a line on the graph\n",
    "geom_line() +\n",
    "\n",
    "# Adjust the y scale to assign start and end points as well\n",
    "# as the interval for tick marks\n",
    "scale_y_continuous(\n",
    "    labels = scales::comma,\n",
    "    breaks = seq(___, ___, ___),\n",
    "    limits = c(___,___)) + \n",
    "\n",
    "# Adjust the x scale to assign start and end points as well\n",
    "# as the interval for tick marks\n",
    "scale_x_continuous(\n",
    "    breaks = seq(___, ___, ___),\n",
    "    limits = c(___,___)) + \n",
    "\n",
    "#Add a title, labels for the x and y axes, color legend, and data source\n",
    "labs(title = \"___\",\n",
    "    x = \"___\", y = \"___\",\n",
    "    caption = \"___\",\n",
    "    color = \"___\") +\n",
    "\n",
    "# shift labels over slightly to clean up appearance\n",
    "theme(axis.text.x = element_text(vjust=.5)) \n",
    "\n",
    "#Display the graph that we just created\n",
    "print(avg_wages_major_plot)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Most Common Majors (Bar plot)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the previous visualizations, we graphed the earnings outcomes broken down by the two most common degree fields. Let's take a deeper look into the most common majors in the cohort, first on their own before segmenting by sex.\n",
    "\n",
    "Recall that we saved the following files:\n",
    "- **`common_major.csv`**: 10 most common majors\n",
    "- **`common_major_sex.csv`** 10 most common majors by sex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color=red> You need to change the directory in the read_csv() statements below. Replace \"..\" with your username.</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# most common majors\n",
    "common_major <- read_csv(\"U:\\\\..\\\\NJ Training\\\\Results\\\\common_major.csv\")\n",
    "\n",
    "# most common majors by sex\n",
    "common_major_sex <- read_csv(\"U:\\\\..\\\\NJ Training\\\\Results\\\\common_major_sex.csv\")\n",
    "\n",
    "# see common_major\n",
    "common_major"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Although we will not create additional graphs segmented by major, to make sure the color scheme is consistent, we can **assign a color palette to the each major**.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Assign a custom color palette to use with the bar graphs\n",
    "palette_color <- c(\"BUSINESS,\" = \"orange\",\n",
    "                   \"PSYCHOLOGY\" = \"blue\",\n",
    "                   \"SOCIAL SCI\" = \"red\",\n",
    "                   \"HEALTH PRO\" = \"purple\",\n",
    "                   \"BIOLOGICAL\" = \"green3\",\n",
    "                   \"COMMUNICAT\" = \"brown\",\n",
    "                   \"EDUCATION.\" = \"black\",\n",
    "                   \"ENGINEERIN\" = \"yellow\",\n",
    "                   \"VISUAL AND\" = \"lavender\",\n",
    "                   \"LIBERAL AR\" = \"seagreen\"\n",
    "                  )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can create the bar plot to show the number of graduates in each major. The code structure is similar to the code we used to create the line plot. But instead of `geom_line()`, we use `geom_col()`. The `x` and `y` variables we include in `aes()` are `n` and `cip_family`. This way, we get a horizontal bar chart with x-axis showing the counts and y-axis showing the descriptions of majors. If you want to create a vertical bar chart, you just need to switch the `x` and `y` variables. In the `scale_fill_manual()` statement, we use `values=` to specify how we want each series to display by passing the `palette_color` variable that we assigned above. `palette_color` relates potential values of `cip_family` to the display color for each. This statement can accept common English names for many colors as well as hexidecimal color codes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a bar chart showing the 10 majors most represented in the graduating cohort\n",
    "\n",
    "#Specify source dataset and x and y variables\n",
    "common_major_plot <- ggplot(common_major, aes(x = n, y = reorder(cip_family,n), fill=cip_family)) + \n",
    "\n",
    "#Plots bars on the graph\n",
    "geom_col() +\n",
    "\n",
    "#Apply your color palette\n",
    "scale_fill_manual(\"\", values = palette_color, guide=FALSE) +\n",
    "\n",
    "#Adjust the x scale to set the interval for tick marks\n",
    "scale_x_continuous(labels = scales::comma,\n",
    "    breaks = seq(0, 6000, 1000),\n",
    "    limits = c(0, 6000)) +\n",
    "\n",
    "#Add titles and axis labels\n",
    "labs(title = \"NJ graduates by major\",\n",
    "     subtitle = \"Top 10 Majors\",\n",
    "     x = \"Number of Graduates\", y = \"Major\",\n",
    "     caption = \"Data Source: NJEEDS data\")\n",
    "\n",
    "#Display the graph we just made\n",
    "print(common_major_plot)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### By Sex (Side-by-side Bar plot)\n",
    "\n",
    "Now let's segment the most common majors by sex. Due to disclosure review limitations (we will discuss this in a later notebook), we will not include values where sex is 0 in this visualization. Here, we will create a side-by-side bar plot for the four most common majors by sex, displaying the proportion of graduates receiving the degree by sex.\n",
    "\n",
    "First, we need to isolate the four most common majors for the male and female sexs in `common_major_sex`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# isolate most common majors\n",
    "top_4 <- common_major_sex %>%\n",
    "    filter(sex!=0) %>%\n",
    "    group_by(sex) %>%\n",
    "    arrange(desc(prop)) %>%\n",
    "    slice(1:4) %>%\n",
    "    ungroup()\n",
    "\n",
    "top_4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, the most common majors for REDACTED are not always the most common majors for REDACTED. Here, if a major does not exist in `top_4` for one sex but does for the other, it will be the only bar in the side-by-side plot. To create a side-by-side plot, add the `fill` aesthetic, as well as `position_dodge()` inside the `geom_col()` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a bar chart showing percentage of graduates by major and sex for most common majors within male and female sex\n",
    "#Specify source dataset and x and y variables\n",
    "top_4_major_sex_plot <- ggplot(top_4, aes(x = cip_family, y = prop, fill=as.factor(sex))) + \n",
    "\n",
    "#Plots bars on the graph as side by side\n",
    "geom_col(stat = \"identity\", position=position_dodge()) +\n",
    "\n",
    "#Adjust the y scale to set the interval for tick marks\n",
    "scale_y_continuous(labels = scales::comma,\n",
    "    breaks = seq(0, 1, .1),\n",
    "    limits = c(0, .5)) +\n",
    "\n",
    "#Add titles and axis labels\n",
    "labs(title = \"Proportion of Graduates by Major and Sex\",\n",
    "     subtitle = \"Top 4 Majors per Sex\",\n",
    "     x = \"Major\", y = \"Proportion\",\n",
    "     caption = \"Data Source: NJEEDS data\") +\n",
    "\n",
    "scale_fill_discrete(name = \"Sex\", labels = c(\"Male\", \"Female\"))\n",
    "\n",
    "#Display the graph we just made\n",
    "print(top_4_major_sex_plot)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Stable Employment Outcomes (Bar plot, Side-by-side)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Instead of comparing the differences in majors by sex for the cohort, let's take a look at the sex differences in earnings outcome. Recall in the second data exploration notebook where we found the proportion of individuals in the cohort experiencing at least one quarter of full-quarter employment. In this visualization, again utilizing a side-by-side bar plot, we will compare the sex composition of all graduates relative to those who experienced at least one quarter of full-quarter employment in their first three years after graduation.\n",
    "\n",
    "Recall that we saved the following files:\n",
    "- **`common_sex`**: Breakdown by sex for the entire cohort\n",
    "- **`full_q_stats_sex`**: Breakdown by sex for those experiencing full-quarter employment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sex breakdown \n",
    "common_sex <- read_csv(\"U:\\\\..\\\\NJ Training\\\\Results\\\\common_sex.csv\")\n",
    "\n",
    "# full quarter info by sex\n",
    "full_q_stats_sex <- read_csv(\"U:\\\\..\\\\NJ Training\\\\Results\\\\full_q_stats_sex.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we will combine the two data frames, `full_q_stats_sex` and `common_sex` so that we have both the gender breakdown for the full cohort as well as for those experiencing at least one quarter of full-quarter employment in the same data frame. Before doing so, to avoid confusion between the `prop` variables in the two data frames, we will rename one of them. Afterwards, similar to our approach in visualizing the number of individuals employed and their average quarterly earnings by quarter, we will aggregate these two `prop` columns so that each row is either a full-quarter or full cohort observation, with a separate variable denoting its status."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# manipulate data frame for visualization\n",
    "full_q_stats_sex <- full_q_stats_sex %>%\n",
    "    rename(full_q_prop = prop) %>%\n",
    "    left_join(common_sex, by='sex') %>%\n",
    "    select(-c(num_individuals, num_employers, avg_wage, n)) %>%\n",
    "    pivot_longer(names_to = \"var\", values_to = \"prop\", -c(sex))\n",
    "\n",
    "full_q_stats_sex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From here, we can employ a similar strategy as we did in the previous visualization, adding in a `fill` aesthetic, as well as `position_dodge()` to ensure the side by side nature of the visualization."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a bar chart showing sex breakdown for entire cohort and full-quarter employment\n",
    "#Specify source dataset and x and y variables\n",
    "sex_breakdown_plot <- ggplot(full_q_stats_sex, aes(x = sex, y = prop, fill=as.factor(var))) + \n",
    "\n",
    "#Plots bars on the graph as side by side\n",
    "geom_bar(stat = \"identity\", position=position_dodge()) +\n",
    "\n",
    "#Adjust the y scale to set the interval for tick marks\n",
    "scale_y_continuous(labels = scales::comma,\n",
    "    breaks = seq(0, 1, .1),\n",
    "    limits = c(0, .7)) +\n",
    "\n",
    "#Add titles and axis labels\n",
    "labs(title = \"Proportion of Graduates by Major and Sex\",\n",
    "     subtitle = \"Top 4 Majors per Sex\",\n",
    "     x = \"Major\", y = \"Proportion\",\n",
    "     caption = \"Data Source: NJEEDS data\") +\n",
    "\n",
    "scale_fill_discrete(name = \"Variable\", labels = c(\"Full-Quarter Employment\", \"Entire Cohort\"))\n",
    "\n",
    "#Display the graph we just made\n",
    "print(sex_breakdown_plot)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Employment patterns by quarters (Heatmap)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The final visualization in this notebook is a heatmap displaying our cohort of New Jersey graduates' employment patterns by quarter, as we will focus on the 15 most common patterns. We do not use a heatmap in the classic way where each \"box\" in the map corresponds to a proportion or number. Instead, we will use the heatmap as a format by which to map employment patterns, as we will color-code each box depending on if the pattern has or does not have employment in a specific quarter. We will start by reading in the .csv file of employment patterns.\n",
    "\n",
    "Recall that we saved the following file:\n",
    "- **`patterns`**: Employment patterns for entire cohort"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read in patterns\n",
    "patterns <- read_csv(\"U:\\\\..\\\\NJ Training\\\\Results\\\\patterns.csv\") %>%\n",
    "    head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save counts to use later in the heatmap - we cannot use the counts as index, as there could be duplicate values \n",
    "counts <- patterns$cnt\n",
    "pcts <- patterns$prop"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will add index with unique sequential numbers and remove the `count` AND `pct` columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patterns$Pattern <- seq.int(nrow(patterns))\n",
    "patterns$cnt <- NULL\n",
    "patterns$prop <- NULL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now need to convert this table from wide to long format, since our `geom_tile()` function only works with long data frames. Instead of using `pivot_wider()` as we did to create `patterns` in the second data exploration notebook, we will use `pivot_longer()` to create a data frame with each row corresponding to a pattern/quarter/status combination."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert to long format\n",
    "patterns_long <- pivot_longer(patterns, names_to = 'Quarter', values_to = 'Status', -c(Pattern))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see patterns_long\n",
    "head(patterns_long)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we are ready to create the visualization using the `geom_tile()` layer:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Full code for the plot\n",
    "\n",
    "levels = ordered(c(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15))  # specify in which order to add the rows from our wide table (called \"patterns\") \n",
    "                                                             # we want to preserve the same ordering of rows as they are sorted in the table from highest to lowes\n",
    "\n",
    "patterns_long$Quarter <- factor(patterns_long$Quarter, levels=c(\"Q1\", \"Q2\", \"Q3\", \"Q4\", \"Q5\", \"Q6\", \"Q7\", \"Q8\", \"Q9\", \"Q10\", \"Q11\", \"Q12\"))\n",
    "\n",
    "ggplot(data = patterns_long, aes(x = Quarter, y = ordered(Pattern, levels=rev(levels)))) +    # sort y-axis according to levels specified above\n",
    "geom_tile(aes(fill = Status), colour = 'black') +                                            # fill the table with value from Status column, create black contouring\n",
    "scale_fill_brewer(palette = \"Set1\") +                                                        # specify a color palette\n",
    "theme(text=element_text(size=14,face=\"bold\")) +                                                          # specify font size\n",
    "scale_x_discrete(position = 'top') +                                                         # include x-axis labels on top of the plot\n",
    "labs(\n",
    "    y = \"Employment - Percentages\",\n",
    "    title = 'Employment Patterns by Quarters',\n",
    "    caption = 'Source: NJEEDS data'\n",
    ") +\n",
    "scale_y_discrete(labels=rev(pcts))  # rename the y-axis ticks to correspond to the counts from the table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also have counts on the left side of the y-axis instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Full code for the plot\n",
    "\n",
    "levels = ordered(c(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15))  # specify in which order to add the rows from our wide table (called \"patterns\") \n",
    "                                                             # we want to preserve the same ordering of rows as they are sorted in the table from highest to lowes\n",
    "\n",
    "patterns_long$Quarter <- factor(patterns_long$Quarter, levels=c(\"Q1\", \"Q2\", \"Q3\", \"Q4\", \"Q5\", \"Q6\", \"Q7\", \"Q8\", \"Q9\", \"Q10\", \"Q11\", \"Q12\"))\n",
    "\n",
    "ggplot(data = patterns_long, aes(x = Quarter, y = ordered(Pattern, levels=rev(levels)))) +    # sort y-axis according to levels specified above\n",
    "geom_tile(aes(fill = Status), colour = 'black') +                                            # fill the table with value from Status column, create black contouring\n",
    "scale_fill_brewer(palette = \"Set1\") +                                                        # specify a color palette\n",
    "theme(text=element_text(size=14,face=\"bold\")) +                                                          # specify font size\n",
    "scale_x_discrete(position = 'top') +                                                         # include x-axis labels on top of the plot\n",
    "labs(\n",
    "    y = \"Employment - Percentages\",\n",
    "    title = 'Employment Patterns by Quarters',\n",
    "    caption = 'Source: NJEEDS data'\n",
    ") +\n",
    "scale_y_discrete(labels=rev(counts))  # rename the y-axis ticks to correspond to the counts from the table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course, this notebook only contains a few of the possible visualizations you can create using the `ggplot2` package. Luckily, the R community has created countless resources you can use for making all types of visualizations!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}