Supplemental_Visualizations.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img style=\"float: center;\" src=\"images/CI_horizontal.png\" width=\"600\">\n",
    "<center>\n",
    "    <span style=\"font-size: 1.5em;\">\n",
    "        <a href='https://www.coleridgeinitiative.org'>Website</a>\n",
    "    </span>\n",
    "</center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <center>Supplemental Visualizations</center>\n",
    "\n",
    "<center>Brian Kim, Allison Nunez</center>\n",
    "<br>\n",
    "<a href=\"https://doi.org/10.5281/zenodo.6426203\"><img src=\"https://zenodo.org/badge/DOI/10.5281/zenodo.6426203.svg\" alt=\"DOI\"></a>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "\n",
    "In this notebook, we describe how to create two types of visualizations: a funding sequence chart, and a choropleth map. These use techniques that build on the material covered in the Data Visualization notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": false
   },
   "source": [
    "## Funding sequence chart\n",
    "\n",
    "Consider the following question:\n",
    "\n",
    "**What are the funding histories of graduate students in the three years leading up to their dissertation? How do the funding histories differ, and what are the most frequent funding sequences?**\n",
    "\n",
    "To create a graphic that lets us answer this question, both semester level funding information and time of dissertation are needed. In other words, a linked dataset with UMETRICS and SED should be used. The UMETRICS data allows to get the funding history of students, which can be used in conjunction with SED data to see what the funding histories look like leading up to the dissertation.\n",
    "\n",
    "The example below displays the top ten most common patterns of federal funding in the time before and during the year that a student receives the PhD. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": false
   },
   "source": [
    "### Conceptual design"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": false
   },
   "source": [
    "The final visualization will be organized the following way:\n",
    "\n",
    "```\n",
    "funding pattern \n",
    "\n",
    "- - - X X X X X X | 11%\n",
    "X X X X X X X X X | 10%\n",
    "- - - X X - X X - | 9%\n",
    "- - - X X X X X - | 8%\n",
    "- - - X X X X - - | 7%    percent\n",
    "- - X X X X - - - | 6%    of sample\n",
    "X X - - - - - - - | 5%\n",
    "- - - X X - - - - | 4%\n",
    "X X X - - - - - - | 4%\n",
    "- - - - X X X - - | 4%\n",
    "__________________|\n",
    " -2    -1     0\n",
    "      year\n",
    "\n",
    "```\n",
    "Each row is a pattern where an `X` indicates federal funding and a `-` is no funded. If these were the real data, the first row would show that 11% of the PhD awardees had federal funding only during the last two years before their degree was awarded. The second row shows 10% with federal funding every single semester, nine in a row. The numbers here are arbitrary."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": false
   },
   "source": [
    "### Data preparation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": false
   },
   "source": [
    "From end to beginning:\n",
    "  - Top ten rows by % of total, nine columns of yes/no semester funding\n",
    "  - ...will need to be counted from a unique student-level dataset that has nine columns of yes/no funding\n",
    "  - ...pivoted from the full student X semester-level dataset as `semester_df`\n",
    "  - ...created from those students covered in UMETRICS for the entire time period (cut by institution)\n",
    "  "
   ]
  },
  {
   "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",
    "library(lubridate)\n",
    "library(sf)\n",
    "library(maps)\n",
    "\n",
    "# for calculating percentages\n",
    "library(scales)\n",
    "\n",
    "# to better view images\n",
    "# For easier viewing of graphs\n",
    "theme_set(theme_gray(base_size = 24))\n",
    "options(repr.plot.width = 20, repr.plot.height = 12)"
   ]
  },
  {
   "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",
    "                     Trusted_Connection = \"True\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 1:  Sixteen schools have UMETRICS coverage 2012-2015, and we only want them for this chart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter for these institutions (comes from the joined_semester.sql file)\n",
    "\n",
    "qry <- \"\n",
    "select *\n",
    "from ds_nsf_ncses.dbo.nsf_sed\n",
    "WHERE phdfy = '2015' and \n",
    "phdinst in ('104179', '110680', '139658', '141574', '151351', '153658', '155317', '164988', '170976', '201885', '204796', '209542', '214777', '228778', '240444', '243780')\n",
    "\"\n",
    "sed_upd_cohort <- dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see sed_upd_cohort\n",
    "head(sed_upd_cohort)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see amount of people\n",
    "nrow(sed_upd_cohort)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 2: match 2015 cohort to UMETRICS Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# join sed to umetrics using umetrics_xwalk\n",
    "qry <- \"\n",
    "select c.*, d.semester, d.team_size from (\n",
    "select a.*, b.emp_number \n",
    "from ds_nsf_ncses.dbo.nsf_sed a\n",
    "inner join tr_uncf_excelencia.dbo.sed_umetrics_xwalk b\n",
    "on a.drf_id = b.drf_id\n",
    "where a.phdfy = '2015' and a.phdinst in ('104179', '110680', '139658', '141574', '151351', '153658', '155317', '164988', '170976', \n",
    "    '201885', '204796', '209542', '214777', '228778', '240444', '243780') ) c\n",
    "inner join ds_iris_umetrics.dbo.semester d\n",
    "on c.emp_number = d.emp_number\n",
    "\"\n",
    "cohort_joined <- dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see cohort_joined\n",
    "head(cohort_joined)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "names(cohort_joined)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see number of rows relative to individuals\n",
    "cohort_joined %>%\n",
    "    summarize(n=n(), n_people = n_distinct(drf_id))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 3: Transform data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unique(cohort_joined$semester)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# changed semester column name to relative for graduation\n",
    "cohort_joined <- cohort_joined %>%\n",
    "    mutate(\n",
    "        sem_structure = paste(as.character(as.numeric(substring(semester, 1, 4)) - as.numeric(phdcy)), substring(semester, 6, 8))\n",
    "    ) %>%\n",
    "    mutate(sem_fix_month = case_when(\n",
    "        word(sem_structure, 2) == 'may' ~ paste(word(sem_structure, 1), 'Sum'),\n",
    "        word(sem_structure, 2) == 'jan' ~ paste(word(sem_structure, 1), 'Spr'),\n",
    "        TRUE ~ paste(word(sem_structure, 1), 'Fal'),\n",
    "        )\n",
    "          ) %>%\n",
    "    mutate(upd_semester = case_when (\n",
    "        word(sem_fix_month, 1) == '0' ~ word(sem_fix_month, 2),\n",
    "        TRUE ~ sem_fix_month\n",
    "    )\n",
    "          ) %>%\n",
    "    select(-c(sem_structure, sem_fix_month))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see updated dataframe\n",
    "head(cohort_joined)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# received federal funding if team size is at least 1\n",
    "cohort_joined <- cohort_joined %>%\n",
    "    mutate(\n",
    "        fed_funding = ifelse(team_size >= 1, 1, 0)\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unique(cohort_joined$upd_semester)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# need to complete full_cohort and fill out for all semesters where funding doesn't exist\n",
    "cohort_joined <- cohort_joined %>%\n",
    "    complete(drf_id,upd_semester, fill=list(fed_funding=0))\n",
    "\n",
    "unique(cohort_joined$fed_funding)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# see updated dataframe\n",
    "head(cohort_joined)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# find federal funding presence by semester\n",
    "by_sem <- cohort_joined %>%\n",
    "    group_by(drf_id, upd_semester) %>%\n",
    "    summarize(fed_pres = sum(fed_funding)) %>%\n",
    "    ungroup() %>%\n",
    "    mutate(fed_ind = ifelse(fed_pres == 1, 'yes', 'no')) %>%\n",
    "    select(-c(fed_pres))\n",
    "\n",
    "head(by_sem)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use pivot_wider\n",
    "funding_by_sem <- by_sem %>% \n",
    "    pivot_wider(names_from = upd_semester, values_from = fed_ind) %>%\n",
    "    select(drf_id, \"-3 Spr\", \"-3 Sum\", \"-3 Fal\", \"-2 Spr\", \"-2 Sum\", \"-2 Fal\", \"-1 Spr\", \"-1 Sum\", \"-1 Fal\", \"Spr\", \"Sum\", \"Fal\")\n",
    "\n",
    "head(funding_by_sem)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 4: Find Counts per Individual and Plot Patterns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# find counts per pattern\n",
    "patterns <- funding_by_sem %>%\n",
    "            group_by(`-3 Spr`, `-3 Sum`, `-3 Fal`, `-2 Spr`, `-2 Sum`, `-2 Fal`, `-1 Spr`, `-1 Sum`, `-1 Fal`, `Spr`, `Sum`, `Fal`) %>%\n",
    "            summarise(count = n_distinct(drf_id)) %>%\n",
    "            arrange(desc(count)) %>%\n",
    "            ungroup()\n",
    "\n",
    "head(patterns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patterns <- patterns %>%\n",
    "    mutate(pct = percent(count/sum(count),.01))\n",
    "\n",
    "head(patterns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# grab first 15 patterns\n",
    "patterns_graph <- patterns %>%\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_graph$count\n",
    "pcts <- patterns_graph$pct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patterns_graph$Pattern <- seq.int(nrow(patterns_graph))\n",
    "patterns_graph$count <- NULL\n",
    "patterns_graph$pct <- NULL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patterns_graph"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "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()` when creating `patterns`, 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_graph, names_to = 'Semester', 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 create the visualization using `geom_tile` in `ggplot`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# initial plot\n",
    "\n",
    "ggplot(data = patterns_long, aes(x = Semester, y = Pattern)) + \n",
    "geom_tile(aes(fill = Status), colour = 'black')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# make 1 the highest level and sort the semesters in order\n",
    "\n",
    "# provide order to display the semesters\n",
    "sem_order <- c(\"-3 Spr\", \"-3 Sum\", \"-3 Fal\", \"-2 Spr\", \"-2 Sum\", \"-2 Fal\", \"-1 Spr\", \"-1 Sum\", \"-1 Fal\", \"Spr\", \"Sum\", \"Fal\")\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 lowest\n",
    "\n",
    "ggplot(data = patterns_long, aes(x = factor(Semester, level=sem_order), y = ordered(Pattern, levels=rev(levels)))) +    # sort y-axis according to levels specified above\n",
    "geom_tile(aes(fill = Status), colour = 'black')\n",
    "\n",
    "viz <- ggplot(data = patterns_long, aes(x = factor(Semester, level=sem_order), y = ordered(Pattern, levels=rev(levels)))) +    # sort y-axis according to levels specified above\n",
    "geom_tile(aes(fill = Status), colour = 'black')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# change color palette, specify font size, have semesters at top, add titles, and rename y-axis ticks\n",
    "\n",
    "viz +\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 = \"Individuals - Percentages\",\n",
    "    title = 'Federal Funding Patterns by Semester',\n",
    "    caption = 'Source: SED NCSES and UMETRICS data',\n",
    "    x = \"Semester\"\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": [
    "Counts can also be 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 lowest\n",
    "\n",
    "ggplot(data = patterns_long, aes(x = factor(Semester, level=sem_order), 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 = \"Individuals - Counts\",\n",
    "    title = 'Federal Funding Patterns by Semester',\n",
    "    caption = 'Source: SED NCSES and UMETRICS data',\n",
    "    x = \"Semester\"\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": [
    "## Choropleth map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This example explains how to show regional differences in the number of graduates by state using a map. A choropleth map is a powerful visualization tool which allows for easy comparison and communication of regional differences to external audiences.\n",
    "\n",
    "First, generate the base table of graduates by origin state."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get the 2015 SED cohort, with the individual ID (`drf_id`) and institution code (`phdinst`):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the 2015 SED cohort\n",
    "qry <- \"\n",
    "select drf_id, phdinst\n",
    "from ds_nsf_ncses.dbo.nsf_sed\n",
    "where phdfy = '2015'\n",
    "\"\n",
    "cohort_2015 <- dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "head(cohort_2015)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get the table with the geographic location of educational institutions (using IPEDS code):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the table with the\n",
    "qry <- \"\n",
    "select *\n",
    "from ds_public_1.dbo.ipeds_location\n",
    "\"\n",
    "institution_location <- dbGetQuery(con, qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "head(institution_location)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is a common variable in these two tables - the IPEDS code for the educational institutions (`phdinst` variable in the `cohort_2015` table and `unitid` in the `institution_location` table)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge two tables on the common variable - `phdinst` in the `cohort_table` and `unitid` in the `institution_location`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cohort_inst_location <- merge(cohort_2015, institution_location, by.x ='phdinst', by.y = 'unitid')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "head(cohort_inst_location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Using group_by and summarise(n_distinct) function\n",
    "state_counts <- cohort_inst_location %>%\n",
    "                    group_by(stabbr) %>%\n",
    "                    summarise(counts = n_distinct(drf_id))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "head(state_counts)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is a built-in dataset with state geometry in the `maps` package:\n",
    "> Note, this built-in dataset contains state geometry for 48 states."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "states <- map_data(\"state\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "head(states)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ggplot(data = states,\n",
    "      mapping = aes(x = long, y = lat, group = group)) +\n",
    "    geom_polygon(fill = 'white', color='black')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unique(states$region)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a crosswalk between the fully spelled state name and state abbreviation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "region <- unique(states$region)\n",
    "stabbr <- c('AL','AZ','AR','CA','CO','CT','DE','DC','FL','GA','ID','IL','IN','IA','KS','KY','LA','ME','MD',\n",
    "                  'MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA',\n",
    "                  'RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY')\n",
    "state_crosswalk <- data.frame(stabbr, region)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "head(state_crosswalk)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "states <- left_join(states,state_crosswalk, by='region')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Only on those states that match\n",
    "state_counts <- inner_join(state_counts, states, by='stabbr')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "head(state_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ggplot(data = state_counts,\n",
    "      mapping = aes(x = long, y = lat, group = group, fill = counts)) +\n",
    "        geom_polygon(color=\"gray90\") +\n",
    "        labs(\n",
    "        title = 'Most PhD students in 2015 cohort are from REDACTED',\n",
    "        caption = \"Source: SED NCSES Data\"\n",
    "    )"
   ]
  }
 ],
 "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
}