notebooks/exercicio-empirico-03.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <center> Exercício Empírico III </center>\n",
    "## <center> Econometria I - EAE0324 </center>\n",
    "### <center> Julho 2022 </center>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import statsmodels.api as sm\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import plotly.express as px\n",
    "\n",
    "sns.set_palette(sns.dark_palette(\"#2EA3C5\", reverse=True, n_colors=8))\n",
    "plt.style.use(['seaborn-paper'])\n",
    "pd.set_option('display.max_columns', 100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Este trabalho visa analisar a capacidade do aluno em responder a seguinte pergunta: Quais fatores (x) influenciam o desempenho das escolas no SAEB (y) em Matemática e Português?\n",
    "> \n",
    "> Vocês têm as seguintes informações no banco de dados:\n",
    "> \n",
    "> i) Desempenho das escolas no SAEB para os anos iniciais (1 a 4 ano) e finais (5 a 8 ano)  do ensino fundamental; desempenho para o SAEB do ensino médio. Variáveis nomeadas na base de dados como: inicial_mat; inicial_pt; final_mat; final_pt; medio_mat e medio_pt. Note que não são todas as escolas que possuem informações do SAEB e isso ocorre devido a alguma delas oferecerem apenas os anos finais, iniciais ou ensino médio. Podem excluir escolas sem NENHUMA informação das notas do SAEB.\n",
    "> \n",
    "> ii) Informações das escolas coletadas no Censo Escolar.\n",
    "> \n",
    "> ***ATIVIDADES***\n",
    "> \n",
    "> 1) Análise descritiva das variáveis quantitativas e qualitativas da base: média, desvio padrão, mínimo, máximo, proporção etc. Lembrar a diferença na hora de realizar a análise para variáveis qualitativas e quantitativas como comentei na monitoria.\n",
    "> \n",
    "> 2) Levantar o número de escolas que oferecem i) apenas os anos iniciais de ensino; ii) apenas os anos finais; iii) finais + iniciais; iv) apenas ensino médio e v) aquelas que oferecem tudo. Uma dica aqui é usar gráficos de barra, por exemplo. Pensem como informar esses dados de uma forma visualmente fácil de se entender.\n",
    "> \n",
    "> 3) Propor três modelos econométricos que relacionem as notas do SAEB com as variáveis explicativas. As variáveis que vocês usarão como explicativas é de livre escolha, desde que argumentem de forma clara qual a relação com as notas do SAEB. Importante: é preciso incluir tanto variáveis qualitativas quanto quantitativas no modelo. Não há um número mínimo de variáveis que vocês devem adicionar, mas tentem incluir aquelas que vocês achem mais relevantes. Que não seja apenas uma, evidentemente. Além disso, tentem mudar algumas das variáveis escolhidas de um modelo para o outro.\n",
    "> \n",
    "> Notem que temos seis potenciais variáveis explicadas (y).\n",
    "> \n",
    "> Vocês podem fazer transformações nas variáveis (exemplo, total de matrículas) desde que faça sentido.\n",
    "> \n",
    "> 4) Rodem os modelos propostos e expliquem os resultados, significância estatística e outras análises que vocês acham relevantes. Aqui deixo livre para vocês comentarem o que acham mais interessante. Usem todo o conhecimento das aulas e monitorias. Isso será levado em consideração na hora que eu corrigir.\n",
    "> \n",
    "> 5) Citem limitações dos modelos propostos.\n",
    "> \n",
    "> \n",
    "> \n",
    "> No arquivo em Excel consta o significado das variáveis do banco de dados. Vocês vão ver que as variáveis que começas com \"num_\" não constam no arquivo, mas elas se referem, por exemplo, ao número de professores no ensino fundamental (num_proffundamental). Se não ficar claro o significado dessas variáveis, me avisem.\n",
    ">\n",
    ">***A entrega é até o dia 06/07/22 às 23h59min***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "\n",
    "## Tratamento dos Dados"
   ]
  },
  {
   "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>co_municipio</th>\n",
       "      <th>codigo_escola</th>\n",
       "      <th>medio_mat</th>\n",
       "      <th>medio_pt</th>\n",
       "      <th>final_mat</th>\n",
       "      <th>final_pt</th>\n",
       "      <th>inicial_mat</th>\n",
       "      <th>inicial_pt</th>\n",
       "      <th>nu_ano_censo</th>\n",
       "      <th>co_regiao</th>\n",
       "      <th>co_uf</th>\n",
       "      <th>tp_dependencia</th>\n",
       "      <th>tp_localizacao</th>\n",
       "      <th>in_laboratorio_informatica</th>\n",
       "      <th>in_laboratorio_ciencias</th>\n",
       "      <th>in_quadra_esportes</th>\n",
       "      <th>in_cozinha</th>\n",
       "      <th>in_biblioteca</th>\n",
       "      <th>in_sala_leitura</th>\n",
       "      <th>nu_salas_utilizadas</th>\n",
       "      <th>nu_equip_tv</th>\n",
       "      <th>nu_equip_impressora</th>\n",
       "      <th>nu_computador</th>\n",
       "      <th>in_internet</th>\n",
       "      <th>nu_funcionarios</th>\n",
       "      <th>in_alimentacao</th>\n",
       "      <th>tp_atividade_complementar</th>\n",
       "      <th>num_profcreche</th>\n",
       "      <th>num_profpreescola</th>\n",
       "      <th>num_proffundamental</th>\n",
       "      <th>num_profmedio</th>\n",
       "      <th>num_matri_creche_total</th>\n",
       "      <th>num_turmas_creche_total</th>\n",
       "      <th>num_matri_preescola_total</th>\n",
       "      <th>num_turmas_preescola_total</th>\n",
       "      <th>num_matri_fund_total_geral</th>\n",
       "      <th>num_turmas_fund_total_geral</th>\n",
       "      <th>in_energia_inexistente</th>\n",
       "      <th>in_esgoto_inexistente</th>\n",
       "      <th>in_equip_tv</th>\n",
       "      <th>in_final_semana</th>\n",
       "      <th>num_turmas_fund_1</th>\n",
       "      <th>num_turmas_fund_2</th>\n",
       "      <th>num_turmas_fund_3</th>\n",
       "      <th>num_turmas_fund_4</th>\n",
       "      <th>num_turmas_fund_5</th>\n",
       "      <th>num_turmas_fund_6</th>\n",
       "      <th>num_turmas_fund_7</th>\n",
       "      <th>num_turmas_fund_8</th>\n",
       "      <th>num_matri_fund_1a4</th>\n",
       "      <th>num_matri_fund_5a8</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1100205</td>\n",
       "      <td>11000058</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>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>4. Privada</td>\n",
       "      <td>1. Urbana</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>39</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>40</td>\n",
       "      <td>1</td>\n",
       "      <td>150</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>153.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>60.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</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>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1100205</td>\n",
       "      <td>11000198</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>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>4. Privada</td>\n",
       "      <td>1. Urbana</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>33</td>\n",
       "      <td>18</td>\n",
       "      <td>9</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>196</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>16.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>149.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</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>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1100205</td>\n",
       "      <td>11000201</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>221.98</td>\n",
       "      <td>212.63</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>3. Municipal</td>\n",
       "      <td>1. Urbana</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>46</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</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>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1100205</td>\n",
       "      <td>11000244</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>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>4. Privada</td>\n",
       "      <td>1. Urbana</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>46</td>\n",
       "      <td>1</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</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>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1100205</td>\n",
       "      <td>11000252</td>\n",
       "      <td>331.5</td>\n",
       "      <td>333.01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>4. Privada</td>\n",
       "      <td>1. Urbana</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>96</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>111.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</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>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   co_municipio  codigo_escola  medio_mat  medio_pt  final_mat  final_pt  \\\n",
       "0       1100205       11000058        NaN       NaN        NaN       NaN   \n",
       "1       1100205       11000198        NaN       NaN        NaN       NaN   \n",
       "2       1100205       11000201        NaN       NaN        NaN       NaN   \n",
       "3       1100205       11000244        NaN       NaN        NaN       NaN   \n",
       "4       1100205       11000252      331.5    333.01        NaN       NaN   \n",
       "\n",
       "   inicial_mat  inicial_pt  nu_ano_censo  co_regiao  co_uf tp_dependencia  \\\n",
       "0          NaN         NaN          2017          1     11     4. Privada   \n",
       "1          NaN         NaN          2017          1     11     4. Privada   \n",
       "2       221.98      212.63          2017          1     11   3. Municipal   \n",
       "3          NaN         NaN          2017          1     11     4. Privada   \n",
       "4          NaN         NaN          2017          1     11     4. Privada   \n",
       "\n",
       "  tp_localizacao  in_laboratorio_informatica  in_laboratorio_ciencias  \\\n",
       "0      1. Urbana                           1                        1   \n",
       "1      1. Urbana                           1                        1   \n",
       "2      1. Urbana                           1                        0   \n",
       "3      1. Urbana                           0                        0   \n",
       "4      1. Urbana                           1                        1   \n",
       "\n",
       "   in_quadra_esportes  in_cozinha  in_biblioteca  in_sala_leitura  \\\n",
       "0                   1           1              1                0   \n",
       "1                   1           1              1                1   \n",
       "2                   0           1              1                1   \n",
       "3                   1           1              0                0   \n",
       "4                   1           0              1                1   \n",
       "\n",
       "   nu_salas_utilizadas  nu_equip_tv  nu_equip_impressora  nu_computador  \\\n",
       "0                   39            5                    5             40   \n",
       "1                   33           18                    9             80   \n",
       "2                   12            1                    5             17   \n",
       "3                   16            5                   15             46   \n",
       "4                   23            2                    2             49   \n",
       "\n",
       "   in_internet  nu_funcionarios  in_alimentacao  tp_atividade_complementar  \\\n",
       "0            1              150               0                          0   \n",
       "1            1              196               0                          1   \n",
       "2            1               46               1                          0   \n",
       "3            1               65               0                          0   \n",
       "4            1               96               0                          1   \n",
       "\n",
       "   num_profcreche  num_profpreescola  num_proffundamental  num_profmedio  \\\n",
       "0             NaN                3.0                240.0          153.0   \n",
       "1            16.0               16.0                149.0           78.0   \n",
       "2             NaN                NaN                 44.0            NaN   \n",
       "3             NaN                NaN                 78.0           52.0   \n",
       "4             9.0               18.0                111.0           61.0   \n",
       "\n",
       "   num_matri_creche_total  num_turmas_creche_total  num_matri_preescola_total  \\\n",
       "0                     0.0                      NaN                       60.0   \n",
       "1                    65.0                      5.0                      105.0   \n",
       "2                     0.0                      NaN                        0.0   \n",
       "3                     0.0                      NaN                        0.0   \n",
       "4                    41.0                      3.0                      102.0   \n",
       "\n",
       "   num_turmas_preescola_total  num_matri_fund_total_geral  \\\n",
       "0                         3.0                         0.0   \n",
       "1                         5.0                         0.0   \n",
       "2                         NaN                         0.0   \n",
       "3                         NaN                         0.0   \n",
       "4                         6.0                         0.0   \n",
       "\n",
       "   num_turmas_fund_total_geral  in_energia_inexistente  in_esgoto_inexistente  \\\n",
       "0                          0.0                       0                      0   \n",
       "1                          0.0                       0                      0   \n",
       "2                          0.0                       0                      0   \n",
       "3                          0.0                       0                      0   \n",
       "4                          0.0                       0                      0   \n",
       "\n",
       "   in_equip_tv  in_final_semana  num_turmas_fund_1  num_turmas_fund_2  \\\n",
       "0            1                0                NaN                NaN   \n",
       "1            1                0                NaN                NaN   \n",
       "2            1                0                NaN                NaN   \n",
       "3            1                0                NaN                NaN   \n",
       "4            1                0                NaN                NaN   \n",
       "\n",
       "   num_turmas_fund_3  num_turmas_fund_4  num_turmas_fund_5  num_turmas_fund_6  \\\n",
       "0                NaN                NaN                NaN                NaN   \n",
       "1                NaN                NaN                NaN                NaN   \n",
       "2                NaN                NaN                NaN                NaN   \n",
       "3                NaN                NaN                NaN                NaN   \n",
       "4                NaN                NaN                NaN                NaN   \n",
       "\n",
       "   num_turmas_fund_7  num_turmas_fund_8  num_matri_fund_1a4  \\\n",
       "0                NaN                NaN                 0.0   \n",
       "1                NaN                NaN                 0.0   \n",
       "2                NaN                NaN                 0.0   \n",
       "3                NaN                NaN                 0.0   \n",
       "4                NaN                NaN                 0.0   \n",
       "\n",
       "   num_matri_fund_5a8  \n",
       "0                 0.0  \n",
       "1                 0.0  \n",
       "2                 0.0  \n",
       "3                 0.0  \n",
       "4                 0.0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_stata('data/raw/exercicio-empirico-03.dta')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 74479 entries, 0 to 74478\n",
      "Data columns (total 51 columns):\n",
      " #   Column                       Non-Null Count  Dtype   \n",
      "---  ------                       --------------  -----   \n",
      " 0   co_municipio                 74479 non-null  int32   \n",
      " 1   codigo_escola                74479 non-null  int32   \n",
      " 2   medio_mat                    9381 non-null   float64 \n",
      " 3   medio_pt                     9381 non-null   float64 \n",
      " 4   final_mat                    24425 non-null  float64 \n",
      " 5   final_pt                     24425 non-null  float64 \n",
      " 6   inicial_mat                  39944 non-null  float64 \n",
      " 7   inicial_pt                   39944 non-null  float64 \n",
      " 8   nu_ano_censo                 74479 non-null  int16   \n",
      " 9   co_regiao                    74479 non-null  int8    \n",
      " 10  co_uf                        74479 non-null  int8    \n",
      " 11  tp_dependencia               74479 non-null  category\n",
      " 12  tp_localizacao               74479 non-null  category\n",
      " 13  in_laboratorio_informatica   74479 non-null  int8    \n",
      " 14  in_laboratorio_ciencias      74479 non-null  int8    \n",
      " 15  in_quadra_esportes           74479 non-null  int8    \n",
      " 16  in_cozinha                   74479 non-null  int8    \n",
      " 17  in_biblioteca                74479 non-null  int8    \n",
      " 18  in_sala_leitura              74479 non-null  int8    \n",
      " 19  nu_salas_utilizadas          74479 non-null  int16   \n",
      " 20  nu_equip_tv                  74479 non-null  int16   \n",
      " 21  nu_equip_impressora          74479 non-null  int16   \n",
      " 22  nu_computador                74479 non-null  int16   \n",
      " 23  in_internet                  74479 non-null  int8    \n",
      " 24  nu_funcionarios              74479 non-null  int16   \n",
      " 25  in_alimentacao               74479 non-null  int8    \n",
      " 26  tp_atividade_complementar    74479 non-null  int8    \n",
      " 27  num_profcreche               5966 non-null   float64 \n",
      " 28  num_profpreescola            24633 non-null  float64 \n",
      " 29  num_proffundamental          66788 non-null  float64 \n",
      " 30  num_profmedio                20278 non-null  float64 \n",
      " 31  num_matri_creche_total       74479 non-null  float32 \n",
      " 32  num_turmas_creche_total      6069 non-null   float64 \n",
      " 33  num_matri_preescola_total    74479 non-null  float32 \n",
      " 34  num_turmas_preescola_total   24633 non-null  float64 \n",
      " 35  num_matri_fund_total_geral   74479 non-null  float32 \n",
      " 36  num_turmas_fund_total_geral  74479 non-null  float32 \n",
      " 37  in_energia_inexistente       74479 non-null  int8    \n",
      " 38  in_esgoto_inexistente        74479 non-null  int8    \n",
      " 39  in_equip_tv                  74479 non-null  int8    \n",
      " 40  in_final_semana              74479 non-null  int8    \n",
      " 41  num_turmas_fund_1            21 non-null     float64 \n",
      " 42  num_turmas_fund_2            11 non-null     float64 \n",
      " 43  num_turmas_fund_3            13 non-null     float64 \n",
      " 44  num_turmas_fund_4            7 non-null      float64 \n",
      " 45  num_turmas_fund_5            46 non-null     float64 \n",
      " 46  num_turmas_fund_6            55 non-null     float64 \n",
      " 47  num_turmas_fund_7            121 non-null    float64 \n",
      " 48  num_turmas_fund_8            1555 non-null   float64 \n",
      " 49  num_matri_fund_1a4           74479 non-null  float32 \n",
      " 50  num_matri_fund_5a8           74479 non-null  float32 \n",
      "dtypes: category(2), float32(6), float64(20), int16(6), int32(2), int8(15)\n",
      "memory usage: 16.3 MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "co_municipio                       0\n",
       "codigo_escola                      0\n",
       "medio_mat                      65098\n",
       "medio_pt                       65098\n",
       "final_mat                      50054\n",
       "final_pt                       50054\n",
       "inicial_mat                    34535\n",
       "inicial_pt                     34535\n",
       "nu_ano_censo                       0\n",
       "co_regiao                          0\n",
       "co_uf                              0\n",
       "tp_dependencia                     0\n",
       "tp_localizacao                     0\n",
       "in_laboratorio_informatica         0\n",
       "in_laboratorio_ciencias            0\n",
       "in_quadra_esportes                 0\n",
       "in_cozinha                         0\n",
       "in_biblioteca                      0\n",
       "in_sala_leitura                    0\n",
       "nu_salas_utilizadas                0\n",
       "nu_equip_tv                        0\n",
       "nu_equip_impressora                0\n",
       "nu_computador                      0\n",
       "in_internet                        0\n",
       "nu_funcionarios                    0\n",
       "in_alimentacao                     0\n",
       "tp_atividade_complementar          0\n",
       "num_profcreche                 68513\n",
       "num_profpreescola              49846\n",
       "num_proffundamental             7691\n",
       "num_profmedio                  54201\n",
       "num_matri_creche_total             0\n",
       "num_turmas_creche_total        68410\n",
       "num_matri_preescola_total          0\n",
       "num_turmas_preescola_total     49846\n",
       "num_matri_fund_total_geral         0\n",
       "num_turmas_fund_total_geral        0\n",
       "in_energia_inexistente             0\n",
       "in_esgoto_inexistente              0\n",
       "in_equip_tv                        0\n",
       "in_final_semana                    0\n",
       "num_turmas_fund_1              74458\n",
       "num_turmas_fund_2              74468\n",
       "num_turmas_fund_3              74466\n",
       "num_turmas_fund_4              74472\n",
       "num_turmas_fund_5              74433\n",
       "num_turmas_fund_6              74424\n",
       "num_turmas_fund_7              74358\n",
       "num_turmas_fund_8              72924\n",
       "num_matri_fund_1a4                 0\n",
       "num_matri_fund_5a8                 0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 24425 entries, 0 to 24424\n",
      "Data columns (total 19 columns):\n",
      " #   Column                       Non-Null Count  Dtype   \n",
      "---  ------                       --------------  -----   \n",
      " 0   final_mat                    24425 non-null  float64 \n",
      " 1   final_pt                     24425 non-null  float64 \n",
      " 2   in_laboratorio_informatica   24425 non-null  int8    \n",
      " 3   in_laboratorio_ciencias      24425 non-null  int8    \n",
      " 4   in_quadra_esportes           24425 non-null  int8    \n",
      " 5   in_cozinha                   24425 non-null  int8    \n",
      " 6   in_biblioteca                24425 non-null  int8    \n",
      " 7   in_sala_leitura              24425 non-null  int8    \n",
      " 8   in_internet                  24425 non-null  int8    \n",
      " 9   in_alimentacao               24425 non-null  int8    \n",
      " 10  tp_atividade_complementar    24425 non-null  int8    \n",
      " 11  tp_localizacao               24425 non-null  category\n",
      " 12  num_proffundamental          24425 non-null  float64 \n",
      " 13  num_matri_fund_total_geral   24425 non-null  float32 \n",
      " 14  num_turmas_fund_total_geral  24425 non-null  float32 \n",
      " 15  nu_salas_utilizadas          24425 non-null  int16   \n",
      " 16  nu_funcionarios              24425 non-null  int16   \n",
      " 17  num_matri_fund_5a8           24425 non-null  float32 \n",
      " 18  rural                        24425 non-null  int64   \n",
      "dtypes: category(1), float32(3), float64(3), int16(2), int64(1), int8(9)\n",
      "memory usage: 1.4 MB\n"
     ]
    }
   ],
   "source": [
    "# Selecionando as observaçoes relevantes para o 1o modelo\n",
    "df1 = df.loc[df['final_mat'].notna() & df['final_pt'].notna()].reset_index()\n",
    "\n",
    "df1 = df1[['final_mat', 'final_pt', 'in_laboratorio_informatica', 'in_laboratorio_ciencias', 'in_quadra_esportes', \n",
    "          'in_cozinha', 'in_biblioteca', 'in_sala_leitura', 'in_internet', 'in_alimentacao', 'tp_atividade_complementar',\n",
    "          'tp_localizacao', 'num_proffundamental', 'num_matri_fund_total_geral', 'num_turmas_fund_total_geral', \n",
    "          'nu_salas_utilizadas', 'nu_funcionarios', 'num_matri_fund_5a8']]\n",
    "\n",
    "df1['rural'] = list(df1['tp_localizacao'].map({'1. Urbana': 0, '2. Rural': 1}))\n",
    "\n",
    "df1.to_csv(\"data/processed/exercicio-empirico-03-01.csv\")\n",
    "df1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 39944 entries, 0 to 39943\n",
      "Data columns (total 24 columns):\n",
      " #   Column                       Non-Null Count  Dtype   \n",
      "---  ------                       --------------  -----   \n",
      " 0   inicial_mat                  39944 non-null  float64 \n",
      " 1   inicial_pt                   39944 non-null  float64 \n",
      " 2   in_laboratorio_informatica   39944 non-null  int8    \n",
      " 3   in_laboratorio_ciencias      39944 non-null  int8    \n",
      " 4   in_quadra_esportes           39944 non-null  int8    \n",
      " 5   in_cozinha                   39944 non-null  int8    \n",
      " 6   in_biblioteca                39944 non-null  int8    \n",
      " 7   in_sala_leitura              39944 non-null  int8    \n",
      " 8   in_internet                  39944 non-null  int8    \n",
      " 9   in_alimentacao               39944 non-null  int8    \n",
      " 10  tp_atividade_complementar    39944 non-null  int8    \n",
      " 11  tp_localizacao               39944 non-null  category\n",
      " 12  num_proffundamental          39944 non-null  float64 \n",
      " 13  num_matri_fund_total_geral   39944 non-null  float32 \n",
      " 14  num_turmas_fund_total_geral  39944 non-null  float32 \n",
      " 15  nu_salas_utilizadas          39944 non-null  int16   \n",
      " 16  nu_funcionarios              39944 non-null  int16   \n",
      " 17  num_matri_fund_1a4           39944 non-null  float32 \n",
      " 18  matri/prof                   39944 non-null  float64 \n",
      " 19  matri/salas                  39944 non-null  float32 \n",
      " 20  com_escola_1                 39944 non-null  int8    \n",
      " 21  com_escola_2                 39944 non-null  int8    \n",
      " 22  rural                        39944 non-null  int64   \n",
      " 23  alimentacao_rural            39944 non-null  int64   \n",
      "dtypes: category(1), float32(4), float64(4), int16(2), int64(2), int8(11)\n",
      "memory usage: 3.0 MB\n"
     ]
    }
   ],
   "source": [
    "# Selecionando as observaçoes relevantes para o 2o e 3o modelo\n",
    "df2 = df.loc[df['inicial_pt'].notna() & df['inicial_mat'].notna()].reset_index()\n",
    "\n",
    "df2 = df2[['inicial_mat', 'inicial_pt', 'in_laboratorio_informatica', 'in_laboratorio_ciencias', 'in_quadra_esportes', \n",
    "           'in_cozinha', 'in_biblioteca', 'in_sala_leitura', 'in_internet', 'in_alimentacao', 'tp_atividade_complementar',\n",
    "           'tp_localizacao', 'num_proffundamental', 'num_matri_fund_total_geral', 'num_turmas_fund_total_geral', \n",
    "           'nu_salas_utilizadas', 'nu_funcionarios', 'num_matri_fund_1a4']]\n",
    "\n",
    "# Criando variaveis do 3o modelo\n",
    "df2['matri/prof'] = df2['num_matri_fund_1a4'] / df2['num_proffundamental']\n",
    "df2['matri/salas'] = df2['num_matri_fund_1a4'] / df2['nu_salas_utilizadas']\n",
    "df2['com_escola_1'] = df2['in_laboratorio_informatica'] + df2['in_laboratorio_ciencias'] + df2['in_quadra_esportes'] + df2['in_cozinha'] + df2['in_biblioteca'] + df2['in_sala_leitura'] + df2['in_internet'] + df2['in_alimentacao']\n",
    "df2['com_escola_2'] = df2['in_laboratorio_informatica'] + df2['in_laboratorio_ciencias'] + df2['in_quadra_esportes'] + df2['in_cozinha'] + df2['in_internet']\n",
    "\n",
    "\n",
    "df2['rural'] = list(df2['tp_localizacao'].map({'1. Urbana': 0, '2. Rural': 1}))\n",
    "df2['alimentacao_rural'] = df2['in_alimentacao'] * df2['rural']\n",
    "\n",
    "# Mostrando informaçoes básicas do Df após o tratamento\n",
    "df2.to_csv(\"data/processed/exercicio-empirico-03-02.csv\")\n",
    "df2.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "\n",
    "## Descrição dos Dados"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "O objetivo deste exercício é estimar quais fatores influenciam o desempenho das escolas no SAEB em Matemática e Português. Para isto, usaremos os Dados da Educação Básica de 2013. Nesta base, possuímos informações sobre as notas das escolas, número de matrículas, número de professores, localização, entre outras.\n",
    "\n",
    "Antes de partir para a estimação dos modelos, seguem a quantidade de escolas por região e sua oferta dos níveis de ensino. Através do último gráfico, vemos que as escolas que oferecem todos os níveis de ensino não minoria."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Text(0.5, 0, 'Região')"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAA4IAAAJbCAYAAACxTyoyAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8qNh9FAAAACXBIWXMAAAsTAAALEwEAmpwYAABAhklEQVR4nO3de7xsd1kf/s8DCSgkMQYO+KMQAxZQLiHACRBIJCEp0KqIFpTa2qJAgCIiVATBloBFiVSRQgsN+AIrNxGUREEkEUIScieEq1wEQpVyCZckYgkm8Pz+mHVg2Nn77JnZlzn7rPf79TqvWeu7Zq15ZuUkTz6zbtXdAQAAYDxutOwCAAAA2F6CIAAAwMgIggAAACMjCAIAAIyMIAgAADAygiAAAMDICILs96rqSVX1tar63i38jEdX1QvWec8TquoRiy4f3vPKqjpigfpeUFWPnuP9V1TV+6vq8uHPLy/wmQvVOsf2193nAMxmG3vlF6Z6y/M20iuq6m1VdZMF1ntDVR0/x/uvm6r58vV69RrbWKjWObZ/SlU9YZh+dVU9dKs+i/3HAcsuALbBI5N8IMmPJXnTsoro7pdvZPnwnsduXkXrum93X7voyttcKwAbs1298lXd/czN2FB3/6vN2M4MvtzdR21kA9tYK8zMEUH2a1X1A0luneS/ZNLk9oyfUlWvqKrzquqTVfXgYfxmVfX6qvpgVb2nqu40jD+qqj4yHCX7k3U+89VV9eKquriq/qaqjpr6zD2/1v1wVb172N5FVXXTFcufUFWXVNUHqupVVXWjYfzsYd2Dqurtw/IPrvbLZlU9sao+XlXnJvmhqfH7VNW5VXVZVf1JVd1sjv15RVU9d6j7nKo6ZBh/YVV9dBh/+nStw/Tnh33y4ao6vapuPIz/q+E7fKiqTpm1DgA2zzJ65dRnzNIrnjv0xA9V1W9NrXtFVX1PVd1mqOPyoaf88Cqf85tV9bGqekeSW0yN/1hVXTis+/I9/XbG2m9Q7/DnNcPYB6vq51bUesTQf1879M2XTW3vF4bv+KGqOnnWOmBRgiD7u3+d5E+TnJ3kvitCzw8meWCSn0nynGHsl5Jc2d13T/L8JP9zGH92kh/r7nskmeVI10HdfZ8kv5Hk6ass/6Mkpwzbe3CS61Ysf2N3H93dRyb5epKfWLH8IUm+MCy/R5L3Ti+sqn+W5KlJdmfy6+7Rw/hNkrwwycO6+15JLk7yH9f4DhfVd06DedjU+N8Odb8/yc9W1S2SPCLJXYbx01bZ1q2TvLm775rJf3ceVJPTj/7nUN89k/yLqjp2jVoA2Drb2St/Yaq3nLBi2Q16xTD+4u4+OsmRSY6sqnusWO/fJDlrOGp37ySfmV5YVfdJ8i+S3C3JLyS53zB+yyS/kuT4Yd1vJvnpVWq+RX33qaG791LvUUlu1913HfbPW1fZ3l2SnJLkrkmOqao7VtVtM9l/xyY5JsnTagsvr4BEEGT/9zOZhKrrk5yVZPrUjLd29zeTvC/JEcPY/ZO8Jkm6+21JfmQYf0+SV1TVLybpGT73jOF1ettJkuEo2sHd/a7hc67u7m+tWP8ew6+bH8wkBN5lxfIPJjm+qk5Nsru7/2HF8qMzaYrXdPc1Sd42jN85k0b6rqq6PJOG+INrfIf7dvdRw58zpsZXfrerk3wtySuHwPi1VbZ1VXefs2K9Oyf5SHf/XXdfl+SPkzxgjVoA2Drb2StfNdVb3rVi2Wq9IklOrKpLklyeSX9b2RMvSfLzVfWcJHfs7q+vWH7/JH/a3dd192eTnDuMH5NJT7xw6IknJbnDKjV/earmo7r70r3U+6kkh1fVS6rqxO6+epXt/U13f2LYrx8Y1tud5B3dfdXQ09+W5L6rrAubRhBkvzWc6nLfJKdX1RVJfjyTZrfHN5JkCGE3XmdzT8zklJk7ZXKkbL3ra78xvM6y7dW8Ismjh18TX5zkptMLu/vjmfzq+TdJXlpVv7DKNnqV6UpyyVQzu0t3P3nO2r7ruw3/47A7yVuS/Nskf7CXdb693iwfVFWPmPoF9jZz1gnAOpbcK1e6Qa+oqu9J8nuZHGk8Msmf5IY98ZwkP5rki0neXFUnrrLttXri6VM98c7d/Tsbqbe7v5pJuDwvyTPWuOxh0Z74y3t64hw1wpoEQfZnj0jyku4+oruPSHLbJPevvV8Td36Sn02Smtxx6yPD+O27+/wkz0pyYJKDFy1qOEJ39Z5TYqrq+1a5JuHmSa4cGuDPrtzGEIq+1t2vTvI/Mjk9dNolSU6qqoOr6uAk/3IY/2iS21fV3Yft3Lyq/vmi32XYxkFJvm84avhrmZwWM4uPJfmR4dqOAzK5LuU902/o7jdNNej/u5E6AVjVPtkrp3xPJmHpq1V1WG54qUSq6geTfK67X5bkDUnuvkq9P1VVBw7987hh/MJMjjbedtjOLfZML2o43bS6+48zOW32qBlXvWSo5ZChr/7LJBdNv6G7//uenriRGmEPdw1lf/bITJpRkqS7r6+qszO5Jm0tL03yB1X1gST/kMmpk0nyu0NgqiR/OPzitxH/PpPTZ34/yf9LcvyK5c9PclmSL2RyuslKd0/y36rqm0n+Mcl/mF7Y3Z8dtv3eYRuXDuP/NFy4/vKh0VQm4e1vV/mMi6pqz6+mf9rdz1vjuxyc5Izh+sNO8p/XeN936e6vV9WTkvxlJr+Gvqm7z5tlXQA2zb7cK9PdV1XV6zM5A+bvk1ywytuOT/JrVfVPSb6c5FErtnFxVf11kg8luSKTAJju/uLQh06vqgMzuV7/ccPnTLvFiqNwL+3uV65R8m2TvKqqKsn1mVxPOcv3/Oxwucf5w9CLuvuKWdaFRVX3LJc7AQAAsL9waigAAMDICIIAAAAjIwgCAACMjCAIAAAwMoIgAADAyOy3j4+45S1v2UccccSyywBgG7z3ve/9UnfvWnYdO4UeCTAOe+uP+20QPOKII3LppZcuuwwAtkFVfWbZNewkeiTAOOytPzo1FAAAYGQEQQAAgJERBAEAAEZGEAQAABgZQRAAAGBkBEEAAICREQQBAABGRhAEAAAYGUEQAABgZARBAACAkTlg2QUAAOxkP3rqq5ddwrY75xmPXnYJwAY5IggAADAygiAAAMDICIIAAAAjIwgCAACMjCAIAAAwMoIgAADAyAiCAAAAIyMIAgAAjIwgCAAAMDKCIAAAwMgIggAAACMjCAIAAIyMIAgAADAyByy7AAAAxuOYX3/hskvYdhf89tOXXQLcgCOCAAAAIyMIAgAAjIwgCAAAMDKCIAAAwMgIggAAACMjCAIAAIyMIAgAADAygiAAAMDICIIAAAAjIwgCAACMjCAIAAAwMoIgAADAyGxJEKyq+1bV+VV1XlW9aBh7+jD/2qo6cKNjAAAALGarjgh+JsmDuvvYJLeqqgcmOWGY/0CSh1fVrRYd26KaAQAARmFLgmB3f767rx1mr0ty1yRnD/NnJTkmye4NjAEAALCgLb1GsKqOTLIryVVJrhmGr05y6PBn0bG1Pu/kqrq0qi698sorN+EbAMD+QY8EYNqWBcGqOizJS5M8JpMAd8iw6JBMguFGxlbV3ad19+7u3r1r167N+SIAsB/QIwGYtlU3izkgyWuS/Gp3fz7JJUkeOCw+KcmFGxwDAABgQVt1RPCRSY5O8jtVdXaSH0pyTlWdl+SoJG/p7i8uOrZFNQMAAIzCAVux0e5+fZLXrxi+IMmpK9536qJjAAAALMYD5QEAAEZGEAQAABgZQRAAAGBkBEEAAICREQQBAABGRhAEAAAYGUEQAABgZARBAACAkREEAQAARkYQBAAAGBlBEAAAYGQEQQAAgJERBAEAAEZGEAQAABgZQRAAAGBkBEEAAICREQQBAABGRhAEAAAYGUEQAABgZARBAACAkREEAQAARkYQBAAAGBlBEAAAYGQEQQAAgJERBAEAAEZGEAQAABgZQRAAAGBkBEEAAICREQQBAABGRhAEAAAYGUEQAABgZARBAACAkREEAQAARkYQBAAAGBlBEAAAYGQEQQAAgJFZNwhW1e2H14Oq6rFVddutLwsAWIveDMBGzXJE8JXD628muXWSP9q6cgCAGejNAGzILEFwz3t2dffzk/QW1gMArE9vBmBDZgmCV1fVW5K8p6oqyQFbWxIAsA69GYANmaVxPCLJ7br701V1YJLHbHFNAMDe6c0AbMgsQbCS7K6qhw/TSfJ7W1YRALAevRmADZnl1NA3Jrlnkscl2ZXkyC2tCABYj94MwIbMEgS/v7ufleSL3f3rSb5vvRWq6jZVdVlVXVtVB1TVEVX1hao6u6reMfW+p1fVeVX12uHUlpnHAGDE5u7NADBtliD4reH1a1X1sCR3mmGdryQ5McmFU2Nndvfx3f3gJKmqWyU5obuPTfKBJA+fdWyGzweA/dkivRkAvm2WIPikqrppkqdlEu6est4K3X1td391xfAJVXVuVT11mN+d5Oxh+qwkx8wxBgBjNndvBoBpa94spqoOGya/kOTmSb6U5LkLfs7nMvm18htJTq+qv05yaJJrhuVXD/Ozjq1V88lJTk6Sww8/fMFSAWDftJHerEcCMG1vdw19cyYPqK0V453kQfN8SHd/I5MQmKr6iyR3yyTU3XZ4yyFJrppjbK3POS3JaUmye/duD9cFYH+zcG/WIwGYtmYQ7O4TNutDqurg7v6HYfYBSV6S5NNJ/mOS30lyUibXE14y4xgAjM5m9mYAxm3dawSr6p9X1Z9W1eXD6x1nWOfAqjoryT2S/FWSp1XVe6vq/CSf7e6LuvuLSc6pqvOSHJXkLbOOLfRNAWA/sUhvBoBpszxQ/rQkz+juS6rqPklekeT4va3Q3ddlcvRu2g2uYejuU5OcusgYAIzY3L0ZAKbNctfQG3f3JUnS3RfPuA4AsHX0ZgA2ZJYjgp+oqhdncm3eMUk+ubUlAQDr0JsB2JBZfkF8XJJ3Jbnd8PqLW1oRALAevRmADZnliOBDkpze3V1VleShSf5ya8sCAPZCbwZgQ2Y5Ivj07u4kGV7/09aWBACsQ28GYENmCYI32zMx/Op4860rBwCYgd4MwIbMcmrom6rq7UnOTXJckjdtbUkAwDr0ZgA2ZN0g2N2/OzSbuyT5s+7+yNaXBQCsRW8GYKPWPTW0qn6quz+c5INJnlVVJ259WQDAWvRmADZqlmsEnzS8/lqSlyV53taVAwDMQG8GYENmCYI3r6oDktyou9+T5J+2uCYAYO/0ZgA2ZJYg+I4kFyX5o6r63iTf2NqSAIB16M0AbMgsN4t5TpLnTA09dOvKAQDWozcDsFFrHhGsqtdNTb9wavqtW10UAHBDejMAm2Vvp4b+f1PTu6emb7byjQDAttCbAdgUewuCPcM0ALB99GYANsXerhE8sqrOSFIrpu++LZUBACvpzQBsir0FwXtvWxUAwCz0ZgA2xZpBsLs/s52FAAB7pzcDsFlmeY4gAAAA+xFBEAAAYGTWDYJVdYeqel1VnVFVB1TVU7ajMABgdXozABs1yxHB05L8VpKDuvv6JA/b2pIAgHXozQBsyEynhnb3h7a6EABgdnozABsxSxD8P1X1S0kOrqrHJfnUFtcEAOyd3gzAhswSBB+f5PokFyU5MMl/3NKKAID16M0AbMiazxGsqsOmZt84/EmSg5N8ZSuLAgBuSG8GYLOsGQSTvDlJJzk0yQ8m+ViSOyf5dJLdW14ZALCS3gzApljz1NDuPqG7H5Tk40nu0N33T3KHTJoOALDN9GYANsss1wjeIck1w/Q1Se64deUAADPQmwHYkL2dGrrHa5NcUFXvT3JkktdvbUkAwDr0ZgA2ZN0g2N0vrqrXJbl9kk9395VbXxYAsBa9GYCNmuWIYIYGo8kAwD5CbwZgI2a5RhAAAID9iCAIAAAwMusGwaq6X1VdWFXvr6oLquqY7SgMAFid3gzARs1yjeB/S/KI7v77qrpdkjckecDWlgUA7IXeDMCGzHRqaHf//fD6d1tbDgAwC70ZgI2Y5Yjg2VX1liQXJblfkrO3siAAYF16M4zE0U985rJL2HaXvOwFyy5hFPYaBKvqRknOS/KmJHdK8vbuft92FAYA3JDeDMBm2GsQ7O5vVdUTu/snk1y+PSUBAGvRmwHYDLOcGvqVqvq9JBck+WaSdPefbmlVAMDe6M0AbMgsQfCK4fUuw2sn0WwAYHmuGF71ZgAWsm4Q7O7nVtWdktwuybuSHLbeOlV1myR/kUmDOqi7r6+qFyXZneSy7n7K8L6FxwBgrBbpzQAwbZYHyv/nJM9P8sJMfnF87Qzb/UqSE5NcOGzjXpkEwuOS3KSqjt7I2ALfEwD2Gwv2ZgD4tllODT2pux9YVe/q7q6qA9dbobuvTXJtVe0Zul+SM4fps5Ick+T6DYxdMkPdbKIfPfXVyy5h253zjEcvuwSAtczdmwFg2kwPlK+q703SVXXTJLXe+1dxaJJrhumrh/mNjAHAqG1CbwZgxGY5InhKkncnuUOSdyb5jQU+5+okhwzThyS5KpO7nC06tqqqOjnJyUly+OGHL1AmAOwIp2TO3qxHAjBt3SOC3f2u7r5Pkh/u7gd097sW+JwLMrlmMElOyuTawY2MrVXrad29u7t379q1a4EyAWDft0hv1iMBmLbuEcGqOirJ45McVsNFf939M+usc2CSv0xyjyR/leRZmVwzeG6Sy7v74uF9C48BwFgt0psBYNosp4b+7yS/kuSzs260u6/L5OjdtItWed8NHgUx6xgAjNjcvRkAps30QPnufueWVwIAzEpvBmBDZjoiWFWXJvlgJs8qSnf/4pZWBQDsjd4MwIbMetfQZ8XpJwCwrzglejMAGzBLEPxod5+x5ZUAALPSmwHYkFmC4CFVdU6S9+c7p5/88pZWBQDsjd4MwIbMEgSfv+VVAADz0JsB2JBZgmBveRUAwDz0ZgA2ZM0gWFVHd/clSR65ZyiTB8R/Nck521AbADBFbwZgs+ztiOAjq+qm3f3kPQNVVUlev/VlAQCr0JsB2BRrBsHu/rWqumVVHTY1fNskd936sgCAlfRmADbLXq8R7O4vVdW79swm+UqSJ+9lFQBgC+nNAGyGdW8W090nbEchAMBs9GYANmpvN4v586x+V7Lu7p/cupIAgNXozQBslr0dEfylFfP3SfLsJFdsWTUAwN7ozQBsir3dLOYzSVJVxyX59STXJPkP3f3+baoNAJiiNwOwWfZ2auhJSZ6Z5LNJntrdH9u2qgCAG9CbAdgsezs19B1J/ibJ15O8cPKYoonuftgW1wUA3JDeDMCm2FsQvP22VQEAzEJvBmBTrHuNIACwb9CbAdgsN1p2AQAAAGwvQRAAAGBkBEEAAICREQQBAABGRhAEAAAYGUEQAABgZARBAACAkREEAQAARmbNB8oDAOP0oNP+fNklbKt3nvwTyy4BYNs5IggAADAygiAAAMDICIIAAAAjIwgCAACMjCAIAAAwMoIgAADAyAiCAAAAIyMIAgAAjIwgCAAAMDIHLLsA2B8d8+svXHYJ2+6C3376sksAAGBGjggCAACMjCAIAAAwMoIgAADAyAiCAAAAIyMIAgAAjMy2BcGqOqKqvlBVZ1fVO4axp1fVeVX12qo6cJ4xAAAAFrPdRwTP7O7ju/vBVXWrJCd097FJPpDk4bOObXPNAAAA+5XtDoInVNW5VfXUJLuTnD2Mn5XkmDnGAAAAWNB2PlD+c0nulOQbSU5PcnCSLw7Lrk5y6PDnmhnGAAAAWNC2HRHs7m909z929/VJ/iLJJ5McMiw+JMlVmQS9WcZWVVUnV9WlVXXplVdeudlfAQB2LD0SgGnbebOYg6dmH5Dkb5M8cJg/KcmFSS6ZcWxV3X1ad+/u7t27du3axOoBYGfTIwGYtp3XCB5XVe+tqvOTfLa7L0pyTlWdl+SoJG/p7i/OMraNNQMAAOx3tu0awe5+W5K3rRg7Ncmpi4wBAACwmO28WQwAAMCWueejHrvsErbd+97wyoXWEwSBpTv6ic9cdgnb7pKXvWDZJQAAI7bdzxEEAABgyQRBAACAkREEAQAARkYQBAAAGBlBEAAAYGQEQQAAgJERBAEAAEZGEAQAABgZQRAAAGBkBEEAAICREQQBAABGRhAEAAAYGUEQAABgZARBAACAkREEAQAARkYQBAAAGBlBEAAAYGQEQQAAgJERBAEAAEbmgGUXsCwPOu3Pl13CtnvnyT+x7BIAAIB9gCOCAAAAIyMIAgAAjIwgCAAAMDKCIAAAwMgIggAAACMjCAIAAIyMIAgAADAygiAAAMDIjPaB8gA71T0f9dhll7Dt3veGVy67BADYrzgiCAAAMDKCIAAAwMgIggAAACMjCAIAAIyMIAgAADAygiAAAMDICIIAAAAjIwgCAACMjCAIAAAwMoIgAADAyAiCAAAAIyMIAgAAjIwgCAAAMDI7JghW1Yuq6tyqevGyawEAANjJdkQQrKp7JTmou49LcpOqOnrZNQEAAOxUOyIIJrlfkjOH6bOSHLPEWgAAAHa06u5l17CuqnpWksu6++1VdVKS+3f381Z538lJTh5m75zkY9tY5qxumeRLyy5ih7HP5mN/zcf+ms++ur9+sLt3LbuIfZkeuV+yv+Zjf83H/prPvrq/1uyPOyUIPinJld39xqr66SS37e7/vuy6FlFVl3b37mXXsZPYZ/Oxv+Zjf83H/mIr+fs1H/trPvbXfOyv+ezE/bVTTg29IMmJw/RJSS5cYi0AAAA72o4Igt19WZJrq+rcJN/s7ouXXRMAAMBOdcCyC5hVdz9l2TVsktOWXcAOZJ/Nx/6aj/01H/uLreTv13zsr/nYX/Oxv+az4/bXjrhGEAAAgM2zI04NBQAAYPMIggAAACMjCAIAAIyMILiNquo2VXVZVV1bVTvmRj3LUlX3rarzq+q8qnrRsuvZ11XV3Yb9dW5Vvaqqatk17QRV9dSqOm/ZdezrquqIqvpCVZ1dVe9Ydj3sX/TH+eiP89EfF6M/zmYn90dBcHt9JZPnIXoO4mw+k+RB3X1skltV1d2XXdA+7mPdff/uPm6Y31EPNV2GqrppkqOWXccOcmZ3H9/dD152Iex39Mf56I/z0R/npD/ObUf2R0FwG3X3td391WXXsVN09+e7+9ph9rok31xmPfu67r5uavYbSf5uWbXsII9J8ofLLmIHOWH4Rf2pyy6E/Yv+OB/9cT7640L0x/nsyP4oCLLPq6ojk+zq7o8su5Z9XVU9rKo+lOTWSb687Hr2ZVV1YJLju/udy65lh/hckjslOSHJScO/l8AS6Y+z0x9npz/Obcf2R0GQfVpVHZbkpZn8MsU6uvuM7r5bkr9P8uPLrmcf9/NJXrfsInaK7v5Gd/9jd1+f5C+S3G3ZNcGY6Y/z0R/noj/OYSf3R0GQfdZww4DXJPnV7v78suvZ1w3n8+9xTZKvL6uWHeLOSZ5YVW9PcteqevKyC9qXVdXBU7MPSPLJZdUCY6c/zkd/nJv+OIed3B8FwW1UVQdW1VlJ7pHkr6rqvsuuaR/3yCRHJ/md4U5Mxyy7oH3cQ6vq3VX17kxOfdlRd67abt39jO5+SHc/NMmHu/sly65pH3dcVb23qs5P8tnuvmjZBbH/0B/npj/OR3+cg/44tx3bH6u7l10DAAAA28gRQQAAgJERBAEAAEZGEAQAABgZQRAAAGBkBEEAAICREQRhH1VVR1TVl4dbg19cVT835/oPraqfHaYfVVVvrqrHV9URW1IwAGwD/RE2hyAI+7YLuvv4JMcned48K3b327v7j4fZQ5M8O8mdu/uKTawPAJZBf4QNEgRhZzgkyU2q6vur6k1V9c6qeltV7UqSqvr9qjq/ql5WVR8dxh5dVb86rH//JC/P5KGn9xuW//DwgN1zq+oVVVXL+GIAsAH6IyxIEIR92zFVdU6STyR5VpJnJvnf3f2gJC9L8rSquneSI7r7/klemOQHVtnOE4dfTv9dkucMY6cmeVp3H5fkuiQP29JvAgCbR3+EDTpg2QUAe3VBd/94VZ2Y5NFJDktybFU9LZN/fz+c5I5JLkmS7v5UVX1pegNVdeMk/7Wqjk5yfZJbDItu393vHabPT3Lnrf4yALBJ9EfYIEcEYQfo7r9Ocqsk/y/J87r7+O4+NsmTk/xtknsnkwvok9xyxer3yOQX0WOTPCHJnlNcrhh+LU0mp8Z8fEu/BABsMv0RFueIIOwcL0nyuCSPqapfy+SHnFd09+uq6rNVdX6SDyb53Ir1PpbksKp6Z5L3TI0/M8n/Gi59+HiSM7b6CwDAFtAfYQHV3cuuAdigqjqwu6+rqjskec1wPQQAjJr+CGtzRBD2Dy+pqh9JcvMkT1t2MQCwj9AfYQ2OCAIAAIyMm8UAAACMjCAIAAAwMoIgAADAyAiCAACwoKq6cVU9sapusuxaYB6CIPuEqnpSVX2tqr53Cz/j0VX1gnXe84SqesSiy4f3vHJ4cO2mqqorqur9VXX58OdH1/s+e9nWw6rqlxdY74er6uw53n9KVf3dVM1nLfCZC9U652d8fng9oqouHKaPr6o3bOXnAuwrquq/VNWHq+qDVXVpVd1+CTU8uqpWPvR9vXWum+oxl6/Xo+fc9tlV9f6p+aqqv1+lN7wgyUe7+59W2ca3++Ys/w8xY13HV9Ubqur2VfWxFcsOqarPCaXMwuMj2Fc8MskHkvxYkjctq4jufvlGlg/veey8n1tVxyc5vrtPWeet9+3ua6fmz5n3s5Kku7fz4bjPn2W/rWWbawUYnaq6f5IHJjlqeObebZP84xJKeXSSC5N8aY51vtzdR21JNRM3qqo7dvcnkjwgyVdXvqG7nz7LhjbSC9fY3qer6h+q6sju/sAw/BNJ3r5aKIWVHBFk6arqB5LcOsl/ySQQ7hk/papeUVXnVdUnq+rBw/jNqur1w6+W76mqOw3jj6qqjwxHzf5knc98dVW9uKourqq/qaqjpj7zCcP0D1fVu4ftXVRVN12x/AlVdUlVfaCqXlVVNxrGzx7WPaiq3j4s/+AQ9jZzv337iNVe9tUPDWOXVdUFNXmW0ncdHa2qX6mqjw7f8/dX+Zw7Db8Ovz/JyVPjB1XVHw374JKqut8cta9V7wnDvrq8qt6zSq1r/XPbVVV/Oaz7V1V164V2KsA4/UCSL3b3dUnS3X/f3V9Nkqr6har60PDn5GHsiKp633BU6pNV9V+r6jFV9d6h1xw6vO+OVXXmMP72Pf9trskZLs8d+s45NTmK9VNJdif5s6o6d3jfj1XVhUNPePmePjuLqvr80C8+XFWn1+T0zRtX1WvqO0c+f26GTf1JvvP/Jo8c5vd8xqp9cC99c/r/IY4e3vPBqnpZVd141u+2whun6kuSR0zXCHsjCLIv+NdJ/jTJ2UnuW1U3m1r2g5n8SvkzSZ4zjP1Skiu7++5Jnp/kfw7jz07yY919jySzHJU7qLvvk+Q3kqz2a94fJTll2N6Dk1y3Yvkbu/vo7j4yydcz+RVu2kOSfGFYfo8k752hpvVcNDTEP15l2Wr76nNJTuzueyV5SpLfWmW9Zye55/A9n7PK8t9P8rxheU2N/0aSN3f30Ul+Ksn/WKPmZ9d3Ttl55Tr1Pi3JLw+/7v6rNba32j+35yY5c/g7cXomfy8AmM2ZSe42BKQXV9XuJKnJkcFnJzk2yTFJnlbfufThR5I8K8ldMjmS9/3dfe8kFyT52eE9/yPJY4fxP0jyn6c+82+HvvL+JD/b3X+W5NIkP9Xdx9XkFNFfyeRsmaOSfDPJT69S+y3qu08N3T2M3zqTHnXXTP5/90FJjkpyu+6+69Av3jrDvnlrkh+rqsrkiOB5U8vW6oO/n9X75rRXJXn8UMdhSf7NDLWs5k8yCX+pqoOS3DeTf56wLkGQfcHPZBKqrk9yVr47ALy1u7+Z5H1JjhjG7p/kNUnS3W/LpBklyXuSvKKqfjFJz/C5e045nN52ksk59kkO7u53DZ9zdXd/a8X696jJEckPZhIC77Ji+QeTHF9VpybZ3d3/sLKAqjqjqi5P8sokT5hqZLdZo+b7dvdR3f2zqyxbbV/dNMmrqupDSV6xSo3JJKC+pqoeleQbqyw/aur0zNdPjf+LJM8d6v+LJLeqqtVON3/+UPNRK06bXa3e85P8TlX90lD7alb75/btvxPD6wPWWBeAFYb+dM9MfjD8epIzq+pfZHKE7h3dfdXwnrdlEjSS5CPd/anu/kaSTyV5xzD+oSSHV9XBmfy3+PShT/yXJLed+tg1e/DgmCRHJrlwWP+kJHdY5X1fnuoxR3X3pcP4Vd295/KJPZ/xqaG2l1TVid199Qy75x+T/N8kP5/koiTT/y+wVh9cq28mSYYjpjfq7j0/EL82C/at7v50kq9V1d2T/HiSv9pzZBfW4xpBlqomp4XeN5NGkSTfk+SQfOc6wW8kSXd/a4bTJp6YSeN4WCZHzu4+hMu17Ak930qyyCkZr0jyL7v7E1X1q0kOml7Y3R+vqntn8h/ml1bVy7r7VSve87BkrmsE92a1ffUrST6W5N8muUUmv7au9GNJTsjkyOwTkhy/YnmvMV2ZfP//u1n1dvdvV9VfZhKsL5r6ZfcG62WOf241uWj+4mH25Zt9nQbATjf1Y+xZVfWlJD85zK9l+hq0b03N7/lv842SfHYv1++t99/ySnJ6d5+8yrJZTP+w+a0kN+7ur1bVkZn84PyMqjpuxr77J0lenkmfXFnjDfpgVa3VN2dWk+s295zx9NipgLtWfY/M5MfeP1jk8xgnRwRZtkckeUl3H9HdR2Tya+H9V5weutL5GU47qaqHJvnIMH777j4/k1NVDkxy8KJFdfc1Sa6uqhOGz/m+Va5NuHmSK6vqe/Kd02C+bTiq97XufnUmp4vcY9F6NuCQJJ/v7s7k1J3vMnyn23b3WUn+U5I7rbKN91fVjw/T09/zrCRPmtrWhr9fVd2huy/v7t9MckWS28246rf/TiT5uXz3qTvp7n+a+rVYCASYUlV3rqofGqYryd2S/J8klyQ5cbiG76Ak/zKTo2LrGo62fbW+cw34gTVcp74X/5Dv9O4Lh8++7bD+LfZML2o43bS6+48zuYTgqBlXfWsmdwZdeYO2tfrgWn0zSdLdVyX55tT7/01u2LfOX+Uo51r+ZNjG/bL38A7fRRBk2R6Z5C17ZoZfJM/O5CjVWl6a5DZV9YFMrjfY8x/h3x1O0/xAkj/cc6H7Bvz7TE75eH+St2cSLqc9P8llSd6VyWknK909ySXDKSMnD3Vvt5cn+aWhhtWC8Y2TvG7YlxdlcurOSr+S5JRhP0xf6/C8DP8cquojWfu6zOlrBNdrZk8brlH5QJKPZnLtyCxOSfKvhvV+OpPrNgCYzUGZXCLw4UxO7bxRJj/SfjbJqZn82HZhkhd19xVzbPffJnn60D8uz3dOK13Lq5P8UVWd291fzKS/nz78t/0dSW61yjorrxHc2z0Cbpvk3UNP/N1Mwt2exz6tdgZKkqS7/7G7f2e4nGHaWn3wV7J635z2i0n+YPj/lquTLPy4ouH00KsyOY3XaaHMrCYHCgAAABgLRwQBAABGRhAEAAAYGUEQAABgZARBAACAkREEAQAARma/faD8LW95yz7iiCOWXQYA2+C9733vl7p717Lr2Cn0SIBx2Ft/3G+D4BFHHJFLL13vkWUA7A+q6jPLrmEn0SMBxmFv/dGpoQAAACMjCAIAAIyMIAgAADAygiAAAMDICIIAAAAjIwgCAACMjCAIAAAwMoIgAADAyAiCAAAAI3PAsgsAAGA8jvn1Fy67hG13wW8/fdklwA04IggAADAygiAAAMDIbFsQrKqbVdVbq+rsqjq9qm5aVS+qqnOr6sVT75tpDAAAgMVs5xHBhya5qLuPT3JxkmcmOai7j0tyk6o6uqruNcvYNtYMAACw39nOIPjJJDcfpg9N0knOHObPSnJMkvvNOAYAAMCCtjMIfiLJMVX14SS7k1yf5Jph2dWZhMNDZxxbVVWdXFWXVtWlV1555aYWDwA7mR4JwLTtDIL/Icmfd/ddk7w1yYFJDhmWHZLkqkyC3ixjq+ru07p7d3fv3rVr1yaXDwA7lx4JwLTtDIKV5CvD9JeG1xOH15OSXJjkghnHAAAAWNB2BsHXJfmZqjo7yb9N8pIk11bVuUm+2d0Xd/dls4xtY80AAAD7nQO264O6+6okD1kx/JRV3jfTGAAAAIvxQHkAAICREQQBAABGRhAEAAAYGUEQAABgZARBAACAkREEAQAARkYQBAAAGBlBEAAAYGQEQQAAgJERBAEAAEZGEAQAABgZQRAAAGBkBEEAAICREQQBAABGRhAEAAAYGUEQAABgZARBAACAkREEAQAARkYQBAAAGBlBEAAAYGQEQQAAgJHZtiBYVQ+tqrOHP5+rqodX1dOr6ryqem1VHTi8b6YxAAAAFrNtQbC7397dx3f38Un+T5LLkpzQ3ccm+UCSh1fVrWYZ266aAQAA9kfbfmpoVd0hyReS3C3J2cPwWUmOSbJ7xjEAAAAWtIxrBH86yZ8lOTTJNcPY1cP8rGOrqqqTq+rSqrr0yiuv3NSiAWAn0yMBmLaMIPgTSc7IJNQdMowdkuSqOcZW1d2ndffu7t69a9euza4bAHYsPRKAadsaBKvqB5L8U3d/OcklSR44LDopyYVzjAEAALCg7T4i+JNJTk+S7v5iknOq6rwkRyV5y6xj21wzAADAfuWA7fyw7v5fK+ZPTXLqImMAAAAsxgPlAQAARkYQBAAAGBlBEAAAYGQEQQAAgJERBAEAAEZGEAQAABgZQRAAAGBkBEEAAICREQQBAABGRhAEAAAYGUEQAABgZARBAACAkREEAQAARkYQBAAAGBlBEAAAYGQEQQAAgJERBAEAAEZGEAQAABgZQRAAAGBkBEEAAICR2dYgWFX/vqr+uqrOrqp/VlUvqqpzq+rFU++ZaQwAAIDFbFsQrKp/luSB3X1idx+f5NZJDuru45LcpKqOrqp7zTK2XTUDAADsjw7Yxs96SJIbV9VfJ/lIko8mOXNYdlaSY5JcP+PYJdtUMwAAwH5nO08NvXWSm3T3iUn+X5LvS3LNsOzqJIcOf2YZAwAAYEHbGQSvTvLuYfqdSSrJIcP8IUmuGt4zy9iqqurkqrq0qi698sorN7F0ANjZ9EgApm1nEDw/yZHD9FFJOsmJw/xJSS5McsGMY6vq7tO6e3d37961a9emFg8AO5keCcC0bQuC3X15kq9X1dlJjk7y35JcW1XnJvlmd1/c3ZfNMrZdNQMAAOyPtvNmMenuX10x9JRV3jPTGAAAAIvxQHkAAICREQQBAABGRhAEAAAYmYWCYFXdfng9qKoeW1W33dyyAIC16MMAbNSiRwRfObz+ZiYPiv+jzSkHAJiBPgzAhiwaBPest6u7n5/JMwEBgO2hDwOwIYsGwaur6i1J3lNVlW1+DAUAjJw+DMCGLNo4HpHkdt396ao6MMljNrEmAGDv9GEANmTRIFhJdlfVw4fpJPm9TakIAFiPPgzAhix6augbk9wzyeOS7Epy5KZVBACsRx8GYEMWDYLf393PSvLF7v71JN+3iTUBAHunDwOwIYsGwW8Nr1+rqocludMm1QMArE8fBmBDFg2CT6qqmyZ5WpITkzxl80oCANahDwOwIXPdLKaqDhsmv5Dk5km+lOS5m10UAHBD+jAAm2Xeu4a+OZOH1taK8U7yoE2pCABYiz4MwKaYKwh29wlbVQgAsHf6MACbZaFrBKvqn1fVn1bV5cPrHTe7MABgdfowABu16M1iTkvy2919VJIXJHnFplUEAKxHHwZgQxYNgjfu7kuSpLsv3sB2AID56cMAbMi8N4vZ4xNV9eIkFyY5JsknN68kAGAd+vA+5EdPffWyS9h25zzj0csuAdigRX9BfFySdyW53fD6i5tWEQCwHn0YgA1Z9IjgQ5Kc3t1dVZXkoUn+cm8rVNURSS5K8jdJ/qm7H1xVT0/yk0k+k+TR3X3drGML1g0A+4O5+zAATFv0iODTu7uTZHj9TzOud2Z3Hz+EwFslOaG7j03ygSQPn3VswZoBYH+xaB8GgCSLB8Gb7ZkYfom8+YzrnVBV51bVU5PsTnL2MH5WJtc4zDoGAGO2aB8GgCSLnxr6pqp6e5JzkxyX5E0zrPO5JHdK8o0kpyc5OMkXh2VXJzl0+HPNDGOrqqqTk5ycJIcffvhMXwQAdqC5+7AeCcC0hYJgd//u0IDukuTPuvsjM6zzjUxCYKrqLzIJd/9sWHxIkqsyCXq3nWFsrc84LZNnK2X37t09x1cCgB1jwT6sRwLwbQudGlpVP9XdH07ywSTPqqoTZ1jn4KnZByT52yQPHOZPyuQW2JfMOAYAo7VIHwaAaYteI/ik4fXXkrwsyfNmWOe4qnpvVZ2f5LPdfVGSc6rqvCRHJXlLd39xlrEFawaA/cUifRgAvm3RawRvXlUHJLlRd7+nqv5pvRW6+21J3rZi7NQkpy4yBgAjNncfBoBpix4RfEcmzwT8o6r63gzX/gEA20IfBmBDFr1ZzHOSPGdq6KGbUw4AsB59GICNmuuIYFW9bmr6hVPTb93MogCAG9KHAdgs854a+v9NTe+emr7ZyjcCAJtOHwZgU8wbBHuGaQBga+jDAGyKea8RPLKqzkhSK6bvvumVAQAr6cMAbIp5g+C9t6QKAGAW+jAAm2KuINjdn9mqQgCAvdOHAdgsiz5HEAAAgB1KEAQAABiZhYJgVd2hql5XVWdU1QFV9ZTNLgwAWJ0+DMBGLXpE8LQkv5XkoO6+PsnDNq8kAGAd+jAAG7LwqaHd/aHNLAQAmJ0+DMBGLBoE/09V/VKSg6vqcUk+tYk1AQB7pw8DsCGLBsHHJ7k+yUVJDkzyHzetIgBgPfowABsy13MEq+qwqdk3Dn+S5OAkX9msogCAG9KHAdgscwXBJG9O0kkOTfKDST6W5M5JPp1k96ZWBgCspA8DsCnmOjW0u0/o7gcl+XiSO3T3/ZPcIZNGBABsIX0YgM2y6DWCd0hyzTB9TZI7bk45AMAM9GEANmTeU0P3eG2SC6rq/UmOTPL6zSsJAFiHPgzAhiwUBLv7xVX1uiS3T/Lp7r5yc8sCANaiDwOwURt5oPyV3X3xvM2nqp5aVecN0y+qqnOr6sVTy2caA4AxW7QPA0CygSC4iKq6aZKjhul7JTmou49LcpOqOnrWse2sGQAAYH+zrUEwyWOS/OEwfb8kZw7TZyU5Zo4xAAAAFrRQEKyq+1XVhVX1/qq6oKrWDWdVdWCS47v7ncPQofnOHc+uHuZnHVvrM06uqkur6tIrr3SmDAD7pwX7sB4JwLctekTwvyV5RHffI8nPDPPr+fkkr5uavzrJIcP0IUmummNsVd19Wnfv7u7du3btmuV7AMBONHcf1iMBmLaRm8X8/fD6dzOucuckT6yqtye5a5JbJjlxWHZSkguTXDDjGACM2gJ9GAC+bdHnCJ5dVW9JclEm1/Cdvd4K3f2MPdNVdV53P7eqXlxV5ya5vLsvHpZdO8sYAIzY3H0YAKbNHQSr6kZJzkvypiR3SvL27n7fPNvo7mOH16essmymMQAYo83owwAwdxDs7m9V1RO7+yeTXL75JQEAa9GHAdgMi54a+pWq+r1Mrt/7ZpJ0959uWlUAwN7owwBsyKJB8Irh9S7DayfRgABge1wxvOrDACxkoSA43OjlTklul+RdSQ7b1KoAgDXpwwBs1EJBsKr+c5Ijk/xQknsneW2Sh2xiXQDAGvRhADZq0ecIntTdj0xydXd3kgM3sSYAYO/0YQA2ZOEHylfV9ybpqrppktq8kgCA9ejDAGzEojeLOSXJu5PcIck7k/zGZhUEAKzrlOjDAGzAojeLeVeS+1TVLbv7S5tcE/ugHz311csuYdud84xHL7sEgFXpwzAeRz/xmcsuYdtd8rIXLLuEUVj0ZjFHJXl8ksOqqpKku39mE+sCANagDwOwUYueGvq/k/xKks9uXikAwIz0YQA2ZOEHynf3Oze1EgBgVvowABuy8BHBqro0yQeTdJJ09y9uWlUAwN7owwBsyEbuGvqsOCUFAJbhlOjDAGzAokHwo919xqZWAgDMSh8GYEMWDYKHVNU5Sd6f75yS8subVhUAsDf6MAAbsmgQfP6mVgEAzEMfBmBDFg2CvalVAADz0IcB2JC5gmBVHd3dlyR55J6hJPdI8tUk52xybQDAFH0YgM0y7xHBR1bVTbv7yXsGqqqSvH5zywIAVqEPA7Ap5gqC3f1rVXXLqjpsavi2Se663rpVdbckpyX5ZpK/TfKLSX4vye4kl3X3U4b3vWiWMQAYm430YQCYNvc1gt39pap6157ZJF9J8uS9rLLHx7r7/klSVa9Kcp8kB3X3cVX1sqo6OpOQuO7YcFoMAIzOBvowAHzbQjeL6e4TFljnuqnZbyQ5McmZw/xZSY5Jcv2MY4IgAKO1SB8GgGnz3izmz7P6ncq6u39yhvUfluS3knwiyeeSXDMsujqT01quT/KpGcYAYHQ22ocBYI95jwj+0or5+yR5dpIrZlm5u89IckZVvSSTgHfIsOiQJFdlchroLGOrqqqTk5ycJIcffvgsJQHATrJwH9YjAZh2o3ne3N2f6e7PJDk8ycuS/Osk/6G7H77eulV106nZazL5RfPEYf6kJBcmuWDGsbXqO627d3f37l27ds36tQBgR9hIH9YjAZg276mhJyV5ZpLPJnlqd39sjtUfWlVPG6Y/kcmvki+qqnOTXN7dFw+fce0sYwAwNhvswwDwbfOeGvqOJH+T5OtJXjh5dNFEdz9sbyt29+lJTl8xfINHQaz2eAiPjACAJBvowwAwbd4gePstqQIAmIU+DMCmmPeB8p/ZqkIAgL3ThwHYLHPdLAYAAICdTxAEAAAYGUEQAABgZARBAACAkREEAQAARkYQBAAAGBlBEAAAYGQEQQAAgJERBAEAAEZGEAQAABgZQRAAAGBkBEEAAICREQQBAABGRhAEAAAYGUEQAABgZARBAACAkREEAQAARkYQBAAAGBlBEAAAYGS2LQhW1X2r6vyqOq+qXjSMPX2Yf21VHTjPGAAAAIvZziOCn0nyoO4+NsmtquqBSU4Y5j+Q5OFVdatZxraxZgAAgP3OtgXB7v58d187zF6X5K5Jzh7mz0pyTJLdM44BAACwoG2/RrCqjkyyK8lVSa4Zhq9OcujwZ5axtbZ9clVdWlWXXnnllZtaNwDsZHokANO2NQhW1WFJXprkMZmEukOGRYdkEgxnHVtVd5/W3bu7e/euXbs2uXoA2Ln0SACmbefNYg5I8pokv9rdn09ySZIHDotPSnLhHGMAAAAsaDuPCD4yydFJfqeqzk7yQ0nOqarzkhyV5C3d/cVZxraxZgAAgP3OAdv1Qd39+iSvXzF8QZJTV7zv1FnGAAAAWIwHygMAAIyMIAgAADAy23ZqKACwMzzotD9fdgnb6p0n/8SySwDYdo4IAgAAjIwgCAAAMDKCIAAAwMgIggAAACMjCAIAAIyMu4YCAAD7hXs+6rHLLmHbve8Nr1xoPUcEAQAARkYQBAAAGBlBEAAAYGQEQQAAgJERBAEAAEZGEAQAABgZQRAAAGBkBEEAAICREQQBAABGRhAEAAAYGUEQAABgZARBAACAkdm2IFhVt6mqy6rq2qo6YBh7UVWdW1UvnnrfTGMAAAAsZjuPCH4lyYlJLkySqrpXkoO6+7gkN6mqo2cd28aaAQAA9jsHbNcHdfe1Sa6tqj1D90ty5jB9VpJjklw/49gl21AyAADAfmnbguAqDk3yqWH66iR3zST0zTK2qqo6OcnJSXL44YdvesEwq2N+/YXLLmHbXfDbT192CcBe6JEATFvmzWKuTnLIMH1IkqvmGFtVd5/W3bu7e/euXbs2v2IA2KH0SACmLTMIXpDJNYNJclIm1w7OOgYAAMCCtvOuoQdW1VlJ7pHkr5IcmMk1g+cm+WZ3X9zdl80ytl01AwAA7I+282Yx12VyRG/aRau87ymzjAEAALAYD5QHAAAYGUEQAABgZARBAACAkREEAQAARkYQBAAAGBlBEAAAYGQEQQAAgJERBAEAAEZGEAQAABgZQRAAAGBkDlh2AcvyoNP+fNklbLt3nvwTyy4BAADYBzgiCAAAMDKCIAAAwMgIggAAACMjCAIAAIzMaG8WA+w7jn7iM5ddwra75GUvWHYJAMCIOSIIAAAwMoIgAADAyAiCAAAAIyMIAgAAjMyOuVlMVb0oye4kl3X3U5ZdD8Cy3PNRj112CdvufW945bJLAID9yo44IlhV90pyUHcfl+QmVXX0smsCAADYqXZEEExyvyRnDtNnJTlmibUAAADsaDslCB6a5Jph+uphHgAAgAVUdy+7hnVV1ZOSXNndb6yqn05y2+7+76u87+QkJw+zd07ysW0sc1a3TPKlZRexw9hn87G/5mN/zWdf3V8/2N27ll3EvkyP3C/ZX/Oxv+Zjf81nX91fa/bHnRIE75Xk8d39+Kr6n0le3d0XL7uuRVTVpd29e9l17CT22Xzsr/nYX/Oxv9hK/n7Nx/6aj/01H/trPjtxf+2IU0O7+7Ik11bVuUm+uVNDIAAAwL5gxzw+wiMjAAAANseOOCK4nzlt2QXsQPbZfOyv+dhf87G/2Er+fs3H/pqP/TUf+2s+O25/7YhrBAEAANg8jggCAACMjCAIAAAwMoIgAADAyAiC26iqblNVl1XVtVW1Y+7YuixVdd+qOr+qzquqFy27nn1dVd1t2F/nVtWrqqqWXdNOUFVPrarzll3Hvq6qjqiqL1TV2VX1jmXXw/5Ff5yP/jgf/XEx+uNsdnJ/FAS311eSnJjkwmUXskN8JsmDuvvYJLeqqrsvu6B93Me6+/7dfdwwv6MearoMVXXTJEctu44d5MzuPr67H7zsQtjv6I/z0R/noz/OSX+c247sj4LgNurua7v7q8uuY6fo7s9397XD7HVJvrnMevZ13X3d1Ow3kvzdsmrZQR6T5A+XXcQOcsLwi/pTl10I+xf9cT7643z0x4Xoj/PZkf1REGSfV1VHJtnV3R9Zdi37uqp6WFV9KMmtk3x52fXsy6rqwCTHd/c7l13LDvG5JHdKckKSk4Z/L4El0h9npz/OTn+c247tj4Ig+7SqOizJSzP5ZYp1dPcZ3X23JH+f5MeXXc8+7ueTvG7ZRewU3f2N7v7H7r4+yV8kuduya4Ix0x/noz/ORX+cw07uj4Ig+6zhhgGvSfKr3f35ZdezrxvO59/jmiRfX1YtO8Sdkzyxqt6e5K5V9eRlF7Qvq6qDp2YfkOSTy6oFxk5/nI/+ODf9cQ47uT8Kgtuoqg6sqrOS3CPJX1XVfZdd0z7ukUmOTvI7w52Yjll2Qfu4h1bVu6vq3Zmc+rKj7ly13br7Gd39kO5+aJIPd/dLll3TPu64qnpvVZ2f5LPdfdGyC2L/oT/OTX+cj/44B/1xbju2P1Z3L7sGAAAAtpEjggAAACMjCAIAAIyMIAgAADAygiAAAMDICIIAAAAjIwjCPqqqjqiqLw+3Br+4qn5uzvUfWlU/O0w/qqreXFWPr6ojtqRgANgG+iNsDkEQ9m0XdPfxSY5P8rx5Vuzut3f3Hw+zhyZ5dpI7d/cVm1gfACyD/ggbJAjCznBIkptU1fdX1Zuq6p1V9baq2pUkVfX7VXV+Vb2sqj46jD26qn51WP/+SV6eyUNP7zcs/+HhAbvnVtUrqqqW8cUAYAP0R1iQIAj7tmOq6pwkn0jyrCTPTPK/u/tBSV6W5GlVde8kR3T3/ZO8MMkPrLKdJw6/nP67JM8Zxk5N8rTuPi7JdUketqXfBAA2j/4IG3TAsgsA9uqC7v7xqjoxyaOTHJbk2Kp6Wib//n44yR2TXJIk3f2pqvrS9Aaq6sZJ/mtVHZ3k+iS3GBbdvrvfO0yfn+TOW/1lAGCT6I+wQY4Iwg7Q3X+d5FZJ/l+S53X38d19bJInJ/nbJPdOJhfQJ7nlitXvkckvoscmeUKSPae4XDH8WppMTo35+JZ+CQDYZPojLM4RQdg5XpLkcUkeU1W/lskPOa/o7tdV1Wer6vwkH0zyuRXrfSzJYVX1ziTvmRp/ZpL/NVz68PEkZ2z1FwCALaA/wgKqu5ddA7BBVXVgd19XVXdI8prheggAGDX9EdbmiCDsH15SVT+S5OZJnrbsYgBgH6E/whocEQQAABgZN4sBAAAYGUEQAABgZARBAACAkREEAQAARkYQBAAAGBlBEAAAYGT+f1TlHURmL+IQAAAAAElFTkSuQmCC",
      "text/plain": [
       "<Figure size 1080x720 with 4 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig, ax = plt.subplots(2, 2, figsize=(15, 10), sharey='row')\n",
    "\n",
    "\n",
    "# Escolas que oferecem os anos iniciais de ensino - I\n",
    "i = df.loc[df['num_profcreche'].notna() | df['num_profpreescola'].notna()]\n",
    "i = i.pivot_table(index='co_regiao', values='codigo_escola', aggfunc='count')\n",
    "sns.barplot(x=i.index, y='codigo_escola', data=i, orient='v', ax=ax[0, 0])\n",
    "\n",
    "ax[0, 0].set_title('Anos Iniciais de Ensino - I')\n",
    "ax[0, 0].set_ylabel('Número de Escolas')\n",
    "ax[0, 0].set_xlabel('Região')\n",
    "\n",
    "\n",
    "# Escolas que oferecem os anos finais - II\n",
    "ii = df.loc[df['num_proffundamental'].notna()]\n",
    "ii = ii.pivot_table(index='co_regiao', values='codigo_escola', aggfunc='count')\n",
    "sns.barplot(x=ii.index, y='codigo_escola', data=ii, orient='v', ax=ax[0, 1])\n",
    "\n",
    "ax[0, 1].set_title('Anos Finais de Ensino - II')\n",
    "ax[0, 1].set_ylabel('Número de Escolas')\n",
    "ax[0, 1].set_xlabel('Região')\n",
    "\n",
    "# Escolas que oferecem os anos inciais e finais - III\n",
    "iii = df.loc[df['num_profcreche'].notna() & df['num_profpreescola'].notna() & df['num_proffundamental'].notna()]\n",
    "iii = iii.pivot_table(index='co_regiao', values='codigo_escola', aggfunc='count')\n",
    "sns.barplot(x=iii.index, y='codigo_escola', data=iii, orient='v', ax=ax[1, 0])\n",
    "\n",
    "ax[1, 0].set_title('Anos Iniciais + Finais de Ensino - III')\n",
    "ax[1, 0].set_ylabel('Número de Escolas')\n",
    "ax[1, 0].set_xlabel('Região')\n",
    "\n",
    "# Escolas que oferecem apenas o ensino médio - IV\n",
    "iv = df.loc[df['num_profmedio'].notna()]\n",
    "iv = iv.pivot_table(index='co_regiao', values='codigo_escola', aggfunc='count')\n",
    "sns.barplot(x=iv.index, y='codigo_escola', data=iv, orient='v', ax=ax[1, 1])\n",
    "\n",
    "ax[1, 1].set_title('Somente Ens. Médio - IV')\n",
    "ax[1, 1].set_ylabel('Número de Escolas')\n",
    "ax[1, 1].set_xlabel('Região')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Text(0.5, 0, 'Região')"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAA3gAAAHuCAYAAADNzq45AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8qNh9FAAAACXBIWXMAAAsTAAALEwEAmpwYAAAgmUlEQVR4nO3de5Cld13n8c+XTGQ1ECHSgJRkYzRguCQBJoSQBHNTU7sEogXq7uoua2CQReQiIOC6CK7KZVe8oGjwtshFuUiILASIEJKQCxkCGC4CRkBlExwIBFECAb77Rz9TNu1cuntyzpn59etVNdXP85znnPNtqquod37PeU51dwAAADjw3WbRAwAAAHDrEHgAAACDEHgAAACDEHgAAACDEHgAAACDEHgAAACDEHgALERVHVFVF1bVR6d/T1rx2NFV9f6qem9V3X6Nr3dcVZ25gTkeW1Ufnt7v3VX1iPW+xjre6xeq6if3cs6TqmrLrfBep1XVW1cdu0dVfWBfXxuA/ZfAA2DuqqqSnJ/kD7r7Hkm2JvmRqvqh6ZRzkvxhd9+vu/9xDa93UJLjkqwr8Krq4Ul+Iskp3X1sku9LcvB6XmMGnpRknwMvySVJ7lVV37bi2COTvOZWeG0A9lMCD4BFODPJ57r71UnS3V9I8swkPzOtwj1x2n59klTVz1XV1VX1l1X1mOnYo6rqtVX1ziQvT/LcJP+1qt43rV6dU1VXTftvqKrb7WKOpyV5end/Zprjpu5+1fT6/256vw9U1S/sfEJV3VBVv1VVH6mql1XVQ6f3ubaqjpjO+aOq+u1pBfKDVXXC6jeuqqOq6m1V9Z5pJfMuVfX4JHdLclVV/el03hunc65dEcB71d1fS3JBlmN5p0dE4AEMTeABsAj3SvLeVcfem+To7r4oye8k+aXu/sGqOivJUncfn+WVvkdX1bdPzzkmyUO7+z8k+R9ZXvU7rrvfkeSd3X1Cdx+X5F1Jzt3NHO9bfbCqvjnJbyf590nul+T7qurk6eG7JHlFku9Jcu8kZ3b3CUlemuSxK17mLknun+THpsdW+60kj+7uByT5/SQ/392/leT/JTmhu39kOu8/T+eclOQXp9XPtXpNllftUlXfneTg7v7QOp4PwAHm1rgEBABm6fuSnF1Vp07735rku6btC/dwCefhVfWaLIfWtyS5aDfn9S6O3TPJh7r775JkWk07KcllWV55vHw6/sEkb5+e84EkK1fq/rS7O8l7q2pLVd1h5wPT5wpPSvKGqdcOSnLdbuZ7clU9bNo+Isldk1y/m3NXe2eSl1XVYbF6B7ApCDwAFuHD+cZLB5Plz9B9eBfnVpZXt175DQeXV6T+eQ/v8RtJnt3dF083Tnnobua4X5ZDaK2+smL76yv2v57lUNupV22v3L9Nkk9Nq4u7VVWnJXlgkgd295enG6TcdtU5P53lzxFm9et199er6s+z/L/1I5L8+J7eD4ADn0s0AViEi5IctvOOldOK1i8n+dXdnHtuVf2b6dx77txe5R+TrLzj5qFJrq+q22T3YfPCJM/beSOSqjq0qn40yUeSHF1Vd5vuaPnILF/muR4/PL3msUm+2t037Xxg2v5cVX3/dM7BVXX0Ln6PQ5PcOMXdA5McnVW6+zemy1KP280cr87yjVtu2927CmgABiLwAJi77v56kh9Msq2qPprkmiSv6+7X7eLcNyV5S5KrpxWsl+QbV8p2ekeSB043NjktyS8meXOSq5J8fDdznJ/kj5O8a3rtdyT5Snd/Kcnjp+e/L8lfdPdl6/w1d1TVNUlemWTbLh7/T0meVlXvn95j5+WdL01y6XRZ6FuSfNt0KeiTk7x/nTMky3fTvHNcngmwKdTyxwMAgFtLVf1Rkj/p7gsXPQsAm4sVPAAAgEFYwQMAABiEFTwAAIBBCDwAAIBBCDwAAIBBHJBfdH6nO92pjzjiiEWPAQAAsBDvec97PtPdS6uPH5CBd8QRR2T79u2LHgMAAGAhquqTuzruEk0AAIBBCDwAAIBBCDwAAIBBzCTwquqEqrq8qi6rqhdNx5427b+iqg7e3TEAAAA2ZlYreJ9Mcnp3n5zkzlX1vUlOm/b/Msk5VXXn1cdmNAsAAMCmMJPA6+4buvvmafeWJPdOcvG0f1GSE5Ns3cUxAAAANmimn8GrqmOSLCX5fJIvTIdvSnKH6d/qY3t6rW1Vtb2qtu/YsWMG0wIAABzYZhZ4VXVYkhcnOTfLAXfo9NChWQ6+XR3bre4+r7u3dvfWpaV/9X1+AAAAm96sbrKyJcnLkzy1u29IcnWS750ePjPJlbs5BgAAwAbNagXvkUmOT/KCqro4yXcluaSqLktyXJLzu/sfVh+b0SwAAACbQnX3omdYt61bt/b27dsXPQYAAMBCVNV7unvr6uO+6BwAAGAQAg8AAGAQAg8AAGAQAg8AAGAQAg8AAGAQAg8AAGAQAg8AAGAQAg8AAGAQWxY9APCNHvL8P1r0COyHLvnZRy16BADgAGAFDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBACDwAAYBAzCbyqultVXVNVN1fVlqo6q6ounv5dX1XnTOfdtOL4YbOYBQAAYLPYMqPXvTHJGUlenyTdfWGSC5Okqq5KctF03rXdfeqMZgAAANhUZrKC1903d/fnVh+vqiOTfLq7vzgdOrqqLq2q51VVzWIWAACAzWLen8H7oUyrepOjkjwkyR2TnL2nJ1bVtqraXlXbd+zYMcMRAQAADkzzDryzk1ywc6e7b+zuTnJ+kvvs6YndfV53b+3urUtLS7OdEgAA4AA0t8Crqrsm+Up3f3baP6SqDpoePinJdfOaBQAAYESzuovmwVV1UZJjk7ylqk5I8vAkb1hx2lFJrq6qS5LcPclrZzELAADAZjGTu2h29y1Jzlx1+KpV57wvyf1n8f4AAACbkS86BwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGITAAwAAGMRMAq+q7lZV11TVzVW1paqOqKpPV9XFVfXWFec9raouq6pXVNXBs5gFAABgs9gyo9e9MckZSV6/4tjbuvvHdu5U1Z2TnNbdJ1fVzyY5J8lrZjQPAPvoxGe+cNEjsB+64leetugRAFhhJit43X1zd39u1eHTqurSqnrytL81ycXT9kVJTpzFLAAAAJvFrFbwVrs+yT2SfDnJG6rqL5LcIckXpsdvmvZ3q6q2JdmWJIcffvis5gQAADhgzeUmK9395e7+p+7+apI3JrlPlqPu0OmUQ5N8fi+vcV53b+3urUtLSzOdFwAA4EA0l8Crqtuv2D0pyXVJrk7yvdOxM5NcOY9ZAAAARjWTSzSnO2K+OcmxSd6S5JKqeliWL9G8tLuvms67pKouS/K3SX5tFrMAAABsFjMJvO6+Jcurcis9ZxfnPT/J82cxAwAAwGbji84BAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGMZPAq6q7VdU1VXVzVW2pqhOq6vKquqyqXrTivJuq6uLp32GzmAUAAGCzmNUK3o1Jzkhy5bT/ySSnd/fJSe5cVfedjl/b3adO/26c0SwAAACbwkwCr7tv7u7Prdi/obtvnnZvSfK1afvoqrq0qp5XVTWLWQAAADaLuX4Gr6qOSbLU3R+aDh2V5CFJ7pjk7L08d1tVba+q7Tt27JjxpAAAAAeeuQXe9Bm7Fyc5d+ex7r6xuzvJ+Unus6fnd/d53b21u7cuLS3NdFYAAIAD0VwCr6q2JHl5kqd29w3TsUOq6qDplJOSXDePWQAAAEa1ZRYvWlUHJ3lzkmOTvCXJJUmOT/KC6aN2z0zypSR/UFVfTPLxJM+exSwAAACbxUwCr7tvSXLmqsPP2cWp95/F+wMAAGxGvugcAABgEAIPAABgEAIPAABgEAIPAABgEHsNvKr6zunn7arq0VX1HbMfCwAAgPVaywre700/fzHJXZL88ezGAQAAYKPWEng7z1nq7l9K0jOcBwAAgA1aS+DdVFXnJ3lXLX9L+Uy+Ow8AAIB9s5ZYe0SSu3f3x6vq4CTnzngmAAAANmAtgVdJtlbVOdN2kvzqzCYCAABgQ9Zyieark9wvyWOSLCU5ZqYTAQAAsCFrCbw7dvezkvxDdz8zybfOeCYAAAA2YC2B9/Xp5xer6mFJ7jHDeQAAANigtQTe46vqtkmekuSMJE+c7UgAAABsxG5vslJVh02bn05ySJLPJHnOPIYCAABg/fZ0F83XZflLzWvV8U5y+swmAgAAYEN2G3jdfdo8BwEAAGDf7PUzeFX13VX1Z1X1vunnUfMYDAAAgPVZy01WzkvyK919XJLnJXnpTCcCAABgQ9YSeAd199VJ0t3vXuNzAAAAmLM93WRlp49V1a8nuTLJiUmum+1IAAAAbMRaVuMek+QdSe4+/fyJmU4EAADAhqxlBe8Hkryhu7uqKslZSd4827EAAABYr7Ws4D2tuztJpp8/M9uRAAAA2Ii1BN637NyYVvAOmd04AAAAbNRaLtF8bVVdmOTSJKckee1sRwIAAGAj9hp43f2/p8C7V5LXd/eHZj8WAAAA67XXSzSr6ge7+4NJrk3yrKo6Y/ZjAQAAsF5r+Qze46efT0/ykiTPnd04AAAAbNRaAu+QqtqS5Dbd/a4kX5nxTAAAAGzAWgLvrUmuSvLHVfXNSb4825EAAADYiLXcZOXZSZ694tBZsxsHAACAjdrtCl5VvXLF9gtXbP/fWQ8FAADA+u3pEs1vX7G9dcX2t6w+EQAAgMXbU+D1GrYBAADYT+zpM3jHVNUFSWrV9n3nMhkAAADrsqfAe8DcpgAAAGCf7TbwuvuT8xwEAACAfbOW78EDAADgACDwAAAABrHXwKuqI6vqlVV1QVVtqaonzmMwAAAA1mctK3jnJfnlJLfr7q8medhsRwIAAGAj1nSJZnd/YNaDAAAAsG/WEnh/W1U/leT2VfWYJH8z45kAAADYgLUE3mOTfDXJVUkOTvLfZjoRAAAAG7Lb78GrqsNW7L56+pckt09y455etKruluSNSe6V6bN7VfWiJFuTXNPdT5zO+1fHAAAA2Jg9reC9Lslrk1yU5GNZDraPJXnrGl73xiRnJLkySarq/lkOvVOSfFNVHb+rYxv/NQAAANht4HX3ad19epKPJjmyux+c5MgkH9nbi3b3zd39uRWHHpTkbdP2RUlO3M0xAAAANmgtn8E7MskXpu0vJDlqA+9zhxWvcdO0v6tju1VV26pqe1Vt37FjxwZGAAAAGNtaAu8VSa6oqt9NcnmSV23gfW5Kcui0fWiSz+/m2G5193ndvbW7ty4tLW1gBAAAgLHtNfC6+9eTnJ3k95M8rLtftIH3uSLLn8lLkjOz/Nm8XR0DAABgg9b6Rec7uvvd3b2mayOr6uCquijJsUnekuWvV7i5qi5N8rXpta5ZfWyDvwMAAADZw9ck7IvuviXLq3IrXbWL83w1AgAAwK1kTSt4AAAA7P/2GnhV9aCqurKq3l9VV1SVrzMAAADYD63lEs3/leQR3f33VXX3JH+S5KTZjgUAAMB6rfUmK38//fy72Y4DAADARq1lBe/iqjo/yzdJeVCSi2c5EAAAABuzx8CrqtskuSzJa5PcI8mF3f3eeQwGAADA+uwx8Lr761X1uO5+eJL3zWckAAAANmItl2jeWFW/muSKJF9Lku7+s5lOBQAAwLqtJfA+Mf281/Szkwg8AACA/cxeA6+7n1NV90hy9yTvSHLYzKcCAABg3fYaeFX180mOSfJdSR6Q5BVJfmDGcwEAALBOa/kevDO7+5FJburuTnLwjGcCAABgA9b0RedV9c1Juqpum6RmOxIAAAAbsZabrPxCkncmOTLJ25P891kOBAAAwMas5SYr70jywKq6U3d/Zg4zAQAAsAFrucnKcUkem+Swqqok6e4fnvFcAAAArNNaLtF8WZInJfnUbEcBAABgX6zpi867++0znwQAAIB9sqYVvKranuTaJJ0k3f0TM50KAACAdVvrXTSfFZdoAgAA7NfWEnh/1d0XzHwSAAAA9slaAu/QqrokyfvzL5do/vRMpwIAAGDd1hJ4vzTzKQAAANhnawm8nvkUAAAA7LPdBl5VHd/dVyd55M5DSY5N8rkkl8xhNgAAANZhTyt4j6yq23b3E3YeqKpK8qrZjwUAAMB67TbwuvvpVXWnqjpsxeHvSHLv2Y8FAADAeu3xM3jd/ZmqesfO3SQ3JnnCHp4CAADAguz1Jivdfdo8BgEAAGDf7OkmK3+eXd9Bs7v74bMbCQAAgI3Y0wreT63af2CSn0vyiZlNAwAAwIbt6SYrn0ySqjolyTOTfCHJf+nu989pNgAAANZhT5donpnkGUk+leTJ3f2RuU0FAADAuu3pEs23Jvlwki8leeHyV+At6+6HzXguAAAA1mlPgfedc5sCAACAfbbXz+ABAABwYLjNogcAAADg1iHwAAAABiHwAAAABiHwAAAABiHwAAAABiHwAAAABiHwAAAABiHwAAAABiHwAAAABjG3wKuqs6rq4unf9VV1TlXdtOLYYfOaBQAAYERb5vVG3X1hkguTpKquSnJRkmu7+9R5zQAAADCyuV+iWVVHJvl0d38xydFVdWlVPa+qat6zAAAAjGQRn8H7oSSvn7aPSvKQJHdMcvYCZgEAABjG3C7RXOHsLEdeuvvGJKmq85PcL8kFu3tSVW1Lsi1JDj/88H0a4PTz/nyfns+Y3r7Nf2MAAODANtcVvKq6a5KvdPdnq+qQqjpoeuikJNft6bndfV53b+3urUtLSzOfFQAA4EAz7xW8hyd5w7R9VJI/qKovJvl4kmfPeRYAYADHP+4Zix6B/dDVL3neokeAhZhr4HX3767Yfl+S+8/z/QEAAEbmi84BAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGIfAAAAAGsWXRAwAAwKju96OPXvQI7Ife+ye/N7PXtoIHAAAwCIEHAAAwCIEHAAAwCIEHAAAwCIEHAAAwCIEHAAAwCIEHAAAwCIEHAAAwCIEHAAAwCIEHAAAwCIEHAAAwCIEHAAAwCIEHAAAwCIEHAAAwCIEHAAAwCIEHAAAwCIEHAAAwiLkFXlUdUVWfrqqLq+qt07GnVdVlVfWKqjp4XrMAAACMaN4reG/r7lO7+/ur6s5JTuvuk5P8ZZJz5jwLAADAUOYdeKdV1aVV9eQkW5NcPB2/KMmJc54FAABgKFvm+F7XJ7lHki8neUOS2yf5h+mxm5LcYU9PrqptSbYlyeGHHz6zIQEAAA5Uc1vB6+4vd/c/dfdXk7wxyXVJDp0ePjTJ5/fy/PO6e2t3b11aWprtsAAAAAeged5k5fYrdk9K8tdJvnfaPzPJlfOaBQAAYETz/AzeKVX1nqq6PMmnuvuqJJdU1WVJjkty/hxnAQAAGM7cPoPX3W9K8qZVx56f5PnzmgEAAGBkvugcAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEAIPAABgEHMLvKo6oaour6rLqupF07Gbquri6d9h85oFAABgRFvm+F6fTHJ6d99cVa+oqvsmuba7T53jDAAAAMOa2wped9/Q3TdPu7ck+VqSo6vq0qp6XlXVvGYBAAAY0dw/g1dVxyRZ6u4PJTkqyUOS3DHJ2Xt53raq2l5V23fs2DGHSQEAAA4scw286XN2L05ybpJ0943d3UnOT3KfPT23u8/r7q3dvXVpaWnmswIAABxo5nmTlS1JXp7kqd19Q1UdUlUHTQ+flOS6ec0CAAAwonmu4D0yyfFJXlBVFyc5JsnVVXVJkrsnee0cZwEAABjO3O6i2d2vSvKqVYfvP6/3BwAAGJ0vOgcAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABiEwAMAABjEwgOvql5UVZdW1a8vehYAAIAD2UIDr6run+R23X1Kkm+qquMXOQ8AAMCBbNEreA9K8rZp+6IkJy5wFgAAgANadffi3rzqWUmu6e4Lq+rMJA/u7ufu5txtSbZNu/dM8pE5jTm6OyX5zKKHgN3w98n+yt8m+yt/m+zP/H3euv5tdy+tPrhlEZOscFOSQ6ftQ5N8fncndvd5Sc6bw0ybSlVt7+6ti54DdsXfJ/srf5vsr/xtsj/z9zkfi75E84okZ0zbZya5coGzAAAAHNAWGnjdfU2Sm6vq0iRf6+53L3IeAACAA9miL9FMdz9x0TNsci57ZX/m75P9lb9N9lf+Ntmf+fucg4XeZAUAAIBbz6I/gwcAAMCtROABAAAMQuABAAAMQuBtYlV1t6q6pqpurqqF33AHdqqqE6rq8qq6rKpetOh5YKequs/0t3lpVf1hVdWiZ4KVqurJVXXZoueAlarqiKr6dFVdXFVvXfQ8oxN4m9uNWf4eQt8/yP7mk0lO7+6Tk9y5qu676IFg8pHufnB3nzLt+8Je9htVddskxy16DtiNt3X3qd39/YseZHQCbxPr7pu7+3OLngNW6+4buvvmafeWJF9b5DywU3ffsmL3y0n+blGzwC6cm+T/LHoI2I3TpqsfnrzoQUYn8ID9VlUdk2Spuz+06Flgp6p6WFV9IMldknx20fNAklTVwUlO7e63L3oW2IXrk9wjyWlJzpz+/50ZEXjAfqmqDkvy4iz/F2nYb3T3Bd19nyR/n+Shi54HJj+e5JWLHgJ2pbu/3N3/1N1fTfLGJPdZ9EwjE3jAfme66c/Lkzy1u29Y9Dyw0/QZp52+kORLi5oFVrlnksdV1YVJ7l1VT1j0QLBTVd1+xe5JSa5b1CybgcDbxKrq4Kq6KMmxSd5SVScseiaYPDLJ8UleMN1x68RFDwSTs6rqnVX1zixfoulucOwXuvtnu/sHuvusJB/s7t9c9EywwilV9Z6qujzJp7r7qkUPNLLq7kXPAAAAwK3ACh4AAMAgBB4AAMAgBB4AAMAgBB4AAMAgBB4AAMAgBB4Am1ZVHVFVn52+juPdVfUf1/n8s6rqR6btH62q11XVY6vqiJkMDAB7IfAA2Oyu6O5Tk5ya5LnreWJ3X9jdfzrt3iHJzyW5Z3d/4lacDwDWTOABwLJDk3xTVd2xql5bVW+vqjdV1VKSVNWvVdXlVfWSqvqr6dijquqp0/MfnOR3svyFvg+aHv+e6YvRL62ql1ZVLeIXA2DzEHgAbHYnVtUlST6W5FlJnpHkZd19epKXJHlKVT0gyRHd/eAkL0xy1128zuOmlcAfS/Ls6djzkzylu09JckuSh830NwFg09uy6AEAYMGu6O6HVtUZSR6V5LAkJ1fVU7L8/5MfTHJUkquTpLv/pqo+s/IFquqgJP+zqo5P8tUk3zY99J3d/Z5p+/Ik95z1LwPA5mYFDwCSdPdfJLlzkn9O8tzuPrW7T07yhCR/neQByfKNWZLcadXTj83yCt/JSX4yyc5LMT8xrf4ly5dwfnSmvwQAm54VPAD4F7+Z5DFJzq2qp2f5P4S+tLtfWVWfqqrLk1yb5PpVz/tIksOq6u1J3rXi+DOS/O700buPJrlg1r8AAJtbdfeiZwCA/V5VHdzdt1TVkUlePn0eDwD2K1bwAGBtfrOqjk5ySJKnLHoYANgVK3gAAACDcJMVAACAQQg8AACAQQg8AACAQQg8AACAQQg8AACAQQg8AACAQfx/kCWOlIp51RAAAAAASUVORK5CYII=",
      "text/plain": [
       "<Figure size 1080x576 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig, ax = plt.subplots(figsize=(15, 8), sharey='row')\n",
    "\n",
    "# Escolas que oferecem tudo - V\n",
    "v = df.loc[df['num_profmedio'].notna() & df['num_profcreche'].notna() &\n",
    "           df['num_profpreescola'].notna() & df['num_proffundamental'].notna()]\n",
    "v = v.pivot_table(index='co_regiao', values='codigo_escola', aggfunc='count')\n",
    "\n",
    "sns.barplot(x=v.index, y='codigo_escola', data=v, orient='v', ax=ax)\n",
    "\n",
    "ax.set_title('Oferta Completa - V')\n",
    "ax.set_ylabel('Número de Escolas')\n",
    "ax.set_xlabel('Região')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "\n",
    "## Modelos de Regressão\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Modelo 01\n",
    "\n",
    "Como abordagem inicial, estimaremos o modelo:\n",
    "\n",
    "$$\\text{final-mat} = \\beta_0 + \\beta_1 \\text{salas} + \\beta_2 \\text{professores} + \\beta_3 \\text{funcionarios} + \\beta_4 \\text{rural} + u_i$$\n",
    "\n",
    "Onde as variáveis \"salas\", \"professores\" e \"funcionários\" indicam as respectivas quantidades para cada escola, e a variável \"rural\" é uma dummy que assume o valor $1$ quando a escola está situada na área rural e $0$ caso a escola esteja na região urbana. Além disso, é esperado que os sinais das variáveis \"salas\", \"professores\" e \"funcionários\" sejam positivos, indicando que mais recursos à disposição dos alunos - tanto físicos quanto humanos - impactem positivamente o seu desempenho escolar. Já para a variável \"rural\", é esperado que seu sinal seja negativo, seguindo a percepção difundida no senso comum de que moradores de áreas rurais tendem a ter mais dificuldade no acesso à educação."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                  Modelo 01                                   \n",
      "==============================================================================\n",
      "Dep. Variable:              final_mat   R-squared:                       0.074\n",
      "Model:                            OLS   Adj. R-squared:                  0.074\n",
      "Method:                 Least Squares   F-statistic:                     387.1\n",
      "Date:                Wed, 06 Jul 2022   Prob (F-statistic):               0.00\n",
      "Time:                        21:43:49   Log-Likelihood:            -1.1083e+05\n",
      "No. Observations:               24425   AIC:                         2.217e+05\n",
      "Df Residuals:                   24420   BIC:                         2.217e+05\n",
      "Df Model:                           4                                         \n",
      "Covariance Type:                  HC1                                         \n",
      "=======================================================================================\n",
      "                          coef    std err          z      P>|z|      [0.025      0.975]\n",
      "---------------------------------------------------------------------------------------\n",
      "const                 250.4236      0.469    533.926      0.000     249.504     251.343\n",
      "nu_salas_utilizadas     0.3304      0.048      6.914      0.000       0.237       0.424\n",
      "num_proffundamental     0.0126      0.004      3.036      0.002       0.004       0.021\n",
      "nu_funcionarios        -0.0060      0.013     -0.463      0.644      -0.031       0.019\n",
      "rural                 -11.9353      0.418    -28.521      0.000     -12.756     -11.115\n",
      "==============================================================================\n",
      "Omnibus:                     1038.315   Durbin-Watson:                   1.134\n",
      "Prob(Omnibus):                  0.000   Jarque-Bera (JB):             1408.980\n",
      "Skew:                           0.430   Prob(JB):                    1.11e-306\n",
      "Kurtosis:                       3.803   Cond. No.                         403.\n",
      "==============================================================================\n",
      "\n",
      "Notes:\n",
      "[1] Standard Errors are heteroscedasticity robust (HC1)\n"
     ]
    }
   ],
   "source": [
    "X = df1[['nu_salas_utilizadas', 'num_proffundamental', 'nu_funcionarios', 'rural']]\n",
    "X = sm.add_constant(X)\n",
    "\n",
    "Y = df1['final_mat']\n",
    "\n",
    "model1 = sm.OLS(Y, X, missing='drop')\n",
    "results1 = model1.fit(cov_type='HC1')\n",
    "\n",
    "summ1 = results1.summary(title='Modelo 01')\n",
    "\n",
    "summ1_slim = results1.summary(title='Modelo 03', slim=True)\n",
    "\n",
    "print(summ1)\n",
    "\n",
    "with open('model_1.txt', 'w') as file:\n",
    "    file.write(summ1.as_text())\n",
    "    file.write('\\n\\n\\n\\n\\n\\n\\n')\n",
    "    file.write(summ1_slim.as_text())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Começando pelo termo de erro, o p-valor do teste de Jarque-Bera indica sucesso ao rejeitar a hipótese nula de que os erros do modelo seguem uma distribuição normal. Apesar disso, seguiremos com a interpretação geral do modelo e o teste de hipóteses. \n",
    "\n",
    "Os resultados do primeiro modelo confirmam, em grande parte, as nossas suposições feitas *a priori*. Para este modelo, uma sala a mais na escola está relacionada, em média, com $0,3304$ pontos adicionados à nota na seção de matemática do SAEB de um aluno do ensino fundamental II. De forma análoga, um professor a mais dedicado ao ensino fundamental está relacionado com um aumento de $0,0126$ pontos na mesma nota. Os resultados para ambas as variáveis são estatisticamente significantes ao nível de 5%. \n",
    "\n",
    "Uma ressalva deve ser feita ao efeito do número de funcionários da escola: os resultados dizem que um membro a mais no *staff* da escola diminui a nota dos alunos na seção de matemática do SAEB em $0,006$, entretanto, esse resultado não é estatisticamente diferente de zero com uma significância de 5%.\n",
    "\n",
    "Adicionalmente, é possível ver que escolas em áreas rurais realmente possuem uma desvantagem grande em relação às escolas em zonas urbanas. Segundo os resultados da regressão, os alunos de escolas em zonas rurais tem uma nota, em média, $11,9$ pontos menor.\n",
    "\n",
    "Por fim, conforme a medida de ajuste $R^2$, este primeiro modelo explica 7,4% da variação dos dados e, conforme a estatística F, as variáveis da regressão são estatisticamente diferentes de zero quando consideradas de forma conjunta. Além disso, é importante notar que os desvios padrão do modelo foram ajustados para heterocedasticidade seguindo o proposto por White."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Modelo 02\n",
    "\n",
    "Tendo em vista os resultados acima, iremos estimar o próximo modelo:\n",
    "\n",
    "$$\\text{inicial-mat} = \\beta_0 + \\beta_1 \\text{salas} + \\beta_2 \\text{professores} + \\beta_3 \\text{matriculas} + \\beta_4 \\text{rural} + \\beta_5 \\text{espaço-escola} + u_i$$\n",
    "\n",
    "Onde a variável \"matrículas\" indica o número de alunos matriculados naquela escola da 1º à 4º série do ensino fundamental. Já a variável \"``espaço-escola``\" indica a completude do espaço escolar, isto é, se ele oferece laboratórios (informática e ciências), quadra, cozinha, biblioteca, sala de leitura, Internet e alimentação, sendo o número 8 atribuído para a escola que oferece todos esses recursos e 0 para a escola que não oferece nenhum deles. As variáveis trazidas do modelo anterior mantêm seus significados.\n",
    "\n",
    "É esperado que a variável \"matrículas\" apresente um sinal negativo, indicando que escolas super-lotadas diminuem o desempenho acadêmico dos alunos. Já para a variável \"``espaço-escola``\", espera-se que seu sinal seja positivo, sinalizando que espaços mais completos influenciam positivamente o desempenho dos alunos. O sinal esperado das outras variáveis permanece o mesmo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                  Modelo 02                                   \n",
      "==============================================================================\n",
      "Dep. Variable:            inicial_mat   R-squared:                       0.171\n",
      "Model:                            OLS   Adj. R-squared:                  0.171\n",
      "Method:                 Least Squares   F-statistic:                     1627.\n",
      "Date:                Wed, 06 Jul 2022   Prob (F-statistic):               0.00\n",
      "Time:                        21:43:45   Log-Likelihood:            -1.8385e+05\n",
      "No. Observations:               39944   AIC:                         3.677e+05\n",
      "Df Residuals:                   39938   BIC:                         3.678e+05\n",
      "Df Model:                           5                                         \n",
      "Covariance Type:                  HC1                                         \n",
      "=======================================================================================\n",
      "                          coef    std err          z      P>|z|      [0.025      0.975]\n",
      "---------------------------------------------------------------------------------------\n",
      "const                 191.9743      0.507    378.428      0.000     190.980     192.969\n",
      "nu_salas_utilizadas     0.0393      0.025      1.600      0.110      -0.009       0.087\n",
      "num_proffundamental    -0.0115      0.003     -3.909      0.000      -0.017      -0.006\n",
      "num_matri_fund_1a4     -0.1789      0.163     -1.096      0.273      -0.499       0.141\n",
      "rural                 -10.9903      0.331    -33.217      0.000     -11.639     -10.342\n",
      "com_escola_1            5.9185      0.101     58.313      0.000       5.720       6.117\n",
      "==============================================================================\n",
      "Omnibus:                     1254.077   Durbin-Watson:                   1.076\n",
      "Prob(Omnibus):                  0.000   Jarque-Bera (JB):             1436.251\n",
      "Skew:                           0.411   Prob(JB):                         0.00\n",
      "Kurtosis:                       3.434   Cond. No.                         354.\n",
      "==============================================================================\n",
      "\n",
      "Notes:\n",
      "[1] Standard Errors are heteroscedasticity robust (HC1)\n"
     ]
    }
   ],
   "source": [
    "X = df2[['nu_salas_utilizadas', 'num_proffundamental', 'num_matri_fund_1a4', 'rural', 'com_escola_1']]\n",
    "X = sm.add_constant(X)\n",
    "\n",
    "Y = df2['inicial_mat']\n",
    "\n",
    "model2 = sm.OLS(Y, X, missing='drop')\n",
    "results2 = model2.fit(cov_type='HC1')\n",
    "\n",
    "summ2 = results2.summary(title='Modelo 02', slim=False)\n",
    "\n",
    "summ2_slim = results2.summary(title='Modelo 03', slim=True)\n",
    "\n",
    "print(summ2)\n",
    "\n",
    "with open('model_2.txt', 'w') as file:\n",
    "    file.write(summ2.as_text())\n",
    "    file.write('\\n\\n\\n\\n\\n\\n\\n')\n",
    "    file.write(summ2_slim.as_text())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Os resultados acima indicam algumas diferenças com o primeiro modelo estimado. Notavelmente, a variável \"``num_proffundamental``\" mudou de sinal, divergindo da intuição delineada *à priori*, e a variável \"``nu_salas_utilizadas``\" deixou de ser estatisticamente significante ao nível de 5%. De forma geral, este segundo modelo apresenta um ajuste melhor aos dados, explicando 17% da variação da variável dependente, mas continua desrespeitando a hipótese da normalidade do termo de erro, da mesma forma que o modelo anterior.\n",
    "\n",
    "Quanto aos outros resultados e às novas variáveis, pode-se ver que uma escola mais completa impacta positivamente a nota na seção de matemática do SAEB de um aluno do ensino fundamental I. Mas, apesar do resultado estatisticamente significante, devemos notar que essa variável atribui o mesmo peso relativo a todos os sub-indicadores que entram em seu cálculo (\"``in_laboratorio_informatica``\", \"``in_laboratorio_ciencias``\", \"``in_quadra_esportes``\", etc), o que pode não ser verdade em todas as situações. Por exemplo, é razoável presumir que a presença de uma biblioteca no espaço escolar contribua mais para o desempenho de um aluno do que a presença de uma quadra de esportes.\n",
    "\n",
    "Adicionalmente, os resultados da regressão indicam que uma matrícula a mais nas turmas do ensino fundamental I diminui, em média, $0,17$ ponto no resultado do SAEB de um aluno. Entretanto, esse resultado não é estatisticamente significante ao nível de 5%.\n",
    "\n",
    "Por fim, os comentários para os erros padrões robustos, a estatística F e para a variável \"rural\" permanecem os mesmos, salvo a mudança de magnitude no coeficiente da variável."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Modelo 03\n",
    "\n",
    "Por último, iremos estimar o modelo:\n",
    "\n",
    "$$\\text{inicial-pt} = \\beta_0 + \\beta_1 \\text{matri/prof} + \\beta_2 \\text{matri/sala} + \\beta_3 \\text{rural} + \\beta_4 \\text{espaço-escola} + \\beta_5 \\text{biblioteca} + \\beta_6 \\text{leitura} + u_i$$\n",
    "\n",
    "Diferentemente dos anteriores, a lógica dessa forma funcional não parte da observação em nível do capital físico e humano das escolas, mas sim da sua situação relativa ao número de alunos da escola e como ela impacta o desempenho acadêmico dos alunos do ensino fundamental I. Posto isso, as variáveis \"``matrícula/prof``\" e \"``matrícula/sala``\" indicam o número de alunos para cada professor e o número de alunos em cada sala, respectivamente. As variáveis \"``biblioteca``\" e \"``sala-leitura``\" são *dummies* que indicam, respectivamente, a presença de uma biblioteca e uma sala de leitura na escola. É importante notar, entretanto, que as variáveis \"``biblioteca``\" e \"``sala-leitura``\" foram retiradas do cálculo da variável \"``espaço-escola``\" para que não incorrêssemos em multicolineariedade. Com isso, o valor atribuído às escolas mais completas passa a ser 6.\n",
    "\n",
    "Também em contraste aos modelos anteriores, o sinal esperado das variáveis deste modelo é negativo, exceção feita as variáveis \"``biblioteca``\", \"``leitura``\" e \"``espaço-escola``\". Esperar que o sinal das outras variáveis do modelo seja negativo reflete a intuição de que um maior número de alunos para cada professor e sala indica uma sobrecarga dos docentes e, talvez, a precariedade do espaço escolar. Esses fatores, em última instância, teriam efeito negativo no desempenho acadêmico dos alunos.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                  Modelo 03                                   \n",
      "==============================================================================\n",
      "Dep. Variable:             inicial_pt   R-squared:                       0.204\n",
      "Model:                            OLS   Adj. R-squared:                  0.204\n",
      "Method:                 Least Squares   F-statistic:                     1632.\n",
      "Date:                Wed, 06 Jul 2022   Prob (F-statistic):               0.00\n",
      "Time:                        21:43:40   Log-Likelihood:            -1.8133e+05\n",
      "No. Observations:               39944   AIC:                         3.627e+05\n",
      "Df Residuals:                   39937   BIC:                         3.627e+05\n",
      "Df Model:                           6                                         \n",
      "Covariance Type:                  HC1                                         \n",
      "===================================================================================\n",
      "                      coef    std err          z      P>|z|      [0.025      0.975]\n",
      "-----------------------------------------------------------------------------------\n",
      "const             187.1190      0.413    453.426      0.000     186.310     187.928\n",
      "matri/prof         -2.8782      2.321     -1.240      0.215      -7.428       1.671\n",
      "matri/salas        -0.0922      1.172     -0.079      0.937      -2.390       2.205\n",
      "rural             -12.4252      0.313    -39.662      0.000     -13.039     -11.811\n",
      "com_escola_2        7.0949      0.123     57.646      0.000       6.854       7.336\n",
      "in_biblioteca       2.6456      0.258     10.256      0.000       2.140       3.151\n",
      "in_sala_leitura     2.1871      0.266      8.229      0.000       1.666       2.708\n",
      "==============================================================================\n",
      "Omnibus:                       91.771   Durbin-Watson:                   1.173\n",
      "Prob(Omnibus):                  0.000   Jarque-Bera (JB):               92.373\n",
      "Skew:                           0.116   Prob(JB):                     8.74e-21\n",
      "Kurtosis:                       3.039   Cond. No.                         76.6\n",
      "==============================================================================\n",
      "\n",
      "Notes:\n",
      "[1] Standard Errors are heteroscedasticity robust (HC1)\n"
     ]
    }
   ],
   "source": [
    "X = df2[['matri/prof', 'matri/salas', 'rural', 'com_escola_2', 'in_biblioteca', 'in_sala_leitura']]\n",
    "X = sm.add_constant(X)\n",
    "\n",
    "Y = df2['inicial_pt']\n",
    "\n",
    "model3 = sm.OLS(Y, X, missing='drop')\n",
    "results3 = model3.fit(cov_type='HC1')\n",
    "\n",
    "summ3 = results3.summary(title='Modelo 03')\n",
    "summ3_slim = results3.summary(title='Modelo 03', slim=True)\n",
    "\n",
    "print(summ3)\n",
    "\n",
    "with open('model_3.txt', 'w') as file:\n",
    "    file.write(summ3.as_text())\n",
    "    file.write('\\n\\n\\n\\n\\n\\n\\n')\n",
    "    file.write(summ3_slim.as_text())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Os resultados do terceiro e último modelo, repetem os resultados para o teste de Jarque-Bera e para a estatistica F obtidos no primeiro modelo. Observando a tabela de resumo, é possível ver que o ajuste deste modelo ($R^2$) é ligeiramente maior do que o do modelo anterior, entretanto, é possível que isso reflita a característica não decrescente do $R^2$, e não a efetividade do modelo proposto em explicar as mudanças da variável dependente.\n",
    "\n",
    "Posto isso, os coeficientes das variáveis \"``matri/prof``\" e \"``matri/sala``\" seguem a intuição definida anteriormente, entretanto, esses mesmos coeficientes falham em rejeitar a hipótese nula ao nível de 5% de significância. Um dos fatores por trás desse resultado pode residir em uma limitação da base de dados, que não discrimina o número de professores e salas dedicadas ao ensino fundamental I. Com isso, o cálculo dessas variáveis foi feito utilizando a quantidade total de professores e salas destinada às duas etapas do ensino fundamental, o que, provavelmente, leva a subestimação das razões \"``matri/prof``\" e \"``matri/sala``\". Em contrapartida, as variáveis \"``biblioteca``\" e \"``leitura``\" seguem a intuição proposta anteriormente e são estatisticamente significativas, indicando que a presença desses ambientes nas escolas leva a um aumento de cerca de 2 pontos na nota da seção de português do SAEB dos alunos do ensino fundamental I."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://www.statsmodels.org/stable/generated/statsmodels.iolib.summary.Summary.add_table_2cols.html#statsmodels.iolib.summary.Summary.add_table_2cols"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.6 64-bit",
   "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.8.6"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "7cfd136509320d90c1cb01fa03d1b7c3436995b65c6c0a2aec6badd76f1b3eb8"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}