notebooks/tratamento_casos_obitos_hep_brasil.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tratamento dos dados relacionados aos casos e óbitos de Hepatite no brasil "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importações dos pacotes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from funcoes import limpa_casos_h, limpa_obitos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bases de dados utilizadas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[MonitoramentoHepatites-Brasil.xls](https://github.com/Pedro-correa-almeida/projeto_um_mal_silencioso/blob/main/dados_brutos/MonitoramentoHepatites-Brasil.xls) : arquivo xls com diversas tabelas relacionados aos dados de casos e óbitos de Hepatite no Brasil entre 2007 e 2019<br>\n",
    "[casos_hepatite_2001-2006_brasil.csv](https://github.com/Pedro-correa-almeida/projeto_um_mal_silencioso/blob/main/dados_brutos/casos_hepatite_2001-2006_brasil.csv) : arquivo csv com dados de casos de Hepatite entre 2001 e 2006<br>\n",
    "[serie_2001_2020_TCU_populacao.xls](https://github.com/Pedro-correa-almeida/projeto_um_mal_silencioso/blob/main/dados_brutos/serie_2001_2020_TCU_populacao.xls) : arquivo xls com dados com dados da estimativa da população brasileira feita pelo IBGE entre 2001 e 2020<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Leitura dos dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Lendo os dados de casos e óbitos Hepatite A, B e C entre 2007 e 2019 \n",
    "dados_recentes = pd.read_html('../dados_brutos/MonitoramentoHepatites-Brasil.xls', thousands='.')\n",
    "\n",
    "#Lendo os dados de casos e óbitos de Hepatite A, B e C entre 2001 e 2006\n",
    "dados_antigos = pd.read_csv('../dados_brutos/casos_hepatite_2001-2006_brasil.csv', encoding='ISO-8859-1', skiprows=4, \n",
    "                            skipfooter=13, usecols=[0,1,2,3], sep=';', engine='python')\n",
    "\n",
    "#Lendo os dados de estimativa populacional feita pelo IBGE e limitando os dados entre 2001 e 2020 e só dados da população do Brasil inteiro\n",
    "pop_2001_2019 = pd.read_excel('../dados_brutos/serie_2001_2020_TCU_populacao.xls', skiprows=4, index_col=0, \n",
    "                              nrows=2, usecols=range(0,20))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Funções"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on function limpa_casos_h in module funcoes:\n",
      "\n",
      "limpa_casos_h(dados: pandas.core.frame.DataFrame, hep: str)\n",
      "    Função que recebe um dataset com dados sobre casos relacionado à um tipo de hepatite e realiza algumas transformações:\n",
      "        \n",
      "        Retira as colunas que agrupam diversos anos, coluna 'Total' e coluna '1999-2006'\n",
      "        Retira a segunda linha do dataframe que se refere à taxa de incidência e, primeiramente serão usados apenas os dados de casos totais para juntar os dados recentes e mais antigos\n",
      "        Transforma a disposição da tabela, transformando as colunas em apenas uma coluna('Ano') atribuindo o vírus da hepatite(A,B,C) como nome da coluna de valores numéricos\n",
      "        Retira a primeira linha que antes se referia ao nome da variável numérica('Casos')\n",
      "        Transforma o tipo de dados da coluna 'Ano' em inteiro e a coluna com os dados numéricos para float(podem haver valores nulos) para depois facilitar a manipulação e evitar problemas  \n",
      "        \n",
      "    Parâmetros:\n",
      "        \n",
      "        dados : DataFrame onde estão os dados mais recentes dos casos de hepatite, tipo : pd.DataFrame\n",
      "        hep : o vírus da hepatite que o DataFrame se refere, tipo : str\n",
      "    \n",
      "    Retorno:\n",
      "        \n",
      "        dados : DataFrame com todas as limpezas realizadas, tipo : pd.DataFrame\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(limpa_casos_h)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on function limpa_obitos in module funcoes:\n",
      "\n",
      "limpa_obitos(dados: pandas.core.frame.DataFrame, nome_valores: str)\n",
      "    Função que recebe o DataFrame relacionado aos óbitos de cada virus e realiza algumas transformações:\n",
      "        \n",
      "        Retira as colunas que agrupam diversos anos, coluna 'Total' e coluna '2000-2006' \n",
      "        Retira a palavra 'Hepatite' antes do vírus(A,B,C)\n",
      "        Renomeia a coluna Óbitos por virus\n",
      "        Retira os dados relacionados à Hepatite D\n",
      "        Transforma as colunas relacionadas aos Anos em apenas uma coluna('Ano') e atribui o 'nome_valores' como nome da coluna com os valores numéricos\n",
      "        Trasnforma o tipo de dados da coluna 'Ano' em inteiro e a coluna com os dados numéricos para float(podem haver valores nulos) a fim de evitar problemas e facilitar a manipulação\n",
      "    \n",
      "    Parâmetros:\n",
      "    \n",
      "        dados : DataFrame onde estão os dados mais recentes dos óbitos de hepatite, tipo : pd.DataFrame\n",
      "        hep : o nome que a coluna com os valores numéricos vai se referir, podendo ser a região do DataFrame ou a palavra 'Obitos' por exemplo, tipo : str\n",
      "        \n",
      "    Retorno :\n",
      "        \n",
      "        dados : retorna um Dataframe com os dados sobre os óbitos de hepatite A, B e C, tipo : pd.DataFrame\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(limpa_obitos)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tratando os dados"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "No primeiro momento vou ler as tabelas dos dados recentes que seram utilizadas na análise, a tabela sobre casos da Hepatite A, B e C, e a tabela com os óbitos totais por Hepatite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Lendo os dados relacionados à Hepatite A\n",
    "hep_A = dados_recentes[1]\n",
    "\n",
    "#Lendo os dados relacionados à Hepatite B\n",
    "hep_B = dados_recentes[3]\n",
    "\n",
    "#Lendo os dados relacionados à Hepatite C\n",
    "hep_C = dados_recentes[5]\n",
    "\n",
    "#Lendo os dados relacionados aos óbitos por Hepatite\n",
    "hep_obitos = dados_recentes[10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Submetendo as tabelas relacionadas aos casos de Hepatite para função 'limpa_casos_h' a fim de fazer as devidas transformações, após isso juntando as 3 tabelas relacionadas às Hepatites em apenas uma tabela 'hep'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ano</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007</td>\n",
       "      <td>13351.0</td>\n",
       "      <td>12406.0</td>\n",
       "      <td>17173.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2008</td>\n",
       "      <td>11680.0</td>\n",
       "      <td>13400.0</td>\n",
       "      <td>20110.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009</td>\n",
       "      <td>10920.0</td>\n",
       "      <td>15004.0</td>\n",
       "      <td>21854.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010</td>\n",
       "      <td>6929.0</td>\n",
       "      <td>13711.0</td>\n",
       "      <td>21066.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011</td>\n",
       "      <td>7468.0</td>\n",
       "      <td>16683.0</td>\n",
       "      <td>24231.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2012</td>\n",
       "      <td>6628.0</td>\n",
       "      <td>16286.0</td>\n",
       "      <td>25450.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2013</td>\n",
       "      <td>6270.0</td>\n",
       "      <td>16720.0</td>\n",
       "      <td>25401.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2014</td>\n",
       "      <td>6424.0</td>\n",
       "      <td>16743.0</td>\n",
       "      <td>24124.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2015</td>\n",
       "      <td>3302.0</td>\n",
       "      <td>16149.0</td>\n",
       "      <td>26946.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2016</td>\n",
       "      <td>1207.0</td>\n",
       "      <td>14829.0</td>\n",
       "      <td>28732.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2017</td>\n",
       "      <td>2137.0</td>\n",
       "      <td>14251.0</td>\n",
       "      <td>25937.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2018</td>\n",
       "      <td>2188.0</td>\n",
       "      <td>14686.0</td>\n",
       "      <td>27773.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2019</td>\n",
       "      <td>891.0</td>\n",
       "      <td>13971.0</td>\n",
       "      <td>22747.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Ano        A        B        C\n",
       "0   2007  13351.0  12406.0  17173.0\n",
       "1   2008  11680.0  13400.0  20110.0\n",
       "2   2009  10920.0  15004.0  21854.0\n",
       "3   2010   6929.0  13711.0  21066.0\n",
       "4   2011   7468.0  16683.0  24231.0\n",
       "5   2012   6628.0  16286.0  25450.0\n",
       "6   2013   6270.0  16720.0  25401.0\n",
       "7   2014   6424.0  16743.0  24124.0\n",
       "8   2015   3302.0  16149.0  26946.0\n",
       "9   2016   1207.0  14829.0  28732.0\n",
       "10  2017   2137.0  14251.0  25937.0\n",
       "11  2018   2188.0  14686.0  27773.0\n",
       "12  2019    891.0  13971.0  22747.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Limpando os DataFrames\n",
    "hep_A = limpa_casos_h(hep_A, 'A')\n",
    "hep_B = limpa_casos_h(hep_B, 'B')\n",
    "hep_C = limpa_casos_h(hep_C, 'C')\n",
    "\n",
    "#Juntando os 3 DataFrames\n",
    "hep = pd.merge(hep_A,pd.merge(hep_B, hep_C))\n",
    "hep"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fazendo as devidas transformações na base de dados entre 2001 e 2006 para juntar com os dados mais recentes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ano</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>21011</td>\n",
       "      <td>8344</td>\n",
       "      <td>6713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>14468</td>\n",
       "      <td>8841</td>\n",
       "      <td>8081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003</td>\n",
       "      <td>14715</td>\n",
       "      <td>11673</td>\n",
       "      <td>10241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>20585</td>\n",
       "      <td>12835</td>\n",
       "      <td>13285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2005</td>\n",
       "      <td>25302</td>\n",
       "      <td>14107</td>\n",
       "      <td>13702</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Ano      A      B      C\n",
       "0  2001  21011   8344   6713\n",
       "1  2002  14468   8841   8081\n",
       "2  2003  14715  11673  10241\n",
       "3  2004  20585  12835  13285\n",
       "4  2005  25302  14107  13702"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Renomeando os nomes das colunas\n",
    "dados_antigos = dados_antigos.rename(columns={'Ano Diag/sintomas':'Ano', 'Vírus A':'A', 'Vírus B':'B', 'Vírus C':'C'})\n",
    "\n",
    "#Ordenando as colunas pra mesma ordem dos dados recentes\n",
    "dados_antigos = dados_antigos[['Ano', 'A', 'B', 'C']]\n",
    "\n",
    "#Transformando o tipo de dados da coluna 'Ano' para inteiro\n",
    "dados_antigos['Ano'] = dados_antigos['Ano'].astype('int64')\n",
    "\n",
    "#Mostrando as 5 primeiras linhas \n",
    "dados_antigos.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ano</th>\n",
       "      <th>virus</th>\n",
       "      <th>Casos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>A</td>\n",
       "      <td>21011.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>A</td>\n",
       "      <td>14468.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003</td>\n",
       "      <td>A</td>\n",
       "      <td>14715.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>A</td>\n",
       "      <td>20585.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2005</td>\n",
       "      <td>A</td>\n",
       "      <td>25302.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2006</td>\n",
       "      <td>A</td>\n",
       "      <td>19095.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2007</td>\n",
       "      <td>A</td>\n",
       "      <td>13351.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2008</td>\n",
       "      <td>A</td>\n",
       "      <td>11680.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2009</td>\n",
       "      <td>A</td>\n",
       "      <td>10920.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2010</td>\n",
       "      <td>A</td>\n",
       "      <td>6929.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2011</td>\n",
       "      <td>A</td>\n",
       "      <td>7468.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2012</td>\n",
       "      <td>A</td>\n",
       "      <td>6628.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2013</td>\n",
       "      <td>A</td>\n",
       "      <td>6270.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2014</td>\n",
       "      <td>A</td>\n",
       "      <td>6424.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2015</td>\n",
       "      <td>A</td>\n",
       "      <td>3302.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2016</td>\n",
       "      <td>A</td>\n",
       "      <td>1207.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2017</td>\n",
       "      <td>A</td>\n",
       "      <td>2137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2018</td>\n",
       "      <td>A</td>\n",
       "      <td>2188.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2019</td>\n",
       "      <td>A</td>\n",
       "      <td>891.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2001</td>\n",
       "      <td>B</td>\n",
       "      <td>8344.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2002</td>\n",
       "      <td>B</td>\n",
       "      <td>8841.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2003</td>\n",
       "      <td>B</td>\n",
       "      <td>11673.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2004</td>\n",
       "      <td>B</td>\n",
       "      <td>12835.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2005</td>\n",
       "      <td>B</td>\n",
       "      <td>14107.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2006</td>\n",
       "      <td>B</td>\n",
       "      <td>11330.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2007</td>\n",
       "      <td>B</td>\n",
       "      <td>12406.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2008</td>\n",
       "      <td>B</td>\n",
       "      <td>13400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2009</td>\n",
       "      <td>B</td>\n",
       "      <td>15004.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2010</td>\n",
       "      <td>B</td>\n",
       "      <td>13711.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2011</td>\n",
       "      <td>B</td>\n",
       "      <td>16683.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2012</td>\n",
       "      <td>B</td>\n",
       "      <td>16286.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2013</td>\n",
       "      <td>B</td>\n",
       "      <td>16720.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2014</td>\n",
       "      <td>B</td>\n",
       "      <td>16743.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2015</td>\n",
       "      <td>B</td>\n",
       "      <td>16149.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2016</td>\n",
       "      <td>B</td>\n",
       "      <td>14829.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2017</td>\n",
       "      <td>B</td>\n",
       "      <td>14251.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2018</td>\n",
       "      <td>B</td>\n",
       "      <td>14686.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2019</td>\n",
       "      <td>B</td>\n",
       "      <td>13971.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2001</td>\n",
       "      <td>C</td>\n",
       "      <td>6713.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2002</td>\n",
       "      <td>C</td>\n",
       "      <td>8081.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2003</td>\n",
       "      <td>C</td>\n",
       "      <td>10241.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2004</td>\n",
       "      <td>C</td>\n",
       "      <td>13285.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2005</td>\n",
       "      <td>C</td>\n",
       "      <td>13702.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2006</td>\n",
       "      <td>C</td>\n",
       "      <td>10319.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>2007</td>\n",
       "      <td>C</td>\n",
       "      <td>17173.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2008</td>\n",
       "      <td>C</td>\n",
       "      <td>20110.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2009</td>\n",
       "      <td>C</td>\n",
       "      <td>21854.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>2010</td>\n",
       "      <td>C</td>\n",
       "      <td>21066.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2011</td>\n",
       "      <td>C</td>\n",
       "      <td>24231.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2012</td>\n",
       "      <td>C</td>\n",
       "      <td>25450.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>2013</td>\n",
       "      <td>C</td>\n",
       "      <td>25401.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>2014</td>\n",
       "      <td>C</td>\n",
       "      <td>24124.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>2015</td>\n",
       "      <td>C</td>\n",
       "      <td>26946.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>2016</td>\n",
       "      <td>C</td>\n",
       "      <td>28732.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>2017</td>\n",
       "      <td>C</td>\n",
       "      <td>25937.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>2018</td>\n",
       "      <td>C</td>\n",
       "      <td>27773.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>2019</td>\n",
       "      <td>C</td>\n",
       "      <td>22747.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Ano virus    Casos\n",
       "0   2001     A  21011.0\n",
       "1   2002     A  14468.0\n",
       "2   2003     A  14715.0\n",
       "3   2004     A  20585.0\n",
       "4   2005     A  25302.0\n",
       "5   2006     A  19095.0\n",
       "6   2007     A  13351.0\n",
       "7   2008     A  11680.0\n",
       "8   2009     A  10920.0\n",
       "9   2010     A   6929.0\n",
       "10  2011     A   7468.0\n",
       "11  2012     A   6628.0\n",
       "12  2013     A   6270.0\n",
       "13  2014     A   6424.0\n",
       "14  2015     A   3302.0\n",
       "15  2016     A   1207.0\n",
       "16  2017     A   2137.0\n",
       "17  2018     A   2188.0\n",
       "18  2019     A    891.0\n",
       "19  2001     B   8344.0\n",
       "20  2002     B   8841.0\n",
       "21  2003     B  11673.0\n",
       "22  2004     B  12835.0\n",
       "23  2005     B  14107.0\n",
       "24  2006     B  11330.0\n",
       "25  2007     B  12406.0\n",
       "26  2008     B  13400.0\n",
       "27  2009     B  15004.0\n",
       "28  2010     B  13711.0\n",
       "29  2011     B  16683.0\n",
       "30  2012     B  16286.0\n",
       "31  2013     B  16720.0\n",
       "32  2014     B  16743.0\n",
       "33  2015     B  16149.0\n",
       "34  2016     B  14829.0\n",
       "35  2017     B  14251.0\n",
       "36  2018     B  14686.0\n",
       "37  2019     B  13971.0\n",
       "38  2001     C   6713.0\n",
       "39  2002     C   8081.0\n",
       "40  2003     C  10241.0\n",
       "41  2004     C  13285.0\n",
       "42  2005     C  13702.0\n",
       "43  2006     C  10319.0\n",
       "44  2007     C  17173.0\n",
       "45  2008     C  20110.0\n",
       "46  2009     C  21854.0\n",
       "47  2010     C  21066.0\n",
       "48  2011     C  24231.0\n",
       "49  2012     C  25450.0\n",
       "50  2013     C  25401.0\n",
       "51  2014     C  24124.0\n",
       "52  2015     C  26946.0\n",
       "53  2016     C  28732.0\n",
       "54  2017     C  25937.0\n",
       "55  2018     C  27773.0\n",
       "56  2019     C  22747.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Juntando os dados antigos com os dados recentes\n",
    "dados = dados_antigos.append(hep)\n",
    "\n",
    "#Transformando as colunas A, B e C para uma coluna virus\n",
    "dados = pd.melt(dados, 'Ano', var_name='virus', value_name='Casos')\n",
    "\n",
    "dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ano</th>\n",
       "      <th>virus</th>\n",
       "      <th>Casos</th>\n",
       "      <th>Obitos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>A</td>\n",
       "      <td>21011.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>A</td>\n",
       "      <td>14468.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003</td>\n",
       "      <td>A</td>\n",
       "      <td>14715.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>A</td>\n",
       "      <td>20585.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2005</td>\n",
       "      <td>A</td>\n",
       "      <td>25302.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2006</td>\n",
       "      <td>A</td>\n",
       "      <td>19095.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2007</td>\n",
       "      <td>A</td>\n",
       "      <td>13351.0</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2008</td>\n",
       "      <td>A</td>\n",
       "      <td>11680.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2009</td>\n",
       "      <td>A</td>\n",
       "      <td>10920.0</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2010</td>\n",
       "      <td>A</td>\n",
       "      <td>6929.0</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2011</td>\n",
       "      <td>A</td>\n",
       "      <td>7468.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2012</td>\n",
       "      <td>A</td>\n",
       "      <td>6628.0</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2013</td>\n",
       "      <td>A</td>\n",
       "      <td>6270.0</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2014</td>\n",
       "      <td>A</td>\n",
       "      <td>6424.0</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2015</td>\n",
       "      <td>A</td>\n",
       "      <td>3302.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2016</td>\n",
       "      <td>A</td>\n",
       "      <td>1207.0</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2017</td>\n",
       "      <td>A</td>\n",
       "      <td>2137.0</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2018</td>\n",
       "      <td>A</td>\n",
       "      <td>2188.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2019</td>\n",
       "      <td>A</td>\n",
       "      <td>891.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2001</td>\n",
       "      <td>B</td>\n",
       "      <td>8344.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2002</td>\n",
       "      <td>B</td>\n",
       "      <td>8841.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2003</td>\n",
       "      <td>B</td>\n",
       "      <td>11673.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2004</td>\n",
       "      <td>B</td>\n",
       "      <td>12835.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2005</td>\n",
       "      <td>B</td>\n",
       "      <td>14107.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2006</td>\n",
       "      <td>B</td>\n",
       "      <td>11330.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2007</td>\n",
       "      <td>B</td>\n",
       "      <td>12406.0</td>\n",
       "      <td>515.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2008</td>\n",
       "      <td>B</td>\n",
       "      <td>13400.0</td>\n",
       "      <td>566.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2009</td>\n",
       "      <td>B</td>\n",
       "      <td>15004.0</td>\n",
       "      <td>482.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2010</td>\n",
       "      <td>B</td>\n",
       "      <td>13711.0</td>\n",
       "      <td>549.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2011</td>\n",
       "      <td>B</td>\n",
       "      <td>16683.0</td>\n",
       "      <td>538.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2012</td>\n",
       "      <td>B</td>\n",
       "      <td>16286.0</td>\n",
       "      <td>443.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2013</td>\n",
       "      <td>B</td>\n",
       "      <td>16720.0</td>\n",
       "      <td>456.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2014</td>\n",
       "      <td>B</td>\n",
       "      <td>16743.0</td>\n",
       "      <td>469.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2015</td>\n",
       "      <td>B</td>\n",
       "      <td>16149.0</td>\n",
       "      <td>451.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2016</td>\n",
       "      <td>B</td>\n",
       "      <td>14829.0</td>\n",
       "      <td>477.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2017</td>\n",
       "      <td>B</td>\n",
       "      <td>14251.0</td>\n",
       "      <td>414.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2018</td>\n",
       "      <td>B</td>\n",
       "      <td>14686.0</td>\n",
       "      <td>424.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2019</td>\n",
       "      <td>B</td>\n",
       "      <td>13971.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2001</td>\n",
       "      <td>C</td>\n",
       "      <td>6713.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2002</td>\n",
       "      <td>C</td>\n",
       "      <td>8081.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2003</td>\n",
       "      <td>C</td>\n",
       "      <td>10241.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2004</td>\n",
       "      <td>C</td>\n",
       "      <td>13285.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2005</td>\n",
       "      <td>C</td>\n",
       "      <td>13702.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2006</td>\n",
       "      <td>C</td>\n",
       "      <td>10319.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>2007</td>\n",
       "      <td>C</td>\n",
       "      <td>17173.0</td>\n",
       "      <td>1800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2008</td>\n",
       "      <td>C</td>\n",
       "      <td>20110.0</td>\n",
       "      <td>1898.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2009</td>\n",
       "      <td>C</td>\n",
       "      <td>21854.0</td>\n",
       "      <td>1879.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>2010</td>\n",
       "      <td>C</td>\n",
       "      <td>21066.0</td>\n",
       "      <td>1967.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2011</td>\n",
       "      <td>C</td>\n",
       "      <td>24231.0</td>\n",
       "      <td>2012.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2012</td>\n",
       "      <td>C</td>\n",
       "      <td>25450.0</td>\n",
       "      <td>2032.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>2013</td>\n",
       "      <td>C</td>\n",
       "      <td>25401.0</td>\n",
       "      <td>2013.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>2014</td>\n",
       "      <td>C</td>\n",
       "      <td>24124.0</td>\n",
       "      <td>2087.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>2015</td>\n",
       "      <td>C</td>\n",
       "      <td>26946.0</td>\n",
       "      <td>2028.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>2016</td>\n",
       "      <td>C</td>\n",
       "      <td>28732.0</td>\n",
       "      <td>2023.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>2017</td>\n",
       "      <td>C</td>\n",
       "      <td>25937.0</td>\n",
       "      <td>1720.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>2018</td>\n",
       "      <td>C</td>\n",
       "      <td>27773.0</td>\n",
       "      <td>1574.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>2019</td>\n",
       "      <td>C</td>\n",
       "      <td>22747.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Ano virus    Casos  Obitos\n",
       "0   2001     A  21011.0     NaN\n",
       "1   2002     A  14468.0     NaN\n",
       "2   2003     A  14715.0     NaN\n",
       "3   2004     A  20585.0     NaN\n",
       "4   2005     A  25302.0     NaN\n",
       "5   2006     A  19095.0     NaN\n",
       "6   2007     A  13351.0    47.0\n",
       "7   2008     A  11680.0    50.0\n",
       "8   2009     A  10920.0    45.0\n",
       "9   2010     A   6929.0    52.0\n",
       "10  2011     A   7468.0    30.0\n",
       "11  2012     A   6628.0    38.0\n",
       "12  2013     A   6270.0    35.0\n",
       "13  2014     A   6424.0    29.0\n",
       "14  2015     A   3302.0    24.0\n",
       "15  2016     A   1207.0    29.0\n",
       "16  2017     A   2137.0    22.0\n",
       "17  2018     A   2188.0    28.0\n",
       "18  2019     A    891.0     NaN\n",
       "19  2001     B   8344.0     NaN\n",
       "20  2002     B   8841.0     NaN\n",
       "21  2003     B  11673.0     NaN\n",
       "22  2004     B  12835.0     NaN\n",
       "23  2005     B  14107.0     NaN\n",
       "24  2006     B  11330.0     NaN\n",
       "25  2007     B  12406.0   515.0\n",
       "26  2008     B  13400.0   566.0\n",
       "27  2009     B  15004.0   482.0\n",
       "28  2010     B  13711.0   549.0\n",
       "29  2011     B  16683.0   538.0\n",
       "30  2012     B  16286.0   443.0\n",
       "31  2013     B  16720.0   456.0\n",
       "32  2014     B  16743.0   469.0\n",
       "33  2015     B  16149.0   451.0\n",
       "34  2016     B  14829.0   477.0\n",
       "35  2017     B  14251.0   414.0\n",
       "36  2018     B  14686.0   424.0\n",
       "37  2019     B  13971.0     NaN\n",
       "38  2001     C   6713.0     NaN\n",
       "39  2002     C   8081.0     NaN\n",
       "40  2003     C  10241.0     NaN\n",
       "41  2004     C  13285.0     NaN\n",
       "42  2005     C  13702.0     NaN\n",
       "43  2006     C  10319.0     NaN\n",
       "44  2007     C  17173.0  1800.0\n",
       "45  2008     C  20110.0  1898.0\n",
       "46  2009     C  21854.0  1879.0\n",
       "47  2010     C  21066.0  1967.0\n",
       "48  2011     C  24231.0  2012.0\n",
       "49  2012     C  25450.0  2032.0\n",
       "50  2013     C  25401.0  2013.0\n",
       "51  2014     C  24124.0  2087.0\n",
       "52  2015     C  26946.0  2028.0\n",
       "53  2016     C  28732.0  2023.0\n",
       "54  2017     C  25937.0  1720.0\n",
       "55  2018     C  27773.0  1574.0\n",
       "56  2019     C  22747.0     NaN"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Utilizando a função 'limpa_obitos' para realizar as trasnformações necessárias\n",
    "hep_obitos = limpa_obitos(hep_obitos, 'Obitos')\n",
    "\n",
    "#Juntando os dados de casos e de óbitos\n",
    "dados = pd.merge(dados, hep_obitos, on=['Ano', 'virus'], how='left')\n",
    "\n",
    "dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ano</th>\n",
       "      <th>Pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>172385826.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>174632960.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003</td>\n",
       "      <td>176871437.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>181581024.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2005</td>\n",
       "      <td>184184264.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2006</td>\n",
       "      <td>186770562.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2007</td>\n",
       "      <td>183989711.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2008</td>\n",
       "      <td>189612814.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2009</td>\n",
       "      <td>191480630.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2010</td>\n",
       "      <td>190747855.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2011</td>\n",
       "      <td>192379287.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2012</td>\n",
       "      <td>193946886.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2013</td>\n",
       "      <td>201032714.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2014</td>\n",
       "      <td>202768562.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2015</td>\n",
       "      <td>204450649.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2016</td>\n",
       "      <td>206081432.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2017</td>\n",
       "      <td>207660929.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2018</td>\n",
       "      <td>208494900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2019</td>\n",
       "      <td>210147125.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Ano          Pop\n",
       "0   2001  172385826.0\n",
       "1   2002  174632960.0\n",
       "2   2003  176871437.0\n",
       "3   2004  181581024.0\n",
       "4   2005  184184264.0\n",
       "5   2006  186770562.0\n",
       "6   2007  183989711.0\n",
       "7   2008  189612814.0\n",
       "8   2009  191480630.0\n",
       "9   2010  190747855.0\n",
       "10  2011  192379287.0\n",
       "11  2012  193946886.0\n",
       "12  2013  201032714.0\n",
       "13  2014  202768562.0\n",
       "14  2015  204450649.0\n",
       "15  2016  206081432.0\n",
       "16  2017  207660929.0\n",
       "17  2018  208494900.0\n",
       "18  2019  210147125.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Reitando colunas nulas na base de dados de população\n",
    "pop_2001_2019 = pop_2001_2019.dropna()\n",
    "\n",
    "#Transformando as colunas relacionadas aos anos em uma coluna 'Ano'\n",
    "pop_2001_2019 = pd.melt(pop_2001_2019, var_name='Ano', value_name='Pop')\n",
    "\n",
    "#Transformando o tipo da coluna 'Ano' em inteiro\n",
    "pop_2001_2019['Ano'] = pop_2001_2019['Ano'].astype('int64')\n",
    "\n",
    "pop_2001_2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ano</th>\n",
       "      <th>virus</th>\n",
       "      <th>Casos</th>\n",
       "      <th>Obitos</th>\n",
       "      <th>Pop</th>\n",
       "      <th>taxa_incid_por100k</th>\n",
       "      <th>taxa_obitos_por100k</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>A</td>\n",
       "      <td>21011.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>172385826.0</td>\n",
       "      <td>12.188357</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001</td>\n",
       "      <td>B</td>\n",
       "      <td>8344.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>172385826.0</td>\n",
       "      <td>4.840305</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001</td>\n",
       "      <td>C</td>\n",
       "      <td>6713.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>172385826.0</td>\n",
       "      <td>3.894172</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2002</td>\n",
       "      <td>A</td>\n",
       "      <td>14468.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>174632960.0</td>\n",
       "      <td>8.284805</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2002</td>\n",
       "      <td>B</td>\n",
       "      <td>8841.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>174632960.0</td>\n",
       "      <td>5.062618</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2002</td>\n",
       "      <td>C</td>\n",
       "      <td>8081.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>174632960.0</td>\n",
       "      <td>4.627420</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2003</td>\n",
       "      <td>A</td>\n",
       "      <td>14715.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>176871437.0</td>\n",
       "      <td>8.319602</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2003</td>\n",
       "      <td>B</td>\n",
       "      <td>11673.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>176871437.0</td>\n",
       "      <td>6.599709</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2003</td>\n",
       "      <td>C</td>\n",
       "      <td>10241.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>176871437.0</td>\n",
       "      <td>5.790081</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2004</td>\n",
       "      <td>A</td>\n",
       "      <td>20585.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>181581024.0</td>\n",
       "      <td>11.336537</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2004</td>\n",
       "      <td>B</td>\n",
       "      <td>12835.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>181581024.0</td>\n",
       "      <td>7.068470</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2004</td>\n",
       "      <td>C</td>\n",
       "      <td>13285.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>181581024.0</td>\n",
       "      <td>7.316293</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2005</td>\n",
       "      <td>A</td>\n",
       "      <td>25302.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>184184264.0</td>\n",
       "      <td>13.737330</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2005</td>\n",
       "      <td>B</td>\n",
       "      <td>14107.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>184184264.0</td>\n",
       "      <td>7.659178</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2005</td>\n",
       "      <td>C</td>\n",
       "      <td>13702.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>184184264.0</td>\n",
       "      <td>7.439289</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2006</td>\n",
       "      <td>A</td>\n",
       "      <td>19095.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>186770562.0</td>\n",
       "      <td>10.223774</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2006</td>\n",
       "      <td>B</td>\n",
       "      <td>11330.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>186770562.0</td>\n",
       "      <td>6.066266</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2006</td>\n",
       "      <td>C</td>\n",
       "      <td>10319.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>186770562.0</td>\n",
       "      <td>5.524961</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2007</td>\n",
       "      <td>A</td>\n",
       "      <td>13351.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>183989711.0</td>\n",
       "      <td>7.256384</td>\n",
       "      <td>0.025545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2007</td>\n",
       "      <td>B</td>\n",
       "      <td>12406.0</td>\n",
       "      <td>515.0</td>\n",
       "      <td>183989711.0</td>\n",
       "      <td>6.742768</td>\n",
       "      <td>0.279907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2007</td>\n",
       "      <td>C</td>\n",
       "      <td>17173.0</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>183989711.0</td>\n",
       "      <td>9.333674</td>\n",
       "      <td>0.978316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2008</td>\n",
       "      <td>A</td>\n",
       "      <td>11680.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>189612814.0</td>\n",
       "      <td>6.159921</td>\n",
       "      <td>0.026370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2008</td>\n",
       "      <td>B</td>\n",
       "      <td>13400.0</td>\n",
       "      <td>566.0</td>\n",
       "      <td>189612814.0</td>\n",
       "      <td>7.067033</td>\n",
       "      <td>0.298503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2008</td>\n",
       "      <td>C</td>\n",
       "      <td>20110.0</td>\n",
       "      <td>1898.0</td>\n",
       "      <td>189612814.0</td>\n",
       "      <td>10.605823</td>\n",
       "      <td>1.000987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2009</td>\n",
       "      <td>A</td>\n",
       "      <td>10920.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>191480630.0</td>\n",
       "      <td>5.702927</td>\n",
       "      <td>0.023501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2009</td>\n",
       "      <td>B</td>\n",
       "      <td>15004.0</td>\n",
       "      <td>482.0</td>\n",
       "      <td>191480630.0</td>\n",
       "      <td>7.835780</td>\n",
       "      <td>0.251723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2009</td>\n",
       "      <td>C</td>\n",
       "      <td>21854.0</td>\n",
       "      <td>1879.0</td>\n",
       "      <td>191480630.0</td>\n",
       "      <td>11.413165</td>\n",
       "      <td>0.981300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2010</td>\n",
       "      <td>A</td>\n",
       "      <td>6929.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>190747855.0</td>\n",
       "      <td>3.632544</td>\n",
       "      <td>0.027261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2010</td>\n",
       "      <td>B</td>\n",
       "      <td>13711.0</td>\n",
       "      <td>549.0</td>\n",
       "      <td>190747855.0</td>\n",
       "      <td>7.188023</td>\n",
       "      <td>0.287815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2010</td>\n",
       "      <td>C</td>\n",
       "      <td>21066.0</td>\n",
       "      <td>1967.0</td>\n",
       "      <td>190747855.0</td>\n",
       "      <td>11.043899</td>\n",
       "      <td>1.031204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2011</td>\n",
       "      <td>A</td>\n",
       "      <td>7468.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>192379287.0</td>\n",
       "      <td>3.881915</td>\n",
       "      <td>0.015594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2011</td>\n",
       "      <td>B</td>\n",
       "      <td>16683.0</td>\n",
       "      <td>538.0</td>\n",
       "      <td>192379287.0</td>\n",
       "      <td>8.671932</td>\n",
       "      <td>0.279656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2011</td>\n",
       "      <td>C</td>\n",
       "      <td>24231.0</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>192379287.0</td>\n",
       "      <td>12.595431</td>\n",
       "      <td>1.045851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2012</td>\n",
       "      <td>A</td>\n",
       "      <td>6628.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>193946886.0</td>\n",
       "      <td>3.417430</td>\n",
       "      <td>0.019593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2012</td>\n",
       "      <td>B</td>\n",
       "      <td>16286.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>193946886.0</td>\n",
       "      <td>8.397144</td>\n",
       "      <td>0.228413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2012</td>\n",
       "      <td>C</td>\n",
       "      <td>25450.0</td>\n",
       "      <td>2032.0</td>\n",
       "      <td>193946886.0</td>\n",
       "      <td>13.122149</td>\n",
       "      <td>1.047710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2013</td>\n",
       "      <td>A</td>\n",
       "      <td>6270.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>201032714.0</td>\n",
       "      <td>3.118895</td>\n",
       "      <td>0.017410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2013</td>\n",
       "      <td>B</td>\n",
       "      <td>16720.0</td>\n",
       "      <td>456.0</td>\n",
       "      <td>201032714.0</td>\n",
       "      <td>8.317054</td>\n",
       "      <td>0.226829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2013</td>\n",
       "      <td>C</td>\n",
       "      <td>25401.0</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>201032714.0</td>\n",
       "      <td>12.635257</td>\n",
       "      <td>1.001330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2014</td>\n",
       "      <td>A</td>\n",
       "      <td>6424.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>202768562.0</td>\n",
       "      <td>3.168144</td>\n",
       "      <td>0.014302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2014</td>\n",
       "      <td>B</td>\n",
       "      <td>16743.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>202768562.0</td>\n",
       "      <td>8.257197</td>\n",
       "      <td>0.231298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2014</td>\n",
       "      <td>C</td>\n",
       "      <td>24124.0</td>\n",
       "      <td>2087.0</td>\n",
       "      <td>202768562.0</td>\n",
       "      <td>11.897308</td>\n",
       "      <td>1.029252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2015</td>\n",
       "      <td>A</td>\n",
       "      <td>3302.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>204450649.0</td>\n",
       "      <td>1.615060</td>\n",
       "      <td>0.011739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2015</td>\n",
       "      <td>B</td>\n",
       "      <td>16149.0</td>\n",
       "      <td>451.0</td>\n",
       "      <td>204450649.0</td>\n",
       "      <td>7.898728</td>\n",
       "      <td>0.220591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>2015</td>\n",
       "      <td>C</td>\n",
       "      <td>26946.0</td>\n",
       "      <td>2028.0</td>\n",
       "      <td>204450649.0</td>\n",
       "      <td>13.179709</td>\n",
       "      <td>0.991926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2016</td>\n",
       "      <td>A</td>\n",
       "      <td>1207.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>206081432.0</td>\n",
       "      <td>0.585691</td>\n",
       "      <td>0.014072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2016</td>\n",
       "      <td>B</td>\n",
       "      <td>14829.0</td>\n",
       "      <td>477.0</td>\n",
       "      <td>206081432.0</td>\n",
       "      <td>7.195699</td>\n",
       "      <td>0.231462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>2016</td>\n",
       "      <td>C</td>\n",
       "      <td>28732.0</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>206081432.0</td>\n",
       "      <td>13.942062</td>\n",
       "      <td>0.981651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2017</td>\n",
       "      <td>A</td>\n",
       "      <td>2137.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>207660929.0</td>\n",
       "      <td>1.029081</td>\n",
       "      <td>0.010594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2017</td>\n",
       "      <td>B</td>\n",
       "      <td>14251.0</td>\n",
       "      <td>414.0</td>\n",
       "      <td>207660929.0</td>\n",
       "      <td>6.862629</td>\n",
       "      <td>0.199363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>2017</td>\n",
       "      <td>C</td>\n",
       "      <td>25937.0</td>\n",
       "      <td>1720.0</td>\n",
       "      <td>207660929.0</td>\n",
       "      <td>12.490072</td>\n",
       "      <td>0.828273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>2018</td>\n",
       "      <td>A</td>\n",
       "      <td>2188.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>208494900.0</td>\n",
       "      <td>1.049426</td>\n",
       "      <td>0.013430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>2018</td>\n",
       "      <td>B</td>\n",
       "      <td>14686.0</td>\n",
       "      <td>424.0</td>\n",
       "      <td>208494900.0</td>\n",
       "      <td>7.043817</td>\n",
       "      <td>0.203362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>2018</td>\n",
       "      <td>C</td>\n",
       "      <td>27773.0</td>\n",
       "      <td>1574.0</td>\n",
       "      <td>208494900.0</td>\n",
       "      <td>13.320710</td>\n",
       "      <td>0.754935</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>2019</td>\n",
       "      <td>A</td>\n",
       "      <td>891.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210147125.0</td>\n",
       "      <td>0.423989</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>2019</td>\n",
       "      <td>B</td>\n",
       "      <td>13971.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210147125.0</td>\n",
       "      <td>6.648199</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>2019</td>\n",
       "      <td>C</td>\n",
       "      <td>22747.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210147125.0</td>\n",
       "      <td>10.824321</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Ano virus    Casos  Obitos          Pop  taxa_incid_por100k  \\\n",
       "0   2001     A  21011.0     NaN  172385826.0           12.188357   \n",
       "1   2001     B   8344.0     NaN  172385826.0            4.840305   \n",
       "2   2001     C   6713.0     NaN  172385826.0            3.894172   \n",
       "3   2002     A  14468.0     NaN  174632960.0            8.284805   \n",
       "4   2002     B   8841.0     NaN  174632960.0            5.062618   \n",
       "5   2002     C   8081.0     NaN  174632960.0            4.627420   \n",
       "6   2003     A  14715.0     NaN  176871437.0            8.319602   \n",
       "7   2003     B  11673.0     NaN  176871437.0            6.599709   \n",
       "8   2003     C  10241.0     NaN  176871437.0            5.790081   \n",
       "9   2004     A  20585.0     NaN  181581024.0           11.336537   \n",
       "10  2004     B  12835.0     NaN  181581024.0            7.068470   \n",
       "11  2004     C  13285.0     NaN  181581024.0            7.316293   \n",
       "12  2005     A  25302.0     NaN  184184264.0           13.737330   \n",
       "13  2005     B  14107.0     NaN  184184264.0            7.659178   \n",
       "14  2005     C  13702.0     NaN  184184264.0            7.439289   \n",
       "15  2006     A  19095.0     NaN  186770562.0           10.223774   \n",
       "16  2006     B  11330.0     NaN  186770562.0            6.066266   \n",
       "17  2006     C  10319.0     NaN  186770562.0            5.524961   \n",
       "18  2007     A  13351.0    47.0  183989711.0            7.256384   \n",
       "19  2007     B  12406.0   515.0  183989711.0            6.742768   \n",
       "20  2007     C  17173.0  1800.0  183989711.0            9.333674   \n",
       "21  2008     A  11680.0    50.0  189612814.0            6.159921   \n",
       "22  2008     B  13400.0   566.0  189612814.0            7.067033   \n",
       "23  2008     C  20110.0  1898.0  189612814.0           10.605823   \n",
       "24  2009     A  10920.0    45.0  191480630.0            5.702927   \n",
       "25  2009     B  15004.0   482.0  191480630.0            7.835780   \n",
       "26  2009     C  21854.0  1879.0  191480630.0           11.413165   \n",
       "27  2010     A   6929.0    52.0  190747855.0            3.632544   \n",
       "28  2010     B  13711.0   549.0  190747855.0            7.188023   \n",
       "29  2010     C  21066.0  1967.0  190747855.0           11.043899   \n",
       "30  2011     A   7468.0    30.0  192379287.0            3.881915   \n",
       "31  2011     B  16683.0   538.0  192379287.0            8.671932   \n",
       "32  2011     C  24231.0  2012.0  192379287.0           12.595431   \n",
       "33  2012     A   6628.0    38.0  193946886.0            3.417430   \n",
       "34  2012     B  16286.0   443.0  193946886.0            8.397144   \n",
       "35  2012     C  25450.0  2032.0  193946886.0           13.122149   \n",
       "36  2013     A   6270.0    35.0  201032714.0            3.118895   \n",
       "37  2013     B  16720.0   456.0  201032714.0            8.317054   \n",
       "38  2013     C  25401.0  2013.0  201032714.0           12.635257   \n",
       "39  2014     A   6424.0    29.0  202768562.0            3.168144   \n",
       "40  2014     B  16743.0   469.0  202768562.0            8.257197   \n",
       "41  2014     C  24124.0  2087.0  202768562.0           11.897308   \n",
       "42  2015     A   3302.0    24.0  204450649.0            1.615060   \n",
       "43  2015     B  16149.0   451.0  204450649.0            7.898728   \n",
       "44  2015     C  26946.0  2028.0  204450649.0           13.179709   \n",
       "45  2016     A   1207.0    29.0  206081432.0            0.585691   \n",
       "46  2016     B  14829.0   477.0  206081432.0            7.195699   \n",
       "47  2016     C  28732.0  2023.0  206081432.0           13.942062   \n",
       "48  2017     A   2137.0    22.0  207660929.0            1.029081   \n",
       "49  2017     B  14251.0   414.0  207660929.0            6.862629   \n",
       "50  2017     C  25937.0  1720.0  207660929.0           12.490072   \n",
       "51  2018     A   2188.0    28.0  208494900.0            1.049426   \n",
       "52  2018     B  14686.0   424.0  208494900.0            7.043817   \n",
       "53  2018     C  27773.0  1574.0  208494900.0           13.320710   \n",
       "54  2019     A    891.0     NaN  210147125.0            0.423989   \n",
       "55  2019     B  13971.0     NaN  210147125.0            6.648199   \n",
       "56  2019     C  22747.0     NaN  210147125.0           10.824321   \n",
       "\n",
       "    taxa_obitos_por100k  \n",
       "0                   NaN  \n",
       "1                   NaN  \n",
       "2                   NaN  \n",
       "3                   NaN  \n",
       "4                   NaN  \n",
       "5                   NaN  \n",
       "6                   NaN  \n",
       "7                   NaN  \n",
       "8                   NaN  \n",
       "9                   NaN  \n",
       "10                  NaN  \n",
       "11                  NaN  \n",
       "12                  NaN  \n",
       "13                  NaN  \n",
       "14                  NaN  \n",
       "15                  NaN  \n",
       "16                  NaN  \n",
       "17                  NaN  \n",
       "18             0.025545  \n",
       "19             0.279907  \n",
       "20             0.978316  \n",
       "21             0.026370  \n",
       "22             0.298503  \n",
       "23             1.000987  \n",
       "24             0.023501  \n",
       "25             0.251723  \n",
       "26             0.981300  \n",
       "27             0.027261  \n",
       "28             0.287815  \n",
       "29             1.031204  \n",
       "30             0.015594  \n",
       "31             0.279656  \n",
       "32             1.045851  \n",
       "33             0.019593  \n",
       "34             0.228413  \n",
       "35             1.047710  \n",
       "36             0.017410  \n",
       "37             0.226829  \n",
       "38             1.001330  \n",
       "39             0.014302  \n",
       "40             0.231298  \n",
       "41             1.029252  \n",
       "42             0.011739  \n",
       "43             0.220591  \n",
       "44             0.991926  \n",
       "45             0.014072  \n",
       "46             0.231462  \n",
       "47             0.981651  \n",
       "48             0.010594  \n",
       "49             0.199363  \n",
       "50             0.828273  \n",
       "51             0.013430  \n",
       "52             0.203362  \n",
       "53             0.754935  \n",
       "54                  NaN  \n",
       "55                  NaN  \n",
       "56                  NaN  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Juntando os dados de casos e óbitos com de população com base na coluna 'Ano'\n",
    "dados = pd.merge(dados, pop_2001_2019, on='Ano')\n",
    "\n",
    "#Criando a coluna 'taxa_incid_por100k' representando a quantidade de casos por 100.000 habitantes\n",
    "dados['taxa_incid_por100k'] = dados['Casos'] * 100000 / dados['Pop']\n",
    "\n",
    "#Criando a coluna 'taxa_obitos_por100k' representando a quantidade de óbitos por 100.000 habitantes\n",
    "dados['taxa_obitos_por100k'] = dados['Obitos'] * 100000 / dados['Pop']\n",
    "\n",
    "dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Removendo coluna 'Pop' pois não será mais utilizada\n",
    "dados = dados.drop(columns='Pop')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Salvando os dados na pasta 'dados_tratados'\n",
    "dados.set_index('Ano').to_csv('../dados_tratados/casos_obitos_hep')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}