create_chronic_absenteeism_dataset.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>#</th>\n",
       "      <th>School Name</th>\n",
       "      <th>Auth (14)</th>\n",
       "      <th>Program (16)</th>\n",
       "      <th>Charter (14)</th>\n",
       "      <th>Facility (4)</th>\n",
       "      <th>School Code</th>\n",
       "      <th>School Type</th>\n",
       "      <th>Note</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18</td>\n",
       "      <td>Greenwood Academy</td>\n",
       "      <td>WCCUSD</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCCUSD School</td>\n",
       "      <td>No</td>\n",
       "      <td>0733253</td>\n",
       "      <td>Alternative</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>63</td>\n",
       "      <td>Vista Independent Study/Virtual Academy</td>\n",
       "      <td>WCCUSD</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCCUSD School</td>\n",
       "      <td>No</td>\n",
       "      <td>0730325</td>\n",
       "      <td>Alternative</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Aspire Richmond Technology Academy</td>\n",
       "      <td>WCCUSD</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCC Charter</td>\n",
       "      <td>Yes</td>\n",
       "      <td>0132118</td>\n",
       "      <td>Elementary</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Bayview Elementary</td>\n",
       "      <td>WCCUSD</td>\n",
       "      <td>ILCoP</td>\n",
       "      <td>WCCUSD School</td>\n",
       "      <td>No</td>\n",
       "      <td>6004600</td>\n",
       "      <td>Elementary</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>Cesar E. Chavez Elementary</td>\n",
       "      <td>WCCUSD</td>\n",
       "      <td>ILCoP</td>\n",
       "      <td>WCCUSD School</td>\n",
       "      <td>No</td>\n",
       "      <td>6114094</td>\n",
       "      <td>Elementary</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>1</td>\n",
       "      <td>Aspire Ca College Preparatory Academy</td>\n",
       "      <td>WCCUSD</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCC Charter</td>\n",
       "      <td>Yes</td>\n",
       "      <td>0132100</td>\n",
       "      <td>Middle-High</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>25</td>\n",
       "      <td>Invictus Academy of Richmond</td>\n",
       "      <td>CCCOE</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCC Charter</td>\n",
       "      <td>No</td>\n",
       "      <td>0137026</td>\n",
       "      <td>Middle-High</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>35</td>\n",
       "      <td>Making Waves Academy</td>\n",
       "      <td>CCCOE</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCC Charter</td>\n",
       "      <td>No</td>\n",
       "      <td>0114470</td>\n",
       "      <td>Middle-High</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>58</td>\n",
       "      <td>Summit: K2</td>\n",
       "      <td>CCCOE</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCC Charter</td>\n",
       "      <td>Yes</td>\n",
       "      <td>0129684</td>\n",
       "      <td>Middle-High</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>59</td>\n",
       "      <td>Summit: Tamalpais</td>\n",
       "      <td>WCCUSD</td>\n",
       "      <td>Non-ILCoP</td>\n",
       "      <td>WCC Charter</td>\n",
       "      <td>No</td>\n",
       "      <td>0133637</td>\n",
       "      <td>Middle-High</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>66 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     #                              School Name Auth (14) Program (16)  \\\n",
       "0   18                        Greenwood Academy    WCCUSD    Non-ILCoP   \n",
       "1   63  Vista Independent Study/Virtual Academy    WCCUSD    Non-ILCoP   \n",
       "2    2       Aspire Richmond Technology Academy    WCCUSD    Non-ILCoP   \n",
       "3    3                       Bayview Elementary    WCCUSD        ILCoP   \n",
       "4    6               Cesar E. Chavez Elementary    WCCUSD        ILCoP   \n",
       "..  ..                                      ...       ...          ...   \n",
       "61   1    Aspire Ca College Preparatory Academy    WCCUSD    Non-ILCoP   \n",
       "62  25             Invictus Academy of Richmond     CCCOE    Non-ILCoP   \n",
       "63  35                     Making Waves Academy     CCCOE    Non-ILCoP   \n",
       "64  58                               Summit: K2     CCCOE    Non-ILCoP   \n",
       "65  59                        Summit: Tamalpais    WCCUSD    Non-ILCoP   \n",
       "\n",
       "    Charter (14)  Facility (4) School Code  School Type Note  \n",
       "0   WCCUSD School           No     0733253  Alternative  NaN  \n",
       "1   WCCUSD School           No     0730325  Alternative  NaN  \n",
       "2     WCC Charter          Yes     0132118   Elementary  NaN  \n",
       "3   WCCUSD School           No     6004600   Elementary  NaN  \n",
       "4   WCCUSD School           No     6114094   Elementary  NaN  \n",
       "..            ...          ...         ...          ...  ...  \n",
       "61    WCC Charter          Yes     0132100  Middle-High  NaN  \n",
       "62    WCC Charter           No     0137026  Middle-High  NaN  \n",
       "63    WCC Charter           No     0114470  Middle-High  NaN  \n",
       "64    WCC Charter          Yes     0129684  Middle-High  NaN  \n",
       "65    WCC Charter           No     0133637  Middle-High  NaN  \n",
       "\n",
       "[66 rows x 9 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wcc_schools_df = pd.read_csv('cef_provided_files/cef_school_list.csv', encoding='ISO-8859-1',dtype=str)\n",
    "wcc_schools_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2023"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Academic Year</th>\n",
       "      <th>Aggregate Level</th>\n",
       "      <th>County Code</th>\n",
       "      <th>District Code</th>\n",
       "      <th>School Code</th>\n",
       "      <th>County Name</th>\n",
       "      <th>District Name</th>\n",
       "      <th>School Name</th>\n",
       "      <th>Charter School</th>\n",
       "      <th>DASS</th>\n",
       "      <th>Reporting Category</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumulativeEnrollment</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>2893518</td>\n",
       "      <td>719562</td>\n",
       "      <td>24.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>3059625</td>\n",
       "      <td>764487</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR13</td>\n",
       "      <td>1266699</td>\n",
       "      <td>309500</td>\n",
       "      <td>24.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR46</td>\n",
       "      <td>1318062</td>\n",
       "      <td>270008</td>\n",
       "      <td>20.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR78</td>\n",
       "      <td>888084</td>\n",
       "      <td>202692</td>\n",
       "      <td>22.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343647</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SF</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343648</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SH</td>\n",
       "      <td>24</td>\n",
       "      <td>8</td>\n",
       "      <td>33.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343649</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SM</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343650</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>256</td>\n",
       "      <td>74</td>\n",
       "      <td>28.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343651</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>289</td>\n",
       "      <td>80</td>\n",
       "      <td>27.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>343652 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Academic Year Aggregate Level County Code District Code School Code  \\\n",
       "0            2022-23               T          00           NaN         NaN   \n",
       "1            2022-23               T          00           NaN         NaN   \n",
       "2            2022-23               T          00           NaN         NaN   \n",
       "3            2022-23               T          00           NaN         NaN   \n",
       "4            2022-23               T          00           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "343647       2022-23               C          58           NaN         NaN   \n",
       "343648       2022-23               C          58           NaN         NaN   \n",
       "343649       2022-23               C          58           NaN         NaN   \n",
       "343650       2022-23               C          58           NaN         NaN   \n",
       "343651       2022-23               C          58           NaN         NaN   \n",
       "\n",
       "       County Name District Name School Name Charter School DASS  \\\n",
       "0            State           NaN         NaN            All  All   \n",
       "1            State           NaN         NaN            All  All   \n",
       "2            State           NaN         NaN            All  All   \n",
       "3            State           NaN         NaN            All  All   \n",
       "4            State           NaN         NaN            All  All   \n",
       "...            ...           ...         ...            ...  ...   \n",
       "343647        Yuba           NaN         NaN            Yes  Yes   \n",
       "343648        Yuba           NaN         NaN            Yes  Yes   \n",
       "343649        Yuba           NaN         NaN            Yes  Yes   \n",
       "343650        Yuba           NaN         NaN            Yes  Yes   \n",
       "343651        Yuba           NaN         NaN            Yes  Yes   \n",
       "\n",
       "       Reporting Category ChronicAbsenteeismEligibleCumulativeEnrollment  \\\n",
       "0                      GF                                        2893518   \n",
       "1                      GM                                        3059625   \n",
       "2                    GR13                                        1266699   \n",
       "3                    GR46                                        1318062   \n",
       "4                    GR78                                         888084   \n",
       "...                   ...                                            ...   \n",
       "343647                 SF                                              *   \n",
       "343648                 SH                                             24   \n",
       "343649                 SM                                              *   \n",
       "343650                 SS                                            256   \n",
       "343651                 TA                                            289   \n",
       "\n",
       "       ChronicAbsenteeismCount ChronicAbsenteeismRate  \n",
       "0                       719562                   24.9  \n",
       "1                       764487                     25  \n",
       "2                       309500                   24.4  \n",
       "3                       270008                   20.5  \n",
       "4                       202692                   22.8  \n",
       "...                        ...                    ...  \n",
       "343647                       *                      *  \n",
       "343648                       8                   33.3  \n",
       "343649                       *                      *  \n",
       "343650                      74                   28.9  \n",
       "343651                      80                   27.7  \n",
       "\n",
       "[343652 rows x 14 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chronicabsenteeism23_df = pd.read_csv('chronic_absenteeism_csv_files/chronicabsenteeism23.csv', encoding='ISO-8859-1',dtype=str)\n",
    "chronicabsenteeism23_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Academic Year</th>\n",
       "      <th>Aggregate Level</th>\n",
       "      <th>County Code</th>\n",
       "      <th>District Code</th>\n",
       "      <th>School Code</th>\n",
       "      <th>County Name</th>\n",
       "      <th>District Name</th>\n",
       "      <th>School Name</th>\n",
       "      <th>Charter School</th>\n",
       "      <th>DASS</th>\n",
       "      <th>Reporting Category</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumulativeEnrollment</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "      <th>is_wcc_entity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>2893518</td>\n",
       "      <td>719562</td>\n",
       "      <td>24.9</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>3059625</td>\n",
       "      <td>764487</td>\n",
       "      <td>25</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR13</td>\n",
       "      <td>1266699</td>\n",
       "      <td>309500</td>\n",
       "      <td>24.4</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR46</td>\n",
       "      <td>1318062</td>\n",
       "      <td>270008</td>\n",
       "      <td>20.5</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR78</td>\n",
       "      <td>888084</td>\n",
       "      <td>202692</td>\n",
       "      <td>22.8</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343647</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SF</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343648</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SH</td>\n",
       "      <td>24</td>\n",
       "      <td>8</td>\n",
       "      <td>33.3</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343649</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SM</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343650</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>256</td>\n",
       "      <td>74</td>\n",
       "      <td>28.9</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343651</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>289</td>\n",
       "      <td>80</td>\n",
       "      <td>27.7</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>343652 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Academic Year Aggregate Level County Code District Code School Code  \\\n",
       "0            2022-23               T          00           NaN         NaN   \n",
       "1            2022-23               T          00           NaN         NaN   \n",
       "2            2022-23               T          00           NaN         NaN   \n",
       "3            2022-23               T          00           NaN         NaN   \n",
       "4            2022-23               T          00           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "343647       2022-23               C          58           NaN         NaN   \n",
       "343648       2022-23               C          58           NaN         NaN   \n",
       "343649       2022-23               C          58           NaN         NaN   \n",
       "343650       2022-23               C          58           NaN         NaN   \n",
       "343651       2022-23               C          58           NaN         NaN   \n",
       "\n",
       "       County Name District Name School Name Charter School DASS  \\\n",
       "0            State           NaN         NaN            All  All   \n",
       "1            State           NaN         NaN            All  All   \n",
       "2            State           NaN         NaN            All  All   \n",
       "3            State           NaN         NaN            All  All   \n",
       "4            State           NaN         NaN            All  All   \n",
       "...            ...           ...         ...            ...  ...   \n",
       "343647        Yuba           NaN         NaN            Yes  Yes   \n",
       "343648        Yuba           NaN         NaN            Yes  Yes   \n",
       "343649        Yuba           NaN         NaN            Yes  Yes   \n",
       "343650        Yuba           NaN         NaN            Yes  Yes   \n",
       "343651        Yuba           NaN         NaN            Yes  Yes   \n",
       "\n",
       "       Reporting Category ChronicAbsenteeismEligibleCumulativeEnrollment  \\\n",
       "0                      GF                                        2893518   \n",
       "1                      GM                                        3059625   \n",
       "2                    GR13                                        1266699   \n",
       "3                    GR46                                        1318062   \n",
       "4                    GR78                                         888084   \n",
       "...                   ...                                            ...   \n",
       "343647                 SF                                              *   \n",
       "343648                 SH                                             24   \n",
       "343649                 SM                                              *   \n",
       "343650                 SS                                            256   \n",
       "343651                 TA                                            289   \n",
       "\n",
       "       ChronicAbsenteeismCount ChronicAbsenteeismRate  is_wcc_entity  \n",
       "0                       719562                   24.9          False  \n",
       "1                       764487                     25          False  \n",
       "2                       309500                   24.4          False  \n",
       "3                       270008                   20.5          False  \n",
       "4                       202692                   22.8          False  \n",
       "...                        ...                    ...            ...  \n",
       "343647                       *                      *          False  \n",
       "343648                       8                   33.3          False  \n",
       "343649                       *                      *          False  \n",
       "343650                      74                   28.9          False  \n",
       "343651                      80                   27.7          False  \n",
       "\n",
       "[343652 rows x 15 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 1: Create a set of unique school codes from dataframe2 for efficient lookup\n",
    "wcc_schools_list = set(wcc_schools_df['School Code'])\n",
    "\n",
    "# Step 2: Use the .isin() method to check if each school code in dataframe1 is in the set of schools from dataframe2\n",
    "chronicabsenteeism23_df['is_wcc_entity'] = chronicabsenteeism23_df['School Code'].isin(wcc_schools_list)\n",
    "\n",
    "chronicabsenteeism23_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Academic Year</th>\n",
       "      <th>Aggregate Level</th>\n",
       "      <th>County Code</th>\n",
       "      <th>District Code</th>\n",
       "      <th>School Code</th>\n",
       "      <th>County Name</th>\n",
       "      <th>District Name</th>\n",
       "      <th>School Name</th>\n",
       "      <th>Charter School</th>\n",
       "      <th>DASS</th>\n",
       "      <th>Reporting Category</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumulativeEnrollment</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "      <th>is_wcc_entity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>2893518</td>\n",
       "      <td>719562</td>\n",
       "      <td>24.9</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>3059625</td>\n",
       "      <td>764487</td>\n",
       "      <td>25</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR13</td>\n",
       "      <td>1266699</td>\n",
       "      <td>309500</td>\n",
       "      <td>24.4</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR46</td>\n",
       "      <td>1318062</td>\n",
       "      <td>270008</td>\n",
       "      <td>20.5</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR78</td>\n",
       "      <td>888084</td>\n",
       "      <td>202692</td>\n",
       "      <td>22.8</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331945</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>RH</td>\n",
       "      <td>34</td>\n",
       "      <td>26</td>\n",
       "      <td>76.5</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331949</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>RW</td>\n",
       "      <td>33</td>\n",
       "      <td>26</td>\n",
       "      <td>78.8</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331950</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SD</td>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "      <td>80</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331954</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>61</td>\n",
       "      <td>50</td>\n",
       "      <td>82</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331955</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>73</td>\n",
       "      <td>57</td>\n",
       "      <td>78.1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>90725 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Academic Year Aggregate Level County Code District Code School Code  \\\n",
       "0            2022-23               T          00           NaN         NaN   \n",
       "1            2022-23               T          00           NaN         NaN   \n",
       "2            2022-23               T          00           NaN         NaN   \n",
       "3            2022-23               T          00           NaN         NaN   \n",
       "4            2022-23               T          00           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "331945       2022-23               D          58         72769     0000000   \n",
       "331949       2022-23               D          58         72769     0000000   \n",
       "331950       2022-23               D          58         72769     0000000   \n",
       "331954       2022-23               D          58         72769     0000000   \n",
       "331955       2022-23               D          58         72769     0000000   \n",
       "\n",
       "       County Name         District Name      School Name Charter School DASS  \\\n",
       "0            State                   NaN              NaN            All  All   \n",
       "1            State                   NaN              NaN            All  All   \n",
       "2            State                   NaN              NaN            All  All   \n",
       "3            State                   NaN              NaN            All  All   \n",
       "4            State                   NaN              NaN            All  All   \n",
       "...            ...                   ...              ...            ...  ...   \n",
       "331945        Yuba  Wheatland Union High  District Office            No   Yes   \n",
       "331949        Yuba  Wheatland Union High  District Office            No   Yes   \n",
       "331950        Yuba  Wheatland Union High  District Office            No   Yes   \n",
       "331954        Yuba  Wheatland Union High  District Office            No   Yes   \n",
       "331955        Yuba  Wheatland Union High  District Office            No   Yes   \n",
       "\n",
       "       Reporting Category ChronicAbsenteeismEligibleCumulativeEnrollment  \\\n",
       "0                      GF                                        2893518   \n",
       "1                      GM                                        3059625   \n",
       "2                    GR13                                        1266699   \n",
       "3                    GR46                                        1318062   \n",
       "4                    GR78                                         888084   \n",
       "...                   ...                                            ...   \n",
       "331945                 RH                                             34   \n",
       "331949                 RW                                             33   \n",
       "331950                 SD                                             15   \n",
       "331954                 SS                                             61   \n",
       "331955                 TA                                             73   \n",
       "\n",
       "       ChronicAbsenteeismCount ChronicAbsenteeismRate  is_wcc_entity  \n",
       "0                       719562                   24.9          False  \n",
       "1                       764487                     25          False  \n",
       "2                       309500                   24.4          False  \n",
       "3                       270008                   20.5          False  \n",
       "4                       202692                   22.8          False  \n",
       "...                        ...                    ...            ...  \n",
       "331945                      26                   76.5          False  \n",
       "331949                      26                   78.8          False  \n",
       "331950                      12                     80          False  \n",
       "331954                      50                     82          False  \n",
       "331955                      57                   78.1          False  \n",
       "\n",
       "[90725 rows x 15 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Existing conditions for \"Aggregate Level\" and \"is_wcc_entity\"\n",
    "condition1 = chronicabsenteeism23_df['Aggregate Level'].isin(['T', 'D'])\n",
    "condition2 = chronicabsenteeism23_df['is_wcc_entity']\n",
    "\n",
    "# New conditions to exclude rows with \"*\" in the chronic absenteeism columns\n",
    "condition3 = ~chronicabsenteeism23_df['ChronicAbsenteeismEligibleCumulativeEnrollment'].str.contains('*', regex=False)\n",
    "condition4 = ~chronicabsenteeism23_df['ChronicAbsenteeismCount'].str.contains('*', regex=False)\n",
    "condition5 = ~chronicabsenteeism23_df['ChronicAbsenteeismRate'].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 & condition4 & condition5)\n",
    "\n",
    "# Filter dataframe1 using the combined condition\n",
    "final_filtered_dataframe23 = chronicabsenteeism23_df[combined_condition]\n",
    "\n",
    "final_filtered_dataframe23"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Academic Year</th>\n",
       "      <th>Aggregate Level</th>\n",
       "      <th>County Code</th>\n",
       "      <th>District Code</th>\n",
       "      <th>School Code</th>\n",
       "      <th>County Name</th>\n",
       "      <th>District Name</th>\n",
       "      <th>School Name</th>\n",
       "      <th>Charter School</th>\n",
       "      <th>DASS</th>\n",
       "      <th>Reporting Category</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumulativeEnrollment</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "      <th>is_wcc_entity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>2893518</td>\n",
       "      <td>719562</td>\n",
       "      <td>24.9</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>3059625</td>\n",
       "      <td>764487</td>\n",
       "      <td>25</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR13</td>\n",
       "      <td>1266699</td>\n",
       "      <td>309500</td>\n",
       "      <td>24.4</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR46</td>\n",
       "      <td>1318062</td>\n",
       "      <td>270008</td>\n",
       "      <td>20.5</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR78</td>\n",
       "      <td>888084</td>\n",
       "      <td>202692</td>\n",
       "      <td>22.8</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331909</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>All</td>\n",
       "      <td>SD</td>\n",
       "      <td>183</td>\n",
       "      <td>57</td>\n",
       "      <td>31.1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331910</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>All</td>\n",
       "      <td>SE</td>\n",
       "      <td>42</td>\n",
       "      <td>16</td>\n",
       "      <td>38.1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331912</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>All</td>\n",
       "      <td>SH</td>\n",
       "      <td>46</td>\n",
       "      <td>18</td>\n",
       "      <td>39.1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331914</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>All</td>\n",
       "      <td>SS</td>\n",
       "      <td>920</td>\n",
       "      <td>227</td>\n",
       "      <td>24.7</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331915</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>1145</td>\n",
       "      <td>261</td>\n",
       "      <td>22.8</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>41525 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Academic Year Aggregate Level County Code District Code School Code  \\\n",
       "0            2022-23               T          00           NaN         NaN   \n",
       "1            2022-23               T          00           NaN         NaN   \n",
       "2            2022-23               T          00           NaN         NaN   \n",
       "3            2022-23               T          00           NaN         NaN   \n",
       "4            2022-23               T          00           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "331909       2022-23               D          58         72769     0000000   \n",
       "331910       2022-23               D          58         72769     0000000   \n",
       "331912       2022-23               D          58         72769     0000000   \n",
       "331914       2022-23               D          58         72769     0000000   \n",
       "331915       2022-23               D          58         72769     0000000   \n",
       "\n",
       "       County Name         District Name      School Name Charter School DASS  \\\n",
       "0            State                   NaN              NaN            All  All   \n",
       "1            State                   NaN              NaN            All  All   \n",
       "2            State                   NaN              NaN            All  All   \n",
       "3            State                   NaN              NaN            All  All   \n",
       "4            State                   NaN              NaN            All  All   \n",
       "...            ...                   ...              ...            ...  ...   \n",
       "331909        Yuba  Wheatland Union High  District Office            No   All   \n",
       "331910        Yuba  Wheatland Union High  District Office            No   All   \n",
       "331912        Yuba  Wheatland Union High  District Office            No   All   \n",
       "331914        Yuba  Wheatland Union High  District Office            No   All   \n",
       "331915        Yuba  Wheatland Union High  District Office            No   All   \n",
       "\n",
       "       Reporting Category ChronicAbsenteeismEligibleCumulativeEnrollment  \\\n",
       "0                      GF                                        2893518   \n",
       "1                      GM                                        3059625   \n",
       "2                    GR13                                        1266699   \n",
       "3                    GR46                                        1318062   \n",
       "4                    GR78                                         888084   \n",
       "...                   ...                                            ...   \n",
       "331909                 SD                                            183   \n",
       "331910                 SE                                             42   \n",
       "331912                 SH                                             46   \n",
       "331914                 SS                                            920   \n",
       "331915                 TA                                           1145   \n",
       "\n",
       "       ChronicAbsenteeismCount ChronicAbsenteeismRate  is_wcc_entity  \n",
       "0                       719562                   24.9          False  \n",
       "1                       764487                     25          False  \n",
       "2                       309500                   24.4          False  \n",
       "3                       270008                   20.5          False  \n",
       "4                       202692                   22.8          False  \n",
       "...                        ...                    ...            ...  \n",
       "331909                      57                   31.1          False  \n",
       "331910                      16                   38.1          False  \n",
       "331912                      18                   39.1          False  \n",
       "331914                     227                   24.7          False  \n",
       "331915                     261                   22.8          False  \n",
       "\n",
       "[41525 rows x 15 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Conditions for rows to be removed\n",
    "condition1 = (final_filtered_dataframe23['Aggregate Level'] == 'T') & (final_filtered_dataframe23['DASS'] != 'All')\n",
    "condition2 = (final_filtered_dataframe23['Aggregate Level'] == 'D') & (final_filtered_dataframe23['DASS'] != 'All')\n",
    "\n",
    "# Combine conditions with an OR '|', since rows meeting either condition should be removed\n",
    "conditions_combined = condition1 | condition2\n",
    "\n",
    "# Invert the conditions to keep rows that do NOT meet either condition\n",
    "final_filtered_dataframe23 = final_filtered_dataframe23[~conditions_combined]\n",
    "final_filtered_dataframe23"
   ]
  },
  {
   "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>Aggregate Level</th>\n",
       "      <th>DASS</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>D</td>\n",
       "      <td>All</td>\n",
       "      <td>40495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S</td>\n",
       "      <td>No</td>\n",
       "      <td>942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S</td>\n",
       "      <td>Yes</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>T</td>\n",
       "      <td>All</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Aggregate Level DASS  count\n",
       "0               D  All  40495\n",
       "1               S  No     942\n",
       "2               S  Yes     13\n",
       "3               T  All     75"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_counts = final_filtered_dataframe23.groupby(['Aggregate Level', 'DASS']).size().reset_index(name='count')\n",
    "grouped_counts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2022"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Academic Year</th>\n",
       "      <th>Aggregate Level</th>\n",
       "      <th>County Code</th>\n",
       "      <th>District Code</th>\n",
       "      <th>School Code</th>\n",
       "      <th>County Name</th>\n",
       "      <th>District Name</th>\n",
       "      <th>School Name</th>\n",
       "      <th>Charter School</th>\n",
       "      <th>DASS</th>\n",
       "      <th>Reporting Category</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumulativeEnrollment</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>2913279</td>\n",
       "      <td>866427</td>\n",
       "      <td>29.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>3077837</td>\n",
       "      <td>931454</td>\n",
       "      <td>30.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR13</td>\n",
       "      <td>1280630</td>\n",
       "      <td>397646</td>\n",
       "      <td>31.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR46</td>\n",
       "      <td>1333547</td>\n",
       "      <td>363188</td>\n",
       "      <td>27.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR78</td>\n",
       "      <td>903122</td>\n",
       "      <td>241996</td>\n",
       "      <td>26.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342602</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SF</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342603</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SH</td>\n",
       "      <td>22</td>\n",
       "      <td>10</td>\n",
       "      <td>45.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342604</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SM</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342605</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>245</td>\n",
       "      <td>83</td>\n",
       "      <td>33.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342606</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>263</td>\n",
       "      <td>87</td>\n",
       "      <td>33.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>342607 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Academic Year Aggregate Level County Code District Code School Code  \\\n",
       "0            2021-22               T          00           NaN         NaN   \n",
       "1            2021-22               T          00           NaN         NaN   \n",
       "2            2021-22               T          00           NaN         NaN   \n",
       "3            2021-22               T          00           NaN         NaN   \n",
       "4            2021-22               T          00           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "342602       2021-22               C          58           NaN         NaN   \n",
       "342603       2021-22               C          58           NaN         NaN   \n",
       "342604       2021-22               C          58           NaN         NaN   \n",
       "342605       2021-22               C          58           NaN         NaN   \n",
       "342606       2021-22               C          58           NaN         NaN   \n",
       "\n",
       "       County Name District Name School Name Charter School DASS  \\\n",
       "0            State           NaN         NaN            All  All   \n",
       "1            State           NaN         NaN            All  All   \n",
       "2            State           NaN         NaN            All  All   \n",
       "3            State           NaN         NaN            All  All   \n",
       "4            State           NaN         NaN            All  All   \n",
       "...            ...           ...         ...            ...  ...   \n",
       "342602        Yuba           NaN         NaN            Yes  Yes   \n",
       "342603        Yuba           NaN         NaN            Yes  Yes   \n",
       "342604        Yuba           NaN         NaN            Yes  Yes   \n",
       "342605        Yuba           NaN         NaN            Yes  Yes   \n",
       "342606        Yuba           NaN         NaN            Yes  Yes   \n",
       "\n",
       "       Reporting Category ChronicAbsenteeismEligibleCumulativeEnrollment  \\\n",
       "0                      GF                                        2913279   \n",
       "1                      GM                                        3077837   \n",
       "2                    GR13                                        1280630   \n",
       "3                    GR46                                        1333547   \n",
       "4                    GR78                                         903122   \n",
       "...                   ...                                            ...   \n",
       "342602                 SF                                              *   \n",
       "342603                 SH                                             22   \n",
       "342604                 SM                                              *   \n",
       "342605                 SS                                            245   \n",
       "342606                 TA                                            263   \n",
       "\n",
       "       ChronicAbsenteeismCount ChronicAbsenteeismRate  \n",
       "0                       866427                   29.7  \n",
       "1                       931454                   30.3  \n",
       "2                       397646                   31.1  \n",
       "3                       363188                   27.2  \n",
       "4                       241996                   26.8  \n",
       "...                        ...                    ...  \n",
       "342602                       *                      *  \n",
       "342603                      10                   45.5  \n",
       "342604                       *                      *  \n",
       "342605                      83                   33.9  \n",
       "342606                      87                   33.1  \n",
       "\n",
       "[342607 rows x 14 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chronicabsenteeism22_v3_df = pd.read_csv('chronic_absenteeism_csv_files/chronicabsenteeism22-v3.csv', encoding='ISO-8859-1',dtype=str)\n",
    "chronicabsenteeism22_v3_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Academic Year</th>\n",
       "      <th>Aggregate Level</th>\n",
       "      <th>County Code</th>\n",
       "      <th>District Code</th>\n",
       "      <th>School Code</th>\n",
       "      <th>County Name</th>\n",
       "      <th>District Name</th>\n",
       "      <th>School Name</th>\n",
       "      <th>Charter School</th>\n",
       "      <th>DASS</th>\n",
       "      <th>Reporting Category</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumulativeEnrollment</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "      <th>is_wcc_entity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>2913279</td>\n",
       "      <td>866427</td>\n",
       "      <td>29.7</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>3077837</td>\n",
       "      <td>931454</td>\n",
       "      <td>30.3</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR13</td>\n",
       "      <td>1280630</td>\n",
       "      <td>397646</td>\n",
       "      <td>31.1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR46</td>\n",
       "      <td>1333547</td>\n",
       "      <td>363188</td>\n",
       "      <td>27.2</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR78</td>\n",
       "      <td>903122</td>\n",
       "      <td>241996</td>\n",
       "      <td>26.8</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342602</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SF</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342603</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SH</td>\n",
       "      <td>22</td>\n",
       "      <td>10</td>\n",
       "      <td>45.5</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342604</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SM</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342605</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>245</td>\n",
       "      <td>83</td>\n",
       "      <td>33.9</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342606</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>C</td>\n",
       "      <td>58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>263</td>\n",
       "      <td>87</td>\n",
       "      <td>33.1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>342607 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Academic Year Aggregate Level County Code District Code School Code  \\\n",
       "0            2021-22               T          00           NaN         NaN   \n",
       "1            2021-22               T          00           NaN         NaN   \n",
       "2            2021-22               T          00           NaN         NaN   \n",
       "3            2021-22               T          00           NaN         NaN   \n",
       "4            2021-22               T          00           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "342602       2021-22               C          58           NaN         NaN   \n",
       "342603       2021-22               C          58           NaN         NaN   \n",
       "342604       2021-22               C          58           NaN         NaN   \n",
       "342605       2021-22               C          58           NaN         NaN   \n",
       "342606       2021-22               C          58           NaN         NaN   \n",
       "\n",
       "       County Name District Name School Name Charter School DASS  \\\n",
       "0            State           NaN         NaN            All  All   \n",
       "1            State           NaN         NaN            All  All   \n",
       "2            State           NaN         NaN            All  All   \n",
       "3            State           NaN         NaN            All  All   \n",
       "4            State           NaN         NaN            All  All   \n",
       "...            ...           ...         ...            ...  ...   \n",
       "342602        Yuba           NaN         NaN            Yes  Yes   \n",
       "342603        Yuba           NaN         NaN            Yes  Yes   \n",
       "342604        Yuba           NaN         NaN            Yes  Yes   \n",
       "342605        Yuba           NaN         NaN            Yes  Yes   \n",
       "342606        Yuba           NaN         NaN            Yes  Yes   \n",
       "\n",
       "       Reporting Category ChronicAbsenteeismEligibleCumulativeEnrollment  \\\n",
       "0                      GF                                        2913279   \n",
       "1                      GM                                        3077837   \n",
       "2                    GR13                                        1280630   \n",
       "3                    GR46                                        1333547   \n",
       "4                    GR78                                         903122   \n",
       "...                   ...                                            ...   \n",
       "342602                 SF                                              *   \n",
       "342603                 SH                                             22   \n",
       "342604                 SM                                              *   \n",
       "342605                 SS                                            245   \n",
       "342606                 TA                                            263   \n",
       "\n",
       "       ChronicAbsenteeismCount ChronicAbsenteeismRate  is_wcc_entity  \n",
       "0                       866427                   29.7          False  \n",
       "1                       931454                   30.3          False  \n",
       "2                       397646                   31.1          False  \n",
       "3                       363188                   27.2          False  \n",
       "4                       241996                   26.8          False  \n",
       "...                        ...                    ...            ...  \n",
       "342602                       *                      *          False  \n",
       "342603                      10                   45.5          False  \n",
       "342604                       *                      *          False  \n",
       "342605                      83                   33.9          False  \n",
       "342606                      87                   33.1          False  \n",
       "\n",
       "[342607 rows x 15 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 1: Create a set of unique school codes from dataframe2 for efficient lookup\n",
    "wcc_schools_list = set(wcc_schools_df['School Code'])\n",
    "\n",
    "# Step 2: Use the .isin() method to check if each school code in dataframe1 is in the set of schools from dataframe2\n",
    "chronicabsenteeism22_v3_df['is_wcc_entity'] = chronicabsenteeism22_v3_df['School Code'].isin(wcc_schools_list)\n",
    "\n",
    "chronicabsenteeism22_v3_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Academic Year</th>\n",
       "      <th>Aggregate Level</th>\n",
       "      <th>County Code</th>\n",
       "      <th>District Code</th>\n",
       "      <th>School Code</th>\n",
       "      <th>County Name</th>\n",
       "      <th>District Name</th>\n",
       "      <th>School Name</th>\n",
       "      <th>Charter School</th>\n",
       "      <th>DASS</th>\n",
       "      <th>Reporting Category</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumulativeEnrollment</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "      <th>is_wcc_entity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>2913279</td>\n",
       "      <td>866427</td>\n",
       "      <td>29.7</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>3077837</td>\n",
       "      <td>931454</td>\n",
       "      <td>30.3</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR13</td>\n",
       "      <td>1280630</td>\n",
       "      <td>397646</td>\n",
       "      <td>31.1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR46</td>\n",
       "      <td>1333547</td>\n",
       "      <td>363188</td>\n",
       "      <td>27.2</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR78</td>\n",
       "      <td>903122</td>\n",
       "      <td>241996</td>\n",
       "      <td>26.8</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330926</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>RH</td>\n",
       "      <td>28</td>\n",
       "      <td>18</td>\n",
       "      <td>64.3</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330930</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>RW</td>\n",
       "      <td>35</td>\n",
       "      <td>32</td>\n",
       "      <td>91.4</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330931</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SD</td>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>75</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330936</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>70</td>\n",
       "      <td>55</td>\n",
       "      <td>78.6</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330937</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>75</td>\n",
       "      <td>59</td>\n",
       "      <td>78.7</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>90899 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Academic Year Aggregate Level County Code District Code School Code  \\\n",
       "0            2021-22               T          00           NaN         NaN   \n",
       "1            2021-22               T          00           NaN         NaN   \n",
       "2            2021-22               T          00           NaN         NaN   \n",
       "3            2021-22               T          00           NaN         NaN   \n",
       "4            2021-22               T          00           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "330926       2021-22               D          58         72769     0000000   \n",
       "330930       2021-22               D          58         72769     0000000   \n",
       "330931       2021-22               D          58         72769     0000000   \n",
       "330936       2021-22               D          58         72769     0000000   \n",
       "330937       2021-22               D          58         72769     0000000   \n",
       "\n",
       "       County Name         District Name      School Name Charter School DASS  \\\n",
       "0            State                   NaN              NaN            All  All   \n",
       "1            State                   NaN              NaN            All  All   \n",
       "2            State                   NaN              NaN            All  All   \n",
       "3            State                   NaN              NaN            All  All   \n",
       "4            State                   NaN              NaN            All  All   \n",
       "...            ...                   ...              ...            ...  ...   \n",
       "330926        Yuba  Wheatland Union High  District Office            No   Yes   \n",
       "330930        Yuba  Wheatland Union High  District Office            No   Yes   \n",
       "330931        Yuba  Wheatland Union High  District Office            No   Yes   \n",
       "330936        Yuba  Wheatland Union High  District Office            No   Yes   \n",
       "330937        Yuba  Wheatland Union High  District Office            No   Yes   \n",
       "\n",
       "       Reporting Category ChronicAbsenteeismEligibleCumulativeEnrollment  \\\n",
       "0                      GF                                        2913279   \n",
       "1                      GM                                        3077837   \n",
       "2                    GR13                                        1280630   \n",
       "3                    GR46                                        1333547   \n",
       "4                    GR78                                         903122   \n",
       "...                   ...                                            ...   \n",
       "330926                 RH                                             28   \n",
       "330930                 RW                                             35   \n",
       "330931                 SD                                             16   \n",
       "330936                 SS                                             70   \n",
       "330937                 TA                                             75   \n",
       "\n",
       "       ChronicAbsenteeismCount ChronicAbsenteeismRate  is_wcc_entity  \n",
       "0                       866427                   29.7          False  \n",
       "1                       931454                   30.3          False  \n",
       "2                       397646                   31.1          False  \n",
       "3                       363188                   27.2          False  \n",
       "4                       241996                   26.8          False  \n",
       "...                        ...                    ...            ...  \n",
       "330926                      18                   64.3          False  \n",
       "330930                      32                   91.4          False  \n",
       "330931                      12                     75          False  \n",
       "330936                      55                   78.6          False  \n",
       "330937                      59                   78.7          False  \n",
       "\n",
       "[90899 rows x 15 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Existing conditions for \"Aggregate Level\" and \"is_wcc_entity\"\n",
    "condition1 = chronicabsenteeism22_v3_df['Aggregate Level'].isin(['T', 'D'])\n",
    "condition2 = chronicabsenteeism22_v3_df['is_wcc_entity']\n",
    "\n",
    "# New conditions to exclude rows with \"*\" in the chronic absenteeism columns\n",
    "condition3 = ~chronicabsenteeism22_v3_df['ChronicAbsenteeismEligibleCumulativeEnrollment'].str.contains('*', regex=False)\n",
    "condition4 = ~chronicabsenteeism22_v3_df['ChronicAbsenteeismCount'].str.contains('*', regex=False)\n",
    "condition5 = ~chronicabsenteeism22_v3_df['ChronicAbsenteeismRate'].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 & condition4 & condition5)\n",
    "\n",
    "# Filter the dataframe using the combined condition\n",
    "final_filtered_dataframe22 = chronicabsenteeism22_v3_df[combined_condition]\n",
    "\n",
    "final_filtered_dataframe22"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Academic Year</th>\n",
       "      <th>Aggregate Level</th>\n",
       "      <th>County Code</th>\n",
       "      <th>District Code</th>\n",
       "      <th>School Code</th>\n",
       "      <th>County Name</th>\n",
       "      <th>District Name</th>\n",
       "      <th>School Name</th>\n",
       "      <th>Charter School</th>\n",
       "      <th>DASS</th>\n",
       "      <th>Reporting Category</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumulativeEnrollment</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "      <th>is_wcc_entity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>2913279</td>\n",
       "      <td>866427</td>\n",
       "      <td>29.7</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>3077837</td>\n",
       "      <td>931454</td>\n",
       "      <td>30.3</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR13</td>\n",
       "      <td>1280630</td>\n",
       "      <td>397646</td>\n",
       "      <td>31.1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR46</td>\n",
       "      <td>1333547</td>\n",
       "      <td>363188</td>\n",
       "      <td>27.2</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR78</td>\n",
       "      <td>903122</td>\n",
       "      <td>241996</td>\n",
       "      <td>26.8</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330891</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>All</td>\n",
       "      <td>SE</td>\n",
       "      <td>50</td>\n",
       "      <td>15</td>\n",
       "      <td>30</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330892</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>All</td>\n",
       "      <td>SF</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>27.3</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330893</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>All</td>\n",
       "      <td>SH</td>\n",
       "      <td>34</td>\n",
       "      <td>15</td>\n",
       "      <td>44.1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330895</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>All</td>\n",
       "      <td>SS</td>\n",
       "      <td>830</td>\n",
       "      <td>225</td>\n",
       "      <td>27.1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330896</th>\n",
       "      <td>2021-22</td>\n",
       "      <td>D</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>Wheatland Union High</td>\n",
       "      <td>District Office</td>\n",
       "      <td>No</td>\n",
       "      <td>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>1111</td>\n",
       "      <td>259</td>\n",
       "      <td>23.3</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>41610 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Academic Year Aggregate Level County Code District Code School Code  \\\n",
       "0            2021-22               T          00           NaN         NaN   \n",
       "1            2021-22               T          00           NaN         NaN   \n",
       "2            2021-22               T          00           NaN         NaN   \n",
       "3            2021-22               T          00           NaN         NaN   \n",
       "4            2021-22               T          00           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "330891       2021-22               D          58         72769     0000000   \n",
       "330892       2021-22               D          58         72769     0000000   \n",
       "330893       2021-22               D          58         72769     0000000   \n",
       "330895       2021-22               D          58         72769     0000000   \n",
       "330896       2021-22               D          58         72769     0000000   \n",
       "\n",
       "       County Name         District Name      School Name Charter School DASS  \\\n",
       "0            State                   NaN              NaN            All  All   \n",
       "1            State                   NaN              NaN            All  All   \n",
       "2            State                   NaN              NaN            All  All   \n",
       "3            State                   NaN              NaN            All  All   \n",
       "4            State                   NaN              NaN            All  All   \n",
       "...            ...                   ...              ...            ...  ...   \n",
       "330891        Yuba  Wheatland Union High  District Office            No   All   \n",
       "330892        Yuba  Wheatland Union High  District Office            No   All   \n",
       "330893        Yuba  Wheatland Union High  District Office            No   All   \n",
       "330895        Yuba  Wheatland Union High  District Office            No   All   \n",
       "330896        Yuba  Wheatland Union High  District Office            No   All   \n",
       "\n",
       "       Reporting Category ChronicAbsenteeismEligibleCumulativeEnrollment  \\\n",
       "0                      GF                                        2913279   \n",
       "1                      GM                                        3077837   \n",
       "2                    GR13                                        1280630   \n",
       "3                    GR46                                        1333547   \n",
       "4                    GR78                                         903122   \n",
       "...                   ...                                            ...   \n",
       "330891                 SE                                             50   \n",
       "330892                 SF                                             11   \n",
       "330893                 SH                                             34   \n",
       "330895                 SS                                            830   \n",
       "330896                 TA                                           1111   \n",
       "\n",
       "       ChronicAbsenteeismCount ChronicAbsenteeismRate  is_wcc_entity  \n",
       "0                       866427                   29.7          False  \n",
       "1                       931454                   30.3          False  \n",
       "2                       397646                   31.1          False  \n",
       "3                       363188                   27.2          False  \n",
       "4                       241996                   26.8          False  \n",
       "...                        ...                    ...            ...  \n",
       "330891                      15                     30          False  \n",
       "330892                       3                   27.3          False  \n",
       "330893                      15                   44.1          False  \n",
       "330895                     225                   27.1          False  \n",
       "330896                     259                   23.3          False  \n",
       "\n",
       "[41610 rows x 15 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Conditions for rows to be removed\n",
    "condition1 = (final_filtered_dataframe22['Aggregate Level'] == 'T') & (final_filtered_dataframe22['DASS'] != 'All')\n",
    "condition2 = (final_filtered_dataframe22['Aggregate Level'] == 'D') & (final_filtered_dataframe22['DASS'] != 'All')\n",
    "\n",
    "# Combine conditions with an OR '|', since rows meeting either condition should be removed\n",
    "conditions_combined = condition1 | condition2\n",
    "\n",
    "# Invert the conditions to keep rows that do NOT meet either condition\n",
    "final_filtered_dataframe22 = final_filtered_dataframe22[~conditions_combined]\n",
    "final_filtered_dataframe22"
   ]
  },
  {
   "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>Aggregate Level</th>\n",
       "      <th>DASS</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>D</td>\n",
       "      <td>All</td>\n",
       "      <td>40585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S</td>\n",
       "      <td>No</td>\n",
       "      <td>939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S</td>\n",
       "      <td>Yes</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>T</td>\n",
       "      <td>All</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Aggregate Level DASS  count\n",
       "0               D  All  40585\n",
       "1               S  No     939\n",
       "2               S  Yes     11\n",
       "3               T  All     75"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_counts = final_filtered_dataframe22.groupby(['Aggregate Level', 'DASS']).size().reset_index(name='count')\n",
    "grouped_counts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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 School</th>\n",
       "      <th>Reporting Category</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumula</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</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>Yes</td>\n",
       "      <td>GF</td>\n",
       "      <td>11699</td>\n",
       "      <td>1270</td>\n",
       "      <td>10.9</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>GF</td>\n",
       "      <td>109029</td>\n",
       "      <td>11268</td>\n",
       "      <td>10.3</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>No</td>\n",
       "      <td>GF</td>\n",
       "      <td>97419</td>\n",
       "      <td>10025</td>\n",
       "      <td>10.3</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>Yes</td>\n",
       "      <td>GM</td>\n",
       "      <td>11650</td>\n",
       "      <td>1448</td>\n",
       "      <td>12.4</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>No</td>\n",
       "      <td>GM</td>\n",
       "      <td>104393</td>\n",
       "      <td>12414</td>\n",
       "      <td>11.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263135</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>All</td>\n",
       "      <td>SS</td>\n",
       "      <td>3745101</td>\n",
       "      <td>724682</td>\n",
       "      <td>19.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263136</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>439770</td>\n",
       "      <td>86323</td>\n",
       "      <td>19.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263137</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>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>6072413</td>\n",
       "      <td>866842</td>\n",
       "      <td>14.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263138</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>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>5379464</td>\n",
       "      <td>769832</td>\n",
       "      <td>14.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263139</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>722404</td>\n",
       "      <td>104966</td>\n",
       "      <td>14.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>263140 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Academic Year Aggregate Level County Code District Code School Code  \\\n",
       "0            2020-21               C          01           NaN         NaN   \n",
       "1            2020-21               C          01           NaN         NaN   \n",
       "2            2020-21               C          01           NaN         NaN   \n",
       "3            2020-21               C          01           NaN         NaN   \n",
       "4            2020-21               C          01           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "263135       2020-21               T          00           NaN         NaN   \n",
       "263136       2020-21               T          00           NaN         NaN   \n",
       "263137       2020-21               T          00           NaN         NaN   \n",
       "263138       2020-21               T          00           NaN         NaN   \n",
       "263139       2020-21               T          00           NaN         NaN   \n",
       "\n",
       "       County Name District Name School Name Charter School  \\\n",
       "0          Alameda           NaN         NaN            Yes   \n",
       "1          Alameda           NaN         NaN            All   \n",
       "2          Alameda           NaN         NaN             No   \n",
       "3          Alameda           NaN         NaN            Yes   \n",
       "4          Alameda           NaN         NaN             No   \n",
       "...            ...           ...         ...            ...   \n",
       "263135       State           NaN         NaN            All   \n",
       "263136       State           NaN         NaN            Yes   \n",
       "263137       State           NaN         NaN            All   \n",
       "263138       State           NaN         NaN             No   \n",
       "263139       State           NaN         NaN            Yes   \n",
       "\n",
       "       Reporting Category ChronicAbsenteeismEligibleCumula  \\\n",
       "0                      GF                            11699   \n",
       "1                      GF                           109029   \n",
       "2                      GF                            97419   \n",
       "3                      GM                            11650   \n",
       "4                      GM                           104393   \n",
       "...                   ...                              ...   \n",
       "263135                 SS                          3745101   \n",
       "263136                 SS                           439770   \n",
       "263137                 TA                          6072413   \n",
       "263138                 TA                          5379464   \n",
       "263139                 TA                           722404   \n",
       "\n",
       "       ChronicAbsenteeismCount ChronicAbsenteeismRate  \n",
       "0                         1270                   10.9  \n",
       "1                        11268                   10.3  \n",
       "2                        10025                   10.3  \n",
       "3                         1448                   12.4  \n",
       "4                        12414                   11.9  \n",
       "...                        ...                    ...  \n",
       "263135                  724682                   19.4  \n",
       "263136                   86323                   19.6  \n",
       "263137                  866842                   14.3  \n",
       "263138                  769832                   14.3  \n",
       "263139                  104966                   14.5  \n",
       "\n",
       "[263140 rows x 13 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chronicabsenteeism21_df = pd.read_csv('chronic_absenteeism_csv_files/chronicabsenteeism21.csv', encoding='ISO-8859-1',dtype=str)\n",
    "chronicabsenteeism21_df"
   ]
  },
  {
   "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>County Name</th>\n",
       "      <th>District Name</th>\n",
       "      <th>School Name</th>\n",
       "      <th>Charter School</th>\n",
       "      <th>Reporting Category</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumula</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "      <th>is_wcc_entity</th>\n",
       "      <th>DASS</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>Yes</td>\n",
       "      <td>GF</td>\n",
       "      <td>11699</td>\n",
       "      <td>1270</td>\n",
       "      <td>10.9</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</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>GF</td>\n",
       "      <td>109029</td>\n",
       "      <td>11268</td>\n",
       "      <td>10.3</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</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>No</td>\n",
       "      <td>GF</td>\n",
       "      <td>97419</td>\n",
       "      <td>10025</td>\n",
       "      <td>10.3</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</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>Yes</td>\n",
       "      <td>GM</td>\n",
       "      <td>11650</td>\n",
       "      <td>1448</td>\n",
       "      <td>12.4</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</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>No</td>\n",
       "      <td>GM</td>\n",
       "      <td>104393</td>\n",
       "      <td>12414</td>\n",
       "      <td>11.9</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263135</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>All</td>\n",
       "      <td>SS</td>\n",
       "      <td>3745101</td>\n",
       "      <td>724682</td>\n",
       "      <td>19.4</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263136</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>439770</td>\n",
       "      <td>86323</td>\n",
       "      <td>19.6</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263137</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>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>6072413</td>\n",
       "      <td>866842</td>\n",
       "      <td>14.3</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263138</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>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>5379464</td>\n",
       "      <td>769832</td>\n",
       "      <td>14.3</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263139</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>722404</td>\n",
       "      <td>104966</td>\n",
       "      <td>14.5</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>263140 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Academic Year Aggregate Level County Code District Code School Code  \\\n",
       "0            2020-21               C          01           NaN         NaN   \n",
       "1            2020-21               C          01           NaN         NaN   \n",
       "2            2020-21               C          01           NaN         NaN   \n",
       "3            2020-21               C          01           NaN         NaN   \n",
       "4            2020-21               C          01           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "263135       2020-21               T          00           NaN         NaN   \n",
       "263136       2020-21               T          00           NaN         NaN   \n",
       "263137       2020-21               T          00           NaN         NaN   \n",
       "263138       2020-21               T          00           NaN         NaN   \n",
       "263139       2020-21               T          00           NaN         NaN   \n",
       "\n",
       "       County Name District Name School Name Charter School  \\\n",
       "0          Alameda           NaN         NaN            Yes   \n",
       "1          Alameda           NaN         NaN            All   \n",
       "2          Alameda           NaN         NaN             No   \n",
       "3          Alameda           NaN         NaN            Yes   \n",
       "4          Alameda           NaN         NaN             No   \n",
       "...            ...           ...         ...            ...   \n",
       "263135       State           NaN         NaN            All   \n",
       "263136       State           NaN         NaN            Yes   \n",
       "263137       State           NaN         NaN            All   \n",
       "263138       State           NaN         NaN             No   \n",
       "263139       State           NaN         NaN            Yes   \n",
       "\n",
       "       Reporting Category ChronicAbsenteeismEligibleCumula  \\\n",
       "0                      GF                            11699   \n",
       "1                      GF                           109029   \n",
       "2                      GF                            97419   \n",
       "3                      GM                            11650   \n",
       "4                      GM                           104393   \n",
       "...                   ...                              ...   \n",
       "263135                 SS                          3745101   \n",
       "263136                 SS                           439770   \n",
       "263137                 TA                          6072413   \n",
       "263138                 TA                          5379464   \n",
       "263139                 TA                           722404   \n",
       "\n",
       "       ChronicAbsenteeismCount ChronicAbsenteeismRate  is_wcc_entity DASS  \n",
       "0                         1270                   10.9          False   NV  \n",
       "1                        11268                   10.3          False   NV  \n",
       "2                        10025                   10.3          False   NV  \n",
       "3                         1448                   12.4          False   NV  \n",
       "4                        12414                   11.9          False   NV  \n",
       "...                        ...                    ...            ...  ...  \n",
       "263135                  724682                   19.4          False   NV  \n",
       "263136                   86323                   19.6          False   NV  \n",
       "263137                  866842                   14.3          False   NV  \n",
       "263138                  769832                   14.3          False   NV  \n",
       "263139                  104966                   14.5          False   NV  \n",
       "\n",
       "[263140 rows x 15 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 1: Create a set of unique school codes from dataframe2 for efficient lookup\n",
    "wcc_schools_list = set(wcc_schools_df['School Code'])\n",
    "\n",
    "# Step 2: Use the .isin() method to check if each school code in dataframe1 is in the set of schools from dataframe2\n",
    "chronicabsenteeism21_df['is_wcc_entity'] = chronicabsenteeism21_df['School Code'].isin(wcc_schools_list)\n",
    "chronicabsenteeism21_df['DASS'] = 'NV'\n",
    "chronicabsenteeism21_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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 School</th>\n",
       "      <th>Reporting Category</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumula</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "      <th>is_wcc_entity</th>\n",
       "      <th>DASS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4081</th>\n",
       "      <td>2020-21</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>2092</td>\n",
       "      <td>351</td>\n",
       "      <td>16.8</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4082</th>\n",
       "      <td>2020-21</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>Yes</td>\n",
       "      <td>GF</td>\n",
       "      <td>2008</td>\n",
       "      <td>283</td>\n",
       "      <td>14.1</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4083</th>\n",
       "      <td>2020-21</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>No</td>\n",
       "      <td>GF</td>\n",
       "      <td>84</td>\n",
       "      <td>68</td>\n",
       "      <td>81</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4084</th>\n",
       "      <td>2020-21</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>2098</td>\n",
       "      <td>420</td>\n",
       "      <td>20</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4085</th>\n",
       "      <td>2020-21</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>Yes</td>\n",
       "      <td>GM</td>\n",
       "      <td>1954</td>\n",
       "      <td>319</td>\n",
       "      <td>16.3</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263135</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>All</td>\n",
       "      <td>SS</td>\n",
       "      <td>3745101</td>\n",
       "      <td>724682</td>\n",
       "      <td>19.4</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263136</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>439770</td>\n",
       "      <td>86323</td>\n",
       "      <td>19.6</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263137</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>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>6072413</td>\n",
       "      <td>866842</td>\n",
       "      <td>14.3</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263138</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>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>5379464</td>\n",
       "      <td>769832</td>\n",
       "      <td>14.3</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263139</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>722404</td>\n",
       "      <td>104966</td>\n",
       "      <td>14.5</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>41441 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Academic Year Aggregate Level County Code District Code School Code  \\\n",
       "4081         2020-21               D          01         10017     0000000   \n",
       "4082         2020-21               D          01         10017     0000000   \n",
       "4083         2020-21               D          01         10017     0000000   \n",
       "4084         2020-21               D          01         10017     0000000   \n",
       "4085         2020-21               D          01         10017     0000000   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "263135       2020-21               T          00           NaN         NaN   \n",
       "263136       2020-21               T          00           NaN         NaN   \n",
       "263137       2020-21               T          00           NaN         NaN   \n",
       "263138       2020-21               T          00           NaN         NaN   \n",
       "263139       2020-21               T          00           NaN         NaN   \n",
       "\n",
       "       County Name                       District Name      School Name  \\\n",
       "4081       Alameda  Alameda County Office of Education  District Office   \n",
       "4082       Alameda  Alameda County Office of Education  District Office   \n",
       "4083       Alameda  Alameda County Office of Education  District Office   \n",
       "4084       Alameda  Alameda County Office of Education  District Office   \n",
       "4085       Alameda  Alameda County Office of Education  District Office   \n",
       "...            ...                                 ...              ...   \n",
       "263135       State                                 NaN              NaN   \n",
       "263136       State                                 NaN              NaN   \n",
       "263137       State                                 NaN              NaN   \n",
       "263138       State                                 NaN              NaN   \n",
       "263139       State                                 NaN              NaN   \n",
       "\n",
       "       Charter School Reporting Category ChronicAbsenteeismEligibleCumula  \\\n",
       "4081              All                 GF                             2092   \n",
       "4082              Yes                 GF                             2008   \n",
       "4083               No                 GF                               84   \n",
       "4084              All                 GM                             2098   \n",
       "4085              Yes                 GM                             1954   \n",
       "...               ...                ...                              ...   \n",
       "263135            All                 SS                          3745101   \n",
       "263136            Yes                 SS                           439770   \n",
       "263137            All                 TA                          6072413   \n",
       "263138             No                 TA                          5379464   \n",
       "263139            Yes                 TA                           722404   \n",
       "\n",
       "       ChronicAbsenteeismCount ChronicAbsenteeismRate  is_wcc_entity DASS  \n",
       "4081                       351                   16.8          False   NV  \n",
       "4082                       283                   14.1          False   NV  \n",
       "4083                        68                     81          False   NV  \n",
       "4084                       420                     20          False   NV  \n",
       "4085                       319                   16.3          False   NV  \n",
       "...                        ...                    ...            ...  ...  \n",
       "263135                  724682                   19.4          False   NV  \n",
       "263136                   86323                   19.6          False   NV  \n",
       "263137                  866842                   14.3          False   NV  \n",
       "263138                  769832                   14.3          False   NV  \n",
       "263139                  104966                   14.5          False   NV  \n",
       "\n",
       "[41441 rows x 15 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Existing conditions for \"Aggregate Level\" and \"is_wcc_entity\"\n",
    "condition1 = chronicabsenteeism21_df['Aggregate Level'].isin(['T', 'D'])\n",
    "condition2 = chronicabsenteeism21_df['is_wcc_entity']\n",
    "\n",
    "# New conditions to exclude rows with \"*\" in the chronic absenteeism columns\n",
    "condition3 = ~chronicabsenteeism21_df['ChronicAbsenteeismEligibleCumula'].isnull()\n",
    "condition4 = ~chronicabsenteeism21_df['ChronicAbsenteeismCount'].isnull()\n",
    "condition5 = ~chronicabsenteeism21_df['ChronicAbsenteeismRate'].isnull()\n",
    "\n",
    "# Combine all conditions using \"AND\" (&) for the chronic absenteeism conditions and \"OR\" (|) for the initial conditions\n",
    "combined_condition = (condition1 | condition2) & (condition3 & condition4 & condition5)\n",
    "\n",
    "# Filter dataframe1 using the combined condition\n",
    "final_filtered_dataframe21 = chronicabsenteeism21_df[combined_condition]\n",
    "\n",
    "final_filtered_dataframe21"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_order = [\n",
    "    'Academic Year',\t\n",
    "    'Aggregate Level',\n",
    "    'County Code',\n",
    "    'District Code',\t\n",
    "    'School Code',\t\n",
    "    'County Name',\t\n",
    "    'District Name',\t\n",
    "    'School Name',\n",
    "    'Charter School',\n",
    "    'DASS',\t\n",
    "    'Reporting Category',\t\n",
    "    'ChronicAbsenteeismEligibleCumula',\t\n",
    "    'ChronicAbsenteeismCount',\t\n",
    "    'ChronicAbsenteeismRate',\t\n",
    "    'is_wcc_entity'\t\n",
    "]\n",
    "\n",
    "# Reorder and drop columns\n",
    "final_filtered_dataframe21 = final_filtered_dataframe21[new_order]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>CharterYN</th>\n",
       "      <th>ReportingCategory</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumula</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>112861</td>\n",
       "      <td>16501</td>\n",
       "      <td>14.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>GF</td>\n",
       "      <td>101285</td>\n",
       "      <td>15082</td>\n",
       "      <td>14.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>GF</td>\n",
       "      <td>11850</td>\n",
       "      <td>1530</td>\n",
       "      <td>12.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>120048</td>\n",
       "      <td>18167</td>\n",
       "      <td>15.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>GM</td>\n",
       "      <td>108370</td>\n",
       "      <td>16793</td>\n",
       "      <td>15.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239805</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>SS</td>\n",
       "      <td>3524760</td>\n",
       "      <td>536247</td>\n",
       "      <td>15.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239806</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>439477</td>\n",
       "      <td>76914</td>\n",
       "      <td>17.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239807</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>6258845</td>\n",
       "      <td>755950</td>\n",
       "      <td>12.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239808</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>5611163</td>\n",
       "      <td>676060</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239809</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>706658</td>\n",
       "      <td>99129</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>239810 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       AcademicYear AggregateLevel CountyCode DistrictCode SchoolCode  \\\n",
       "0           2018-19              C         01          NaN        NaN   \n",
       "1           2018-19              C         01          NaN        NaN   \n",
       "2           2018-19              C         01          NaN        NaN   \n",
       "3           2018-19              C         01          NaN        NaN   \n",
       "4           2018-19              C         01          NaN        NaN   \n",
       "...             ...            ...        ...          ...        ...   \n",
       "239805      2018-19              T         00          NaN        NaN   \n",
       "239806      2018-19              T         00          NaN        NaN   \n",
       "239807      2018-19              T         00          NaN        NaN   \n",
       "239808      2018-19              T         00          NaN        NaN   \n",
       "239809      2018-19              T         00          NaN        NaN   \n",
       "\n",
       "       CountyName DistrictName SchoolName CharterYN ReportingCategory  \\\n",
       "0         Alameda          NaN        NaN       All                GF   \n",
       "1         Alameda          NaN        NaN        No                GF   \n",
       "2         Alameda          NaN        NaN       Yes                GF   \n",
       "3         Alameda          NaN        NaN       All                GM   \n",
       "4         Alameda          NaN        NaN        No                GM   \n",
       "...           ...          ...        ...       ...               ...   \n",
       "239805      State          NaN        NaN        No                SS   \n",
       "239806      State          NaN        NaN       Yes                SS   \n",
       "239807      State          NaN        NaN       All                TA   \n",
       "239808      State          NaN        NaN        No                TA   \n",
       "239809      State          NaN        NaN       Yes                TA   \n",
       "\n",
       "       ChronicAbsenteeismEligibleCumula ChronicAbsenteeismCount  \\\n",
       "0                                112861                   16501   \n",
       "1                                101285                   15082   \n",
       "2                                 11850                    1530   \n",
       "3                                120048                   18167   \n",
       "4                                108370                   16793   \n",
       "...                                 ...                     ...   \n",
       "239805                          3524760                  536247   \n",
       "239806                           439477                   76914   \n",
       "239807                          6258845                  755950   \n",
       "239808                          5611163                  676060   \n",
       "239809                           706658                   99129   \n",
       "\n",
       "       ChronicAbsenteeismRate  \n",
       "0                        14.6  \n",
       "1                        14.9  \n",
       "2                        12.9  \n",
       "3                        15.1  \n",
       "4                        15.5  \n",
       "...                       ...  \n",
       "239805                   15.2  \n",
       "239806                   17.5  \n",
       "239807                   12.1  \n",
       "239808                     12  \n",
       "239809                     14  \n",
       "\n",
       "[239810 rows x 13 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chronicabsenteeism19_df = pd.read_csv('chronic_absenteeism_csv_files/chronicabsenteeism19.csv', encoding='ISO-8859-1',dtype=str)\n",
    "chronicabsenteeism19_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>CharterYN</th>\n",
       "      <th>ReportingCategory</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumula</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "      <th>is_wcc_entity</th>\n",
       "      <th>DASS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>112861</td>\n",
       "      <td>16501</td>\n",
       "      <td>14.6</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>GF</td>\n",
       "      <td>101285</td>\n",
       "      <td>15082</td>\n",
       "      <td>14.9</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>GF</td>\n",
       "      <td>11850</td>\n",
       "      <td>1530</td>\n",
       "      <td>12.9</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>120048</td>\n",
       "      <td>18167</td>\n",
       "      <td>15.1</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>GM</td>\n",
       "      <td>108370</td>\n",
       "      <td>16793</td>\n",
       "      <td>15.5</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239805</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>SS</td>\n",
       "      <td>3524760</td>\n",
       "      <td>536247</td>\n",
       "      <td>15.2</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239806</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>439477</td>\n",
       "      <td>76914</td>\n",
       "      <td>17.5</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239807</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>6258845</td>\n",
       "      <td>755950</td>\n",
       "      <td>12.1</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239808</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>5611163</td>\n",
       "      <td>676060</td>\n",
       "      <td>12</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239809</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>706658</td>\n",
       "      <td>99129</td>\n",
       "      <td>14</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>239810 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       AcademicYear AggregateLevel CountyCode DistrictCode SchoolCode  \\\n",
       "0           2018-19              C         01          NaN        NaN   \n",
       "1           2018-19              C         01          NaN        NaN   \n",
       "2           2018-19              C         01          NaN        NaN   \n",
       "3           2018-19              C         01          NaN        NaN   \n",
       "4           2018-19              C         01          NaN        NaN   \n",
       "...             ...            ...        ...          ...        ...   \n",
       "239805      2018-19              T         00          NaN        NaN   \n",
       "239806      2018-19              T         00          NaN        NaN   \n",
       "239807      2018-19              T         00          NaN        NaN   \n",
       "239808      2018-19              T         00          NaN        NaN   \n",
       "239809      2018-19              T         00          NaN        NaN   \n",
       "\n",
       "       CountyName DistrictName SchoolName CharterYN ReportingCategory  \\\n",
       "0         Alameda          NaN        NaN       All                GF   \n",
       "1         Alameda          NaN        NaN        No                GF   \n",
       "2         Alameda          NaN        NaN       Yes                GF   \n",
       "3         Alameda          NaN        NaN       All                GM   \n",
       "4         Alameda          NaN        NaN        No                GM   \n",
       "...           ...          ...        ...       ...               ...   \n",
       "239805      State          NaN        NaN        No                SS   \n",
       "239806      State          NaN        NaN       Yes                SS   \n",
       "239807      State          NaN        NaN       All                TA   \n",
       "239808      State          NaN        NaN        No                TA   \n",
       "239809      State          NaN        NaN       Yes                TA   \n",
       "\n",
       "       ChronicAbsenteeismEligibleCumula ChronicAbsenteeismCount  \\\n",
       "0                                112861                   16501   \n",
       "1                                101285                   15082   \n",
       "2                                 11850                    1530   \n",
       "3                                120048                   18167   \n",
       "4                                108370                   16793   \n",
       "...                                 ...                     ...   \n",
       "239805                          3524760                  536247   \n",
       "239806                           439477                   76914   \n",
       "239807                          6258845                  755950   \n",
       "239808                          5611163                  676060   \n",
       "239809                           706658                   99129   \n",
       "\n",
       "       ChronicAbsenteeismRate  is_wcc_entity DASS  \n",
       "0                        14.6          False   NV  \n",
       "1                        14.9          False   NV  \n",
       "2                        12.9          False   NV  \n",
       "3                        15.1          False   NV  \n",
       "4                        15.5          False   NV  \n",
       "...                       ...            ...  ...  \n",
       "239805                   15.2          False   NV  \n",
       "239806                   17.5          False   NV  \n",
       "239807                   12.1          False   NV  \n",
       "239808                     12          False   NV  \n",
       "239809                     14          False   NV  \n",
       "\n",
       "[239810 rows x 15 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 1: Create a set of unique school codes from dataframe2 for efficient lookup\n",
    "wcc_schools_list = set(wcc_schools_df['School Code'])\n",
    "\n",
    "# Step 2: Use the .isin() method to check if each school code in dataframe1 is in the set of schools from dataframe2\n",
    "chronicabsenteeism19_df['is_wcc_entity'] = chronicabsenteeism19_df['SchoolCode'].isin(wcc_schools_list)\n",
    "chronicabsenteeism19_df['DASS'] = 'NV'\n",
    "chronicabsenteeism19_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>CharterYN</th>\n",
       "      <th>ReportingCategory</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumula</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "      <th>is_wcc_entity</th>\n",
       "      <th>DASS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3950</th>\n",
       "      <td>2018-19</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>1978</td>\n",
       "      <td>368</td>\n",
       "      <td>18.6</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3951</th>\n",
       "      <td>2018-19</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>No</td>\n",
       "      <td>GF</td>\n",
       "      <td>166</td>\n",
       "      <td>110</td>\n",
       "      <td>66.3</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3952</th>\n",
       "      <td>2018-19</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>Yes</td>\n",
       "      <td>GF</td>\n",
       "      <td>1818</td>\n",
       "      <td>263</td>\n",
       "      <td>14.5</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3953</th>\n",
       "      <td>2018-19</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>1980</td>\n",
       "      <td>371</td>\n",
       "      <td>18.7</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3954</th>\n",
       "      <td>2018-19</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>No</td>\n",
       "      <td>GM</td>\n",
       "      <td>264</td>\n",
       "      <td>124</td>\n",
       "      <td>47</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239805</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>SS</td>\n",
       "      <td>3524760</td>\n",
       "      <td>536247</td>\n",
       "      <td>15.2</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239806</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>439477</td>\n",
       "      <td>76914</td>\n",
       "      <td>17.5</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239807</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>6258845</td>\n",
       "      <td>755950</td>\n",
       "      <td>12.1</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239808</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>5611163</td>\n",
       "      <td>676060</td>\n",
       "      <td>12</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239809</th>\n",
       "      <td>2018-19</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>706658</td>\n",
       "      <td>99129</td>\n",
       "      <td>14</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>41964 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       AcademicYear AggregateLevel CountyCode DistrictCode SchoolCode  \\\n",
       "3950        2018-19              D         01        10017    0000000   \n",
       "3951        2018-19              D         01        10017    0000000   \n",
       "3952        2018-19              D         01        10017    0000000   \n",
       "3953        2018-19              D         01        10017    0000000   \n",
       "3954        2018-19              D         01        10017    0000000   \n",
       "...             ...            ...        ...          ...        ...   \n",
       "239805      2018-19              T         00          NaN        NaN   \n",
       "239806      2018-19              T         00          NaN        NaN   \n",
       "239807      2018-19              T         00          NaN        NaN   \n",
       "239808      2018-19              T         00          NaN        NaN   \n",
       "239809      2018-19              T         00          NaN        NaN   \n",
       "\n",
       "       CountyName                        DistrictName       SchoolName  \\\n",
       "3950      Alameda  Alameda County Office of Education  District Office   \n",
       "3951      Alameda  Alameda County Office of Education  District Office   \n",
       "3952      Alameda  Alameda County Office of Education  District Office   \n",
       "3953      Alameda  Alameda County Office of Education  District Office   \n",
       "3954      Alameda  Alameda County Office of Education  District Office   \n",
       "...           ...                                 ...              ...   \n",
       "239805      State                                 NaN              NaN   \n",
       "239806      State                                 NaN              NaN   \n",
       "239807      State                                 NaN              NaN   \n",
       "239808      State                                 NaN              NaN   \n",
       "239809      State                                 NaN              NaN   \n",
       "\n",
       "       CharterYN ReportingCategory ChronicAbsenteeismEligibleCumula  \\\n",
       "3950         All                GF                             1978   \n",
       "3951          No                GF                              166   \n",
       "3952         Yes                GF                             1818   \n",
       "3953         All                GM                             1980   \n",
       "3954          No                GM                              264   \n",
       "...          ...               ...                              ...   \n",
       "239805        No                SS                          3524760   \n",
       "239806       Yes                SS                           439477   \n",
       "239807       All                TA                          6258845   \n",
       "239808        No                TA                          5611163   \n",
       "239809       Yes                TA                           706658   \n",
       "\n",
       "       ChronicAbsenteeismCount ChronicAbsenteeismRate  is_wcc_entity DASS  \n",
       "3950                       368                   18.6          False   NV  \n",
       "3951                       110                   66.3          False   NV  \n",
       "3952                       263                   14.5          False   NV  \n",
       "3953                       371                   18.7          False   NV  \n",
       "3954                       124                     47          False   NV  \n",
       "...                        ...                    ...            ...  ...  \n",
       "239805                  536247                   15.2          False   NV  \n",
       "239806                   76914                   17.5          False   NV  \n",
       "239807                  755950                   12.1          False   NV  \n",
       "239808                  676060                     12          False   NV  \n",
       "239809                   99129                     14          False   NV  \n",
       "\n",
       "[41964 rows x 15 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Existing conditions for \"Aggregate Level\" and \"is_wcc_entity\"\n",
    "condition1 = chronicabsenteeism19_df['AggregateLevel'].isin(['T', 'D'])\n",
    "condition2 = chronicabsenteeism19_df['is_wcc_entity']\n",
    "\n",
    "# New conditions to exclude rows with \"*\" in the chronic absenteeism columns\n",
    "condition3 = ~chronicabsenteeism19_df['ChronicAbsenteeismEligibleCumula'].isnull()\n",
    "condition4 = ~chronicabsenteeism19_df['ChronicAbsenteeismCount'].isnull()\n",
    "condition5 = ~chronicabsenteeism19_df['ChronicAbsenteeismRate'].isnull()\n",
    "\n",
    "# Combine all conditions using \"AND\" (&) for the chronic absenteeism conditions and \"OR\" (|) for the initial conditions\n",
    "combined_condition = (condition1 | condition2) & (condition3 & condition4 & condition5)\n",
    "\n",
    "# Filter dataframe1 using the combined condition\n",
    "final_filtered_dataframe19 = chronicabsenteeism19_df[combined_condition]\n",
    "\n",
    "final_filtered_dataframe19"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_order = [\n",
    "    'AcademicYear',\t\n",
    "    'AggregateLevel',\n",
    "    'CountyCode',\n",
    "    'DistrictCode',\t\n",
    "    'SchoolCode',\t\n",
    "    'CountyName',\t\n",
    "    'DistrictName',\t\n",
    "    'SchoolName',\n",
    "    'CharterYN',\n",
    "    'DASS',\t\n",
    "    'ReportingCategory',\t\n",
    "    'ChronicAbsenteeismEligibleCumula',\t\n",
    "    'ChronicAbsenteeismCount',\t\n",
    "    'ChronicAbsenteeismRate',\t\n",
    "    'is_wcc_entity'\t\n",
    "]\n",
    "\n",
    "# Reorder and drop columns\n",
    "final_filtered_dataframe19 = final_filtered_dataframe19[new_order]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2018"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>CharterYN</th>\n",
       "      <th>ReportingCategory</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumula</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>113231</td>\n",
       "      <td>11448</td>\n",
       "      <td>10.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>GF</td>\n",
       "      <td>102461</td>\n",
       "      <td>10292</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>GF</td>\n",
       "      <td>11056</td>\n",
       "      <td>1246</td>\n",
       "      <td>11.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>119966</td>\n",
       "      <td>12531</td>\n",
       "      <td>10.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>GM</td>\n",
       "      <td>109010</td>\n",
       "      <td>11344</td>\n",
       "      <td>10.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240392</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>SS</td>\n",
       "      <td>3610723</td>\n",
       "      <td>499336</td>\n",
       "      <td>13.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240393</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>438944</td>\n",
       "      <td>73929</td>\n",
       "      <td>16.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240394</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>6315131</td>\n",
       "      <td>702531</td>\n",
       "      <td>11.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240395</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>5688600</td>\n",
       "      <td>629719</td>\n",
       "      <td>11.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240396</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>690446</td>\n",
       "      <td>92822</td>\n",
       "      <td>13.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>240397 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       AcademicYear AggregateLevel CountyCode DistrictCode SchoolCode  \\\n",
       "0           2017-18              C         01          NaN        NaN   \n",
       "1           2017-18              C         01          NaN        NaN   \n",
       "2           2017-18              C         01          NaN        NaN   \n",
       "3           2017-18              C         01          NaN        NaN   \n",
       "4           2017-18              C         01          NaN        NaN   \n",
       "...             ...            ...        ...          ...        ...   \n",
       "240392      2017-18              T         00          NaN        NaN   \n",
       "240393      2017-18              T         00          NaN        NaN   \n",
       "240394      2017-18              T         00          NaN        NaN   \n",
       "240395      2017-18              T         00          NaN        NaN   \n",
       "240396      2017-18              T         00          NaN        NaN   \n",
       "\n",
       "       CountyName DistrictName SchoolName CharterYN ReportingCategory  \\\n",
       "0         Alameda          NaN        NaN       All                GF   \n",
       "1         Alameda          NaN        NaN        No                GF   \n",
       "2         Alameda          NaN        NaN       Yes                GF   \n",
       "3         Alameda          NaN        NaN       All                GM   \n",
       "4         Alameda          NaN        NaN        No                GM   \n",
       "...           ...          ...        ...       ...               ...   \n",
       "240392      State          NaN        NaN        No                SS   \n",
       "240393      State          NaN        NaN       Yes                SS   \n",
       "240394      State          NaN        NaN       All                TA   \n",
       "240395      State          NaN        NaN        No                TA   \n",
       "240396      State          NaN        NaN       Yes                TA   \n",
       "\n",
       "       ChronicAbsenteeismEligibleCumula ChronicAbsenteeismCount  \\\n",
       "0                                113231                   11448   \n",
       "1                                102461                   10292   \n",
       "2                                 11056                    1246   \n",
       "3                                119966                   12531   \n",
       "4                                109010                   11344   \n",
       "...                                 ...                     ...   \n",
       "240392                          3610723                  499336   \n",
       "240393                           438944                   73929   \n",
       "240394                          6315131                  702531   \n",
       "240395                          5688600                  629719   \n",
       "240396                           690446                   92822   \n",
       "\n",
       "       ChronicAbsenteeismRate  \n",
       "0                        10.1  \n",
       "1                          10  \n",
       "2                        11.3  \n",
       "3                        10.4  \n",
       "4                        10.4  \n",
       "...                       ...  \n",
       "240392                   13.8  \n",
       "240393                   16.8  \n",
       "240394                   11.1  \n",
       "240395                   11.1  \n",
       "240396                   13.4  \n",
       "\n",
       "[240397 rows x 13 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chronicabsenteeism18_df = pd.read_csv('chronic_absenteeism_csv_files/chronicabsenteeism18.csv', encoding='ISO-8859-1',dtype=str)\n",
    "chronicabsenteeism18_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>CharterYN</th>\n",
       "      <th>ReportingCategory</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumula</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "      <th>is_wcc_entity</th>\n",
       "      <th>DASS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>113231</td>\n",
       "      <td>11448</td>\n",
       "      <td>10.1</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>GF</td>\n",
       "      <td>102461</td>\n",
       "      <td>10292</td>\n",
       "      <td>10</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>GF</td>\n",
       "      <td>11056</td>\n",
       "      <td>1246</td>\n",
       "      <td>11.3</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>119966</td>\n",
       "      <td>12531</td>\n",
       "      <td>10.4</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>C</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>GM</td>\n",
       "      <td>109010</td>\n",
       "      <td>11344</td>\n",
       "      <td>10.4</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240392</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>SS</td>\n",
       "      <td>3610723</td>\n",
       "      <td>499336</td>\n",
       "      <td>13.8</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240393</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>438944</td>\n",
       "      <td>73929</td>\n",
       "      <td>16.8</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240394</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>6315131</td>\n",
       "      <td>702531</td>\n",
       "      <td>11.1</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240395</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>5688600</td>\n",
       "      <td>629719</td>\n",
       "      <td>11.1</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240396</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>690446</td>\n",
       "      <td>92822</td>\n",
       "      <td>13.4</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>240397 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       AcademicYear AggregateLevel CountyCode DistrictCode SchoolCode  \\\n",
       "0           2017-18              C         01          NaN        NaN   \n",
       "1           2017-18              C         01          NaN        NaN   \n",
       "2           2017-18              C         01          NaN        NaN   \n",
       "3           2017-18              C         01          NaN        NaN   \n",
       "4           2017-18              C         01          NaN        NaN   \n",
       "...             ...            ...        ...          ...        ...   \n",
       "240392      2017-18              T         00          NaN        NaN   \n",
       "240393      2017-18              T         00          NaN        NaN   \n",
       "240394      2017-18              T         00          NaN        NaN   \n",
       "240395      2017-18              T         00          NaN        NaN   \n",
       "240396      2017-18              T         00          NaN        NaN   \n",
       "\n",
       "       CountyName DistrictName SchoolName CharterYN ReportingCategory  \\\n",
       "0         Alameda          NaN        NaN       All                GF   \n",
       "1         Alameda          NaN        NaN        No                GF   \n",
       "2         Alameda          NaN        NaN       Yes                GF   \n",
       "3         Alameda          NaN        NaN       All                GM   \n",
       "4         Alameda          NaN        NaN        No                GM   \n",
       "...           ...          ...        ...       ...               ...   \n",
       "240392      State          NaN        NaN        No                SS   \n",
       "240393      State          NaN        NaN       Yes                SS   \n",
       "240394      State          NaN        NaN       All                TA   \n",
       "240395      State          NaN        NaN        No                TA   \n",
       "240396      State          NaN        NaN       Yes                TA   \n",
       "\n",
       "       ChronicAbsenteeismEligibleCumula ChronicAbsenteeismCount  \\\n",
       "0                                113231                   11448   \n",
       "1                                102461                   10292   \n",
       "2                                 11056                    1246   \n",
       "3                                119966                   12531   \n",
       "4                                109010                   11344   \n",
       "...                                 ...                     ...   \n",
       "240392                          3610723                  499336   \n",
       "240393                           438944                   73929   \n",
       "240394                          6315131                  702531   \n",
       "240395                          5688600                  629719   \n",
       "240396                           690446                   92822   \n",
       "\n",
       "       ChronicAbsenteeismRate  is_wcc_entity DASS  \n",
       "0                        10.1          False   NV  \n",
       "1                          10          False   NV  \n",
       "2                        11.3          False   NV  \n",
       "3                        10.4          False   NV  \n",
       "4                        10.4          False   NV  \n",
       "...                       ...            ...  ...  \n",
       "240392                   13.8          False   NV  \n",
       "240393                   16.8          False   NV  \n",
       "240394                   11.1          False   NV  \n",
       "240395                   11.1          False   NV  \n",
       "240396                   13.4          False   NV  \n",
       "\n",
       "[240397 rows x 15 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 1: Create a set of unique school codes from dataframe2 for efficient lookup\n",
    "wcc_schools_list = set(wcc_schools_df['School Code'])\n",
    "\n",
    "# Step 2: Use the .isin() method to check if each school code in dataframe1 is in the set of schools from dataframe2\n",
    "chronicabsenteeism18_df['is_wcc_entity'] = chronicabsenteeism18_df['SchoolCode'].isin(wcc_schools_list)\n",
    "chronicabsenteeism18_df['DASS'] = 'NV'\n",
    "chronicabsenteeism18_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>CharterYN</th>\n",
       "      <th>ReportingCategory</th>\n",
       "      <th>ChronicAbsenteeismEligibleCumula</th>\n",
       "      <th>ChronicAbsenteeismCount</th>\n",
       "      <th>ChronicAbsenteeismRate</th>\n",
       "      <th>is_wcc_entity</th>\n",
       "      <th>DASS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4036</th>\n",
       "      <td>2017-18</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>1757</td>\n",
       "      <td>374</td>\n",
       "      <td>21.3</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4037</th>\n",
       "      <td>2017-18</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>No</td>\n",
       "      <td>GF</td>\n",
       "      <td>176</td>\n",
       "      <td>124</td>\n",
       "      <td>70.5</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4038</th>\n",
       "      <td>2017-18</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>Yes</td>\n",
       "      <td>GF</td>\n",
       "      <td>1587</td>\n",
       "      <td>253</td>\n",
       "      <td>15.9</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4039</th>\n",
       "      <td>2017-18</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>1786</td>\n",
       "      <td>353</td>\n",
       "      <td>19.8</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4040</th>\n",
       "      <td>2017-18</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>No</td>\n",
       "      <td>GM</td>\n",
       "      <td>292</td>\n",
       "      <td>126</td>\n",
       "      <td>43.2</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240392</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>SS</td>\n",
       "      <td>3610723</td>\n",
       "      <td>499336</td>\n",
       "      <td>13.8</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240393</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>SS</td>\n",
       "      <td>438944</td>\n",
       "      <td>73929</td>\n",
       "      <td>16.8</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240394</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>TA</td>\n",
       "      <td>6315131</td>\n",
       "      <td>702531</td>\n",
       "      <td>11.1</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240395</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>TA</td>\n",
       "      <td>5688600</td>\n",
       "      <td>629719</td>\n",
       "      <td>11.1</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240396</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>TA</td>\n",
       "      <td>690446</td>\n",
       "      <td>92822</td>\n",
       "      <td>13.4</td>\n",
       "      <td>False</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>42235 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       AcademicYear AggregateLevel CountyCode DistrictCode SchoolCode  \\\n",
       "4036        2017-18              D         01        10017    0000000   \n",
       "4037        2017-18              D         01        10017    0000000   \n",
       "4038        2017-18              D         01        10017    0000000   \n",
       "4039        2017-18              D         01        10017    0000000   \n",
       "4040        2017-18              D         01        10017    0000000   \n",
       "...             ...            ...        ...          ...        ...   \n",
       "240392      2017-18              T         00          NaN        NaN   \n",
       "240393      2017-18              T         00          NaN        NaN   \n",
       "240394      2017-18              T         00          NaN        NaN   \n",
       "240395      2017-18              T         00          NaN        NaN   \n",
       "240396      2017-18              T         00          NaN        NaN   \n",
       "\n",
       "       CountyName                        DistrictName       SchoolName  \\\n",
       "4036      Alameda  Alameda County Office of Education  District Office   \n",
       "4037      Alameda  Alameda County Office of Education  District Office   \n",
       "4038      Alameda  Alameda County Office of Education  District Office   \n",
       "4039      Alameda  Alameda County Office of Education  District Office   \n",
       "4040      Alameda  Alameda County Office of Education  District Office   \n",
       "...           ...                                 ...              ...   \n",
       "240392      State                                 NaN              NaN   \n",
       "240393      State                                 NaN              NaN   \n",
       "240394      State                                 NaN              NaN   \n",
       "240395      State                                 NaN              NaN   \n",
       "240396      State                                 NaN              NaN   \n",
       "\n",
       "       CharterYN ReportingCategory ChronicAbsenteeismEligibleCumula  \\\n",
       "4036         All                GF                             1757   \n",
       "4037          No                GF                              176   \n",
       "4038         Yes                GF                             1587   \n",
       "4039         All                GM                             1786   \n",
       "4040          No                GM                              292   \n",
       "...          ...               ...                              ...   \n",
       "240392        No                SS                          3610723   \n",
       "240393       Yes                SS                           438944   \n",
       "240394       All                TA                          6315131   \n",
       "240395        No                TA                          5688600   \n",
       "240396       Yes                TA                           690446   \n",
       "\n",
       "       ChronicAbsenteeismCount ChronicAbsenteeismRate  is_wcc_entity DASS  \n",
       "4036                       374                   21.3          False   NV  \n",
       "4037                       124                   70.5          False   NV  \n",
       "4038                       253                   15.9          False   NV  \n",
       "4039                       353                   19.8          False   NV  \n",
       "4040                       126                   43.2          False   NV  \n",
       "...                        ...                    ...            ...  ...  \n",
       "240392                  499336                   13.8          False   NV  \n",
       "240393                   73929                   16.8          False   NV  \n",
       "240394                  702531                   11.1          False   NV  \n",
       "240395                  629719                   11.1          False   NV  \n",
       "240396                   92822                   13.4          False   NV  \n",
       "\n",
       "[42235 rows x 15 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Existing conditions for \"Aggregate Level\" and \"is_wcc_entity\"\n",
    "condition1 = chronicabsenteeism18_df['AggregateLevel'].isin(['T', 'D'])\n",
    "condition2 = chronicabsenteeism18_df['is_wcc_entity']\n",
    "\n",
    "# New conditions to exclude rows with \"*\" in the chronic absenteeism columns\n",
    "condition3 = ~chronicabsenteeism18_df['ChronicAbsenteeismEligibleCumula'].isnull()\n",
    "condition4 = ~chronicabsenteeism18_df['ChronicAbsenteeismCount'].isnull()\n",
    "condition5 = ~chronicabsenteeism18_df['ChronicAbsenteeismRate'].isnull()\n",
    "\n",
    "# Combine all conditions using \"AND\" (&) for the chronic absenteeism conditions and \"OR\" (|) for the initial conditions\n",
    "combined_condition = (condition1 | condition2) & (condition3 & condition4 & condition5)\n",
    "\n",
    "# Filter dataframe1 using the combined condition\n",
    "final_filtered_dataframe18 = chronicabsenteeism18_df[combined_condition]\n",
    "\n",
    "final_filtered_dataframe18"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_order = [\n",
    "    'AcademicYear',\t\n",
    "    'AggregateLevel',\n",
    "    'CountyCode',\n",
    "    'DistrictCode',\t\n",
    "    'SchoolCode',\t\n",
    "    'CountyName',\t\n",
    "    'DistrictName',\t\n",
    "    'SchoolName',\n",
    "    'CharterYN',\n",
    "    'DASS',\t\n",
    "    'ReportingCategory',\t\n",
    "    'ChronicAbsenteeismEligibleCumula',\t\n",
    "    'ChronicAbsenteeismCount',\t\n",
    "    'ChronicAbsenteeismRate',\t\n",
    "    'is_wcc_entity'\t\n",
    "]\n",
    "\n",
    "# Reorder and drop columns\n",
    "final_filtered_dataframe18 = final_filtered_dataframe18[new_order]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_filtered_dataframe23.columns = ['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",
    "                               'dass',\n",
    "                               'reporting_category', \n",
    "                               'enrollment_count', \n",
    "                               'absenteeism_count', \n",
    "                               'absenteeism_rate',\n",
    "                               'is_wcc_entity']\n",
    "final_filtered_dataframe22.columns = ['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",
    "                               'dass',\n",
    "                               'reporting_category', \n",
    "                               'enrollment_count', \n",
    "                               'absenteeism_count', \n",
    "                               'absenteeism_rate',\n",
    "                               'is_wcc_entity']\n",
    "final_filtered_dataframe21.columns = ['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",
    "                               'dass',\n",
    "                               'reporting_category', \n",
    "                               'enrollment_count', \n",
    "                               'absenteeism_count', \n",
    "                               'absenteeism_rate',\n",
    "                               'is_wcc_entity']\n",
    "final_filtered_dataframe19.columns = ['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",
    "                               'dass',\n",
    "                               'reporting_category', \n",
    "                               'enrollment_count', \n",
    "                               'absenteeism_count', \n",
    "                               'absenteeism_rate',\n",
    "                               'is_wcc_entity']\n",
    "final_filtered_dataframe18.columns = ['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",
    "                               'dass',\n",
    "                               'reporting_category', \n",
    "                               'enrollment_count', \n",
    "                               'absenteeism_count', \n",
    "                               'absenteeism_rate',\n",
    "                               'is_wcc_entity']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>dass</th>\n",
       "      <th>reporting_category</th>\n",
       "      <th>enrollment_count</th>\n",
       "      <th>absenteeism_count</th>\n",
       "      <th>absenteeism_rate</th>\n",
       "      <th>is_wcc_entity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GF</td>\n",
       "      <td>2893518</td>\n",
       "      <td>719562</td>\n",
       "      <td>24.9</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GM</td>\n",
       "      <td>3059625</td>\n",
       "      <td>764487</td>\n",
       "      <td>25</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR13</td>\n",
       "      <td>1266699</td>\n",
       "      <td>309500</td>\n",
       "      <td>24.4</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR46</td>\n",
       "      <td>1318062</td>\n",
       "      <td>270008</td>\n",
       "      <td>20.5</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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>All</td>\n",
       "      <td>All</td>\n",
       "      <td>GR78</td>\n",
       "      <td>888084</td>\n",
       "      <td>202692</td>\n",
       "      <td>22.8</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240392</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>NV</td>\n",
       "      <td>SS</td>\n",
       "      <td>3610723</td>\n",
       "      <td>499336</td>\n",
       "      <td>13.8</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240393</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NV</td>\n",
       "      <td>SS</td>\n",
       "      <td>438944</td>\n",
       "      <td>73929</td>\n",
       "      <td>16.8</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240394</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>All</td>\n",
       "      <td>NV</td>\n",
       "      <td>TA</td>\n",
       "      <td>6315131</td>\n",
       "      <td>702531</td>\n",
       "      <td>11.1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240395</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>NV</td>\n",
       "      <td>TA</td>\n",
       "      <td>5688600</td>\n",
       "      <td>629719</td>\n",
       "      <td>11.1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240396</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>T</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NV</td>\n",
       "      <td>TA</td>\n",
       "      <td>690446</td>\n",
       "      <td>92822</td>\n",
       "      <td>13.4</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>208775 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       academic_year aggregate_level county_code district_code school_code  \\\n",
       "0            2022-23               T          00           NaN         NaN   \n",
       "1            2022-23               T          00           NaN         NaN   \n",
       "2            2022-23               T          00           NaN         NaN   \n",
       "3            2022-23               T          00           NaN         NaN   \n",
       "4            2022-23               T          00           NaN         NaN   \n",
       "...              ...             ...         ...           ...         ...   \n",
       "240392       2017-18               T          00           NaN         NaN   \n",
       "240393       2017-18               T          00           NaN         NaN   \n",
       "240394       2017-18               T          00           NaN         NaN   \n",
       "240395       2017-18               T          00           NaN         NaN   \n",
       "240396       2017-18               T          00           NaN         NaN   \n",
       "\n",
       "       county_name district_name school_name charter_yn dass  \\\n",
       "0            State           NaN         NaN        All  All   \n",
       "1            State           NaN         NaN        All  All   \n",
       "2            State           NaN         NaN        All  All   \n",
       "3            State           NaN         NaN        All  All   \n",
       "4            State           NaN         NaN        All  All   \n",
       "...            ...           ...         ...        ...  ...   \n",
       "240392       State           NaN         NaN         No   NV   \n",
       "240393       State           NaN         NaN        Yes   NV   \n",
       "240394       State           NaN         NaN        All   NV   \n",
       "240395       State           NaN         NaN         No   NV   \n",
       "240396       State           NaN         NaN        Yes   NV   \n",
       "\n",
       "       reporting_category enrollment_count absenteeism_count absenteeism_rate  \\\n",
       "0                      GF          2893518            719562             24.9   \n",
       "1                      GM          3059625            764487               25   \n",
       "2                    GR13          1266699            309500             24.4   \n",
       "3                    GR46          1318062            270008             20.5   \n",
       "4                    GR78           888084            202692             22.8   \n",
       "...                   ...              ...               ...              ...   \n",
       "240392                 SS          3610723            499336             13.8   \n",
       "240393                 SS           438944             73929             16.8   \n",
       "240394                 TA          6315131            702531             11.1   \n",
       "240395                 TA          5688600            629719             11.1   \n",
       "240396                 TA           690446             92822             13.4   \n",
       "\n",
       "        is_wcc_entity  \n",
       "0               False  \n",
       "1               False  \n",
       "2               False  \n",
       "3               False  \n",
       "4               False  \n",
       "...               ...  \n",
       "240392          False  \n",
       "240393          False  \n",
       "240394          False  \n",
       "240395          False  \n",
       "240396          False  \n",
       "\n",
       "[208775 rows x 15 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_chronicabsenteeism_df = pd.concat([final_filtered_dataframe23, \n",
    "                          final_filtered_dataframe22, \n",
    "                          final_filtered_dataframe21, \n",
    "                          final_filtered_dataframe19, \n",
    "                          final_filtered_dataframe18])\n",
    "final_chronicabsenteeism_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_chronicabsenteeism_df.to_csv('final_long_datasets_domain/final_chronic_absenteeism_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
}