Combinations_of_Concern_5_sc02092021_07am.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Combinations of Concern_5 - sc02092021_07am.ipynb",
      "provenance": [],
      "machine_shape": "hm",
      "mount_file_id": "1bNJVln2KNPkgHUuE5dJCPIOuED_MysN5",
      "authorship_tag": "ABX9TyNtAcySvZteWQlTXNd8leNN",
      "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_BR_SC_Combinations_of_Concern_5/blob/main/Combinations_of_Concern_5_sc02092021_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'>**Vac_Covid19_BR_SC_Combinations_of_Concern_5**\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 Sep 2021\n",
        "\n",
        "\n",
        "**Introduction**:\n",
        "\n",
        "This is the 5th monthly edition of my personal project named 'Combinations_of_Concern - CoC'. The first 4 were initiated and developped during my attendance to [Alura Bootcamp Applied Data Science II (Brazilian Winter 2021)] (https://www.alura.com.br/bootcamp/data-science-aplicada/matriculas-abertas). This bootcamp experience was done in parallel to a sequence of 32 Alura courses focused exclusively on the Python language between 01 May and 30 Aug 2021.\n",
        "\n",
        "\n",
        "This 5th edition is a continuation and extension of the exploratory work I already done during the first four editions, when I reviewed the OpenDataSus registers of covid19 vaccination in the Brazilian State of Santa Catarina(SC).\n",
        "\n",
        "For the first two editions, due to my yet very limited knowledge of Python I used a mix of terminal command line, Python&Colab and Excel, one for each of the 3 phases of the project. From the 3rd edition the learnings from the Alura courses helped to deliver all the project in one Colab notebook.\n",
        "\n",
        "The main findings until here were: \n",
        "\n",
        "* From February to end July 2021 a constant trend of ~ 12% of registers for which we can not confirm which vaccine an individual received when I cross-checked 3 variables from the master dataset. I named this troublesome combination as <Font color = 'brow'>'Combinations of Concern' - CoC </Font> (step 5 here after for definition).\n",
        "\n",
        "<font color='blue'>My objective for this 5th edition, is to check to which direction, up or down, the trend of hte proportion of 'Combinations_of_Concern' is heading on.\n",
        "\n",
        "Before we move ahead, let's just a quick remind how the masters datasets evolved so far plus an spoil alert for this fifth edition:\n",
        "\n",
        "|                                                   Editions <br>'Combinations_of_Concern\" <br> github links                                                   |   Published  |     raw file    | Raw size | columns | rows <br>(registrations) | new <br>registrations | Total nr <br>CoC's registrations | CoC <br>% per total |\n",
        "|:------------------------------------------------------------------------------------------------------------------------------------------------------------:|:------------:|:---------------:|:--------:|:-------:|:------------------------:|:---------------------:|:--------------------------------:|:-------------------:|\n",
        "| [1](https://github.com/amf60/Vac_Covid19_BR_SC_Combinations_of_Concern_1) <br> <br>[2](https://github.com/amf60/Vac_Covid19_BR_SC_Combinations_of_Concern_2) | early Jun 21 | sc3005_05am.csv |  1.11 GB |    34   |                2.059.426 |                       | 259.507                          |         12 %        |\n",
        "|                                           [3](https://github.com/amf60/Vac_Covid19_BR_SC_Combinations_of_Concern_3)                                          | early Jul 21 | sc0207_07am.csv |  1.71 GB |    34   |                3.133.344 |             1.116.119 | 368.654                          |         12 %        |\n",
        "|                                           [4](https://github.com/amf60/Vac_Covid19_BR_SC_Combinations_of_Concern_4)                                          | early Aug 21 | sc0408_10am.csv |  2.6 GB  |    34   |                4.854.329 |             1.720.985 | 576.103                          |         12 %        |\n",
        "|                                           [5](https://github.com/amf60/Vac_Covid19_BR_SC_Combinations_of_Concern_5)                                          | early Sep 21 | sc0209_07am.csv |  3.66 GB |    34   |                6.822.321 |             1.967.992 | 767.214                          |         11%         |"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Qgkm-oQDARKH"
      },
      "source": [
        "amf: the table here after was done with the help from this [source](https://www.tablesgenerator.com/markdown_tables)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Stz9aY9L85H-"
      },
      "source": [
        "<font color ='orange'>AMF: Technical Warning: At the end of Augst 2021, due to the significant increase on the rows in the raw dataset(sc0209_07am), I was obliged to subscribe to Google Colab Pro in order to have a connection to GPU and high-RAM runtime available. The two next blocs of code are from Google Colab's instruction on to have access to GPU and high_RAM runtime. (https://colab.research.google.com/notebooks/pro.ipynb#scrollTo=V1G82GuO-tez)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5BfDm9RDxYzv",
        "outputId": "73315153-424e-4b10-93c9-1606bb45b614"
      },
      "source": [
        "gpu_info = !nvidia-smi\n",
        "gpu_info = '\\n'.join(gpu_info)\n",
        "if gpu_info.find('failed') >= 0:\n",
        "  print('Not connected to a GPU')\n",
        "else:\n",
        "  print(gpu_info)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Not connected to a GPU\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "knkcVy0xyrQE",
        "outputId": "9422364c-a679-4b86-e5d4-4d05f2aab728"
      },
      "source": [
        "from psutil import virtual_memory\n",
        "ram_gb = virtual_memory().total / 1e9\n",
        "print('Your runtime has {:.1f} gigabytes of available RAM\\n'.format(ram_gb))\n",
        "\n",
        "if ram_gb < 20:\n",
        "  print('Not using a high-RAM runtime')\n",
        "else:\n",
        "  print('You are using a high-RAM runtime!')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Your runtime has 27.3 gigabytes of available RAM\n",
            "\n",
            "You are using a high-RAM runtime!\n"
          ]
        }
      ]
    },
    {
      "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",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "e1bd52cd-1af0-4f27-a5fa-892ba0ada43c"
      },
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import matplotlib as mpl\n",
        "#import seaborn as sns\n",
        "\n",
        "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": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "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"
          ]
        }
      ]
    },
    {
      "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 4 minutes for a 3.6 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 31 August 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 Sep 21 at 07 am, and re-named my downloaded master file to match this (sc020921_07am.csv).\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": [
        "sc0209_07am = pd.read_csv('/content/drive/MyDrive/Alura DS Bootcamp/Combinations of Concern Projects/Vac_C19_CoC_5_sc0209_07am/sc020921_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": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Bpx6EwYgBmWm",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "7938b5fe-163f-418c-93ab-d3dfe25b7466"
      },
      "source": [
        "sc0209_07am.shape"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(6822321, 34)"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZUhu8oW-YGsa"
      },
      "source": [
        "#<font color = 'brown'>IMPORTANT REMINDERS BEFORE WE MOVE ON: \n",
        "\n",
        " \n",
        "\n",
        "1. For information: 1.967.992 new registers were added between 04 Aug 21 (4.854.329 registrations) and 02 Sep 21. \n",
        "\n",
        "2. Each row, or register, correspond to the application of one dose, either Dose 1 or Dose 2. Therefore the total of 6.822.321 registrations in this dataset does not mean 6.822.321 vaccinated people in Santa Catarina.\n",
        "\n",
        "3.   Per oficial [DIVE's boletim published on 01 Sep 21](http://www.dive.sc.gov.br/conteudos/boletim2021/corona57/corona57.pdf), up to 28 August the distribution betweeen doses 1 and dose 2 were:\n",
        "\n",
        "*   dose 1 -      4.223.939\n",
        "*   dose 2 -      1.806.959\n",
        "*   Single dose -   221.850\n",
        "*   Total -       6.252.748 (so, not as updated as our master dataset sc0209_07am)\n",
        "\n",
        "4. Acording to the national central records for covid-19 distribution consulted on 02 Sep 2021 at 04 pm, the Brazilian state of Santa Catarina received from the Federal government to this date a total of 7.938.290 doses.\n",
        "\n",
        "5. The four covid19 vaccines used in Santa Catarina (SC) until 02 Sep 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": {
        "id": "Ecm0anUe-h_h",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "dc33fde2-f2c4-49ca-9315-53405b5ae528"
      },
      "source": [
        "sc0209_07am.info()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 6822321 entries, 0 to 6822320\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                     float64\n",
            " 3   c4_p_dn                        object \n",
            " 4   c5_p_sex                       object \n",
            " 5   c6_p_r_codigo                  float64\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(6), int64(5), object(23)\n",
            "memory usage: 1.7+ GB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QRwLSOkg-6zm",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 479
        },
        "outputId": "be202793-103a-4bd1-8598-2af025c11e0b"
      },
      "source": [
        "sc0209_07am.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>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>8abd775e-0b0d-4b0c-b9a2-e61529d9e629-i0b0</td>\n",
              "      <td>e950263bfdb40a5e7b351def93b16de705648e9a0cedf9...</td>\n",
              "      <td>41.0</td>\n",
              "      <td>1980-04-08</td>\n",
              "      <td>M</td>\n",
              "      <td>99.0</td>\n",
              "      <td>SEM INFORMACAO</td>\n",
              "      <td>431490.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>PORTO ALEGRE</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>90520.0</td>\n",
              "      <td>B</td>\n",
              "      <td>7032218</td>\n",
              "      <td>MUNICIPIO DE CRICIUMA</td>\n",
              "      <td>UNIDADE BASICA DE SAUDE CENTRO</td>\n",
              "      <td>420460</td>\n",
              "      <td>CRICIUMA</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>FA9095</td>\n",
              "      <td>MINISTERIO DA SAUDE</td>\n",
              "      <td>Organization/00394544000851</td>\n",
              "      <td>2021-07-06</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>87</td>\n",
              "      <td>Vacina covid-19 - BNT162b2 - BioNTech/Fosun Ph...</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-07-06T15:28:41.000Z</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>9e2c6ed0-af55-4e72-aa79-f704780eb34b-i0b0</td>\n",
              "      <td>93ba0606fc9ff53b616a35be59e4adaddffa148a7822fa...</td>\n",
              "      <td>22.0</td>\n",
              "      <td>1998-05-05</td>\n",
              "      <td>F</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420410.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CAXAMBU DO SUL</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89880.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2537745</td>\n",
              "      <td>MUNICIPIO DE CHAPECO</td>\n",
              "      <td>CENTRO DE SAUDE DA FAMILIA NORTE</td>\n",
              "      <td>420420</td>\n",
              "      <td>CHAPECO</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>200279</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-03-04</td>\n",
              "      <td>2ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-03-05T10:54:23.000Z</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>d27610a5-c219-406d-9be9-5620c13907e5-i0b0</td>\n",
              "      <td>f73cb61dcdb860f39cfdf99c1b1da4655528912c71c6de...</td>\n",
              "      <td>36.0</td>\n",
              "      <td>1985-01-12</td>\n",
              "      <td>M</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420890.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>JARAGUA DO SUL</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89254.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2745178</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE JARAGUA DO SUL</td>\n",
              "      <td>CENTRAL DE IMUNIZACAO</td>\n",
              "      <td>420890</td>\n",
              "      <td>JARAGUA DO SUL</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>C202106107</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-07-22</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Saudetech</td>\n",
              "      <td>2021-07-22T19:31:40.000Z</td>\n",
              "      <td>17961</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2f2c77b2-688e-4428-944d-20c3db3e5553-i0b0</td>\n",
              "      <td>085843e2bc46f9b133062a411c6411a1afc43dd9f36a5f...</td>\n",
              "      <td>33.0</td>\n",
              "      <td>1987-07-15</td>\n",
              "      <td>F</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420130.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>ARAQUARI</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89245.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2511681</td>\n",
              "      <td>JOINVILLE SECRETARIA DA SAUDE</td>\n",
              "      <td>UBSF BUCAREIN</td>\n",
              "      <td>420910</td>\n",
              "      <td>JOINVILLE</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>214VCD057Z</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>Organization/33781055000135</td>\n",
              "      <td>2021-05-29</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-29T21:38:16.000Z</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>80ea7e85-a765-45fa-88cc-07ea09239476-i0b0</td>\n",
              "      <td>4a3f52a247ba87dec7d4c076bd40cdeccc93b09a43ad3b...</td>\n",
              "      <td>39.0</td>\n",
              "      <td>1982-05-22</td>\n",
              "      <td>F</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420900.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>JOACABA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89600.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2560658</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE JOACABA</td>\n",
              "      <td>ESF CENTRO 1</td>\n",
              "      <td>420900</td>\n",
              "      <td>JOACABA</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>EY0586</td>\n",
              "      <td>MINISTERIO DA SAUDE</td>\n",
              "      <td>Organization/00394544000851</td>\n",
              "      <td>2021-07-05</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>87</td>\n",
              "      <td>Vacina covid-19 - BNT162b2 - BioNTech/Fosun Ph...</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-07-05T17:43:04.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  8abd775e-0b0d-4b0c-b9a2-e61529d9e629-i0b0  ...                 16341\n",
              "1  9e2c6ed0-af55-4e72-aa79-f704780eb34b-i0b0  ...                 16341\n",
              "2  d27610a5-c219-406d-9be9-5620c13907e5-i0b0  ...                 17961\n",
              "3  2f2c77b2-688e-4428-944d-20c3db3e5553-i0b0  ...                 16341\n",
              "4  80ea7e85-a765-45fa-88cc-07ea09239476-i0b0  ...                 16341\n",
              "\n",
              "[5 rows x 34 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ]
    },
    {
      "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": [
        "sc0209_07am['c4_p_dn'] = pd.to_datetime(sc0209_07am['c4_p_dn'])\n",
        "sc0209_07am['c28_v_dataaplicacao'] = pd.to_datetime(sc0209_07am['c28_v_dataaplicacao'])\n",
        "sc0209_07am['c33_data_importacao_rnds'] = pd.to_datetime(sc0209_07am['c33_data_importacao_rnds']).dt.date\n",
        "sc0209_07am['c33_data_importacao_rnds'] = pd.to_datetime(sc0209_07am['c33_data_importacao_rnds']).dt.normalize()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tYM8udK3g5OU",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 315
        },
        "outputId": "be372f5a-d432-4eab-feb4-328a6d93e767"
      },
      "source": [
        "sc0209_07am.tail(3)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>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>6822318</th>\n",
              "      <td>3e2d30aa-49cc-4f75-a9eb-cbb46833185f-i0b0</td>\n",
              "      <td>6a2cde3792443fb4d61df5a29c4db905c004142b0972fc...</td>\n",
              "      <td>69.0</td>\n",
              "      <td>1952-01-06</td>\n",
              "      <td>M</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421170.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>ORLEANS</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88870.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2513846</td>\n",
              "      <td>MUNICIPIO DE ORLEANS</td>\n",
              "      <td>UNIDADE SAO LUCAS</td>\n",
              "      <td>421170</td>\n",
              "      <td>ORLEANS</td>\n",
              "      <td>SC</td>\n",
              "      <td>203</td>\n",
              "      <td>Pessoas de 70 a 74 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210131</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-04-21</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-04-28</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6822319</th>\n",
              "      <td>df5f1392-2831-4f62-9ce8-e90174d87d56-i0b0</td>\n",
              "      <td>8c97e84edad03f1dcd12b6f61f1d9516edcfb2ce618d9d...</td>\n",
              "      <td>87.0</td>\n",
              "      <td>1934-05-07</td>\n",
              "      <td>F</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420420.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CHAPECO</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89804.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2537729</td>\n",
              "      <td>MUNICIPIO DE CHAPECO</td>\n",
              "      <td>CENTRO DE SAUDE DA FAMILIA CRISTO REI</td>\n",
              "      <td>420420</td>\n",
              "      <td>CHAPECO</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>213VCD031W</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>Organization/33781055000135</td>\n",
              "      <td>2021-05-25</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-25</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6822320</th>\n",
              "      <td>454d9c46-e972-463d-8fb8-4a5f521a1782-i0b0</td>\n",
              "      <td>cb6ce369b46afe8b64c9d5d3080cb4cc764120d91fa5d5...</td>\n",
              "      <td>52.0</td>\n",
              "      <td>1969-04-07</td>\n",
              "      <td>M</td>\n",
              "      <td>1.0</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>88310.0</td>\n",
              "      <td>B</td>\n",
              "      <td>7072414</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE CAMBORIU</td>\n",
              "      <td>POLICLINICA MUNICIPAL</td>\n",
              "      <td>420320</td>\n",
              "      <td>CAMBORIU</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>215VCD147W</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>Organization/33781055000135</td>\n",
              "      <td>2021-08-03</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-08-03</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                    c1_document_id  ... c34_id_sistema_origem\n",
              "6822318  3e2d30aa-49cc-4f75-a9eb-cbb46833185f-i0b0  ...                 16341\n",
              "6822319  df5f1392-2831-4f62-9ce8-e90174d87d56-i0b0  ...                 16341\n",
              "6822320  454d9c46-e972-463d-8fb8-4a5f521a1782-i0b0  ...                 16341\n",
              "\n",
              "[3 rows x 34 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "M3a6blf8gwna",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "f9b6cb75-afe1-44a1-cf42-e8e8a545ff58"
      },
      "source": [
        "sc0209_07am.info()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 6822321 entries, 0 to 6822320\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                     float64       \n",
            " 3   c4_p_dn                        datetime64[ns]\n",
            " 4   c5_p_sex                       object        \n",
            " 5   c6_p_r_codigo                  float64       \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(6), int64(5), object(20)\n",
            "memory usage: 1.7+ GB\n"
          ]
        }
      ]
    },
    {
      "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": {
        "id": "2DrHYKsIyiUF",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 479
        },
        "outputId": "c054e642-3a3b-4463-8cf8-7567b41d1528"
      },
      "source": [
        "sc0209_07am.loc[sc0209_07am['c29_v_descricao_dose'] == '    1ª Dose', 'c29_v_descricao_dose'] = 'D1'\n",
        "sc0209_07am.loc[sc0209_07am['c29_v_descricao_dose'] == '    2ª Dose', 'c29_v_descricao_dose'] = 'D2'\n",
        "sc0209_07am.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>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>8abd775e-0b0d-4b0c-b9a2-e61529d9e629-i0b0</td>\n",
              "      <td>e950263bfdb40a5e7b351def93b16de705648e9a0cedf9...</td>\n",
              "      <td>41.0</td>\n",
              "      <td>1980-04-08</td>\n",
              "      <td>M</td>\n",
              "      <td>99.0</td>\n",
              "      <td>SEM INFORMACAO</td>\n",
              "      <td>431490.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>PORTO ALEGRE</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>RS</td>\n",
              "      <td>90520.0</td>\n",
              "      <td>B</td>\n",
              "      <td>7032218</td>\n",
              "      <td>MUNICIPIO DE CRICIUMA</td>\n",
              "      <td>UNIDADE BASICA DE SAUDE CENTRO</td>\n",
              "      <td>420460</td>\n",
              "      <td>CRICIUMA</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>FA9095</td>\n",
              "      <td>MINISTERIO DA SAUDE</td>\n",
              "      <td>Organization/00394544000851</td>\n",
              "      <td>2021-07-06</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>87</td>\n",
              "      <td>Vacina covid-19 - BNT162b2 - BioNTech/Fosun Ph...</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-07-06</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>9e2c6ed0-af55-4e72-aa79-f704780eb34b-i0b0</td>\n",
              "      <td>93ba0606fc9ff53b616a35be59e4adaddffa148a7822fa...</td>\n",
              "      <td>22.0</td>\n",
              "      <td>1998-05-05</td>\n",
              "      <td>F</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420410.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CAXAMBU DO SUL</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89880.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2537745</td>\n",
              "      <td>MUNICIPIO DE CHAPECO</td>\n",
              "      <td>CENTRO DE SAUDE DA FAMILIA NORTE</td>\n",
              "      <td>420420</td>\n",
              "      <td>CHAPECO</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>200279</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-03-04</td>\n",
              "      <td>2ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-03-05</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>d27610a5-c219-406d-9be9-5620c13907e5-i0b0</td>\n",
              "      <td>f73cb61dcdb860f39cfdf99c1b1da4655528912c71c6de...</td>\n",
              "      <td>36.0</td>\n",
              "      <td>1985-01-12</td>\n",
              "      <td>M</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420890.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>JARAGUA DO SUL</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89254.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2745178</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE JARAGUA DO SUL</td>\n",
              "      <td>CENTRAL DE IMUNIZACAO</td>\n",
              "      <td>420890</td>\n",
              "      <td>JARAGUA DO SUL</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>C202106107</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-07-22</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Saudetech</td>\n",
              "      <td>2021-07-22</td>\n",
              "      <td>17961</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2f2c77b2-688e-4428-944d-20c3db3e5553-i0b0</td>\n",
              "      <td>085843e2bc46f9b133062a411c6411a1afc43dd9f36a5f...</td>\n",
              "      <td>33.0</td>\n",
              "      <td>1987-07-15</td>\n",
              "      <td>F</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420130.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>ARAQUARI</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89245.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2511681</td>\n",
              "      <td>JOINVILLE SECRETARIA DA SAUDE</td>\n",
              "      <td>UBSF BUCAREIN</td>\n",
              "      <td>420910</td>\n",
              "      <td>JOINVILLE</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>214VCD057Z</td>\n",
              "      <td>FUNDACAO OSWALDO CRUZ</td>\n",
              "      <td>Organization/33781055000135</td>\n",
              "      <td>2021-05-29</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-29</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>80ea7e85-a765-45fa-88cc-07ea09239476-i0b0</td>\n",
              "      <td>4a3f52a247ba87dec7d4c076bd40cdeccc93b09a43ad3b...</td>\n",
              "      <td>39.0</td>\n",
              "      <td>1982-05-22</td>\n",
              "      <td>F</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420900.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>JOACABA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89600.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2560658</td>\n",
              "      <td>PREFEITURA MUNICIPAL DE JOACABA</td>\n",
              "      <td>ESF CENTRO 1</td>\n",
              "      <td>420900</td>\n",
              "      <td>JOACABA</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>EY0586</td>\n",
              "      <td>MINISTERIO DA SAUDE</td>\n",
              "      <td>Organization/00394544000851</td>\n",
              "      <td>2021-07-05</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>87</td>\n",
              "      <td>Vacina covid-19 - BNT162b2 - BioNTech/Fosun Ph...</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-07-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  8abd775e-0b0d-4b0c-b9a2-e61529d9e629-i0b0  ...                 16341\n",
              "1  9e2c6ed0-af55-4e72-aa79-f704780eb34b-i0b0  ...                 16341\n",
              "2  d27610a5-c219-406d-9be9-5620c13907e5-i0b0  ...                 17961\n",
              "3  2f2c77b2-688e-4428-944d-20c3db3e5553-i0b0  ...                 16341\n",
              "4  80ea7e85-a765-45fa-88cc-07ea09239476-i0b0  ...                 16341\n",
              "\n",
              "[5 rows x 34 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ]
    },
    {
      "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": {
        "id": "lW3CBia_DVxv",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 932
        },
        "outputId": "0e597fa0-b175-470b-ff6c-8299ccb5d25b"
      },
      "source": [
        "sc0209_07am.sort_values(by='c28_v_dataaplicacao', ascending=True, inplace=True)\n",
        "sc0209_07am"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>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>2341431</th>\n",
              "      <td>c0622be3-b4a1-4e4f-9c21-8e0e0cc7f7da-i0b0</td>\n",
              "      <td>8ba3755c79ba0f8bbb14ac74e74116c9b2b4cd9ba13cb6...</td>\n",
              "      <td>35.0</td>\n",
              "      <td>1985-03-17</td>\n",
              "      <td>F</td>\n",
              "      <td>3.0</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>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-04-25</td>\n",
              "      <td>18602</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1746232</th>\n",
              "      <td>4af998fc-ba82-40e6-bfd4-d2fcde965c29-i0b0</td>\n",
              "      <td>537c73cbf959a7ade3fe86d87aa7ab9f8b1de3ad55a0b0...</td>\n",
              "      <td>55.0</td>\n",
              "      <td>1965-10-26</td>\n",
              "      <td>F</td>\n",
              "      <td>1.0</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>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-04-26</td>\n",
              "      <td>18602</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2474106</th>\n",
              "      <td>d041ac6e-fdfd-4875-944e-6d2c4cc79191-i0b0</td>\n",
              "      <td>6e5a239adbcd1b2cea00f85873bc3d12521eb58564d489...</td>\n",
              "      <td>37.0</td>\n",
              "      <td>1983-05-18</td>\n",
              "      <td>M</td>\n",
              "      <td>1.0</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>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-03-12</td>\n",
              "      <td>18602</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6419100</th>\n",
              "      <td>dffd702e-cd88-4e7c-8795-e4e0a7784af2-i0b0</td>\n",
              "      <td>4c569cc933658e66d31439b75f16bb6465acbf8e516674...</td>\n",
              "      <td>74.0</td>\n",
              "      <td>1946-12-05</td>\n",
              "      <td>M</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420380.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CANOINHAS</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89460.0</td>\n",
              "      <td>B</td>\n",
              "      <td>9522611</td>\n",
              "      <td>MUNICIPIO DE CANOINHAS</td>\n",
              "      <td>UNIDADE BASICA DE SAUDE ALVINO MUHLMANN</td>\n",
              "      <td>420380</td>\n",
              "      <td>CANOINHAS</td>\n",
              "      <td>SC</td>\n",
              "      <td>203</td>\n",
              "      <td>Pessoas de 70 a 74 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210131</td>\n",
              "      <td>SINOVAC/BUTANTA</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-01-10</td>\n",
              "      <td>2ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>IDS Saúde</td>\n",
              "      <td>2021-01-10</td>\n",
              "      <td>18562</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6702305</th>\n",
              "      <td>818361b6-9747-435d-8bad-5d079290656f-i0b0</td>\n",
              "      <td>3717784b0535b1db0d866d510059a48dbc486892b1770a...</td>\n",
              "      <td>46.0</td>\n",
              "      <td>1974-03-02</td>\n",
              "      <td>F</td>\n",
              "      <td>99.0</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>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-02-22</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>294794</th>\n",
              "      <td>9bd9c022-735a-419b-81c2-e504fcb8de9e-i0b0</td>\n",
              "      <td>5413c66d3d0a847cf2cf6a256dedbafeb55df5c09babdc...</td>\n",
              "      <td>22.0</td>\n",
              "      <td>1999-01-07</td>\n",
              "      <td>M</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420230.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>BIGUACU</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88160.0</td>\n",
              "      <td>B</td>\n",
              "      <td>116912</td>\n",
              "      <td>MUNICIPIO DE BIGUACU</td>\n",
              "      <td>CENTRAL MUNICIPAL DE REDE DE FRIO DE BIGUACU</td>\n",
              "      <td>420230</td>\n",
              "      <td>BIGUACU</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 60 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210396</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>294790</th>\n",
              "      <td>7e9c0723-5aa7-43d7-b02b-c1bda72611c1-i0b0</td>\n",
              "      <td>a67aa706e42d40a520eb00cea14ce80761acee51ed4b9f...</td>\n",
              "      <td>24.0</td>\n",
              "      <td>1997-04-20</td>\n",
              "      <td>M</td>\n",
              "      <td>2.0</td>\n",
              "      <td>PRETA</td>\n",
              "      <td>420420.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CHAPECO</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89809.0</td>\n",
              "      <td>E</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>1601</td>\n",
              "      <td>Trabalhadores Industriais</td>\n",
              "      <td>16.0</td>\n",
              "      <td>Trabalhadores Industriais</td>\n",
              "      <td>210362</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>294788</th>\n",
              "      <td>ba479266-ba43-4b68-9685-2bb8a3358e49-i0b0</td>\n",
              "      <td>2f2a0b7b61bf9e3016ee60d5708f73d446a0eac488ba96...</td>\n",
              "      <td>18.0</td>\n",
              "      <td>2003-04-29</td>\n",
              "      <td>M</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420420.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CHAPECO</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89816.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2537583</td>\n",
              "      <td>MUNICIPIO DE CHAPECO</td>\n",
              "      <td>CENTRO DE SAUDE DA FAMILIA MARECHAL BORMANN</td>\n",
              "      <td>420420</td>\n",
              "      <td>CHAPECO</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 60 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210362</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>294812</th>\n",
              "      <td>4d893ef2-4255-4969-b90c-5ae10dd220d8-i0b0</td>\n",
              "      <td>0c79727c57a48610a5b4807b4c9eaf575a49e973391d8b...</td>\n",
              "      <td>37.0</td>\n",
              "      <td>1984-08-25</td>\n",
              "      <td>F</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421290.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>PINHALZINHO</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89870.0</td>\n",
              "      <td>B</td>\n",
              "      <td>7291868</td>\n",
              "      <td>FUNDO DE SAUDE DE PINHALZINHO</td>\n",
              "      <td>CENTRO CLINICO ESPECIALIZADO PREFEITO JOSE BRU...</td>\n",
              "      <td>421290</td>\n",
              "      <td>PINHALZINHO</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 60 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210362</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2622695</th>\n",
              "      <td>5c5272b2-c8ce-44c9-9396-00e12f8fc384-i0b0</td>\n",
              "      <td>b4e82db94e12b65ac970cd409fa4f0a640c71cbac13950...</td>\n",
              "      <td>27.0</td>\n",
              "      <td>1994-02-04</td>\n",
              "      <td>F</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420430.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CONCORDIA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89705.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2304074</td>\n",
              "      <td>MUNICIPIO DE CONCORDIA</td>\n",
              "      <td>UNIDADE SANITARIA CENTRAL</td>\n",
              "      <td>420430</td>\n",
              "      <td>CONCORDIA</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 60 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210372</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>16341</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>6822321 rows × 34 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                                    c1_document_id  ... c34_id_sistema_origem\n",
              "2341431  c0622be3-b4a1-4e4f-9c21-8e0e0cc7f7da-i0b0  ...                 18602\n",
              "1746232  4af998fc-ba82-40e6-bfd4-d2fcde965c29-i0b0  ...                 18602\n",
              "2474106  d041ac6e-fdfd-4875-944e-6d2c4cc79191-i0b0  ...                 18602\n",
              "6419100  dffd702e-cd88-4e7c-8795-e4e0a7784af2-i0b0  ...                 18562\n",
              "6702305  818361b6-9747-435d-8bad-5d079290656f-i0b0  ...                 18602\n",
              "...                                            ...  ...                   ...\n",
              "294794   9bd9c022-735a-419b-81c2-e504fcb8de9e-i0b0  ...                 16341\n",
              "294790   7e9c0723-5aa7-43d7-b02b-c1bda72611c1-i0b0  ...                 16341\n",
              "294788   ba479266-ba43-4b68-9685-2bb8a3358e49-i0b0  ...                 16341\n",
              "294812   4d893ef2-4255-4969-b90c-5ae10dd220d8-i0b0  ...                 16341\n",
              "2622695  5c5272b2-c8ce-44c9-9396-00e12f8fc384-i0b0  ...                 16341\n",
              "\n",
              "[6822321 rows x 34 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 12
        }
      ]
    },
    {
      "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": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LrgQtXGVSi6N"
      },
      "source": [
        "sc0209_07am['c35_SC_UDVE_Nr'] = sc0209_07am.apply(get_udve, axis = 1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zkQhVkXXnRzE"
      },
      "source": [
        "sc0209_07am_udve = sc0209_07am"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wJejN3ulUPWW",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 479
        },
        "outputId": "6ed327a6-ed99-480a-9343-dd71a7e384ee"
      },
      "source": [
        "sc0209_07am_udve.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>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>2341431</th>\n",
              "      <td>c0622be3-b4a1-4e4f-9c21-8e0e0cc7f7da-i0b0</td>\n",
              "      <td>8ba3755c79ba0f8bbb14ac74e74116c9b2b4cd9ba13cb6...</td>\n",
              "      <td>35.0</td>\n",
              "      <td>1985-03-17</td>\n",
              "      <td>F</td>\n",
              "      <td>3.0</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>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-04-25</td>\n",
              "      <td>18602</td>\n",
              "      <td>udve13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1746232</th>\n",
              "      <td>4af998fc-ba82-40e6-bfd4-d2fcde965c29-i0b0</td>\n",
              "      <td>537c73cbf959a7ade3fe86d87aa7ab9f8b1de3ad55a0b0...</td>\n",
              "      <td>55.0</td>\n",
              "      <td>1965-10-26</td>\n",
              "      <td>F</td>\n",
              "      <td>1.0</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>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-04-26</td>\n",
              "      <td>18602</td>\n",
              "      <td>udve13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2474106</th>\n",
              "      <td>d041ac6e-fdfd-4875-944e-6d2c4cc79191-i0b0</td>\n",
              "      <td>6e5a239adbcd1b2cea00f85873bc3d12521eb58564d489...</td>\n",
              "      <td>37.0</td>\n",
              "      <td>1983-05-18</td>\n",
              "      <td>M</td>\n",
              "      <td>1.0</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>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-03-12</td>\n",
              "      <td>18602</td>\n",
              "      <td>udve07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6419100</th>\n",
              "      <td>dffd702e-cd88-4e7c-8795-e4e0a7784af2-i0b0</td>\n",
              "      <td>4c569cc933658e66d31439b75f16bb6465acbf8e516674...</td>\n",
              "      <td>74.0</td>\n",
              "      <td>1946-12-05</td>\n",
              "      <td>M</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420380.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CANOINHAS</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89460.0</td>\n",
              "      <td>B</td>\n",
              "      <td>9522611</td>\n",
              "      <td>MUNICIPIO DE CANOINHAS</td>\n",
              "      <td>UNIDADE BASICA DE SAUDE ALVINO MUHLMANN</td>\n",
              "      <td>420380</td>\n",
              "      <td>CANOINHAS</td>\n",
              "      <td>SC</td>\n",
              "      <td>203</td>\n",
              "      <td>Pessoas de 70 a 74 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210131</td>\n",
              "      <td>SINOVAC/BUTANTA</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-01-10</td>\n",
              "      <td>2ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>IDS Saúde</td>\n",
              "      <td>2021-01-10</td>\n",
              "      <td>18562</td>\n",
              "      <td>udve07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6702305</th>\n",
              "      <td>818361b6-9747-435d-8bad-5d079290656f-i0b0</td>\n",
              "      <td>3717784b0535b1db0d866d510059a48dbc486892b1770a...</td>\n",
              "      <td>46.0</td>\n",
              "      <td>1974-03-02</td>\n",
              "      <td>F</td>\n",
              "      <td>99.0</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>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-02-22</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",
              "2341431  c0622be3-b4a1-4e4f-9c21-8e0e0cc7f7da-i0b0  ...         udve13\n",
              "1746232  4af998fc-ba82-40e6-bfd4-d2fcde965c29-i0b0  ...         udve13\n",
              "2474106  d041ac6e-fdfd-4875-944e-6d2c4cc79191-i0b0  ...         udve07\n",
              "6419100  dffd702e-cd88-4e7c-8795-e4e0a7784af2-i0b0  ...         udve07\n",
              "6702305  818361b6-9747-435d-8bad-5d079290656f-i0b0  ...         udve13\n",
              "\n",
              "[5 rows x 35 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tX8gaXlIngZj",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "15a7e6b4-68f5-452d-9be0-89e9c3ff20c9"
      },
      "source": [
        "sc0209_07am_udve.info()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 6822321 entries, 2341431 to 2622695\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                     float64       \n",
            " 3   c4_p_dn                        datetime64[ns]\n",
            " 4   c5_p_sex                       object        \n",
            " 5   c6_p_r_codigo                  float64       \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(6), int64(5), object(21)\n",
            "memory usage: 1.8+ GB\n"
          ]
        }
      ]
    },
    {
      "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": {
        "id": "46KpvC8iNs8M",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "dc67bc9f-2e90-44fe-c0ba-cd0314a940f2"
      },
      "source": [
        "v_fabri_codigo_nome_sysorig_sc0209_07am_udve = sc0209_07am_udve[['c26_v_fabricante_nome','c30_v_codigo','c31_v_nome', 'c32_sistema_origem',]].info()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 6822321 entries, 2341431 to 2622695\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: 260.3+ MB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8M7f5lHQSqtQ",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        },
        "outputId": "172dfc13-d3e1-4c48-c737-6dfe13541904"
      },
      "source": [
        "v_fabri_codigo_nome_sysorig_sc0209_07am_udve = sc0209_07am_udve[['c26_v_fabricante_nome','c30_v_codigo','c31_v_nome','c32_sistema_origem']]\n",
        "v_fabri_codigo_nome_sysorig_sc0209_07am_udve"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>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>2341431</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>1746232</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>2474106</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>6419100</th>\n",
              "      <td>SINOVAC/BUTANTA</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>IDS Saúde</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6702305</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>294794</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>294790</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>294788</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>294812</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>2622695</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",
              "  </tbody>\n",
              "</table>\n",
              "<p>6822321 rows × 4 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                c26_v_fabricante_nome  ...             c32_sistema_origem\n",
              "2341431  SERUM INSTITUTE OF INDIA LTD  ...  ESUS APS - NACIONAL (OFFLINE)\n",
              "1746232  SERUM INSTITUTE OF INDIA LTD  ...  ESUS APS - NACIONAL (OFFLINE)\n",
              "2474106  SERUM INSTITUTE OF INDIA LTD  ...  ESUS APS - NACIONAL (OFFLINE)\n",
              "6419100               SINOVAC/BUTANTA  ...                      IDS Saúde\n",
              "6702305  SERUM INSTITUTE OF INDIA LTD  ...  ESUS APS - NACIONAL (OFFLINE)\n",
              "...                               ...  ...                            ...\n",
              "294794              FUNDACAO BUTANTAN  ...                       Novo PNI\n",
              "294790              FUNDACAO BUTANTAN  ...                       Novo PNI\n",
              "294788              FUNDACAO BUTANTAN  ...                       Novo PNI\n",
              "294812              FUNDACAO BUTANTAN  ...                       Novo PNI\n",
              "2622695             FUNDACAO BUTANTAN  ...                       Novo PNI\n",
              "\n",
              "[6822321 rows x 4 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Ven-J1FtQyx7",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "3cb5a325-7afa-4d26-b2d0-e21f7b371e98"
      },
      "source": [
        "v_fabri_codigo_nome_sysorig_sc0209_07am_udve.groupby(['c26_v_fabricante_nome','c30_v_codigo','c31_v_nome','c32_sistema_origem']).size()"
      ],
      "execution_count": null,
      "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                                   5207\n",
              "                                                                                                                  G-MUS - Gestão Municipal de Saúde      13524\n",
              "                                                                                                                  G-Mus Inovadora Sistemas                 124\n",
              "ASTRAZENECA FARMACEUTICA DO BRASIL LTDA  85            Vacina Covid-19 - Covishield                               IPM istemas LTDA                        5186\n",
              "BUTANTAN                                 86            Covid-19-Coronavac-Sinovac/Butantan                        IPM istemas LTDA                         424\n",
              "BioNTech/Fosun Pharma/Pfizer             87            Vacina covid-19 - BNT162b2 - BioNTech/Fosun Pharma/Pfizer  ESUS APS - NACIONAL (OFFLINE)         264837\n",
              "FUNDACAO BUTANTAN                        86            Covid-19-Coronavac-Sinovac/Butantan                        G-MUS                                  99097\n",
              "                                                                                                                  G-MUS - Gestão Municipal de Saúde      55691\n",
              "                                                                                                                  G-Mus Inovadora Sistemas                 573\n",
              "                                                                                                                  IDS Saúde                                 34\n",
              "                                                                                                                  Novo PNI                             1358215\n",
              "                                                                                                                  Saudetech                              65618\n",
              "                                                                                                                  saudetech                              11909\n",
              "FUNDACAO OSWALDO CRUZ                    85            Vacina Covid-19 - Covishield                               G-MUS                                 156716\n",
              "                                                                                                                  G-MUS - Gestão Municipal de Saúde      83205\n",
              "                                                                                                                  G-Mus Inovadora Sistemas                2164\n",
              "                                                                                                                  IDS Saúde                               3995\n",
              "                                                                                                                  Novo PNI                             1985905\n",
              "                                                                                                                  Saudetech                             115144\n",
              "                                                                                                                  saudetech                              33658\n",
              "                                         86            Covid-19-Coronavac-Sinovac/Butantan                        IDS Saúde                                  2\n",
              "                                                                                                                  Novo PNI                                  11\n",
              "                                         88            Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag              IDS Saúde                                  1\n",
              "INTERVAX LTDA                            85            Vacina Covid-19 - Covishield                               IDS Saúde                                  1\n",
              "JANSSEN                                  88            Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag              IPM istemas LTDA                         378\n",
              "JANSSEN-CILAG                            88            Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag              IDS SAUDE                                809\n",
              "                                                                                                                  IDS Saúde                               5443\n",
              "                                                                                                                  Saudetech                              11213\n",
              "                                                                                                                  saudetech                               2833\n",
              "Janssen Biologics B.V.                   88            Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag              G-MUS                                  14084\n",
              "                                                                                                                  G-MUS - Gestão Municipal de Saúde       7855\n",
              "                                                                                                                  G-Mus Inovadora Sistemas                 171\n",
              "Janssen-Cilag                            88            Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag              ESUS APS - NACIONAL (OFFLINE)          33747\n",
              "MINISTERIO DA SAUDE                      87            Vacina covid-19 - BNT162b2 - BioNTech/Fosun Pharma/Pfizer  Novo PNI                              952391\n",
              "                                         88            Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag              Novo PNI                              160267\n",
              "                                         89            Covid-19-AstraZeneca                                       Novo PNI                              216075\n",
              "OXFORD-ASTRAZENECA                       85            Vacina Covid-19 - Covishield                               IDS SAUDE                               9444\n",
              "                                                                                                                  IDS Saúde                              58381\n",
              "                                         86            Covid-19-Coronavac-Sinovac/Butantan                        IDS Saúde                                 10\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                        2461\n",
              "PFIZER/BIONTECH                          86            Covid-19-Coronavac-Sinovac/Butantan                        IDS Saúde                                  2\n",
              "                                         87            Vacina covid-19 - BNT162b2 - BioNTech/Fosun Pharma/Pfizer  G-MUS                                 104381\n",
              "                                                                                                                  G-MUS - Gestão Municipal de Saúde      62236\n",
              "                                                                                                                  G-Mus Inovadora Sistemas                 789\n",
              "                                                                                                                  IDS SAUDE                               4731\n",
              "                                                                                                                  IDS Saúde                              24750\n",
              "                                                                                                                  Saudetech                              70114\n",
              "                                                                                                                  saudetech                              16321\n",
              "SERUM INSTITUTE OF INDIA LTD             86            Covid-19-Coronavac-Sinovac/Butantan                        ESUS APS - NACIONAL (OFFLINE)         307621\n",
              "SERUM INSTITUTE OF INDIA LTD.            85            Vacina Covid-19 - Covishield                               G-MUS                                     71\n",
              "                                                                                                                  IDS Saúde                                  1\n",
              "SINOVAC LIFE SCIENCE CO LTD              85            Vacina Covid-19 - Covishield                               ESUS APS - NACIONAL (OFFLINE)         459561\n",
              "SINOVAC/BUTANTA                          85            Vacina Covid-19 - Covishield                               IDS Saúde                                  5\n",
              "                                         86            Covid-19-Coronavac-Sinovac/Butantan                        IDS Saúde                               7993\n",
              "                                         88            Vacina covid-19 - Ad26.COV2.S - Janssen-Cilag              IDS Saúde                                  2\n",
              "SINOVAC/BUTANTAN                         86            Covid-19-Coronavac-Sinovac/Butantan                        IDS SAUDE                               5194\n",
              "                                                                                                                  IDS Saúde                              21744\n",
              "dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ]
    },
    {
      "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'] == 88 and row['c31_v_nome'] == 'Vacina Covid-19 - Covishield':\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'] == '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": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oQPR4WD360hg"
      },
      "source": [
        "#<font color = 'brown'> When compared to last previous update on 04 Aug 2021, we notice the following changes in the 'Combination of Concern' categories:\n",
        "\n",
        "*   <font color = 'blue'> A significant increase of CoC_1 frequency. \n",
        "*   <font color = 'blue'> No CoC_2 group in August 21 (there was 2 in May);\n",
        "*   <font color = 'blue'> A small reduction on the nr of CoC_3 category.\n",
        "\n",
        "The data above may suggest that clean up of the database is not happening in systematic and large scale for the CoC_1 category.\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VL-ncW_iykzT"
      },
      "source": [
        "sc0209_07am_udve['c_36_Combination_of_Concern'] = sc0209_07am_udve.apply(combination_of_concern, axis=1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Yc_72JsiPzwz"
      },
      "source": [
        "sc0209_07am_udve_CoC = sc0209_07am_udve"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "b-HUAnYkQBQr",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "6c1f1cd9-508b-49f1-ab95-ab1a1d5706b6"
      },
      "source": [
        "sc0209_07am_udve_CoC.c_36_Combination_of_Concern.unique()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array(['CoC_1', 'no_issue', 'CoC_3'], dtype=object)"
            ]
          },
          "metadata": {},
          "execution_count": 24
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2shASYKpUOHJ",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 932
        },
        "outputId": "60163cfa-4c6f-48ea-c561-0eef1f67271b"
      },
      "source": [
        "sc0209_07am_udve_CoC"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>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>2341431</th>\n",
              "      <td>c0622be3-b4a1-4e4f-9c21-8e0e0cc7f7da-i0b0</td>\n",
              "      <td>8ba3755c79ba0f8bbb14ac74e74116c9b2b4cd9ba13cb6...</td>\n",
              "      <td>35.0</td>\n",
              "      <td>1985-03-17</td>\n",
              "      <td>F</td>\n",
              "      <td>3.0</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>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-04-25</td>\n",
              "      <td>18602</td>\n",
              "      <td>udve13</td>\n",
              "      <td>CoC_1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1746232</th>\n",
              "      <td>4af998fc-ba82-40e6-bfd4-d2fcde965c29-i0b0</td>\n",
              "      <td>537c73cbf959a7ade3fe86d87aa7ab9f8b1de3ad55a0b0...</td>\n",
              "      <td>55.0</td>\n",
              "      <td>1965-10-26</td>\n",
              "      <td>F</td>\n",
              "      <td>1.0</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>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-04-26</td>\n",
              "      <td>18602</td>\n",
              "      <td>udve13</td>\n",
              "      <td>CoC_1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2474106</th>\n",
              "      <td>d041ac6e-fdfd-4875-944e-6d2c4cc79191-i0b0</td>\n",
              "      <td>6e5a239adbcd1b2cea00f85873bc3d12521eb58564d489...</td>\n",
              "      <td>37.0</td>\n",
              "      <td>1983-05-18</td>\n",
              "      <td>M</td>\n",
              "      <td>1.0</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>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-03-12</td>\n",
              "      <td>18602</td>\n",
              "      <td>udve07</td>\n",
              "      <td>CoC_1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6419100</th>\n",
              "      <td>dffd702e-cd88-4e7c-8795-e4e0a7784af2-i0b0</td>\n",
              "      <td>4c569cc933658e66d31439b75f16bb6465acbf8e516674...</td>\n",
              "      <td>74.0</td>\n",
              "      <td>1946-12-05</td>\n",
              "      <td>M</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420380.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CANOINHAS</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89460.0</td>\n",
              "      <td>B</td>\n",
              "      <td>9522611</td>\n",
              "      <td>MUNICIPIO DE CANOINHAS</td>\n",
              "      <td>UNIDADE BASICA DE SAUDE ALVINO MUHLMANN</td>\n",
              "      <td>420380</td>\n",
              "      <td>CANOINHAS</td>\n",
              "      <td>SC</td>\n",
              "      <td>203</td>\n",
              "      <td>Pessoas de 70 a 74 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210131</td>\n",
              "      <td>SINOVAC/BUTANTA</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2021-01-10</td>\n",
              "      <td>2ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>IDS Saúde</td>\n",
              "      <td>2021-01-10</td>\n",
              "      <td>18562</td>\n",
              "      <td>udve07</td>\n",
              "      <td>no_issue</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6702305</th>\n",
              "      <td>818361b6-9747-435d-8bad-5d079290656f-i0b0</td>\n",
              "      <td>3717784b0535b1db0d866d510059a48dbc486892b1770a...</td>\n",
              "      <td>46.0</td>\n",
              "      <td>1974-03-02</td>\n",
              "      <td>F</td>\n",
              "      <td>99.0</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>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>ESUS APS - NACIONAL (OFFLINE)</td>\n",
              "      <td>2021-02-22</td>\n",
              "      <td>18602</td>\n",
              "      <td>udve13</td>\n",
              "      <td>CoC_1</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",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>294794</th>\n",
              "      <td>9bd9c022-735a-419b-81c2-e504fcb8de9e-i0b0</td>\n",
              "      <td>5413c66d3d0a847cf2cf6a256dedbafeb55df5c09babdc...</td>\n",
              "      <td>22.0</td>\n",
              "      <td>1999-01-07</td>\n",
              "      <td>M</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420230.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>BIGUACU</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>88160.0</td>\n",
              "      <td>B</td>\n",
              "      <td>116912</td>\n",
              "      <td>MUNICIPIO DE BIGUACU</td>\n",
              "      <td>CENTRAL MUNICIPAL DE REDE DE FRIO DE BIGUACU</td>\n",
              "      <td>420230</td>\n",
              "      <td>BIGUACU</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 60 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210396</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>16341</td>\n",
              "      <td>udve13</td>\n",
              "      <td>no_issue</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>294790</th>\n",
              "      <td>7e9c0723-5aa7-43d7-b02b-c1bda72611c1-i0b0</td>\n",
              "      <td>a67aa706e42d40a520eb00cea14ce80761acee51ed4b9f...</td>\n",
              "      <td>24.0</td>\n",
              "      <td>1997-04-20</td>\n",
              "      <td>M</td>\n",
              "      <td>2.0</td>\n",
              "      <td>PRETA</td>\n",
              "      <td>420420.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CHAPECO</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89809.0</td>\n",
              "      <td>E</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>1601</td>\n",
              "      <td>Trabalhadores Industriais</td>\n",
              "      <td>16.0</td>\n",
              "      <td>Trabalhadores Industriais</td>\n",
              "      <td>210362</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>16341</td>\n",
              "      <td>udve04</td>\n",
              "      <td>no_issue</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>294788</th>\n",
              "      <td>ba479266-ba43-4b68-9685-2bb8a3358e49-i0b0</td>\n",
              "      <td>2f2a0b7b61bf9e3016ee60d5708f73d446a0eac488ba96...</td>\n",
              "      <td>18.0</td>\n",
              "      <td>2003-04-29</td>\n",
              "      <td>M</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420420.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CHAPECO</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89816.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2537583</td>\n",
              "      <td>MUNICIPIO DE CHAPECO</td>\n",
              "      <td>CENTRO DE SAUDE DA FAMILIA MARECHAL BORMANN</td>\n",
              "      <td>420420</td>\n",
              "      <td>CHAPECO</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 60 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210362</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>16341</td>\n",
              "      <td>udve03</td>\n",
              "      <td>no_issue</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>294812</th>\n",
              "      <td>4d893ef2-4255-4969-b90c-5ae10dd220d8-i0b0</td>\n",
              "      <td>0c79727c57a48610a5b4807b4c9eaf575a49e973391d8b...</td>\n",
              "      <td>37.0</td>\n",
              "      <td>1984-08-25</td>\n",
              "      <td>F</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>421290.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>PINHALZINHO</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89870.0</td>\n",
              "      <td>B</td>\n",
              "      <td>7291868</td>\n",
              "      <td>FUNDO DE SAUDE DE PINHALZINHO</td>\n",
              "      <td>CENTRO CLINICO ESPECIALIZADO PREFEITO JOSE BRU...</td>\n",
              "      <td>421290</td>\n",
              "      <td>PINHALZINHO</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 60 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210362</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>16341</td>\n",
              "      <td>udve03</td>\n",
              "      <td>no_issue</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2622695</th>\n",
              "      <td>5c5272b2-c8ce-44c9-9396-00e12f8fc384-i0b0</td>\n",
              "      <td>b4e82db94e12b65ac970cd409fa4f0a640c71cbac13950...</td>\n",
              "      <td>27.0</td>\n",
              "      <td>1994-02-04</td>\n",
              "      <td>F</td>\n",
              "      <td>1.0</td>\n",
              "      <td>BRANCA</td>\n",
              "      <td>420430.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>CONCORDIA</td>\n",
              "      <td>BRASIL</td>\n",
              "      <td>SC</td>\n",
              "      <td>89705.0</td>\n",
              "      <td>B</td>\n",
              "      <td>2304074</td>\n",
              "      <td>MUNICIPIO DE CONCORDIA</td>\n",
              "      <td>UNIDADE SANITARIA CENTRAL</td>\n",
              "      <td>420430</td>\n",
              "      <td>CONCORDIA</td>\n",
              "      <td>SC</td>\n",
              "      <td>201</td>\n",
              "      <td>Pessoas de 60 a 64 anos</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Faixa Etária</td>\n",
              "      <td>210372</td>\n",
              "      <td>FUNDACAO BUTANTAN</td>\n",
              "      <td>Organization/61189445000156</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>1ª Dose</td>\n",
              "      <td>86</td>\n",
              "      <td>Covid-19-Coronavac-Sinovac/Butantan</td>\n",
              "      <td>Novo PNI</td>\n",
              "      <td>2021-08-30</td>\n",
              "      <td>16341</td>\n",
              "      <td>udve04</td>\n",
              "      <td>no_issue</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>6822321 rows × 36 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                                    c1_document_id  ... c_36_Combination_of_Concern\n",
              "2341431  c0622be3-b4a1-4e4f-9c21-8e0e0cc7f7da-i0b0  ...                       CoC_1\n",
              "1746232  4af998fc-ba82-40e6-bfd4-d2fcde965c29-i0b0  ...                       CoC_1\n",
              "2474106  d041ac6e-fdfd-4875-944e-6d2c4cc79191-i0b0  ...                       CoC_1\n",
              "6419100  dffd702e-cd88-4e7c-8795-e4e0a7784af2-i0b0  ...                    no_issue\n",
              "6702305  818361b6-9747-435d-8bad-5d079290656f-i0b0  ...                       CoC_1\n",
              "...                                            ...  ...                         ...\n",
              "294794   9bd9c022-735a-419b-81c2-e504fcb8de9e-i0b0  ...                    no_issue\n",
              "294790   7e9c0723-5aa7-43d7-b02b-c1bda72611c1-i0b0  ...                    no_issue\n",
              "294788   ba479266-ba43-4b68-9685-2bb8a3358e49-i0b0  ...                    no_issue\n",
              "294812   4d893ef2-4255-4969-b90c-5ae10dd220d8-i0b0  ...                    no_issue\n",
              "2622695  5c5272b2-c8ce-44c9-9396-00e12f8fc384-i0b0  ...                    no_issue\n",
              "\n",
              "[6822321 rows x 36 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 25
        }
      ]
    },
    {
      "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": {
        "id": "1Jwo700GCDnP",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 418
        },
        "outputId": "a626b5de-ed94-4dc4-d769-374b7f48725d"
      },
      "source": [
        "udve_CoC = pd.crosstab(sc0209_07am_udve_CoC.c35_SC_UDVE_Nr , sc0209_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": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<style  type=\"text/css\" >\n",
              "#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row0_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row0_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row0_col2,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row3_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row3_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row3_col2,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row7_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row7_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row7_col2,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row10_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row10_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row10_col2,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row16_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row16_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row16_col2{\n",
              "            text-align:  right;\n",
              "            background-color:  white;\n",
              "        }#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row1_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row1_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row1_col2,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row2_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row2_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row2_col2,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row5_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row5_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row5_col2,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row6_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row6_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row6_col2,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row8_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row8_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row8_col2,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row9_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row9_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row9_col2{\n",
              "            text-align:  right;\n",
              "            background-color:  orange;\n",
              "        }#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row4_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row4_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row4_col2,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row11_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row11_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row11_col2,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row13_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row13_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row13_col2,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row14_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row14_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row14_col2,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row15_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row15_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row15_col2{\n",
              "            text-align:  right;\n",
              "            background-color:  yellow;\n",
              "        }#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row12_col0,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row12_col1,#T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row12_col2{\n",
              "            text-align:  right;\n",
              "            background-color:  red;\n",
              "        }</style><table id=\"T_9712bf58_0d8e_11ec_a21b_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_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row0\" class=\"row_heading level0 row0\" >udve01</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row0_col0\" class=\"data row0 col0\" >801</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row0_col1\" class=\"data row0 col1\" >0</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row0_col2\" class=\"data row0 col2\" >205,327</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row1\" class=\"row_heading level0 row1\" >udve02</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row1_col0\" class=\"data row1 col0\" >13,897</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row1_col1\" class=\"data row1 col1\" >0</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row1_col2\" class=\"data row1 col2\" >132,596</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row2\" class=\"row_heading level0 row2\" >udve03</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row2_col0\" class=\"data row2 col0\" >21,240</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row2_col1\" class=\"data row2 col1\" >0</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row2_col2\" class=\"data row2 col2\" >462,773</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row3\" class=\"row_heading level0 row3\" >udve04</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row3_col0\" class=\"data row3 col0\" >46</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row3_col1\" class=\"data row3 col1\" >0</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row3_col2\" class=\"data row3 col2\" >153,603</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row4\" class=\"row_heading level0 row4\" >udve05</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row4_col0\" class=\"data row4 col0\" >1,074</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row4_col1\" class=\"data row4 col1\" >0</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row4_col2\" class=\"data row4 col2\" >200,399</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row5\" class=\"row_heading level0 row5\" >udve06</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row5_col0\" class=\"data row5 col0\" >36,221</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row5_col1\" class=\"data row5 col1\" >0</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row5_col2\" class=\"data row5 col2\" >230,489</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row6\" class=\"row_heading level0 row6\" >udve07</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row6_col0\" class=\"data row6 col0\" >47,218</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row6_col1\" class=\"data row6 col1\" >28</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row6_col2\" class=\"data row6 col2\" >262,479</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row7\" class=\"row_heading level0 row7\" >udve08</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row7_col0\" class=\"data row7 col0\" >0</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row7_col1\" class=\"data row7 col1\" >0</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row7_col2\" class=\"data row7 col2\" >237,516</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row8\" class=\"row_heading level0 row8\" >udve09</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row8_col0\" class=\"data row8 col0\" >47,827</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row8_col1\" class=\"data row8 col1\" >1</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row8_col2\" class=\"data row8 col2\" >651,320</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row9\" class=\"row_heading level0 row9\" >udve10</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row9_col0\" class=\"data row9 col0\" >20,990</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row9_col1\" class=\"data row9 col1\" >0</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row9_col2\" class=\"data row9 col2\" >625,220</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row10\" class=\"row_heading level0 row10\" >udve11</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row10_col0\" class=\"data row10 col0\" >56</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row10_col1\" class=\"data row10 col1\" >0</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row10_col2\" class=\"data row10 col2\" >273,306</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row11\" class=\"row_heading level0 row11\" >udve12</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row11_col0\" class=\"data row11 col0\" >5,158</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row11_col1\" class=\"data row11 col1\" >0</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row11_col2\" class=\"data row11 col2\" >671,219</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row12\" class=\"row_heading level0 row12\" >udve13</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row12_col0\" class=\"data row12 col0\" >568,143</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row12_col1\" class=\"data row12 col1\" >0</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row12_col2\" class=\"data row12 col2\" >676,604</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row13\" class=\"row_heading level0 row13\" >udve14</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row13_col0\" class=\"data row13 col0\" >1,453</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row13_col1\" class=\"data row13 col1\" >0</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row13_col2\" class=\"data row13 col2\" >288,156</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row14\" class=\"row_heading level0 row14\" >udve15</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row14_col0\" class=\"data row14 col0\" >1,520</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row14_col1\" class=\"data row14 col1\" >0</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row14_col2\" class=\"data row14 col2\" >374,073</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row15\" class=\"row_heading level0 row15\" >udve16</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row15_col0\" class=\"data row15 col0\" >1,399</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row15_col1\" class=\"data row15 col1\" >0</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row15_col2\" class=\"data row15 col2\" >404,980</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002level0_row16\" class=\"row_heading level0 row16\" >udve17</th>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row16_col0\" class=\"data row16 col0\" >139</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row16_col1\" class=\"data row16 col1\" >3</td>\n",
              "                        <td id=\"T_9712bf58_0d8e_11ec_a21b_0242ac1c0002row16_col2\" class=\"data row16 col2\" >190,017</td>\n",
              "            </tr>\n",
              "    </tbody></table>"
            ],
            "text/plain": [
              "<pandas.io.formats.style.Styler at 0x7fa456ea0810>"
            ]
          },
          "metadata": {},
          "execution_count": 26
        }
      ]
    },
    {
      "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, 3 and 10 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": {
        "id": "xvtWptwbzK-h",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 418
        },
        "outputId": "a5967b26-983b-4f0d-a502-24033ca14bb2"
      },
      "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": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<style  type=\"text/css\" >\n",
              "#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row0_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row0_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row0_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row1_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row1_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row1_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row2_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row2_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row2_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row3_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row3_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row3_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row4_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row4_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row4_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row5_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row5_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row5_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row6_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row6_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row6_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row7_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row7_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row7_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row8_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row8_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row8_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row9_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row9_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row9_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row10_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row10_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row10_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row11_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row11_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row11_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row12_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row12_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row12_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row13_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row13_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row13_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row14_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row14_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row14_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row15_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row15_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row15_col2,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row16_col0,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row16_col1,#T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row16_col2{\n",
              "            text-align:  right;\n",
              "        }</style><table id=\"T_9717aa4a_0d8e_11ec_a21b_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_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row0\" class=\"row_heading level0 row0\" >udve01</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row0_col0\" class=\"data row0 col0\" >0.39%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row0_col1\" class=\"data row0 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row0_col2\" class=\"data row0 col2\" >99.61%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row1\" class=\"row_heading level0 row1\" >udve02</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row1_col0\" class=\"data row1 col0\" >9.49%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row1_col1\" class=\"data row1 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row1_col2\" class=\"data row1 col2\" >90.51%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row2\" class=\"row_heading level0 row2\" >udve03</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row2_col0\" class=\"data row2 col0\" >4.39%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row2_col1\" class=\"data row2 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row2_col2\" class=\"data row2 col2\" >95.61%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row3\" class=\"row_heading level0 row3\" >udve04</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row3_col0\" class=\"data row3 col0\" >0.03%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row3_col1\" class=\"data row3 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row3_col2\" class=\"data row3 col2\" >99.97%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row4\" class=\"row_heading level0 row4\" >udve05</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row4_col0\" class=\"data row4 col0\" >0.53%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row4_col1\" class=\"data row4 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row4_col2\" class=\"data row4 col2\" >99.47%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row5\" class=\"row_heading level0 row5\" >udve06</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row5_col0\" class=\"data row5 col0\" >13.58%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row5_col1\" class=\"data row5 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row5_col2\" class=\"data row5 col2\" >86.42%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row6\" class=\"row_heading level0 row6\" >udve07</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row6_col0\" class=\"data row6 col0\" >15.25%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row6_col1\" class=\"data row6 col1\" >0.01%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row6_col2\" class=\"data row6 col2\" >84.75%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row7\" class=\"row_heading level0 row7\" >udve08</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row7_col0\" class=\"data row7 col0\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row7_col1\" class=\"data row7 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row7_col2\" class=\"data row7 col2\" >100.00%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row8\" class=\"row_heading level0 row8\" >udve09</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row8_col0\" class=\"data row8 col0\" >6.84%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row8_col1\" class=\"data row8 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row8_col2\" class=\"data row8 col2\" >93.16%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row9\" class=\"row_heading level0 row9\" >udve10</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row9_col0\" class=\"data row9 col0\" >3.25%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row9_col1\" class=\"data row9 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row9_col2\" class=\"data row9 col2\" >96.75%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row10\" class=\"row_heading level0 row10\" >udve11</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row10_col0\" class=\"data row10 col0\" >0.02%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row10_col1\" class=\"data row10 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row10_col2\" class=\"data row10 col2\" >99.98%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row11\" class=\"row_heading level0 row11\" >udve12</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row11_col0\" class=\"data row11 col0\" >0.76%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row11_col1\" class=\"data row11 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row11_col2\" class=\"data row11 col2\" >99.24%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row12\" class=\"row_heading level0 row12\" >udve13</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row12_col0\" class=\"data row12 col0\" >45.64%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row12_col1\" class=\"data row12 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row12_col2\" class=\"data row12 col2\" >54.36%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row13\" class=\"row_heading level0 row13\" >udve14</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row13_col0\" class=\"data row13 col0\" >0.50%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row13_col1\" class=\"data row13 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row13_col2\" class=\"data row13 col2\" >99.50%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row14\" class=\"row_heading level0 row14\" >udve15</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row14_col0\" class=\"data row14 col0\" >0.40%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row14_col1\" class=\"data row14 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row14_col2\" class=\"data row14 col2\" >99.60%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row15\" class=\"row_heading level0 row15\" >udve16</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row15_col0\" class=\"data row15 col0\" >0.34%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row15_col1\" class=\"data row15 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row15_col2\" class=\"data row15 col2\" >99.66%</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002level0_row16\" class=\"row_heading level0 row16\" >udve17</th>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row16_col0\" class=\"data row16 col0\" >0.07%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row16_col1\" class=\"data row16 col1\" >0.00%</td>\n",
              "                        <td id=\"T_9717aa4a_0d8e_11ec_a21b_0242ac1c0002row16_col2\" class=\"data row16 col2\" >99.93%</td>\n",
              "            </tr>\n",
              "    </tbody></table>"
            ],
            "text/plain": [
              "<pandas.io.formats.style.Styler at 0x7fa31223a9d0>"
            ]
          },
          "metadata": {},
          "execution_count": 27
        }
      ]
    },
    {
      "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": {
        "id": "MZPUwY8LtDFd",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "b9ec3c63-8bb0-4f8f-e60a-307a2bb2c1be"
      },
      "source": [
        "udve_13 = sc0209_07am_udve_CoC.loc[sc0209_07am_udve_CoC['c35_SC_UDVE_Nr'] == 'udve13']\n",
        "udve_13.shape\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(1244747, 36)"
            ]
          },
          "metadata": {},
          "execution_count": 28
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_ts6nB9PzWB5",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 523
        },
        "outputId": "093a6210-6261-4d18-e3f4-87649e5504fc"
      },
      "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": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<style  type=\"text/css\" >\n",
              "#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row0_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row0_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row1_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row1_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row2_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row2_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row3_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row3_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row4_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row4_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row5_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row5_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row6_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row6_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row7_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row7_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row8_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row8_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row9_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row9_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row10_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row10_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row11_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row11_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row12_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row12_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row13_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row13_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row14_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row14_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row15_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row15_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row16_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row16_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row17_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row17_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row18_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row18_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row19_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row19_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row20_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row20_col1,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row21_col0,#T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row21_col1{\n",
              "            text-align:  right;\n",
              "        }</style><table id=\"T_9aef8a20_0d8e_11ec_a21b_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_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row0\" class=\"row_heading level0 row0\" >AGUAS MORNAS</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row0_col0\" class=\"data row0 col0\" >0</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row0_col1\" class=\"data row0 col1\" >7,284</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row1\" class=\"row_heading level0 row1\" >ALFREDO WAGNER</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row1_col0\" class=\"data row1 col0\" >0</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row1_col1\" class=\"data row1 col1\" >9,683</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row2\" class=\"row_heading level0 row2\" >ANGELINA</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row2_col0\" class=\"data row2 col0\" >0</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row2_col1\" class=\"data row2 col1\" >6,280</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row3\" class=\"row_heading level0 row3\" >ANITAPOLIS</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row3_col0\" class=\"data row3 col0\" >765</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row3_col1\" class=\"data row3 col1\" >3,161</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row4\" class=\"row_heading level0 row4\" >ANTONIO CARLOS</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row4_col0\" class=\"data row4 col0\" >688</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row4_col1\" class=\"data row4 col1\" >9,741</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row5\" class=\"row_heading level0 row5\" >BIGUACU</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row5_col0\" class=\"data row5 col0\" >0</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row5_col1\" class=\"data row5 col1\" >66,925</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row6\" class=\"row_heading level0 row6\" >CANELINHA</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row6_col0\" class=\"data row6 col0\" >0</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row6_col1\" class=\"data row6 col1\" >10,379</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row7\" class=\"row_heading level0 row7\" >FLORIANOPOLIS</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row7_col0\" class=\"data row7 col0\" >416,510</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row7_col1\" class=\"data row7 col1\" >174,933</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row8\" class=\"row_heading level0 row8\" >GAROPABA</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row8_col0\" class=\"data row8 col0\" >0</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row8_col1\" class=\"data row8 col1\" >28,024</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row9\" class=\"row_heading level0 row9\" >GOVERNADOR CELSO RAMOS</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row9_col0\" class=\"data row9 col0\" >33</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row9_col1\" class=\"data row9 col1\" >16,533</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row10\" class=\"row_heading level0 row10\" >LEOBERTO LEAL</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row10_col0\" class=\"data row10 col0\" >0</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row10_col1\" class=\"data row10 col1\" >3,721</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row11\" class=\"row_heading level0 row11\" >MAJOR GERCINO</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row11_col0\" class=\"data row11 col0\" >0</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row11_col1\" class=\"data row11 col1\" >3,662</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row12\" class=\"row_heading level0 row12\" >NOVA TRENTO</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row12_col0\" class=\"data row12 col0\" >883</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row12_col1\" class=\"data row12 col1\" >12,812</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row13\" class=\"row_heading level0 row13\" >PALHOCA</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row13_col0\" class=\"data row13 col0\" >0</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row13_col1\" class=\"data row13 col1\" >172,403</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row14\" class=\"row_heading level0 row14\" >PAULO LOPES</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row14_col0\" class=\"data row14 col0\" >21</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row14_col1\" class=\"data row14 col1\" >7,847</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row15\" class=\"row_heading level0 row15\" >RANCHO QUEIMADO</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row15_col0\" class=\"data row15 col0\" >252</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row15_col1\" class=\"data row15 col1\" >3,768</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row16\" class=\"row_heading level0 row16\" >SANTO AMARO DA IMPERATRIZ</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row16_col0\" class=\"data row16 col0\" >4,225</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row16_col1\" class=\"data row16 col1\" >22,584</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row17\" class=\"row_heading level0 row17\" >SAO BONIFACIO</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row17_col0\" class=\"data row17 col0\" >0</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row17_col1\" class=\"data row17 col1\" >3,682</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row18\" class=\"row_heading level0 row18\" >SAO JOAO BATISTA</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row18_col0\" class=\"data row18 col0\" >296</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row18_col1\" class=\"data row18 col1\" >23,992</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row19\" class=\"row_heading level0 row19\" >SAO JOSE</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row19_col0\" class=\"data row19 col0\" >119,589</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row19_col1\" class=\"data row19 col1\" >76,327</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row20\" class=\"row_heading level0 row20\" >SAO PEDRO DE ALCANTARA</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row20_col0\" class=\"data row20 col0\" >243</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row20_col1\" class=\"data row20 col1\" >4,088</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002level0_row21\" class=\"row_heading level0 row21\" >TIJUCAS</th>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row21_col0\" class=\"data row21 col0\" >24,638</td>\n",
              "                        <td id=\"T_9aef8a20_0d8e_11ec_a21b_0242ac1c0002row21_col1\" class=\"data row21 col1\" >8,775</td>\n",
              "            </tr>\n",
              "    </tbody></table>"
            ],
            "text/plain": [
              "<pandas.io.formats.style.Styler at 0x7fa458275810>"
            ]
          },
          "metadata": {},
          "execution_count": 29
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Y95EQpZO2qhM"
      },
      "source": [
        "#<Font color = 'orange'>**UDVE 07**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wcOZ8Aes0bV8",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "3ab59d4e-34df-4f34-ea34-ee6f1d7d3bc3"
      },
      "source": [
        "udve_07 = sc0209_07am_udve_CoC.loc[sc0209_07am_udve_CoC['c35_SC_UDVE_Nr'] == 'udve07']\n",
        "udve_07.shape"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(309725, 36)"
            ]
          },
          "metadata": {},
          "execution_count": 30
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "e2bJJnsC0ptS",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 334
        },
        "outputId": "9ac3356a-5a75-42f6-ef1e-fe5844b55f74"
      },
      "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": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<style  type=\"text/css\" >\n",
              "#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row0_col0,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row0_col1,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row0_col2,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row1_col0,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row1_col1,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row1_col2,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row2_col0,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row2_col1,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row2_col2,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row3_col0,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row3_col1,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row3_col2,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row4_col0,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row4_col1,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row4_col2,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row5_col0,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row5_col1,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row5_col2,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row6_col0,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row6_col1,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row6_col2,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row7_col0,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row7_col1,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row7_col2,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row8_col0,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row8_col1,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row8_col2,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row9_col0,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row9_col1,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row9_col2,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row10_col0,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row10_col1,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row10_col2,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row11_col0,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row11_col1,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row11_col2,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row12_col0,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row12_col1,#T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row12_col2{\n",
              "            text-align:  right;\n",
              "        }</style><table id=\"T_9b74db4e_0d8e_11ec_a21b_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_9b74db4e_0d8e_11ec_a21b_0242ac1c0002level0_row0\" class=\"row_heading level0 row0\" >BELA VISTA DO TOLDO</th>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row0_col0\" class=\"data row0 col0\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row0_col1\" class=\"data row0 col1\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row0_col2\" class=\"data row0 col2\" >5,330</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002level0_row1\" class=\"row_heading level0 row1\" >CAMPO ALEGRE</th>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row1_col0\" class=\"data row1 col0\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row1_col1\" class=\"data row1 col1\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row1_col2\" class=\"data row1 col2\" >12,076</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002level0_row2\" class=\"row_heading level0 row2\" >CANOINHAS</th>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row2_col0\" class=\"data row2 col0\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row2_col1\" class=\"data row2 col1\" >21</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row2_col2\" class=\"data row2 col2\" >42,181</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002level0_row3\" class=\"row_heading level0 row3\" >IRINEOPOLIS</th>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row3_col0\" class=\"data row3 col0\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row3_col1\" class=\"data row3 col1\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row3_col2\" class=\"data row3 col2\" >10,158</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002level0_row4\" class=\"row_heading level0 row4\" >ITAIOPOLIS</th>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row4_col0\" class=\"data row4 col0\" >62</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row4_col1\" class=\"data row4 col1\" >7</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row4_col2\" class=\"data row4 col2\" >19,789</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002level0_row5\" class=\"row_heading level0 row5\" >MAFRA</th>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row5_col0\" class=\"data row5 col0\" >42,738</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row5_col1\" class=\"data row5 col1\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row5_col2\" class=\"data row5 col2\" >12,675</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002level0_row6\" class=\"row_heading level0 row6\" >MAJOR VIEIRA</th>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row6_col0\" class=\"data row6 col0\" >33</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row6_col1\" class=\"data row6 col1\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row6_col2\" class=\"data row6 col2\" >6,966</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002level0_row7\" class=\"row_heading level0 row7\" >MONTE CASTELO</th>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row7_col0\" class=\"data row7 col0\" >342</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row7_col1\" class=\"data row7 col1\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row7_col2\" class=\"data row7 col2\" >7,949</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002level0_row8\" class=\"row_heading level0 row8\" >PAPANDUVA</th>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row8_col0\" class=\"data row8 col0\" >97</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row8_col1\" class=\"data row8 col1\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row8_col2\" class=\"data row8 col2\" >18,169</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002level0_row9\" class=\"row_heading level0 row9\" >PORTO UNIAO</th>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row9_col0\" class=\"data row9 col0\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row9_col1\" class=\"data row9 col1\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row9_col2\" class=\"data row9 col2\" >33,902</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002level0_row10\" class=\"row_heading level0 row10\" >RIO NEGRINHO</th>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row10_col0\" class=\"data row10 col0\" >3,936</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row10_col1\" class=\"data row10 col1\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row10_col2\" class=\"data row10 col2\" >1,780</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002level0_row11\" class=\"row_heading level0 row11\" >SAO BENTO DO SUL</th>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row11_col0\" class=\"data row11 col0\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row11_col1\" class=\"data row11 col1\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row11_col2\" class=\"data row11 col2\" >72,940</td>\n",
              "            </tr>\n",
              "            <tr>\n",
              "                        <th id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002level0_row12\" class=\"row_heading level0 row12\" >TRES BARRAS</th>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row12_col0\" class=\"data row12 col0\" >10</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row12_col1\" class=\"data row12 col1\" >0</td>\n",
              "                        <td id=\"T_9b74db4e_0d8e_11ec_a21b_0242ac1c0002row12_col2\" class=\"data row12 col2\" >18,564</td>\n",
              "            </tr>\n",
              "    </tbody></table>"
            ],
            "text/plain": [
              "<pandas.io.formats.style.Styler at 0x7fa32234dd10>"
            ]
          },
          "metadata": {},
          "execution_count": 31
        }
      ]
    },
    {
      "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": {
        "id": "fp_R-m2vLC4-",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 359
        },
        "outputId": "afe8b103-a2e2-4a3b-9393-a7c4f1dab724"
      },
      "source": [
        "c_28 = sc0209_07am_udve_CoC['c28_v_dataaplicacao'].groupby([sc0209_07am_udve_CoC['c28_v_dataaplicacao'].dt.year.rename('year'), sc0209_07am_udve_CoC['c28_v_dataaplicacao'].dt.month.rename('month')]).agg({'count'})\n",
        "c_28"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th></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=\"8\" valign=\"top\">2021</th>\n",
              "      <th>1</th>\n",
              "      <td>67818</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>169561</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>595833</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>805597</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>750808</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>1051602</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>1644150</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>1736949</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "              count\n",
              "year month         \n",
              "2020 12           3\n",
              "2021 1        67818\n",
              "     2       169561\n",
              "     3       595833\n",
              "     4       805597\n",
              "     5       750808\n",
              "     6      1051602\n",
              "     7      1644150\n",
              "     8      1736949"
            ]
          },
          "metadata": {},
          "execution_count": 32
        }
      ]
    },
    {
      "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 sc0209_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": {
        "id": "1D2OI1O6OqbN",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "a1ecd476-c21f-4af2-8098-c7345b6a097f"
      },
      "source": [
        "c28_36 = sc0209_07am_udve_CoC.groupby([(sc0209_07am_udve_CoC['c28_v_dataaplicacao'].dt.strftime('%B')),'c_36_Combination_of_Concern']).size()\n",
        "c28_36"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "c28_v_dataaplicacao  c_36_Combination_of_Concern\n",
              "April                CoC_1                           117382\n",
              "                     CoC_3                                8\n",
              "                     no_issue                        688207\n",
              "August               CoC_1                           134373\n",
              "                     no_issue                       1602576\n",
              "December             CoC_1                                3\n",
              "February             CoC_1                            32456\n",
              "                     CoC_3                               12\n",
              "                     no_issue                        137093\n",
              "January              CoC_1                            11061\n",
              "                     CoC_3                                8\n",
              "                     no_issue                         56749\n",
              "July                 CoC_1                           163955\n",
              "                     no_issue                       1480195\n",
              "June                 CoC_1                           103511\n",
              "                     no_issue                        948091\n",
              "March                CoC_1                           116791\n",
              "                     CoC_3                                3\n",
              "                     no_issue                        479039\n",
              "May                  CoC_1                            87650\n",
              "                     CoC_3                                1\n",
              "                     no_issue                        663157\n",
              "dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 33
        }
      ]
    },
    {
      "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 June with end July 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": [
        "sc0209_07am_c_28_c_36 = {\n",
        "    'Month':    ['December','January','February', 'March', 'April','May','June','July', 'August'],\n",
        "    'CoC_1':     [3, 11061, 32456, 116791, 117382, 87650, 103511, 163955, 134373],\n",
        "    'CoC_2':     [0, 0, 0, 0, 0, 0, 0, 0, 0],\n",
        "    'CoC_3':     [0, 8, 12, 3, 8, 1, 0, 0, 0],\n",
        "    'no_issue':  [0, 56749, 137093, 479039, 688207, 663157, 948091, 1480195, 1602576]\n",
        "    }"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fLk-HbXn7JIQ",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 328
        },
        "outputId": "4ef37f69-8620-4003-bc68-ea623ecbf8b8"
      },
      "source": [
        "sc0209_07am_df_c_28_c_36 = pd.DataFrame(data=sc0209_07am_c_28_c_36)\n",
        "sc0209_07am_df_c_28_c_36"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>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>11061</td>\n",
              "      <td>0</td>\n",
              "      <td>8</td>\n",
              "      <td>56749</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>February</td>\n",
              "      <td>32456</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>137093</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>March</td>\n",
              "      <td>116791</td>\n",
              "      <td>0</td>\n",
              "      <td>3</td>\n",
              "      <td>479039</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>April</td>\n",
              "      <td>117382</td>\n",
              "      <td>0</td>\n",
              "      <td>8</td>\n",
              "      <td>688207</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>May</td>\n",
              "      <td>87650</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>663157</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>June</td>\n",
              "      <td>103511</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>948091</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>July</td>\n",
              "      <td>163955</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1480195</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>August</td>\n",
              "      <td>134373</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1602576</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   11061      0      8     56749\n",
              "2  February   32456      0     12    137093\n",
              "3     March  116791      0      3    479039\n",
              "4     April  117382      0      8    688207\n",
              "5       May   87650      0      1    663157\n",
              "6      June  103511      0      0    948091\n",
              "7      July  163955      0      0   1480195\n",
              "8    August  134373      0      0   1602576"
            ]
          },
          "metadata": {},
          "execution_count": 35
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "OtQ_Z4VdBlA-",
        "outputId": "9bec7be3-a7ee-44b3-d378-e61470088ad8"
      },
      "source": [
        "sc0209_07am_df_c_28_c_36.sum()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Month       DecemberJanuaryFebruaryMarchAprilMayJuneJulyAu...\n",
              "CoC_1                                                  767182\n",
              "CoC_2                                                       0\n",
              "CoC_3                                                      32\n",
              "no_issue                                              6055107\n",
              "dtype: object"
            ]
          },
          "metadata": {},
          "execution_count": 36
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QXg6QRvMWNar"
      },
      "source": [
        "#<font color = 'green'>**Step 10** - Recalling data from 04 August 2021 and 02 July 21 for comparisons with data from up to 02 September 21:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lxMWAZPi-Jcb"
      },
      "source": [
        "sc0408_10am_c_28_c_36 = {\n",
        "    'Month':    ['December','January','February', 'March', 'April','May','June','July'],\n",
        "    'CoC_1':     [3, 12607, 34525, 127466, 121957, 81883, 92074,105555],\n",
        "    'CoC_2':     [0, 0, 0, 0, 0, 0, 0, 0],\n",
        "    'CoC_3':     [0, 9, 12, 3, 8, 1, 0, 0],\n",
        "    'no_issue':  [0, 57701, 138245, 486487, 687464, 647836, 927710, 1322208]\n",
        "    }"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Fx98EL6JWIRB",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 297
        },
        "outputId": "f89cdcdb-d1f5-4908-db36-abe534c3713d"
      },
      "source": [
        "sc0408_10am_c_28_c_36 = pd.DataFrame(data=sc0408_10am_c_28_c_36)\n",
        "sc0408_10am_c_28_c_36"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>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>12607</td>\n",
              "      <td>0</td>\n",
              "      <td>9</td>\n",
              "      <td>57701</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>February</td>\n",
              "      <td>34525</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>138245</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>March</td>\n",
              "      <td>127466</td>\n",
              "      <td>0</td>\n",
              "      <td>3</td>\n",
              "      <td>486487</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>April</td>\n",
              "      <td>121957</td>\n",
              "      <td>0</td>\n",
              "      <td>8</td>\n",
              "      <td>687464</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>May</td>\n",
              "      <td>81883</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>647836</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>June</td>\n",
              "      <td>92074</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>927710</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>July</td>\n",
              "      <td>105555</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1322208</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   12607      0      9     57701\n",
              "2  February   34525      0     12    138245\n",
              "3     March  127466      0      3    486487\n",
              "4     April  121957      0      8    687464\n",
              "5       May   81883      0      1    647836\n",
              "6      June   92074      0      0    927710\n",
              "7      July  105555      0      0   1322208"
            ]
          },
          "metadata": {},
          "execution_count": 38
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Demdj2Re-4wD"
      },
      "source": [
        ""
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7WOp6AFnV2mS"
      },
      "source": [
        "sc_0207_07am_c_28_c_36 = {'Month':['December', 'January',  'February', 'March', 'April',     'May',    'Jun'],\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": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 266
        },
        "id": "-ut9o9yD_zUM",
        "outputId": "6c4871a1-6396-42c9-ec24-2867c6f97ad5"
      },
      "source": [
        "sc_0207_07am_c_28_c_36 = pd.DataFrame(data=sc_0207_07am_c_28_c_36)\n",
        "sc_0207_07am_c_28_c_36"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>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>Jun</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       Jun   47241      0      0    776442"
            ]
          },
          "metadata": {},
          "execution_count": 40
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vE0uXQ4KV2Oj"
      },
      "source": [
        "# **<Font color='blue'>Brief Comments and key conclusions:**\n",
        "\n",
        "* At the end of August 2021 we still notice a 11% of registrations ( (CoC_1 + CoC_3 = 767.182 ) out of a total of 6.822.321)) classified as 'Combinations_of_Concern'. \n",
        "\n",
        "* In other words, based on the federal government open source database used for this work, in the state of Santa Catarina we have 767.182 registers (out of an total o 6.822.321) that we can not confirm which vacine an individual received.  \n",
        "\n",
        "* This confirm the overall monthly trend around 12 % of Combinations of Concern identified since the vaccination plan started in the Brazilian state of Santa Catarina last January. \n",
        "\n",
        "* UDVE 13 alone clusters 74% of the state total 'Combinations of Concern' (568143 out of 767182).\n",
        "\n",
        "* Most of them in Florianópolis, the capital of Santa Catarina state, where for 45% of the registers up to end of August 2021, based on the federal source we used for this work, we do not know which vacine an individual received.\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'). \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 this data sciences 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.\n",
        "\n",
        "PS: To run the program for this 5th edition I was obliged to upgrade to a paying Google Colab Pro, to be able to use a GPU and high-RAM runtime! I suppose that was due to the increase of 1.967.992 news registers from previous month.\n"
      ]
    }
  ]
}