merged-pdfs.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2d535189-4e4c-4e83-994a-fa4e8c87b8fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pandas.api.types import CategoricalDtype\n",
    "import numpy as np\n",
    "import re\n",
    "pd.set_option('display.max_rows', 500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "0e2dc9f9-7cc1-4e69-bb24-127fc006fb04",
   "metadata": {},
   "outputs": [],
   "source": [
    "# If we only want high security prisons\n",
    "# high_security = [\"CAC\", \"CCI\", \"COR\", \"LAC\", \"SAC\", \"HDSP\", \"KVSP\", \"PBSP\", \"SVSP\", \"SATF\"]\n",
    "# df_highsecurity = df.drop(df[~df['Institution'].isin(high_security)].index, axis=0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e959bdfb-1ed6-459e-a521-1cadc69c912b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new data type for months in order to sort by month\n",
    "months_dtype = CategoricalDtype(categories=['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'], ordered=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ce730486-0e2e-4b00-84f1-dfcfbac55e9f",
   "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>Institution</th>\n",
       "      <th>Year</th>\n",
       "      <th>Top Category</th>\n",
       "      <th>Subcategory</th>\n",
       "      <th>Metric Title</th>\n",
       "      <th>Jan</th>\n",
       "      <th>Feb</th>\n",
       "      <th>Mar</th>\n",
       "      <th>Apr</th>\n",
       "      <th>May</th>\n",
       "      <th>Jun</th>\n",
       "      <th>Jul</th>\n",
       "      <th>Aug</th>\n",
       "      <th>Sep</th>\n",
       "      <th>Oct</th>\n",
       "      <th>Nov</th>\n",
       "      <th>Dec</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>2009</td>\n",
       "      <td>Custody Operations</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Contract Beds</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",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>2009</td>\n",
       "      <td>Custody Operations</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Design Beds</td>\n",
       "      <td>1916</td>\n",
       "      <td>1953</td>\n",
       "      <td>1945</td>\n",
       "      <td>1961</td>\n",
       "      <td>1969</td>\n",
       "      <td>1999</td>\n",
       "      <td>2007</td>\n",
       "      <td>1986</td>\n",
       "      <td>1966</td>\n",
       "      <td>2006</td>\n",
       "      <td>1969</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>2009</td>\n",
       "      <td>Custody Operations</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Non-Traditional Beds</td>\n",
       "      <td>265</td>\n",
       "      <td>62</td>\n",
       "      <td>72</td>\n",
       "      <td>72</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>72</td>\n",
       "      <td>67</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>2009</td>\n",
       "      <td>Custody Operations</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Overcrowding Beds</td>\n",
       "      <td>1801</td>\n",
       "      <td>1886</td>\n",
       "      <td>1851</td>\n",
       "      <td>1884</td>\n",
       "      <td>1894</td>\n",
       "      <td>1897</td>\n",
       "      <td>1927</td>\n",
       "      <td>1919</td>\n",
       "      <td>1884</td>\n",
       "      <td>1926</td>\n",
       "      <td>1902</td>\n",
       "      <td>1920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>2009</td>\n",
       "      <td>Custody Operations</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Temporary Beds (FamilyVisiting/Tank Beds)</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",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Institution  Year        Top Category         Subcategory  \\\n",
       "0        CCWF  2009  Custody Operations  Total Bed Capacity   \n",
       "1        CCWF  2009  Custody Operations  Total Bed Capacity   \n",
       "2        CCWF  2009  Custody Operations  Total Bed Capacity   \n",
       "3        CCWF  2009  Custody Operations  Total Bed Capacity   \n",
       "4        CCWF  2009  Custody Operations  Total Bed Capacity   \n",
       "\n",
       "                                Metric Title   Jan   Feb   Mar   Apr   May  \\\n",
       "0                              Contract Beds     0     0     0     0     0   \n",
       "1                                Design Beds  1916  1953  1945  1961  1969   \n",
       "2                       Non-Traditional Beds   265    62    72    72    36   \n",
       "3                          Overcrowding Beds  1801  1886  1851  1884  1894   \n",
       "4  Temporary Beds (FamilyVisiting/Tank Beds)     0     0     0     0     0   \n",
       "\n",
       "    Jun   Jul   Aug   Sep   Oct   Nov   Dec  \n",
       "0     0     0     0     0     0     0     0  \n",
       "1  1999  2007  1986  1966  2006  1969  1993  \n",
       "2     0     0    72    67     0     0     0  \n",
       "3  1897  1927  1919  1884  1926  1902  1920  \n",
       "4     0     0     0     0     0     0     0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load in 2009-2019 data\n",
    "df_2009_2019 = pd.read_csv(\"finished-csvs/finished-2009-2019-everything.csv\", \n",
    "                           encoding=\"utf-8\", dtype={'Month':months_dtype})\n",
    "df_2009_2019.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e57b12f6-41a9-4ba7-ac0f-b21cd9b3964e",
   "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>Institution</th>\n",
       "      <th>Year</th>\n",
       "      <th>Subcategory</th>\n",
       "      <th>Metric Title</th>\n",
       "      <th>Jan</th>\n",
       "      <th>Feb</th>\n",
       "      <th>Mar</th>\n",
       "      <th>Apr</th>\n",
       "      <th>May</th>\n",
       "      <th>Jun</th>\n",
       "      <th>Jul</th>\n",
       "      <th>Aug</th>\n",
       "      <th>Sep</th>\n",
       "      <th>Oct</th>\n",
       "      <th>Nov</th>\n",
       "      <th>Dec</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>2009</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Contract Beds</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",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>2009</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Design Beds</td>\n",
       "      <td>1916</td>\n",
       "      <td>1953</td>\n",
       "      <td>1945</td>\n",
       "      <td>1961</td>\n",
       "      <td>1969</td>\n",
       "      <td>1999</td>\n",
       "      <td>2007</td>\n",
       "      <td>1986</td>\n",
       "      <td>1966</td>\n",
       "      <td>2006</td>\n",
       "      <td>1969</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>2009</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Non-Traditional Beds</td>\n",
       "      <td>265</td>\n",
       "      <td>62</td>\n",
       "      <td>72</td>\n",
       "      <td>72</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>72</td>\n",
       "      <td>67</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>2009</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Overcrowding Beds</td>\n",
       "      <td>1801</td>\n",
       "      <td>1886</td>\n",
       "      <td>1851</td>\n",
       "      <td>1884</td>\n",
       "      <td>1894</td>\n",
       "      <td>1897</td>\n",
       "      <td>1927</td>\n",
       "      <td>1919</td>\n",
       "      <td>1884</td>\n",
       "      <td>1926</td>\n",
       "      <td>1902</td>\n",
       "      <td>1920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>2009</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Temporary Beds (FamilyVisiting/Tank Beds)</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",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201282</th>\n",
       "      <td>SVSP</td>\n",
       "      <td>2019</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Per 100 Staff</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.09</td>\n",
       "      <td>0.51</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.26</td>\n",
       "      <td>0.69</td>\n",
       "      <td>0.09</td>\n",
       "      <td>0.35</td>\n",
       "      <td>0.34</td>\n",
       "      <td>0.60</td>\n",
       "      <td>0.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201283</th>\n",
       "      <td>SVSP</td>\n",
       "      <td>2019</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Dismissals (Non-Medical)</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201284</th>\n",
       "      <td>SVSP</td>\n",
       "      <td>2019</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Adverse Actions Total (Medical)</td>\n",
       "      <td>2</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>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201285</th>\n",
       "      <td>SVSP</td>\n",
       "      <td>2019</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Per 100 Staff</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.09</td>\n",
       "      <td>0.09</td>\n",
       "      <td>0.08</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.09</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201286</th>\n",
       "      <td>SVSP</td>\n",
       "      <td>2019</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Dismissals (Medical)</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",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>201287 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Institution  Year         Subcategory  \\\n",
       "0             CCWF  2009  Total Bed Capacity   \n",
       "1             CCWF  2009  Total Bed Capacity   \n",
       "2             CCWF  2009  Total Bed Capacity   \n",
       "3             CCWF  2009  Total Bed Capacity   \n",
       "4             CCWF  2009  Total Bed Capacity   \n",
       "...            ...   ...                 ...   \n",
       "201282        SVSP  2019     Adverse Actions   \n",
       "201283        SVSP  2019     Adverse Actions   \n",
       "201284        SVSP  2019     Adverse Actions   \n",
       "201285        SVSP  2019     Adverse Actions   \n",
       "201286        SVSP  2019     Adverse Actions   \n",
       "\n",
       "                                     Metric Title   Jan   Feb   Mar   Apr  \\\n",
       "0                                   Contract Beds     0     0     0     0   \n",
       "1                                     Design Beds  1916  1953  1945  1961   \n",
       "2                            Non-Traditional Beds   265    62    72    72   \n",
       "3                               Overcrowding Beds  1801  1886  1851  1884   \n",
       "4       Temporary Beds (FamilyVisiting/Tank Beds)     0     0     0     0   \n",
       "...                                           ...   ...   ...   ...   ...   \n",
       "201282                              Per 100 Staff  0.44  0.09  0.51  0.00   \n",
       "201283                   Dismissals (Non-Medical)     0     1     1     0   \n",
       "201284            Adverse Actions Total (Medical)     2     1     1     1   \n",
       "201285                              Per 100 Staff  0.17  0.09  0.09  0.08   \n",
       "201286                       Dismissals (Medical)     0     0     0     0   \n",
       "\n",
       "         May   Jun   Jul   Aug   Sep   Oct   Nov   Dec  \n",
       "0          0     0     0     0     0     0     0     0  \n",
       "1       1969  1999  2007  1986  1966  2006  1969  1993  \n",
       "2         36     0     0    72    67     0     0     0  \n",
       "3       1894  1897  1927  1919  1884  1926  1902  1920  \n",
       "4          0     0     0     0     0     0     0     0  \n",
       "...      ...   ...   ...   ...   ...   ...   ...   ...  \n",
       "201282  0.00  0.26  0.69  0.09  0.35  0.34  0.60  0.51  \n",
       "201283     0     0     0     0     0     0     0     0  \n",
       "201284     0     0     0     0     0     1     0     0  \n",
       "201285  0.00  0.00  0.00  0.00  0.00  0.09  0.00  0.00  \n",
       "201286     0     0     0     0     0     0     0     0  \n",
       "\n",
       "[201287 rows x 16 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop Top Category column, which doesn't exist in later datasets\n",
    "df_2009_2019_no_top = df_2009_2019.drop(columns=['Top Category'], axis=1)\n",
    "df_2009_2019_no_top.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c0706c56-df87-4229-95cc-704d4d3887eb",
   "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>Institution</th>\n",
       "      <th>Subcategory</th>\n",
       "      <th>Metric Title</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Contract Beds</td>\n",
       "      <td>2009</td>\n",
       "      <td>Jan</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Design Beds</td>\n",
       "      <td>2009</td>\n",
       "      <td>Jan</td>\n",
       "      <td>1916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Non-Traditional Beds</td>\n",
       "      <td>2009</td>\n",
       "      <td>Jan</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Overcrowding Beds</td>\n",
       "      <td>2009</td>\n",
       "      <td>Jan</td>\n",
       "      <td>1801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Temporary Beds (FamilyVisiting/Tank Beds)</td>\n",
       "      <td>2009</td>\n",
       "      <td>Jan</td>\n",
       "      <td>0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2415439</th>\n",
       "      <td>SVSP</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Per 100 Staff</td>\n",
       "      <td>2019</td>\n",
       "      <td>Dec</td>\n",
       "      <td>0.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2415440</th>\n",
       "      <td>SVSP</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Dismissals (Non-Medical)</td>\n",
       "      <td>2019</td>\n",
       "      <td>Dec</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2415441</th>\n",
       "      <td>SVSP</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Adverse Actions Total (Medical)</td>\n",
       "      <td>2019</td>\n",
       "      <td>Dec</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2415442</th>\n",
       "      <td>SVSP</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Per 100 Staff</td>\n",
       "      <td>2019</td>\n",
       "      <td>Dec</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2415443</th>\n",
       "      <td>SVSP</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Dismissals (Medical)</td>\n",
       "      <td>2019</td>\n",
       "      <td>Dec</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2415444 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Institution         Subcategory  \\\n",
       "0              CCWF  Total Bed Capacity   \n",
       "1              CCWF  Total Bed Capacity   \n",
       "2              CCWF  Total Bed Capacity   \n",
       "3              CCWF  Total Bed Capacity   \n",
       "4              CCWF  Total Bed Capacity   \n",
       "...             ...                 ...   \n",
       "2415439        SVSP     Adverse Actions   \n",
       "2415440        SVSP     Adverse Actions   \n",
       "2415441        SVSP     Adverse Actions   \n",
       "2415442        SVSP     Adverse Actions   \n",
       "2415443        SVSP     Adverse Actions   \n",
       "\n",
       "                                      Metric Title  Year Month Value  \n",
       "0                                    Contract Beds  2009   Jan     0  \n",
       "1                                      Design Beds  2009   Jan  1916  \n",
       "2                             Non-Traditional Beds  2009   Jan   265  \n",
       "3                                Overcrowding Beds  2009   Jan  1801  \n",
       "4        Temporary Beds (FamilyVisiting/Tank Beds)  2009   Jan     0  \n",
       "...                                            ...   ...   ...   ...  \n",
       "2415439                              Per 100 Staff  2019   Dec  0.51  \n",
       "2415440                   Dismissals (Non-Medical)  2019   Dec     0  \n",
       "2415441            Adverse Actions Total (Medical)  2019   Dec     0  \n",
       "2415442                              Per 100 Staff  2019   Dec  0.00  \n",
       "2415443                       Dismissals (Medical)  2019   Dec     0  \n",
       "\n",
       "[2415444 rows x 6 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Melt the 2009-2019 to a long format\n",
    "df_2009_2019_melted = pd.melt(df_2009_2019_no_top, \n",
    "                              id_vars=['Institution','Subcategory','Metric Title','Year'], \n",
    "                              value_name='Value', var_name='Month', ignore_index=True)\n",
    "df_2009_2019_melted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "99c77f87-80d7-45e9-a470-73061506ac36",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Institution       object\n",
       "Subcategory       object\n",
       "Metric Title      object\n",
       "Year               int64\n",
       "Month           category\n",
       "Value             object\n",
       "dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert month to a categorical type\n",
    "df_2009_2019_melted = df_2009_2019_melted.astype({'Month':months_dtype})\n",
    "df_2009_2019_melted.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "15e42180-7b17-49ff-8b23-11676f5ce7ad",
   "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>Institution</th>\n",
       "      <th>Category</th>\n",
       "      <th>Subcategory</th>\n",
       "      <th>Metric Title</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Custody Operations</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Blueprint Crowding Capacity</td>\n",
       "      <td>Jan</td>\n",
       "      <td>2020</td>\n",
       "      <td>2964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Custody Operations</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Blueprint Crowding Capacity</td>\n",
       "      <td>Feb</td>\n",
       "      <td>2020</td>\n",
       "      <td>2964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Custody Operations</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Blueprint Crowding Capacity</td>\n",
       "      <td>Mar</td>\n",
       "      <td>2020</td>\n",
       "      <td>2964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Custody Operations</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Blueprint Crowding Capacity</td>\n",
       "      <td>Apr</td>\n",
       "      <td>2020</td>\n",
       "      <td>2964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Custody Operations</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Blueprint Crowding Capacity</td>\n",
       "      <td>May</td>\n",
       "      <td>2020</td>\n",
       "      <td>2964</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874555</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Administration</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Dismissals (Medical) 2</td>\n",
       "      <td>Feb</td>\n",
       "      <td>2023</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874556</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Administration</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Dismissals (Medical) 2</td>\n",
       "      <td>Mar</td>\n",
       "      <td>2023</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874557</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Administration</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Dismissals (Medical) 2</td>\n",
       "      <td>Apr</td>\n",
       "      <td>2023</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874558</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Administration</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Dismissals (Medical) 2</td>\n",
       "      <td>May</td>\n",
       "      <td>2023</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874559</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Administration</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Dismissals (Medical) 2</td>\n",
       "      <td>Jun</td>\n",
       "      <td>2023</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>874560 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Institution            Category         Subcategory  \\\n",
       "0             CCWF  Custody Operations  Total Bed Capacity   \n",
       "1             CCWF  Custody Operations  Total Bed Capacity   \n",
       "2             CCWF  Custody Operations  Total Bed Capacity   \n",
       "3             CCWF  Custody Operations  Total Bed Capacity   \n",
       "4             CCWF  Custody Operations  Total Bed Capacity   \n",
       "...            ...                 ...                 ...   \n",
       "874555         WSP      Administration     Adverse Actions   \n",
       "874556         WSP      Administration     Adverse Actions   \n",
       "874557         WSP      Administration     Adverse Actions   \n",
       "874558         WSP      Administration     Adverse Actions   \n",
       "874559         WSP      Administration     Adverse Actions   \n",
       "\n",
       "                       Metric Title Month  Year Value  \n",
       "0       Blueprint Crowding Capacity   Jan  2020  2964  \n",
       "1       Blueprint Crowding Capacity   Feb  2020  2964  \n",
       "2       Blueprint Crowding Capacity   Mar  2020  2964  \n",
       "3       Blueprint Crowding Capacity   Apr  2020  2964  \n",
       "4       Blueprint Crowding Capacity   May  2020  2964  \n",
       "...                             ...   ...   ...   ...  \n",
       "874555       Dismissals (Medical) 2   Feb  2023     0  \n",
       "874556       Dismissals (Medical) 2   Mar  2023     0  \n",
       "874557       Dismissals (Medical) 2   Apr  2023     0  \n",
       "874558       Dismissals (Medical) 2   May  2023     0  \n",
       "874559       Dismissals (Medical) 2   Jun  2023     0  \n",
       "\n",
       "[874560 rows x 7 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read 2020-2023 population data\n",
    "df_2020_2023_POP = pd.read_csv(\"finished-csvs/finished-2020-2023-population.csv\", encoding=\"utf-8\", dtype={'Month':months_dtype})\n",
    "df_2020_2023_POP.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "3394b0b7-e338-40b0-b867-5fc89f8a40c3",
   "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>Institution</th>\n",
       "      <th>Subcategory</th>\n",
       "      <th>Metric Title</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Blueprint Crowding Capacity</td>\n",
       "      <td>Jan</td>\n",
       "      <td>2020</td>\n",
       "      <td>2964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Blueprint Crowding Capacity</td>\n",
       "      <td>Feb</td>\n",
       "      <td>2020</td>\n",
       "      <td>2964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Blueprint Crowding Capacity</td>\n",
       "      <td>Mar</td>\n",
       "      <td>2020</td>\n",
       "      <td>2964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Blueprint Crowding Capacity</td>\n",
       "      <td>Apr</td>\n",
       "      <td>2020</td>\n",
       "      <td>2964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Blueprint Crowding Capacity</td>\n",
       "      <td>May</td>\n",
       "      <td>2020</td>\n",
       "      <td>2964</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874555</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Dismissals (Medical) 2</td>\n",
       "      <td>Feb</td>\n",
       "      <td>2023</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874556</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Dismissals (Medical) 2</td>\n",
       "      <td>Mar</td>\n",
       "      <td>2023</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874557</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Dismissals (Medical) 2</td>\n",
       "      <td>Apr</td>\n",
       "      <td>2023</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874558</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Dismissals (Medical) 2</td>\n",
       "      <td>May</td>\n",
       "      <td>2023</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874559</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Adverse Actions</td>\n",
       "      <td>Dismissals (Medical) 2</td>\n",
       "      <td>Jun</td>\n",
       "      <td>2023</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>874560 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Institution         Subcategory                 Metric Title Month  \\\n",
       "0             CCWF  Total Bed Capacity  Blueprint Crowding Capacity   Jan   \n",
       "1             CCWF  Total Bed Capacity  Blueprint Crowding Capacity   Feb   \n",
       "2             CCWF  Total Bed Capacity  Blueprint Crowding Capacity   Mar   \n",
       "3             CCWF  Total Bed Capacity  Blueprint Crowding Capacity   Apr   \n",
       "4             CCWF  Total Bed Capacity  Blueprint Crowding Capacity   May   \n",
       "...            ...                 ...                          ...   ...   \n",
       "874555         WSP     Adverse Actions       Dismissals (Medical) 2   Feb   \n",
       "874556         WSP     Adverse Actions       Dismissals (Medical) 2   Mar   \n",
       "874557         WSP     Adverse Actions       Dismissals (Medical) 2   Apr   \n",
       "874558         WSP     Adverse Actions       Dismissals (Medical) 2   May   \n",
       "874559         WSP     Adverse Actions       Dismissals (Medical) 2   Jun   \n",
       "\n",
       "        Year Value  \n",
       "0       2020  2964  \n",
       "1       2020  2964  \n",
       "2       2020  2964  \n",
       "3       2020  2964  \n",
       "4       2020  2964  \n",
       "...      ...   ...  \n",
       "874555  2023     0  \n",
       "874556  2023     0  \n",
       "874557  2023     0  \n",
       "874558  2023     0  \n",
       "874559  2023     0  \n",
       "\n",
       "[874560 rows x 6 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop category column, as it doesn't exist in other datasets for concatenation\n",
    "df_2020_2023_POP_no_cat = df_2020_2023_POP.drop(columns=['Category'], axis=1)\n",
    "df_2020_2023_POP_no_cat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "83dd0570-a0e6-4a23-9977-389fabb3cb15",
   "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>Subcategory</th>\n",
       "      <th>Institution</th>\n",
       "      <th>Metric Title</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>ASP</td>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>Jan</td>\n",
       "      <td>2020</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>ASP</td>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>Feb</td>\n",
       "      <td>2020</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>ASP</td>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>Mar</td>\n",
       "      <td>2020</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>ASP</td>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>Apr</td>\n",
       "      <td>2020</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>ASP</td>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>May</td>\n",
       "      <td>2020</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Subcategory Institution         Metric Title Month  Year Value\n",
       "0  Number of Incidents         ASP  Number of Incidents   Jan  2020    22\n",
       "1  Number of Incidents         ASP  Number of Incidents   Feb  2020    22\n",
       "2  Number of Incidents         ASP  Number of Incidents   Mar  2020    17\n",
       "3  Number of Incidents         ASP  Number of Incidents   Apr  2020    19\n",
       "4  Number of Incidents         ASP  Number of Incidents   May  2020    17"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read the incident data from same year as population, which we'll merge\n",
    "df_2020_2023_Incidents = pd.read_csv(\"finished-csvs/finished-2020-2023-incidents.csv\", encoding=\"utf-8\", dtype={'Month':months_dtype})\n",
    "df_2020_2023_Incidents.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "49db269d-fdb1-464b-b8c5-8789270e02eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop any duplicate rows (with all column values except 'Value' duplicated) in each of these three,\n",
    "# keeping the 2nd occurence as it is the more recent one\n",
    "df_2009_2019_melted.drop_duplicates(subset=['Institution', 'Subcategory', 'Metric Title', \n",
    "                                            'Year', 'Month'], \n",
    "                                    inplace=True, ignore_index=True, keep = 'last')\n",
    "df_2020_2023_POP_no_cat.drop_duplicates(subset=['Institution', 'Subcategory', 'Metric Title', \n",
    "                                            'Year', 'Month'], \n",
    "                                        inplace=True, ignore_index=True, keep='last')\n",
    "df_2020_2023_Incidents.drop_duplicates(subset=['Institution', 'Subcategory', 'Metric Title', \n",
    "                                            'Year', 'Month'], \n",
    "                                       inplace=True, ignore_index=True, keep='last')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6fa81677-7650-4de4-a67c-f55ada70ba58",
   "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>Institution</th>\n",
       "      <th>Subcategory</th>\n",
       "      <th>Metric Title</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>119230</th>\n",
       "      <td>CAC</td>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>Documented Use of Force</td>\n",
       "      <td>Dec</td>\n",
       "      <td>2019</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119231</th>\n",
       "      <td>CAC</td>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>Documented Use of Force</td>\n",
       "      <td>Jan</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119232</th>\n",
       "      <td>CAC</td>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>Documented Use of Force</td>\n",
       "      <td>Feb</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119233</th>\n",
       "      <td>CAC</td>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>Documented Use of Force</td>\n",
       "      <td>Mar</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119234</th>\n",
       "      <td>CAC</td>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>Documented Use of Force</td>\n",
       "      <td>Apr</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119235</th>\n",
       "      <td>CAC</td>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>Documented Use of Force</td>\n",
       "      <td>May</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3350025</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Mental Health</td>\n",
       "      <td>UOF Incidents Involving Mental Health Inmates</td>\n",
       "      <td>2023</td>\n",
       "      <td>Jun</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3350026</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Mental Health</td>\n",
       "      <td>UOF Incidents Involving Mental Health Inmates</td>\n",
       "      <td>2023</td>\n",
       "      <td>Jul</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3350027</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Mental Health</td>\n",
       "      <td>UOF Incidents Involving Mental Health Inmates</td>\n",
       "      <td>2023</td>\n",
       "      <td>Aug</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3350028</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Mental Health</td>\n",
       "      <td>UOF Incidents Involving Mental Health Inmates</td>\n",
       "      <td>2023</td>\n",
       "      <td>Sep</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3350029</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Mental Health</td>\n",
       "      <td>UOF Incidents Involving Mental Health Inmates</td>\n",
       "      <td>2023</td>\n",
       "      <td>Oct</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119241</th>\n",
       "      <td>CAC</td>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>Documented Use of Force</td>\n",
       "      <td>Nov</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119242</th>\n",
       "      <td>CAC</td>\n",
       "      <td>Number of Incidents</td>\n",
       "      <td>Documented Use of Force</td>\n",
       "      <td>Dec</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3350030 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Institution         Subcategory  \\\n",
       "0              CCWF  Total Bed Capacity   \n",
       "1              CCWF  Total Bed Capacity   \n",
       "2              CCWF  Total Bed Capacity   \n",
       "3              CCWF  Total Bed Capacity   \n",
       "4              CCWF  Total Bed Capacity   \n",
       "...             ...                 ...   \n",
       "3350025         WSP       Mental Health   \n",
       "3350026         WSP       Mental Health   \n",
       "3350027         WSP       Mental Health   \n",
       "3350028         WSP       Mental Health   \n",
       "3350029         WSP       Mental Health   \n",
       "\n",
       "                                          Metric Title  Year Month Value  \n",
       "0                                        Contract Beds  2009   Jan     0  \n",
       "1                                          Design Beds  2009   Jan  1916  \n",
       "2                                 Non-Traditional Beds  2009   Jan   265  \n",
       "3                                    Overcrowding Beds  2009   Jan  1801  \n",
       "4            Temporary Beds (FamilyVisiting/Tank Beds)  2009   Jan     0  \n",
       "...                                                ...   ...   ...   ...  \n",
       "3350025  UOF Incidents Involving Mental Health Inmates  2023   Jun    24  \n",
       "3350026  UOF Incidents Involving Mental Health Inmates  2023   Jul    22  \n",
       "3350027  UOF Incidents Involving Mental Health Inmates  2023   Aug    17  \n",
       "3350028  UOF Incidents Involving Mental Health Inmates  2023   Sep    23  \n",
       "3350029  UOF Incidents Involving Mental Health Inmates  2023   Oct     9  \n",
       "\n",
       "[3350030 rows x 6 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Note: errant values in pop dataframe -- deal with this below\n",
    "df_2020_2023_POP_no_cat.loc[(df_2020_2023_POP_no_cat['Institution'] == 'CAC') &\n",
    "                           (df_2020_2023_POP_no_cat['Metric Title'] == 'Documented Use of Force')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "79871665-3c4d-44cf-99e0-242deebee605",
   "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>Institution</th>\n",
       "      <th>Subcategory</th>\n",
       "      <th>Metric Title</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Value</th>\n",
       "      <th>file</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Contract Beds</td>\n",
       "      <td>2009</td>\n",
       "      <td>Jan</td>\n",
       "      <td>0</td>\n",
       "      <td>df_2009_2019_melted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Design Beds</td>\n",
       "      <td>2009</td>\n",
       "      <td>Jan</td>\n",
       "      <td>1916</td>\n",
       "      <td>df_2009_2019_melted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Non-Traditional Beds</td>\n",
       "      <td>2009</td>\n",
       "      <td>Jan</td>\n",
       "      <td>265</td>\n",
       "      <td>df_2009_2019_melted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Overcrowding Beds</td>\n",
       "      <td>2009</td>\n",
       "      <td>Jan</td>\n",
       "      <td>1801</td>\n",
       "      <td>df_2009_2019_melted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CCWF</td>\n",
       "      <td>Total Bed Capacity</td>\n",
       "      <td>Temporary Beds (FamilyVisiting/Tank Beds)</td>\n",
       "      <td>2009</td>\n",
       "      <td>Jan</td>\n",
       "      <td>0</td>\n",
       "      <td>df_2009_2019_melted</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Institution         Subcategory                               Metric Title  \\\n",
       "0        CCWF  Total Bed Capacity                              Contract Beds   \n",
       "1        CCWF  Total Bed Capacity                                Design Beds   \n",
       "2        CCWF  Total Bed Capacity                       Non-Traditional Beds   \n",
       "3        CCWF  Total Bed Capacity                          Overcrowding Beds   \n",
       "4        CCWF  Total Bed Capacity  Temporary Beds (FamilyVisiting/Tank Beds)   \n",
       "\n",
       "   Year Month Value                 file  \n",
       "0  2009   Jan     0  df_2009_2019_melted  \n",
       "1  2009   Jan  1916  df_2009_2019_melted  \n",
       "2  2009   Jan   265  df_2009_2019_melted  \n",
       "3  2009   Jan  1801  df_2009_2019_melted  \n",
       "4  2009   Jan     0  df_2009_2019_melted  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add column indicating which file row came from\n",
    "df_2009_2019_melted['file'] = 'df_2009_2019_melted'\n",
    "df_2020_2023_POP_no_cat['file'] = 'df_2020_2023_POP_no_cat'\n",
    "df_2020_2023_Incidents['file'] = 'df_2020_2023_Incidents'\n",
    "\n",
    "# Row bind all dataframes together\n",
    "df = pd.concat([df_2009_2019_melted, df_2020_2023_POP_no_cat, df_2020_2023_Incidents], \n",
    "               ignore_index=True, verify_integrity=True)\n",
    "\n",
    "# Drop duplicates based on all columns except file name\n",
    "df.drop_duplicates(subset=['Institution', 'Subcategory', 'Metric Title', \n",
    "                                            'Year', 'Month', 'Value'], \n",
    "                                       inplace=True, ignore_index=True, keep='first')\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "b52efcc4-b4ff-4306-8b4d-8281033b69c5",
   "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>Institution</th>\n",
       "      <th>Subcategory</th>\n",
       "      <th>Metric Title</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Value</th>\n",
       "      <th>file</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASP</td>\n",
       "      <td>ASU EOP Hub</td>\n",
       "      <td>Actual Population</td>\n",
       "      <td>2009</td>\n",
       "      <td>Jan</td>\n",
       "      <td>0</td>\n",
       "      <td>df_2009_2019_melted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ASP</td>\n",
       "      <td>ASU Overflow</td>\n",
       "      <td>Actual Population</td>\n",
       "      <td>2009</td>\n",
       "      <td>Jan</td>\n",
       "      <td>0</td>\n",
       "      <td>df_2009_2019_melted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ASP</td>\n",
       "      <td>Academic Programs -\\nAttendance Tracking</td>\n",
       "      <td>Average # Days in School (DIS)</td>\n",
       "      <td>2009</td>\n",
       "      <td>Jan</td>\n",
       "      <td>98</td>\n",
       "      <td>df_2009_2019_melted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ASP</td>\n",
       "      <td>Academic Programs -\\nAttendance Tracking</td>\n",
       "      <td>Average Daily Attendance</td>\n",
       "      <td>2009</td>\n",
       "      <td>Jan</td>\n",
       "      <td>663</td>\n",
       "      <td>df_2009_2019_melted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ASP</td>\n",
       "      <td>Academic Programs -\\nAttendance Tracking</td>\n",
       "      <td>Avg. Length of Time in Assignment(LTA)</td>\n",
       "      <td>2009</td>\n",
       "      <td>Jan</td>\n",
       "      <td>154</td>\n",
       "      <td>df_2009_2019_melted</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Institution                               Subcategory  \\\n",
       "0         ASP                               ASU EOP Hub   \n",
       "1         ASP                              ASU Overflow   \n",
       "2         ASP  Academic Programs -\\nAttendance Tracking   \n",
       "3         ASP  Academic Programs -\\nAttendance Tracking   \n",
       "4         ASP  Academic Programs -\\nAttendance Tracking   \n",
       "\n",
       "                             Metric Title  Year Month Value  \\\n",
       "0                       Actual Population  2009   Jan     0   \n",
       "1                       Actual Population  2009   Jan     0   \n",
       "2          Average # Days in School (DIS)  2009   Jan    98   \n",
       "3                Average Daily Attendance  2009   Jan   663   \n",
       "4  Avg. Length of Time in Assignment(LTA)  2009   Jan   154   \n",
       "\n",
       "                  file  \n",
       "0  df_2009_2019_melted  \n",
       "1  df_2009_2019_melted  \n",
       "2  df_2009_2019_melted  \n",
       "3  df_2009_2019_melted  \n",
       "4  df_2009_2019_melted  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sorted = df.sort_values(by=['Year','Month','Institution','Subcategory','Metric Title'], \n",
    "                           ignore_index=True)\n",
    "df_sorted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "31c72d0f-e84f-4882-840e-ed8e548f759e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# See how many rows are duplicates based on all columns except 'Value'\n",
    "dup = df_sorted[df_sorted.duplicated(subset=['Institution', 'Subcategory', 'Metric Title',\n",
    "                                                  'Year', 'Month'],\n",
    "                                    keep = False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "46ec3436-3bba-479d-9573-aa9239e94216",
   "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>Institution</th>\n",
       "      <th>Subcategory</th>\n",
       "      <th>Metric Title</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Value</th>\n",
       "      <th>file</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2257733</th>\n",
       "      <td>ASP</td>\n",
       "      <td>FMLA</td>\n",
       "      <td>Medical</td>\n",
       "      <td>2019</td>\n",
       "      <td>Dec</td>\n",
       "      <td>624</td>\n",
       "      <td>df_2020_2023_POP_no_cat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2257736</th>\n",
       "      <td>ASP</td>\n",
       "      <td>FMLA</td>\n",
       "      <td>Non-Custody</td>\n",
       "      <td>2019</td>\n",
       "      <td>Dec</td>\n",
       "      <td>939</td>\n",
       "      <td>df_2020_2023_POP_no_cat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2258096</th>\n",
       "      <td>ASP</td>\n",
       "      <td>Inmate Disciplinaries</td>\n",
       "      <td>Administrative RVRs</td>\n",
       "      <td>2019</td>\n",
       "      <td>Dec</td>\n",
       "      <td>11</td>\n",
       "      <td>df_2020_2023_POP_no_cat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2258105</th>\n",
       "      <td>ASP</td>\n",
       "      <td>Inmate Disciplinaries</td>\n",
       "      <td>Battery on a Prisoner</td>\n",
       "      <td>2019</td>\n",
       "      <td>Dec</td>\n",
       "      <td>7</td>\n",
       "      <td>df_2020_2023_POP_no_cat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2258109</th>\n",
       "      <td>ASP</td>\n",
       "      <td>Inmate Disciplinaries</td>\n",
       "      <td>Conduct</td>\n",
       "      <td>2019</td>\n",
       "      <td>Dec</td>\n",
       "      <td>30</td>\n",
       "      <td>df_2020_2023_POP_no_cat</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Institution            Subcategory           Metric Title  Year Month  \\\n",
       "2257733         ASP                   FMLA                Medical  2019   Dec   \n",
       "2257736         ASP                   FMLA            Non-Custody  2019   Dec   \n",
       "2258096         ASP  Inmate Disciplinaries    Administrative RVRs  2019   Dec   \n",
       "2258105         ASP  Inmate Disciplinaries  Battery on a Prisoner  2019   Dec   \n",
       "2258109         ASP  Inmate Disciplinaries                Conduct  2019   Dec   \n",
       "\n",
       "        Value                     file  \n",
       "2257733   624  df_2020_2023_POP_no_cat  \n",
       "2257736   939  df_2020_2023_POP_no_cat  \n",
       "2258096    11  df_2020_2023_POP_no_cat  \n",
       "2258105     7  df_2020_2023_POP_no_cat  \n",
       "2258109    30  df_2020_2023_POP_no_cat  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Most of the dups are with 2020_2023_pop file having value of '0' or '-'\n",
    "# Check values where this is not true\n",
    "dup.loc[(dup['file'] == 'df_2020_2023_POP_no_cat') & \n",
    "        (dup['Value'] != '0') & (dup['Value'] != '-')].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "f1a485e6-ce68-4204-ad33-37dd9558732f",
   "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>Institution</th>\n",
       "      <th>Subcategory</th>\n",
       "      <th>Metric Title</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Value</th>\n",
       "      <th>file</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2289769</th>\n",
       "      <td>ASP</td>\n",
       "      <td>Type of Force</td>\n",
       "      <td>Baton</td>\n",
       "      <td>2020</td>\n",
       "      <td>Jan</td>\n",
       "      <td>-</td>\n",
       "      <td>df_2020_2023_POP_no_cat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2289775</th>\n",
       "      <td>ASP</td>\n",
       "      <td>Type of Force</td>\n",
       "      <td>OC</td>\n",
       "      <td>2020</td>\n",
       "      <td>Jan</td>\n",
       "      <td>-</td>\n",
       "      <td>df_2020_2023_POP_no_cat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2289777</th>\n",
       "      <td>ASP</td>\n",
       "      <td>Type of Force</td>\n",
       "      <td>Other</td>\n",
       "      <td>2020</td>\n",
       "      <td>Jan</td>\n",
       "      <td>-</td>\n",
       "      <td>df_2020_2023_POP_no_cat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2289779</th>\n",
       "      <td>ASP</td>\n",
       "      <td>Type of Force</td>\n",
       "      <td>Physical Force</td>\n",
       "      <td>2020</td>\n",
       "      <td>Jan</td>\n",
       "      <td>-</td>\n",
       "      <td>df_2020_2023_POP_no_cat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2290435</th>\n",
       "      <td>CAC</td>\n",
       "      <td>Type of Force</td>\n",
       "      <td>Baton</td>\n",
       "      <td>2020</td>\n",
       "      <td>Jan</td>\n",
       "      <td>-</td>\n",
       "      <td>df_2020_2023_POP_no_cat</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3350025</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Type of Force</td>\n",
       "      <td>37 mm/40 mm</td>\n",
       "      <td>2020</td>\n",
       "      <td>Dec</td>\n",
       "      <td>-</td>\n",
       "      <td>df_2020_2023_POP_no_cat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3350026</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Type of Force</td>\n",
       "      <td>Baton</td>\n",
       "      <td>2020</td>\n",
       "      <td>Dec</td>\n",
       "      <td>-</td>\n",
       "      <td>df_2020_2023_POP_no_cat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3350027</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Type of Force</td>\n",
       "      <td>OC</td>\n",
       "      <td>2020</td>\n",
       "      <td>Dec</td>\n",
       "      <td>-</td>\n",
       "      <td>df_2020_2023_POP_no_cat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3350028</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Type of Force</td>\n",
       "      <td>Other</td>\n",
       "      <td>2020</td>\n",
       "      <td>Dec</td>\n",
       "      <td>-</td>\n",
       "      <td>df_2020_2023_POP_no_cat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3350029</th>\n",
       "      <td>WSP</td>\n",
       "      <td>Type of Force</td>\n",
       "      <td>Physical Force</td>\n",
       "      <td>2020</td>\n",
       "      <td>Dec</td>\n",
       "      <td>-</td>\n",
       "      <td>df_2020_2023_POP_no_cat</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3350030 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Institution                               Subcategory  \\\n",
       "0               ASP                               ASU EOP Hub   \n",
       "1               ASP                              ASU Overflow   \n",
       "2               ASP  Academic Programs -\\nAttendance Tracking   \n",
       "3               ASP  Academic Programs -\\nAttendance Tracking   \n",
       "4               ASP  Academic Programs -\\nAttendance Tracking   \n",
       "...             ...                                       ...   \n",
       "3350025         WSP                             Type of Force   \n",
       "3350026         WSP                             Type of Force   \n",
       "3350027         WSP                             Type of Force   \n",
       "3350028         WSP                             Type of Force   \n",
       "3350029         WSP                             Type of Force   \n",
       "\n",
       "                                   Metric Title  Year Month Value  \n",
       "0                             Actual Population  2009   Jan     0  \n",
       "1                             Actual Population  2009   Jan     0  \n",
       "2                Average # Days in School (DIS)  2009   Jan    98  \n",
       "3                      Average Daily Attendance  2009   Jan   663  \n",
       "4        Avg. Length of Time in Assignment(LTA)  2009   Jan   154  \n",
       "...                                         ...   ...   ...   ...  \n",
       "3350025                             37 mm/40 mm  2023   Oct     2  \n",
       "3350026                                   Baton  2023   Oct     1  \n",
       "3350027                  Non-Conventional Force  2023   Oct     0  \n",
       "3350028                                      OC  2023   Oct     8  \n",
       "3350029                          Physical Force  2023   Oct     2  \n",
       "\n",
       "[3350030 rows x 6 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dup.loc[(dup['file'] == 'df_2020_2023_POP_no_cat') & (dup['Value'] == '-')] # lots of '-' Value in 2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "96788669-819c-43c7-a915-aa5380521477",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write final merged dataframe to CSV\n",
    "df_final.to_csv(\"finished-csvs/all-data-combined-long-format.csv\", encoding=\"utf-8\", index=False)"
   ]
  }
 ],
 "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}