Lecture_2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "# <font color =#d14d0f>[ES-22AC] State Prisons and County Jails</font>\n",
    "### Part Two\n",
    "\n",
    "***\n",
    "\n",
    "### Professor: Victoria Robinson \n",
    "\n",
    "This notebook continues the exploration into incarceration trends and impacts of prison realignment in California.\n",
    "\n",
    "*Estimated Time: 45 minutes*\n",
    "\n",
    "---\n",
    "\n",
    "### Table of Contents\n",
    "\n",
    "[THE DATA](#sectiondata)<br>\n",
    "\n",
    "\n",
    "[CONTEXT](#sectioncontext)<br>\n",
    "\n",
    "\n",
    "[JAILS](#section1)<br>\n",
    "\n",
    "1. [DATA](#subsection1)<br>\n",
    "2. [DATA ANALYSIS](#subsection2)<br>\n",
    "3. [GENERAL:SENTENCED & UNSENTENCED](#subsection3)<br>\n",
    "4. [GENDER DIVISION](#subsection4)<br>\n",
    "\n",
    "[Final Survey](#section2)<br>\n",
    "\n",
    "---\n",
    "\n",
    "**Please run the cell below before you begin.** It will load libraries needed to continue analyzing our data!\n",
    "\n",
    "**Dependencies:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "from datascience import * # This loads tools from the datascience library\n",
    "import numpy as np # Loads numerical methods\n",
    "\n",
    "from otter import Notebook # This will allow us to export your responses as a pdf\n",
    "\n",
    "import matplotlib  # This loads some visualization tools\n",
    "%matplotlib inline\n",
    "import matplotlib.pyplot as plt\n",
    "plt.style.use('fivethirtyeight') # This selects a certain 'style' for our graphs\n",
    "\n",
    "import ipywidgets as widgets # Loads a widgets library that will allow for interactive learning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "## <font color =#d14d0f>The Data</font> <a id='sectiondata'></a>\n",
    "---\n",
    "\n",
    "In this notebook, you will use data from the Jail Profile Survey provided by the Board of State and Community Corrections (BSCC). \n",
    "\n",
    "\n",
    "The Jail Profile Survey releases reports on data recorded by local agencies. Some of the information we will find here include the total number of unsentenced and sentenced males and females in each of these facilities. This data has been used to determine the needs of each of these counties when determining the distribution of state bond monies and future projections for other jail needs.\n",
    "\n",
    "If you are interested in learning more please visit:\n",
    "\n",
    "[Jail Profile Survey](http://www.bscc.ca.gov/downloads/JPSWorkbook.pdf) \n",
    "\n",
    "[Jail Profile Survey data](https://app.bscc.ca.gov/joq//jps/QuerySelection.asp)\n",
    "\n",
    "\n",
    "## <font color =#d14d0f>Context</font>  <a id='sectioncontext'></a>\n",
    "---\n",
    "\n",
    "Throughout the course, we have learned about the policies of realignment, incarceration, and crime trends in California. By exploring these data sets we hope to obtain a clearer picture of the magnitude of prison and county jail overcrowding per facility and region, and the effects of realignment policies on state prisons and county jails.\n",
    "\n",
    "To review, the key difference between state prisons and jails involve the process of sentencing. Prisons are designed for long term sentences, while jails are for those who are unsentenced or have short term sentences. It is important to note that short-term sentences are generally one year or less. Another difference is that prisons are larger and controlled at the state level. In contrast, jails are smaller and handled by a city or county.\n",
    "\n",
    "The relationship between the two institutions is emphasized by mass incarceration. Through this activity, we will analyze how overcrowding within California’s state prisons influenced the size of jail populations after realignment policies were implemented."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "## <font color =#d14d0f>Jails</font> <a id='section1'></a>\n",
    "---\n",
    "To observe how realignment influenced California's jails, we obtained data from 1996 to 2018, so that we could understand the trend over time. We will start by observing California's state prisons using the data we found through the BSCC.\n",
    "\n",
    "### **Data**\n",
    "\n",
    "#### Data Dictionary\n",
    "Below you will find a data dictionary for future reference. This data dictionary goes over what the column names represent in the data we will load below.\n",
    "\n",
    "|Column Name   | Description |\n",
    "|--------------|---------|\n",
    "|Jurisdiction | The unit of government that has legal authority over an inmate (state or federal)|\n",
    "|Facility | Name of the county jail |\n",
    "|Year |Year that the data was collected |\n",
    "|Month | Month that the data was collected |\n",
    "|Unsentenced males| Non-sentenced inmates are all inmates other than those who have been sentenced on all charges pending * ** |\n",
    "|Unsentenced females| Non-sentenced inmates are all inmates other than those who have been sentenced on all charges pending * **|\n",
    "|Sentenced males| Sentenced inmates are those who have been sentenced on all charges and are no longer on trial. This category includes inmates who are being incarcerated pending or during an appeal. * |\n",
    "|Sentenced females|Sentenced inmates are those who have been sentenced on all charges and are no longer on trial. This category includes inmates who are being incarcerated pending or during an appeal. * |\n",
    "|Total facility ADP| ADP Total should include all inmates (including those under contract from any agency/jurisdiction) assigned to all single/double and multiple occupancy cells, administrative segregation, disciplinary isolation, and medical and mental health beds.|\n",
    "\n",
    "\n",
    "**Please note the following:**\n",
    "\n",
    "\\* Note that the counts for sentenced and unsentences male/female inmates is an *average daily population (ADP)* for the given month.\n",
    "\n",
    "\n",
    "** For example, if an inmate has been sentenced on three charges but is still being tried on a fourth charge, they should be reported as “non-sentenced.”\n",
    "\n",
    "** If an inmate is found not to be competent for trial and is detained in a county jail facility, count them in Non-Sentenced (Male/Female & Misdemeanor/Felony). If they are detained in the state hospital, do not count them in any category."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "#### Importing the Data\n",
    "\n",
    "Let's start off by importing our jails data into our Jupyter Notebook. As a refresher, we do this by creating an instance of a table (`Table()`) and populating it with our data which we read in from a location within our folders. It takes in a file and reads and outputs it as a table for our use here!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "jail = Table().read_table(\"data/jails_cleaned.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Now that we've assigned it to a variable for future use, let's see the first five entries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "jail.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "### **Data Analysis**\n",
    "Before we begin, please note the following:\n",
    "1. Throughout the notebook you will encounter ellipsis (`...`) This is an indication that you need to replace the `...` with your code.\n",
    "2. Questions that are to be answered are clearly marked with <font color = #d14d0f>**bold orange text**</font>. At the end of this notebook, we will run a command that will export all your answers into a pdf."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "Just as we did in our first Prisons notebook, the first thing we want to do is check that the number of months does not exceed 276.\n",
    "\n",
    "<font color = #d14d0f>**Question 1a)**</font> Group by `Facility` using the `group()` table function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "# Replace the ellipsis below\n",
    "jail_grouped = jail.group(...)\n",
    "jail_grouped"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Now that you have grouped by facility, we see that the group function produced a two-column table with unique facility names and the number of times each occurred in our table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "<font color = #d14d0f>**Question 1b)**</font> Sort by `count` using the `sort()` table function and set descending to True."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "# Replace the ellipsis below\n",
    "jail_grouped.sort(..., descending = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Do you see any count abnormalities? In contrast to our Prisons notebook, there do not seem to be any issues with the counts!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "In the table above, we produced a table with a row entry for each month in a given year for 23 years for each jail. Like we did in our previous notebook, we want to get the **cumulative yearly totals for all institutions** to explore how this total number of inmates in the jail population changed over time. To accomplish this, the first thing we need to do is aggregate or group some columns.\n",
    "\n",
    "Our first step is to select the appropriate columns. We select the columns 'Year', 'Unsentenced males', 'Unsentenced females', 'Sentenced males', 'Sentenced females', and 'Total facility ADP' using the `select()` function.\n",
    "\n",
    "Next we want to store this as a new variable for future use. We assign this new table to a variable called `data_year`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "data_year= jail.select('Year','Unsentenced males', 'Unsentenced females','Sentenced males','Sentenced females', 'Total facility ADP')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "<font color = #d14d0f>**Question 2)**</font> If we want to obtain cumulative yearly totals for all institutions, what should we group by in the code below?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "# Replace the ellipsis with your code\n",
    "data_year = data_year.group(..., sum)\n",
    "data_year.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Our next step is to plot the trend year by year. Oftentimes it is much more useful to visually inspect data as it can reveal trends that are otherwise not easily seen in tables. Our first investigation will be into the total facility ADP over the years.\n",
    "\n",
    "As a refresher for plots, remember to use the `plot(x, y)` table function. Where the `x` and `y` represent two column names that you would like to plot along the x and y axis respectively. We then append this to our table's name using dot notation. This tells the computer in which table to look for those columns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "<font color = #d14d0f>**Question 3)**</font> Plot `Total facility ADP sum` over time. We also ask that you label your axes. We have included a descriptive title for you!\n",
    "\n",
    "Hint: Think about what two column names are relevant to this goal. Do not forget to write the column names inside quotations!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "# Replace the ellipsis with your code\n",
    "data_year.plot(..., ...)\n",
    "\n",
    "plt.xlabel(...)\n",
    "plt.ylabel(...)\n",
    "plt.title(\"Total Facility ADP Over Time\");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "For the remainder of the notebook, we will focus on years that mark significant shifts in population among jails. We will focus mainly on 2011 and 2014 by plotting red dots to mark those years in cells below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "First, let's obtain the data where the year equals 2011. To do this we will use the `where()` table function to limit our data to the year 2011 and assign it to a new variable `data2011`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "data2011 = data_year.where(\"Year\", 2011)\n",
    "\n",
    "data2011.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Note that we are only interested in the last column, 'Total facility ADP sum' and the year. Let's run a few more commands to assign the values in these two columns as our x and y coordinates for our first red dot."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "x_coordinate_2011 = data2011.column('Year')\n",
    "y_coordinate_2011 = data2011.column('Total facility ADP sum')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "Now it's your turn!\n",
    "\n",
    "<font color = #d14d0f>**Question 4a)**</font> Filter your data to the year 2014 following a similar process as above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "# Replace the ellipsis with your code\n",
    "... = data_year.where(..., ...)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "<font color = #d14d0f>**Question 4b)**</font> Save the value in the `Year` column as your 2014 x_coordinate. Repeat the process for `Total facility ADP sum` and save it as your 2014 y_coordinate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "# Replace the ellipsis with your code\n",
    "... = ....column(...)\n",
    "... = ....column(...)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Now we can add these data points as red dots on our plot! There's no need to edit the cell below, simply run it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "data_year.plot(\"Year\", \"Total facility ADP sum\")\n",
    "plt.xlabel(\"Year\")\n",
    "plt.ylabel(\"Total Facility ADP\")\n",
    "plt.title(\"Total Facility ADP over time\")\n",
    "\n",
    "plt.plot(x_coordinate_2011, y_coordinate_2011, 'ro')\n",
    "plt.plot(x_coordinate_2014, y_coordinate_2014, 'ro');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "<font color = #d14d0f>**Question 5)**</font> In looking at the graph you produced, how does it reflect the systematic changes of California's potential jail population? Name a court case that is represented by a red dot in your plot."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "*Replace this text with your response!*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "<!-- END QUESTION --> \n",
    "\n",
    "### **General: Designed and Staffed Capacity**\n",
    "In this section we revisit designed and staffed capacities. We will go ahead and compare the sentenced and unsentences populations for county jails. We will specifically be creating and looking at the following comparisons:\n",
    "* Overall (male and female) sentenced and (male and female) unsentenced\n",
    "* Male unsentenced vs male sentenced\n",
    "* Female unsentenced vs female sentenced"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "#### General: Sentenced vs Unsentenced\n",
    "\n",
    "To understand how sentenced vs unsentenced jail populations have changed over time, we will need to estimate the totals for each of these two categories. In looking at our data set we can notice that is is composed of 4 main columns, however none displays the total for these categories. \n",
    "\n",
    "|Sentenced males|Sentenced females|Unsentenced males| Unsentenced females|Total facility ADP|\n",
    "|--------------|---------|------|----------|-----|\n",
    "\n",
    "Therefore, we begin by calculating the sentenced total and the unsentenced total. We will need to combine male and female data for each of these two categories."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Let's begin by separating each column into a new variable so that we can easily extract and later combine the values that we need. As a refresher, you can use the table function `column()` to select the values in a given column."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "First, we select all the values for male sentenced and female sentenced and save it into new variables `m_sentenced` and `f_sentenced` respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "m_sentenced = data_year.column(\"Sentenced males sum\")\n",
    "f_sentenced = data_year.column(\"Sentenced females sum\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "Your turn!\n",
    "\n",
    "<font color = #d14d0f>**Question 6a)**</font> Repeat the procedure above, but now selected the columns for **unsentenced** males and females, and save them into new variables `m_unsentenced` and `f_unsentenced` respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "# Replace the ellipsis with your code\n",
    "\n",
    "... = data_year.column(...)\n",
    "... = data_year.column(...)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Now that we've saved all the relevant variables, let's see what `m_sentenced` looks like!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "m_sentenced"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "It looks like a list of numbers! This means that we can add the values from `m_sentenced` and `f_sentenced` into a single column to get the total sentenced per year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "sentenced_all =  m_sentenced + f_sentenced"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "<font color = #d14d0f>**Question 6b)**</font> Add `m_unsentenced` and`f_unsentenced` together and assign the sum to a new variable and call it `unsentenced_all`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "# Replace the ellipsis with your code\n",
    "\n",
    "unsentenced_all = ..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "We can now go ahead and add these two values to our original data table (called `data_year`). We will use the table function `with_column()` which takes in a label for your column, and the values that you want to assign to that new column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "data_year = data_year.with_column(\"Total Sentenced\", sentenced_all)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "<font color = #d14d0f>**Question 6c)**</font> Now it's your turn to add the list of values in `unsentenced_all`. Name the new column `Total Unsentenced`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "# Replace the ellipsis with your code\n",
    "\n",
    "data_year = data_year.with_column(..., ...)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Now let's look at our table to ensure that everything is in order. Use the `show` function to view the first 5 rows of your new table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "data_year.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "In the following cell, we will select the necessary columns to plot both sentenced and unsentenced totals over time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "totals = data_year.select(\"Year\", \"Total Sentenced\", \"Total Unsentenced\")\n",
    "\n",
    "# Now to plot our data!\n",
    "totals.plot(\"Year\")\n",
    "\n",
    "plt.title(\"Total Sentenced vs Total Unsentenced\")\n",
    "plt.xlabel(\"Year\")\n",
    "plt.ylabel(\"Number of People\");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "<font color = #d14d0f>**Question 7)**</font> Do you notice anything interesting about this visualization we just plotted?. What do you notice about the difference in the two lines before the year 2000? Do you notice anything interesting after 2010?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "*Replace this text with your response!*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "<!-- END QUESTION --> \n",
    "\n",
    "---\n",
    "Now let's explore this same data, but as **percentages**. Think about why we might want to work with percentages. Our end goal here will be to plot percent ADP over time.\n",
    "\n",
    "To do this, we can get our total using the column `Total facility ADP sum` and then divide the total sentenced and total unsentenced by this value. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "total_adp = data_year.column(\"Total facility ADP sum\")\n",
    "\n",
    "sent_percent = sentenced_all / total_adp * 100\n",
    "unsent_percent = unsentenced_all / total_adp * 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Great! Now that we have our percentages, let's repeat the same process as above to add these new percentage values to our `data_year` table and select the relevant columns that we want in order to plot percent ADP over time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "data_year = data_year.with_column(\"Total Sentenced Percent\", sent_percent)\n",
    "data_year = data_year.with_column(\"Total Unsentenced Percent\", unsent_percent)\n",
    "\n",
    "percent_totals = data_year.select(\"Year\", \"Total Sentenced Percent\", \"Total Unsentenced Percent\")\n",
    "percent_totals"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Now let's plot this data using our same line plot method as before!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false,
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "percent_totals.plot(\"Year\")\n",
    "plt.title(\"Total Sentenced Percent vs Total Unsentenced Percent\")\n",
    "plt.xlabel(\"Year\")\n",
    "plt.ylabel(\"Percent of People\");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Similar to above, let's try plotting dots so we can see what happened at specific years in the jails, namely, 2011 and 2014. Let's see how these specific years play out on our plot above by plotting red dots representing those years.\n",
    "\n",
    "First, let's start off by getting the data in the years 2011 and 2014."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "data2011 = percent_totals.where(\"Year\", 2011)\n",
    "data2014 = percent_totals.where(\"Year\", 2014)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Next, we want to get the x and y coordinates of each point using the data we just found. For example, using data2011, can now assign x_coordinate2011 to the year column and the y_coordinate2011 to the Percent column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "x_coordinate2011 = data2011.column('Year')\n",
    "y_coordinate2011 = data2011.column(\"Total Sentenced Percent\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "<font color = #d14d0f>**Question 8a)**</font>  Now, repeat this procedure for the year 2014. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "# Replace the ellipsis with your code\n",
    "\n",
    "x_coordinate2014 = data2014.column(...)\n",
    "y_coordinate2014 = data2014.column(...)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "<font color = #d14d0f>**Question 8b)**</font> Now that you've calculated the coordinates in 2011 and 2014 for *sentenced* percents, let's repeat the same process for *unsentenced* percents."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "# Replace the ellipsis with your code\n",
    "\n",
    "x_coordinate2011_un = data2011.column(...)\n",
    "y_coordinate2011_un = data2011.column(...)\n",
    "\n",
    "x_coordinate2014_un = data2014.column(...)\n",
    "y_coordinate2014_un = data2014.column(...)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Finally we add our points to our plot by running the cell below!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "percent_totals.plot(\"Year\")\n",
    "plt.title(\"Total Sentenced Percent vs Total Unsentenced Percent\")\n",
    "plt.xlabel(\"Year\")\n",
    "plt.ylabel(\"Percent of People\")\n",
    "\n",
    "plt.plot(x_coordinate2011, y_coordinate2011, 'ro')\n",
    "plt.plot(x_coordinate2014, y_coordinate2014, 'ro')\n",
    "plt.plot(x_coordinate2011_un, y_coordinate2011_un, 'ro')\n",
    "plt.plot(x_coordinate2014_un, y_coordinate2014_un, 'ro');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "<font color = #d14d0f>**Question 9)**</font> Is there anything interesting that you see related to the percentages and years? How does looking at percentages and numbers compare?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tag": "include",
    "tags": [
     "include"
    ]
   },
   "source": [
    "*Replace this text with your response!*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "<!-- END QUESTION --> \n",
    "\n",
    "### **Gender Divison**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "#### Sentenced vs Unsentenced\n",
    "\n",
    "We just looked at the total number of people who were sentenced and the total number of people who were unsentenced by year. Next, let's look at just the number of **males who were sentenced vs the number of males who were unsentenced**. Let's start by selecting the relevant columns that we are going to use for our analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "males = data_year.select(\"Year\", \"Sentenced males sum\", \"Unsentenced males sum\")\n",
    "males.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "We can now use these columns and plot the total number of males sentenced and unsentenced per year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "males.plot(\"Year\")\n",
    "\n",
    "plt.title(\"Total Sentenced vs Total Unsentenced (Males)\")\n",
    "plt.xlabel(\"Year\")\n",
    "plt.ylabel(\"Number of Males\");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "We have found and plotted the counts of males but let's try finding the **percentage** of males from the total ADP count. Start by getting the sum of the sentenced males column, the sum of the unsentenced males column, and then dividing both of these columns by the total ADP.\n",
    "\n",
    "Hint: See if you can use a variable from earlier analysis!\n",
    "\n",
    "As a challenge, try coding the above process yourself in a blank code cell below. Otherwise, run the cell below that!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "# An empty code cell for your challenge should you choose to accept it.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "male_sent = males.column(\"Sentenced males sum\")\n",
    "male_unsent = males.column(\"Unsentenced males sum\")\n",
    "\n",
    "m_sent_percent = male_sent / total_adp * 100\n",
    "m_unsent_percent = male_unsent / total_adp * 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Let's add these new percentage columns to our male table and then select the appropriate columns for plotting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "males = males.with_column(\"Total Male Sentenced Percent\", m_sent_percent)\n",
    "males = males.with_column(\"Total Male Unsentenced Percent\", m_unsent_percent)\n",
    "m_percent_totals = males.select(\"Year\", \"Total Male Sentenced Percent\", \"Total Male Unsentenced Percent\")\n",
    "\n",
    "m_percent_totals"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Now let us plot the **percentage** of males who were sentenced vs the percentage of males who were unsentenced."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "m_percent_totals.plot(\"Year\")\n",
    "\n",
    "plt.title(\"Total Sentenced vs Total Unsentenced Percents (Males)\")\n",
    "plt.xlabel(\"Year\")\n",
    "plt.ylabel(\"Percent of Males\");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "What's the next step? Adding the red dots! Now that you are more familiar with the process, we will allow for more coding below by outlining the steps in the comments.\n",
    "\n",
    "<font color = #d14d0f>**Optional Challenge)**</font> Complete the entire process that ends in the creation of two red markers for the years 2011 and 2014. Otherwise, we will have a code cell further down will all the code filled in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "outputs": [],
   "source": [
    "# STEP ONE: Filter data\n",
    "# STEP TWO: Create x and y coordinates for the two years\n",
    "# STEP THREE: Create a base plot of total sentences vs. total unsentenced over time\n",
    "# STEP FOUR: Add your points as red dots on our plot!\n",
    "\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Compare your optional challenge response to the code below, or run this cell to complete the optional challenge."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "# Filtering data for just 2011 and 2014\n",
    "data2011 = m_percent_totals.where(\"Year\", 2011)\n",
    "data2014 = m_percent_totals.where(\"Year\", 2014)\n",
    "\n",
    "# Create x and y coordinates for 2011\n",
    "x_coordinate2011 = data2011.column('Year')\n",
    "y_coordinate2011 = data2011.column(\"Total Male Sentenced Percent\")\n",
    "\n",
    "# Create x and y coordinates for 2014\n",
    "x_coordinate2014 = data2014.column('Year')\n",
    "y_coordinate2014 = data2014.column(\"Total Male Sentenced Percent\")\n",
    "\n",
    "# Base plot creation\n",
    "m_percent_totals.plot(\"Year\")\n",
    "\n",
    "plt.title(\"Total Sentenced vs Total Unsentenced Percents (Males)\")\n",
    "plt.xlabel(\"Year\")\n",
    "plt.ylabel(\"Percent of Males\")\n",
    "\n",
    "\n",
    "# Adding our points\n",
    "plt.plot(x_coordinate2011, y_coordinate2011, 'ro')\n",
    "plt.plot(x_coordinate2014, y_coordinate2014, 'ro')\n",
    "plt.plot(x_coordinate2011_un, y_coordinate2011_un, 'ro')\n",
    "plt.plot(x_coordinate2014_un, y_coordinate2014_un, 'ro')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "<font color = #d14d0f>**Question 10)**</font> Look at the two plots that you made specifically for males. What patterns do you notice? Is there anything interesting you notice related to the years/points we plotted?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "*Replace this text with your response!*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "<!-- END QUESTION --> \n",
    "\n",
    "#### Females: Sentenced vs. Unsentenced\n",
    "\n",
    "Above we analyzed just the males that were sentenced and unsentenced. Now we will do the same with females. Because the process is exactly similar to what we've done before we will simply have you run the code cells to generate the plots and focus on analysis instead!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false,
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "# First, we create a table with only female information:\n",
    "females = data_year.select('Year', 'Sentenced females sum', 'Unsentenced females sum')\n",
    "\n",
    "# Next, we generate a plot that shows the number of both sentenced and unsentenced \n",
    "# females over the years.\n",
    "females.plot('Year')\n",
    "\n",
    "plt.title(\"Total Sentenced vs Total Unsentenced (Females)\")\n",
    "plt.xlabel(\"Year\")\n",
    "plt.ylabel(\"Number of Females\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "<font color = #d14d0f>**Question 11)**</font> What do you notice about this plot compared to the related males plot? Make sure you are comparing the correct graphs. This plot does not deal with percentages!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "*Replace this text with your response!*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "<!-- END QUESTION --> \n",
    "\n",
    "---\n",
    "We can also calculate the percentages of sentenced females and unsentenced females and plot this relationship."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First, store necessary data in new variables for future use\n",
    "female_sent = females.column('Sentenced females sum')\n",
    "female_unsent = females.column('Unsentenced females sum')\n",
    "\n",
    "# Then calculate percentages:\n",
    "f_sent_percent = (female_sent / total_adp) * 100\n",
    "f_unsent_percent = (female_unsent / total_adp) * 100\n",
    "\n",
    "# Finally, add the new variables to our table and select relevant columns for plotting:\n",
    "females = females.with_columns(\n",
    "    \"Total Female Sentenced Percent\", f_sent_percent,\n",
    "    \"Total Female Unsentenced Percent\", f_unsent_percent)\n",
    "\n",
    "f_percent_totals = females.select('Year', 'Total Female Sentenced Percent', 'Total Female Unsentenced Percent')\n",
    "f_percent_totals"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "Using these three columns, we can plot the percentages for females as well as add the red dots on the years 2011 and 2014."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "outputs": [],
   "source": [
    "# Select years of interest\n",
    "data2011 = f_percent_totals.where(\"Year\", 2011)\n",
    "data2014 = f_percent_totals.where(\"Year\", 2014)\n",
    "\n",
    "# Get the coordinates for the Year 2011 for the Total Female Sentenced Percent \n",
    "x_coordinate2011 = data2011.column('Year')\n",
    "y_coordinate2011 = data2011.column(\"Total Female Sentenced Percent\")\n",
    "\n",
    "# Get the coordinates for the Year 2014 for the Total Female Sentenced Percent \n",
    "x_coordinate2014 = data2014.column('Year')\n",
    "y_coordinate2014 = data2014.column(\"Total Female Sentenced Percent\")\n",
    "\n",
    "# Get the coordinates for the Year 2011 for the Total Female Unsentenced Percent \n",
    "x_coordinate2011_un = data2011.column('Year')\n",
    "y_coordinate2011_un = data2011.column(\"Total Female Unsentenced Percent\")\n",
    "\n",
    "# Get the coordinates for the Year 2014 for the Total Female Unsentenced Percent \n",
    "x_coordinate2014_un = data2014.column('Year')\n",
    "y_coordinate2014_un = data2014.column(\"Total Female Unsentenced Percent\")\n",
    "\n",
    "# Plot \n",
    "f_percent_totals.plot(\"Year\")\n",
    "plt.title(\"Total Sentenced vs Total Unsentenced Percents (Females)\")\n",
    "plt.xlabel(\"Year\")\n",
    "plt.ylabel(\"Percent of Females\")\n",
    "\n",
    "# Add red dots\n",
    "plt.plot(x_coordinate2011, y_coordinate2011, 'ro')\n",
    "plt.plot(x_coordinate2014, y_coordinate2014, 'ro')\n",
    "plt.plot(x_coordinate2011_un, y_coordinate2011_un, 'ro')\n",
    "plt.plot(x_coordinate2014_un, y_coordinate2014_un, 'ro')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "<font color = #d14d0f>**Question 12)**</font> Compare the totals plot (completed before) to the one you just created. What kind of story does this plot reveal?\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "*Replace this text with your response!*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "<!-- END QUESTION -->\n",
    "\n",
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "<font color = #d14d0f>**Question 13)**</font> Given the last graph from the previous notebook, compare your final results to the ones presented here. What story do these two data sets (Prisons and Jails) tell you when you look at them side by side? How do they show the impact of realignment? The graph is shown below.\n",
    "\n",
    "<img src='./data/lecture1_finalgraph.png'>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "include"
    ]
   },
   "source": [
    "*Replace this text with your response!*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "<!-- END QUESTION --> \n",
    "\n",
    "Thank you for taking the time to complete the notebook! We hope you found it useful and insightful."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "## Saving the Notebook as an PDF\n",
    "\n",
    "Congrats on finishing this notebook! \n",
    "\n",
    "1. **Important:** Click the Save icon located at the far left on the top toolbar. Make sure to do this before following the next steps. You can also click 'File' > 'Save and Checkpoint'.\n",
    "2. Run the code cell below:\n",
    "    * There is no need to edit the code cell.\n",
    "    * If no file automatically downloads or opens in another tab that is viewable, **right click on the link generated and save it.**\n",
    "5. Once the file downloads, open it using a PDF reader to make sure that everything looks okay."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from otter.export import export_notebook\n",
    "from IPython.display import display, HTML\n",
    "export_notebook(\"Lecture_2.ipynb\", filtering=True, pagebreaks=True)\n",
    "display(HTML(\"Save this notebook, then click <a href='Lecture_2.pdf' download>here</a> to open the pdf.\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "ignore"
    ]
   },
   "source": [
    "---\n",
    "Notebook developed by: Ashley Quiterio, Shalini Kunapuli, Alma Pineda, and William McEachen\n",
    "\n",
    "Notebook renovated by: Skye Pickett, Caitlin Yee, Salvador Ramirez Jr \n",
    "\n",
    "Data Science Modules: https://ds-modules.github.io\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}