create_merge_enrollment.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>academic_year</th>\n",
       "      <th>aggregate_level</th>\n",
       "      <th>county_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>school_code</th>\n",
       "      <th>reporting_category</th>\n",
       "      <th>cumulative_enrollment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>GF</td>\n",
       "      <td>2233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>GM</td>\n",
       "      <td>2419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>RA</td>\n",
       "      <td>690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>RB</td>\n",
       "      <td>859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>RD</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84884</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6004782</td>\n",
       "      <td>TA</td>\n",
       "      <td>482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84885</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6112015</td>\n",
       "      <td>TA</td>\n",
       "      <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84886</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6099717</td>\n",
       "      <td>TA</td>\n",
       "      <td>451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84887</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6004824</td>\n",
       "      <td>TA</td>\n",
       "      <td>453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84888</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6004873</td>\n",
       "      <td>TA</td>\n",
       "      <td>545</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84889 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      academic_year aggregate_level county_code district_code school_code  \\\n",
       "0           2022-23               D          01         10017     0000000   \n",
       "1           2022-23               D          01         10017     0000000   \n",
       "2           2022-23               D          01         10017     0000000   \n",
       "3           2022-23               D          01         10017     0000000   \n",
       "4           2022-23               D          01         10017     0000000   \n",
       "...             ...             ...         ...           ...         ...   \n",
       "84884       2017-18               S          07         61796     6004782   \n",
       "84885       2017-18               S          07         61796     6112015   \n",
       "84886       2017-18               S          07         61796     6099717   \n",
       "84887       2017-18               S          07         61796     6004824   \n",
       "84888       2017-18               S          07         61796     6004873   \n",
       "\n",
       "      reporting_category cumulative_enrollment  \n",
       "0                     GF                  2233  \n",
       "1                     GM                  2419  \n",
       "2                     RA                   690  \n",
       "3                     RB                   859  \n",
       "4                     RD                    99  \n",
       "...                  ...                   ...  \n",
       "84884                 TA                   482  \n",
       "84885                 TA                   500  \n",
       "84886                 TA                   451  \n",
       "84887                 TA                   453  \n",
       "84888                 TA                   545  \n",
       "\n",
       "[84889 rows x 7 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cum_enrollment_df = pd.read_csv('final_long_datasets_domain/final_cum_enrollment_df.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "cum_enrollment_df"
   ]
  },
  {
   "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>county_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>school_code</th>\n",
       "      <th>aggregate_level</th>\n",
       "      <th>reporting_category</th>\n",
       "      <th>EO</th>\n",
       "      <th>IFEP</th>\n",
       "      <th>EL</th>\n",
       "      <th>RFEP</th>\n",
       "      <th>TBD</th>\n",
       "      <th>TotalEnrollment</th>\n",
       "      <th>AR</th>\n",
       "      <th>LTEL</th>\n",
       "      <th>EL4+</th>\n",
       "      <th>EL03Y</th>\n",
       "      <th>EL45Y</th>\n",
       "      <th>EL6+Y</th>\n",
       "      <th>Total-EE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>00</td>\n",
       "      <td>00000</td>\n",
       "      <td>0000000</td>\n",
       "      <td>T</td>\n",
       "      <td>TA</td>\n",
       "      <td>3569666</td>\n",
       "      <td>261767</td>\n",
       "      <td>1271150</td>\n",
       "      <td>1104495</td>\n",
       "      <td>13335</td>\n",
       "      <td>6220413</td>\n",
       "      <td>144146</td>\n",
       "      <td>218135</td>\n",
       "      <td>250479</td>\n",
       "      <td>658390</td>\n",
       "      <td>234842</td>\n",
       "      <td>377918</td>\n",
       "      <td>2375645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>00</td>\n",
       "      <td>00000</td>\n",
       "      <td>0000000</td>\n",
       "      <td>T</td>\n",
       "      <td>GF</td>\n",
       "      <td>1728140</td>\n",
       "      <td>140174</td>\n",
       "      <td>580771</td>\n",
       "      <td>568942</td>\n",
       "      <td>6414</td>\n",
       "      <td>3024441</td>\n",
       "      <td>62609</td>\n",
       "      <td>87882</td>\n",
       "      <td>114543</td>\n",
       "      <td>315737</td>\n",
       "      <td>107805</td>\n",
       "      <td>157229</td>\n",
       "      <td>1149713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>00</td>\n",
       "      <td>00000</td>\n",
       "      <td>0000000</td>\n",
       "      <td>T</td>\n",
       "      <td>GM</td>\n",
       "      <td>1841526</td>\n",
       "      <td>121593</td>\n",
       "      <td>690379</td>\n",
       "      <td>535553</td>\n",
       "      <td>6921</td>\n",
       "      <td>3195972</td>\n",
       "      <td>81537</td>\n",
       "      <td>130253</td>\n",
       "      <td>135936</td>\n",
       "      <td>342653</td>\n",
       "      <td>127037</td>\n",
       "      <td>220689</td>\n",
       "      <td>1225932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>D</td>\n",
       "      <td>TA</td>\n",
       "      <td>1506</td>\n",
       "      <td>121</td>\n",
       "      <td>870</td>\n",
       "      <td>489</td>\n",
       "      <td>119</td>\n",
       "      <td>3105</td>\n",
       "      <td>136</td>\n",
       "      <td>159</td>\n",
       "      <td>143</td>\n",
       "      <td>432</td>\n",
       "      <td>188</td>\n",
       "      <td>250</td>\n",
       "      <td>1359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>D</td>\n",
       "      <td>GF</td>\n",
       "      <td>718</td>\n",
       "      <td>69</td>\n",
       "      <td>431</td>\n",
       "      <td>283</td>\n",
       "      <td>61</td>\n",
       "      <td>1562</td>\n",
       "      <td>64</td>\n",
       "      <td>77</td>\n",
       "      <td>75</td>\n",
       "      <td>215</td>\n",
       "      <td>98</td>\n",
       "      <td>118</td>\n",
       "      <td>714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21097</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>58</td>\n",
       "      <td>72751</td>\n",
       "      <td>0000000</td>\n",
       "      <td>D</td>\n",
       "      <td>GM</td>\n",
       "      <td>647</td>\n",
       "      <td>4</td>\n",
       "      <td>44</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>715</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>23</td>\n",
       "      <td>8</td>\n",
       "      <td>13</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21098</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>D</td>\n",
       "      <td>TA</td>\n",
       "      <td>885</td>\n",
       "      <td>28</td>\n",
       "      <td>36</td>\n",
       "      <td>146</td>\n",
       "      <td>1</td>\n",
       "      <td>1096</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21099</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>D</td>\n",
       "      <td>GF</td>\n",
       "      <td>395</td>\n",
       "      <td>18</td>\n",
       "      <td>15</td>\n",
       "      <td>71</td>\n",
       "      <td>0</td>\n",
       "      <td>499</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21100</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>D</td>\n",
       "      <td>GM</td>\n",
       "      <td>489</td>\n",
       "      <td>10</td>\n",
       "      <td>21</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "      <td>596</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21101</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>58</td>\n",
       "      <td>72769</td>\n",
       "      <td>0000000</td>\n",
       "      <td>D</td>\n",
       "      <td>GX</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21102 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      academic_year county_code district_code school_code aggregate_level  \\\n",
       "0           2017-18          00         00000     0000000               T   \n",
       "1           2017-18          00         00000     0000000               T   \n",
       "2           2017-18          00         00000     0000000               T   \n",
       "3           2017-18          01         10017     0000000               D   \n",
       "4           2017-18          01         10017     0000000               D   \n",
       "...             ...         ...           ...         ...             ...   \n",
       "21097       2022-23          58         72751     0000000               D   \n",
       "21098       2022-23          58         72769     0000000               D   \n",
       "21099       2022-23          58         72769     0000000               D   \n",
       "21100       2022-23          58         72769     0000000               D   \n",
       "21101       2022-23          58         72769     0000000               D   \n",
       "\n",
       "      reporting_category       EO    IFEP       EL     RFEP    TBD  \\\n",
       "0                     TA  3569666  261767  1271150  1104495  13335   \n",
       "1                     GF  1728140  140174   580771   568942   6414   \n",
       "2                     GM  1841526  121593   690379   535553   6921   \n",
       "3                     TA     1506     121      870      489    119   \n",
       "4                     GF      718      69      431      283     61   \n",
       "...                  ...      ...     ...      ...      ...    ...   \n",
       "21097                 GM      647       4       44       20      0   \n",
       "21098                 TA      885      28       36      146      1   \n",
       "21099                 GF      395      18       15       71      0   \n",
       "21100                 GM      489      10       21       75      1   \n",
       "21101                 GX        1       0        0        0      0   \n",
       "\n",
       "      TotalEnrollment      AR    LTEL    EL4+   EL03Y   EL45Y   EL6+Y Total-EE  \n",
       "0             6220413  144146  218135  250479  658390  234842  377918  2375645  \n",
       "1             3024441   62609   87882  114543  315737  107805  157229  1149713  \n",
       "2             3195972   81537  130253  135936  342653  127037  220689  1225932  \n",
       "3                3105     136     159     143     432     188     250     1359  \n",
       "4                1562      64      77      75     215      98     118      714  \n",
       "...               ...     ...     ...     ...     ...     ...     ...      ...  \n",
       "21097             715       3       6      12      23       8      13       64  \n",
       "21098            1096       0      24       8       4       0      32      182  \n",
       "21099             499       0       9       4       2       0      13       86  \n",
       "21100             596       0      15       4       2       0      19       96  \n",
       "21101               1       0       0       0       0       0       0        0  \n",
       "\n",
       "[21102 rows x 19 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "el_enrollment_df = pd.read_csv('final_long_datasets_domain/final_enrollment_el_df.csv', encoding='ISO-8859-1',dtype=str)\n",
    "pd.set_option('display.max_columns', None)\n",
    "el_enrollment_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>reporting_category</th>\n",
       "      <th>cumulative_enrollment</th>\n",
       "      <th>EO</th>\n",
       "      <th>IFEP</th>\n",
       "      <th>EL</th>\n",
       "      <th>RFEP</th>\n",
       "      <th>TBD</th>\n",
       "      <th>TotalEnrollment</th>\n",
       "      <th>AR</th>\n",
       "      <th>LTEL</th>\n",
       "      <th>EL4+</th>\n",
       "      <th>EL03Y</th>\n",
       "      <th>EL45Y</th>\n",
       "      <th>EL6+Y</th>\n",
       "      <th>Total-EE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>GF</td>\n",
       "      <td>2233</td>\n",
       "      <td>962</td>\n",
       "      <td>158</td>\n",
       "      <td>516</td>\n",
       "      <td>234</td>\n",
       "      <td>10</td>\n",
       "      <td>1893</td>\n",
       "      <td>74</td>\n",
       "      <td>99</td>\n",
       "      <td>75</td>\n",
       "      <td>268</td>\n",
       "      <td>105</td>\n",
       "      <td>143</td>\n",
       "      <td>750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>GM</td>\n",
       "      <td>2419</td>\n",
       "      <td>962</td>\n",
       "      <td>116</td>\n",
       "      <td>591</td>\n",
       "      <td>181</td>\n",
       "      <td>4</td>\n",
       "      <td>1857</td>\n",
       "      <td>75</td>\n",
       "      <td>113</td>\n",
       "      <td>110</td>\n",
       "      <td>293</td>\n",
       "      <td>110</td>\n",
       "      <td>188</td>\n",
       "      <td>772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>RA</td>\n",
       "      <td>690</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>RB</td>\n",
       "      <td>859</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-23</td>\n",
       "      <td>D</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>RD</td>\n",
       "      <td>99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <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>84884</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6004782</td>\n",
       "      <td>TA</td>\n",
       "      <td>482</td>\n",
       "      <td>296</td>\n",
       "      <td>29</td>\n",
       "      <td>104</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>448</td>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>75</td>\n",
       "      <td>16</td>\n",
       "      <td>13</td>\n",
       "      <td>123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84885</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6112015</td>\n",
       "      <td>TA</td>\n",
       "      <td>500</td>\n",
       "      <td>365</td>\n",
       "      <td>35</td>\n",
       "      <td>57</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>485</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>47</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84886</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6099717</td>\n",
       "      <td>TA</td>\n",
       "      <td>451</td>\n",
       "      <td>312</td>\n",
       "      <td>17</td>\n",
       "      <td>59</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>413</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>47</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84887</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6004824</td>\n",
       "      <td>TA</td>\n",
       "      <td>453</td>\n",
       "      <td>110</td>\n",
       "      <td>12</td>\n",
       "      <td>254</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>412</td>\n",
       "      <td>46</td>\n",
       "      <td>9</td>\n",
       "      <td>23</td>\n",
       "      <td>176</td>\n",
       "      <td>56</td>\n",
       "      <td>22</td>\n",
       "      <td>290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84888</th>\n",
       "      <td>2017-18</td>\n",
       "      <td>S</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6004873</td>\n",
       "      <td>TA</td>\n",
       "      <td>545</td>\n",
       "      <td>287</td>\n",
       "      <td>16</td>\n",
       "      <td>138</td>\n",
       "      <td>51</td>\n",
       "      <td>0</td>\n",
       "      <td>492</td>\n",
       "      <td>26</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>82</td>\n",
       "      <td>35</td>\n",
       "      <td>21</td>\n",
       "      <td>189</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84889 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      academic_year aggregate_level county_code district_code school_code  \\\n",
       "0           2022-23               D          01         10017     0000000   \n",
       "1           2022-23               D          01         10017     0000000   \n",
       "2           2022-23               D          01         10017     0000000   \n",
       "3           2022-23               D          01         10017     0000000   \n",
       "4           2022-23               D          01         10017     0000000   \n",
       "...             ...             ...         ...           ...         ...   \n",
       "84884       2017-18               S          07         61796     6004782   \n",
       "84885       2017-18               S          07         61796     6112015   \n",
       "84886       2017-18               S          07         61796     6099717   \n",
       "84887       2017-18               S          07         61796     6004824   \n",
       "84888       2017-18               S          07         61796     6004873   \n",
       "\n",
       "      reporting_category cumulative_enrollment   EO IFEP   EL RFEP  TBD  \\\n",
       "0                     GF                  2233  962  158  516  234   10   \n",
       "1                     GM                  2419  962  116  591  181    4   \n",
       "2                     RA                   690  NaN  NaN  NaN  NaN  NaN   \n",
       "3                     RB                   859  NaN  NaN  NaN  NaN  NaN   \n",
       "4                     RD                    99  NaN  NaN  NaN  NaN  NaN   \n",
       "...                  ...                   ...  ...  ...  ...  ...  ...   \n",
       "84884                 TA                   482  296   29  104   19    0   \n",
       "84885                 TA                   500  365   35   57   28    0   \n",
       "84886                 TA                   451  312   17   59   25    0   \n",
       "84887                 TA                   453  110   12  254   36    0   \n",
       "84888                 TA                   545  287   16  138   51    0   \n",
       "\n",
       "      TotalEnrollment   AR LTEL EL4+ EL03Y EL45Y EL6+Y Total-EE  \n",
       "0                1893   74   99   75   268   105   143      750  \n",
       "1                1857   75  113  110   293   110   188      772  \n",
       "2                 NaN  NaN  NaN  NaN   NaN   NaN   NaN      NaN  \n",
       "3                 NaN  NaN  NaN  NaN   NaN   NaN   NaN      NaN  \n",
       "4                 NaN  NaN  NaN  NaN   NaN   NaN   NaN      NaN  \n",
       "...               ...  ...  ...  ...   ...   ...   ...      ...  \n",
       "84884             448   16    4    9    75    16    13      123  \n",
       "84885             485    5    0    5    47     8     2       85  \n",
       "84886             413    7    0    5    47    11     1       84  \n",
       "84887             412   46    9   23   176    56    22      290  \n",
       "84888             492   26   10   20    82    35    21      189  \n",
       "\n",
       "[84889 rows x 20 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "complete_enrollment_df = pd.merge(cum_enrollment_df, el_enrollment_df, on=['academic_year', 'county_code', 'district_code', 'school_code', 'aggregate_level', 'reporting_category'], how='left')\n",
    "complete_enrollment_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the mapping from metric_id\n",
    "year_mapping = {\n",
    "    '2017-18': '2018',\n",
    "    '2018-19': '2019',\n",
    "    '2019-20': '2020',\n",
    "    '2020-21': '2021',\n",
    "    '2021-22': '2022',\n",
    "    '2022-23': '2023'\n",
    "}\n",
    "\n",
    "# Use .loc to ensure you're modifying the original DataFrame\n",
    "complete_enrollment_df.loc[:, 'academic_year'] = complete_enrollment_df['academic_year'].astype(str).replace(year_mapping)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_enrollment_df['aggregate_level'] = complete_enrollment_df['aggregate_level'].replace({'T': 'State', 'D': 'District', 'S': 'School'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>academic_year</th>\n",
       "      <th>aggregate_level</th>\n",
       "      <th>county_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>school_code</th>\n",
       "      <th>reporting_category</th>\n",
       "      <th>cumulative_enrollment</th>\n",
       "      <th>EO</th>\n",
       "      <th>IFEP</th>\n",
       "      <th>EL</th>\n",
       "      <th>RFEP</th>\n",
       "      <th>TBD</th>\n",
       "      <th>TotalEnrollment</th>\n",
       "      <th>AR</th>\n",
       "      <th>LTEL</th>\n",
       "      <th>EL4+</th>\n",
       "      <th>EL03Y</th>\n",
       "      <th>EL45Y</th>\n",
       "      <th>EL6+Y</th>\n",
       "      <th>Total-EE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023</td>\n",
       "      <td>District</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>GF</td>\n",
       "      <td>2233</td>\n",
       "      <td>962</td>\n",
       "      <td>158</td>\n",
       "      <td>516</td>\n",
       "      <td>234</td>\n",
       "      <td>10</td>\n",
       "      <td>1893</td>\n",
       "      <td>74</td>\n",
       "      <td>99</td>\n",
       "      <td>75</td>\n",
       "      <td>268</td>\n",
       "      <td>105</td>\n",
       "      <td>143</td>\n",
       "      <td>750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023</td>\n",
       "      <td>District</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>GM</td>\n",
       "      <td>2419</td>\n",
       "      <td>962</td>\n",
       "      <td>116</td>\n",
       "      <td>591</td>\n",
       "      <td>181</td>\n",
       "      <td>4</td>\n",
       "      <td>1857</td>\n",
       "      <td>75</td>\n",
       "      <td>113</td>\n",
       "      <td>110</td>\n",
       "      <td>293</td>\n",
       "      <td>110</td>\n",
       "      <td>188</td>\n",
       "      <td>772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023</td>\n",
       "      <td>District</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>RA</td>\n",
       "      <td>690</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023</td>\n",
       "      <td>District</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>RB</td>\n",
       "      <td>859</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023</td>\n",
       "      <td>District</td>\n",
       "      <td>01</td>\n",
       "      <td>10017</td>\n",
       "      <td>0000000</td>\n",
       "      <td>RD</td>\n",
       "      <td>99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <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>84884</th>\n",
       "      <td>2018</td>\n",
       "      <td>School</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6004782</td>\n",
       "      <td>TA</td>\n",
       "      <td>482</td>\n",
       "      <td>296</td>\n",
       "      <td>29</td>\n",
       "      <td>104</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>448</td>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>75</td>\n",
       "      <td>16</td>\n",
       "      <td>13</td>\n",
       "      <td>123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84885</th>\n",
       "      <td>2018</td>\n",
       "      <td>School</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6112015</td>\n",
       "      <td>TA</td>\n",
       "      <td>500</td>\n",
       "      <td>365</td>\n",
       "      <td>35</td>\n",
       "      <td>57</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>485</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>47</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84886</th>\n",
       "      <td>2018</td>\n",
       "      <td>School</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6099717</td>\n",
       "      <td>TA</td>\n",
       "      <td>451</td>\n",
       "      <td>312</td>\n",
       "      <td>17</td>\n",
       "      <td>59</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>413</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>47</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84887</th>\n",
       "      <td>2018</td>\n",
       "      <td>School</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6004824</td>\n",
       "      <td>TA</td>\n",
       "      <td>453</td>\n",
       "      <td>110</td>\n",
       "      <td>12</td>\n",
       "      <td>254</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>412</td>\n",
       "      <td>46</td>\n",
       "      <td>9</td>\n",
       "      <td>23</td>\n",
       "      <td>176</td>\n",
       "      <td>56</td>\n",
       "      <td>22</td>\n",
       "      <td>290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84888</th>\n",
       "      <td>2018</td>\n",
       "      <td>School</td>\n",
       "      <td>07</td>\n",
       "      <td>61796</td>\n",
       "      <td>6004873</td>\n",
       "      <td>TA</td>\n",
       "      <td>545</td>\n",
       "      <td>287</td>\n",
       "      <td>16</td>\n",
       "      <td>138</td>\n",
       "      <td>51</td>\n",
       "      <td>0</td>\n",
       "      <td>492</td>\n",
       "      <td>26</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>82</td>\n",
       "      <td>35</td>\n",
       "      <td>21</td>\n",
       "      <td>189</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84889 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      academic_year aggregate_level county_code district_code school_code  \\\n",
       "0              2023        District          01         10017     0000000   \n",
       "1              2023        District          01         10017     0000000   \n",
       "2              2023        District          01         10017     0000000   \n",
       "3              2023        District          01         10017     0000000   \n",
       "4              2023        District          01         10017     0000000   \n",
       "...             ...             ...         ...           ...         ...   \n",
       "84884          2018          School          07         61796     6004782   \n",
       "84885          2018          School          07         61796     6112015   \n",
       "84886          2018          School          07         61796     6099717   \n",
       "84887          2018          School          07         61796     6004824   \n",
       "84888          2018          School          07         61796     6004873   \n",
       "\n",
       "      reporting_category cumulative_enrollment   EO IFEP   EL RFEP  TBD  \\\n",
       "0                     GF                  2233  962  158  516  234   10   \n",
       "1                     GM                  2419  962  116  591  181    4   \n",
       "2                     RA                   690  NaN  NaN  NaN  NaN  NaN   \n",
       "3                     RB                   859  NaN  NaN  NaN  NaN  NaN   \n",
       "4                     RD                    99  NaN  NaN  NaN  NaN  NaN   \n",
       "...                  ...                   ...  ...  ...  ...  ...  ...   \n",
       "84884                 TA                   482  296   29  104   19    0   \n",
       "84885                 TA                   500  365   35   57   28    0   \n",
       "84886                 TA                   451  312   17   59   25    0   \n",
       "84887                 TA                   453  110   12  254   36    0   \n",
       "84888                 TA                   545  287   16  138   51    0   \n",
       "\n",
       "      TotalEnrollment   AR LTEL EL4+ EL03Y EL45Y EL6+Y Total-EE  \n",
       "0                1893   74   99   75   268   105   143      750  \n",
       "1                1857   75  113  110   293   110   188      772  \n",
       "2                 NaN  NaN  NaN  NaN   NaN   NaN   NaN      NaN  \n",
       "3                 NaN  NaN  NaN  NaN   NaN   NaN   NaN      NaN  \n",
       "4                 NaN  NaN  NaN  NaN   NaN   NaN   NaN      NaN  \n",
       "...               ...  ...  ...  ...   ...   ...   ...      ...  \n",
       "84884             448   16    4    9    75    16    13      123  \n",
       "84885             485    5    0    5    47     8     2       85  \n",
       "84886             413    7    0    5    47    11     1       84  \n",
       "84887             412   46    9   23   176    56    22      290  \n",
       "84888             492   26   10   20    82    35    21      189  \n",
       "\n",
       "[84889 rows x 20 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "complete_enrollment_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_enrollment_df.to_csv('tableau_ready_files/enrollment_fact.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
}