{ "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
INMATE_DOC_NUMBERINMATE_LAST_NAMEINMATE_FIRST_NAMEINMATE_MIDDLE_INITIALINMATE_NAME_SUFFIXINMATE_NAME_SOUNDEX_CODEINMATE_GENDER_CODEINMATE_RACE_CODEINMATE_BIRTH_DATEINMATE_ETHNIC_AFFILIATION...CURRENT_PENDING_REVIEWS_FLAGESCAPE_HISTORY_FLAGPRIOR_INCARCERATIONS_FLAGNEXT_PAROLE_REVIEW_TYPE_CODETIME_OF_LAST_MOVEMENTPOPULATION/MANAGEMENT_UNITINMATE_POSITIVELY_IDENTIFIEDPAROLE_AND_TERMINATE_STATUSINMATE_LABEL_STATUS_CODEPRIMARY_OFFENSE_QUALIFIER
00000004AARONDAVIDCMALEWHITE1961-10-15UNKNOWN...NNY00:09:00YES
10000006AARONGERALDMALEWHITE1951-07-17UNKNOWN...NNY00:11:00YES
20000008AARONJAMESMMALEWHITE1963-12-29UNKNOWN...NNY23:59:00YESFILE JACKET LABEL PRINTEDPRINCIPAL
30000010AARONKENNETHTMALEBLACK1953-05-18UNKNOWN...NNY00:13:00YES
40000014AARONMOYERMALEWHITE1921-08-26UNKNOWN...NNY00:12:00YES
\n", "

5 rows × 67 columns

\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
OFFENDER_NC_DOC_ID_NUMBEROFFENDER_BIRTH_DATEOFFENDER_GENDER_CODEOFFENDER_RACE_CODEOFFENDER_HEIGHT_(IN_INCHES)OFFENDER_WEIGHT_(IN_LBS)OFFENDER_SKIN_COMPLEXION_CODEOFFENDER_HAIR_COLOR_CODEOFFENDER_EYE_COLOR_CODEOFFENDER_BODY_BUILD_CODE...OFFENDER_ETHNIC_CODEOFFENDER_PRIMARY_LANGUAGE_CODEOFFENDER_SHIRT_SIZEOFFENDER_PANTS_SIZEOFFENDER_JACKET_SIZEOFFENDER_SHOE_SIZEOFFENDER_DRESS_SIZENEXT_PHOTO_YEARDATE_OF_LAST_UPDATETIME_OF_LAST_UPDATE
000000011974-04-04FEMALEBLACK66180UNKNOWNBLACKBROWNUNKNOWN...UNKNOWNENGLISH000000000000002015-02-0413:32:12
100000031955-07-24MALEWHITE74240LIGHTBROWNBLUESTOCKY...EUROPEAN/N.AM./AUSTRENGLISH000000000000002015-05-0517:20:06
200000041961-10-15MALEWHITE70150UNKNOWNBROWNGREENUNKNOWN...UNKNOWNENGLISH000000000000001995-06-2500:00:00
300000051972-01-22MALEWHITE70145UNKNOWNBLONDEBROWNUNKNOWN...UNKNOWNENGLISH000000000000002001-12-2013:36:13
400000061951-07-17MALEWHITE69150UNKNOWNBROWNBLUEUNKNOWN...UNKNOWNENGLISH000000000000001995-06-2500:00:00
\n", "

5 rows × 25 columns

\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
INMATE_DOC_NUMBERINMATE_LAST_NAMEINMATE_FIRST_NAMEINMATE_MIDDLE_INITIALINMATE_NAME_SUFFIXINMATE_NAME_SOUNDEX_CODEINMATE_GENDER_CODEINMATE_RACE_CODEINMATE_BIRTH_DATEINMATE_ETHNIC_AFFILIATION...CURRENT_PENDING_REVIEWS_FLAGESCAPE_HISTORY_FLAGPRIOR_INCARCERATIONS_FLAGNEXT_PAROLE_REVIEW_TYPE_CODETIME_OF_LAST_MOVEMENTPOPULATION/MANAGEMENT_UNITINMATE_POSITIVELY_IDENTIFIEDPAROLE_AND_TERMINATE_STATUSINMATE_LABEL_STATUS_CODEPRIMARY_OFFENSE_QUALIFIER
00000004AARONDAVIDCMALEWHITE1961-10-15UNKNOWN...NNY00:09:00YES
10000006AARONGERALDMALEWHITE1951-07-17UNKNOWN...NNY00:11:00YES
20000008AARONJAMESMMALEWHITE1963-12-29UNKNOWN...NNY23:59:00YESFILE JACKET LABEL PRINTEDPRINCIPAL
30000010AARONKENNETHTMALEBLACK1953-05-18UNKNOWN...NNY00:13:00YES
40000014AARONMOYERMALEWHITE1921-08-26UNKNOWN...NNY00:12:00YES
\n", "

5 rows × 67 columns

\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
count(distinct OFFENDER_NC_DOC_ID_NUMBER)
0314241
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
count(distinct INMATE_DOC_NUMBER)count(*)
0461421461421
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
INMATE_DOC_NUMBERINMATE_COMMITMENT_PREFIXINMATE_SENTENCE_COMPONENTINMATE_COMPUTATION_STATUS_FLAGSENTENCE_BEGIN_DATE_(FOR_MAX)ACTUAL_SENTENCE_END_DATEPROJECTED_RELEASE_DATE_(PRD)PAROLE_DISCHARGE_DATEPAROLE_SUPERVISION_BEGIN_DATE
00000004AA001EXPIRED1983-07-121984-07-111984-07-110001-01-010001-01-01
10000004AA002EXPIRED0001-01-011984-07-111984-07-110001-01-010001-01-01
20000006AA001EXPIRED1973-01-301973-03-280001-01-010001-01-010001-01-01
30000006AB001EXPIRED1973-04-111975-08-181974-08-100001-01-010001-01-01
40000006AB002EXPIRED1973-04-241975-08-181974-08-100001-01-010001-01-01
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
INMATE_DOC_NUMBERINMATE_COMMITMENT_PREFIXINMATE_SENTENCE_COMPONENTINMATE_COMPUTATION_STATUS_FLAGSENTENCE_BEGIN_DATE_(FOR_MAX)ACTUAL_SENTENCE_END_DATEPROJECTED_RELEASE_DATE_(PRD)PAROLE_DISCHARGE_DATEPAROLE_SUPERVISION_BEGIN_DATErelease_year
00000004AA001EXPIRED1983-07-121984-07-111984-07-110001-01-010001-01-011984
10000010AB001EXPIRED1975-06-111983-06-271987-01-310001-01-010001-01-011983
20000026AA001EXPIRED1983-07-181984-09-091984-09-090001-01-010001-01-011984
30000031AA001EXPIRED1971-08-131981-11-021992-02-250001-01-010001-01-011981
40000033AB001EXPIRED1979-11-061980-02-041980-02-040001-01-010001-01-011980
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
INMATE_DOC_NUMBERINMATE_COMMITMENT_PREFIXINMATE_SENTENCE_COMPONENTINMATE_COMPUTATION_STATUS_FLAGSENTENCE_BEGIN_DATE_(FOR_MAX)ACTUAL_SENTENCE_END_DATEPROJECTED_RELEASE_DATE_(PRD)PAROLE_DISCHARGE_DATEPAROLE_SUPERVISION_BEGIN_DATErelease_year
count141670141670141670141670141670141670141670141670141670141670.000000
unique996191813569836485016173169NaN
top0169217AA001EXPIRED0001-01-011982-12-150001-01-010001-01-010001-01-01NaN
freq168332014167014163371775218993141491141492NaN
meanNaNNaNNaNNaNNaNNaNNaNNaNNaN1984.887061
stdNaNNaNNaNNaNNaNNaNNaNNaNNaN2.819820
minNaNNaNNaNNaNNaNNaNNaNNaNNaN1980.000000
25%NaNNaNNaNNaNNaNNaNNaNNaNNaN1983.000000
50%NaNNaNNaNNaNNaNNaNNaNNaNNaN1985.000000
75%NaNNaNNaNNaNNaNNaNNaNNaNNaN1987.000000
maxNaNNaNNaNNaNNaNNaNNaNNaNNaN1989.000000
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
INMATE_DOC_NUMBERINMATE_COMMITMENT_PREFIXINMATE_SENTENCE_COMPONENTINMATE_COMPUTATION_STATUS_FLAGSENTENCE_BEGIN_DATE_(FOR_MAX)ACTUAL_SENTENCE_END_DATEPROJECTED_RELEASE_DATE_(PRD)PAROLE_DISCHARGE_DATEPAROLE_SUPERVISION_BEGIN_DATE
release_year
1980995499549954995499549954995499549954
1981114081140811408114081140811408114081140811408
1982127491274912749127491274912749127491274912749
1983161981619816198161981619816198161981619816198
1984135861358613586135861358613586135861358613586
1985143831438314383143831438314383143831438314383
1986149201492014920149201492014920149201492014920
1987147711477114771147711477114771147711477114771
1988156351563515635156351563515635156351563515635
1989180661806618066180661806618066180661806618066
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
INMATE_DOC_NUMBERINMATE_COMMITMENT_PREFIXINMATE_SENTENCE_COMPONENTINMATE_COMPUTATION_STATUS_FLAGSENTENCE_BEGIN_DATE_(FOR_MAX)ACTUAL_SENTENCE_END_DATEPROJECTED_RELEASE_DATE_(PRD)PAROLE_DISCHARGE_DATEPAROLE_SUPERVISION_BEGIN_DATErelease_year
release_year
19809752131219113621533991
19811112114121951364149510101
198212432151219733651441991
19831579915122034365133015151
19841330915122023366115415161
19851395616122022365101815151
1986143601613192336583514131
1987141921613191336576120191
1988149731512194736682026251
19891709918131940365104953531
\n", "
" ], "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": [ "" ] }, "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": [ "
" ] }, "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
INMATE_DOC_NUMBERINMATE_RACE_CODErelease_yearsentence_lengthSENTENCE_BEGIN_DATE_(FOR_MAX)ACTUAL_SENTENCE_END_DATE
00000004WHITE19843651983-07-121984-07-11
10000010BLACK198329381975-06-111983-06-27
20000026WHITE19844191983-07-181984-09-09
30000031BLACK198137341971-08-131981-11-02
40000033BLACK1980901979-11-061980-02-04
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sentence_length
meancount
INMATE_RACE_CODE
463.0000002
ASIAN/ORTL170383.63829847
BLACK35888.33235572248
INDIAN24411.0747333372
OTHER105091.892128686
UNKNOWN483735.3333333
WHITE30625.26593163005
\n", "
" ], "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\u001b[0m in \u001b[0;36m\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\u001b[0m in \u001b[0;36m\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 }