Match Sentencing Data with Profile Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ODOC Public Inmate Data\n",
    "\n",
    "\n",
    "This notebook is intended as a start for research of the ODOC data.  To use this notebook, follow the setup instructiion found [here](https://github.com/codefortulsa/odoc-parse). Then download the data published [here](http://doc.publishpath.com/odoc-public-inmate-data).  Unzip the file and place the files in a subdirectory called 'data'.\n",
    "\n",
    "The set of files includes a ReadMe.txt which describes the files and their fixed formats. The sections of this notebook show description of each file and how to import it into pandas dataframes.  NOTE: the widths variables differ slighty from the description to handle some difference in the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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_NUM</th>\n",
       "      <th>STATUTE_CODE</th>\n",
       "      <th>SENTENCING_COUNTY</th>\n",
       "      <th>JS_DATE</th>\n",
       "      <th>CRF_NUMBER</th>\n",
       "      <th>INCARCERATED_TERM_IN_YEARS</th>\n",
       "      <th>PROBATION_TERM_IN_YEARS</th>\n",
       "      <th>LAST_NAME</th>\n",
       "      <th>FIRST_NAME</th>\n",
       "      <th>MIDDLE_NAME</th>\n",
       "      <th>...</th>\n",
       "      <th>FACILITY</th>\n",
       "      <th>BIRTH_DATE</th>\n",
       "      <th>SEX</th>\n",
       "      <th>RACE</th>\n",
       "      <th>HAIR</th>\n",
       "      <th>HEIGHT_FT</th>\n",
       "      <th>HEIGHT_IN</th>\n",
       "      <th>WEIGHT</th>\n",
       "      <th>EYE</th>\n",
       "      <th>STATUS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8359</td>\n",
       "      <td>21-1720</td>\n",
       "      <td>POTTAWATOMIE COUNTY COURT</td>\n",
       "      <td>13-MAR-13</td>\n",
       "      <td>2010-300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.5</td>\n",
       "      <td>MESLES</td>\n",
       "      <td>ARDELL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>INACTIVE</td>\n",
       "      <td>06-APR-87</td>\n",
       "      <td>M</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>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10337</td>\n",
       "      <td>21-701.7</td>\n",
       "      <td>BRYAN COUNTY COURT</td>\n",
       "      <td>13-APR-20</td>\n",
       "      <td>1-0</td>\n",
       "      <td>7777.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SAWYER</td>\n",
       "      <td>FRANK</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>INACTIVE</td>\n",
       "      <td>01-JAN-00</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>UNK</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10337</td>\n",
       "      <td>21-701.7</td>\n",
       "      <td>BRYAN COUNTY COURT</td>\n",
       "      <td>14-DEC-62</td>\n",
       "      <td>20-15645</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.0</td>\n",
       "      <td>SAWYER</td>\n",
       "      <td>FRANK</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>INACTIVE</td>\n",
       "      <td>01-JAN-00</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>UNK</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20413</td>\n",
       "      <td>21-1731</td>\n",
       "      <td>TULSA COUNTY COURT</td>\n",
       "      <td>19-APR-77</td>\n",
       "      <td>77-240</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>KNIGHTON</td>\n",
       "      <td>RAYMOND</td>\n",
       "      <td>J</td>\n",
       "      <td>...</td>\n",
       "      <td>INACTIVE</td>\n",
       "      <td>20-AUG-48</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22574</td>\n",
       "      <td>21-741</td>\n",
       "      <td>TULSA COUNTY COURT</td>\n",
       "      <td>03-MAR-64</td>\n",
       "      <td>20-393</td>\n",
       "      <td>7777.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>STOCKTON</td>\n",
       "      <td>MELVIN</td>\n",
       "      <td>H</td>\n",
       "      <td>...</td>\n",
       "      <td>INACTIVE</td>\n",
       "      <td>07-DEC-45</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>GRAY</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1335214</th>\n",
       "      <td>996281</td>\n",
       "      <td>21-798</td>\n",
       "      <td>UNKNOWN COUNTY COURT</td>\n",
       "      <td>08-JUN-82</td>\n",
       "      <td>9-897</td>\n",
       "      <td>7777.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PILON</td>\n",
       "      <td>GARY</td>\n",
       "      <td>V</td>\n",
       "      <td>...</td>\n",
       "      <td>INACTIVE</td>\n",
       "      <td>18-SEP-53</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>GREEN</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1335215</th>\n",
       "      <td>996281</td>\n",
       "      <td>21-444</td>\n",
       "      <td>WASHINGTON JURISDICTION</td>\n",
       "      <td>29-MAY-80</td>\n",
       "      <td>79-1236</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PILON</td>\n",
       "      <td>GARY</td>\n",
       "      <td>V</td>\n",
       "      <td>...</td>\n",
       "      <td>INACTIVE</td>\n",
       "      <td>18-SEP-53</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>GREEN</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1335216</th>\n",
       "      <td>997494</td>\n",
       "      <td>21-799</td>\n",
       "      <td>CALIFORNIA JURISDICTION</td>\n",
       "      <td>01-FEB-94</td>\n",
       "      <td>94-8235</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AKERS</td>\n",
       "      <td>MARK</td>\n",
       "      <td>W</td>\n",
       "      <td>...</td>\n",
       "      <td>INACTIVE</td>\n",
       "      <td>17-JUL-70</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1335217</th>\n",
       "      <td>997494</td>\n",
       "      <td>21-799</td>\n",
       "      <td>CALIFORNIA JURISDICTION</td>\n",
       "      <td>01-FEB-94</td>\n",
       "      <td>94-8235</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AKERS</td>\n",
       "      <td>MARK</td>\n",
       "      <td>W</td>\n",
       "      <td>...</td>\n",
       "      <td>INACTIVE</td>\n",
       "      <td>17-JUL-70</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1335218</th>\n",
       "      <td>997494</td>\n",
       "      <td>21-799</td>\n",
       "      <td>CALIFORNIA JURISDICTION</td>\n",
       "      <td>01-FEB-94</td>\n",
       "      <td>94-8235</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AKERS</td>\n",
       "      <td>MARK</td>\n",
       "      <td>W</td>\n",
       "      <td>...</td>\n",
       "      <td>INACTIVE</td>\n",
       "      <td>17-JUL-70</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1335102 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         DOC_NUM STATUTE_CODE          SENTENCING_COUNTY    JS_DATE  \\\n",
       "0           8359      21-1720  POTTAWATOMIE COUNTY COURT  13-MAR-13   \n",
       "1          10337     21-701.7         BRYAN COUNTY COURT  13-APR-20   \n",
       "2          10337     21-701.7         BRYAN COUNTY COURT  14-DEC-62   \n",
       "3          20413      21-1731         TULSA COUNTY COURT  19-APR-77   \n",
       "4          22574       21-741         TULSA COUNTY COURT  03-MAR-64   \n",
       "...          ...          ...                        ...        ...   \n",
       "1335214   996281       21-798       UNKNOWN COUNTY COURT  08-JUN-82   \n",
       "1335215   996281       21-444    WASHINGTON JURISDICTION  29-MAY-80   \n",
       "1335216   997494       21-799    CALIFORNIA JURISDICTION  01-FEB-94   \n",
       "1335217   997494       21-799    CALIFORNIA JURISDICTION  01-FEB-94   \n",
       "1335218   997494       21-799    CALIFORNIA JURISDICTION  01-FEB-94   \n",
       "\n",
       "        CRF_NUMBER  INCARCERATED_TERM_IN_YEARS  PROBATION_TERM_IN_YEARS  \\\n",
       "0         2010-300                         NaN                      1.5   \n",
       "1              1-0                      7777.0                      NaN   \n",
       "2         20-15645                         NaN                     40.0   \n",
       "3           77-240                         NaN                      3.0   \n",
       "4           20-393                      7777.0                      NaN   \n",
       "...            ...                         ...                      ...   \n",
       "1335214      9-897                      7777.0                      NaN   \n",
       "1335215    79-1236                        10.0                      NaN   \n",
       "1335216    94-8235                         5.0                      NaN   \n",
       "1335217    94-8235                         3.0                      NaN   \n",
       "1335218    94-8235                         3.0                      NaN   \n",
       "\n",
       "        LAST_NAME FIRST_NAME MIDDLE_NAME  ...  FACILITY BIRTH_DATE SEX   RACE  \\\n",
       "0          MESLES     ARDELL         NaN  ...  INACTIVE  06-APR-87   M    NaN   \n",
       "1          SAWYER      FRANK           N  ...  INACTIVE  01-JAN-00   M  WHITE   \n",
       "2          SAWYER      FRANK           N  ...  INACTIVE  01-JAN-00   M  WHITE   \n",
       "3        KNIGHTON    RAYMOND           J  ...  INACTIVE  20-AUG-48   M  BLACK   \n",
       "4        STOCKTON     MELVIN           H  ...  INACTIVE  07-DEC-45   M  WHITE   \n",
       "...           ...        ...         ...  ...       ...        ...  ..    ...   \n",
       "1335214     PILON       GARY           V  ...  INACTIVE  18-SEP-53   M  WHITE   \n",
       "1335215     PILON       GARY           V  ...  INACTIVE  18-SEP-53   M  WHITE   \n",
       "1335216     AKERS       MARK           W  ...  INACTIVE  17-JUL-70   M  WHITE   \n",
       "1335217     AKERS       MARK           W  ...  INACTIVE  17-JUL-70   M  WHITE   \n",
       "1335218     AKERS       MARK           W  ...  INACTIVE  17-JUL-70   M  WHITE   \n",
       "\n",
       "          HAIR HEIGHT_FT HEIGHT_IN  WEIGHT    EYE    STATUS  \n",
       "0          NaN       NaN       NaN     NaN    NaN  Inactive  \n",
       "1          UNK       5.0       1.0     NaN  BLACK  Inactive  \n",
       "2          UNK       5.0       1.0     NaN  BLACK  Inactive  \n",
       "3        BROWN       NaN       NaN     NaN  BROWN  Inactive  \n",
       "4        BROWN       5.0      10.0   150.0   GRAY  Inactive  \n",
       "...        ...       ...       ...     ...    ...       ...  \n",
       "1335214  BROWN       6.0       2.0   185.0  GREEN  Inactive  \n",
       "1335215  BROWN       6.0       2.0   185.0  GREEN  Inactive  \n",
       "1335216  BROWN       5.0       9.0   178.0   BLUE  Inactive  \n",
       "1335217  BROWN       5.0       9.0   178.0   BLUE  Inactive  \n",
       "1335218  BROWN       5.0       9.0   178.0   BLUE  Inactive  \n",
       "\n",
       "[1335102 rows x 22 columns]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = 'data/Vendor_Profile_Sample_Text.DAT'\n",
    "\n",
    "# uncomment this line to use the full dataset\n",
    "file = 'data/Vendor_Profile_Extract_Text.DAT'\n",
    "\n",
    "names = [\n",
    "    \"DOC_NUM\"       \n",
    "    ,\"LAST_NAME\"     \n",
    "    ,\"FIRST_NAME\"    \n",
    "    ,\"MIDDLE_NAME\"   \n",
    "    ,\"SUFFIX\"        \n",
    "    ,\"LAST_MOVE_DATE\"\n",
    "    ,\"FACILITY\"      \n",
    "    ,\"BIRTH_DATE\"    \n",
    "    ,\"SEX\"           \n",
    "    ,\"RACE\"          \n",
    "    ,\"HAIR\"          \n",
    "    ,\"HEIGHT_FT\"     \n",
    "    ,\"HEIGHT_IN\"     \n",
    "    ,\"WEIGHT\"        \n",
    "    ,\"EYE\"           \n",
    "    ,\"STATUS\"\n",
    "]\n",
    "widths = [\n",
    "    11,\n",
    "    30,\n",
    "    30,\n",
    "    30,\n",
    "    5,\n",
    "    9,\n",
    "    40,\n",
    "    9,\n",
    "    1,\n",
    "    40,\n",
    "    40,\n",
    "    2,\n",
    "    2,\n",
    "    4,\n",
    "    40,10\n",
    "]\n",
    "\n",
    "profile_df = pd.read_fwf(file, \n",
    "    header=None,\n",
    "    widths=widths,\n",
    "    names=names)\n",
    "\n",
    "profile_df\n",
    "\n",
    "from datetime import datetime\n",
    "\n",
    "file = 'data/Vendor_sentence_Sample_Text.dat'\n",
    "\n",
    "# uncomment this line to use the full dataset\n",
    "file = 'data/Vendor_sentence_Extract_Text.dat'\n",
    "\n",
    "\n",
    "names =[\n",
    "    \"DOC_NUM\",                   \n",
    "    \"STATUTE_CODE\",              \n",
    "    \"SENTENCING_COUNTY\",         \n",
    "    \"JS_DATE\",                   \n",
    "    \"CRF_NUMBER\",                \n",
    "    \"INCARCERATED_TERM_IN_YEARS\",\n",
    "    \"PROBATION_TERM_IN_YEARS\"\n",
    "]\n",
    "\n",
    "widths = [\n",
    "    11,\n",
    "    40,\n",
    "    40,\n",
    "    9,\n",
    "    40,\n",
    "    13,\n",
    "    13\n",
    "]\n",
    "\n",
    "sentence_df = pd.read_fwf(file, \n",
    "    header=None,\n",
    "    widths=widths,\n",
    "    names=names)\n",
    "\n",
    "# merge, but keep all sentencing data (profile doc_nums are unique. Sentencing doc_nums are not)\n",
    "combined_data = pd.merge(sentence_df, profile_df, on='DOC_NUM', how='left')\n",
    "# drop invalid data\n",
    "combined_data.dropna(subset=['LAST_NAME'], inplace=True)  \n",
    "combined_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "scrolled": false
   },
   "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_NUM</th>\n",
       "      <th>STATUTE_CODE</th>\n",
       "      <th>SENTENCING_COUNTY</th>\n",
       "      <th>JS_DATE</th>\n",
       "      <th>CRF_NUMBER</th>\n",
       "      <th>INCARCERATED_TERM_IN_YEARS</th>\n",
       "      <th>PROBATION_TERM_IN_YEARS</th>\n",
       "      <th>LAST_NAME</th>\n",
       "      <th>FIRST_NAME</th>\n",
       "      <th>MIDDLE_NAME</th>\n",
       "      <th>...</th>\n",
       "      <th>RACE</th>\n",
       "      <th>HAIR</th>\n",
       "      <th>HEIGHT_FT</th>\n",
       "      <th>HEIGHT_IN</th>\n",
       "      <th>WEIGHT</th>\n",
       "      <th>EYE</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>oscn_county</th>\n",
       "      <th>oscn_year</th>\n",
       "      <th>oscn_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8359</td>\n",
       "      <td>21-1720</td>\n",
       "      <td>POTTAWATOMIE COUNTY COURT</td>\n",
       "      <td>13-MAR-13</td>\n",
       "      <td>2010-300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.5</td>\n",
       "      <td>MESLES</td>\n",
       "      <td>ARDELL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>Inactive</td>\n",
       "      <td>pottawatomie</td>\n",
       "      <td>2010</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10337</td>\n",
       "      <td>21-701.7</td>\n",
       "      <td>BRYAN COUNTY COURT</td>\n",
       "      <td>13-APR-20</td>\n",
       "      <td>1-0</td>\n",
       "      <td>7777.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SAWYER</td>\n",
       "      <td>FRANK</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>UNK</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>bryan</td>\n",
       "      <td>2001</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10337</td>\n",
       "      <td>21-701.7</td>\n",
       "      <td>BRYAN COUNTY COURT</td>\n",
       "      <td>14-DEC-62</td>\n",
       "      <td>20-15645</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.0</td>\n",
       "      <td>SAWYER</td>\n",
       "      <td>FRANK</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>UNK</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>bryan</td>\n",
       "      <td>2020</td>\n",
       "      <td>15645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20413</td>\n",
       "      <td>21-1731</td>\n",
       "      <td>TULSA COUNTY COURT</td>\n",
       "      <td>19-APR-77</td>\n",
       "      <td>77-240</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>KNIGHTON</td>\n",
       "      <td>RAYMOND</td>\n",
       "      <td>J</td>\n",
       "      <td>...</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>tulsa</td>\n",
       "      <td>1977</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22574</td>\n",
       "      <td>21-741</td>\n",
       "      <td>TULSA COUNTY COURT</td>\n",
       "      <td>03-MAR-64</td>\n",
       "      <td>20-393</td>\n",
       "      <td>7777.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>STOCKTON</td>\n",
       "      <td>MELVIN</td>\n",
       "      <td>H</td>\n",
       "      <td>...</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>GRAY</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>tulsa</td>\n",
       "      <td>2020</td>\n",
       "      <td>393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1335036</th>\n",
       "      <td>939190</td>\n",
       "      <td>63-2-407</td>\n",
       "      <td>JEFFERSON COUNTY COURT</td>\n",
       "      <td>24-AUG-89</td>\n",
       "      <td>88-1835</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TURNER</td>\n",
       "      <td>ALICE</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>jefferson</td>\n",
       "      <td>1988</td>\n",
       "      <td>1835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1335037</th>\n",
       "      <td>939190</td>\n",
       "      <td>63-2-407</td>\n",
       "      <td>JEFFERSON COUNTY COURT</td>\n",
       "      <td>24-AUG-89</td>\n",
       "      <td>88-1835</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TURNER</td>\n",
       "      <td>ALICE</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>jefferson</td>\n",
       "      <td>1988</td>\n",
       "      <td>1835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1335188</th>\n",
       "      <td>990184</td>\n",
       "      <td>21-798</td>\n",
       "      <td>WASHINGTON COUNTY COURT</td>\n",
       "      <td>16-MAY-84</td>\n",
       "      <td>84-6869</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MOLINA</td>\n",
       "      <td>RUDOLFO</td>\n",
       "      <td>V</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>washington</td>\n",
       "      <td>1984</td>\n",
       "      <td>6869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1335189</th>\n",
       "      <td>990184</td>\n",
       "      <td>21-645</td>\n",
       "      <td>WASHINGTON COUNTY COURT</td>\n",
       "      <td>16-MAY-84</td>\n",
       "      <td>84-1686</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MOLINA</td>\n",
       "      <td>RUDOLFO</td>\n",
       "      <td>V</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>washington</td>\n",
       "      <td>1984</td>\n",
       "      <td>1686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1335190</th>\n",
       "      <td>990184</td>\n",
       "      <td>21-741</td>\n",
       "      <td>WASHINGTON COUNTY COURT</td>\n",
       "      <td>16-MAY-84</td>\n",
       "      <td>84-1686</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MOLINA</td>\n",
       "      <td>RUDOLFO</td>\n",
       "      <td>V</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>washington</td>\n",
       "      <td>1984</td>\n",
       "      <td>1686</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1279993 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         DOC_NUM STATUTE_CODE          SENTENCING_COUNTY    JS_DATE  \\\n",
       "0           8359      21-1720  POTTAWATOMIE COUNTY COURT  13-MAR-13   \n",
       "1          10337     21-701.7         BRYAN COUNTY COURT  13-APR-20   \n",
       "2          10337     21-701.7         BRYAN COUNTY COURT  14-DEC-62   \n",
       "3          20413      21-1731         TULSA COUNTY COURT  19-APR-77   \n",
       "4          22574       21-741         TULSA COUNTY COURT  03-MAR-64   \n",
       "...          ...          ...                        ...        ...   \n",
       "1335036   939190     63-2-407     JEFFERSON COUNTY COURT  24-AUG-89   \n",
       "1335037   939190     63-2-407     JEFFERSON COUNTY COURT  24-AUG-89   \n",
       "1335188   990184       21-798    WASHINGTON COUNTY COURT  16-MAY-84   \n",
       "1335189   990184       21-645    WASHINGTON COUNTY COURT  16-MAY-84   \n",
       "1335190   990184       21-741    WASHINGTON COUNTY COURT  16-MAY-84   \n",
       "\n",
       "        CRF_NUMBER  INCARCERATED_TERM_IN_YEARS  PROBATION_TERM_IN_YEARS  \\\n",
       "0         2010-300                         NaN                      1.5   \n",
       "1              1-0                      7777.0                      NaN   \n",
       "2         20-15645                         NaN                     40.0   \n",
       "3           77-240                         NaN                      3.0   \n",
       "4           20-393                      7777.0                      NaN   \n",
       "...            ...                         ...                      ...   \n",
       "1335036    88-1835                         1.0                      NaN   \n",
       "1335037    88-1835                         1.0                      NaN   \n",
       "1335188    84-6869                        20.0                      NaN   \n",
       "1335189    84-1686                        10.0                      NaN   \n",
       "1335190    84-1686                        10.0                      NaN   \n",
       "\n",
       "        LAST_NAME FIRST_NAME MIDDLE_NAME  ...   RACE   HAIR HEIGHT_FT  \\\n",
       "0          MESLES     ARDELL         NaN  ...    NaN    NaN       NaN   \n",
       "1          SAWYER      FRANK           N  ...  WHITE    UNK       5.0   \n",
       "2          SAWYER      FRANK           N  ...  WHITE    UNK       5.0   \n",
       "3        KNIGHTON    RAYMOND           J  ...  BLACK  BROWN       NaN   \n",
       "4        STOCKTON     MELVIN           H  ...  WHITE  BROWN       5.0   \n",
       "...           ...        ...         ...  ...    ...    ...       ...   \n",
       "1335036    TURNER      ALICE           M  ...  WHITE  BLACK       5.0   \n",
       "1335037    TURNER      ALICE           M  ...  WHITE  BLACK       5.0   \n",
       "1335188    MOLINA    RUDOLFO           V  ...      M  BLACK       6.0   \n",
       "1335189    MOLINA    RUDOLFO           V  ...      M  BLACK       6.0   \n",
       "1335190    MOLINA    RUDOLFO           V  ...      M  BLACK       6.0   \n",
       "\n",
       "        HEIGHT_IN WEIGHT    EYE    STATUS   oscn_county  oscn_year  \\\n",
       "0             NaN    NaN    NaN  Inactive  pottawatomie       2010   \n",
       "1             1.0    NaN  BLACK  Inactive         bryan       2001   \n",
       "2             1.0    NaN  BLACK  Inactive         bryan       2020   \n",
       "3             NaN    NaN  BROWN  Inactive         tulsa       1977   \n",
       "4            10.0  150.0   GRAY  Inactive         tulsa       2020   \n",
       "...           ...    ...    ...       ...           ...        ...   \n",
       "1335036       3.0  175.0  BROWN  Inactive     jefferson       1988   \n",
       "1335037       3.0  175.0  BROWN  Inactive     jefferson       1988   \n",
       "1335188       1.0  185.0  BROWN  Inactive    washington       1984   \n",
       "1335189       1.0  185.0  BROWN  Inactive    washington       1984   \n",
       "1335190       1.0  185.0  BROWN  Inactive    washington       1984   \n",
       "\n",
       "         oscn_number  \n",
       "0                300  \n",
       "1                  0  \n",
       "2              15645  \n",
       "3                240  \n",
       "4                393  \n",
       "...              ...  \n",
       "1335036         1835  \n",
       "1335037         1835  \n",
       "1335188         6869  \n",
       "1335189         1686  \n",
       "1335190         1686  \n",
       "\n",
       "[1279993 rows x 25 columns]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import oscn\n",
    "import re\n",
    "\n",
    "\n",
    "counties = oscn.counties\n",
    "debug = False  # make True to print bad data that will be dropped\n",
    "\n",
    "def parse_county(dataCounty):\n",
    "    if \"JURISDICTION\" in dataCounty:\n",
    "        # should be mostly out of state cases\n",
    "        if debug: print(dataCounty)\n",
    "        return None\n",
    "    county = dataCounty.split(\" \")[0].lower()\n",
    "    if county in counties:\n",
    "        return county\n",
    "    if debug: print(dataCounty)\n",
    "    return None\n",
    "\n",
    "def parse_year(crf_number):\n",
    "    split_number = re.split(\"-| \", crf_number)\n",
    "    for i in range(2):\n",
    "        try:\n",
    "            maybe_year = int(split_number[i])\n",
    "            if (maybe_year < 2021 and maybe_year > 1900):\n",
    "                return split_number[i]\n",
    "            if (maybe_year < 2021 and maybe_year > 1900):\n",
    "                return split_number[i]\n",
    "            if (maybe_year < 100 and maybe_year > 20):\n",
    "                return \"19\" + split_number[i]\n",
    "            if (maybe_year < 10 and maybe_year >= 0):\n",
    "                return \"200\" + split_number[i]\n",
    "            if (maybe_year <= 20 and maybe_year >= 10):\n",
    "                return \"20\" + split_number[i]\n",
    "        except:\n",
    "            continue\n",
    "    if debug: print(crf_number)\n",
    "    return None\n",
    "    \n",
    "def parse_number(crf_number):\n",
    "    # this could be cleaned up more. Sometimes has letters in it (invalid) and other things wrong\n",
    "    split_number = crf_number.split(\"-\")\n",
    "    return split_number[len(split_number)-1]\n",
    "\n",
    "\n",
    "combined_data['oscn_county'] = combined_data['SENTENCING_COUNTY'].apply(parse_county)\n",
    "combined_data.dropna(subset=['oscn_county'], inplace=True)  \n",
    "combined_data['oscn_year'] = combined_data['CRF_NUMBER'].apply(parse_year)\n",
    "combined_data.dropna(subset=['oscn_year'], inplace=True)  \n",
    "combined_data['oscn_number'] = combined_data['CRF_NUMBER'].apply(parse_number)\n",
    "combined_data.dropna(subset=['oscn_number'], inplace=True)  \n",
    "\n",
    "\n",
    "combined_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "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_NUM</th>\n",
       "      <th>STATUTE_CODE</th>\n",
       "      <th>SENTENCING_COUNTY</th>\n",
       "      <th>JS_DATE</th>\n",
       "      <th>CRF_NUMBER</th>\n",
       "      <th>INCARCERATED_TERM_IN_YEARS</th>\n",
       "      <th>PROBATION_TERM_IN_YEARS</th>\n",
       "      <th>LAST_NAME</th>\n",
       "      <th>FIRST_NAME</th>\n",
       "      <th>MIDDLE_NAME</th>\n",
       "      <th>...</th>\n",
       "      <th>RACE</th>\n",
       "      <th>HAIR</th>\n",
       "      <th>HEIGHT_FT</th>\n",
       "      <th>HEIGHT_IN</th>\n",
       "      <th>WEIGHT</th>\n",
       "      <th>EYE</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>oscn_county</th>\n",
       "      <th>oscn_year</th>\n",
       "      <th>oscn_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1279959</th>\n",
       "      <td>771746</td>\n",
       "      <td>21-1713</td>\n",
       "      <td>CADDO COUNTY COURT</td>\n",
       "      <td>25-MAY-17</td>\n",
       "      <td>2015-289</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.00</td>\n",
       "      <td>KENEMER</td>\n",
       "      <td>CODY</td>\n",
       "      <td>MARSHALL</td>\n",
       "      <td>...</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>218.0</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>Active</td>\n",
       "      <td>caddo</td>\n",
       "      <td>2015</td>\n",
       "      <td>289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638685</th>\n",
       "      <td>384982</td>\n",
       "      <td>63-2-401</td>\n",
       "      <td>PITTSBURG COUNTY COURT</td>\n",
       "      <td>07-MAY-15</td>\n",
       "      <td>CF-2014-707</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.00</td>\n",
       "      <td>CRAIG</td>\n",
       "      <td>STEVEN</td>\n",
       "      <td>R</td>\n",
       "      <td>...</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>231.0</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>Active</td>\n",
       "      <td>pittsburg</td>\n",
       "      <td>2014</td>\n",
       "      <td>707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>410937</th>\n",
       "      <td>232755</td>\n",
       "      <td>63-2-405</td>\n",
       "      <td>OKLAHOMA COUNTY COURT</td>\n",
       "      <td>21-FEB-96</td>\n",
       "      <td>95-203</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JOHNSON</td>\n",
       "      <td>DAVID</td>\n",
       "      <td>I</td>\n",
       "      <td>...</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187.0</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Active</td>\n",
       "      <td>oklahoma</td>\n",
       "      <td>1995</td>\n",
       "      <td>203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>694338</th>\n",
       "      <td>414028</td>\n",
       "      <td>21-1435</td>\n",
       "      <td>MCCURTAIN COUNTY COURT</td>\n",
       "      <td>07-JAN-02</td>\n",
       "      <td>2001-285</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MAGUIRE</td>\n",
       "      <td>STEVEN</td>\n",
       "      <td>BRANDON</td>\n",
       "      <td>...</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>209.0</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>mccurtain</td>\n",
       "      <td>2001</td>\n",
       "      <td>285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439780</th>\n",
       "      <td>240295</td>\n",
       "      <td>63-2-402</td>\n",
       "      <td>OKLAHOMA COUNTY COURT</td>\n",
       "      <td>16-APR-04</td>\n",
       "      <td>2004-602</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KUYKENDALL</td>\n",
       "      <td>ROY</td>\n",
       "      <td>GEORGE</td>\n",
       "      <td>...</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>oklahoma</td>\n",
       "      <td>2004</td>\n",
       "      <td>602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327658</th>\n",
       "      <td>209287</td>\n",
       "      <td>63-2-401.G</td>\n",
       "      <td>CREEK COUNTY COURT</td>\n",
       "      <td>03-SEP-03</td>\n",
       "      <td>2003-118</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LITTLE</td>\n",
       "      <td>JEREMY</td>\n",
       "      <td>SCOTT</td>\n",
       "      <td>...</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Active</td>\n",
       "      <td>creek</td>\n",
       "      <td>2003</td>\n",
       "      <td>118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>774168</th>\n",
       "      <td>456779</td>\n",
       "      <td>21-1592</td>\n",
       "      <td>COMANCHE COUNTY COURT</td>\n",
       "      <td>18-SEP-03</td>\n",
       "      <td>2003-388</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.55</td>\n",
       "      <td>CHAO</td>\n",
       "      <td>MARILYN</td>\n",
       "      <td>MONICA</td>\n",
       "      <td>...</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>202.0</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>comanche</td>\n",
       "      <td>2003</td>\n",
       "      <td>388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499441</th>\n",
       "      <td>256012</td>\n",
       "      <td>63-2-407</td>\n",
       "      <td>WAGONER COUNTY COURT</td>\n",
       "      <td>21-APR-97</td>\n",
       "      <td>1996-397</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.00</td>\n",
       "      <td>HOLLEY</td>\n",
       "      <td>TERRI</td>\n",
       "      <td>E</td>\n",
       "      <td>...</td>\n",
       "      <td>NATIVE AMERICAN</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>wagoner</td>\n",
       "      <td>1996</td>\n",
       "      <td>397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>894502</th>\n",
       "      <td>538835</td>\n",
       "      <td>21-1713</td>\n",
       "      <td>COTTON COUNTY COURT</td>\n",
       "      <td>10-FEB-06</td>\n",
       "      <td>2004-62D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.00</td>\n",
       "      <td>DENHAM</td>\n",
       "      <td>KEVIN</td>\n",
       "      <td>S</td>\n",
       "      <td>...</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>cotton</td>\n",
       "      <td>2004</td>\n",
       "      <td>62D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1159895</th>\n",
       "      <td>695198</td>\n",
       "      <td>21-650.9</td>\n",
       "      <td>TULSA COUNTY COURT</td>\n",
       "      <td>21-MAR-18</td>\n",
       "      <td>2018-767</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MCGEE</td>\n",
       "      <td>ANDREW</td>\n",
       "      <td>STEVEN</td>\n",
       "      <td>...</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>Active</td>\n",
       "      <td>tulsa</td>\n",
       "      <td>2018</td>\n",
       "      <td>767</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         DOC_NUM STATUTE_CODE       SENTENCING_COUNTY    JS_DATE   CRF_NUMBER  \\\n",
       "1279959   771746      21-1713      CADDO COUNTY COURT  25-MAY-17     2015-289   \n",
       "638685    384982     63-2-401  PITTSBURG COUNTY COURT  07-MAY-15  CF-2014-707   \n",
       "410937    232755     63-2-405   OKLAHOMA COUNTY COURT  21-FEB-96       95-203   \n",
       "694338    414028      21-1435  MCCURTAIN COUNTY COURT  07-JAN-02     2001-285   \n",
       "439780    240295     63-2-402   OKLAHOMA COUNTY COURT  16-APR-04     2004-602   \n",
       "...          ...          ...                     ...        ...          ...   \n",
       "327658    209287   63-2-401.G      CREEK COUNTY COURT  03-SEP-03     2003-118   \n",
       "774168    456779      21-1592   COMANCHE COUNTY COURT  18-SEP-03     2003-388   \n",
       "499441    256012     63-2-407    WAGONER COUNTY COURT  21-APR-97     1996-397   \n",
       "894502    538835      21-1713     COTTON COUNTY COURT  10-FEB-06     2004-62D   \n",
       "1159895   695198     21-650.9      TULSA COUNTY COURT  21-MAR-18     2018-767   \n",
       "\n",
       "         INCARCERATED_TERM_IN_YEARS  PROBATION_TERM_IN_YEARS   LAST_NAME  \\\n",
       "1279959                         NaN                     5.00     KENEMER   \n",
       "638685                          NaN                    20.00       CRAIG   \n",
       "410937                          1.0                      NaN     JOHNSON   \n",
       "694338                          3.0                      NaN     MAGUIRE   \n",
       "439780                          NaN                      NaN  KUYKENDALL   \n",
       "...                             ...                      ...         ...   \n",
       "327658                         12.0                      NaN      LITTLE   \n",
       "774168                          NaN                     7.55        CHAO   \n",
       "499441                          NaN                     3.00      HOLLEY   \n",
       "894502                          NaN                     5.00      DENHAM   \n",
       "1159895                         6.0                      NaN       MCGEE   \n",
       "\n",
       "        FIRST_NAME MIDDLE_NAME  ...             RACE   HAIR HEIGHT_FT  \\\n",
       "1279959       CODY    MARSHALL  ...            WHITE  BROWN       5.0   \n",
       "638685      STEVEN           R  ...            WHITE  BROWN       5.0   \n",
       "410937       DAVID           I  ...            BLACK  BLACK       6.0   \n",
       "694338      STEVEN     BRANDON  ...            WHITE  BROWN       6.0   \n",
       "439780         ROY      GEORGE  ...            WHITE  BROWN       5.0   \n",
       "...            ...         ...  ...              ...    ...       ...   \n",
       "327658      JEREMY       SCOTT  ...            WHITE  BROWN       6.0   \n",
       "774168     MARILYN      MONICA  ...         HISPANIC  BROWN       5.0   \n",
       "499441       TERRI           E  ...  NATIVE AMERICAN  BROWN       5.0   \n",
       "894502       KEVIN           S  ...            WHITE  BROWN       5.0   \n",
       "1159895     ANDREW      STEVEN  ...            WHITE  BLACK       5.0   \n",
       "\n",
       "        HEIGHT_IN WEIGHT    EYE    STATUS  oscn_county  oscn_year  oscn_number  \n",
       "1279959      11.0  218.0   BLUE    Active        caddo       2015          289  \n",
       "638685       10.0  231.0   BLUE    Active    pittsburg       2014          707  \n",
       "410937        0.0  187.0  BROWN    Active     oklahoma       1995          203  \n",
       "694338        0.0  209.0  BROWN  Inactive    mccurtain       2001          285  \n",
       "439780        5.0  200.0   BLUE  Inactive     oklahoma       2004          602  \n",
       "...           ...    ...    ...       ...          ...        ...          ...  \n",
       "327658        0.0  185.0  BROWN    Active        creek       2003          118  \n",
       "774168        8.0  202.0  BROWN  Inactive     comanche       2003          388  \n",
       "499441        6.0  200.0  BROWN  Inactive      wagoner       1996          397  \n",
       "894502       10.0  180.0   BLUE  Inactive       cotton       2004          62D  \n",
       "1159895      10.0  279.0   BLUE    Active        tulsa       2018          767  \n",
       "\n",
       "[1000 rows x 25 columns]"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_df = combined_data.sample(1000)\n",
    "sample_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n",
      "[]\n",
      "[]\n",
      "[]\n",
      "[]\n",
      "oklahoma-CF-2014-661R\n",
      "delaware-CF-2014-18A\n",
      "[]\n",
      "[]\n",
      "oklahoma-CF-2011-6448R\n",
      "oklahoma-CF-2008-3357R\n",
      "craig-CF-2000-13A\n",
      "[]\n",
      "[]\n",
      "carter-CF-2015-83B\n",
      "rogers-CF-2019-135R\n",
      "delaware-CF-2005-307R\n",
      "oklahoma-CF-2013-3087R\n",
      "oklahoma-CF-2000-636R\n",
      "[]\n",
      "pittsburg-CF-2017-452R\n",
      "[]\n",
      "[]\n",
      "[]\n",
      "[]\n",
      "tulsa-CF-2008-1077R\n",
      "[]\n",
      "[]\n",
      "oklahoma-CF-2011-5064R\n",
      "[]\n",
      "[]\n",
      "tulsa-CF-1983-732 CT 2\n",
      "[]\n",
      "marshall-CF-2014-101R\n",
      "woodward-CF-2009-411R\n",
      "oklahoma-CF-2013-132R\n",
      "oklahoma-CF-2009-2920R\n",
      "okmulgee-CF-2008-110R\n",
      "oklahoma-CF-2017-5627R\n",
      "[]\n",
      "[]\n",
      "osage-CF-2007-313C\n",
      "[]\n",
      "[]\n",
      "payne-CF-2010-267R\n",
      "cleveland-CF-2012-1759R\n",
      "[]\n",
      "[]\n",
      "[]\n",
      "delaware-CF-2017-138R\n",
      "[]\n",
      "carter-CF-2019-372A\n",
      "[]\n",
      "cleveland-CF-2001-1641R\n",
      "oklahoma-CF-2013-1261R\n",
      "cotton-CF-2004-62D\n"
     ]
    },
    {
     "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_NUM</th>\n",
       "      <th>STATUTE_CODE</th>\n",
       "      <th>SENTENCING_COUNTY</th>\n",
       "      <th>JS_DATE</th>\n",
       "      <th>CRF_NUMBER</th>\n",
       "      <th>INCARCERATED_TERM_IN_YEARS</th>\n",
       "      <th>PROBATION_TERM_IN_YEARS</th>\n",
       "      <th>LAST_NAME</th>\n",
       "      <th>FIRST_NAME</th>\n",
       "      <th>MIDDLE_NAME</th>\n",
       "      <th>...</th>\n",
       "      <th>HEIGHT_IN</th>\n",
       "      <th>WEIGHT</th>\n",
       "      <th>EYE</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>oscn_county</th>\n",
       "      <th>oscn_year</th>\n",
       "      <th>oscn_number</th>\n",
       "      <th>defendants</th>\n",
       "      <th>defendant_match</th>\n",
       "      <th>judge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1279959</th>\n",
       "      <td>771746</td>\n",
       "      <td>21-1713</td>\n",
       "      <td>CADDO COUNTY COURT</td>\n",
       "      <td>25-MAY-17</td>\n",
       "      <td>2015-289</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.00</td>\n",
       "      <td>KENEMER</td>\n",
       "      <td>CODY</td>\n",
       "      <td>MARSHALL</td>\n",
       "      <td>...</td>\n",
       "      <td>11.0</td>\n",
       "      <td>218.0</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>Active</td>\n",
       "      <td>caddo</td>\n",
       "      <td>2015</td>\n",
       "      <td>289</td>\n",
       "      <td>[KENEMER, CODY MARSHALL]</td>\n",
       "      <td>True</td>\n",
       "      <td>STEPHENS, DAVID A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638685</th>\n",
       "      <td>384982</td>\n",
       "      <td>63-2-401</td>\n",
       "      <td>PITTSBURG COUNTY COURT</td>\n",
       "      <td>07-MAY-15</td>\n",
       "      <td>CF-2014-707</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.00</td>\n",
       "      <td>CRAIG</td>\n",
       "      <td>STEVEN</td>\n",
       "      <td>R</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>231.0</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>Active</td>\n",
       "      <td>pittsburg</td>\n",
       "      <td>2014</td>\n",
       "      <td>707</td>\n",
       "      <td>[CRAIG, STEVEN R. II]</td>\n",
       "      <td>True</td>\n",
       "      <td>MILLS, TIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>410937</th>\n",
       "      <td>232755</td>\n",
       "      <td>63-2-405</td>\n",
       "      <td>OKLAHOMA COUNTY COURT</td>\n",
       "      <td>21-FEB-96</td>\n",
       "      <td>95-203</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JOHNSON</td>\n",
       "      <td>DAVID</td>\n",
       "      <td>I</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187.0</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Active</td>\n",
       "      <td>oklahoma</td>\n",
       "      <td>1995</td>\n",
       "      <td>203</td>\n",
       "      <td>[ISSAC, DAVID]</td>\n",
       "      <td>False</td>\n",
       "      <td>CASWELL, SUSAN P</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>694338</th>\n",
       "      <td>414028</td>\n",
       "      <td>21-1435</td>\n",
       "      <td>MCCURTAIN COUNTY COURT</td>\n",
       "      <td>07-JAN-02</td>\n",
       "      <td>2001-285</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MAGUIRE</td>\n",
       "      <td>STEVEN</td>\n",
       "      <td>BRANDON</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>209.0</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>mccurtain</td>\n",
       "      <td>2001</td>\n",
       "      <td>285</td>\n",
       "      <td>[MAGUIRE, STEVEN BRANDON]</td>\n",
       "      <td>True</td>\n",
       "      <td>UNASSIGNED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439780</th>\n",
       "      <td>240295</td>\n",
       "      <td>63-2-402</td>\n",
       "      <td>OKLAHOMA COUNTY COURT</td>\n",
       "      <td>16-APR-04</td>\n",
       "      <td>2004-602</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KUYKENDALL</td>\n",
       "      <td>ROY</td>\n",
       "      <td>GEORGE</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>oklahoma</td>\n",
       "      <td>2004</td>\n",
       "      <td>602</td>\n",
       "      <td>[Kuykendall, Roy G]</td>\n",
       "      <td>True</td>\n",
       "      <td>BRAGG, SUSAN W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327658</th>\n",
       "      <td>209287</td>\n",
       "      <td>63-2-401.G</td>\n",
       "      <td>CREEK COUNTY COURT</td>\n",
       "      <td>03-SEP-03</td>\n",
       "      <td>2003-118</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LITTLE</td>\n",
       "      <td>JEREMY</td>\n",
       "      <td>SCOTT</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Active</td>\n",
       "      <td>creek</td>\n",
       "      <td>2003</td>\n",
       "      <td>118</td>\n",
       "      <td>[WIECK, KEVIN CROCKER]</td>\n",
       "      <td>False</td>\n",
       "      <td>VASSAR, JOE SAM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>774168</th>\n",
       "      <td>456779</td>\n",
       "      <td>21-1592</td>\n",
       "      <td>COMANCHE COUNTY COURT</td>\n",
       "      <td>18-SEP-03</td>\n",
       "      <td>2003-388</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.55</td>\n",
       "      <td>CHAO</td>\n",
       "      <td>MARILYN</td>\n",
       "      <td>MONICA</td>\n",
       "      <td>...</td>\n",
       "      <td>8.0</td>\n",
       "      <td>202.0</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>comanche</td>\n",
       "      <td>2003</td>\n",
       "      <td>388</td>\n",
       "      <td>[CHAO, MARILYN MONICA]</td>\n",
       "      <td>True</td>\n",
       "      <td>MCCALL, C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499441</th>\n",
       "      <td>256012</td>\n",
       "      <td>63-2-407</td>\n",
       "      <td>WAGONER COUNTY COURT</td>\n",
       "      <td>21-APR-97</td>\n",
       "      <td>1996-397</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.00</td>\n",
       "      <td>HOLLEY</td>\n",
       "      <td>TERRI</td>\n",
       "      <td>E</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>wagoner</td>\n",
       "      <td>1996</td>\n",
       "      <td>397</td>\n",
       "      <td>[HOLLEY, TERRI ELIZABETH]</td>\n",
       "      <td>True</td>\n",
       "      <td>SEWELL, BRUCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>894502</th>\n",
       "      <td>538835</td>\n",
       "      <td>21-1713</td>\n",
       "      <td>COTTON COUNTY COURT</td>\n",
       "      <td>10-FEB-06</td>\n",
       "      <td>2004-62D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.00</td>\n",
       "      <td>DENHAM</td>\n",
       "      <td>KEVIN</td>\n",
       "      <td>S</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>cotton</td>\n",
       "      <td>2004</td>\n",
       "      <td>62D</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1159895</th>\n",
       "      <td>695198</td>\n",
       "      <td>21-650.9</td>\n",
       "      <td>TULSA COUNTY COURT</td>\n",
       "      <td>21-MAR-18</td>\n",
       "      <td>2018-767</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MCGEE</td>\n",
       "      <td>ANDREW</td>\n",
       "      <td>STEVEN</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>Active</td>\n",
       "      <td>tulsa</td>\n",
       "      <td>2018</td>\n",
       "      <td>767</td>\n",
       "      <td>[McGEE, ANDREW STEVEN]</td>\n",
       "      <td>True</td>\n",
       "      <td>B, CF DOCKET</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         DOC_NUM STATUTE_CODE       SENTENCING_COUNTY    JS_DATE   CRF_NUMBER  \\\n",
       "1279959   771746      21-1713      CADDO COUNTY COURT  25-MAY-17     2015-289   \n",
       "638685    384982     63-2-401  PITTSBURG COUNTY COURT  07-MAY-15  CF-2014-707   \n",
       "410937    232755     63-2-405   OKLAHOMA COUNTY COURT  21-FEB-96       95-203   \n",
       "694338    414028      21-1435  MCCURTAIN COUNTY COURT  07-JAN-02     2001-285   \n",
       "439780    240295     63-2-402   OKLAHOMA COUNTY COURT  16-APR-04     2004-602   \n",
       "...          ...          ...                     ...        ...          ...   \n",
       "327658    209287   63-2-401.G      CREEK COUNTY COURT  03-SEP-03     2003-118   \n",
       "774168    456779      21-1592   COMANCHE COUNTY COURT  18-SEP-03     2003-388   \n",
       "499441    256012     63-2-407    WAGONER COUNTY COURT  21-APR-97     1996-397   \n",
       "894502    538835      21-1713     COTTON COUNTY COURT  10-FEB-06     2004-62D   \n",
       "1159895   695198     21-650.9      TULSA COUNTY COURT  21-MAR-18     2018-767   \n",
       "\n",
       "         INCARCERATED_TERM_IN_YEARS  PROBATION_TERM_IN_YEARS   LAST_NAME  \\\n",
       "1279959                         NaN                     5.00     KENEMER   \n",
       "638685                          NaN                    20.00       CRAIG   \n",
       "410937                          1.0                      NaN     JOHNSON   \n",
       "694338                          3.0                      NaN     MAGUIRE   \n",
       "439780                          NaN                      NaN  KUYKENDALL   \n",
       "...                             ...                      ...         ...   \n",
       "327658                         12.0                      NaN      LITTLE   \n",
       "774168                          NaN                     7.55        CHAO   \n",
       "499441                          NaN                     3.00      HOLLEY   \n",
       "894502                          NaN                     5.00      DENHAM   \n",
       "1159895                         6.0                      NaN       MCGEE   \n",
       "\n",
       "        FIRST_NAME MIDDLE_NAME  ... HEIGHT_IN WEIGHT    EYE    STATUS  \\\n",
       "1279959       CODY    MARSHALL  ...      11.0  218.0   BLUE    Active   \n",
       "638685      STEVEN           R  ...      10.0  231.0   BLUE    Active   \n",
       "410937       DAVID           I  ...       0.0  187.0  BROWN    Active   \n",
       "694338      STEVEN     BRANDON  ...       0.0  209.0  BROWN  Inactive   \n",
       "439780         ROY      GEORGE  ...       5.0  200.0   BLUE  Inactive   \n",
       "...            ...         ...  ...       ...    ...    ...       ...   \n",
       "327658      JEREMY       SCOTT  ...       0.0  185.0  BROWN    Active   \n",
       "774168     MARILYN      MONICA  ...       8.0  202.0  BROWN  Inactive   \n",
       "499441       TERRI           E  ...       6.0  200.0  BROWN  Inactive   \n",
       "894502       KEVIN           S  ...      10.0  180.0   BLUE  Inactive   \n",
       "1159895     ANDREW      STEVEN  ...      10.0  279.0   BLUE    Active   \n",
       "\n",
       "        oscn_county oscn_year oscn_number                 defendants  \\\n",
       "1279959       caddo      2015         289   [KENEMER, CODY MARSHALL]   \n",
       "638685    pittsburg      2014         707      [CRAIG, STEVEN R. II]   \n",
       "410937     oklahoma      1995         203             [ISSAC, DAVID]   \n",
       "694338    mccurtain      2001         285  [MAGUIRE, STEVEN BRANDON]   \n",
       "439780     oklahoma      2004         602        [Kuykendall, Roy G]   \n",
       "...             ...       ...         ...                        ...   \n",
       "327658        creek      2003         118     [WIECK, KEVIN CROCKER]   \n",
       "774168     comanche      2003         388     [CHAO, MARILYN MONICA]   \n",
       "499441      wagoner      1996         397  [HOLLEY, TERRI ELIZABETH]   \n",
       "894502       cotton      2004         62D                       None   \n",
       "1159895       tulsa      2018         767     [McGEE, ANDREW STEVEN]   \n",
       "\n",
       "         defendant_match              judge  \n",
       "1279959             True  STEPHENS, DAVID A  \n",
       "638685              True         MILLS, TIM  \n",
       "410937             False   CASWELL, SUSAN P  \n",
       "694338              True         UNASSIGNED  \n",
       "439780              True     BRAGG, SUSAN W  \n",
       "...                  ...                ...  \n",
       "327658             False    VASSAR, JOE SAM  \n",
       "774168              True          MCCALL, C  \n",
       "499441              True      SEWELL, BRUCE  \n",
       "894502              None               None  \n",
       "1159895             True       B, CF DOCKET  \n",
       "\n",
       "[1000 rows x 28 columns]"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_oscn_judges(oscn_county, oscn_year, oscn_number, last_name):\n",
    "    try:\n",
    "        case = oscn.request.Case(f'{oscn_county}-CF-{oscn_year}-{oscn_number}')\n",
    "        if case.valid:\n",
    "            defendants = find_defendants(case)\n",
    "            if defendants:\n",
    "                match = name_match(defendants, last_name)\n",
    "                return (defendants, match, case.judge)\n",
    "        if debug: print(f'{oscn_county}-CF-{oscn_year}-{oscn_number}')\n",
    "        return (None, None, None)\n",
    "    except:\n",
    "        if debug: print(f'{oscn_county}-CF-{oscn_year}-{oscn_number}')\n",
    "        return (None, None, None)\n",
    "\n",
    "def find_defendants(case):\n",
    "    if case.valid:\n",
    "        defendants = []\n",
    "        for party in case.parties:\n",
    "            if party[\"type\"] == \"Defendant\":\n",
    "                defendants.append(party[\"name\"])\n",
    "        if defendants == []:\n",
    "            return None\n",
    "        return defendants\n",
    "    return None\n",
    "\n",
    "def name_match(defendants, last_name):\n",
    "    for defendant in defendants:\n",
    "        defendant_last_name = defendant.split(\",\")[0]\n",
    "        if last_name.lower() == defendant_last_name.lower():\n",
    "            return True\n",
    "    return False\n",
    "    \n",
    "    \n",
    "sample_df['defendants'], sample_df['defendant_match'], sample_df['judge'] = zip(*sample_df.apply(\n",
    "    lambda x: get_oscn_judges(x['oscn_county'], x['oscn_year'], x['oscn_number'], x['LAST_NAME']), axis=1\n",
    "))\n",
    "sample_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "sample_df.to_csv (r'/home/kendall/src/odoc-parse/sentencingData.csv', index = False, header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True     743\n",
       "False     92\n",
       "Name: defendant_match, dtype: int64"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_df['defendant_match'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "UNASSIGNED            94\n",
       "PALUMBO, AMY          50\n",
       "ELLIOTT, RAY C        47\n",
       "COYLE, HEATHER        41\n",
       "KIRBY, RICHARD        39\n",
       "                      ..\n",
       "THYGESEN, NORMAN       1\n",
       "SEWELL, BRUCE          1\n",
       "LOVELL, BRIAN N        1\n",
       "HAWORTH, TIMOTHY       1\n",
       "ROPER,  MICHELLE K     1\n",
       "Name: judge, Length: 194, dtype: int64"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_df['judge'].value_counts()"
   ]
  },
  {
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}