Lecture_3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <font color =#d14d0f>[ES-22AC] State Prisons and County Jails</font>\n",
    "### Part Three\n",
    "\n",
    "***\n",
    "\n",
    "### Professor: Victoria Robinson "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "34e4d49d-9687-4e5b-a27a-a0dcd1c4d570",
    "deepnote_cell_height": 171,
    "deepnote_cell_type": "code",
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 2617,
    "execution_start": 1649696010920,
    "source_hash": "c2aea1fe",
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Run this cell to set up your notebook\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from otter import Notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "4ff38d7170c64e9f820c04a4228daaca",
    "deepnote_cell_height": 248.78125,
    "deepnote_cell_type": "markdown",
    "owner_user_id": "f002be3a-ffea-4f50-b514-adb6f2c4b8ee",
    "tags": []
   },
   "source": [
    "**------**\n",
    "\n",
    "**For any additional support needed for this assignment, please check the bCourses assignment for updates**\n",
    "        \n",
    "**------**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "0e9a8bc78cf64b93a1ccfaa42573d411",
    "deepnote_cell_type": "text-cell-h1",
    "is_collapsed": false,
    "tags": []
   },
   "source": [
    "# COVID-19 in Prisons"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "64f8ab9eb6f043978466b386f702e6f3",
    "deepnote_cell_height": 119.59375,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "In California, during the early months of the COVID-19 pandemic, Coronavirus spread most quickly through state prisons. Due to the close proximity of roommates, poor living conditions, and uncertainty of the transmissibility of the virus, it became extremely difficult to contain outbreaks. The data we'll be analyzing comes from California's open data portal. Feel free to read more about the data on the [CDCR's website](https://data.ca.gov/dataset/cdcr-population-covid-19-tracking)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "f7a61363f1664ce6a296b6c31b1dfb43",
    "deepnote_cell_height": 636,
    "deepnote_cell_type": "code",
    "deepnote_table_loading": false,
    "deepnote_table_state": {
     "filters": [],
     "pageIndex": 2575,
     "pageSize": 10,
     "sortBy": []
    },
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 226,
    "execution_start": 1649696013544,
    "scrolled": false,
    "source_hash": "64e7318e",
    "tags": []
   },
   "outputs": [],
   "source": [
    "# load the data\n",
    "covid_in_prisons = pd.read_csv('data/covid19dashboard2023.csv')\n",
    "covid_in_prisons.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "8f488392c61e4d468ac3e2dc785110c5",
    "deepnote_cell_type": "text-cell-h3",
    "is_collapsed": false,
    "tags": []
   },
   "source": [
    "### About the Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "1fdeb83d60044e65abdc6265d52a563f",
    "deepnote_cell_height": 169.984375,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "Before we jump into any data analysis, it's important to familiarize ourselves with the dataset. \n",
    "\n",
    "Recall that the 'prisons' dataset was a Table. Here, the covid_in_prisons dataset is a DataFrame. (If you'd like to know more, read through the documentation of [DataFrames](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.html).) It looks just like a table did and has very similar features, but slightly different ways to walk through them.\n",
    "\n",
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "\n",
    "<font color = #d14d0f>**QUESTION 1**:</font> Scroll through the prisons dataset above. What do you notice? What do the rows and columns mean?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "cae531ea4cee4c75b81a63681fc7bb36",
    "deepnote_cell_height": 52.390625,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "*Replace this text with your response!*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "eaf6c670257141308b3126775d15c92e",
    "deepnote_cell_height": 111.1875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- END QUESTION -->\n",
    "In this notebook, we are focusing less on the technicalities of code writing and more on the common techniques that are used to manipulate data that we have already seen.  \n",
    "\n",
    "Below, display the different column names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "e0357e41c0e64391a5bd8ff4906450f2",
    "deepnote_cell_height": 156.578125,
    "deepnote_cell_type": "code",
    "deepnote_output_heights": [
     59.578125
    ],
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 62,
    "execution_start": 1649696013772,
    "source_hash": "3fc533b",
    "tags": []
   },
   "outputs": [],
   "source": [
    "covid_in_prisons.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "fa5f6b63da174b2d9cb4380f410019a6",
    "deepnote_cell_height": 97.1875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "Each row represents a prison or detention center on a particular day. The data starts March 10, 2020 and ends April 11, 2023. Below, display the different prisons and detention centers represented in the data using the *unique()* function of DataFrames."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "2baf41b86a2f4ead90ce734a5d7b2999",
    "deepnote_cell_height": 712.046875,
    "deepnote_cell_type": "code",
    "deepnote_output_heights": [
     597.046875
    ],
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 104,
    "execution_start": 1649696013832,
    "source_hash": "8a698056",
    "tags": []
   },
   "outputs": [],
   "source": [
    "institution_names = covid_in_prisons['InstitutionName'].unique()\n",
    "institution_names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "0b816f50189245c3b4646b4d921e3f2b",
    "deepnote_cell_height": 52.390625,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "Based on the following cell's code, we can see that there are 35 institutions represented in the covid_in_prisons DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "d0d8d1697b404c34a87386a1c50bdbf4",
    "deepnote_cell_height": 136.1875,
    "deepnote_cell_type": "code",
    "deepnote_output_heights": [
     21.1875
    ],
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 2,
    "execution_start": 1649696013937,
    "source_hash": "e5d768d6",
    "tags": []
   },
   "outputs": [],
   "source": [
    "# len() is a function that takes the length of an array/list (in this case, institution_names)\n",
    "len(institution_names)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "cd69a577234c4d61acafa6ac26a2e3f9",
    "deepnote_cell_height": 111.1875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "To see how many rows/records there are for any Institution in our DataFrame, we should take the sum of the amount of rows that have the given Institution Name in its 'InstitutionName' column."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "8069eda15bd9410d90f7783cde618be5",
    "deepnote_cell_height": 74.796875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "\n",
    "<font color = #d14d0f>**QUESTION 2.5**:</font>\n",
    "In the below cell, find the total number of rows/records from Avenal State Prison that exist in the covid_in_prisons DataFrame.\n",
    "\n",
    "<b>*Note</b>: For your code to work, the prison name needs to be written identically to how it is represented in institution_names (including spaces, capitalization, and parenthesis). **It is written in institution_names as: 'San Quentin State Prison (SQ)'***\n",
    "\n",
    "<b>*Hint*</b>: Remember we use [] to extract a column from a dataframe(table). <br>\n",
    "<b>*Hint*</b>: == checks if two things are equal to each other. We are trying to find if an institution is named Avenal State Prison. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "a9b17e92035e4f0d8809b307c6886913",
    "deepnote_cell_height": 118.1875,
    "deepnote_cell_type": "code",
    "deepnote_output_heights": [
     21.1875
    ],
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 50,
    "execution_start": 1649696013937,
    "source_hash": "9b2e12c4",
    "tags": []
   },
   "outputs": [],
   "source": [
    "sum(covid_in_prisons[...] == ...)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Your answer should be 1128."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "c5ec4204b10745cc95b47acc370a4f95",
    "deepnote_cell_height": 133.59375,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "\n",
    "<font color = #d14d0f>**QUESTION 2.5**:</font> Replicate the code from the cell above to find the amount of rows/records from San Quentin State Prison."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "8aac2e6b77c54e84bfbfb356fe05fde2",
    "deepnote_cell_height": 154.1875,
    "deepnote_cell_type": "code",
    "deepnote_output_heights": [
     21.1875
    ],
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 7,
    "execution_start": 1649696013981,
    "source_hash": "eedc5680",
    "tags": []
   },
   "outputs": [],
   "source": [
    "... # Delete this comment and write your line of code here\n",
    "# (copy and paste the cell above then modify the prison name!)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "cab0d40bff094cc8bdbcf69c1fca8d71",
    "deepnote_cell_height": 133.59375,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- END QUESTION --> \n",
    "We can actually see that there are the same number (1128) of records/rows for each Institution in our DataFrame by running the following cell. (There will be more discussion on how this doesn't imply that the data from each Institution is collected in the same ways.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "c68481a996f246b583ba0ef88abb964d",
    "deepnote_cell_height": 708,
    "deepnote_cell_type": "code",
    "deepnote_output_heights": [
     611
    ],
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 49,
    "execution_start": 1649696013981,
    "source_hash": "db58018e",
    "tags": []
   },
   "outputs": [],
   "source": [
    "covid_in_prisons.groupby('InstitutionName').size()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "96d12cc53e854fab8e964368be707e52",
    "deepnote_cell_height": 52.390625,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "Now, let's start our EDA (exploratory data analysis). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "cdb1bf709be74b62ac0bb371c2e7e349",
    "deepnote_cell_type": "text-cell-h3",
    "is_collapsed": false,
    "tags": []
   },
   "source": [
    "### Exploratory Data Analysis (EDA)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "31644d2a7d1d4ef58dfa6885c4459d3f",
    "deepnote_cell_height": 133.59375,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "**IMPORTANT NOTE:** the columns *TotalConfirmed* and *TotalDeaths* in the prisons dataset are **cummulative**. \n",
    "\n",
    "For example, if we take row 9992 in the covid_in_prisons dataset and look at the number in the 'TotalConfirmed' column, 2200 represented the TOTAL number of confirmed cases at California Correctional Center (CCC) up until 05/24/22. It's important to recognize that there were not 2200 new cases at ISP on this day, rather 2200 total confirmed cases at ISP up until 05/24/22."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "9670c13aaf7e431b9a1022935eb17ab1",
    "deepnote_cell_height": 307.75,
    "deepnote_cell_type": "code",
    "deepnote_output_heights": [
     174.75
    ],
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 5,
    "execution_start": 1649696014025,
    "source_hash": "18049605",
    "tags": []
   },
   "outputs": [],
   "source": [
    "# This function grabs the 9992nd row and shows the data from each column for this particular row.\n",
    "covid_in_prisons.iloc[9992]\n",
    "# Read the notes about what this data means above!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "\n",
    "<font color = #d14d0f>**QUESTION 3:**</font> Fill in the following code to isolate the last day in the data set (April 11, 2023) and find the Institution with the highest number of confirmed cases. Sort by the total number of confirmed cases from each facility."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "66ae64aa3cd6491bace8a7022558eb31",
    "deepnote_cell_height": 431,
    "deepnote_cell_type": "code",
    "deepnote_table_loading": false,
    "deepnote_table_state": {
     "filters": [],
     "pageIndex": 3,
     "pageSize": 10,
     "sortBy": []
    },
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 33,
    "execution_start": 1649696014084,
    "source_hash": "f62d2690",
    "tags": []
   },
   "outputs": [],
   "source": [
    "apr11_23 = covid_in_prisons[covid_in_prisons['Date'] == '2023-04-11'] # Remember we use [] to select a column\n",
    "apr11_23 = apr11_23.sort_values(..., ascending = ...) # Sort by total number of confirmed cases, highest first\n",
    "apr11_23.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "dd42447e4b154c1cb65cb66f25aea29d",
    "deepnote_cell_type": "text-cell-p",
    "is_collapsed": false,
    "tags": []
   },
   "source": [
    "According to the table above, the center with the highest number of confirmed cases is Wasco State Prison (WSP). Note, that we have no data on how often each prison tests inmates. For example, it is possible some centers may test more frequently than others, causing a higher case count because an individual can test positive multiple times."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "da796d1816004ffeb84d998da5868327",
    "deepnote_cell_height": 74.796875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "\n",
    "<font color = #d14d0f>**QUESTION 3.5:**</font> The table above is sorted in decreasing order according to the \"TotalConfirmed\" column. Do you recognize any prisons that are at the top of the table? "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "c7f6b58329ad4bc4bd427a03ff7eb907",
    "deepnote_cell_height": 52.390625,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "*Replace this text with your response!*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "\n",
    "<font color = #d14d0f>**QUESTION 4:**</font> Now, let's create a histogram to visualize the 8 institutions with the highest total confirmed cases.\n",
    "\n",
    "Fill in the code to the *barplot* function. x and y are *column names* from the data we pass in.\n",
    "\n",
    "*Hint*: we define the data in the cell below, and *x,y* should be strings (surrounded by quotes). \n",
    "\n",
    "**Note**: If you get a red textbox above your graph with the words \"FutureWarning\", don't worry about. It's simply an alert on how some of the notation we're using is getting updated in the future and won't affect your code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "8287fd3198324f5f8ba1f49e87b4815c",
    "deepnote_cell_height": 501.1875,
    "deepnote_cell_type": "code",
    "deepnote_output_heights": [
     21.1875,
     280
    ],
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 488,
    "execution_start": 1649696014113,
    "owner_user_id": "6461a15d-c525-4c0f-afd6-f07313cda3f0",
    "source_hash": "88355157",
    "tags": []
   },
   "outputs": [],
   "source": [
    "top8 = apr11_23[:8].reset_index() # top 8 institutions we are interested in\n",
    "sns.barplot(data = ..., y = ..., x = ...) # Fix this code\n",
    "plt.xlabel('Total Confirmed Cases')\n",
    "plt.ylabel('Institution Name')\n",
    "plt.title('Total Confirmed Cases for Top 8 Institutions');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "e1088c9a1fd4457ab35c2fda8acbb095",
    "deepnote_cell_height": 52.390625,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- END QUESTION --> \n",
    "Next, we will create a histogram to visualize the 8 institutions with the highest total confirmed cases."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "d6820ca12b934cec80ca62c3347e2b0f",
    "deepnote_cell_height": 88.796875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "\n",
    "<font color = #d14d0f>**QUESTION 4.5:**</font> What are some potential issues in drawing conclusions based on solely totals in data?\n",
    "\n",
    "*Hint: Consider how the populations of each prison may relate to the number of positive cases.*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "13d3bcf64b5d4fd29b690c096cfcb817",
    "deepnote_cell_height": 52.390625,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "*Replace this text with your response!*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "1c2cbe153c944e23a19ed0392d9f8c00",
    "deepnote_cell_height": 178.390625,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- END QUESTION --> \n",
    "\n",
    "Totals are very helpful units of measure because they indicate exact values. However, (especially if you're stuck on Question 4) consider: In a histogram like above, a Prison with 200 positive cases would be represented as a very small value. Notice that a Prison with 200 positive cases and a population of 250 is a highly contaminated prison, while a Prison with 200 positive cases and a population of 2000 is not as significant. \n",
    "\n",
    "For the rest of this notebook we are going to use the totals. We noted this just to remind you to always consider factors of the data that can misleading and to acknowledge what is and is not represented in the covid_in_prisons DataFrame."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "52185d7444964c49a55a3e063ff89d2a",
    "deepnote_cell_height": 74.796875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "Next, let's analyze the total number of new cases in the 14 days before each collection record (from the 'NewInTheLast14Days' column) across all institutions. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "b02aa0991a1340f4ab927920e75388f9",
    "deepnote_cell_height": 454,
    "deepnote_cell_type": "code",
    "deepnote_table_loading": false,
    "deepnote_table_state": {
     "filters": [],
     "pageIndex": 73,
     "pageSize": 10,
     "sortBy": []
    },
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 48,
    "execution_start": 1649696014611,
    "source_hash": "4196b7f",
    "tags": []
   },
   "outputs": [],
   "source": [
    "per14Days = covid_in_prisons.groupby('Date')['NewInTheLast14Days'].agg(np.sum).to_frame().reset_index()\n",
    "per14Days"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "7612f49210b247bdb61c1614b484a72e",
    "deepnote_cell_type": "text-cell-p",
    "is_collapsed": false,
    "tags": []
   },
   "source": [
    "Next, create a line plot below to visualize the table above. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "6476d76d8abb41bba26be1ce536eb042",
    "deepnote_cell_height": 531.1875,
    "deepnote_cell_type": "code",
    "deepnote_output_heights": [
     21.1875,
     328
    ],
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 1583,
    "execution_start": 1649696014685,
    "source_hash": "a1f85626",
    "tags": []
   },
   "outputs": [],
   "source": [
    "sns.lineplot(data=per14Days,  x='Date', y='NewInTheLast14Days')\n",
    "plt.xticks(['2020-03-10', '2020-06-10', '2020-09-10', '2020-12-10', '2021-03-10', '2021-06-10', '2021-09-10', '2021-12-10', '2022-03-10', '2022-06-10', '2022-09-10', '2022-12-10', '2023-3-10'], rotation=90)\n",
    "plt.ylabel('New Cases in the Last 14 Days')\n",
    "plt.title('New Cases Per 14 Day Period')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "c3aaa297af7e42a583ad69bf3dfed381",
    "deepnote_cell_height": 74.796875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "\n",
    "<font color = #d14d0f>**QUESTION 5:**</font> What do you notice about the above line plot? In what months and years do the two main peaks seem to coincide with? *Optional:* Any ideas about what was going on during these times in regards to COVID-19?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "6d932ab2cf594f56b84eff89f08fd1df",
    "deepnote_cell_height": 52.390625,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "*Replace this text with your response!*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "d0753aed70704b32805e24153bfcf4bf",
    "deepnote_cell_height": 52.390625,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "Below, calculate the death rate for each institution (from Jan 6, 2022)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "0811ebc8804e462b9fad055c9b774d57",
    "deepnote_cell_height": 744,
    "deepnote_cell_type": "code",
    "deepnote_table_loading": false,
    "deepnote_table_state": {
     "filters": [],
     "pageIndex": 0,
     "pageSize": 10,
     "sortBy": []
    },
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 92,
    "execution_start": 1649696016235,
    "source_hash": "145abb63",
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Since the data is cummulative, the data from the jan6_22 DataFrame is the sum. Then find the percent by divinding by 736.\n",
    "# Recall that 1128 is the number of rows in the DataFrame for each institution\n",
    "rates = apr11_23['TotalDeaths']/1128\n",
    "\n",
    "# Create a new column in the apr11_23 DataFrame \n",
    "apr11_23['DeathRates'] = rates\n",
    "\n",
    "# Sort the DeathRates column in descending order.\n",
    "apr11_23.sort_values('DeathRates', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "ce021e81ad1d40bc94693d4e55c133be",
    "deepnote_cell_height": 74.796875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "\n",
    "<font color = #d14d0f>**QUESTION 6:**</font> Name the three institutions with the highest COVID-19 death rates based on the DataFrame above. Read through the comments of the cell above for an explanation of how the DataFrame above was created."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "a8483c61990541539e362a3de8290230",
    "deepnote_cell_height": 52.390625,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "*Replace this text with your response!*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "23a86331f013488abcec42fded7fb5c1",
    "deepnote_cell_height": 74.796875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- END QUESTION --> \n",
    "Below we plot line graphs that show the Positive COVID Cases and COVID Deaths over time at the two prisons with the highest death rate in our data set (**California Institution for Men (CIM)** and **San Quentin State Prison (SQ)**)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "dfa749483e67408799468a2df44f6b58",
    "deepnote_cell_height": 587,
    "deepnote_cell_type": "code",
    "deepnote_output_heights": [
     328
    ],
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 2779,
    "execution_start": 1649696016321,
    "source_hash": "614c72f7",
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Run this code to create line plots ... feel free to ignore this code or take a look if interested!\n",
    "CIM_only = covid_in_prisons[covid_in_prisons['InstitutionName'] == 'California Institution for Men (CIM)'].sort_values('Date')\n",
    "sns.lineplot(x = 'Date', y='TotalDeaths', data=CIM_only, label = 'Total COVID Deaths at CIM')\n",
    "sns.lineplot(x = 'Date', y='TotalConfirmed', data=CIM_only, label = 'Total Positive COVID Cases at CIM')\n",
    "SQ_only = covid_in_prisons[covid_in_prisons['InstitutionName'] == 'San Quentin State Prison (SQ)'].sort_values('Date')\n",
    "sns.lineplot(x = 'Date', y='TotalDeaths', data=SQ_only, label = 'Total COVID Deaths at SQ')\n",
    "sns.lineplot(x = 'Date', y='TotalConfirmed', data=SQ_only, label = 'Total Positive COVID Cases at SQ')\n",
    "plt.title('COVID Cases and COVID Deaths at California Institution for Men (CIM) and San Quentin State Prison (SQ)')\n",
    "plt.ylabel('Number of People')\n",
    "plt.xticks(['2020-03-10', '2020-06-10', '2020-09-10', '2020-12-10', '2021-03-10', '2021-06-10', '2021-09-10', '2021-12-10', '2022-03-10', '2022-06-10', '2022-09-10', '2022-12-10', '2023-3-10'], rotation=90);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "0e0026a91a964f8f9438653a2d829ab9",
    "deepnote_cell_height": 133.59375,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "Note that even though you likely can't see the whole lines for the Total COVID Deaths of both institutions, they are both there but they just overlap.\n",
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "<font color = #d14d0f>**QUESTION 7:**</font> Which institution seems to have had a major spike in Total Positive COVID Cases in 2020? Approximately what month(s) did this take place in?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "ca6124fbb3694199874c84f9f01c1a13",
    "deepnote_cell_height": 52.390625,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "*Replace this text with your response!*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "2841a6f81ad14a1d87b351ac54d849d2",
    "deepnote_cell_type": "text-cell-h3",
    "is_collapsed": false,
    "tags": []
   },
   "source": [
    "### San Quentin State Prison in 2020"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "7163ce66485c48a984361b6c81a665e0",
    "deepnote_cell_height": 97.1875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- END QUESTION --> \n",
    "We will now look at San Quentin State Prison in particular during 2020, the primary pandemic year. We will plot 3 different lines on one graph to compare the San Quentin State Prison's population, cumulative positive COVID cases, and cumulative deaths caused by COVID. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "4cced2969aa04ed899776cebab6a856b",
    "deepnote_cell_height": 153,
    "deepnote_cell_type": "code",
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 36,
    "execution_start": 1649696064939,
    "source_hash": "49752408",
    "tags": []
   },
   "outputs": [],
   "source": [
    "SQ_Covid_2020 = pd.read_csv(\"data/SQ_2020.csv\") #importing cleaned data about San Quentin SP for 2020\n",
    "prisons_df = pd.read_csv(\"data/monthly_cdcr_upto2022.csv\") #importing prisons table from Lecture 1 as a DataFrame\n",
    "SQ_pop_df = prisons_df[prisons_df['institution_name']== 'SQ (SAN QUENTIN SP)']\n",
    "SQ_pop_2020 = SQ_pop_df[SQ_pop_df['year'] == 2020]\n",
    "# Run this cell, it isn't supposed to return anything since there are only assignment statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "723c5b168735489aafa02a4d3065ae7e",
    "deepnote_cell_height": 554.890625,
    "deepnote_cell_type": "code",
    "deepnote_output_heights": [
     null,
     280
    ],
    "deepnote_table_loading": false,
    "deepnote_table_state": {
     "filters": [],
     "pageIndex": 0,
     "pageSize": 10,
     "sortBy": []
    },
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 361,
    "execution_start": 1649696069105,
    "source_hash": "6778f54f",
    "tags": []
   },
   "outputs": [],
   "source": [
    "sns.lineplot(x='month', y='population_felons', data=SQ_pop_2020, label='SQ Population');\n",
    "sns.lineplot(x = 'Date', y='Total Deaths', data=SQ_Covid_2020, label = 'Total COVID Deaths at SQ');\n",
    "sns.lineplot(x = 'Date', y='Total Confirmed', data=SQ_Covid_2020, label = 'Total Positive COVID Cases at SQ');\n",
    "plt.xlabel('Month (in numbers ie, 2 = February, 2020)')\n",
    "plt.ylabel('Number of People')\n",
    "plt.title('San Quentin State Prison in 2020')\n",
    "plt.ylim(0,5000);\n",
    "# Don't worry if a red message pops up with your graph - it is a warning, but not an error.\n",
    "# All you need for analysis is the graph"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "cefda59a70474227a4c882d1dc19c7b2",
    "deepnote_cell_height": 88.796875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "<font color = #d14d0f>**QUESTION 8:**</font> Based on this graph, assign a range of months to the following: \n",
    "\n",
    "(Replace each **...** below with your response.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "d2e7402fcef243a8bdd17d4f0422d455",
    "deepnote_cell_height": 125.1875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "**Little to no COVID cases in the following month range:** ... \n",
    "\n",
    "**Rapidly increasing amount of COVID cases in the following month range:** ...\n",
    "\n",
    "**High but plateauing/minimally changing amount of COVID cases in the following month range:**  ..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "f1d660e423314bd8953e701454455c63",
    "deepnote_cell_height": 141.984375,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- END QUESTION --> \n",
    "As you may have noticed, the graph shows a decline in San Quentin State Prison population by about 1,000 people from the start to end of the year 2020. The San Quentin State Prison website from CDCR (the source of the data) spoke to this decrease in population with this statement: \"*To reduce crowding and promote better physical distancing, San Quentin State Prison’s inmate population has been reduced from 4,051 in March [2020] to 3,129 on Aug. 12 [2020]. This reduction has been accomplished through the suspension of intake from county jails, expedited releases and natural releases from the prison.*\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "7de185541f574806a974a2e91bccd8da",
    "deepnote_cell_height": 97.1875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "<font color = #d14d0f>**QUESTION 9:**</font> What are your opinions on this quotation? What are the consequences (positive and/or negative) of these reactions to the COVID outbreak in San Quentin State Prison? *There is no right answer; This question looking for you to consider the quotation and the context of the data to formulate an opinion of your own.*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "2047ba45afda4ec488da75a51d092be5",
    "deepnote_cell_height": 52.390625,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "*Replace this text with your response!*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "881042b272704e5995f2fc369fe45ea1",
    "deepnote_cell_height": 263.1875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- END QUESTION --> \n",
    "<!-- BEGIN QUESTION --> \n",
    "\n",
    "<font color = #d14d0f>**QUESTION 10:**</font> Reflection time! Please write a minimum of 4 sentences about what you've come to realize from this COVID_in_Prisons Homework Notebook.\n",
    "\n",
    "If you need any ideas for what to brainstorm, consider these topics:\n",
    "* San Quentin SP's [response to COVID outbreak in 2020](https://www.cdcr.ca.gov/covid19/san-quentin-state-prison-response/)\n",
    "* The prisons with the most and least COVID cases (seen before Question 4)\n",
    "* The spread of COVID-19 and diseases in institutions\n",
    "* How the methods of data collected were or weren't helpful \n",
    "    * (how would you change it if you collected the data?)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "853d4618df0a4d3287b2634aaeb60d15",
    "deepnote_cell_height": 52.390625,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "*Replace this text with your response!*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "99413aa4d3e743c1865b2d2d93253870",
    "deepnote_cell_height": 279.1875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "<!-- END QUESTION --> \n",
    "\n",
    "Thank you for taking the time to complete the homework notebook! Remember if you are struggling to check out the **resources for help** listed at the top of this notebook to get all of your questions answered. We hope you enjoyed this notebook and are so glad you took a step out of your comfort zone to view this material from a new perspective.\n",
    "\n",
    "**<font color = #d14d0f>TO SUBMIT YOUR HOMEWORK:</font>**\n",
    "\n",
    "**1. Complete this [Final Survey](https://docs.google.com/forms/d/e/1FAIpQLScDK227bq_qLJDLsysk-UOEXy7G6_7YuYHGp2ogzeRBh4OvEw/viewform?usp=sf_link)** to help improve this module.\n",
    "\n",
    "**2. Run all your cells by clicking \"Cell\" > \"Run All\".** (Should take no more than ~10 seconds)\n",
    "\n",
    "**3. Save the notebook by clicking \"File\" > \"Save and checkpoint\".**\n",
    "\n",
    "**4. Run the next cell!**\n",
    "\n",
    "**5. Submit completed Lecture 3 notebook to your corresponding bCourses assignment.** (Click the link that the next cell returns, then upload the downloaded pdf to your bCourses assignment.)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_id": "a57ea2bbcf6549fa97da6a071652014e",
    "deepnote_cell_height": 261.1875,
    "deepnote_cell_type": "code",
    "deepnote_to_be_reexecuted": true,
    "execution_millis": 2,
    "execution_start": 1649500184227,
    "source_hash": "10818aa0",
    "tags": []
   },
   "outputs": [],
   "source": [
    "#This may take a few extra seconds.\n",
    "from otter.export import export_notebook\n",
    "from IPython.display import display, HTML\n",
    "export_notebook(\"Lecture_3.ipynb\", filtering=True, pagebreaks=True)\n",
    "display(HTML(\"Save this notebook, then click <a href='Lecture_3.pdf' download>here</a> to open the pdf.\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "5697a464e2864248b731b971869bd5b6",
    "deepnote_cell_height": 97.1875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "If the cell above returns an error or clicking the link that is returned doesn't work for you, don't worry! Simply right click and choose **\"Save Link As...\" to save a copy of your pdf** onto your computer. Then, complete **Step 5: Submit completed Lecture 3 notebook to your corresponding bCourses assignment.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "0bed3fee12404930815e0e3e6864d72c",
    "deepnote_cell_height": 89.796875,
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "---\n",
    "Notebook developed by: Skye Pickett, Caitlin Yee, Salvador Ramirez Jr.\n",
    "\n",
    "Data Science Modules: http://ds-modules.github.io"
   ]
  }
 ],
 "metadata": {
  "deepnote": {
   "is_reactive": false
  },
  "deepnote_execution_queue": [],
  "deepnote_notebook_id": "b3c9a012-6663-402e-8ee4-c9405669b032",
  "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
}