analisa_dados_vacinacao_COVID19_BR.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "analisa_dados_vacinacao_COVID19_BR.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true,
      "authorship_tag": "ABX9TyMw+BDR8hsZkq24bg3xBVDP",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    },
    "widgets": {
      "application/vnd.jupyter.widget-state+json": {
        "b4dfffaaecbc4d43b31aec3f7e8b403c": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "HBoxModel",
          "state": {
            "_view_name": "HBoxView",
            "_dom_classes": [],
            "_model_name": "HBoxModel",
            "_view_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_view_count": null,
            "_view_module_version": "1.5.0",
            "box_style": "",
            "layout": "IPY_MODEL_2da308b71a694333934a58003b0a0a94",
            "_model_module": "@jupyter-widgets/controls",
            "children": [
              "IPY_MODEL_1d72e35c49334f7eb79988e3fa185efc",
              "IPY_MODEL_e446d12c1f2446bb9846fcc5d76464fd"
            ]
          }
        },
        "2da308b71a694333934a58003b0a0a94": {
          "model_module": "@jupyter-widgets/base",
          "model_name": "LayoutModel",
          "state": {
            "_view_name": "LayoutView",
            "grid_template_rows": null,
            "right": null,
            "justify_content": null,
            "_view_module": "@jupyter-widgets/base",
            "overflow": null,
            "_model_module_version": "1.2.0",
            "_view_count": null,
            "flex_flow": null,
            "width": null,
            "min_width": null,
            "border": null,
            "align_items": null,
            "bottom": null,
            "_model_module": "@jupyter-widgets/base",
            "top": null,
            "grid_column": null,
            "overflow_y": null,
            "overflow_x": null,
            "grid_auto_flow": null,
            "grid_area": null,
            "grid_template_columns": null,
            "flex": null,
            "_model_name": "LayoutModel",
            "justify_items": null,
            "grid_row": null,
            "max_height": null,
            "align_content": null,
            "visibility": null,
            "align_self": null,
            "height": null,
            "min_height": null,
            "padding": null,
            "grid_auto_rows": null,
            "grid_gap": null,
            "max_width": null,
            "order": null,
            "_view_module_version": "1.2.0",
            "grid_template_areas": null,
            "object_position": null,
            "object_fit": null,
            "grid_auto_columns": null,
            "margin": null,
            "display": null,
            "left": null
          }
        },
        "1d72e35c49334f7eb79988e3fa185efc": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "FloatProgressModel",
          "state": {
            "_view_name": "ProgressView",
            "style": "IPY_MODEL_46d41e1e5bfd4b16ba2cd4ea2df4c4d2",
            "_dom_classes": [],
            "description": "100%",
            "_model_name": "FloatProgressModel",
            "bar_style": "success",
            "max": 30,
            "_view_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "value": 30,
            "_view_count": null,
            "_view_module_version": "1.5.0",
            "orientation": "horizontal",
            "min": 0,
            "description_tooltip": null,
            "_model_module": "@jupyter-widgets/controls",
            "layout": "IPY_MODEL_213b585ccaec403d9a7a7b9b68fddf62"
          }
        },
        "e446d12c1f2446bb9846fcc5d76464fd": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "HTMLModel",
          "state": {
            "_view_name": "HTMLView",
            "style": "IPY_MODEL_878d8fd7c85f42c8b2c3994b5fc6e07d",
            "_dom_classes": [],
            "description": "",
            "_model_name": "HTMLModel",
            "placeholder": "​",
            "_view_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "value": " 30/30 [11:57&lt;00:00, 23.92s/it]",
            "_view_count": null,
            "_view_module_version": "1.5.0",
            "description_tooltip": null,
            "_model_module": "@jupyter-widgets/controls",
            "layout": "IPY_MODEL_0b421086ecec49f390a9e475db19d619"
          }
        },
        "46d41e1e5bfd4b16ba2cd4ea2df4c4d2": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "ProgressStyleModel",
          "state": {
            "_view_name": "StyleView",
            "_model_name": "ProgressStyleModel",
            "description_width": "initial",
            "_view_module": "@jupyter-widgets/base",
            "_model_module_version": "1.5.0",
            "_view_count": null,
            "_view_module_version": "1.2.0",
            "bar_color": null,
            "_model_module": "@jupyter-widgets/controls"
          }
        },
        "213b585ccaec403d9a7a7b9b68fddf62": {
          "model_module": "@jupyter-widgets/base",
          "model_name": "LayoutModel",
          "state": {
            "_view_name": "LayoutView",
            "grid_template_rows": null,
            "right": null,
            "justify_content": null,
            "_view_module": "@jupyter-widgets/base",
            "overflow": null,
            "_model_module_version": "1.2.0",
            "_view_count": null,
            "flex_flow": null,
            "width": null,
            "min_width": null,
            "border": null,
            "align_items": null,
            "bottom": null,
            "_model_module": "@jupyter-widgets/base",
            "top": null,
            "grid_column": null,
            "overflow_y": null,
            "overflow_x": null,
            "grid_auto_flow": null,
            "grid_area": null,
            "grid_template_columns": null,
            "flex": null,
            "_model_name": "LayoutModel",
            "justify_items": null,
            "grid_row": null,
            "max_height": null,
            "align_content": null,
            "visibility": null,
            "align_self": null,
            "height": null,
            "min_height": null,
            "padding": null,
            "grid_auto_rows": null,
            "grid_gap": null,
            "max_width": null,
            "order": null,
            "_view_module_version": "1.2.0",
            "grid_template_areas": null,
            "object_position": null,
            "object_fit": null,
            "grid_auto_columns": null,
            "margin": null,
            "display": null,
            "left": null
          }
        },
        "878d8fd7c85f42c8b2c3994b5fc6e07d": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "DescriptionStyleModel",
          "state": {
            "_view_name": "StyleView",
            "_model_name": "DescriptionStyleModel",
            "description_width": "",
            "_view_module": "@jupyter-widgets/base",
            "_model_module_version": "1.5.0",
            "_view_count": null,
            "_view_module_version": "1.2.0",
            "_model_module": "@jupyter-widgets/controls"
          }
        },
        "0b421086ecec49f390a9e475db19d619": {
          "model_module": "@jupyter-widgets/base",
          "model_name": "LayoutModel",
          "state": {
            "_view_name": "LayoutView",
            "grid_template_rows": null,
            "right": null,
            "justify_content": null,
            "_view_module": "@jupyter-widgets/base",
            "overflow": null,
            "_model_module_version": "1.2.0",
            "_view_count": null,
            "flex_flow": null,
            "width": null,
            "min_width": null,
            "border": null,
            "align_items": null,
            "bottom": null,
            "_model_module": "@jupyter-widgets/base",
            "top": null,
            "grid_column": null,
            "overflow_y": null,
            "overflow_x": null,
            "grid_auto_flow": null,
            "grid_area": null,
            "grid_template_columns": null,
            "flex": null,
            "_model_name": "LayoutModel",
            "justify_items": null,
            "grid_row": null,
            "max_height": null,
            "align_content": null,
            "visibility": null,
            "align_self": null,
            "height": null,
            "min_height": null,
            "padding": null,
            "grid_auto_rows": null,
            "grid_gap": null,
            "max_width": null,
            "order": null,
            "_view_module_version": "1.2.0",
            "grid_template_areas": null,
            "object_position": null,
            "object_fit": null,
            "grid_auto_columns": null,
            "margin": null,
            "display": null,
            "left": null
          }
        }
      }
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/ffmenezes/panorama_vacinacao_covid19/blob/main/analisa_dados_vacinacao_COVID19_BR.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Yqp1NSgB1bWl"
      },
      "source": [
        "# **INTRO**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uXEztiTw4adW"
      },
      "source": [
        "Notebook apresentando a forma de extração dos dados para construção do painel **[Vacina COVID-19: panorama atual](http://antiamador.org/bi/vacinaBR)**. Ao final, são feitas análises mais detalhadas, buscando dirimir algumas dúvidas acerca dos dados."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "42ElvjtL1Zo3"
      },
      "source": [
        "# **PRE**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Pkse2Ylm4XGP"
      },
      "source": [
        "## importação das bibliotecas a serem utilizadas"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OZj99tMx4WSS"
      },
      "source": [
        "import os\n",
        "import tqdm"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LIdeVaLFX4EX",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "781ee678-8cde-4d7b-8090-c67847852032"
      },
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Mounted at /content/drive\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PxVloPn_Btih"
      },
      "source": [
        "import numpy as np\n",
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "%matplotlib inline"
      ],
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BoZtHLa5BsEz"
      },
      "source": [
        "import re\n",
        "from unicodedata import normalize\n",
        "import urllib\n",
        "from bs4 import BeautifulSoup"
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6YhbyFBICGhN"
      },
      "source": [
        "# **PATH**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9RA0Kt4nCOXQ"
      },
      "source": [
        "## configuro como diretório padrão uma pasta no meu Drive\n",
        "os.chdir('/content/drive/Shareddrives/gdrive/datasets/minsaude/vacinacao_covid/')"
      ],
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8qu2MSGjB4o8"
      },
      "source": [
        "# **FUNCTIONS**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FEjN586HB4b3"
      },
      "source": [
        "## função para mostrar barra de progresso no download dos arquivos\n",
        "class DownloadProgressBar(tqdm.tqdm):\n",
        "    def update_to(self, b=1, bsize=1, tsize=None):\n",
        "        if tsize is not None:\n",
        "            self.total = tsize\n",
        "        self.update(b * bsize - self.n)\n",
        "\n",
        "def download_url(url, output_path):\n",
        "    with DownloadProgressBar(unit='B', unit_scale=True,\n",
        "                             miniters=1, desc=url.split('/')[-1]) as t:\n",
        "        urllib.request.urlretrieve(url, filename=output_path, reporthook=t.update_to)"
      ],
      "execution_count": 8,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kPZpMOvcj46M"
      },
      "source": [
        "# **START**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_SjHmjOrj783"
      },
      "source": [
        "## **Load Data**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aNE0cIcxl0k3"
      },
      "source": [
        "### **Municípios**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1xlLcq0yl07p"
      },
      "source": [
        "municipios = pd.merge(pd.read_csv('https://raw.githubusercontent.com/kelvins/Municipios-Brasileiros/main/csv/municipios.csv', dtype={'codigo_ibge':'Int32','nome':'object','latitude':'float64','longitude':'float64','capital':'bool','codigo_uf':'Int8'}),\n",
        "                      pd.read_csv('https://raw.githubusercontent.com/kelvins/Municipios-Brasileiros/main/csv/estados.csv', header=0, names=['codigo_uf','sigla_uf','nome_uf','lat_uf','long_uf'], dtype={'codigo_uf':'Int8', 'sigla_uf':'object','nome_uf':'object','lat_uf':'float64','long_uf':'float64'})[['codigo_uf','sigla_uf','nome_uf']],\n",
        "                      left_on='codigo_uf',right_on='codigo_uf')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Wy9YkQyYkMZF"
      },
      "source": [
        "municipios.insert(1,'codigo_ibge_6', np.nan)\n",
        "municipios['codigo_ibge_6'] = municipios.codigo_ibge.astype(str).str[:6].astype('int32')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rUJCLoShravF"
      },
      "source": [
        "## agrega informação de estimativa populacional por município, para 2020, extraída do site do IBGE\n",
        "municipios = municipios.merge(\n",
        "    pd.read_csv('apoio/estim_pop_2020.csv', sep=';', skiprows=3, skipfooter=97, engine='python', usecols=['Cód.','2020']).rename(columns={'Cód.': 'codigo_ibge','2020': 'pop_estimada_2020'}),\n",
        "    how='left', left_on = 'codigo_ibge', right_on='codigo_ibge')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "id": "cxQY0E84miU3",
        "outputId": "2554b8cf-61be-4cdd-846a-31b9d41f407e"
      },
      "source": [
        "municipios.sample(5)"
      ],
      "execution_count": null,
      "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>codigo_ibge</th>\n",
              "      <th>codigo_ibge_6</th>\n",
              "      <th>nome</th>\n",
              "      <th>latitude</th>\n",
              "      <th>longitude</th>\n",
              "      <th>capital</th>\n",
              "      <th>codigo_uf</th>\n",
              "      <th>sigla_uf</th>\n",
              "      <th>nome_uf</th>\n",
              "      <th>pop_estimada_2020</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>2675</th>\n",
              "      <td>2612109</td>\n",
              "      <td>261210</td>\n",
              "      <td>Salgadinho</td>\n",
              "      <td>-7.92690</td>\n",
              "      <td>-35.6503</td>\n",
              "      <td>False</td>\n",
              "      <td>26</td>\n",
              "      <td>PE</td>\n",
              "      <td>Pernambuco</td>\n",
              "      <td>11068</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5115</th>\n",
              "      <td>2703700</td>\n",
              "      <td>270370</td>\n",
              "      <td>Jaramataia</td>\n",
              "      <td>-9.66224</td>\n",
              "      <td>-37.0046</td>\n",
              "      <td>False</td>\n",
              "      <td>27</td>\n",
              "      <td>AL</td>\n",
              "      <td>Alagoas</td>\n",
              "      <td>5761</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4184</th>\n",
              "      <td>3504800</td>\n",
              "      <td>350480</td>\n",
              "      <td>Bálsamo</td>\n",
              "      <td>-20.73480</td>\n",
              "      <td>-49.5865</td>\n",
              "      <td>False</td>\n",
              "      <td>35</td>\n",
              "      <td>SP</td>\n",
              "      <td>São Paulo</td>\n",
              "      <td>9139</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4454</th>\n",
              "      <td>3528700</td>\n",
              "      <td>352870</td>\n",
              "      <td>Marabá Paulista</td>\n",
              "      <td>-22.10680</td>\n",
              "      <td>-51.9617</td>\n",
              "      <td>False</td>\n",
              "      <td>35</td>\n",
              "      <td>SP</td>\n",
              "      <td>São Paulo</td>\n",
              "      <td>5948</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1479</th>\n",
              "      <td>2904308</td>\n",
              "      <td>290430</td>\n",
              "      <td>Brejões</td>\n",
              "      <td>-13.10390</td>\n",
              "      <td>-39.7988</td>\n",
              "      <td>False</td>\n",
              "      <td>29</td>\n",
              "      <td>BA</td>\n",
              "      <td>Bahia</td>\n",
              "      <td>14222</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      codigo_ibge  codigo_ibge_6  ...     nome_uf  pop_estimada_2020\n",
              "2675      2612109         261210  ...  Pernambuco              11068\n",
              "5115      2703700         270370  ...     Alagoas               5761\n",
              "4184      3504800         350480  ...   São Paulo               9139\n",
              "4454      3528700         352870  ...   São Paulo               5948\n",
              "1479      2904308         290430  ...       Bahia              14222\n",
              "\n",
              "[5 rows x 10 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3LYNgmfRs9a9"
      },
      "source": [
        "# municipios.to_csv('processed/municipios.csv', index=False)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "q10GmJ9M4nry"
      },
      "source": [
        "### **Registros de vacinação (OpenDataSUS)**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qsThPmyQB2XE"
      },
      "source": [
        "#### ***Download***"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aPbzdTmYB1vu"
      },
      "source": [
        "url_base = 'https://opendatasus.saude.gov.br/dataset/covid-19-vacinacao/resource/ef3bd0b8-b605-474b-9ae5-c97390c197a8'\n",
        "b = BeautifulSoup(urllib.request.urlopen(url_base))"
      ],
      "execution_count": 9,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "pSKnd9jCCk1E",
        "outputId": "6bb3e05b-4b41-40db-a7d7-6fb25e1a1a1a"
      },
      "source": [
        "## como os links são dinâmicos, faço um scrapping na página inicial do repositório e busco os últimos disponíveis\n",
        "links = {link.text:link.get('href') for link in b.findAll('a', attrs={'href': re.compile(\".csv$\")})}\n",
        "links"
      ],
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'Dados AC': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DAC/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados AL': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DAL/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados AM': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DAM/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados AP': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DAP/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados BA': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DBA/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados CE': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DCE/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados Completos': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/completo/2021-04-15/part-00000-1c90f316-21cb-46b8-a2fb-57d03964b29d-c000.csv',\n",
              " 'Dados DF': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DDF/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados ES': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DES/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados GO': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DGO/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados MA': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DMA/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados MG': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DMG/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados MS': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DMS/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados MT': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DMT/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados PA': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DPA/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados PB': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DPB/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados PE': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DPE/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados PI': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DPI/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados PR': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DPR/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados RJ': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DRJ/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados RN': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DRN/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados RO': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DRO/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados RR': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DRR/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados RS': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DRS/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados SC': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DSC/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados SE': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DSE/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados SP': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DSP/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv',\n",
              " 'Dados TO': 'https://s3-sa-east-1.amazonaws.com/ckan.saude.gov.br/PNI/vacina/uf/2021-04-15/uf%3DTO/part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv'}"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wZaNEj7vY6Bc",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "7769ebc1-4a0b-4f72-d14e-1236353890ca"
      },
      "source": [
        "## Para download do arquivo completo\n",
        "for k,v in links.items():\n",
        "  if 'Completos' in k:\n",
        "    download_url(v,'{}.csv'.format(k.split(' ')[-1]))"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "part-00000-1c90f316-21cb-46b8-a2fb-57d03964b29d-c000.csv: 16.7GB [11:37, 24.0MB/s]                            \n"
          ],
          "name": "stderr"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "C5aqX59iDbT4",
        "outputId": "7c3bba3a-ad3e-4271-cd7c-d789dc8f0f2c"
      },
      "source": [
        "## Para download dos arquivos por UF\n",
        "for k,v in links.items():\n",
        "  if 'Completos' not in k:\n",
        "    download_url(v,'{}.csv'.format(k.split(' ')[-1]))"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 57.0MB [00:05, 10.3MB/s]                            \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 278MB [00:18, 15.0MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 291MB [00:27, 10.5MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 52.3MB [00:05, 10.0MB/s]                            \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 1.43GB [01:14, 19.1MB/s]                            \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 654MB [00:38, 17.0MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 189MB [00:13, 13.9MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 365MB [00:21, 16.7MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 485MB [00:27, 17.9MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 424MB [00:26, 15.8MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 1.57GB [01:21, 19.4MB/s]                            \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 218MB [00:15, 14.5MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 211MB [00:13, 15.9MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 469MB [00:27, 17.4MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 388MB [00:22, 17.3MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 694MB [00:45, 15.3MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 206MB [00:12, 16.1MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 783MB [00:43, 18.2MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 1.23GB [01:07, 18.1MB/s]                            \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 285MB [00:19, 14.7MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 96.9MB [00:07, 12.3MB/s]                            \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 47.2MB [00:05, 9.32MB/s]                            \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 1.28GB [01:07, 18.9MB/s]                            \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 501MB [00:34, 14.5MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 161MB [00:11, 14.5MB/s]                           \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 4.27GB [04:03, 17.5MB/s]                            \n",
            "part-00000-5476c5fc-7a69-407c-abce-9eebb0134b29.c000.csv: 104MB [00:07, 13.8MB/s]                           \n"
          ],
          "name": "stderr"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "and9uAE9fd5A"
      },
      "source": [
        "**Há diferença entre os arquivos 'completo' e por UF?**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 117,
          "referenced_widgets": [
            "b4dfffaaecbc4d43b31aec3f7e8b403c",
            "2da308b71a694333934a58003b0a0a94",
            "1d72e35c49334f7eb79988e3fa185efc",
            "e446d12c1f2446bb9846fcc5d76464fd",
            "46d41e1e5bfd4b16ba2cd4ea2df4c4d2",
            "213b585ccaec403d9a7a7b9b68fddf62",
            "878d8fd7c85f42c8b2c3994b5fc6e07d",
            "0b421086ecec49f390a9e475db19d619"
          ]
        },
        "id": "v5QblI92e5Ul",
        "outputId": "58bba26d-40c0-47ee-d700-0ae93560da5f"
      },
      "source": [
        "soma_linhas = {}\n",
        "\n",
        "for file in tqdm.tqdm_notebook(os.listdir()):\n",
        "  if '.csv' in file:\n",
        "    with open(file) as f:\n",
        "        soma_linhas[file.split('.')[0]] = sum(1 for line in f)"
      ],
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:3: TqdmDeprecationWarning: This function will be removed in tqdm==5.0.0\n",
            "Please use `tqdm.notebook.tqdm` instead of `tqdm.tqdm_notebook`\n",
            "  This is separate from the ipykernel package so we can avoid doing imports until\n"
          ],
          "name": "stderr"
        },
        {
          "output_type": "display_data",
          "data": {
            "application/vnd.jupyter.widget-view+json": {
              "model_id": "b4dfffaaecbc4d43b31aec3f7e8b403c",
              "version_minor": 0,
              "version_major": 2
            },
            "text/plain": [
              "HBox(children=(FloatProgress(value=0.0, max=30.0), HTML(value='')))"
            ]
          },
          "metadata": {
            "tags": []
          }
        },
        {
          "output_type": "stream",
          "text": [
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8w8nrt3YgXZE",
        "outputId": "4a1fc958-38fe-4700-e86d-177c516b18f5"
      },
      "source": [
        "print(sum([v-1 for k,v in soma_linhas.items() if k != 'Completos']),'linhas de registros nos arquivos por UF')\n",
        "print(soma_linhas['Completos']-1,'linhas de registros no arquivo completo.')"
      ],
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "31189012 linhas de registros nos arquivos por UF\n",
            "31189012 linhas de registros no arquivo completo.\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oHJ9eXOJ2wtL"
      },
      "source": [
        "**Ótimo, como não há divergência quantitativa entre os arquivos, a decisão de qual utilizar será puramente pela perfomance. Vamos fazer agregações por arquivos de UFs então.**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lIlm2TqWqYb3"
      },
      "source": [
        "#### ***Generate AGGR data***"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fXPEpgkn5a31"
      },
      "source": [
        "## filtra apenas os arquivos \"UF.csv\" da pasta\n",
        "lista_arquivos = [file for file in os.listdir() if len(file.split('.')[0]) == 2]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-f4c9vvxqX8j",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 117,
          "referenced_widgets": [
            "b029e374a7b841568b7c3971a627b61e",
            "d65e13d978d846a7805ba24b4e678380",
            "4171f713736c43c18778ac4af99316dd",
            "bb263af1bb684838aa1aa00fd238559b",
            "0d8d62be433149e2bacba00bb506ae79",
            "dad99439f7704d82afa9c6c6cb8e7afd",
            "eb282f71c6904b8d9372048f9d277c25",
            "286882ff47384ec1b03e10d0f05ab301"
          ]
        },
        "outputId": "2eb1f962-57c1-457c-bf3e-d270a3b797d0"
      },
      "source": [
        "## iteração para ler cada arquivo, selecionando apenas algumas colunas e fazendo tratamentos básicos\n",
        "for file in tqdm.tqdm_notebook(lista_arquivos):\n",
        "\n",
        "  df_vacinas = pd.read_csv(file, sep=';', encoding='utf8',\n",
        "                          usecols=[\n",
        "                                    'document_id', 'paciente_id', \n",
        "                                    # 'paciente_idade', \n",
        "                                    # 'paciente_dataNascimento', 'paciente_enumSexoBiologico', \n",
        "                                    #'paciente_racaCor_codigo', \n",
        "                                    # 'paciente_racaCor_valor', \n",
        "                                    'paciente_endereco_coIbgeMunicipio', \n",
        "                                    # 'paciente_endereco_coPais', 'paciente_endereco_nmMunicipio', 'paciente_endereco_nmPais', 'paciente_endereco_uf', 'paciente_endereco_cep', 'paciente_nacionalidade_enumNacionalidade', \n",
        "                                    # 'estabelecimento_valor', \n",
        "                                    # 'estabelecimento_razaoSocial', 'estalecimento_noFantasia', \n",
        "                                    'estabelecimento_municipio_codigo', \n",
        "                                    # 'estabelecimento_municipio_nome', 'estabelecimento_uf', \n",
        "                                    # 'vacina_grupoAtendimento_codigo', \n",
        "                                    'vacina_grupoAtendimento_nome',\n",
        "                                    # 'vacina_categoria_codigo', \n",
        "                                    'vacina_categoria_nome',\n",
        "                                    # 'vacina_lote', 'vacina_fabricante_nome', 'vacina_fabricante_referencia', \n",
        "                                    'vacina_dataAplicacao', 'vacina_descricao_dose',\n",
        "                                    # 'vacina_codigo', \n",
        "                                    'vacina_nome', \n",
        "                                    # 'sistema_origem', \n",
        "                                    'data_importacao_rnds'\n",
        "                                    ],\n",
        "                          parse_dates=['vacina_dataAplicacao', 'data_importacao_rnds'],\n",
        "                          dtype= {'paciente_endereco_coIbgeMunicipio': 'Int32', 'vacina_categoria_codigo':'Int8', 'vacina_grupoAtendimento_codigo':'Int16', 'estabelecimento_municipio_codigo': 'Int32'},\n",
        "                          )\n",
        "  \n",
        "  ## marcador da UF na primeira coluna\n",
        "  df_vacinas.insert(0, 'UF', file.split('.')[0])\n",
        "  \n",
        "  ## elimina linhas duplicadas\n",
        "  df_vacinas.drop_duplicates(subset='document_id', keep='last', inplace=True)\n",
        "  \n",
        "  ## normaliza e limpa o campo de descrição da dose, eliminando espaços excedentes\n",
        "  df_vacinas.vacina_descricao_dose = df_vacinas.vacina_descricao_dose.apply(lambda x: normalize('NFKD', x).encode('ASCII', 'ignore').decode('ASCII').strip())\n",
        "  \n",
        "  ## cria um contador de cada 1ª dose aplicada\n",
        "  df_vacinas['contador_1a_dose'] = np.where(df_vacinas.vacina_descricao_dose == '1a Dose', 1, 0)\n",
        "  \n",
        "  ## cria um contador de cada 2ª dose (ou dose única) aplicada\n",
        "  df_vacinas['contador_2a_dose'] = np.where((df_vacinas.vacina_descricao_dose == '2a Dose') | (df_vacinas.vacina_descricao_dose == 'Unica'), 1, 0)\n",
        "\n",
        "  \"\"\"\n",
        "  Cria um \"contador de dose aplicada\" para cada linha. \n",
        "  ATENÇÃO! A soma dos contadores anteriores não fecharão com este, pois há descrições inseridas que fogem do padrão, como por exemplo: \"Dose\". \n",
        "  A priori, não se tem como identificar se é a única, a primeira, a segunda, a terceira... pois não segue um padrão. Análise deve ser feita com cada arquivo e cada paciente.\n",
        "  Para o painel de BI, apenas os contadores iniciais são utilizados, descartando os casos não registrados corretamente, que são uma pequeníssima parcela.\n",
        "  \"\"\"\n",
        "  df_vacinas.insert(0, 'contador', 1)\n",
        "\n",
        "  ## formatação da data de aplicação, eliminando hora\n",
        "  df_vacinas.vacina_dataAplicacao = df_vacinas.vacina_dataAplicacao.dt.date\n",
        "  df_vacinas.vacina_dataAplicacao = pd.to_datetime(df_vacinas.vacina_dataAplicacao)\n",
        "\n",
        "  ## faz correção manual quando data aplicação é maior que data registro\n",
        "  df_vacinas['vacina_dataAplicacao'] = np.where(df_vacinas['vacina_dataAplicacao'] > df_vacinas['data_importacao_rnds'], df_vacinas['data_importacao_rnds'], df_vacinas['vacina_dataAplicacao'])\n",
        "  df_vacinas.vacina_dataAplicacao = df_vacinas.vacina_dataAplicacao.dt.date\n",
        "  df_vacinas.vacina_dataAplicacao = pd.to_datetime(df_vacinas.vacina_dataAplicacao)\n",
        "\n",
        "  ## quando município de endereço do paciente não é preenchido, ou é valor não identificado na base oficial, considera o município do estabelecimento como o de residência\n",
        "  df_vacinas.paciente_endereco_coIbgeMunicipio = df_vacinas.paciente_endereco_coIbgeMunicipio.map(municipios.set_index('codigo_ibge_6').codigo_ibge.to_dict())\n",
        "  df_vacinas.estabelecimento_municipio_codigo = df_vacinas.estabelecimento_municipio_codigo.map(municipios.set_index('codigo_ibge_6').codigo_ibge.to_dict())\n",
        "  df_vacinas['paciente_endereco_coIbgeMunicipio'] = df_vacinas['paciente_endereco_coIbgeMunicipio'].fillna(df_vacinas['estabelecimento_municipio_codigo'])\n",
        "  df_vacinas.paciente_endereco_coIbgeMunicipio = df_vacinas.paciente_endereco_coIbgeMunicipio.astype('Int32')\n",
        "\n",
        "  ## faz agregação para contar número de doses aplicadas por dia, com base em determinadas características, e exporta para CSV da UF\n",
        "  pd.concat([df_vacinas.groupby(['paciente_endereco_coIbgeMunicipio', 'vacina_dataAplicacao', 'vacina_categoria_nome', 'vacina_grupoAtendimento_nome', 'vacina_nome']).contador.sum(),\n",
        "            df_vacinas.groupby(['paciente_endereco_coIbgeMunicipio', 'vacina_dataAplicacao', 'vacina_categoria_nome', 'vacina_grupoAtendimento_nome', 'vacina_nome']).contador_1a_dose.sum(),\n",
        "            df_vacinas.groupby(['paciente_endereco_coIbgeMunicipio', 'vacina_dataAplicacao', 'vacina_categoria_nome', 'vacina_grupoAtendimento_nome', 'vacina_nome']).contador_2a_dose.sum()],\n",
        "            axis=1).to_csv('processed/aggr_doses_{}.csv'.format(file.split('.')[0]), header=False)\n",
        "\n",
        "  ## marca qual a data/hora mais recente de importação no RNDS de cada UF\n",
        "  df_vacinas.groupby('UF').data_importacao_rnds.max().reset_index().to_csv('processed/max_date.csv', mode='a', header=False, index=False)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:3: TqdmDeprecationWarning: This function will be removed in tqdm==5.0.0\n",
            "Please use `tqdm.notebook.tqdm` instead of `tqdm.tqdm_notebook`\n",
            "  This is separate from the ipykernel package so we can avoid doing imports until\n"
          ],
          "name": "stderr"
        },
        {
          "output_type": "display_data",
          "data": {
            "application/vnd.jupyter.widget-view+json": {
              "model_id": "b029e374a7b841568b7c3971a627b61e",
              "version_minor": 0,
              "version_major": 2
            },
            "text/plain": [
              "HBox(children=(FloatProgress(value=0.0, max=27.0), HTML(value='')))"
            ]
          },
          "metadata": {
            "tags": []
          }
        },
        {
          "output_type": "stream",
          "text": [
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2IUcvrfS8cf8"
      },
      "source": [
        "# **ANALYSIS**\n",
        "Acima, foi o procedimento básico para gerar os dados agregados vistos no [painel](http://antiamador.org/bi/vacinaBR).<br>\n",
        "\n",
        "Abaixo, vou pegar um (ou alguns) arquivos como parâmetro e fazer uma análise mais detalhada, **por meio de questionamentos**, para apresentar alguns pontos incongruentes dos dados, ou pelo menos, intrigantes. Também, detalhar a motivação dos tratamentos realizados na agregação. <br>\n",
        "\n",
        "Aproveito para reforçar o link da fonte das informações, inclusive com a metodologia para consumo via API, e o dicionário de dados: https://opendatasus.saude.gov.br/dataset/covid-19-vacinacao"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RaJSSFLj948J"
      },
      "source": [
        "## **LOAD**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EwxWNRgK8fwh"
      },
      "source": [
        "file = 'RS.csv'"
      ],
      "execution_count": 18,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KUWuWkTQ9WZt"
      },
      "source": [
        "df_vacinas = pd.read_csv(file, sep=';', encoding='utf8',\n",
        "                          usecols=[\n",
        "                                    'document_id', \n",
        "                                    'paciente_id', 'paciente_idade', 'paciente_dataNascimento', 'paciente_enumSexoBiologico', 'paciente_racaCor_codigo', 'paciente_racaCor_valor', 'paciente_endereco_coIbgeMunicipio', 'paciente_endereco_coPais', 'paciente_endereco_nmMunicipio', 'paciente_endereco_nmPais', 'paciente_endereco_uf', 'paciente_endereco_cep', 'paciente_nacionalidade_enumNacionalidade', \n",
        "                                    'estabelecimento_valor', 'estabelecimento_razaoSocial', 'estalecimento_noFantasia', 'estabelecimento_municipio_codigo', 'estabelecimento_municipio_nome', 'estabelecimento_uf', \n",
        "                                    'vacina_grupoAtendimento_codigo', 'vacina_grupoAtendimento_nome', 'vacina_categoria_codigo', 'vacina_categoria_nome', \n",
        "                                    'vacina_lote', 'vacina_fabricante_nome', 'vacina_fabricante_referencia', \n",
        "                                    'vacina_dataAplicacao', 'vacina_descricao_dose',\n",
        "                                    'vacina_codigo', 'vacina_nome', \n",
        "                                    'sistema_origem', 'data_importacao_rnds'\n",
        "                                    ],\n",
        "                          parse_dates=['vacina_dataAplicacao', 'data_importacao_rnds', 'paciente_dataNascimento'],\n",
        "                          dtype= {'paciente_endereco_coIbgeMunicipio': 'Int32', \n",
        "                                  'vacina_categoria_codigo':'Int8', \n",
        "                                  'vacina_grupoAtendimento_codigo':'Int16', \n",
        "                                  'estabelecimento_municipio_codigo': 'Int32'},\n",
        "                          )"
      ],
      "execution_count": 19,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "tJWsYK5p-qoa",
        "outputId": "9fc42de8-3e93-4a8a-a46b-4d18787fef7e"
      },
      "source": [
        "df_vacinas.info()"
      ],
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 2329087 entries, 0 to 2329086\n",
            "Data columns (total 33 columns):\n",
            " #   Column                                    Dtype              \n",
            "---  ------                                    -----              \n",
            " 0   document_id                               object             \n",
            " 1   paciente_id                               object             \n",
            " 2   paciente_idade                            int64              \n",
            " 3   paciente_dataNascimento                   datetime64[ns]     \n",
            " 4   paciente_enumSexoBiologico                object             \n",
            " 5   paciente_racaCor_codigo                   int64              \n",
            " 6   paciente_racaCor_valor                    object             \n",
            " 7   paciente_endereco_coIbgeMunicipio         Int32              \n",
            " 8   paciente_endereco_coPais                  float64            \n",
            " 9   paciente_endereco_nmMunicipio             object             \n",
            " 10  paciente_endereco_nmPais                  object             \n",
            " 11  paciente_endereco_uf                      object             \n",
            " 12  paciente_endereco_cep                     float64            \n",
            " 13  paciente_nacionalidade_enumNacionalidade  object             \n",
            " 14  estabelecimento_valor                     int64              \n",
            " 15  estabelecimento_razaoSocial               object             \n",
            " 16  estalecimento_noFantasia                  object             \n",
            " 17  estabelecimento_municipio_codigo          Int32              \n",
            " 18  estabelecimento_municipio_nome            object             \n",
            " 19  estabelecimento_uf                        object             \n",
            " 20  vacina_grupoAtendimento_codigo            Int16              \n",
            " 21  vacina_grupoAtendimento_nome              object             \n",
            " 22  vacina_categoria_codigo                   Int8               \n",
            " 23  vacina_categoria_nome                     object             \n",
            " 24  vacina_lote                               object             \n",
            " 25  vacina_fabricante_nome                    object             \n",
            " 26  vacina_fabricante_referencia              object             \n",
            " 27  vacina_dataAplicacao                      datetime64[ns, UTC]\n",
            " 28  vacina_descricao_dose                     object             \n",
            " 29  vacina_codigo                             int64              \n",
            " 30  vacina_nome                               object             \n",
            " 31  sistema_origem                            object             \n",
            " 32  data_importacao_rnds                      datetime64[ns]     \n",
            "dtypes: Int16(1), Int32(2), Int8(1), datetime64[ns, UTC](1), datetime64[ns](2), float64(2), int64(4), object(20)\n",
            "memory usage: 548.6+ MB\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 513
        },
        "id": "ERtk02b8-vFZ",
        "outputId": "d26b1c08-8fb4-4666-ad1f-483aefbe3891"
      },
      "source": [
        "df_vacinas.sample(5)"
      ],
      "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>document_id</th>\n",
              "      <th>paciente_id</th>\n",
              "      <th>paciente_idade</th>\n",
              "      <th>paciente_dataNascimento</th>\n",
              "      <th>paciente_enumSexoBiologico</th>\n",
              "      <th>paciente_racaCor_codigo</th>\n",
              "      <th>paciente_racaCor_valor</th>\n",
              "      <th>paciente_endereco_coIbgeMunicipio</th>\n",
              "      <th>paciente_endereco_coPais</th>\n",
              "      <th>paciente_endereco_nmMunicipio</th>\n",
              "      <th>paciente_endereco_nmPais</th>\n",
              "      <th>paciente_endereco_uf</th>\n",
              "      <th>paciente_endereco_cep</th>\n",
              "      <th>paciente_nacionalidade_enumNacionalidade</th>\n",
              "      <th>estabelecimento_valor</th>\n",
              "      <th>estabelecimento_razaoSocial</th>\n",
              "      <th>estalecimento_noFantasia</th>\n",
              "      <th>estabelecimento_municipio_codigo</th>\n",
              "      <th>estabelecimento_municipio_nome</th>\n",
              "      <th>estabelecimento_uf</th>\n",
              "      <th>vacina_grupoAtendimento_codigo</th>\n",
              "      <th>vacina_grupoAtendimento_nome</th>\n",
              "      <th>vacina_categoria_codigo</th>\n",
              "      <th>vacina_categoria_nome</th>\n",
              "      <th>vacina_lote</th>\n",
              "      <th>vacina_fabricante_nome</th>\n",
              "      <th>vacina_fabricante_referencia</th>\n",
              "      <th>vacina_dataAplicacao</th>\n",
              "      <th>vacina_descricao_dose</th>\n",
              "      <th>vacina_codigo</th>\n",
              "      <th>vacina_nome</th>\n",
              "      <th>sistema_origem</th>\n",
              "      <th>data_importacao_rnds</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>136417</th>\n",
              "      <td>18880fec-b6f3-4cef-852d-f3ea025fa248-i0b0</td>\n",
              "      <td>1c45003daee9007f9d82d324f3f7799d74254d167103af...</td>\n",
              "      <td>58</td>\n",
              "      <td>1962-11-22</td>\n",
              "      <td>F</td>\n",
              "      <td>2</td>\n",
              "      <td>PRETA</td>\n",
              "      <td>431225</td>\n",
              "      <td>10.0</td>\n",
              "      <td>MINAS DO LEAO</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>96755.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2226693</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE MINAS DO LEAO</td>\n",
              "      <td>CENTRO MUNICIPAL DE SAUDE MINAS DO LEAO</td>\n",
              "      <td>431225</td>\n",
              "      <td>MINAS DO LEAO</td>\n",
              "      <td>RS</td>\n",
              "      <td>905</td>\n",
              "      <td>Cuidador de Idosos</td>\n",
              "      <td>9</td>\n",
              "      <td>Trabalhadores de Saúde</td>\n",
              "      <td>200279</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-02-09 03:00:00+00:00</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-02-13 01:19:33</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>969601</th>\n",
              "      <td>c345270e-7857-4c5e-ab33-70b3a44744b6-i0b0</td>\n",
              "      <td>f252e888adf3e1426d51335b1b00fa0f308f9b9510121b...</td>\n",
              "      <td>64</td>\n",
              "      <td>1956-06-13</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>431490</td>\n",
              "      <td>10.0</td>\n",
              "      <td>PORTO ALEGRE</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>91430.0</td>\n",
              "      <td>B</td>\n",
              "      <td>7250479</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE PORTO ALEGRE</td>\n",
              "      <td>GERENCIA DISTRITAL CENTRO</td>\n",
              "      <td>431490</td>\n",
              "      <td>PORTO ALEGRE</td>\n",
              "      <td>RS</td>\n",
              "      <td>905</td>\n",
              "      <td>Cuidador de Idosos</td>\n",
              "      <td>9</td>\n",
              "      <td>Trabalhadores de Saúde</td>\n",
              "      <td>4120Z005</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>Organization/33781055000135</td>\n",
              "      <td>2021-02-25 03:00:00+00:00</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>85</td>\n",
              "      <td>Vacina Covid-19 - Covishield</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-03-16 13:22:54</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1662095</th>\n",
              "      <td>f65a3912-880c-4c4e-ae9b-feba2e18b4e2-i0b0</td>\n",
              "      <td>dd150afa4f7f275bc932e299d75b4829d950fc968c58d9...</td>\n",
              "      <td>73</td>\n",
              "      <td>1947-09-26</td>\n",
              "      <td>M</td>\n",
              "      <td>4</td>\n",
              "      <td>AMARELA</td>\n",
              "      <td>432215</td>\n",
              "      <td>10.0</td>\n",
              "      <td>TUNAS</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>99330.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2245604</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE TUNAS</td>\n",
              "      <td>CENTRO DE SAUDE DE TUNAS TUNAS</td>\n",
              "      <td>432215</td>\n",
              "      <td>TUNAS</td>\n",
              "      <td>RS</td>\n",
              "      <td>203</td>\n",
              "      <td>Pessoas de 70 a 74 anos</td>\n",
              "      <td>2</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210075</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-03-26 03:00:00+00:00</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-03-28 20:50:59</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>273561</th>\n",
              "      <td>d53e3e91-fbba-42aa-9c6a-e0d9a2c550ee-i0b0</td>\n",
              "      <td>0669f98657314182f265677a6a82324d8acc66b279b4f6...</td>\n",
              "      <td>65</td>\n",
              "      <td>1955-05-18</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>430820</td>\n",
              "      <td>10.0</td>\n",
              "      <td>FLORES DA CUNHA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>95270.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2240289</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE FLORES DA CUNHA</td>\n",
              "      <td>CENTRO DE SAUDE MUNICIPAL IRMA BENEDITA ZORZI</td>\n",
              "      <td>430820</td>\n",
              "      <td>FLORES DA CUNHA</td>\n",
              "      <td>RS</td>\n",
              "      <td>202</td>\n",
              "      <td>Pessoas de 65 a 69 anos</td>\n",
              "      <td>2</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210134</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-04-03 03:00:00+00:00</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-04-05 20:05:05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2067964</th>\n",
              "      <td>ca06d144-9113-4f4a-8750-4a3836684926-i0b0</td>\n",
              "      <td>5117eba4f5cd322a81f37b1b58cc233c226dbf10f5f808...</td>\n",
              "      <td>61</td>\n",
              "      <td>1960-03-04</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>431440</td>\n",
              "      <td>10.0</td>\n",
              "      <td>PELOTAS</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>96080.0</td>\n",
              "      <td>B</td>\n",
              "      <td>433489</td>\n",
              "      <td>MUNICIPIO DE PELOTAS</td>\n",
              "      <td>CENTRAL REDE DE FRIO MUNICIPL NUCLEO MUNCIPAL</td>\n",
              "      <td>431440</td>\n",
              "      <td>PELOTAS</td>\n",
              "      <td>RS</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 60 a 64 anos</td>\n",
              "      <td>2</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>213VCD006ZVA</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>Organization/33781055000135</td>\n",
              "      <td>2021-04-11 03:00:00+00:00</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>85</td>\n",
              "      <td>Vacina Covid-19 - Covishield</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-04-13 13:12:46</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                       document_id  ... data_importacao_rnds\n",
              "136417   18880fec-b6f3-4cef-852d-f3ea025fa248-i0b0  ...  2021-02-13 01:19:33\n",
              "969601   c345270e-7857-4c5e-ab33-70b3a44744b6-i0b0  ...  2021-03-16 13:22:54\n",
              "1662095  f65a3912-880c-4c4e-ae9b-feba2e18b4e2-i0b0  ...  2021-03-28 20:50:59\n",
              "273561   d53e3e91-fbba-42aa-9c6a-e0d9a2c550ee-i0b0  ...  2021-04-05 20:05:05\n",
              "2067964  ca06d144-9113-4f4a-8750-4a3836684926-i0b0  ...  2021-04-13 13:12:46\n",
              "\n",
              "[5 rows x 33 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 24
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1aOGz7M1-BiH"
      },
      "source": [
        "## **QUESTION**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3mIivVV9-K4F"
      },
      "source": [
        "### Há dados duplicados?\n",
        "Sim. Ao que tudo indica, o 'Identificador do Documento' (`document_id`) é a chave única da base. Deveria então, representar uma vacinação exclusivamente. Em alguns arquivos de determinados Estados, não é o que acontece. Podemos ver inclusive que todas informações são idênticas."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "id": "iRNgdxMMgo5S",
        "outputId": "3ec40c00-07cf-4ccc-b083-1fa4a593402f"
      },
      "source": [
        "df_vacinas.loc[df_vacinas.duplicated(subset='document_id',keep=False)].sort_values(by='document_id')[:10]"
      ],
      "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>document_id</th>\n",
              "      <th>paciente_id</th>\n",
              "      <th>paciente_idade</th>\n",
              "      <th>paciente_dataNascimento</th>\n",
              "      <th>paciente_enumSexoBiologico</th>\n",
              "      <th>paciente_racaCor_codigo</th>\n",
              "      <th>paciente_racaCor_valor</th>\n",
              "      <th>paciente_endereco_coIbgeMunicipio</th>\n",
              "      <th>paciente_endereco_coPais</th>\n",
              "      <th>paciente_endereco_nmMunicipio</th>\n",
              "      <th>paciente_endereco_nmPais</th>\n",
              "      <th>paciente_endereco_uf</th>\n",
              "      <th>paciente_endereco_cep</th>\n",
              "      <th>paciente_nacionalidade_enumNacionalidade</th>\n",
              "      <th>estabelecimento_valor</th>\n",
              "      <th>estabelecimento_razaoSocial</th>\n",
              "      <th>estalecimento_noFantasia</th>\n",
              "      <th>estabelecimento_municipio_codigo</th>\n",
              "      <th>estabelecimento_municipio_nome</th>\n",
              "      <th>estabelecimento_uf</th>\n",
              "      <th>vacina_grupoAtendimento_codigo</th>\n",
              "      <th>vacina_grupoAtendimento_nome</th>\n",
              "      <th>vacina_categoria_codigo</th>\n",
              "      <th>vacina_categoria_nome</th>\n",
              "      <th>vacina_lote</th>\n",
              "      <th>vacina_fabricante_nome</th>\n",
              "      <th>vacina_fabricante_referencia</th>\n",
              "      <th>vacina_dataAplicacao</th>\n",
              "      <th>vacina_descricao_dose</th>\n",
              "      <th>vacina_codigo</th>\n",
              "      <th>vacina_nome</th>\n",
              "      <th>sistema_origem</th>\n",
              "      <th>data_importacao_rnds</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1689853</th>\n",
              "      <td>00022e15-e1d5-4366-a9bf-fe5c483866a9-i0b0</td>\n",
              "      <td>10d322daa792f30230e2f4c15d9222bff146fbad1ce605...</td>\n",
              "      <td>76</td>\n",
              "      <td>1945-01-13</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>431320</td>\n",
              "      <td>10.0</td>\n",
              "      <td>NOVA PETROPOLIS</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>95150.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2238357</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE NOVA PETROPOLIS</td>\n",
              "      <td>UNIDADE BASICA DE SAUDE CENTRO</td>\n",
              "      <td>431320</td>\n",
              "      <td>NOVA PETROPOLIS</td>\n",
              "      <td>RS</td>\n",
              "      <td>204</td>\n",
              "      <td>Pessoas de 75 a 79 anos</td>\n",
              "      <td>2</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210051</td>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-03-12 00:00:00+00:00</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-03-22 00:39:30</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1277332</th>\n",
              "      <td>00022e15-e1d5-4366-a9bf-fe5c483866a9-i0b0</td>\n",
              "      <td>10d322daa792f30230e2f4c15d9222bff146fbad1ce605...</td>\n",
              "      <td>76</td>\n",
              "      <td>1945-01-13</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>431320</td>\n",
              "      <td>10.0</td>\n",
              "      <td>NOVA PETROPOLIS</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>95150.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2238357</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE NOVA PETROPOLIS</td>\n",
              "      <td>UNIDADE BASICA DE SAUDE CENTRO</td>\n",
              "      <td>431320</td>\n",
              "      <td>NOVA PETROPOLIS</td>\n",
              "      <td>RS</td>\n",
              "      <td>204</td>\n",
              "      <td>Pessoas de 75 a 79 anos</td>\n",
              "      <td>2</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210051</td>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-03-12 00:00:00+00:00</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-03-22 00:39:30</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>395627</th>\n",
              "      <td>000e4e5e-03ba-4dbd-b1f4-c6985d3d2b99-i0b0</td>\n",
              "      <td>1b8789acd40fbf5c0789eef14ae728f38f8f683da344ab...</td>\n",
              "      <td>68</td>\n",
              "      <td>1952-07-30</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>430544</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CHUVISCA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>96193.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2224844</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE CHUVISCA</td>\n",
              "      <td>POSTO DE SAUDE</td>\n",
              "      <td>430544</td>\n",
              "      <td>CHUVISCA</td>\n",
              "      <td>RS</td>\n",
              "      <td>202</td>\n",
              "      <td>Pessoas de 65 a 69 anos</td>\n",
              "      <td>2</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210086</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-04-07 03:00:00+00:00</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-04-07 17:05:46</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1493443</th>\n",
              "      <td>000e4e5e-03ba-4dbd-b1f4-c6985d3d2b99-i0b0</td>\n",
              "      <td>1b8789acd40fbf5c0789eef14ae728f38f8f683da344ab...</td>\n",
              "      <td>68</td>\n",
              "      <td>1952-07-30</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>430544</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CHUVISCA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>96193.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2224844</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE CHUVISCA</td>\n",
              "      <td>POSTO DE SAUDE</td>\n",
              "      <td>430544</td>\n",
              "      <td>CHUVISCA</td>\n",
              "      <td>RS</td>\n",
              "      <td>202</td>\n",
              "      <td>Pessoas de 65 a 69 anos</td>\n",
              "      <td>2</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210086</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-04-07 03:00:00+00:00</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-04-07 17:05:46</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1352960</th>\n",
              "      <td>000fd77c-e7e1-4752-92b6-b505702c1b3d-i0b0</td>\n",
              "      <td>a80450816a9cbf69459088ed0bd646bf1cdce76fb4480a...</td>\n",
              "      <td>58</td>\n",
              "      <td>1962-03-29</td>\n",
              "      <td>F</td>\n",
              "      <td>5</td>\n",
              "      <td>INDIGENA</td>\n",
              "      <td>430885</td>\n",
              "      <td>10.0</td>\n",
              "      <td>GENTIL</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>99160.0</td>\n",
              "      <td>B</td>\n",
              "      <td>6177743</td>\n",
              "      <td>MINISTERIO DA SAUDE</td>\n",
              "      <td>POLO BASE BARRA DO RIBEIRO</td>\n",
              "      <td>430190</td>\n",
              "      <td>BARRA DO RIBEIRO</td>\n",
              "      <td>RS</td>\n",
              "      <td>701</td>\n",
              "      <td>Povos indígenas em terras indígenas</td>\n",
              "      <td>7</td>\n",
              "      <td>Povos Indígenas</td>\n",
              "      <td>202010020</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-03-22 03:00:00+00:00</td>\n",
              "      <td>2ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-03-23 19:06:49</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1627228</th>\n",
              "      <td>000fd77c-e7e1-4752-92b6-b505702c1b3d-i0b0</td>\n",
              "      <td>a80450816a9cbf69459088ed0bd646bf1cdce76fb4480a...</td>\n",
              "      <td>58</td>\n",
              "      <td>1962-03-29</td>\n",
              "      <td>F</td>\n",
              "      <td>5</td>\n",
              "      <td>INDIGENA</td>\n",
              "      <td>430885</td>\n",
              "      <td>10.0</td>\n",
              "      <td>GENTIL</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>99160.0</td>\n",
              "      <td>B</td>\n",
              "      <td>6177743</td>\n",
              "      <td>MINISTERIO DA SAUDE</td>\n",
              "      <td>POLO BASE BARRA DO RIBEIRO</td>\n",
              "      <td>430190</td>\n",
              "      <td>BARRA DO RIBEIRO</td>\n",
              "      <td>RS</td>\n",
              "      <td>701</td>\n",
              "      <td>Povos indígenas em terras indígenas</td>\n",
              "      <td>7</td>\n",
              "      <td>Povos Indígenas</td>\n",
              "      <td>202010020</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-03-22 03:00:00+00:00</td>\n",
              "      <td>2ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-03-23 19:06:49</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1112779</th>\n",
              "      <td>0010ec62-bdb5-4ee2-80a5-855f7362732e-i0b0</td>\n",
              "      <td>d47dbdf8d96a024b71ec550f9028eee59ce045b741e7f0...</td>\n",
              "      <td>63</td>\n",
              "      <td>1958-02-23</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>431500</td>\n",
              "      <td>10.0</td>\n",
              "      <td>PORTO LUCENA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>98980.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2250268</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE PORTO LUCENA</td>\n",
              "      <td>POSTO DE SAUDE PSF 1</td>\n",
              "      <td>431500</td>\n",
              "      <td>PORTO LUCENA</td>\n",
              "      <td>RS</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 60 a 64 anos</td>\n",
              "      <td>2</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210132</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-04-06 03:00:00+00:00</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-04-07 19:06:45</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2107773</th>\n",
              "      <td>0010ec62-bdb5-4ee2-80a5-855f7362732e-i0b0</td>\n",
              "      <td>d47dbdf8d96a024b71ec550f9028eee59ce045b741e7f0...</td>\n",
              "      <td>63</td>\n",
              "      <td>1958-02-23</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>431500</td>\n",
              "      <td>10.0</td>\n",
              "      <td>PORTO LUCENA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>98980.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2250268</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE PORTO LUCENA</td>\n",
              "      <td>POSTO DE SAUDE PSF 1</td>\n",
              "      <td>431500</td>\n",
              "      <td>PORTO LUCENA</td>\n",
              "      <td>RS</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 60 a 64 anos</td>\n",
              "      <td>2</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210132</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-04-06 03:00:00+00:00</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-04-07 19:06:45</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>443032</th>\n",
              "      <td>0012eec0-52a6-404d-ac65-78247a7bc67b-i0b0</td>\n",
              "      <td>9579463a4694c5a4007321a16ee9fd46aac65309b95b90...</td>\n",
              "      <td>82</td>\n",
              "      <td>1938-04-11</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>431340</td>\n",
              "      <td>10.0</td>\n",
              "      <td>NOVO HAMBURGO</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>93332.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2230933</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE NOVO HAMBURGO</td>\n",
              "      <td>CASA DE VACINAS TISIOLOGIA E ESTOMIAS</td>\n",
              "      <td>431340</td>\n",
              "      <td>NOVO HAMBURGO</td>\n",
              "      <td>RS</td>\n",
              "      <td>205</td>\n",
              "      <td>Pessoas de 80 anos ou mais</td>\n",
              "      <td>2</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>4120Z026</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-03-02 00:00:00+00:00</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>85</td>\n",
              "      <td>Vacina Covid-19 - Covishield</td>\n",
              "      <td>G-MUS - Gestão Municipal de Saúde</td>\n",
              "      <td>2021-03-08 17:06:10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1138674</th>\n",
              "      <td>0012eec0-52a6-404d-ac65-78247a7bc67b-i0b0</td>\n",
              "      <td>9579463a4694c5a4007321a16ee9fd46aac65309b95b90...</td>\n",
              "      <td>82</td>\n",
              "      <td>1938-04-11</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>431340</td>\n",
              "      <td>10.0</td>\n",
              "      <td>NOVO HAMBURGO</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>93332.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2230933</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE NOVO HAMBURGO</td>\n",
              "      <td>CASA DE VACINAS TISIOLOGIA E ESTOMIAS</td>\n",
              "      <td>431340</td>\n",
              "      <td>NOVO HAMBURGO</td>\n",
              "      <td>RS</td>\n",
              "      <td>205</td>\n",
              "      <td>Pessoas de 80 anos ou mais</td>\n",
              "      <td>2</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>4120Z026</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-03-02 00:00:00+00:00</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>85</td>\n",
              "      <td>Vacina Covid-19 - Covishield</td>\n",
              "      <td>G-MUS - Gestão Municipal de Saúde</td>\n",
              "      <td>2021-03-08 17:06:10</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                       document_id  ... data_importacao_rnds\n",
              "1689853  00022e15-e1d5-4366-a9bf-fe5c483866a9-i0b0  ...  2021-03-22 00:39:30\n",
              "1277332  00022e15-e1d5-4366-a9bf-fe5c483866a9-i0b0  ...  2021-03-22 00:39:30\n",
              "395627   000e4e5e-03ba-4dbd-b1f4-c6985d3d2b99-i0b0  ...  2021-04-07 17:05:46\n",
              "1493443  000e4e5e-03ba-4dbd-b1f4-c6985d3d2b99-i0b0  ...  2021-04-07 17:05:46\n",
              "1352960  000fd77c-e7e1-4752-92b6-b505702c1b3d-i0b0  ...  2021-03-23 19:06:49\n",
              "1627228  000fd77c-e7e1-4752-92b6-b505702c1b3d-i0b0  ...  2021-03-23 19:06:49\n",
              "1112779  0010ec62-bdb5-4ee2-80a5-855f7362732e-i0b0  ...  2021-04-07 19:06:45\n",
              "2107773  0010ec62-bdb5-4ee2-80a5-855f7362732e-i0b0  ...  2021-04-07 19:06:45\n",
              "443032   0012eec0-52a6-404d-ac65-78247a7bc67b-i0b0  ...  2021-03-08 17:06:10\n",
              "1138674  0012eec0-52a6-404d-ac65-78247a7bc67b-i0b0  ...  2021-03-08 17:06:10\n",
              "\n",
              "[10 rows x 33 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "h29GkGzBkQgv",
        "outputId": "d77cd846-9704-4110-c052-5a706c52faa7"
      },
      "source": [
        "df_vacinas.shape"
      ],
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(2329087, 33)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 26
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DH9Wf5icjgTM"
      },
      "source": [
        "df_vacinas.drop_duplicates(subset='document_id', keep='last', inplace=True)"
      ],
      "execution_count": 27,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "M5bvMfGx_iQl",
        "outputId": "224657c9-8933-446f-881f-20a197d4b3c4"
      },
      "source": [
        "df_vacinas.shape"
      ],
      "execution_count": 28,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(2318382, 33)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 28
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MoyYhb4b_0FC"
      },
      "source": [
        "### Quem foi o/a paciente #0?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "egUYAbMrAaMw"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uZZwP7b7AQHq"
      },
      "source": [
        "### Quem foi o paciente mais velho?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GWlhorQ6AZ5c"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "w_H-sEjDAQCG"
      },
      "source": [
        "### E o mais novo?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xalbU3krAZj4"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dLqnf6K5AP-S"
      },
      "source": [
        "### Quais os tipo de doses aplicadas?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SJLYvCwEAZLE"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ENx9ItbLAP6Q"
      },
      "source": [
        "### Algum paciente tomou mais de duas doses?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GVmH-45l_rS5"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7i59YqPUAP2O"
      },
      "source": [
        "### Quantos estão respeitando o tempo para tomar a 2ª dose?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "b_n0RijF_rL3"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AXDy95yCAPvz"
      },
      "source": [
        "### Alguém tomou dois (ou mais) tipos de vacina?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TnYBCTHZ_rbT"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}