analysis.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "9f3e6093",
      "metadata": {},
      "outputs": [],
      "source": [
        "import os\n",
        "import shutil\n",
        "import zipfile\n",
        "import sys\n",
        "import requests\n",
        "import concurrent.futures\n",
        "import urllib3\n",
        "import sqlite3\n",
        "import pandas as pd\n",
        "from prettytable import PrettyTable\n",
        "import seaborn as sb\n",
        "\n",
        "download_dir = './tmp/'\n",
        "data_dir = './data/'\n",
        "extract_dir = './data/raw/'\n",
        "database_dir = './data/database.db'"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "P5hcBRrFVhi0",
      "metadata": {
        "id": "P5hcBRrFVhi0"
      },
      "source": [
        "# Download Data"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "Xp4xpWwy03Ps",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Xp4xpWwy03Ps",
        "outputId": "23ecab44-237a-49b2-f305-cd726ac66d85"
      },
      "outputs": [],
      "source": [
        "# Desabilita os avisos de certificado SSL\n",
        "urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)\n",
        "\n",
        "urls = [\n",
        "  'https://download.inep.gov.br/informacoes_estatisticas/indicadores_educacionais/2019/remuneracao_media_docentes/remuneracao_docentes_brasil_2019.zip',\n",
        "  'https://download.inep.gov.br/informacoes_estatisticas/indicadores_educacionais/2019/remuneracao_media_docentes/remuneracao_docentes_uf_2019.zip',\n",
        "  'https://download.inep.gov.br/informacoes_estatisticas/indicadores_educacionais/2019/remuneracao_media_docentes/remuneracao_docentes_municipios_2019.zip',\n",
        "  'https://download.inep.gov.br/educacao_basica/portal_ideb/planilhas_para_download/2019/divulgacao_brasil_ideb_2019.zip',\n",
        "  'https://download.inep.gov.br/educacao_basica/portal_ideb/planilhas_para_download/2019/divulgacao_regioes_ufs_ideb_2019.zip',\n",
        "  'https://download.inep.gov.br/educacao_basica/portal_ideb/planilhas_para_download/2019/divulgacao_anos_iniciais_municipios_2019.zip',\n",
        "  'https://download.inep.gov.br/educacao_basica/portal_ideb/planilhas_para_download/2019/divulgacao_anos_finais_municipios_2019.zip',\n",
        "  'https://download.inep.gov.br/educacao_basica/portal_ideb/planilhas_para_download/2019/divulgacao_ensino_medio_municipios_2019.zip',\n",
        "  'https://download.inep.gov.br/educacao_basica/portal_ideb/planilhas_para_download/2019/divulgacao_anos_iniciais_escolas_2019.zip',\n",
        "  'https://download.inep.gov.br/educacao_basica/portal_ideb/planilhas_para_download/2019/divulgacao_anos_finais_escolas_2019.zip',\n",
        "  'https://download.inep.gov.br/educacao_basica/portal_ideb/planilhas_para_download/2019/divulgacao_ensino_medio_escolas_2019.zip'\n",
        "]\n",
        "\n",
        "if os.path.exists(download_dir):\n",
        "  shutil.rmtree(download_dir)\n",
        "    \n",
        "os.makedirs(download_dir, exist_ok=True)\n",
        "\n",
        "if os.path.exists(data_dir):\n",
        "  shutil.rmtree(data_dir)\n",
        "    \n",
        "os.makedirs(data_dir, exist_ok=True)\n",
        "\n",
        "if os.path.exists(extract_dir):\n",
        "  shutil.rmtree(extract_dir)\n",
        "    \n",
        "os.makedirs(extract_dir, exist_ok=True)\n",
        "\n",
        "def download_file(url):\n",
        "    filename = os.path.join(download_dir, os.path.basename(url))\n",
        "    if 'google.colab' in sys.modules:\n",
        "      # Se estiver no Google Colab, usa o comando !wget\n",
        "      !wget -P tmp/ --no-check-certificate {url}\n",
        "    else:\n",
        "      with requests.get(url, stream=True, verify=False) as r:\n",
        "        with open(filename, 'wb') as f:\n",
        "          shutil.copyfileobj(r.raw, f)\n",
        "      return filename\n",
        "\n",
        "if __name__ == \"__main__\":\n",
        "  with concurrent.futures.ThreadPoolExecutor() as executor:\n",
        "    future_to_url = {executor.submit(download_file, url): url for url in urls}\n",
        "    for future in concurrent.futures.as_completed(future_to_url):\n",
        "      url = future_to_url[future]\n",
        "      try:\n",
        "        filename = future.result()\n",
        "        print(f\"Downloaded {url} to {filename}\")\n",
        "      except Exception as e:\n",
        "        print(f\"Failed to download {url}: {e}\")\n",
        "\n",
        "  for filename in os.listdir(download_dir):\n",
        "    shutil.move(os.path.join(download_dir, filename), os.path.join(extract_dir, filename))\n",
        "\n",
        "  for filename in os.listdir(extract_dir):\n",
        "    if filename.endswith(\".zip\"):\n",
        "      zip_path = os.path.join(extract_dir, filename)\n",
        "      with zipfile.ZipFile(zip_path, 'r') as zip_ref:\n",
        "        found = False\n",
        "        for file_name in zip_ref.namelist():\n",
        "          if file_name.endswith(\".xlsx\"):\n",
        "            dest_filename = os.path.basename(file_name)\n",
        "            dest_path = os.path.join(extract_dir, dest_filename)\n",
        "            with zip_ref.open(file_name) as zip_file, open(dest_path, 'wb') as extracted_file:\n",
        "              extracted_file.write(zip_file.read())\n",
        "            print(f'File {file_name} extracted to {extract_dir}')\n",
        "            found = True\n",
        "\n",
        "        if not found:\n",
        "          print(f'Error: No .xlsx files found in the ZIP file {zip_path}')\n",
        "\n",
        "      os.remove(zip_path)\n",
        "      print(f'ZIP file {zip_path} removed')\n",
        "    else:\n",
        "      print(f'The file {filename} is not a valid ZIP file')\n"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "zHaiFEOyVn8K",
      "metadata": {
        "id": "zHaiFEOyVn8K"
      },
      "source": [
        "# Database creation"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "ec373ae8",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ec373ae8",
        "outputId": "1e142443-0649-4928-fb2d-563b274f3709"
      },
      "outputs": [],
      "source": [
        "region_column = 'region'\n",
        "state_column = 'UF'\n",
        "city_id_column = 'city_id'\n",
        "city_name_column = 'city_name'\n",
        "adm_dependence_column = 'adm_dependence'\n",
        "education_column = 'teacher_education'\n",
        "teacher_number_column = 'teacher_quantity'\n",
        "rais_teacher_percentage_column = 'rais_teacher_percentage'\n",
        "first_quartile_column = 'first_quartile'\n",
        "median_column ='median'\n",
        "average_column = 'average'\n",
        "third_quartile_column = 'third_quartile',\n",
        "standard_deviation_column = 'standard_deviation'\n",
        "weekly_workload_column ='weekly_workload'\n",
        "standardized_pay_40_hours_column ='standardized_pay_40_hours'\n",
        "\n",
        "state_or_region = 'state_or_region'\n",
        "\n",
        "school_id_column = 'school_id'\n",
        "school_name_column = 'school_name'\n",
        "school_type_column = 'school_type'\n",
        "\n",
        "first_to_fifth_grade_results_column = '1st_to_5th_grade_results'\n",
        "first_grade_results_column = '1st_grade_results'\n",
        "second_grade_results_column = '2nd_grade_results'\n",
        "third_grade_results_column = '3rd_grade_results'\n",
        "fourth_grade_results_column = '4th_grade_results'\n",
        "fifth_grade_results_column = '5th_grade_results'\n",
        "\n",
        "sixth_to_nineth_grade_results_column = '6th_to_9th_grade_results'\n",
        "sixth_grade_results_column = '6th_grade_results'\n",
        "seventh_grade_results_column = '7th_grade_results'\n",
        "eighth_grade_results_column = '8th_grade_results'\n",
        "ninth_grade_results_column = '9th_grade_results'\n",
        "\n",
        "first_to_third_high_grade_results_column = '1st_to_3rd_grade_results'\n",
        "first_high_grade_results_column = '1st_grade_results'\n",
        "second_high_grade_results_column = '2nd_grade_results'\n",
        "third_high_grade_results_column = '3rd_grade_results'\n",
        "\n",
        "yield_indicator_column = 'yield_indicator' # P\n",
        "math_results_column = 'math_results'\n",
        "portuguese_results_column = 'portuguese_results'\n",
        "standardized_average_column = 'standardized_average' # N\n",
        "ideb_column = 'IDEB_N_x_P'\n",
        "\n",
        "\n",
        "# Verificando os arquivos extraídos\n",
        "extracted_files = os.listdir(extract_dir)\n",
        "extracted_files = sorted(extracted_files)\n",
        "\n",
        "# Conectar ao banco de dados SQLite (isso criará o arquivo exemplo.db se não existir)\n",
        "conn = sqlite3.connect(database_dir)\n",
        "\n",
        "\n",
        "columns = [\n",
        "\n",
        "    [\n",
        "        adm_dependence_column,\n",
        "        education_column, teacher_number_column, rais_teacher_percentage_column, first_quartile_column,\n",
        "        median_column, average_column, third_quartile_column, standard_deviation_column, weekly_workload_column,\n",
        "        standardized_pay_40_hours_column\n",
        "    ], # Brasil columns\n",
        "\n",
        "    [\n",
        "        region_column, state_column, city_id_column, city_name_column, adm_dependence_column,\n",
        "        education_column, teacher_number_column, rais_teacher_percentage_column, first_quartile_column,\n",
        "        median_column, average_column, third_quartile_column, standard_deviation_column, weekly_workload_column,\n",
        "        standardized_pay_40_hours_column\n",
        "    ], # Municipios columns\n",
        "\n",
        "    [\n",
        "        region_column, state_column, adm_dependence_column,\n",
        "        education_column, teacher_number_column, rais_teacher_percentage_column, first_quartile_column,\n",
        "        median_column, average_column, third_quartile_column, standard_deviation_column, weekly_workload_column,\n",
        "        standardized_pay_40_hours_column\n",
        "    ], # UFs columns\n",
        "\n",
        "    [\n",
        "        state_column, city_id_column, city_name_column,\n",
        "        school_id_column, school_name_column, school_type_column, sixth_to_nineth_grade_results_column, sixth_grade_results_column,\n",
        "        seventh_grade_results_column, eighth_grade_results_column, ninth_grade_results_column, yield_indicator_column,\n",
        "        math_results_column, portuguese_results_column, standardized_average_column, ideb_column\n",
        "    ],\n",
        "    [\n",
        "        state_column, city_id_column, city_name_column,\n",
        "        school_type_column, sixth_to_nineth_grade_results_column, sixth_grade_results_column,\n",
        "        seventh_grade_results_column, eighth_grade_results_column, ninth_grade_results_column, yield_indicator_column,\n",
        "        math_results_column, portuguese_results_column, standardized_average_column, ideb_column\n",
        "    ],\n",
        "\n",
        "    [\n",
        "        state_column, city_id_column, city_name_column, school_id_column, school_name_column,\n",
        "        school_type_column, first_to_fifth_grade_results_column, first_grade_results_column, second_grade_results_column,\n",
        "        third_grade_results_column, fourth_grade_results_column, fifth_grade_results_column, yield_indicator_column,\n",
        "        math_results_column, portuguese_results_column, standardized_average_column, ideb_column\n",
        "    ],\n",
        "    [\n",
        "        state_column, city_id_column, city_name_column,\n",
        "        school_type_column, first_to_fifth_grade_results_column, first_grade_results_column, second_grade_results_column,\n",
        "        third_grade_results_column, fourth_grade_results_column, fifth_grade_results_column, yield_indicator_column,\n",
        "        math_results_column, portuguese_results_column, standardized_average_column, ideb_column\n",
        "    ],\n",
        "    [\n",
        "        school_type_column, first_to_fifth_grade_results_column, first_grade_results_column, second_grade_results_column,\n",
        "        third_grade_results_column, fourth_grade_results_column, fifth_grade_results_column, yield_indicator_column,\n",
        "        math_results_column, portuguese_results_column, standardized_average_column, ideb_column\n",
        "    ],\n",
        "    [\n",
        "        state_column, city_id_column, city_name_column,\n",
        "        school_id_column, school_name_column, school_type_column,\n",
        "        first_to_third_high_grade_results_column, first_high_grade_results_column,\n",
        "        second_high_grade_results_column, third_high_grade_results_column, yield_indicator_column,\n",
        "        math_results_column, portuguese_results_column, standardized_average_column, ideb_column\n",
        "    ],\n",
        "    [\n",
        "        state_column, city_id_column, city_name_column, school_type_column,\n",
        "        first_to_third_high_grade_results_column, first_high_grade_results_column,\n",
        "        second_high_grade_results_column, third_high_grade_results_column, yield_indicator_column,\n",
        "        math_results_column, portuguese_results_column, standardized_average_column, ideb_column\n",
        "    ],\n",
        "    [\n",
        "        state_or_region, school_type_column,\n",
        "        first_to_fifth_grade_results_column, first_grade_results_column, second_grade_results_column,\n",
        "        third_grade_results_column, fourth_grade_results_column, fifth_grade_results_column, yield_indicator_column,\n",
        "        math_results_column, portuguese_results_column, standardized_average_column, ideb_column\n",
        "    ]\n",
        "]\n",
        "\n",
        "primary_keys = [\n",
        "\n",
        "    [adm_dependence_column, education_column],\n",
        "    [city_id_column, adm_dependence_column, education_column],\n",
        "    [state_column, adm_dependence_column, education_column],\n",
        "    [school_id_column],\n",
        "    [city_id_column, school_type_column],\n",
        "    [school_id_column],\n",
        "    [city_id_column, school_type_column],\n",
        "    [school_type_column],\n",
        "    [school_id_column],\n",
        "    [city_id_column, school_type_column],\n",
        "    [state_or_region, school_type_column]\n",
        "\n",
        "]\n",
        "\n",
        "dtype = [\n",
        "\n",
        "    {\n",
        "        adm_dependence_column: 'string',\n",
        "        education_column: 'string',\n",
        "        teacher_number_column: 'int32',\n",
        "        rais_teacher_percentage_column: 'float32',\n",
        "        first_quartile_column: 'float32',\n",
        "        median_column: 'float32',\n",
        "        average_column: 'float32',\n",
        "        third_quartile_column: 'float32',\n",
        "        standard_deviation_column: 'float32' ,\n",
        "        weekly_workload_column: 'float32',\n",
        "        standardized_pay_40_hours_column: 'float32',\n",
        "    },\n",
        "\n",
        "    {\n",
        "\n",
        "        region_column: 'string',\n",
        "        state_column: 'string',\n",
        "        city_id_column: 'int32',\n",
        "        city_name_column: 'string',\n",
        "        adm_dependence_column: 'string',\n",
        "        education_column: 'string',\n",
        "        teacher_number_column: 'int32',\n",
        "        rais_teacher_percentage_column: 'float32',\n",
        "        first_quartile_column: 'float32',\n",
        "        median_column: 'float32',\n",
        "        average_column: 'float32',\n",
        "        third_quartile_column: 'float32',\n",
        "        standard_deviation_column: 'float32' ,\n",
        "        weekly_workload_column: 'float32',\n",
        "        standardized_pay_40_hours_column: 'float32',\n",
        "    },\n",
        "\n",
        "    {\n",
        "        region_column: 'string',\n",
        "        state_column: 'string',\n",
        "        adm_dependence_column: 'string',\n",
        "        education_column: 'string',\n",
        "        teacher_number_column: 'int32',\n",
        "        rais_teacher_percentage_column: 'float32',\n",
        "        first_quartile_column: 'float32',\n",
        "        median_column: 'float32',\n",
        "        average_column: 'float32',\n",
        "        third_quartile_column: 'float32',\n",
        "        standard_deviation_column: 'float32' ,\n",
        "        weekly_workload_column: 'float32',\n",
        "        standardized_pay_40_hours_column: 'float32',\n",
        "\n",
        "    },\n",
        "\n",
        "    {\n",
        "\n",
        "        state_column: 'string',\n",
        "        city_id_column: 'int32',\n",
        "        city_name_column: 'string',\n",
        "        school_id_column: 'int32',\n",
        "        school_name_column: 'string',\n",
        "        school_type_column: 'string',\n",
        "        sixth_to_nineth_grade_results_column: 'float32',\n",
        "        sixth_grade_results_column: 'float32',\n",
        "        seventh_grade_results_column: 'float32',\n",
        "        eighth_grade_results_column: 'float32',\n",
        "        ninth_grade_results_column: 'float32',\n",
        "        yield_indicator_column: 'float32',\n",
        "        math_results_column: 'float32',\n",
        "        portuguese_results_column: 'float32',\n",
        "        standardized_average_column: 'float32',\n",
        "        ideb_column: 'float32'\n",
        "\n",
        "    },\n",
        "\n",
        "    {\n",
        "\n",
        "        state_column: 'string',\n",
        "        city_id_column: 'int32',\n",
        "        city_name_column: 'string',\n",
        "        school_type_column: 'string',\n",
        "        sixth_to_nineth_grade_results_column: 'float32',\n",
        "        sixth_grade_results_column: 'float32',\n",
        "        seventh_grade_results_column: 'float32',\n",
        "        eighth_grade_results_column: 'float32',\n",
        "        ninth_grade_results_column: 'float32',\n",
        "        yield_indicator_column: 'float32',\n",
        "        math_results_column: 'float32',\n",
        "        portuguese_results_column: 'float32',\n",
        "        standardized_average_column: 'float32',\n",
        "        ideb_column: 'float32'\n",
        "\n",
        "    },\n",
        "\n",
        "    {\n",
        "\n",
        "        state_column: 'string',\n",
        "        city_id_column: 'int32',\n",
        "        city_name_column: 'string',\n",
        "        school_id_column: 'int32',\n",
        "        school_name_column: 'string',\n",
        "        school_type_column: 'string',\n",
        "        first_to_fifth_grade_results_column: 'float32',\n",
        "        first_grade_results_column: 'float32',\n",
        "        second_grade_results_column: 'float32',\n",
        "        third_grade_results_column: 'float32',\n",
        "        fourth_grade_results_column: 'float32',\n",
        "        fifth_grade_results_column: 'float32',\n",
        "        yield_indicator_column: 'float32',\n",
        "        math_results_column: 'float32',\n",
        "        portuguese_results_column: 'float32',\n",
        "        standardized_average_column: 'float32',\n",
        "        ideb_column: 'float32'\n",
        "\n",
        "    },\n",
        "\n",
        "    {\n",
        "\n",
        "        state_column: 'string',\n",
        "        city_id_column: 'int32',\n",
        "        city_name_column: 'string',\n",
        "        school_type_column: 'string',\n",
        "        first_to_fifth_grade_results_column: 'float32',\n",
        "        first_grade_results_column: 'float32',\n",
        "        second_grade_results_column: 'float32',\n",
        "        third_grade_results_column: 'float32',\n",
        "        fourth_grade_results_column: 'float32',\n",
        "        fifth_grade_results_column: 'float32',\n",
        "        yield_indicator_column: 'float32',\n",
        "        math_results_column: 'float32',\n",
        "        portuguese_results_column: 'float32',\n",
        "        standardized_average_column: 'float32',\n",
        "        ideb_column: 'float32'\n",
        "\n",
        "    },\n",
        "\n",
        "    {\n",
        "        school_type_column: 'string',\n",
        "        first_to_fifth_grade_results_column: 'float32',\n",
        "        first_grade_results_column: 'float32',\n",
        "        second_grade_results_column: 'float32',\n",
        "        third_grade_results_column: 'float32',\n",
        "        fourth_grade_results_column: 'float32',\n",
        "        fifth_grade_results_column: 'float32',\n",
        "        yield_indicator_column: 'float32',\n",
        "        math_results_column: 'float32',\n",
        "        portuguese_results_column: 'float32',\n",
        "        standardized_average_column: 'float32',\n",
        "        ideb_column: 'float32'\n",
        "\n",
        "    },\n",
        "\n",
        "    {\n",
        "\n",
        "        state_column: 'string',\n",
        "        city_id_column: 'int32',\n",
        "        city_name_column: 'string',\n",
        "        school_id_column: 'int32',\n",
        "        school_name_column: 'string',\n",
        "        school_type_column: 'string',\n",
        "        first_to_third_high_grade_results_column: 'float32',\n",
        "        first_high_grade_results_column: 'float32',\n",
        "        second_high_grade_results_column: 'float32',\n",
        "        third_high_grade_results_column: 'float32',\n",
        "        yield_indicator_column: 'float32',\n",
        "        math_results_column: 'float32',\n",
        "        portuguese_results_column: 'float32',\n",
        "        standardized_average_column: 'float32',\n",
        "        ideb_column: 'float32'\n",
        "\n",
        "    },\n",
        "\n",
        "    {\n",
        "\n",
        "        state_column: 'string',\n",
        "        city_id_column: 'int32',\n",
        "        city_name_column: 'string',\n",
        "        school_type_column: 'string',\n",
        "        first_to_third_high_grade_results_column: 'float32',\n",
        "        first_high_grade_results_column: 'float32',\n",
        "        second_high_grade_results_column: 'float32',\n",
        "        third_high_grade_results_column: 'float32',\n",
        "        yield_indicator_column: 'float32',\n",
        "        math_results_column: 'float32',\n",
        "        portuguese_results_column: 'float32',\n",
        "        standardized_average_column: 'float32',\n",
        "        ideb_column: 'float32'\n",
        "\n",
        "    },\n",
        "\n",
        "    {\n",
        "\n",
        "        state_or_region: 'string',\n",
        "        school_type_column: 'string',\n",
        "        first_to_third_high_grade_results_column: 'float32',\n",
        "        first_high_grade_results_column: 'float32',\n",
        "        second_high_grade_results_column: 'float32',\n",
        "        third_high_grade_results_column: 'float32',\n",
        "        yield_indicator_column: 'float32',\n",
        "        math_results_column: 'float32',\n",
        "        portuguese_results_column: 'float32',\n",
        "        standardized_average_column: 'float32',\n",
        "        ideb_column: 'float32'\n",
        "\n",
        "    },\n",
        "\n",
        "\n",
        "]\n",
        "\n",
        "table_names = [\n",
        "    'wage_brasil',\n",
        "    'wage_per_city',\n",
        "    'wage_per_state',\n",
        "    'primary_schools_final_years',\n",
        "    'cities_primary_schools_final_years',\n",
        "    'primary_schools_early_years',\n",
        "    'cities_primary_schools_early_years',\n",
        "    'brasil_primary_schools_early_years',\n",
        "    'high_schools',\n",
        "    'cities_high_schools',\n",
        "    'states_region_primary_schools_early_years'\n",
        "]\n",
        "\n",
        "\n",
        "intervals = [\n",
        "    [('A', 'B')], [('A', 'A')], [('A', 'A')],[('G', 'AV'), ('BC', 'BW'), ('CA', 'CG'), ('CI', 'CP')],\n",
        "    [('E', 'AT'), ('BA', 'BU'), ('BY', 'CE'), ('CG', 'CN')],\n",
        "    [('G', 'BC'), ('BK', 'CE'), ('CI', 'CO'), ('CQ', 'CX')],\n",
        "    [('E', 'BA'), ('BI', 'CC'), ('CG', 'CM'), ('CO', 'CV')],\n",
        "    [('A', 'A'), ('C', 'AY'), ('BG', 'CA'), ('CE', 'CK'), ('CM', 'CT')],\n",
        "    [('G', 'L'), ('Q', 'Q'), ('S', 'U'), ('Y', 'Y'), ('AA', 'AB')],\n",
        "    [('E', 'J'), ('O', 'O'), ('Q', 'S'), ('W', 'W'), ('Y', 'Z')],\n",
        "    [('C', 'AY'), ('BG', 'CA'), ('CE', 'CK'), ('CM', 'CT')]\n",
        "]\n",
        "\n",
        "# Função para converter letra para número de coluna no Excel\n",
        "def char_to_number(letter):\n",
        "    number = 0\n",
        "    for i, l in enumerate(reversed(letter)):\n",
        "        number += (ord(l) - 64) * (26 ** i)\n",
        "    return number - 1\n",
        "\n",
        "# Array para armazenar os índices das colunas\n",
        "drop_columns = []\n",
        "\n",
        "# Percorrendo os intervalos e adicionando os índices das colunas ao array\n",
        "\n",
        "for interval in intervals:\n",
        "    temp = []\n",
        "    for begin, end in interval:\n",
        "        index_begin = char_to_number(begin)\n",
        "        index_end = char_to_number(end)\n",
        "        temp.extend(range(index_begin, index_end + 1))\n",
        "    drop_columns.append(temp)\n",
        "\n",
        "skip_rows = [[9, 3], [9, 3], [9, 3], [10, 3], [10, 3], [10, 3], [10, 3], [10, 3], [10, 3], [10, 3], [10, 6]]\n",
        "\n",
        "unwanted = ['a', 'c', 'd', '-']\n",
        "\n",
        "for i in range(0, 11) :\n",
        "    dataframe = pd.read_excel(f'{extract_dir}{extracted_files[i]}', skiprows=skip_rows[i][0], header=None, skipfooter=skip_rows[i][1], dtype=dtype[i])\n",
        "\n",
        "    columns_to_drop = dataframe.columns[drop_columns[i]]\n",
        "    dataframe = dataframe.drop(columns_to_drop, axis=1)\n",
        "\n",
        "    dataframe.columns = columns[i]\n",
        "    \n",
        "\n",
        "    dataframe_filtered = dataframe.dropna()\n",
        "\n",
        "    mask_unwanted = dataframe.isin(unwanted).any(axis=1)\n",
        "    dataframe_filtered = dataframe_filtered[~mask_unwanted]\n",
        "\n",
        "    if (i == 7) or (i == 10):\n",
        "        dataframe_filtered.loc[dataframe_filtered[school_type_column].str.contains(r'Privada \\(\\d\\)', regex=True), school_type_column] = 'Privada'\n",
        "        dataframe_filtered.loc[dataframe_filtered[school_type_column].str.contains(r'Estadual \\(\\d\\)', regex=True), school_type_column] = 'Estadual'\n",
        "        dataframe_filtered.loc[dataframe_filtered[school_type_column].str.contains(r'Pública \\(\\d\\)', regex=True), school_type_column] = 'Pública'\n",
        "        dataframe_filtered.loc[dataframe_filtered[school_type_column].str.contains(r'Municipal \\(\\d\\)', regex=True), school_type_column] = 'Municipal'\n",
        "        dataframe_filtered.loc[dataframe_filtered[school_type_column].str.contains(r'Total \\(\\d\\)\\(\\d\\)', regex=True), school_type_column] = 'Total'\n",
        "        dataframe_filtered.loc[dataframe_filtered[school_type_column].str.contains(r'Total \\(\\d\\)', regex=True), school_type_column] = 'Total'\n",
        "\n",
        "    if (i == 0) or (i == 2):\n",
        "        dataframe_filtered[standardized_pay_40_hours_column] = dataframe_filtered[standardized_pay_40_hours_column].astype(str).str.replace(',', '', regex=False)\n",
        "\n",
        "    dataframe_filtered.to_sql(table_names[i], conn, index=False, if_exists='replace')\n",
        "\n",
        "    dataframe_filtered.set_index(primary_keys[i], inplace=True)\n",
        "    \n",
        "    print(f'{table_names[i]} table created')\n",
        "\n",
        "print('Import completed')\n",
        "conn.close()"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "MKoNtSifVu-6",
      "metadata": {
        "id": "MKoNtSifVu-6"
      },
      "source": [
        "# Data Analysis"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "2d138d62",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2d138d62",
        "outputId": "2c3796ed-8763-4deb-eafc-2841a3312aa5"
      },
      "outputs": [],
      "source": [
        "def display_df(df, query):\n",
        "    table = PrettyTable()\n",
        "    table.field_names = df.columns.tolist()\n",
        "    for row in df.itertuples(index=False, name=None):\n",
        "        table.add_row(row)\n",
        "    print(f\"Results for query: {query}\\n\")\n",
        "    print(table)\n",
        "    print(\"\\n\")\n",
        "\n",
        "# Establish a connection to the SQLite database\n",
        "conn = sqlite3.connect(database_dir)\n",
        "\n",
        "# Example query to fetch data from a table\n",
        "query = \"\"\"\n",
        "CREATE VIEW IDEB_related_to_wages AS\n",
        "\tSELECT \n",
        "\t    cpsey.UF,\n",
        "\t    cpsey.city_id,\n",
        "\t    cpsey.city_name,\n",
        "\t    cpsey.school_type,\n",
        "\t    cpsey.\"1st_to_5th_grade_results\",\n",
        "\t    cpsey.math_results AS math_grades_1st_to_5st,\n",
        "\t    cpsey.portuguese_results AS portuguese_grades_1st_to_5st,\n",
        "\t    cpsey.yield_indicator AS performance_ratio_1st_to_5st,\n",
        "\t    cpsey.standardized_average AS standardized_average_1st_to_5st,\n",
        "\t    CAST(cpsey.IDEB_N_x_P AS FLOAT) AS IDEB_1st_to_5st,\n",
        "\t    cpsfy.\"6th_to_9th_grade_results\",\n",
        "\t    cpsfy.math_results AS math_grades_6st_to_9st,\n",
        "\t    cpsfy.portuguese_results AS portuguese_grades_6st_to_9st,\n",
        "\t    cpsfy.yield_indicator AS performance_ratio_6st_to_9st,\n",
        "\t    cpsfy.standardized_average AS standardized_average_6st_to_9st,\n",
        "\t    CAST(cpsfy.IDEB_N_x_P AS FLOAT) AS IDEB_6st_to_9st,\n",
        "\t    chs.\"1st_to_3rd_grade_results\",\n",
        "\t    chs.math_results AS math_grades_high_school,\n",
        "\t    chs.portuguese_results AS portuguese_grades_high_school,\n",
        "\t    chs.yield_indicator AS performance_ratio_high_school,\n",
        "\t    chs.standardized_average AS standardized_average_high_school,\n",
        "\t    CAST(chs.IDEB_N_x_P AS FLOAT) AS IDEB_high_school ,\n",
        "\t    COALESCE(wps.teacher_quantity, wpc.teacher_quantity) AS teachers_quantity,\n",
        "\t    COALESCE(wps.median, wpc.median) AS wages_median,\n",
        "\t    COALESCE(wps.average, wpc.average) AS wages_average,\n",
        "\t    COALESCE(wps.standard_deviation, wpc.standard_deviation) AS wages_standard_deviation,\n",
        "\t    CAST(COALESCE(wps.weekly_workload, wpc.weekly_workload) AS FLOAT) AS teachers_weekly_working_hours_average,\n",
        "\t    CASE \n",
        "        \tWHEN cpsey.school_type = 'Pública' \n",
        "        \t\tTHEN \n",
        "        \t\t\t((COALESCE (CAST(wps.standardized_pay_40_hours AS FLOAT), 0) * COALESCE (CAST(wps.teacher_quantity AS FLOAT), 0)) + \n",
        "        \t\t\t(COALESCE (CAST(wpc.standardized_pay_40_hours AS FLOAT), 0) * COALESCE (CAST(wpc.teacher_quantity AS FLOAT), 0))) /\n",
        "        \t\t\t(COALESCE (CAST(wps.teacher_quantity AS FLOAT), 0) + COALESCE (CAST(wpc.teacher_quantity AS FLOAT), 0))\n",
        "    \t    \tELSE CAST(COALESCE(wps.standardized_pay_40_hours, wpc.standardized_pay_40_hours) AS FLOAT)\n",
        "\t    END AS \"wages_for_40h_working_hours\"\n",
        "\t\tFROM cities_primary_schools_early_years cpsey\n",
        "\t\tLEFT JOIN cities_primary_schools_final_years cpsfy \n",
        "\t\t\tON cpsey.city_id = cpsfy.city_id AND cpsey.school_type = cpsfy.school_type\n",
        "\t\tLEFT JOIN cities_high_schools chs\n",
        "\t\t\tON chs.city_id = cpsey.city_id AND chs.school_type = cpsey.school_type\n",
        "\t\tLEFT JOIN wage_per_city wpc \n",
        "\t\t\tON wpc.city_id = cpsey.city_id AND wpc.teacher_education = 'Total'\n",
        "\t\tLEFT JOIN wage_per_state wps\n",
        "\t\t\tON cpsey.school_type = 'Estadual' AND wps.UF = cpsey.UF AND wps.teacher_education = 'Total' AND wps.adm_dependence = 'Estadual' \n",
        "\t\tWHERE COALESCE(wps.standardized_pay_40_hours, wpc.standardized_pay_40_hours) IS NOT NULL \n",
        "\"\"\"\n",
        "\n",
        "conn.execute(query)\n",
        "\n",
        "querySelectView = \"SELECT * FROM IDEB_related_to_wages\"\n",
        "# Execute the query and load the results into a pandas DataFrame\n",
        "df = pd.read_sql_query(querySelectView, conn)\n",
        "\n",
        "display_df(df, querySelectView)\n",
        "\n",
        "# Close the connection\n",
        "conn.close()\n"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "d5748f8e",
      "metadata": {},
      "source": [
        "## Analysis"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "f7887443",
      "metadata": {},
      "source": [
        "### National High School Analysis"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "7f0a4879",
      "metadata": {},
      "outputs": [],
      "source": [
        "def getNationalData(nTiles=1, tilesToUse=[]):\n",
        "    conn = sqlite3.connect(database_dir)\n",
        "\n",
        "    tiles_condition = \"\"\n",
        "    if len(tilesToUse) > 0:\n",
        "        tiles_condition = f\"AND interval_position IN ({', '.join(tilesToUse)})\"\n",
        "\n",
        "    query = f\"\"\"\n",
        "    WITH data AS (\n",
        "        SELECT \n",
        "            *,\n",
        "            NTILE({nTiles}) OVER(ORDER BY wages_for_40h_working_hours) AS interval_position\n",
        "        FROM (\n",
        "            SELECT * from IDEB_related_to_wages irtw\n",
        "            WHERE irtw.IDEB_high_school IS NOT NULL \n",
        "            )\n",
        "    )\n",
        "    SELECT \n",
        "        city_name,\n",
        "        UF,\n",
        "        school_type,\n",
        "        IDEB_1st_to_5st,\n",
        "        IDEB_6st_to_9st,\n",
        "        IDEB_high_school,\n",
        "        teachers_weekly_working_hours_average,\n",
        "        wages_for_40h_working_hours\n",
        "    FROM data\n",
        "    WHERE school_type = 'Pública' {tiles_condition}\n",
        "    \"\"\"\n",
        "\n",
        "    data = pd.read_sql_query(query, conn)\n",
        "\n",
        "    conn.close()\n",
        "\n",
        "    return data"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "30316b0d",
      "metadata": {},
      "outputs": [],
      "source": [
        "plotToUse = int(input(\"Tipo de grafico [0 - Grafico de Dispersao, 1 - Mapa de calor]: \"))\n",
        "\n",
        "if plotToUse == 0:\n",
        "    ideb = int(input(\"IDEB selecionado [1 - Ensino fundamental I; 2 - Ensino Fundamental 2; 3 - Ensino Medio]: \"))\n",
        "getBySubDivisions = input(\"Precisa de divisoes (quantis)? [apenas enter para pular] \")\n",
        "\n",
        "nTiles = 1\n",
        "tilesToUse = []\n",
        "if getBySubDivisions:\n",
        "    nTiles = int(input(\"Numero de divisoes (Quantis): \"))\n",
        "    inputAnswer = int(input(\"Utilizar o quantil numero [0 para finalizar selecao de quantis]: \"))\n",
        "    while inputAnswer != 0:\n",
        "        tilesToUse.append(str(inputAnswer))\n",
        "        inputAnswer = int(input(\"Utilizar o quantil numero [0 para finalizar selecao de quantis]: \"))\n",
        "\n",
        "idebOptions = [\"IDEB_1st_to_5st\", \"IDEB_6st_to_9st\", \"IDEB_high_school\"]\n",
        "\n",
        "data = getNationalData(nTiles, tilesToUse).drop(columns=['city_name', 'UF', 'school_type'])\n",
        "\n",
        "if plotToUse == 0:\n",
        "    data.plot.scatter(x=\"wages_for_40h_working_hours\", y=idebOptions[ideb-1])\n",
        "elif plotToUse == 1:\n",
        "    # Correlations\n",
        "    corrData = data\n",
        "    corr = corrData.corr()\n",
        "    sb.heatmap(corr)\n",
        "\n",
        "    print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "87b6845b",
      "metadata": {},
      "source": [
        "### Regional High School Analysis"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "a82f107d",
      "metadata": {},
      "outputs": [],
      "source": [
        "def getDataframeByUFs(UFs, nTiles=1, tilesToUse=[]):\n",
        "    conn = sqlite3.connect(database_dir)\n",
        "\n",
        "    tiles_condition = \"\"\n",
        "    if len(tilesToUse) > 0:\n",
        "        tiles_condition = f\"AND interval_position IN ({', '.join(tilesToUse)})\"\n",
        "\n",
        "    query = f\"\"\"\n",
        "    WITH data AS (\n",
        "        SELECT \n",
        "            *,\n",
        "            NTILE({nTiles}) OVER(ORDER BY wages_for_40h_working_hours) AS interval_position\n",
        "        FROM (\n",
        "            SELECT * from IDEB_related_to_wages irtw\n",
        "            WHERE irtw.IDEB_high_school IS NOT NULL AND UF IN ('{\"', '\".join(UFs)}')\n",
        "            )\n",
        "    )\n",
        "    SELECT \n",
        "        \"city_name\",\n",
        "        \"UF\",\n",
        "        school_type,\n",
        "        IDEB_1st_to_5st,\n",
        "        IDEB_6st_to_9st,\n",
        "        IDEB_high_school,\n",
        "        teachers_weekly_working_hours_average,\n",
        "        wages_for_40h_working_hours\n",
        "    FROM data\n",
        "    WHERE school_type = 'Pública' {tiles_condition}\n",
        "    \"\"\"\n",
        "\n",
        "    data = pd.read_sql_query(query, conn)\n",
        "\n",
        "    conn.close()\n",
        "\n",
        "    return data"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "58656b1d",
      "metadata": {},
      "source": [
        "#### Custom Plot (Gráfico de Dispersão ou Mapa de calor)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "6018a75f",
      "metadata": {},
      "outputs": [],
      "source": [
        "plotToUse = int(input(\"Tipo de grafico [0 - Grafico de Dispersao, 1 - Mapa de calor]: \"))\n",
        "\n",
        "region = (input(\"Regiao a ser analisada [Norte, Nordeste, Centro-Oeste, Sudeste ou Sul]: \")).lower()\n",
        "if plotToUse == 0:\n",
        "    ideb = int(input(\"IDEB selecionado [1 - Ensino fundamental I; 2 - Ensino Fundamental 2; 3 - Ensino Medio]: \"))\n",
        "getBySubDivisions = input(\"Precisa de divisoes (quantis)? [apenas enter para pular] \")\n",
        "\n",
        "nTiles = 1\n",
        "tilesToUse = []\n",
        "if getBySubDivisions:\n",
        "    nTiles = int(input(\"Numero de divisoes (Quantis): \"))\n",
        "    inputAnswer = int(input(\"Utilizar o quantil numero [0 para finalizar selecao de quantis]: \"))\n",
        "    while inputAnswer != 0:\n",
        "        tilesToUse.append(str(inputAnswer))\n",
        "        inputAnswer = int(input(\"Utilizar o quantil numero [0 para finalizar selecao de quantis]: \"))\n",
        "\n",
        "regions = {\n",
        "    \"norte\": ['AC', 'AP', 'AM', 'PA', 'RO', 'RR', 'TO'],\n",
        "    \"nordeste\": [\"AL\", \"BA\", \"CE\", \"MA\", \"PB\", \"PE\", \"PI\", \"RN\", \"SE\"],\n",
        "    \"centro-oeste\": [\"DF\", \"GO\", \"MT\", \"MS\"],\n",
        "    \"sudeste\": [\"MG\", \"ES\", \"SP\", \"RJ\"],\n",
        "    \"sul\": [\"PR\", \"SC\", \"RS\"]\n",
        "}\n",
        "\n",
        "idebOptions = [\"IDEB_1st_to_5st\", \"IDEB_6st_to_9st\", \"IDEB_high_school\"]\n",
        "\n",
        "data = getDataframeByUFs(regions[region], nTiles, tilesToUse).drop(columns=['city_name', 'UF', 'school_type'])\n",
        "\n",
        "if plotToUse == 0:\n",
        "    data.plot.scatter(x=\"wages_for_40h_working_hours\", y=idebOptions[ideb-1])\n",
        "elif plotToUse == 1:\n",
        "    # Correlations\n",
        "    corrData = data\n",
        "    corr = corrData.corr()\n",
        "    sb.heatmap(corr)\n",
        "\n",
        "    print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "5a9514fd",
      "metadata": {},
      "source": [
        "#### Norte"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "b95d5f8d",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "northData = getDataframeByUFs(['AC', 'AP', 'AM', 'PA', 'RO', 'RR', 'TO']).drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = northData.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "89300e6e",
      "metadata": {},
      "source": [
        "#### Nordeste"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "ece0851d",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "northeastData = getDataframeByUFs([\"AL\", \"BA\", \"CE\", \"MA\", \"PB\", \"PE\", \"PI\", \"RN\", \"SE\"]).drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = northeastData.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "b4ebf1e3",
      "metadata": {},
      "source": [
        "#### Centro-Oeste"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "46cdacd3",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "midWest = getDataframeByUFs([\"DF\", \"GO\", \"MT\", \"MS\"]).drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = midWest.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "23025296",
      "metadata": {},
      "source": [
        "#### Sudeste"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "a7403b85",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "southeast = getDataframeByUFs([\"MG\", \"ES\", \"SP\", \"RJ\"]).drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = southeast.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "7fae58db",
      "metadata": {},
      "source": [
        "#### Sul"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "ee156609",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "south = getDataframeByUFs([\"PR\", \"SC\", \"RS\"]).drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = south.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "7b5d619d",
      "metadata": {},
      "source": [
        "### By State Analysis"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "1187e2bb",
      "metadata": {},
      "outputs": [],
      "source": [
        "def getDataframeByUF(UF, nTiles=1, tilesToUse=[]):\n",
        "    conn = sqlite3.connect(database_dir)\n",
        "\n",
        "    tiles_condition = \"\"\n",
        "    if len(tilesToUse) > 0:\n",
        "        tiles_condition = f\"AND interval_position IN ({', '.join(tilesToUse)})\"\n",
        "\n",
        "    query = f\"\"\"\n",
        "    WITH data AS (\n",
        "        SELECT \n",
        "            *,\n",
        "            NTILE({nTiles}) OVER(ORDER BY wages_for_40h_working_hours) AS interval_position\n",
        "        FROM (\n",
        "            SELECT * from IDEB_related_to_wages irtw\n",
        "            WHERE irtw.IDEB_high_school IS NOT NULL \n",
        "            AND UF = '{UF}'\n",
        "            )\n",
        "    )\n",
        "    SELECT \n",
        "        \"city_name\",\n",
        "        \"UF\",\n",
        "        school_type,\n",
        "        IDEB_1st_to_5st,\n",
        "        IDEB_6st_to_9st,\n",
        "        IDEB_high_school,\n",
        "        teachers_weekly_working_hours_average,\n",
        "        wages_for_40h_working_hours\n",
        "    FROM data\n",
        "    WHERE school_type = 'Pública' {tiles_condition}\n",
        "    \"\"\"\n",
        "\n",
        "    data = pd.read_sql_query(query, conn)\n",
        "\n",
        "\n",
        "    conn.close()\n",
        "\n",
        "    return data"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "a45a3e53",
      "metadata": {},
      "source": [
        "#### Custom Plot (Gráfico de Dispersão ou Mapa de calor)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "4f139acd",
      "metadata": {},
      "outputs": [],
      "source": [
        "plotToUse = int(input(\"Tipo de grafico [0 - Grafico de Dispersao, 1 - Mapa de calor]: \"))\n",
        "\n",
        "state = input(\"Estado a ser analisado: \")\n",
        "if plotToUse == 0:\n",
        "    ideb = int(input(\"IDEB selecionado [1 - Ensino fundamental I; 2 - Ensino Fundamental 2; 3 - Ensino Medio]: \"))\n",
        "\n",
        "getBySubDivisions = input(\"Precisa de divisoes (quantis)? [apenas enter para pular] \")\n",
        "\n",
        "nTiles = 1\n",
        "tilesToUse = []\n",
        "if getBySubDivisions:\n",
        "    nTiles = int(input(\"Numero de divisoes (Quantis): \"))\n",
        "    inputAnswer = int(input(\"Utilizar o quantil numero [0 para finalizar selecao de quantis]: \"))\n",
        "    while inputAnswer != 0:\n",
        "        tilesToUse.append(str(inputAnswer))\n",
        "        inputAnswer = int(input(\"Utilizar o quantil numero [0 para finalizar selecao de quantis]: \"))\n",
        "\n",
        "\n",
        "idebOptions = [\"IDEB_1st_to_5st\", \"IDEB_6st_to_9st\", \"IDEB_high_school\"]\n",
        "\n",
        "data = getDataframeByUF(state, nTiles, tilesToUse).drop(columns=['city_name', 'UF', 'school_type'])\n",
        "\n",
        "\n",
        "if plotToUse == 0:\n",
        "    data.plot.scatter(x=\"wages_for_40h_working_hours\", y=idebOptions[ideb-1])\n",
        "elif plotToUse == 1:\n",
        "    # Correlations\n",
        "    corrData = data\n",
        "    corr = corrData.corr()\n",
        "    sb.heatmap(corr)\n",
        "\n",
        "    print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Acre"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "acre = getDataframeByUF(\"AC\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = acre.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Alagoas"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "alagoas = getDataframeByUF(\"AL\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = alagoas.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Amapá"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "amapa = getDataframeByUF(\"AP\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = amapa.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Amazonas"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "amazonas = getDataframeByUF(\"AM\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = amazonas.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Bahia"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "bahia = getDataframeByUF(\"BA\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = bahia.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Ceará"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "ceara = getDataframeByUF(\"CE\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = ceara.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Distrito Federal"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "df = getDataframeByUF(\"DF\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = df.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Espírito Santo"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "espirito_santo = getDataframeByUF(\"ES\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = espirito_santo.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Goiás"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "goias = getDataframeByUF(\"GO\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = goias.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Maranhão"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "maranhao = getDataframeByUF(\"MA\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = maranhao.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Mato Grosso"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "mato_grosso = getDataframeByUF(\"MT\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = mato_grosso.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Mato Grosso do Sul"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "mato_grosso_sul = getDataframeByUF(\"MS\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = mato_grosso_sul.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Minas Gerais"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "minas_gerais = getDataframeByUF(\"MG\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = minas_gerais.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Pará"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "para = getDataframeByUF(\"PA\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = para.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Paraíba"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "paraiba = getDataframeByUF(\"PB\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = paraiba.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Paraná"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "parana = getDataframeByUF(\"PR\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = parana.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Pernambuco"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "pernambuco = getDataframeByUF(\"PE\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = pernambuco.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Piauí"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "piaui = getDataframeByUF(\"PI\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = piaui.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Rio de Janeiro"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "rio_janeiro = getDataframeByUF(\"RJ\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = rio_janeiro.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Rio Grande do Norte"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "rio_grande_norte = getDataframeByUF(\"RN\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = rio_grande_norte.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Rio Grande do Sul"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "rio_grande_sul = getDataframeByUF(\"RS\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = rio_grande_sul.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Rondônia"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "rondonia = getDataframeByUF(\"RO\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = rondonia.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Roraima"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "roraima = getDataframeByUF(\"RR\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = roraima.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Santa Catarina"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "santa_catarina = getDataframeByUF(\"SC\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = santa_catarina.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### São Paulo"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "sao_paulo = getDataframeByUF(\"SP\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = sao_paulo.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Sergipe"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "sergipe = getDataframeByUF(\"SE\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = sergipe.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1252b8ed",
      "metadata": {},
      "source": [
        "#### Tocantins"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f4032296",
      "metadata": {},
      "outputs": [],
      "source": [
        "# Correlations\n",
        "tocantins = getDataframeByUF(\"TO\").drop(columns=['city_name', 'UF', 'school_type'])\n",
        "corr = tocantins.corr()\n",
        "sb.heatmap(corr)\n",
        "\n",
        "print(corr)"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3 (ipykernel)",
      "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.11.1"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}