Inmates Charges Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "20998604",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import ast"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "bf23a825",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the dataset\n",
    "df = pd.read_csv(\"csvs/inmate_details.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4156c4e5",
   "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>SWIS ID</th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Race</th>\n",
       "      <th>Height</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Hair</th>\n",
       "      <th>Eyes</th>\n",
       "      <th>Arresting Agency</th>\n",
       "      <th>Booking Date</th>\n",
       "      <th>Assigned Facility</th>\n",
       "      <th>Projected Release Date</th>\n",
       "      <th>Charge Type Counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>812456</td>\n",
       "      <td>Adair, Jasmine Marie</td>\n",
       "      <td>27</td>\n",
       "      <td>Female</td>\n",
       "      <td>Black</td>\n",
       "      <td>5 ft 2 in</td>\n",
       "      <td>108 lbs</td>\n",
       "      <td>Black</td>\n",
       "      <td>Brown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>03/23/2024 07:56 AM</td>\n",
       "      <td>MCDC</td>\n",
       "      <td>03/24/2024</td>\n",
       "      <td>{'ASSAULT III (C Felony)': 1, 'DUII (A Misdeme...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>697319</td>\n",
       "      <td>Lovell, Brian</td>\n",
       "      <td>35</td>\n",
       "      <td>Male</td>\n",
       "      <td>White</td>\n",
       "      <td>5 ft 8 in</td>\n",
       "      <td>130 lbs</td>\n",
       "      <td>Blonde</td>\n",
       "      <td>Blue</td>\n",
       "      <td>PSU</td>\n",
       "      <td>08/27/2023 09:26 AM</td>\n",
       "      <td>MCDC</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>{'FURN FALSE INFO POL (A Misdemeanor)': 1, 'IN...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>767069</td>\n",
       "      <td>Crawford Jr, Shawn Degail</td>\n",
       "      <td>31</td>\n",
       "      <td>Male</td>\n",
       "      <td>Black</td>\n",
       "      <td>5 ft 8 in</td>\n",
       "      <td>145 lbs</td>\n",
       "      <td>Black</td>\n",
       "      <td>Brown</td>\n",
       "      <td>Portland Sheriff</td>\n",
       "      <td>12/19/2018 02:35 PM</td>\n",
       "      <td>MCIJ</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>{'MURDER I (A Felony)': 1, 'MANSLAUGHTER I (A ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>738450</td>\n",
       "      <td>Jackson, Deshawn Anthonyj</td>\n",
       "      <td>33</td>\n",
       "      <td>Male</td>\n",
       "      <td>Black</td>\n",
       "      <td>5 ft 7 in</td>\n",
       "      <td>150 lbs</td>\n",
       "      <td>Black</td>\n",
       "      <td>Brown</td>\n",
       "      <td>Portland Police, North Precinct</td>\n",
       "      <td>08/27/2019 07:55 AM</td>\n",
       "      <td>MCIJ</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>{'SODOMY I (A Felony)': 2, 'UNLAW SEX PEN I (A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>616393</td>\n",
       "      <td>Atchison, Horace Eugene</td>\n",
       "      <td>51</td>\n",
       "      <td>Male</td>\n",
       "      <td>Black</td>\n",
       "      <td>5 ft 8 in</td>\n",
       "      <td>215 lbs</td>\n",
       "      <td>Black</td>\n",
       "      <td>Brown</td>\n",
       "      <td>Portland Sheriff</td>\n",
       "      <td>09/03/2019 01:58 PM</td>\n",
       "      <td>MCIJ</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>{'MURDER I (A Felony)': 2, 'ASSAULT II (B Felo...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SWIS ID                       Name  Age  Gender   Race     Height   Weight  \\\n",
       "0   812456       Adair, Jasmine Marie   27  Female  Black  5 ft 2 in  108 lbs   \n",
       "1   697319              Lovell, Brian   35    Male  White  5 ft 8 in  130 lbs   \n",
       "2   767069  Crawford Jr, Shawn Degail   31    Male  Black  5 ft 8 in  145 lbs   \n",
       "3   738450  Jackson, Deshawn Anthonyj   33    Male  Black  5 ft 7 in  150 lbs   \n",
       "4   616393    Atchison, Horace Eugene   51    Male  Black  5 ft 8 in  215 lbs   \n",
       "\n",
       "     Hair   Eyes                 Arresting Agency         Booking Date  \\\n",
       "0   Black  Brown                              NaN  03/23/2024 07:56 AM   \n",
       "1  Blonde   Blue                              PSU  08/27/2023 09:26 AM   \n",
       "2   Black  Brown                 Portland Sheriff  12/19/2018 02:35 PM   \n",
       "3   Black  Brown  Portland Police, North Precinct  08/27/2019 07:55 AM   \n",
       "4   Black  Brown                 Portland Sheriff  09/03/2019 01:58 PM   \n",
       "\n",
       "  Assigned Facility Projected Release Date  \\\n",
       "0              MCDC             03/24/2024   \n",
       "1              MCDC                Unknown   \n",
       "2              MCIJ                Unknown   \n",
       "3              MCIJ                Unknown   \n",
       "4              MCIJ                Unknown   \n",
       "\n",
       "                                  Charge Type Counts  \n",
       "0  {'ASSAULT III (C Felony)': 1, 'DUII (A Misdeme...  \n",
       "1  {'FURN FALSE INFO POL (A Misdemeanor)': 1, 'IN...  \n",
       "2  {'MURDER I (A Felony)': 1, 'MANSLAUGHTER I (A ...  \n",
       "3  {'SODOMY I (A Felony)': 2, 'UNLAW SEX PEN I (A...  \n",
       "4  {'MURDER I (A Felony)': 2, 'ASSAULT II (B Felo...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "bef9734e",
   "metadata": {},
   "source": [
    "## Change datatypes of some columns that need to be converted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4b162109",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 910 entries, 0 to 909\n",
      "Data columns (total 14 columns):\n",
      " #   Column                  Non-Null Count  Dtype         \n",
      "---  ------                  --------------  -----         \n",
      " 0   SWIS ID                 910 non-null    int64         \n",
      " 1   Name                    910 non-null    object        \n",
      " 2   Age                     910 non-null    int64         \n",
      " 3   Gender                  910 non-null    object        \n",
      " 4   Race                    910 non-null    object        \n",
      " 5   Height                  910 non-null    object        \n",
      " 6   Weight                  910 non-null    object        \n",
      " 7   Hair                    910 non-null    object        \n",
      " 8   Eyes                    910 non-null    object        \n",
      " 9   Arresting Agency        901 non-null    object        \n",
      " 10  Booking Date            910 non-null    datetime64[ns]\n",
      " 11  Assigned Facility       910 non-null    object        \n",
      " 12  Projected Release Date  910 non-null    object        \n",
      " 13  Charge Type Counts      910 non-null    object        \n",
      "dtypes: datetime64[ns](1), int64(2), object(11)\n",
      "memory usage: 99.7+ KB\n"
     ]
    }
   ],
   "source": [
    "df['Booking Date'] = pd.to_datetime(df['Booking Date'])\n",
    "df['Age'] = df['Age'].astype(int)\n",
    "df.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "ae51ddcc",
   "metadata": {},
   "source": [
    "## Convert the column of Charge Type Counts : Dictionary in string to value counts of dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8ede196c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    {'ASSAULT III (C Felony)': 1, 'DUII (A Misdeme...\n",
       "1    {'FURN FALSE INFO POL (A Misdemeanor)': 1, 'IN...\n",
       "2    {'MURDER I (A Felony)': 1, 'MANSLAUGHTER I (A ...\n",
       "3    {'SODOMY I (A Felony)': 2, 'UNLAW SEX PEN I (A...\n",
       "4    {'MURDER I (A Felony)': 2, 'ASSAULT II (B Felo...\n",
       "Name: Charge Type Counts, dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert string containing dict to dict\n",
    "df[\"Charge Type Counts\"] = df[\"Charge Type Counts\"].apply(lambda x: ast.literal_eval(x))\n",
    "df[\"Charge Type Counts\"].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b1e50734",
   "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>SWIS ID</th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Race</th>\n",
       "      <th>Height</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Hair</th>\n",
       "      <th>Eyes</th>\n",
       "      <th>Arresting Agency</th>\n",
       "      <th>Booking Date</th>\n",
       "      <th>Assigned Facility</th>\n",
       "      <th>Projected Release Date</th>\n",
       "      <th>Charge Type Counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>136511</td>\n",
       "      <td>Schlaht, Gabriel David</td>\n",
       "      <td>46</td>\n",
       "      <td>Male</td>\n",
       "      <td>White</td>\n",
       "      <td>6 ft 0 in</td>\n",
       "      <td>230 lbs</td>\n",
       "      <td>Brown</td>\n",
       "      <td>Blue</td>\n",
       "      <td>Portland Sheriff</td>\n",
       "      <td>2023-03-02 20:30:00</td>\n",
       "      <td>MCIJ</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>804866</td>\n",
       "      <td>Saidstuart, Tory Dwanell</td>\n",
       "      <td>24</td>\n",
       "      <td>Male</td>\n",
       "      <td>Black</td>\n",
       "      <td>5 ft 6 in</td>\n",
       "      <td>180 lbs</td>\n",
       "      <td>Black</td>\n",
       "      <td>Brown</td>\n",
       "      <td>Portland Police, East Precinct</td>\n",
       "      <td>2021-03-31 14:08:00</td>\n",
       "      <td>MCDC</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>716132</td>\n",
       "      <td>Crawford, Damien L</td>\n",
       "      <td>38</td>\n",
       "      <td>Male</td>\n",
       "      <td>Black</td>\n",
       "      <td>6 ft 2 in</td>\n",
       "      <td>205 lbs</td>\n",
       "      <td>Black</td>\n",
       "      <td>Brown</td>\n",
       "      <td>Portland Police, Other</td>\n",
       "      <td>2022-01-11 13:18:00</td>\n",
       "      <td>MCIJ</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>778119</td>\n",
       "      <td>Denison, Levi Mitchellray</td>\n",
       "      <td>29</td>\n",
       "      <td>Male</td>\n",
       "      <td>White</td>\n",
       "      <td>5 ft 10 in</td>\n",
       "      <td>200 lbs</td>\n",
       "      <td>Blonde</td>\n",
       "      <td>Blue</td>\n",
       "      <td>Portland Police, East Precinct</td>\n",
       "      <td>2023-08-16 18:48:00</td>\n",
       "      <td>MCDC</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>313</th>\n",
       "      <td>724354</td>\n",
       "      <td>Taylor, Anthony Scott</td>\n",
       "      <td>44</td>\n",
       "      <td>Male</td>\n",
       "      <td>White</td>\n",
       "      <td>6 ft 0 in</td>\n",
       "      <td>204 lbs</td>\n",
       "      <td>Brown</td>\n",
       "      <td>Brown</td>\n",
       "      <td>Portland Police, East Precinct</td>\n",
       "      <td>2023-11-29 14:18:00</td>\n",
       "      <td>MCIJ</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     SWIS ID                       Name  Age Gender   Race      Height  \\\n",
       "102   136511     Schlaht, Gabriel David   46   Male  White   6 ft 0 in   \n",
       "16    804866   Saidstuart, Tory Dwanell   24   Male  Black   5 ft 6 in   \n",
       "36    716132         Crawford, Damien L   38   Male  Black   6 ft 2 in   \n",
       "193   778119  Denison, Levi Mitchellray   29   Male  White  5 ft 10 in   \n",
       "313   724354      Taylor, Anthony Scott   44   Male  White   6 ft 0 in   \n",
       "\n",
       "      Weight    Hair   Eyes                Arresting Agency  \\\n",
       "102  230 lbs   Brown   Blue                Portland Sheriff   \n",
       "16   180 lbs   Black  Brown  Portland Police, East Precinct   \n",
       "36   205 lbs   Black  Brown          Portland Police, Other   \n",
       "193  200 lbs  Blonde   Blue  Portland Police, East Precinct   \n",
       "313  204 lbs   Brown  Brown  Portland Police, East Precinct   \n",
       "\n",
       "           Booking Date Assigned Facility Projected Release Date  \\\n",
       "102 2023-03-02 20:30:00              MCIJ                Unknown   \n",
       "16  2021-03-31 14:08:00              MCDC                Unknown   \n",
       "36  2022-01-11 13:18:00              MCIJ                Unknown   \n",
       "193 2023-08-16 18:48:00              MCDC                Unknown   \n",
       "313 2023-11-29 14:18:00              MCIJ                Unknown   \n",
       "\n",
       "     Charge Type Counts  \n",
       "102                 216  \n",
       "16                  159  \n",
       "36                  116  \n",
       "193                  88  \n",
       "313                  86  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"Charge Type Counts\"] = df[\"Charge Type Counts\"].apply(lambda x: sum(x.values()))\n",
    "df = df.sort_values(by=\"Charge Type Counts\", ascending=False)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a3e1df4",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}