create_expulsion_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_expulsions</th>\n",
       "      <th>expelled_students_total</th>\n",
       "      <th>expelled_students_defiance_only</th>\n",
       "      <th>expulsion_rate_total</th>\n",
       "      <th>expulsion_count_violent_injury</th>\n",
       "      <th>expulsion_count_violent_no_injury</th>\n",
       "      <th>expulsion_count_weapons</th>\n",
       "      <th>expulsion_count_drugs</th>\n",
       "      <th>expulsion_count_defiance</th>\n",
       "      <th>expulsion_count_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>21</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>76</td>\n",
       "      <td>76</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>25</td>\n",
       "      <td>18</td>\n",
       "      <td>20</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>2</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>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>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>4</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>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>26</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <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>5</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>15</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2</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>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </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>201</td>\n",
       "      <td>194</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>37</td>\n",
       "      <td>56</td>\n",
       "      <td>30</td>\n",
       "      <td>63</td>\n",
       "      <td>0</td>\n",
       "      <td>15</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>236</td>\n",
       "      <td>229</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38</td>\n",
       "      <td>70</td>\n",
       "      <td>37</td>\n",
       "      <td>76</td>\n",
       "      <td>0</td>\n",
       "      <td>15</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_expulsions expelled_students_total  \\\n",
       "0                     106188               21                      21   \n",
       "1                     113568               76                      76   \n",
       "2                        294                0                       0   \n",
       "3                      61753                4                       4   \n",
       "4                      18805               26                      26   \n",
       "...                      ...              ...                     ...   \n",
       "226174                  5408                5                       5   \n",
       "226175                 25463               15                      15   \n",
       "226176                  1442                2                       2   \n",
       "226177                495182              201                     194   \n",
       "226178                787503              236                     229   \n",
       "\n",
       "       expelled_students_defiance_only expulsion_rate_total  \\\n",
       "0                                    0                  0.0   \n",
       "1                                    0                  0.1   \n",
       "2                                    0                  0.0   \n",
       "3                                    0                  0.0   \n",
       "4                                    0                  0.1   \n",
       "...                                ...                  ...   \n",
       "226174                               0                  0.1   \n",
       "226175                               0                  0.1   \n",
       "226176                               0                  0.1   \n",
       "226177                               0                  0.0   \n",
       "226178                               0                  0.0   \n",
       "\n",
       "       expulsion_count_violent_injury expulsion_count_violent_no_injury  \\\n",
       "0                                  13                                 5   \n",
       "1                                  25                                18   \n",
       "2                                   0                                 0   \n",
       "3                                   3                                 1   \n",
       "4                                  12                                 9   \n",
       "...                               ...                               ...   \n",
       "226174                              2                                 1   \n",
       "226175                              2                                 5   \n",
       "226176                              0                                 1   \n",
       "226177                             37                                56   \n",
       "226178                             38                                70   \n",
       "\n",
       "       expulsion_count_weapons expulsion_count_drugs expulsion_count_defiance  \\\n",
       "0                            0                     2                        0   \n",
       "1                           20                    11                        0   \n",
       "2                            0                     0                        0   \n",
       "3                            0                     0                        0   \n",
       "4                            4                     1                        0   \n",
       "...                        ...                   ...                      ...   \n",
       "226174                       0                     2                        0   \n",
       "226175                       2                     4                        0   \n",
       "226176                       1                     0                        0   \n",
       "226177                      30                    63                        0   \n",
       "226178                      37                    76                        0   \n",
       "\n",
       "       expulsion_count_other  \n",
       "0                          1  \n",
       "1                          2  \n",
       "2                          0  \n",
       "3                          0  \n",
       "4                          0  \n",
       "...                      ...  \n",
       "226174                     0  \n",
       "226175                     2  \n",
       "226176                     0  \n",
       "226177                    15  \n",
       "226178                    15  \n",
       "\n",
       "[226179 rows x 21 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "expulsion23_df = pd.read_csv('expulsion_data_csv_files/expulsion23.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "expulsion23_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_expulsions',\n",
    "    'expelled_students_total',\n",
    "    'expelled_students_defiance_only',\n",
    "    'expulsion_rate_total',\n",
    "    'expulsion_count_violent_injury',\n",
    "    'expulsion_count_violent_no_injury',\n",
    "    'expulsion_count_weapons',\n",
    "    'expulsion_count_drugs',\n",
    "    'expulsion_count_defiance',\n",
    "    'expulsion_count_other'\n",
    "]\n",
    "expulsion23_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_expulsions</th>\n",
       "      <th>expelled_students_total</th>\n",
       "      <th>expelled_students_defiance_only</th>\n",
       "      <th>expulsion_rate_total</th>\n",
       "      <th>expulsion_count_violent_injury</th>\n",
       "      <th>expulsion_count_violent_no_injury</th>\n",
       "      <th>expulsion_count_weapons</th>\n",
       "      <th>expulsion_count_drugs</th>\n",
       "      <th>expulsion_count_defiance</th>\n",
       "      <th>expulsion_count_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>17</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>46</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16</td>\n",
       "      <td>15</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</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>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>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>7</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>22</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </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>9</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>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>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>107</td>\n",
       "      <td>106</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>26</td>\n",
       "      <td>44</td>\n",
       "      <td>13</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>5</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>122</td>\n",
       "      <td>121</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>31</td>\n",
       "      <td>47</td>\n",
       "      <td>15</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>6</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_expulsions expelled_students_total  \\\n",
       "0                     107288               17                      17   \n",
       "1                     114785               46                      46   \n",
       "2                        202                0                       0   \n",
       "3                      62116                7                       7   \n",
       "4                      19528               22                      22   \n",
       "...                      ...              ...                     ...   \n",
       "225348                  5299                1                       1   \n",
       "225349                 22073                9                       9   \n",
       "225350                  1361                0                       0   \n",
       "225351                474651              107                     106   \n",
       "225352                775005              122                     121   \n",
       "\n",
       "       expelled_students_defiance_only expulsion_rate_total  \\\n",
       "0                                    0                  0.0   \n",
       "1                                    0                  0.0   \n",
       "2                                    0                  0.0   \n",
       "3                                    0                  0.0   \n",
       "4                                    0                  0.1   \n",
       "...                                ...                  ...   \n",
       "225348                               0                  0.0   \n",
       "225349                               0                  0.0   \n",
       "225350                               0                  0.0   \n",
       "225351                               0                  0.0   \n",
       "225352                               0                  0.0   \n",
       "\n",
       "       expulsion_count_violent_injury expulsion_count_violent_no_injury  \\\n",
       "0                                   9                                 5   \n",
       "1                                  16                                15   \n",
       "2                                   0                                 0   \n",
       "3                                   3                                 3   \n",
       "4                                  10                                 6   \n",
       "...                               ...                               ...   \n",
       "225348                              0                                 1   \n",
       "225349                              3                                 4   \n",
       "225350                              0                                 0   \n",
       "225351                             26                                44   \n",
       "225352                             31                                47   \n",
       "\n",
       "       expulsion_count_weapons expulsion_count_drugs expulsion_count_defiance  \\\n",
       "0                            1                     2                        0   \n",
       "1                           11                     2                        0   \n",
       "2                            0                     0                        0   \n",
       "3                            0                     1                        0   \n",
       "4                            5                     0                        0   \n",
       "...                        ...                   ...                      ...   \n",
       "225348                       0                     0                        0   \n",
       "225349                       2                     0                        0   \n",
       "225350                       0                     0                        0   \n",
       "225351                      13                    19                        0   \n",
       "225352                      15                    23                        0   \n",
       "\n",
       "       expulsion_count_other  \n",
       "0                          0  \n",
       "1                          2  \n",
       "2                          0  \n",
       "3                          0  \n",
       "4                          1  \n",
       "...                      ...  \n",
       "225348                     0  \n",
       "225349                     0  \n",
       "225350                     0  \n",
       "225351                     5  \n",
       "225352                     6  \n",
       "\n",
       "[225353 rows x 21 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "expulsion22_df = pd.read_csv('expulsion_data_csv_files/expulsion22-v3.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "expulsion22_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_expulsions',\n",
    "    'expelled_students_total',\n",
    "    'expelled_students_defiance_only',\n",
    "    'expulsion_rate_total',\n",
    "    'expulsion_count_violent_injury',\n",
    "    'expulsion_count_violent_no_injury',\n",
    "    'expulsion_count_weapons',\n",
    "    'expulsion_count_drugs',\n",
    "    'expulsion_count_defiance',\n",
    "    'expulsion_count_other'\n",
    "]\n",
    "\n",
    "expulsion22_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_expulsions</th>\n",
       "      <th>expelled_students_total</th>\n",
       "      <th>expelled_students_defiance_only</th>\n",
       "      <th>expulsion_rate_total</th>\n",
       "      <th>expulsion_count_violent_injury</th>\n",
       "      <th>expulsion_count_violent_no_injury</th>\n",
       "      <th>expulsion_count_weapons</th>\n",
       "      <th>expulsion_count_drugs</th>\n",
       "      <th>expulsion_count_defiance</th>\n",
       "      <th>expulsion_count_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>1</td>\n",
       "      <td>1</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>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>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>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>1</td>\n",
       "      <td>1</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>1</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>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>...</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>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>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>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>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>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>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>6</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>8</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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_expulsions expelled_students_total  \\\n",
       "0                     110211                1                       1   \n",
       "1                     117612                0                       0   \n",
       "2                         82                0                       0   \n",
       "3                      62638                1                       1   \n",
       "4                      20475                0                       0   \n",
       "...                      ...              ...                     ...   \n",
       "222802                  4946                0                       0   \n",
       "222803                 18778                0                       0   \n",
       "222804                  1255                0                       0   \n",
       "222805                461279                6                       6   \n",
       "222806                762790                8                       8   \n",
       "\n",
       "       expelled_students_defiance_only expulsion_rate_total  \\\n",
       "0                                    0                  0.0   \n",
       "1                                    0                  0.0   \n",
       "2                                    0                  0.0   \n",
       "3                                    0                  0.0   \n",
       "4                                    0                  0.0   \n",
       "...                                ...                  ...   \n",
       "222802                               0                  0.0   \n",
       "222803                               0                  0.0   \n",
       "222804                               0                  0.0   \n",
       "222805                               0                  0.0   \n",
       "222806                               0                  0.0   \n",
       "\n",
       "       expulsion_count_violent_injury expulsion_count_violent_no_injury  \\\n",
       "0                                   0                                 0   \n",
       "1                                   0                                 0   \n",
       "2                                   0                                 0   \n",
       "3                                   0                                 0   \n",
       "4                                   0                                 0   \n",
       "...                               ...                               ...   \n",
       "222802                              0                                 0   \n",
       "222803                              0                                 0   \n",
       "222804                              0                                 0   \n",
       "222805                              2                                 1   \n",
       "222806                              2                                 2   \n",
       "\n",
       "       expulsion_count_weapons expulsion_count_drugs expulsion_count_defiance  \\\n",
       "0                            0                     0                        0   \n",
       "1                            0                     0                        0   \n",
       "2                            0                     0                        0   \n",
       "3                            0                     0                        0   \n",
       "4                            0                     0                        0   \n",
       "...                        ...                   ...                      ...   \n",
       "222802                       0                     0                        0   \n",
       "222803                       0                     0                        0   \n",
       "222804                       0                     0                        0   \n",
       "222805                       1                     2                        0   \n",
       "222806                       1                     3                        0   \n",
       "\n",
       "       expulsion_count_other  \n",
       "0                          1  \n",
       "1                          0  \n",
       "2                          0  \n",
       "3                          1  \n",
       "4                          0  \n",
       "...                      ...  \n",
       "222802                     0  \n",
       "222803                     0  \n",
       "222804                     0  \n",
       "222805                     0  \n",
       "222806                     0  \n",
       "\n",
       "[222807 rows x 21 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "expulsion21_df = pd.read_csv('expulsion_data_csv_files/expulsion21.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "expulsion21_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_expulsions',\n",
    "    'expelled_students_total',\n",
    "    'expelled_students_defiance_only',\n",
    "    'expulsion_rate_total',\n",
    "    'expulsion_count_violent_injury',\n",
    "    'expulsion_count_violent_no_injury',\n",
    "    'expulsion_count_weapons',\n",
    "    'expulsion_count_drugs',\n",
    "    'expulsion_count_defiance',\n",
    "    'expulsion_count_other'\n",
    "]\n",
    "\n",
    "expulsion21_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_expulsions</th>\n",
       "      <th>expelled_students_total</th>\n",
       "      <th>expelled_students_defiance_only</th>\n",
       "      <th>expulsion_rate_total</th>\n",
       "      <th>expulsion_count_violent_injury</th>\n",
       "      <th>expulsion_count_violent_no_injury</th>\n",
       "      <th>expulsion_count_weapons</th>\n",
       "      <th>expulsion_count_drugs</th>\n",
       "      <th>expulsion_count_defiance</th>\n",
       "      <th>expulsion_count_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>25</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>62</td>\n",
       "      <td>61</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>18</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>6</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>26</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>15</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </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>8</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>140</td>\n",
       "      <td>139</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36</td>\n",
       "      <td>47</td>\n",
       "      <td>17</td>\n",
       "      <td>35</td>\n",
       "      <td>1</td>\n",
       "      <td>4</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>152</td>\n",
       "      <td>151</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38</td>\n",
       "      <td>49</td>\n",
       "      <td>18</td>\n",
       "      <td>42</td>\n",
       "      <td>1</td>\n",
       "      <td>4</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_expulsions expelled_students_total  \\\n",
       "0                     113224               25                      24   \n",
       "1                     120733               62                      61   \n",
       "2                         14                0                       0   \n",
       "3                      62265                6                       5   \n",
       "4                      22059               26                      26   \n",
       "...                      ...              ...                     ...   \n",
       "223628                  5660                2                       2   \n",
       "223629                 22770                8                       7   \n",
       "223630                  1419                2                       2   \n",
       "223631                485392              140                     139   \n",
       "223632                791193              152                     151   \n",
       "\n",
       "       expelled_students_defiance_only expulsion_rate_total  \\\n",
       "0                                    0                  0.0   \n",
       "1                                    0                  0.1   \n",
       "2                                    0                  0.0   \n",
       "3                                    0                  0.0   \n",
       "4                                    0                  0.1   \n",
       "...                                ...                  ...   \n",
       "223628                               0                  0.0   \n",
       "223629                               0                  0.0   \n",
       "223630                               0                  0.1   \n",
       "223631                               1                  0.0   \n",
       "223632                               1                  0.0   \n",
       "\n",
       "       expulsion_count_violent_injury expulsion_count_violent_no_injury  \\\n",
       "0                                  14                                 7   \n",
       "1                                  18                                17   \n",
       "2                                   0                                 0   \n",
       "3                                   1                                 3   \n",
       "4                                  15                                 7   \n",
       "...                               ...                               ...   \n",
       "223628                              2                                 0   \n",
       "223629                              3                                 1   \n",
       "223630                              0                                 2   \n",
       "223631                             36                                47   \n",
       "223632                             38                                49   \n",
       "\n",
       "       expulsion_count_weapons expulsion_count_drugs expulsion_count_defiance  \\\n",
       "0                            1                     3                        0   \n",
       "1                            8                    18                        0   \n",
       "2                            0                     0                        0   \n",
       "3                            0                     2                        0   \n",
       "4                            1                     2                        0   \n",
       "...                        ...                   ...                      ...   \n",
       "223628                       0                     0                        0   \n",
       "223629                       2                     2                        0   \n",
       "223630                       0                     0                        0   \n",
       "223631                      17                    35                        1   \n",
       "223632                      18                    42                        1   \n",
       "\n",
       "       expulsion_count_other  \n",
       "0                          0  \n",
       "1                          1  \n",
       "2                          0  \n",
       "3                          0  \n",
       "4                          1  \n",
       "...                      ...  \n",
       "223628                     0  \n",
       "223629                     0  \n",
       "223630                     0  \n",
       "223631                     4  \n",
       "223632                     4  \n",
       "\n",
       "[223633 rows x 21 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "expulsion20_df = pd.read_csv('expulsion_data_csv_files/expulsion20.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "expulsion20_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_expulsions',\n",
    "    'expelled_students_total',\n",
    "    'expelled_students_defiance_only',\n",
    "    'expulsion_rate_total',\n",
    "    'expulsion_count_violent_injury',\n",
    "    'expulsion_count_violent_no_injury',\n",
    "    'expulsion_count_weapons',\n",
    "    'expulsion_count_drugs',\n",
    "    'expulsion_count_defiance',\n",
    "    'expulsion_count_other'\n",
    "]\n",
    "\n",
    "expulsion20_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_expulsions</th>\n",
       "      <th>expelled_students_total</th>\n",
       "      <th>expelled_students_defiance_only</th>\n",
       "      <th>expulsion_rate_total</th>\n",
       "      <th>expulsion_count_violent_injury</th>\n",
       "      <th>expulsion_count_violent_no_injury</th>\n",
       "      <th>expulsion_count_weapons</th>\n",
       "      <th>expulsion_count_drugs</th>\n",
       "      <th>expulsion_count_defiance</th>\n",
       "      <th>expulsion_count_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>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>114224</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>Yes</td>\n",
       "      <td>GF</td>\n",
       "      <td>12096</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>No</td>\n",
       "      <td>GF</td>\n",
       "      <td>102681</td>\n",
       "      <td>32</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>121852</td>\n",
       "      <td>94</td>\n",
       "      <td>94</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>30</td>\n",
       "      <td>34</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>4</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>Yes</td>\n",
       "      <td>GM</td>\n",
       "      <td>12192</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>461441</td>\n",
       "      <td>204</td>\n",
       "      <td>203</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>64</td>\n",
       "      <td>29</td>\n",
       "      <td>72</td>\n",
       "      <td>3</td>\n",
       "      <td>5</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>No</td>\n",
       "      <td>SS</td>\n",
       "      <td>3554982</td>\n",
       "      <td>4355</td>\n",
       "      <td>4320</td>\n",
       "      <td>19</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1071</td>\n",
       "      <td>1194</td>\n",
       "      <td>590</td>\n",
       "      <td>1384</td>\n",
       "      <td>19</td>\n",
       "      <td>97</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>5236</td>\n",
       "      <td>5191</td>\n",
       "      <td>24</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1213</td>\n",
       "      <td>1422</td>\n",
       "      <td>711</td>\n",
       "      <td>1754</td>\n",
       "      <td>24</td>\n",
       "      <td>112</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>234</td>\n",
       "      <td>233</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>71</td>\n",
       "      <td>34</td>\n",
       "      <td>89</td>\n",
       "      <td>3</td>\n",
       "      <td>6</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>5002</td>\n",
       "      <td>4958</td>\n",
       "      <td>21</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1182</td>\n",
       "      <td>1351</td>\n",
       "      <td>677</td>\n",
       "      <td>1665</td>\n",
       "      <td>21</td>\n",
       "      <td>106</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        All                 GF   \n",
       "1          Alameda           NaN         NaN        Yes                 GF   \n",
       "2          Alameda           NaN         NaN        No                  GF   \n",
       "3          Alameda           NaN         NaN        All                 GM   \n",
       "4          Alameda           NaN         NaN        Yes                 GM   \n",
       "...            ...           ...         ...        ...                ...   \n",
       "355142       State           NaN         NaN        Yes                 SS   \n",
       "355143       State           NaN         NaN        No                  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_expulsions expelled_students_total  \\\n",
       "0                     114224               34                      34   \n",
       "1                      12096                2                       2   \n",
       "2                     102681               32                      32   \n",
       "3                     121852               94                      94   \n",
       "4                      12192                7                       7   \n",
       "...                      ...              ...                     ...   \n",
       "355142                461441              204                     203   \n",
       "355143               3554982             4355                    4320   \n",
       "355144               6329883             5236                    5191   \n",
       "355145                758633              234                     233   \n",
       "355146               5678140             5002                    4958   \n",
       "\n",
       "       expelled_students_defiance_only expulsion_rate_total  \\\n",
       "0                                    0                    0   \n",
       "1                                    0                    0   \n",
       "2                                    0                    0   \n",
       "3                                    0                  0.1   \n",
       "4                                    0                  0.1   \n",
       "...                                ...                  ...   \n",
       "355142                               3                    0   \n",
       "355143                              19                  0.1   \n",
       "355144                              24                  0.1   \n",
       "355145                               3                    0   \n",
       "355146                              21                  0.1   \n",
       "\n",
       "       expulsion_count_violent_injury expulsion_count_violent_no_injury  \\\n",
       "0                                  18                                 9   \n",
       "1                                   0                                 1   \n",
       "2                                  18                                 8   \n",
       "3                                  30                                34   \n",
       "4                                   0                                 4   \n",
       "...                               ...                               ...   \n",
       "355142                             31                                64   \n",
       "355143                           1071                              1194   \n",
       "355144                           1213                              1422   \n",
       "355145                             31                                71   \n",
       "355146                           1182                              1351   \n",
       "\n",
       "       expulsion_count_weapons expulsion_count_drugs expulsion_count_defiance  \\\n",
       "0                            1                     6                        0   \n",
       "1                            0                     1                        0   \n",
       "2                            1                     5                        0   \n",
       "3                           12                    14                        0   \n",
       "4                            1                     1                        0   \n",
       "...                        ...                   ...                      ...   \n",
       "355142                      29                    72                        3   \n",
       "355143                     590                  1384                       19   \n",
       "355144                     711                  1754                       24   \n",
       "355145                      34                    89                        3   \n",
       "355146                     677                  1665                       21   \n",
       "\n",
       "       expulsion_count_other  \n",
       "0                          0  \n",
       "1                          0  \n",
       "2                          0  \n",
       "3                          4  \n",
       "4                          1  \n",
       "...                      ...  \n",
       "355142                     5  \n",
       "355143                    97  \n",
       "355144                   112  \n",
       "355145                     6  \n",
       "355146                   106  \n",
       "\n",
       "[355147 rows x 21 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "expulsion19_df = pd.read_csv('expulsion_data_csv_files/expulsion19.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "expulsion19_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_expulsions',\n",
    "    'expelled_students_total',\n",
    "    'expelled_students_defiance_only',\n",
    "    'expulsion_rate_total',\n",
    "    'expulsion_count_violent_injury',\n",
    "    'expulsion_count_violent_no_injury',\n",
    "    'expulsion_count_weapons',\n",
    "    'expulsion_count_drugs',\n",
    "    'expulsion_count_defiance',\n",
    "    'expulsion_count_other'\n",
    "]\n",
    "\n",
    "expulsion19_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_expulsions</th>\n",
       "      <th>expelled_students_total</th>\n",
       "      <th>expelled_students_defiance_only</th>\n",
       "      <th>expulsion_rate_total</th>\n",
       "      <th>expulsion_count_violent_injury</th>\n",
       "      <th>expulsion_count_violent_no_injury</th>\n",
       "      <th>expulsion_count_weapons</th>\n",
       "      <th>expulsion_count_drugs</th>\n",
       "      <th>expulsion_count_defiance</th>\n",
       "      <th>expulsion_count_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>Yes</td>\n",
       "      <td>GF</td>\n",
       "      <td>11260</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </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>No</td>\n",
       "      <td>GF</td>\n",
       "      <td>103727</td>\n",
       "      <td>36</td>\n",
       "      <td>35</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>37</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>No</td>\n",
       "      <td>GM</td>\n",
       "      <td>110721</td>\n",
       "      <td>75</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>22</td>\n",
       "      <td>17</td>\n",
       "      <td>25</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>Yes</td>\n",
       "      <td>GM</td>\n",
       "      <td>11437</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</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>All</td>\n",
       "      <td>SS</td>\n",
       "      <td>4037882</td>\n",
       "      <td>4578</td>\n",
       "      <td>4539</td>\n",
       "      <td>25</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1078</td>\n",
       "      <td>1394</td>\n",
       "      <td>710</td>\n",
       "      <td>1241</td>\n",
       "      <td>26</td>\n",
       "      <td>129</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>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>478701</td>\n",
       "      <td>166</td>\n",
       "      <td>166</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28</td>\n",
       "      <td>54</td>\n",
       "      <td>25</td>\n",
       "      <td>52</td>\n",
       "      <td>2</td>\n",
       "      <td>5</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>5369</td>\n",
       "      <td>5326</td>\n",
       "      <td>26</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1207</td>\n",
       "      <td>1601</td>\n",
       "      <td>836</td>\n",
       "      <td>1545</td>\n",
       "      <td>27</td>\n",
       "      <td>153</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>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>753611</td>\n",
       "      <td>189</td>\n",
       "      <td>189</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "      <td>65</td>\n",
       "      <td>28</td>\n",
       "      <td>57</td>\n",
       "      <td>2</td>\n",
       "      <td>7</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>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>5748545</td>\n",
       "      <td>5180</td>\n",
       "      <td>5138</td>\n",
       "      <td>24</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1177</td>\n",
       "      <td>1536</td>\n",
       "      <td>808</td>\n",
       "      <td>1488</td>\n",
       "      <td>25</td>\n",
       "      <td>146</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        Yes                 GF   \n",
       "1          Alameda           NaN         NaN        No                  GF   \n",
       "2          Alameda           NaN         NaN        All                 GF   \n",
       "3          Alameda           NaN         NaN        No                  GM   \n",
       "4          Alameda           NaN         NaN        Yes                 GM   \n",
       "...            ...           ...         ...        ...                ...   \n",
       "355499       State           NaN         NaN        All                 SS   \n",
       "355500       State           NaN         NaN        Yes                 SS   \n",
       "355501       State           NaN         NaN        All                 TA   \n",
       "355502       State           NaN         NaN        Yes                 TA   \n",
       "355503       State           NaN         NaN        No                  TA   \n",
       "\n",
       "       cumulative_enrollment total_expulsions expelled_students_total  \\\n",
       "0                      11260                1                       1   \n",
       "1                     103727               36                      35   \n",
       "2                     114424               37                      36   \n",
       "3                     110721               75                      75   \n",
       "4                      11437               10                      10   \n",
       "...                      ...              ...                     ...   \n",
       "355499               4037882             4578                    4539   \n",
       "355500                478701              166                     166   \n",
       "355501               6384919             5369                    5326   \n",
       "355502                753611              189                     189   \n",
       "355503               5748545             5180                    5138   \n",
       "\n",
       "       expelled_students_defiance_only expulsion_rate_total  \\\n",
       "0                                    0                  0.0   \n",
       "1                                    0                  0.0   \n",
       "2                                    0                  0.0   \n",
       "3                                    0                  0.1   \n",
       "4                                    1                  0.1   \n",
       "...                                ...                  ...   \n",
       "355499                              25                  0.1   \n",
       "355500                               2                  0.0   \n",
       "355501                              26                  0.1   \n",
       "355502                               2                  0.0   \n",
       "355503                              24                  0.1   \n",
       "\n",
       "       expulsion_count_violent_injury expulsion_count_violent_no_injury  \\\n",
       "0                                   0                                 1   \n",
       "1                                  13                                 6   \n",
       "2                                  13                                 7   \n",
       "3                                  22                                17   \n",
       "4                                   1                                 2   \n",
       "...                               ...                               ...   \n",
       "355499                           1078                              1394   \n",
       "355500                             28                                54   \n",
       "355501                           1207                              1601   \n",
       "355502                             30                                65   \n",
       "355503                           1177                              1536   \n",
       "\n",
       "       expulsion_count_weapons expulsion_count_drugs expulsion_count_defiance  \\\n",
       "0                            0                     0                        0   \n",
       "1                            8                     9                        0   \n",
       "2                            8                     9                        0   \n",
       "3                           25                    10                        0   \n",
       "4                            3                     1                        1   \n",
       "...                        ...                   ...                      ...   \n",
       "355499                     710                  1241                       26   \n",
       "355500                      25                    52                        2   \n",
       "355501                     836                  1545                       27   \n",
       "355502                      28                    57                        2   \n",
       "355503                     808                  1488                       25   \n",
       "\n",
       "       expulsion_count_other  \n",
       "0                          0  \n",
       "1                          0  \n",
       "2                          0  \n",
       "3                          1  \n",
       "4                          2  \n",
       "...                      ...  \n",
       "355499                   129  \n",
       "355500                     5  \n",
       "355501                   153  \n",
       "355502                     7  \n",
       "355503                   146  \n",
       "\n",
       "[355504 rows x 21 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "expulsion18_df = pd.read_csv('expulsion_data_csv_files/expulsion18.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "expulsion18_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_expulsions',\n",
    "    'expelled_students_total',\n",
    "    'expelled_students_defiance_only',\n",
    "    'expulsion_rate_total',\n",
    "    'expulsion_count_violent_injury',\n",
    "    'expulsion_count_violent_no_injury',\n",
    "    'expulsion_count_weapons',\n",
    "    'expulsion_count_drugs',\n",
    "    'expulsion_count_defiance',\n",
    "    'expulsion_count_other'\n",
    "]\n",
    "\n",
    "expulsion18_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_expulsions</th>\n",
       "      <th>expelled_students_total</th>\n",
       "      <th>expelled_students_defiance_only</th>\n",
       "      <th>expulsion_rate_total</th>\n",
       "      <th>expulsion_count_violent_injury</th>\n",
       "      <th>expulsion_count_violent_no_injury</th>\n",
       "      <th>expulsion_count_weapons</th>\n",
       "      <th>expulsion_count_drugs</th>\n",
       "      <th>expulsion_count_defiance</th>\n",
       "      <th>expulsion_count_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>21</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>76</td>\n",
       "      <td>76</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>25</td>\n",
       "      <td>18</td>\n",
       "      <td>20</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>2</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>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>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>4</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>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>26</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <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>All</td>\n",
       "      <td>SS</td>\n",
       "      <td>4037882</td>\n",
       "      <td>4578</td>\n",
       "      <td>4539</td>\n",
       "      <td>25</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1078</td>\n",
       "      <td>1394</td>\n",
       "      <td>710</td>\n",
       "      <td>1241</td>\n",
       "      <td>26</td>\n",
       "      <td>129</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>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>478701</td>\n",
       "      <td>166</td>\n",
       "      <td>166</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28</td>\n",
       "      <td>54</td>\n",
       "      <td>25</td>\n",
       "      <td>52</td>\n",
       "      <td>2</td>\n",
       "      <td>5</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>5369</td>\n",
       "      <td>5326</td>\n",
       "      <td>26</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1207</td>\n",
       "      <td>1601</td>\n",
       "      <td>836</td>\n",
       "      <td>1545</td>\n",
       "      <td>27</td>\n",
       "      <td>153</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>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>753611</td>\n",
       "      <td>189</td>\n",
       "      <td>189</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "      <td>65</td>\n",
       "      <td>28</td>\n",
       "      <td>57</td>\n",
       "      <td>2</td>\n",
       "      <td>7</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>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>5748545</td>\n",
       "      <td>5180</td>\n",
       "      <td>5138</td>\n",
       "      <td>24</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1177</td>\n",
       "      <td>1536</td>\n",
       "      <td>808</td>\n",
       "      <td>1488</td>\n",
       "      <td>25</td>\n",
       "      <td>146</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        All                 SS   \n",
       "355500       State           NaN         NaN        Yes                 SS   \n",
       "355501       State           NaN         NaN        All                 TA   \n",
       "355502       State           NaN         NaN        Yes                 TA   \n",
       "355503       State           NaN         NaN        No                  TA   \n",
       "\n",
       "       cumulative_enrollment total_expulsions expelled_students_total  \\\n",
       "0                     106188               21                      21   \n",
       "1                     113568               76                      76   \n",
       "2                        294                0                       0   \n",
       "3                      61753                4                       4   \n",
       "4                      18805               26                      26   \n",
       "...                      ...              ...                     ...   \n",
       "355499               4037882             4578                    4539   \n",
       "355500                478701              166                     166   \n",
       "355501               6384919             5369                    5326   \n",
       "355502                753611              189                     189   \n",
       "355503               5748545             5180                    5138   \n",
       "\n",
       "       expelled_students_defiance_only expulsion_rate_total  \\\n",
       "0                                    0                  0.0   \n",
       "1                                    0                  0.1   \n",
       "2                                    0                  0.0   \n",
       "3                                    0                  0.0   \n",
       "4                                    0                  0.1   \n",
       "...                                ...                  ...   \n",
       "355499                              25                  0.1   \n",
       "355500                               2                  0.0   \n",
       "355501                              26                  0.1   \n",
       "355502                               2                  0.0   \n",
       "355503                              24                  0.1   \n",
       "\n",
       "       expulsion_count_violent_injury expulsion_count_violent_no_injury  \\\n",
       "0                                  13                                 5   \n",
       "1                                  25                                18   \n",
       "2                                   0                                 0   \n",
       "3                                   3                                 1   \n",
       "4                                  12                                 9   \n",
       "...                               ...                               ...   \n",
       "355499                           1078                              1394   \n",
       "355500                             28                                54   \n",
       "355501                           1207                              1601   \n",
       "355502                             30                                65   \n",
       "355503                           1177                              1536   \n",
       "\n",
       "       expulsion_count_weapons expulsion_count_drugs expulsion_count_defiance  \\\n",
       "0                            0                     2                        0   \n",
       "1                           20                    11                        0   \n",
       "2                            0                     0                        0   \n",
       "3                            0                     0                        0   \n",
       "4                            4                     1                        0   \n",
       "...                        ...                   ...                      ...   \n",
       "355499                     710                  1241                       26   \n",
       "355500                      25                    52                        2   \n",
       "355501                     836                  1545                       27   \n",
       "355502                      28                    57                        2   \n",
       "355503                     808                  1488                       25   \n",
       "\n",
       "       expulsion_count_other  \n",
       "0                          1  \n",
       "1                          2  \n",
       "2                          0  \n",
       "3                          0  \n",
       "4                          0  \n",
       "...                      ...  \n",
       "355499                   129  \n",
       "355500                     5  \n",
       "355501                   153  \n",
       "355502                     7  \n",
       "355503                   146  \n",
       "\n",
       "[1608623 rows x 21 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_expulsion_df = pd.concat([expulsion23_df, expulsion22_df, expulsion21_df, expulsion20_df, expulsion19_df, expulsion18_df])\n",
    "final_expulsion_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_expulsions</th>\n",
       "      <th>expelled_students_total</th>\n",
       "      <th>expelled_students_defiance_only</th>\n",
       "      <th>expulsion_rate_total</th>\n",
       "      <th>expulsion_count_violent_injury</th>\n",
       "      <th>expulsion_count_violent_no_injury</th>\n",
       "      <th>expulsion_count_weapons</th>\n",
       "      <th>expulsion_count_drugs</th>\n",
       "      <th>expulsion_count_defiance</th>\n",
       "      <th>expulsion_count_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>21</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>76</td>\n",
       "      <td>76</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>25</td>\n",
       "      <td>18</td>\n",
       "      <td>20</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>2</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>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",
       "      <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>4</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</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>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>26</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>4</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>All</td>\n",
       "      <td>SS</td>\n",
       "      <td>4037882</td>\n",
       "      <td>4578</td>\n",
       "      <td>4539</td>\n",
       "      <td>25</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1078</td>\n",
       "      <td>1394</td>\n",
       "      <td>710</td>\n",
       "      <td>1241</td>\n",
       "      <td>26</td>\n",
       "      <td>129</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>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>478701</td>\n",
       "      <td>166</td>\n",
       "      <td>166</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28</td>\n",
       "      <td>54</td>\n",
       "      <td>25</td>\n",
       "      <td>52</td>\n",
       "      <td>2</td>\n",
       "      <td>5</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>5369</td>\n",
       "      <td>5326</td>\n",
       "      <td>26</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1207</td>\n",
       "      <td>1601</td>\n",
       "      <td>836</td>\n",
       "      <td>1545</td>\n",
       "      <td>27</td>\n",
       "      <td>153</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>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>753611</td>\n",
       "      <td>189</td>\n",
       "      <td>189</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "      <td>65</td>\n",
       "      <td>28</td>\n",
       "      <td>57</td>\n",
       "      <td>2</td>\n",
       "      <td>7</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>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>5748545</td>\n",
       "      <td>5180</td>\n",
       "      <td>5138</td>\n",
       "      <td>24</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1177</td>\n",
       "      <td>1536</td>\n",
       "      <td>808</td>\n",
       "      <td>1488</td>\n",
       "      <td>25</td>\n",
       "      <td>146</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        All                 SS   \n",
       "355500       State           NaN         NaN        Yes                 SS   \n",
       "355501       State           NaN         NaN        All                 TA   \n",
       "355502       State           NaN         NaN        Yes                 TA   \n",
       "355503       State           NaN         NaN        No                  TA   \n",
       "\n",
       "       cumulative_enrollment total_expulsions expelled_students_total  \\\n",
       "0                     106188               21                      21   \n",
       "1                     113568               76                      76   \n",
       "2                        294                0                       0   \n",
       "3                      61753                4                       4   \n",
       "4                      18805               26                      26   \n",
       "...                      ...              ...                     ...   \n",
       "355499               4037882             4578                    4539   \n",
       "355500                478701              166                     166   \n",
       "355501               6384919             5369                    5326   \n",
       "355502                753611              189                     189   \n",
       "355503               5748545             5180                    5138   \n",
       "\n",
       "       expelled_students_defiance_only expulsion_rate_total  \\\n",
       "0                                    0                  0.0   \n",
       "1                                    0                  0.1   \n",
       "2                                    0                  0.0   \n",
       "3                                    0                  0.0   \n",
       "4                                    0                  0.1   \n",
       "...                                ...                  ...   \n",
       "355499                              25                  0.1   \n",
       "355500                               2                  0.0   \n",
       "355501                              26                  0.1   \n",
       "355502                               2                  0.0   \n",
       "355503                              24                  0.1   \n",
       "\n",
       "       expulsion_count_violent_injury expulsion_count_violent_no_injury  \\\n",
       "0                                  13                                 5   \n",
       "1                                  25                                18   \n",
       "2                                   0                                 0   \n",
       "3                                   3                                 1   \n",
       "4                                  12                                 9   \n",
       "...                               ...                               ...   \n",
       "355499                           1078                              1394   \n",
       "355500                             28                                54   \n",
       "355501                           1207                              1601   \n",
       "355502                             30                                65   \n",
       "355503                           1177                              1536   \n",
       "\n",
       "       expulsion_count_weapons expulsion_count_drugs expulsion_count_defiance  \\\n",
       "0                            0                     2                        0   \n",
       "1                           20                    11                        0   \n",
       "2                            0                     0                        0   \n",
       "3                            0                     0                        0   \n",
       "4                            4                     1                        0   \n",
       "...                        ...                   ...                      ...   \n",
       "355499                     710                  1241                       26   \n",
       "355500                      25                    52                        2   \n",
       "355501                     836                  1545                       27   \n",
       "355502                      28                    57                        2   \n",
       "355503                     808                  1488                       25   \n",
       "\n",
       "       expulsion_count_other  is_wcc_entity  \n",
       "0                          1          False  \n",
       "1                          2          False  \n",
       "2                          0          False  \n",
       "3                          0          False  \n",
       "4                          0          False  \n",
       "...                      ...            ...  \n",
       "355499                   129          False  \n",
       "355500                     5          False  \n",
       "355501                   153          False  \n",
       "355502                     7          False  \n",
       "355503                   146          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 the wcc school list 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_expulsion_df['is_wcc_entity'] = final_expulsion_df['school_code'].isin(wcc_schools_list)\n",
    "\n",
    "final_expulsion_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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_expulsions</th>\n",
       "      <th>expelled_students_total</th>\n",
       "      <th>expelled_students_defiance_only</th>\n",
       "      <th>expulsion_rate_total</th>\n",
       "      <th>expulsion_count_violent_injury</th>\n",
       "      <th>expulsion_count_violent_no_injury</th>\n",
       "      <th>expulsion_count_weapons</th>\n",
       "      <th>expulsion_count_drugs</th>\n",
       "      <th>expulsion_count_defiance</th>\n",
       "      <th>expulsion_count_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>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",
       "      <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>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",
       "      <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>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",
       "      <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>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",
       "      <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>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",
       "      <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>All</td>\n",
       "      <td>SS</td>\n",
       "      <td>4037882</td>\n",
       "      <td>4578</td>\n",
       "      <td>4539</td>\n",
       "      <td>25</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1078</td>\n",
       "      <td>1394</td>\n",
       "      <td>710</td>\n",
       "      <td>1241</td>\n",
       "      <td>26</td>\n",
       "      <td>129</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>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>478701</td>\n",
       "      <td>166</td>\n",
       "      <td>166</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28</td>\n",
       "      <td>54</td>\n",
       "      <td>25</td>\n",
       "      <td>52</td>\n",
       "      <td>2</td>\n",
       "      <td>5</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>5369</td>\n",
       "      <td>5326</td>\n",
       "      <td>26</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1207</td>\n",
       "      <td>1601</td>\n",
       "      <td>836</td>\n",
       "      <td>1545</td>\n",
       "      <td>27</td>\n",
       "      <td>153</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>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>753611</td>\n",
       "      <td>189</td>\n",
       "      <td>189</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "      <td>65</td>\n",
       "      <td>28</td>\n",
       "      <td>57</td>\n",
       "      <td>2</td>\n",
       "      <td>7</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>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>5748545</td>\n",
       "      <td>5180</td>\n",
       "      <td>5138</td>\n",
       "      <td>24</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1177</td>\n",
       "      <td>1536</td>\n",
       "      <td>808</td>\n",
       "      <td>1488</td>\n",
       "      <td>25</td>\n",
       "      <td>146</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>191512 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_expulsions  \\\n",
       "3152          All                 GF                  2233                0   \n",
       "3153          All                 GM                  2419                0   \n",
       "3155          All                 RA                   690                0   \n",
       "3156          All                 RB                   859                0   \n",
       "3157          All                 RD                    99                0   \n",
       "...           ...                ...                   ...              ...   \n",
       "355499        All                 SS               4037882             4578   \n",
       "355500        Yes                 SS                478701              166   \n",
       "355501        All                 TA               6384919             5369   \n",
       "355502        Yes                 TA                753611              189   \n",
       "355503        No                  TA               5748545             5180   \n",
       "\n",
       "       expelled_students_total expelled_students_defiance_only  \\\n",
       "3152                         0                               0   \n",
       "3153                         0                               0   \n",
       "3155                         0                               0   \n",
       "3156                         0                               0   \n",
       "3157                         0                               0   \n",
       "...                        ...                             ...   \n",
       "355499                    4539                              25   \n",
       "355500                     166                               2   \n",
       "355501                    5326                              26   \n",
       "355502                     189                               2   \n",
       "355503                    5138                              24   \n",
       "\n",
       "       expulsion_rate_total expulsion_count_violent_injury  \\\n",
       "3152                    0.0                              0   \n",
       "3153                    0.0                              0   \n",
       "3155                    0.0                              0   \n",
       "3156                    0.0                              0   \n",
       "3157                    0.0                              0   \n",
       "...                     ...                            ...   \n",
       "355499                  0.1                           1078   \n",
       "355500                  0.0                             28   \n",
       "355501                  0.1                           1207   \n",
       "355502                  0.0                             30   \n",
       "355503                  0.1                           1177   \n",
       "\n",
       "       expulsion_count_violent_no_injury expulsion_count_weapons  \\\n",
       "3152                                   0                       0   \n",
       "3153                                   0                       0   \n",
       "3155                                   0                       0   \n",
       "3156                                   0                       0   \n",
       "3157                                   0                       0   \n",
       "...                                  ...                     ...   \n",
       "355499                              1394                     710   \n",
       "355500                                54                      25   \n",
       "355501                              1601                     836   \n",
       "355502                                65                      28   \n",
       "355503                              1536                     808   \n",
       "\n",
       "       expulsion_count_drugs expulsion_count_defiance expulsion_count_other  \\\n",
       "3152                       0                        0                     0   \n",
       "3153                       0                        0                     0   \n",
       "3155                       0                        0                     0   \n",
       "3156                       0                        0                     0   \n",
       "3157                       0                        0                     0   \n",
       "...                      ...                      ...                   ...   \n",
       "355499                  1241                       26                   129   \n",
       "355500                    52                        2                     5   \n",
       "355501                  1545                       27                   153   \n",
       "355502                    57                        2                     7   \n",
       "355503                  1488                       25                   146   \n",
       "\n",
       "        is_wcc_entity  \n",
       "3152            False  \n",
       "3153            False  \n",
       "3155            False  \n",
       "3156            False  \n",
       "3157            False  \n",
       "...               ...  \n",
       "355499          False  \n",
       "355500          False  \n",
       "355501          False  \n",
       "355502          False  \n",
       "355503          False  \n",
       "\n",
       "[191512 rows x 22 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Existing conditions for \"Aggregate Level\" and \"is_wcc_entity\"\n",
    "condition1 = final_expulsion_df['aggregate_level'].isin(['T', 'D'])\n",
    "condition2 = final_expulsion_df['is_wcc_entity']\n",
    "\n",
    "# New conditions to exclude rows with \"*\" in the chronic absenteeism columns\n",
    "condition3 = ~final_expulsion_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_expulsion_df = final_expulsion_df[combined_condition]\n",
    "\n",
    "final_filtered_expulsion_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_filtered_expulsion_df.to_csv('final_long_datasets_domain/final_expulsion_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
}