8_Simple_Education_Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import geopandas as gpd\n",
    "import contextily as ctx\n",
    "import matplotlib.pyplot as plt\n",
    "from pathlib import Path\n",
    "from mpl_toolkits.axes_grid1 import make_axes_locatable\n",
    "from tqdm.auto import tqdm\n",
    "from pandarallel import pandarallel\n",
    "\n",
    "tqdm.pandas()\n",
    "pandarallel.initialize(progress_bar=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load and preprocess the data\n",
    "para_muni = gpd.read_file(\"outputs/para_muni.geojson\")\n",
    "hex_gdf = gpd.read_parquet(\"outputs/25022025_para_hexs_final.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Number of Regions in Pará\n",
    "para_muni[\"name_region\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Validate we only have municipalities of Pará\n",
    "para_muni[\"abbrev_state\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Number of Municipalities in Para\n",
    "para_muni[\"name_muni\"].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "para_muni.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "para_muni.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hex_gdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"V002_adj\" in hex_gdf.columns, \"V003_adj\" in hex_gdf.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hex_gdf.columns.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hex_gdf.crs.to_string(), para_muni.crs.to_string()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Project the para_muni to the same crs as hex_gdf\n",
    "para_muni = para_muni.to_crs(hex_gdf.crs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"name_muni\" in hex_gdf.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "muni_gdf = gpd.GeoDataFrame(\n",
    "    hex_gdf.groupby(\"name_muni\")\n",
    "    .agg(\n",
    "        {\n",
    "            # Sum all population columns\n",
    "            \"population_2020\": \"sum\",\n",
    "            \"pop_3_months_3_years_adj\": \"sum\",\n",
    "            \"pop_4_5_years_adj\": \"sum\",\n",
    "            \"pop_6_10_years_adj\": \"sum\",\n",
    "            \"pop_11_14_years_adj\": \"sum\",\n",
    "            \"pop_15_17_years_adj\": \"sum\",\n",
    "            \"pop_18_years_adj\": \"sum\",\n",
    "            \"pop_total_adj\": \"sum\",\n",
    "            # Calculate the average income\n",
    "            \"income_total_adj\": \"sum\",\n",
    "            \"income_pc\": \"mean\",\n",
    "            # Count all schools\n",
    "            \"ensino_fundamental\": \"sum\",\n",
    "            \"educacao_infantil\": \"sum\",\n",
    "            \"ensino_medio\": \"sum\",\n",
    "            \"educacao_jovens_adultos\": \"sum\",\n",
    "            \"educacao_profissional\": \"sum\",\n",
    "            \"no_specified\": \"sum\",\n",
    "            # Count all private/public schools\n",
    "            \"privada\": \"sum\",\n",
    "            \"publica\": \"sum\",\n",
    "            # Count all types of public schools\n",
    "            \"federal\": \"sum\",\n",
    "            \"estadual\": \"sum\",\n",
    "            \"municipal\": \"sum\",\n",
    "        }\n",
    "    )\n",
    "    .merge(para_muni[[\"name_muni\", \"geometry\"]], on=\"name_muni\"),\n",
    "    geometry=\"geometry\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if False:\n",
    "    # Plot all variables at municipality level\n",
    "    for col in muni_gdf.columns:\n",
    "        if col not in [\"name_muni\", \"geometry\"]:\n",
    "            muni_gdf.plot(col, legend=True, figsize=(20, 10))\n",
    "            plt.title(col.replace(\"_\", \" \").title())\n",
    "            plt.axis(\"off\")\n",
    "            # Save the plot\n",
    "            output_fn = Path(f\"outputs/maps/munis/munis_{col}.png\")\n",
    "            output_fn.parent.mkdir(parents=True, exist_ok=True)\n",
    "            plt.savefig(output_fn, dpi=300, bbox_inches=\"tight\")\n",
    "            # Do not show the plot\n",
    "            plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "selected_municipality = \"Belém\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "muni_hexagons = hex_gdf[hex_gdf[\"name_muni\"] == selected_municipality]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "education_levels = [\"INF_CRE\", \"INF_PRE\", \"FUND_AI\", \"FUND_AF\", \"MED\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hex_gdf[\n",
    "    [\n",
    "        \"PRIVATE_QT_MAT_INF_CRE\",\n",
    "        \"PRIVATE_QT_MAT_INF_PRE\",\n",
    "        \"PRIVATE_QT_MAT_FUND_AF\",\n",
    "        \"PRIVATE_QT_MAT_FUND_AI\",\n",
    "        \"PRIVATE_QT_MAT_MED\",\n",
    "    ]\n",
    "].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hex_gdf[\n",
    "    [\n",
    "        \"PRIVATE_QT_MAT_INF_CRE\",\n",
    "        \"PRIVATE_QT_MAT_INF_PRE\",\n",
    "        \"PRIVATE_QT_MAT_FUND_AF\",\n",
    "        \"PRIVATE_QT_MAT_FUND_AI\",\n",
    "        \"PRIVATE_QT_MAT_MED\",\n",
    "    ]\n",
    "].sum().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hex_gdf[\n",
    "    [\n",
    "        \"QT_MAT_INF_CRE\",\n",
    "        \"QT_MAT_INF_PRE\",\n",
    "        \"QT_MAT_FUND_AF\",\n",
    "        \"QT_MAT_FUND_AI\",\n",
    "        \"QT_MAT_MED\",\n",
    "    ]\n",
    "].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hex_gdf[\n",
    "    [\n",
    "        \"QT_MAT_INF_CRE\",\n",
    "        \"QT_MAT_INF_PRE\",\n",
    "        \"QT_MAT_FUND_AF\",\n",
    "        \"QT_MAT_FUND_AI\",\n",
    "        \"QT_MAT_MED\",\n",
    "    ]\n",
    "].sum().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "100 * hex_gdf[\n",
    "    [\n",
    "        \"PRIVATE_QT_MAT_INF_CRE\",\n",
    "        \"PRIVATE_QT_MAT_INF_PRE\",\n",
    "        \"PRIVATE_QT_MAT_FUND_AF\",\n",
    "        \"PRIVATE_QT_MAT_FUND_AI\",\n",
    "        \"PRIVATE_QT_MAT_MED\",\n",
    "    ]\n",
    "].sum().sum() / hex_gdf[\n",
    "    [\n",
    "        \"QT_MAT_INF_CRE\",\n",
    "        \"QT_MAT_INF_PRE\",\n",
    "        \"QT_MAT_FUND_AF\",\n",
    "        \"QT_MAT_FUND_AI\",\n",
    "        \"QT_MAT_MED\",\n",
    "    ]\n",
    "].sum().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for level in education_levels:\n",
    "    print(\n",
    "        level,\n",
    "        \":\",\n",
    "        round(\n",
    "            100\n",
    "            * hex_gdf[f\"PRIVATE_QT_MAT_{level}\"].sum()\n",
    "            / hex_gdf[f\"QT_MAT_{level}\"].sum(),\n",
    "            2,\n",
    "        ),\n",
    "        \"%\",\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate defaults for the table\n",
    "for level in education_levels:\n",
    "\n",
    "    hex_gdf[f\"QT_MAT_{level}_PROP\"] = hex_gdf[f\"QT_MAT_{level}\"] / hex_gdf[\"QT_MAT_BAS\"]\n",
    "\n",
    "    hex_gdf[f\"PROP_TURNO_COMPLETO_{level}\"] = (\n",
    "        hex_gdf[f\"QT_MAT_{level}_INT\"] / hex_gdf[f\"QT_MAT_{level}\"]\n",
    "    )\n",
    "\n",
    "    # Initialize variables\n",
    "    hex_gdf[f\"PROP_TURNO_NOCTURNO_{level}\"] = np.nan\n",
    "    hex_gdf[f\"QT_PUESTOS_{level}\"] = np.nan\n",
    "\n",
    "    if level in [\"INF_CRE\", \"INF_PRE\", \"FUND_AI\"]:\n",
    "        hex_gdf[f\"PROP_TURNO_NOCTURNO_{level}\"] = (\n",
    "            \"N/A\"  # Small children don't attend night classes\n",
    "        )\n",
    "        hex_gdf[f\"QT_PUESTOS_{level}\"] = hex_gdf[f\"QT_MAT_{level}\"] * (\n",
    "            1 + hex_gdf[f\"PROP_TURNO_COMPLETO_{level}\"]\n",
    "        )\n",
    "    else:\n",
    "        hex_gdf[f\"PROP_TURNO_NOCTURNO_{level}\"] = (\n",
    "            hex_gdf[\"QT_MAT_BAS_N\"] * hex_gdf[f\"QT_MAT_{level}_PROP\"]\n",
    "        )\n",
    "        hex_gdf[f\"QT_PUESTOS_{level}\"] = (\n",
    "            hex_gdf[f\"QT_MAT_{level}\"]\n",
    "            * (1 + hex_gdf[f\"PROP_TURNO_COMPLETO_{level}\"])\n",
    "            * (1 - hex_gdf[f\"PROP_TURNO_NOCTURNO_{level}\"])\n",
    "        )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "belem_hexs = hex_gdf[hex_gdf[\"name_muni\"] == \"Belém\"]\n",
    "belem_hexs.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_table = []\n",
    "# total number of students on each level\n",
    "main_table.append(\n",
    "    belem_hexs[[f\"QT_MAT_{level}\" for level in education_levels]].sum().values.tolist()\n",
    ")\n",
    "# percentage of students in integral time (Tempo Integral)\n",
    "main_table.append(\n",
    "    [\n",
    "        belem_hexs[f\"QT_MAT_{level}_INT\"].sum() / belem_hexs[f\"QT_MAT_{level}\"].sum()\n",
    "        for level in education_levels\n",
    "    ]\n",
    ")\n",
    "# percentage of students in nocturnal time (Tempo Noturno)\n",
    "main_table.append(\n",
    "    [\n",
    "        (\n",
    "            (belem_hexs[\"QT_MAT_BAS_N\"] * belem_hexs[f\"QT_MAT_{level}_PROP\"]).sum()\n",
    "            / belem_hexs[f\"QT_MAT_{level}\"].sum()\n",
    "            if level not in [\"INF_CRE\", \"INF_PRE\", \"FUND_AI\"]\n",
    "            else 0\n",
    "        )\n",
    "        for level in education_levels\n",
    "    ]\n",
    ")\n",
    "# total number of places available\n",
    "total_places = []\n",
    "for i, level in enumerate(education_levels):\n",
    "    # total number of students * (1 + percentage of students in integral time) * (1 - percentage of students in nocturnal time\n",
    "    num_cadeiras = main_table[0][i] * (1 + main_table[1][i]) * (1 - main_table[2][i])\n",
    "    total_places.append(num_cadeiras)\n",
    "main_table.append(total_places)\n",
    "\n",
    "main_table = pd.DataFrame(\n",
    "    main_table,\n",
    "    columns=education_levels,\n",
    "    index=[\"Total Alunos\", \"Integral (%)\", \"Nocturno (%)\", \"Num. Cadeiras\"],\n",
    ")\n",
    "main_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the number of classrooms needed based on the user defined number of chairs per classroom\n",
    "\n",
    "# Number of chairs per classroom\n",
    "num_chairs = 30\n",
    "\n",
    "# Number of classrooms needed in total\n",
    "main_table.loc[\"Num. Salas Total\"] = main_table.loc[\"Num. Cadeiras\"] / num_chairs\n",
    "\n",
    "# Actual number of classrooms\n",
    "main_table.loc[\"Num. Salas Atuais\"] = [\n",
    "    (belem_hexs[\"QT_SALAS_UTILIZADAS\"] * belem_hexs[f\"QT_MAT_{level}_PROP\"]).sum()\n",
    "    for level in education_levels\n",
    "]\n",
    "\n",
    "# Number of classrooms needed in each level\n",
    "main_table.loc[\"Num. Salas Novas\"] = np.ceil(\n",
    "    np.maximum(\n",
    "        main_table.loc[\"Num. Salas Total\"] - main_table.loc[\"Num. Salas Atuais\"], 0\n",
    "    )\n",
    ")\n",
    "\n",
    "main_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "creche = \"INF_CRE\"\n",
    "hex_gdf[\n",
    "    [\n",
    "        f\"QT_MAT_{level}\",  # Total de Estudantes\n",
    "        f\"PROP_TURNO_COMPLETO_{creche}\",  # % Tempo Integral\n",
    "        f\"PROP_TURNO_NOCTURNO_{creche}\",  # % Noturno\n",
    "        f\"QT_PUESTOS_{creche}\",  # # de cadeiras\n",
    "    ]\n",
    "].dropna().round()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the number of classrooms needed based on the user defined number of chairs per classroom\n",
    "puestos_por_sala = 30\n",
    "for level in education_levels:\n",
    "    hex_gdf[f\"QT_SALAS_NECESARIAS_TOTAL_{level}\"] = (\n",
    "        hex_gdf[f\"QT_PUESTOS_{level}\"] / puestos_por_sala\n",
    "    )\n",
    "    hex_gdf[f\"QT_SALAS_ACTUALES_{level}\"] = (\n",
    "        hex_gdf[\"QT_SALAS_UTILIZADAS\"] * hex_gdf[f\"QT_MAT_{level}_PROP\"]\n",
    "    )\n",
    "    hex_gdf[f\"QT_SALAS_NECESARIAS_EXTRA_{level}\"] = np.ceil(\n",
    "        np.maximum(\n",
    "            hex_gdf[f\"QT_SALAS_NECESARIAS_TOTAL_{level}\"]\n",
    "            - hex_gdf[f\"QT_SALAS_ACTUALES_{level}\"],\n",
    "            0,\n",
    "        )\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hex_gdf[\"publica\"].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hex_gdf[\"privada\"].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hex_gdf[[\"federal\", \"estadual\", \"municipal\"]].sum().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hex_gdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hex_gdf.to_parquet(\"outputs/25022025_dashboard_hexs.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace \"pop_3_months_3_years\" with  \"pop_INF_CRE\"\n",
    "hex_gdf = hex_gdf.rename(\n",
    "    columns={\n",
    "        \"pop_3_months_3_years_adj\": \"pop_INF_CRE\",\n",
    "        \"pop_4_5_years_adj\": \"pop_INF_PRE\",\n",
    "        \"pop_6_10_years_adj\": \"pop_FUND_AI\",\n",
    "        \"pop_11_14_years_adj\": \"pop_FUND_AF\",\n",
    "        \"pop_15_17_years_adj\": \"pop_MED\",\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# A - population on each level\n",
    "hex_gdf[[f\"pop_{level}\" for level in education_levels]].sum().round(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# A1 - % population outside of school\n",
    "pop_not_in_school_per_level = []\n",
    "for level in education_levels:\n",
    "    print()\n",
    "    print(\"####\", level, \"####\")\n",
    "    total_students = hex_gdf[f\"QT_MAT_{level}\"].sum()\n",
    "    print(\"total_students:\", int(total_students))\n",
    "    total_pop = hex_gdf[f\"pop_{level}\"].sum()\n",
    "    print(\"total_pop:\", int(total_pop))\n",
    "    pop_in_school = total_students / total_pop\n",
    "    print(\"pop_in_school %\", f\"{(pop_in_school * 100):.02f}\")\n",
    "    print(\"pop_not_in_school %\", f\"{((1 - pop_in_school)*100):.02f}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Visualize the results for the selected municipality\n",
    "belem_hexs = hex_gdf[hex_gdf[\"name_muni\"] == \"Belém\"]\n",
    "for level in education_levels:\n",
    "    ax = belem_hexs.plot(\n",
    "        f\"QT_SALAS_NECESARIAS_EXTRA_{level}\",\n",
    "        alpha=0.5,\n",
    "        legend=True,\n",
    "        cmap=\"RdYlGn_r\",\n",
    "        figsize=(20, 10),\n",
    "        missing_kwds={\"color\": \"lightgrey\"},\n",
    "    )\n",
    "    ax.set_axis_off()\n",
    "    ax.set_title(f\"Number of extra classrooms\\nneeded for {level} in Belém\")\n",
    "    # Add a basemap\n",
    "    ctx.add_basemap(\n",
    "        ax, source=ctx.providers.CartoDB.Positron, crs=belem_hexs.crs, attribution=\"\"\n",
    "    )\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot the selected hexagon borders\n",
    "ax = selected_hexagon.plot(edgecolor=\"red\", facecolor=\"none\", figsize=(20, 10))\n",
    "schools.clip(belem_hexs).plot(color=\"red\", ax=ax, alpha=0.5)\n",
    "\n",
    "# Set axis limits to belem\n",
    "minx, miny, maxx, maxy = selected_hexagon.total_bounds + np.array(\n",
    "    [-0.01, -0.01, 0.01, 0.01]\n",
    ")\n",
    "ax.set_xlim([minx, maxx])\n",
    "ax.set_ylim([miny, maxy])\n",
    "\n",
    "ax.set_axis_off()\n",
    "\n",
    "ax.set_title(\"Selected hexagon with schools\")\n",
    "\n",
    "# Add a basemap\n",
    "ctx.add_basemap(ax, source=ctx.providers.CartoDB.Positron, crs=belem_hexs.crs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the index of the hexagon with the highest number of students\n",
    "selected_hexagon = belem_hexs.loc[[belem_hexs[\"ensino_fundamental\"].idxmax()]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot the selected hexagon borders\n",
    "ax = selected_hexagon.plot(edgecolor=\"red\", facecolor=\"none\", figsize=(20, 10))\n",
    "belem_hexs.plot(\"ensino_fundamental\", ax=ax, alpha=0.5, legend=True)\n",
    "\n",
    "# Set axis limits to belem\n",
    "minx, miny, maxx, maxy = belem_hexs.total_bounds\n",
    "ax.set_xlim([minx, maxx])\n",
    "ax.set_ylim([miny, maxy])\n",
    "\n",
    "ax.set_axis_off()\n",
    "\n",
    "ax.set_title(\"Number of students in elementary school in Belém\")\n",
    "\n",
    "# Add a basemap\n",
    "ctx.add_basemap(ax, source=ctx.providers.CartoDB.Positron, crs=belem_hexs.crs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "schools = gpd.read_parquet(\"outputs/20240129_para_schools_final.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "schools.crs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "belem_hexs.crs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "schools.to_crs(belem_hexs.crs, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "selected_hexagon[\"code_school\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "schools.clip(selected_hexagon).shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ax = selected_hexagon.plot(alpha=0.5)\n",
    "schools.clip(selected_hexagon).plot(ax=ax, color=\"red\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "schools.clip(belem_hexs).shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "creche = \"INF_CRE\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "newhex_gdf = hex_gdf.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "del hex_gdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hex_gdf = newhex_gdf.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "education_levels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def plot_municipality_extra_classrooms(muni: str):\n",
    "    # Visualize the results for the selected municipality\n",
    "    muni_hexs = hex_gdf[hex_gdf[\"name_muni\"] == muni]\n",
    "\n",
    "    # Create a figure with one map for each level\n",
    "    fig, axes = plt.subplots(\n",
    "        1, len(education_levels), figsize=(len(education_levels) * 10, 8)\n",
    "    )\n",
    "\n",
    "    fig.suptitle(f\"Number of extra classrooms needed in {muni}\", fontsize=16)\n",
    "\n",
    "    v_min = (\n",
    "        muni_hexs[[f\"QT_SALAS_NECESARIAS_EXTRA_{level}\" for level in education_levels]]\n",
    "        .min()\n",
    "        .min()\n",
    "    )\n",
    "    v_max = (\n",
    "        muni_hexs[[f\"QT_SALAS_NECESARIAS_EXTRA_{level}\" for level in education_levels]]\n",
    "        .max()\n",
    "        .max()\n",
    "    )\n",
    "\n",
    "    for i, level in enumerate(education_levels):\n",
    "        plot_params = dict(\n",
    "            alpha=0.5,\n",
    "            legend=False,\n",
    "            cmap=\"RdYlGn_r\",\n",
    "            ax=axes[i],\n",
    "            missing_kwds={\"color\": \"lightgrey\"},\n",
    "        )\n",
    "        if i == len(education_levels) - 1:\n",
    "            divider = make_axes_locatable(axes[i])\n",
    "            plot_params[\"cax\"] = divider.append_axes(\"right\", size=\"5%\", pad=0.1)\n",
    "            plot_params[\"legend\"] = True\n",
    "            plot_params[\"vmin\"] = v_min\n",
    "            plot_params[\"vmax\"] = v_max\n",
    "        ax = muni_hexs.plot(f\"QT_SALAS_NECESARIAS_EXTRA_{level}\", **plot_params)\n",
    "        ax.set_axis_off()\n",
    "        ax.set_title(f\"Edu Level: {level}\")\n",
    "        ctx.add_basemap(\n",
    "            ax, source=ctx.providers.CartoDB.Positron, crs=hex_gdf.crs, attribution=\"\"\n",
    "        )\n",
    "\n",
    "    plt.tight_layout()\n",
    "\n",
    "    output_filename = (\n",
    "        Path(\"outputs/maps/para_extra_classrooms\") / f\"{muni}_extra_classrooms.png\"\n",
    "    )\n",
    "    output_filename.parent.mkdir(parents=True, exist_ok=True)\n",
    "\n",
    "    if not output_filename.exists():\n",
    "        plt.savefig(output_filename, dpi=300, bbox_inches=\"tight\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "muni_gdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "muni_gdf.parallel_apply(\n",
    "    lambda x: plot_municipality_extra_classrooms(x[\"name_muni\"]), axis=1\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "metrics_per_education_level = {}\n",
    "\n",
    "for level in education_levels:\n",
    "\n",
    "    number_of_students = hex_gdf[f\"QT_MAT_{level}\"].sum().round()\n",
    "\n",
    "    full_time_percentage = (\n",
    "        hex_gdf[f\"QT_MAT_{level}_INT\"].sum() / number_of_students * 100\n",
    "    ).round(2)\n",
    "\n",
    "    if level in [\"INF_CRE\", \"INF_PRE\", \"FUND_AI\"]:\n",
    "        night_percentage = \"N/A\"  # Small children don't attend night classes\n",
    "    else:\n",
    "        night_percentage = (\n",
    "            hex_gdf[f\"QT_MAT_{level}_N\"].sum() / number_of_students * 100\n",
    "        ).round(2)\n",
    "\n",
    "    number_of_chairs = number_of_students * (\n",
    "        1 + full_time_percentage / number_of_students\n",
    "    ).round(2)\n",
    "\n",
    "    total_classrooms = (number_of_chairs / chairs_per_classroom).round()\n",
    "\n",
    "    current_classrooms = ()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot population by municipality\n",
    "\n",
    "# Use sjoin to get the municipality of each hexagon\n",
    "hex_gdf = gpd.sjoin(hex_gdf, para_muni, how=\"left\", op=\"intersects\")\n",
    "\n",
    "# Group hexagons by municipality and calculate the population sum for all population columns\n",
    "pop_cols = [col for col in hex_gdf.columns if \"pop\" in col]\n",
    "pop_by_muni = hex_gdf.groupby(\"name_muni\")[pop_cols].sum().reset_index()\n",
    "\n",
    "# Use merge to get the geometry of the municipalities\n",
    "pop_by_muni = pop_by_muni.merge(para_muni[[\"name_muni\", \"geometry\"]], on=\"name_muni\")\n",
    "\n",
    "# Plot the population of each municipality\n",
    "fig = px.choropleth_mapbox(\n",
    "    pop_by_muni,\n",
    "    geojson=pop_by_muni.geometry,\n",
    "    locations=pop_by_muni.index,\n",
    "    color=\"pop_total\",\n",
    "    mapbox_style=\"carto-positron\",\n",
    "    center={\"lat\": -3.29, \"lon\": -52.71},\n",
    "    zoom=5,\n",
    "    opacity=0.5,\n",
    "    labels={\"pop_total\": \"Population\"},\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group hexagons by municipality and calculate the population sum for all population columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'hex',\n",
    " 'geometry',\n",
    "# Population \n",
    " 'population_2020',\n",
    " 'pop_3_5_years_adj',\n",
    " 'pop_6_14_years_adj',\n",
    " 'pop_15_17_years_adj',\n",
    " 'pop_18_years_adj',\n",
    "# Socioeconomic\n",
    " 'V002_adj', # Number of residents\n",
    " 'V003_adj', # Number of residents with income \n",
    " 'income_pc',\n",
    "'urban_area'\n",
    "# Schools\n",
    "## Count by level\n",
    " 'ensino_fundamental',\n",
    " 'educacao_infantil',\n",
    " 'ensino_medio',\n",
    " 'educacao_jovens_adultos',\n",
    " 'educacao_profissional',\n",
    " 'no_specified',\n",
    "# Count by private/public\n",
    " 'privada',\n",
    " 'publica',\n",
    "# Count by type of public school\n",
    "'federal',\n",
    " 'estadual',\n",
    " 'municipal',"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import geopandas as gpd\n",
    "import contextily as ctx\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# Define the columns to plot\n",
    "columns = [\n",
    "    \"population_2020\",\n",
    "    \"pop_3_5_years_adj\",\n",
    "    \"pop_6_14_years_adj\",\n",
    "    \"pop_15_17_years_adj\",\n",
    "    \"pop_18_years_adj\",\n",
    "]\n",
    "\n",
    "# Create subplots\n",
    "fig, axes = plt.subplots(nrows=1, ncols=len(columns), figsize=(15, 5))\n",
    "\n",
    "# Set a shared colorscale for all the maps\n",
    "vmin = hex_gdf[columns].min().min()\n",
    "vmax = hex_gdf[columns].max().max()\n",
    "\n",
    "# Plot choropleth maps for each column\n",
    "for i, column in enumerate(columns):\n",
    "    # Plot the choropleth map\n",
    "    hex_gdf.plot(\n",
    "        column=column,\n",
    "        cmap=\"Blues\",\n",
    "        linewidth=0.8,\n",
    "        ax=axes[i],\n",
    "        edgecolor=\"0.8\",\n",
    "        legend=False,\n",
    "    )\n",
    "\n",
    "    # Set plot title with clean labels\n",
    "    clean_label = column.replace(\"_\", \" \").title()\n",
    "    axes[i].set_title(clean_label)\n",
    "\n",
    "    # Remove axis ticks\n",
    "    axes[i].set_xticks([])\n",
    "    axes[i].set_yticks([])\n",
    "\n",
    "    # Add basemap using contextily\n",
    "    ctx.add_basemap(\n",
    "        ax=axes[i], crs=hex_gdf.crs.to_string(), source=ctx.providers.CartoDB.Positron\n",
    "    )\n",
    "\n",
    "# Adjust the spacing between subplots\n",
    "plt.tight_layout()\n",
    "\n",
    "# Show the plot\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "education_levels = [\"INF_CRE\", \"INF_PRE\", \"FUND_AI\", \"FUND_AF\", \"MED\"]\n",
    "metrics_per_education_level = {}\n",
    "for level in education_levels:\n",
    "    number_of_students = hex_gdf[f\"QT_MAT_{level}\"].sum().round()\n",
    "    full_time_percentage = (\n",
    "        hex_gdf[f\"QT_MAT_{level}_INT\"].sum() / number_of_students * 100\n",
    "    ).round(2)\n",
    "    if level in [\"INF_CRE\", \"INF_PRE\", \"FUND_AI\"]:\n",
    "        night_percentage = \"N/A\"  # Small children don't attend night classes\n",
    "    else:\n",
    "        night_percentage = (\n",
    "            hex_gdf[f\"QT_MAT_{level}_N\"].sum() / number_of_students * 100\n",
    "        ).round(2)\n",
    "    number_of_chairs = number_of_students * (\n",
    "        1 + full_time_percentage / number_of_students\n",
    "    ).round(2)\n",
    "    total_classrooms = (number_of_chairs / chairs_per_classroom).round()\n",
    "    current_classrooms = ()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calculate_table_data(hex_gdf):\n",
    "    # Placeholder values for the number of chairs per classroom\n",
    "    chairs_per_classroom = 25\n",
    "\n",
    "    education_levels = [\"INF_CRE\", \"INF_PRE\", \"FUND_AI\", \"FUND_AF\", \"MED\"]\n",
    "    metrics_per_education_level = {}\n",
    "    for level in education_levels:\n",
    "        number_of_students = hex_gdf[f\"QT_MAT_{level}\"].sum().round()\n",
    "        full_time_percentage = (\n",
    "            hex_gdf[f\"QT_MAT_{level}_INT\"].sum() / number_of_students * 100\n",
    "        ).round(2)\n",
    "        if level in [\"INF_CRE\", \"INF_PRE\", \"FUND_AI\"]:\n",
    "            night_percentage = \"N/A\"  # Small children don't attend night classes\n",
    "        else:\n",
    "            night_percentage = (\n",
    "                hex_gdf[f\"QT_MAT_{level}_N\"].sum() / number_of_students * 100\n",
    "            ).round(2)\n",
    "        number_of_chairs = number_of_students * (\n",
    "            1 + full_time_percentage / number_of_students\n",
    "        ).round(2)\n",
    "        total_classrooms = (number_of_chairs / chairs_per_classroom).round()\n",
    "        current_classrooms = ()\n",
    "\n",
    "    return table_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not selected_municipality:\n",
    "    return \"\"\n",
    "# Filter the hexagons for the selected municipality\n",
    "muni_hexagons = hex_gdf[\n",
    "    hex_gdf.intersects(\n",
    "        para_muni[para_muni[\"name_muni\"] == selected_municipality].geometry.iloc[0]\n",
    "    )\n",
    "]\n",
    "# Calculate the values for each metric\n",
    "print([col for col in muni_hexagons.columns if \"QT_MAT\" in col])\n",
    "\n",
    "# Placeholder values for the number of chairs per classroom\n",
    "number_of_chairs_per_students = 25"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    " # Calculate the values for each metric\n",
    "    table_data = pd.DataFrame(\n",
    "        {\n",
    "            \"Metric\": [\n",
    "                \"Alunos\",\n",
    "                \"% Tempo Integral\",\n",
    "                \"% Noturno\",\n",
    "                \"# de Cadeiras\",\n",
    "                \"# Cadeiras / Sala\",\n",
    "                \"Total Salas Necessárias\",\n",
    "                \"Salas Atuais\",\n",
    "                \"Salas Faltantes\",\n",
    "            ],\n",
    "            \"Creche\": [\n",
    "                int(hex_gdf[\"QT_MAT_INF_CRE\"].sum()),\n",
    "                round(\n",
    "                    hex_gdf[\"QT_MAT_INF_CRE_INT\"].sum()\n",
    "                    / hex_gdf[\"QT_MAT_INF_CRE\"].sum()\n",
    "                    * 100,\n",
    "                    2,\n",
    "                ),\n",
    "                \"N/A\",\n",
    "                int(\n",
    "                    hex_gdf[\"QT_MAT_INF_CRE\"].sum()\n",
    "                    * (\n",
    "                        1\n",
    "                        + hex_gdf[\"QT_MAT_INF_CRE_INT\"].sum()\n",
    "                        / hex_gdf[\"QT_MAT_INF_CRE\"].sum()\n",
    "                    )\n",
    "                ),\n",
    "                number_of_chairs_per_students,\n",
    "                int(\n",
    "                    hex_gdf[\"QT_MAT_INF_CRE\"].sum()\n",
    "                    * (\n",
    "                        1\n",
    "                        + hex_gdf[\"QT_MAT_INF_CRE_INT\"].sum()\n",
    "                        / hex_gdf[\"QT_MAT_INF_CRE\"].sum()\n",
    "                    )\n",
    "                    / number_of_chairs_per_students\n",
    "                ),\n",
    "                np.nan,\n",
    "                np.nan,\n",
    "            ],\n",
    "            \"Pre-escola\": [\n",
    "                int(hex_gdf[\"QT_MAT_INF_PRE\"].sum()),\n",
    "                round(\n",
    "                    hex_gdf[\"QT_MAT_INF_PRE_INT\"].sum()\n",
    "                    / hex_gdf[\"QT_MAT_INF_PRE\"].sum()\n",
    "                    * 100,\n",
    "                    2,\n",
    "                ),\n",
    "                \"N/A\",\n",
    "                int(hex_gdf[\"QT_MAT_MED\"].sum()),\n",
    "                round(\n",
    "                    hex_gdf[\"QT_MAT_MED_INT\"].sum() / hex_gdf[\"QT_MAT_MED\"].sum() * 100,\n",
    "                    2,\n",
    "                ),\n",
    "                \"N/A\",\n",
    "                int(\n",
    "                    hex_gdf[\"QT_MAT_MED\"].sum()\n",
    "                    * (\n",
    "                        1\n",
    "                        + hex_gdf[\"QT_MAT_MED_INT\"].sum() / hex_gdf[\"QT_MAT_MED\"].sum()\n",
    "                    )\n",
    "                    * (1 - hex_gdf[\"QT_MAT_MED_N\"].sum() / hex_gdf[\"QT_MAT_MED\"].sum())\n",
    "                ),\n",
    "                number_of_chairs_per_students,\n",
    "                int(\n",
    "                    hex_gdf[\"QT_MAT_MED\"].sum()\n",
    "                    * (\n",
    "                        1\n",
    "                        + hex_gdf[\"QT_MAT_MED_INT\"].sum() / hex_gdf[\"QT_MAT_MED\"].sum()\n",
    "                    )\n",
    "                    * (1 - hex_gdf[\"QT_MAT_MED_N\"].sum() / hex_gdf[\"QT_MAT_MED\"].sum())\n",
    "                    / number_of_chairs_per_students\n",
    "                ),\n",
    "                np.nan,\n",
    "                np.nan,\n",
    "            ],\n",
    "        }\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    " if not selected_municipality:\n",
    "        return \"\"\n",
    "    # Filter the hexagons for the selected municipality\n",
    "    muni_hexagons = hex_gdf[\n",
    "        hex_gdf.intersects(\n",
    "            para_muni[para_muni[\"name_muni\"] == selected_municipality].geometry.iloc[0]\n",
    "        )\n",
    "    ]\n",
    "    # Calculate the values for each metric\n",
    "    print([col for col in muni_hexagons.columns if \"QT_MAT\" in col])\n",
    "\n",
    "    # Placeholder values for the number of chairs per classroom\n",
    "    number_of_chairs_per_students = 25\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".env",
   "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.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}