DLM arresting officers.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/owner/projects/python_envs/oscn/lib/python3.6/importlib/_bootstrap.py:219: RuntimeWarning: numpy.dtype size changed, may indicate binary incompatibility. Expected 96, got 88\n",
      "  return f(*args, **kwds)\n",
      "/Users/owner/projects/python_envs/oscn/lib/python3.6/importlib/_bootstrap.py:219: RuntimeWarning: numpy.dtype size changed, may indicate binary incompatibility. Expected 96, got 88\n",
      "  return f(*args, **kwds)\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "\n",
    "import os\n",
    "import sys\n",
    "module_path = os.path.abspath(os.path.join('..'))\n",
    "if module_path not in sys.path:\n",
    "    sys.path.append(module_path)\n",
    "\n",
    "    \n",
    "from ice_hold.inmates import county\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "inmates = county.inmate_generator()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inmate = next(inmates)\n",
    "\n",
    "print(inmate['arrest']['Arrest Date'])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## create dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 37.4 s, sys: 902 ms, total: 38.3 s\n",
      "Wall time: 2min 15s\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/owner/projects/python_envs/oscn/lib/python3.6/site-packages/ipykernel_launcher.py:1: DeprecationWarning: generator 'inmate_generator' raised StopIteration\n",
      "  \"\"\"Entry point for launching an IPython kernel.\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>id</th>\n",
       "      <th>BookingDate</th>\n",
       "      <th>ArrestDate</th>\n",
       "      <th>ArrestedBy</th>\n",
       "      <th>ReleaseDate</th>\n",
       "      <th>Race</th>\n",
       "      <th>Height</th>\n",
       "      <th>Weight</th>\n",
       "      <th>CountCharges</th>\n",
       "      <th>Charges</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20190128071</td>\n",
       "      <td>1/28/2019</td>\n",
       "      <td></td>\n",
       "      <td>/</td>\n",
       "      <td></td>\n",
       "      <td>H</td>\n",
       "      <td>5' 09\"</td>\n",
       "      <td>140</td>\n",
       "      <td>0</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20190128070</td>\n",
       "      <td>1/28/2019</td>\n",
       "      <td>1/28/2019</td>\n",
       "      <td>TCSO / COLBERT</td>\n",
       "      <td></td>\n",
       "      <td>H</td>\n",
       "      <td>5' 07\"</td>\n",
       "      <td>160</td>\n",
       "      <td>1</td>\n",
       "      <td>[{'Description': 'HOLD/ICE', 'Case #': '', 'Co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20190128069</td>\n",
       "      <td>1/28/2019</td>\n",
       "      <td>1/28/2019</td>\n",
       "      <td>TCSO / COLBERT</td>\n",
       "      <td></td>\n",
       "      <td>H</td>\n",
       "      <td>5' 06\"</td>\n",
       "      <td>150</td>\n",
       "      <td>1</td>\n",
       "      <td>[{'Description': 'HOLD/ICE', 'Case #': '', 'Co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20190128068</td>\n",
       "      <td>1/28/2019</td>\n",
       "      <td>1/28/2019</td>\n",
       "      <td>TPD / REID</td>\n",
       "      <td></td>\n",
       "      <td>B</td>\n",
       "      <td>5' 09\"</td>\n",
       "      <td>140</td>\n",
       "      <td>3</td>\n",
       "      <td>[{'Description': 'STOLEN PROPERTY (KCSP) AFCF'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20190128067</td>\n",
       "      <td>1/28/2019</td>\n",
       "      <td>1/28/2019</td>\n",
       "      <td>TPD / RANGEL</td>\n",
       "      <td></td>\n",
       "      <td>W</td>\n",
       "      <td>5' 07\"</td>\n",
       "      <td>170</td>\n",
       "      <td>3</td>\n",
       "      <td>[{'Description': 'POSS CD SCHED I II 1ST OFFEN...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            id BookingDate ArrestDate      ArrestedBy ReleaseDate Race  \\\n",
       "0  20190128071   1/28/2019                          /                H   \n",
       "1  20190128070   1/28/2019  1/28/2019  TCSO / COLBERT                H   \n",
       "2  20190128069   1/28/2019  1/28/2019  TCSO / COLBERT                H   \n",
       "3  20190128068   1/28/2019  1/28/2019      TPD / REID                B   \n",
       "4  20190128067   1/28/2019  1/28/2019    TPD / RANGEL                W   \n",
       "\n",
       "   Height Weight  CountCharges  \\\n",
       "0  5' 09\"    140             0   \n",
       "1  5' 07\"    160             1   \n",
       "2  5' 06\"    150             1   \n",
       "3  5' 09\"    140             3   \n",
       "4  5' 07\"    170             3   \n",
       "\n",
       "                                             Charges  \n",
       "0                                                 []  \n",
       "1  [{'Description': 'HOLD/ICE', 'Case #': '', 'Co...  \n",
       "2  [{'Description': 'HOLD/ICE', 'Case #': '', 'Co...  \n",
       "3  [{'Description': 'STOLEN PROPERTY (KCSP) AFCF'...  \n",
       "4  [{'Description': 'POSS CD SCHED I II 1ST OFFEN...  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inmates = county.inmate_generator()\n",
    "\n",
    "columns = ['id',\n",
    "           'BookingDate',\n",
    "           'ArrestDate',\n",
    "           'ArrestedBy',\n",
    "           'ReleaseDate',\n",
    "           'Race',\n",
    "           'Height',\n",
    "           'Weight',\n",
    "           'CountCharges',\n",
    "           'Charges',\n",
    "          ]\n",
    "\n",
    "def inmate_data(i): \n",
    "    return [i['Booking ID'],\n",
    "            i['Booking Date'],\n",
    "            i['arrest']['Arrest Date'],\n",
    "            i['arrest']['Arrested By'],\n",
    "            i['arrest']['Release Date'],\n",
    "            i['features']['Race'],\n",
    "            i['features']['Height'],\n",
    "            i['features']['Weight'],\n",
    "            len(i['charges']),\n",
    "            i['charges'],\n",
    "           ]\n",
    "    \n",
    "\n",
    "%time  all_inmates = pd.DataFrame([inmate_data(i) for i in inmates], columns = columns)\n",
    "\n",
    "all_inmates.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>id</th>\n",
       "      <th>BookingDate</th>\n",
       "      <th>ArrestDate</th>\n",
       "      <th>ReleaseDate</th>\n",
       "      <th>Race</th>\n",
       "      <th>Height</th>\n",
       "      <th>Weight</th>\n",
       "      <th>CountCharges</th>\n",
       "      <th>Charges</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ArrestedBy</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>/</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>/ PATTERSON</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>/ WARREN</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BIXB / ALLEN</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BIXB / BURNS</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BIXB / DISCH</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BIXB / HENDERSON</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BIXB / LITTLE</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BIXB / PARENTEAU</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BIXB / PARTENTEAU</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BIXB / WALSH</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / B.TAYLOR</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / BASKINS</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / BROCE</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / BUHLER</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / BURDEN</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / CHISUM</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / COOK</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / CRAWFORD</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / DANIELS</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / DUSSLING</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / FISHER</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / GERAS</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / JAMES</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / JESSIE</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / JOHNSON</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / JORDAN</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / KOLAR</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROK / LAUDERDALE</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / VILLA</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / VILLAREAL</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / VILLARREAL</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / VIRGEN</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / VIRGIN</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WAGES</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WALKER</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WALL</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WARD</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WARNE</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WARREN</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WATKINS</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WEAVER</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WEBER</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WESTCOTT</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WHALEY</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WHEELER</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WIGGS</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WILCOX</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WILLIAMS</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WOLFE</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / WOODARD</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / XIONG</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / YOST</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / ZAFERES</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / ZENONI</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TPD / ZUNIGA</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USM / FOSTER</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USM / REED</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USM / SPEAKS</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>622 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   id  BookingDate  ArrestDate  ReleaseDate  Race  Height  \\\n",
       "ArrestedBy                                                                  \n",
       "                    2            2           2            2     2       2   \n",
       "/                   3            3           3            3     3       3   \n",
       "/ PATTERSON         1            1           1            1     1       1   \n",
       "/ WARREN            2            2           2            2     2       2   \n",
       "BIXB / ALLEN        1            1           1            1     1       1   \n",
       "BIXB / BURNS        1            1           1            1     1       1   \n",
       "BIXB / DISCH        1            1           1            1     1       1   \n",
       "BIXB / HENDERSON    1            1           1            1     1       1   \n",
       "BIXB / LITTLE       1            1           1            1     1       1   \n",
       "BIXB / PARENTEAU    1            1           1            1     1       1   \n",
       "BIXB / PARTENTEAU   1            1           1            1     1       1   \n",
       "BIXB / WALSH        1            1           1            1     1       1   \n",
       "BROK / B.TAYLOR     1            1           1            1     1       1   \n",
       "BROK / BASKINS      2            2           2            2     2       2   \n",
       "BROK / BROCE        1            1           1            1     1       1   \n",
       "BROK / BUHLER       2            2           2            2     2       2   \n",
       "BROK / BURDEN       1            1           1            1     1       1   \n",
       "BROK / CHISUM       2            2           2            2     2       2   \n",
       "BROK / COOK         1            1           1            1     1       1   \n",
       "BROK / CRAWFORD     1            1           1            1     1       1   \n",
       "BROK / DANIELS      1            1           1            1     1       1   \n",
       "BROK / DUSSLING     1            1           1            1     1       1   \n",
       "BROK / FISHER       5            5           5            5     5       5   \n",
       "BROK / GERAS        1            1           1            1     1       1   \n",
       "BROK / JAMES        1            1           1            1     1       1   \n",
       "BROK / JESSIE       2            2           2            2     2       2   \n",
       "BROK / JOHNSON      1            1           1            1     1       1   \n",
       "BROK / JORDAN       1            1           1            1     1       1   \n",
       "BROK / KOLAR        1            1           1            1     1       1   \n",
       "BROK / LAUDERDALE   5            5           5            5     5       5   \n",
       "...                ..          ...         ...          ...   ...     ...   \n",
       "TPD / VILLA         3            3           3            3     3       3   \n",
       "TPD / VILLAREAL     1            1           1            1     1       1   \n",
       "TPD / VILLARREAL    5            5           5            5     5       5   \n",
       "TPD / VIRGEN        2            2           2            2     2       2   \n",
       "TPD / VIRGIN        1            1           1            1     1       1   \n",
       "TPD / WAGES         1            1           1            1     1       1   \n",
       "TPD / WALKER        1            1           1            1     1       1   \n",
       "TPD / WALL          2            2           2            2     2       2   \n",
       "TPD / WARD          7            7           7            7     7       7   \n",
       "TPD / WARNE         3            3           3            3     3       3   \n",
       "TPD / WARREN        1            1           1            1     1       1   \n",
       "TPD / WATKINS       4            4           4            4     4       4   \n",
       "TPD / WEAVER        2            2           2            2     2       2   \n",
       "TPD / WEBER         6            6           6            6     6       6   \n",
       "TPD / WESTCOTT      1            1           1            1     1       1   \n",
       "TPD / WHALEY        1            1           1            1     1       1   \n",
       "TPD / WHEELER       1            1           1            1     1       1   \n",
       "TPD / WIGGS         1            1           1            1     1       1   \n",
       "TPD / WILCOX        4            4           4            4     4       4   \n",
       "TPD / WILLIAMS      4            4           4            4     4       4   \n",
       "TPD / WOLFE         2            2           2            2     2       2   \n",
       "TPD / WOODARD       1            1           1            1     1       1   \n",
       "TPD / XIONG         1            1           1            1     1       1   \n",
       "TPD / YOST          3            3           3            3     3       3   \n",
       "TPD / ZAFERES       3            3           3            3     3       3   \n",
       "TPD / ZENONI        1            1           1            1     1       1   \n",
       "TPD / ZUNIGA        6            6           6            6     6       6   \n",
       "USM / FOSTER        1            1           1            1     1       1   \n",
       "USM / REED          2            2           2            2     2       2   \n",
       "USM / SPEAKS        1            1           1            1     1       1   \n",
       "\n",
       "                   Weight  CountCharges  Charges  \n",
       "ArrestedBy                                        \n",
       "                        2             2        2  \n",
       "/                       3             3        3  \n",
       "/ PATTERSON             1             1        1  \n",
       "/ WARREN                2             2        2  \n",
       "BIXB / ALLEN            1             1        1  \n",
       "BIXB / BURNS            1             1        1  \n",
       "BIXB / DISCH            1             1        1  \n",
       "BIXB / HENDERSON        1             1        1  \n",
       "BIXB / LITTLE           1             1        1  \n",
       "BIXB / PARENTEAU        1             1        1  \n",
       "BIXB / PARTENTEAU       1             1        1  \n",
       "BIXB / WALSH            1             1        1  \n",
       "BROK / B.TAYLOR         1             1        1  \n",
       "BROK / BASKINS          2             2        2  \n",
       "BROK / BROCE            1             1        1  \n",
       "BROK / BUHLER           2             2        2  \n",
       "BROK / BURDEN           1             1        1  \n",
       "BROK / CHISUM           2             2        2  \n",
       "BROK / COOK             1             1        1  \n",
       "BROK / CRAWFORD         1             1        1  \n",
       "BROK / DANIELS          1             1        1  \n",
       "BROK / DUSSLING         1             1        1  \n",
       "BROK / FISHER           5             5        5  \n",
       "BROK / GERAS            1             1        1  \n",
       "BROK / JAMES            1             1        1  \n",
       "BROK / JESSIE           2             2        2  \n",
       "BROK / JOHNSON          1             1        1  \n",
       "BROK / JORDAN           1             1        1  \n",
       "BROK / KOLAR            1             1        1  \n",
       "BROK / LAUDERDALE       5             5        5  \n",
       "...                   ...           ...      ...  \n",
       "TPD / VILLA             3             3        3  \n",
       "TPD / VILLAREAL         1             1        1  \n",
       "TPD / VILLARREAL        5             5        5  \n",
       "TPD / VIRGEN            2             2        2  \n",
       "TPD / VIRGIN            1             1        1  \n",
       "TPD / WAGES             1             1        1  \n",
       "TPD / WALKER            1             1        1  \n",
       "TPD / WALL              2             2        2  \n",
       "TPD / WARD              7             7        7  \n",
       "TPD / WARNE             3             3        3  \n",
       "TPD / WARREN            1             1        1  \n",
       "TPD / WATKINS           4             4        4  \n",
       "TPD / WEAVER            2             2        2  \n",
       "TPD / WEBER             6             6        6  \n",
       "TPD / WESTCOTT          1             1        1  \n",
       "TPD / WHALEY            1             1        1  \n",
       "TPD / WHEELER           1             1        1  \n",
       "TPD / WIGGS             1             1        1  \n",
       "TPD / WILCOX            4             4        4  \n",
       "TPD / WILLIAMS          4             4        4  \n",
       "TPD / WOLFE             2             2        2  \n",
       "TPD / WOODARD           1             1        1  \n",
       "TPD / XIONG             1             1        1  \n",
       "TPD / YOST              3             3        3  \n",
       "TPD / ZAFERES           3             3        3  \n",
       "TPD / ZENONI            1             1        1  \n",
       "TPD / ZUNIGA            6             6        6  \n",
       "USM / FOSTER            1             1        1  \n",
       "USM / REED              2             2        2  \n",
       "USM / SPEAKS            1             1        1  \n",
       "\n",
       "[622 rows x 9 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_inmates.groupby('ArrestedBy').count()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert null hold to str \n",
    "\n",
    "def safe_hold(hold_val):\n",
    "    return hold_val if not pd.isna(hold_val) else \"No hold\"\n",
    "\n",
    "all_inmates['Hold'] = all_inmates['Hold'].apply(safe_hold)\n",
    "\n",
    "all_inmates.groupby('Hold').count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fix null charge str\n",
    "\n",
    "def safe_charge(chg_val):\n",
    "    return chg_val if not pd.isna(chg_val) else \"No charge listed\"\n",
    "\n",
    "all_inmates['Charge'] = all_inmates['Charge'].apply(safe_charge)\n",
    "\n",
    "all_inmates.groupby('Charge').count()\n",
    "\n",
    "# safety check for null names\n",
    "\n",
    "def safe_name(name_str):\n",
    "    return name_str if not pd.isna(name_str) else \"Not_entered\"\n",
    "\n",
    "\n",
    "all_inmates['FirstName'] = all_inmates['FirstName'].apply(safe_name)\n",
    "all_inmates['LastName'] = all_inmates['LastName'].apply(safe_name)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sort by Booking date so that last booking is best record\n",
    "sorted_inmates = all_inmates.sort_values('BookingDate')\n",
    "sorted_inmates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from elasticsearch import Elasticsearch\n",
    "from elasticsearch import helpers\n",
    "es = Elasticsearch(http_compress=True)\n",
    "\n",
    "keys = ['FirstName', 'LastName', 'id', 'Gender', 'BookingDate', 'Hold', 'ReleaseDate', 'Race', 'Charge', 'Jail']\n",
    "\n",
    "\n",
    "def filterKeys(doc):\n",
    "    return {k: doc[k] for k in keys }\n",
    "\n",
    "def doc_generator(df):\n",
    "    df_iter = df.iterrows()\n",
    "    for idx, document in df_iter:\n",
    "        yield {\n",
    "                \"_index\": 'inmates',\n",
    "                \"_type\": \"_doc\",\n",
    "                \"_id\" : f\"{document['id']}\",\n",
    "                \"_source\": filterKeys(document),\n",
    "            }\n",
    "    raise StopIteration\n",
    "\n",
    "helpers.bulk(es, doc_generator(sorted_inmates))"
   ]
  },
  {
   "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
}