notebooks/Pré_Processamento_Dados_de_Saneamento.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Pré-Processamento - Dados de Saneamento",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xRvLs2L0WUQy"
      },
      "source": [
        "# Informações Sobre os Dados"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "esN1cFxUbOZz"
      },
      "source": [
        "# Instalando Bibliotecas Adicionais"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3uhCuTiWbRtE"
      },
      "source": [
        "!pip install https://github.com/pandas-profiling/pandas-profiling/archive/master.zip &> /dev/null"
      ],
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TtGEgvz2ZhiB"
      },
      "source": [
        "# Importando Bibliotecas"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Jjcgkc1KZk2d"
      },
      "source": [
        "import altair as alt\n",
        "import numpy as np\n",
        "import pandas as pd\n",
        "from ipywidgets import widgets\n",
        "from pandas_profiling import ProfileReport"
      ],
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "T-tdOfYObbqO"
      },
      "source": [
        "# Carregando os Dados"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nxMSuHGaDpT7"
      },
      "source": [
        "Carregando os dados aglutinados de desmatamento e casos de dengue, para pegar a lista de municípios."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1K7OqxFWEYTE",
        "outputId": "130922b2-5972-4e5f-faba-6fc5a8ef43f5"
      },
      "source": [
        "df_aglut = pd.read_csv(\n",
        "    f'https://raw.githubusercontent.com/george-gca/MO826_IA368_CF044/main/data/processed/dados_conjuntos.csv')\n",
        "\n",
        "cities = set(df_aglut.Municipio.unique())\n",
        "print(f'{len(cities)} municípios')"
      ],
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "740 municípios\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dA8XnGc7di1F"
      },
      "source": [
        "Criando dicionário que associa município ao seu respectivo estado"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5tYu-h9FQALg"
      },
      "source": [
        "states = set(df_aglut.Estado.unique())\n",
        "state_dict = df_aglut[['Municipio', 'Estado']].set_index('Municipio').to_dict()['Estado']"
      ],
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "KiSOdLzZ_3Nn",
        "outputId": "b5eb8d66-fcd8-474c-dc53-122afd70bba4"
      },
      "source": [
        "years = list(range(2010, 2020))\n",
        "print(f'Anos a serem considerados: {years}')"
      ],
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Anos a serem considerados: [2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019]\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nhp3647E3gRJ"
      },
      "source": [
        "def load_csv_info(group, years, cities, state_dict):\n",
        "  dfs = []\n",
        "  for year in years:\n",
        "    df = pd.read_csv(\n",
        "        f'https://raw.githubusercontent.com/george-gca/MO826_IA368_CF044/main/data/external/saneamento/{year}_{group}.csv',\n",
        "        sep='\\t')\n",
        "\n",
        "    # remove line breaks from columns titles\n",
        "    columns = [c.replace('\\n', ' ') for c in df.columns]\n",
        "    df.columns = columns\n",
        "\n",
        "    # remove (Município) (code) from city name, rename Localidade to Municipio\n",
        "    df.Localidade = df.Localidade.str.split().str[:-2]\n",
        "    df.Localidade = df.Localidade.str.join(' ')\n",
        "    df.rename(columns={'Localidade': 'Municipio'}, inplace=True)\n",
        "\n",
        "    # keep only cities that are in the previous list of cities\n",
        "    df = df[df.Municipio.isin(cities)]\n",
        "    \n",
        "    # fill state information\n",
        "    # TODO: fix complains about slicing\n",
        "    df['Estado'] = df.Municipio\n",
        "    df['Estado'] = df.Estado.replace(state_dict)\n",
        "    df['Ano'] = year\n",
        "\n",
        "    # reorder columns to put Municipio, Estado, and Ano at first\n",
        "    columns = list(df.columns)\n",
        "    columns = columns[:1] + columns[-2:] + columns[1:-2]\n",
        "    df = df[columns]\n",
        "\n",
        "    # remove '%' and '.', replace ',' by '.' for numeric types\n",
        "    for c in df.columns[3:]:\n",
        "      if df[c].dtype == object:\n",
        "        df[c] = df[c].str.replace('%', '')\n",
        "        df[c] = df[c].str.replace('.', '')\n",
        "        df[c] = df[c].str.replace(',', '.')\n",
        "\n",
        "    dfs.append(df)\n",
        "\n",
        "  return pd.concat(dfs, ignore_index=True)"
      ],
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 694
        },
        "id": "kekjxxMUQScX",
        "outputId": "731d1d05-1efb-4fc1-80c8-ba8982b09a45"
      },
      "source": [
        "df_01 = load_csv_info('01', years, cities, state_dict)\n",
        "df_01"
      ],
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Municipio</th>\n",
              "      <th>Estado</th>\n",
              "      <th>Ano</th>\n",
              "      <th>População total (pessoas) (IBGE)</th>\n",
              "      <th>População das cidades declarantes do SNIS (pessoas) (SNIS)</th>\n",
              "      <th>População urbana total (pessoas) (SNIS)</th>\n",
              "      <th>Área do município (km²) (IBGE)</th>\n",
              "      <th>População total que mora em domicílios sem acesso ao serviço de coleta de esgoto (pessoas) (SNIS)</th>\n",
              "      <th>População urbana que mora em domicílios sem acesso ao serviço de coleta de esgoto (pessoas) (SNIS)</th>\n",
              "      <th>Parcela da população urbana que mora em domicílios sem acesso ao serviço de coleta de esgoto (% da população) (SNIS)</th>\n",
              "      <th>Parcela da população total que mora em domicílios sem acesso ao serviço de coleta de esgoto (% da população) (SNIS)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ariquemes</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>90353</td>\n",
              "      <td>90353</td>\n",
              "      <td>76525</td>\n",
              "      <td>4426.570</td>\n",
              "      <td>90353</td>\n",
              "      <td>76525</td>\n",
              "      <td>100.0</td>\n",
              "      <td>100.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Cacoal</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>78574</td>\n",
              "      <td>78574</td>\n",
              "      <td>61921</td>\n",
              "      <td>3792.950</td>\n",
              "      <td>42574</td>\n",
              "      <td>25921</td>\n",
              "      <td>41.9</td>\n",
              "      <td>54.2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Jaru</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>52005</td>\n",
              "      <td>52005</td>\n",
              "      <td>35118</td>\n",
              "      <td>2944.130</td>\n",
              "      <td>52005</td>\n",
              "      <td>35118</td>\n",
              "      <td>100.0</td>\n",
              "      <td>100.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Ji-Paraná</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>116610</td>\n",
              "      <td>116610</td>\n",
              "      <td>104858</td>\n",
              "      <td>6896.650</td>\n",
              "      <td>116610</td>\n",
              "      <td>104858</td>\n",
              "      <td>100.0</td>\n",
              "      <td>100.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Porto Velho</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>428527</td>\n",
              "      <td>428527</td>\n",
              "      <td>390733</td>\n",
              "      <td>34090.960</td>\n",
              "      <td>422016</td>\n",
              "      <td>384222</td>\n",
              "      <td>98.3</td>\n",
              "      <td>98.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1265</th>\n",
              "      <td>Santo Antônio do Leverger</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>16628</td>\n",
              "      <td>-</td>\n",
              "      <td>6448</td>\n",
              "      <td>11735.750</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1266</th>\n",
              "      <td>Sinop</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>142996</td>\n",
              "      <td>142996</td>\n",
              "      <td>118536</td>\n",
              "      <td>3942.230</td>\n",
              "      <td>100677</td>\n",
              "      <td>76217</td>\n",
              "      <td>64.3</td>\n",
              "      <td>70.4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1267</th>\n",
              "      <td>Sorriso</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>90313</td>\n",
              "      <td>90313</td>\n",
              "      <td>79239</td>\n",
              "      <td>9347.560</td>\n",
              "      <td>60306</td>\n",
              "      <td>49232</td>\n",
              "      <td>62.1</td>\n",
              "      <td>66.8</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1268</th>\n",
              "      <td>Tangará da Serra</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>103750</td>\n",
              "      <td>103750</td>\n",
              "      <td>94411</td>\n",
              "      <td>11601.250</td>\n",
              "      <td>74224</td>\n",
              "      <td>64885</td>\n",
              "      <td>68.7</td>\n",
              "      <td>71.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1269</th>\n",
              "      <td>Várzea Grande</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>284971</td>\n",
              "      <td>284971</td>\n",
              "      <td>280580</td>\n",
              "      <td>1048.210</td>\n",
              "      <td>199059</td>\n",
              "      <td>194668</td>\n",
              "      <td>69.4</td>\n",
              "      <td>69.9</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1270 rows × 11 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                      Municipio  ... Parcela da população total que mora em domicílios sem acesso ao serviço de coleta de esgoto (% da população) (SNIS)\n",
              "0                     Ariquemes  ...                                              100.0                                                                 \n",
              "1                        Cacoal  ...                                               54.2                                                                 \n",
              "2                          Jaru  ...                                              100.0                                                                 \n",
              "3                     Ji-Paraná  ...                                              100.0                                                                 \n",
              "4                   Porto Velho  ...                                               98.5                                                                 \n",
              "...                         ...  ...                                                ...                                                                 \n",
              "1265  Santo Antônio do Leverger  ...                                                  -                                                                 \n",
              "1266                      Sinop  ...                                               70.4                                                                 \n",
              "1267                    Sorriso  ...                                               66.8                                                                 \n",
              "1268           Tangará da Serra  ...                                               71.5                                                                 \n",
              "1269              Várzea Grande  ...                                               69.9                                                                 \n",
              "\n",
              "[1270 rows x 11 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 694
        },
        "id": "psfHx1HdTjC4",
        "outputId": "912460ee-2d33-47a2-c54d-69e766aa06cb"
      },
      "source": [
        "df_02 = load_csv_info('02', years, cities, state_dict)\n",
        "df_02"
      ],
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Municipio</th>\n",
              "      <th>Estado</th>\n",
              "      <th>Ano</th>\n",
              "      <th>Internações por doenças gastrointestinais infecciosas (Número de internações) (DATASUS)</th>\n",
              "      <th>Internações por febre amarela (Número de internações) (DATASUS)</th>\n",
              "      <th>Internações por dengue (Número de internações) (DATASUS)</th>\n",
              "      <th>Internações por leptospirose (Número de internações) (DATASUS)</th>\n",
              "      <th>Internações por malária (Número de internações) (DATASUS)</th>\n",
              "      <th>Internações por esquistossomose (Número de internações) (DATASUS)</th>\n",
              "      <th>Taxa de incidência de internações por doenças associadas à falta de saneamento (Internações por 10 mil habitantes) (DATASUS)</th>\n",
              "      <th>Internações por doenças associadas à falta de saneamento (Número de internações) (DATASUS)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ariquemes</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>111.0</td>\n",
              "      <td>0</td>\n",
              "      <td>113.0</td>\n",
              "      <td>0</td>\n",
              "      <td>40</td>\n",
              "      <td>0</td>\n",
              "      <td>29.22</td>\n",
              "      <td>264.000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Cacoal</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>222.0</td>\n",
              "      <td>0</td>\n",
              "      <td>179.0</td>\n",
              "      <td>0</td>\n",
              "      <td>6</td>\n",
              "      <td>0</td>\n",
              "      <td>51.80</td>\n",
              "      <td>407.000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Jaru</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>64.0</td>\n",
              "      <td>0</td>\n",
              "      <td>109.0</td>\n",
              "      <td>0</td>\n",
              "      <td>31</td>\n",
              "      <td>0</td>\n",
              "      <td>39.23</td>\n",
              "      <td>204.000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Ji-Paraná</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>461.0</td>\n",
              "      <td>0</td>\n",
              "      <td>524.0</td>\n",
              "      <td>1</td>\n",
              "      <td>39</td>\n",
              "      <td>0</td>\n",
              "      <td>87.90</td>\n",
              "      <td>1.025</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Porto Velho</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>510.0</td>\n",
              "      <td>0</td>\n",
              "      <td>255.0</td>\n",
              "      <td>2</td>\n",
              "      <td>259</td>\n",
              "      <td>0</td>\n",
              "      <td>23.94</td>\n",
              "      <td>1.026</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1265</th>\n",
              "      <td>Santo Antônio do Leverger</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>2.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1.20</td>\n",
              "      <td>2.000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1266</th>\n",
              "      <td>Sinop</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>21.0</td>\n",
              "      <td>0</td>\n",
              "      <td>9.0</td>\n",
              "      <td>0</td>\n",
              "      <td>3</td>\n",
              "      <td>0</td>\n",
              "      <td>2.31</td>\n",
              "      <td>33.000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1267</th>\n",
              "      <td>Sorriso</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>26.0</td>\n",
              "      <td>0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>0</td>\n",
              "      <td>3</td>\n",
              "      <td>0</td>\n",
              "      <td>3.43</td>\n",
              "      <td>31.000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1268</th>\n",
              "      <td>Tangará da Serra</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>133.0</td>\n",
              "      <td>0</td>\n",
              "      <td>17.0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>14.55</td>\n",
              "      <td>151.000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1269</th>\n",
              "      <td>Várzea Grande</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>104.0</td>\n",
              "      <td>0</td>\n",
              "      <td>7.0</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>3.97</td>\n",
              "      <td>113.000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1270 rows × 11 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                      Municipio  ... Internações por doenças associadas à falta de saneamento (Número de internações) (DATASUS)\n",
              "0                     Ariquemes  ...                                            264.000                                        \n",
              "1                        Cacoal  ...                                            407.000                                        \n",
              "2                          Jaru  ...                                            204.000                                        \n",
              "3                     Ji-Paraná  ...                                              1.025                                        \n",
              "4                   Porto Velho  ...                                              1.026                                        \n",
              "...                         ...  ...                                                ...                                        \n",
              "1265  Santo Antônio do Leverger  ...                                              2.000                                        \n",
              "1266                      Sinop  ...                                             33.000                                        \n",
              "1267                    Sorriso  ...                                             31.000                                        \n",
              "1268           Tangará da Serra  ...                                            151.000                                        \n",
              "1269              Várzea Grande  ...                                            113.000                                        \n",
              "\n",
              "[1270 rows x 11 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 745
        },
        "id": "J_TF0dD_UcYx",
        "outputId": "6c6e4de8-9367-4cdc-94a1-8f4dbc22cba2"
      },
      "source": [
        "df_03 = load_csv_info('03', years, cities, state_dict)\n",
        "df_03"
      ],
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:22: SettingWithCopyWarning: \n",
            "A value is trying to be set on a copy of a slice from a DataFrame.\n",
            "Try using .loc[row_indexer,col_indexer] = value instead\n",
            "\n",
            "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n"
          ],
          "name": "stderr"
        },
        {
          "output_type": "execute_result",
          "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>Municipio</th>\n",
              "      <th>Estado</th>\n",
              "      <th>Ano</th>\n",
              "      <th>Taxa de incidência de internações de  doenças gastrointestinais infecciosas (Internações por 10 mil habitantes) (DATASUS)</th>\n",
              "      <th>Taxa de incidência de internações de  febre amarela (Internações por 10 mil habitantes) (DATASUS)</th>\n",
              "      <th>Taxa de incidência de internações de  dengue (Internações por 10 mil habitantes) (DATASUS)</th>\n",
              "      <th>Taxa de incidência de internações de  leptospirose (Internações por 10 mil habitantes) (DATASUS)</th>\n",
              "      <th>Taxa de incidência de internações de  malária (Internações por 10 mil habitantes) (DATASUS)</th>\n",
              "      <th>Taxa de incidência de internações de  esquistossomose (Internações por 10 mil habitantes) (DATASUS)</th>\n",
              "      <th>Internações por doenças respiratórias: gripes e pneumonias (Número de internações) (DATASUS)</th>\n",
              "      <th>Incidência de internações por doenças respiratórias: gripes e pneumonias (Internações por 10 mil habitantes) (DATASUS)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ariquemes</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>12.29</td>\n",
              "      <td>0.00</td>\n",
              "      <td>12.51</td>\n",
              "      <td>0.00</td>\n",
              "      <td>4.43</td>\n",
              "      <td>0.00</td>\n",
              "      <td>397.0</td>\n",
              "      <td>43.94</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Cacoal</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>28.25</td>\n",
              "      <td>0.00</td>\n",
              "      <td>22.78</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.76</td>\n",
              "      <td>0.00</td>\n",
              "      <td>450.0</td>\n",
              "      <td>57.27</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Jaru</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>12.31</td>\n",
              "      <td>0.00</td>\n",
              "      <td>20.96</td>\n",
              "      <td>0.00</td>\n",
              "      <td>5.96</td>\n",
              "      <td>0.00</td>\n",
              "      <td>162.0</td>\n",
              "      <td>31.15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Ji-Paraná</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>39.53</td>\n",
              "      <td>0.00</td>\n",
              "      <td>44.94</td>\n",
              "      <td>0.09</td>\n",
              "      <td>3.34</td>\n",
              "      <td>0.00</td>\n",
              "      <td>766.0</td>\n",
              "      <td>65.69</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Porto Velho</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>11.90</td>\n",
              "      <td>0.00</td>\n",
              "      <td>5.95</td>\n",
              "      <td>0.05</td>\n",
              "      <td>6.04</td>\n",
              "      <td>0.00</td>\n",
              "      <td>979.0</td>\n",
              "      <td>22.85</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1265</th>\n",
              "      <td>Santo Antônio do Leverger</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>1.20</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>21.0</td>\n",
              "      <td>12.63</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1266</th>\n",
              "      <td>Sinop</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>1.47</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.63</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.21</td>\n",
              "      <td>0.00</td>\n",
              "      <td>102.0</td>\n",
              "      <td>7.13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1267</th>\n",
              "      <td>Sorriso</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>2.88</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.22</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.33</td>\n",
              "      <td>0.00</td>\n",
              "      <td>147.0</td>\n",
              "      <td>16.28</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1268</th>\n",
              "      <td>Tangará da Serra</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>12.82</td>\n",
              "      <td>0.00</td>\n",
              "      <td>1.64</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.10</td>\n",
              "      <td>0.00</td>\n",
              "      <td>436.0</td>\n",
              "      <td>42.02</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1269</th>\n",
              "      <td>Várzea Grande</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>3.65</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.25</td>\n",
              "      <td>0.04</td>\n",
              "      <td>0.04</td>\n",
              "      <td>0.00</td>\n",
              "      <td>669.0</td>\n",
              "      <td>23.48</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1270 rows × 11 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                      Municipio  ... Incidência de internações por doenças respiratórias: gripes e pneumonias (Internações por 10 mil habitantes) (DATASUS)\n",
              "0                     Ariquemes  ...                                              43.94                                                                    \n",
              "1                        Cacoal  ...                                              57.27                                                                    \n",
              "2                          Jaru  ...                                              31.15                                                                    \n",
              "3                     Ji-Paraná  ...                                              65.69                                                                    \n",
              "4                   Porto Velho  ...                                              22.85                                                                    \n",
              "...                         ...  ...                                                ...                                                                    \n",
              "1265  Santo Antônio do Leverger  ...                                              12.63                                                                    \n",
              "1266                      Sinop  ...                                               7.13                                                                    \n",
              "1267                    Sorriso  ...                                              16.28                                                                    \n",
              "1268           Tangará da Serra  ...                                              42.02                                                                    \n",
              "1269              Várzea Grande  ...                                              23.48                                                                    \n",
              "\n",
              "[1270 rows x 11 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 643
        },
        "id": "km7-zY3YU2ko",
        "outputId": "5d02d1a7-04ed-4aae-ad6e-7c1d4bbf7362"
      },
      "source": [
        "df_04 = load_csv_info('04', years, cities, state_dict)\n",
        "df_04"
      ],
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Municipio</th>\n",
              "      <th>Estado</th>\n",
              "      <th>Ano</th>\n",
              "      <th>Extensão da rede de distribuição de água, em km (km) (SNIS)</th>\n",
              "      <th>Extensão da rede de distribuição de esgoto, em km (km) (SNIS)</th>\n",
              "      <th>Investimentos realizados pelo prestador de serviços, em R$ de 2017 (R$ a preços de 2019) (ITB)</th>\n",
              "      <th>Investimentos realizados pelo município, em R$ de 2017 (R$ a preços de 2019) (ITB)</th>\n",
              "      <th>Investimentos realizados pelo governo estadual, em R$ de 2017 (R$ a preços de 2019) (ITB)</th>\n",
              "      <th>Investimentos totais em saneamento (R$ a preços de 2019) (ITB)</th>\n",
              "      <th>Investimentos per capita em saneamento (R$ a preços de 2019) (ITB)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ariquemes</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>182.20</td>\n",
              "      <td>0.00</td>\n",
              "      <td>311124.09</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>311124.09</td>\n",
              "      <td>3.44</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Cacoal</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>407.00</td>\n",
              "      <td>158.00</td>\n",
              "      <td>964646.18</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>964646.18</td>\n",
              "      <td>12.28</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Jaru</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>91.10</td>\n",
              "      <td>0.00</td>\n",
              "      <td>285941.82</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>285941.82</td>\n",
              "      <td>5.50</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Ji-Paraná</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>282.70</td>\n",
              "      <td>0.00</td>\n",
              "      <td>509546.33</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>509546.33</td>\n",
              "      <td>4.37</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Porto Velho</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>881.50</td>\n",
              "      <td>30.00</td>\n",
              "      <td>1825895.75</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>1825895.75</td>\n",
              "      <td>4.26</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1265</th>\n",
              "      <td>Santo Antônio do Leverger</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1266</th>\n",
              "      <td>Sinop</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>952.56</td>\n",
              "      <td>225.61</td>\n",
              "      <td>7640351.90</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>7640351.90</td>\n",
              "      <td>53.43</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1267</th>\n",
              "      <td>Sorriso</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>462.89</td>\n",
              "      <td>311.28</td>\n",
              "      <td>10720087.36</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>10720087.36</td>\n",
              "      <td>118.70</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1268</th>\n",
              "      <td>Tangará da Serra</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>355.15</td>\n",
              "      <td>138.34</td>\n",
              "      <td>1239501.58</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>1239501.58</td>\n",
              "      <td>11.95</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1269</th>\n",
              "      <td>Várzea Grande</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>1250.45</td>\n",
              "      <td>187.67</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1270 rows × 10 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                      Municipio  ... Investimentos per capita em saneamento (R$ a preços de 2019) (ITB)\n",
              "0                     Ariquemes  ...                                               3.44                \n",
              "1                        Cacoal  ...                                              12.28                \n",
              "2                          Jaru  ...                                               5.50                \n",
              "3                     Ji-Paraná  ...                                               4.37                \n",
              "4                   Porto Velho  ...                                               4.26                \n",
              "...                         ...  ...                                                ...                \n",
              "1265  Santo Antônio do Leverger  ...                                               0.00                \n",
              "1266                      Sinop  ...                                              53.43                \n",
              "1267                    Sorriso  ...                                             118.70                \n",
              "1268           Tangará da Serra  ...                                              11.95                \n",
              "1269              Várzea Grande  ...                                               0.00                \n",
              "\n",
              "[1270 rows x 10 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 711
        },
        "id": "30mkkLMTU5Sh",
        "outputId": "387fcc34-1816-450e-d3d1-cf09654487fa"
      },
      "source": [
        "df_05 = load_csv_info('05', years, cities, state_dict)\n",
        "df_05"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Municipio</th>\n",
              "      <th>Estado</th>\n",
              "      <th>Ano</th>\n",
              "      <th>Número de moradias permanentes (habitações) (IBGE)</th>\n",
              "      <th>Número de moradias permanentes em áreas urbanas (habitações) (IBGE)</th>\n",
              "      <th>Número de pessoas por moradia (pessoas por moradia) (IBGE)</th>\n",
              "      <th>Número de pessoas por moradia (área urbana) (pessoas por moradia) (IBGE)</th>\n",
              "      <th>PIB, em R$ milhões a preços de 2017 (R$ milhões a preços de 2019) (IBGE)</th>\n",
              "      <th>PIB per capita, em R$ a preços de 2017 (R$ por habitante a preços de 2019) (IBGE)</th>\n",
              "      <th>Área do município (km²) (IBGE)</th>\n",
              "      <th>Densidade demográfica (pessoas por km²) (Pessoas por km²) (IBGE)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ariquemes</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>27241</td>\n",
              "      <td>22976</td>\n",
              "      <td>3.32</td>\n",
              "      <td>3.33</td>\n",
              "      <td>2229.460</td>\n",
              "      <td>24674.98</td>\n",
              "      <td>4426.570</td>\n",
              "      <td>20.41</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Cacoal</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>24249</td>\n",
              "      <td>19416</td>\n",
              "      <td>3.24</td>\n",
              "      <td>3.19</td>\n",
              "      <td>1938.340</td>\n",
              "      <td>24668.96</td>\n",
              "      <td>3792.950</td>\n",
              "      <td>20.72</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Jaru</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>15946</td>\n",
              "      <td>10967</td>\n",
              "      <td>3.26</td>\n",
              "      <td>3.20</td>\n",
              "      <td>1302.520</td>\n",
              "      <td>25046.01</td>\n",
              "      <td>2944.130</td>\n",
              "      <td>17.66</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Ji-Paraná</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>35652</td>\n",
              "      <td>32278</td>\n",
              "      <td>3.27</td>\n",
              "      <td>3.25</td>\n",
              "      <td>3031.040</td>\n",
              "      <td>25992.99</td>\n",
              "      <td>6896.650</td>\n",
              "      <td>16.91</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Porto Velho</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>117142</td>\n",
              "      <td>108175</td>\n",
              "      <td>3.66</td>\n",
              "      <td>3.61</td>\n",
              "      <td>14856.300</td>\n",
              "      <td>34668.30</td>\n",
              "      <td>34090.960</td>\n",
              "      <td>12.57</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1265</th>\n",
              "      <td>Santo Antônio do Leverger</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>8441</td>\n",
              "      <td>3070</td>\n",
              "      <td>1.97</td>\n",
              "      <td>2.10</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>11735.750</td>\n",
              "      <td>1.42</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1266</th>\n",
              "      <td>Sinop</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>43864</td>\n",
              "      <td>37630</td>\n",
              "      <td>3.26</td>\n",
              "      <td>3.15</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>3942.230</td>\n",
              "      <td>36.27</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1267</th>\n",
              "      <td>Sorriso</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>25804</td>\n",
              "      <td>23169</td>\n",
              "      <td>3.50</td>\n",
              "      <td>3.42</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>9347.560</td>\n",
              "      <td>9.66</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1268</th>\n",
              "      <td>Tangará da Serra</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>32937</td>\n",
              "      <td>30954</td>\n",
              "      <td>3.15</td>\n",
              "      <td>3.05</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>11601.250</td>\n",
              "      <td>8.94</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1269</th>\n",
              "      <td>Várzea Grande</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>94990</td>\n",
              "      <td>96089</td>\n",
              "      <td>3.00</td>\n",
              "      <td>2.92</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>1048.210</td>\n",
              "      <td>271.86</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1270 rows × 11 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                      Municipio  ... Densidade demográfica (pessoas por km²) (Pessoas por km²) (IBGE)\n",
              "0                     Ariquemes  ...                                              20.41              \n",
              "1                        Cacoal  ...                                              20.72              \n",
              "2                          Jaru  ...                                              17.66              \n",
              "3                     Ji-Paraná  ...                                              16.91              \n",
              "4                   Porto Velho  ...                                              12.57              \n",
              "...                         ...  ...                                                ...              \n",
              "1265  Santo Antônio do Leverger  ...                                               1.42              \n",
              "1266                      Sinop  ...                                              36.27              \n",
              "1267                    Sorriso  ...                                               9.66              \n",
              "1268           Tangará da Serra  ...                                               8.94              \n",
              "1269              Várzea Grande  ...                                             271.86              \n",
              "\n",
              "[1270 rows x 11 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NDQt_OvztxAo"
      },
      "source": [
        "# Convertendo os Tipos de Dados"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "W0-TZig77Msu"
      },
      "source": [
        "def convert_dtypes(df):\n",
        "  df.replace({'-': np.NaN}, inplace=True)\n",
        "  for c in df.columns[3:]:\n",
        "    # if c.startswith('Área') or c.startswith('Parcela'):\n",
        "    if df[c].dtype == object:\n",
        "      df[c] = df[c].astype(float)\n",
        "    # else:\n",
        "      # df[c] = df[c].astype(int)\n",
        "  return df"
      ],
      "execution_count": 12,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qtuHjTbuYlo7"
      },
      "source": [
        "df_01 = convert_dtypes(df_01)\n",
        "df_02 = convert_dtypes(df_02)\n",
        "df_03 = convert_dtypes(df_03)\n",
        "df_04 = convert_dtypes(df_04)\n",
        "df_05 = convert_dtypes(df_05)"
      ],
      "execution_count": 13,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ZH1dupFaXoqj",
        "outputId": "f241ffaf-6484-4f30-cb6d-722103ab1092"
      },
      "source": [
        "print('Dados 01')\n",
        "for state in states:\n",
        "  df_tmp = df_01[df_01.Estado == state.upper()]\n",
        "  print(f'Total de Municípios em {state.upper()}: {len(df_tmp)}')\n",
        "  print(f'Total de Municípios Completos em {state.upper()}: {len(df_tmp.dropna())}\\n')"
      ],
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Dados 01\n",
            "Total de Municípios em AM: 150\n",
            "Total de Municípios Completos em AM: 82\n",
            "\n",
            "Total de Municípios em MA: 230\n",
            "Total de Municípios Completos em MA: 196\n",
            "\n",
            "Total de Municípios em AP: 20\n",
            "Total de Municípios Completos em AP: 20\n",
            "\n",
            "Total de Municípios em TO: 90\n",
            "Total de Municípios Completos em TO: 90\n",
            "\n",
            "Total de Municípios em RR: 20\n",
            "Total de Municípios Completos em RR: 20\n",
            "\n",
            "Total de Municípios em AC: 20\n",
            "Total de Municípios Completos em AC: 19\n",
            "\n",
            "Total de Municípios em PA: 460\n",
            "Total de Municípios Completos em PA: 379\n",
            "\n",
            "Total de Municípios em MT: 200\n",
            "Total de Municípios Completos em MT: 150\n",
            "\n",
            "Total de Municípios em RO: 80\n",
            "Total de Municípios Completos em RO: 76\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Wdo8XotFYNEJ",
        "outputId": "71589b3f-7037-4fbd-dab4-87a532d330ac"
      },
      "source": [
        "print('Dados 02')\n",
        "for state in states:\n",
        "  df_tmp = df_02[df_02.Estado == state.upper()]\n",
        "  print(f'Total de Municípios em {state.upper()}: {len(df_tmp)}')\n",
        "  print(f'Total de Municípios Completos em {state.upper()}: {len(df_tmp.dropna())}\\n')"
      ],
      "execution_count": 15,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Dados 02\n",
            "Total de Municípios em AM: 150\n",
            "Total de Municípios Completos em AM: 150\n",
            "\n",
            "Total de Municípios em MA: 230\n",
            "Total de Municípios Completos em MA: 230\n",
            "\n",
            "Total de Municípios em AP: 20\n",
            "Total de Municípios Completos em AP: 20\n",
            "\n",
            "Total de Municípios em TO: 90\n",
            "Total de Municípios Completos em TO: 90\n",
            "\n",
            "Total de Municípios em RR: 20\n",
            "Total de Municípios Completos em RR: 20\n",
            "\n",
            "Total de Municípios em AC: 20\n",
            "Total de Municípios Completos em AC: 20\n",
            "\n",
            "Total de Municípios em PA: 460\n",
            "Total de Municípios Completos em PA: 460\n",
            "\n",
            "Total de Municípios em MT: 200\n",
            "Total de Municípios Completos em MT: 200\n",
            "\n",
            "Total de Municípios em RO: 80\n",
            "Total de Municípios Completos em RO: 80\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BSqNdqRhYOTu",
        "outputId": "1a1ed742-5224-42eb-8353-1d68dd6f18a7"
      },
      "source": [
        "print('Dados 03')\n",
        "for state in states:\n",
        "  df_tmp = df_03[df_03.Estado == state.upper()]\n",
        "  print(f'Total de Municípios em {state.upper()}: {len(df_tmp)}')\n",
        "  print(f'Total de Municípios Completos em {state.upper()}: {len(df_tmp.dropna())}\\n')"
      ],
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Dados 03\n",
            "Total de Municípios em AM: 150\n",
            "Total de Municípios Completos em AM: 150\n",
            "\n",
            "Total de Municípios em MA: 230\n",
            "Total de Municípios Completos em MA: 230\n",
            "\n",
            "Total de Municípios em AP: 20\n",
            "Total de Municípios Completos em AP: 20\n",
            "\n",
            "Total de Municípios em TO: 90\n",
            "Total de Municípios Completos em TO: 90\n",
            "\n",
            "Total de Municípios em RR: 20\n",
            "Total de Municípios Completos em RR: 20\n",
            "\n",
            "Total de Municípios em AC: 20\n",
            "Total de Municípios Completos em AC: 20\n",
            "\n",
            "Total de Municípios em PA: 460\n",
            "Total de Municípios Completos em PA: 460\n",
            "\n",
            "Total de Municípios em MT: 200\n",
            "Total de Municípios Completos em MT: 200\n",
            "\n",
            "Total de Municípios em RO: 80\n",
            "Total de Municípios Completos em RO: 80\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "gGU9X9B-YOHg",
        "outputId": "6b4a9737-b9dc-4839-f6c9-f19aeb0bb9c0"
      },
      "source": [
        "print('Dados 04')\n",
        "for state in states:\n",
        "  df_tmp = df_04[df_04.Estado == state.upper()]\n",
        "  print(f'Total de Municípios em {state.upper()}: {len(df_tmp)}')\n",
        "  print(f'Total de Municípios Completos em {state.upper()}: {len(df_tmp.dropna())}\\n')"
      ],
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Dados 04\n",
            "Total de Municípios em AM: 150\n",
            "Total de Municípios Completos em AM: 78\n",
            "\n",
            "Total de Municípios em MA: 230\n",
            "Total de Municípios Completos em MA: 183\n",
            "\n",
            "Total de Municípios em AP: 20\n",
            "Total de Municípios Completos em AP: 20\n",
            "\n",
            "Total de Municípios em TO: 90\n",
            "Total de Municípios Completos em TO: 90\n",
            "\n",
            "Total de Municípios em RR: 20\n",
            "Total de Municípios Completos em RR: 20\n",
            "\n",
            "Total de Municípios em AC: 20\n",
            "Total de Municípios Completos em AC: 18\n",
            "\n",
            "Total de Municípios em PA: 460\n",
            "Total de Municípios Completos em PA: 350\n",
            "\n",
            "Total de Municípios em MT: 200\n",
            "Total de Municípios Completos em MT: 144\n",
            "\n",
            "Total de Municípios em RO: 80\n",
            "Total de Municípios Completos em RO: 71\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "JHw2-choYNoQ",
        "outputId": "ebc9c9e0-40b9-4707-9280-01295c19fd03"
      },
      "source": [
        "print('Dados 05')\n",
        "for state in states:\n",
        "  df_tmp = df_05[df_05.Estado == state.upper()]\n",
        "  print(f'Total de Municípios em {state.upper()}: {len(df_tmp)}')\n",
        "  print(f'Total de Municípios Completos em {state.upper()}: {len(df_tmp.dropna())}\\n')"
      ],
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Dados 05\n",
            "Total de Municípios em AM: 150\n",
            "Total de Municípios Completos em AM: 135\n",
            "\n",
            "Total de Municípios em MA: 230\n",
            "Total de Municípios Completos em MA: 207\n",
            "\n",
            "Total de Municípios em AP: 20\n",
            "Total de Municípios Completos em AP: 18\n",
            "\n",
            "Total de Municípios em TO: 90\n",
            "Total de Municípios Completos em TO: 81\n",
            "\n",
            "Total de Municípios em RR: 20\n",
            "Total de Municípios Completos em RR: 18\n",
            "\n",
            "Total de Municípios em AC: 20\n",
            "Total de Municípios Completos em AC: 18\n",
            "\n",
            "Total de Municípios em PA: 460\n",
            "Total de Municípios Completos em PA: 414\n",
            "\n",
            "Total de Municípios em MT: 200\n",
            "Total de Municípios Completos em MT: 180\n",
            "\n",
            "Total de Municípios em RO: 80\n",
            "Total de Municípios Completos em RO: 72\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fXBIhizwdCo8"
      },
      "source": [
        "# Gerando Estatísticas Básicas Sobre os Dados"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vl_s4FfcvvYx"
      },
      "source": [
        "Quantos municípios possuem cada estado na base de dados"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4Zyd07WkvqVH",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "9b6087b3-d345-4e53-dad3-5b0ebe28b8ab"
      },
      "source": [
        "states = df_01.Estado.unique()\n",
        "for state in states:\n",
        "  print(f'{state} possui {len(df_01[df_01.Estado == state].Municipio.unique())} municípios no grupo 01')\n",
        "  print(f'{state} possui {len(df_02[df_02.Estado == state].Municipio.unique())} municípios no grupo 02')\n",
        "  print(f'{state} possui {len(df_03[df_03.Estado == state].Municipio.unique())} municípios no grupo 03')\n",
        "  print(f'{state} possui {len(df_04[df_04.Estado == state].Municipio.unique())} municípios no grupo 04')\n",
        "  print(f'{state} possui {len(df_05[df_05.Estado == state].Municipio.unique())} municípios no grupo 05\\n')"
      ],
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "RO possui 8 municípios no grupo 01\n",
            "RO possui 8 municípios no grupo 02\n",
            "RO possui 8 municípios no grupo 03\n",
            "RO possui 8 municípios no grupo 04\n",
            "RO possui 8 municípios no grupo 05\n",
            "\n",
            "AC possui 2 municípios no grupo 01\n",
            "AC possui 2 municípios no grupo 02\n",
            "AC possui 2 municípios no grupo 03\n",
            "AC possui 2 municípios no grupo 04\n",
            "AC possui 2 municípios no grupo 05\n",
            "\n",
            "AM possui 15 municípios no grupo 01\n",
            "AM possui 15 municípios no grupo 02\n",
            "AM possui 15 municípios no grupo 03\n",
            "AM possui 15 municípios no grupo 04\n",
            "AM possui 15 municípios no grupo 05\n",
            "\n",
            "RR possui 2 municípios no grupo 01\n",
            "RR possui 2 municípios no grupo 02\n",
            "RR possui 2 municípios no grupo 03\n",
            "RR possui 2 municípios no grupo 04\n",
            "RR possui 2 municípios no grupo 05\n",
            "\n",
            "PA possui 45 municípios no grupo 01\n",
            "PA possui 45 municípios no grupo 02\n",
            "PA possui 45 municípios no grupo 03\n",
            "PA possui 45 municípios no grupo 04\n",
            "PA possui 45 municípios no grupo 05\n",
            "\n",
            "AP possui 2 municípios no grupo 01\n",
            "AP possui 2 municípios no grupo 02\n",
            "AP possui 2 municípios no grupo 03\n",
            "AP possui 2 municípios no grupo 04\n",
            "AP possui 2 municípios no grupo 05\n",
            "\n",
            "TO possui 8 municípios no grupo 01\n",
            "TO possui 8 municípios no grupo 02\n",
            "TO possui 8 municípios no grupo 03\n",
            "TO possui 8 municípios no grupo 04\n",
            "TO possui 8 municípios no grupo 05\n",
            "\n",
            "MA possui 21 municípios no grupo 01\n",
            "MA possui 21 municípios no grupo 02\n",
            "MA possui 21 municípios no grupo 03\n",
            "MA possui 21 municípios no grupo 04\n",
            "MA possui 21 municípios no grupo 05\n",
            "\n",
            "MT possui 20 municípios no grupo 01\n",
            "MT possui 20 municípios no grupo 02\n",
            "MT possui 20 municípios no grupo 03\n",
            "MT possui 20 municípios no grupo 04\n",
            "MT possui 20 municípios no grupo 05\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2Z9KdFdsdJYj"
      },
      "source": [
        "# Filtrando Colunas de Interesse e Aglutinando os Dados"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 572
        },
        "id": "JXXFruPUdLBK",
        "outputId": "023f7fae-7f05-4b3c-8fa2-880bd15b397f"
      },
      "source": [
        "df_01_columns = list(df_01.columns)\n",
        "df_01_columns = df_01_columns[:3] + \\\n",
        "  ['População total que mora em domicílios sem acesso ao serviço de coleta de esgoto (pessoas) (SNIS)',\n",
        "   'Parcela da população total que mora em domicílios sem acesso ao serviço de coleta de esgoto (% da população) (SNIS)']\n",
        "\n",
        "df_04_columns = ['Extensão da rede de distribuição de esgoto, em km (km) (SNIS)']\n",
        "\n",
        "df_05_columns = ['Densidade demográfica (pessoas por km²) (Pessoas por km²) (IBGE)']\n",
        "\n",
        "df_useful = pd.concat([df_01[df_01_columns], df_04[df_04_columns], df_05[df_05_columns]], axis=1)\n",
        "df_useful"
      ],
      "execution_count": 20,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Municipio</th>\n",
              "      <th>Estado</th>\n",
              "      <th>Ano</th>\n",
              "      <th>População total que mora em domicílios sem acesso ao serviço de coleta de esgoto (pessoas) (SNIS)</th>\n",
              "      <th>Parcela da população total que mora em domicílios sem acesso ao serviço de coleta de esgoto (% da população) (SNIS)</th>\n",
              "      <th>Extensão da rede de distribuição de esgoto, em km (km) (SNIS)</th>\n",
              "      <th>Densidade demográfica (pessoas por km²) (Pessoas por km²) (IBGE)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ariquemes</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>90353.0</td>\n",
              "      <td>100.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>20.41</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Cacoal</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>42574.0</td>\n",
              "      <td>54.2</td>\n",
              "      <td>158.00</td>\n",
              "      <td>20.72</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Jaru</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>52005.0</td>\n",
              "      <td>100.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>17.66</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Ji-Paraná</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>116610.0</td>\n",
              "      <td>100.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>16.91</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Porto Velho</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>422016.0</td>\n",
              "      <td>98.5</td>\n",
              "      <td>30.00</td>\n",
              "      <td>12.57</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1265</th>\n",
              "      <td>Santo Antônio do Leverger</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1.42</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1266</th>\n",
              "      <td>Sinop</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>100677.0</td>\n",
              "      <td>70.4</td>\n",
              "      <td>225.61</td>\n",
              "      <td>36.27</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1267</th>\n",
              "      <td>Sorriso</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>60306.0</td>\n",
              "      <td>66.8</td>\n",
              "      <td>311.28</td>\n",
              "      <td>9.66</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1268</th>\n",
              "      <td>Tangará da Serra</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>74224.0</td>\n",
              "      <td>71.5</td>\n",
              "      <td>138.34</td>\n",
              "      <td>8.94</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1269</th>\n",
              "      <td>Várzea Grande</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>199059.0</td>\n",
              "      <td>69.9</td>\n",
              "      <td>187.67</td>\n",
              "      <td>271.86</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1270 rows × 7 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                      Municipio  ... Densidade demográfica (pessoas por km²) (Pessoas por km²) (IBGE)\n",
              "0                     Ariquemes  ...                                              20.41              \n",
              "1                        Cacoal  ...                                              20.72              \n",
              "2                          Jaru  ...                                              17.66              \n",
              "3                     Ji-Paraná  ...                                              16.91              \n",
              "4                   Porto Velho  ...                                              12.57              \n",
              "...                         ...  ...                                                ...              \n",
              "1265  Santo Antônio do Leverger  ...                                               1.42              \n",
              "1266                      Sinop  ...                                              36.27              \n",
              "1267                    Sorriso  ...                                               9.66              \n",
              "1268           Tangará da Serra  ...                                               8.94              \n",
              "1269              Várzea Grande  ...                                             271.86              \n",
              "\n",
              "[1270 rows x 7 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "lRX3AM9Slofk",
        "outputId": "36d4c5f2-7630-4b38-b40f-27236dc87dd4"
      },
      "source": [
        "df_useful.Municipio.unique()"
      ],
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array(['Ariquemes', 'Cacoal', 'Jaru', 'Ji-Paraná', 'Porto Velho',\n",
              "       'Rolim de Moura', 'Vilhena', 'Cruzeiro do Sul', 'Rio Branco',\n",
              "       'Careiro da Várzea', 'Coari', 'Humaitá', 'Iranduba', 'Itacoatiara',\n",
              "       'Manacapuru', 'Manaus', 'Manicoré', 'Maués', 'Novo Airão',\n",
              "       'Parintins', 'Presidente Figueiredo', 'Rio Preto da Eva',\n",
              "       'Tabatinga', 'Tefé', 'Boa Vista', 'Abaetetuba', 'Acará',\n",
              "       'Alenquer', 'Altamira', 'Ananindeua', 'Barcarena', 'Belém',\n",
              "       'Benevides', 'Bragança', 'Breu Branco', 'Breves', 'Cametá',\n",
              "       'Capanema', 'Capitão Poço', 'Castanhal', 'Dom Eliseu',\n",
              "       'Igarapé-Miri', 'Ipixuna do Pará', 'Itaituba', 'Itupiranga',\n",
              "       'Jacundá', 'Juruti', 'Marabá', 'Marituba', 'Moju', 'Monte Alegre',\n",
              "       'Novo Repartimento', 'Óbidos', 'Oriximiná', 'Paragominas',\n",
              "       'Parauapebas', 'Portel', 'Redenção', 'Rondon do Pará',\n",
              "       'Santa Bárbara do Pará', 'Santana do Araguaia', 'Santarém',\n",
              "       'São Félix do Xingu', 'São Miguel do Guamá', 'Tailândia',\n",
              "       'Tomé-Açu', 'Tucuruí', 'Ulianópolis', 'Vigia', 'Viseu', 'Macapá',\n",
              "       'Santana', 'Araguaína', 'Gurupi', 'Paraíso do Tocantins',\n",
              "       'Porto Nacional', 'Palmas', 'Açailândia', 'Alcântara', 'Bacabal',\n",
              "       'Balsas', 'Barra do Corda', 'Buriticupu', 'Coroatá', 'Grajaú',\n",
              "       'Imperatriz', 'Itapecuru Mirim', 'Paço do Lumiar', 'Pinheiro',\n",
              "       'Raposa', 'Santa Inês', 'Santa Luzia', 'São José de Ribamar',\n",
              "       'São Luís', 'Viana', 'Zé Doca', 'Santa Rita', 'Bonfim',\n",
              "       'Nova União', 'São João Batista', 'Alvorada', 'Cachoeirinha',\n",
              "       'Lajeado', 'Alta Floresta', 'Barão de Melgaço', 'Barra do Garças',\n",
              "       'Cáceres', 'Chapada dos Guimarães', 'Cuiabá', 'Jangada',\n",
              "       'Lucas do Rio Verde', 'Nossa Senhora do Livramento',\n",
              "       'Nova Brasilândia', 'Planalto da Serra', 'Poconé',\n",
              "       'Primavera do Leste', 'Rondonópolis', 'Rosário Oeste',\n",
              "       'Santo Antônio do Leverger', 'Sinop', 'Sorriso',\n",
              "       'Tangará da Serra', 'Várzea Grande'], dtype=object)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 524
        },
        "id": "0JsnaE2BmpAQ",
        "outputId": "50285062-6b1e-485b-e34e-f05ab95d2622"
      },
      "source": [
        "df_aglut = pd.read_csv(\n",
        "    f'https://raw.githubusercontent.com/george-gca/MO826_IA368_CF044/main/data/processed/dados_conjuntos_col_ano.csv')\n",
        "\n",
        "df_aglut"
      ],
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Lat</th>\n",
              "      <th>Long</th>\n",
              "      <th>Municipio</th>\n",
              "      <th>Estado</th>\n",
              "      <th>AreaKm2</th>\n",
              "      <th>Ano</th>\n",
              "      <th>Desmatado</th>\n",
              "      <th>Incremento</th>\n",
              "      <th>Floresta</th>\n",
              "      <th>NaoFloresta</th>\n",
              "      <th>Hidrografia</th>\n",
              "      <th>PercDesmatado</th>\n",
              "      <th>Internações - Dengue</th>\n",
              "      <th>Internações Ano Seguinte - Dengue</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>-1.73866</td>\n",
              "      <td>-48.85524</td>\n",
              "      <td>Abaetetuba</td>\n",
              "      <td>PA</td>\n",
              "      <td>1617</td>\n",
              "      <td>2008</td>\n",
              "      <td>683.3</td>\n",
              "      <td>1.2</td>\n",
              "      <td>505.6</td>\n",
              "      <td>13.6</td>\n",
              "      <td>413.6</td>\n",
              "      <td>0.422573</td>\n",
              "      <td>88.0</td>\n",
              "      <td>52.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>-4.96265</td>\n",
              "      <td>-48.39406</td>\n",
              "      <td>Abel Figueiredo</td>\n",
              "      <td>PA</td>\n",
              "      <td>616</td>\n",
              "      <td>2008</td>\n",
              "      <td>561.5</td>\n",
              "      <td>2.0</td>\n",
              "      <td>54.3</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.911526</td>\n",
              "      <td>12.0</td>\n",
              "      <td>25.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>-9.46606</td>\n",
              "      <td>-49.33901</td>\n",
              "      <td>Abreulândia</td>\n",
              "      <td>TO</td>\n",
              "      <td>1900</td>\n",
              "      <td>2008</td>\n",
              "      <td>7.4</td>\n",
              "      <td>1.9</td>\n",
              "      <td>47.9</td>\n",
              "      <td>1844.3</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.003895</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>-2.03379</td>\n",
              "      <td>-48.31904</td>\n",
              "      <td>Acará</td>\n",
              "      <td>PA</td>\n",
              "      <td>4361</td>\n",
              "      <td>2008</td>\n",
              "      <td>2263.7</td>\n",
              "      <td>47.9</td>\n",
              "      <td>1971.3</td>\n",
              "      <td>43.3</td>\n",
              "      <td>70.5</td>\n",
              "      <td>0.519078</td>\n",
              "      <td>86.0</td>\n",
              "      <td>57.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>-15.19422</td>\n",
              "      <td>-56.31716</td>\n",
              "      <td>Acorizal</td>\n",
              "      <td>MT</td>\n",
              "      <td>841</td>\n",
              "      <td>2008</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>840.8</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8875</th>\n",
              "      <td>-6.84864</td>\n",
              "      <td>-49.25551</td>\n",
              "      <td>Xinguara</td>\n",
              "      <td>PA</td>\n",
              "      <td>3793</td>\n",
              "      <td>2019</td>\n",
              "      <td>3381.1</td>\n",
              "      <td>1.2</td>\n",
              "      <td>394.3</td>\n",
              "      <td>0.0</td>\n",
              "      <td>17.6</td>\n",
              "      <td>0.891405</td>\n",
              "      <td>17.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8876</th>\n",
              "      <td>-3.25303</td>\n",
              "      <td>-45.97435</td>\n",
              "      <td>Zé Doca</td>\n",
              "      <td>MA</td>\n",
              "      <td>2438</td>\n",
              "      <td>2019</td>\n",
              "      <td>2021.6</td>\n",
              "      <td>1.1</td>\n",
              "      <td>376.2</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.829204</td>\n",
              "      <td>8.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8877</th>\n",
              "      <td>-6.76170</td>\n",
              "      <td>-50.32800</td>\n",
              "      <td>Água Azul do Norte</td>\n",
              "      <td>PA</td>\n",
              "      <td>7125</td>\n",
              "      <td>2019</td>\n",
              "      <td>4674.0</td>\n",
              "      <td>9.7</td>\n",
              "      <td>2323.1</td>\n",
              "      <td>127.9</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.656000</td>\n",
              "      <td>35.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8878</th>\n",
              "      <td>-14.08771</td>\n",
              "      <td>-52.43685</td>\n",
              "      <td>Água Boa</td>\n",
              "      <td>MT</td>\n",
              "      <td>7486</td>\n",
              "      <td>2019</td>\n",
              "      <td>40.1</td>\n",
              "      <td>0.0</td>\n",
              "      <td>123.5</td>\n",
              "      <td>7322.4</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.005357</td>\n",
              "      <td>10.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8879</th>\n",
              "      <td>0.07317</td>\n",
              "      <td>-55.75937</td>\n",
              "      <td>Óbidos</td>\n",
              "      <td>PA</td>\n",
              "      <td>28031</td>\n",
              "      <td>2019</td>\n",
              "      <td>1979.3</td>\n",
              "      <td>35.0</td>\n",
              "      <td>18174.5</td>\n",
              "      <td>4595.5</td>\n",
              "      <td>1351.5</td>\n",
              "      <td>0.070611</td>\n",
              "      <td>1.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>8880 rows × 14 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "           Lat  ...  Internações Ano Seguinte - Dengue\n",
              "0     -1.73866  ...                               52.0\n",
              "1     -4.96265  ...                               25.0\n",
              "2     -9.46606  ...                                0.0\n",
              "3     -2.03379  ...                               57.0\n",
              "4    -15.19422  ...                                0.0\n",
              "...        ...  ...                                ...\n",
              "8875  -6.84864  ...                                NaN\n",
              "8876  -3.25303  ...                                NaN\n",
              "8877  -6.76170  ...                                NaN\n",
              "8878 -14.08771  ...                                NaN\n",
              "8879   0.07317  ...                                NaN\n",
              "\n",
              "[8880 rows x 14 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "9NitdSCzuE7r",
        "outputId": "634d1d87-86ab-4e57-b4d3-2ec7dad6f1d0"
      },
      "source": [
        "cities_with_na = set(df_aglut[df_aglut['Internações - Dengue'].isna()].Municipio.unique())\n",
        "print(f'Municípios com dados de dengue faltantes: {len(cities_with_na)}')\n",
        "cities_with_na"
      ],
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Municípios com dados de dengue faltantes: 33\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'Alto Alegre',\n",
              " 'Amajari',\n",
              " 'Amapá',\n",
              " 'Boa Vista',\n",
              " 'Bonfim',\n",
              " 'Calçoene',\n",
              " 'Cantá',\n",
              " 'Caracaraí',\n",
              " 'Caroebe',\n",
              " 'Curuá',\n",
              " 'Ferreira Gomes',\n",
              " 'Guiratinga',\n",
              " 'Iracema',\n",
              " 'Itaubal',\n",
              " 'Laranjal do Jari',\n",
              " 'Macapá',\n",
              " 'Mazagão',\n",
              " 'Mucajaí',\n",
              " 'Normandia',\n",
              " 'Oiapoque',\n",
              " 'Pacaraima',\n",
              " 'Pedra Branca do Amapari',\n",
              " 'Porto Grande',\n",
              " 'Rorainópolis',\n",
              " 'Santana',\n",
              " 'Serra do Navio',\n",
              " 'São João da Baliza',\n",
              " 'São Luiz',\n",
              " 'Taipas do Tocantins',\n",
              " 'Tartarugalzinho',\n",
              " 'Uiramutã',\n",
              " 'Urucurituba',\n",
              " 'Vitória do Jari'}"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 524
        },
        "id": "oyQXy4cCuy55",
        "outputId": "84fdf5a4-2078-44fc-bd86-7f60606a1df5"
      },
      "source": [
        "df_aglut_clean = df_aglut[~df_aglut.Municipio.isin(cities_with_na)]\n",
        "df_aglut_clean"
      ],
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Lat</th>\n",
              "      <th>Long</th>\n",
              "      <th>Municipio</th>\n",
              "      <th>Estado</th>\n",
              "      <th>AreaKm2</th>\n",
              "      <th>Ano</th>\n",
              "      <th>Desmatado</th>\n",
              "      <th>Incremento</th>\n",
              "      <th>Floresta</th>\n",
              "      <th>NaoFloresta</th>\n",
              "      <th>Hidrografia</th>\n",
              "      <th>PercDesmatado</th>\n",
              "      <th>Internações - Dengue</th>\n",
              "      <th>Internações Ano Seguinte - Dengue</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>-1.73866</td>\n",
              "      <td>-48.85524</td>\n",
              "      <td>Abaetetuba</td>\n",
              "      <td>PA</td>\n",
              "      <td>1617</td>\n",
              "      <td>2008</td>\n",
              "      <td>683.3</td>\n",
              "      <td>1.2</td>\n",
              "      <td>505.6</td>\n",
              "      <td>13.6</td>\n",
              "      <td>413.6</td>\n",
              "      <td>0.422573</td>\n",
              "      <td>88.0</td>\n",
              "      <td>52.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>-4.96265</td>\n",
              "      <td>-48.39406</td>\n",
              "      <td>Abel Figueiredo</td>\n",
              "      <td>PA</td>\n",
              "      <td>616</td>\n",
              "      <td>2008</td>\n",
              "      <td>561.5</td>\n",
              "      <td>2.0</td>\n",
              "      <td>54.3</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.911526</td>\n",
              "      <td>12.0</td>\n",
              "      <td>25.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>-9.46606</td>\n",
              "      <td>-49.33901</td>\n",
              "      <td>Abreulândia</td>\n",
              "      <td>TO</td>\n",
              "      <td>1900</td>\n",
              "      <td>2008</td>\n",
              "      <td>7.4</td>\n",
              "      <td>1.9</td>\n",
              "      <td>47.9</td>\n",
              "      <td>1844.3</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.003895</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>-2.03379</td>\n",
              "      <td>-48.31904</td>\n",
              "      <td>Acará</td>\n",
              "      <td>PA</td>\n",
              "      <td>4361</td>\n",
              "      <td>2008</td>\n",
              "      <td>2263.7</td>\n",
              "      <td>47.9</td>\n",
              "      <td>1971.3</td>\n",
              "      <td>43.3</td>\n",
              "      <td>70.5</td>\n",
              "      <td>0.519078</td>\n",
              "      <td>86.0</td>\n",
              "      <td>57.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>-15.19422</td>\n",
              "      <td>-56.31716</td>\n",
              "      <td>Acorizal</td>\n",
              "      <td>MT</td>\n",
              "      <td>841</td>\n",
              "      <td>2008</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>840.8</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8875</th>\n",
              "      <td>-6.84864</td>\n",
              "      <td>-49.25551</td>\n",
              "      <td>Xinguara</td>\n",
              "      <td>PA</td>\n",
              "      <td>3793</td>\n",
              "      <td>2019</td>\n",
              "      <td>3381.1</td>\n",
              "      <td>1.2</td>\n",
              "      <td>394.3</td>\n",
              "      <td>0.0</td>\n",
              "      <td>17.6</td>\n",
              "      <td>0.891405</td>\n",
              "      <td>17.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8876</th>\n",
              "      <td>-3.25303</td>\n",
              "      <td>-45.97435</td>\n",
              "      <td>Zé Doca</td>\n",
              "      <td>MA</td>\n",
              "      <td>2438</td>\n",
              "      <td>2019</td>\n",
              "      <td>2021.6</td>\n",
              "      <td>1.1</td>\n",
              "      <td>376.2</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.829204</td>\n",
              "      <td>8.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8877</th>\n",
              "      <td>-6.76170</td>\n",
              "      <td>-50.32800</td>\n",
              "      <td>Água Azul do Norte</td>\n",
              "      <td>PA</td>\n",
              "      <td>7125</td>\n",
              "      <td>2019</td>\n",
              "      <td>4674.0</td>\n",
              "      <td>9.7</td>\n",
              "      <td>2323.1</td>\n",
              "      <td>127.9</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.656000</td>\n",
              "      <td>35.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8878</th>\n",
              "      <td>-14.08771</td>\n",
              "      <td>-52.43685</td>\n",
              "      <td>Água Boa</td>\n",
              "      <td>MT</td>\n",
              "      <td>7486</td>\n",
              "      <td>2019</td>\n",
              "      <td>40.1</td>\n",
              "      <td>0.0</td>\n",
              "      <td>123.5</td>\n",
              "      <td>7322.4</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.005357</td>\n",
              "      <td>10.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8879</th>\n",
              "      <td>0.07317</td>\n",
              "      <td>-55.75937</td>\n",
              "      <td>Óbidos</td>\n",
              "      <td>PA</td>\n",
              "      <td>28031</td>\n",
              "      <td>2019</td>\n",
              "      <td>1979.3</td>\n",
              "      <td>35.0</td>\n",
              "      <td>18174.5</td>\n",
              "      <td>4595.5</td>\n",
              "      <td>1351.5</td>\n",
              "      <td>0.070611</td>\n",
              "      <td>1.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>8484 rows × 14 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "           Lat  ...  Internações Ano Seguinte - Dengue\n",
              "0     -1.73866  ...                               52.0\n",
              "1     -4.96265  ...                               25.0\n",
              "2     -9.46606  ...                                0.0\n",
              "3     -2.03379  ...                               57.0\n",
              "4    -15.19422  ...                                0.0\n",
              "...        ...  ...                                ...\n",
              "8875  -6.84864  ...                                NaN\n",
              "8876  -3.25303  ...                                NaN\n",
              "8877  -6.76170  ...                                NaN\n",
              "8878 -14.08771  ...                                NaN\n",
              "8879   0.07317  ...                                NaN\n",
              "\n",
              "[8484 rows x 14 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 24
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 694
        },
        "id": "YacldNhIoMEZ",
        "outputId": "9a6a0056-dd1d-4cff-e934-366400fe5dcb"
      },
      "source": [
        "df_merged = pd.merge(df_useful, df_aglut_clean, how='inner', on=['Municipio', 'Estado', 'Ano'])\n",
        "df_merged"
      ],
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Municipio</th>\n",
              "      <th>Estado</th>\n",
              "      <th>Ano</th>\n",
              "      <th>População total que mora em domicílios sem acesso ao serviço de coleta de esgoto (pessoas) (SNIS)</th>\n",
              "      <th>Parcela da população total que mora em domicílios sem acesso ao serviço de coleta de esgoto (% da população) (SNIS)</th>\n",
              "      <th>Extensão da rede de distribuição de esgoto, em km (km) (SNIS)</th>\n",
              "      <th>Densidade demográfica (pessoas por km²) (Pessoas por km²) (IBGE)</th>\n",
              "      <th>Lat</th>\n",
              "      <th>Long</th>\n",
              "      <th>AreaKm2</th>\n",
              "      <th>Desmatado</th>\n",
              "      <th>Incremento</th>\n",
              "      <th>Floresta</th>\n",
              "      <th>NaoFloresta</th>\n",
              "      <th>Hidrografia</th>\n",
              "      <th>PercDesmatado</th>\n",
              "      <th>Internações - Dengue</th>\n",
              "      <th>Internações Ano Seguinte - Dengue</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ariquemes</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>90353.0</td>\n",
              "      <td>100.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>20.41</td>\n",
              "      <td>-9.97866</td>\n",
              "      <td>-63.05645</td>\n",
              "      <td>4481</td>\n",
              "      <td>3175.0</td>\n",
              "      <td>4.3</td>\n",
              "      <td>1216.2</td>\n",
              "      <td>89.6</td>\n",
              "      <td>0.2</td>\n",
              "      <td>0.708547</td>\n",
              "      <td>107.0</td>\n",
              "      <td>25.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Cacoal</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>42574.0</td>\n",
              "      <td>54.2</td>\n",
              "      <td>158.00</td>\n",
              "      <td>20.72</td>\n",
              "      <td>-11.30292</td>\n",
              "      <td>-61.25045</td>\n",
              "      <td>3821</td>\n",
              "      <td>2490.9</td>\n",
              "      <td>1.7</td>\n",
              "      <td>1295.4</td>\n",
              "      <td>26.2</td>\n",
              "      <td>5.8</td>\n",
              "      <td>0.651897</td>\n",
              "      <td>164.0</td>\n",
              "      <td>20.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Jaru</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>52005.0</td>\n",
              "      <td>100.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>17.66</td>\n",
              "      <td>-10.64842</td>\n",
              "      <td>-62.55320</td>\n",
              "      <td>2976</td>\n",
              "      <td>2616.3</td>\n",
              "      <td>0.2</td>\n",
              "      <td>314.8</td>\n",
              "      <td>44.8</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.879133</td>\n",
              "      <td>108.0</td>\n",
              "      <td>43.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Ji-Paraná</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>116610.0</td>\n",
              "      <td>100.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>16.91</td>\n",
              "      <td>-10.44844</td>\n",
              "      <td>-61.69061</td>\n",
              "      <td>6955</td>\n",
              "      <td>2601.2</td>\n",
              "      <td>0.8</td>\n",
              "      <td>4250.0</td>\n",
              "      <td>66.3</td>\n",
              "      <td>35.3</td>\n",
              "      <td>0.374004</td>\n",
              "      <td>514.0</td>\n",
              "      <td>192.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Porto Velho</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>422016.0</td>\n",
              "      <td>98.5</td>\n",
              "      <td>30.00</td>\n",
              "      <td>12.57</td>\n",
              "      <td>-8.98448</td>\n",
              "      <td>-63.99115</td>\n",
              "      <td>34631</td>\n",
              "      <td>7797.0</td>\n",
              "      <td>135.0</td>\n",
              "      <td>25574.8</td>\n",
              "      <td>431.9</td>\n",
              "      <td>751.1</td>\n",
              "      <td>0.225145</td>\n",
              "      <td>228.0</td>\n",
              "      <td>78.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1225</th>\n",
              "      <td>Santo Antônio do Leverger</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1.42</td>\n",
              "      <td>-16.48613</td>\n",
              "      <td>-55.26485</td>\n",
              "      <td>12262</td>\n",
              "      <td>1047.7</td>\n",
              "      <td>1.4</td>\n",
              "      <td>1093.1</td>\n",
              "      <td>10099.5</td>\n",
              "      <td>21.7</td>\n",
              "      <td>0.085443</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1226</th>\n",
              "      <td>Sinop</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>100677.0</td>\n",
              "      <td>70.4</td>\n",
              "      <td>225.61</td>\n",
              "      <td>36.27</td>\n",
              "      <td>-11.74201</td>\n",
              "      <td>-55.40265</td>\n",
              "      <td>3935</td>\n",
              "      <td>2609.9</td>\n",
              "      <td>72.2</td>\n",
              "      <td>1170.4</td>\n",
              "      <td>136.3</td>\n",
              "      <td>18.4</td>\n",
              "      <td>0.663253</td>\n",
              "      <td>6.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1227</th>\n",
              "      <td>Sorriso</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>60306.0</td>\n",
              "      <td>66.8</td>\n",
              "      <td>311.28</td>\n",
              "      <td>9.66</td>\n",
              "      <td>-12.69331</td>\n",
              "      <td>-55.77741</td>\n",
              "      <td>9349</td>\n",
              "      <td>3977.4</td>\n",
              "      <td>0.2</td>\n",
              "      <td>1252.5</td>\n",
              "      <td>4119.1</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.425436</td>\n",
              "      <td>2.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1228</th>\n",
              "      <td>Tangará da Serra</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>74224.0</td>\n",
              "      <td>71.5</td>\n",
              "      <td>138.34</td>\n",
              "      <td>8.94</td>\n",
              "      <td>-14.43270</td>\n",
              "      <td>-58.38021</td>\n",
              "      <td>11333</td>\n",
              "      <td>2184.1</td>\n",
              "      <td>3.7</td>\n",
              "      <td>635.0</td>\n",
              "      <td>8512.8</td>\n",
              "      <td>0.9</td>\n",
              "      <td>0.192720</td>\n",
              "      <td>17.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1229</th>\n",
              "      <td>Várzea Grande</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>199059.0</td>\n",
              "      <td>69.9</td>\n",
              "      <td>187.67</td>\n",
              "      <td>271.86</td>\n",
              "      <td>-15.55785</td>\n",
              "      <td>-56.34462</td>\n",
              "      <td>939</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>1.3</td>\n",
              "      <td>937.7</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>6.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1230 rows × 18 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                      Municipio  ... Internações Ano Seguinte - Dengue\n",
              "0                     Ariquemes  ...                              25.0\n",
              "1                        Cacoal  ...                              20.0\n",
              "2                          Jaru  ...                              43.0\n",
              "3                     Ji-Paraná  ...                             192.0\n",
              "4                   Porto Velho  ...                              78.0\n",
              "...                         ...  ...                               ...\n",
              "1225  Santo Antônio do Leverger  ...                               NaN\n",
              "1226                      Sinop  ...                               NaN\n",
              "1227                    Sorriso  ...                               NaN\n",
              "1228           Tangará da Serra  ...                               NaN\n",
              "1229              Várzea Grande  ...                               NaN\n",
              "\n",
              "[1230 rows x 18 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ZBCXrd7ki0vi",
        "outputId": "aeace3ed-2758-4111-a0aa-3ddb1dc48874"
      },
      "source": [
        "# remove todo mundo que já foi 100% desmatado em 2008 e não tem mais área pra \n",
        "# desmatar antes do último ano da análise\n",
        "cities_filtered = set(df_merged.loc[\n",
        "                      (df_merged.PercDesmatado < 0.6) &\n",
        "                      (df_merged.Ano == 2010),:].Municipio.unique())\n",
        "cities_filtered = set(df_merged.loc[\n",
        "                      (df_merged.PercDesmatado < 1) &\n",
        "                      (df_merged.Ano == 2018) &\n",
        "                      (df_merged.Municipio.isin(cities_filtered)),:].Municipio.unique())\n",
        "\n",
        "print(f'Municípios antes do filtro: {len(df_merged.Municipio.unique())}')\n",
        "print(f'Municípios após o filtro: {len(cities_filtered)}')\n",
        "# cities_filtered"
      ],
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Municípios antes do filtro: 119\n",
            "Municípios após o filtro: 89\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 728
        },
        "id": "KSJiDJ85kpxR",
        "outputId": "77fd6ab5-5335-46ef-a5ee-7bbfa19f7b81"
      },
      "source": [
        "df_merged_filtered = df_merged[df_merged.Municipio.isin(cities_filtered)]\n",
        "df_merged_filtered"
      ],
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Municipio</th>\n",
              "      <th>Estado</th>\n",
              "      <th>Ano</th>\n",
              "      <th>População total que mora em domicílios sem acesso ao serviço de coleta de esgoto (pessoas) (SNIS)</th>\n",
              "      <th>Parcela da população total que mora em domicílios sem acesso ao serviço de coleta de esgoto (% da população) (SNIS)</th>\n",
              "      <th>Extensão da rede de distribuição de esgoto, em km (km) (SNIS)</th>\n",
              "      <th>Densidade demográfica (pessoas por km²) (Pessoas por km²) (IBGE)</th>\n",
              "      <th>Lat</th>\n",
              "      <th>Long</th>\n",
              "      <th>AreaKm2</th>\n",
              "      <th>Desmatado</th>\n",
              "      <th>Incremento</th>\n",
              "      <th>Floresta</th>\n",
              "      <th>NaoFloresta</th>\n",
              "      <th>Hidrografia</th>\n",
              "      <th>PercDesmatado</th>\n",
              "      <th>Internações - Dengue</th>\n",
              "      <th>Internações Ano Seguinte - Dengue</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Ji-Paraná</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>116610.0</td>\n",
              "      <td>100.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>16.91</td>\n",
              "      <td>-10.44844</td>\n",
              "      <td>-61.69061</td>\n",
              "      <td>6955</td>\n",
              "      <td>2601.2</td>\n",
              "      <td>0.8</td>\n",
              "      <td>4250.0</td>\n",
              "      <td>66.3</td>\n",
              "      <td>35.3</td>\n",
              "      <td>0.374004</td>\n",
              "      <td>514.0</td>\n",
              "      <td>192.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Porto Velho</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>422016.0</td>\n",
              "      <td>98.5</td>\n",
              "      <td>30.00</td>\n",
              "      <td>12.57</td>\n",
              "      <td>-8.98448</td>\n",
              "      <td>-63.99115</td>\n",
              "      <td>34631</td>\n",
              "      <td>7797.0</td>\n",
              "      <td>135.0</td>\n",
              "      <td>25574.8</td>\n",
              "      <td>431.9</td>\n",
              "      <td>751.1</td>\n",
              "      <td>0.225145</td>\n",
              "      <td>228.0</td>\n",
              "      <td>78.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>Vilhena</td>\n",
              "      <td>RO</td>\n",
              "      <td>2010</td>\n",
              "      <td>76202.0</td>\n",
              "      <td>100.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>6.51</td>\n",
              "      <td>-12.12351</td>\n",
              "      <td>-60.11211</td>\n",
              "      <td>11584</td>\n",
              "      <td>1730.2</td>\n",
              "      <td>8.8</td>\n",
              "      <td>8162.7</td>\n",
              "      <td>1685.8</td>\n",
              "      <td>1.5</td>\n",
              "      <td>0.149361</td>\n",
              "      <td>112.0</td>\n",
              "      <td>46.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>Cruzeiro do Sul</td>\n",
              "      <td>AC</td>\n",
              "      <td>2010</td>\n",
              "      <td>78507.0</td>\n",
              "      <td>100.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>8.94</td>\n",
              "      <td>-7.95559</td>\n",
              "      <td>-72.73617</td>\n",
              "      <td>9202</td>\n",
              "      <td>982.2</td>\n",
              "      <td>23.0</td>\n",
              "      <td>7973.3</td>\n",
              "      <td>20.7</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.106738</td>\n",
              "      <td>10.0</td>\n",
              "      <td>13.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>Rio Branco</td>\n",
              "      <td>AC</td>\n",
              "      <td>2010</td>\n",
              "      <td>268038.0</td>\n",
              "      <td>79.8</td>\n",
              "      <td>231.00</td>\n",
              "      <td>38.04</td>\n",
              "      <td>-9.99527</td>\n",
              "      <td>-68.15925</td>\n",
              "      <td>9113</td>\n",
              "      <td>2522.7</td>\n",
              "      <td>21.0</td>\n",
              "      <td>6584.8</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.1</td>\n",
              "      <td>0.276824</td>\n",
              "      <td>367.0</td>\n",
              "      <td>193.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1224</th>\n",
              "      <td>Rosário Oeste</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2.31</td>\n",
              "      <td>-14.86180</td>\n",
              "      <td>-56.25158</td>\n",
              "      <td>7493</td>\n",
              "      <td>66.0</td>\n",
              "      <td>0.2</td>\n",
              "      <td>23.0</td>\n",
              "      <td>7395.0</td>\n",
              "      <td>9.0</td>\n",
              "      <td>0.008808</td>\n",
              "      <td>19.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1225</th>\n",
              "      <td>Santo Antônio do Leverger</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1.42</td>\n",
              "      <td>-16.48613</td>\n",
              "      <td>-55.26485</td>\n",
              "      <td>12262</td>\n",
              "      <td>1047.7</td>\n",
              "      <td>1.4</td>\n",
              "      <td>1093.1</td>\n",
              "      <td>10099.5</td>\n",
              "      <td>21.7</td>\n",
              "      <td>0.085443</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1227</th>\n",
              "      <td>Sorriso</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>60306.0</td>\n",
              "      <td>66.8</td>\n",
              "      <td>311.28</td>\n",
              "      <td>9.66</td>\n",
              "      <td>-12.69331</td>\n",
              "      <td>-55.77741</td>\n",
              "      <td>9349</td>\n",
              "      <td>3977.4</td>\n",
              "      <td>0.2</td>\n",
              "      <td>1252.5</td>\n",
              "      <td>4119.1</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.425436</td>\n",
              "      <td>2.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1228</th>\n",
              "      <td>Tangará da Serra</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>74224.0</td>\n",
              "      <td>71.5</td>\n",
              "      <td>138.34</td>\n",
              "      <td>8.94</td>\n",
              "      <td>-14.43270</td>\n",
              "      <td>-58.38021</td>\n",
              "      <td>11333</td>\n",
              "      <td>2184.1</td>\n",
              "      <td>3.7</td>\n",
              "      <td>635.0</td>\n",
              "      <td>8512.8</td>\n",
              "      <td>0.9</td>\n",
              "      <td>0.192720</td>\n",
              "      <td>17.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1229</th>\n",
              "      <td>Várzea Grande</td>\n",
              "      <td>MT</td>\n",
              "      <td>2019</td>\n",
              "      <td>199059.0</td>\n",
              "      <td>69.9</td>\n",
              "      <td>187.67</td>\n",
              "      <td>271.86</td>\n",
              "      <td>-15.55785</td>\n",
              "      <td>-56.34462</td>\n",
              "      <td>939</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>1.3</td>\n",
              "      <td>937.7</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>6.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>920 rows × 18 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                      Municipio  ... Internações Ano Seguinte - Dengue\n",
              "3                     Ji-Paraná  ...                             192.0\n",
              "4                   Porto Velho  ...                              78.0\n",
              "6                       Vilhena  ...                              46.0\n",
              "7               Cruzeiro do Sul  ...                              13.0\n",
              "8                    Rio Branco  ...                             193.0\n",
              "...                         ...  ...                               ...\n",
              "1224              Rosário Oeste  ...                               NaN\n",
              "1225  Santo Antônio do Leverger  ...                               NaN\n",
              "1227                    Sorriso  ...                               NaN\n",
              "1228           Tangará da Serra  ...                               NaN\n",
              "1229              Várzea Grande  ...                               NaN\n",
              "\n",
              "[920 rows x 18 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 27
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Pbr2URjFuKn_"
      },
      "source": [
        "Salvandos os dados por município"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NHi0xiv9rM4K"
      },
      "source": [
        "df_merged.to_csv('desmat_dengue_san.csv', index=False)\n",
        "df_merged_filtered.to_csv('desmat_dengue_san_filtrado.csv', index=False)"
      ],
      "execution_count": 28,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "seCuRWcVuWiI"
      },
      "source": [
        "Gerando os dados agrupados por estado"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 643
        },
        "id": "T7lVAgKyceSF",
        "outputId": "95a0e12f-e963-41d2-dedc-7453adf22873"
      },
      "source": [
        "df_merged_states = df_merged.groupby(['Estado', 'Ano']).sum().reset_index()\n",
        "columns_to_remove = {'Municipio', 'Lat', 'Long',\n",
        "                     'Parcela da população total que mora em domicílios sem acesso ao serviço de coleta de esgoto (% da população) (SNIS)'}\n",
        "columns = [c for c in df_merged_states.columns if c not in columns_to_remove]\n",
        "print(f'Deixando somente as colunas:\\n{columns}\\n')\n",
        "df_merged_states = df_merged_states[columns]\n",
        "df_merged_states"
      ],
      "execution_count": 29,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Deixando somente as colunas:\n",
            "['Estado', 'Ano', 'População total que mora em domicílios sem acesso ao serviço de coleta de esgoto (pessoas) (SNIS)', 'Extensão da rede de distribuição de esgoto, em km (km) (SNIS)', 'Densidade demográfica (pessoas por km²) (Pessoas por km²) (IBGE)', 'AreaKm2', 'Desmatado', 'Incremento', 'Floresta', 'NaoFloresta', 'Hidrografia', 'PercDesmatado', 'Internações - Dengue', 'Internações Ano Seguinte - Dengue']\n",
            "\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "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>Estado</th>\n",
              "      <th>Ano</th>\n",
              "      <th>População total que mora em domicílios sem acesso ao serviço de coleta de esgoto (pessoas) (SNIS)</th>\n",
              "      <th>Extensão da rede de distribuição de esgoto, em km (km) (SNIS)</th>\n",
              "      <th>Densidade demográfica (pessoas por km²) (Pessoas por km²) (IBGE)</th>\n",
              "      <th>AreaKm2</th>\n",
              "      <th>Desmatado</th>\n",
              "      <th>Incremento</th>\n",
              "      <th>Floresta</th>\n",
              "      <th>NaoFloresta</th>\n",
              "      <th>Hidrografia</th>\n",
              "      <th>PercDesmatado</th>\n",
              "      <th>Internações - Dengue</th>\n",
              "      <th>Internações Ano Seguinte - Dengue</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>AC</td>\n",
              "      <td>2010</td>\n",
              "      <td>346545.0</td>\n",
              "      <td>231.00</td>\n",
              "      <td>46.98</td>\n",
              "      <td>18315</td>\n",
              "      <td>3504.9</td>\n",
              "      <td>44.0</td>\n",
              "      <td>14558.1</td>\n",
              "      <td>20.7</td>\n",
              "      <td>0.1</td>\n",
              "      <td>0.383562</td>\n",
              "      <td>377.0</td>\n",
              "      <td>206.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>AC</td>\n",
              "      <td>2011</td>\n",
              "      <td>353473.0</td>\n",
              "      <td>231.00</td>\n",
              "      <td>47.76</td>\n",
              "      <td>18315</td>\n",
              "      <td>3561.5</td>\n",
              "      <td>56.6</td>\n",
              "      <td>14376.1</td>\n",
              "      <td>20.7</td>\n",
              "      <td>0.1</td>\n",
              "      <td>0.389759</td>\n",
              "      <td>206.0</td>\n",
              "      <td>53.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>AC</td>\n",
              "      <td>2012</td>\n",
              "      <td>345283.0</td>\n",
              "      <td>324.88</td>\n",
              "      <td>48.52</td>\n",
              "      <td>18315</td>\n",
              "      <td>3600.3</td>\n",
              "      <td>38.8</td>\n",
              "      <td>14659.5</td>\n",
              "      <td>20.7</td>\n",
              "      <td>0.1</td>\n",
              "      <td>0.394006</td>\n",
              "      <td>53.0</td>\n",
              "      <td>41.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>AC</td>\n",
              "      <td>2013</td>\n",
              "      <td>365525.0</td>\n",
              "      <td>438.97</td>\n",
              "      <td>49.59</td>\n",
              "      <td>18315</td>\n",
              "      <td>3625.8</td>\n",
              "      <td>25.5</td>\n",
              "      <td>14106.2</td>\n",
              "      <td>20.7</td>\n",
              "      <td>0.1</td>\n",
              "      <td>0.396799</td>\n",
              "      <td>41.0</td>\n",
              "      <td>560.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>AC</td>\n",
              "      <td>2014</td>\n",
              "      <td>367610.0</td>\n",
              "      <td>467.27</td>\n",
              "      <td>50.41</td>\n",
              "      <td>18315</td>\n",
              "      <td>3671.9</td>\n",
              "      <td>46.1</td>\n",
              "      <td>14343.6</td>\n",
              "      <td>20.7</td>\n",
              "      <td>0.1</td>\n",
              "      <td>0.401846</td>\n",
              "      <td>560.0</td>\n",
              "      <td>268.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>65</th>\n",
              "      <td>TO</td>\n",
              "      <td>2015</td>\n",
              "      <td>663975.0</td>\n",
              "      <td>1934.73</td>\n",
              "      <td>6916.78</td>\n",
              "      <td>17956</td>\n",
              "      <td>2805.2</td>\n",
              "      <td>2.6</td>\n",
              "      <td>439.2</td>\n",
              "      <td>14603.6</td>\n",
              "      <td>111.0</td>\n",
              "      <td>0.982895</td>\n",
              "      <td>91.0</td>\n",
              "      <td>72.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>66</th>\n",
              "      <td>TO</td>\n",
              "      <td>2016</td>\n",
              "      <td>656048.0</td>\n",
              "      <td>2233.07</td>\n",
              "      <td>6957.17</td>\n",
              "      <td>17956</td>\n",
              "      <td>2807.3</td>\n",
              "      <td>2.1</td>\n",
              "      <td>437.1</td>\n",
              "      <td>14603.6</td>\n",
              "      <td>111.0</td>\n",
              "      <td>0.984190</td>\n",
              "      <td>72.0</td>\n",
              "      <td>85.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>67</th>\n",
              "      <td>TO</td>\n",
              "      <td>2017</td>\n",
              "      <td>597752.0</td>\n",
              "      <td>2368.21</td>\n",
              "      <td>6995.43</td>\n",
              "      <td>17956</td>\n",
              "      <td>2808.2</td>\n",
              "      <td>0.9</td>\n",
              "      <td>436.2</td>\n",
              "      <td>14603.6</td>\n",
              "      <td>111.0</td>\n",
              "      <td>0.984414</td>\n",
              "      <td>85.0</td>\n",
              "      <td>79.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>68</th>\n",
              "      <td>TO</td>\n",
              "      <td>2018</td>\n",
              "      <td>582076.0</td>\n",
              "      <td>2466.85</td>\n",
              "      <td>7093.43</td>\n",
              "      <td>17956</td>\n",
              "      <td>2808.4</td>\n",
              "      <td>0.2</td>\n",
              "      <td>436.0</td>\n",
              "      <td>14603.6</td>\n",
              "      <td>111.0</td>\n",
              "      <td>0.984464</td>\n",
              "      <td>79.0</td>\n",
              "      <td>330.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>69</th>\n",
              "      <td>TO</td>\n",
              "      <td>2019</td>\n",
              "      <td>572099.0</td>\n",
              "      <td>2825.67</td>\n",
              "      <td>7148.47</td>\n",
              "      <td>17956</td>\n",
              "      <td>2810.6</td>\n",
              "      <td>2.2</td>\n",
              "      <td>433.8</td>\n",
              "      <td>14603.6</td>\n",
              "      <td>111.0</td>\n",
              "      <td>0.985269</td>\n",
              "      <td>330.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>70 rows × 14 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "   Estado   Ano  ...  Internações - Dengue  Internações Ano Seguinte - Dengue\n",
              "0      AC  2010  ...                 377.0                              206.0\n",
              "1      AC  2011  ...                 206.0                               53.0\n",
              "2      AC  2012  ...                  53.0                               41.0\n",
              "3      AC  2013  ...                  41.0                              560.0\n",
              "4      AC  2014  ...                 560.0                              268.0\n",
              "..    ...   ...  ...                   ...                                ...\n",
              "65     TO  2015  ...                  91.0                               72.0\n",
              "66     TO  2016  ...                  72.0                               85.0\n",
              "67     TO  2017  ...                  85.0                               79.0\n",
              "68     TO  2018  ...                  79.0                              330.0\n",
              "69     TO  2019  ...                 330.0                                0.0\n",
              "\n",
              "[70 rows x 14 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 29
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 643
        },
        "id": "JbAbton-3XU5",
        "outputId": "bb46af1c-768f-4c77-c588-e7ea80207fb6"
      },
      "source": [
        "df_merged_states_filtered = df_merged_filtered.groupby(['Estado', 'Ano']).sum().reset_index()\n",
        "columns_to_remove = {'Municipio', 'Lat', 'Long',\n",
        "                     'Parcela da população total que mora em domicílios sem acesso ao serviço de coleta de esgoto (% da população) (SNIS)'}\n",
        "columns = [c for c in df_merged_states_filtered.columns if c not in columns_to_remove]\n",
        "print(f'Deixando somente as colunas:\\n{columns}\\n')\n",
        "df_merged_states_filtered = df_merged_states_filtered[columns]\n",
        "df_merged_states_filtered"
      ],
      "execution_count": 30,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Deixando somente as colunas:\n",
            "['Estado', 'Ano', 'População total que mora em domicílios sem acesso ao serviço de coleta de esgoto (pessoas) (SNIS)', 'Extensão da rede de distribuição de esgoto, em km (km) (SNIS)', 'Densidade demográfica (pessoas por km²) (Pessoas por km²) (IBGE)', 'AreaKm2', 'Desmatado', 'Incremento', 'Floresta', 'NaoFloresta', 'Hidrografia', 'PercDesmatado', 'Internações - Dengue', 'Internações Ano Seguinte - Dengue']\n",
            "\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "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>Estado</th>\n",
              "      <th>Ano</th>\n",
              "      <th>População total que mora em domicílios sem acesso ao serviço de coleta de esgoto (pessoas) (SNIS)</th>\n",
              "      <th>Extensão da rede de distribuição de esgoto, em km (km) (SNIS)</th>\n",
              "      <th>Densidade demográfica (pessoas por km²) (Pessoas por km²) (IBGE)</th>\n",
              "      <th>AreaKm2</th>\n",
              "      <th>Desmatado</th>\n",
              "      <th>Incremento</th>\n",
              "      <th>Floresta</th>\n",
              "      <th>NaoFloresta</th>\n",
              "      <th>Hidrografia</th>\n",
              "      <th>PercDesmatado</th>\n",
              "      <th>Internações - Dengue</th>\n",
              "      <th>Internações Ano Seguinte - Dengue</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>AC</td>\n",
              "      <td>2010</td>\n",
              "      <td>346545.0</td>\n",
              "      <td>231.00</td>\n",
              "      <td>46.98</td>\n",
              "      <td>18315</td>\n",
              "      <td>3504.9</td>\n",
              "      <td>44.0</td>\n",
              "      <td>14558.1</td>\n",
              "      <td>20.7</td>\n",
              "      <td>0.1</td>\n",
              "      <td>0.383562</td>\n",
              "      <td>377.0</td>\n",
              "      <td>206.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>AC</td>\n",
              "      <td>2011</td>\n",
              "      <td>353473.0</td>\n",
              "      <td>231.00</td>\n",
              "      <td>47.76</td>\n",
              "      <td>18315</td>\n",
              "      <td>3561.5</td>\n",
              "      <td>56.6</td>\n",
              "      <td>14376.1</td>\n",
              "      <td>20.7</td>\n",
              "      <td>0.1</td>\n",
              "      <td>0.389759</td>\n",
              "      <td>206.0</td>\n",
              "      <td>53.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>AC</td>\n",
              "      <td>2012</td>\n",
              "      <td>345283.0</td>\n",
              "      <td>324.88</td>\n",
              "      <td>48.52</td>\n",
              "      <td>18315</td>\n",
              "      <td>3600.3</td>\n",
              "      <td>38.8</td>\n",
              "      <td>14659.5</td>\n",
              "      <td>20.7</td>\n",
              "      <td>0.1</td>\n",
              "      <td>0.394006</td>\n",
              "      <td>53.0</td>\n",
              "      <td>41.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>AC</td>\n",
              "      <td>2013</td>\n",
              "      <td>365525.0</td>\n",
              "      <td>438.97</td>\n",
              "      <td>49.59</td>\n",
              "      <td>18315</td>\n",
              "      <td>3625.8</td>\n",
              "      <td>25.5</td>\n",
              "      <td>14106.2</td>\n",
              "      <td>20.7</td>\n",
              "      <td>0.1</td>\n",
              "      <td>0.396799</td>\n",
              "      <td>41.0</td>\n",
              "      <td>560.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>AC</td>\n",
              "      <td>2014</td>\n",
              "      <td>367610.0</td>\n",
              "      <td>467.27</td>\n",
              "      <td>50.41</td>\n",
              "      <td>18315</td>\n",
              "      <td>3671.9</td>\n",
              "      <td>46.1</td>\n",
              "      <td>14343.6</td>\n",
              "      <td>20.7</td>\n",
              "      <td>0.1</td>\n",
              "      <td>0.401846</td>\n",
              "      <td>560.0</td>\n",
              "      <td>268.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>65</th>\n",
              "      <td>TO</td>\n",
              "      <td>2015</td>\n",
              "      <td>523611.0</td>\n",
              "      <td>1751.66</td>\n",
              "      <td>6874.24</td>\n",
              "      <td>13937</td>\n",
              "      <td>110.6</td>\n",
              "      <td>0.5</td>\n",
              "      <td>36.3</td>\n",
              "      <td>13706.0</td>\n",
              "      <td>87.1</td>\n",
              "      <td>0.312429</td>\n",
              "      <td>79.0</td>\n",
              "      <td>56.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>66</th>\n",
              "      <td>TO</td>\n",
              "      <td>2016</td>\n",
              "      <td>513125.0</td>\n",
              "      <td>1997.07</td>\n",
              "      <td>6913.90</td>\n",
              "      <td>13937</td>\n",
              "      <td>110.9</td>\n",
              "      <td>0.3</td>\n",
              "      <td>36.0</td>\n",
              "      <td>13706.0</td>\n",
              "      <td>87.1</td>\n",
              "      <td>0.313277</td>\n",
              "      <td>56.0</td>\n",
              "      <td>50.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>67</th>\n",
              "      <td>TO</td>\n",
              "      <td>2017</td>\n",
              "      <td>466153.0</td>\n",
              "      <td>2060.21</td>\n",
              "      <td>6951.44</td>\n",
              "      <td>13937</td>\n",
              "      <td>110.9</td>\n",
              "      <td>0.0</td>\n",
              "      <td>36.0</td>\n",
              "      <td>13706.0</td>\n",
              "      <td>87.1</td>\n",
              "      <td>0.313277</td>\n",
              "      <td>50.0</td>\n",
              "      <td>73.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>68</th>\n",
              "      <td>TO</td>\n",
              "      <td>2018</td>\n",
              "      <td>457203.0</td>\n",
              "      <td>2136.85</td>\n",
              "      <td>7049.06</td>\n",
              "      <td>13937</td>\n",
              "      <td>110.9</td>\n",
              "      <td>0.0</td>\n",
              "      <td>36.0</td>\n",
              "      <td>13706.0</td>\n",
              "      <td>87.1</td>\n",
              "      <td>0.313277</td>\n",
              "      <td>73.0</td>\n",
              "      <td>249.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>69</th>\n",
              "      <td>TO</td>\n",
              "      <td>2019</td>\n",
              "      <td>447286.0</td>\n",
              "      <td>2472.26</td>\n",
              "      <td>7103.36</td>\n",
              "      <td>13937</td>\n",
              "      <td>111.0</td>\n",
              "      <td>0.1</td>\n",
              "      <td>35.9</td>\n",
              "      <td>13706.0</td>\n",
              "      <td>87.1</td>\n",
              "      <td>0.313559</td>\n",
              "      <td>249.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>70 rows × 14 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "   Estado   Ano  ...  Internações - Dengue  Internações Ano Seguinte - Dengue\n",
              "0      AC  2010  ...                 377.0                              206.0\n",
              "1      AC  2011  ...                 206.0                               53.0\n",
              "2      AC  2012  ...                  53.0                               41.0\n",
              "3      AC  2013  ...                  41.0                              560.0\n",
              "4      AC  2014  ...                 560.0                              268.0\n",
              "..    ...   ...  ...                   ...                                ...\n",
              "65     TO  2015  ...                  79.0                               56.0\n",
              "66     TO  2016  ...                  56.0                               50.0\n",
              "67     TO  2017  ...                  50.0                               73.0\n",
              "68     TO  2018  ...                  73.0                              249.0\n",
              "69     TO  2019  ...                 249.0                                0.0\n",
              "\n",
              "[70 rows x 14 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 30
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "G8owYOBdvmzx"
      },
      "source": [
        "Salvando os dados agrupados por estado"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qzQT5SnM4p2n"
      },
      "source": [
        "df_merged_states.to_csv('desmat_dengue_san_estado.csv', index=False)\n",
        "df_merged_states_filtered.to_csv('desmat_dengue_san_estado_filtrado.csv', index=False)"
      ],
      "execution_count": 31,
      "outputs": []
    }
  ]
}