{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Summarizing OpenPoliceData Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook shows examples of the following tasks:\n",
    "- Summarizing available data in OpenPoliceData (OPD)\n",
    "- Exporting data summaries\n",
    "- Generating your own data summaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    import openpolicedata as opd\n",
    "except:\n",
    "    import sys\n",
    "    sys.path.append('../openpolicedata')\n",
    "    import openpolicedata as opd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The OpenPoliceData package has 212 unique datasets\n"
     ]
    }
   ],
   "source": [
    "# Get The number of unique datasets (unique state, source, agency, and table type)\n",
    "print(f\"The OpenPoliceData package has {opd.datasets.num_unique()} unique datasets\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OPD has 9 datasets for all agencies in a state\n",
      "OPD has 120 datasets for individual agencies\n"
     ]
    }
   ],
   "source": [
    "# Find how many datasets are available for full states and how many are available for individual agencies\n",
    "print(f\"OPD has {opd.datasets.num_sources(full_states_only=True)} datasets for all agencies in a state\")\n",
    "print(f\"OPD has {opd.datasets.num_sources()-opd.datasets.num_sources(full_states_only=True)} datasets for individual agencies\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>California</th>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All State Agencies</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Individual Agency</th>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Texas</th>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Carolina</th>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All State Agencies</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Individual Agency</th>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New York</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ohio</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arizona</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Total\n",
       "State                     \n",
       "California                \n",
       "  All State Agencies     2\n",
       "  Individual Agency     23\n",
       "Texas                   20\n",
       "North Carolina            \n",
       "  All State Agencies     1\n",
       "  Individual Agency     17\n",
       "New York                12\n",
       "Ohio                    10\n",
       "Arizona                  9"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find number of datasets from each state\n",
    "opd.datasets.summary_by_state().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Total</th>\n",
       "      <th>N/A</th>\n",
       "      <th>MULTI-YEAR</th>\n",
       "      <th>2022</th>\n",
       "      <th>2021</th>\n",
       "      <th>2020</th>\n",
       "      <th>2019</th>\n",
       "      <th>2018</th>\n",
       "      <th>2017</th>\n",
       "      <th>2016</th>\n",
       "      <th>...</th>\n",
       "      <th>2014</th>\n",
       "      <th>2013</th>\n",
       "      <th>2012</th>\n",
       "      <th>2011</th>\n",
       "      <th>2010</th>\n",
       "      <th>2009</th>\n",
       "      <th>2008</th>\n",
       "      <th>2007</th>\n",
       "      <th>2006</th>\n",
       "      <th>2005</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</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",
       "      <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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>California</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>All State Agencies</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Individual Agency</th>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Texas</th>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Carolina</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>All State Agencies</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Individual Agency</th>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>0</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>...</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",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Total N/A MULTI-YEAR 2022 2021 2020 2019 2018 2017 2016  \\\n",
       "State                                                                          \n",
       "California                                                                     \n",
       "  All State Agencies     2   0          0    0    1    2    2    2    1    1   \n",
       "  Individual Agency     23   0         18    0    5    5    4    4    4    4   \n",
       "Texas                   20   0         13    0    0    2    6    6    6    5   \n",
       "North Carolina                                                                 \n",
       "  All State Agencies     1   0          1    0    0    0    0    0    0    0   \n",
       "  Individual Agency     17   2         14    0    1    1    1    1    1    1   \n",
       "\n",
       "                      ... 2014 2013 2012 2011 2010 2009 2008 2007 2006 2005  \n",
       "State                 ...                                                    \n",
       "California            ...                                                    \n",
       "  All State Agencies  ...    0    0    0    0    0    0    0    0    0    0  \n",
       "  Individual Agency   ...    2    2    2    2    1    0    0    0    0    0  \n",
       "Texas                 ...    5    2    1    1    1    1    0    0    0    0  \n",
       "North Carolina        ...                                                    \n",
       "  All State Agencies  ...    0    0    0    0    0    0    0    0    0    0  \n",
       "  Individual Agency   ...    1    1    1    1    1    1    1    1    1    1  \n",
       "\n",
       "[7 rows x 21 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find number of datasets from each state broken down by year\n",
    "opd.datasets.summary_by_state(by=\"year\").head(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Total</th>\n",
       "      <th>ARRESTS</th>\n",
       "      <th>CALLS FOR SERVICE</th>\n",
       "      <th>CITATIONS</th>\n",
       "      <th>COMPLAINTS</th>\n",
       "      <th>EMPLOYEE</th>\n",
       "      <th>FIELD CONTACTS</th>\n",
       "      <th>OFFICER-INVOLVED SHOOTINGS</th>\n",
       "      <th>OFFICER-INVOLVED SHOOTINGS - CIVILIANS</th>\n",
       "      <th>OFFICER-INVOLVED SHOOTINGS - INCIDENTS</th>\n",
       "      <th>...</th>\n",
       "      <th>TRAFFIC ARRESTS</th>\n",
       "      <th>TRAFFIC CITATIONS</th>\n",
       "      <th>TRAFFIC STOPS</th>\n",
       "      <th>TRAFFIC WARNINGS</th>\n",
       "      <th>USE OF FORCE</th>\n",
       "      <th>USE OF FORCE - CIVILIANS</th>\n",
       "      <th>USE OF FORCE - CIVILIANS/OFFICERS</th>\n",
       "      <th>USE OF FORCE - INCIDENTS</th>\n",
       "      <th>USE OF FORCE - OFFICERS</th>\n",
       "      <th>VEHICLE PURSUITS</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</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",
       "      <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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>California</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>All State Agencies</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Individual Agency</th>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Texas</th>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Carolina</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>All State Agencies</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Individual Agency</th>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Total ARRESTS CALLS FOR SERVICE CITATIONS COMPLAINTS  \\\n",
       "State                                                                       \n",
       "California                                                                  \n",
       "  All State Agencies     2       0                 0         0          0   \n",
       "  Individual Agency     23       0                 5         0          1   \n",
       "Texas                   20       0                 0         0          0   \n",
       "North Carolina                                                              \n",
       "  All State Agencies     1       0                 0         0          0   \n",
       "  Individual Agency     17       0                 1         1          0   \n",
       "\n",
       "                     EMPLOYEE FIELD CONTACTS OFFICER-INVOLVED SHOOTINGS  \\\n",
       "State                                                                     \n",
       "California                                                                \n",
       "  All State Agencies        0              0                          0   \n",
       "  Individual Agency         0              0                          0   \n",
       "Texas                       0              0                          0   \n",
       "North Carolina                                                            \n",
       "  All State Agencies        0              0                          0   \n",
       "  Individual Agency         2              1                          0   \n",
       "\n",
       "                     OFFICER-INVOLVED SHOOTINGS - CIVILIANS  \\\n",
       "State                                                         \n",
       "California                                                    \n",
       "  All State Agencies                                      0   \n",
       "  Individual Agency                                       1   \n",
       "Texas                                                     1   \n",
       "North Carolina                                                \n",
       "  All State Agencies                                      0   \n",
       "  Individual Agency                                       1   \n",
       "\n",
       "                     OFFICER-INVOLVED SHOOTINGS - INCIDENTS  ...  \\\n",
       "State                                                        ...   \n",
       "California                                                   ...   \n",
       "  All State Agencies                                      0  ...   \n",
       "  Individual Agency                                       1  ...   \n",
       "Texas                                                     1  ...   \n",
       "North Carolina                                               ...   \n",
       "  All State Agencies                                      0  ...   \n",
       "  Individual Agency                                       1  ...   \n",
       "\n",
       "                     TRAFFIC ARRESTS TRAFFIC CITATIONS TRAFFIC STOPS  \\\n",
       "State                                                                  \n",
       "California                                                             \n",
       "  All State Agencies               0                 0             0   \n",
       "  Individual Agency                0                 0             7   \n",
       "Texas                              1                 1             5   \n",
       "North Carolina                                                         \n",
       "  All State Agencies               0                 0             1   \n",
       "  Individual Agency                0                 0             7   \n",
       "\n",
       "                     TRAFFIC WARNINGS USE OF FORCE USE OF FORCE - CIVILIANS  \\\n",
       "State                                                                         \n",
       "California                                                                    \n",
       "  All State Agencies                0            0                        0   \n",
       "  Individual Agency                 0            0                        0   \n",
       "Texas                               1            2                        1   \n",
       "North Carolina                                                                \n",
       "  All State Agencies                0            0                        0   \n",
       "  Individual Agency                 0            0                        1   \n",
       "\n",
       "                     USE OF FORCE - CIVILIANS/OFFICERS  \\\n",
       "State                                                    \n",
       "California                                               \n",
       "  All State Agencies                                 0   \n",
       "  Individual Agency                                  1   \n",
       "Texas                                                0   \n",
       "North Carolina                                           \n",
       "  All State Agencies                                 0   \n",
       "  Individual Agency                                  0   \n",
       "\n",
       "                     USE OF FORCE - INCIDENTS USE OF FORCE - OFFICERS  \\\n",
       "State                                                                   \n",
       "California                                                              \n",
       "  All State Agencies                        1                       0   \n",
       "  Individual Agency                         0                       0   \n",
       "Texas                                       0                       0   \n",
       "North Carolina                                                          \n",
       "  All State Agencies                        0                       0   \n",
       "  Individual Agency                         0                       1   \n",
       "\n",
       "                     VEHICLE PURSUITS  \n",
       "State                                  \n",
       "California                             \n",
       "  All State Agencies                0  \n",
       "  Individual Agency                 0  \n",
       "Texas                               0  \n",
       "North Carolina                         \n",
       "  All State Agencies                0  \n",
       "  Individual Agency                 0  \n",
       "\n",
       "[7 rows x 26 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find number of datasets from each state broken down by table type\n",
    "opd.datasets.summary_by_state(by=\"table\").head(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                           Total  \\\n",
      "TableType                                          \n",
      "STOPS                                              \n",
      "  TRAFFIC STOPS (Only)                        66   \n",
      "  STOPS (All)                                 31   \n",
      "  PEDESTRIAN STOPS (Only)                      2   \n",
      "CALLS FOR SERVICE                             22   \n",
      "USE OF FORCE                                       \n",
      "  Single Table                                     \n",
      "    USE OF FORCE                              13   \n",
      "  Multiple Tables                                  \n",
      "    USE OF FORCE - CIVILIANS                   3   \n",
      "    USE OF FORCE - INCIDENTS                   2   \n",
      "    USE OF FORCE - OFFICERS                    2   \n",
      "    USE OF FORCE - CIVILIANS/OFFICERS          1   \n",
      "OFFICER-INVOLVED SHOOTINGS                         \n",
      "  Single Table                                     \n",
      "    OFFICER-INVOLVED SHOOTINGS                12   \n",
      "  Multiple Tables                                  \n",
      "    OFFICER-INVOLVED SHOOTINGS - INCIDENTS     4   \n",
      "    OFFICER-INVOLVED SHOOTINGS - OFFICERS      4   \n",
      "    OFFICER-INVOLVED SHOOTINGS - CIVILIANS     4   \n",
      "COMPLAINTS                                     9   \n",
      "EMPLOYEE                                       9   \n",
      "ARRESTS                                            \n",
      "  ARRESTS (All)                                6   \n",
      "  TRAFFIC ARRESTS (Only)                       2   \n",
      "  PEDESTRIAN ARRESTS (Only)                    1   \n",
      "CITATIONS                                          \n",
      "  CITATIONS (All)                              5   \n",
      "  TRAFFIC CITATIONS (Only)                     4   \n",
      "  PEDESTRIAN CITATIONS (Only)                  2   \n",
      "WARNINGS                                           \n",
      "  TRAFFIC WARNINGS (Only)                      3   \n",
      "  PEDESTRIAN WARNINGS (Only)                   1   \n",
      "FIELD CONTACTS                                 2   \n",
      "VEHICLE PURSUITS                               2   \n",
      "\n",
      "                                                                                   Definition  \n",
      "TableType                                                                                      \n",
      "STOPS                                                                                          \n",
      "  TRAFFIC STOPS (Only)                      Traffic stops are stops by police of motor veh...  \n",
      "  STOPS (All)                               Contains data on both pedestrian and traffic s...  \n",
      "  PEDESTRIAN STOPS (Only)                   Stops of pedestrians based on 'reasonable susp...  \n",
      "CALLS FOR SERVICE                           Includes dispatched calls (911 or non-emergenc...  \n",
      "USE OF FORCE                                                                                   \n",
      "  Single Table                                                                                 \n",
      "    USE OF FORCE                            Documentation of physical force used against c...  \n",
      "  Multiple Tables                                                                              \n",
      "    USE OF FORCE - CIVILIANS                Use of force data may be split into several ta...  \n",
      "    USE OF FORCE - INCIDENTS                Use of force data may be split into several ta...  \n",
      "    USE OF FORCE - OFFICERS                 Use of force data may be split into several ta...  \n",
      "    USE OF FORCE - CIVILIANS/OFFICERS       Use of force data may be split into several ta...  \n",
      "OFFICER-INVOLVED SHOOTINGS                                                                     \n",
      "  Single Table                                                                                 \n",
      "    OFFICER-INVOLVED SHOOTINGS                                          Shootings by officers  \n",
      "  Multiple Tables                                                                              \n",
      "    OFFICER-INVOLVED SHOOTINGS - INCIDENTS  Officer-involved shootings data may be split i...  \n",
      "    OFFICER-INVOLVED SHOOTINGS - OFFICERS   Officer-involved shootings data may be split i...  \n",
      "    OFFICER-INVOLVED SHOOTINGS - CIVILIANS  Officer-involved shootings data may be split i...  \n",
      "COMPLAINTS                                  Complaints of police misconduct made internall...  \n",
      "EMPLOYEE                                             Demographic data of the police workforce  \n",
      "ARRESTS                                                                                        \n",
      "  ARRESTS (All)                                     Seizures or forcible restraints by police  \n",
      "  TRAFFIC ARRESTS (Only)                                  Traffic stops leading to an arrest.  \n",
      "  PEDESTRIAN ARRESTS (Only)                             Pedestrian stops leading to an arrest  \n",
      "CITATIONS                                                                                      \n",
      "  CITATIONS (All)                           Commonly referred to as tickets, citations ind...  \n",
      "  TRAFFIC CITATIONS (Only)                               Traffic stops leading to a citation.  \n",
      "  PEDESTRIAN CITATIONS (Only)                          Pedestrian stops leading to a citation  \n",
      "WARNINGS                                                                                       \n",
      "  TRAFFIC WARNINGS (Only)                                 Traffic stops leading to a warning.  \n",
      "  PEDESTRIAN WARNINGS (Only)                            Pedestrian stops leading to a warning  \n",
      "FIELD CONTACTS                              Consensual contacts between officers and the c...  \n",
      "VEHICLE PURSUITS                            Attempts by officers in vehicles to pursue veh...  \n"
     ]
    }
   ],
   "source": [
    "# Find number of datasets for each type of table\n",
    "print(opd.datasets.summary_by_table_type())"
   ]
  },
  {
   "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>Total</th>\n",
       "      <th>N/A</th>\n",
       "      <th>MULTI-YEAR</th>\n",
       "      <th>2022</th>\n",
       "      <th>2021</th>\n",
       "      <th>2020</th>\n",
       "      <th>2019</th>\n",
       "      <th>2018</th>\n",
       "      <th>2017</th>\n",
       "      <th>2016</th>\n",
       "      <th>...</th>\n",
       "      <th>2013</th>\n",
       "      <th>2012</th>\n",
       "      <th>2011</th>\n",
       "      <th>2010</th>\n",
       "      <th>2009</th>\n",
       "      <th>2008</th>\n",
       "      <th>2007</th>\n",
       "      <th>2006</th>\n",
       "      <th>2005</th>\n",
       "      <th>Definition</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TableType</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",
       "      <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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>STOPS</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>TRAFFIC STOPS (Only)</th>\n",
       "      <td>66</td>\n",
       "      <td>0</td>\n",
       "      <td>64</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Traffic stops are stops by police of motor veh...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STOPS (All)</th>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Contains data on both pedestrian and traffic s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PEDESTRIAN STOPS (Only)</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Stops of pedestrians based on 'reasonable susp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CALLS FOR SERVICE</th>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Includes dispatched calls (911 or non-emergenc...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Total N/A MULTI-YEAR 2022 2021 2020 2019 2018 2017  \\\n",
       "TableType                                                                      \n",
       "STOPS                                                                          \n",
       "  TRAFFIC STOPS (Only)       66   0         64    0    0    2    2    2    2   \n",
       "  STOPS (All)                31   0         30    0    0    1    1    1    0   \n",
       "  PEDESTRIAN STOPS (Only)     2   0          1    0    0    0    0    0    0   \n",
       "CALLS FOR SERVICE            22   0         14    2    8    8    8    7    7   \n",
       "\n",
       "                          2016  ... 2013 2012 2011 2010 2009 2008 2007 2006  \\\n",
       "TableType                       ...                                           \n",
       "STOPS                           ...                                           \n",
       "  TRAFFIC STOPS (Only)       2  ...    2    1    1    1    1    0    0    0   \n",
       "  STOPS (All)                0  ...    0    0    0    0    0    0    0    0   \n",
       "  PEDESTRIAN STOPS (Only)    1  ...    1    1    0    0    0    0    0    0   \n",
       "CALLS FOR SERVICE            6  ...    5    5    4    2    1    1    1    1   \n",
       "\n",
       "                          2005  \\\n",
       "TableType                        \n",
       "STOPS                            \n",
       "  TRAFFIC STOPS (Only)       0   \n",
       "  STOPS (All)                0   \n",
       "  PEDESTRIAN STOPS (Only)    0   \n",
       "CALLS FOR SERVICE            1   \n",
       "\n",
       "                                                                  Definition  \n",
       "TableType                                                                     \n",
       "STOPS                                                                         \n",
       "  TRAFFIC STOPS (Only)     Traffic stops are stops by police of motor veh...  \n",
       "  STOPS (All)              Contains data on both pedestrian and traffic s...  \n",
       "  PEDESTRIAN STOPS (Only)  Stops of pedestrians based on 'reasonable susp...  \n",
       "CALLS FOR SERVICE          Includes dispatched calls (911 or non-emergenc...  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find number of datasets for each type of table broken down by year\n",
    "opd.datasets.summary_by_table_type(by_year=True).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# All returned summary tables are pandas DataFrames so they can be easily exported to CSV files\n",
    "# Find number of datasets for each type of table broken down by year\n",
    "opd.datasets.summary_by_table_type(by_year=True).to_csv(\"table_summary_by_year.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.12 ('opd')",
   "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.8.10"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "a73158d29711b2da05ac73de25b71e5d8cae591f14917bba77a9573b5c85a0ce"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}