07_csvp_altair.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7b1593f4-b288-49fe-9a5d-99727a03e70c",
   "metadata": {
    "id": "7b1593f4-b288-49fe-9a5d-99727a03e70c"
   },
   "outputs": [],
   "source": [
    "import altair as alt\n",
    "import pandas as pd\n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "24b5a8c7-518a-4594-84bc-a454664bad7c",
   "metadata": {
    "id": "24b5a8c7-518a-4594-84bc-a454664bad7c"
   },
   "outputs": [],
   "source": [
    "# %load const\n",
    "CO_STO_AGOS_NL = 33087679\n",
    "CO_STO_AGO_INST = 33176825\n",
    "CO_STO_AGO = 33020566\n",
    "CO_SAO_BENTO = 33062633\n",
    "CO_SAO_VICENTE = 33063648\n",
    "CO_PARQUE = 33065837\n",
    "CO_ELEVA = 33178860\n",
    "CO_ST_INACIO = 33063729\n",
    "\n",
    "CO_MUN = 3304557  # Rio de Janeiro\n",
    "CO_UF_RIO = 33  # RJ\n",
    "\n",
    "MIN_ALUNOS = 30  # Mínimo de alunos fazendo Enem para considerar na análise\n",
    "NUM_MELHORES = 60\n",
    "ANO_ULT = 2019  # último ano a processar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "LQ6Gq0HAsUoi",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17
    },
    "id": "LQ6Gq0HAsUoi",
    "outputId": "1c7b8a4f-6046-4e80-a6b7-df7c6a2667ec"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>\n",
       "@import url('https://fonts.googleapis.com/css?family=Lato');\n",
       "</style>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%html\n",
    "<style>\n",
    "@import url('https://fonts.googleapis.com/css?family=Lato');\n",
    "</style>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "_I8MfzkaspCx",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "_I8MfzkaspCx",
    "outputId": "03cc4e92-4aa2-4b68-8279-a415879b3d00"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ThemeRegistry.enable('my_custom_theme')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def urban_theme():\n",
    "    markColor = \"#1696d2\"\n",
    "    axisColor = \"#000000\"\n",
    "    backgroundColor = \"#FFFFFF\"\n",
    "    font = \"Lato\"\n",
    "    labelFont = \"Lato\"\n",
    "    sourceFont = \"Lato\"\n",
    "    gridColor = \"#DEDDDD\"\n",
    "    main_palette = [\"#1696d2\", \n",
    "                    \"#d2d2d2\",\n",
    "                    \"#000000\", \n",
    "                    \"#fdbf11\", \n",
    "                    \"#ec008b\", \n",
    "                    \"#55b748\", \n",
    "                    \"#5c5859\", \n",
    "                    \"#db2b27\", \n",
    "                   ]\n",
    "    sequential_palette = [\"#cfe8f3\", \n",
    "                          \"#a2d4ec\", \n",
    "                          \"#73bfe2\", \n",
    "                          \"#46abdb\", \n",
    "                          \"#1696d2\", \n",
    "                          \"#12719e\", \n",
    "                         ]\n",
    "    return {\n",
    "        \"width\": 685,\n",
    "        \"height\": 380,   \n",
    "#        \"autosize\": \"fit\",\n",
    "        \"config\": {\n",
    "            \"title\": {\n",
    "                \"anchor\": \"start\",\n",
    "                \"fontSize\": 18,\n",
    "                \"font\": font,\n",
    "                \"fontColor\": \"#000000\"\n",
    "            },\n",
    "            \"axisX\": {\n",
    "               \"domain\": True,\n",
    "               \"domainColor\": axisColor,\n",
    "               \"domainWidth\": 1,\n",
    "               \"grid\": False,\n",
    "               \"labelFontSize\": 12,\n",
    "               \"labelFont\": labelFont,\n",
    "               \"labelAngle\": 0,\n",
    "               \"tickColor\": axisColor,\n",
    "               \"tickSize\": 5,\n",
    "               \"titleFontSize\": 12,\n",
    "               \"titlePadding\": 10,\n",
    "               \"titleFont\": font,\n",
    "               \"title\": \"\",\n",
    "           },\n",
    "           \"axisY\": {\n",
    "               \"domain\": False,\n",
    "               \"grid\": True,\n",
    "               \"gridColor\": gridColor,\n",
    "               \"gridWidth\": 1,\n",
    "               \"labelFontSize\": 12,\n",
    "               \"labelFont\": labelFont,\n",
    "               \"labelPadding\": 8,\n",
    "               \"ticks\": False,\n",
    "               \"titleFontSize\": 12,\n",
    "               \"titlePadding\": 10,\n",
    "               \"titleFont\": font,\n",
    "               \"titleAngle\": 0,\n",
    "               \"titleY\": -10,\n",
    "               \"titleX\": 18,\n",
    "           },\n",
    "           \"background\": backgroundColor,\n",
    "           \"legend\": {\n",
    "               \"labelFontSize\": 12,\n",
    "               \"labelFont\": labelFont,\n",
    "               \"symbolSize\": 100,\n",
    "               \"symbolType\": \"square\",\n",
    "               \"titleFontSize\": 12,\n",
    "               \"titlePadding\": 10,\n",
    "               \"titleFont\": font,\n",
    "               \"title\": \"\",\n",
    "               \"orient\": \"top-left\",\n",
    "               \"offset\": 0,\n",
    "           },\n",
    "           \"view\": {\n",
    "               \"stroke\": \"transparent\",\n",
    "           },\n",
    "           \"range\": {\n",
    "               \"category\": main_palette,\n",
    "               \"diverging\": sequential_palette,\n",
    "           },\n",
    "           \"area\": {\n",
    "               \"fill\": markColor,\n",
    "           },\n",
    "           \"line\": {\n",
    "               \"color\": markColor,\n",
    "               \"stroke\": markColor,\n",
    "               \"strokewidth\": 5,\n",
    "           },\n",
    "           \"trail\": {\n",
    "               \"color\": markColor,\n",
    "               \"stroke\": markColor,\n",
    "               \"strokeWidth\": 0,\n",
    "               \"size\": 1,\n",
    "           },\n",
    "           \"path\": {\n",
    "               \"stroke\": markColor,\n",
    "               \"strokeWidth\": 0.5,\n",
    "           },\n",
    "           \"point\": {\n",
    "               \"filled\": True,\n",
    "           },\n",
    "           \"text\": {\n",
    "               \"font\": sourceFont,\n",
    "               \"color\": markColor,\n",
    "               \"fontSize\": 11,\n",
    "               \"align\": \"right\",\n",
    "               \"fontWeight\": 400,\n",
    "               \"size\": 11,\n",
    "           }, \n",
    "           \"bar\": {\n",
    "                \"size\": 40,\n",
    "                \"binSpacing\": 1,\n",
    "                \"continuousBandSize\": 30,\n",
    "                \"discreteBandSize\": 30,\n",
    "                \"fill\": markColor,\n",
    "                \"stroke\": False,\n",
    "            }, \n",
    "       },\n",
    "    }\n",
    "    \n",
    "    \n",
    "import altair as alt\n",
    "alt.themes.register(\"my_custom_theme\", urban_theme)\n",
    "alt.themes.enable(\"my_custom_theme\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "QGJ0UPRrkVP3",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "QGJ0UPRrkVP3",
    "outputId": "90506c25-f91d-4d6c-ff89-f532bf6459e7"
   },
   "outputs": [],
   "source": [
    "COLABORATORY = False\n",
    "\n",
    "if COLABORATORY and 'dados_dir' not in vars(): \n",
    "  from google.colab import drive\n",
    "  drive.mount('/drive')\n",
    "\n",
    "if COLABORATORY:\n",
    "  dados_dir = Path('/drive/MyDrive/Colab Notebooks/educacao/')\n",
    "else:\n",
    "  dados_dir = Path('./dados')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0babe1c4-e3e2-40fe-93f6-bcfb2d4fef1e",
   "metadata": {
    "id": "0babe1c4-e3e2-40fe-93f6-bcfb2d4fef1e"
   },
   "source": [
    "## Colégio a processar\n",
    "Mudar abaixo para ver outros colégios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "121c0e2e-45df-4bcf-811e-d92496f3dc76",
   "metadata": {
    "id": "121c0e2e-45df-4bcf-811e-d92496f3dc76"
   },
   "outputs": [],
   "source": [
    "CO_PROCESSAR = CO_PARQUE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1f7ae5b4-7c12-416a-bf80-2a01830d4c92",
   "metadata": {
    "id": "1f7ae5b4-7c12-416a-bf80-2a01830d4c92"
   },
   "outputs": [],
   "source": [
    "df_turmas_rio = pd.read_feather(dados_dir / \"turmas_rio.feather\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "2f404ff5-da70-4005-9e53-5dccfbfbcfe2",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "2f404ff5-da70-4005-9e53-5dccfbfbcfe2",
    "outputId": "59317ebb-d531-47a3-e646-5730b97d7fcb"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2019"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_turmas_rio.ano.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "CzJyKFHbsyZL",
   "metadata": {
    "id": "CzJyKFHbsyZL"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a8f590f0-3816-4e7b-a75e-b0dc81f615ce",
   "metadata": {
    "id": "a8f590f0-3816-4e7b-a75e-b0dc81f615ce"
   },
   "outputs": [],
   "source": [
    "em_etapas = tuple(range(25, 39))  # são vicente deveria ter só 25-27\n",
    "df_medio = df_turmas_rio[df_turmas_rio.id_etapa.isin(em_etapas) & (df_turmas_rio.id_escola == CO_SAO_VICENTE)]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "BSnT4zhAs87q",
   "metadata": {
    "id": "BSnT4zhAs87q"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "10fdf564-fc4d-47a9-81a6-02e5b4b675ae",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 435
    },
    "id": "10fdf564-fc4d-47a9-81a6-02e5b4b675ae",
    "outputId": "4d1bc307-9f05-4dfe-c3ea-744bc5fb5afb"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<div id=\"altair-viz-7c369687b7314dfdbac97084d91cf459\"></div>\n",
       "<script type=\"text/javascript\">\n",
       "  (function(spec, embedOpt){\n",
       "    let outputDiv = document.currentScript.previousElementSibling;\n",
       "    if (outputDiv.id !== \"altair-viz-7c369687b7314dfdbac97084d91cf459\") {\n",
       "      outputDiv = document.getElementById(\"altair-viz-7c369687b7314dfdbac97084d91cf459\");\n",
       "    }\n",
       "    const paths = {\n",
       "      \"vega\": \"https://cdn.jsdelivr.net/npm//vega@5?noext\",\n",
       "      \"vega-lib\": \"https://cdn.jsdelivr.net/npm//vega-lib?noext\",\n",
       "      \"vega-lite\": \"https://cdn.jsdelivr.net/npm//vega-lite@4.8.1?noext\",\n",
       "      \"vega-embed\": \"https://cdn.jsdelivr.net/npm//vega-embed@6?noext\",\n",
       "    };\n",
       "\n",
       "    function loadScript(lib) {\n",
       "      return new Promise(function(resolve, reject) {\n",
       "        var s = document.createElement('script');\n",
       "        s.src = paths[lib];\n",
       "        s.async = true;\n",
       "        s.onload = () => resolve(paths[lib]);\n",
       "        s.onerror = () => reject(`Error loading script: ${paths[lib]}`);\n",
       "        document.getElementsByTagName(\"head\")[0].appendChild(s);\n",
       "      });\n",
       "    }\n",
       "\n",
       "    function showError(err) {\n",
       "      outputDiv.innerHTML = `<div class=\"error\" style=\"color:red;\">${err}</div>`;\n",
       "      throw err;\n",
       "    }\n",
       "\n",
       "    function displayChart(vegaEmbed) {\n",
       "      vegaEmbed(outputDiv, spec, embedOpt)\n",
       "        .catch(err => showError(`Javascript Error: ${err.message}<br>This usually means there's a typo in your chart specification. See the javascript console for the full traceback.`));\n",
       "    }\n",
       "\n",
       "    if(typeof define === \"function\" && define.amd) {\n",
       "      requirejs.config({paths});\n",
       "      require([\"vega-embed\"], displayChart, err => showError(`Error loading script: ${err.message}`));\n",
       "    } else if (typeof vegaEmbed === \"function\") {\n",
       "      displayChart(vegaEmbed);\n",
       "    } else {\n",
       "      loadScript(\"vega\")\n",
       "        .then(() => loadScript(\"vega-lite\"))\n",
       "        .then(() => loadScript(\"vega-embed\"))\n",
       "        .catch(showError)\n",
       "        .then(() => displayChart(vegaEmbed));\n",
       "    }\n",
       "  })({\"width\": 685, \"height\": 380, \"config\": {\"title\": {\"anchor\": \"start\", \"fontSize\": 18, \"font\": \"Lato\", \"fontColor\": \"#000000\"}, \"axisX\": {\"domain\": true, \"domainColor\": \"#000000\", \"domainWidth\": 1, \"grid\": false, \"labelFontSize\": 12, \"labelFont\": \"Lato\", \"labelAngle\": 0, \"tickColor\": \"#000000\", \"tickSize\": 5, \"titleFontSize\": 12, \"titlePadding\": 10, \"titleFont\": \"Lato\", \"title\": \"\"}, \"axisY\": {\"domain\": false, \"grid\": true, \"gridColor\": \"#DEDDDD\", \"gridWidth\": 1, \"labelFontSize\": 12, \"labelFont\": \"Lato\", \"labelPadding\": 8, \"ticks\": false, \"titleFontSize\": 12, \"titlePadding\": 10, \"titleFont\": \"Lato\", \"titleAngle\": 0, \"titleY\": -10, \"titleX\": 18}, \"background\": \"#FFFFFF\", \"legend\": {\"labelFontSize\": 12, \"labelFont\": \"Lato\", \"symbolSize\": 100, \"symbolType\": \"square\", \"titleFontSize\": 12, \"titlePadding\": 10, \"titleFont\": \"Lato\", \"title\": \"\", \"orient\": \"top-left\", \"offset\": 0}, \"view\": {\"stroke\": \"transparent\"}, \"range\": {\"category\": [\"#1696d2\", \"#d2d2d2\", \"#000000\", \"#fdbf11\", \"#ec008b\", \"#55b748\", \"#5c5859\", \"#db2b27\"], \"diverging\": [\"#cfe8f3\", \"#a2d4ec\", \"#73bfe2\", \"#46abdb\", \"#1696d2\", \"#12719e\"]}, \"area\": {\"fill\": \"#1696d2\"}, \"line\": {\"color\": \"#1696d2\", \"stroke\": \"#1696d2\", \"strokewidth\": 5}, \"trail\": {\"color\": \"#1696d2\", \"stroke\": \"#1696d2\", \"strokeWidth\": 0, \"size\": 1}, \"path\": {\"stroke\": \"#1696d2\", \"strokeWidth\": 0.5}, \"point\": {\"filled\": true}, \"text\": {\"font\": \"Lato\", \"color\": \"#1696d2\", \"fontSize\": 11, \"align\": \"right\", \"fontWeight\": 400, \"size\": 11}, \"bar\": {\"size\": 40, \"binSpacing\": 1, \"continuousBandSize\": 30, \"discreteBandSize\": 30, \"fill\": \"#1696d2\", \"stroke\": false}}, \"layer\": [{\"data\": {\"name\": \"data-18b2bf274c7f3464a5bcbe4e34cf4761\"}, \"mark\": \"line\", \"encoding\": {\"x\": {\"type\": \"ordinal\", \"axis\": {\"domain\": false, \"labelAngle\": 0}, \"field\": \"ano\", \"title\": null}, \"y\": {\"type\": \"quantitative\", \"field\": \"total\", \"scale\": {\"domain\": [308.5, 382.0]}, \"title\": null}}, \"width\": 500}, {\"data\": {\"name\": \"data-9c30d0f6268e948515706c1c5a6409dc\"}, \"mark\": {\"type\": \"line\", \"color\": \"red\"}, \"encoding\": {\"y\": {\"type\": \"quantitative\", \"field\": \"total\"}}}, {\"data\": {\"name\": \"data-9c30d0f6268e948515706c1c5a6409dc\"}, \"mark\": {\"type\": \"rule\", \"color\": \"orange\"}, \"encoding\": {\"y\": {\"type\": \"quantitative\", \"field\": \"total\"}}}], \"$schema\": \"https://vega.github.io/schema/vega-lite/v4.8.1.json\", \"datasets\": {\"data-18b2bf274c7f3464a5bcbe4e34cf4761\": [{\"ano\": 2007, \"total\": 357.0, \"no_turmas\": 10}, {\"ano\": 2008, \"total\": 369.0, \"no_turmas\": 10}, {\"ano\": 2009, \"total\": 361.0, \"no_turmas\": 10}, {\"ano\": 2010, \"total\": 377.0, \"no_turmas\": 10}, {\"ano\": 2011, \"total\": 353.0, \"no_turmas\": 10}, {\"ano\": 2012, \"total\": 343.0, \"no_turmas\": 10}, {\"ano\": 2013, \"total\": 355.0, \"no_turmas\": 10}, {\"ano\": 2014, \"total\": 382.0, \"no_turmas\": 10}, {\"ano\": 2015, \"total\": 369.0, \"no_turmas\": 10}, {\"ano\": 2016, \"total\": 349.0, \"no_turmas\": 11}, {\"ano\": 2017, \"total\": 333.0, \"no_turmas\": 10}, {\"ano\": 2018, \"total\": 338.0, \"no_turmas\": 10}, {\"ano\": 2019, \"total\": 350.0, \"no_turmas\": 11}], \"data-9c30d0f6268e948515706c1c5a6409dc\": [{\"total\": 356.61538461538464, \"no_turmas\": 10.153846153846153}]}}, {\"mode\": \"vega-lite\"});\n",
       "</script>"
      ],
      "text/plain": [
       "alt.LayerChart(...)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_medio_por_ano = df_medio.groupby(\"ano\").agg(\n",
    "    total=(\"num_matriculas\", \"sum\"), no_turmas=(\"index\", \"count\")\n",
    ")\n",
    "(\n",
    "    (\n",
    "        alt.Chart(df_medio_por_ano.reset_index())\n",
    "        .mark_line()\n",
    "        .encode(\n",
    "            y=alt.Y(\n",
    "                \"total\",\n",
    "                title=None,\n",
    "                scale=alt.Scale(\n",
    "                    domain=[\n",
    "                        df_medio_por_ano.total.min()\n",
    "                        - (df_medio_por_ano.total.max() - df_medio_por_ano.total.min())\n",
    "                        / 2,\n",
    "                        df_medio_por_ano.total.max(),\n",
    "                    ]\n",
    "                ),\n",
    "            ),\n",
    "            x=alt.X(\n",
    "                \"ano:O\",\n",
    "                title=None,\n",
    "                axis=alt.Axis(labelAngle=0, domain=False),\n",
    "            ),\n",
    "        )\n",
    "        .properties(\n",
    "            width=500,\n",
    "        )\n",
    "    )\n",
    "    + alt.Chart(df_medio_por_ano.mean().to_frame().T)\n",
    "    .mark_line(color=\"red\")\n",
    "    .encode(y=\"total\")\n",
    "    + alt.Chart(df_medio_por_ano.mean().to_frame().T)\n",
    "    .mark_rule(color=\"orange\")\n",
    "    .encode(y=\"total\")\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56c0f549-a557-4174-a33a-700c871d864f",
   "metadata": {
    "id": "56c0f549-a557-4174-a33a-700c871d864f"
   },
   "source": [
    "## Perguntas\n",
    "1. como tem variado o número de alunos do primeiro para o terceiro ano"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b606c2ed-a8ac-4500-975b-d28f8dd1bccf",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "b606c2ed-a8ac-4500-975b-d28f8dd1bccf",
    "outputId": "dabfe3b2-cca6-49e1-d342-1d21bd3a4b39"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ano</th>\n",
       "      <th>Série</th>\n",
       "      <th>num_matriculas</th>\n",
       "      <th>num_turmas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007</td>\n",
       "      <td>1º</td>\n",
       "      <td>134</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2007</td>\n",
       "      <td>2º</td>\n",
       "      <td>87</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2007</td>\n",
       "      <td>3º</td>\n",
       "      <td>136</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2008</td>\n",
       "      <td>1º</td>\n",
       "      <td>143</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2008</td>\n",
       "      <td>2º</td>\n",
       "      <td>107</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ano Série  num_matriculas  num_turmas\n",
       "0  2007    1º             134           4\n",
       "1  2007    2º              87           2\n",
       "2  2007    3º             136           4\n",
       "3  2008    1º             143           4\n",
       "4  2008    2º             107           3"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_medio_sv = df_medio[[\"ano\", \"id_etapa\", \"num_matriculas\"]].groupby([\"ano\", \"id_etapa\"]).aggregate(\n",
    "    num_matriculas=(\"num_matriculas\", sum), num_turmas=(\"num_matriculas\", \"count\")\n",
    ").rename({25:'1º', 26: '2º', 27: '3º'}).reset_index().rename(columns={'id_etapa': 'Série'})#.unstack()['num_matriculas'].rename(columns={25:'1º ano', 26: '2º ano', 27: '3º ano'}).reset_index()\n",
    "df_medio_sv.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "cea95124-7a23-44cd-ace0-9ac4cc4a2f1f",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 452
    },
    "id": "cea95124-7a23-44cd-ace0-9ac4cc4a2f1f",
    "outputId": "e31b56da-20df-49b3-f4c0-d66a202f7595"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<div id=\"altair-viz-ee7d6598eea54ac9affc43faac60f6bf\"></div>\n",
       "<script type=\"text/javascript\">\n",
       "  (function(spec, embedOpt){\n",
       "    let outputDiv = document.currentScript.previousElementSibling;\n",
       "    if (outputDiv.id !== \"altair-viz-ee7d6598eea54ac9affc43faac60f6bf\") {\n",
       "      outputDiv = document.getElementById(\"altair-viz-ee7d6598eea54ac9affc43faac60f6bf\");\n",
       "    }\n",
       "    const paths = {\n",
       "      \"vega\": \"https://cdn.jsdelivr.net/npm//vega@5?noext\",\n",
       "      \"vega-lib\": \"https://cdn.jsdelivr.net/npm//vega-lib?noext\",\n",
       "      \"vega-lite\": \"https://cdn.jsdelivr.net/npm//vega-lite@4.8.1?noext\",\n",
       "      \"vega-embed\": \"https://cdn.jsdelivr.net/npm//vega-embed@6?noext\",\n",
       "    };\n",
       "\n",
       "    function loadScript(lib) {\n",
       "      return new Promise(function(resolve, reject) {\n",
       "        var s = document.createElement('script');\n",
       "        s.src = paths[lib];\n",
       "        s.async = true;\n",
       "        s.onload = () => resolve(paths[lib]);\n",
       "        s.onerror = () => reject(`Error loading script: ${paths[lib]}`);\n",
       "        document.getElementsByTagName(\"head\")[0].appendChild(s);\n",
       "      });\n",
       "    }\n",
       "\n",
       "    function showError(err) {\n",
       "      outputDiv.innerHTML = `<div class=\"error\" style=\"color:red;\">${err}</div>`;\n",
       "      throw err;\n",
       "    }\n",
       "\n",
       "    function displayChart(vegaEmbed) {\n",
       "      vegaEmbed(outputDiv, spec, embedOpt)\n",
       "        .catch(err => showError(`Javascript Error: ${err.message}<br>This usually means there's a typo in your chart specification. See the javascript console for the full traceback.`));\n",
       "    }\n",
       "\n",
       "    if(typeof define === \"function\" && define.amd) {\n",
       "      requirejs.config({paths});\n",
       "      require([\"vega-embed\"], displayChart, err => showError(`Error loading script: ${err.message}`));\n",
       "    } else if (typeof vegaEmbed === \"function\") {\n",
       "      displayChart(vegaEmbed);\n",
       "    } else {\n",
       "      loadScript(\"vega\")\n",
       "        .then(() => loadScript(\"vega-lite\"))\n",
       "        .then(() => loadScript(\"vega-embed\"))\n",
       "        .catch(showError)\n",
       "        .then(() => displayChart(vegaEmbed));\n",
       "    }\n",
       "  })({\"width\": 685, \"height\": 380, \"config\": {\"title\": {\"anchor\": \"start\", \"fontSize\": 18, \"font\": \"Lato\", \"fontColor\": \"#000000\"}, \"axisX\": {\"domain\": true, \"domainColor\": \"#000000\", \"domainWidth\": 1, \"grid\": false, \"labelFontSize\": 12, \"labelFont\": \"Lato\", \"labelAngle\": 0, \"tickColor\": \"#000000\", \"tickSize\": 5, \"titleFontSize\": 12, \"titlePadding\": 10, \"titleFont\": \"Lato\", \"title\": \"\"}, \"axisY\": {\"domain\": false, \"grid\": true, \"gridColor\": \"#DEDDDD\", \"gridWidth\": 1, \"labelFontSize\": 12, \"labelFont\": \"Lato\", \"labelPadding\": 8, \"ticks\": false, \"titleFontSize\": 12, \"titlePadding\": 10, \"titleFont\": \"Lato\", \"titleAngle\": 0, \"titleY\": -10, \"titleX\": 18}, \"background\": \"#FFFFFF\", \"legend\": {\"labelFontSize\": 12, \"labelFont\": \"Lato\", \"symbolSize\": 100, \"symbolType\": \"square\", \"titleFontSize\": 12, \"titlePadding\": 10, \"titleFont\": \"Lato\", \"title\": \"\", \"orient\": \"top-left\", \"offset\": 0}, \"view\": {\"stroke\": \"transparent\"}, \"range\": {\"category\": [\"#1696d2\", \"#d2d2d2\", \"#000000\", \"#fdbf11\", \"#ec008b\", \"#55b748\", \"#5c5859\", \"#db2b27\"], \"diverging\": [\"#cfe8f3\", \"#a2d4ec\", \"#73bfe2\", \"#46abdb\", \"#1696d2\", \"#12719e\"]}, \"area\": {\"fill\": \"#1696d2\"}, \"line\": {\"color\": \"#1696d2\", \"stroke\": \"#1696d2\", \"strokewidth\": 5}, \"trail\": {\"color\": \"#1696d2\", \"stroke\": \"#1696d2\", \"strokeWidth\": 0, \"size\": 1}, \"path\": {\"stroke\": \"#1696d2\", \"strokeWidth\": 0.5}, \"point\": {\"filled\": true}, \"text\": {\"font\": \"Lato\", \"color\": \"#1696d2\", \"fontSize\": 11, \"align\": \"right\", \"fontWeight\": 400, \"size\": 11}, \"bar\": {\"size\": 40, \"binSpacing\": 1, \"continuousBandSize\": 30, \"discreteBandSize\": 30, \"fill\": \"#1696d2\", \"stroke\": false}}, \"data\": {\"name\": \"data-1064c423a7d32669a922e59a565a1f84\"}, \"mark\": \"line\", \"encoding\": {\"color\": {\"type\": \"nominal\", \"field\": \"S\\u00e9rie\"}, \"tooltip\": [{\"type\": \"quantitative\", \"field\": \"num_matriculas\"}, {\"type\": \"quantitative\", \"field\": \"num_turmas\"}], \"x\": {\"type\": \"quantitative\", \"field\": \"ano\"}, \"y\": {\"type\": \"quantitative\", \"field\": \"num_matriculas\"}}, \"$schema\": \"https://vega.github.io/schema/vega-lite/v4.8.1.json\", \"datasets\": {\"data-1064c423a7d32669a922e59a565a1f84\": [{\"ano\": 2007, \"S\\u00e9rie\": \"1\\u00ba\", \"num_matriculas\": 134, \"num_turmas\": 4}, {\"ano\": 2007, \"S\\u00e9rie\": \"2\\u00ba\", \"num_matriculas\": 87, \"num_turmas\": 2}, {\"ano\": 2007, \"S\\u00e9rie\": \"3\\u00ba\", \"num_matriculas\": 136, \"num_turmas\": 4}, {\"ano\": 2008, \"S\\u00e9rie\": \"1\\u00ba\", \"num_matriculas\": 143, \"num_turmas\": 4}, {\"ano\": 2008, \"S\\u00e9rie\": \"2\\u00ba\", \"num_matriculas\": 107, \"num_turmas\": 3}, {\"ano\": 2008, \"S\\u00e9rie\": \"3\\u00ba\", \"num_matriculas\": 119, \"num_turmas\": 3}, {\"ano\": 2009, \"S\\u00e9rie\": \"1\\u00ba\", \"num_matriculas\": 136, \"num_turmas\": 4}, {\"ano\": 2009, \"S\\u00e9rie\": \"2\\u00ba\", \"num_matriculas\": 127, \"num_turmas\": 3}, {\"ano\": 2009, \"S\\u00e9rie\": \"3\\u00ba\", \"num_matriculas\": 98, \"num_turmas\": 3}, {\"ano\": 2010, \"S\\u00e9rie\": \"1\\u00ba\", \"num_matriculas\": 146, \"num_turmas\": 4}, {\"ano\": 2010, \"S\\u00e9rie\": \"2\\u00ba\", \"num_matriculas\": 123, \"num_turmas\": 3}, {\"ano\": 2010, \"S\\u00e9rie\": \"3\\u00ba\", \"num_matriculas\": 108, \"num_turmas\": 3}, {\"ano\": 2011, \"S\\u00e9rie\": \"1\\u00ba\", \"num_matriculas\": 129, \"num_turmas\": 4}, {\"ano\": 2011, \"S\\u00e9rie\": \"2\\u00ba\", \"num_matriculas\": 114, \"num_turmas\": 3}, {\"ano\": 2011, \"S\\u00e9rie\": \"3\\u00ba\", \"num_matriculas\": 110, \"num_turmas\": 3}, {\"ano\": 2012, \"S\\u00e9rie\": \"1\\u00ba\", \"num_matriculas\": 136, \"num_turmas\": 4}, {\"ano\": 2012, \"S\\u00e9rie\": \"2\\u00ba\", \"num_matriculas\": 113, \"num_turmas\": 3}, {\"ano\": 2012, \"S\\u00e9rie\": \"3\\u00ba\", \"num_matriculas\": 94, \"num_turmas\": 3}, {\"ano\": 2013, \"S\\u00e9rie\": \"1\\u00ba\", \"num_matriculas\": 142, \"num_turmas\": 4}, {\"ano\": 2013, \"S\\u00e9rie\": \"2\\u00ba\", \"num_matriculas\": 118, \"num_turmas\": 3}, {\"ano\": 2013, \"S\\u00e9rie\": \"3\\u00ba\", \"num_matriculas\": 95, \"num_turmas\": 3}, {\"ano\": 2014, \"S\\u00e9rie\": \"1\\u00ba\", \"num_matriculas\": 155, \"num_turmas\": 4}, {\"ano\": 2014, \"S\\u00e9rie\": \"2\\u00ba\", \"num_matriculas\": 123, \"num_turmas\": 3}, {\"ano\": 2014, \"S\\u00e9rie\": \"3\\u00ba\", \"num_matriculas\": 104, \"num_turmas\": 3}, {\"ano\": 2015, \"S\\u00e9rie\": \"1\\u00ba\", \"num_matriculas\": 143, \"num_turmas\": 4}, {\"ano\": 2015, \"S\\u00e9rie\": \"2\\u00ba\", \"num_matriculas\": 124, \"num_turmas\": 3}, {\"ano\": 2015, \"S\\u00e9rie\": \"3\\u00ba\", \"num_matriculas\": 102, \"num_turmas\": 3}, {\"ano\": 2016, \"S\\u00e9rie\": \"1\\u00ba\", \"num_matriculas\": 119, \"num_turmas\": 4}, {\"ano\": 2016, \"S\\u00e9rie\": \"2\\u00ba\", \"num_matriculas\": 131, \"num_turmas\": 4}, {\"ano\": 2016, \"S\\u00e9rie\": \"3\\u00ba\", \"num_matriculas\": 99, \"num_turmas\": 3}, {\"ano\": 2017, \"S\\u00e9rie\": \"1\\u00ba\", \"num_matriculas\": 121, \"num_turmas\": 4}, {\"ano\": 2017, \"S\\u00e9rie\": \"2\\u00ba\", \"num_matriculas\": 103, \"num_turmas\": 3}, {\"ano\": 2017, \"S\\u00e9rie\": \"3\\u00ba\", \"num_matriculas\": 109, \"num_turmas\": 3}, {\"ano\": 2018, \"S\\u00e9rie\": \"1\\u00ba\", \"num_matriculas\": 144, \"num_turmas\": 4}, {\"ano\": 2018, \"S\\u00e9rie\": \"2\\u00ba\", \"num_matriculas\": 103, \"num_turmas\": 3}, {\"ano\": 2018, \"S\\u00e9rie\": \"3\\u00ba\", \"num_matriculas\": 91, \"num_turmas\": 3}, {\"ano\": 2019, \"S\\u00e9rie\": \"1\\u00ba\", \"num_matriculas\": 134, \"num_turmas\": 4}, {\"ano\": 2019, \"S\\u00e9rie\": \"2\\u00ba\", \"num_matriculas\": 126, \"num_turmas\": 4}, {\"ano\": 2019, \"S\\u00e9rie\": \"3\\u00ba\", \"num_matriculas\": 90, \"num_turmas\": 3}]}}, {\"mode\": \"vega-lite\"});\n",
       "</script>"
      ],
      "text/plain": [
       "alt.Chart(...)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alt.Chart(df_medio_sv).mark_line().encode(x='ano', y='num_matriculas', color='Série', tooltip=('num_matriculas', 'num_turmas'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5993ec68-87ec-4e25-818b-4becbbb48b24",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "5993ec68-87ec-4e25-818b-4becbbb48b24",
    "outputId": "83ddae48-62e8-4312-f901-2b222b2918f3"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Período</th>\n",
       "      <th>Série</th>\n",
       "      <th>Nº alunos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007–2009</td>\n",
       "      <td>1º %</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2007–2009</td>\n",
       "      <td>2º %</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2007–2009</td>\n",
       "      <td>3º %</td>\n",
       "      <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2008–2010</td>\n",
       "      <td>1º %</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2008–2010</td>\n",
       "      <td>2º %</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Período Série  Nº alunos\n",
       "0   2007–2009  1º %      100.0\n",
       "13  2007–2009  2º %       80.0\n",
       "26  2007–2009  3º %       73.0\n",
       "1   2008–2010  1º %      100.0\n",
       "14  2008–2010  2º %       89.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sv_periodo = df_medio_sv.pivot(\n",
    "    index=\"ano\", columns=\"Série\", values=\"num_matriculas\"\n",
    ").reset_index()\n",
    "df_sv_periodo[\"2º\"] = df_sv_periodo[\"2º\"].shift(-1)\n",
    "df_sv_periodo[\"2º %\"] = (df_sv_periodo[\"2º\"]/df_sv_periodo[\"1º\"] * 100).round(0)\n",
    "df_sv_periodo[\"3º\"] = df_sv_periodo[\"3º\"].shift(-2)\n",
    "df_sv_periodo[\"3º %\"] = (df_sv_periodo[\"3º\"]/df_sv_periodo[\"1º\"] * 100).round(0)\n",
    "df_sv_periodo[\"1º %\"] = 100\n",
    "df_sv_periodo[\"Período\"] = (\n",
    "    df_sv_periodo[\"ano\"].astype(str) + \"–\" + (df_sv_periodo[\"ano\"] + 2).astype(str)\n",
    ")\n",
    "df_sv_periodo_no = (\n",
    "    df_sv_periodo.drop(\"ano\", axis=1)\n",
    "    .melt(id_vars=[\"Período\"], value_vars=[\"1º\", \"2º\", \"3º\"], var_name=\"Série\", value_name='Nº alunos')\n",
    "    .sort_values(\"Período\")\n",
    ")\n",
    "df_sv_periodo_per = (\n",
    "    df_sv_periodo.drop(\"ano\", axis=1)\n",
    "    .melt(id_vars=[\"Período\"], value_vars=[\"1º %\", \"2º %\", \"3º %\"], var_name=\"Série\", value_name='Nº alunos')\n",
    "    .sort_values(\"Período\")\n",
    ")\n",
    "\n",
    "df_sv_periodo_per.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "4f9b7701-1b9d-4ae0-b134-744f44b6eea2",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 537
    },
    "id": "4f9b7701-1b9d-4ae0-b134-744f44b6eea2",
    "outputId": "f5b064d8-f71e-4740-dd64-47007d65ab94"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<div id=\"altair-viz-d1cba89b00ee498fbfb2caec50b5048f\"></div>\n",
       "<script type=\"text/javascript\">\n",
       "  (function(spec, embedOpt){\n",
       "    let outputDiv = document.currentScript.previousElementSibling;\n",
       "    if (outputDiv.id !== \"altair-viz-d1cba89b00ee498fbfb2caec50b5048f\") {\n",
       "      outputDiv = document.getElementById(\"altair-viz-d1cba89b00ee498fbfb2caec50b5048f\");\n",
       "    }\n",
       "    const paths = {\n",
       "      \"vega\": \"https://cdn.jsdelivr.net/npm//vega@5?noext\",\n",
       "      \"vega-lib\": \"https://cdn.jsdelivr.net/npm//vega-lib?noext\",\n",
       "      \"vega-lite\": \"https://cdn.jsdelivr.net/npm//vega-lite@4.8.1?noext\",\n",
       "      \"vega-embed\": \"https://cdn.jsdelivr.net/npm//vega-embed@6?noext\",\n",
       "    };\n",
       "\n",
       "    function loadScript(lib) {\n",
       "      return new Promise(function(resolve, reject) {\n",
       "        var s = document.createElement('script');\n",
       "        s.src = paths[lib];\n",
       "        s.async = true;\n",
       "        s.onload = () => resolve(paths[lib]);\n",
       "        s.onerror = () => reject(`Error loading script: ${paths[lib]}`);\n",
       "        document.getElementsByTagName(\"head\")[0].appendChild(s);\n",
       "      });\n",
       "    }\n",
       "\n",
       "    function showError(err) {\n",
       "      outputDiv.innerHTML = `<div class=\"error\" style=\"color:red;\">${err}</div>`;\n",
       "      throw err;\n",
       "    }\n",
       "\n",
       "    function displayChart(vegaEmbed) {\n",
       "      vegaEmbed(outputDiv, spec, embedOpt)\n",
       "        .catch(err => showError(`Javascript Error: ${err.message}<br>This usually means there's a typo in your chart specification. See the javascript console for the full traceback.`));\n",
       "    }\n",
       "\n",
       "    if(typeof define === \"function\" && define.amd) {\n",
       "      requirejs.config({paths});\n",
       "      require([\"vega-embed\"], displayChart, err => showError(`Error loading script: ${err.message}`));\n",
       "    } else if (typeof vegaEmbed === \"function\") {\n",
       "      displayChart(vegaEmbed);\n",
       "    } else {\n",
       "      loadScript(\"vega\")\n",
       "        .then(() => loadScript(\"vega-lite\"))\n",
       "        .then(() => loadScript(\"vega-embed\"))\n",
       "        .catch(showError)\n",
       "        .then(() => displayChart(vegaEmbed));\n",
       "    }\n",
       "  })({\"width\": 685, \"height\": 380, \"config\": {\"title\": {\"anchor\": \"start\", \"fontSize\": 18, \"font\": \"Lato\", \"fontColor\": \"#000000\"}, \"axisX\": {\"domain\": true, \"domainColor\": \"#000000\", \"domainWidth\": 1, \"grid\": false, \"labelFontSize\": 12, \"labelFont\": \"Lato\", \"labelAngle\": 0, \"tickColor\": \"#000000\", \"tickSize\": 5, \"titleFontSize\": 12, \"titlePadding\": 10, \"titleFont\": \"Lato\", \"title\": \"\"}, \"axisY\": {\"domain\": false, \"grid\": true, \"gridColor\": \"#DEDDDD\", \"gridWidth\": 1, \"labelFontSize\": 12, \"labelFont\": \"Lato\", \"labelPadding\": 8, \"ticks\": false, \"titleFontSize\": 12, \"titlePadding\": 10, \"titleFont\": \"Lato\", \"titleAngle\": 0, \"titleY\": -10, \"titleX\": 18}, \"background\": \"#FFFFFF\", \"legend\": {\"labelFontSize\": 12, \"labelFont\": \"Lato\", \"symbolSize\": 100, \"symbolType\": \"square\", \"titleFontSize\": 12, \"titlePadding\": 10, \"titleFont\": \"Lato\", \"title\": \"\", \"orient\": \"top-left\", \"offset\": 0}, \"view\": {\"stroke\": \"transparent\"}, \"range\": {\"category\": [\"#1696d2\", \"#d2d2d2\", \"#000000\", \"#fdbf11\", \"#ec008b\", \"#55b748\", \"#5c5859\", \"#db2b27\"], \"diverging\": [\"#cfe8f3\", \"#a2d4ec\", \"#73bfe2\", \"#46abdb\", \"#1696d2\", \"#12719e\"]}, \"area\": {\"fill\": \"#1696d2\"}, \"line\": {\"color\": \"#1696d2\", \"stroke\": \"#1696d2\", \"strokewidth\": 5}, \"trail\": {\"color\": \"#1696d2\", \"stroke\": \"#1696d2\", \"strokeWidth\": 0, \"size\": 1}, \"path\": {\"stroke\": \"#1696d2\", \"strokeWidth\": 0.5}, \"point\": {\"filled\": true}, \"text\": {\"font\": \"Lato\", \"color\": \"#1696d2\", \"fontSize\": 11, \"align\": \"right\", \"fontWeight\": 400, \"size\": 11}, \"bar\": {\"size\": 40, \"binSpacing\": 1, \"continuousBandSize\": 30, \"discreteBandSize\": 30, \"fill\": \"#1696d2\", \"stroke\": false}}, \"vconcat\": [{\"data\": {\"name\": \"data-c29d80e5cdc46112f6305398bbcc3017\"}, \"mark\": {\"type\": \"line\", \"interpolate\": \"monotone\", \"point\": true}, \"encoding\": {\"color\": {\"type\": \"ordinal\", \"field\": \"Per\\u00edodo\"}, \"tooltip\": [{\"type\": \"nominal\", \"field\": \"Per\\u00edodo\"}, {\"type\": \"quantitative\", \"field\": \"N\\u00ba alunos\"}], \"x\": {\"type\": \"nominal\", \"field\": \"S\\u00e9rie\"}, \"y\": {\"type\": \"quantitative\", \"field\": \"N\\u00ba alunos\"}}, \"width\": 800}, {\"data\": {\"name\": \"data-76df65cf48577843677d52f81196c403\"}, \"mark\": {\"type\": \"line\", \"interpolate\": \"monotone\", \"point\": true}, \"encoding\": {\"color\": {\"type\": \"ordinal\", \"field\": \"Per\\u00edodo\"}, \"tooltip\": [{\"type\": \"nominal\", \"field\": \"Per\\u00edodo\"}, {\"type\": \"quantitative\", \"field\": \"N\\u00ba alunos\"}], \"x\": {\"type\": \"nominal\", \"field\": \"S\\u00e9rie\"}, \"y\": {\"type\": \"quantitative\", \"field\": \"N\\u00ba alunos\"}}, \"width\": 800}], \"$schema\": \"https://vega.github.io/schema/vega-lite/v4.8.1.json\", \"datasets\": {\"data-c29d80e5cdc46112f6305398bbcc3017\": [{\"Per\\u00edodo\": \"2007\\u20132009\", \"S\\u00e9rie\": \"1\\u00ba\", \"N\\u00ba alunos\": 134.0}, {\"Per\\u00edodo\": \"2007\\u20132009\", \"S\\u00e9rie\": \"2\\u00ba\", \"N\\u00ba alunos\": 107.0}, {\"Per\\u00edodo\": \"2007\\u20132009\", \"S\\u00e9rie\": \"3\\u00ba\", \"N\\u00ba alunos\": 98.0}, {\"Per\\u00edodo\": \"2008\\u20132010\", \"S\\u00e9rie\": \"1\\u00ba\", \"N\\u00ba alunos\": 143.0}, {\"Per\\u00edodo\": \"2008\\u20132010\", \"S\\u00e9rie\": \"2\\u00ba\", \"N\\u00ba alunos\": 127.0}, {\"Per\\u00edodo\": \"2008\\u20132010\", \"S\\u00e9rie\": \"3\\u00ba\", \"N\\u00ba alunos\": 108.0}, {\"Per\\u00edodo\": \"2009\\u20132011\", \"S\\u00e9rie\": \"1\\u00ba\", \"N\\u00ba alunos\": 136.0}, {\"Per\\u00edodo\": \"2009\\u20132011\", \"S\\u00e9rie\": \"2\\u00ba\", \"N\\u00ba alunos\": 123.0}, {\"Per\\u00edodo\": \"2009\\u20132011\", \"S\\u00e9rie\": \"3\\u00ba\", \"N\\u00ba alunos\": 110.0}, {\"Per\\u00edodo\": \"2010\\u20132012\", \"S\\u00e9rie\": \"2\\u00ba\", \"N\\u00ba alunos\": 114.0}, {\"Per\\u00edodo\": \"2010\\u20132012\", \"S\\u00e9rie\": \"3\\u00ba\", \"N\\u00ba alunos\": 94.0}, {\"Per\\u00edodo\": \"2010\\u20132012\", \"S\\u00e9rie\": \"1\\u00ba\", \"N\\u00ba alunos\": 146.0}, {\"Per\\u00edodo\": \"2011\\u20132013\", \"S\\u00e9rie\": \"3\\u00ba\", \"N\\u00ba alunos\": 95.0}, {\"Per\\u00edodo\": \"2011\\u20132013\", \"S\\u00e9rie\": \"1\\u00ba\", \"N\\u00ba alunos\": 129.0}, {\"Per\\u00edodo\": \"2011\\u20132013\", \"S\\u00e9rie\": \"2\\u00ba\", \"N\\u00ba alunos\": 113.0}, {\"Per\\u00edodo\": \"2012\\u20132014\", \"S\\u00e9rie\": \"3\\u00ba\", \"N\\u00ba alunos\": 104.0}, {\"Per\\u00edodo\": \"2012\\u20132014\", \"S\\u00e9rie\": \"1\\u00ba\", \"N\\u00ba alunos\": 136.0}, {\"Per\\u00edodo\": \"2012\\u20132014\", \"S\\u00e9rie\": \"2\\u00ba\", \"N\\u00ba alunos\": 118.0}, {\"Per\\u00edodo\": \"2013\\u20132015\", \"S\\u00e9rie\": \"3\\u00ba\", \"N\\u00ba alunos\": 102.0}, {\"Per\\u00edodo\": \"2013\\u20132015\", \"S\\u00e9rie\": \"2\\u00ba\", \"N\\u00ba alunos\": 123.0}, {\"Per\\u00edodo\": \"2013\\u20132015\", \"S\\u00e9rie\": \"1\\u00ba\", \"N\\u00ba alunos\": 142.0}, {\"Per\\u00edodo\": \"2014\\u20132016\", \"S\\u00e9rie\": \"2\\u00ba\", \"N\\u00ba alunos\": 124.0}, {\"Per\\u00edodo\": \"2014\\u20132016\", \"S\\u00e9rie\": \"3\\u00ba\", \"N\\u00ba alunos\": 99.0}, {\"Per\\u00edodo\": \"2014\\u20132016\", \"S\\u00e9rie\": \"1\\u00ba\", \"N\\u00ba alunos\": 155.0}, {\"Per\\u00edodo\": \"2015\\u20132017\", \"S\\u00e9rie\": \"2\\u00ba\", \"N\\u00ba alunos\": 131.0}, {\"Per\\u00edodo\": \"2015\\u20132017\", \"S\\u00e9rie\": \"3\\u00ba\", \"N\\u00ba alunos\": 109.0}, {\"Per\\u00edodo\": \"2015\\u20132017\", \"S\\u00e9rie\": \"1\\u00ba\", \"N\\u00ba alunos\": 143.0}, {\"Per\\u00edodo\": \"2016\\u20132018\", \"S\\u00e9rie\": \"3\\u00ba\", \"N\\u00ba alunos\": 91.0}, {\"Per\\u00edodo\": \"2016\\u20132018\", \"S\\u00e9rie\": \"1\\u00ba\", \"N\\u00ba alunos\": 119.0}, {\"Per\\u00edodo\": \"2016\\u20132018\", \"S\\u00e9rie\": \"2\\u00ba\", \"N\\u00ba alunos\": 103.0}, {\"Per\\u00edodo\": \"2017\\u20132019\", \"S\\u00e9rie\": \"2\\u00ba\", \"N\\u00ba alunos\": 103.0}, {\"Per\\u00edodo\": \"2017\\u20132019\", \"S\\u00e9rie\": \"3\\u00ba\", \"N\\u00ba alunos\": 90.0}, {\"Per\\u00edodo\": \"2017\\u20132019\", \"S\\u00e9rie\": \"1\\u00ba\", \"N\\u00ba alunos\": 121.0}, {\"Per\\u00edodo\": \"2018\\u20132020\", \"S\\u00e9rie\": \"3\\u00ba\", \"N\\u00ba alunos\": null}, {\"Per\\u00edodo\": \"2018\\u20132020\", \"S\\u00e9rie\": \"1\\u00ba\", \"N\\u00ba alunos\": 144.0}, {\"Per\\u00edodo\": \"2018\\u20132020\", \"S\\u00e9rie\": \"2\\u00ba\", \"N\\u00ba alunos\": 126.0}, {\"Per\\u00edodo\": \"2019\\u20132021\", \"S\\u00e9rie\": \"1\\u00ba\", \"N\\u00ba alunos\": 134.0}, {\"Per\\u00edodo\": \"2019\\u20132021\", \"S\\u00e9rie\": \"2\\u00ba\", \"N\\u00ba alunos\": null}, {\"Per\\u00edodo\": \"2019\\u20132021\", \"S\\u00e9rie\": \"3\\u00ba\", \"N\\u00ba alunos\": null}], \"data-76df65cf48577843677d52f81196c403\": [{\"Per\\u00edodo\": \"2007\\u20132009\", \"S\\u00e9rie\": \"1\\u00ba %\", \"N\\u00ba alunos\": 100.0}, {\"Per\\u00edodo\": \"2007\\u20132009\", \"S\\u00e9rie\": \"2\\u00ba %\", \"N\\u00ba alunos\": 80.0}, {\"Per\\u00edodo\": \"2007\\u20132009\", \"S\\u00e9rie\": \"3\\u00ba %\", \"N\\u00ba alunos\": 73.0}, {\"Per\\u00edodo\": \"2008\\u20132010\", \"S\\u00e9rie\": \"1\\u00ba %\", \"N\\u00ba alunos\": 100.0}, {\"Per\\u00edodo\": \"2008\\u20132010\", \"S\\u00e9rie\": \"2\\u00ba %\", \"N\\u00ba alunos\": 89.0}, {\"Per\\u00edodo\": \"2008\\u20132010\", \"S\\u00e9rie\": \"3\\u00ba %\", \"N\\u00ba alunos\": 76.0}, {\"Per\\u00edodo\": \"2009\\u20132011\", \"S\\u00e9rie\": \"1\\u00ba %\", \"N\\u00ba alunos\": 100.0}, {\"Per\\u00edodo\": \"2009\\u20132011\", \"S\\u00e9rie\": \"2\\u00ba %\", \"N\\u00ba alunos\": 90.0}, {\"Per\\u00edodo\": \"2009\\u20132011\", \"S\\u00e9rie\": \"3\\u00ba %\", \"N\\u00ba alunos\": 81.0}, {\"Per\\u00edodo\": \"2010\\u20132012\", \"S\\u00e9rie\": \"2\\u00ba %\", \"N\\u00ba alunos\": 78.0}, {\"Per\\u00edodo\": \"2010\\u20132012\", \"S\\u00e9rie\": \"3\\u00ba %\", \"N\\u00ba alunos\": 64.0}, {\"Per\\u00edodo\": \"2010\\u20132012\", \"S\\u00e9rie\": \"1\\u00ba %\", \"N\\u00ba alunos\": 100.0}, {\"Per\\u00edodo\": \"2011\\u20132013\", \"S\\u00e9rie\": \"3\\u00ba %\", \"N\\u00ba alunos\": 74.0}, {\"Per\\u00edodo\": \"2011\\u20132013\", \"S\\u00e9rie\": \"1\\u00ba %\", \"N\\u00ba alunos\": 100.0}, {\"Per\\u00edodo\": \"2011\\u20132013\", \"S\\u00e9rie\": \"2\\u00ba %\", \"N\\u00ba alunos\": 88.0}, {\"Per\\u00edodo\": \"2012\\u20132014\", \"S\\u00e9rie\": \"3\\u00ba %\", \"N\\u00ba alunos\": 76.0}, {\"Per\\u00edodo\": \"2012\\u20132014\", \"S\\u00e9rie\": \"1\\u00ba %\", \"N\\u00ba alunos\": 100.0}, {\"Per\\u00edodo\": \"2012\\u20132014\", \"S\\u00e9rie\": \"2\\u00ba %\", \"N\\u00ba alunos\": 87.0}, {\"Per\\u00edodo\": \"2013\\u20132015\", \"S\\u00e9rie\": \"3\\u00ba %\", \"N\\u00ba alunos\": 72.0}, {\"Per\\u00edodo\": \"2013\\u20132015\", \"S\\u00e9rie\": \"2\\u00ba %\", \"N\\u00ba alunos\": 87.0}, {\"Per\\u00edodo\": \"2013\\u20132015\", \"S\\u00e9rie\": \"1\\u00ba %\", \"N\\u00ba alunos\": 100.0}, {\"Per\\u00edodo\": \"2014\\u20132016\", \"S\\u00e9rie\": \"2\\u00ba %\", \"N\\u00ba alunos\": 80.0}, {\"Per\\u00edodo\": \"2014\\u20132016\", \"S\\u00e9rie\": \"3\\u00ba %\", \"N\\u00ba alunos\": 64.0}, {\"Per\\u00edodo\": \"2014\\u20132016\", \"S\\u00e9rie\": \"1\\u00ba %\", \"N\\u00ba alunos\": 100.0}, {\"Per\\u00edodo\": \"2015\\u20132017\", \"S\\u00e9rie\": \"2\\u00ba %\", \"N\\u00ba alunos\": 92.0}, {\"Per\\u00edodo\": \"2015\\u20132017\", \"S\\u00e9rie\": \"3\\u00ba %\", \"N\\u00ba alunos\": 76.0}, {\"Per\\u00edodo\": \"2015\\u20132017\", \"S\\u00e9rie\": \"1\\u00ba %\", \"N\\u00ba alunos\": 100.0}, {\"Per\\u00edodo\": \"2016\\u20132018\", \"S\\u00e9rie\": \"3\\u00ba %\", \"N\\u00ba alunos\": 76.0}, {\"Per\\u00edodo\": \"2016\\u20132018\", \"S\\u00e9rie\": \"1\\u00ba %\", \"N\\u00ba alunos\": 100.0}, {\"Per\\u00edodo\": \"2016\\u20132018\", \"S\\u00e9rie\": \"2\\u00ba %\", \"N\\u00ba alunos\": 87.0}, {\"Per\\u00edodo\": \"2017\\u20132019\", \"S\\u00e9rie\": \"2\\u00ba %\", \"N\\u00ba alunos\": 85.0}, {\"Per\\u00edodo\": \"2017\\u20132019\", \"S\\u00e9rie\": \"3\\u00ba %\", \"N\\u00ba alunos\": 74.0}, {\"Per\\u00edodo\": \"2017\\u20132019\", \"S\\u00e9rie\": \"1\\u00ba %\", \"N\\u00ba alunos\": 100.0}, {\"Per\\u00edodo\": \"2018\\u20132020\", \"S\\u00e9rie\": \"3\\u00ba %\", \"N\\u00ba alunos\": null}, {\"Per\\u00edodo\": \"2018\\u20132020\", \"S\\u00e9rie\": \"1\\u00ba %\", \"N\\u00ba alunos\": 100.0}, {\"Per\\u00edodo\": \"2018\\u20132020\", \"S\\u00e9rie\": \"2\\u00ba %\", \"N\\u00ba alunos\": 88.0}, {\"Per\\u00edodo\": \"2019\\u20132021\", \"S\\u00e9rie\": \"1\\u00ba %\", \"N\\u00ba alunos\": 100.0}, {\"Per\\u00edodo\": \"2019\\u20132021\", \"S\\u00e9rie\": \"2\\u00ba %\", \"N\\u00ba alunos\": null}, {\"Per\\u00edodo\": \"2019\\u20132021\", \"S\\u00e9rie\": \"3\\u00ba %\", \"N\\u00ba alunos\": null}]}}, {\"mode\": \"vega-lite\"});\n",
       "</script>"
      ],
      "text/plain": [
       "alt.VConcatChart(...)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def line_chart(df):\n",
    "    return (\n",
    "        alt.Chart(df)\n",
    "        .mark_line(interpolate=\"monotone\", point=True)\n",
    "        .encode(\n",
    "            x=\"Série\",\n",
    "            y=alt.Y(\"Nº alunos\"),#, scale=alt.Scale(zero=False)),\n",
    "            color=\"Período:O\",\n",
    "            tooltip=('Período', \"Nº alunos\")\n",
    "        )\n",
    "        .properties(width=800)\n",
    "    )\n",
    "\n",
    "\n",
    "line_chart(df_sv_periodo_no) & line_chart(df_sv_periodo_per)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2219e6cc-23e1-480e-8c46-fd4185f70edd",
   "metadata": {
    "id": "2219e6cc-23e1-480e-8c46-fd4185f70edd"
   },
   "source": [
    "2. como tem variado as notas dos alunos no enem (a princípio seriam comparáveis)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "88de82f3-7c21-45b7-a31d-9f7283f76370",
   "metadata": {
    "id": "88de82f3-7c21-45b7-a31d-9f7283f76370"
   },
   "outputs": [],
   "source": [
    "df_enem = pd.read_feather(dados_dir / \"enem_anos.feather\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "7f413c0a-6531-4f05-942f-51f98e69ad06",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "7f413c0a-6531-4f05-942f-51f98e69ad06",
    "outputId": "30694050-7acc-4262-c8f3-1b660fe68703"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CO_ESCOLA</th>\n",
       "      <th>Ano</th>\n",
       "      <th>nota_ciências</th>\n",
       "      <th>nota_humanas</th>\n",
       "      <th>nota_português</th>\n",
       "      <th>nota_matemática</th>\n",
       "      <th>nota_redação</th>\n",
       "      <th>nota_final</th>\n",
       "      <th>num_alunos</th>\n",
       "      <th>nota_ciências_perc80</th>\n",
       "      <th>nota_humanas_perc80</th>\n",
       "      <th>nota_português_perc80</th>\n",
       "      <th>nota_matemática_perc80</th>\n",
       "      <th>nota_redação_perc80</th>\n",
       "      <th>nota_final_perc80</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>33135827</td>\n",
       "      <td>2019</td>\n",
       "      <td>647.5</td>\n",
       "      <td>651.0</td>\n",
       "      <td>627.0</td>\n",
       "      <td>789.0</td>\n",
       "      <td>940.0</td>\n",
       "      <td>758.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>676.0</td>\n",
       "      <td>685.5</td>\n",
       "      <td>641.5</td>\n",
       "      <td>823.0</td>\n",
       "      <td>960.0</td>\n",
       "      <td>778.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33062633</td>\n",
       "      <td>2019</td>\n",
       "      <td>636.5</td>\n",
       "      <td>672.0</td>\n",
       "      <td>626.0</td>\n",
       "      <td>789.5</td>\n",
       "      <td>900.0</td>\n",
       "      <td>751.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>662.5</td>\n",
       "      <td>714.0</td>\n",
       "      <td>641.5</td>\n",
       "      <td>853.0</td>\n",
       "      <td>940.0</td>\n",
       "      <td>773.5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>33178860</td>\n",
       "      <td>2019</td>\n",
       "      <td>610.0</td>\n",
       "      <td>662.5</td>\n",
       "      <td>626.0</td>\n",
       "      <td>779.0</td>\n",
       "      <td>910.0</td>\n",
       "      <td>745.5</td>\n",
       "      <td>60.0</td>\n",
       "      <td>652.5</td>\n",
       "      <td>683.5</td>\n",
       "      <td>648.0</td>\n",
       "      <td>848.0</td>\n",
       "      <td>940.0</td>\n",
       "      <td>779.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>33135371</td>\n",
       "      <td>2019</td>\n",
       "      <td>613.0</td>\n",
       "      <td>647.0</td>\n",
       "      <td>620.5</td>\n",
       "      <td>744.0</td>\n",
       "      <td>940.0</td>\n",
       "      <td>743.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>649.5</td>\n",
       "      <td>681.0</td>\n",
       "      <td>641.0</td>\n",
       "      <td>825.0</td>\n",
       "      <td>960.0</td>\n",
       "      <td>769.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>33176825</td>\n",
       "      <td>2019</td>\n",
       "      <td>605.5</td>\n",
       "      <td>649.0</td>\n",
       "      <td>620.5</td>\n",
       "      <td>759.0</td>\n",
       "      <td>920.0</td>\n",
       "      <td>742.0</td>\n",
       "      <td>146.0</td>\n",
       "      <td>643.0</td>\n",
       "      <td>682.0</td>\n",
       "      <td>644.5</td>\n",
       "      <td>815.5</td>\n",
       "      <td>960.0</td>\n",
       "      <td>769.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CO_ESCOLA   Ano  nota_ciências  nota_humanas  nota_português  \\\n",
       "0   33135827  2019          647.5         651.0           627.0   \n",
       "1   33062633  2019          636.5         672.0           626.0   \n",
       "2   33178860  2019          610.0         662.5           626.0   \n",
       "3   33135371  2019          613.0         647.0           620.5   \n",
       "4   33176825  2019          605.5         649.0           620.5   \n",
       "\n",
       "   nota_matemática  nota_redação  nota_final  num_alunos  \\\n",
       "0            789.0         940.0       758.0        31.0   \n",
       "1            789.5         900.0       751.0        45.0   \n",
       "2            779.0         910.0       745.5        60.0   \n",
       "3            744.0         940.0       743.0        99.0   \n",
       "4            759.0         920.0       742.0       146.0   \n",
       "\n",
       "   nota_ciências_perc80  nota_humanas_perc80  nota_português_perc80  \\\n",
       "0                 676.0                685.5                  641.5   \n",
       "1                 662.5                714.0                  641.5   \n",
       "2                 652.5                683.5                  648.0   \n",
       "3                 649.5                681.0                  641.0   \n",
       "4                 643.0                682.0                  644.5   \n",
       "\n",
       "   nota_matemática_perc80  nota_redação_perc80  nota_final_perc80  rank  \n",
       "0                   823.0                960.0              778.0     1  \n",
       "1                   853.0                940.0              773.5     2  \n",
       "2                   848.0                940.0              779.0     3  \n",
       "3                   825.0                960.0              769.0     4  \n",
       "4                   815.5                960.0              769.0     5  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_enem.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e2ba1bac-5bcf-4f7b-94a9-806aebfec11c",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 452
    },
    "id": "e2ba1bac-5bcf-4f7b-94a9-806aebfec11c",
    "outputId": "d3c4eb33-ffcd-4372-f71c-ce2ca2e7bd60"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<div id=\"altair-viz-fbe07f6816da4472989052dd3477f0e9\"></div>\n",
       "<script type=\"text/javascript\">\n",
       "  (function(spec, embedOpt){\n",
       "    let outputDiv = document.currentScript.previousElementSibling;\n",
       "    if (outputDiv.id !== \"altair-viz-fbe07f6816da4472989052dd3477f0e9\") {\n",
       "      outputDiv = document.getElementById(\"altair-viz-fbe07f6816da4472989052dd3477f0e9\");\n",
       "    }\n",
       "    const paths = {\n",
       "      \"vega\": \"https://cdn.jsdelivr.net/npm//vega@5?noext\",\n",
       "      \"vega-lib\": \"https://cdn.jsdelivr.net/npm//vega-lib?noext\",\n",
       "      \"vega-lite\": \"https://cdn.jsdelivr.net/npm//vega-lite@4.8.1?noext\",\n",
       "      \"vega-embed\": \"https://cdn.jsdelivr.net/npm//vega-embed@6?noext\",\n",
       "    };\n",
       "\n",
       "    function loadScript(lib) {\n",
       "      return new Promise(function(resolve, reject) {\n",
       "        var s = document.createElement('script');\n",
       "        s.src = paths[lib];\n",
       "        s.async = true;\n",
       "        s.onload = () => resolve(paths[lib]);\n",
       "        s.onerror = () => reject(`Error loading script: ${paths[lib]}`);\n",
       "        document.getElementsByTagName(\"head\")[0].appendChild(s);\n",
       "      });\n",
       "    }\n",
       "\n",
       "    function showError(err) {\n",
       "      outputDiv.innerHTML = `<div class=\"error\" style=\"color:red;\">${err}</div>`;\n",
       "      throw err;\n",
       "    }\n",
       "\n",
       "    function displayChart(vegaEmbed) {\n",
       "      vegaEmbed(outputDiv, spec, embedOpt)\n",
       "        .catch(err => showError(`Javascript Error: ${err.message}<br>This usually means there's a typo in your chart specification. See the javascript console for the full traceback.`));\n",
       "    }\n",
       "\n",
       "    if(typeof define === \"function\" && define.amd) {\n",
       "      requirejs.config({paths});\n",
       "      require([\"vega-embed\"], displayChart, err => showError(`Error loading script: ${err.message}`));\n",
       "    } else if (typeof vegaEmbed === \"function\") {\n",
       "      displayChart(vegaEmbed);\n",
       "    } else {\n",
       "      loadScript(\"vega\")\n",
       "        .then(() => loadScript(\"vega-lite\"))\n",
       "        .then(() => loadScript(\"vega-embed\"))\n",
       "        .catch(showError)\n",
       "        .then(() => displayChart(vegaEmbed));\n",
       "    }\n",
       "  })({\"width\": 800, \"height\": 380, \"config\": {\"title\": {\"anchor\": \"start\", \"fontSize\": 18, \"font\": \"Lato\", \"fontColor\": \"#000000\"}, \"axisX\": {\"domain\": true, \"domainColor\": \"#000000\", \"domainWidth\": 1, \"grid\": false, \"labelFontSize\": 12, \"labelFont\": \"Lato\", \"labelAngle\": 0, \"tickColor\": \"#000000\", \"tickSize\": 5, \"titleFontSize\": 12, \"titlePadding\": 10, \"titleFont\": \"Lato\", \"title\": \"\"}, \"axisY\": {\"domain\": false, \"grid\": true, \"gridColor\": \"#DEDDDD\", \"gridWidth\": 1, \"labelFontSize\": 12, \"labelFont\": \"Lato\", \"labelPadding\": 8, \"ticks\": false, \"titleFontSize\": 12, \"titlePadding\": 10, \"titleFont\": \"Lato\", \"titleAngle\": 0, \"titleY\": -10, \"titleX\": 18}, \"background\": \"#FFFFFF\", \"legend\": {\"labelFontSize\": 12, \"labelFont\": \"Lato\", \"symbolSize\": 100, \"symbolType\": \"square\", \"titleFontSize\": 12, \"titlePadding\": 10, \"titleFont\": \"Lato\", \"title\": \"\", \"orient\": \"top-left\", \"offset\": 0}, \"view\": {\"stroke\": \"transparent\"}, \"range\": {\"category\": [\"#1696d2\", \"#d2d2d2\", \"#000000\", \"#fdbf11\", \"#ec008b\", \"#55b748\", \"#5c5859\", \"#db2b27\"], \"diverging\": [\"#cfe8f3\", \"#a2d4ec\", \"#73bfe2\", \"#46abdb\", \"#1696d2\", \"#12719e\"]}, \"area\": {\"fill\": \"#1696d2\"}, \"line\": {\"color\": \"#1696d2\", \"stroke\": \"#1696d2\", \"strokewidth\": 5}, \"trail\": {\"color\": \"#1696d2\", \"stroke\": \"#1696d2\", \"strokeWidth\": 0, \"size\": 1}, \"path\": {\"stroke\": \"#1696d2\", \"strokeWidth\": 0.5}, \"point\": {\"filled\": true}, \"text\": {\"font\": \"Lato\", \"color\": \"#1696d2\", \"fontSize\": 11, \"align\": \"right\", \"fontWeight\": 400, \"size\": 11}, \"bar\": {\"size\": 40, \"binSpacing\": 1, \"continuousBandSize\": 30, \"discreteBandSize\": 30, \"fill\": \"#1696d2\", \"stroke\": false}}, \"data\": {\"name\": \"data-e816736c7b1be9217da765db34aae00c\"}, \"mark\": {\"type\": \"line\", \"point\": true}, \"encoding\": {\"color\": {\"type\": \"nominal\", \"field\": \"CO_ESCOLA\"}, \"tooltip\": [{\"type\": \"quantitative\", \"field\": \"rank\"}, {\"type\": \"quantitative\", \"field\": \"nota_final\"}, {\"type\": \"quantitative\", \"field\": \"nota_final_perc80\"}, {\"type\": \"quantitative\", \"field\": \"nota_matem\\u00e1tica\"}], \"x\": {\"type\": \"ordinal\", \"field\": \"Ano\"}, \"y\": {\"type\": \"quantitative\", \"field\": \"nota_matem\\u00e1tica\"}}, \"$schema\": \"https://vega.github.io/schema/vega-lite/v4.8.1.json\", \"datasets\": {\"data-e816736c7b1be9217da765db34aae00c\": [{\"CO_ESCOLA\": 33063648, \"Ano\": 2019, \"nota_ci\\u00eancias\": 589.0, \"nota_humanas\": 637.0, \"nota_portugu\\u00eas\": 620.5, \"nota_matem\\u00e1tica\": 732.0, \"nota_reda\\u00e7\\u00e3o\": 860.0, \"nota_final\": 707.0, \"num_alunos\": 59.0, \"nota_ci\\u00eancias_perc80\": 621.0, \"nota_humanas_perc80\": 667.0, \"nota_portugu\\u00eas_perc80\": 640.5, \"nota_matem\\u00e1tica_perc80\": 776.5, \"nota_reda\\u00e7\\u00e3o_perc80\": 908.0, \"nota_final_perc80\": 748.5, \"rank\": 36}, {\"CO_ESCOLA\": 33063648, \"Ano\": 2018, \"nota_ci\\u00eancias\": 603.5, \"nota_humanas\": 676.5, \"nota_portugu\\u00eas\": 640.0, \"nota_matem\\u00e1tica\": 737.0, \"nota_reda\\u00e7\\u00e3o\": 800.0, \"nota_final\": 721.0, \"num_alunos\": 87.0, \"nota_ci\\u00eancias_perc80\": 652.5, \"nota_humanas_perc80\": 708.5, \"nota_portugu\\u00eas_perc80\": 670.5, \"nota_matem\\u00e1tica_perc80\": 782.0, \"nota_reda\\u00e7\\u00e3o_perc80\": 916.0, \"nota_final_perc80\": 760.0, \"rank\": 39}, {\"CO_ESCOLA\": 33063648, \"Ano\": 2017, \"nota_ci\\u00eancias\": 622.5, \"nota_humanas\": 655.5, \"nota_portugu\\u00eas\": 617.0, \"nota_matem\\u00e1tica\": 761.5, \"nota_reda\\u00e7\\u00e3o\": 780.0, \"nota_final\": 700.0, \"num_alunos\": 107.0, \"nota_ci\\u00eancias_perc80\": 656.5, \"nota_humanas_perc80\": 681.5, \"nota_portugu\\u00eas_perc80\": 644.0, \"nota_matem\\u00e1tica_perc80\": 814.0, \"nota_reda\\u00e7\\u00e3o_perc80\": 900.0, \"nota_final_perc80\": 747.0, \"rank\": 29}, {\"CO_ESCOLA\": 33063648, \"Ano\": 2016, \"nota_ci\\u00eancias\": 632.5, \"nota_humanas\": 656.0, \"nota_portugu\\u00eas\": 641.5, \"nota_matem\\u00e1tica\": 704.5, \"nota_reda\\u00e7\\u00e3o\": 800.0, \"nota_final\": 703.0, \"num_alunos\": 95.0, \"nota_ci\\u00eancias_perc80\": 657.5, \"nota_humanas_perc80\": 697.5, \"nota_portugu\\u00eas_perc80\": 665.5, \"nota_matem\\u00e1tica_perc80\": 791.0, \"nota_reda\\u00e7\\u00e3o_perc80\": 884.0, \"nota_final_perc80\": 745.5, \"rank\": 25}, {\"CO_ESCOLA\": 33063648, \"Ano\": 2015, \"nota_ci\\u00eancias\": 632.0, \"nota_humanas\": 674.0, \"nota_portugu\\u00eas\": 618.5, \"nota_matem\\u00e1tica\": 716.0, \"nota_reda\\u00e7\\u00e3o\": 760.0, \"nota_final\": 701.0, \"num_alunos\": 103.0, \"nota_ci\\u00eancias_perc80\": 676.5, \"nota_humanas_perc80\": 718.0, \"nota_portugu\\u00eas_perc80\": 647.5, \"nota_matem\\u00e1tica_perc80\": 807.5, \"nota_reda\\u00e7\\u00e3o_perc80\": 880.0, \"nota_final_perc80\": 753.5, \"rank\": 19}, {\"CO_ESCOLA\": 33063648, \"Ano\": 2014, \"nota_ci\\u00eancias\": 627.0, \"nota_humanas\": 676.0, \"nota_portugu\\u00eas\": 625.5, \"nota_matem\\u00e1tica\": 710.0, \"nota_reda\\u00e7\\u00e3o\": 740.0, \"nota_final\": 695.0, \"num_alunos\": 104.0, \"nota_ci\\u00eancias_perc80\": 664.0, \"nota_humanas_perc80\": 694.0, \"nota_portugu\\u00eas_perc80\": 651.0, \"nota_matem\\u00e1tica_perc80\": 807.0, \"nota_reda\\u00e7\\u00e3o_perc80\": 840.0, \"nota_final_perc80\": 730.0, \"rank\": 20}, {\"CO_ESCOLA\": 33063648, \"Ano\": 2013, \"nota_ci\\u00eancias\": 623.0, \"nota_humanas\": 684.5, \"nota_portugu\\u00eas\": 624.5, \"nota_matem\\u00e1tica\": 731.5, \"nota_reda\\u00e7\\u00e3o\": 740.0, \"nota_final\": 690.0, \"num_alunos\": 94.0, \"nota_ci\\u00eancias_perc80\": 674.0, \"nota_humanas_perc80\": 730.0, \"nota_portugu\\u00eas_perc80\": 667.5, \"nota_matem\\u00e1tica_perc80\": 779.5, \"nota_reda\\u00e7\\u00e3o_perc80\": 880.0, \"nota_final_perc80\": 749.5, \"rank\": 22}]}}, {\"mode\": \"vega-lite\"});\n",
       "</script>"
      ],
      "text/plain": [
       "alt.Chart(...)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alt.Chart(\n",
    "    df_enem[df_enem.CO_ESCOLA.isin([CO_SAO_VICENTE])]\n",
    "    # df_enem.query(\"rank <= 30 and NU_ANO == @ANO_ULT\").CO_ESCOLA)]\n",
    ").mark_line(point=True).encode(\n",
    "    x=\"Ano:O\",\n",
    "    y=alt.Y(\"nota_matemática\"),\n",
    "    color=\"CO_ESCOLA:N\",\n",
    "    tooltip=(\"rank\", \"nota_final\", \"nota_final_perc80\", 'nota_matemática'),\n",
    ").properties(\n",
    "    width=800\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "fac03d98-4c98-41df-acee-bbdd4c829e82",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 452
    },
    "id": "fac03d98-4c98-41df-acee-bbdd4c829e82",
    "outputId": "9d0596e2-0daf-4773-d4c9-9bca5723c86d"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<div id=\"altair-viz-395eb6e301294e068746641b2a96d532\"></div>\n",
       "<script type=\"text/javascript\">\n",
       "  (function(spec, embedOpt){\n",
       "    let outputDiv = document.currentScript.previousElementSibling;\n",
       "    if (outputDiv.id !== \"altair-viz-395eb6e301294e068746641b2a96d532\") {\n",
       "      outputDiv = document.getElementById(\"altair-viz-395eb6e301294e068746641b2a96d532\");\n",
       "    }\n",
       "    const paths = {\n",
       "      \"vega\": \"https://cdn.jsdelivr.net/npm//vega@5?noext\",\n",
       "      \"vega-lib\": \"https://cdn.jsdelivr.net/npm//vega-lib?noext\",\n",
       "      \"vega-lite\": \"https://cdn.jsdelivr.net/npm//vega-lite@4.8.1?noext\",\n",
       "      \"vega-embed\": \"https://cdn.jsdelivr.net/npm//vega-embed@6?noext\",\n",
       "    };\n",
       "\n",
       "    function loadScript(lib) {\n",
       "      return new Promise(function(resolve, reject) {\n",
       "        var s = document.createElement('script');\n",
       "        s.src = paths[lib];\n",
       "        s.async = true;\n",
       "        s.onload = () => resolve(paths[lib]);\n",
       "        s.onerror = () => reject(`Error loading script: ${paths[lib]}`);\n",
       "        document.getElementsByTagName(\"head\")[0].appendChild(s);\n",
       "      });\n",
       "    }\n",
       "\n",
       "    function showError(err) {\n",
       "      outputDiv.innerHTML = `<div class=\"error\" style=\"color:red;\">${err}</div>`;\n",
       "      throw err;\n",
       "    }\n",
       "\n",
       "    function displayChart(vegaEmbed) {\n",
       "      vegaEmbed(outputDiv, spec, embedOpt)\n",
       "        .catch(err => showError(`Javascript Error: ${err.message}<br>This usually means there's a typo in your chart specification. See the javascript console for the full traceback.`));\n",
       "    }\n",
       "\n",
       "    if(typeof define === \"function\" && define.amd) {\n",
       "      requirejs.config({paths});\n",
       "      require([\"vega-embed\"], displayChart, err => showError(`Error loading script: ${err.message}`));\n",
       "    } else if (typeof vegaEmbed === \"function\") {\n",
       "      displayChart(vegaEmbed);\n",
       "    } else {\n",
       "      loadScript(\"vega\")\n",
       "        .then(() => loadScript(\"vega-lite\"))\n",
       "        .then(() => loadScript(\"vega-embed\"))\n",
       "        .catch(showError)\n",
       "        .then(() => displayChart(vegaEmbed));\n",
       "    }\n",
       "  })({\"width\": 800, \"height\": 380, \"config\": {\"title\": {\"anchor\": \"start\", \"fontSize\": 18, \"font\": \"Lato\", \"fontColor\": \"#000000\"}, \"axisX\": {\"domain\": true, \"domainColor\": \"#000000\", \"domainWidth\": 1, \"grid\": false, \"labelFontSize\": 12, \"labelFont\": \"Lato\", \"labelAngle\": 0, \"tickColor\": \"#000000\", \"tickSize\": 5, \"titleFontSize\": 12, \"titlePadding\": 10, \"titleFont\": \"Lato\", \"title\": \"\"}, \"axisY\": {\"domain\": false, \"grid\": true, \"gridColor\": \"#DEDDDD\", \"gridWidth\": 1, \"labelFontSize\": 12, \"labelFont\": \"Lato\", \"labelPadding\": 8, \"ticks\": false, \"titleFontSize\": 12, \"titlePadding\": 10, \"titleFont\": \"Lato\", \"titleAngle\": 0, \"titleY\": -10, \"titleX\": 18}, \"background\": \"#FFFFFF\", \"legend\": {\"labelFontSize\": 12, \"labelFont\": \"Lato\", \"symbolSize\": 100, \"symbolType\": \"square\", \"titleFontSize\": 12, \"titlePadding\": 10, \"titleFont\": \"Lato\", \"title\": \"\", \"orient\": \"top-left\", \"offset\": 0}, \"view\": {\"stroke\": \"transparent\"}, \"range\": {\"category\": [\"#1696d2\", \"#d2d2d2\", \"#000000\", \"#fdbf11\", \"#ec008b\", \"#55b748\", \"#5c5859\", \"#db2b27\"], \"diverging\": [\"#cfe8f3\", \"#a2d4ec\", \"#73bfe2\", \"#46abdb\", \"#1696d2\", \"#12719e\"]}, \"area\": {\"fill\": \"#1696d2\"}, \"line\": {\"color\": \"#1696d2\", \"stroke\": \"#1696d2\", \"strokewidth\": 5}, \"trail\": {\"color\": \"#1696d2\", \"stroke\": \"#1696d2\", \"strokeWidth\": 0, \"size\": 1}, \"path\": {\"stroke\": \"#1696d2\", \"strokeWidth\": 0.5}, \"point\": {\"filled\": true}, \"text\": {\"font\": \"Lato\", \"color\": \"#1696d2\", \"fontSize\": 11, \"align\": \"right\", \"fontWeight\": 400, \"size\": 11}, \"bar\": {\"size\": 40, \"binSpacing\": 1, \"continuousBandSize\": 30, \"discreteBandSize\": 30, \"fill\": \"#1696d2\", \"stroke\": false}}, \"data\": {\"name\": \"data-8bfb241b64628fe09dfdd77847a7aa4d\"}, \"mark\": {\"type\": \"line\", \"point\": true}, \"encoding\": {\"color\": {\"type\": \"nominal\", \"field\": \"Mat\\u00e9ria\"}, \"tooltip\": [{\"type\": \"nominal\", \"field\": \"Mat\\u00e9ria\"}, {\"type\": \"quantitative\", \"field\": \"Nota\"}, {\"type\": \"quantitative\", \"field\": \"Ano\"}], \"x\": {\"type\": \"ordinal\", \"field\": \"Ano\"}, \"y\": {\"type\": \"quantitative\", \"field\": \"Nota\", \"scale\": {\"zero\": false}}}, \"$schema\": \"https://vega.github.io/schema/vega-lite/v4.8.1.json\", \"datasets\": {\"data-8bfb241b64628fe09dfdd77847a7aa4d\": [{\"Ano\": 2019, \"Mat\\u00e9ria\": \"nota_ci\\u00eancias\", \"Nota\": 589.0}, {\"Ano\": 2018, \"Mat\\u00e9ria\": \"nota_ci\\u00eancias\", \"Nota\": 603.5}, {\"Ano\": 2017, \"Mat\\u00e9ria\": \"nota_ci\\u00eancias\", \"Nota\": 622.5}, {\"Ano\": 2016, \"Mat\\u00e9ria\": \"nota_ci\\u00eancias\", \"Nota\": 632.5}, {\"Ano\": 2015, \"Mat\\u00e9ria\": \"nota_ci\\u00eancias\", \"Nota\": 632.0}, {\"Ano\": 2014, \"Mat\\u00e9ria\": \"nota_ci\\u00eancias\", \"Nota\": 627.0}, {\"Ano\": 2013, \"Mat\\u00e9ria\": \"nota_ci\\u00eancias\", \"Nota\": 623.0}, {\"Ano\": 2019, \"Mat\\u00e9ria\": \"nota_humanas\", \"Nota\": 637.0}, {\"Ano\": 2018, \"Mat\\u00e9ria\": \"nota_humanas\", \"Nota\": 676.5}, {\"Ano\": 2017, \"Mat\\u00e9ria\": \"nota_humanas\", \"Nota\": 655.5}, {\"Ano\": 2016, \"Mat\\u00e9ria\": \"nota_humanas\", \"Nota\": 656.0}, {\"Ano\": 2015, \"Mat\\u00e9ria\": \"nota_humanas\", \"Nota\": 674.0}, {\"Ano\": 2014, \"Mat\\u00e9ria\": \"nota_humanas\", \"Nota\": 676.0}, {\"Ano\": 2013, \"Mat\\u00e9ria\": \"nota_humanas\", \"Nota\": 684.5}, {\"Ano\": 2019, \"Mat\\u00e9ria\": \"nota_portugu\\u00eas\", \"Nota\": 620.5}, {\"Ano\": 2018, \"Mat\\u00e9ria\": \"nota_portugu\\u00eas\", \"Nota\": 640.0}, {\"Ano\": 2017, \"Mat\\u00e9ria\": \"nota_portugu\\u00eas\", \"Nota\": 617.0}, {\"Ano\": 2016, \"Mat\\u00e9ria\": \"nota_portugu\\u00eas\", \"Nota\": 641.5}, {\"Ano\": 2015, \"Mat\\u00e9ria\": \"nota_portugu\\u00eas\", \"Nota\": 618.5}, {\"Ano\": 2014, \"Mat\\u00e9ria\": \"nota_portugu\\u00eas\", \"Nota\": 625.5}, {\"Ano\": 2013, \"Mat\\u00e9ria\": \"nota_portugu\\u00eas\", \"Nota\": 624.5}, {\"Ano\": 2019, \"Mat\\u00e9ria\": \"nota_matem\\u00e1tica\", \"Nota\": 732.0}, {\"Ano\": 2018, \"Mat\\u00e9ria\": \"nota_matem\\u00e1tica\", \"Nota\": 737.0}, {\"Ano\": 2017, \"Mat\\u00e9ria\": \"nota_matem\\u00e1tica\", \"Nota\": 761.5}, {\"Ano\": 2016, \"Mat\\u00e9ria\": \"nota_matem\\u00e1tica\", \"Nota\": 704.5}, {\"Ano\": 2015, \"Mat\\u00e9ria\": \"nota_matem\\u00e1tica\", \"Nota\": 716.0}, {\"Ano\": 2014, \"Mat\\u00e9ria\": \"nota_matem\\u00e1tica\", \"Nota\": 710.0}, {\"Ano\": 2013, \"Mat\\u00e9ria\": \"nota_matem\\u00e1tica\", \"Nota\": 731.5}, {\"Ano\": 2019, \"Mat\\u00e9ria\": \"nota_reda\\u00e7\\u00e3o\", \"Nota\": 860.0}, {\"Ano\": 2018, \"Mat\\u00e9ria\": \"nota_reda\\u00e7\\u00e3o\", \"Nota\": 800.0}, {\"Ano\": 2017, \"Mat\\u00e9ria\": \"nota_reda\\u00e7\\u00e3o\", \"Nota\": 780.0}, {\"Ano\": 2016, \"Mat\\u00e9ria\": \"nota_reda\\u00e7\\u00e3o\", \"Nota\": 800.0}, {\"Ano\": 2015, \"Mat\\u00e9ria\": \"nota_reda\\u00e7\\u00e3o\", \"Nota\": 760.0}, {\"Ano\": 2014, \"Mat\\u00e9ria\": \"nota_reda\\u00e7\\u00e3o\", \"Nota\": 740.0}, {\"Ano\": 2013, \"Mat\\u00e9ria\": \"nota_reda\\u00e7\\u00e3o\", \"Nota\": 740.0}, {\"Ano\": 2019, \"Mat\\u00e9ria\": \"nota_final\", \"Nota\": 707.0}, {\"Ano\": 2018, \"Mat\\u00e9ria\": \"nota_final\", \"Nota\": 721.0}, {\"Ano\": 2017, \"Mat\\u00e9ria\": \"nota_final\", \"Nota\": 700.0}, {\"Ano\": 2016, \"Mat\\u00e9ria\": \"nota_final\", \"Nota\": 703.0}, {\"Ano\": 2015, \"Mat\\u00e9ria\": \"nota_final\", \"Nota\": 701.0}, {\"Ano\": 2014, \"Mat\\u00e9ria\": \"nota_final\", \"Nota\": 695.0}, {\"Ano\": 2013, \"Mat\\u00e9ria\": \"nota_final\", \"Nota\": 690.0}]}}, {\"mode\": \"vega-lite\"});\n",
       "</script>"
      ],
      "text/plain": [
       "alt.Chart(...)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "materias = alt.Chart(\n",
    "    df_enem[df_enem.CO_ESCOLA == CO_SAO_VICENTE].melt(\n",
    "        id_vars=\"Ano\",\n",
    "        value_vars=[c for c in df_enem.columns if c.startswith(\"nota_\") and not c.endswith('80')],\n",
    "        var_name=\"Matéria\",\n",
    "        value_name=\"Nota\",\n",
    "    )\n",
    ").mark_line(point=True).encode(\n",
    "    x=\"Ano:O\",\n",
    "    y=alt.Y(\"Nota\", scale=alt.Scale(zero=False)),\n",
    "    color=\"Matéria\", tooltip=[ \"Matéria\", \"Nota\",\"Ano\"]\n",
    "    #tooltip=(f'{i}:Q' for i in df_enem.columns if i.startswith('nota_')),\n",
    ").properties(\n",
    "    width=800\n",
    ")\n",
    "materias"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9NQX6_8lsSOY",
   "metadata": {
    "id": "9NQX6_8lsSOY"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "Z99JpQNvqZQf",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "Z99JpQNvqZQf",
    "outputId": "16aeb0f7-fd09-4c6a-e12b-ed50804613a3"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CO_ESCOLA</th>\n",
       "      <th>Ano</th>\n",
       "      <th>nota_ciências</th>\n",
       "      <th>nota_humanas</th>\n",
       "      <th>nota_português</th>\n",
       "      <th>nota_matemática</th>\n",
       "      <th>nota_redação</th>\n",
       "      <th>nota_final</th>\n",
       "      <th>num_alunos</th>\n",
       "      <th>nota_ciências_perc80</th>\n",
       "      <th>nota_humanas_perc80</th>\n",
       "      <th>nota_português_perc80</th>\n",
       "      <th>nota_matemática_perc80</th>\n",
       "      <th>nota_redação_perc80</th>\n",
       "      <th>nota_final_perc80</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>33135827</td>\n",
       "      <td>2019</td>\n",
       "      <td>647.5</td>\n",
       "      <td>651.0</td>\n",
       "      <td>627.0</td>\n",
       "      <td>789.0</td>\n",
       "      <td>940.0</td>\n",
       "      <td>758.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>676.0</td>\n",
       "      <td>685.5</td>\n",
       "      <td>641.5</td>\n",
       "      <td>823.0</td>\n",
       "      <td>960.0</td>\n",
       "      <td>778.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33062633</td>\n",
       "      <td>2019</td>\n",
       "      <td>636.5</td>\n",
       "      <td>672.0</td>\n",
       "      <td>626.0</td>\n",
       "      <td>789.5</td>\n",
       "      <td>900.0</td>\n",
       "      <td>751.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>662.5</td>\n",
       "      <td>714.0</td>\n",
       "      <td>641.5</td>\n",
       "      <td>853.0</td>\n",
       "      <td>940.0</td>\n",
       "      <td>773.5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>33178860</td>\n",
       "      <td>2019</td>\n",
       "      <td>610.0</td>\n",
       "      <td>662.5</td>\n",
       "      <td>626.0</td>\n",
       "      <td>779.0</td>\n",
       "      <td>910.0</td>\n",
       "      <td>745.5</td>\n",
       "      <td>60.0</td>\n",
       "      <td>652.5</td>\n",
       "      <td>683.5</td>\n",
       "      <td>648.0</td>\n",
       "      <td>848.0</td>\n",
       "      <td>940.0</td>\n",
       "      <td>779.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>33135371</td>\n",
       "      <td>2019</td>\n",
       "      <td>613.0</td>\n",
       "      <td>647.0</td>\n",
       "      <td>620.5</td>\n",
       "      <td>744.0</td>\n",
       "      <td>940.0</td>\n",
       "      <td>743.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>649.5</td>\n",
       "      <td>681.0</td>\n",
       "      <td>641.0</td>\n",
       "      <td>825.0</td>\n",
       "      <td>960.0</td>\n",
       "      <td>769.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>33176825</td>\n",
       "      <td>2019</td>\n",
       "      <td>605.5</td>\n",
       "      <td>649.0</td>\n",
       "      <td>620.5</td>\n",
       "      <td>759.0</td>\n",
       "      <td>920.0</td>\n",
       "      <td>742.0</td>\n",
       "      <td>146.0</td>\n",
       "      <td>643.0</td>\n",
       "      <td>682.0</td>\n",
       "      <td>644.5</td>\n",
       "      <td>815.5</td>\n",
       "      <td>960.0</td>\n",
       "      <td>769.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CO_ESCOLA   Ano  nota_ciências  nota_humanas  nota_português  \\\n",
       "0   33135827  2019          647.5         651.0           627.0   \n",
       "1   33062633  2019          636.5         672.0           626.0   \n",
       "2   33178860  2019          610.0         662.5           626.0   \n",
       "3   33135371  2019          613.0         647.0           620.5   \n",
       "4   33176825  2019          605.5         649.0           620.5   \n",
       "\n",
       "   nota_matemática  nota_redação  nota_final  num_alunos  \\\n",
       "0            789.0         940.0       758.0        31.0   \n",
       "1            789.5         900.0       751.0        45.0   \n",
       "2            779.0         910.0       745.5        60.0   \n",
       "3            744.0         940.0       743.0        99.0   \n",
       "4            759.0         920.0       742.0       146.0   \n",
       "\n",
       "   nota_ciências_perc80  nota_humanas_perc80  nota_português_perc80  \\\n",
       "0                 676.0                685.5                  641.5   \n",
       "1                 662.5                714.0                  641.5   \n",
       "2                 652.5                683.5                  648.0   \n",
       "3                 649.5                681.0                  641.0   \n",
       "4                 643.0                682.0                  644.5   \n",
       "\n",
       "   nota_matemática_perc80  nota_redação_perc80  nota_final_perc80  rank  \n",
       "0                   823.0                960.0              778.0     1  \n",
       "1                   853.0                940.0              773.5     2  \n",
       "2                   848.0                940.0              779.0     3  \n",
       "3                   825.0                960.0              769.0     4  \n",
       "4                   815.5                960.0              769.0     5  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_enem[:5]#.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "8e8bb984-4416-48e1-a33e-fd04b9b06082",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 419
    },
    "id": "8e8bb984-4416-48e1-a33e-fd04b9b06082",
    "outputId": "b2b65c7a-ac48-4ce1-db09-eeff3bd5dc7e"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ano</th>\n",
       "      <th>CO_ESCOLA</th>\n",
       "      <th>Matéria</th>\n",
       "      <th>Nota</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019</td>\n",
       "      <td>33063648</td>\n",
       "      <td>nota_ciências</td>\n",
       "      <td>589.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018</td>\n",
       "      <td>33063648</td>\n",
       "      <td>nota_ciências</td>\n",
       "      <td>603.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>33063648</td>\n",
       "      <td>nota_ciências</td>\n",
       "      <td>622.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016</td>\n",
       "      <td>33063648</td>\n",
       "      <td>nota_ciências</td>\n",
       "      <td>632.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>33063648</td>\n",
       "      <td>nota_ciências</td>\n",
       "      <td>632.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>2017</td>\n",
       "      <td>33063648</td>\n",
       "      <td>nota_final_perc80</td>\n",
       "      <td>747.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>2016</td>\n",
       "      <td>33063648</td>\n",
       "      <td>nota_final_perc80</td>\n",
       "      <td>745.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>2015</td>\n",
       "      <td>33063648</td>\n",
       "      <td>nota_final_perc80</td>\n",
       "      <td>753.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>2014</td>\n",
       "      <td>33063648</td>\n",
       "      <td>nota_final_perc80</td>\n",
       "      <td>730.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>2013</td>\n",
       "      <td>33063648</td>\n",
       "      <td>nota_final_perc80</td>\n",
       "      <td>749.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Ano  CO_ESCOLA            Matéria   Nota\n",
       "0   2019   33063648      nota_ciências  589.0\n",
       "1   2018   33063648      nota_ciências  603.5\n",
       "2   2017   33063648      nota_ciências  622.5\n",
       "3   2016   33063648      nota_ciências  632.5\n",
       "4   2015   33063648      nota_ciências  632.0\n",
       "..   ...        ...                ...    ...\n",
       "79  2017   33063648  nota_final_perc80  747.0\n",
       "80  2016   33063648  nota_final_perc80  745.5\n",
       "81  2015   33063648  nota_final_perc80  753.5\n",
       "82  2014   33063648  nota_final_perc80  730.0\n",
       "83  2013   33063648  nota_final_perc80  749.5\n",
       "\n",
       "[84 rows x 4 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_enem[df_enem.CO_ESCOLA == CO_SAO_VICENTE].melt(\n",
    "        id_vars=[\"Ano\", 'CO_ESCOLA'],\n",
    "        value_vars=[c for c in df_enem.columns if c.startswith(\"nota_\")],\n",
    "        var_name=\"Matéria\",\n",
    "        value_name=\"Nota\",\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "066be492-eb2a-4614-ae34-f5d0568e092a",
   "metadata": {
    "id": "066be492-eb2a-4614-ae34-f5d0568e092a"
   },
   "source": [
    "3. em que percentil tem ficado as notas?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "sgi00CcKtQ2p",
   "metadata": {
    "id": "sgi00CcKtQ2p"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "aMifoqPZvHmx",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "aMifoqPZvHmx",
    "outputId": "11cd5b34-f564-4238-c877-42a0119b8734"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>ano</th>\n",
       "      <th>id_escola</th>\n",
       "      <th>id_etapa</th>\n",
       "      <th>id_municipio</th>\n",
       "      <th>id_uf</th>\n",
       "      <th>num_matriculas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6791</td>\n",
       "      <td>2007</td>\n",
       "      <td>33056480</td>\n",
       "      <td>21.0</td>\n",
       "      <td>3303302</td>\n",
       "      <td>33</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6798</td>\n",
       "      <td>2007</td>\n",
       "      <td>33056480</td>\n",
       "      <td>21.0</td>\n",
       "      <td>3303302</td>\n",
       "      <td>33</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6802</td>\n",
       "      <td>2007</td>\n",
       "      <td>33018278</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3306008</td>\n",
       "      <td>33</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6808</td>\n",
       "      <td>2007</td>\n",
       "      <td>33056480</td>\n",
       "      <td>21.0</td>\n",
       "      <td>3303302</td>\n",
       "      <td>33</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6814</td>\n",
       "      <td>2007</td>\n",
       "      <td>33056480</td>\n",
       "      <td>21.0</td>\n",
       "      <td>3303302</td>\n",
       "      <td>33</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index   ano  id_escola  id_etapa  id_municipio id_uf  num_matriculas\n",
       "0   6791  2007   33056480      21.0       3303302    33              32\n",
       "1   6798  2007   33056480      21.0       3303302    33              34\n",
       "2   6802  2007   33018278       2.0       3306008    33              23\n",
       "3   6808  2007   33056480      21.0       3303302    33              32\n",
       "4   6814  2007   33056480      21.0       3303302    33              31"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_turmas_rio.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "PcNFNo1-uCzA",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "PcNFNo1-uCzA",
    "outputId": "61271716-d8be-4902-c819-5f1e7feebeba"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_escola</th>\n",
       "      <th>ano</th>\n",
       "      <th>Série</th>\n",
       "      <th>num_matriculas</th>\n",
       "      <th>num_turmas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>33000026</td>\n",
       "      <td>2007</td>\n",
       "      <td>1º</td>\n",
       "      <td>90.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33000026</td>\n",
       "      <td>2007</td>\n",
       "      <td>2º</td>\n",
       "      <td>51.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>33000026</td>\n",
       "      <td>2007</td>\n",
       "      <td>3º</td>\n",
       "      <td>60.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>33000026</td>\n",
       "      <td>2008</td>\n",
       "      <td>1º</td>\n",
       "      <td>73.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>33000026</td>\n",
       "      <td>2008</td>\n",
       "      <td>2º</td>\n",
       "      <td>48.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_escola   ano Série  num_matriculas  num_turmas\n",
       "0   33000026  2007    1º            90.0           3\n",
       "1   33000026  2007    2º            51.0           2\n",
       "2   33000026  2007    3º            60.0           3\n",
       "3   33000026  2008    1º            73.0           3\n",
       "4   33000026  2008    2º            48.0           2"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_medio_alunos = (df_turmas_rio.loc[df_turmas_rio.id_etapa.isin(em_etapas), \n",
    "                                     [\"ano\", \"id_escola\", \"id_etapa\", \"num_matriculas\"]]\n",
    "                   .groupby([ \"id_escola\", \"ano\", \"id_etapa\"])\n",
    "                   .aggregate(num_matriculas=(\"num_matriculas\", sum), num_turmas=(\"num_matriculas\", \"count\"))\n",
    "                   .rename({25:'1º', 26: '2º', 27: '3º'})\n",
    "                   .reset_index()\n",
    "                   .rename(columns={'id_etapa': 'Série'})\n",
    "                   )#.unstack()['num_matriculas'].rename(columns={25:'1º ano', 26: '2º ano', 27: '3º ano'}).reset_index()\n",
    "df_medio_alunos.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "_Xmr9JvntHLp",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "_Xmr9JvntHLp",
    "outputId": "e0f33951-0d1e-4c2d-9448-0f8440a1844a"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Período</th>\n",
       "      <th>Série</th>\n",
       "      <th>Nº alunos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007–2009</td>\n",
       "      <td>1º %</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2007–2009</td>\n",
       "      <td>2º %</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2007–2009</td>\n",
       "      <td>3º %</td>\n",
       "      <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2008–2010</td>\n",
       "      <td>1º %</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2008–2010</td>\n",
       "      <td>2º %</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Período Série  Nº alunos\n",
       "0   2007–2009  1º %      100.0\n",
       "13  2007–2009  2º %       80.0\n",
       "26  2007–2009  3º %       73.0\n",
       "1   2008–2010  1º %      100.0\n",
       "14  2008–2010  2º %       89.0"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_medio = df_turmas_rio[df_turmas_rio.id_etapa.isin(em_etapas)]\n",
    "df_sv_periodo = df_medio_sv.pivot(\n",
    "    index=\"ano\", columns=\"Série\", values=\"num_matriculas\"\n",
    ").reset_index()\n",
    "df_sv_periodo[\"2º\"] = df_sv_periodo[\"2º\"].shift(-1)\n",
    "df_sv_periodo[\"2º %\"] = (df_sv_periodo[\"2º\"]/df_sv_periodo[\"1º\"] * 100).round(0)\n",
    "df_sv_periodo[\"3º\"] = df_sv_periodo[\"3º\"].shift(-2)\n",
    "df_sv_periodo[\"3º %\"] = (df_sv_periodo[\"3º\"]/df_sv_periodo[\"1º\"] * 100).round(0)\n",
    "df_sv_periodo[\"1º %\"] = 100\n",
    "df_sv_periodo[\"Período\"] = (\n",
    "    df_sv_periodo[\"ano\"].astype(str) + \"–\" + (df_sv_periodo[\"ano\"] + 2).astype(str)\n",
    ")\n",
    "df_sv_periodo_no = (\n",
    "    df_sv_periodo.drop(\"ano\", axis=1)\n",
    "    .melt(id_vars=[\"Período\"], value_vars=[\"1º\", \"2º\", \"3º\"], var_name=\"Série\", value_name='Nº alunos')\n",
    "    .sort_values(\"Período\")\n",
    ")\n",
    "df_sv_periodo_per = (\n",
    "    df_sv_periodo.drop(\"ano\", axis=1)\n",
    "    .melt(id_vars=[\"Período\"], value_vars=[\"1º %\", \"2º %\", \"3º %\"], var_name=\"Série\", value_name='Nº alunos')\n",
    "    .sort_values(\"Período\")\n",
    ")\n",
    "\n",
    "df_sv_periodo_per.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "FKyBgNsF2Ipp",
   "metadata": {
    "id": "FKyBgNsF2Ipp"
   },
   "outputs": [],
   "source": [
    "num_escolas = 60"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "N2F6lCeZxiqE",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "N2F6lCeZxiqE",
    "outputId": "8ada7270-8e56-44b0-946c-f55034bc2b72"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ano</th>\n",
       "      <th>id_escola</th>\n",
       "      <th>Matéria</th>\n",
       "      <th>Nota</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019</td>\n",
       "      <td>33135827</td>\n",
       "      <td>nota_ciências</td>\n",
       "      <td>647.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018</td>\n",
       "      <td>33135827</td>\n",
       "      <td>nota_ciências</td>\n",
       "      <td>650.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>33135827</td>\n",
       "      <td>nota_ciências</td>\n",
       "      <td>668.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016</td>\n",
       "      <td>33135827</td>\n",
       "      <td>nota_ciências</td>\n",
       "      <td>685.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>33135827</td>\n",
       "      <td>nota_ciências</td>\n",
       "      <td>682.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Ano  id_escola        Matéria   Nota\n",
       "0  2019   33135827  nota_ciências  647.5\n",
       "1  2018   33135827  nota_ciências  650.5\n",
       "2  2017   33135827  nota_ciências  668.0\n",
       "3  2016   33135827  nota_ciências  685.5\n",
       "4  2015   33135827  nota_ciências  682.0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_enem2 = df_enem[:num_escolas].rename(columns={'CO_ESCOLA': 'id_escola'})\n",
    "df_notas = df_enem.loc[df_enem.CO_ESCOLA.isin(df_enem2.id_escola), :].rename(columns={'CO_ESCOLA': 'id_escola'})\n",
    "df_notas_melt= (df_notas.set_index(['id_escola', 'Ano' ]) # primeiro cria chave com null in valores inexistentes\n",
    "                .reindex(pd.MultiIndex.from_product([df_notas.id_escola.unique(), df_notas.Ano.unique()], names=['id_escola', 'Ano']))\n",
    "                .reset_index()\n",
    "                .melt(\n",
    "                  id_vars=[\"Ano\", 'id_escola'],\n",
    "                  value_vars=[c for c in df_enem.columns if c.startswith(\"nota_\") and not c.endswith('80')],\n",
    "                  var_name=\"Matéria\",\n",
    "                  value_name=\"Nota\",\n",
    "              ))\n",
    "df_notas_melt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "TeEnFwcVb0BN",
   "metadata": {
    "id": "TeEnFwcVb0BN"
   },
   "outputs": [],
   "source": [
    "num_anos = 5\n",
    "df_medio_alunos2 = df_medio_alunos[df_medio_alunos.id_escola.isin(df_enem2.id_escola) & (df_medio_alunos.ano > (df_medio_alunos.ano.max() - num_anos))].copy()\n",
    "df_medio_alunos2[\"Período\"] = (\n",
    "    df_medio_alunos2[\"ano\"].astype(str) + \"–\" + (df_medio_alunos2[\"ano\"] + 2).astype(str)\n",
    ")\n",
    "alunos_index_names = ['id_escola', 'Período', 'Série']\n",
    "new_alunos2_index = pd.MultiIndex.from_product([df_medio_alunos2.id_escola.unique(), \n",
    "                                                df_medio_alunos2.Período.unique(), \n",
    "                                                ['1º', '2º', '3º']], \n",
    "                                               names=alunos_index_names)\n",
    "\n",
    "df_medio_alunos2 = df_medio_alunos2.set_index(alunos_index_names).reindex(new_alunos2_index).reset_index()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "iQpP5ge52wPY",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "iQpP5ge52wPY",
    "outputId": "1d3d245b-488f-491c-958d-0e82e71d2b5f"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((352, 16), (2520, 4), (60, 16))"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_notas.shape, df_notas_melt.shape, df_enem2.shape, "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "bmpSCE0_CpbJ",
   "metadata": {
    "id": "bmpSCE0_CpbJ"
   },
   "outputs": [],
   "source": [
    "def enem_ajusta_cols(df):\n",
    "    return df.rename(\n",
    "        columns={\n",
    "            \"NU_ANO\": \"Ano\",\n",
    "            \"NU_NOTA_CN\": \"nota_ciências\",\n",
    "            \"NU_NOTA_CH\": \"nota_humanas\",\n",
    "            \"NU_NOTA_LC\": \"nota_português\",\n",
    "            \"NU_NOTA_MT\": \"nota_matemática\",\n",
    "            \"NU_NOTA_REDACAO\": \"nota_redação\",\n",
    "            'CO_ESCOLA': 'id_escola'\n",
    "        }\n",
    "    )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20013191-8b2e-4e40-8346-bf2964446445",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "iDzDRI7L03Dq",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "iDzDRI7L03Dq",
    "outputId": "06e0609d-005b-48fc-f697-bf91df15e8e7"
   },
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: 'dados/enem_rio_bins.feather'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m/tmp/ipykernel_2069/3712034122.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdf_enem_bins\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0menem_ajusta_cols\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_feather\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdados_dir\u001b[0m \u001b[0;34m/\u001b[0m \u001b[0;34m'enem_rio_bins.feather'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mdf_enem_bins\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/envs/edu/lib/python3.8/site-packages/pandas/io/feather_format.py\u001b[0m in \u001b[0;36mread_feather\u001b[0;34m(path, columns, use_threads, storage_options)\u001b[0m\n\u001b[1;32m    124\u001b[0m     \u001b[0;32mfrom\u001b[0m \u001b[0mpyarrow\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mfeather\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    125\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 126\u001b[0;31m     with get_handle(\n\u001b[0m\u001b[1;32m    127\u001b[0m         \u001b[0mpath\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"rb\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstorage_options\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mstorage_options\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mis_text\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    128\u001b[0m     ) as handles:\n",
      "\u001b[0;32m~/miniconda3/envs/edu/lib/python3.8/site-packages/pandas/io/common.py\u001b[0m in \u001b[0;36mget_handle\u001b[0;34m(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)\u001b[0m\n\u001b[1;32m    709\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    710\u001b[0m             \u001b[0;31m# Binary mode\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 711\u001b[0;31m             \u001b[0mhandle\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mopen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mhandle\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mioargs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmode\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    712\u001b[0m         \u001b[0mhandles\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mhandle\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    713\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: 'dados/enem_rio_bins.feather'"
     ]
    }
   ],
   "source": [
    "df_enem_bins = enem_ajusta_cols(pd.read_feather(dados_dir / 'enem_rio_bins.feather'))\n",
    "(df_enem_bins).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "s-5IQjzI2uzC",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "s-5IQjzI2uzC",
    "outputId": "7284e763-5f54-493c-fced-5f6cab599437"
   },
   "outputs": [],
   "source": [
    "df_enem_bins2 = df_enem_bins.loc[df_enem_bins.id_escola.isin(df_enem2.id_escola), :]\n",
    "df_enem_bins2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "BxI-rbsN8HvD",
   "metadata": {
    "id": "BxI-rbsN8HvD"
   },
   "outputs": [],
   "source": [
    "brush = alt.selection_single(fields=['id_escola'], \n",
    "                             nearest=True, \n",
    "                             init={'id_escola': CO_SAO_VICENTE})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "h6uQSFRtJvIg",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 611
    },
    "id": "h6uQSFRtJvIg",
    "outputId": "cbda13d5-95ef-4620-c2f0-204683630bbc"
   },
   "outputs": [],
   "source": [
    "\n",
    "base = alt.Chart(df_enem2, title=alt.TitleParams(\"Medianas da nota final (clique para selecionar uma escola)\", subtitle=[\"Redação tem peso 2\", \"Área cinza mostra o 80º percentil\"]))\n",
    "\n",
    "median = (base.mark_bar(size=30, color='cornflowerblue')\n",
    "          .encode(x=alt.X(\"id_escola:N\", sort=alt.SortField(\"rank\"), axis=alt.Axis(labelFontSize=10)), \n",
    "                  y=alt.Y(\"nota_final:Q\", axis=alt.Axis(title=None)), \n",
    "                  tooltip=['nota_final', 'nota_final_perc80', 'rank', 'id_escola'],\n",
    "                  opacity=alt.condition(brush, alt.value(1), alt.value(.6)))\n",
    " \n",
    ").add_selection(brush)\n",
    "\n",
    "perc80 = (base.mark_bar(size=30,  color='lightgray', opacity=.8)\n",
    "          .encode(x=alt.X(\"id_escola:N\", sort=alt.SortField(\"rank\")), \n",
    "                  y=\"nota_final_perc80:Q\")\n",
    " \n",
    ")\n",
    "#alt.data_transformers.enable(max_rows=None)\n",
    "\n",
    "materias = (alt.Chart(df_notas_melt)\n",
    "           .mark_line(point=True)\n",
    "           .encode(\n",
    "                x=alt.X(\"Ano:O\", scale=alt.Scale(padding=0.05)),\n",
    "                y=alt.Y(\"Nota\", scale=alt.Scale(domain=[500, 1000]), axis=alt.Axis(title=None)),\n",
    "                color=alt.Color(\"Matéria\",legend=alt.Legend(orient='top'), scale=alt.Scale(scheme=\"tableau10\"), \n",
    "                                sort=['nota_final', 'nota_redação', 'nota_matemática', 'nota_português', 'nota_ciências','nota_humanas']),\n",
    "                tooltip=[\"Matéria\", \"Nota\",\"Ano\"]\n",
    "                )\n",
    "            #.transform_filter(alt.FieldEqualPredicate(field=\"id_escola\", equal=CO_SAO_BENTO))\n",
    "            .transform_filter(brush)\n",
    "            .properties(\n",
    "                width=500, title=\"Notas a cada ano\"\n",
    "            )\n",
    "           )\n",
    "\n",
    "nalunos = (alt.Chart(df_medio_alunos2)\n",
    "           .mark_line(point=True)\n",
    "           .encode(\n",
    "              x=alt.X(\"Série:O\", scale=alt.Scale(domain=['1º', '2º', '3º'], padding=0.05)),\n",
    "              y=alt.Y(\"num_matriculas:Q\", axis=alt.Axis(title=None), scale=alt.Scale(domain=[0,230])),\n",
    "              color=alt.Color(\"Período:O\", scale=alt.Scale(scheme='blues'), legend=alt.Legend(orient='top')), \n",
    "              tooltip=('Período', \"Série\", \"num_matriculas\")\n",
    "            ).transform_filter(brush)\n",
    "         ).properties(width=400, title=\"Nº matrículas por série\")\n",
    "\n",
    "notas_dist_width=400\n",
    "notas_dist = (\n",
    "    alt.Chart(df_enem_bins2)\n",
    "       .mark_bar(stroke='white', width=notas_dist_width//21)\n",
    "       .encode(x=\"bin_label:O\", \n",
    "               y='nota_final:Q',\n",
    "               tooltip=['nota_final', 'bins'])\n",
    "      .transform_filter(brush)\n",
    "      .properties(width=notas_dist_width, title=\"Histograma das notas\")\n",
    ")         \n",
    "\n",
    "alt.vconcat( \n",
    "    (perc80 + median).properties(width=alt.Step(45)).interactive(),\n",
    "    (materias.interactive() | nalunos.interactive() | notas_dist.interactive()).resolve_scale(color='independent')\n",
    ").configure_point(size=40)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4m0ySdfj38FU",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "4m0ySdfj38FU",
    "outputId": "167e0b64-032a-410a-95a1-44f796e0efba"
   },
   "outputs": [],
   "source": [
    "df_medio_alunos2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee2d7f00-ef15-4fee-bfed-7a150d7e9039",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 419
    },
    "id": "Go5kP6IF_Ngu",
    "outputId": "211c1272-6a55-4866-ea76-31416a5709ae"
   },
   "source": [
    "# Escolas nomes "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "6f626c8c-b25c-4c02-b23d-8263dbbe1d82",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_escolas = pd.read_csv('dados/Análise - Tabela da lista das escolas - Detalhado.csv',sep=';', encoding='utf8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "a1361026-c515-4da3-9633-ed06f0e4cc14",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Restrição de Atendimento</th>\n",
       "      <th>Escola</th>\n",
       "      <th>Código INEP</th>\n",
       "      <th>UF</th>\n",
       "      <th>Município</th>\n",
       "      <th>Localização</th>\n",
       "      <th>Localidade Diferenciada</th>\n",
       "      <th>Categoria Administrativa</th>\n",
       "      <th>Endereço</th>\n",
       "      <th>Telefone</th>\n",
       "      <th>Dependência Administrativa</th>\n",
       "      <th>Categoria Escola Privada</th>\n",
       "      <th>Conveniada Poder Público</th>\n",
       "      <th>Regulamentação pelo Conselho de Educação</th>\n",
       "      <th>Porte da Escola</th>\n",
       "      <th>Etapas e Modalidade de Ensino Oferecidas</th>\n",
       "      <th>Outras Ofertas Educacionais</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ESCOLA ATENDE EXCLUSIVAMENTE ALUNOS COM DEFICI...</td>\n",
       "      <td>EEEE ABNAEL MACHADO DE LIMA - CENE</td>\n",
       "      <td>11000023</td>\n",
       "      <td>RO</td>\n",
       "      <td>Porto Velho</td>\n",
       "      <td>Urbana</td>\n",
       "      <td>A escola não está em área de localização difer...</td>\n",
       "      <td>Pública</td>\n",
       "      <td>AVENIDA AMAZONAS, 6492 ZONA LESTE. TIRADENTES....</td>\n",
       "      <td>(69) 992083054</td>\n",
       "      <td>Estadual</td>\n",
       "      <td>Não Informado</td>\n",
       "      <td>Não</td>\n",
       "      <td>Não</td>\n",
       "      <td>Entre 51 e 200 matrículas de escolarização</td>\n",
       "      <td>Ensino Fundamental</td>\n",
       "      <td>Atendimento Educacional Especializado</td>\n",
       "      <td>-8.758459</td>\n",
       "      <td>-63.854011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ESCOLA EM FUNCIONAMENTO E SEM RESTRIÇÃO DE ATE...</td>\n",
       "      <td>EMEIEF PEQUENOS TALENTOS</td>\n",
       "      <td>11000040</td>\n",
       "      <td>RO</td>\n",
       "      <td>Porto Velho</td>\n",
       "      <td>Urbana</td>\n",
       "      <td>A escola não está em área de localização difer...</td>\n",
       "      <td>Pública</td>\n",
       "      <td>RUA CAETANO, 3256 PREDIO. CALADINHO. 76808-108...</td>\n",
       "      <td>(69) 32274273</td>\n",
       "      <td>Municipal</td>\n",
       "      <td>Não Informado</td>\n",
       "      <td>Não</td>\n",
       "      <td>Sim</td>\n",
       "      <td>Entre 201 e 500 matrículas de escolarização</td>\n",
       "      <td>Educação Infantil</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-8.793730</td>\n",
       "      <td>-63.883919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ESCOLA EM FUNCIONAMENTO E SEM RESTRIÇÃO DE ATE...</td>\n",
       "      <td>CENTRO DE ENSINO CLASSE A</td>\n",
       "      <td>11000058</td>\n",
       "      <td>RO</td>\n",
       "      <td>Porto Velho</td>\n",
       "      <td>Urbana</td>\n",
       "      <td>A escola não está em área de localização difer...</td>\n",
       "      <td>Privada</td>\n",
       "      <td>AVENIDA CARLOS GOMES, 1135 CENTRO. 76801-123 P...</td>\n",
       "      <td>(69) 32244473</td>\n",
       "      <td>Privada</td>\n",
       "      <td>Particular</td>\n",
       "      <td>Não</td>\n",
       "      <td>Sim</td>\n",
       "      <td>Mais de 1000 matrículas de escolarização</td>\n",
       "      <td>Educação Infantil, Ensino Fundamental, Ensino ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-8.760734</td>\n",
       "      <td>-63.901986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ESCOLA EM FUNCIONAMENTO E SEM RESTRIÇÃO DE ATE...</td>\n",
       "      <td>CENTRO EDUCACIONAL PRESBITERIANO 15 DE NOVEMBRO</td>\n",
       "      <td>11000082</td>\n",
       "      <td>RO</td>\n",
       "      <td>Porto Velho</td>\n",
       "      <td>Urbana</td>\n",
       "      <td>A escola não está em área de localização difer...</td>\n",
       "      <td>Privada</td>\n",
       "      <td>RUA ALMIRANTE BARROSO, 1483 SANTA BARBARA. 768...</td>\n",
       "      <td>(69) 32245636</td>\n",
       "      <td>Privada</td>\n",
       "      <td>Particular</td>\n",
       "      <td>Não</td>\n",
       "      <td>Sim</td>\n",
       "      <td>Entre 51 e 200 matrículas de escolarização</td>\n",
       "      <td>Educação Infantil, Ensino Fundamental</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-8.765205</td>\n",
       "      <td>-63.896177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ESCOLA EM FUNCIONAMENTO E SEM RESTRIÇÃO DE ATE...</td>\n",
       "      <td>CENTRO EDUC CORA CORALINA</td>\n",
       "      <td>11000104</td>\n",
       "      <td>RO</td>\n",
       "      <td>Porto Velho</td>\n",
       "      <td>Urbana</td>\n",
       "      <td>A escola não está em área de localização difer...</td>\n",
       "      <td>Privada</td>\n",
       "      <td>RUA MEXICO, 1056 NOVA PORTO VELHO. 76820-190 P...</td>\n",
       "      <td>(69) 32252616</td>\n",
       "      <td>Privada</td>\n",
       "      <td>Particular</td>\n",
       "      <td>Não</td>\n",
       "      <td>Sim</td>\n",
       "      <td>Entre 501 e 1000 matrículas de escolarização</td>\n",
       "      <td>Educação Infantil, Ensino Fundamental</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-8.768633</td>\n",
       "      <td>-63.875471</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Restrição de Atendimento  \\\n",
       "0  ESCOLA ATENDE EXCLUSIVAMENTE ALUNOS COM DEFICI...   \n",
       "1  ESCOLA EM FUNCIONAMENTO E SEM RESTRIÇÃO DE ATE...   \n",
       "2  ESCOLA EM FUNCIONAMENTO E SEM RESTRIÇÃO DE ATE...   \n",
       "3  ESCOLA EM FUNCIONAMENTO E SEM RESTRIÇÃO DE ATE...   \n",
       "4  ESCOLA EM FUNCIONAMENTO E SEM RESTRIÇÃO DE ATE...   \n",
       "\n",
       "                                            Escola  Código INEP  UF  \\\n",
       "0               EEEE ABNAEL MACHADO DE LIMA - CENE     11000023  RO   \n",
       "1                         EMEIEF PEQUENOS TALENTOS     11000040  RO   \n",
       "2                        CENTRO DE ENSINO CLASSE A     11000058  RO   \n",
       "3  CENTRO EDUCACIONAL PRESBITERIANO 15 DE NOVEMBRO     11000082  RO   \n",
       "4                        CENTRO EDUC CORA CORALINA     11000104  RO   \n",
       "\n",
       "     Município Localização                            Localidade Diferenciada  \\\n",
       "0  Porto Velho      Urbana  A escola não está em área de localização difer...   \n",
       "1  Porto Velho      Urbana  A escola não está em área de localização difer...   \n",
       "2  Porto Velho      Urbana  A escola não está em área de localização difer...   \n",
       "3  Porto Velho      Urbana  A escola não está em área de localização difer...   \n",
       "4  Porto Velho      Urbana  A escola não está em área de localização difer...   \n",
       "\n",
       "  Categoria Administrativa                                           Endereço  \\\n",
       "0                  Pública  AVENIDA AMAZONAS, 6492 ZONA LESTE. TIRADENTES....   \n",
       "1                  Pública  RUA CAETANO, 3256 PREDIO. CALADINHO. 76808-108...   \n",
       "2                  Privada  AVENIDA CARLOS GOMES, 1135 CENTRO. 76801-123 P...   \n",
       "3                  Privada  RUA ALMIRANTE BARROSO, 1483 SANTA BARBARA. 768...   \n",
       "4                  Privada  RUA MEXICO, 1056 NOVA PORTO VELHO. 76820-190 P...   \n",
       "\n",
       "         Telefone Dependência Administrativa Categoria Escola Privada  \\\n",
       "0  (69) 992083054                   Estadual            Não Informado   \n",
       "1   (69) 32274273                  Municipal            Não Informado   \n",
       "2   (69) 32244473                    Privada               Particular   \n",
       "3   (69) 32245636                    Privada               Particular   \n",
       "4   (69) 32252616                    Privada               Particular   \n",
       "\n",
       "  Conveniada Poder Público Regulamentação pelo Conselho de Educação  \\\n",
       "0                      Não                                      Não   \n",
       "1                      Não                                      Sim   \n",
       "2                      Não                                      Sim   \n",
       "3                      Não                                      Sim   \n",
       "4                      Não                                      Sim   \n",
       "\n",
       "                                Porte da Escola  \\\n",
       "0    Entre 51 e 200 matrículas de escolarização   \n",
       "1   Entre 201 e 500 matrículas de escolarização   \n",
       "2      Mais de 1000 matrículas de escolarização   \n",
       "3    Entre 51 e 200 matrículas de escolarização   \n",
       "4  Entre 501 e 1000 matrículas de escolarização   \n",
       "\n",
       "            Etapas e Modalidade de Ensino Oferecidas  \\\n",
       "0                                 Ensino Fundamental   \n",
       "1                                  Educação Infantil   \n",
       "2  Educação Infantil, Ensino Fundamental, Ensino ...   \n",
       "3              Educação Infantil, Ensino Fundamental   \n",
       "4              Educação Infantil, Ensino Fundamental   \n",
       "\n",
       "             Outras Ofertas Educacionais  Latitude  Longitude  \n",
       "0  Atendimento Educacional Especializado -8.758459 -63.854011  \n",
       "1                                    NaN -8.793730 -63.883919  \n",
       "2                                    NaN -8.760734 -63.901986  \n",
       "3                                    NaN -8.765205 -63.896177  \n",
       "4                                    NaN -8.768633 -63.875471  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_escolas.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "cc8e8a69-e806-4075-867d-cb53a1b476c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_escolas_rio = df_escolas.loc[df_escolas.UF==\"RJ\", ['Escola', 'Código INEP', 'Categoria Administrativa', 'Endereço', \n",
    "                                                      'Dependência Administrativa', 'Categoria Escola Privada',\n",
    "                                                      'Conveniada Poder Público', 'Regulamentação pelo Conselho de Educação', \n",
    "                                                      'Porte da Escola', 'Etapas e Modalidade de Ensino Oferecidas'] \n",
    "                               ].rename(columns={'Código INEP': 'CO_ENTIDADE'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "31e4d85e-cb61-4da7-b283-e2ba0800b3c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import notas\n",
    "notas.ajusta_nomes(df_escolas_rio)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "bdc47573-a6c7-4bbd-90a1-6f711d2861dc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "138335                     RUA RIACHUELO, 470 PARQUE TURF CLUB. 28013-450 Campos dos Goytacazes - RJ.\n",
       "142890    AVENIDA DAS AMERICAS, 7707 LOJAS 121 A 124. BARRA DA TIJUCA. 22793-081 Rio de Janeiro - RJ.\n",
       "Name: Endereço, dtype: object"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option(\"display.max_colwidth\", None)\n",
    "df_escolas_rio[df_escolas_rio.CO_ENTIDADE.isin([33106754, 33176825])]['Endereço']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "acf5b1d7-0213-4e36-9029-a69cb404d90b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Escola</th>\n",
       "      <th>Bairro</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>135666</th>\n",
       "      <td>COL SAO VICENTE DE PAULO</td>\n",
       "      <td>COSME VELHO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135667</th>\n",
       "      <td>SOCIEDADE BRASILEIRA DE ENSINO - COL SION</td>\n",
       "      <td>COSME VELHO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135691</th>\n",
       "      <td>0204009 ESCOLA MUNICIPAL GUARARAPES CANDIDO</td>\n",
       "      <td>COSME VELHO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137696</th>\n",
       "      <td>0204601 CRECHE MUNICIPAL TIA AMALIA</td>\n",
       "      <td>COSME VELHO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138697</th>\n",
       "      <td>CENTRO DE EDUCACAO INFANTIL CRISTO REDENTOR</td>\n",
       "      <td>COSME VELHO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141655</th>\n",
       "      <td>SOCIEDADE EDUC. PADRE AFFONSO LTDA</td>\n",
       "      <td>COSME VELHO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143163</th>\n",
       "      <td>ESPACO CRIA</td>\n",
       "      <td>COSME VELHO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143388</th>\n",
       "      <td>JANGADA ESCOLA EIRELI</td>\n",
       "      <td>COSME VELHO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Escola       Bairro\n",
       "135666                     COL SAO VICENTE DE PAULO  COSME VELHO\n",
       "135667    SOCIEDADE BRASILEIRA DE ENSINO - COL SION  COSME VELHO\n",
       "135691  0204009 ESCOLA MUNICIPAL GUARARAPES CANDIDO  COSME VELHO\n",
       "137696          0204601 CRECHE MUNICIPAL TIA AMALIA  COSME VELHO\n",
       "138697  CENTRO DE EDUCACAO INFANTIL CRISTO REDENTOR  COSME VELHO\n",
       "141655           SOCIEDADE EDUC. PADRE AFFONSO LTDA  COSME VELHO\n",
       "143163                                  ESPACO CRIA  COSME VELHO\n",
       "143388                        JANGADA ESCOLA EIRELI  COSME VELHO"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df_escolas_rio['Bairro'] = df_escolas_rio['Endereço'].str.extract(r'([^,.\\d]+?)[ .]*\\d{5}-\\d{3}')\n",
    "df_escolas_rio['Bairro'] = df_escolas_rio['Bairro'].str.strip()\n",
    "df_escolas_rio.loc[df_escolas_rio.Bairro == 'COSME VELHO', ['Escola', 'Bairro']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "1cba6fca-c850-42d5-a78e-d91ec390a55d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_escolas_rio.reset_index(drop=True).to_feather(dados_dir / 'escolas_rio.feather')"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "name": "07_csvp_altair.ipynb",
   "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.8.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}