create_suspension_dataset.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>#</th>\n",
       "      <th>School Name</th>\n",
       "      <th>Auth (14)</th>\n",
       "      <th>Program (16)</th>\n",
       "      <th>Charter (14)</th>\n",
       "      <th>Facility (4)</th>\n",
       "      <th>School Code</th>\n",
       "      <th>School Type</th>\n",
       "      <th>Note</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18</td>\n",
       "      <td>Greenwood Academy</td>\n",
       "      <td>WCCUSD</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCCUSD School</td>\n",
       "      <td>No</td>\n",
       "      <td>0733253</td>\n",
       "      <td>Alternative</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>63</td>\n",
       "      <td>Vista Independent Study/Virtual Academy</td>\n",
       "      <td>WCCUSD</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCCUSD School</td>\n",
       "      <td>No</td>\n",
       "      <td>0730325</td>\n",
       "      <td>Alternative</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Aspire Richmond Technology Academy</td>\n",
       "      <td>WCCUSD</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCC Charter</td>\n",
       "      <td>Yes</td>\n",
       "      <td>0132118</td>\n",
       "      <td>Elementary</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Bayview Elementary</td>\n",
       "      <td>WCCUSD</td>\n",
       "      <td>ILCoP</td>\n",
       "      <td>WCCUSD School</td>\n",
       "      <td>No</td>\n",
       "      <td>6004600</td>\n",
       "      <td>Elementary</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>Cesar E. Chavez Elementary</td>\n",
       "      <td>WCCUSD</td>\n",
       "      <td>ILCoP</td>\n",
       "      <td>WCCUSD School</td>\n",
       "      <td>No</td>\n",
       "      <td>6114094</td>\n",
       "      <td>Elementary</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>1</td>\n",
       "      <td>Aspire Ca College Preparatory Academy</td>\n",
       "      <td>WCCUSD</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCC Charter</td>\n",
       "      <td>Yes</td>\n",
       "      <td>0132100</td>\n",
       "      <td>Middle-High</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>25</td>\n",
       "      <td>Invictus Academy of Richmond</td>\n",
       "      <td>CCCOE</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCC Charter</td>\n",
       "      <td>No</td>\n",
       "      <td>0137026</td>\n",
       "      <td>Middle-High</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>35</td>\n",
       "      <td>Making Waves Academy</td>\n",
       "      <td>CCCOE</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCC Charter</td>\n",
       "      <td>No</td>\n",
       "      <td>0114470</td>\n",
       "      <td>Middle-High</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>58</td>\n",
       "      <td>Summit: K2</td>\n",
       "      <td>CCCOE</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCC Charter</td>\n",
       "      <td>Yes</td>\n",
       "      <td>0129684</td>\n",
       "      <td>Middle-High</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>59</td>\n",
       "      <td>Summit: Tamalpais</td>\n",
       "      <td>WCCUSD</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCC Charter</td>\n",
       "      <td>No</td>\n",
       "      <td>0133637</td>\n",
       "      <td>Middle-High</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>66 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     #                              School Name Auth (14) Program (16)  \\\n",
       "0   18                        Greenwood Academy    WCCUSD    Non-ILCoP   \n",
       "1   63  Vista Independent Study/Virtual Academy    WCCUSD    Non-ILCoP   \n",
       "2    2       Aspire Richmond Technology Academy    WCCUSD    Non-ILCoP   \n",
       "3    3                       Bayview Elementary    WCCUSD        ILCoP   \n",
       "4    6               Cesar E. Chavez Elementary    WCCUSD        ILCoP   \n",
       "..  ..                                      ...       ...          ...   \n",
       "61   1    Aspire Ca College Preparatory Academy    WCCUSD    Non-ILCoP   \n",
       "62  25             Invictus Academy of Richmond     CCCOE    Non-ILCoP   \n",
       "63  35                     Making Waves Academy     CCCOE    Non-ILCoP   \n",
       "64  58                               Summit: K2     CCCOE    Non-ILCoP   \n",
       "65  59                        Summit: Tamalpais    WCCUSD    Non-ILCoP   \n",
       "\n",
       "    Charter (14)  Facility (4) School Code  School Type Note  \n",
       "0   WCCUSD School           No     0733253  Alternative  NaN  \n",
       "1   WCCUSD School           No     0730325  Alternative  NaN  \n",
       "2     WCC Charter          Yes     0132118   Elementary  NaN  \n",
       "3   WCCUSD School           No     6004600   Elementary  NaN  \n",
       "4   WCCUSD School           No     6114094   Elementary  NaN  \n",
       "..            ...          ...         ...          ...  ...  \n",
       "61    WCC Charter          Yes     0132100  Middle-High  NaN  \n",
       "62    WCC Charter           No     0137026  Middle-High  NaN  \n",
       "63    WCC Charter           No     0114470  Middle-High  NaN  \n",
       "64    WCC Charter          Yes     0129684  Middle-High  NaN  \n",
       "65    WCC Charter           No     0133637  Middle-High  NaN  \n",
       "\n",
       "[66 rows x 9 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wcc_schools_df = pd.read_csv('cef_provided_files/cef_school_list.csv', encoding='ISO-8859-1',dtype=str)\n",
    "wcc_schools_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2023"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>academic_year</th>\n",
       "      <th>aggregate_level</th>\n",
       "      <th>county_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>school_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>district_name</th>\n",
       "      <th>school_name</th>\n",
       "      <th>charter_yn</th>\n",
       "      <th>reporting_category</th>\n",
       "      <th>cumulative_enrollment</th>\n",
       "      <th>total_suspensions</th>\n",
       "      <th>unduplicated_students_suspended_total</th>\n",
       "      <th>unduplicated_students_suspended_defiance</th>\n",
       "      <th>suspension_rate_total</th>\n",
       "      <th>suspensions_violent_injury</th>\n",
       "      <th>suspensions_violent_no_injury</th>\n",
       "      <th>suspensions_weapons</th>\n",
       "      <th>suspensions_drugs</th>\n",
       "      <th>suspensions_defiance</th>\n",
       "      <th>suspensions_other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>106188</td>\n",
       "      <td>3043</td>\n",
       "      <td>2179</td>\n",
       "      <td>80</td>\n",
       "      <td>2.1</td>\n",
       "      <td>779</td>\n",
       "      <td>1312</td>\n",
       "      <td>86</td>\n",
       "      <td>677</td>\n",
       "      <td>95</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>113568</td>\n",
       "      <td>7030</td>\n",
       "      <td>4448</td>\n",
       "      <td>223</td>\n",
       "      <td>3.9</td>\n",
       "      <td>1616</td>\n",
       "      <td>3573</td>\n",
       "      <td>284</td>\n",
       "      <td>876</td>\n",
       "      <td>264</td>\n",
       "      <td>417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GX</td>\n",
       "      <td>294</td>\n",
       "      <td>19</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>5.1</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RA</td>\n",
       "      <td>61753</td>\n",
       "      <td>684</td>\n",
       "      <td>528</td>\n",
       "      <td>16</td>\n",
       "      <td>0.9</td>\n",
       "      <td>111</td>\n",
       "      <td>384</td>\n",
       "      <td>38</td>\n",
       "      <td>85</td>\n",
       "      <td>19</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RB</td>\n",
       "      <td>18805</td>\n",
       "      <td>2807</td>\n",
       "      <td>1650</td>\n",
       "      <td>78</td>\n",
       "      <td>8.8</td>\n",
       "      <td>895</td>\n",
       "      <td>1365</td>\n",
       "      <td>66</td>\n",
       "      <td>271</td>\n",
       "      <td>96</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226174</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SF</td>\n",
       "      <td>5408</td>\n",
       "      <td>369</td>\n",
       "      <td>223</td>\n",
       "      <td>19</td>\n",
       "      <td>4.1</td>\n",
       "      <td>65</td>\n",
       "      <td>191</td>\n",
       "      <td>6</td>\n",
       "      <td>57</td>\n",
       "      <td>32</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226175</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SH</td>\n",
       "      <td>25463</td>\n",
       "      <td>801</td>\n",
       "      <td>559</td>\n",
       "      <td>47</td>\n",
       "      <td>2.2</td>\n",
       "      <td>105</td>\n",
       "      <td>434</td>\n",
       "      <td>29</td>\n",
       "      <td>142</td>\n",
       "      <td>54</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226176</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SM</td>\n",
       "      <td>1442</td>\n",
       "      <td>59</td>\n",
       "      <td>42</td>\n",
       "      <td>5</td>\n",
       "      <td>2.9</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226177</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>495182</td>\n",
       "      <td>14564</td>\n",
       "      <td>10423</td>\n",
       "      <td>773</td>\n",
       "      <td>2.1</td>\n",
       "      <td>2134</td>\n",
       "      <td>7629</td>\n",
       "      <td>545</td>\n",
       "      <td>2652</td>\n",
       "      <td>985</td>\n",
       "      <td>619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226178</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>787503</td>\n",
       "      <td>18835</td>\n",
       "      <td>13614</td>\n",
       "      <td>1007</td>\n",
       "      <td>1.7</td>\n",
       "      <td>2810</td>\n",
       "      <td>9883</td>\n",
       "      <td>718</td>\n",
       "      <td>3365</td>\n",
       "      <td>1277</td>\n",
       "      <td>782</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>226179 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       academic_year aggregate_level county_code district_code school_code  \\\n",
       "0            2022-23               C          01           NaN         NaN   \n",
       "1            2022-23               C          01           NaN         NaN   \n",
       "2            2022-23               C          01           NaN         NaN   \n",
       "3            2022-23               C          01           NaN         NaN   \n",
       "4            2022-23               C          01           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "226174       2022-23               T          00           NaN         NaN   \n",
       "226175       2022-23               T          00           NaN         NaN   \n",
       "226176       2022-23               T          00           NaN         NaN   \n",
       "226177       2022-23               T          00           NaN         NaN   \n",
       "226178       2022-23               T          00           NaN         NaN   \n",
       "\n",
       "       county_name district_name school_name charter_yn reporting_category  \\\n",
       "0          Alameda           NaN         NaN        All                 GF   \n",
       "1          Alameda           NaN         NaN        All                 GM   \n",
       "2          Alameda           NaN         NaN        All                 GX   \n",
       "3          Alameda           NaN         NaN        All                 RA   \n",
       "4          Alameda           NaN         NaN        All                 RB   \n",
       "...            ...           ...         ...        ...                ...   \n",
       "226174       State           NaN         NaN        Yes                 SF   \n",
       "226175       State           NaN         NaN        Yes                 SH   \n",
       "226176       State           NaN         NaN        Yes                 SM   \n",
       "226177       State           NaN         NaN        Yes                 SS   \n",
       "226178       State           NaN         NaN        Yes                 TA   \n",
       "\n",
       "       cumulative_enrollment total_suspensions  \\\n",
       "0                     106188              3043   \n",
       "1                     113568              7030   \n",
       "2                        294                19   \n",
       "3                      61753               684   \n",
       "4                      18805              2807   \n",
       "...                      ...               ...   \n",
       "226174                  5408               369   \n",
       "226175                 25463               801   \n",
       "226176                  1442                59   \n",
       "226177                495182             14564   \n",
       "226178                787503             18835   \n",
       "\n",
       "       unduplicated_students_suspended_total  \\\n",
       "0                                       2179   \n",
       "1                                       4448   \n",
       "2                                         15   \n",
       "3                                        528   \n",
       "4                                       1650   \n",
       "...                                      ...   \n",
       "226174                                   223   \n",
       "226175                                   559   \n",
       "226176                                    42   \n",
       "226177                                 10423   \n",
       "226178                                 13614   \n",
       "\n",
       "       unduplicated_students_suspended_defiance suspension_rate_total  \\\n",
       "0                                            80                   2.1   \n",
       "1                                           223                   3.9   \n",
       "2                                             0                   5.1   \n",
       "3                                            16                   0.9   \n",
       "4                                            78                   8.8   \n",
       "...                                         ...                   ...   \n",
       "226174                                       19                   4.1   \n",
       "226175                                       47                   2.2   \n",
       "226176                                        5                   2.9   \n",
       "226177                                      773                   2.1   \n",
       "226178                                     1007                   1.7   \n",
       "\n",
       "       suspensions_violent_injury suspensions_violent_no_injury  \\\n",
       "0                             779                          1312   \n",
       "1                            1616                          3573   \n",
       "2                               3                            11   \n",
       "3                             111                           384   \n",
       "4                             895                          1365   \n",
       "...                           ...                           ...   \n",
       "226174                         65                           191   \n",
       "226175                        105                           434   \n",
       "226176                          6                            28   \n",
       "226177                       2134                          7629   \n",
       "226178                       2810                          9883   \n",
       "\n",
       "       suspensions_weapons suspensions_drugs suspensions_defiance  \\\n",
       "0                       86               677                   95   \n",
       "1                      284               876                  264   \n",
       "2                        0                 4                    0   \n",
       "3                       38                85                   19   \n",
       "4                       66               271                   96   \n",
       "...                    ...               ...                  ...   \n",
       "226174                   6                57                   32   \n",
       "226175                  29               142                   54   \n",
       "226176                   2                15                    6   \n",
       "226177                 545              2652                  985   \n",
       "226178                 718              3365                 1277   \n",
       "\n",
       "       suspensions_other  \n",
       "0                     94  \n",
       "1                    417  \n",
       "2                      1  \n",
       "3                     47  \n",
       "4                    114  \n",
       "...                  ...  \n",
       "226174                18  \n",
       "226175                37  \n",
       "226176                 2  \n",
       "226177               619  \n",
       "226178               782  \n",
       "\n",
       "[226179 rows x 21 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "suspension23_df = pd.read_csv('suspension_data_csv_files/suspension23.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "suspension23_df.columns = [\n",
    "    'academic_year',\n",
    "    'aggregate_level',\n",
    "    'county_code',\n",
    "    'district_code',\n",
    "    'school_code',\n",
    "    'county_name',\n",
    "    'district_name',\n",
    "    'school_name',\n",
    "    'charter_yn',\n",
    "    'reporting_category',\n",
    "    'cumulative_enrollment',\n",
    "    'total_suspensions',\n",
    "    'unduplicated_students_suspended_total',\n",
    "    'unduplicated_students_suspended_defiance',\n",
    "    'suspension_rate_total',\n",
    "    'suspensions_violent_injury',\n",
    "    'suspensions_violent_no_injury',\n",
    "    'suspensions_weapons',\n",
    "    'suspensions_drugs',\n",
    "    'suspensions_defiance',\n",
    "    'suspensions_other' \n",
    "]\n",
    "\n",
    "suspension23_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2022"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>academic_year</th>\n",
       "      <th>aggregate_level</th>\n",
       "      <th>county_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>school_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>district_name</th>\n",
       "      <th>school_name</th>\n",
       "      <th>charter_yn</th>\n",
       "      <th>reporting_category</th>\n",
       "      <th>cumulative_enrollment</th>\n",
       "      <th>total_suspensions</th>\n",
       "      <th>unduplicated_students_suspended_total</th>\n",
       "      <th>unduplicated_students_suspended_defiance</th>\n",
       "      <th>suspension_rate_total</th>\n",
       "      <th>suspensions_violent_injury</th>\n",
       "      <th>suspensions_violent_no_injury</th>\n",
       "      <th>suspensions_weapons</th>\n",
       "      <th>suspensions_drugs</th>\n",
       "      <th>suspensions_defiance</th>\n",
       "      <th>suspensions_other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>107288</td>\n",
       "      <td>2627</td>\n",
       "      <td>1875</td>\n",
       "      <td>127</td>\n",
       "      <td>1.7</td>\n",
       "      <td>743</td>\n",
       "      <td>1195</td>\n",
       "      <td>84</td>\n",
       "      <td>379</td>\n",
       "      <td>146</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>114785</td>\n",
       "      <td>6214</td>\n",
       "      <td>4066</td>\n",
       "      <td>263</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1426</td>\n",
       "      <td>3167</td>\n",
       "      <td>291</td>\n",
       "      <td>625</td>\n",
       "      <td>302</td>\n",
       "      <td>403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GX</td>\n",
       "      <td>202</td>\n",
       "      <td>11</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RA</td>\n",
       "      <td>62116</td>\n",
       "      <td>513</td>\n",
       "      <td>413</td>\n",
       "      <td>37</td>\n",
       "      <td>0.7</td>\n",
       "      <td>97</td>\n",
       "      <td>261</td>\n",
       "      <td>35</td>\n",
       "      <td>50</td>\n",
       "      <td>39</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RB</td>\n",
       "      <td>19528</td>\n",
       "      <td>2734</td>\n",
       "      <td>1669</td>\n",
       "      <td>95</td>\n",
       "      <td>8.5</td>\n",
       "      <td>945</td>\n",
       "      <td>1312</td>\n",
       "      <td>71</td>\n",
       "      <td>190</td>\n",
       "      <td>115</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225348</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SF</td>\n",
       "      <td>5299</td>\n",
       "      <td>240</td>\n",
       "      <td>176</td>\n",
       "      <td>11</td>\n",
       "      <td>3.3</td>\n",
       "      <td>34</td>\n",
       "      <td>130</td>\n",
       "      <td>13</td>\n",
       "      <td>44</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225349</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SH</td>\n",
       "      <td>22073</td>\n",
       "      <td>550</td>\n",
       "      <td>391</td>\n",
       "      <td>21</td>\n",
       "      <td>1.8</td>\n",
       "      <td>85</td>\n",
       "      <td>285</td>\n",
       "      <td>35</td>\n",
       "      <td>100</td>\n",
       "      <td>21</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225350</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SM</td>\n",
       "      <td>1361</td>\n",
       "      <td>57</td>\n",
       "      <td>44</td>\n",
       "      <td>2</td>\n",
       "      <td>3.2</td>\n",
       "      <td>7</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225351</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>474651</td>\n",
       "      <td>11404</td>\n",
       "      <td>8408</td>\n",
       "      <td>619</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1559</td>\n",
       "      <td>6057</td>\n",
       "      <td>546</td>\n",
       "      <td>1967</td>\n",
       "      <td>694</td>\n",
       "      <td>581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225352</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>775005</td>\n",
       "      <td>15061</td>\n",
       "      <td>11196</td>\n",
       "      <td>827</td>\n",
       "      <td>1.4</td>\n",
       "      <td>2105</td>\n",
       "      <td>7996</td>\n",
       "      <td>690</td>\n",
       "      <td>2526</td>\n",
       "      <td>927</td>\n",
       "      <td>817</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>225353 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       academic_year aggregate_level county_code district_code school_code  \\\n",
       "0            2021-22               C          01           NaN         NaN   \n",
       "1            2021-22               C          01           NaN         NaN   \n",
       "2            2021-22               C          01           NaN         NaN   \n",
       "3            2021-22               C          01           NaN         NaN   \n",
       "4            2021-22               C          01           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "225348       2021-22               T          00           NaN         NaN   \n",
       "225349       2021-22               T          00           NaN         NaN   \n",
       "225350       2021-22               T          00           NaN         NaN   \n",
       "225351       2021-22               T          00           NaN         NaN   \n",
       "225352       2021-22               T          00           NaN         NaN   \n",
       "\n",
       "       county_name district_name school_name charter_yn reporting_category  \\\n",
       "0          Alameda           NaN         NaN        All                 GF   \n",
       "1          Alameda           NaN         NaN        All                 GM   \n",
       "2          Alameda           NaN         NaN        All                 GX   \n",
       "3          Alameda           NaN         NaN        All                 RA   \n",
       "4          Alameda           NaN         NaN        All                 RB   \n",
       "...            ...           ...         ...        ...                ...   \n",
       "225348       State           NaN         NaN        Yes                 SF   \n",
       "225349       State           NaN         NaN        Yes                 SH   \n",
       "225350       State           NaN         NaN        Yes                 SM   \n",
       "225351       State           NaN         NaN        Yes                 SS   \n",
       "225352       State           NaN         NaN        Yes                 TA   \n",
       "\n",
       "       cumulative_enrollment total_suspensions  \\\n",
       "0                     107288              2627   \n",
       "1                     114785              6214   \n",
       "2                        202                11   \n",
       "3                      62116               513   \n",
       "4                      19528              2734   \n",
       "...                      ...               ...   \n",
       "225348                  5299               240   \n",
       "225349                 22073               550   \n",
       "225350                  1361                57   \n",
       "225351                474651             11404   \n",
       "225352                775005             15061   \n",
       "\n",
       "       unduplicated_students_suspended_total  \\\n",
       "0                                       1875   \n",
       "1                                       4066   \n",
       "2                                          9   \n",
       "3                                        413   \n",
       "4                                       1669   \n",
       "...                                      ...   \n",
       "225348                                   176   \n",
       "225349                                   391   \n",
       "225350                                    44   \n",
       "225351                                  8408   \n",
       "225352                                 11196   \n",
       "\n",
       "       unduplicated_students_suspended_defiance suspension_rate_total  \\\n",
       "0                                           127                   1.7   \n",
       "1                                           263                   3.5   \n",
       "2                                             0                   4.5   \n",
       "3                                            37                   0.7   \n",
       "4                                            95                   8.5   \n",
       "...                                         ...                   ...   \n",
       "225348                                       11                   3.3   \n",
       "225349                                       21                   1.8   \n",
       "225350                                        2                   3.2   \n",
       "225351                                      619                   1.8   \n",
       "225352                                      827                   1.4   \n",
       "\n",
       "       suspensions_violent_injury suspensions_violent_no_injury  \\\n",
       "0                             743                          1195   \n",
       "1                            1426                          3167   \n",
       "2                               4                             4   \n",
       "3                              97                           261   \n",
       "4                             945                          1312   \n",
       "...                           ...                           ...   \n",
       "225348                         34                           130   \n",
       "225349                         85                           285   \n",
       "225350                          7                            28   \n",
       "225351                       1559                          6057   \n",
       "225352                       2105                          7996   \n",
       "\n",
       "       suspensions_weapons suspensions_drugs suspensions_defiance  \\\n",
       "0                       84               379                  146   \n",
       "1                      291               625                  302   \n",
       "2                        0                 1                    0   \n",
       "3                       35                50                   39   \n",
       "4                       71               190                  115   \n",
       "...                    ...               ...                  ...   \n",
       "225348                  13                44                   11   \n",
       "225349                  35               100                   21   \n",
       "225350                   1                16                    2   \n",
       "225351                 546              1967                  694   \n",
       "225352                 690              2526                  927   \n",
       "\n",
       "       suspensions_other  \n",
       "0                     80  \n",
       "1                    403  \n",
       "2                      2  \n",
       "3                     31  \n",
       "4                    101  \n",
       "...                  ...  \n",
       "225348                 8  \n",
       "225349                24  \n",
       "225350                 3  \n",
       "225351               581  \n",
       "225352               817  \n",
       "\n",
       "[225353 rows x 21 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "suspension22_df = pd.read_csv('suspension_data_csv_files/suspension22-v2.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "suspension22_df.columns = [\n",
    "    'academic_year',\n",
    "    'aggregate_level',\n",
    "    'county_code',\n",
    "    'district_code',\n",
    "    'school_code',\n",
    "    'county_name',\n",
    "    'district_name',\n",
    "    'school_name',\n",
    "    'charter_yn',\n",
    "    'reporting_category',\n",
    "    'cumulative_enrollment',\n",
    "    'total_suspensions',\n",
    "    'unduplicated_students_suspended_total',\n",
    "    'unduplicated_students_suspended_defiance',\n",
    "    'suspension_rate_total',\n",
    "    'suspensions_violent_injury',\n",
    "    'suspensions_violent_no_injury',\n",
    "    'suspensions_weapons',\n",
    "    'suspensions_drugs',\n",
    "    'suspensions_defiance',\n",
    "    'suspensions_other' \n",
    "]\n",
    "\n",
    "suspension22_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2021"
   ]
  },
  {
   "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>academic_year</th>\n",
       "      <th>aggregate_level</th>\n",
       "      <th>county_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>school_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>district_name</th>\n",
       "      <th>school_name</th>\n",
       "      <th>charter_yn</th>\n",
       "      <th>reporting_category</th>\n",
       "      <th>cumulative_enrollment</th>\n",
       "      <th>total_suspensions</th>\n",
       "      <th>unduplicated_students_suspended_total</th>\n",
       "      <th>unduplicated_students_suspended_defiance</th>\n",
       "      <th>suspension_rate_total</th>\n",
       "      <th>suspensions_violent_injury</th>\n",
       "      <th>suspensions_violent_no_injury</th>\n",
       "      <th>suspensions_weapons</th>\n",
       "      <th>suspensions_drugs</th>\n",
       "      <th>suspensions_defiance</th>\n",
       "      <th>suspensions_other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-21</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>110211</td>\n",
       "      <td>18</td>\n",
       "      <td>18</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-21</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>117612</td>\n",
       "      <td>92</td>\n",
       "      <td>87</td>\n",
       "      <td>13</td>\n",
       "      <td>0.1</td>\n",
       "      <td>11</td>\n",
       "      <td>52</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-21</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GX</td>\n",
       "      <td>82</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.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>3</th>\n",
       "      <td>2020-21</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RA</td>\n",
       "      <td>62638</td>\n",
       "      <td>31</td>\n",
       "      <td>30</td>\n",
       "      <td>6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-21</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RB</td>\n",
       "      <td>20475</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222802</th>\n",
       "      <td>2020-21</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SF</td>\n",
       "      <td>4946</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222803</th>\n",
       "      <td>2020-21</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SH</td>\n",
       "      <td>18778</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222804</th>\n",
       "      <td>2020-21</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SM</td>\n",
       "      <td>1255</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222805</th>\n",
       "      <td>2020-21</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>461279</td>\n",
       "      <td>356</td>\n",
       "      <td>318</td>\n",
       "      <td>15</td>\n",
       "      <td>0.1</td>\n",
       "      <td>35</td>\n",
       "      <td>186</td>\n",
       "      <td>19</td>\n",
       "      <td>81</td>\n",
       "      <td>16</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222806</th>\n",
       "      <td>2020-21</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>762790</td>\n",
       "      <td>579</td>\n",
       "      <td>516</td>\n",
       "      <td>25</td>\n",
       "      <td>0.1</td>\n",
       "      <td>63</td>\n",
       "      <td>304</td>\n",
       "      <td>32</td>\n",
       "      <td>127</td>\n",
       "      <td>26</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>222807 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       academic_year aggregate_level county_code district_code school_code  \\\n",
       "0            2020-21               C          01           NaN         NaN   \n",
       "1            2020-21               C          01           NaN         NaN   \n",
       "2            2020-21               C          01           NaN         NaN   \n",
       "3            2020-21               C          01           NaN         NaN   \n",
       "4            2020-21               C          01           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "222802       2020-21               T          00           NaN         NaN   \n",
       "222803       2020-21               T          00           NaN         NaN   \n",
       "222804       2020-21               T          00           NaN         NaN   \n",
       "222805       2020-21               T          00           NaN         NaN   \n",
       "222806       2020-21               T          00           NaN         NaN   \n",
       "\n",
       "       county_name district_name school_name charter_yn reporting_category  \\\n",
       "0          Alameda           NaN         NaN        All                 GF   \n",
       "1          Alameda           NaN         NaN        All                 GM   \n",
       "2          Alameda           NaN         NaN        All                 GX   \n",
       "3          Alameda           NaN         NaN        All                 RA   \n",
       "4          Alameda           NaN         NaN        All                 RB   \n",
       "...            ...           ...         ...        ...                ...   \n",
       "222802       State           NaN         NaN        Yes                 SF   \n",
       "222803       State           NaN         NaN        Yes                 SH   \n",
       "222804       State           NaN         NaN        Yes                 SM   \n",
       "222805       State           NaN         NaN        Yes                 SS   \n",
       "222806       State           NaN         NaN        Yes                 TA   \n",
       "\n",
       "       cumulative_enrollment total_suspensions  \\\n",
       "0                     110211                18   \n",
       "1                     117612                92   \n",
       "2                         82                 0   \n",
       "3                      62638                31   \n",
       "4                      20475                 6   \n",
       "...                      ...               ...   \n",
       "222802                  4946                 8   \n",
       "222803                 18778                11   \n",
       "222804                  1255                 4   \n",
       "222805                461279               356   \n",
       "222806                762790               579   \n",
       "\n",
       "       unduplicated_students_suspended_total  \\\n",
       "0                                         18   \n",
       "1                                         87   \n",
       "2                                          0   \n",
       "3                                         30   \n",
       "4                                          6   \n",
       "...                                      ...   \n",
       "222802                                     5   \n",
       "222803                                    10   \n",
       "222804                                     4   \n",
       "222805                                   318   \n",
       "222806                                   516   \n",
       "\n",
       "       unduplicated_students_suspended_defiance suspension_rate_total  \\\n",
       "0                                             4                   0.0   \n",
       "1                                            13                   0.1   \n",
       "2                                             0                   0.0   \n",
       "3                                             6                   0.0   \n",
       "4                                             1                   0.0   \n",
       "...                                         ...                   ...   \n",
       "222802                                        0                   0.1   \n",
       "222803                                        0                   0.1   \n",
       "222804                                        0                   0.3   \n",
       "222805                                       15                   0.1   \n",
       "222806                                       25                   0.1   \n",
       "\n",
       "       suspensions_violent_injury suspensions_violent_no_injury  \\\n",
       "0                               0                            11   \n",
       "1                              11                            52   \n",
       "2                               0                             0   \n",
       "3                               1                            18   \n",
       "4                               0                             5   \n",
       "...                           ...                           ...   \n",
       "222802                          0                             8   \n",
       "222803                          1                             3   \n",
       "222804                          0                             2   \n",
       "222805                         35                           186   \n",
       "222806                         63                           304   \n",
       "\n",
       "       suspensions_weapons suspensions_drugs suspensions_defiance  \\\n",
       "0                        1                 1                    4   \n",
       "1                        2                 3                   13   \n",
       "2                        0                 0                    0   \n",
       "3                        0                 0                    6   \n",
       "4                        0                 0                    1   \n",
       "...                    ...               ...                  ...   \n",
       "222802                   0                 0                    0   \n",
       "222803                   1                 3                    0   \n",
       "222804                   0                 2                    0   \n",
       "222805                  19                81                   16   \n",
       "222806                  32               127                   26   \n",
       "\n",
       "       suspensions_other  \n",
       "0                      1  \n",
       "1                     11  \n",
       "2                      0  \n",
       "3                      6  \n",
       "4                      0  \n",
       "...                  ...  \n",
       "222802                 0  \n",
       "222803                 3  \n",
       "222804                 0  \n",
       "222805                19  \n",
       "222806                27  \n",
       "\n",
       "[222807 rows x 21 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "suspension21_df = pd.read_csv('suspension_data_csv_files/suspension21.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "suspension21_df.columns = [\n",
    "    'academic_year',\n",
    "    'aggregate_level',\n",
    "    'county_code',\n",
    "    'district_code',\n",
    "    'school_code',\n",
    "    'county_name',\n",
    "    'district_name',\n",
    "    'school_name',\n",
    "    'charter_yn',\n",
    "    'reporting_category',\n",
    "    'cumulative_enrollment',\n",
    "    'total_suspensions',\n",
    "    'unduplicated_students_suspended_total',\n",
    "    'unduplicated_students_suspended_defiance',\n",
    "    'suspension_rate_total',\n",
    "    'suspensions_violent_injury',\n",
    "    'suspensions_violent_no_injury',\n",
    "    'suspensions_weapons',\n",
    "    'suspensions_drugs',\n",
    "    'suspensions_defiance',\n",
    "    'suspensions_other' \n",
    "]\n",
    "\n",
    "suspension21_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2020"
   ]
  },
  {
   "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>academic_year</th>\n",
       "      <th>aggregate_level</th>\n",
       "      <th>county_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>school_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>district_name</th>\n",
       "      <th>school_name</th>\n",
       "      <th>charter_yn</th>\n",
       "      <th>reporting_category</th>\n",
       "      <th>cumulative_enrollment</th>\n",
       "      <th>total_suspensions</th>\n",
       "      <th>unduplicated_students_suspended_total</th>\n",
       "      <th>unduplicated_students_suspended_defiance</th>\n",
       "      <th>suspension_rate_total</th>\n",
       "      <th>suspensions_violent_injury</th>\n",
       "      <th>suspensions_violent_no_injury</th>\n",
       "      <th>suspensions_weapons</th>\n",
       "      <th>suspensions_drugs</th>\n",
       "      <th>suspensions_defiance</th>\n",
       "      <th>suspensions_other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-20</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>113224</td>\n",
       "      <td>2240</td>\n",
       "      <td>1665</td>\n",
       "      <td>161</td>\n",
       "      <td>1.5</td>\n",
       "      <td>581</td>\n",
       "      <td>980</td>\n",
       "      <td>39</td>\n",
       "      <td>383</td>\n",
       "      <td>181</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-20</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>120733</td>\n",
       "      <td>5746</td>\n",
       "      <td>3821</td>\n",
       "      <td>436</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1185</td>\n",
       "      <td>2719</td>\n",
       "      <td>198</td>\n",
       "      <td>786</td>\n",
       "      <td>500</td>\n",
       "      <td>358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-20</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GX</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.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>3</th>\n",
       "      <td>2019-20</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RA</td>\n",
       "      <td>62265</td>\n",
       "      <td>508</td>\n",
       "      <td>407</td>\n",
       "      <td>34</td>\n",
       "      <td>0.7</td>\n",
       "      <td>83</td>\n",
       "      <td>261</td>\n",
       "      <td>14</td>\n",
       "      <td>69</td>\n",
       "      <td>35</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-20</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RB</td>\n",
       "      <td>22059</td>\n",
       "      <td>2569</td>\n",
       "      <td>1582</td>\n",
       "      <td>136</td>\n",
       "      <td>7.2</td>\n",
       "      <td>822</td>\n",
       "      <td>1242</td>\n",
       "      <td>43</td>\n",
       "      <td>172</td>\n",
       "      <td>150</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223628</th>\n",
       "      <td>2019-20</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SF</td>\n",
       "      <td>5660</td>\n",
       "      <td>349</td>\n",
       "      <td>228</td>\n",
       "      <td>17</td>\n",
       "      <td>4.0</td>\n",
       "      <td>55</td>\n",
       "      <td>189</td>\n",
       "      <td>9</td>\n",
       "      <td>61</td>\n",
       "      <td>21</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223629</th>\n",
       "      <td>2019-20</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SH</td>\n",
       "      <td>22770</td>\n",
       "      <td>515</td>\n",
       "      <td>374</td>\n",
       "      <td>22</td>\n",
       "      <td>1.6</td>\n",
       "      <td>95</td>\n",
       "      <td>259</td>\n",
       "      <td>16</td>\n",
       "      <td>100</td>\n",
       "      <td>27</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223630</th>\n",
       "      <td>2019-20</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SM</td>\n",
       "      <td>1419</td>\n",
       "      <td>31</td>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>1.8</td>\n",
       "      <td>4</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223631</th>\n",
       "      <td>2019-20</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>485392</td>\n",
       "      <td>9553</td>\n",
       "      <td>7149</td>\n",
       "      <td>643</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1654</td>\n",
       "      <td>4496</td>\n",
       "      <td>291</td>\n",
       "      <td>1925</td>\n",
       "      <td>726</td>\n",
       "      <td>461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223632</th>\n",
       "      <td>2019-20</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>791193</td>\n",
       "      <td>11910</td>\n",
       "      <td>9014</td>\n",
       "      <td>805</td>\n",
       "      <td>1.1</td>\n",
       "      <td>2022</td>\n",
       "      <td>5631</td>\n",
       "      <td>361</td>\n",
       "      <td>2411</td>\n",
       "      <td>902</td>\n",
       "      <td>583</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>223633 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       academic_year aggregate_level county_code district_code school_code  \\\n",
       "0            2019-20               C          01           NaN         NaN   \n",
       "1            2019-20               C          01           NaN         NaN   \n",
       "2            2019-20               C          01           NaN         NaN   \n",
       "3            2019-20               C          01           NaN         NaN   \n",
       "4            2019-20               C          01           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "223628       2019-20               T          00           NaN         NaN   \n",
       "223629       2019-20               T          00           NaN         NaN   \n",
       "223630       2019-20               T          00           NaN         NaN   \n",
       "223631       2019-20               T          00           NaN         NaN   \n",
       "223632       2019-20               T          00           NaN         NaN   \n",
       "\n",
       "       county_name district_name school_name charter_yn reporting_category  \\\n",
       "0          Alameda           NaN         NaN        All                 GF   \n",
       "1          Alameda           NaN         NaN        All                 GM   \n",
       "2          Alameda           NaN         NaN        All                 GX   \n",
       "3          Alameda           NaN         NaN        All                 RA   \n",
       "4          Alameda           NaN         NaN        All                 RB   \n",
       "...            ...           ...         ...        ...                ...   \n",
       "223628       State           NaN         NaN        Yes                 SF   \n",
       "223629       State           NaN         NaN        Yes                 SH   \n",
       "223630       State           NaN         NaN        Yes                 SM   \n",
       "223631       State           NaN         NaN        Yes                 SS   \n",
       "223632       State           NaN         NaN        Yes                 TA   \n",
       "\n",
       "       cumulative_enrollment total_suspensions  \\\n",
       "0                     113224              2240   \n",
       "1                     120733              5746   \n",
       "2                         14                 0   \n",
       "3                      62265               508   \n",
       "4                      22059              2569   \n",
       "...                      ...               ...   \n",
       "223628                  5660               349   \n",
       "223629                 22770               515   \n",
       "223630                  1419                31   \n",
       "223631                485392              9553   \n",
       "223632                791193             11910   \n",
       "\n",
       "       unduplicated_students_suspended_total  \\\n",
       "0                                       1665   \n",
       "1                                       3821   \n",
       "2                                          0   \n",
       "3                                        407   \n",
       "4                                       1582   \n",
       "...                                      ...   \n",
       "223628                                   228   \n",
       "223629                                   374   \n",
       "223630                                    26   \n",
       "223631                                  7149   \n",
       "223632                                  9014   \n",
       "\n",
       "       unduplicated_students_suspended_defiance suspension_rate_total  \\\n",
       "0                                           161                   1.5   \n",
       "1                                           436                   3.2   \n",
       "2                                             0                   0.0   \n",
       "3                                            34                   0.7   \n",
       "4                                           136                   7.2   \n",
       "...                                         ...                   ...   \n",
       "223628                                       17                   4.0   \n",
       "223629                                       22                   1.6   \n",
       "223630                                        2                   1.8   \n",
       "223631                                      643                   1.5   \n",
       "223632                                      805                   1.1   \n",
       "\n",
       "       suspensions_violent_injury suspensions_violent_no_injury  \\\n",
       "0                             581                           980   \n",
       "1                            1185                          2719   \n",
       "2                               0                             0   \n",
       "3                              83                           261   \n",
       "4                             822                          1242   \n",
       "...                           ...                           ...   \n",
       "223628                         55                           189   \n",
       "223629                         95                           259   \n",
       "223630                          4                            18   \n",
       "223631                       1654                          4496   \n",
       "223632                       2022                          5631   \n",
       "\n",
       "       suspensions_weapons suspensions_drugs suspensions_defiance  \\\n",
       "0                       39               383                  181   \n",
       "1                      198               786                  500   \n",
       "2                        0                 0                    0   \n",
       "3                       14                69                   35   \n",
       "4                       43               172                  150   \n",
       "...                    ...               ...                  ...   \n",
       "223628                   9                61                   21   \n",
       "223629                  16               100                   27   \n",
       "223630                   1                 6                    2   \n",
       "223631                 291              1925                  726   \n",
       "223632                 361              2411                  902   \n",
       "\n",
       "       suspensions_other  \n",
       "0                     76  \n",
       "1                    358  \n",
       "2                      0  \n",
       "3                     46  \n",
       "4                    140  \n",
       "...                  ...  \n",
       "223628                14  \n",
       "223629                18  \n",
       "223630                 0  \n",
       "223631               461  \n",
       "223632               583  \n",
       "\n",
       "[223633 rows x 21 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "suspension20_df = pd.read_csv('suspension_data_csv_files/suspension20.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "suspension20_df.columns = [\n",
    "    'academic_year',\n",
    "    'aggregate_level',\n",
    "    'county_code',\n",
    "    'district_code',\n",
    "    'school_code',\n",
    "    'county_name',\n",
    "    'district_name',\n",
    "    'school_name',\n",
    "    'charter_yn',\n",
    "    'reporting_category',\n",
    "    'cumulative_enrollment',\n",
    "    'total_suspensions',\n",
    "    'unduplicated_students_suspended_total',\n",
    "    'unduplicated_students_suspended_defiance',\n",
    "    'suspension_rate_total',\n",
    "    'suspensions_violent_injury',\n",
    "    'suspensions_violent_no_injury',\n",
    "    'suspensions_weapons',\n",
    "    'suspensions_drugs',\n",
    "    'suspensions_defiance',\n",
    "    'suspensions_other' \n",
    "]\n",
    "\n",
    "suspension20_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2019"
   ]
  },
  {
   "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>academic_year</th>\n",
       "      <th>aggregate_level</th>\n",
       "      <th>county_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>school_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>district_name</th>\n",
       "      <th>school_name</th>\n",
       "      <th>charter_yn</th>\n",
       "      <th>reporting_category</th>\n",
       "      <th>cumulative_enrollment</th>\n",
       "      <th>total_suspensions</th>\n",
       "      <th>unduplicated_students_suspended_total</th>\n",
       "      <th>unduplicated_students_suspended_defiance</th>\n",
       "      <th>suspension_rate_total</th>\n",
       "      <th>suspensions_violent_injury</th>\n",
       "      <th>suspensions_violent_no_injury</th>\n",
       "      <th>suspensions_weapons</th>\n",
       "      <th>suspensions_drugs</th>\n",
       "      <th>suspensions_defiance</th>\n",
       "      <th>suspensions_other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>GF</td>\n",
       "      <td>102681</td>\n",
       "      <td>2732</td>\n",
       "      <td>1903</td>\n",
       "      <td>268</td>\n",
       "      <td>1.9</td>\n",
       "      <td>647</td>\n",
       "      <td>1129</td>\n",
       "      <td>48</td>\n",
       "      <td>496</td>\n",
       "      <td>322</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>114224</td>\n",
       "      <td>3260</td>\n",
       "      <td>2282</td>\n",
       "      <td>314</td>\n",
       "      <td>2.0</td>\n",
       "      <td>707</td>\n",
       "      <td>1413</td>\n",
       "      <td>52</td>\n",
       "      <td>608</td>\n",
       "      <td>374</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>GF</td>\n",
       "      <td>12096</td>\n",
       "      <td>528</td>\n",
       "      <td>383</td>\n",
       "      <td>46</td>\n",
       "      <td>3.2</td>\n",
       "      <td>60</td>\n",
       "      <td>284</td>\n",
       "      <td>4</td>\n",
       "      <td>112</td>\n",
       "      <td>52</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>GM</td>\n",
       "      <td>110192</td>\n",
       "      <td>7177</td>\n",
       "      <td>4416</td>\n",
       "      <td>624</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1396</td>\n",
       "      <td>3363</td>\n",
       "      <td>181</td>\n",
       "      <td>1032</td>\n",
       "      <td>831</td>\n",
       "      <td>374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>121852</td>\n",
       "      <td>8510</td>\n",
       "      <td>5252</td>\n",
       "      <td>763</td>\n",
       "      <td>4.3</td>\n",
       "      <td>1580</td>\n",
       "      <td>4079</td>\n",
       "      <td>215</td>\n",
       "      <td>1217</td>\n",
       "      <td>992</td>\n",
       "      <td>427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355142</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>SS</td>\n",
       "      <td>3956293</td>\n",
       "      <td>293127</td>\n",
       "      <td>175890</td>\n",
       "      <td>28678</td>\n",
       "      <td>4.4</td>\n",
       "      <td>37746</td>\n",
       "      <td>143914</td>\n",
       "      <td>8475</td>\n",
       "      <td>48972</td>\n",
       "      <td>43669</td>\n",
       "      <td>10351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355143</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>461441</td>\n",
       "      <td>14690</td>\n",
       "      <td>10489</td>\n",
       "      <td>1373</td>\n",
       "      <td>2.3</td>\n",
       "      <td>2107</td>\n",
       "      <td>7170</td>\n",
       "      <td>438</td>\n",
       "      <td>2712</td>\n",
       "      <td>1671</td>\n",
       "      <td>592</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355144</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>6329883</td>\n",
       "      <td>354516</td>\n",
       "      <td>219446</td>\n",
       "      <td>34280</td>\n",
       "      <td>3.5</td>\n",
       "      <td>45201</td>\n",
       "      <td>171942</td>\n",
       "      <td>10470</td>\n",
       "      <td>63132</td>\n",
       "      <td>51185</td>\n",
       "      <td>12586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355145</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>758633</td>\n",
       "      <td>18839</td>\n",
       "      <td>13589</td>\n",
       "      <td>1738</td>\n",
       "      <td>1.8</td>\n",
       "      <td>2686</td>\n",
       "      <td>9120</td>\n",
       "      <td>575</td>\n",
       "      <td>3552</td>\n",
       "      <td>2099</td>\n",
       "      <td>807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355146</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>5678140</td>\n",
       "      <td>335677</td>\n",
       "      <td>206391</td>\n",
       "      <td>32574</td>\n",
       "      <td>3.6</td>\n",
       "      <td>42515</td>\n",
       "      <td>162822</td>\n",
       "      <td>9895</td>\n",
       "      <td>59580</td>\n",
       "      <td>49086</td>\n",
       "      <td>11779</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>355147 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       academic_year aggregate_level county_code district_code school_code  \\\n",
       "0            2018-19               C          01           NaN         NaN   \n",
       "1            2018-19               C          01           NaN         NaN   \n",
       "2            2018-19               C          01           NaN         NaN   \n",
       "3            2018-19               C          01           NaN         NaN   \n",
       "4            2018-19               C          01           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "355142       2018-19               T          00           NaN         NaN   \n",
       "355143       2018-19               T          00           NaN         NaN   \n",
       "355144       2018-19               T          00           NaN         NaN   \n",
       "355145       2018-19               T          00           NaN         NaN   \n",
       "355146       2018-19               T          00           NaN         NaN   \n",
       "\n",
       "       county_name district_name school_name charter_yn reporting_category  \\\n",
       "0          Alameda           NaN         NaN        No                  GF   \n",
       "1          Alameda           NaN         NaN        All                 GF   \n",
       "2          Alameda           NaN         NaN        Yes                 GF   \n",
       "3          Alameda           NaN         NaN        No                  GM   \n",
       "4          Alameda           NaN         NaN        All                 GM   \n",
       "...            ...           ...         ...        ...                ...   \n",
       "355142       State           NaN         NaN        All                 SS   \n",
       "355143       State           NaN         NaN        Yes                 SS   \n",
       "355144       State           NaN         NaN        All                 TA   \n",
       "355145       State           NaN         NaN        Yes                 TA   \n",
       "355146       State           NaN         NaN        No                  TA   \n",
       "\n",
       "       cumulative_enrollment total_suspensions  \\\n",
       "0                     102681              2732   \n",
       "1                     114224              3260   \n",
       "2                      12096               528   \n",
       "3                     110192              7177   \n",
       "4                     121852              8510   \n",
       "...                      ...               ...   \n",
       "355142               3956293            293127   \n",
       "355143                461441             14690   \n",
       "355144               6329883            354516   \n",
       "355145                758633             18839   \n",
       "355146               5678140            335677   \n",
       "\n",
       "       unduplicated_students_suspended_total  \\\n",
       "0                                       1903   \n",
       "1                                       2282   \n",
       "2                                        383   \n",
       "3                                       4416   \n",
       "4                                       5252   \n",
       "...                                      ...   \n",
       "355142                                175890   \n",
       "355143                                 10489   \n",
       "355144                                219446   \n",
       "355145                                 13589   \n",
       "355146                                206391   \n",
       "\n",
       "       unduplicated_students_suspended_defiance suspension_rate_total  \\\n",
       "0                                           268                   1.9   \n",
       "1                                           314                   2.0   \n",
       "2                                            46                   3.2   \n",
       "3                                           624                   4.0   \n",
       "4                                           763                   4.3   \n",
       "...                                         ...                   ...   \n",
       "355142                                    28678                   4.4   \n",
       "355143                                     1373                   2.3   \n",
       "355144                                    34280                   3.5   \n",
       "355145                                     1738                   1.8   \n",
       "355146                                    32574                   3.6   \n",
       "\n",
       "       suspensions_violent_injury suspensions_violent_no_injury  \\\n",
       "0                             647                          1129   \n",
       "1                             707                          1413   \n",
       "2                              60                           284   \n",
       "3                            1396                          3363   \n",
       "4                            1580                          4079   \n",
       "...                           ...                           ...   \n",
       "355142                      37746                        143914   \n",
       "355143                       2107                          7170   \n",
       "355144                      45201                        171942   \n",
       "355145                       2686                          9120   \n",
       "355146                      42515                        162822   \n",
       "\n",
       "       suspensions_weapons suspensions_drugs suspensions_defiance  \\\n",
       "0                       48               496                  322   \n",
       "1                       52               608                  374   \n",
       "2                        4               112                   52   \n",
       "3                      181              1032                  831   \n",
       "4                      215              1217                  992   \n",
       "...                    ...               ...                  ...   \n",
       "355142                8475             48972                43669   \n",
       "355143                 438              2712                 1671   \n",
       "355144               10470             63132                51185   \n",
       "355145                 575              3552                 2099   \n",
       "355146                9895             59580                49086   \n",
       "\n",
       "       suspensions_other  \n",
       "0                     90  \n",
       "1                    106  \n",
       "2                     16  \n",
       "3                    374  \n",
       "4                    427  \n",
       "...                  ...  \n",
       "355142             10351  \n",
       "355143               592  \n",
       "355144             12586  \n",
       "355145               807  \n",
       "355146             11779  \n",
       "\n",
       "[355147 rows x 21 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "suspension19_df = pd.read_csv('suspension_data_csv_files/suspension19.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "suspension19_df.columns = [\n",
    "    'academic_year',\n",
    "    'aggregate_level',\n",
    "    'county_code',\n",
    "    'district_code',\n",
    "    'school_code',\n",
    "    'county_name',\n",
    "    'district_name',\n",
    "    'school_name',\n",
    "    'charter_yn',\n",
    "    'reporting_category',\n",
    "    'cumulative_enrollment',\n",
    "    'total_suspensions',\n",
    "    'unduplicated_students_suspended_total',\n",
    "    'unduplicated_students_suspended_defiance',\n",
    "    'suspension_rate_total',\n",
    "    'suspensions_violent_injury',\n",
    "    'suspensions_violent_no_injury',\n",
    "    'suspensions_weapons',\n",
    "    'suspensions_drugs',\n",
    "    'suspensions_defiance',\n",
    "    'suspensions_other' \n",
    "]\n",
    "\n",
    "suspension19_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2018"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>academic_year</th>\n",
       "      <th>aggregate_level</th>\n",
       "      <th>county_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>school_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>district_name</th>\n",
       "      <th>school_name</th>\n",
       "      <th>charter_yn</th>\n",
       "      <th>reporting_category</th>\n",
       "      <th>cumulative_enrollment</th>\n",
       "      <th>total_suspensions</th>\n",
       "      <th>unduplicated_students_suspended_total</th>\n",
       "      <th>unduplicated_students_suspended_defiance</th>\n",
       "      <th>suspension_rate_total</th>\n",
       "      <th>suspensions_violent_injury</th>\n",
       "      <th>suspensions_violent_no_injury</th>\n",
       "      <th>suspensions_weapons</th>\n",
       "      <th>suspensions_drugs</th>\n",
       "      <th>suspensions_defiance</th>\n",
       "      <th>suspensions_other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>GF</td>\n",
       "      <td>103727</td>\n",
       "      <td>2706</td>\n",
       "      <td>1867</td>\n",
       "      <td>277</td>\n",
       "      <td>1.8</td>\n",
       "      <td>600</td>\n",
       "      <td>1134</td>\n",
       "      <td>48</td>\n",
       "      <td>431</td>\n",
       "      <td>377</td>\n",
       "      <td>116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>GF</td>\n",
       "      <td>11260</td>\n",
       "      <td>416</td>\n",
       "      <td>316</td>\n",
       "      <td>45</td>\n",
       "      <td>2.8</td>\n",
       "      <td>48</td>\n",
       "      <td>210</td>\n",
       "      <td>14</td>\n",
       "      <td>67</td>\n",
       "      <td>56</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>114424</td>\n",
       "      <td>3122</td>\n",
       "      <td>2180</td>\n",
       "      <td>322</td>\n",
       "      <td>1.9</td>\n",
       "      <td>648</td>\n",
       "      <td>1344</td>\n",
       "      <td>62</td>\n",
       "      <td>498</td>\n",
       "      <td>433</td>\n",
       "      <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>121612</td>\n",
       "      <td>9223</td>\n",
       "      <td>5592</td>\n",
       "      <td>975</td>\n",
       "      <td>4.6</td>\n",
       "      <td>1733</td>\n",
       "      <td>4322</td>\n",
       "      <td>294</td>\n",
       "      <td>994</td>\n",
       "      <td>1370</td>\n",
       "      <td>510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>GM</td>\n",
       "      <td>110721</td>\n",
       "      <td>8066</td>\n",
       "      <td>4887</td>\n",
       "      <td>840</td>\n",
       "      <td>4.4</td>\n",
       "      <td>1560</td>\n",
       "      <td>3711</td>\n",
       "      <td>267</td>\n",
       "      <td>897</td>\n",
       "      <td>1177</td>\n",
       "      <td>454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355499</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>SS</td>\n",
       "      <td>3646836</td>\n",
       "      <td>285036</td>\n",
       "      <td>169199</td>\n",
       "      <td>31565</td>\n",
       "      <td>4.6</td>\n",
       "      <td>36279</td>\n",
       "      <td>142864</td>\n",
       "      <td>9008</td>\n",
       "      <td>36552</td>\n",
       "      <td>48155</td>\n",
       "      <td>12178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355500</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>SS</td>\n",
       "      <td>4037882</td>\n",
       "      <td>300792</td>\n",
       "      <td>179640</td>\n",
       "      <td>33379</td>\n",
       "      <td>4.4</td>\n",
       "      <td>38786</td>\n",
       "      <td>150200</td>\n",
       "      <td>9588</td>\n",
       "      <td>38632</td>\n",
       "      <td>50668</td>\n",
       "      <td>12918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355501</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>6384919</td>\n",
       "      <td>363406</td>\n",
       "      <td>223867</td>\n",
       "      <td>40218</td>\n",
       "      <td>3.5</td>\n",
       "      <td>46433</td>\n",
       "      <td>179219</td>\n",
       "      <td>11786</td>\n",
       "      <td>50547</td>\n",
       "      <td>59808</td>\n",
       "      <td>15613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355502</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>5748545</td>\n",
       "      <td>343813</td>\n",
       "      <td>210691</td>\n",
       "      <td>38005</td>\n",
       "      <td>3.7</td>\n",
       "      <td>43394</td>\n",
       "      <td>169973</td>\n",
       "      <td>11070</td>\n",
       "      <td>47873</td>\n",
       "      <td>56789</td>\n",
       "      <td>14714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355503</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>753611</td>\n",
       "      <td>19594</td>\n",
       "      <td>13712</td>\n",
       "      <td>2257</td>\n",
       "      <td>1.8</td>\n",
       "      <td>3039</td>\n",
       "      <td>9247</td>\n",
       "      <td>716</td>\n",
       "      <td>2674</td>\n",
       "      <td>3019</td>\n",
       "      <td>899</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>355504 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       academic_year aggregate_level county_code district_code school_code  \\\n",
       "0            2017-18               C          01           NaN         NaN   \n",
       "1            2017-18               C          01           NaN         NaN   \n",
       "2            2017-18               C          01           NaN         NaN   \n",
       "3            2017-18               C          01           NaN         NaN   \n",
       "4            2017-18               C          01           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "355499       2017-18               T          00           NaN         NaN   \n",
       "355500       2017-18               T          00           NaN         NaN   \n",
       "355501       2017-18               T          00           NaN         NaN   \n",
       "355502       2017-18               T          00           NaN         NaN   \n",
       "355503       2017-18               T          00           NaN         NaN   \n",
       "\n",
       "       county_name district_name school_name charter_yn reporting_category  \\\n",
       "0          Alameda           NaN         NaN        No                  GF   \n",
       "1          Alameda           NaN         NaN        Yes                 GF   \n",
       "2          Alameda           NaN         NaN        All                 GF   \n",
       "3          Alameda           NaN         NaN        All                 GM   \n",
       "4          Alameda           NaN         NaN        No                  GM   \n",
       "...            ...           ...         ...        ...                ...   \n",
       "355499       State           NaN         NaN        No                  SS   \n",
       "355500       State           NaN         NaN        All                 SS   \n",
       "355501       State           NaN         NaN        All                 TA   \n",
       "355502       State           NaN         NaN        No                  TA   \n",
       "355503       State           NaN         NaN        Yes                 TA   \n",
       "\n",
       "       cumulative_enrollment total_suspensions  \\\n",
       "0                     103727              2706   \n",
       "1                      11260               416   \n",
       "2                     114424              3122   \n",
       "3                     121612              9223   \n",
       "4                     110721              8066   \n",
       "...                      ...               ...   \n",
       "355499               3646836            285036   \n",
       "355500               4037882            300792   \n",
       "355501               6384919            363406   \n",
       "355502               5748545            343813   \n",
       "355503                753611             19594   \n",
       "\n",
       "       unduplicated_students_suspended_total  \\\n",
       "0                                       1867   \n",
       "1                                        316   \n",
       "2                                       2180   \n",
       "3                                       5592   \n",
       "4                                       4887   \n",
       "...                                      ...   \n",
       "355499                                169199   \n",
       "355500                                179640   \n",
       "355501                                223867   \n",
       "355502                                210691   \n",
       "355503                                 13712   \n",
       "\n",
       "       unduplicated_students_suspended_defiance suspension_rate_total  \\\n",
       "0                                           277                   1.8   \n",
       "1                                            45                   2.8   \n",
       "2                                           322                   1.9   \n",
       "3                                           975                   4.6   \n",
       "4                                           840                   4.4   \n",
       "...                                         ...                   ...   \n",
       "355499                                    31565                   4.6   \n",
       "355500                                    33379                   4.4   \n",
       "355501                                    40218                   3.5   \n",
       "355502                                    38005                   3.7   \n",
       "355503                                     2257                   1.8   \n",
       "\n",
       "       suspensions_violent_injury suspensions_violent_no_injury  \\\n",
       "0                             600                          1134   \n",
       "1                              48                           210   \n",
       "2                             648                          1344   \n",
       "3                            1733                          4322   \n",
       "4                            1560                          3711   \n",
       "...                           ...                           ...   \n",
       "355499                      36279                        142864   \n",
       "355500                      38786                        150200   \n",
       "355501                      46433                        179219   \n",
       "355502                      43394                        169973   \n",
       "355503                       3039                          9247   \n",
       "\n",
       "       suspensions_weapons suspensions_drugs suspensions_defiance  \\\n",
       "0                       48               431                  377   \n",
       "1                       14                67                   56   \n",
       "2                       62               498                  433   \n",
       "3                      294               994                 1370   \n",
       "4                      267               897                 1177   \n",
       "...                    ...               ...                  ...   \n",
       "355499                9008             36552                48155   \n",
       "355500                9588             38632                50668   \n",
       "355501               11786             50547                59808   \n",
       "355502               11070             47873                56789   \n",
       "355503                 716              2674                 3019   \n",
       "\n",
       "       suspensions_other  \n",
       "0                    116  \n",
       "1                     21  \n",
       "2                    137  \n",
       "3                    510  \n",
       "4                    454  \n",
       "...                  ...  \n",
       "355499             12178  \n",
       "355500             12918  \n",
       "355501             15613  \n",
       "355502             14714  \n",
       "355503               899  \n",
       "\n",
       "[355504 rows x 21 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "suspension18_df = pd.read_csv('suspension_data_csv_files/suspension18.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "suspension18_df.columns = [\n",
    "    'academic_year',\n",
    "    'aggregate_level',\n",
    "    'county_code',\n",
    "    'district_code',\n",
    "    'school_code',\n",
    "    'county_name',\n",
    "    'district_name',\n",
    "    'school_name',\n",
    "    'charter_yn',\n",
    "    'reporting_category',\n",
    "    'cumulative_enrollment',\n",
    "    'total_suspensions',\n",
    "    'unduplicated_students_suspended_total',\n",
    "    'unduplicated_students_suspended_defiance',\n",
    "    'suspension_rate_total',\n",
    "    'suspensions_violent_injury',\n",
    "    'suspensions_violent_no_injury',\n",
    "    'suspensions_weapons',\n",
    "    'suspensions_drugs',\n",
    "    'suspensions_defiance',\n",
    "    'suspensions_other' \n",
    "]\n",
    "\n",
    "suspension18_df"
   ]
  },
  {
   "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>academic_year</th>\n",
       "      <th>aggregate_level</th>\n",
       "      <th>county_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>school_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>district_name</th>\n",
       "      <th>school_name</th>\n",
       "      <th>charter_yn</th>\n",
       "      <th>reporting_category</th>\n",
       "      <th>cumulative_enrollment</th>\n",
       "      <th>total_suspensions</th>\n",
       "      <th>unduplicated_students_suspended_total</th>\n",
       "      <th>unduplicated_students_suspended_defiance</th>\n",
       "      <th>suspension_rate_total</th>\n",
       "      <th>suspensions_violent_injury</th>\n",
       "      <th>suspensions_violent_no_injury</th>\n",
       "      <th>suspensions_weapons</th>\n",
       "      <th>suspensions_drugs</th>\n",
       "      <th>suspensions_defiance</th>\n",
       "      <th>suspensions_other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>106188</td>\n",
       "      <td>3043</td>\n",
       "      <td>2179</td>\n",
       "      <td>80</td>\n",
       "      <td>2.1</td>\n",
       "      <td>779</td>\n",
       "      <td>1312</td>\n",
       "      <td>86</td>\n",
       "      <td>677</td>\n",
       "      <td>95</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>113568</td>\n",
       "      <td>7030</td>\n",
       "      <td>4448</td>\n",
       "      <td>223</td>\n",
       "      <td>3.9</td>\n",
       "      <td>1616</td>\n",
       "      <td>3573</td>\n",
       "      <td>284</td>\n",
       "      <td>876</td>\n",
       "      <td>264</td>\n",
       "      <td>417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GX</td>\n",
       "      <td>294</td>\n",
       "      <td>19</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>5.1</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RA</td>\n",
       "      <td>61753</td>\n",
       "      <td>684</td>\n",
       "      <td>528</td>\n",
       "      <td>16</td>\n",
       "      <td>0.9</td>\n",
       "      <td>111</td>\n",
       "      <td>384</td>\n",
       "      <td>38</td>\n",
       "      <td>85</td>\n",
       "      <td>19</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RB</td>\n",
       "      <td>18805</td>\n",
       "      <td>2807</td>\n",
       "      <td>1650</td>\n",
       "      <td>78</td>\n",
       "      <td>8.8</td>\n",
       "      <td>895</td>\n",
       "      <td>1365</td>\n",
       "      <td>66</td>\n",
       "      <td>271</td>\n",
       "      <td>96</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355499</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>SS</td>\n",
       "      <td>3646836</td>\n",
       "      <td>285036</td>\n",
       "      <td>169199</td>\n",
       "      <td>31565</td>\n",
       "      <td>4.6</td>\n",
       "      <td>36279</td>\n",
       "      <td>142864</td>\n",
       "      <td>9008</td>\n",
       "      <td>36552</td>\n",
       "      <td>48155</td>\n",
       "      <td>12178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355500</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>SS</td>\n",
       "      <td>4037882</td>\n",
       "      <td>300792</td>\n",
       "      <td>179640</td>\n",
       "      <td>33379</td>\n",
       "      <td>4.4</td>\n",
       "      <td>38786</td>\n",
       "      <td>150200</td>\n",
       "      <td>9588</td>\n",
       "      <td>38632</td>\n",
       "      <td>50668</td>\n",
       "      <td>12918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355501</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>6384919</td>\n",
       "      <td>363406</td>\n",
       "      <td>223867</td>\n",
       "      <td>40218</td>\n",
       "      <td>3.5</td>\n",
       "      <td>46433</td>\n",
       "      <td>179219</td>\n",
       "      <td>11786</td>\n",
       "      <td>50547</td>\n",
       "      <td>59808</td>\n",
       "      <td>15613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355502</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>5748545</td>\n",
       "      <td>343813</td>\n",
       "      <td>210691</td>\n",
       "      <td>38005</td>\n",
       "      <td>3.7</td>\n",
       "      <td>43394</td>\n",
       "      <td>169973</td>\n",
       "      <td>11070</td>\n",
       "      <td>47873</td>\n",
       "      <td>56789</td>\n",
       "      <td>14714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355503</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>753611</td>\n",
       "      <td>19594</td>\n",
       "      <td>13712</td>\n",
       "      <td>2257</td>\n",
       "      <td>1.8</td>\n",
       "      <td>3039</td>\n",
       "      <td>9247</td>\n",
       "      <td>716</td>\n",
       "      <td>2674</td>\n",
       "      <td>3019</td>\n",
       "      <td>899</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1608623 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       academic_year aggregate_level county_code district_code school_code  \\\n",
       "0            2022-23               C          01           NaN         NaN   \n",
       "1            2022-23               C          01           NaN         NaN   \n",
       "2            2022-23               C          01           NaN         NaN   \n",
       "3            2022-23               C          01           NaN         NaN   \n",
       "4            2022-23               C          01           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "355499       2017-18               T          00           NaN         NaN   \n",
       "355500       2017-18               T          00           NaN         NaN   \n",
       "355501       2017-18               T          00           NaN         NaN   \n",
       "355502       2017-18               T          00           NaN         NaN   \n",
       "355503       2017-18               T          00           NaN         NaN   \n",
       "\n",
       "       county_name district_name school_name charter_yn reporting_category  \\\n",
       "0          Alameda           NaN         NaN        All                 GF   \n",
       "1          Alameda           NaN         NaN        All                 GM   \n",
       "2          Alameda           NaN         NaN        All                 GX   \n",
       "3          Alameda           NaN         NaN        All                 RA   \n",
       "4          Alameda           NaN         NaN        All                 RB   \n",
       "...            ...           ...         ...        ...                ...   \n",
       "355499       State           NaN         NaN        No                  SS   \n",
       "355500       State           NaN         NaN        All                 SS   \n",
       "355501       State           NaN         NaN        All                 TA   \n",
       "355502       State           NaN         NaN        No                  TA   \n",
       "355503       State           NaN         NaN        Yes                 TA   \n",
       "\n",
       "       cumulative_enrollment total_suspensions  \\\n",
       "0                     106188              3043   \n",
       "1                     113568              7030   \n",
       "2                        294                19   \n",
       "3                      61753               684   \n",
       "4                      18805              2807   \n",
       "...                      ...               ...   \n",
       "355499               3646836            285036   \n",
       "355500               4037882            300792   \n",
       "355501               6384919            363406   \n",
       "355502               5748545            343813   \n",
       "355503                753611             19594   \n",
       "\n",
       "       unduplicated_students_suspended_total  \\\n",
       "0                                       2179   \n",
       "1                                       4448   \n",
       "2                                         15   \n",
       "3                                        528   \n",
       "4                                       1650   \n",
       "...                                      ...   \n",
       "355499                                169199   \n",
       "355500                                179640   \n",
       "355501                                223867   \n",
       "355502                                210691   \n",
       "355503                                 13712   \n",
       "\n",
       "       unduplicated_students_suspended_defiance suspension_rate_total  \\\n",
       "0                                            80                   2.1   \n",
       "1                                           223                   3.9   \n",
       "2                                             0                   5.1   \n",
       "3                                            16                   0.9   \n",
       "4                                            78                   8.8   \n",
       "...                                         ...                   ...   \n",
       "355499                                    31565                   4.6   \n",
       "355500                                    33379                   4.4   \n",
       "355501                                    40218                   3.5   \n",
       "355502                                    38005                   3.7   \n",
       "355503                                     2257                   1.8   \n",
       "\n",
       "       suspensions_violent_injury suspensions_violent_no_injury  \\\n",
       "0                             779                          1312   \n",
       "1                            1616                          3573   \n",
       "2                               3                            11   \n",
       "3                             111                           384   \n",
       "4                             895                          1365   \n",
       "...                           ...                           ...   \n",
       "355499                      36279                        142864   \n",
       "355500                      38786                        150200   \n",
       "355501                      46433                        179219   \n",
       "355502                      43394                        169973   \n",
       "355503                       3039                          9247   \n",
       "\n",
       "       suspensions_weapons suspensions_drugs suspensions_defiance  \\\n",
       "0                       86               677                   95   \n",
       "1                      284               876                  264   \n",
       "2                        0                 4                    0   \n",
       "3                       38                85                   19   \n",
       "4                       66               271                   96   \n",
       "...                    ...               ...                  ...   \n",
       "355499                9008             36552                48155   \n",
       "355500                9588             38632                50668   \n",
       "355501               11786             50547                59808   \n",
       "355502               11070             47873                56789   \n",
       "355503                 716              2674                 3019   \n",
       "\n",
       "       suspensions_other  \n",
       "0                     94  \n",
       "1                    417  \n",
       "2                      1  \n",
       "3                     47  \n",
       "4                    114  \n",
       "...                  ...  \n",
       "355499             12178  \n",
       "355500             12918  \n",
       "355501             15613  \n",
       "355502             14714  \n",
       "355503               899  \n",
       "\n",
       "[1608623 rows x 21 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_suspension_df = pd.concat([suspension23_df, suspension22_df, suspension21_df, suspension20_df, suspension19_df, suspension18_df])\n",
    "final_suspension_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>academic_year</th>\n",
       "      <th>aggregate_level</th>\n",
       "      <th>county_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>school_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>district_name</th>\n",
       "      <th>school_name</th>\n",
       "      <th>charter_yn</th>\n",
       "      <th>reporting_category</th>\n",
       "      <th>cumulative_enrollment</th>\n",
       "      <th>total_suspensions</th>\n",
       "      <th>unduplicated_students_suspended_total</th>\n",
       "      <th>unduplicated_students_suspended_defiance</th>\n",
       "      <th>suspension_rate_total</th>\n",
       "      <th>suspensions_violent_injury</th>\n",
       "      <th>suspensions_violent_no_injury</th>\n",
       "      <th>suspensions_weapons</th>\n",
       "      <th>suspensions_drugs</th>\n",
       "      <th>suspensions_defiance</th>\n",
       "      <th>suspensions_other</th>\n",
       "      <th>is_wcc_entity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>106188</td>\n",
       "      <td>3043</td>\n",
       "      <td>2179</td>\n",
       "      <td>80</td>\n",
       "      <td>2.1</td>\n",
       "      <td>779</td>\n",
       "      <td>1312</td>\n",
       "      <td>86</td>\n",
       "      <td>677</td>\n",
       "      <td>95</td>\n",
       "      <td>94</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>113568</td>\n",
       "      <td>7030</td>\n",
       "      <td>4448</td>\n",
       "      <td>223</td>\n",
       "      <td>3.9</td>\n",
       "      <td>1616</td>\n",
       "      <td>3573</td>\n",
       "      <td>284</td>\n",
       "      <td>876</td>\n",
       "      <td>264</td>\n",
       "      <td>417</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GX</td>\n",
       "      <td>294</td>\n",
       "      <td>19</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>5.1</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RA</td>\n",
       "      <td>61753</td>\n",
       "      <td>684</td>\n",
       "      <td>528</td>\n",
       "      <td>16</td>\n",
       "      <td>0.9</td>\n",
       "      <td>111</td>\n",
       "      <td>384</td>\n",
       "      <td>38</td>\n",
       "      <td>85</td>\n",
       "      <td>19</td>\n",
       "      <td>47</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RB</td>\n",
       "      <td>18805</td>\n",
       "      <td>2807</td>\n",
       "      <td>1650</td>\n",
       "      <td>78</td>\n",
       "      <td>8.8</td>\n",
       "      <td>895</td>\n",
       "      <td>1365</td>\n",
       "      <td>66</td>\n",
       "      <td>271</td>\n",
       "      <td>96</td>\n",
       "      <td>114</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355499</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>SS</td>\n",
       "      <td>3646836</td>\n",
       "      <td>285036</td>\n",
       "      <td>169199</td>\n",
       "      <td>31565</td>\n",
       "      <td>4.6</td>\n",
       "      <td>36279</td>\n",
       "      <td>142864</td>\n",
       "      <td>9008</td>\n",
       "      <td>36552</td>\n",
       "      <td>48155</td>\n",
       "      <td>12178</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355500</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>SS</td>\n",
       "      <td>4037882</td>\n",
       "      <td>300792</td>\n",
       "      <td>179640</td>\n",
       "      <td>33379</td>\n",
       "      <td>4.4</td>\n",
       "      <td>38786</td>\n",
       "      <td>150200</td>\n",
       "      <td>9588</td>\n",
       "      <td>38632</td>\n",
       "      <td>50668</td>\n",
       "      <td>12918</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355501</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>6384919</td>\n",
       "      <td>363406</td>\n",
       "      <td>223867</td>\n",
       "      <td>40218</td>\n",
       "      <td>3.5</td>\n",
       "      <td>46433</td>\n",
       "      <td>179219</td>\n",
       "      <td>11786</td>\n",
       "      <td>50547</td>\n",
       "      <td>59808</td>\n",
       "      <td>15613</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355502</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>5748545</td>\n",
       "      <td>343813</td>\n",
       "      <td>210691</td>\n",
       "      <td>38005</td>\n",
       "      <td>3.7</td>\n",
       "      <td>43394</td>\n",
       "      <td>169973</td>\n",
       "      <td>11070</td>\n",
       "      <td>47873</td>\n",
       "      <td>56789</td>\n",
       "      <td>14714</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355503</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>753611</td>\n",
       "      <td>19594</td>\n",
       "      <td>13712</td>\n",
       "      <td>2257</td>\n",
       "      <td>1.8</td>\n",
       "      <td>3039</td>\n",
       "      <td>9247</td>\n",
       "      <td>716</td>\n",
       "      <td>2674</td>\n",
       "      <td>3019</td>\n",
       "      <td>899</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1608623 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       academic_year aggregate_level county_code district_code school_code  \\\n",
       "0            2022-23               C          01           NaN         NaN   \n",
       "1            2022-23               C          01           NaN         NaN   \n",
       "2            2022-23               C          01           NaN         NaN   \n",
       "3            2022-23               C          01           NaN         NaN   \n",
       "4            2022-23               C          01           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "355499       2017-18               T          00           NaN         NaN   \n",
       "355500       2017-18               T          00           NaN         NaN   \n",
       "355501       2017-18               T          00           NaN         NaN   \n",
       "355502       2017-18               T          00           NaN         NaN   \n",
       "355503       2017-18               T          00           NaN         NaN   \n",
       "\n",
       "       county_name district_name school_name charter_yn reporting_category  \\\n",
       "0          Alameda           NaN         NaN        All                 GF   \n",
       "1          Alameda           NaN         NaN        All                 GM   \n",
       "2          Alameda           NaN         NaN        All                 GX   \n",
       "3          Alameda           NaN         NaN        All                 RA   \n",
       "4          Alameda           NaN         NaN        All                 RB   \n",
       "...            ...           ...         ...        ...                ...   \n",
       "355499       State           NaN         NaN        No                  SS   \n",
       "355500       State           NaN         NaN        All                 SS   \n",
       "355501       State           NaN         NaN        All                 TA   \n",
       "355502       State           NaN         NaN        No                  TA   \n",
       "355503       State           NaN         NaN        Yes                 TA   \n",
       "\n",
       "       cumulative_enrollment total_suspensions  \\\n",
       "0                     106188              3043   \n",
       "1                     113568              7030   \n",
       "2                        294                19   \n",
       "3                      61753               684   \n",
       "4                      18805              2807   \n",
       "...                      ...               ...   \n",
       "355499               3646836            285036   \n",
       "355500               4037882            300792   \n",
       "355501               6384919            363406   \n",
       "355502               5748545            343813   \n",
       "355503                753611             19594   \n",
       "\n",
       "       unduplicated_students_suspended_total  \\\n",
       "0                                       2179   \n",
       "1                                       4448   \n",
       "2                                         15   \n",
       "3                                        528   \n",
       "4                                       1650   \n",
       "...                                      ...   \n",
       "355499                                169199   \n",
       "355500                                179640   \n",
       "355501                                223867   \n",
       "355502                                210691   \n",
       "355503                                 13712   \n",
       "\n",
       "       unduplicated_students_suspended_defiance suspension_rate_total  \\\n",
       "0                                            80                   2.1   \n",
       "1                                           223                   3.9   \n",
       "2                                             0                   5.1   \n",
       "3                                            16                   0.9   \n",
       "4                                            78                   8.8   \n",
       "...                                         ...                   ...   \n",
       "355499                                    31565                   4.6   \n",
       "355500                                    33379                   4.4   \n",
       "355501                                    40218                   3.5   \n",
       "355502                                    38005                   3.7   \n",
       "355503                                     2257                   1.8   \n",
       "\n",
       "       suspensions_violent_injury suspensions_violent_no_injury  \\\n",
       "0                             779                          1312   \n",
       "1                            1616                          3573   \n",
       "2                               3                            11   \n",
       "3                             111                           384   \n",
       "4                             895                          1365   \n",
       "...                           ...                           ...   \n",
       "355499                      36279                        142864   \n",
       "355500                      38786                        150200   \n",
       "355501                      46433                        179219   \n",
       "355502                      43394                        169973   \n",
       "355503                       3039                          9247   \n",
       "\n",
       "       suspensions_weapons suspensions_drugs suspensions_defiance  \\\n",
       "0                       86               677                   95   \n",
       "1                      284               876                  264   \n",
       "2                        0                 4                    0   \n",
       "3                       38                85                   19   \n",
       "4                       66               271                   96   \n",
       "...                    ...               ...                  ...   \n",
       "355499                9008             36552                48155   \n",
       "355500                9588             38632                50668   \n",
       "355501               11786             50547                59808   \n",
       "355502               11070             47873                56789   \n",
       "355503                 716              2674                 3019   \n",
       "\n",
       "       suspensions_other  is_wcc_entity  \n",
       "0                     94          False  \n",
       "1                    417          False  \n",
       "2                      1          False  \n",
       "3                     47          False  \n",
       "4                    114          False  \n",
       "...                  ...            ...  \n",
       "355499             12178          False  \n",
       "355500             12918          False  \n",
       "355501             15613          False  \n",
       "355502             14714          False  \n",
       "355503               899          False  \n",
       "\n",
       "[1608623 rows x 22 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 1: Create a set of unique school codes from wcc dataframe for efficient lookup\n",
    "wcc_schools_list = set(wcc_schools_df['School Code'])\n",
    "\n",
    "# Step 2: Use the .isin() method to check if each school code in dataframe1 is in the set of schools from dataframe2\n",
    "final_suspension_df['is_wcc_entity'] = final_suspension_df['school_code'].isin(wcc_schools_list)\n",
    "\n",
    "final_suspension_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>academic_year</th>\n",
       "      <th>aggregate_level</th>\n",
       "      <th>county_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>school_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>district_name</th>\n",
       "      <th>school_name</th>\n",
       "      <th>charter_yn</th>\n",
       "      <th>reporting_category</th>\n",
       "      <th>cumulative_enrollment</th>\n",
       "      <th>total_suspensions</th>\n",
       "      <th>unduplicated_students_suspended_total</th>\n",
       "      <th>unduplicated_students_suspended_defiance</th>\n",
       "      <th>suspension_rate_total</th>\n",
       "      <th>suspensions_violent_injury</th>\n",
       "      <th>suspensions_violent_no_injury</th>\n",
       "      <th>suspensions_weapons</th>\n",
       "      <th>suspensions_drugs</th>\n",
       "      <th>suspensions_defiance</th>\n",
       "      <th>suspensions_other</th>\n",
       "      <th>is_wcc_entity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3152</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>Alameda County Office of Education</td>\n",
       "      <td>District Office</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>2233</td>\n",
       "      <td>44</td>\n",
       "      <td>38</td>\n",
       "      <td>3</td>\n",
       "      <td>1.7</td>\n",
       "      <td>14</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3153</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>Alameda County Office of Education</td>\n",
       "      <td>District Office</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>2419</td>\n",
       "      <td>82</td>\n",
       "      <td>64</td>\n",
       "      <td>6</td>\n",
       "      <td>2.6</td>\n",
       "      <td>14</td>\n",
       "      <td>48</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3155</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>Alameda County Office of Education</td>\n",
       "      <td>District Office</td>\n",
       "      <td>All</td>\n",
       "      <td>RA</td>\n",
       "      <td>690</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.3</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>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3156</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>Alameda County Office of Education</td>\n",
       "      <td>District Office</td>\n",
       "      <td>All</td>\n",
       "      <td>RB</td>\n",
       "      <td>859</td>\n",
       "      <td>65</td>\n",
       "      <td>53</td>\n",
       "      <td>5</td>\n",
       "      <td>6.2</td>\n",
       "      <td>18</td>\n",
       "      <td>37</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3157</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>Alameda County Office of Education</td>\n",
       "      <td>District Office</td>\n",
       "      <td>All</td>\n",
       "      <td>RD</td>\n",
       "      <td>99</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355499</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>SS</td>\n",
       "      <td>3646836</td>\n",
       "      <td>285036</td>\n",
       "      <td>169199</td>\n",
       "      <td>31565</td>\n",
       "      <td>4.6</td>\n",
       "      <td>36279</td>\n",
       "      <td>142864</td>\n",
       "      <td>9008</td>\n",
       "      <td>36552</td>\n",
       "      <td>48155</td>\n",
       "      <td>12178</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355500</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>SS</td>\n",
       "      <td>4037882</td>\n",
       "      <td>300792</td>\n",
       "      <td>179640</td>\n",
       "      <td>33379</td>\n",
       "      <td>4.4</td>\n",
       "      <td>38786</td>\n",
       "      <td>150200</td>\n",
       "      <td>9588</td>\n",
       "      <td>38632</td>\n",
       "      <td>50668</td>\n",
       "      <td>12918</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355501</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>6384919</td>\n",
       "      <td>363406</td>\n",
       "      <td>223867</td>\n",
       "      <td>40218</td>\n",
       "      <td>3.5</td>\n",
       "      <td>46433</td>\n",
       "      <td>179219</td>\n",
       "      <td>11786</td>\n",
       "      <td>50547</td>\n",
       "      <td>59808</td>\n",
       "      <td>15613</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355502</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>5748545</td>\n",
       "      <td>343813</td>\n",
       "      <td>210691</td>\n",
       "      <td>38005</td>\n",
       "      <td>3.7</td>\n",
       "      <td>43394</td>\n",
       "      <td>169973</td>\n",
       "      <td>11070</td>\n",
       "      <td>47873</td>\n",
       "      <td>56789</td>\n",
       "      <td>14714</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355503</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>753611</td>\n",
       "      <td>19594</td>\n",
       "      <td>13712</td>\n",
       "      <td>2257</td>\n",
       "      <td>1.8</td>\n",
       "      <td>3039</td>\n",
       "      <td>9247</td>\n",
       "      <td>716</td>\n",
       "      <td>2674</td>\n",
       "      <td>3019</td>\n",
       "      <td>899</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>183308 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       academic_year aggregate_level county_code district_code school_code  \\\n",
       "3152         2022-23               D          01         10017     0000000   \n",
       "3153         2022-23               D          01         10017     0000000   \n",
       "3155         2022-23               D          01         10017     0000000   \n",
       "3156         2022-23               D          01         10017     0000000   \n",
       "3157         2022-23               D          01         10017     0000000   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "355499       2017-18               T          00           NaN         NaN   \n",
       "355500       2017-18               T          00           NaN         NaN   \n",
       "355501       2017-18               T          00           NaN         NaN   \n",
       "355502       2017-18               T          00           NaN         NaN   \n",
       "355503       2017-18               T          00           NaN         NaN   \n",
       "\n",
       "       county_name                       district_name      school_name  \\\n",
       "3152       Alameda  Alameda County Office of Education  District Office   \n",
       "3153       Alameda  Alameda County Office of Education  District Office   \n",
       "3155       Alameda  Alameda County Office of Education  District Office   \n",
       "3156       Alameda  Alameda County Office of Education  District Office   \n",
       "3157       Alameda  Alameda County Office of Education  District Office   \n",
       "...            ...                                 ...              ...   \n",
       "355499       State                                 NaN              NaN   \n",
       "355500       State                                 NaN              NaN   \n",
       "355501       State                                 NaN              NaN   \n",
       "355502       State                                 NaN              NaN   \n",
       "355503       State                                 NaN              NaN   \n",
       "\n",
       "       charter_yn reporting_category cumulative_enrollment total_suspensions  \\\n",
       "3152          All                 GF                  2233                44   \n",
       "3153          All                 GM                  2419                82   \n",
       "3155          All                 RA                   690                 2   \n",
       "3156          All                 RB                   859                65   \n",
       "3157          All                 RD                    99                 3   \n",
       "...           ...                ...                   ...               ...   \n",
       "355499        No                  SS               3646836            285036   \n",
       "355500        All                 SS               4037882            300792   \n",
       "355501        All                 TA               6384919            363406   \n",
       "355502        No                  TA               5748545            343813   \n",
       "355503        Yes                 TA                753611             19594   \n",
       "\n",
       "       unduplicated_students_suspended_total  \\\n",
       "3152                                      38   \n",
       "3153                                      64   \n",
       "3155                                       2   \n",
       "3156                                      53   \n",
       "3157                                       2   \n",
       "...                                      ...   \n",
       "355499                                169199   \n",
       "355500                                179640   \n",
       "355501                                223867   \n",
       "355502                                210691   \n",
       "355503                                 13712   \n",
       "\n",
       "       unduplicated_students_suspended_defiance suspension_rate_total  \\\n",
       "3152                                          3                   1.7   \n",
       "3153                                          6                   2.6   \n",
       "3155                                          0                   0.3   \n",
       "3156                                          5                   6.2   \n",
       "3157                                          0                   2.0   \n",
       "...                                         ...                   ...   \n",
       "355499                                    31565                   4.6   \n",
       "355500                                    33379                   4.4   \n",
       "355501                                    40218                   3.5   \n",
       "355502                                    38005                   3.7   \n",
       "355503                                     2257                   1.8   \n",
       "\n",
       "       suspensions_violent_injury suspensions_violent_no_injury  \\\n",
       "3152                           14                            20   \n",
       "3153                           14                            48   \n",
       "3155                            1                             1   \n",
       "3156                           18                            37   \n",
       "3157                            0                             2   \n",
       "...                           ...                           ...   \n",
       "355499                      36279                        142864   \n",
       "355500                      38786                        150200   \n",
       "355501                      46433                        179219   \n",
       "355502                      43394                        169973   \n",
       "355503                       3039                          9247   \n",
       "\n",
       "       suspensions_weapons suspensions_drugs suspensions_defiance  \\\n",
       "3152                     0                 6                    3   \n",
       "3153                     2                 7                    7   \n",
       "3155                     0                 0                    0   \n",
       "3156                     0                 4                    5   \n",
       "3157                     0                 1                    0   \n",
       "...                    ...               ...                  ...   \n",
       "355499                9008             36552                48155   \n",
       "355500                9588             38632                50668   \n",
       "355501               11786             50547                59808   \n",
       "355502               11070             47873                56789   \n",
       "355503                 716              2674                 3019   \n",
       "\n",
       "       suspensions_other  is_wcc_entity  \n",
       "3152                   1          False  \n",
       "3153                   4          False  \n",
       "3155                   0          False  \n",
       "3156                   1          False  \n",
       "3157                   0          False  \n",
       "...                  ...            ...  \n",
       "355499             12178          False  \n",
       "355500             12918          False  \n",
       "355501             15613          False  \n",
       "355502             14714          False  \n",
       "355503               899          False  \n",
       "\n",
       "[183308 rows x 22 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Existing conditions for \"Aggregate Level\" and \"is_wcc_entity\"\n",
    "condition1 = final_suspension_df['aggregate_level'].isin(['T', 'D'])\n",
    "condition2 = final_suspension_df['is_wcc_entity']\n",
    "\n",
    "# New conditions to exclude rows with \"*\" in the chronic absenteeism columns\n",
    "condition3 = ~final_suspension_df['cumulative_enrollment'].str.contains('*', regex=False)\n",
    "\n",
    "# Combine all conditions using \"AND\" (&) for the chronic absenteeism conditions and \"OR\" (|) for the initial conditions\n",
    "combined_condition = (condition1 | condition2) & (condition3)\n",
    "\n",
    "# Filter dataframe1 using the combined condition\n",
    "final_filtered_suspension_df = final_suspension_df[combined_condition]\n",
    "\n",
    "final_filtered_suspension_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_filtered_suspension_df.to_csv('final_long_datasets_domain/final_suspension_df.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}