Parse and analyze Missouri prison records.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Missouri prison records\n",
    "\n",
    "This notebook has code to download, unzip and parse a fixed-width file of [inmates held by the Missouri Department of Corrections](https://doc.mo.gov/Sunshine_Law/) and load it into a pandas dataframe. The department also releases a handful of XML files with column specs, field descriptions and lookups for facility locations and county abbreviations.\n",
    "\n",
    "No analysis, yet -- just loading it up."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xml.etree.ElementTree as ET\n",
    "from zipfile import ZipFile\n",
    "from collections import OrderedDict\n",
    "\n",
    "import requests\n",
    "from slugify import slugify\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The download function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def download_files():\n",
    "    files = ['https://doc.mo.gov/xml/sunshine_layout.xml',\n",
    "             'https://doc.mo.gov/xml/sunshine_desc.xml',\n",
    "             'https://doc.mo.gov/xml/sunshine_locations.xml',\n",
    "             'https://doc.mo.gov/xml/sunshine_counties.xml',\n",
    "             'https://doc.mo.gov/Sunshine_Law/fak930.zip']\n",
    "    \n",
    "    for f in files:\n",
    "        r = requests.get(f)\n",
    "        r.raise_for_status()\n",
    "\n",
    "        filename = f.split('/')[-1]\n",
    "\n",
    "        with open(filename, 'wb') as f:\n",
    "            for block in r.iter_content(1024):\n",
    "                f.write(block)\n",
    "    \n",
    "    return [x.split('/')[-1] for x in files]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "to download the files and unzip the main data file,\n",
    "un-comment the lines in this cell and then run it\n",
    "'''\n",
    "\n",
    "# download_files()\n",
    "# with ZipFile('fak930.zip', 'r') as z:\n",
    "#    z.extractall('.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to parse the county abbreviation file\n",
    "\n",
    "This function parses the county XML file and returns a dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def county_dict(infile='sunshine_counties.xml'):\n",
    "    tree = ET.parse(infile)\n",
    "    locations = tree.getroot()\n",
    "    \n",
    "    return {x.find('acronym').text: x.find('fullname').text\n",
    "            for x in locations}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to parse the file layout XML\n",
    "\n",
    "This function parses the XML with the file layout. It returns an OrderedDict where each key is a slugified column name and each value is a `(start_position, end_position)` tuple -- the format we'll need when we pass the values to the `colspecs` argument of pandas' [`read_fwf()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_fwf.html) method.\n",
    "\n",
    "Here's an example of an item in the OrderedDict that gets returned: `('doc-id', (0, 8))`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def layout_dict(infile='sunshine_layout.xml'):\n",
    "\n",
    "    tree = ET.parse(infile)\n",
    "    locations = tree.getroot()\n",
    "\n",
    "    counter = 0\n",
    "    d = OrderedDict()\n",
    "    for l in locations:\n",
    "        desc = slugify(l.find('desc').text, to_lower=True)\n",
    "        start = counter\n",
    "        data_len = int(l.find('length').text.split(',')[0].strip())\n",
    "        end = start + data_len\n",
    "        d[desc] = (start, end)\n",
    "        counter += data_len\n",
    "    \n",
    "    return d"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to parse the locations XML\n",
    "\n",
    "This function parses the locations XML file and returns a dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def location_dict(infile='sunshine_locations.xml'):\n",
    "    tree = ET.parse(infile)\n",
    "    locations = tree.getroot()\n",
    "\n",
    "    return {x.find('acronym').text: x.find('name').text\n",
    "            for x in locations}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get the column names and colspecs for our data frame\n",
    "\n",
    "First, we parse the file layout. The data file doesn't have headers, so we'll extract that list from the dictionary keys. The colspecs, in turn, are the values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_layout = layout_dict()\n",
    "columns = list(file_layout.keys())\n",
    "colspecs = list(file_layout.values())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to parse dates\n",
    "\n",
    "The dates in the data are formatted `YYYYMMDD`. Or they're nonsense dates (99999999), or null, or 0. So we'll try to parse the date in that format; if not, return `NaN`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def dateparse(x):\n",
    "    try:\n",
    "        return pd.datetime.strptime(x, '%Y%m%d')\n",
    "    except (TypeError, ValueError):\n",
    "        return np.NaN"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read the data into a data frame\n",
    "\n",
    "... using the `read_fwf()` method. Apart from the file name, we're handing this method six additional arguments:\n",
    "- `header=None`: The data file doesn't have a header row\n",
    "- `names=columns`: The column names\n",
    "- `colspecs=colspecs`: The list of tuples with start/end positions for each break\n",
    "- `dtypes={'doc-id': str}`: Interpret the first column (a number with leading zeroes) as a string\n",
    "- `parse_dates=[...]`: The columns to parse as dates\n",
    "- `date_parser=dateparse`: Use the `dateparser` function, defined above, to handle dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_fwf('SunshineLawFak930',\n",
    "                 header=None,\n",
    "                 names=columns,\n",
    "                 colspecs=colspecs,\n",
    "                 dtypes={'doc-id': str},\n",
    "                 parse_dates=['birth-date', 'sentence-i-date', 'sentence-maximum-release-date',\n",
    "                              'sentence-i-minimum-release-date', 'sentence-f-probation-date'],\n",
    "                 date_parser=dateparse)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>doc-id</th>\n",
       "      <th>offender-last-name</th>\n",
       "      <th>offender-first-name</th>\n",
       "      <th>offender-middle-name</th>\n",
       "      <th>generation-suffix</th>\n",
       "      <th>interface-race-description</th>\n",
       "      <th>interface-sex-description</th>\n",
       "      <th>birth-date</th>\n",
       "      <th>offender-assigned-place</th>\n",
       "      <th>doc-loc-func-flag</th>\n",
       "      <th>...</th>\n",
       "      <th>sentence-maximum-release-date</th>\n",
       "      <th>sentence-i-minimum-release-date</th>\n",
       "      <th>sentence-i-length-years</th>\n",
       "      <th>sentence-i-length-months</th>\n",
       "      <th>sentence-i-length-days</th>\n",
       "      <th>sentence-f-probation-date</th>\n",
       "      <th>probation-type</th>\n",
       "      <th>probation-term-yrs</th>\n",
       "      <th>probation-term-months</th>\n",
       "      <th>probation-term-days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1075064</th>\n",
       "      <td>1327750</td>\n",
       "      <td>WALKER</td>\n",
       "      <td>REBECCA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Black</td>\n",
       "      <td>Female</td>\n",
       "      <td>1986-08-29</td>\n",
       "      <td>23</td>\n",
       "      <td>F</td>\n",
       "      <td>...</td>\n",
       "      <td>2023-04-24 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2018-04-25</td>\n",
       "      <td>SES</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1075065</th>\n",
       "      <td>1327753</td>\n",
       "      <td>OTT</td>\n",
       "      <td>MARTY</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>White</td>\n",
       "      <td>Male</td>\n",
       "      <td>1972-02-12</td>\n",
       "      <td>44</td>\n",
       "      <td>F</td>\n",
       "      <td>...</td>\n",
       "      <td>2023-04-23 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2018-04-24</td>\n",
       "      <td>SES</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1075066</th>\n",
       "      <td>1327757</td>\n",
       "      <td>BARTLETT</td>\n",
       "      <td>TRAVIS</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>White</td>\n",
       "      <td>Male</td>\n",
       "      <td>1987-09-11</td>\n",
       "      <td>26</td>\n",
       "      <td>F</td>\n",
       "      <td>...</td>\n",
       "      <td>2020-04-18 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>2018-04-19</td>\n",
       "      <td>SES</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1075067</th>\n",
       "      <td>1327757</td>\n",
       "      <td>BARTLETT</td>\n",
       "      <td>TRAVIS</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>White</td>\n",
       "      <td>Male</td>\n",
       "      <td>1987-09-11</td>\n",
       "      <td>26</td>\n",
       "      <td>F</td>\n",
       "      <td>...</td>\n",
       "      <td>2020-04-18 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>2018-04-19</td>\n",
       "      <td>SES</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1075068</th>\n",
       "      <td>1327785</td>\n",
       "      <td>MCKOY</td>\n",
       "      <td>COURTNEY</td>\n",
       "      <td>O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Black</td>\n",
       "      <td>Male</td>\n",
       "      <td>1980-11-28</td>\n",
       "      <td>35</td>\n",
       "      <td>F</td>\n",
       "      <td>...</td>\n",
       "      <td>2023-04-22 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2018-04-23</td>\n",
       "      <td>SES</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          doc-id offender-last-name offender-first-name offender-middle-name  \\\n",
       "1075064  1327750             WALKER             REBECCA                  NaN   \n",
       "1075065  1327753                OTT               MARTY                    B   \n",
       "1075066  1327757           BARTLETT              TRAVIS                    A   \n",
       "1075067  1327757           BARTLETT              TRAVIS                    A   \n",
       "1075068  1327785              MCKOY            COURTNEY                    O   \n",
       "\n",
       "        generation-suffix interface-race-description  \\\n",
       "1075064               NaN                      Black   \n",
       "1075065               NaN                      White   \n",
       "1075066               NaN                      White   \n",
       "1075067               NaN                      White   \n",
       "1075068               NaN                      Black   \n",
       "\n",
       "        interface-sex-description birth-date offender-assigned-place  \\\n",
       "1075064                    Female 1986-08-29                      23   \n",
       "1075065                      Male 1972-02-12                      44   \n",
       "1075066                      Male 1987-09-11                      26   \n",
       "1075067                      Male 1987-09-11                      26   \n",
       "1075068                      Male 1980-11-28                      35   \n",
       "\n",
       "        doc-loc-func-flag         ...         sentence-maximum-release-date  \\\n",
       "1075064                 F         ...                   2023-04-24 00:00:00   \n",
       "1075065                 F         ...                   2023-04-23 00:00:00   \n",
       "1075066                 F         ...                   2020-04-18 00:00:00   \n",
       "1075067                 F         ...                   2020-04-18 00:00:00   \n",
       "1075068                 F         ...                   2023-04-22 00:00:00   \n",
       "\n",
       "        sentence-i-minimum-release-date sentence-i-length-years  \\\n",
       "1075064                             NaN                       4   \n",
       "1075065                             NaN                       4   \n",
       "1075066                             NaN                       0   \n",
       "1075067                             NaN                       0   \n",
       "1075068                             NaN                       4   \n",
       "\n",
       "         sentence-i-length-months sentence-i-length-days  \\\n",
       "1075064                         0                      0   \n",
       "1075065                         0                      0   \n",
       "1075066                         6                      0   \n",
       "1075067                         6                      0   \n",
       "1075068                         0                      0   \n",
       "\n",
       "        sentence-f-probation-date probation-type probation-term-yrs  \\\n",
       "1075064                2018-04-25            SES                  5   \n",
       "1075065                2018-04-24            SES                  5   \n",
       "1075066                2018-04-19            SES                  2   \n",
       "1075067                2018-04-19            SES                  2   \n",
       "1075068                2018-04-23            SES                  5   \n",
       "\n",
       "        probation-term-months probation-term-days  \n",
       "1075064                     0                   0  \n",
       "1075065                     0                   0  \n",
       "1075066                     0                   0  \n",
       "1075067                     0                   0  \n",
       "1075068                     0                   0  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}