M3_sc0207_07am.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "M3 - sc0207_07am.ipynb",
      "provenance": [],
      "mount_file_id": "1M45jnWTYFCMqLT6shcaOoLFdYh4S5Os_",
      "authorship_tag": "ABX9TyNsvsWfV5ZF4ziqX7bqcnB8",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/amf60/vac_covid19_sc_Combinations-of-Concern/blob/main/M3_sc0207_07am.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tbyIR-Hx_Dqu"
      },
      "source": [
        "<Font color = 'brown'>**Covid-19 vaccination records in the Brazilian state of Santa Catarina**\n",
        "\n",
        "<Font color = 'brown'>**COMBINATIONS OF CONCERN - CoC**\n",
        "\n",
        "\n",
        "*   Clinical Data Scientist: Aldir MEDEIROS FILHO\n",
        "*   FLORIANOPOLIS, [Santa Catarina(SC)](https://en.wikipedia.org/wiki/Santa_Catarina_(state), Brasil\n",
        "*   02 Jul 2021\n",
        "\n",
        "\n",
        "**Introduction**:\n",
        "\n",
        "This project was created as a task for Module 3 (out 5) of the [Alura Bootcamp Data Sciences Applicada](https://www.alura.com.br/bootcamp/data-science-aplicada/matriculas-abertas).\n",
        "\n",
        "It is a continuation and extension of the exploratory work I already done for the first two modules of this bootcamp, when I reviewed the OpenDataSus registers of covid19 vaccination in the Brazilian State of Santa Catarina(SC) up to 30 May 20021.\n",
        "\n",
        "The main finding from this [initial work](https://github.com/amf60/which_covid19_vaccine_did_you_receive_in_SC_BR) done using a mix of terminal command line, Python&Cloab and Excel was that:\n",
        "\n",
        "> for ~ 12% of registers (out of ~ 2 Million end of May 2021), we can not confirm which vaccine an individual received when I cross-checked 3 variables from the master dataset. I named this troublesome combinations as <Font color = 'brow'>'Combinations of Concern' - CoC </Font> (step 5 here after for definition).\n",
        "\n",
        "> This ~ 12% proportion of CoC's registrations was already noticed back in March and April, when the nr of registers were less than 1 Million and so I could use only Excel.\n",
        "\n",
        "<Font color = 'green'>***DES-EXCELANDO!!!!***\n",
        "\n",
        "<font color='blue'>My objectives for this Module 3 project were:\n",
        "\n",
        "\n",
        "\n",
        "*   <font color='blue'>to use only Google Colab notebook and Python related packages, to check the OpenDataSus registration data up to 30 June 2021, and;\n",
        "*   <font color='blue'>verify if the 12 % trend of 'Combinations of Concern' is still a matter for concern end June 2021.\n",
        "\n",
        "This project is a work in progress and is being conceived and designed with the end-user in mind: the SC DIVE's UDVE managers and local vaccination teams accross the state of Santa Catarina. \n",
        "\n",
        "My final goal is that local UDVE teams may use the delivers of the final bootcamp project to help correct, clean and update their covid 19 registration files. \n",
        "\n",
        "I am also aiming a high level of automation of the entire monitoring process, so the end-users can, at any time, quickly and easily track progress of the corrective actions to address the 'Combinations of Concern' - CoC."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "p1sJ-FQtDr6i"
      },
      "source": [
        "# <font color = 'blue'>**Phase 1 - importing master dataset and some data wrangling**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UXno7EepuOcF"
      },
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import matplotlib as mpl\n",
        "#import seaborn as sns"
      ],
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "N6bt9kCy-tF3",
        "outputId": "07d5d952-7343-476a-d3e3-2402679e329c"
      },
      "source": [
        "print('Versão do pandas -> %s' % pd.__version__)\n",
        "print('Versão do numpy -> %s' % np.__version__)\n",
        "print('Versão do matplotlib -> %s' % mpl.__version__)\n",
        "#print('Versão do seaborn -> %s' % sns.__version__)\n",
        "#print('Versão do scipy -> %s' % scipy.__version__)"
      ],
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Versão do pandas -> 1.1.5\n",
            "Versão do numpy -> 1.19.5\n",
            "Versão do matplotlib -> 3.2.2\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "upPmma0xyOjQ"
      },
      "source": [
        "# <Font color = 'green'>**Step 1** - imported the raw master csv dataset into the Google Drive folder created for this project.\n",
        "\n",
        "This is the fastest process I found to upload file into Colab (less than 2 minutes for a 1.7 GB master file).\n",
        "\n",
        "However when the file was inside the drive folder, when right clicked on it, I could not find the path to this file using pd.read_csv(''). \n",
        "\n",
        "To resolve it I used tips from this [source](https://neptune.ai/blog/google-colab-dealing-with-files). That helped me transfer the file from the drive folder to Colab Files, as shown in the Alura teachings, and then to copy path to add to pd.read_csv."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "A3-1LAbo0YTA"
      },
      "source": [
        "# <Font Color = 'green'>**Step 2** - Imported the raw master file into Google Colab, renamed the headers and some data cleaning\n",
        "\n",
        "Renamed all 34 original colum's head & variable names (shorter and identifiable by column nr). For the original colunm names please check this [source](https://opendatasus.saude.gov.br/dataset/b772ee55-07cd-44d8-958f-b12edd004e0b/resource/38ead83d-b115-4219-852e-7244792bc311/download/dicionario-de-dados-vacinacao.pdf).\n",
        "\n",
        "To have the most updated dataset up to 30 Jun 2021, I downloaded the file from [OpenDataSus](https://opendatasus.saude.gov.br/dataset/covid-19-vacinacao/resource/ef3bd0b8-b605-474b-9ae5-c97390c197a8?view_id=6bf433e7-16ec-4dfa-a725-08db02322bd6) on 02 Jul 21 at 07 am, and re-named my downloaded master file to match this (sc0207_07am_csv).\n",
        "\n",
        "Per the original source records at the time of the download, the latest updated of the original database was done on 01 July 21 at 3 pm. I hope, we will have most of the updated registrations up to 30 June 2021 to compare with the data from  end May 2021 used for modules 1 and 2.\n",
        "\n",
        "FYI: between the last update done to the original source database on 29 Jun 21 and 01 Jul 21, 92.876 registrations were added to the OpenDataSus' database for the state of Santa Catarina.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kbMkOkAcW86k"
      },
      "source": [
        "used tip from this [link](https://www.geeksforgeeks.org/pandas-strip-whitespace-from-entire-dataframe/) to clear whitespace "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GqyEcp7bun6N"
      },
      "source": [
        "sc0207_07am = pd.read_csv('/content/drive/MyDrive/Alura DS Bootcamp/Modulo 3/M3 - Projeto/0207_07am.csv', sep =';', header = 0, skipinitialspace = True, names=['c1_document_id','c2_p_id','c3_p_idade','c4_p_dn','c5_p_sex','c6_p_r_codigo','c7_p_r_valor','c8_p_ibgemunicipio','c9_p_pais','c10_p_nmmunicipio','c11_p_endereco_nmpais','c12_p_uf','c13_p_cep','c14_p_enumnacionalidade','c15_e_valor','c16_e_razaosocial','c17_e_nofantasia','c18_e_municipio_codigo','c19_e_municipio_nome', 'c20_e_uf', 'c21_v_grupoatendimento_codigo','c22_v_grupoatendimento_nome','c23_v_categoria_codigo','c24_v_categoria_nome','c25_v_lote', 'c26_v_fabricante_nome','c27_v_fabricante_referencia','c28_v_dataaplicacao','c29_v_descricao_dose','c30_v_codigo','c31_v_nome','c32_sistema_origem','c33_data_importacao_rnds','c34_id_sistema_origem'])"
      ],
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Bpx6EwYgBmWm",
        "outputId": "ea14c801-7946-49f5-ebf6-f4c48ca69d89"
      },
      "source": [
        "sc0207_07am.shape"
      ],
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(3133344, 34)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZUhu8oW-YGsa"
      },
      "source": [
        "#<font color = 'brown'>IMPORTANT REMINDERS BEFORE WE MOVE ON: \n",
        "\n",
        " \n",
        "\n",
        "1.   Each row, or register, correspond to the application of one dose, either Dose 1 or Dose 2. Therefore the total of 3,133,344 registrations in this dataset does not mean 3,133,344 vaccinated people in Santa Catarina.\n",
        "\n",
        "2.   Per oficial [DIVE's boletim published on 30 June 21](http://www.dive.sc.gov.br/conteudos/boletim2021/corona48/boletim48.pdf) (but with last updated data from 26 Jun 21) the distribution betweeen doses 1 and dose 2 is:\n",
        "\n",
        "*   dose 1 -  2.259.615 (75%)\n",
        "*   dose 2 -    750.416 (25%)\n",
        "*   Total -   3.014.077 (so, not as updated as our dataset)\n",
        "\n",
        "\n",
        "3. The four covid19 vaccines used in Santa Catarina (SC) until 30 Jun 21 are:\n",
        "\n",
        "| Vaccine <br>Manufacturers \t| Nr of <br>doses \t| Actual <br>doses interval \t| Interval changed <br>during<br> the vaccination plan <br>from \t| Vaccine available<br> in SC since? \t|\n",
        "|:-:\t|:-:\t|:-:\t|:-:\t|:-:\t|\n",
        "| Coronavac_Butantan \t| 2 \t| 21 to 28 days \t| 14 Days \t| 18 Jan 21 \t|\n",
        "| AztraZeneca \t| 2 \t| 120 days \t| 28 days \t| 23 Jan 21 \t|\n",
        "| Pfizer \t| 2 \t| 120 days \t| No \t| 02 May 21 \t|\n",
        "| Janssen \t| 1 \t| NA \t| NA \t| 24 Jun 21 \t|\n",
        "\n",
        "\n",
        "The above table was easily created with a tool from this [source](https://www.tablesgenerator.com/markdown_tables) as suggested by tip from this [one](https://towardsdatascience.com/cheat-sheet-for-google-colab-63853778c093)\n",
        "\n",
        "For further information about the change of the vacination intervals [Folha de Sao Paulo - 05 May 2021](https://www1.folha.uol.com.br/equilibrioesaude/2021/05/veja-quais-sao-os-intervalos-entre-as-doses-das-vacinas-contra-covid-no-brasil-e-por-que-eles-sao-diferentes.shtml)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Ecm0anUe-h_h",
        "outputId": "bb643b48-ffb3-4274-86f1-a7313dcb0c6e"
      },
      "source": [
        "sc0207_07am.info()"
      ],
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 3133344 entries, 0 to 3133343\n",
            "Data columns (total 34 columns):\n",
            " #   Column                         Dtype  \n",
            "---  ------                         -----  \n",
            " 0   c1_document_id                 object \n",
            " 1   c2_p_id                        object \n",
            " 2   c3_p_idade                     int64  \n",
            " 3   c4_p_dn                        object \n",
            " 4   c5_p_sex                       object \n",
            " 5   c6_p_r_codigo                  int64  \n",
            " 6   c7_p_r_valor                   object \n",
            " 7   c8_p_ibgemunicipio             float64\n",
            " 8   c9_p_pais                      float64\n",
            " 9   c10_p_nmmunicipio              object \n",
            " 10  c11_p_endereco_nmpais          object \n",
            " 11  c12_p_uf                       object \n",
            " 12  c13_p_cep                      float64\n",
            " 13  c14_p_enumnacionalidade        object \n",
            " 14  c15_e_valor                    int64  \n",
            " 15  c16_e_razaosocial              object \n",
            " 16  c17_e_nofantasia               object \n",
            " 17  c18_e_municipio_codigo         int64  \n",
            " 18  c19_e_municipio_nome           object \n",
            " 19  c20_e_uf                       object \n",
            " 20  c21_v_grupoatendimento_codigo  int64  \n",
            " 21  c22_v_grupoatendimento_nome    object \n",
            " 22  c23_v_categoria_codigo         float64\n",
            " 23  c24_v_categoria_nome           object \n",
            " 24  c25_v_lote                     object \n",
            " 25  c26_v_fabricante_nome          object \n",
            " 26  c27_v_fabricante_referencia    object \n",
            " 27  c28_v_dataaplicacao            object \n",
            " 28  c29_v_descricao_dose           object \n",
            " 29  c30_v_codigo                   int64  \n",
            " 30  c31_v_nome                     object \n",
            " 31  c32_sistema_origem             object \n",
            " 32  c33_data_importacao_rnds       object \n",
            " 33  c34_id_sistema_origem          int64  \n",
            "dtypes: float64(4), int64(7), object(23)\n",
            "memory usage: 812.8+ MB\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 479
        },
        "id": "QRwLSOkg-6zm",
        "outputId": "ce491099-9517-465f-e518-7dce7f46b0b7"
      },
      "source": [
        "sc0207_07am.head()"
      ],
      "execution_count": 6,
      "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>c1_document_id</th>\n",
              "      <th>c2_p_id</th>\n",
              "      <th>c3_p_idade</th>\n",
              "      <th>c4_p_dn</th>\n",
              "      <th>c5_p_sex</th>\n",
              "      <th>c6_p_r_codigo</th>\n",
              "      <th>c7_p_r_valor</th>\n",
              "      <th>c8_p_ibgemunicipio</th>\n",
              "      <th>c9_p_pais</th>\n",
              "      <th>c10_p_nmmunicipio</th>\n",
              "      <th>c11_p_endereco_nmpais</th>\n",
              "      <th>c12_p_uf</th>\n",
              "      <th>c13_p_cep</th>\n",
              "      <th>c14_p_enumnacionalidade</th>\n",
              "      <th>c15_e_valor</th>\n",
              "      <th>c16_e_razaosocial</th>\n",
              "      <th>c17_e_nofantasia</th>\n",
              "      <th>c18_e_municipio_codigo</th>\n",
              "      <th>c19_e_municipio_nome</th>\n",
              "      <th>c20_e_uf</th>\n",
              "      <th>c21_v_grupoatendimento_codigo</th>\n",
              "      <th>c22_v_grupoatendimento_nome</th>\n",
              "      <th>c23_v_categoria_codigo</th>\n",
              "      <th>c24_v_categoria_nome</th>\n",
              "      <th>c25_v_lote</th>\n",
              "      <th>c26_v_fabricante_nome</th>\n",
              "      <th>c27_v_fabricante_referencia</th>\n",
              "      <th>c28_v_dataaplicacao</th>\n",
              "      <th>c29_v_descricao_dose</th>\n",
              "      <th>c30_v_codigo</th>\n",
              "      <th>c31_v_nome</th>\n",
              "      <th>c32_sistema_origem</th>\n",
              "      <th>c33_data_importacao_rnds</th>\n",
              "      <th>c34_id_sistema_origem</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>516d2087-4083-4e96-9a6b-0b7d921bc60b-i0b0</td>\n",
              "      <td>64158724acd2b0714ca51ca2748d1b285888d3618d60a2...</td>\n",
              "      <td>20</td>\n",
              "      <td>2000-11-09</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420650.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>GUARAMIRIM</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89270.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2300095</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE GUARAMIRIM</td>\n",
              "      <td>UNIDADE SANITARIA CENTRO</td>\n",
              "      <td>420650</td>\n",
              "      <td>GUARAMIRIM</td>\n",
              "      <td>SC</td>\n",
              "      <td>926</td>\n",
              "      <td>Outros</td>\n",
              "      <td>9.0</td>\n",
              "      <td>Trabalhadores de Saúde</td>\n",
              "      <td>213VCD040W</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>Organization/33781055000135</td>\n",
              "      <td>2021-05-27</td>\n",
              "      <td>2ª Dose</td>\n",
              "      <td>85</td>\n",
              "      <td>Vacina Covid-19 - Covishield</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-05-27T12:33:46.000Z</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>38d31763-5853-41bd-974d-da2693264d67-i0b0</td>\n",
              "      <td>b8eab30700ab2bee3a1c410af14f1b6ee4038c13921079...</td>\n",
              "      <td>85</td>\n",
              "      <td>1935-08-21</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420100.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>ANITA GARIBALDI</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88590.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2300397</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE ANITA GARIBALDI</td>\n",
              "      <td>UNIDADE SANITARIA CENTRAL DE ANITA GARIBALDI</td>\n",
              "      <td>420100</td>\n",
              "      <td>ANITA GARIBALDI</td>\n",
              "      <td>SC</td>\n",
              "      <td>205</td>\n",
              "      <td>Pessoas de 80 anos ou mais</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>4120Z027</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>Organization/33781055000135</td>\n",
              "      <td>2021-03-01</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-02T19:15:03.000Z</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>70620076-f2c4-4f47-b7b9-02d72320d5e5-i0b0</td>\n",
              "      <td>8eeac61d017d7e82e28aae7efdcc8ade5b99c81c1db466...</td>\n",
              "      <td>75</td>\n",
              "      <td>1945-07-19</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420820.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>ITAJAI</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88309.0</td>\n",
              "      <td>B</td>\n",
              "      <td>6539858</td>\n",
              "      <td>MUNICIPIO DE ITAJAI</td>\n",
              "      <td>SECRETARIA MUNICIPAL DE SAUDE DE ITAJAI</td>\n",
              "      <td>420820</td>\n",
              "      <td>ITAJAI</td>\n",
              "      <td>SC</td>\n",
              "      <td>204</td>\n",
              "      <td>Pessoas de 75 a 79 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>2101131</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-04-11</td>\n",
              "      <td>2ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>G-MUS</td>\n",
              "      <td>2021-04-21T20:06:48.000Z</td>\n",
              "      <td>17901</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>08ee6668-0ce1-45ef-99af-63e19c55c5a9-i0b0</td>\n",
              "      <td>a3b8717f2a5f31d930b91536775e45ed8d9d9c4619893c...</td>\n",
              "      <td>59</td>\n",
              "      <td>1961-06-04</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421240.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>PEDRAS GRANDES</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88720.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2689790</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE PEDRAS GRANDES</td>\n",
              "      <td>UNIDADE SANITARIA CENTRAL</td>\n",
              "      <td>421240</td>\n",
              "      <td>PEDRAS GRANDES</td>\n",
              "      <td>SC</td>\n",
              "      <td>106</td>\n",
              "      <td>Doenças Cardiovasculares e Cerebrovasculares</td>\n",
              "      <td>1.0</td>\n",
              "      <td>Comorbidades</td>\n",
              "      <td>214VCD057Z</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>Organization/33781055000135</td>\n",
              "      <td>2021-05-24</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-05-26T13:39:00.000Z</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>c90ace10-63c2-4c6a-8239-5665f830d8a5-i0b0</td>\n",
              "      <td>0c4b9df78b5a384a3b84604be8719a5b10624fe6e52878...</td>\n",
              "      <td>67</td>\n",
              "      <td>1953-07-22</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420240.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>BLUMENAU</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89026.0</td>\n",
              "      <td>B</td>\n",
              "      <td>7983409</td>\n",
              "      <td>MUNICIPIO DE BLUMENAU</td>\n",
              "      <td>VIGILANCIA EPIDEMIOLOGICA</td>\n",
              "      <td>420240</td>\n",
              "      <td>BLUMENAU</td>\n",
              "      <td>SC</td>\n",
              "      <td>301</td>\n",
              "      <td>Pessoas de 60 nos ou mais Institucionalizadas</td>\n",
              "      <td>3.0</td>\n",
              "      <td>Pessoas de 60 anos ou mais institucionalizadas</td>\n",
              "      <td>210148</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-04-26</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-05-05T17:42:00.000Z</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                              c1_document_id  ... c34_id_sistema_origem\n",
              "0  516d2087-4083-4e96-9a6b-0b7d921bc60b-i0b0  ...                 16341\n",
              "1  38d31763-5853-41bd-974d-da2693264d67-i0b0  ...                 16341\n",
              "2  70620076-f2c4-4f47-b7b9-02d72320d5e5-i0b0  ...                 17901\n",
              "3  08ee6668-0ce1-45ef-99af-63e19c55c5a9-i0b0  ...                 16341\n",
              "4  c90ace10-63c2-4c6a-8239-5665f830d8a5-i0b0  ...                 16341\n",
              "\n",
              "[5 rows x 34 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kio8DP2Micjz"
      },
      "source": [
        "## <Font color = 'green'>*Converted the 3 date's columns from object to datetime.*\n",
        "\n",
        "Tips for the conversion of c33 taken from this [link](https://www.statology.org/convert-datetime-to-date-pandas/)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zHKquj2wbH4b"
      },
      "source": [
        "sc0207_07am['c4_p_dn'] = pd.to_datetime(sc0207_07am['c4_p_dn'])\n",
        "sc0207_07am['c28_v_dataaplicacao'] = pd.to_datetime(sc0207_07am['c28_v_dataaplicacao'])\n",
        "sc0207_07am['c33_data_importacao_rnds'] = pd.to_datetime(sc0207_07am['c33_data_importacao_rnds']).dt.date\n",
        "sc0207_07am['c33_data_importacao_rnds'] = pd.to_datetime(sc0207_07am['c33_data_importacao_rnds']).dt.normalize()"
      ],
      "execution_count": 7,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 315
        },
        "id": "tYM8udK3g5OU",
        "outputId": "30d35c43-7f82-47bc-f3e9-619c62f31642"
      },
      "source": [
        "sc0207_07am.tail(3)"
      ],
      "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>c1_document_id</th>\n",
              "      <th>c2_p_id</th>\n",
              "      <th>c3_p_idade</th>\n",
              "      <th>c4_p_dn</th>\n",
              "      <th>c5_p_sex</th>\n",
              "      <th>c6_p_r_codigo</th>\n",
              "      <th>c7_p_r_valor</th>\n",
              "      <th>c8_p_ibgemunicipio</th>\n",
              "      <th>c9_p_pais</th>\n",
              "      <th>c10_p_nmmunicipio</th>\n",
              "      <th>c11_p_endereco_nmpais</th>\n",
              "      <th>c12_p_uf</th>\n",
              "      <th>c13_p_cep</th>\n",
              "      <th>c14_p_enumnacionalidade</th>\n",
              "      <th>c15_e_valor</th>\n",
              "      <th>c16_e_razaosocial</th>\n",
              "      <th>c17_e_nofantasia</th>\n",
              "      <th>c18_e_municipio_codigo</th>\n",
              "      <th>c19_e_municipio_nome</th>\n",
              "      <th>c20_e_uf</th>\n",
              "      <th>c21_v_grupoatendimento_codigo</th>\n",
              "      <th>c22_v_grupoatendimento_nome</th>\n",
              "      <th>c23_v_categoria_codigo</th>\n",
              "      <th>c24_v_categoria_nome</th>\n",
              "      <th>c25_v_lote</th>\n",
              "      <th>c26_v_fabricante_nome</th>\n",
              "      <th>c27_v_fabricante_referencia</th>\n",
              "      <th>c28_v_dataaplicacao</th>\n",
              "      <th>c29_v_descricao_dose</th>\n",
              "      <th>c30_v_codigo</th>\n",
              "      <th>c31_v_nome</th>\n",
              "      <th>c32_sistema_origem</th>\n",
              "      <th>c33_data_importacao_rnds</th>\n",
              "      <th>c34_id_sistema_origem</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>3133341</th>\n",
              "      <td>638cb145-03ad-467b-ba61-9882daa0e69f-i0b0</td>\n",
              "      <td>c09ecac4c281db5397a7715d1412c0c91fc0f404f167b4...</td>\n",
              "      <td>45</td>\n",
              "      <td>1975-09-01</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421190.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>PALHOCA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88136.0</td>\n",
              "      <td>B</td>\n",
              "      <td>6082378</td>\n",
              "      <td>PMP SECRETARIA MUNICIPAL DE SAUDE</td>\n",
              "      <td>SECRETARIA MUNICIPAL DE SAUDE PALHOCA</td>\n",
              "      <td>421190</td>\n",
              "      <td>PALHOCA</td>\n",
              "      <td>SC</td>\n",
              "      <td>103</td>\n",
              "      <td>Diabetes Mellitus</td>\n",
              "      <td>1.0</td>\n",
              "      <td>Comorbidades</td>\n",
              "      <td>EW0199</td>\n",
              "      <td>PFIZER/BIONTECH</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-05-21</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>87</td>\n",
              "      <td>Vacina covid-19 - BNT162b2 - BioNTech/Fosun Ph...</td>\n",
              "      <td>G-MUS - Gestão Municipal de Saúde</td>\n",
              "      <td>2021-05-21</td>\n",
              "      <td>17923</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3133342</th>\n",
              "      <td>38ca7827-ef71-4a24-9685-b2a42b0be15a-i0b0</td>\n",
              "      <td>bf9fbdd891092acc16c1e531064261f1b58211168f0d7e...</td>\n",
              "      <td>18</td>\n",
              "      <td>2002-11-27</td>\n",
              "      <td>M</td>\n",
              "      <td>2</td>\n",
              "      <td>PRETA</td>\n",
              "      <td>420290.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>BRUSQUE</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88354.0</td>\n",
              "      <td>B</td>\n",
              "      <td>7313667</td>\n",
              "      <td>MUNICIPIO DE BRUSQUE</td>\n",
              "      <td>UNIDADE DE SAUDE CENTRAL</td>\n",
              "      <td>420290</td>\n",
              "      <td>BRUSQUE</td>\n",
              "      <td>SC</td>\n",
              "      <td>402</td>\n",
              "      <td>Exército Brasileiro - EB</td>\n",
              "      <td>4.0</td>\n",
              "      <td>Forças Armadas (membros ativos)</td>\n",
              "      <td>FA7478</td>\n",
              "      <td>PFIZER/BIONTECH</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-06-03</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>87</td>\n",
              "      <td>Vacina covid-19 - BNT162b2 - BioNTech/Fosun Ph...</td>\n",
              "      <td>G-MUS</td>\n",
              "      <td>2021-06-03</td>\n",
              "      <td>18204</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3133343</th>\n",
              "      <td>63a51ead-7143-4968-b276-38c828053868-i0b0</td>\n",
              "      <td>51e26d0b211d35037ee226dff96b3dacff06c08937ae37...</td>\n",
              "      <td>56</td>\n",
              "      <td>1965-03-11</td>\n",
              "      <td>M</td>\n",
              "      <td>99</td>\n",
              "      <td>SEM INFORMACAO</td>\n",
              "      <td>420240.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>BLUMENAU</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89035.0</td>\n",
              "      <td>B</td>\n",
              "      <td>3112438</td>\n",
              "      <td>MUNICIPIO DE INDAIAL</td>\n",
              "      <td>ESTRATEGIA DE SAUDE DA FAMILIA REMO WENDORF I</td>\n",
              "      <td>420750</td>\n",
              "      <td>INDAIAL</td>\n",
              "      <td>SC</td>\n",
              "      <td>926</td>\n",
              "      <td>Outros</td>\n",
              "      <td>9.0</td>\n",
              "      <td>Trabalhadores de Saúde</td>\n",
              "      <td>214VCD057Z</td>\n",
              "      <td>OXFORD-ASTRAZENECA</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-05-18</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>85</td>\n",
              "      <td>Vacina Covid-19 - Covishield</td>\n",
              "      <td>IDS Saúde</td>\n",
              "      <td>2021-05-18</td>\n",
              "      <td>18467</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                    c1_document_id  ... c34_id_sistema_origem\n",
              "3133341  638cb145-03ad-467b-ba61-9882daa0e69f-i0b0  ...                 17923\n",
              "3133342  38ca7827-ef71-4a24-9685-b2a42b0be15a-i0b0  ...                 18204\n",
              "3133343  63a51ead-7143-4968-b276-38c828053868-i0b0  ...                 18467\n",
              "\n",
              "[3 rows x 34 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "M3a6blf8gwna",
        "outputId": "c85dc447-ea5c-491a-96d2-5196bd579f1a"
      },
      "source": [
        "sc0207_07am.info()"
      ],
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 3133344 entries, 0 to 3133343\n",
            "Data columns (total 34 columns):\n",
            " #   Column                         Dtype         \n",
            "---  ------                         -----         \n",
            " 0   c1_document_id                 object        \n",
            " 1   c2_p_id                        object        \n",
            " 2   c3_p_idade                     int64         \n",
            " 3   c4_p_dn                        datetime64[ns]\n",
            " 4   c5_p_sex                       object        \n",
            " 5   c6_p_r_codigo                  int64         \n",
            " 6   c7_p_r_valor                   object        \n",
            " 7   c8_p_ibgemunicipio             float64       \n",
            " 8   c9_p_pais                      float64       \n",
            " 9   c10_p_nmmunicipio              object        \n",
            " 10  c11_p_endereco_nmpais          object        \n",
            " 11  c12_p_uf                       object        \n",
            " 12  c13_p_cep                      float64       \n",
            " 13  c14_p_enumnacionalidade        object        \n",
            " 14  c15_e_valor                    int64         \n",
            " 15  c16_e_razaosocial              object        \n",
            " 16  c17_e_nofantasia               object        \n",
            " 17  c18_e_municipio_codigo         int64         \n",
            " 18  c19_e_municipio_nome           object        \n",
            " 19  c20_e_uf                       object        \n",
            " 20  c21_v_grupoatendimento_codigo  int64         \n",
            " 21  c22_v_grupoatendimento_nome    object        \n",
            " 22  c23_v_categoria_codigo         float64       \n",
            " 23  c24_v_categoria_nome           object        \n",
            " 24  c25_v_lote                     object        \n",
            " 25  c26_v_fabricante_nome          object        \n",
            " 26  c27_v_fabricante_referencia    object        \n",
            " 27  c28_v_dataaplicacao            datetime64[ns]\n",
            " 28  c29_v_descricao_dose           object        \n",
            " 29  c30_v_codigo                   int64         \n",
            " 30  c31_v_nome                     object        \n",
            " 31  c32_sistema_origem             object        \n",
            " 32  c33_data_importacao_rnds       datetime64[ns]\n",
            " 33  c34_id_sistema_origem          int64         \n",
            "dtypes: datetime64[ns](3), float64(4), int64(7), object(20)\n",
            "memory usage: 812.8+ MB\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "orYTSLtyyi2C"
      },
      "source": [
        "##<font color = 'green'>*Eliminated empty space and shortned the code for variable column 'c29_v_descricao_dose'*"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 479
        },
        "id": "2DrHYKsIyiUF",
        "outputId": "023f3963-0310-43c5-b401-703ea6f3e454"
      },
      "source": [
        "sc0207_07am.loc[sc0207_07am['c29_v_descricao_dose'] == '    1ª Dose', 'c29_v_descricao_dose'] = 'D1'\n",
        "sc0207_07am.loc[sc0207_07am['c29_v_descricao_dose'] == '    2ª Dose', 'c29_v_descricao_dose'] = 'D2'\n",
        "sc0207_07am.head()"
      ],
      "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>c1_document_id</th>\n",
              "      <th>c2_p_id</th>\n",
              "      <th>c3_p_idade</th>\n",
              "      <th>c4_p_dn</th>\n",
              "      <th>c5_p_sex</th>\n",
              "      <th>c6_p_r_codigo</th>\n",
              "      <th>c7_p_r_valor</th>\n",
              "      <th>c8_p_ibgemunicipio</th>\n",
              "      <th>c9_p_pais</th>\n",
              "      <th>c10_p_nmmunicipio</th>\n",
              "      <th>c11_p_endereco_nmpais</th>\n",
              "      <th>c12_p_uf</th>\n",
              "      <th>c13_p_cep</th>\n",
              "      <th>c14_p_enumnacionalidade</th>\n",
              "      <th>c15_e_valor</th>\n",
              "      <th>c16_e_razaosocial</th>\n",
              "      <th>c17_e_nofantasia</th>\n",
              "      <th>c18_e_municipio_codigo</th>\n",
              "      <th>c19_e_municipio_nome</th>\n",
              "      <th>c20_e_uf</th>\n",
              "      <th>c21_v_grupoatendimento_codigo</th>\n",
              "      <th>c22_v_grupoatendimento_nome</th>\n",
              "      <th>c23_v_categoria_codigo</th>\n",
              "      <th>c24_v_categoria_nome</th>\n",
              "      <th>c25_v_lote</th>\n",
              "      <th>c26_v_fabricante_nome</th>\n",
              "      <th>c27_v_fabricante_referencia</th>\n",
              "      <th>c28_v_dataaplicacao</th>\n",
              "      <th>c29_v_descricao_dose</th>\n",
              "      <th>c30_v_codigo</th>\n",
              "      <th>c31_v_nome</th>\n",
              "      <th>c32_sistema_origem</th>\n",
              "      <th>c33_data_importacao_rnds</th>\n",
              "      <th>c34_id_sistema_origem</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>516d2087-4083-4e96-9a6b-0b7d921bc60b-i0b0</td>\n",
              "      <td>64158724acd2b0714ca51ca2748d1b285888d3618d60a2...</td>\n",
              "      <td>20</td>\n",
              "      <td>2000-11-09</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420650.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>GUARAMIRIM</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89270.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2300095</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE GUARAMIRIM</td>\n",
              "      <td>UNIDADE SANITARIA CENTRO</td>\n",
              "      <td>420650</td>\n",
              "      <td>GUARAMIRIM</td>\n",
              "      <td>SC</td>\n",
              "      <td>926</td>\n",
              "      <td>Outros</td>\n",
              "      <td>9.0</td>\n",
              "      <td>Trabalhadores de Saúde</td>\n",
              "      <td>213VCD040W</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>Organization/33781055000135</td>\n",
              "      <td>2021-05-27</td>\n",
              "      <td>D2</td>\n",
              "      <td>85</td>\n",
              "      <td>Vacina Covid-19 - Covishield</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-05-27</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>38d31763-5853-41bd-974d-da2693264d67-i0b0</td>\n",
              "      <td>b8eab30700ab2bee3a1c410af14f1b6ee4038c13921079...</td>\n",
              "      <td>85</td>\n",
              "      <td>1935-08-21</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420100.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>ANITA GARIBALDI</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88590.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2300397</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE ANITA GARIBALDI</td>\n",
              "      <td>UNIDADE SANITARIA CENTRAL DE ANITA GARIBALDI</td>\n",
              "      <td>420100</td>\n",
              "      <td>ANITA GARIBALDI</td>\n",
              "      <td>SC</td>\n",
              "      <td>205</td>\n",
              "      <td>Pessoas de 80 anos ou mais</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>4120Z027</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>Organization/33781055000135</td>\n",
              "      <td>2021-03-01</td>\n",
              "      <td>D1</td>\n",
              "      <td>85</td>\n",
              "      <td>Vacina Covid-19 - Covishield</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-03-02</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>70620076-f2c4-4f47-b7b9-02d72320d5e5-i0b0</td>\n",
              "      <td>8eeac61d017d7e82e28aae7efdcc8ade5b99c81c1db466...</td>\n",
              "      <td>75</td>\n",
              "      <td>1945-07-19</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420820.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>ITAJAI</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88309.0</td>\n",
              "      <td>B</td>\n",
              "      <td>6539858</td>\n",
              "      <td>MUNICIPIO DE ITAJAI</td>\n",
              "      <td>SECRETARIA MUNICIPAL DE SAUDE DE ITAJAI</td>\n",
              "      <td>420820</td>\n",
              "      <td>ITAJAI</td>\n",
              "      <td>SC</td>\n",
              "      <td>204</td>\n",
              "      <td>Pessoas de 75 a 79 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>2101131</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-04-11</td>\n",
              "      <td>D2</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>G-MUS</td>\n",
              "      <td>2021-04-21</td>\n",
              "      <td>17901</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>08ee6668-0ce1-45ef-99af-63e19c55c5a9-i0b0</td>\n",
              "      <td>a3b8717f2a5f31d930b91536775e45ed8d9d9c4619893c...</td>\n",
              "      <td>59</td>\n",
              "      <td>1961-06-04</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421240.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>PEDRAS GRANDES</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88720.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2689790</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE PEDRAS GRANDES</td>\n",
              "      <td>UNIDADE SANITARIA CENTRAL</td>\n",
              "      <td>421240</td>\n",
              "      <td>PEDRAS GRANDES</td>\n",
              "      <td>SC</td>\n",
              "      <td>106</td>\n",
              "      <td>Doenças Cardiovasculares e Cerebrovasculares</td>\n",
              "      <td>1.0</td>\n",
              "      <td>Comorbidades</td>\n",
              "      <td>214VCD057Z</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>Organization/33781055000135</td>\n",
              "      <td>2021-05-24</td>\n",
              "      <td>D1</td>\n",
              "      <td>85</td>\n",
              "      <td>Vacina Covid-19 - Covishield</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-05-26</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>c90ace10-63c2-4c6a-8239-5665f830d8a5-i0b0</td>\n",
              "      <td>0c4b9df78b5a384a3b84604be8719a5b10624fe6e52878...</td>\n",
              "      <td>67</td>\n",
              "      <td>1953-07-22</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420240.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>BLUMENAU</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89026.0</td>\n",
              "      <td>B</td>\n",
              "      <td>7983409</td>\n",
              "      <td>MUNICIPIO DE BLUMENAU</td>\n",
              "      <td>VIGILANCIA EPIDEMIOLOGICA</td>\n",
              "      <td>420240</td>\n",
              "      <td>BLUMENAU</td>\n",
              "      <td>SC</td>\n",
              "      <td>301</td>\n",
              "      <td>Pessoas de 60 nos ou mais Institucionalizadas</td>\n",
              "      <td>3.0</td>\n",
              "      <td>Pessoas de 60 anos ou mais institucionalizadas</td>\n",
              "      <td>210148</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-04-26</td>\n",
              "      <td>D2</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-05-05</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                              c1_document_id  ... c34_id_sistema_origem\n",
              "0  516d2087-4083-4e96-9a6b-0b7d921bc60b-i0b0  ...                 16341\n",
              "1  38d31763-5853-41bd-974d-da2693264d67-i0b0  ...                 16341\n",
              "2  70620076-f2c4-4f47-b7b9-02d72320d5e5-i0b0  ...                 17901\n",
              "3  08ee6668-0ce1-45ef-99af-63e19c55c5a9-i0b0  ...                 16341\n",
              "4  c90ace10-63c2-4c6a-8239-5665f830d8a5-i0b0  ...                 16341\n",
              "\n",
              "[5 rows x 34 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oE6SCVTcDWId"
      },
      "source": [
        "## <Font color = 'green'>*Sorting the entire dataset based on dates of the variable 'c28_v_dataaplicacao'*"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "id": "lW3CBia_DVxv",
        "outputId": "ef684ada-a602-47d4-a9f2-f524c2576e36"
      },
      "source": [
        "sc0207_07am.sort_values(by='c28_v_dataaplicacao', ascending=True, inplace=True)\n",
        "sc0207_07am"
      ],
      "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>c1_document_id</th>\n",
              "      <th>c2_p_id</th>\n",
              "      <th>c3_p_idade</th>\n",
              "      <th>c4_p_dn</th>\n",
              "      <th>c5_p_sex</th>\n",
              "      <th>c6_p_r_codigo</th>\n",
              "      <th>c7_p_r_valor</th>\n",
              "      <th>c8_p_ibgemunicipio</th>\n",
              "      <th>c9_p_pais</th>\n",
              "      <th>c10_p_nmmunicipio</th>\n",
              "      <th>c11_p_endereco_nmpais</th>\n",
              "      <th>c12_p_uf</th>\n",
              "      <th>c13_p_cep</th>\n",
              "      <th>c14_p_enumnacionalidade</th>\n",
              "      <th>c15_e_valor</th>\n",
              "      <th>c16_e_razaosocial</th>\n",
              "      <th>c17_e_nofantasia</th>\n",
              "      <th>c18_e_municipio_codigo</th>\n",
              "      <th>c19_e_municipio_nome</th>\n",
              "      <th>c20_e_uf</th>\n",
              "      <th>c21_v_grupoatendimento_codigo</th>\n",
              "      <th>c22_v_grupoatendimento_nome</th>\n",
              "      <th>c23_v_categoria_codigo</th>\n",
              "      <th>c24_v_categoria_nome</th>\n",
              "      <th>c25_v_lote</th>\n",
              "      <th>c26_v_fabricante_nome</th>\n",
              "      <th>c27_v_fabricante_referencia</th>\n",
              "      <th>c28_v_dataaplicacao</th>\n",
              "      <th>c29_v_descricao_dose</th>\n",
              "      <th>c30_v_codigo</th>\n",
              "      <th>c31_v_nome</th>\n",
              "      <th>c32_sistema_origem</th>\n",
              "      <th>c33_data_importacao_rnds</th>\n",
              "      <th>c34_id_sistema_origem</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>340733</th>\n",
              "      <td>4af998fc-ba82-40e6-bfd4-d2fcde965c29-i0b0</td>\n",
              "      <td>537c73cbf959a7ade3fe86d87aa7ab9f8b1de3ad55a0b0...</td>\n",
              "      <td>55</td>\n",
              "      <td>1965-10-26</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421660.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>SAO JOSE</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88103.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2622726</td>\n",
              "      <td>PREF MUN FLORIANOPOLIS SMS FMS</td>\n",
              "      <td>CS CANASVIEIRAS</td>\n",
              "      <td>420540</td>\n",
              "      <td>FLORIANOPOLIS</td>\n",
              "      <td>SC</td>\n",
              "      <td>923</td>\n",
              "      <td>Técnico de Enfermagem</td>\n",
              "      <td>9.0</td>\n",
              "      <td>Trabalhadores de Saúde</td>\n",
              "      <td>202010036</td>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2020-12-21</td>\n",
              "      <td>D1</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-04-26</td>\n",
              "      <td>18602</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2878375</th>\n",
              "      <td>c0622be3-b4a1-4e4f-9c21-8e0e0cc7f7da-i0b0</td>\n",
              "      <td>8ba3755c79ba0f8bbb14ac74e74116c9b2b4cd9ba13cb6...</td>\n",
              "      <td>35</td>\n",
              "      <td>1985-03-17</td>\n",
              "      <td>F</td>\n",
              "      <td>3</td>\n",
              "      <td>PARDA</td>\n",
              "      <td>421660.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>SAO JOSE</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88106.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2622726</td>\n",
              "      <td>PREF MUN FLORIANOPOLIS SMS FMS</td>\n",
              "      <td>CS CANASVIEIRAS</td>\n",
              "      <td>420540</td>\n",
              "      <td>FLORIANOPOLIS</td>\n",
              "      <td>SC</td>\n",
              "      <td>905</td>\n",
              "      <td>Cuidador de Idosos</td>\n",
              "      <td>9.0</td>\n",
              "      <td>Trabalhadores de Saúde</td>\n",
              "      <td>202010036</td>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2020-12-21</td>\n",
              "      <td>D1</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-04-25</td>\n",
              "      <td>18602</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>461906</th>\n",
              "      <td>d041ac6e-fdfd-4875-944e-6d2c4cc79191-i0b0</td>\n",
              "      <td>6e5a239adbcd1b2cea00f85873bc3d12521eb58564d489...</td>\n",
              "      <td>37</td>\n",
              "      <td>1983-05-18</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421500.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>RIO NEGRINHO</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89295.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2510987</td>\n",
              "      <td>FUNDO MUNICIPAL DE SAUDE DE RIO NEGRINHO</td>\n",
              "      <td>CENTRO INTEGRADO DE SAUDE</td>\n",
              "      <td>421500</td>\n",
              "      <td>RIO NEGRINHO</td>\n",
              "      <td>SC</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>202010036</td>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2020-12-22</td>\n",
              "      <td>D1</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-03-12</td>\n",
              "      <td>18602</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3023718</th>\n",
              "      <td>818361b6-9747-435d-8bad-5d079290656f-i0b0</td>\n",
              "      <td>3717784b0535b1db0d866d510059a48dbc486892b1770a...</td>\n",
              "      <td>46</td>\n",
              "      <td>1974-03-02</td>\n",
              "      <td>F</td>\n",
              "      <td>99</td>\n",
              "      <td>SEM INFORMACAO</td>\n",
              "      <td>421660.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>SAO JOSE</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>NaN</td>\n",
              "      <td>B</td>\n",
              "      <td>2663805</td>\n",
              "      <td>SECRETARIA MUNICIPAL DE SAUDE DE SAO JOSE</td>\n",
              "      <td>CENTRO DE SAUDE PICADAS DO SUL</td>\n",
              "      <td>421660</td>\n",
              "      <td>SAO JOSE</td>\n",
              "      <td>SC</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>200279</td>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-01-11</td>\n",
              "      <td>D1</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-02-22</td>\n",
              "      <td>18602</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>127931</th>\n",
              "      <td>c9c8dc1f-d435-423d-94d0-5f68fe9b00e9-i0b0</td>\n",
              "      <td>b8d8268a51de519cb024ce8c94bfa1a13221f73d44d4b1...</td>\n",
              "      <td>91</td>\n",
              "      <td>1929-07-04</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421660.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>SAO JOSE</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88111.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2663805</td>\n",
              "      <td>SECRETARIA MUNICIPAL DE SAUDE DE SAO JOSE</td>\n",
              "      <td>CENTRO DE SAUDE PICADAS DO SUL</td>\n",
              "      <td>421660</td>\n",
              "      <td>SAO JOSE</td>\n",
              "      <td>SC</td>\n",
              "      <td>205</td>\n",
              "      <td>Pessoas de 80 anos ou mais</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210015</td>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-01-11</td>\n",
              "      <td>D1</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-03-30</td>\n",
              "      <td>18602</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",
              "      <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>2196808</th>\n",
              "      <td>ff4af52d-c6bc-4471-9b01-3522b6515697-i0b0</td>\n",
              "      <td>0e25dbe30f74c7dbdf3dae00e4d4db8a1a83a32b751e5c...</td>\n",
              "      <td>52</td>\n",
              "      <td>1968-11-02</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421005.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>MACIEIRA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89518.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2302705</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE MACIEIRA</td>\n",
              "      <td>POSTO DE SAUDE DE MACIEIRA</td>\n",
              "      <td>421005</td>\n",
              "      <td>MACIEIRA</td>\n",
              "      <td>SC</td>\n",
              "      <td>106</td>\n",
              "      <td>Doenças Cardiovasculares e Cerebrovasculares</td>\n",
              "      <td>1.0</td>\n",
              "      <td>Comorbidades</td>\n",
              "      <td>210282</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>D2</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2511442</th>\n",
              "      <td>6653c3fd-00a2-43b9-b0a4-e8b69ffdb3a7-i0b0</td>\n",
              "      <td>d6500954078bfd99ddfe2dbdfb664771d3b04923dc2380...</td>\n",
              "      <td>48</td>\n",
              "      <td>1972-07-14</td>\n",
              "      <td>F</td>\n",
              "      <td>4</td>\n",
              "      <td>AMARELA</td>\n",
              "      <td>420320.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CAMBORIU</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88348.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2303515</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE CAMBORIU</td>\n",
              "      <td>UNIDADE SAUDE DA FAMILIA TABULEIRO</td>\n",
              "      <td>420320</td>\n",
              "      <td>CAMBORIU</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 18 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>215VCD125W</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>Organization/33781055000135</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>D1</td>\n",
              "      <td>85</td>\n",
              "      <td>Vacina Covid-19 - Covishield</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1115673</th>\n",
              "      <td>98d36b1f-4410-4c22-a36f-417e2b9eefd3-i0b0</td>\n",
              "      <td>5b6610df0d1c168dd8682d5d5c0fc1c35d3f23e626826e...</td>\n",
              "      <td>47</td>\n",
              "      <td>1973-11-13</td>\n",
              "      <td>M</td>\n",
              "      <td>2</td>\n",
              "      <td>PRETA</td>\n",
              "      <td>421890.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>URUBICI</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88650.0</td>\n",
              "      <td>B</td>\n",
              "      <td>6066577</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE URUBICI</td>\n",
              "      <td>UNIDADE BASICA DE SAUDE ENO VIEIRA DE ANDRADE</td>\n",
              "      <td>421890</td>\n",
              "      <td>URUBICI</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 18 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210282</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>D1</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1115671</th>\n",
              "      <td>886a748a-219d-4f0a-8174-7553c7439326-i0b0</td>\n",
              "      <td>5d9dca698af7de73ed939b8378727bc2eb104d3bcab66a...</td>\n",
              "      <td>52</td>\n",
              "      <td>1969-01-17</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420210.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>BARRA VELHA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88390.0</td>\n",
              "      <td>B</td>\n",
              "      <td>4057678</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE BARRA VELHA</td>\n",
              "      <td>UBSF SERTAOZINHO</td>\n",
              "      <td>420210</td>\n",
              "      <td>BARRA VELHA</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 18 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>FA9090</td>\n",
              "      <td>MINISTERIO DA SAUDE</td>\n",
              "      <td>Organization/00394544000851</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>D1</td>\n",
              "      <td>87</td>\n",
              "      <td>Vacina covid-19 - BNT162b2 - BioNTech/Fosun Ph...</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10814</th>\n",
              "      <td>ac8003b3-702f-4a7b-b284-acf576b829d2-i0b0</td>\n",
              "      <td>1e26a0b7f23650def2b74bfc994f753196cba746e74dc6...</td>\n",
              "      <td>53</td>\n",
              "      <td>1967-11-15</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421750.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>SEARA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89770.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2555476</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE SEARA</td>\n",
              "      <td>POSTO DE SAUDE BAIRRO SAO JOAO</td>\n",
              "      <td>421750</td>\n",
              "      <td>SEARA</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 18 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210A21A</td>\n",
              "      <td>MINISTERIO DA SAUDE</td>\n",
              "      <td>Organization/00394544000851</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>Dose</td>\n",
              "      <td>88</td>\n",
              "      <td>Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>3133344 rows × 34 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                                    c1_document_id  ... c34_id_sistema_origem\n",
              "340733   4af998fc-ba82-40e6-bfd4-d2fcde965c29-i0b0  ...                 18602\n",
              "2878375  c0622be3-b4a1-4e4f-9c21-8e0e0cc7f7da-i0b0  ...                 18602\n",
              "461906   d041ac6e-fdfd-4875-944e-6d2c4cc79191-i0b0  ...                 18602\n",
              "3023718  818361b6-9747-435d-8bad-5d079290656f-i0b0  ...                 18602\n",
              "127931   c9c8dc1f-d435-423d-94d0-5f68fe9b00e9-i0b0  ...                 18602\n",
              "...                                            ...  ...                   ...\n",
              "2196808  ff4af52d-c6bc-4471-9b01-3522b6515697-i0b0  ...                 16341\n",
              "2511442  6653c3fd-00a2-43b9-b0a4-e8b69ffdb3a7-i0b0  ...                 16341\n",
              "1115673  98d36b1f-4410-4c22-a36f-417e2b9eefd3-i0b0  ...                 16341\n",
              "1115671  886a748a-219d-4f0a-8174-7553c7439326-i0b0  ...                 16341\n",
              "10814    ac8003b3-702f-4a7b-b284-acf576b829d2-i0b0  ...                 16341\n",
              "\n",
              "[3133344 rows x 34 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "T_BSNRm5oW1L"
      },
      "source": [
        "# <Font color ='blue'> **Phase 2 - Adding a new column in preparation for the dataset for analysis**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UDN9cGEc6wTI"
      },
      "source": [
        "# <Font color = 'green'>**Step 3** _ Adding a column identifying the UDVE of each register (row) in the dataset.\n",
        "\n",
        "SC has 295 counties. [DIVE](http://www.dive.sc.gov.br), the local state instituition in charge of the distribution and tracking of the covid19 vaccines applications, shared the 295 in 17 UDVEs (Decentralized Epidemiological Surveillance Units in english).\n",
        "\n",
        "Usually DIVE refers to its UDVEs by the name of the main city in the region an UDVE covers. \n",
        "\n",
        "To facilitate the coding for this project I renamed the UDVEs with a number, where 01 starts on the most western side of the state of SC and double digits ones the eastern (or costal) regions. \n",
        "\n",
        "These numbers tried to match the number used for regionales by another state institution helping coordinate the covid19 vaccinations in the state, called [COSENS SC](https://www.cosemssc.org.br/regioes_de_saude/). \n",
        "\n",
        "Tried because as you may notice, DIVE is organised in 17 UDVE regions and COSEMS SC in 16 regions and their regions borders not always match. How that helps, I do not know."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "C4wFC2XiMw9H"
      },
      "source": [
        "<Font color = 'green'>Based on tip from this [source](https://stackoverflow.com/questions/63558830/how-to-create-a-new-column-in-pandas-and-add-value-to-that-new-column-based-on-t) managed to succesfully create a new column labelled 'c35_SC_UDVE_Nr', where each register got an identification for its UDVE based on the county code where the vaccine shot was applied."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6yNhsGVwM-iX"
      },
      "source": [
        "def get_udve(row):\n",
        "    if row ['c18_e_municipio_codigo'] in [420080,420208,420209,420215,420490,420500,420535,420640,420660,420765,420840,421100,421200,421223,421415,421507,421520,421555,421625,421670,421720,421875]:\n",
        "        return 'udve01'\n",
        "    elif row['c18_e_municipio_codigo'] in [420010,420253,420445,420517,420530,420560,420768,420917,420945,421055,421185,421227,421340,421610,421910,421950,421970]:\n",
        "        return 'udve02'\n",
        "    elif row['c18_e_municipio_codigo'] in [420050,420055,420165,420257,420310,420350,420410,420420,420435,420440,420470,420475,420543,420665,420775,420785,420895,421050,421090,421140,421145,421165,421187,421210,421290,421315,421420,421535,421568,421569,421575,421600,421690,421715,421730,421755,421775,421795,421885]:\n",
        "        return 'udve03'\n",
        "    elif row['c18_e_municipio_codigo'] in [420075,420127,420430,420760,420770,420780,420800,420985,421260,421310,421390,421750,421960]:\n",
        "        return 'udve04'\n",
        "    elif row['c18_e_municipio_codigo'] in [420005,420040,420287,420360,420390,420400,420415,420520,420670,420680,420860,420900,420920,421003,421105,421180,421790,421850,421915,421917,421985]:\n",
        "        return 'udve05'\n",
        "    elif row['c18_e_municipio_codigo'] in [420160,420300,420315,420480,420550,420555,420675,420757,420970,421005,421070,421300,421335,421440,421540,421550,421605,421825,421930]:\n",
        "        return 'udve06'\n",
        "    elif row['c18_e_municipio_codigo'] in [420213,420330,420380,420790,420810,421010,421030,421110,421220,421360,421500,421580,421830]:\n",
        "        return 'udve07'\n",
        "    elif row['c18_e_municipio_codigo'] in [420450,420650,420890,421060,421740]:\n",
        "        return 'udve08'\n",
        "    elif row['c18_e_municipio_codigo'] in [420130,420205,420210,420580,420845,420910,421620,421635]:\n",
        "        return 'udve09'\n",
        "    elif row['c18_e_municipio_codigo'] in [420200,420245,420320,420710,420820,420830,421000,421130,421250,421280,421350]:\n",
        "        return 'udve10'\n",
        "    elif row['c18_e_municipio_codigo'] in [420020,420030,420180,420190,420285,420419,420510,420690,420740,420850,420915,420950,420990,421085,421270,421370,421400,421410,421450,421460,421480,421530,421567,421780,421860,421920,421935,421940]:\n",
        "        return 'udve11'\n",
        "    elif row['c18_e_municipio_codigo'] in [420125,420170,420220,420240,420270,420290,420515,420590,420630,420750,421320,421470,421510,421820]:\n",
        "        return 'udve12'\n",
        "    elif row['c18_e_municipio_codigo'] in [420060,420070,420090,420110,420120,420230,420370,420540,420570,420600,420980,421020,421150,421190,421230,421430,421570,421590,421630,421660,421725,421800]:\n",
        "        return 'udve13'\n",
        "    elif row['c18_e_municipio_codigo'] in [420100,420243,420250,420260,420325,420340,420417,420455,420930,421175,421189,421205,421330,421505,421650,421680,421890,421895]:\n",
        "        return 'udve14'\n",
        "    elif row['c18_e_municipio_codigo'] in [420150,420280,420395,420610,420620,420720,420730,420880,420940,421240,421265,421490,421545,421560,421700,421710,421840,421870]:\n",
        "        return 'udve15'\n",
        "    elif row['c18_e_municipio_codigo'] in [420425,420460,420545,420700,420960,421120,421160,421170,421760,421835,421900,42200]:\n",
        "        return 'udve16'\n",
        "    elif row['c18_e_municipio_codigo'] in [420140,420195,420207,420519,420870,421040,421080,421125,421225,421380,421565,421640,421770,421810,421880]:\n",
        "        return 'udve17'\n"
      ],
      "execution_count": 12,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LrgQtXGVSi6N"
      },
      "source": [
        "sc0207_07am['c35_SC_UDVE_Nr'] = sc0207_07am.apply(get_udve, axis = 1)"
      ],
      "execution_count": 13,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zkQhVkXXnRzE"
      },
      "source": [
        "sc0207_07am_udve = sc0207_07am"
      ],
      "execution_count": 14,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 479
        },
        "id": "wJejN3ulUPWW",
        "outputId": "ab318211-e029-42ce-fd30-ab2fb83c7fd2"
      },
      "source": [
        "sc0207_07am_udve.head()"
      ],
      "execution_count": 15,
      "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>c1_document_id</th>\n",
              "      <th>c2_p_id</th>\n",
              "      <th>c3_p_idade</th>\n",
              "      <th>c4_p_dn</th>\n",
              "      <th>c5_p_sex</th>\n",
              "      <th>c6_p_r_codigo</th>\n",
              "      <th>c7_p_r_valor</th>\n",
              "      <th>c8_p_ibgemunicipio</th>\n",
              "      <th>c9_p_pais</th>\n",
              "      <th>c10_p_nmmunicipio</th>\n",
              "      <th>c11_p_endereco_nmpais</th>\n",
              "      <th>c12_p_uf</th>\n",
              "      <th>c13_p_cep</th>\n",
              "      <th>c14_p_enumnacionalidade</th>\n",
              "      <th>c15_e_valor</th>\n",
              "      <th>c16_e_razaosocial</th>\n",
              "      <th>c17_e_nofantasia</th>\n",
              "      <th>c18_e_municipio_codigo</th>\n",
              "      <th>c19_e_municipio_nome</th>\n",
              "      <th>c20_e_uf</th>\n",
              "      <th>c21_v_grupoatendimento_codigo</th>\n",
              "      <th>c22_v_grupoatendimento_nome</th>\n",
              "      <th>c23_v_categoria_codigo</th>\n",
              "      <th>c24_v_categoria_nome</th>\n",
              "      <th>c25_v_lote</th>\n",
              "      <th>c26_v_fabricante_nome</th>\n",
              "      <th>c27_v_fabricante_referencia</th>\n",
              "      <th>c28_v_dataaplicacao</th>\n",
              "      <th>c29_v_descricao_dose</th>\n",
              "      <th>c30_v_codigo</th>\n",
              "      <th>c31_v_nome</th>\n",
              "      <th>c32_sistema_origem</th>\n",
              "      <th>c33_data_importacao_rnds</th>\n",
              "      <th>c34_id_sistema_origem</th>\n",
              "      <th>c35_SC_UDVE_Nr</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>340733</th>\n",
              "      <td>4af998fc-ba82-40e6-bfd4-d2fcde965c29-i0b0</td>\n",
              "      <td>537c73cbf959a7ade3fe86d87aa7ab9f8b1de3ad55a0b0...</td>\n",
              "      <td>55</td>\n",
              "      <td>1965-10-26</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421660.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>SAO JOSE</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88103.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2622726</td>\n",
              "      <td>PREF MUN FLORIANOPOLIS SMS FMS</td>\n",
              "      <td>CS CANASVIEIRAS</td>\n",
              "      <td>420540</td>\n",
              "      <td>FLORIANOPOLIS</td>\n",
              "      <td>SC</td>\n",
              "      <td>923</td>\n",
              "      <td>Técnico de Enfermagem</td>\n",
              "      <td>9.0</td>\n",
              "      <td>Trabalhadores de Saúde</td>\n",
              "      <td>202010036</td>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2020-12-21</td>\n",
              "      <td>D1</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-04-26</td>\n",
              "      <td>18602</td>\n",
              "      <td>udve13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2878375</th>\n",
              "      <td>c0622be3-b4a1-4e4f-9c21-8e0e0cc7f7da-i0b0</td>\n",
              "      <td>8ba3755c79ba0f8bbb14ac74e74116c9b2b4cd9ba13cb6...</td>\n",
              "      <td>35</td>\n",
              "      <td>1985-03-17</td>\n",
              "      <td>F</td>\n",
              "      <td>3</td>\n",
              "      <td>PARDA</td>\n",
              "      <td>421660.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>SAO JOSE</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88106.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2622726</td>\n",
              "      <td>PREF MUN FLORIANOPOLIS SMS FMS</td>\n",
              "      <td>CS CANASVIEIRAS</td>\n",
              "      <td>420540</td>\n",
              "      <td>FLORIANOPOLIS</td>\n",
              "      <td>SC</td>\n",
              "      <td>905</td>\n",
              "      <td>Cuidador de Idosos</td>\n",
              "      <td>9.0</td>\n",
              "      <td>Trabalhadores de Saúde</td>\n",
              "      <td>202010036</td>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2020-12-21</td>\n",
              "      <td>D1</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-04-25</td>\n",
              "      <td>18602</td>\n",
              "      <td>udve13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>461906</th>\n",
              "      <td>d041ac6e-fdfd-4875-944e-6d2c4cc79191-i0b0</td>\n",
              "      <td>6e5a239adbcd1b2cea00f85873bc3d12521eb58564d489...</td>\n",
              "      <td>37</td>\n",
              "      <td>1983-05-18</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421500.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>RIO NEGRINHO</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89295.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2510987</td>\n",
              "      <td>FUNDO MUNICIPAL DE SAUDE DE RIO NEGRINHO</td>\n",
              "      <td>CENTRO INTEGRADO DE SAUDE</td>\n",
              "      <td>421500</td>\n",
              "      <td>RIO NEGRINHO</td>\n",
              "      <td>SC</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>202010036</td>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2020-12-22</td>\n",
              "      <td>D1</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-03-12</td>\n",
              "      <td>18602</td>\n",
              "      <td>udve07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3023718</th>\n",
              "      <td>818361b6-9747-435d-8bad-5d079290656f-i0b0</td>\n",
              "      <td>3717784b0535b1db0d866d510059a48dbc486892b1770a...</td>\n",
              "      <td>46</td>\n",
              "      <td>1974-03-02</td>\n",
              "      <td>F</td>\n",
              "      <td>99</td>\n",
              "      <td>SEM INFORMACAO</td>\n",
              "      <td>421660.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>SAO JOSE</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>NaN</td>\n",
              "      <td>B</td>\n",
              "      <td>2663805</td>\n",
              "      <td>SECRETARIA MUNICIPAL DE SAUDE DE SAO JOSE</td>\n",
              "      <td>CENTRO DE SAUDE PICADAS DO SUL</td>\n",
              "      <td>421660</td>\n",
              "      <td>SAO JOSE</td>\n",
              "      <td>SC</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>200279</td>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-01-11</td>\n",
              "      <td>D1</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-02-22</td>\n",
              "      <td>18602</td>\n",
              "      <td>udve13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>127931</th>\n",
              "      <td>c9c8dc1f-d435-423d-94d0-5f68fe9b00e9-i0b0</td>\n",
              "      <td>b8d8268a51de519cb024ce8c94bfa1a13221f73d44d4b1...</td>\n",
              "      <td>91</td>\n",
              "      <td>1929-07-04</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421660.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>SAO JOSE</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88111.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2663805</td>\n",
              "      <td>SECRETARIA MUNICIPAL DE SAUDE DE SAO JOSE</td>\n",
              "      <td>CENTRO DE SAUDE PICADAS DO SUL</td>\n",
              "      <td>421660</td>\n",
              "      <td>SAO JOSE</td>\n",
              "      <td>SC</td>\n",
              "      <td>205</td>\n",
              "      <td>Pessoas de 80 anos ou mais</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210015</td>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-01-11</td>\n",
              "      <td>D1</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-03-30</td>\n",
              "      <td>18602</td>\n",
              "      <td>udve13</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                    c1_document_id  ... c35_SC_UDVE_Nr\n",
              "340733   4af998fc-ba82-40e6-bfd4-d2fcde965c29-i0b0  ...         udve13\n",
              "2878375  c0622be3-b4a1-4e4f-9c21-8e0e0cc7f7da-i0b0  ...         udve13\n",
              "461906   d041ac6e-fdfd-4875-944e-6d2c4cc79191-i0b0  ...         udve07\n",
              "3023718  818361b6-9747-435d-8bad-5d079290656f-i0b0  ...         udve13\n",
              "127931   c9c8dc1f-d435-423d-94d0-5f68fe9b00e9-i0b0  ...         udve13\n",
              "\n",
              "[5 rows x 35 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "tX8gaXlIngZj",
        "outputId": "457f12e3-3a51-4b05-8290-92c8e957c60d"
      },
      "source": [
        "sc0207_07am_udve.info()"
      ],
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 3133344 entries, 340733 to 10814\n",
            "Data columns (total 35 columns):\n",
            " #   Column                         Dtype         \n",
            "---  ------                         -----         \n",
            " 0   c1_document_id                 object        \n",
            " 1   c2_p_id                        object        \n",
            " 2   c3_p_idade                     int64         \n",
            " 3   c4_p_dn                        datetime64[ns]\n",
            " 4   c5_p_sex                       object        \n",
            " 5   c6_p_r_codigo                  int64         \n",
            " 6   c7_p_r_valor                   object        \n",
            " 7   c8_p_ibgemunicipio             float64       \n",
            " 8   c9_p_pais                      float64       \n",
            " 9   c10_p_nmmunicipio              object        \n",
            " 10  c11_p_endereco_nmpais          object        \n",
            " 11  c12_p_uf                       object        \n",
            " 12  c13_p_cep                      float64       \n",
            " 13  c14_p_enumnacionalidade        object        \n",
            " 14  c15_e_valor                    int64         \n",
            " 15  c16_e_razaosocial              object        \n",
            " 16  c17_e_nofantasia               object        \n",
            " 17  c18_e_municipio_codigo         int64         \n",
            " 18  c19_e_municipio_nome           object        \n",
            " 19  c20_e_uf                       object        \n",
            " 20  c21_v_grupoatendimento_codigo  int64         \n",
            " 21  c22_v_grupoatendimento_nome    object        \n",
            " 22  c23_v_categoria_codigo         float64       \n",
            " 23  c24_v_categoria_nome           object        \n",
            " 24  c25_v_lote                     object        \n",
            " 25  c26_v_fabricante_nome          object        \n",
            " 26  c27_v_fabricante_referencia    object        \n",
            " 27  c28_v_dataaplicacao            datetime64[ns]\n",
            " 28  c29_v_descricao_dose           object        \n",
            " 29  c30_v_codigo                   int64         \n",
            " 30  c31_v_nome                     object        \n",
            " 31  c32_sistema_origem             object        \n",
            " 32  c33_data_importacao_rnds       datetime64[ns]\n",
            " 33  c34_id_sistema_origem          int64         \n",
            " 34  c35_SC_UDVE_Nr                 object        \n",
            "dtypes: datetime64[ns](3), float64(4), int64(7), object(21)\n",
            "memory usage: 860.6+ MB\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xIIS2bVSNJrz"
      },
      "source": [
        "#<font color = 'blue'>**Phase 3 - Identifying and formally defining 'Combinations of Concern' (CoC)**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uZZ5u-2INt35"
      },
      "source": [
        "# <Font Color='green'>**Step 4** - Identifying and counting the different combinations between the 4 variables that allow identication of which covid19 vaccine an individual received.\n",
        "\n",
        "| Column name                               | Column name in English    | comment                       |\n",
        "|-------------|-----------------------------|---------------------------|\n",
        "|<Font color ='brown'>c26_v_fabricante_nome | c26_vaccine manufacturer  | \n",
        "|<Font color ='brown'>c30_v_codigo          | c30_v_code                |\n",
        "|<Font color ='brown'>c31_v_nome            | c31_v_name                |\n",
        "|<Font color ='brown'>c32_sistema_origem    | c32_source_system         | system used at local level to enter registration data\n",
        "\n",
        "\n",
        "The process used in this step was done based on the learning gathered from this [source](https://stackoverflow.com/questions/35268817/unique-combinations-of-values-in-selected-columns-in-pandas-data-frame-and-count)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "46KpvC8iNs8M",
        "outputId": "46470422-7c6d-492d-a0d3-01ab865aa613"
      },
      "source": [
        "v_fabri_codigo_nome_sysorig_sc0207_07am_udve = sc0207_07am_udve[['c26_v_fabricante_nome','c30_v_codigo','c31_v_nome', 'c32_sistema_origem',]].info()"
      ],
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 3133344 entries, 340733 to 10814\n",
            "Data columns (total 4 columns):\n",
            " #   Column                 Dtype \n",
            "---  ------                 ----- \n",
            " 0   c26_v_fabricante_nome  object\n",
            " 1   c30_v_codigo           int64 \n",
            " 2   c31_v_nome             object\n",
            " 3   c32_sistema_origem     object\n",
            "dtypes: int64(1), object(3)\n",
            "memory usage: 119.5+ MB\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        },
        "id": "8M7f5lHQSqtQ",
        "outputId": "4ed2b2fa-ade9-44bd-8328-e962e0c50d87"
      },
      "source": [
        "v_fabri_codigo_nome_sysorig_sc0207_07am_udve = sc0207_07am_udve[['c26_v_fabricante_nome','c30_v_codigo','c31_v_nome','c32_sistema_origem']]\n",
        "v_fabri_codigo_nome_sysorig_sc0207_07am_udve"
      ],
      "execution_count": 18,
      "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>c26_v_fabricante_nome</th>\n",
              "      <th>c30_v_codigo</th>\n",
              "      <th>c31_v_nome</th>\n",
              "      <th>c32_sistema_origem</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>340733</th>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2878375</th>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>461906</th>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3023718</th>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>127931</th>\n",
              "      <td>SERUM INSTITUTE OF INDIA LTD</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2196808</th>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2511442</th>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>85</td>\n",
              "      <td>Vacina Covid-19 - Covishield</td>\n",
              "      <td>Novo PNI</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1115673</th>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1115671</th>\n",
              "      <td>MINISTERIO DA SAUDE</td>\n",
              "      <td>87</td>\n",
              "      <td>Vacina covid-19 - BNT162b2 - BioNTech/Fosun Ph...</td>\n",
              "      <td>Novo PNI</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10814</th>\n",
              "      <td>MINISTERIO DA SAUDE</td>\n",
              "      <td>88</td>\n",
              "      <td>Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag</td>\n",
              "      <td>Novo PNI</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>3133344 rows × 4 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                c26_v_fabricante_nome  ...             c32_sistema_origem\n",
              "340733   SERUM INSTITUTE OF INDIA LTD  ...  ESUS APS - NACIONAL (OFFLINE)\n",
              "2878375  SERUM INSTITUTE OF INDIA LTD  ...  ESUS APS - NACIONAL (OFFLINE)\n",
              "461906   SERUM INSTITUTE OF INDIA LTD  ...  ESUS APS - NACIONAL (OFFLINE)\n",
              "3023718  SERUM INSTITUTE OF INDIA LTD  ...  ESUS APS - NACIONAL (OFFLINE)\n",
              "127931   SERUM INSTITUTE OF INDIA LTD  ...  ESUS APS - NACIONAL (OFFLINE)\n",
              "...                               ...  ...                            ...\n",
              "2196808             FUNDACAO BUTANTAN  ...                       Novo PNI\n",
              "2511442         FUNDACAO OSWALDO CRUZ  ...                       Novo PNI\n",
              "1115673             FUNDACAO BUTANTAN  ...                       Novo PNI\n",
              "1115671           MINISTERIO DA SAUDE  ...                       Novo PNI\n",
              "10814             MINISTERIO DA SAUDE  ...                       Novo PNI\n",
              "\n",
              "[3133344 rows x 4 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Ven-J1FtQyx7",
        "outputId": "96400d60-4c3f-4906-85d5-ec779d720712"
      },
      "source": [
        "v_fabri_codigo_nome_sysorig_sc0207_07am_udve.groupby(['c26_v_fabricante_nome','c30_v_codigo','c31_v_nome','c32_sistema_origem']).size()"
      ],
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "c26_v_fabricante_nome                    c30_v_codigo  c31_v_nome                                                 c32_sistema_origem               \n",
              "ASTRAZENECA AB                           85            Vacina Covid-19 - Covishield                               G-MUS                                    755\n",
              "                                                                                                                  G-MUS - Gestão Municipal de Saúde       9493\n",
              "ASTRAZENECA FARMACEUTICA DO BRASIL LTDA  85            Vacina Covid-19 - Covishield                               IPM istemas LTDA                        2348\n",
              "BUTANTAN                                 86            Covid-19-Coronavac-Sinovac/Butantan                        IPM istemas LTDA                         202\n",
              "BioNTech/Fosun Pharma/Pfizer             87            Vacina covid-19 - BNT162b2 - BioNTech/Fosun Pharma/Pfizer  ESUS APS - NACIONAL (OFFLINE)          21651\n",
              "FUNDACAO BUTANTAN                        86            Covid-19-Coronavac-Sinovac/Butantan                        G-MUS                                  68462\n",
              "                                                                                                                  G-MUS - Gestão Municipal de Saúde      39339\n",
              "                                                                                                                  IDS Saúde                                 34\n",
              "                                                                                                                  Novo PNI                             1011188\n",
              "                                                                                                                  Saudetech                              37979\n",
              "                                                                                                                  saudetech                               4840\n",
              "FUNDACAO OSWALDO CRUZ                    85            Vacina Covid-19 - Covishield                               G-MUS                                  66043\n",
              "                                                                                                                  G-MUS - Gestão Municipal de Saúde      35683\n",
              "                                                                                                                  IDS Saúde                               3664\n",
              "                                                                                                                  Novo PNI                              979119\n",
              "                                                                                                                  Saudetech                              48301\n",
              "                                                                                                                  saudetech                              15865\n",
              "                                         86            Covid-19-Coronavac-Sinovac/Butantan                        IDS Saúde                                  2\n",
              "                                                                                                                  Novo PNI                                  12\n",
              "                                         88            Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag              IDS Saúde                                  1\n",
              "JANSSEN-CILAG                            88            Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag              IDS SAUDE                                176\n",
              "                                                                                                                  Saudetech                               1391\n",
              "Janssen Biologics B.V.                   88            Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag              G-MUS - Gestão Municipal de Saúde       1329\n",
              "Janssen-Cilag                            88            Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag              ESUS APS - NACIONAL (OFFLINE)           2115\n",
              "MINISTERIO DA SAUDE                      87            Vacina covid-19 - BNT162b2 - BioNTech/Fosun Pharma/Pfizer  Novo PNI                              171280\n",
              "                                         88            Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag              Novo PNI                               14984\n",
              "                                         89            Covid-19-AstraZeneca                                       Novo PNI                              117213\n",
              "OXFORD-ASTRAZENECA                       85            Vacina Covid-19 - Covishield                               IDS SAUDE                               4105\n",
              "                                                                                                                  IDS Saúde                              28444\n",
              "                                         86            Covid-19-Coronavac-Sinovac/Butantan                        IDS Saúde                                  9\n",
              "                                         87            Vacina covid-19 - BNT162b2 - BioNTech/Fosun Pharma/Pfizer  IDS Saúde                                  2\n",
              "PFIZER                                   87            Vacina covid-19 - BNT162b2 - BioNTech/Fosun Pharma/Pfizer  IPM istemas LTDA                         350\n",
              "PFIZER/BIONTECH                          86            Covid-19-Coronavac-Sinovac/Butantan                        IDS Saúde                                  1\n",
              "                                         87            Vacina covid-19 - BNT162b2 - BioNTech/Fosun Pharma/Pfizer  G-MUS                                  13941\n",
              "                                                                                                                  G-MUS - Gestão Municipal de Saúde      16860\n",
              "                                                                                                                  IDS SAUDE                                828\n",
              "                                                                                                                  IDS Saúde                               3269\n",
              "                                                                                                                  Saudetech                              15071\n",
              "                                                                                                                  saudetech                               2561\n",
              "SERUM INSTITUTE OF INDIA LTD             86            Covid-19-Coronavac-Sinovac/Butantan                        ESUS APS - NACIONAL (OFFLINE)         223321\n",
              "SERUM INSTITUTE OF INDIA LTD.            85            Vacina Covid-19 - Covishield                               G-MUS                                     65\n",
              "                                                                                                                  IDS Saúde                                  1\n",
              "SINOVAC LIFE SCIENCE CO LTD              85            Vacina Covid-19 - Covishield                               ESUS APS - NACIONAL (OFFLINE)         144865\n",
              "SINOVAC/BUTANTA                          81            Pendente Identificação                                     IDS Saúde                                436\n",
              "                                         85            Vacina Covid-19 - Covishield                               IDS Saúde                                  6\n",
              "                                         86            Covid-19-Coronavac-Sinovac/Butantan                        IDS Saúde                               7754\n",
              "SINOVAC/BUTANTAN                         86            Covid-19-Coronavac-Sinovac/Butantan                        IDS SAUDE                               3715\n",
              "                                                                                                                  IDS Saúde                              14271\n",
              "dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yH9cfFY0sBqt"
      },
      "source": [
        "# <Font color = 'green'>**Step 5** - Defining and creating a new column ['c_36_Combination_of_Concern'] that identify and classify 'combinations of concern' based on their frequency.\n",
        "\n",
        "#<Font color = 'brown'>**Definition of Combination of Concern(CoC)'**: within an individual vaccination registration, any combination between the 4 variables above mentioned that would raise doubt about which covid vaccine an individual received. \n",
        "\n",
        "Per example: 'SERUM INSTITUTE OF INDIA LTD' is ONE OF the manufacturers across the globe for AztraZeneca(AZ) covid 19 related vaccines. AZ covid19 vaccines produced by SERUM are known as ['COVISHIELD'](https://www.bbc.com/news/health-57695835).  That's inconsistent with a 'c31_v_nome' = 'Covid-19-Coronavac-Sinovac/Butantan' in the same register, as this last one is manufactured by Coronavac China and/or Butantan Brasil.\n",
        "\n",
        "For more details about identification of covid19 vaccine manufacturers and vaccine names please check references at these [ANVISA source]((https://www.gov.br/anvisa/pt-br/assuntos/paf/coronavirus/vacinas)) re-accessed on 26 Jun 2021.\n",
        "\n",
        "# <font color ='brown'> Coding 'Combinations of Concern' based on their frequency.\n",
        "\n",
        "*   <Font color = 'brown'>**CoC_1** = Very High Frequency - more than 100,000 registers\n",
        "*   <Font color = 'brown'>**CoC_2** = High Frequency - between 10,000 and 100,000 registers\n",
        "*   <Font color = 'brown'>**CoC_3** = 'Low' Frequency - less than 10,000 registers\n",
        "*   <Font color = 'brown'>**'no_issue'** = At best knowledge of this author no remarkble issue identified at the time this project was prepared ( 30 June 2021)\n",
        "\n",
        "<font color = 'brown'>**Author's note:** Strictly speaking, from the Pharmacovigilance point of view, 'no issue' is not strictly correct. But it will fulfill for the moment the purpose of this part of the work. More details in the conclusion at the end.\n",
        "\n",
        "The set of codes here after I was inspired by tips from this [source](https://towardsdatascience.com/the-ultimate-guide-for-column-creation-with-pandas-dataframes-83b8c565110e)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2d_fJWICmmrz"
      },
      "source": [
        "def combination_of_concern(row):\n",
        "    if row['c26_v_fabricante_nome'] == 'SERUM INSTITUTE OF INDIA LTD' and row['c30_v_codigo'] == 86 and row['c31_v_nome'] == 'Covid-19-Coronavac-Sinovac/Butantan':\n",
        "        return 'CoC_1'\n",
        "    elif row['c26_v_fabricante_nome'] == 'SINOVAC LIFE SCIENCE CO LTD' and row['c30_v_codigo'] == 85 and row['c31_v_nome'] == 'Vacina Covid-19 - Covishield':\n",
        "        return 'CoC_1'\n",
        "    elif row['c26_v_fabricante_nome'] == 'MINISTERIO DA SAUDE' and row['c30_v_codigo'] == 89 and row['c31_v_nome'] == 'Covid-19-AstraZeneca ':\n",
        "        return 'CoC_1'\n",
        "    elif row['c26_v_fabricante_nome'] == 'SINOVAC/BUTANTA' and row['c30_v_codigo'] == 85 and row['c31_v_nome'] == 'Vacina Covid-19 - Covishield':\n",
        "        return 'CoC_3'\n",
        "    elif row['c26_v_fabricante_nome'] == 'SINOVAC/BUTANTA' and row['c30_v_codigo'] == 81 and row['c31_v_nome'] == 'Pendente Identificação':\n",
        "        return 'CoC_3'\n",
        "    elif row['c26_v_fabricante_nome'] == 'FUNDACAO OSWALDO CRUZ' and row['c30_v_codigo'] == 86 and row['c31_v_nome'] == 'Covid-19-Coronavac-Sinovac/Butantan':\n",
        "        return 'CoC_3'\n",
        "    elif row['c26_v_fabricante_nome'] == 'FUNDACAO OSWALDO CRUZ' and row['c30_v_codigo'] == 88 and row['c31_v_nome'] == 'Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag':\n",
        "        return 'CoC_3'\n",
        "    elif row['c26_v_fabricante_nome'] == 'OXFORD-ASTRAZENECA' and row['c30_v_codigo'] == 86 and row['c31_v_nome'] == 'Covid-19-Coronavac-Sinovac/Butantan':\n",
        "        return 'CoC_3'\n",
        "    elif row['c26_v_fabricante_nome'] == 'OXFORD-ASTRAZENECA' and row['c30_v_codigo'] == 87 and row['c31_v_nome'] == 'Vacina covid-19 - BNT162b2 - BioNTech/Fosun Ph...':\n",
        "        return 'CoC_3'\n",
        "    elif row['c26_v_fabricante_nome'] == 'BioNTech/Fosun Pharma/Pfizer' and row['c30_v_codigo'] == 87 and row['c31_v_nome'] == 'Vacina covid-19 - BNT162b2 - BioNTech/Fosun Ph...':\n",
        "        return 'CoC_3'\n",
        "    elif row['c26_v_fabricante_nome'] == 'PFIZER/BIONTECH' and row['c30_v_codigo'] == 86 and row['c31_v_nome'] == 'Covid-19-Coronavac-Sinovac/Butantan':\n",
        "        return 'CoC_3'\n",
        "    elif row['c26_v_fabricante_nome'] == 'INTERVAX LTDA' and row['c30_v_codigo'] == 85 and row['c31_v_nome'] == 'Vacina Covid-19 - Covishield':\n",
        "        return 'CoC_3'\n",
        "    else:\n",
        "        return 'no_issue'\n",
        "     "
      ],
      "execution_count": 20,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oQPR4WD360hg"
      },
      "source": [
        "#<font color = 'brown'> When compared to last previous update on 30 May 2021, we notice the following changes in the 'Combination of Concern' categories:\n",
        "\n",
        "*   <font color = 'blue'> A significant increase on the nr of CoC_1 groups (1 to 3). The previously two CoC_2 groups from 30 May 21 significantly increased in frequency and therefore shifted to CoC_1 (''SINOVAC LIFE SCIENCE CO LTD' ...) and ('MINISTERIO DA SAUDE' ... )\n",
        "*   <font color = 'blue'> No new CoC_2 group in June (there was 2 in May);\n",
        "*   <font color = 'blue'> No new group added to the CoC_3 category in June. Same profile of the C0C_3 groups recorded last May.\n",
        "\n",
        "The data above mays suggest that clean up of the database is not happening in systematic and large scale.\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VL-ncW_iykzT"
      },
      "source": [
        "sc0207_07am_udve['c_36_Combination_of_Concern'] = sc0207_07am_udve.apply(combination_of_concern, axis=1)"
      ],
      "execution_count": 21,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Yc_72JsiPzwz"
      },
      "source": [
        "sc0207_07am_udve_CoC = sc0207_07am_udve"
      ],
      "execution_count": 22,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "b-HUAnYkQBQr",
        "outputId": "1c39c437-18aa-499c-ab98-7befa13abe0f"
      },
      "source": [
        "sc0207_07am_udve_CoC.c_36_Combination_of_Concern.unique()"
      ],
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array(['CoC_1', 'no_issue', 'CoC_3'], dtype=object)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 462
        },
        "id": "2shASYKpUOHJ",
        "outputId": "1023356f-70b4-4015-f3f8-259247d5ff28"
      },
      "source": [
        "sc0207_07am_udve_CoC.tail()"
      ],
      "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>c1_document_id</th>\n",
              "      <th>c2_p_id</th>\n",
              "      <th>c3_p_idade</th>\n",
              "      <th>c4_p_dn</th>\n",
              "      <th>c5_p_sex</th>\n",
              "      <th>c6_p_r_codigo</th>\n",
              "      <th>c7_p_r_valor</th>\n",
              "      <th>c8_p_ibgemunicipio</th>\n",
              "      <th>c9_p_pais</th>\n",
              "      <th>c10_p_nmmunicipio</th>\n",
              "      <th>c11_p_endereco_nmpais</th>\n",
              "      <th>c12_p_uf</th>\n",
              "      <th>c13_p_cep</th>\n",
              "      <th>c14_p_enumnacionalidade</th>\n",
              "      <th>c15_e_valor</th>\n",
              "      <th>c16_e_razaosocial</th>\n",
              "      <th>c17_e_nofantasia</th>\n",
              "      <th>c18_e_municipio_codigo</th>\n",
              "      <th>c19_e_municipio_nome</th>\n",
              "      <th>c20_e_uf</th>\n",
              "      <th>c21_v_grupoatendimento_codigo</th>\n",
              "      <th>c22_v_grupoatendimento_nome</th>\n",
              "      <th>c23_v_categoria_codigo</th>\n",
              "      <th>c24_v_categoria_nome</th>\n",
              "      <th>c25_v_lote</th>\n",
              "      <th>c26_v_fabricante_nome</th>\n",
              "      <th>c27_v_fabricante_referencia</th>\n",
              "      <th>c28_v_dataaplicacao</th>\n",
              "      <th>c29_v_descricao_dose</th>\n",
              "      <th>c30_v_codigo</th>\n",
              "      <th>c31_v_nome</th>\n",
              "      <th>c32_sistema_origem</th>\n",
              "      <th>c33_data_importacao_rnds</th>\n",
              "      <th>c34_id_sistema_origem</th>\n",
              "      <th>c35_SC_UDVE_Nr</th>\n",
              "      <th>c_36_Combination_of_Concern</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>2196808</th>\n",
              "      <td>ff4af52d-c6bc-4471-9b01-3522b6515697-i0b0</td>\n",
              "      <td>0e25dbe30f74c7dbdf3dae00e4d4db8a1a83a32b751e5c...</td>\n",
              "      <td>52</td>\n",
              "      <td>1968-11-02</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421005.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>MACIEIRA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89518.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2302705</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE MACIEIRA</td>\n",
              "      <td>POSTO DE SAUDE DE MACIEIRA</td>\n",
              "      <td>421005</td>\n",
              "      <td>MACIEIRA</td>\n",
              "      <td>SC</td>\n",
              "      <td>106</td>\n",
              "      <td>Doenças Cardiovasculares e Cerebrovasculares</td>\n",
              "      <td>1.0</td>\n",
              "      <td>Comorbidades</td>\n",
              "      <td>210282</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>D2</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>16341</td>\n",
              "      <td>udve06</td>\n",
              "      <td>no_issue</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2511442</th>\n",
              "      <td>6653c3fd-00a2-43b9-b0a4-e8b69ffdb3a7-i0b0</td>\n",
              "      <td>d6500954078bfd99ddfe2dbdfb664771d3b04923dc2380...</td>\n",
              "      <td>48</td>\n",
              "      <td>1972-07-14</td>\n",
              "      <td>F</td>\n",
              "      <td>4</td>\n",
              "      <td>AMARELA</td>\n",
              "      <td>420320.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CAMBORIU</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88348.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2303515</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE CAMBORIU</td>\n",
              "      <td>UNIDADE SAUDE DA FAMILIA TABULEIRO</td>\n",
              "      <td>420320</td>\n",
              "      <td>CAMBORIU</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 18 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>215VCD125W</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>Organization/33781055000135</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>D1</td>\n",
              "      <td>85</td>\n",
              "      <td>Vacina Covid-19 - Covishield</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>16341</td>\n",
              "      <td>udve10</td>\n",
              "      <td>no_issue</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1115673</th>\n",
              "      <td>98d36b1f-4410-4c22-a36f-417e2b9eefd3-i0b0</td>\n",
              "      <td>5b6610df0d1c168dd8682d5d5c0fc1c35d3f23e626826e...</td>\n",
              "      <td>47</td>\n",
              "      <td>1973-11-13</td>\n",
              "      <td>M</td>\n",
              "      <td>2</td>\n",
              "      <td>PRETA</td>\n",
              "      <td>421890.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>URUBICI</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88650.0</td>\n",
              "      <td>B</td>\n",
              "      <td>6066577</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE URUBICI</td>\n",
              "      <td>UNIDADE BASICA DE SAUDE ENO VIEIRA DE ANDRADE</td>\n",
              "      <td>421890</td>\n",
              "      <td>URUBICI</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 18 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210282</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>D1</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>16341</td>\n",
              "      <td>udve14</td>\n",
              "      <td>no_issue</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1115671</th>\n",
              "      <td>886a748a-219d-4f0a-8174-7553c7439326-i0b0</td>\n",
              "      <td>5d9dca698af7de73ed939b8378727bc2eb104d3bcab66a...</td>\n",
              "      <td>52</td>\n",
              "      <td>1969-01-17</td>\n",
              "      <td>F</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420210.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>BARRA VELHA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88390.0</td>\n",
              "      <td>B</td>\n",
              "      <td>4057678</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE BARRA VELHA</td>\n",
              "      <td>UBSF SERTAOZINHO</td>\n",
              "      <td>420210</td>\n",
              "      <td>BARRA VELHA</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 18 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>FA9090</td>\n",
              "      <td>MINISTERIO DA SAUDE</td>\n",
              "      <td>Organization/00394544000851</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>D1</td>\n",
              "      <td>87</td>\n",
              "      <td>Vacina covid-19 - BNT162b2 - BioNTech/Fosun Ph...</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>16341</td>\n",
              "      <td>udve09</td>\n",
              "      <td>no_issue</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10814</th>\n",
              "      <td>ac8003b3-702f-4a7b-b284-acf576b829d2-i0b0</td>\n",
              "      <td>1e26a0b7f23650def2b74bfc994f753196cba746e74dc6...</td>\n",
              "      <td>53</td>\n",
              "      <td>1967-11-15</td>\n",
              "      <td>M</td>\n",
              "      <td>1</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421750.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>SEARA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89770.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2555476</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE SEARA</td>\n",
              "      <td>POSTO DE SAUDE BAIRRO SAO JOAO</td>\n",
              "      <td>421750</td>\n",
              "      <td>SEARA</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 18 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210A21A</td>\n",
              "      <td>MINISTERIO DA SAUDE</td>\n",
              "      <td>Organization/00394544000851</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>Dose</td>\n",
              "      <td>88</td>\n",
              "      <td>Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-06-30</td>\n",
              "      <td>16341</td>\n",
              "      <td>udve04</td>\n",
              "      <td>no_issue</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                    c1_document_id  ... c_36_Combination_of_Concern\n",
              "2196808  ff4af52d-c6bc-4471-9b01-3522b6515697-i0b0  ...                    no_issue\n",
              "2511442  6653c3fd-00a2-43b9-b0a4-e8b69ffdb3a7-i0b0  ...                    no_issue\n",
              "1115673  98d36b1f-4410-4c22-a36f-417e2b9eefd3-i0b0  ...                    no_issue\n",
              "1115671  886a748a-219d-4f0a-8174-7553c7439326-i0b0  ...                    no_issue\n",
              "10814    ac8003b3-702f-4a7b-b284-acf576b829d2-i0b0  ...                    no_issue\n",
              "\n",
              "[5 rows x 36 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 24
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UxEWg2DYDpEh"
      },
      "source": [
        "# <Font color ='blue'>**Phase 4 - Crosstabing UDVE and CoC at county level**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "D2NmBIaEC3H6"
      },
      "source": [
        "# <Font color = 'green'>**Step 6** - Crosstabing UDVE and CoC\n",
        "\n",
        "Tips from Alura curso 'Estatistica com Python parte 1: Frequencias e Medidas - aula 2 - Distribuicao de frequencia + for formating right - [source](https://stackoverflow.com/questions/17232013/how-to-set-the-pandas-dataframe-data-left-right-alignment) and [source](https://pbpython.com/styling-pandas.html)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 418
        },
        "id": "1Jwo700GCDnP",
        "outputId": "855b6a55-122f-4fc6-e039-6d61233e981b"
      },
      "source": [
        "udve_CoC = pd.crosstab(sc0207_07am_udve_CoC.c35_SC_UDVE_Nr , sc0207_07am_udve_CoC.c_36_Combination_of_Concern)\n",
        "def highlight(s):\n",
        "    if s.CoC_1 > 100000:\n",
        "        return ['background-color: red'] * 3\n",
        "    elif (s.CoC_1 > 10000) and (s.CoC_1 < 100000):\n",
        "        return ['background-color: orange'] * 3\n",
        "    elif (s.CoC_1 > 1000) and (s.CoC_1 < 10000):\n",
        "        return ['background-color: yellow'] * 3\n",
        "    else:\n",
        "        return ['background-color: white'] * 3\n",
        "\n",
        "udve_CoC.style.set_properties(**{'text-align': 'right'}).format('{0:,.0f}').apply(highlight, axis=1)\n"
      ],
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<style  type=\"text/css\" >\n",
              "#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row0_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row0_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row0_col2,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row1_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row1_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row1_col2,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row4_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row4_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row4_col2,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row11_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row11_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row11_col2,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row13_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row13_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row13_col2,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row14_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row14_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row14_col2,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row15_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row15_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row15_col2{\n",
              "            text-align:  right;\n",
              "            background-color:  yellow;\n",
              "        }#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row2_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row2_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row2_col2,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row5_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row5_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row5_col2,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row6_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row6_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row6_col2,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row8_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row8_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row8_col2,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row9_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row9_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row9_col2{\n",
              "            text-align:  right;\n",
              "            background-color:  orange;\n",
              "        }#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row3_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row3_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row3_col2,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row7_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row7_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row7_col2,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row10_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row10_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row10_col2,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row16_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row16_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row16_col2{\n",
              "            text-align:  right;\n",
              "            background-color:  white;\n",
              "        }#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row12_col0,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row12_col1,#T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row12_col2{\n",
              "            text-align:  right;\n",
              "            background-color:  red;\n",
              "        }</style><table id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002\" ><thead>    <tr>        <th class=\"index_name level0\" >c_36_Combination_of_Concern</th>        <th class=\"col_heading level0 col0\" >CoC_1</th>        <th class=\"col_heading level0 col1\" >CoC_3</th>        <th class=\"col_heading level0 col2\" >no_issue</th>    </tr>    <tr>        <th class=\"index_name level0\" >c35_SC_UDVE_Nr</th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>    </tr></thead><tbody>\n",
              "                <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row0\" class=\"row_heading level0 row0\" >udve01</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row0_col0\" class=\"data row0 col0\" >3,001</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row0_col1\" class=\"data row0 col1\" >0</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row0_col2\" class=\"data row0 col2\" >104,380</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row1\" class=\"row_heading level0 row1\" >udve02</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row1_col0\" class=\"data row1 col0\" >8,944</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row1_col1\" class=\"data row1 col1\" >0</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row1_col2\" class=\"data row1 col2\" >64,017</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row2\" class=\"row_heading level0 row2\" >udve03</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row2_col0\" class=\"data row2 col0\" >16,789</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row2_col1\" class=\"data row2 col1\" >0</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row2_col2\" class=\"data row2 col2\" >235,006</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row3\" class=\"row_heading level0 row3\" >udve04</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row3_col0\" class=\"data row3 col0\" >52</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row3_col1\" class=\"data row3 col1\" >0</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row3_col2\" class=\"data row3 col2\" >76,558</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row4\" class=\"row_heading level0 row4\" >udve05</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row4_col0\" class=\"data row4 col0\" >1,579</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row4_col1\" class=\"data row4 col1\" >0</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row4_col2\" class=\"data row4 col2\" >102,668</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row5\" class=\"row_heading level0 row5\" >udve06</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row5_col0\" class=\"data row5 col0\" >21,537</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row5_col1\" class=\"data row5 col1\" >0</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row5_col2\" class=\"data row5 col2\" >102,344</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row6\" class=\"row_heading level0 row6\" >udve07</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row6_col0\" class=\"data row6 col0\" >31,151</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row6_col1\" class=\"data row6 col1\" >27</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row6_col2\" class=\"data row6 col2\" >114,479</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row7\" class=\"row_heading level0 row7\" >udve08</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row7_col0\" class=\"data row7 col0\" >0</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row7_col1\" class=\"data row7 col1\" >0</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row7_col2\" class=\"data row7 col2\" >104,834</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row8\" class=\"row_heading level0 row8\" >udve09</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row8_col0\" class=\"data row8 col0\" >21,845</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row8_col1\" class=\"data row8 col1\" >1</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row8_col2\" class=\"data row8 col2\" >304,253</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row9\" class=\"row_heading level0 row9\" >udve10</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row9_col0\" class=\"data row9 col0\" >19,058</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row9_col1\" class=\"data row9 col1\" >0</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row9_col2\" class=\"data row9 col2\" >262,760</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row10\" class=\"row_heading level0 row10\" >udve11</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row10_col0\" class=\"data row10 col0\" >56</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row10_col1\" class=\"data row10 col1\" >0</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row10_col2\" class=\"data row10 col2\" >136,373</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row11\" class=\"row_heading level0 row11\" >udve12</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row11_col0\" class=\"data row11 col0\" >5,468</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row11_col1\" class=\"data row11 col1\" >436</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row11_col2\" class=\"data row11 col2\" >326,218</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row12\" class=\"row_heading level0 row12\" >udve13</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row12_col0\" class=\"data row12 col0\" >229,208</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row12_col1\" class=\"data row12 col1\" >0</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row12_col2\" class=\"data row12 col2\" >218,256</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row13\" class=\"row_heading level0 row13\" >udve14</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row13_col0\" class=\"data row13 col0\" >2,541</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row13_col1\" class=\"data row13 col1\" >0</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row13_col2\" class=\"data row13 col2\" >153,942</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row14\" class=\"row_heading level0 row14\" >udve15</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row14_col0\" class=\"data row14 col0\" >3,892</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row14_col1\" class=\"data row14 col1\" >0</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row14_col2\" class=\"data row14 col2\" >178,643</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row15\" class=\"row_heading level0 row15\" >udve16</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row15_col0\" class=\"data row15 col0\" >2,765</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row15_col1\" class=\"data row15 col1\" >0</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row15_col2\" class=\"data row15 col2\" >178,370</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002level0_row16\" class=\"row_heading level0 row16\" >udve17</th>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row16_col0\" class=\"data row16 col0\" >300</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row16_col1\" class=\"data row16 col1\" >3</td>\n",
              "                        <td id=\"T_b95f421c_dd09_11eb_aaf2_0242ac1c0002row16_col2\" class=\"data row16 col2\" >94,372</td>\n",
              "            </tr>\n",
              "    </tbody></table>"
            ],
            "text/plain": [
              "<pandas.io.formats.style.Styler at 0x7f868b4f6690>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZLlDO3ixf7DR"
      },
      "source": [
        "Tip from [source](https://pandas.pydata.org/pandas-docs/stable/user_guide/style.html) (Finer Control: Display Values)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "q1x2m7R-54GP"
      },
      "source": [
        "# <font color = 'green'>Proportions of CoCs categories per UDVE \n",
        "\n",
        "tip [source](https://stackoverflow.com/questions/50820659/compute-row-percentages-in-pandas-dataframe)\n",
        "\n",
        "UDVEs 13, 7, 6, 9, 10 and 3 are, respectively, the ones of greatest concern due to the potential large cluster of unknown vaccination status (CoC1), based on the data of this dataset.\n",
        "\n",
        "<Font color = 'red'>Percentual - a great example of how 'data cleaning' in clinical data sciences can be a very sensitive matter. CoC_3 is low in frequency but potentially high in consequencies ('No one is safe, until everyone is'). Low percentage does not mean 'low risk'."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 418
        },
        "id": "xvtWptwbzK-h",
        "outputId": "85c2e070-044f-4e86-ce2e-5a55e24be0bb"
      },
      "source": [
        "Per_udve_CoC = udve_CoC[['CoC_1','CoC_3','no_issue']] = udve_CoC[['CoC_1','CoC_3','no_issue']].apply(lambda x: x/x.sum(), axis=1) * 1\n",
        "\n",
        "Per_udve_CoC.style.set_properties(**{'text-align': 'right'}).format(\"{:.2%}\")"
      ],
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<style  type=\"text/css\" >\n",
              "#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row0_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row0_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row0_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row1_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row1_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row1_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row2_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row2_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row2_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row3_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row3_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row3_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row4_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row4_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row4_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row5_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row5_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row5_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row6_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row6_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row6_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row7_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row7_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row7_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row8_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row8_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row8_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row9_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row9_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row9_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row10_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row10_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row10_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row11_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row11_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row11_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row12_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row12_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row12_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row13_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row13_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row13_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row14_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row14_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row14_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row15_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row15_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row15_col2,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row16_col0,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row16_col1,#T_b966606a_dd09_11eb_aaf2_0242ac1c0002row16_col2{\n",
              "            text-align:  right;\n",
              "        }</style><table id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002\" ><thead>    <tr>        <th class=\"index_name level0\" >c_36_Combination_of_Concern</th>        <th class=\"col_heading level0 col0\" >CoC_1</th>        <th class=\"col_heading level0 col1\" >CoC_3</th>        <th class=\"col_heading level0 col2\" >no_issue</th>    </tr>    <tr>        <th class=\"index_name level0\" >c35_SC_UDVE_Nr</th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>    </tr></thead><tbody>\n",
              "                <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row0\" class=\"row_heading level0 row0\" >udve01</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row0_col0\" class=\"data row0 col0\" >2.79%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row0_col1\" class=\"data row0 col1\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row0_col2\" class=\"data row0 col2\" >97.21%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row1\" class=\"row_heading level0 row1\" >udve02</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row1_col0\" class=\"data row1 col0\" >12.26%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row1_col1\" class=\"data row1 col1\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row1_col2\" class=\"data row1 col2\" >87.74%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row2\" class=\"row_heading level0 row2\" >udve03</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row2_col0\" class=\"data row2 col0\" >6.67%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row2_col1\" class=\"data row2 col1\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row2_col2\" class=\"data row2 col2\" >93.33%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row3\" class=\"row_heading level0 row3\" >udve04</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row3_col0\" class=\"data row3 col0\" >0.07%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row3_col1\" class=\"data row3 col1\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row3_col2\" class=\"data row3 col2\" >99.93%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row4\" class=\"row_heading level0 row4\" >udve05</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row4_col0\" class=\"data row4 col0\" >1.51%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row4_col1\" class=\"data row4 col1\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row4_col2\" class=\"data row4 col2\" >98.49%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row5\" class=\"row_heading level0 row5\" >udve06</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row5_col0\" class=\"data row5 col0\" >17.39%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row5_col1\" class=\"data row5 col1\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row5_col2\" class=\"data row5 col2\" >82.61%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row6\" class=\"row_heading level0 row6\" >udve07</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row6_col0\" class=\"data row6 col0\" >21.39%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row6_col1\" class=\"data row6 col1\" >0.02%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row6_col2\" class=\"data row6 col2\" >78.59%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row7\" class=\"row_heading level0 row7\" >udve08</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row7_col0\" class=\"data row7 col0\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row7_col1\" class=\"data row7 col1\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row7_col2\" class=\"data row7 col2\" >100.00%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row8\" class=\"row_heading level0 row8\" >udve09</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row8_col0\" class=\"data row8 col0\" >6.70%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row8_col1\" class=\"data row8 col1\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row8_col2\" class=\"data row8 col2\" >93.30%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row9\" class=\"row_heading level0 row9\" >udve10</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row9_col0\" class=\"data row9 col0\" >6.76%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row9_col1\" class=\"data row9 col1\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row9_col2\" class=\"data row9 col2\" >93.24%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row10\" class=\"row_heading level0 row10\" >udve11</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row10_col0\" class=\"data row10 col0\" >0.04%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row10_col1\" class=\"data row10 col1\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row10_col2\" class=\"data row10 col2\" >99.96%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row11\" class=\"row_heading level0 row11\" >udve12</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row11_col0\" class=\"data row11 col0\" >1.65%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row11_col1\" class=\"data row11 col1\" >0.13%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row11_col2\" class=\"data row11 col2\" >98.22%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row12\" class=\"row_heading level0 row12\" >udve13</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row12_col0\" class=\"data row12 col0\" >51.22%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row12_col1\" class=\"data row12 col1\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row12_col2\" class=\"data row12 col2\" >48.78%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row13\" class=\"row_heading level0 row13\" >udve14</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row13_col0\" class=\"data row13 col0\" >1.62%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row13_col1\" class=\"data row13 col1\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row13_col2\" class=\"data row13 col2\" >98.38%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row14\" class=\"row_heading level0 row14\" >udve15</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row14_col0\" class=\"data row14 col0\" >2.13%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row14_col1\" class=\"data row14 col1\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row14_col2\" class=\"data row14 col2\" >97.87%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row15\" class=\"row_heading level0 row15\" >udve16</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row15_col0\" class=\"data row15 col0\" >1.53%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row15_col1\" class=\"data row15 col1\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row15_col2\" class=\"data row15 col2\" >98.47%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002level0_row16\" class=\"row_heading level0 row16\" >udve17</th>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row16_col0\" class=\"data row16 col0\" >0.32%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row16_col1\" class=\"data row16 col1\" >0.00%</td>\n",
              "                        <td id=\"T_b966606a_dd09_11eb_aaf2_0242ac1c0002row16_col2\" class=\"data row16 col2\" >99.68%</td>\n",
              "            </tr>\n",
              "    </tbody></table>"
            ],
            "text/plain": [
              "<pandas.io.formats.style.Styler at 0x7f869f76fcd0>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 26
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hpgMy2Gt1kYC"
      },
      "source": [
        "# <Font color = 'green'>**Step 07** - Crosstabing UDVE and CoC, per county, for the two UDVES with the highest frequency of 'Combinations of Concern'(CoC): UDVEs 13 and 07 respectively."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "E1qiU7RI2Vxb"
      },
      "source": [
        "# <font color ='red'>**UDVE 13**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MZPUwY8LtDFd",
        "outputId": "26dbda6c-83f9-4a2f-eb6a-d971170c68f4"
      },
      "source": [
        "udve_13 = sc0207_07am_udve_CoC.loc[sc0207_07am_udve_CoC['c35_SC_UDVE_Nr'] == 'udve13']\n",
        "udve_13.shape\n"
      ],
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(447464, 36)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 27
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 523
        },
        "id": "_ts6nB9PzWB5",
        "outputId": "7bd9b319-308f-4a93-b987-e5ea4bcbbf1d"
      },
      "source": [
        "udve_13_CoC = pd.crosstab(udve_13.c19_e_municipio_nome , udve_13.c_36_Combination_of_Concern)\n",
        "udve_13_CoC.style.set_properties(**{'text-align': 'right'}).format('{0:,.0f}')"
      ],
      "execution_count": 28,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<style  type=\"text/css\" >\n",
              "#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row0_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row0_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row1_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row1_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row2_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row2_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row3_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row3_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row4_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row4_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row5_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row5_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row6_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row6_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row7_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row7_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row8_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row8_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row9_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row9_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row10_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row10_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row11_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row11_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row12_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row12_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row13_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row13_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row14_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row14_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row15_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row15_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row16_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row16_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row17_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row17_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row18_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row18_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row19_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row19_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row20_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row20_col1,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row21_col0,#T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row21_col1{\n",
              "            text-align:  right;\n",
              "        }</style><table id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002\" ><thead>    <tr>        <th class=\"index_name level0\" >c_36_Combination_of_Concern</th>        <th class=\"col_heading level0 col0\" >CoC_1</th>        <th class=\"col_heading level0 col1\" >no_issue</th>    </tr>    <tr>        <th class=\"index_name level0\" >c19_e_municipio_nome</th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>    </tr></thead><tbody>\n",
              "                <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row0\" class=\"row_heading level0 row0\" >AGUAS MORNAS</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row0_col0\" class=\"data row0 col0\" >45</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row0_col1\" class=\"data row0 col1\" >3,773</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row1\" class=\"row_heading level0 row1\" >ALFREDO WAGNER</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row1_col0\" class=\"data row1 col0\" >0</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row1_col1\" class=\"data row1 col1\" >4,811</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row2\" class=\"row_heading level0 row2\" >ANGELINA</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row2_col0\" class=\"data row2 col0\" >0</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row2_col1\" class=\"data row2 col1\" >2,472</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row3\" class=\"row_heading level0 row3\" >ANITAPOLIS</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row3_col0\" class=\"data row3 col0\" >770</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row3_col1\" class=\"data row3 col1\" >1,207</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row4\" class=\"row_heading level0 row4\" >ANTONIO CARLOS</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row4_col0\" class=\"data row4 col0\" >816</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row4_col1\" class=\"data row4 col1\" >4,408</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row5\" class=\"row_heading level0 row5\" >BIGUACU</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row5_col0\" class=\"data row5 col0\" >0</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row5_col1\" class=\"data row5 col1\" >32,875</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row6\" class=\"row_heading level0 row6\" >CANELINHA</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row6_col0\" class=\"data row6 col0\" >0</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row6_col1\" class=\"data row6 col1\" >4,620</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row7\" class=\"row_heading level0 row7\" >FLORIANOPOLIS</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row7_col0\" class=\"data row7 col0\" >146,171</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row7_col1\" class=\"data row7 col1\" >6,825</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row8\" class=\"row_heading level0 row8\" >GAROPABA</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row8_col0\" class=\"data row8 col0\" >0</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row8_col1\" class=\"data row8 col1\" >12,354</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row9\" class=\"row_heading level0 row9\" >GOVERNADOR CELSO RAMOS</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row9_col0\" class=\"data row9 col0\" >42</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row9_col1\" class=\"data row9 col1\" >7,091</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row10\" class=\"row_heading level0 row10\" >LEOBERTO LEAL</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row10_col0\" class=\"data row10 col0\" >0</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row10_col1\" class=\"data row10 col1\" >1,778</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row11\" class=\"row_heading level0 row11\" >MAJOR GERCINO</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row11_col0\" class=\"data row11 col0\" >1</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row11_col1\" class=\"data row11 col1\" >1,993</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row12\" class=\"row_heading level0 row12\" >NOVA TRENTO</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row12_col0\" class=\"data row12 col0\" >2,738</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row12_col1\" class=\"data row12 col1\" >6,252</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row13\" class=\"row_heading level0 row13\" >PALHOCA</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row13_col0\" class=\"data row13 col0\" >0</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row13_col1\" class=\"data row13 col1\" >81,933</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row14\" class=\"row_heading level0 row14\" >PAULO LOPES</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row14_col0\" class=\"data row14 col0\" >21</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row14_col1\" class=\"data row14 col1\" >3,550</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row15\" class=\"row_heading level0 row15\" >RANCHO QUEIMADO</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row15_col0\" class=\"data row15 col0\" >590</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row15_col1\" class=\"data row15 col1\" >1,885</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row16\" class=\"row_heading level0 row16\" >SANTO AMARO DA IMPERATRIZ</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row16_col0\" class=\"data row16 col0\" >4,246</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row16_col1\" class=\"data row16 col1\" >9,302</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row17\" class=\"row_heading level0 row17\" >SAO BONIFACIO</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row17_col0\" class=\"data row17 col0\" >0</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row17_col1\" class=\"data row17 col1\" >1,940</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row18\" class=\"row_heading level0 row18\" >SAO JOAO BATISTA</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row18_col0\" class=\"data row18 col0\" >387</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row18_col1\" class=\"data row18 col1\" >7,949</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row19\" class=\"row_heading level0 row19\" >SAO JOSE</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row19_col0\" class=\"data row19 col0\" >61,738</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row19_col1\" class=\"data row19 col1\" >17,242</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row20\" class=\"row_heading level0 row20\" >SAO PEDRO DE ALCANTARA</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row20_col0\" class=\"data row20 col0\" >299</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row20_col1\" class=\"data row20 col1\" >2,894</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002level0_row21\" class=\"row_heading level0 row21\" >TIJUCAS</th>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row21_col0\" class=\"data row21 col0\" >11,344</td>\n",
              "                        <td id=\"T_baba75dc_dd09_11eb_aaf2_0242ac1c0002row21_col1\" class=\"data row21 col1\" >1,102</td>\n",
              "            </tr>\n",
              "    </tbody></table>"
            ],
            "text/plain": [
              "<pandas.io.formats.style.Styler at 0x7f869f76b6d0>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 28
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Y95EQpZO2qhM"
      },
      "source": [
        "#<Font color = 'orange'>**UDVE 07**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wcOZ8Aes0bV8",
        "outputId": "ba5e4d40-f4c6-47f9-9608-43266e752108"
      },
      "source": [
        "udve_07 = sc0207_07am_udve_CoC.loc[sc0207_07am_udve_CoC['c35_SC_UDVE_Nr'] == 'udve07']\n",
        "udve_07.shape"
      ],
      "execution_count": 29,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(145657, 36)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 29
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 334
        },
        "id": "e2bJJnsC0ptS",
        "outputId": "5d894ee5-7d8a-490c-d280-a8a20dc7b741"
      },
      "source": [
        "udve_07_CoC = pd.crosstab(udve_07.c19_e_municipio_nome , udve_07.c_36_Combination_of_Concern)\n",
        "udve_07_CoC.style.set_properties(**{'text-align': 'right'}).format('{0:,.0f}')"
      ],
      "execution_count": 30,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<style  type=\"text/css\" >\n",
              "#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row0_col0,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row0_col1,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row0_col2,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row1_col0,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row1_col1,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row1_col2,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row2_col0,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row2_col1,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row2_col2,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row3_col0,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row3_col1,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row3_col2,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row4_col0,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row4_col1,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row4_col2,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row5_col0,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row5_col1,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row5_col2,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row6_col0,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row6_col1,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row6_col2,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row7_col0,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row7_col1,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row7_col2,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row8_col0,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row8_col1,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row8_col2,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row9_col0,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row9_col1,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row9_col2,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row10_col0,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row10_col1,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row10_col2,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row11_col0,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row11_col1,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row11_col2,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row12_col0,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row12_col1,#T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row12_col2{\n",
              "            text-align:  right;\n",
              "        }</style><table id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002\" ><thead>    <tr>        <th class=\"index_name level0\" >c_36_Combination_of_Concern</th>        <th class=\"col_heading level0 col0\" >CoC_1</th>        <th class=\"col_heading level0 col1\" >CoC_3</th>        <th class=\"col_heading level0 col2\" >no_issue</th>    </tr>    <tr>        <th class=\"index_name level0\" >c19_e_municipio_nome</th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>    </tr></thead><tbody>\n",
              "                <tr>\n",
              "                        <th id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002level0_row0\" class=\"row_heading level0 row0\" >BELA VISTA DO TOLDO</th>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row0_col0\" class=\"data row0 col0\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row0_col1\" class=\"data row0 col1\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row0_col2\" class=\"data row0 col2\" >2,488</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002level0_row1\" class=\"row_heading level0 row1\" >CAMPO ALEGRE</th>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row1_col0\" class=\"data row1 col0\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row1_col1\" class=\"data row1 col1\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row1_col2\" class=\"data row1 col2\" >5,314</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002level0_row2\" class=\"row_heading level0 row2\" >CANOINHAS</th>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row2_col0\" class=\"data row2 col0\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row2_col1\" class=\"data row2 col1\" >19</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row2_col2\" class=\"data row2 col2\" >18,791</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002level0_row3\" class=\"row_heading level0 row3\" >IRINEOPOLIS</th>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row3_col0\" class=\"data row3 col0\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row3_col1\" class=\"data row3 col1\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row3_col2\" class=\"data row3 col2\" >4,472</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002level0_row4\" class=\"row_heading level0 row4\" >ITAIOPOLIS</th>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row4_col0\" class=\"data row4 col0\" >172</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row4_col1\" class=\"data row4 col1\" >8</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row4_col2\" class=\"data row4 col2\" >8,683</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002level0_row5\" class=\"row_heading level0 row5\" >MAFRA</th>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row5_col0\" class=\"data row5 col0\" >26,573</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row5_col1\" class=\"data row5 col1\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row5_col2\" class=\"data row5 col2\" >2,551</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002level0_row6\" class=\"row_heading level0 row6\" >MAJOR VIEIRA</th>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row6_col0\" class=\"data row6 col0\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row6_col1\" class=\"data row6 col1\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row6_col2\" class=\"data row6 col2\" >3,356</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002level0_row7\" class=\"row_heading level0 row7\" >MONTE CASTELO</th>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row7_col0\" class=\"data row7 col0\" >397</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row7_col1\" class=\"data row7 col1\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row7_col2\" class=\"data row7 col2\" >3,551</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002level0_row8\" class=\"row_heading level0 row8\" >PAPANDUVA</th>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row8_col0\" class=\"data row8 col0\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row8_col1\" class=\"data row8 col1\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row8_col2\" class=\"data row8 col2\" >8,277</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002level0_row9\" class=\"row_heading level0 row9\" >PORTO UNIAO</th>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row9_col0\" class=\"data row9 col0\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row9_col1\" class=\"data row9 col1\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row9_col2\" class=\"data row9 col2\" >17,884</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002level0_row10\" class=\"row_heading level0 row10\" >RIO NEGRINHO</th>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row10_col0\" class=\"data row10 col0\" >3,936</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row10_col1\" class=\"data row10 col1\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row10_col2\" class=\"data row10 col2\" >0</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002level0_row11\" class=\"row_heading level0 row11\" >SAO BENTO DO SUL</th>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row11_col0\" class=\"data row11 col0\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row11_col1\" class=\"data row11 col1\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row11_col2\" class=\"data row11 col2\" >31,489</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002level0_row12\" class=\"row_heading level0 row12\" >TRES BARRAS</th>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row12_col0\" class=\"data row12 col0\" >73</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row12_col1\" class=\"data row12 col1\" >0</td>\n",
              "                        <td id=\"T_bafbb92a_dd09_11eb_aaf2_0242ac1c0002row12_col2\" class=\"data row12 col2\" >7,623</td>\n",
              "            </tr>\n",
              "    </tbody></table>"
            ],
            "text/plain": [
              "<pandas.io.formats.style.Styler at 0x7f869f76b990>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 30
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0N3Z0VERxP5N"
      },
      "source": [
        "# <Font Color = 'blue'> **PHASE 5** - 'Time Serie' Exploration"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zCcbXfWg-mTp"
      },
      "source": [
        "#<Font color='green'> **Step 08** - Counting total nr of registers per month"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WeM2mhl7Qkek"
      },
      "source": [
        "Tips from [source](https://stackoverflow.com/questions/38792122/how-to-group-and-count-rows-by-month-and-year-using-pandas)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 317
        },
        "id": "fp_R-m2vLC4-",
        "outputId": "a6e19a20-a940-40a4-f7f9-fea0bdd9a6cf"
      },
      "source": [
        "c_28 = sc0207_07am_udve_CoC['c28_v_dataaplicacao'].groupby([sc0207_07am_udve_CoC['c28_v_dataaplicacao'].dt.year.rename('year'), sc0207_07am_udve_CoC['c28_v_dataaplicacao'].dt.month.rename('month')]).agg({'count'})\n",
        "c_28"
      ],
      "execution_count": 33,
      "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></th>\n",
              "      <th>count</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>year</th>\n",
              "      <th>month</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>2020</th>\n",
              "      <th>12</th>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th rowspan=\"6\" valign=\"top\">2021</th>\n",
              "      <th>1</th>\n",
              "      <td>68348</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>171546</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>597378</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>784707</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>687679</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>823683</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "             count\n",
              "year month        \n",
              "2020 12          3\n",
              "2021 1       68348\n",
              "     2      171546\n",
              "     3      597378\n",
              "     4      784707\n",
              "     5      687679\n",
              "     6      823683"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 33
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "B_L08Dpzoc40"
      },
      "source": [
        "# <Font Color = 'green'>**Step 9** - Checking 'Combination of Concern' frequency since start of covid19 vaccination in Santa Catarina as per dataset sc0207_07am.\n",
        "\n",
        "Tips from [source](https://stackoverflow.com/questions/44908383/how-can-i-group-by-month-from-a-date-field-using-python-pandas)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1D2OI1O6OqbN",
        "outputId": "ea13f81c-acc0-4595-fb43-647ccc0156b4"
      },
      "source": [
        "c28_36 = sc0207_07am_udve_CoC.groupby([(sc0207_07am_udve_CoC['c28_v_dataaplicacao'].dt.strftime('%B')),'c_36_Combination_of_Concern']).size()\n",
        "c28_36"
      ],
      "execution_count": 34,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "c28_v_dataaplicacao  c_36_Combination_of_Concern\n",
              "April                CoC_1                          103964\n",
              "                     CoC_3                               6\n",
              "                     no_issue                       680737\n",
              "December             CoC_1                               3\n",
              "February             CoC_1                           34385\n",
              "                     CoC_3                              13\n",
              "                     no_issue                       137148\n",
              "January              CoC_1                           11834\n",
              "                     CoC_3                               9\n",
              "                     no_issue                        56505\n",
              "June                 CoC_1                           47241\n",
              "                     no_issue                       776442\n",
              "March                CoC_1                          112652\n",
              "                     CoC_3                             437\n",
              "                     no_issue                       484289\n",
              "May                  CoC_1                           58107\n",
              "                     CoC_3                               2\n",
              "                     no_issue                       629570\n",
              "dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 34
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IAjgb7HbbFFG"
      },
      "source": [
        "<Font color = 'red'> Here I could try to find out how to pivot the table above to the wished result here after (done manually). However, I enjoy this 'sensorial tête_à_tête' with the data as it provides very interesting insights. \n",
        "\n",
        "Per example we notice that: \n",
        "\n",
        "\n",
        "*   Wrong registrations entered for December 2020 are still not corrected (vaccination in SC only started on 18 Jan 21);\n",
        "*   When we compare data from end Mai wiht end June we notice that frequency for category CoC_1 is still raising for the early months of January, February and March, but reducing for April, May and June."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xhk7xRhkz5Ko"
      },
      "source": [
        "sc0207_07am_c_28_c_36 = {'Month':    ['December','January','February', 'March', 'April',  'May',  'June'],\n",
        "            'CoC_1':     [3,             11834,    34385,   112652,  103964,  58107,   47241],\n",
        "            'CoC_2':     [0,                 0,        0,        0,       0,      0,       0],\n",
        "            'CoC_3':     [0,                 9,       13,      437,       6,      2,       0],\n",
        "            'no_issue':  [0,             56505,   137148,   484289,  680737, 629570,  776442]}"
      ],
      "execution_count": 35,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 286
        },
        "id": "fLk-HbXn7JIQ",
        "outputId": "e29853c5-95c8-49db-bbd1-c17198cbe50b"
      },
      "source": [
        "sc0207_07am_df_c_28_c_36 = pd.DataFrame(data=sc0207_07am_c_28_c_36)\n",
        "sc0207_07am_df_c_28_c_36"
      ],
      "execution_count": 36,
      "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>Month</th>\n",
              "      <th>CoC_1</th>\n",
              "      <th>CoC_2</th>\n",
              "      <th>CoC_3</th>\n",
              "      <th>no_issue</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>December</td>\n",
              "      <td>3</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>January</td>\n",
              "      <td>11834</td>\n",
              "      <td>0</td>\n",
              "      <td>9</td>\n",
              "      <td>56505</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>February</td>\n",
              "      <td>34385</td>\n",
              "      <td>0</td>\n",
              "      <td>13</td>\n",
              "      <td>137148</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>March</td>\n",
              "      <td>112652</td>\n",
              "      <td>0</td>\n",
              "      <td>437</td>\n",
              "      <td>484289</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>April</td>\n",
              "      <td>103964</td>\n",
              "      <td>0</td>\n",
              "      <td>6</td>\n",
              "      <td>680737</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>May</td>\n",
              "      <td>58107</td>\n",
              "      <td>0</td>\n",
              "      <td>2</td>\n",
              "      <td>629570</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>June</td>\n",
              "      <td>47241</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>776442</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      Month   CoC_1  CoC_2  CoC_3  no_issue\n",
              "0  December       3      0      0         0\n",
              "1   January   11834      0      9     56505\n",
              "2  February   34385      0     13    137148\n",
              "3     March  112652      0    437    484289\n",
              "4     April  103964      0      6    680737\n",
              "5       May   58107      0      2    629570\n",
              "6      June   47241      0      0    776442"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 36
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QXg6QRvMWNar"
      },
      "source": [
        "#<font color = 'green'>**Step 10** - Recalling data from 30 May 2021 for comparisons with data from up to 30 June 2021:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7WOp6AFnV2mS"
      },
      "source": [
        "sc3005_c_28_c_36 = {'Month':['December', 'January',  'February', 'March', 'April',  'May'],\n",
        "                    'CoC_1':   [3,            9191,       25045,   92355,   63923,   5293],\n",
        "                    'CoC_2':   [0,            2539,        6312,   16436,   36890,  58452],\n",
        "                    'CoC_3':   [0,               9,          12,       3,       8,      3],\n",
        "                 'no_issue':   [0,           56134,      136639,   476531, 650123, 427518]}"
      ],
      "execution_count": 37,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 235
        },
        "id": "Fx98EL6JWIRB",
        "outputId": "155edfc7-7304-467c-8821-47c8b529de6b"
      },
      "source": [
        "sc3005_df_c_28_c_36 = pd.DataFrame(data=sc3005_c_28_c_36)\n",
        "sc3005_df_c_28_c_36"
      ],
      "execution_count": 38,
      "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>Month</th>\n",
              "      <th>CoC_1</th>\n",
              "      <th>CoC_2</th>\n",
              "      <th>CoC_3</th>\n",
              "      <th>no_issue</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>December</td>\n",
              "      <td>3</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>January</td>\n",
              "      <td>9191</td>\n",
              "      <td>2539</td>\n",
              "      <td>9</td>\n",
              "      <td>56134</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>February</td>\n",
              "      <td>25045</td>\n",
              "      <td>6312</td>\n",
              "      <td>12</td>\n",
              "      <td>136639</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>March</td>\n",
              "      <td>92355</td>\n",
              "      <td>16436</td>\n",
              "      <td>3</td>\n",
              "      <td>476531</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>April</td>\n",
              "      <td>63923</td>\n",
              "      <td>36890</td>\n",
              "      <td>8</td>\n",
              "      <td>650123</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>May</td>\n",
              "      <td>5293</td>\n",
              "      <td>58452</td>\n",
              "      <td>3</td>\n",
              "      <td>427518</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      Month  CoC_1  CoC_2  CoC_3  no_issue\n",
              "0  December      3      0      0         0\n",
              "1   January   9191   2539      9     56134\n",
              "2  February  25045   6312     12    136639\n",
              "3     March  92355  16436      3    476531\n",
              "4     April  63923  36890      8    650123\n",
              "5       May   5293  58452      3    427518"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 38
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vE0uXQ4KV2Oj"
      },
      "source": [
        "# **<Font color='blue'>Brief Comments and conclusions:**\n",
        "\n",
        "*   <font color = 'green'>**DES_EXCELEI !!!** </font>- With the help of the Alura Bootcamp DS 2, Aluras courses Programming with Python, Python for DataSciences, services as stackoverflow, medium and others, I accomplished my objective to deliver this module 3 project entirely within one Google Colab notebook using Python related packages.\n",
        "\n",
        "*   We notice that, in Santa Catarina, on 30 Jun 21, the overall trend of 12% registrations of covid_19 vaccinations with 'Combinations of Concern' is still valid (368,833 (CoC_1 + CoC_3) out of a total of 3,133,344 registrations).\n",
        "\n",
        "*   UDVE 13 alone clusters 62% of the state total of 'Combinations of Concern' (229,208 out of 368,833).\n",
        "\n",
        "*   \"'*No_issue*'\" - as already mentioned, from the pharmacovigilance point of view, is not totally correct, as I noticed some other 'combinations of worrying' (to distinguish of the definition of 'CoC'). So I may tackle 'combinations of worrying' in the projects of the next two modules of this bootcamp.\n",
        "\n",
        "*   Finally, as explained, I am tailoring all the above with the end_user in mind: the SC UDVE manager and local teams at the each 'vaccination center'. Therefore, at the end of this bootcamp we may use the material above to easily identify and isolate all the registrations with 'combinations of concern' for appreciation at several levels - UDVE, county, vaccination center, etc. More about how that can be done in a sensitive way with efficiente Risk Based Monitoring techniques I use in clinical trials may be presented in the next two modules.\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AmixHdWZ1dk2"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}