create_cum_enrollment_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>AcademicYear</th>\n",
       "      <th>AggregateLevel</th>\n",
       "      <th>CountyCode</th>\n",
       "      <th>DistrictCode</th>\n",
       "      <th>SchoolCode</th>\n",
       "      <th>CountyName</th>\n",
       "      <th>DistrictName</th>\n",
       "      <th>SchoolName</th>\n",
       "      <th>Charter</th>\n",
       "      <th>ReportingCategory</th>\n",
       "      <th>CumulativeEnrollment</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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>226179 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       AcademicYear AggregateLevel CountyCode DistrictCode SchoolCode  \\\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",
       "       CountyName DistrictName SchoolName Charter ReportingCategory  \\\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",
       "       CumulativeEnrollment  \n",
       "0                    106188  \n",
       "1                    113568  \n",
       "2                       294  \n",
       "3                     61753  \n",
       "4                     18805  \n",
       "...                     ...  \n",
       "226174                 5408  \n",
       "226175                25463  \n",
       "226176                 1442  \n",
       "226177               495182  \n",
       "226178               787503  \n",
       "\n",
       "[226179 rows x 11 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "enrollment_2023_df = pd.read_csv('cumulative_enrollment_csv_files/cenroll2223.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "enrollment_2023_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>AcademicYear</th>\n",
       "      <th>AggregateLevel</th>\n",
       "      <th>CountyCode</th>\n",
       "      <th>DistrictCode</th>\n",
       "      <th>SchoolCode</th>\n",
       "      <th>CountyName</th>\n",
       "      <th>DistrictName</th>\n",
       "      <th>SchoolName</th>\n",
       "      <th>Charter</th>\n",
       "      <th>ReportingCategory</th>\n",
       "      <th>CumulativeEnrollment</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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>225353 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       AcademicYear AggregateLevel CountyCode DistrictCode SchoolCode  \\\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",
       "       CountyName DistrictName SchoolName Charter ReportingCategory  \\\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",
       "       CumulativeEnrollment  \n",
       "0                    107288  \n",
       "1                    114785  \n",
       "2                       202  \n",
       "3                     62116  \n",
       "4                     19528  \n",
       "...                     ...  \n",
       "225348                 5299  \n",
       "225349                22073  \n",
       "225350                 1361  \n",
       "225351               474651  \n",
       "225352               775005  \n",
       "\n",
       "[225353 rows x 11 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "enrollment_2022_df = pd.read_csv('cumulative_enrollment_csv_files/cenroll2122.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "enrollment_2022_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>AcademicYear</th>\n",
       "      <th>AggregateLevel</th>\n",
       "      <th>CountyCode</th>\n",
       "      <th>DistrictCode</th>\n",
       "      <th>SchoolCode</th>\n",
       "      <th>CountyName</th>\n",
       "      <th>DistrictName</th>\n",
       "      <th>SchoolName</th>\n",
       "      <th>Charter</th>\n",
       "      <th>ReportingCategory</th>\n",
       "      <th>CumulativeEnrollment</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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>222807 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       AcademicYear AggregateLevel CountyCode DistrictCode SchoolCode  \\\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",
       "       CountyName DistrictName SchoolName Charter ReportingCategory  \\\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",
       "       CumulativeEnrollment  \n",
       "0                    110211  \n",
       "1                    117612  \n",
       "2                        82  \n",
       "3                     62638  \n",
       "4                     20475  \n",
       "...                     ...  \n",
       "222802                 4946  \n",
       "222803                18778  \n",
       "222804                 1255  \n",
       "222805               461279  \n",
       "222806               762790  \n",
       "\n",
       "[222807 rows x 11 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "enrollment_2021_df = pd.read_csv('cumulative_enrollment_csv_files/cenroll2021.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "enrollment_2021_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>AcademicYear</th>\n",
       "      <th>AggregateLevel</th>\n",
       "      <th>CountyCode</th>\n",
       "      <th>DistrictCode</th>\n",
       "      <th>SchoolCode</th>\n",
       "      <th>CountyName</th>\n",
       "      <th>DistrictName</th>\n",
       "      <th>SchoolName</th>\n",
       "      <th>Charter</th>\n",
       "      <th>ReportingCategory</th>\n",
       "      <th>CumulativeEnrollment</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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>223633 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       AcademicYear AggregateLevel CountyCode DistrictCode SchoolCode  \\\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",
       "       CountyName DistrictName SchoolName Charter ReportingCategory  \\\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",
       "       CumulativeEnrollment  \n",
       "0                    113224  \n",
       "1                    120733  \n",
       "2                        14  \n",
       "3                     62265  \n",
       "4                     22059  \n",
       "...                     ...  \n",
       "223628                 5660  \n",
       "223629                22770  \n",
       "223630                 1419  \n",
       "223631               485392  \n",
       "223632               791193  \n",
       "\n",
       "[223633 rows x 11 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "enrollment_2020_df = pd.read_csv('cumulative_enrollment_csv_files/cenroll1920.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "enrollment_2020_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>AcademicYear</th>\n",
       "      <th>AggregateLevel</th>\n",
       "      <th>CountyCode</th>\n",
       "      <th>DistrictCode</th>\n",
       "      <th>SchoolCode</th>\n",
       "      <th>CountyName</th>\n",
       "      <th>DistrictName</th>\n",
       "      <th>SchoolName</th>\n",
       "      <th>Charter</th>\n",
       "      <th>ReportingCategory</th>\n",
       "      <th>CumulativeEnrollment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>S</td>\n",
       "      <td>30</td>\n",
       "      <td>73635</td>\n",
       "      <td>6110936</td>\n",
       "      <td>Orange</td>\n",
       "      <td>Saddleback Valley Unified</td>\n",
       "      <td>Portola Hills Elementary</td>\n",
       "      <td>All</td>\n",
       "      <td>RA</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>S</td>\n",
       "      <td>30</td>\n",
       "      <td>73635</td>\n",
       "      <td>6110936</td>\n",
       "      <td>Orange</td>\n",
       "      <td>Saddleback Valley Unified</td>\n",
       "      <td>Portola Hills Elementary</td>\n",
       "      <td>All</td>\n",
       "      <td>RB</td>\n",
       "      <td>*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>S</td>\n",
       "      <td>30</td>\n",
       "      <td>73635</td>\n",
       "      <td>6110936</td>\n",
       "      <td>Orange</td>\n",
       "      <td>Saddleback Valley Unified</td>\n",
       "      <td>Portola Hills Elementary</td>\n",
       "      <td>All</td>\n",
       "      <td>RD</td>\n",
       "      <td>*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>S</td>\n",
       "      <td>30</td>\n",
       "      <td>73635</td>\n",
       "      <td>6110936</td>\n",
       "      <td>Orange</td>\n",
       "      <td>Saddleback Valley Unified</td>\n",
       "      <td>Portola Hills Elementary</td>\n",
       "      <td>All</td>\n",
       "      <td>RF</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>S</td>\n",
       "      <td>30</td>\n",
       "      <td>73635</td>\n",
       "      <td>6110936</td>\n",
       "      <td>Orange</td>\n",
       "      <td>Saddleback Valley Unified</td>\n",
       "      <td>Portola Hills Elementary</td>\n",
       "      <td>All</td>\n",
       "      <td>RH</td>\n",
       "      <td>110</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355142</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Imperial</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>38536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355143</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Mateo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>88986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355144</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>42</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Santa Barbara</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>66537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355145</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kern</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>183911</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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>355147 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       AcademicYear AggregateLevel CountyCode DistrictCode SchoolCode  \\\n",
       "0           2018-19              S         30        73635    6110936   \n",
       "1           2018-19              S         30        73635    6110936   \n",
       "2           2018-19              S         30        73635    6110936   \n",
       "3           2018-19              S         30        73635    6110936   \n",
       "4           2018-19              S         30        73635    6110936   \n",
       "...             ...            ...        ...          ...        ...   \n",
       "355142      2018-19              C         13          NaN        NaN   \n",
       "355143      2018-19              C         41          NaN        NaN   \n",
       "355144      2018-19              C         42          NaN        NaN   \n",
       "355145      2018-19              C         15          NaN        NaN   \n",
       "355146      2018-19              T         00          NaN        NaN   \n",
       "\n",
       "           CountyName               DistrictName                SchoolName  \\\n",
       "0              Orange  Saddleback Valley Unified  Portola Hills Elementary   \n",
       "1              Orange  Saddleback Valley Unified  Portola Hills Elementary   \n",
       "2              Orange  Saddleback Valley Unified  Portola Hills Elementary   \n",
       "3              Orange  Saddleback Valley Unified  Portola Hills Elementary   \n",
       "4              Orange  Saddleback Valley Unified  Portola Hills Elementary   \n",
       "...               ...                        ...                       ...   \n",
       "355142       Imperial                        NaN                       NaN   \n",
       "355143      San Mateo                        NaN                       NaN   \n",
       "355144  Santa Barbara                        NaN                       NaN   \n",
       "355145           Kern                        NaN                       NaN   \n",
       "355146          State                        NaN                       NaN   \n",
       "\n",
       "       Charter ReportingCategory CumulativeEnrollment  \n",
       "0          All                RA                   90  \n",
       "1          All                RB                    *  \n",
       "2          All                RD                    *  \n",
       "3          All                RF                   18  \n",
       "4          All                RH                  110  \n",
       "...        ...               ...                  ...  \n",
       "355142     No                 TA                38536  \n",
       "355143     No                 TA                88986  \n",
       "355144     No                 TA                66537  \n",
       "355145     No                 TA               183911  \n",
       "355146     No                 TA              5678140  \n",
       "\n",
       "[355147 rows x 11 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "enrollment_2019_df = pd.read_csv('cumulative_enrollment_csv_files/cenroll1819.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "enrollment_2019_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>AcademicYear</th>\n",
       "      <th>AggregateLevel</th>\n",
       "      <th>CountyCode</th>\n",
       "      <th>DistrictCode</th>\n",
       "      <th>SchoolCode</th>\n",
       "      <th>CountyName</th>\n",
       "      <th>DistrictName</th>\n",
       "      <th>SchoolName</th>\n",
       "      <th>Charter</th>\n",
       "      <th>ReportingCategory</th>\n",
       "      <th>CumulativeEnrollment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RD</td>\n",
       "      <td>*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RH</td>\n",
       "      <td>*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RI</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RT</td>\n",
       "      <td>*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>RW</td>\n",
       "      <td>39</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355499</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>19</td>\n",
       "      <td>64733</td>\n",
       "      <td>6120679</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>Los Angeles Unified</td>\n",
       "      <td>Columbus Avenue</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355500</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>19</td>\n",
       "      <td>64733</td>\n",
       "      <td>6016224</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>Los Angeles Unified</td>\n",
       "      <td>Cabrillo Avenue Elementary</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355501</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>49</td>\n",
       "      <td>70714</td>\n",
       "      <td>6119655</td>\n",
       "      <td>Sonoma</td>\n",
       "      <td>Gravenstein Union Elementary</td>\n",
       "      <td>Gravenstein Community Day</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355502</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>43</td>\n",
       "      <td>69419</td>\n",
       "      <td>6046957</td>\n",
       "      <td>Santa Clara</td>\n",
       "      <td>Cupertino Union</td>\n",
       "      <td>Louis E. Stocklmeir Elementary</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>1296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355503</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>43</td>\n",
       "      <td>69690</td>\n",
       "      <td>6049159</td>\n",
       "      <td>Santa Clara</td>\n",
       "      <td>Sunnyvale</td>\n",
       "      <td>Vargas Elementary</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>551</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>355504 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       AcademicYear AggregateLevel CountyCode DistrictCode SchoolCode  \\\n",
       "0           2017-18              C         02          NaN        NaN   \n",
       "1           2017-18              C         02          NaN        NaN   \n",
       "2           2017-18              C         02          NaN        NaN   \n",
       "3           2017-18              C         02          NaN        NaN   \n",
       "4           2017-18              C         02          NaN        NaN   \n",
       "...             ...            ...        ...          ...        ...   \n",
       "355499      2017-18              S         19        64733    6120679   \n",
       "355500      2017-18              S         19        64733    6016224   \n",
       "355501      2017-18              S         49        70714    6119655   \n",
       "355502      2017-18              S         43        69419    6046957   \n",
       "355503      2017-18              S         43        69690    6049159   \n",
       "\n",
       "         CountyName                  DistrictName  \\\n",
       "0            Alpine                           NaN   \n",
       "1            Alpine                           NaN   \n",
       "2            Alpine                           NaN   \n",
       "3            Alpine                           NaN   \n",
       "4            Alpine                           NaN   \n",
       "...             ...                           ...   \n",
       "355499  Los Angeles           Los Angeles Unified   \n",
       "355500  Los Angeles           Los Angeles Unified   \n",
       "355501       Sonoma  Gravenstein Union Elementary   \n",
       "355502  Santa Clara               Cupertino Union   \n",
       "355503  Santa Clara                     Sunnyvale   \n",
       "\n",
       "                            SchoolName Charter ReportingCategory  \\\n",
       "0                                  NaN     All                RD   \n",
       "1                                  NaN     All                RH   \n",
       "2                                  NaN     All                RI   \n",
       "3                                  NaN     All                RT   \n",
       "4                                  NaN     All                RW   \n",
       "...                                ...     ...               ...   \n",
       "355499                 Columbus Avenue     No                 TA   \n",
       "355500      Cabrillo Avenue Elementary     No                 TA   \n",
       "355501       Gravenstein Community Day     No                 TA   \n",
       "355502  Louis E. Stocklmeir Elementary     No                 TA   \n",
       "355503               Vargas Elementary     No                 TA   \n",
       "\n",
       "       CumulativeEnrollment  \n",
       "0                         *  \n",
       "1                         *  \n",
       "2                        44  \n",
       "3                         *  \n",
       "4                        39  \n",
       "...                     ...  \n",
       "355499                  575  \n",
       "355500                  445  \n",
       "355501                    *  \n",
       "355502                 1296  \n",
       "355503                  551  \n",
       "\n",
       "[355504 rows x 11 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "enrollment_2018_df = pd.read_csv('cumulative_enrollment_csv_files/cenroll1718.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "enrollment_2018_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>AcademicYear</th>\n",
       "      <th>AggregateLevel</th>\n",
       "      <th>CountyCode</th>\n",
       "      <th>DistrictCode</th>\n",
       "      <th>SchoolCode</th>\n",
       "      <th>CountyName</th>\n",
       "      <th>DistrictName</th>\n",
       "      <th>SchoolName</th>\n",
       "      <th>Charter</th>\n",
       "      <th>ReportingCategory</th>\n",
       "      <th>CumulativeEnrollment</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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355499</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>19</td>\n",
       "      <td>64733</td>\n",
       "      <td>6120679</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>Los Angeles Unified</td>\n",
       "      <td>Columbus Avenue</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355500</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>19</td>\n",
       "      <td>64733</td>\n",
       "      <td>6016224</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>Los Angeles Unified</td>\n",
       "      <td>Cabrillo Avenue Elementary</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355501</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>49</td>\n",
       "      <td>70714</td>\n",
       "      <td>6119655</td>\n",
       "      <td>Sonoma</td>\n",
       "      <td>Gravenstein Union Elementary</td>\n",
       "      <td>Gravenstein Community Day</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355502</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>43</td>\n",
       "      <td>69419</td>\n",
       "      <td>6046957</td>\n",
       "      <td>Santa Clara</td>\n",
       "      <td>Cupertino Union</td>\n",
       "      <td>Louis E. Stocklmeir Elementary</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>1296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355503</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>43</td>\n",
       "      <td>69690</td>\n",
       "      <td>6049159</td>\n",
       "      <td>Santa Clara</td>\n",
       "      <td>Sunnyvale</td>\n",
       "      <td>Vargas Elementary</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>551</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1608623 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       AcademicYear AggregateLevel CountyCode DistrictCode SchoolCode  \\\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              S         19        64733    6120679   \n",
       "355500      2017-18              S         19        64733    6016224   \n",
       "355501      2017-18              S         49        70714    6119655   \n",
       "355502      2017-18              S         43        69419    6046957   \n",
       "355503      2017-18              S         43        69690    6049159   \n",
       "\n",
       "         CountyName                  DistrictName  \\\n",
       "0           Alameda                           NaN   \n",
       "1           Alameda                           NaN   \n",
       "2           Alameda                           NaN   \n",
       "3           Alameda                           NaN   \n",
       "4           Alameda                           NaN   \n",
       "...             ...                           ...   \n",
       "355499  Los Angeles           Los Angeles Unified   \n",
       "355500  Los Angeles           Los Angeles Unified   \n",
       "355501       Sonoma  Gravenstein Union Elementary   \n",
       "355502  Santa Clara               Cupertino Union   \n",
       "355503  Santa Clara                     Sunnyvale   \n",
       "\n",
       "                            SchoolName Charter ReportingCategory  \\\n",
       "0                                  NaN     All                GF   \n",
       "1                                  NaN     All                GM   \n",
       "2                                  NaN     All                GX   \n",
       "3                                  NaN     All                RA   \n",
       "4                                  NaN     All                RB   \n",
       "...                                ...     ...               ...   \n",
       "355499                 Columbus Avenue     No                 TA   \n",
       "355500      Cabrillo Avenue Elementary     No                 TA   \n",
       "355501       Gravenstein Community Day     No                 TA   \n",
       "355502  Louis E. Stocklmeir Elementary     No                 TA   \n",
       "355503               Vargas Elementary     No                 TA   \n",
       "\n",
       "       CumulativeEnrollment  \n",
       "0                    106188  \n",
       "1                    113568  \n",
       "2                       294  \n",
       "3                     61753  \n",
       "4                     18805  \n",
       "...                     ...  \n",
       "355499                  575  \n",
       "355500                  445  \n",
       "355501                    *  \n",
       "355502                 1296  \n",
       "355503                  551  \n",
       "\n",
       "[1608623 rows x 11 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_enrollment_df = pd.concat([enrollment_2023_df, enrollment_2022_df, enrollment_2021_df, enrollment_2020_df, enrollment_2019_df, enrollment_2018_df])\n",
    "final_enrollment_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Step 1: Create a set of unique school codes from wcc schools 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 \n",
    "final_enrollment_df['is_wcc_entity'] = final_enrollment_df['SchoolCode'].isin(wcc_schools_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Existing conditions for \"Aggregate Level\" and \"is_wcc_entity\"\n",
    "condition1 = final_enrollment_df['AggregateLevel'].isin(['T', 'D'])\n",
    "condition2 = final_enrollment_df['is_wcc_entity']\n",
    "\n",
    "# New conditions to exclude rows with \"*\" in the chronic absenteeism columns\n",
    "condition3 = ~final_enrollment_df['CumulativeEnrollment'].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_enrollment_df = final_enrollment_df[combined_condition]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>academic_year</th>\n",
       "      <th>aggregate_level</th>\n",
       "      <th>county_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>school_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>district_name</th>\n",
       "      <th>school_name</th>\n",
       "      <th>charter_yn</th>\n",
       "      <th>reporting_category</th>\n",
       "      <th>cumulative_enrollment</th>\n",
       "      <th>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>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>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>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>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>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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355433</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>D</td>\n",
       "      <td>30</td>\n",
       "      <td>66613</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Orange</td>\n",
       "      <td>Ocean View</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>8586</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355443</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>D</td>\n",
       "      <td>49</td>\n",
       "      <td>71001</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Sonoma</td>\n",
       "      <td>West Side Union Elementary</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>188</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355475</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>D</td>\n",
       "      <td>15</td>\n",
       "      <td>63354</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Kern</td>\n",
       "      <td>Blake Elementary</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>13</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355479</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>D</td>\n",
       "      <td>49</td>\n",
       "      <td>70813</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Sonoma</td>\n",
       "      <td>Monte Rio Union Elementary</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>103</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355485</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>D</td>\n",
       "      <td>12</td>\n",
       "      <td>62968</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Humboldt</td>\n",
       "      <td>Orick Elementary</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>26</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>183308 rows × 12 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",
       "355433       2017-18               D          30         66613     0000000   \n",
       "355443       2017-18               D          49         71001     0000000   \n",
       "355475       2017-18               D          15         63354     0000000   \n",
       "355479       2017-18               D          49         70813     0000000   \n",
       "355485       2017-18               D          12         62968     0000000   \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",
       "355433      Orange                          Ocean View  District Office   \n",
       "355443      Sonoma          West Side Union Elementary  District Office   \n",
       "355475        Kern                    Blake Elementary  District Office   \n",
       "355479      Sonoma          Monte Rio Union Elementary  District Office   \n",
       "355485    Humboldt                    Orick Elementary  District Office   \n",
       "\n",
       "       charter_yn reporting_category cumulative_enrollment  is_wcc_entity  \n",
       "3152          All                 GF                  2233          False  \n",
       "3153          All                 GM                  2419          False  \n",
       "3155          All                 RA                   690          False  \n",
       "3156          All                 RB                   859          False  \n",
       "3157          All                 RD                    99          False  \n",
       "...           ...                ...                   ...            ...  \n",
       "355433        No                  TA                  8586          False  \n",
       "355443        No                  TA                   188          False  \n",
       "355475        No                  TA                    13          False  \n",
       "355479        No                  TA                   103          False  \n",
       "355485        No                  TA                    26          False  \n",
       "\n",
       "[183308 rows x 12 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_filtered_enrollment_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",
    "    'is_wcc_entity'\n",
    "]\n",
    "\n",
    "final_filtered_enrollment_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Condition for dropping rows\n",
    "# Keep rows where 'aggregate_level' is NOT 'D' OR 'charter_yn' IS 'All'\n",
    "condition = ~((final_filtered_enrollment_df['aggregate_level'] == 'D') & (final_filtered_enrollment_df['charter_yn'] != 'All'))\n",
    "\n",
    "# Apply the condition to the DataFrame\n",
    "final_filtered_enrollment_df = final_filtered_enrollment_df[condition]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Columns you want to remove\n",
    "columns_to_drop = ['county_name', 'district_name', 'school_name', 'charter_yn', 'is_wcc_entity' ]\n",
    "\n",
    "# Drop the specified columns\n",
    "final_filtered_enrollment_df = final_filtered_enrollment_df.drop(columns=columns_to_drop)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>reporting_category</th>\n",
       "      <th>cumulative_enrollment</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>GF</td>\n",
       "      <td>2233</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>GM</td>\n",
       "      <td>2419</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>RA</td>\n",
       "      <td>690</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>RB</td>\n",
       "      <td>859</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>RD</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>354804</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6004782</td>\n",
       "      <td>TA</td>\n",
       "      <td>482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>354911</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6112015</td>\n",
       "      <td>TA</td>\n",
       "      <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355127</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6099717</td>\n",
       "      <td>TA</td>\n",
       "      <td>451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355182</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6004824</td>\n",
       "      <td>TA</td>\n",
       "      <td>453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355344</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6004873</td>\n",
       "      <td>TA</td>\n",
       "      <td>545</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84889 rows × 7 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",
       "354804       2017-18               S          07         61796     6004782   \n",
       "354911       2017-18               S          07         61796     6112015   \n",
       "355127       2017-18               S          07         61796     6099717   \n",
       "355182       2017-18               S          07         61796     6004824   \n",
       "355344       2017-18               S          07         61796     6004873   \n",
       "\n",
       "       reporting_category cumulative_enrollment  \n",
       "3152                   GF                  2233  \n",
       "3153                   GM                  2419  \n",
       "3155                   RA                   690  \n",
       "3156                   RB                   859  \n",
       "3157                   RD                    99  \n",
       "...                   ...                   ...  \n",
       "354804                 TA                   482  \n",
       "354911                 TA                   500  \n",
       "355127                 TA                   451  \n",
       "355182                 TA                   453  \n",
       "355344                 TA                   545  \n",
       "\n",
       "[84889 rows x 7 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_filtered_enrollment_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_filtered_enrollment_df.to_csv('final_long_datasets_domain/final_cum_enrollment_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
}