02_Dataset_Exploration_and_Visualization.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dataset Exploration\n",
    "----------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "\n",
    "In an ideal world, we will have all of the data we want with all of the desirable properties (no missing values, no errors, standard formats, and so on). \n",
    "However, that is hardly ever true - and we have to work with using our datasets to answer questions of interest as intelligently as possible. \n",
    "\n",
    "In this notebook, we will explore our datasets to answer some questions of interest. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Learning Objectives\n",
    "\n",
    "This notebook will give you the opportunity to spend some hands-on time with the data. \n",
    "\n",
    "This notebook will take you around the different ways you can analyze your data. This involves looking at basic metrics in the larger dataset, taking a random sample, creating derived variables, making sense of the missing values, and so on. \n",
    "\n",
    "This will be done using both SQL and `pandas` in Python. The `sqlite3` Python package will give you the opportunity to interact with the database using SQL to pull data into Python. Some additional manipulations will be handled by Pandas in Python (by converting your datasets into dataframes).\n",
    "\n",
    "This notebook will provide an introduction and examples for: \n",
    "\n",
    "- How to create new tables from the larger tables in database (sometimes called the \"analytical frame\")\n",
    "- How to explore different variables of interest\n",
    "- How to explore aggregate metrics\n",
    "- How to handle missing values\n",
    "- How to join newly created tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Methods\n",
    "\n",
    "We will be using the `sqlite3` Python package to access tables in our database - SQLite3. \n",
    "\n",
    "To read the results of our queries, we will be using the `pandas` Python package, which has the ability to read tabular data from SQL queries into a pandas DataFrame object. Within `pandas`, we will use various commands to:\n",
    "\n",
    "- Create statistical summaries\n",
    "- Create subsets of the data\n",
    "\n",
    "Within SQL, we will use various queries to:\n",
    "\n",
    "- select data subsets\n",
    "- Sum over groups\n",
    "- create new tables\n",
    "- Count distinct values of desired variables\n",
    "- Order data by chosen variables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Python Setup\n",
    "\n",
    "In Python, we `import` packages. The `import` command allows us to use libraries created by others in our own work by \"importing\" them. You can think of importing a library as opening up a toolbox and pulling out a specific tool. Among the most famous Python packages:\n",
    "- `numpy` is short for \"numerical Python\". `numpy` is a lynchpin in Python's scientific computing stack. Its strengths include a powerful *N*-dimensional array object, and a large suite of functions for doing numerical computing. \n",
    "- `pandas` is a library in Python for data analysis that uses the DataFrame object (modeled after R DataFrames, for those familiar with that language) which is similiar to a spreedsheet but allows you to do your analysis programaticaly rather than the point-and-click of Excel. It is a lynchpin of the PyData stack and is built on top of `numpy`.  \n",
    "- `sqlite3` is a library that helps us connect to an sqlite3 database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pandas-related imports\n",
    "import pandas as pd\n",
    "\n",
    "# database interaction imports\n",
    "import sqlite3\n",
    "\n",
    "# Graphing\n",
    "import matplotlib as mpl\n",
    "import seaborn as sns\n",
    "%matplotlib inline "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__When in doubt, use shift + tab to read the documentation of a method.__\n",
    "\n",
    "__The `help()` function provides information on what you can do with a function.__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on built-in function connect in module _sqlite3:\n",
      "\n",
      "connect(...)\n",
      "    connect(database[, timeout, detect_types, isolation_level,\n",
      "            check_same_thread, factory, cached_statements, uri])\n",
      "    \n",
      "    Opens a connection to the SQLite database file *database*. You can use\n",
      "    \":memory:\" to open a database connection to a database that resides in\n",
      "    RAM instead of on disk.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# for example\n",
    "help(sqlite3.connect)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load the Data\n",
    "\n",
    "We can execute SQL queries using Python to get the best of both worlds. For example, Python - and pandas in particular - make it much easier to calculate descriptive statistics of the data. Additionally, as we will see in the Data Visualization exercises, it is relatively easy to create data visualizations using Python. \n",
    "\n",
    "Pandas provides many ways to load data. It allows the user to read the data from a local csv or excel file, pull the data from a relational database, or read directly from a URL (when you have internet access). Since we are working with an SQLite3 database, we will demonstrate how to use pandas to read data from a relational database. For examples to read data from a CSV file, refert to the pandas documentation [Getting Data In/Out](pandas.pydata.org/pandas-docs/stable/10min.html#getting-data-in-out).\n",
    "\n",
    "The function to run a SQL query and pull the data into a pandas dataframe (more to come) is `pd.read_sql()`. Just like doing a SQL query from pgAdmin, this function will ask for some information about the database, and what query you would like to run. Let's walk through the example below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Establish a Connection to the Database\n",
    "\n",
    "The first parameter is the connection to the database. To create a connection we will use the SQLAlchemy package and tell it which database we want to connect to, just like in pgAdmin. Additional details on creating a connection to the database are provided in the [Databases](02_1_Databases.ipynb) notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Parameter 1: Connection__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# to create a connection to the database, \n",
    "# we need to pass the name of the database \n",
    "\n",
    "DB = 'ncdoc.db'\n",
    "\n",
    "conn = sqlite3.connect(DB)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Formulate Data Query\n",
    "\n",
    "Depending on what data we are interested in, we can use different queries to pull different data. In this example, we will pull all the content of the offenders data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Create a query as a `string` object in Python__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = '''\n",
    "SELECT *\n",
    "FROM inmate\n",
    "LIMIT 20;\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note:\n",
    "\n",
    "- the three quotation marks surrounding the query body is called multi-line string. It is quite handy for writing SQL queries because the new line character will be considered part of the string, instead of breaking the string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT *\n",
      "FROM inmate\n",
      "LIMIT 20;\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Now that we have defined a variable `query`, we can call it in the code\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Note that the `LIMIT` provides one simple way to get a \"sample\" of data; however, using `LIMIT` does **not provide a _random_** sample. You may get different samples of data than others using just the `LIMIT` clause, but it is just based on what is fastest for the database to return."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pull Data from the Database\n",
    "\n",
    "Now that we have the two parameters (database connection and query), we can pass them to the `pd.read_sql()` function, and obtain the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# here we pass the query and the connection to the pd.read_sql() function and assign the variable `wage` \n",
    "# to the dataframe returned by the function\n",
    "df = pd.read_sql(query, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INMATE_DOC_NUMBER</th>\n",
       "      <th>INMATE_LAST_NAME</th>\n",
       "      <th>INMATE_FIRST_NAME</th>\n",
       "      <th>INMATE_MIDDLE_INITIAL</th>\n",
       "      <th>INMATE_NAME_SUFFIX</th>\n",
       "      <th>INMATE_NAME_SOUNDEX_CODE</th>\n",
       "      <th>INMATE_GENDER_CODE</th>\n",
       "      <th>INMATE_RACE_CODE</th>\n",
       "      <th>INMATE_BIRTH_DATE</th>\n",
       "      <th>INMATE_ETHNIC_AFFILIATION</th>\n",
       "      <th>...</th>\n",
       "      <th>CURRENT_PENDING_REVIEWS_FLAG</th>\n",
       "      <th>ESCAPE_HISTORY_FLAG</th>\n",
       "      <th>PRIOR_INCARCERATIONS_FLAG</th>\n",
       "      <th>NEXT_PAROLE_REVIEW_TYPE_CODE</th>\n",
       "      <th>TIME_OF_LAST_MOVEMENT</th>\n",
       "      <th>POPULATION/MANAGEMENT_UNIT</th>\n",
       "      <th>INMATE_POSITIVELY_IDENTIFIED</th>\n",
       "      <th>PAROLE_AND_TERMINATE_STATUS</th>\n",
       "      <th>INMATE_LABEL_STATUS_CODE</th>\n",
       "      <th>PRIMARY_OFFENSE_QUALIFIER</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000004</td>\n",
       "      <td>AARON</td>\n",
       "      <td>DAVID</td>\n",
       "      <td>C</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>MALE</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>1961-10-15</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>00:09:00</td>\n",
       "      <td></td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000006</td>\n",
       "      <td>AARON</td>\n",
       "      <td>GERALD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>MALE</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>1951-07-17</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>00:11:00</td>\n",
       "      <td></td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000008</td>\n",
       "      <td>AARON</td>\n",
       "      <td>JAMES</td>\n",
       "      <td>M</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>MALE</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>1963-12-29</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>23:59:00</td>\n",
       "      <td></td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>FILE JACKET LABEL PRINTED</td>\n",
       "      <td>PRINCIPAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000010</td>\n",
       "      <td>AARON</td>\n",
       "      <td>KENNETH</td>\n",
       "      <td>T</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>MALE</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>1953-05-18</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>00:13:00</td>\n",
       "      <td></td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000014</td>\n",
       "      <td>AARON</td>\n",
       "      <td>MOYER</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>MALE</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>1921-08-26</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>00:12:00</td>\n",
       "      <td></td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 67 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  INMATE_DOC_NUMBER INMATE_LAST_NAME INMATE_FIRST_NAME INMATE_MIDDLE_INITIAL  \\\n",
       "0           0000004            AARON             DAVID                     C   \n",
       "1           0000006            AARON            GERALD                         \n",
       "2           0000008            AARON             JAMES                     M   \n",
       "3           0000010            AARON           KENNETH                     T   \n",
       "4           0000014            AARON             MOYER                         \n",
       "\n",
       "  INMATE_NAME_SUFFIX INMATE_NAME_SOUNDEX_CODE INMATE_GENDER_CODE  \\\n",
       "0                                                           MALE   \n",
       "1                                                           MALE   \n",
       "2                                                           MALE   \n",
       "3                                                           MALE   \n",
       "4                                                           MALE   \n",
       "\n",
       "  INMATE_RACE_CODE INMATE_BIRTH_DATE INMATE_ETHNIC_AFFILIATION  ...  \\\n",
       "0            WHITE        1961-10-15                   UNKNOWN  ...   \n",
       "1            WHITE        1951-07-17                   UNKNOWN  ...   \n",
       "2            WHITE        1963-12-29                   UNKNOWN  ...   \n",
       "3            BLACK        1953-05-18                   UNKNOWN  ...   \n",
       "4            WHITE        1921-08-26                   UNKNOWN  ...   \n",
       "\n",
       "  CURRENT_PENDING_REVIEWS_FLAG ESCAPE_HISTORY_FLAG PRIOR_INCARCERATIONS_FLAG  \\\n",
       "0                            N                   N                         Y   \n",
       "1                            N                   N                         Y   \n",
       "2                            N                   N                         Y   \n",
       "3                            N                   N                         Y   \n",
       "4                            N                   N                         Y   \n",
       "\n",
       "  NEXT_PAROLE_REVIEW_TYPE_CODE TIME_OF_LAST_MOVEMENT  \\\n",
       "0                                           00:09:00   \n",
       "1                                           00:11:00   \n",
       "2                                           23:59:00   \n",
       "3                                           00:13:00   \n",
       "4                                           00:12:00   \n",
       "\n",
       "  POPULATION/MANAGEMENT_UNIT INMATE_POSITIVELY_IDENTIFIED  \\\n",
       "0                                                     YES   \n",
       "1                                                     YES   \n",
       "2                                                     YES   \n",
       "3                                                     YES   \n",
       "4                                                     YES   \n",
       "\n",
       "  PAROLE_AND_TERMINATE_STATUS   INMATE_LABEL_STATUS_CODE  \\\n",
       "0                                                          \n",
       "1                                                          \n",
       "2                              FILE JACKET LABEL PRINTED   \n",
       "3                                                          \n",
       "4                                                          \n",
       "\n",
       "  PRIMARY_OFFENSE_QUALIFIER  \n",
       "0                            \n",
       "1                            \n",
       "2                 PRINCIPAL  \n",
       "3                            \n",
       "4                            \n",
       "\n",
       "[5 rows x 67 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Look at the data\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analysis: Using Python and SQL\n",
    "\n",
    "__What are the characteristics of inmates/offenders in North Carolina?__\n",
    "\n",
    "Before we go any further, let's take a look at some of the data that we're working with."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__North Carolina Department of Corrections Data__:\n",
    "- `inmate`: Characteristics about each inmate.\n",
    "- `offender`: Characteristics about each offender.\n",
    "- `sentences`: Transactional-level data about sentences.\n",
    "\n",
    "Note that each row in the both the `inmate` and `offender` tables represents one person. However, the same person can have multiple sentences. Information about each person is included in the `inmate` and `offender` tables, separate from the actual sentences, because we don't want to have to repeat the information about about each person multiple times, as would be the case if we had just one large table with all of the relevant information. \n",
    "\n",
    "Let's bring in a subset of the offender data to explore it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = '''\n",
    "SELECT *\n",
    "FROM offender\n",
    "limit 100;\n",
    "'''\n",
    "offender = pd.read_sql(query, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>OFFENDER_NC_DOC_ID_NUMBER</th>\n",
       "      <th>OFFENDER_BIRTH_DATE</th>\n",
       "      <th>OFFENDER_GENDER_CODE</th>\n",
       "      <th>OFFENDER_RACE_CODE</th>\n",
       "      <th>OFFENDER_HEIGHT_(IN_INCHES)</th>\n",
       "      <th>OFFENDER_WEIGHT_(IN_LBS)</th>\n",
       "      <th>OFFENDER_SKIN_COMPLEXION_CODE</th>\n",
       "      <th>OFFENDER_HAIR_COLOR_CODE</th>\n",
       "      <th>OFFENDER_EYE_COLOR_CODE</th>\n",
       "      <th>OFFENDER_BODY_BUILD_CODE</th>\n",
       "      <th>...</th>\n",
       "      <th>OFFENDER_ETHNIC_CODE</th>\n",
       "      <th>OFFENDER_PRIMARY_LANGUAGE_CODE</th>\n",
       "      <th>OFFENDER_SHIRT_SIZE</th>\n",
       "      <th>OFFENDER_PANTS_SIZE</th>\n",
       "      <th>OFFENDER_JACKET_SIZE</th>\n",
       "      <th>OFFENDER_SHOE_SIZE</th>\n",
       "      <th>OFFENDER_DRESS_SIZE</th>\n",
       "      <th>NEXT_PHOTO_YEAR</th>\n",
       "      <th>DATE_OF_LAST_UPDATE</th>\n",
       "      <th>TIME_OF_LAST_UPDATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000001</td>\n",
       "      <td>1974-04-04</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>66</td>\n",
       "      <td>180</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>...</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>ENGLISH</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>0000</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>2015-02-04</td>\n",
       "      <td>13:32:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000003</td>\n",
       "      <td>1955-07-24</td>\n",
       "      <td>MALE</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>74</td>\n",
       "      <td>240</td>\n",
       "      <td>LIGHT</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>STOCKY</td>\n",
       "      <td>...</td>\n",
       "      <td>EUROPEAN/N.AM./AUSTR</td>\n",
       "      <td>ENGLISH</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>0000</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>2015-05-05</td>\n",
       "      <td>17:20:06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000004</td>\n",
       "      <td>1961-10-15</td>\n",
       "      <td>MALE</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>70</td>\n",
       "      <td>150</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>GREEN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>...</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>ENGLISH</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>0000</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>1995-06-25</td>\n",
       "      <td>00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000005</td>\n",
       "      <td>1972-01-22</td>\n",
       "      <td>MALE</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>70</td>\n",
       "      <td>145</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>BLONDE</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>...</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>ENGLISH</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>0000</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>2001-12-20</td>\n",
       "      <td>13:36:13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000006</td>\n",
       "      <td>1951-07-17</td>\n",
       "      <td>MALE</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>69</td>\n",
       "      <td>150</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>...</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>ENGLISH</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>0000</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>1995-06-25</td>\n",
       "      <td>00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  OFFENDER_NC_DOC_ID_NUMBER OFFENDER_BIRTH_DATE OFFENDER_GENDER_CODE  \\\n",
       "0                   0000001          1974-04-04               FEMALE   \n",
       "1                   0000003          1955-07-24                 MALE   \n",
       "2                   0000004          1961-10-15                 MALE   \n",
       "3                   0000005          1972-01-22                 MALE   \n",
       "4                   0000006          1951-07-17                 MALE   \n",
       "\n",
       "  OFFENDER_RACE_CODE OFFENDER_HEIGHT_(IN_INCHES) OFFENDER_WEIGHT_(IN_LBS)  \\\n",
       "0              BLACK                          66                      180   \n",
       "1              WHITE                          74                      240   \n",
       "2              WHITE                          70                      150   \n",
       "3              WHITE                          70                      145   \n",
       "4              WHITE                          69                      150   \n",
       "\n",
       "  OFFENDER_SKIN_COMPLEXION_CODE OFFENDER_HAIR_COLOR_CODE  \\\n",
       "0                       UNKNOWN                    BLACK   \n",
       "1                         LIGHT                    BROWN   \n",
       "2                       UNKNOWN                    BROWN   \n",
       "3                       UNKNOWN                   BLONDE   \n",
       "4                       UNKNOWN                    BROWN   \n",
       "\n",
       "  OFFENDER_EYE_COLOR_CODE OFFENDER_BODY_BUILD_CODE  ...  OFFENDER_ETHNIC_CODE  \\\n",
       "0                   BROWN                  UNKNOWN  ...               UNKNOWN   \n",
       "1                    BLUE                   STOCKY  ...  EUROPEAN/N.AM./AUSTR   \n",
       "2                   GREEN                  UNKNOWN  ...               UNKNOWN   \n",
       "3                   BROWN                  UNKNOWN  ...               UNKNOWN   \n",
       "4                    BLUE                  UNKNOWN  ...               UNKNOWN   \n",
       "\n",
       "  OFFENDER_PRIMARY_LANGUAGE_CODE OFFENDER_SHIRT_SIZE OFFENDER_PANTS_SIZE  \\\n",
       "0                        ENGLISH                  00                  00   \n",
       "1                        ENGLISH                  00                  00   \n",
       "2                        ENGLISH                  00                  00   \n",
       "3                        ENGLISH                  00                  00   \n",
       "4                        ENGLISH                  00                  00   \n",
       "\n",
       "  OFFENDER_JACKET_SIZE OFFENDER_SHOE_SIZE OFFENDER_DRESS_SIZE NEXT_PHOTO_YEAR  \\\n",
       "0                   00               0000                  00              00   \n",
       "1                   00               0000                  00              00   \n",
       "2                   00               0000                  00              00   \n",
       "3                   00               0000                  00              00   \n",
       "4                   00               0000                  00              00   \n",
       "\n",
       "  DATE_OF_LAST_UPDATE TIME_OF_LAST_UPDATE  \n",
       "0          2015-02-04            13:32:12  \n",
       "1          2015-05-05            17:20:06  \n",
       "2          1995-06-25            00:00:00  \n",
       "3          2001-12-20            13:36:13  \n",
       "4          1995-06-25            00:00:00  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "offender.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we use the `head()` method to look at the top few rows of the offender data. As you can see, we have lots of information about the person, such as date of birth, gender, height, weight, hair color and so on. Let's see all of the types of variables that we have in this table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['OFFENDER_NC_DOC_ID_NUMBER', 'OFFENDER_BIRTH_DATE',\n",
       "       'OFFENDER_GENDER_CODE', 'OFFENDER_RACE_CODE',\n",
       "       'OFFENDER_HEIGHT_(IN_INCHES)', 'OFFENDER_WEIGHT_(IN_LBS)',\n",
       "       'OFFENDER_SKIN_COMPLEXION_CODE', 'OFFENDER_HAIR_COLOR_CODE',\n",
       "       'OFFENDER_EYE_COLOR_CODE', 'OFFENDER_BODY_BUILD_CODE',\n",
       "       'CITY_WHERE_OFFENDER_BORN', 'NC_COUNTY_WHERE_OFFENDER_BORN',\n",
       "       'STATE_WHERE_OFFENDER_BORN', 'COUNTRY_WHERE_OFFENDER_BORN',\n",
       "       'OFFENDER_CITIZENSHIP_CODE', 'OFFENDER_ETHNIC_CODE',\n",
       "       'OFFENDER_PRIMARY_LANGUAGE_CODE', 'OFFENDER_SHIRT_SIZE',\n",
       "       'OFFENDER_PANTS_SIZE', 'OFFENDER_JACKET_SIZE', 'OFFENDER_SHOE_SIZE',\n",
       "       'OFFENDER_DRESS_SIZE', 'NEXT_PHOTO_YEAR', 'DATE_OF_LAST_UPDATE',\n",
       "       'TIME_OF_LAST_UPDATE'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "offender.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identifying Missing Values\n",
    "\n",
    "We might be concerned about missing values in our data. Let's take a look at some inmate data to show an example of how we might find them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = '''\n",
    "SELECT *\n",
    "FROM inmate\n",
    "limit 10000;\n",
    "'''\n",
    "inmate = pd.read_sql(query, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INMATE_DOC_NUMBER</th>\n",
       "      <th>INMATE_LAST_NAME</th>\n",
       "      <th>INMATE_FIRST_NAME</th>\n",
       "      <th>INMATE_MIDDLE_INITIAL</th>\n",
       "      <th>INMATE_NAME_SUFFIX</th>\n",
       "      <th>INMATE_NAME_SOUNDEX_CODE</th>\n",
       "      <th>INMATE_GENDER_CODE</th>\n",
       "      <th>INMATE_RACE_CODE</th>\n",
       "      <th>INMATE_BIRTH_DATE</th>\n",
       "      <th>INMATE_ETHNIC_AFFILIATION</th>\n",
       "      <th>...</th>\n",
       "      <th>CURRENT_PENDING_REVIEWS_FLAG</th>\n",
       "      <th>ESCAPE_HISTORY_FLAG</th>\n",
       "      <th>PRIOR_INCARCERATIONS_FLAG</th>\n",
       "      <th>NEXT_PAROLE_REVIEW_TYPE_CODE</th>\n",
       "      <th>TIME_OF_LAST_MOVEMENT</th>\n",
       "      <th>POPULATION/MANAGEMENT_UNIT</th>\n",
       "      <th>INMATE_POSITIVELY_IDENTIFIED</th>\n",
       "      <th>PAROLE_AND_TERMINATE_STATUS</th>\n",
       "      <th>INMATE_LABEL_STATUS_CODE</th>\n",
       "      <th>PRIMARY_OFFENSE_QUALIFIER</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000004</td>\n",
       "      <td>AARON</td>\n",
       "      <td>DAVID</td>\n",
       "      <td>C</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>MALE</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>1961-10-15</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>00:09:00</td>\n",
       "      <td></td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000006</td>\n",
       "      <td>AARON</td>\n",
       "      <td>GERALD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>MALE</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>1951-07-17</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>00:11:00</td>\n",
       "      <td></td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000008</td>\n",
       "      <td>AARON</td>\n",
       "      <td>JAMES</td>\n",
       "      <td>M</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>MALE</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>1963-12-29</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>23:59:00</td>\n",
       "      <td></td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>FILE JACKET LABEL PRINTED</td>\n",
       "      <td>PRINCIPAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000010</td>\n",
       "      <td>AARON</td>\n",
       "      <td>KENNETH</td>\n",
       "      <td>T</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>MALE</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>1953-05-18</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>00:13:00</td>\n",
       "      <td></td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000014</td>\n",
       "      <td>AARON</td>\n",
       "      <td>MOYER</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>MALE</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>1921-08-26</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>00:12:00</td>\n",
       "      <td></td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 67 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  INMATE_DOC_NUMBER INMATE_LAST_NAME INMATE_FIRST_NAME INMATE_MIDDLE_INITIAL  \\\n",
       "0           0000004            AARON             DAVID                     C   \n",
       "1           0000006            AARON            GERALD                         \n",
       "2           0000008            AARON             JAMES                     M   \n",
       "3           0000010            AARON           KENNETH                     T   \n",
       "4           0000014            AARON             MOYER                         \n",
       "\n",
       "  INMATE_NAME_SUFFIX INMATE_NAME_SOUNDEX_CODE INMATE_GENDER_CODE  \\\n",
       "0                                                           MALE   \n",
       "1                                                           MALE   \n",
       "2                                                           MALE   \n",
       "3                                                           MALE   \n",
       "4                                                           MALE   \n",
       "\n",
       "  INMATE_RACE_CODE INMATE_BIRTH_DATE INMATE_ETHNIC_AFFILIATION  ...  \\\n",
       "0            WHITE        1961-10-15                   UNKNOWN  ...   \n",
       "1            WHITE        1951-07-17                   UNKNOWN  ...   \n",
       "2            WHITE        1963-12-29                   UNKNOWN  ...   \n",
       "3            BLACK        1953-05-18                   UNKNOWN  ...   \n",
       "4            WHITE        1921-08-26                   UNKNOWN  ...   \n",
       "\n",
       "  CURRENT_PENDING_REVIEWS_FLAG ESCAPE_HISTORY_FLAG PRIOR_INCARCERATIONS_FLAG  \\\n",
       "0                            N                   N                         Y   \n",
       "1                            N                   N                         Y   \n",
       "2                            N                   N                         Y   \n",
       "3                            N                   N                         Y   \n",
       "4                            N                   N                         Y   \n",
       "\n",
       "  NEXT_PAROLE_REVIEW_TYPE_CODE TIME_OF_LAST_MOVEMENT  \\\n",
       "0                                           00:09:00   \n",
       "1                                           00:11:00   \n",
       "2                                           23:59:00   \n",
       "3                                           00:13:00   \n",
       "4                                           00:12:00   \n",
       "\n",
       "  POPULATION/MANAGEMENT_UNIT INMATE_POSITIVELY_IDENTIFIED  \\\n",
       "0                                                     YES   \n",
       "1                                                     YES   \n",
       "2                                                     YES   \n",
       "3                                                     YES   \n",
       "4                                                     YES   \n",
       "\n",
       "  PAROLE_AND_TERMINATE_STATUS   INMATE_LABEL_STATUS_CODE  \\\n",
       "0                                                          \n",
       "1                                                          \n",
       "2                              FILE JACKET LABEL PRINTED   \n",
       "3                                                          \n",
       "4                                                          \n",
       "\n",
       "  PRIMARY_OFFENSE_QUALIFIER  \n",
       "0                            \n",
       "1                            \n",
       "2                 PRINCIPAL  \n",
       "3                            \n",
       "4                            \n",
       "\n",
       "[5 rows x 67 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inmate.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some values seem to be missing. We don't really care as much about a missing middle initial, but we might be concerned that the inmate's race might be missing. Let's see if we can identify if there are any missing in that variable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "BLACK         5154\n",
       "WHITE         4619\n",
       "OTHER          154\n",
       "INDIAN          64\n",
       "ASIAN/ORTL       8\n",
       "                 1\n",
       "Name: INMATE_RACE_CODE, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Missing values\n",
    "inmate['INMATE_RACE_CODE'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It looks like there's one missing value out of the 10,000 sample that we took from the inmate table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also, some offenders are missing the NC County where they were born. Let's see how many."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "OTHER          41\n",
       "FORSYTH         9\n",
       "                5\n",
       "GUILFORD        4\n",
       "BURKE           4\n",
       "WAKE            3\n",
       "NEW HANOVER     3\n",
       "CATAWBA         3\n",
       "VANCE           3\n",
       "WILKES          2\n",
       "WARREN          2\n",
       "PERSON          2\n",
       "FRANKLIN        1\n",
       "SAMPSON         1\n",
       "MECKLENBURG     1\n",
       "ROWAN           1\n",
       "ROBESON         1\n",
       "PITT            1\n",
       "CUMBERLAND      1\n",
       "GASTON          1\n",
       "STOKES          1\n",
       "RUTHERFORD      1\n",
       "SURRY           1\n",
       "GRANVILLE       1\n",
       "ONSLOW          1\n",
       "HENDERSON       1\n",
       "CASWELL         1\n",
       "JOHNSTON        1\n",
       "CARTERET        1\n",
       "CRAVEN          1\n",
       "ROCKINGHAM      1\n",
       "Name: NC_COUNTY_WHERE_OFFENDER_BORN, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "offender['NC_COUNTY_WHERE_OFFENDER_BORN'].value_counts() # some are missing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is just for the sample. What about for the whole dataset?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(distinct OFFENDER_NC_DOC_ID_NUMBER)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>314241</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count(distinct OFFENDER_NC_DOC_ID_NUMBER)\n",
       "0                                     314241"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#generating read SQL\n",
    "query = '''\n",
    "SELECT count(distinct OFFENDER_NC_DOC_ID_NUMBER)\n",
    "FROM offender\n",
    "WHERE NC_COUNTY_WHERE_OFFENDER_BORN IS \"\"\n",
    "'''\n",
    "# read the query into a DataFrame\n",
    "missing_county = pd.read_sql(query, conn)\n",
    "# print the resulting DataFrame\n",
    "missing_county"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For reference, we can also find he total number of people. We count both the distinct inmate DOC numbers as well as the number of rows just to make sure that we don't have any duplicates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(distinct INMATE_DOC_NUMBER)</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>461421</td>\n",
       "      <td>461421</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count(distinct INMATE_DOC_NUMBER)  count(*)\n",
       "0                             461421    461421"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#generating read SQL\n",
    "query = '''\n",
    "SELECT count(distinct INMATE_DOC_NUMBER), count(*)\n",
    "FROM inmate\n",
    "'''\n",
    "# read the query into a DataFrame\n",
    "unique_offender = pd.read_sql(query, conn)\n",
    "# print the resulting DataFrame\n",
    "unique_offender"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Date Variables\n",
    "\n",
    "SQL and Python have specific ways of dealing with date variables so that we can use them in intuitive ways. For example, we can extract out the year from a date and use that separately from the date itself. For example, suppose we want to get everyone who was an inmate during the 1980s."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's look at every inmate in the 1980s\n",
    "\n",
    "# set the SQL query\n",
    "query =\"\"\"\n",
    "SELECT *, CAST(strftime(\"%Y\",ACTUAL_SENTENCE_END_DATE) as integer) as release_year\n",
    "FROM sentences\n",
    "WHERE release_year >= 1980 AND release_year < 1990\n",
    "\"\"\"\n",
    "\n",
    "# print the query for reference\n",
    "print(query)\n",
    "\n",
    "# read the query \n",
    "\n",
    "in80 = pd.read_sql(query, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check first few rows to verify it's doing what we want\n",
    "in80.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary Statistics\n",
    "\n",
    "In this section, we look at aggregate statistics on the data. We'll start by looking at the sentences dataset. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = \"\"\"\n",
    "SELECT *\n",
    "FROM sentences\n",
    "\"\"\"\n",
    "# print results\n",
    "sentences = pd.read_sql(qry,conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INMATE_DOC_NUMBER</th>\n",
       "      <th>INMATE_COMMITMENT_PREFIX</th>\n",
       "      <th>INMATE_SENTENCE_COMPONENT</th>\n",
       "      <th>INMATE_COMPUTATION_STATUS_FLAG</th>\n",
       "      <th>SENTENCE_BEGIN_DATE_(FOR_MAX)</th>\n",
       "      <th>ACTUAL_SENTENCE_END_DATE</th>\n",
       "      <th>PROJECTED_RELEASE_DATE_(PRD)</th>\n",
       "      <th>PAROLE_DISCHARGE_DATE</th>\n",
       "      <th>PAROLE_SUPERVISION_BEGIN_DATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000004</td>\n",
       "      <td>AA</td>\n",
       "      <td>001</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>1983-07-12</td>\n",
       "      <td>1984-07-11</td>\n",
       "      <td>1984-07-11</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>0001-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000004</td>\n",
       "      <td>AA</td>\n",
       "      <td>002</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>1984-07-11</td>\n",
       "      <td>1984-07-11</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>0001-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000006</td>\n",
       "      <td>AA</td>\n",
       "      <td>001</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>1973-01-30</td>\n",
       "      <td>1973-03-28</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>0001-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000006</td>\n",
       "      <td>AB</td>\n",
       "      <td>001</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>1973-04-11</td>\n",
       "      <td>1975-08-18</td>\n",
       "      <td>1974-08-10</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>0001-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000006</td>\n",
       "      <td>AB</td>\n",
       "      <td>002</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>1973-04-24</td>\n",
       "      <td>1975-08-18</td>\n",
       "      <td>1974-08-10</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>0001-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  INMATE_DOC_NUMBER INMATE_COMMITMENT_PREFIX INMATE_SENTENCE_COMPONENT  \\\n",
       "0           0000004                       AA                       001   \n",
       "1           0000004                       AA                       002   \n",
       "2           0000006                       AA                       001   \n",
       "3           0000006                       AB                       001   \n",
       "4           0000006                       AB                       002   \n",
       "\n",
       "  INMATE_COMPUTATION_STATUS_FLAG SENTENCE_BEGIN_DATE_(FOR_MAX)  \\\n",
       "0                        EXPIRED                    1983-07-12   \n",
       "1                        EXPIRED                    0001-01-01   \n",
       "2                        EXPIRED                    1973-01-30   \n",
       "3                        EXPIRED                    1973-04-11   \n",
       "4                        EXPIRED                    1973-04-24   \n",
       "\n",
       "  ACTUAL_SENTENCE_END_DATE PROJECTED_RELEASE_DATE_(PRD) PAROLE_DISCHARGE_DATE  \\\n",
       "0               1984-07-11                   1984-07-11            0001-01-01   \n",
       "1               1984-07-11                   1984-07-11            0001-01-01   \n",
       "2               1973-03-28                   0001-01-01            0001-01-01   \n",
       "3               1975-08-18                   1974-08-10            0001-01-01   \n",
       "4               1975-08-18                   1974-08-10            0001-01-01   \n",
       "\n",
       "  PAROLE_SUPERVISION_BEGIN_DATE  \n",
       "0                    0001-01-01  \n",
       "1                    0001-01-01  \n",
       "2                    0001-01-01  \n",
       "3                    0001-01-01  \n",
       "4                    0001-01-01  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sentences.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note the `INMATE_SENTENCE_COMPONENT` column. This shows that the there might be multiple rows for multi-part sentences, and the sentence end date is the same for each of these separate sentences. Since we want to make sure they are treated as one whole sentence, we can simply take the first component of each sentence (since we are only interested in the sentence end date for right now). We'll make sure to do this for all future queries.\n",
    "\n",
    "Let's look at how many sentences ended in the 1980s."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's get this in a data frame to explore further\n",
    "\n",
    "qry = \"\"\"\n",
    "SELECT *, CAST(strftime(\"%Y\",ACTUAL_SENTENCE_END_DATE) as integer) as release_year\n",
    "FROM sentences\n",
    "WHERE release_year >= 1980 AND release_year < 1990\n",
    "AND INMATE_SENTENCE_COMPONENT == '001'\n",
    "\"\"\"\n",
    "# \n",
    "df = pd.read_sql(qry, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INMATE_DOC_NUMBER</th>\n",
       "      <th>INMATE_COMMITMENT_PREFIX</th>\n",
       "      <th>INMATE_SENTENCE_COMPONENT</th>\n",
       "      <th>INMATE_COMPUTATION_STATUS_FLAG</th>\n",
       "      <th>SENTENCE_BEGIN_DATE_(FOR_MAX)</th>\n",
       "      <th>ACTUAL_SENTENCE_END_DATE</th>\n",
       "      <th>PROJECTED_RELEASE_DATE_(PRD)</th>\n",
       "      <th>PAROLE_DISCHARGE_DATE</th>\n",
       "      <th>PAROLE_SUPERVISION_BEGIN_DATE</th>\n",
       "      <th>release_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000004</td>\n",
       "      <td>AA</td>\n",
       "      <td>001</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>1983-07-12</td>\n",
       "      <td>1984-07-11</td>\n",
       "      <td>1984-07-11</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>1984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000010</td>\n",
       "      <td>AB</td>\n",
       "      <td>001</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>1975-06-11</td>\n",
       "      <td>1983-06-27</td>\n",
       "      <td>1987-01-31</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>1983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000026</td>\n",
       "      <td>AA</td>\n",
       "      <td>001</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>1983-07-18</td>\n",
       "      <td>1984-09-09</td>\n",
       "      <td>1984-09-09</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>1984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000031</td>\n",
       "      <td>AA</td>\n",
       "      <td>001</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>1971-08-13</td>\n",
       "      <td>1981-11-02</td>\n",
       "      <td>1992-02-25</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>1981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000033</td>\n",
       "      <td>AB</td>\n",
       "      <td>001</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>1979-11-06</td>\n",
       "      <td>1980-02-04</td>\n",
       "      <td>1980-02-04</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>1980</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  INMATE_DOC_NUMBER INMATE_COMMITMENT_PREFIX INMATE_SENTENCE_COMPONENT  \\\n",
       "0           0000004                       AA                       001   \n",
       "1           0000010                       AB                       001   \n",
       "2           0000026                       AA                       001   \n",
       "3           0000031                       AA                       001   \n",
       "4           0000033                       AB                       001   \n",
       "\n",
       "  INMATE_COMPUTATION_STATUS_FLAG SENTENCE_BEGIN_DATE_(FOR_MAX)  \\\n",
       "0                        EXPIRED                    1983-07-12   \n",
       "1                        EXPIRED                    1975-06-11   \n",
       "2                        EXPIRED                    1983-07-18   \n",
       "3                        EXPIRED                    1971-08-13   \n",
       "4                        EXPIRED                    1979-11-06   \n",
       "\n",
       "  ACTUAL_SENTENCE_END_DATE PROJECTED_RELEASE_DATE_(PRD) PAROLE_DISCHARGE_DATE  \\\n",
       "0               1984-07-11                   1984-07-11            0001-01-01   \n",
       "1               1983-06-27                   1987-01-31            0001-01-01   \n",
       "2               1984-09-09                   1984-09-09            0001-01-01   \n",
       "3               1981-11-02                   1992-02-25            0001-01-01   \n",
       "4               1980-02-04                   1980-02-04            0001-01-01   \n",
       "\n",
       "  PAROLE_SUPERVISION_BEGIN_DATE  release_year  \n",
       "0                    0001-01-01          1984  \n",
       "1                    0001-01-01          1983  \n",
       "2                    0001-01-01          1984  \n",
       "3                    0001-01-01          1981  \n",
       "4                    0001-01-01          1980  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1416700"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INMATE_DOC_NUMBER</th>\n",
       "      <th>INMATE_COMMITMENT_PREFIX</th>\n",
       "      <th>INMATE_SENTENCE_COMPONENT</th>\n",
       "      <th>INMATE_COMPUTATION_STATUS_FLAG</th>\n",
       "      <th>SENTENCE_BEGIN_DATE_(FOR_MAX)</th>\n",
       "      <th>ACTUAL_SENTENCE_END_DATE</th>\n",
       "      <th>PROJECTED_RELEASE_DATE_(PRD)</th>\n",
       "      <th>PAROLE_DISCHARGE_DATE</th>\n",
       "      <th>PAROLE_SUPERVISION_BEGIN_DATE</th>\n",
       "      <th>release_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>141670</td>\n",
       "      <td>141670</td>\n",
       "      <td>141670</td>\n",
       "      <td>141670</td>\n",
       "      <td>141670</td>\n",
       "      <td>141670</td>\n",
       "      <td>141670</td>\n",
       "      <td>141670</td>\n",
       "      <td>141670</td>\n",
       "      <td>141670.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>99619</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>5698</td>\n",
       "      <td>3648</td>\n",
       "      <td>5016</td>\n",
       "      <td>173</td>\n",
       "      <td>169</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>0169217</td>\n",
       "      <td>AA</td>\n",
       "      <td>001</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>1982-12-15</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>16</td>\n",
       "      <td>83320</td>\n",
       "      <td>141670</td>\n",
       "      <td>141633</td>\n",
       "      <td>7177</td>\n",
       "      <td>521</td>\n",
       "      <td>8993</td>\n",
       "      <td>141491</td>\n",
       "      <td>141492</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1984.887061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.819820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1980.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1983.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1985.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1987.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1989.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       INMATE_DOC_NUMBER INMATE_COMMITMENT_PREFIX INMATE_SENTENCE_COMPONENT  \\\n",
       "count             141670                   141670                    141670   \n",
       "unique             99619                       18                         1   \n",
       "top              0169217                       AA                       001   \n",
       "freq                  16                    83320                    141670   \n",
       "mean                 NaN                      NaN                       NaN   \n",
       "std                  NaN                      NaN                       NaN   \n",
       "min                  NaN                      NaN                       NaN   \n",
       "25%                  NaN                      NaN                       NaN   \n",
       "50%                  NaN                      NaN                       NaN   \n",
       "75%                  NaN                      NaN                       NaN   \n",
       "max                  NaN                      NaN                       NaN   \n",
       "\n",
       "       INMATE_COMPUTATION_STATUS_FLAG SENTENCE_BEGIN_DATE_(FOR_MAX)  \\\n",
       "count                          141670                        141670   \n",
       "unique                              3                          5698   \n",
       "top                           EXPIRED                    0001-01-01   \n",
       "freq                           141633                          7177   \n",
       "mean                              NaN                           NaN   \n",
       "std                               NaN                           NaN   \n",
       "min                               NaN                           NaN   \n",
       "25%                               NaN                           NaN   \n",
       "50%                               NaN                           NaN   \n",
       "75%                               NaN                           NaN   \n",
       "max                               NaN                           NaN   \n",
       "\n",
       "       ACTUAL_SENTENCE_END_DATE PROJECTED_RELEASE_DATE_(PRD)  \\\n",
       "count                    141670                       141670   \n",
       "unique                     3648                         5016   \n",
       "top                  1982-12-15                   0001-01-01   \n",
       "freq                        521                         8993   \n",
       "mean                        NaN                          NaN   \n",
       "std                         NaN                          NaN   \n",
       "min                         NaN                          NaN   \n",
       "25%                         NaN                          NaN   \n",
       "50%                         NaN                          NaN   \n",
       "75%                         NaN                          NaN   \n",
       "max                         NaN                          NaN   \n",
       "\n",
       "       PAROLE_DISCHARGE_DATE PAROLE_SUPERVISION_BEGIN_DATE   release_year  \n",
       "count                 141670                        141670  141670.000000  \n",
       "unique                   173                           169            NaN  \n",
       "top               0001-01-01                    0001-01-01            NaN  \n",
       "freq                  141491                        141492            NaN  \n",
       "mean                     NaN                           NaN    1984.887061  \n",
       "std                      NaN                           NaN       2.819820  \n",
       "min                      NaN                           NaN    1980.000000  \n",
       "25%                      NaN                           NaN    1983.000000  \n",
       "50%                      NaN                           NaN    1985.000000  \n",
       "75%                      NaN                           NaN    1987.000000  \n",
       "max                      NaN                           NaN    1989.000000  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we can get descriptive stats from the DataFrame:\n",
    "df.describe(include='all')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's find out how many unique inmates there were within this time period."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "99619"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['INMATE_DOC_NUMBER'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There were almost 100,000 unique inmates and about 1.4 million sentences in the 1980s.\n",
    "\n",
    "Now, let's look at the characteristics of the sentences by year. First, let's look at how many sentences there were in each year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INMATE_DOC_NUMBER</th>\n",
       "      <th>INMATE_COMMITMENT_PREFIX</th>\n",
       "      <th>INMATE_SENTENCE_COMPONENT</th>\n",
       "      <th>INMATE_COMPUTATION_STATUS_FLAG</th>\n",
       "      <th>SENTENCE_BEGIN_DATE_(FOR_MAX)</th>\n",
       "      <th>ACTUAL_SENTENCE_END_DATE</th>\n",
       "      <th>PROJECTED_RELEASE_DATE_(PRD)</th>\n",
       "      <th>PAROLE_DISCHARGE_DATE</th>\n",
       "      <th>PAROLE_SUPERVISION_BEGIN_DATE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>release_year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1980</th>\n",
       "      <td>9954</td>\n",
       "      <td>9954</td>\n",
       "      <td>9954</td>\n",
       "      <td>9954</td>\n",
       "      <td>9954</td>\n",
       "      <td>9954</td>\n",
       "      <td>9954</td>\n",
       "      <td>9954</td>\n",
       "      <td>9954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1981</th>\n",
       "      <td>11408</td>\n",
       "      <td>11408</td>\n",
       "      <td>11408</td>\n",
       "      <td>11408</td>\n",
       "      <td>11408</td>\n",
       "      <td>11408</td>\n",
       "      <td>11408</td>\n",
       "      <td>11408</td>\n",
       "      <td>11408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1982</th>\n",
       "      <td>12749</td>\n",
       "      <td>12749</td>\n",
       "      <td>12749</td>\n",
       "      <td>12749</td>\n",
       "      <td>12749</td>\n",
       "      <td>12749</td>\n",
       "      <td>12749</td>\n",
       "      <td>12749</td>\n",
       "      <td>12749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1983</th>\n",
       "      <td>16198</td>\n",
       "      <td>16198</td>\n",
       "      <td>16198</td>\n",
       "      <td>16198</td>\n",
       "      <td>16198</td>\n",
       "      <td>16198</td>\n",
       "      <td>16198</td>\n",
       "      <td>16198</td>\n",
       "      <td>16198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1984</th>\n",
       "      <td>13586</td>\n",
       "      <td>13586</td>\n",
       "      <td>13586</td>\n",
       "      <td>13586</td>\n",
       "      <td>13586</td>\n",
       "      <td>13586</td>\n",
       "      <td>13586</td>\n",
       "      <td>13586</td>\n",
       "      <td>13586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1985</th>\n",
       "      <td>14383</td>\n",
       "      <td>14383</td>\n",
       "      <td>14383</td>\n",
       "      <td>14383</td>\n",
       "      <td>14383</td>\n",
       "      <td>14383</td>\n",
       "      <td>14383</td>\n",
       "      <td>14383</td>\n",
       "      <td>14383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1986</th>\n",
       "      <td>14920</td>\n",
       "      <td>14920</td>\n",
       "      <td>14920</td>\n",
       "      <td>14920</td>\n",
       "      <td>14920</td>\n",
       "      <td>14920</td>\n",
       "      <td>14920</td>\n",
       "      <td>14920</td>\n",
       "      <td>14920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1987</th>\n",
       "      <td>14771</td>\n",
       "      <td>14771</td>\n",
       "      <td>14771</td>\n",
       "      <td>14771</td>\n",
       "      <td>14771</td>\n",
       "      <td>14771</td>\n",
       "      <td>14771</td>\n",
       "      <td>14771</td>\n",
       "      <td>14771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1988</th>\n",
       "      <td>15635</td>\n",
       "      <td>15635</td>\n",
       "      <td>15635</td>\n",
       "      <td>15635</td>\n",
       "      <td>15635</td>\n",
       "      <td>15635</td>\n",
       "      <td>15635</td>\n",
       "      <td>15635</td>\n",
       "      <td>15635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1989</th>\n",
       "      <td>18066</td>\n",
       "      <td>18066</td>\n",
       "      <td>18066</td>\n",
       "      <td>18066</td>\n",
       "      <td>18066</td>\n",
       "      <td>18066</td>\n",
       "      <td>18066</td>\n",
       "      <td>18066</td>\n",
       "      <td>18066</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              INMATE_DOC_NUMBER  INMATE_COMMITMENT_PREFIX  \\\n",
       "release_year                                                \n",
       "1980                       9954                      9954   \n",
       "1981                      11408                     11408   \n",
       "1982                      12749                     12749   \n",
       "1983                      16198                     16198   \n",
       "1984                      13586                     13586   \n",
       "1985                      14383                     14383   \n",
       "1986                      14920                     14920   \n",
       "1987                      14771                     14771   \n",
       "1988                      15635                     15635   \n",
       "1989                      18066                     18066   \n",
       "\n",
       "              INMATE_SENTENCE_COMPONENT  INMATE_COMPUTATION_STATUS_FLAG  \\\n",
       "release_year                                                              \n",
       "1980                               9954                            9954   \n",
       "1981                              11408                           11408   \n",
       "1982                              12749                           12749   \n",
       "1983                              16198                           16198   \n",
       "1984                              13586                           13586   \n",
       "1985                              14383                           14383   \n",
       "1986                              14920                           14920   \n",
       "1987                              14771                           14771   \n",
       "1988                              15635                           15635   \n",
       "1989                              18066                           18066   \n",
       "\n",
       "              SENTENCE_BEGIN_DATE_(FOR_MAX)  ACTUAL_SENTENCE_END_DATE  \\\n",
       "release_year                                                            \n",
       "1980                                   9954                      9954   \n",
       "1981                                  11408                     11408   \n",
       "1982                                  12749                     12749   \n",
       "1983                                  16198                     16198   \n",
       "1984                                  13586                     13586   \n",
       "1985                                  14383                     14383   \n",
       "1986                                  14920                     14920   \n",
       "1987                                  14771                     14771   \n",
       "1988                                  15635                     15635   \n",
       "1989                                  18066                     18066   \n",
       "\n",
       "              PROJECTED_RELEASE_DATE_(PRD)  PAROLE_DISCHARGE_DATE  \\\n",
       "release_year                                                        \n",
       "1980                                  9954                   9954   \n",
       "1981                                 11408                  11408   \n",
       "1982                                 12749                  12749   \n",
       "1983                                 16198                  16198   \n",
       "1984                                 13586                  13586   \n",
       "1985                                 14383                  14383   \n",
       "1986                                 14920                  14920   \n",
       "1987                                 14771                  14771   \n",
       "1988                                 15635                  15635   \n",
       "1989                                 18066                  18066   \n",
       "\n",
       "              PAROLE_SUPERVISION_BEGIN_DATE  \n",
       "release_year                                 \n",
       "1980                                   9954  \n",
       "1981                                  11408  \n",
       "1982                                  12749  \n",
       "1983                                  16198  \n",
       "1984                                  13586  \n",
       "1985                                  14383  \n",
       "1986                                  14920  \n",
       "1987                                  14771  \n",
       "1988                                  15635  \n",
       "1989                                  18066  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sent_by_year = df.groupby('release_year')\n",
    "sent_by_year.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we first create an aggregated object using the `groupby` method. Then, we use it to perform certain calculations on each of the groups. Since we grouped by `release_year`, we are able to obtain various statistics of other variables within each year. \n",
    "\n",
    "Now, let's look at how many unique people there are within each year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INMATE_DOC_NUMBER</th>\n",
       "      <th>INMATE_COMMITMENT_PREFIX</th>\n",
       "      <th>INMATE_SENTENCE_COMPONENT</th>\n",
       "      <th>INMATE_COMPUTATION_STATUS_FLAG</th>\n",
       "      <th>SENTENCE_BEGIN_DATE_(FOR_MAX)</th>\n",
       "      <th>ACTUAL_SENTENCE_END_DATE</th>\n",
       "      <th>PROJECTED_RELEASE_DATE_(PRD)</th>\n",
       "      <th>PAROLE_DISCHARGE_DATE</th>\n",
       "      <th>PAROLE_SUPERVISION_BEGIN_DATE</th>\n",
       "      <th>release_year</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>release_year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1980</th>\n",
       "      <td>9752</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1911</td>\n",
       "      <td>362</td>\n",
       "      <td>1533</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1981</th>\n",
       "      <td>11121</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1951</td>\n",
       "      <td>364</td>\n",
       "      <td>1495</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1982</th>\n",
       "      <td>12432</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1973</td>\n",
       "      <td>365</td>\n",
       "      <td>1441</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1983</th>\n",
       "      <td>15799</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2034</td>\n",
       "      <td>365</td>\n",
       "      <td>1330</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1984</th>\n",
       "      <td>13309</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2023</td>\n",
       "      <td>366</td>\n",
       "      <td>1154</td>\n",
       "      <td>15</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1985</th>\n",
       "      <td>13956</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2022</td>\n",
       "      <td>365</td>\n",
       "      <td>1018</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1986</th>\n",
       "      <td>14360</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1923</td>\n",
       "      <td>365</td>\n",
       "      <td>835</td>\n",
       "      <td>14</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1987</th>\n",
       "      <td>14192</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1913</td>\n",
       "      <td>365</td>\n",
       "      <td>761</td>\n",
       "      <td>20</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1988</th>\n",
       "      <td>14973</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1947</td>\n",
       "      <td>366</td>\n",
       "      <td>820</td>\n",
       "      <td>26</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1989</th>\n",
       "      <td>17099</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1940</td>\n",
       "      <td>365</td>\n",
       "      <td>1049</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              INMATE_DOC_NUMBER  INMATE_COMMITMENT_PREFIX  \\\n",
       "release_year                                                \n",
       "1980                       9752                        13   \n",
       "1981                      11121                        14   \n",
       "1982                      12432                        15   \n",
       "1983                      15799                        15   \n",
       "1984                      13309                        15   \n",
       "1985                      13956                        16   \n",
       "1986                      14360                        16   \n",
       "1987                      14192                        16   \n",
       "1988                      14973                        15   \n",
       "1989                      17099                        18   \n",
       "\n",
       "              INMATE_SENTENCE_COMPONENT  INMATE_COMPUTATION_STATUS_FLAG  \\\n",
       "release_year                                                              \n",
       "1980                                  1                               2   \n",
       "1981                                  1                               2   \n",
       "1982                                  1                               2   \n",
       "1983                                  1                               2   \n",
       "1984                                  1                               2   \n",
       "1985                                  1                               2   \n",
       "1986                                  1                               3   \n",
       "1987                                  1                               3   \n",
       "1988                                  1                               2   \n",
       "1989                                  1                               3   \n",
       "\n",
       "              SENTENCE_BEGIN_DATE_(FOR_MAX)  ACTUAL_SENTENCE_END_DATE  \\\n",
       "release_year                                                            \n",
       "1980                                   1911                       362   \n",
       "1981                                   1951                       364   \n",
       "1982                                   1973                       365   \n",
       "1983                                   2034                       365   \n",
       "1984                                   2023                       366   \n",
       "1985                                   2022                       365   \n",
       "1986                                   1923                       365   \n",
       "1987                                   1913                       365   \n",
       "1988                                   1947                       366   \n",
       "1989                                   1940                       365   \n",
       "\n",
       "              PROJECTED_RELEASE_DATE_(PRD)  PAROLE_DISCHARGE_DATE  \\\n",
       "release_year                                                        \n",
       "1980                                  1533                      9   \n",
       "1981                                  1495                     10   \n",
       "1982                                  1441                      9   \n",
       "1983                                  1330                     15   \n",
       "1984                                  1154                     15   \n",
       "1985                                  1018                     15   \n",
       "1986                                   835                     14   \n",
       "1987                                   761                     20   \n",
       "1988                                   820                     26   \n",
       "1989                                  1049                     53   \n",
       "\n",
       "              PAROLE_SUPERVISION_BEGIN_DATE  release_year  \n",
       "release_year                                               \n",
       "1980                                      9             1  \n",
       "1981                                     10             1  \n",
       "1982                                      9             1  \n",
       "1983                                     15             1  \n",
       "1984                                     16             1  \n",
       "1985                                     15             1  \n",
       "1986                                     13             1  \n",
       "1987                                     19             1  \n",
       "1988                                     25             1  \n",
       "1989                                     53             1  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sent_by_year.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This suggests that there's an increasing trend of more and more sentences for more and more unique people over the years.\n",
    "\n",
    "Let's look at a graph of this. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7f0296782208>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAZAAAAEHCAYAAACA3BA3AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjAsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+17YcXAAAgAElEQVR4nO3dd3hUZfbA8e8htIQaSIJAKAECoRcjoFgQUcAC2AEVrLiKq667Kopt7a66lp9tXWHBRQF1VVApNhRRWkBK6KEEQksg9PTM+f0xNziEhCSTSWaSnM/zzMPk3HZmEubMve/73ldUFWOMMaakqvk7AWOMMRWTFRBjjDFesQJijDHGK1ZAjDHGeMUKiDHGGK9U93cC3goLC9PWrVv7Ow1jjKkwwsLCmDdv3jxVHeyL/VXYAtK6dWvi4uL8nYYxxlQoIhLmq33ZJSxjjDFesQJijDHGK1ZAjDHGeKXCtoEUJDs7m6SkJDIyMvydiqkkateuTWRkJDVq1PB3KsYEnEpVQJKSkqhXrx6tW7dGRPydjqngVJUDBw6QlJREVFSUv9MxJuBUqktYGRkZNG7c2IqH8QkRoXHjxnZGa0whKlUBAax4GJ+yvydjClepLmEZY4wpWGZOLh/8ss2n+yzyDEREJolIsojEe8RmiMhK57FdRFZ6LHtERBJEZKOIDPKID3ZiCSIy3iMeJSJLRGSzs9+avnyBxhhj4J35W3h53kaf7rM4l7AmAycNe1fV61W1h6r2AP4HfA4gIp2AEUBnZ5t3RCRIRIKAt4EhQCdgpLMuwEvAa6oaDRwEbiv1q/KjunXrArB9+3ZEhP/7v/87seyee+5h8uTJANx8882EhIRw9OjRE8vvu+8+RIT9+/efiH3xxReICBs2bABgzZo19OjRgx49etCoUSOioqLo0aMHAwcOZPv27QQHB59Y3qNHDz788MNCc23dujVdu3ala9eudOrUiccee4zMzMwTy9euXcuAAQNo37490dHRPPPMM3hOQDZnzhxiY2Pp2LEjMTEx/O1vfyv0WE899RQhISEkJycX+F516dLllPVfeeWVYr9XQUFB9OjRg+7du9OrVy9+++23E/su7D3Je/3dunXjggsuIDExsdD8janINuw9wtvzE7iyZ3Of7rfIAqKqC4DUgpaJ+wLxdcA0JzQMmK6qmaq6DUgAejuPBFXdqqpZwHRgmLP9AOAzZ/spwPBSvJ6AEhERwRtvvEFWVlaBy9u1a8fMmTMBcLlczJ8/n+bNT/4FT5s2jXPPPZfp06cD0LVrV1auXMnKlSsZOnQoL7/8MitXruT7778HoG3btieWr1y5ktGjR582x/nz57NmzRqWLl3K1q1bGTt2LADp6ekMHTqU8ePHs2nTJlatWsVvv/3GO++8A0B8fDz33HMPU6dOZf369cTHx9OmTZvTHissLIxXX321iHetYEW9V8HBwaxcuZJVq1bxwgsv8Mgjj5xYdrr3ZP78+axevZr+/fvz7LPPepWbMYEsJ9fFQ5+tpkFwDR6/vFPRG5RAadtAzgP2qepm5+fmwGKP5UlODGBnvngfoDFwSFVzClj/FCIyFhgL0LJly9Mm9vev1rJu95HivYpi6tSsPk9e0bnY64eHh9OvXz+mTJnCHXfcccrykSNHMmPGDG688UZ++ukn+vXrx5w5c04sP3bsGL/++ivz589n6NChPPXUU754GQWqW7cu7733Hi1atCA1NZUvvviCfv36cckllwAQEhLCW2+9Rf/+/Rk3bhz/+Mc/mDBhAjExMQBUr16du++++7THuPXWW5k8eTIPP/wwjRo1KlF+Rb1Xno4cOUJoaGiJ9n/22Wfz5ptvlmgbYyqCSb9uY3XSYd4a1ZNGdXzbQlDaXlgj+ePsA6CgLivqRbxAqvq+qsaqamx4eHiJEvWX8ePH8+qrr5Kbm3vKsujoaFJSUjh48CDTpk1jxIgRJy3/8ssvGTx4MO3bt6dRo0asWLGiyONt2bLlpMs1v/zyS7FzrV+/PlFRUWzevJm1a9dy5plnnrS8bdu2HDt2jCNHjhAfH3/K8qLUrVuXW2+9lTfeeKNE20HR71V6ejo9evQgJiaG22+/nccff/zEsuK8J3PnzmX48Epz8msMANv3H+fVbzdxcacmXNa1qc/37/UZiIhUB64CPD9FkoAWHj9HArud5wXF9wMNRaS6cxbiuX6plORMoSxFRUXRu3dvPv744wKXX3XVVUyfPp0lS5bwr3/966Rl06ZN4/777wdgxIgRTJs2jV69ep32eHmXa7yV18ahqoV2YS1N19Z7772XHj168Ne//rXI/eWPn+69yruEBbBo0SJGjx5NfLy738fp3pMLL7yQffv2ERERYZewTKXicikP/281NatX49nhXcqkS3ppzkAGAhtUNckjNgsYISK1RCQKiAaWAsuAaKfHVU3cDe2z1P1pNR+4xtl+DDCzFDkFpEcffZSXXnoJl8t1yrIRI0bw+OOPc/HFF1Ot2h+/jgMHDvDjjz9y++2307p1a15++WVmzJhxUiO2rx09epTt27fTvn17OnfufMrt8rdu3UrdunWpV68enTt3Zvny5SU+RsOGDRk1atSJthSAxo0bc/DgwZPWS01NJSzs5LtOF/Ze5Xf22Wezf/9+UlJSisxn/vz5JCYm0rlzZ5544okSvhpjAte0ZTtYsi2Vxy7rSJP6tcvkGMXpxjsNWAR0EJEkEcnrJTWCky9foaprgU+AdcBcYJyq5jpnF/cA84D1wCfOugAPAw+ISALuNpGJpX9ZgSUmJoZOnTrx9ddfn7KsZcuWPPfcc6e0H3z22WeMHj2axMREtm/fzs6dO4mKimLhwoVlkuOxY8e4++67GT58OKGhodxwww0sXLjwRON8eno69957Lw899BAADz74IM8//zybNm0C3A3b//znP4t1rAceeIB//etf5OS4m77q1q1L06ZN+eGHHwB38Zg7dy7nnnvuSdsV9l7lt2HDBnJzc2ncuHGx8gkODub111/nww8/JDW1wP4ixlQouw+l88LsDfRr15jrYlsUvYGXiryEpaojC4nfXEj8OeC5AuKzgdkFxLfi7qVVqU2YMIGePXsWuOzOO+88JTZt2jTGjx9/Uuzqq6/m448/5rzzziv0OHnX+/Pceuut3HvvvYWuf+GFF6KquFwurrzyyhNtB8HBwcycOZM///nPjBs3jtzcXG666SbuueceALp168brr7/OyJEjSUtLQ0S47LLLCn8DPISFhXHllVfy2muvnYh9+OGHjBs37sSlrSeffJK2bduesm1B7xX80QYC7stvU6ZMISgoqNjvSdOmTRk5ciRvv/32Se0nxlQ0qspjX8aT61JevKpbmd5NQcrykkhZio2N1fyXWNavX0/Hjh39lJGprOzvylQkX/6+i/tnrOSJyztx67mn3gRURJaraqwvjlXp7oVljDFV1f5jmfz9q7X0atmQMee0LvPj2b2wqoA+ffqcNMIc4L///S9du3b1+bGee+45Pv3005Ni1157LRMmTPD5sYwxJ3tq1lqOZ+by0tXdCKpW9jcCrXQF5HTdT6uqJUuWlNuxJkyYUKmKRUW9xGuqnm/X7uXr1Xv468XtiW5Sr1yOWakuYdWuXZsDBw7Yf3rjE3kTStWuXTZdII3xlcPp2Tz2ZTwxZ9TjT/1P7XxSVirVGUhkZCRJSUnF6v9vTHHkTWlrTCB7YfZ69h/LZOKYs6gRVH7nBZWqgNSoUcOmHjXGVCm/Juxn+rKd3HlBG7pGNijXY1eqS1jGGFOVpGXlMP7z1USF1eEvA9uX+/Er1RmIMcZUJa/M28TO1HRmjO1L7RpB5X58OwMxxpgKaMWOg/znt23c1LcVfdoU77Y9vmYFxBhjKpjMnFwe+mw1TevX5qHBHfyWh13CMsaYCubtHxNISD7Gf245i3q1a/gtDzsDMcaYCmTd7iO889MWrurZnAs7RPg1FysgxhhTQeTkunj4f6tpGOL7+c29YZewjDGmgvhg4TbW7DrM26N6Eerj+c29YWcgxhhTAWxNOcZr321iUOcmXNr1DH+nA1gBMcaYgOdyKeP/t4Za1avxzLCymd/cG1ZAjDEmwH20dAdLt6fy2OWdiCij+c29UZw50SeJSLKIxOeL/1lENorIWhH5h0f8ERFJcJYN8ogPdmIJIjLeIx4lIktEZLOIzBAR/1/YM8aYALHrUDovzl7PedFhXHtmYN3YszhnIJOBwZ4BEbkQGAZ0U9XOwCtOvBMwAujsbPOOiASJSBDwNjAE6ASMdNYFeAl4TVWjgYPAbaV9UcYYUxmoKhO+WIMCz1/ZNWAuXeUpsoCo6gIgNV/4LuBFVc101kl24sOA6aqaqarbgASgt/NIUNWtqpoFTAeGifvdGAB85mw/BRheytdkjDGVwhe/7+KnjSk8OKgDLRqF+DudU3jbBtIeOM+59PSziJzlxJsDOz3WS3JihcUbA4dUNSdfvEAiMlZE4kQkzub8MMZUZilHM3n663Wc2SqU0We39nc6BfK2gFQHQoG+wIPAJ87ZREHnV+pFvECq+r6qxqpqbHh4eMmzNsaYCuKpWWtJy8zlpau7lsv85t7wdiBhEvC5uueOXSoiLiDMibfwWC8S2O08Lyi+H2goItWdsxDP9Y0xpkqaG7+Xb9bs4cFBHWgXUT7zm3vD2zOQL3G3XSAi7YGauIvBLGCEiNQSkSggGlgKLAOinR5XNXE3tM9yCtB84Bpnv2OAmd6+GGOMqegOp2Xz+Mx4OjWtz9jz2/g7ndMqTjfeacAioIOIJInIbcAkoI3TtXc6MEbd1gKfAOuAucA4Vc11zi7uAeYB64FPnHUBHgYeEJEE3G0iE337Ek0gOXg8i+/X7fN3GsYErOdmryP1eBb/uKZbuc5v7g1xnwRUPLGxsRoXF+fvNEwJPfrFGj5esoOpt/Xh3Ogwf6djTED5ZXMKN01cyl392/Lw4JgyOYaILFfVWF/sK7DLm6lUjmZk8+XvuwB4bvZ6cl0V88uLMWXheGYOj3y+hjZhdbjvomh/p1MsVkBMufny912kZeVya78o1u85whdOMTHGwMvzNrLrUDovXdPNL/Obe8MKiCkXqsrUxTvo3Kw+j13Wke4tGvLKvI2kZ+X6OzVj/G55YipTFm1ndN9WnNW6kb/TKTYrIKZcxCUeZOO+o9zYtxXVqgkTLu3I3iMZTFy41d+pGeNXGdnu+c2bNQjmwTJq9ygrVkBMuZi6OJF6taozrEczAHpHNWJQ5ya8+9MWko9m+Dk7Y/znrR8T2JJynOev6krdWhVrjj8rIKbMHTiWyZw1e7n6zEhCav7xH+ThwTFk5rh4/fvNfszOGP9Zu/sw7/68hat7RXJB+4p3dw0rIKbMfRKXRFauixv6tDwp3ia8Ljf2bcX0pTvYvO+on7Izxj9ycl089NlqQkNq8vjlHf2djlesgJgyletSPlqSSJ+oRkQ3OfWWDPdeFE2dmtV5Yc4GP2RnjP+8/8tW1u4+wjPDOtMwpGJOg2QFxJSpBZtSSDqYzo19WxW4vFGdmowb0I4fNyTza8L+cs7OGP/YknKM17/fzJAuZzCka1N/p+M1KyCmTE1dnEhY3VoM6nxGoevcfE5rmjcM5rlv1uOywYWmknPPb76a4BpB/H1YZ3+nUypWQEyZSTqYxo8bk7n+rEhqVi/8T612jSAeGtyBdTa40FQBU5cksmz7QR6/vBMR9QJnfnNvWAExZWba0h0AjOzdsog14Ypuzege2YCXbXChqcSSDqbx0pwNnN8+nKt7FTp3XoVhBcSUiawcFzOW7eSimAgiQ4ueirNaNeFRG1xoKjFV5dEv4p35zbsE3Pzm3rACYsrEvLV72X8sixsKaTwvSJ82jbmkk3twYcrRzDLMzpjy978Vu1iwKYWHB8cU60tVRWAFxJSJqYsTadEomAuiSzY4avyQvMGFm8ooM2PKX/LRDJ75eh2xrUK5qQRfqgKdFRDjc5v2HWXJtlRG9Xbf96ok2oTX5YY+LZm+bKcNLjSVxpMz15KenctL13Qr8f+JQGYFxPjcR4sTqRlUjetiI73a/r6B7QmpEcSLNrjQVAJz1uxhTvxe7h8YTdvwuv5Ox6esgBifOp6Zw+crdjGk6xk0rlvLq33kDS78YUMyv9ngQlOBHUrL4vGZa+ncrD53nBfY85t7ozhzok8SkWRn/vO82FMisktEVjqPSz2WPSIiCSKyUUQGecQHO7EEERnvEY8SkSUisllEZohIxRzTbwCYtWo3RzNzCh15XlwnBhfOtsGFpuJ69pv1HEyrGPObe6M4r2gyMLiA+Guq2sN5zAYQkU7ACKCzs807IhIkIkHA28AQoBMw0lkX4CVnX9HAQeC20rwg4z/uSaMSiTmjHrGtQku1r7zBhWt32+BCUzH9vCmFz5Yn8acL2tC5WQN/p1MmiiwgqroASC3m/oYB01U1U1W3AQlAb+eRoKpbVTULmA4ME3dH6AHAZ872U4DhJXwNJkCs3HmItbuPcEPfVj7p435Ft2Z0i2zAK9/a4EJTsRzLzOHRz9fQNrwOfx5QMeY390ZpzqnuEZHVziWuvK+bzYGdHuskObHC4o2BQ6qaky9eIBEZKyJxIhKXkpJSitRNWZi6eAd1agZxZU/fjLDNG1y453AGk37d5pN9GlOWVJVv1+7liv9byO7D6bx0dcWZ39wb3haQd4G2QA9gD/CqEy/oa6d6ES+Qqr6vqrGqGhseXvEmX6nMDqVl8fXq3Qzv2dyns6r1bdOYizs14Z35CTa40AS0+F2HGfXvJYz973KCqglTbulNbAWa39wbXhUQVd2nqrmq6gL+jfsSFbjPIFp4rBoJ7D5NfD/QUESq54ubCuaz5Ulk5rhK3XhekEecwYVv/GCDC03gST6SwYOfruKKtxayYa97fo+5953H+RVwhsGS8uqroog0VdU9zo9XAnk9tGYBH4vIP4FmQDSwFPeZRrSIRAG7cDe0j1JVFZH5wDW420XGADO9fTHGP1wud+P5ma1C6di0vs/3nze4cOqSHdx8TmvaRZw6MZUx5S09K5d//7KV937eQnauizvOa8O4C9vRILiGv1MrN0UWEBGZBvQHwkQkCXgS6C8iPXBfbtoO3AmgqmtF5BNgHZADjFPVXGc/9wDzgCBgkqqudQ7xMDBdRJ4Ffgcm+uzVmXLx65b9bD+Qxn0Dy66x8N6Lovl8xS5emL2BiTefVWbHMaYoLpcya9VuXpq7gT2HMxjS5QzGD4mhVeM6/k6t3BVZQFR1ZAHhQj/kVfU54LkC4rOB2QXEt/LHJTBTAU1dnEhoSA2GdCm7mdUa163F3Re246W5G/gtYT/ntAsrs2MZU5i47ak88816Vu08RNfmDXj9+h70adPY32n5TeUb2WLK1d7DGXy/PpnrzmpR5r1NbulngwuNf+xMTWPcxyu45r1F7D2czqvXdmfmuH5VuniAl20gxuSZtnQHLlVu6F32dxitXSOIBwd14P4ZK/ly5S6u6uXdvbaMKa6jGdm8PX8LkxZuo1o1uO+iaO68oA0hNe2jE6yAmFLIznUxfdkOzo8Op2Xj8pnfYGj3Zkz6dRsvz9vIpV2bVuo+9sZ/cnJdzIjbyT+/3cSB41lc3SuSBwd14IwGFXsKWl+zS1jGaz+s38e+I5ll0nW3MJ6DCycutMGFxvcWbErhsjcXMuGLeNqG1+Wre87l1eu6W/EogJ2BGK9NXbyDZg1qMyAmolyPmze48N2ftnD9WS0I8/Kuv8Z4Skg+ynPfrGf+xhRaNgrhvRt7MajzGZVi6tmyYmcgxitbU46xMGE/I3u3JMgPE+SMHxJDenauzVxoSi31eBZPzIxn0Ou/ELf9II9eGsN3D5zP4C5NrXgUwc5AjFc+WrKD6tWE63u3KHrlMtDWGVz4kQ0uNF7KzMnlw98SefPHzaRl5TKqd0vuHxjt9Tw2VZGdgZgSy8jO5bPlSQzqfAYR9fx3Xfi+i6IJtpkLTQmpKnPj93DJawt4bvZ6zmwVytz7zuOZ4V2seJSQnYGYEvtq1W4Op2eXa+N5QdyDC9vyj7kb+W3Lfs5pa4MLzenF7zrMM1+vY8m2VNo3qcuUW3tzQRW4Z1VZsTMQU2JTl+ygXURd+rbx/51Gb+0XRfOGwTxvgwvNaew7ksFfP3Hf8DAh+RjPDu/C7HvPs+JRSnYGYkokftdhVu08xJNXdAqIBkbPwYUzV+3iyp42uLAsHE7L5ufNKfy8MYXMnFyahwYT2TCY5qHBNG8YQvPQYJ/ext9X0rJy+PeCbbz38xZyXcrY8903PKxfu+rc8LAsBd5v3AS0qYsTCa4RFFCjwId2b8bEhdt4ee5GhnSxwYW+oKps3HeUHzck89OGFJbvOEiuSwkNqUH94BrMW7uX7NyTz/gahtSgecNg9yPU/W+kR4EJDalRbl86XC7li9938fK8jew9ksFlXZvy8OCYchvwWlVYATHFdiQjm5krdzO0e7OAumV13uDCkf9ezMSF2xh3YTt/p1QhpWfl8tuW/e6isTGFXYfSAejcrD5392/LhTERdI9sSFA1weVSUo5lknQwnV2H0tl1MJ2kg2nsOpTOtv3HWZiwn7R80xCH1Aw6qbjkLzIR9WpRzQddwpduS+XZb9axOukw3SIb8H+jenJWJZ/YyV+sgJhi+3x5EunZuX5vPC/I2W0bM7CjDS4sqZ2pafy4IZkfNySzaOsBsnJc1KkZxLnRYdx7UTv6d4igSf1Te9pVqyY0qV+bJvVrc2ar0FOWqyqH0rLZdSj9RJFJOpjGLuf5yp2HOJSWfdI2NYOq0bRh7VPOYtyXy0Jo2rA2NYIKb7bdcSCNF+asZ078Xpo2qM1r13dnWPfmPilKpmBWQEyxqCpTl+yge2QDukY28Hc6BRo/JIZBry/gje8388zwLv5OJyBl57pYtj2V+U7R2JJyHIA2YXW4sU8rBsREcFZUKLWql+4yoIgQWqcmoXVq0qV5wX8vxzNz/jh7yXcW8/OmFJLzTWEsAmfUr13gWcyiLQf4z6/bCaomPHBxe+44rw3BNe1SZlmzAmKKZfHWVBKSj/HyNd38nUqh2kXUZVTvlny8dAdjzmlNu4i6/k4pICQfzeCnjSnM35DMws37OZqZQ82gavRp04gbnKLROqz8J0OqU6s67ZvUo32TggeBZubksudQxklFJu8sZnniQb5ZvYccp+edCCdueFjQGZMpG1ZATLFMXZJIg+AaXNG9mb9TOa37B0bzxe+7eHHOBj4YE+vvdPzC5VJW7zrM/A3JzN+YzOqkwwA0qV+Ly7s3pX+HCM5tF0adAOw15alW9SBah9UptLjlupR9R9wFJjSkpn1h8IPiTGk7CbgcSFbVLvmW/Q14GQhX1f3i7mLxBnApkAbcrKornHXHAI85mz6rqlOc+JnAZCAY94yF96mqdegPIMlHM5gXv5cx57QO+B5OnoMLF205wNltq8aEP0cysvllk7sB/OdNyew/loUI9GzRkL9d0p4LYyLo1LR+QHS99pWgakKzhsE0axjs71SqrOJ8BZkMvAV86BkUkRbAxcAOj/AQINp59AHeBfqISCPcc6nH4p5HfbmIzFLVg846Y4HFuAvIYGCO9y/J+Nony3aS41Ju6NPS36kUy639opi6KJHnZq9j1rhzK2UjqqqSkHzsRAP48sSD5LiUBsE1uKB9OANiIji/fTiN6tT0d6qmEivOnOgLRKR1AYteAx4CZnrEhgEfOmcQi0WkoYg0BfoD36lqKoCIfAcMFpGfgPqqusiJfwgMxwpIwMh1KdOW7qRfu8a0Ca8Ylwhq1wjiwcEd+MuMVZVqcGFGdi6LthzgR+fSVNJBdzfbmDPqccf5bRgQE0HPFg2pfpqeSsb4klcXQUVkKLBLVVflOyVuDuz0+DnJiZ0unlRA3ASI+RuS2XUonccu6+jvVEpkWPfmlWJwYdLBNKctI4XftuwnI9tFcI0g+rVrzF3923Jhhwi7hGP8psQFRERCgAnAJQUtLiCmXsQLO/ZY3Je7aNmyYlxOqeimLkkkol4tBnZq4u9USiRvcOGofy9h0q/buLt/xRlcmJ3rYtbK3UxcuI11e44A0LJRCCPOakn/DuH0bdO4whZEU7l4cwbSFogC8s4+IoEVItIb9xmE5wQRkcBuJ94/X/wnJx5ZwPoFUtX3gfcBYmNjraG9jO04kMbPm1L484Do0w7gClTntA1jYMcI3pm/hetjWwT8rbrTsnKYvnQnH/yyld2HM4g5ox4TLu3IhTERtA2vU6kawE3lUOJPBVVdo6oRqtpaVVvjLgK9VHUvMAsYLW59gcOqugeYB1wiIqEiEor77GWes+yoiPR1enCN5uQ2FeNHHy1NpJoII/00aZQvjB/SkfTsXN74YbO/UynUweNZvP79Jvq9+CNPf72OyNAQ/nPzWcy57zzuOL8N7SLqWvEwAak43Xin4T57CBORJOBJVZ1YyOqzcXfhTcDdjfcWAFVNFZFngGXOek/nNagDd/FHN945WAN6QMjMyeXTuCQGdoygaYOKe409b3DhR0vcgwvbBlBHgN2H0vngl21MW7qD9OxcBnZswl3923BmK7tvk6kYitMLa2QRy1t7PFdgXCHrTQImFRCPA+y+EwFmzpq9pB7PCsj7XpXUfc7gwhdmB8bgws37jvLez1uZuXIXAEN7NONPF7QtdES2MYEqsIeiGr+ZujiR1o1D6FcJZvkLq1uLu/q35eV5/h1cuGLHQd79aQvfrdtHcI0gbjq7Fbef14bm1ovKVFBWQMwpNuw9QlziQSZc2rHSDMK77dwopi5O5PnZ65k5rl+5vS5V5adNKbz70xaWbkulYUgN7rsomjHntLZBfqbCswJiTjF1cSI1q1fjmjMrxwA8+GPmwgc+WcWsVbsZ3rNshxvl5Lr4Zs0e3v1pCxv2HqVpg9o8cXknrj+rRcDfg8qY4rK/ZHOSY5k5fLFiF5d3a0poJfuGPLxHcyb9uo2X521kcJczymQsRUZ2Lp/G7eT9X7ayMzWddhF1eeXa7gzt3oya1SteV2hjTscKiDnJl7/v4nhWLjdVgsbz/DwHF/7n1+3c1b+tz/Z9OD2bqYsTmbRwGweOZ9GzZUMev6wTAzs2qZbpYEkAABnDSURBVDSXAY3JzwqIOUFVmbo4kc7N6tOjRUN/p1Mm/hhcmMB1sZGlHly470gGExdu4+MlOziWmUP/DuHcdUFbekc1srEbptKzAmJOWJ54kA17j/LCVV0r9Yefe+bCX3jjh808Pcy7HuRbU47x/oKtfL5iFzkuF5d3c3fF7dSsvo+zNSZwWQExJ0xdnEi9WtUZ1iOwJ40qrXYR9RjZu4VXgwtXJx3ivZ+3MCd+LzWDqnH9WS2447w2tGwcUoYZGxOYrIAYAA4cy2T2mr2M7N2CkJqV/8/i/oHt+fL33bw4ZwP/Hn36wYWqyq8JB3j35wR+TThAvdrVubt/W24+J4rweoF9fy1jylLl/6QwxfLp8iSycl3cUAkbzwviObhw8dYD9G1z6uDCXJcyN34v7/28hTW7DhNRrxaPXhrDyN4tqVe7hh+yNiawWAExuFzKx0t20DuqUZW6nYbn4MIv7/5jcGFmTi6fr9jFv37ewvYDaUSF1eHFq7pyZa/m1Kput1E3Jo8VEMOCzSnsSE3jb4M6+DuVcuU5uPCr1bsZEBPBR0t2MGnhNpKPZtK1eQPevaEXl3Q+gyDrimvMKayAGKYu3kFY3ZoM7nyGv1Mpd8N7uGcu/PtX63jsy3iOZuRwbrswXru+B+e0bVype6MZU1pWQKq4XYfS+XHDPu7q37ZKjpSuVk144vJOjJ60lIs6RvCnC9rSLbJyjoExxtesgFRx05bsQIGRvavuFMF92jRm/dODbcS4MSVU9b5ymhOyclxMX7aTAR0iiAyt2uMYrHgYU3JWQKqwb9ftZf+xzEoxaZQxpvxZAanCpi5OJDI0mPPbh/s7FWNMBVRkARGRSSKSLCLxHrFnRGS1iKwUkW9FpJkTFxF5U0QSnOW9PLYZIyKbnccYj/iZIrLG2eZNsW4v5SIh+SiLt6Yyqk9L66JqjPFKcc5AJgOD88VeVtVuqtoD+Bp4wokPAaKdx1jgXQARaQQ8CfQBegNPikios827zrp52+U/likDUxfvoGZQNa6LbeHvVIwxFVSRBURVFwCp+WJHPH6sA6jzfBjwobotBhqKSFNgEPCdqqaq6kHgO2Cws6y+qi5SVQU+BIaX+lWZ00rLyuF/K5IY0vUMwkp5O3NjTNXldTdeEXkOGA0cBi50ws2BnR6rJTmx08WTCoibMvTVqt0czcixxnNjTKl43YiuqhNUtQXwEXCPEy7oYrp6ES+QiIwVkTgRiUtJSSlpygb3nWX/uziRDk3qEdsqtOgNjDGmEL7ohfUxcLXzPAnwvKgeCewuIh5ZQLxAqvq+qsaqamx4uPUc8saqpMPE7zrCjX1b2m06jDGl4lUBEZFojx+HAhuc57OA0U5vrL7AYVXdA8wDLhGRUKfx/BJgnrPsqIj0dXpfjQZmevtiTNGmLk4kpGYQw3valUJjTOkU2QYiItOA/kCYiCTh7k11qYh0AFxAIvAnZ/XZwKVAApAG3AKgqqki8gywzFnvaVXNa5i/C3dPr2BgjvMwZeBQWhZfrdrN1WdG2nwWxphSK7KAqOrIAsITC1lXgXGFLJsETCogHgd4NzG1KZHPlieRmePixj7WeG6MKT0biV5FqLonjTqzVSidmtX3dzrGmErACkgV8duWA2zdf5wb+1bdu+4aY3zLCkgVMXVxIqEhNRjSpam/UzHGVBJWQKqAvYcz+HbdPq6LbUHtGjantzHGN6yAVAHTl+0g16WM6mOXr4wxvmMFpJLLyXUxfelOzm8fTqvGdfydjjGmErECUsl9vz6ZvUcyuNHOPowxPmYFpJL7aEkizRrUZkBMhL9TMcZUMlZAKrFt+4/zy+b9jOzdkupB9qs2xviWfapUUi6X8n8/bKZ6NeH63jZplDHG96yAVEK5LmX856v5/PddjD2/DRH1avs7JWNMJeT1hFImMGXluPjLJyv5ZvUe7h3Qjr9c3N7fKRljKikrIJVIRnYud3+0gh83JPPIkBjuvKCtv1MyxlRiVkAqieOZOdw+JY7F2w7w7PAuNl2tMabMWQGpBA6nZXPz5KWsTjrMP6/rzpU9I4veyBhjSskKSAW3/1gmN01cSkLyUd4e1YvBXc7wd0rGmCrCCkgFtudwOjd8sITdh9L5YMxZXNDe5ok3xpQfKyAVVOKB49zwwRIOpWXz4a196B3VyN8pGWOqmCLHgYjIJBFJFpF4j9jLIrJBRFaLyBci0tBj2SMikiAiG0VkkEd8sBNLEJHxHvEoEVkiIptFZIaI1PTlC6yMNu87yrXvLeJYZg4f32HFwxjjH8UZSDgZGJwv9h3QRVW7AZuARwBEpBMwAujsbPOOiASJSBDwNjAE6ASMdNYFeAl4TVWjgYPAbaV6RZVc/K7DXPevRSgwY+zZdItsWOQ2xhhTFoosIKq6AEjNF/tWVXOcHxcDed1+hgHTVTVTVbcBCUBv55GgqltVNQuYDgwTEQEGAJ85208BhpfyNVVacdtTGfn+YkJqVufTO8+mwxn1/J2SMaYK88WtTG4F5jjPmwM7PZYlObHC4o2BQx7FKC9u8vllcwo3TVxKeL1afPqns2kdZnN7GGP8q1QFREQmADnAR3mhAlZTL+KFHW+siMSJSFxKSkpJ062wvl27l9smx9GqcQgz7jybZg2D/Z2SMcZ4X0BEZAxwOXCDquZ96CcBnrd+jQR2nya+H2goItXzxQukqu+raqyqxoaHV40uqzNX7uKuj1bQsVl9po/tS3i9Wv5OyRhjAC8LiIgMBh4GhqpqmseiWcAIEaklIlFANLAUWAZEOz2uauJuaJ/lFJ75wDXO9mOAmd69lMrn4yU7uH/GSs5qHcpHt/ehYYh1UDPGBI7idOOdBiwCOohIkojcBrwF1AO+E5GVIvIegKquBT4B1gFzgXGqmuu0cdwDzAPWA58464K7ED0gIgm420Qm+vQVVlD/XrCVR79YwwXtw5l8S2/q1rIhO8aYwCJ/XH2qWGJjYzUuLs7faficqvL695t544fNXNr1DF6/vic1q9u0LcYY3xCR5aoa64t92dfaAKKqPPfNej5YuI1rzozkxau62lS0xpiAZQUkQOS6lMe+XMO0pTsZc3YrnryiM9WqFdRJzRhjAoMVkACQnevib5+uYubK3dzdvy0PDuqAe4ylMcYELisgfpaRncufp/3Od+v28eCgDoy7sJ2/UzLGmGKxAuJHaVk5jP1wOQsT9vP3oZ0Zc05rf6dkjDHFZgXETw6nZ3Pr5GX8vuMgL1/TjWtjWxS9kTHGBBArIH5w4FgmoyctZdO+o7w1qheXdm3q75SMMabErICUs31HMrjhgyXsTE3j/ZtiuTAmwt8pGWOMV6yAlKOdqWnc8MESDhzLZPItvTm7bWN/p2SMMV6zAlJOEpKPceMHS0jPzmXq7X3o2TLU3ykZY0ypWAEpB2t3H2b0xKWIwPSxfenYtL6/UzLGmFKzAlLGlice5Jb/LKVurepMvb0PbcLr+jslY4zxCSsgZei3hP3c/mEc4fVq8dHtfYgMDfF3SsYY4zNWQMrID+v3cddHK2jdOISpt/Uhon5tf6dkjDE+ZQWkDHy1ajd/mbGSTs3qM+WW3oTWsYmgjDGVjxUQH5uxbAfjP1/DWa0aMfHmWOrVruHvlIwxpkxYAfGhSQu38fTX6zgvOoz3b4oluGaQv1MyxpgyYwXER976cTOvfLuJQZ2b8ObIntSqbsXDGFO5FWdO9Ekikiwi8R6xa0VkrYi4RCQ23/qPiEiCiGwUkUEe8cFOLEFExnvEo0RkiYhsFpEZIlLhGgxe/34Tr3y7iSt7NuftUb2seBhjqoTizJc6GRicLxYPXAUs8AyKSCdgBNDZ2eYdEQkSkSDgbWAI0AkY6awL8BLwmqpGAweB27x7KeVPVfnntxt5/fvNXHNmJK9c292moDXGVBlFftqp6gIgNV9svapuLGD1YcB0Vc1U1W1AAtDbeSSo6lZVzQKmA8PEPe3eAOAzZ/spwHCvX005UlVe/XYTb/6YwPWxLfjH1d0IsilojTFViK+/LjcHdnr8nOTECos3Bg6pak6+eIFEZKyIxIlIXEpKik8TLwlV5R/zNvLW/ARG9m7BC1d1tfnLjTFVjq8LSEGfoupFvECq+r6qxqpqbHh4uJcplo6q8uLcDbz70xZG9WnJc8OteBhjqiZf98JKAjyn1osEdjvPC4rvBxqKSHXnLMRz/YCjqjw/ez3//mUbN/ZtydNDu1jxMMZUWb4+A5kFjBCRWiISBUQDS4FlQLTT46om7ob2WaqqwHzgGmf7McBMH+fkE6rKs9+4i8eYs1vxzDArHsaYqq043XinAYuADiKSJCK3iciVIpIEnA18IyLzAFR1LfAJsA6YC4xT1Vzn7OIeYB6wHvjEWRfgYeABEUnA3SYy0bcvsfRUlae/XsfEhdu4+ZzWPDW0M+72f2OMqbrEfRJQ8cTGxmpcXFyZH0dVeWrWWqYsSuS2c6N47LKOVjyMMRWWiCxX1dii1yyajUQ/DZdLeXLWWv67OJE7zovi0UuteBhjTB4rIIVwuZTHZsbz8ZId3HlBG8YPjrHiYYwxHqyAFMDlUiZ8uYZpS3dyV/+2PDSogxUPY4zJxwpIPi6X8sjna5gRt5N7LmzHXy9pb8XDGGMKYAXEQ65Lefh/q/lseRL3DmjHXy624mGMMYWxAuLIdSkPfraKz1fs4v6B0dw/sL2/UzLGmIBmBQR38fjbp6v44vdd/GVge+4bGO3vlIwxJuBV+QKSk+vir5+uYubK3fztkvbcM8CKhzHGFEeVLiA5uS7+8skqvlq1mwcHdWDche38nZIxxlQYVbaAZOe6uH/GSr5ZvYfxQ2L40wVt/Z2SMcZUKFWygGTnurh32u/Mid/Lo5fGMPZ8Kx7GGFNSVa6AZOW4+PO0Fcxbu4/HLuvI7ee18XdKxhhTIVWpApKV42Lcxyv4bt0+nri8E7eeG+XvlIwxpsKqMgUkMyeXcR+t4Pv1yTx1RSdu7mfFwxhjSqNKFJDMnFzumrqCHzck88ywztx0dmt/p2SMMRVepS8gGdm53DV1OfM3pvDs8C7c2LeVv1MyxphKoVIXkIzsXO7873J+3pTC81d2ZVSflv5OyRhjKo1KW0AysnO548M4Fibs56Wru3L9WVY8jDHGl4ozJ/okEUkWkXiPWCMR+U5ENjv/hjpxEZE3RSRBRFaLSC+PbcY4628WkTEe8TNFZI2zzZvig9vfpmflcvuUvOLRzYqHMcaUgSILCDAZGJwvNh74QVWjgR+cnwGGANHOYyzwLrgLDvAk0AfoDTyZV3ScdcZ6bJf/WCWSnpXLbVOW8euW/bx8TXeui21Rmt0ZY4wpRJEFRFUXAKn5wsOAKc7zKcBwj/iH6rYYaCgiTYFBwHeqmqqqB4HvgMHOsvqqukhVFfjQY18llpaVwy2Tl7J46wFevbY715wZ6e2ujDHGFKE4ZyAFaaKqewCcfyOceHNgp8d6SU7sdPGkAuIFEpGxIhInInEpKSknLTuemcPN/1nG0m2p/PO6HlzVy4qHMcaUJW8LSGEKar9QL+IFUtX3VTVWVWPDw8NPxI9l5nDzf5YStz2V167vwfCehdYgY4wxPuJtAdnnXH7C+TfZiScBno0OkcDuIuKRBcSL7WhGNjdPWsqKHYd4Y0RPhvWw4mGMMeXB2wIyC8jrSTUGmOkRH+30xuoLHHYucc0DLhGRUKfx/BJgnrPsqIj0dXpfjfbYV5GOZmQzZtJSft95iDdH9OSK7s28fDnGGGNKqshxICIyDegPhIlIEu7eVC8Cn4jIbcAO4Fpn9dnApUACkAbcAqCqqSLyDLDMWe9pVc1rmL8Ld0+vYGCO8yhSriqjJy1lTdJh3hrZkyFdmxZnM2OMMT4i7s5PFU/DljEaduM/eWtULwZ3OcPf6RhjTIUgIstVNdYX+6qwI9EzsnN598YzubhTE3+nYowxVZKve2GVm5aNQ6x4GGOMH1XYAlK/dg1/p2CMMVVahS0gxhhj/MsKiDHGGK9YATHGGOMVKyDGGGO8YgXEGGOMV6yAGGOM8YoVEGOMMV6xAmKMMcYrFfZeWCJyFNjo7zzyCQP2+zuJfCyn4gnEnCAw87KciicQc6oNJKlqqaYOz1ORC0icr24I5iuWU/FYTsUXiHlZTsVTFXKyS1jGGGO8YgXEGGOMVypyAXnf3wkUwHIqHsup+AIxL8upeCp9ThW2DcQYY4x/VeQzEGOMMX5kBcQYY4xXAqaAiMgkEUkWkXiPWHcRWSQia0TkKxGp78RriMgUJ75eRB7x2GawiGwUkQQRGR8gOZ2yH3/nJSItRGS+E1srIvcFQE61RWSpiKxycvq7v3Py2C5IRH4Xka8DIScR2e7EV4pIXIDk1FBEPhORDc6ys/2dl4h0cN6jvMcREbnfnzk5y/7i/I3Hi8g0EakdADnd5+SzttjvkaoGxAM4H+gFxHvElgEXOM9vBZ5xno8CpjvPQ4DtQGsgCNgCtAFqAquATv7MqbD9BMB71RTo5cTrAZv8/V4BAtR14jWAJUBff//+nNgDwMfA1/7+3Tk/bwfCAuXvyfl5CnC787wm0DAQ8vLYNgjYC7Ty8995c2AbEOws+wS42c85dQHinVh14HsguqhjB8wZiKouAFLzhTsAC5zn3wFX560O1BGR6kAwkAUcAXoDCaq6VVWzgOnAMD/nVNh+vOaLvFR1j6qucPZ3FFiP+w/bnzmpqh5z1qnhPLzu5eGr35+IRAKXAR94m4uvc/IlX+TkfMM9H5jo7DNLVQ/5O698214EbFHVxADIqToQ7CwLAXb7OaeOwGJVTVPVHOBn4Mqijh0wBaQQ8cBQ5/m1QAvn+WfAcWAPsAN4RVVTcX8A7vTYPolSfCj6KKfy4nVeItIa6In7G79fc3IuFa0EkoHvVNXvOQGvAw8BLh/nUpqcFPhWRJaLyNgAyKkNkAL8x7nU94GI1AmAvDyNAKb5OydV3QW84sT2AIdV9Vt/5uSsf76INBaREOBSj20KFegF5FZgnIgsx32ZJcuJ9wZygWZAFPBXEWmD+xJIfr7up1zSnMqLV3mJSF3gf8D9qurrb7clzklVc1W1BxAJ9BaRLv7MSUQuB5JVdbmP8/A6J2dZP1XtBQxxtj3fzzlVx30Z5V1V7Yn7Q6pUbZA+ygsAEamJ+wP1U3/nJCKhuK+MRDnL6ojIjf7MSVXXAy/hPluZi/vyf05RB6nu46R9SlU3AJcAiEh73JcRwH0db66qZgPJIvIrEIv77MOzakZSilNDH+W01ZfH92VeIlIDd/H4SFU/D4ScPLY9JCI/AYNxfzvyV049gaEicinuG9HVF5Gpquqz//DevE+qutvZNllEvsD9wbDglJ2XX04LcN+kL++M8TPKoICU4m9qCLBCVfcFQE4KbFPVFGebz4FzgKl+zGmrqk7EuQQpIs/jvoJzWgF9BiIiEc6/1YDHgPecRTuAAeJWB+gLbMDdcBQtIlHON44RwCw/51QuSpqXiAjuP5b1qvrPAMkpXEQaOtsEAwPx8XtY0pxU9RFVjVTV1rj/nn70ZfHwJicRqSMi9Zxt6uD+oPBZkfUmJ1XdC+wUkQ7OehcB63yZkzd5eWw6krK5fOVNTjuAviIS4vw/vAh3G6Q/c/LcpiVwFcV5v7xt+ff1w0l2D5CNu/LdBtyHu3fQJuBF/hg5Xxf3qeha3H+kD3rs51Jn/S3AhADJ6ZT9+Dsv4Fzc34RWAyudx6V+zqkb8LuTUzzwhL/fp3z760/pe2H54n1qg/sSwypnWaD8nfcA4pzf35dAaIDkFQIcABqUJh8f5/R33B/c8cB/gVoBkNMvTmwVcFFxjm23MjHGGOOVgL6EZYwxJnBZATHGGOMVKyDGGGO8YgXEGGOMV6yAGGOM8YoVEGOMMV6xAmKqNBH5SURi/Z2HMRWRFRBT6TmjbqvE37qIBPk7B1N1VIn/VKbqEZHW4p4w5x1gBXCTuCfYWSEin4r7JpL5t7mkoHVE5AkRWSbuyXbed24/gYjcKyLrRGS1iEx3YnXEPcHPMnHflbbQ6QRE5BcR6eHx868i0q2wfTiv6RcnvxUico4T7y/uycE+Btb48G005vRKO7TfHvYIxAfuSXJcuO/1E4b7Zn91nGUP49wmBfgJ983kTrdOI4/9/he4wnm+G+cWFDiTJwHPAzfmxXDfSqJOITmOAV53nrcH4k63D9y35KjtxKM91u+P++63Uf5+3+1RtR4BfTdeY0opUVUXi/uW7J2AX52Th5rAonzr9j3NOheKyEO4P8Ab4b6P0Fe47/n0kYh8ifveT+C+seFQEfmb83NtoCUF3yzvU+BxEXkQ9+23Jxexj93AW85ZSy7uopNnqapuK86bYoyvWAExldlx51/BPTnVyNOsW+A64p6r+h0gVlV3ishTuD/QwX2L7PNxzzPxuIh0dvZztapuLCo5VU0Tke9wzw1xHe4zobxcTtmHc+x9QHfcl58zCnitxpQbawMxVcFioJ+ItANwbqPdvpjr5BWL/U6byDXO8mpAC1Wdj3u2woa473Q6D/izRztJzyJy+wB4E1imf8ygV9g+GgB7VNUF3IR7jm9j/MYKiKn01D1xz83ANBFZjbtYxBRnHXXP6/1v3I3TX+KecwbcH95TRWQN7lvQv+as+wzuudxXi0i88/PpcluOe07q/3iEC9vHO8AYEVmM+/KVnXUYv7LbuRvjRyLSDHdDfoxzZmFMhWFnIMb4iYiMBpbgnhDKioepcOwMxJgyJiKDgJfyhbep6pX+yMcYX7ECYowxxit2CcsYY4xXrIAYY4zxihUQY4wxXrECYowxxiv/D7uterVcYxAgAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "sent_by_year.nunique().reset_index().plot('release_year','INMATE_DOC_NUMBER')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using Multiple Tables\n",
    "\n",
    "Suppose we wanted to know whether the average sentence length differed by race. To do this, we would need to group by race, then calculate the average sentence length. The `sentence` table doesn't contain information about inmate race though -- that information is in the `inmate` table. To get sentence length by race, then, we need to join the tables so that we can information from both tables. Note that in the code below, we make sure to get the difference in days using the `JulianDay` command.\n",
    "\n",
    "As before, let's look at only the cases in the 1980s."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# check how many records from our inmate data matches the sentence data\n",
    "\n",
    "qry = \"\"\"\n",
    "SELECT sentences.INMATE_DOC_NUMBER, inmate.INMATE_RACE_CODE, CAST(strftime(\"%Y\",sentences.ACTUAL_SENTENCE_END_DATE) as integer) as release_year,\n",
    "     CAST(JulianDay(sentences.ACTUAL_SENTENCE_END_DATE) - JulianDay(sentences.\"SENTENCE_BEGIN_DATE_(FOR_MAX)\") as integer) as sentence_length,\n",
    "      sentences.\"SENTENCE_BEGIN_DATE_(FOR_MAX)\", sentences.ACTUAL_SENTENCE_END_DATE\n",
    "FROM sentences\n",
    "LEFT JOIN inmate\n",
    "ON sentences.INMATE_DOC_NUMBER = inmate.INMATE_DOC_NUMBER\n",
    "WHERE release_year >= 1980 AND release_year < 1990\n",
    "AND INMATE_SENTENCE_COMPONENT == '001'\n",
    "\"\"\"\n",
    "sent_in = pd.read_sql(qry, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INMATE_DOC_NUMBER</th>\n",
       "      <th>INMATE_RACE_CODE</th>\n",
       "      <th>release_year</th>\n",
       "      <th>sentence_length</th>\n",
       "      <th>SENTENCE_BEGIN_DATE_(FOR_MAX)</th>\n",
       "      <th>ACTUAL_SENTENCE_END_DATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000004</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>1984</td>\n",
       "      <td>365</td>\n",
       "      <td>1983-07-12</td>\n",
       "      <td>1984-07-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000010</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>1983</td>\n",
       "      <td>2938</td>\n",
       "      <td>1975-06-11</td>\n",
       "      <td>1983-06-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000026</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>1984</td>\n",
       "      <td>419</td>\n",
       "      <td>1983-07-18</td>\n",
       "      <td>1984-09-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000031</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>1981</td>\n",
       "      <td>3734</td>\n",
       "      <td>1971-08-13</td>\n",
       "      <td>1981-11-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000033</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>1980</td>\n",
       "      <td>90</td>\n",
       "      <td>1979-11-06</td>\n",
       "      <td>1980-02-04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  INMATE_DOC_NUMBER INMATE_RACE_CODE  release_year  sentence_length  \\\n",
       "0           0000004            WHITE          1984              365   \n",
       "1           0000010            BLACK          1983             2938   \n",
       "2           0000026            WHITE          1984              419   \n",
       "3           0000031            BLACK          1981             3734   \n",
       "4           0000033            BLACK          1980               90   \n",
       "\n",
       "  SENTENCE_BEGIN_DATE_(FOR_MAX) ACTUAL_SENTENCE_END_DATE  \n",
       "0                    1983-07-12               1984-07-11  \n",
       "1                    1975-06-11               1983-06-27  \n",
       "2                    1983-07-18               1984-09-09  \n",
       "3                    1971-08-13               1981-11-02  \n",
       "4                    1979-11-06               1980-02-04  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sent_in.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">sentence_length</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>INMATE_RACE_CODE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>463.000000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ASIAN/ORTL</th>\n",
       "      <td>170383.638298</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BLACK</th>\n",
       "      <td>35888.332355</td>\n",
       "      <td>72248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>INDIAN</th>\n",
       "      <td>24411.074733</td>\n",
       "      <td>3372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OTHER</th>\n",
       "      <td>105091.892128</td>\n",
       "      <td>686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UNKNOWN</th>\n",
       "      <td>483735.333333</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WHITE</th>\n",
       "      <td>30625.265931</td>\n",
       "      <td>63005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 sentence_length       \n",
       "                            mean  count\n",
       "INMATE_RACE_CODE                       \n",
       "                      463.000000      2\n",
       "ASIAN/ORTL         170383.638298     47\n",
       "BLACK               35888.332355  72248\n",
       "INDIAN              24411.074733   3372\n",
       "OTHER              105091.892128    686\n",
       "UNKNOWN            483735.333333      3\n",
       "WHITE               30625.265931  63005"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sent_in[['INMATE_RACE_CODE', 'sentence_length']].groupby('INMATE_RACE_CODE').agg({'sentence_length':['mean','count']})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Percentiles\n",
    "\n",
    "In this section, we will calculate percentiles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'inoff' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-40-48b3ba2c6bf8>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# what is the distribution of height in our sample?\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mheight\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0minoff\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'OFFENDER_HEIGHT_(IN_INCHES)'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfloat\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m \u001b[0minoff\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'OFFENDER_HEIGHT_(IN_INCHES)'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mheight\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'inoff' is not defined"
     ]
    }
   ],
   "source": [
    "# what is the distribution of height in our sample?\n",
    "height = inoff['OFFENDER_HEIGHT_(IN_INCHES)'].astype(float)\n",
    "inoff['OFFENDER_HEIGHT_(IN_INCHES)'] = height"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'inoff' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-41-c379a15b930f>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# Percentiles of height\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0minoff\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'OFFENDER_HEIGHT_(IN_INCHES)'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdescribe\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpercentiles\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0.1\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;36m0.25\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;36m0.5\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m0.75\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m0.9\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'inoff' is not defined"
     ]
    }
   ],
   "source": [
    "# Percentiles of height\n",
    "inoff['OFFENDER_HEIGHT_(IN_INCHES)'].describe(percentiles=[0.1,0.25,0.5, 0.75, 0.9])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Percentiles of height by gender\n",
    "inoff.groupby('INMATE_GENDER_CODE')['OFFENDER_HEIGHT_(IN_INCHES)'].describe(percentiles=[0.1,0.25,0.5, 0.75, 0.9])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "toc_cell": false,
   "toc_position": {
    "height": "566px",
    "left": "0px",
    "right": "954px",
    "top": "110px",
    "width": "179px"
   },
   "toc_section_display": "block",
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}