covid-eda-and-dashboard.ipynb

{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.7.12","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat_minor":4,"nbformat":4,"cells":[{"source":"<a href=\"https://www.kaggle.com/code/lucasfontes/covid-eda-and-dashboard?scriptVersionId=113751756\" target=\"_blank\"><img align=\"left\" alt=\"Kaggle\" title=\"Open in Kaggle\" src=\"https://kaggle.com/static/images/open-in-kaggle.svg\"></a>","metadata":{},"cell_type":"markdown","outputs":[],"execution_count":0},{"cell_type":"markdown","source":"# Resumo TL;DR\n\nCódigo para execução de projeto do curso de Analista de Dados da EBAC.\n\nAnálise exploratória de dados da COVID19, para construção de um dashboard no Google Data Studio\n\nDashboard:\n- Google Data Studio ([link](https://datastudio.google.com/reporting/68f1b012-02ab-4ad5-a0cd-7464026a95e3)).\n\nProcessamento:\n- Kaggle Notebook ([link](https://www.kaggle.com/code/lucasfontes/covid-eda-and-dashboard/notebook?scriptVersionId=107676869)).\n\n**Fontes:**\n- Casos pela universidade John Hopkins ([link](https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data/csse_covid_19_daily_reports));\n- Vacinação pela universidade de Oxford ([link](https://covid.ourworldindata.org/data/owid-covid-data.csv)).\n\n","metadata":{}},{"cell_type":"markdown","source":"# 1. Contexto\n","metadata":{}},{"cell_type":"markdown","source":"## 1.1 Pandemia do Coronavírus","metadata":{}},{"cell_type":"markdown","source":"> A COVID-19 é uma infecção respiratória aguda causada pelo coronavírus SARS-CoV-2, potencialmente grave, de elevada transmissibilidade e de distribuição global. Fonte: Governo brasileiro ([link](https://www.gov.br/saude/pt-br/coronavirus/o-que-e-o-coronavirus)).","metadata":{}},{"cell_type":"markdown","source":"A disponibilidade de dados sobre a evolução da pandemia no tempo em uma determinada região geográfica é fundamental para o seu combate! Este projeto busca construir um dashboard de dados para exploração e visualização interativa de dados sobre o avanço de casos e da vacinação do Brasil. \n\nO processamento de dados está neste [link](https://www.kaggle.com/code/lucasfontes/covid-eda-and-dashboard/notebook?scriptVersionId=107676869) e o dashboard, neste [link](https://datastudio.google.com/reporting/68f1b012-02ab-4ad5-a0cd-7464026a95e3).","metadata":{}},{"cell_type":"markdown","source":"## 1.2 Dados","metadata":{}},{"cell_type":"markdown","source":"Os dados sobre **casos da COVID-19** são compilados pelo centro de ciência de sistemas e engenharia da universidade americana **John Hopkins** ([link](https://www.jhu.edu)). Os dados são atualizados diariamente deste janeiro de 2020 com uma granularidade temporal de dias e geográfica de regiões de países (estados, condados, etc.). O website do projeto pode ser acessado neste [link](https://systems.jhu.edu/research/public-health/ncov/) enquanto os dados, neste [link](https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data/csse_covid_19_daily_reports). Abaixo estão descritos os dados derivados do seu processamento.","metadata":{}},{"cell_type":"markdown","source":" - **date**: data de referência;\n - **state**: estado;\n - **country**: país; \n - **population**: população estimada;\n - **confirmed**: número acumulado de infectados;\n - **confirmed_1d**: número diário de infectados;\n - **confirmed_moving_avg_7d**: média móvel de 7 dias do número diário de infectados;\n - **confirmed_moving_avg_7d_rate_14d**: média móvel de 7 dias dividido pela média móvel de 7 dias de 14 dias atrás;\n - **deaths**: número acumulado de mortos;\n - **deaths_1d**: número diário de mortos;\n - **deaths_moving_avg_7d**: média móvel de 7 dias do número diário de mortos;\n - **deaths_moving_avg_7d**: média móvel de 7 dias dividido pela média móvel de 7 dias de 14 dias atrás;\n - **month**: mês de referência;\n - **year**: ano de referência.","metadata":{}},{"cell_type":"markdown","source":"Os dados sobre **vacinação da COVID-19** são compilados pelo projeto Nosso Mundo em Dados (*Our World in Data* ou OWID) da universidade britânica de **Oxford** ([link](https://www.ox.ac.uk)). Os dados são **atualizados diariamente** deste janeiro de 2020 com uma **granularidade temporal de dias e geográfica de países**. O website do projeto pode ser acessado neste [link](https://ourworldindata.org) enquanto os dados, neste [link](https://covid.ourworldindata.org/data/owid-covid-data.csv). Abaixo estão descritos os dados derivados do seu processamento.","metadata":{}},{"cell_type":"markdown","source":" - **date**: data de referência;\n - **country**: país;\n - **population**: população estimada;\n - **total**: número acumulado de doses administradas;\n - **one_shot**: número acumulado de pessoas com uma dose;\n - **one_shot_perc**: número acumulado relativo de pessoas com uma dose;\n - **two_shots**: número acumulado de pessoas com duas doses;\n - **two_shot_perc**: número acumulado relativo de pessoas com duas doses;\n - **three_shots**: número acumulado de pessoas com três doses;\n - **three_shot_perc**: número acumulado relativo de pessoas com três doses;\n - **month**: mês de referência;\n - **year**: ano de referência.","metadata":{}},{"cell_type":"markdown","source":"# 2. Análise Exploratória","metadata":{}},{"cell_type":"markdown","source":"Nesta sessão vamos utilizar os seguintes pacotes Python para processar os dados bruto em um formato adequado para um painel para exploração interativa de dados.","metadata":{}},{"cell_type":"markdown","source":"## 2.1 Pacotes e bibliotecas","metadata":{}},{"cell_type":"code","source":"import math\nfrom typing import Iterator\nfrom datetime import datetime, timedelta\n\nimport numpy as np\nimport pandas as pd","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2022-12-13T21:04:28.432291Z","iopub.execute_input":"2022-12-13T21:04:28.432719Z","iopub.status.idle":"2022-12-13T21:04:28.459588Z","shell.execute_reply.started":"2022-12-13T21:04:28.432633Z","shell.execute_reply":"2022-12-13T21:04:28.458706Z"},"trusted":true},"execution_count":1,"outputs":[]},{"cell_type":"markdown","source":"## 2.2 Casos","metadata":{}},{"cell_type":"markdown","source":"### 2.2.1 Extração\n","metadata":{}},{"cell_type":"markdown","source":"Vamos processar os dados de **casos** da universidade John Hopkins.\n\nO dado está compilado em um arquivo por dia, exemplo para 2021/12/01.","metadata":{}},{"cell_type":"code","source":"cases = pd.read_csv('https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/01-12-2021.csv', sep=',')","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2022-12-13T21:04:28.512315Z","iopub.execute_input":"2022-12-13T21:04:28.512741Z","iopub.status.idle":"2022-12-13T21:04:28.725114Z","shell.execute_reply.started":"2022-12-13T21:04:28.512692Z","shell.execute_reply":"2022-12-13T21:04:28.723951Z"},"trusted":true},"execution_count":2,"outputs":[]},{"cell_type":"code","source":"cases.head()","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2022-12-13T21:04:28.72657Z","iopub.execute_input":"2022-12-13T21:04:28.726927Z","iopub.status.idle":"2022-12-13T21:04:28.75859Z","shell.execute_reply.started":"2022-12-13T21:04:28.72686Z","shell.execute_reply":"2022-12-13T21:04:28.757386Z"},"trusted":true},"execution_count":3,"outputs":[{"execution_count":3,"output_type":"execute_result","data":{"text/plain":"   FIPS Admin2 Province_State Country_Region          Last_Update       Lat  \\\n0   NaN    NaN            NaN    Afghanistan  2021-01-13 05:22:15  33.93911   \n1   NaN    NaN            NaN        Albania  2021-01-13 05:22:15  41.15330   \n2   NaN    NaN            NaN        Algeria  2021-01-13 05:22:15  28.03390   \n3   NaN    NaN            NaN        Andorra  2021-01-13 05:22:15  42.50630   \n4   NaN    NaN            NaN         Angola  2021-01-13 05:22:15 -11.20270   \n\n       Long_  Confirmed  Deaths  Recovered  Active Combined_Key  \\\n0  67.709953      53584    2301      44608    6675  Afghanistan   \n1  20.168300      64627    1252      38421   24954      Albania   \n2   1.659600     102641    2816      69608   30217      Algeria   \n3   1.521800       8682      86       7930     666      Andorra   \n4  17.873900      18343     422      15512    2409       Angola   \n\n   Incident_Rate  Case_Fatality_Ratio  \n0     137.647787             4.294192  \n1    2245.708527             1.937271  \n2     234.067409             2.743543  \n3   11236.653077             0.990555  \n4      55.811022             2.300605  ","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>FIPS</th>\n      <th>Admin2</th>\n      <th>Province_State</th>\n      <th>Country_Region</th>\n      <th>Last_Update</th>\n      <th>Lat</th>\n      <th>Long_</th>\n      <th>Confirmed</th>\n      <th>Deaths</th>\n      <th>Recovered</th>\n      <th>Active</th>\n      <th>Combined_Key</th>\n      <th>Incident_Rate</th>\n      <th>Case_Fatality_Ratio</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Afghanistan</td>\n      <td>2021-01-13 05:22:15</td>\n      <td>33.93911</td>\n      <td>67.709953</td>\n      <td>53584</td>\n      <td>2301</td>\n      <td>44608</td>\n      <td>6675</td>\n      <td>Afghanistan</td>\n      <td>137.647787</td>\n      <td>4.294192</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Albania</td>\n      <td>2021-01-13 05:22:15</td>\n      <td>41.15330</td>\n      <td>20.168300</td>\n      <td>64627</td>\n      <td>1252</td>\n      <td>38421</td>\n      <td>24954</td>\n      <td>Albania</td>\n      <td>2245.708527</td>\n      <td>1.937271</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Algeria</td>\n      <td>2021-01-13 05:22:15</td>\n      <td>28.03390</td>\n      <td>1.659600</td>\n      <td>102641</td>\n      <td>2816</td>\n      <td>69608</td>\n      <td>30217</td>\n      <td>Algeria</td>\n      <td>234.067409</td>\n      <td>2.743543</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Andorra</td>\n      <td>2021-01-13 05:22:15</td>\n      <td>42.50630</td>\n      <td>1.521800</td>\n      <td>8682</td>\n      <td>86</td>\n      <td>7930</td>\n      <td>666</td>\n      <td>Andorra</td>\n      <td>11236.653077</td>\n      <td>0.990555</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Angola</td>\n      <td>2021-01-13 05:22:15</td>\n      <td>-11.20270</td>\n      <td>17.873900</td>\n      <td>18343</td>\n      <td>422</td>\n      <td>15512</td>\n      <td>2409</td>\n      <td>Angola</td>\n      <td>55.811022</td>\n      <td>2.300605</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{}}]},{"cell_type":"markdown","source":"Portanto, precisaremos iterar dentro de um intervalo de tempo definido para extraí-lo.","metadata":{}},{"cell_type":"code","source":"def date_range(start_date: datetime, end_date: datetime) -> Iterator[datetime]:\n  date_range_days: int = (end_date - start_date).days\n  for lag in range(date_range_days):\n    yield start_date + timedelta(lag)","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:04:28.759805Z","iopub.execute_input":"2022-12-13T21:04:28.760269Z","iopub.status.idle":"2022-12-13T21:04:28.765933Z","shell.execute_reply.started":"2022-12-13T21:04:28.760236Z","shell.execute_reply":"2022-12-13T21:04:28.764775Z"},"trusted":true},"execution_count":4,"outputs":[]},{"cell_type":"code","source":"start_date = datetime(2021,  1,  1)\nend_date   = datetime(2022,  1,  1)","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:04:28.768413Z","iopub.execute_input":"2022-12-13T21:04:28.768713Z","iopub.status.idle":"2022-12-13T21:04:28.776362Z","shell.execute_reply.started":"2022-12-13T21:04:28.768686Z","shell.execute_reply":"2022-12-13T21:04:28.775316Z"},"trusted":true},"execution_count":5,"outputs":[]},{"cell_type":"markdown","source":"De maneira iterativa, vamos selecionar as colunas de interesse e as linhas referentes ao Brasil.","metadata":{}},{"cell_type":"code","source":"cases = None\ncases_is_empty = True\n\nfor date in date_range(start_date=start_date, end_date=end_date):\n\n  date_str = date.strftime('%m-%d-%Y')\n  data_source_url = f'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/{date_str}.csv'\n\n  case = pd.read_csv(data_source_url, sep=',')\n\n  case = case.drop(['FIPS', 'Admin2', 'Last_Update', 'Lat', 'Long_', 'Recovered', 'Active', 'Combined_Key', 'Case_Fatality_Ratio'], axis=1)\n  case = case.query('Country_Region == \"Brazil\"').reset_index(drop=True)\n  case['Date'] = pd.to_datetime(date.strftime('%Y-%m-%d'))\n\n  if cases_is_empty:\n    cases = case\n    cases_is_empty = False\n  else:\n    cases = cases.append(case, ignore_index=True)","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:04:28.778021Z","iopub.execute_input":"2022-12-13T21:04:28.778311Z","iopub.status.idle":"2022-12-13T21:05:52.262249Z","shell.execute_reply.started":"2022-12-13T21:04:28.778281Z","shell.execute_reply":"2022-12-13T21:05:52.260848Z"},"trusted":true},"execution_count":6,"outputs":[]},{"cell_type":"code","source":"cases.query('Province_State == \"Sao Paulo\"').head()","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.263644Z","iopub.execute_input":"2022-12-13T21:05:52.263953Z","iopub.status.idle":"2022-12-13T21:05:52.283611Z","shell.execute_reply.started":"2022-12-13T21:05:52.263924Z","shell.execute_reply":"2022-12-13T21:05:52.282612Z"},"trusted":true},"execution_count":7,"outputs":[{"execution_count":7,"output_type":"execute_result","data":{"text/plain":"    Province_State Country_Region  Confirmed  Deaths  Incident_Rate       Date\n24       Sao Paulo         Brazil    1466191   46775    3192.990778 2021-01-01\n51       Sao Paulo         Brazil    1467953   46808    3196.827966 2021-01-02\n78       Sao Paulo         Brazil    1471422   46845    3204.382565 2021-01-03\n105      Sao Paulo         Brazil    1473670   46888    3209.278136 2021-01-04\n132      Sao Paulo         Brazil    1486551   47222    3237.329676 2021-01-05","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>Province_State</th>\n      <th>Country_Region</th>\n      <th>Confirmed</th>\n      <th>Deaths</th>\n      <th>Incident_Rate</th>\n      <th>Date</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>24</th>\n      <td>Sao Paulo</td>\n      <td>Brazil</td>\n      <td>1466191</td>\n      <td>46775</td>\n      <td>3192.990778</td>\n      <td>2021-01-01</td>\n    </tr>\n    <tr>\n      <th>51</th>\n      <td>Sao Paulo</td>\n      <td>Brazil</td>\n      <td>1467953</td>\n      <td>46808</td>\n      <td>3196.827966</td>\n      <td>2021-01-02</td>\n    </tr>\n    <tr>\n      <th>78</th>\n      <td>Sao Paulo</td>\n      <td>Brazil</td>\n      <td>1471422</td>\n      <td>46845</td>\n      <td>3204.382565</td>\n      <td>2021-01-03</td>\n    </tr>\n    <tr>\n      <th>105</th>\n      <td>Sao Paulo</td>\n      <td>Brazil</td>\n      <td>1473670</td>\n      <td>46888</td>\n      <td>3209.278136</td>\n      <td>2021-01-04</td>\n    </tr>\n    <tr>\n      <th>132</th>\n      <td>Sao Paulo</td>\n      <td>Brazil</td>\n      <td>1486551</td>\n      <td>47222</td>\n      <td>3237.329676</td>\n      <td>2021-01-05</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{}}]},{"cell_type":"markdown","source":"### 2.2.2 Transformação\n","metadata":{}},{"cell_type":"markdown","source":"Vamos manipular os dados para o dashboard. O foco é em garantir uma boa granularidade e qualidade da base de dados.","metadata":{}},{"cell_type":"code","source":"cases.head()","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.285259Z","iopub.execute_input":"2022-12-13T21:05:52.28563Z","iopub.status.idle":"2022-12-13T21:05:52.299041Z","shell.execute_reply.started":"2022-12-13T21:05:52.2856Z","shell.execute_reply":"2022-12-13T21:05:52.298016Z"},"trusted":true},"execution_count":8,"outputs":[{"execution_count":8,"output_type":"execute_result","data":{"text/plain":"  Province_State Country_Region  Confirmed  Deaths  Incident_Rate       Date\n0           Acre         Brazil      41689     796    4726.992352 2021-01-01\n1        Alagoas         Brazil     105091    2496    3148.928928 2021-01-01\n2          Amapa         Brazil      68361     926    8083.066602 2021-01-01\n3       Amazonas         Brazil     201574    5295    4863.536793 2021-01-01\n4          Bahia         Brazil     494684    9159    3326.039611 2021-01-01","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>Province_State</th>\n      <th>Country_Region</th>\n      <th>Confirmed</th>\n      <th>Deaths</th>\n      <th>Incident_Rate</th>\n      <th>Date</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Acre</td>\n      <td>Brazil</td>\n      <td>41689</td>\n      <td>796</td>\n      <td>4726.992352</td>\n      <td>2021-01-01</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Alagoas</td>\n      <td>Brazil</td>\n      <td>105091</td>\n      <td>2496</td>\n      <td>3148.928928</td>\n      <td>2021-01-01</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Amapa</td>\n      <td>Brazil</td>\n      <td>68361</td>\n      <td>926</td>\n      <td>8083.066602</td>\n      <td>2021-01-01</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Amazonas</td>\n      <td>Brazil</td>\n      <td>201574</td>\n      <td>5295</td>\n      <td>4863.536793</td>\n      <td>2021-01-01</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Bahia</td>\n      <td>Brazil</td>\n      <td>494684</td>\n      <td>9159</td>\n      <td>3326.039611</td>\n      <td>2021-01-01</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{}}]},{"cell_type":"code","source":"cases.shape","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.300489Z","iopub.execute_input":"2022-12-13T21:05:52.3008Z","iopub.status.idle":"2022-12-13T21:05:52.307163Z","shell.execute_reply.started":"2022-12-13T21:05:52.300769Z","shell.execute_reply":"2022-12-13T21:05:52.306272Z"},"trusted":true},"execution_count":9,"outputs":[{"execution_count":9,"output_type":"execute_result","data":{"text/plain":"(9855, 6)"},"metadata":{}}]},{"cell_type":"code","source":"cases.info()","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.310943Z","iopub.execute_input":"2022-12-13T21:05:52.311604Z","iopub.status.idle":"2022-12-13T21:05:52.330548Z","shell.execute_reply.started":"2022-12-13T21:05:52.311574Z","shell.execute_reply":"2022-12-13T21:05:52.329204Z"},"trusted":true},"execution_count":10,"outputs":[{"name":"stdout","text":"<class 'pandas.core.frame.DataFrame'>\nRangeIndex: 9855 entries, 0 to 9854\nData columns (total 6 columns):\n #   Column          Non-Null Count  Dtype         \n---  ------          --------------  -----         \n 0   Province_State  9855 non-null   object        \n 1   Country_Region  9855 non-null   object        \n 2   Confirmed       9855 non-null   int64         \n 3   Deaths          9855 non-null   int64         \n 4   Incident_Rate   9855 non-null   float64       \n 5   Date            9855 non-null   datetime64[ns]\ndtypes: datetime64[ns](1), float64(1), int64(2), object(2)\nmemory usage: 462.1+ KB\n","output_type":"stream"}]},{"cell_type":"markdown","source":"Atualizando o nome das colunas","metadata":{}},{"cell_type":"code","source":"cases = cases.rename(\n    columns={\n        'Province_State': 'state',\n        'Country_Region': 'country'\n    }\n\n)","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.331834Z","iopub.execute_input":"2022-12-13T21:05:52.332197Z","iopub.status.idle":"2022-12-13T21:05:52.339975Z","shell.execute_reply.started":"2022-12-13T21:05:52.332167Z","shell.execute_reply":"2022-12-13T21:05:52.337883Z"},"trusted":true},"execution_count":11,"outputs":[]},{"cell_type":"code","source":"for col in cases.columns:\n    cases = cases.rename(columns={col: col.lower()})","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.341975Z","iopub.execute_input":"2022-12-13T21:05:52.345914Z","iopub.status.idle":"2022-12-13T21:05:52.364026Z","shell.execute_reply.started":"2022-12-13T21:05:52.34588Z","shell.execute_reply":"2022-12-13T21:05:52.353552Z"},"trusted":true},"execution_count":12,"outputs":[]},{"cell_type":"markdown","source":"Ajustando o nome dos estados.","metadata":{}},{"cell_type":"code","source":"states_map = {\n    'Amapa': 'Amapá',\n    'Ceara': 'Ceará',\n    'Espirito Santo': 'Espírito Santo',\n    'Goias': 'Goiás',\n    'Para': 'Pará',\n    'Paraiba': 'Paraíba',\n    'Parana': 'Paraná',\n    'Piaui': 'Piauí',\n    'Rondonia': 'Rondônia',\n    'Sao Paulo': 'São Paulo'\n}","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.366231Z","iopub.execute_input":"2022-12-13T21:05:52.366932Z","iopub.status.idle":"2022-12-13T21:05:52.374784Z","shell.execute_reply.started":"2022-12-13T21:05:52.366889Z","shell.execute_reply":"2022-12-13T21:05:52.373045Z"},"trusted":true},"execution_count":13,"outputs":[]},{"cell_type":"code","source":"cases['state'] = cases['state'].apply(lambda state: states_map.get(state) if state in states_map.keys() else state)","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.376544Z","iopub.execute_input":"2022-12-13T21:05:52.37741Z","iopub.status.idle":"2022-12-13T21:05:52.389856Z","shell.execute_reply.started":"2022-12-13T21:05:52.377377Z","shell.execute_reply":"2022-12-13T21:05:52.388866Z"},"trusted":true},"execution_count":14,"outputs":[]},{"cell_type":"markdown","source":"Vamos então computar novas colunas para enriquecer a base de dados.","metadata":{}},{"cell_type":"markdown","source":"**Chaves temporais**","metadata":{}},{"cell_type":"code","source":"cases['month'] = cases['date'].apply(lambda date: date.strftime('%Y-%m'))\ncases['year'] = cases['date'].apply(lambda date: date.strftime('%Y'))","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.39152Z","iopub.execute_input":"2022-12-13T21:05:52.392193Z","iopub.status.idle":"2022-12-13T21:05:52.52802Z","shell.execute_reply.started":"2022-12-13T21:05:52.392151Z","shell.execute_reply":"2022-12-13T21:05:52.527177Z"},"trusted":true},"execution_count":15,"outputs":[]},{"cell_type":"markdown","source":"**População estimada do estado**","metadata":{}},{"cell_type":"code","source":"cases['population'] = round(100000 * (cases['confirmed'] / cases['incident_rate']))\ncases = cases.drop('incident_rate', axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.529616Z","iopub.execute_input":"2022-12-13T21:05:52.53036Z","iopub.status.idle":"2022-12-13T21:05:52.54079Z","shell.execute_reply.started":"2022-12-13T21:05:52.530316Z","shell.execute_reply":"2022-12-13T21:05:52.5397Z"},"trusted":true},"execution_count":16,"outputs":[]},{"cell_type":"markdown","source":"**Número, média móvel (7 dias) e estabilidade (14 dias) de casos e mortes por estado**","metadata":{}},{"cell_type":"markdown","source":"| 1 | 2 | 3 | 4 | 5 | 6 | <font color='red'>7</font> | <font color='green'>8</font> | 9 | 10 | 11 | 12 | 13 | <font color='blue'>14<font color='red'> | 15 | 16 | 17 | 18 | 19 | 20 | 21 |\n| - | - | - | - | - | - | - | - | - | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- |\n| <font color='red'>D-6</font> | <font color='red'>D-5</font> | <font color='red'>D-4</font> | <font color='red'>D-3</font> | <font color='red'>D-2</font> | <font color='red'>D-1</font> | <font color='red'>D0</font> | | | | | | | | | | | | | | |\n| D-7 | <font color='green'>D-6</font> | <font color='green'>D-5</font> | <font color='green'>D-4</font> | <font color='green'>D-3</font> | <font color='green'>D-2</font> | <font color='green'>D-1</font> | <font color='green'>D0</font> | | | | | | | | | | | | | |\n| D-13 | D-12 | D-11 | D-10 | D-9 | D-8 | D-7 | <font color='blue'>D-6</font> | <font color='blue'>D-5</font> | <font color='blue'>D-4</font> | <font color='blue'>D-3</font> | <font color='blue'>D-2</font> | <font color='blue'>D-1</font> | <font color='blue'>D0</font> | | | | | | | |","metadata":{}},{"cell_type":"code","source":"cases_ = None\ncases_is_empty = True\n\ndef get_trend(rate: float) -> str:\n    \n    if np.isnan(rate):\n        return np.NaN\n    \n    if rate < 0.75:\n        status = 'downward'\n    elif rate > 1.15:\n        status = 'upward'\n    else:\n        status = 'stable'\n    \n    return status","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.542586Z","iopub.execute_input":"2022-12-13T21:05:52.543194Z","iopub.status.idle":"2022-12-13T21:05:52.550431Z","shell.execute_reply.started":"2022-12-13T21:05:52.543152Z","shell.execute_reply":"2022-12-13T21:05:52.549408Z"},"trusted":true},"execution_count":17,"outputs":[]},{"cell_type":"code","source":"for state in cases['state'].drop_duplicates():\n    \n    cases_per_state = cases.query(f'state == \"{state}\"').reset_index(drop=True)\n    cases_per_state = cases_per_state.sort_values(by=['date'])\n    \n    cases_per_state['confirmed_1d'] = cases_per_state['confirmed'].diff(periods=1)\n    cases_per_state['confirmed_moving_avg_7d'] = np.ceil(cases_per_state['confirmed_1d'].rolling(window=7).mean())\n    cases_per_state['confirmed_moving_avg_7d_rate_14d'] = cases_per_state['confirmed_moving_avg_7d']/cases_per_state['confirmed_moving_avg_7d'].shift(periods=14)\n    cases_per_state['confirmed_trend'] = cases_per_state['confirmed_moving_avg_7d_rate_14d'].apply(get_trend)\n    \n    cases_per_state['deaths_1d'] = cases_per_state['deaths'].diff(periods=1)\n    cases_per_state['deaths_moving_avg_7d'] = np.ceil(cases_per_state['deaths_1d'].rolling(window=7).mean())\n    cases_per_state['deaths_moving_avg_7d_rate_14d'] = cases_per_state['deaths_moving_avg_7d']/cases_per_state['deaths_moving_avg_7d'].shift(periods=14)\n    cases_per_state['deaths_trend'] = cases_per_state['deaths_moving_avg_7d_rate_14d'].apply(get_trend)    \n    \n    if cases_is_empty:\n        cases_ = cases_per_state\n        cases_is_empty = False\n    else:\n        cases_ = cases_.append(cases_per_state, ignore_index=True)\n        \ncases = cases_\ncases_ = None","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.551484Z","iopub.execute_input":"2022-12-13T21:05:52.552288Z","iopub.status.idle":"2022-12-13T21:05:52.918689Z","shell.execute_reply.started":"2022-12-13T21:05:52.552257Z","shell.execute_reply":"2022-12-13T21:05:52.917885Z"},"trusted":true},"execution_count":18,"outputs":[]},{"cell_type":"markdown","source":"Garantir o tipo do dado é fundamental para consistência da base de dados. Vamos fazer o *type casting* das colunas.","metadata":{}},{"cell_type":"code","source":"cases['population'] = cases['population'].astype('Int64')\ncases['confirmed_1d'] = cases['confirmed_1d'].astype('Int64')\ncases['confirmed_moving_avg_7d'] = cases['confirmed_moving_avg_7d'].astype('Int64')\ncases['deaths_1d'] = cases['deaths_1d'].astype('Int64')\ncases['deaths_moving_avg_7d'] = cases['deaths_moving_avg_7d'].astype('Int64')","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.920121Z","iopub.execute_input":"2022-12-13T21:05:52.921715Z","iopub.status.idle":"2022-12-13T21:05:52.932022Z","shell.execute_reply.started":"2022-12-13T21:05:52.921672Z","shell.execute_reply":"2022-12-13T21:05:52.931175Z"},"trusted":true},"execution_count":19,"outputs":[]},{"cell_type":"markdown","source":"Por fim, vamos reorganizar as colunas e conferir o resultado final.","metadata":{}},{"cell_type":"code","source":"cases = cases[['date', 'country', 'state', 'population', 'confirmed', 'confirmed_1d', 'confirmed_moving_avg_7d', 'confirmed_moving_avg_7d_rate_14d', 'confirmed_trend', 'deaths', 'deaths_1d', 'deaths_moving_avg_7d', 'deaths_moving_avg_7d_rate_14d', 'deaths_trend', 'month', 'year']]","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.933932Z","iopub.execute_input":"2022-12-13T21:05:52.934733Z","iopub.status.idle":"2022-12-13T21:05:52.947338Z","shell.execute_reply.started":"2022-12-13T21:05:52.934688Z","shell.execute_reply":"2022-12-13T21:05:52.946274Z"},"trusted":true},"execution_count":20,"outputs":[]},{"cell_type":"code","source":"cases.head(n=25)","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.948834Z","iopub.execute_input":"2022-12-13T21:05:52.9498Z","iopub.status.idle":"2022-12-13T21:05:52.981198Z","shell.execute_reply.started":"2022-12-13T21:05:52.949768Z","shell.execute_reply":"2022-12-13T21:05:52.980083Z"},"trusted":true},"execution_count":21,"outputs":[{"execution_count":21,"output_type":"execute_result","data":{"text/plain":"         date country state  population  confirmed  confirmed_1d  \\\n0  2021-01-01  Brazil  Acre      881935      41689          <NA>   \n1  2021-01-02  Brazil  Acre      881935      41941           252   \n2  2021-01-03  Brazil  Acre      881935      42046           105   \n3  2021-01-04  Brazil  Acre      881935      42117            71   \n4  2021-01-05  Brazil  Acre      881935      42170            53   \n5  2021-01-06  Brazil  Acre      881935      42378           208   \n6  2021-01-07  Brazil  Acre      881935      42478           100   \n7  2021-01-08  Brazil  Acre      881935      42814           336   \n8  2021-01-09  Brazil  Acre      881935      42908            94   \n9  2021-01-10  Brazil  Acre      881935      43127           219   \n10 2021-01-11  Brazil  Acre      881935      43346           219   \n11 2021-01-12  Brazil  Acre      881935      43432            86   \n12 2021-01-13  Brazil  Acre      881935      43785           353   \n13 2021-01-14  Brazil  Acre      881935      44068           283   \n14 2021-01-15  Brazil  Acre      881935      44156            88   \n15 2021-01-16  Brazil  Acre      881935      44621           465   \n16 2021-01-17  Brazil  Acre      881935      44767           146   \n17 2021-01-18  Brazil  Acre      881935      44775             8   \n18 2021-01-19  Brazil  Acre      881935      45208           433   \n19 2021-01-20  Brazil  Acre      881935      45429           221   \n20 2021-01-21  Brazil  Acre      881935      45729           300   \n21 2021-01-22  Brazil  Acre      881935      45987           258   \n22 2021-01-23  Brazil  Acre      881935      46239           252   \n23 2021-01-24  Brazil  Acre      881935      46429           190   \n24 2021-01-25  Brazil  Acre      881935      46539           110   \n\n    confirmed_moving_avg_7d  confirmed_moving_avg_7d_rate_14d confirmed_trend  \\\n0                      <NA>                               NaN             NaN   \n1                      <NA>                               NaN             NaN   \n2                      <NA>                               NaN             NaN   \n3                      <NA>                               NaN             NaN   \n4                      <NA>                               NaN             NaN   \n5                      <NA>                               NaN             NaN   \n6                      <NA>                               NaN             NaN   \n7                       161                               NaN             NaN   \n8                       139                               NaN             NaN   \n9                       155                               NaN             NaN   \n10                      176                               NaN             NaN   \n11                      181                               NaN             NaN   \n12                      201                               NaN             NaN   \n13                      228                               NaN             NaN   \n14                      192                               NaN             NaN   \n15                      245                               NaN             NaN   \n16                      235                               NaN             NaN   \n17                      205                               NaN             NaN   \n18                      254                               NaN             NaN   \n19                      235                               NaN             NaN   \n20                      238                               NaN             NaN   \n21                      262                          1.627329          upward   \n22                      232                          1.669065          upward   \n23                      238                          1.535484          upward   \n24                      252                          1.431818          upward   \n\n    deaths  deaths_1d  deaths_moving_avg_7d  deaths_moving_avg_7d_rate_14d  \\\n0      796       <NA>                  <NA>                            NaN   \n1      798          2                  <NA>                            NaN   \n2      802          4                  <NA>                            NaN   \n3      806          4                  <NA>                            NaN   \n4      808          2                  <NA>                            NaN   \n5      814          6                  <NA>                            NaN   \n6      821          7                  <NA>                            NaN   \n7      823          2                     4                            NaN   \n8      823          0                     4                            NaN   \n9      825          2                     4                            NaN   \n10     826          1                     3                            NaN   \n11     827          1                     3                            NaN   \n12     829          2                     3                            NaN   \n13     832          3                     2                            NaN   \n14     835          3                     2                            NaN   \n15     835          0                     2                            NaN   \n16     836          1                     2                            NaN   \n17     837          1                     2                            NaN   \n18     839          2                     2                            NaN   \n19     840          1                     2                            NaN   \n20     844          4                     2                            NaN   \n21     846          2                     2                            0.5   \n22     848          2                     2                            0.5   \n23     850          2                     2                            0.5   \n24     854          4                     3                            1.0   \n\n   deaths_trend    month  year  \n0           NaN  2021-01  2021  \n1           NaN  2021-01  2021  \n2           NaN  2021-01  2021  \n3           NaN  2021-01  2021  \n4           NaN  2021-01  2021  \n5           NaN  2021-01  2021  \n6           NaN  2021-01  2021  \n7           NaN  2021-01  2021  \n8           NaN  2021-01  2021  \n9           NaN  2021-01  2021  \n10          NaN  2021-01  2021  \n11          NaN  2021-01  2021  \n12          NaN  2021-01  2021  \n13          NaN  2021-01  2021  \n14          NaN  2021-01  2021  \n15          NaN  2021-01  2021  \n16          NaN  2021-01  2021  \n17          NaN  2021-01  2021  \n18          NaN  2021-01  2021  \n19          NaN  2021-01  2021  \n20          NaN  2021-01  2021  \n21     downward  2021-01  2021  \n22     downward  2021-01  2021  \n23     downward  2021-01  2021  \n24       stable  2021-01  2021  ","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>date</th>\n      <th>country</th>\n      <th>state</th>\n      <th>population</th>\n      <th>confirmed</th>\n      <th>confirmed_1d</th>\n      <th>confirmed_moving_avg_7d</th>\n      <th>confirmed_moving_avg_7d_rate_14d</th>\n      <th>confirmed_trend</th>\n      <th>deaths</th>\n      <th>deaths_1d</th>\n      <th>deaths_moving_avg_7d</th>\n      <th>deaths_moving_avg_7d_rate_14d</th>\n      <th>deaths_trend</th>\n      <th>month</th>\n      <th>year</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2021-01-01</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>41689</td>\n      <td>&lt;NA&gt;</td>\n      <td>&lt;NA&gt;</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>796</td>\n      <td>&lt;NA&gt;</td>\n      <td>&lt;NA&gt;</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2021-01-02</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>41941</td>\n      <td>252</td>\n      <td>&lt;NA&gt;</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>798</td>\n      <td>2</td>\n      <td>&lt;NA&gt;</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2021-01-03</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>42046</td>\n      <td>105</td>\n      <td>&lt;NA&gt;</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>802</td>\n      <td>4</td>\n      <td>&lt;NA&gt;</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2021-01-04</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>42117</td>\n      <td>71</td>\n      <td>&lt;NA&gt;</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>806</td>\n      <td>4</td>\n      <td>&lt;NA&gt;</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2021-01-05</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>42170</td>\n      <td>53</td>\n      <td>&lt;NA&gt;</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>808</td>\n      <td>2</td>\n      <td>&lt;NA&gt;</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>2021-01-06</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>42378</td>\n      <td>208</td>\n      <td>&lt;NA&gt;</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>814</td>\n      <td>6</td>\n      <td>&lt;NA&gt;</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>2021-01-07</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>42478</td>\n      <td>100</td>\n      <td>&lt;NA&gt;</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>821</td>\n      <td>7</td>\n      <td>&lt;NA&gt;</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>2021-01-08</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>42814</td>\n      <td>336</td>\n      <td>161</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>823</td>\n      <td>2</td>\n      <td>4</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>2021-01-09</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>42908</td>\n      <td>94</td>\n      <td>139</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>823</td>\n      <td>0</td>\n      <td>4</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>2021-01-10</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>43127</td>\n      <td>219</td>\n      <td>155</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>825</td>\n      <td>2</td>\n      <td>4</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>2021-01-11</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>43346</td>\n      <td>219</td>\n      <td>176</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>826</td>\n      <td>1</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>2021-01-12</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>43432</td>\n      <td>86</td>\n      <td>181</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>827</td>\n      <td>1</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>2021-01-13</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>43785</td>\n      <td>353</td>\n      <td>201</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>829</td>\n      <td>2</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>2021-01-14</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>44068</td>\n      <td>283</td>\n      <td>228</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>832</td>\n      <td>3</td>\n      <td>2</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>14</th>\n      <td>2021-01-15</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>44156</td>\n      <td>88</td>\n      <td>192</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>835</td>\n      <td>3</td>\n      <td>2</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>15</th>\n      <td>2021-01-16</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>44621</td>\n      <td>465</td>\n      <td>245</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>835</td>\n      <td>0</td>\n      <td>2</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>16</th>\n      <td>2021-01-17</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>44767</td>\n      <td>146</td>\n      <td>235</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>836</td>\n      <td>1</td>\n      <td>2</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>17</th>\n      <td>2021-01-18</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>44775</td>\n      <td>8</td>\n      <td>205</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>837</td>\n      <td>1</td>\n      <td>2</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>18</th>\n      <td>2021-01-19</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>45208</td>\n      <td>433</td>\n      <td>254</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>839</td>\n      <td>2</td>\n      <td>2</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>19</th>\n      <td>2021-01-20</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>45429</td>\n      <td>221</td>\n      <td>235</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>840</td>\n      <td>1</td>\n      <td>2</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>20</th>\n      <td>2021-01-21</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>45729</td>\n      <td>300</td>\n      <td>238</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>844</td>\n      <td>4</td>\n      <td>2</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>21</th>\n      <td>2021-01-22</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>45987</td>\n      <td>258</td>\n      <td>262</td>\n      <td>1.627329</td>\n      <td>upward</td>\n      <td>846</td>\n      <td>2</td>\n      <td>2</td>\n      <td>0.5</td>\n      <td>downward</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>22</th>\n      <td>2021-01-23</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>46239</td>\n      <td>252</td>\n      <td>232</td>\n      <td>1.669065</td>\n      <td>upward</td>\n      <td>848</td>\n      <td>2</td>\n      <td>2</td>\n      <td>0.5</td>\n      <td>downward</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>23</th>\n      <td>2021-01-24</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>46429</td>\n      <td>190</td>\n      <td>238</td>\n      <td>1.535484</td>\n      <td>upward</td>\n      <td>850</td>\n      <td>2</td>\n      <td>2</td>\n      <td>0.5</td>\n      <td>downward</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>24</th>\n      <td>2021-01-25</td>\n      <td>Brazil</td>\n      <td>Acre</td>\n      <td>881935</td>\n      <td>46539</td>\n      <td>110</td>\n      <td>252</td>\n      <td>1.431818</td>\n      <td>upward</td>\n      <td>854</td>\n      <td>4</td>\n      <td>3</td>\n      <td>1.0</td>\n      <td>stable</td>\n      <td>2021-01</td>\n      <td>2021</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{}}]},{"cell_type":"markdown","source":"### 2.2.3 Carregamento\n","metadata":{}},{"cell_type":"markdown","source":"Com os dados manipulados, vamos persisti-lo em disco, fazer o seu download e carrega-lo no Google Data Studio.","metadata":{"execution":{"iopub.status.busy":"2022-10-04T22:24:40.310246Z","iopub.execute_input":"2022-10-04T22:24:40.310615Z","iopub.status.idle":"2022-10-04T22:24:40.324461Z","shell.execute_reply.started":"2022-10-04T22:24:40.310563Z","shell.execute_reply":"2022-10-04T22:24:40.321568Z"}}},{"cell_type":"code","source":"cases.to_csv('./covid-cases.csv', sep=',', index=False)","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:52.982666Z","iopub.execute_input":"2022-12-13T21:05:52.984247Z","iopub.status.idle":"2022-12-13T21:05:53.121256Z","shell.execute_reply.started":"2022-12-13T21:05:52.984213Z","shell.execute_reply":"2022-12-13T21:05:53.120139Z"},"trusted":true},"execution_count":22,"outputs":[]},{"cell_type":"markdown","source":"## 2.3 Vacinação","metadata":{}},{"cell_type":"markdown","source":"Vamos processar os dados de **vacinação** da universidade de Oxford.","metadata":{}},{"cell_type":"markdown","source":"### 2.3.1 Extração\n","metadata":{}},{"cell_type":"markdown","source":"Os dados estão compilados em um único arquivo.","metadata":{}},{"cell_type":"code","source":"vaccines = pd.read_csv('https://covid.ourworldindata.org/data/owid-covid-data.csv', sep=',', parse_dates=[3], infer_datetime_format=True)","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:53.122858Z","iopub.execute_input":"2022-12-13T21:05:53.123823Z","iopub.status.idle":"2022-12-13T21:05:55.270122Z","shell.execute_reply.started":"2022-12-13T21:05:53.123763Z","shell.execute_reply":"2022-12-13T21:05:55.269248Z"},"trusted":true},"execution_count":23,"outputs":[]},{"cell_type":"code","source":"vaccines.head()","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:55.274704Z","iopub.execute_input":"2022-12-13T21:05:55.275057Z","iopub.status.idle":"2022-12-13T21:05:55.30331Z","shell.execute_reply.started":"2022-12-13T21:05:55.275028Z","shell.execute_reply":"2022-12-13T21:05:55.302313Z"},"trusted":true},"execution_count":24,"outputs":[{"execution_count":24,"output_type":"execute_result","data":{"text/plain":"  iso_code continent     location       date  total_cases  new_cases  \\\n0      AFG      Asia  Afghanistan 2020-02-24          5.0        5.0   \n1      AFG      Asia  Afghanistan 2020-02-25          5.0        0.0   \n2      AFG      Asia  Afghanistan 2020-02-26          5.0        0.0   \n3      AFG      Asia  Afghanistan 2020-02-27          5.0        0.0   \n4      AFG      Asia  Afghanistan 2020-02-28          5.0        0.0   \n\n   new_cases_smoothed  total_deaths  new_deaths  new_deaths_smoothed  ...  \\\n0                 NaN           NaN         NaN                  NaN  ...   \n1                 NaN           NaN         NaN                  NaN  ...   \n2                 NaN           NaN         NaN                  NaN  ...   \n3                 NaN           NaN         NaN                  NaN  ...   \n4                 NaN           NaN         NaN                  NaN  ...   \n\n   male_smokers  handwashing_facilities  hospital_beds_per_thousand  \\\n0           NaN                  37.746                         0.5   \n1           NaN                  37.746                         0.5   \n2           NaN                  37.746                         0.5   \n3           NaN                  37.746                         0.5   \n4           NaN                  37.746                         0.5   \n\n   life_expectancy  human_development_index  population  \\\n0            64.83                    0.511  41128772.0   \n1            64.83                    0.511  41128772.0   \n2            64.83                    0.511  41128772.0   \n3            64.83                    0.511  41128772.0   \n4            64.83                    0.511  41128772.0   \n\n   excess_mortality_cumulative_absolute  excess_mortality_cumulative  \\\n0                                   NaN                          NaN   \n1                                   NaN                          NaN   \n2                                   NaN                          NaN   \n3                                   NaN                          NaN   \n4                                   NaN                          NaN   \n\n   excess_mortality  excess_mortality_cumulative_per_million  \n0               NaN                                      NaN  \n1               NaN                                      NaN  \n2               NaN                                      NaN  \n3               NaN                                      NaN  \n4               NaN                                      NaN  \n\n[5 rows x 67 columns]","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>iso_code</th>\n      <th>continent</th>\n      <th>location</th>\n      <th>date</th>\n      <th>total_cases</th>\n      <th>new_cases</th>\n      <th>new_cases_smoothed</th>\n      <th>total_deaths</th>\n      <th>new_deaths</th>\n      <th>new_deaths_smoothed</th>\n      <th>...</th>\n      <th>male_smokers</th>\n      <th>handwashing_facilities</th>\n      <th>hospital_beds_per_thousand</th>\n      <th>life_expectancy</th>\n      <th>human_development_index</th>\n      <th>population</th>\n      <th>excess_mortality_cumulative_absolute</th>\n      <th>excess_mortality_cumulative</th>\n      <th>excess_mortality</th>\n      <th>excess_mortality_cumulative_per_million</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>AFG</td>\n      <td>Asia</td>\n      <td>Afghanistan</td>\n      <td>2020-02-24</td>\n      <td>5.0</td>\n      <td>5.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>37.746</td>\n      <td>0.5</td>\n      <td>64.83</td>\n      <td>0.511</td>\n      <td>41128772.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>AFG</td>\n      <td>Asia</td>\n      <td>Afghanistan</td>\n      <td>2020-02-25</td>\n      <td>5.0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>37.746</td>\n      <td>0.5</td>\n      <td>64.83</td>\n      <td>0.511</td>\n      <td>41128772.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>AFG</td>\n      <td>Asia</td>\n      <td>Afghanistan</td>\n      <td>2020-02-26</td>\n      <td>5.0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>37.746</td>\n      <td>0.5</td>\n      <td>64.83</td>\n      <td>0.511</td>\n      <td>41128772.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>AFG</td>\n      <td>Asia</td>\n      <td>Afghanistan</td>\n      <td>2020-02-27</td>\n      <td>5.0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>37.746</td>\n      <td>0.5</td>\n      <td>64.83</td>\n      <td>0.511</td>\n      <td>41128772.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>AFG</td>\n      <td>Asia</td>\n      <td>Afghanistan</td>\n      <td>2020-02-28</td>\n      <td>5.0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>37.746</td>\n      <td>0.5</td>\n      <td>64.83</td>\n      <td>0.511</td>\n      <td>41128772.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 67 columns</p>\n</div>"},"metadata":{}}]},{"cell_type":"markdown","source":"Filtrando as colunas de interesse e linhas referentes ao Brasil.","metadata":{}},{"cell_type":"code","source":"vaccines = vaccines.query('location == \"Brazil\"').reset_index(drop=True)\nvaccines = vaccines[['location','population','total_vaccinations','people_vaccinated','people_fully_vaccinated','total_boosters','date']]","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:55.304554Z","iopub.execute_input":"2022-12-13T21:05:55.304864Z","iopub.status.idle":"2022-12-13T21:05:55.326702Z","shell.execute_reply.started":"2022-12-13T21:05:55.304825Z","shell.execute_reply":"2022-12-13T21:05:55.325824Z"},"trusted":true},"execution_count":25,"outputs":[]},{"cell_type":"markdown","source":"Vamos manipular os dados para o dashboard. O foco é garantir uma boa granularidade e qualidade de base de dados.","metadata":{}},{"cell_type":"markdown","source":"### 2.3.2 Transformação\n","metadata":{}},{"cell_type":"markdown","source":"Vamos manipular os dados para o dashboard. O foco é em garantir uma boa granularidade e qualidade da base de dados.","metadata":{}},{"cell_type":"code","source":"vaccines.head()","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:55.328046Z","iopub.execute_input":"2022-12-13T21:05:55.328348Z","iopub.status.idle":"2022-12-13T21:05:55.341863Z","shell.execute_reply.started":"2022-12-13T21:05:55.32832Z","shell.execute_reply":"2022-12-13T21:05:55.34079Z"},"trusted":true},"execution_count":26,"outputs":[{"execution_count":26,"output_type":"execute_result","data":{"text/plain":"  location   population  total_vaccinations  people_vaccinated  \\\n0   Brazil  215313504.0                 NaN                NaN   \n1   Brazil  215313504.0                 NaN                NaN   \n2   Brazil  215313504.0                 NaN                NaN   \n3   Brazil  215313504.0                 NaN                NaN   \n4   Brazil  215313504.0                 NaN                NaN   \n\n   people_fully_vaccinated  total_boosters       date  \n0                      NaN             NaN 2020-02-26  \n1                      NaN             NaN 2020-02-27  \n2                      NaN             NaN 2020-02-28  \n3                      NaN             NaN 2020-02-29  \n4                      NaN             NaN 2020-03-01  ","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>location</th>\n      <th>population</th>\n      <th>total_vaccinations</th>\n      <th>people_vaccinated</th>\n      <th>people_fully_vaccinated</th>\n      <th>total_boosters</th>\n      <th>date</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Brazil</td>\n      <td>215313504.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2020-02-26</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Brazil</td>\n      <td>215313504.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2020-02-27</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Brazil</td>\n      <td>215313504.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2020-02-28</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Brazil</td>\n      <td>215313504.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2020-02-29</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Brazil</td>\n      <td>215313504.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2020-03-01</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{}}]},{"cell_type":"code","source":"vaccines.shape","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:55.34323Z","iopub.execute_input":"2022-12-13T21:05:55.343632Z","iopub.status.idle":"2022-12-13T21:05:55.353058Z","shell.execute_reply.started":"2022-12-13T21:05:55.343603Z","shell.execute_reply":"2022-12-13T21:05:55.352145Z"},"trusted":true},"execution_count":27,"outputs":[{"execution_count":27,"output_type":"execute_result","data":{"text/plain":"(1021, 7)"},"metadata":{}}]},{"cell_type":"code","source":"vaccines.info()","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:55.354234Z","iopub.execute_input":"2022-12-13T21:05:55.355131Z","iopub.status.idle":"2022-12-13T21:05:55.371775Z","shell.execute_reply.started":"2022-12-13T21:05:55.355099Z","shell.execute_reply":"2022-12-13T21:05:55.369228Z"},"trusted":true},"execution_count":28,"outputs":[{"name":"stdout","text":"<class 'pandas.core.frame.DataFrame'>\nRangeIndex: 1021 entries, 0 to 1020\nData columns (total 7 columns):\n #   Column                   Non-Null Count  Dtype         \n---  ------                   --------------  -----         \n 0   location                 1021 non-null   object        \n 1   population               1021 non-null   float64       \n 2   total_vaccinations       621 non-null    float64       \n 3   people_vaccinated        617 non-null    float64       \n 4   people_fully_vaccinated  601 non-null    float64       \n 5   total_boosters           391 non-null    float64       \n 6   date                     1021 non-null   datetime64[ns]\ndtypes: datetime64[ns](1), float64(5), object(1)\nmemory usage: 56.0+ KB\n","output_type":"stream"}]},{"cell_type":"markdown","source":"Vamos começar tratando os dados faltantes, a estratégia será a de preencher os buracos com o valor anterior válido mais próximo.","metadata":{}},{"cell_type":"code","source":"vaccines = vaccines.fillna(method='ffill')","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:55.373538Z","iopub.execute_input":"2022-12-13T21:05:55.373956Z","iopub.status.idle":"2022-12-13T21:05:55.381444Z","shell.execute_reply.started":"2022-12-13T21:05:55.373926Z","shell.execute_reply":"2022-12-13T21:05:55.380059Z"},"trusted":true},"execution_count":29,"outputs":[]},{"cell_type":"markdown","source":"Vamos também filtrar a base de dados de acordo com a coluna `date` para garantir que ambas as bases de dados tratam do mesmo período de tempo.","metadata":{}},{"cell_type":"code","source":"vaccines = vaccines[(vaccines['date'] >= '2021-01-01') & (vaccines['date'] <= '2021-12-31')].reset_index(drop=True)","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:55.38257Z","iopub.execute_input":"2022-12-13T21:05:55.38351Z","iopub.status.idle":"2022-12-13T21:05:55.39358Z","shell.execute_reply.started":"2022-12-13T21:05:55.383478Z","shell.execute_reply":"2022-12-13T21:05:55.392467Z"},"trusted":true},"execution_count":30,"outputs":[]},{"cell_type":"markdown","source":"Alterando o nome das colunas","metadata":{"execution":{"iopub.status.busy":"2022-10-04T22:27:32.430785Z","iopub.execute_input":"2022-10-04T22:27:32.431999Z","iopub.status.idle":"2022-10-04T22:27:32.440791Z","shell.execute_reply.started":"2022-10-04T22:27:32.431953Z","shell.execute_reply":"2022-10-04T22:27:32.437986Z"}}},{"cell_type":"code","source":"vaccines = vaccines.rename(\n    columns={\n        'location': 'country',\n        'total_vaccinations': 'total',\n        'people_vaccinated': 'one_shot',\n        'people_fully_vaccinated': 'two_shots',\n        'total_boosters': 'three_shots'\n    }\n)","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:55.394933Z","iopub.execute_input":"2022-12-13T21:05:55.395373Z","iopub.status.idle":"2022-12-13T21:05:55.404833Z","shell.execute_reply.started":"2022-12-13T21:05:55.395343Z","shell.execute_reply":"2022-12-13T21:05:55.403928Z"},"trusted":true},"execution_count":31,"outputs":[]},{"cell_type":"markdown","source":"Computando novas colunas para enriquecer a base de dados.","metadata":{}},{"cell_type":"markdown","source":"**Chaves temporais**","metadata":{}},{"cell_type":"code","source":"vaccines['month'] = vaccines['date'].apply(lambda date: date.strftime('%Y-%m'))\nvaccines['year'] = vaccines['date'].apply(lambda date: date.strftime('%Y'))","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:55.40614Z","iopub.execute_input":"2022-12-13T21:05:55.406497Z","iopub.status.idle":"2022-12-13T21:05:55.42182Z","shell.execute_reply.started":"2022-12-13T21:05:55.406468Z","shell.execute_reply":"2022-12-13T21:05:55.420884Z"},"trusted":true},"execution_count":32,"outputs":[]},{"cell_type":"markdown","source":"**Dados relativos**","metadata":{}},{"cell_type":"code","source":"vaccines['one_shot_perc'] = round(vaccines['one_shot'] / vaccines['population'], 4)\nvaccines['two_shots_perc'] = round(vaccines['two_shots'] / vaccines['population'], 4)\nvaccines['three_shots_perc'] = round(vaccines['three_shots'] / vaccines['population'], 4)","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:55.423621Z","iopub.execute_input":"2022-12-13T21:05:55.423915Z","iopub.status.idle":"2022-12-13T21:05:55.431899Z","shell.execute_reply.started":"2022-12-13T21:05:55.423888Z","shell.execute_reply":"2022-12-13T21:05:55.431211Z"},"trusted":true},"execution_count":33,"outputs":[]},{"cell_type":"markdown","source":"**Garantir o tipo do dado é fundamental para consistência da base de dados. Vamos fazer o *type casting* das colunas.**","metadata":{}},{"cell_type":"code","source":"vaccines['population'] = vaccines['population'].astype('Int64')\nvaccines['total'] = vaccines['total'].astype('Int64')\nvaccines['one_shot'] = vaccines['one_shot'].astype('Int64')\nvaccines['two_shots'] = vaccines['two_shots'].astype('Int64')\nvaccines['three_shots'] = vaccines['three_shots'].astype('Int64')","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:55.433002Z","iopub.execute_input":"2022-12-13T21:05:55.43331Z","iopub.status.idle":"2022-12-13T21:05:55.447166Z","shell.execute_reply.started":"2022-12-13T21:05:55.433282Z","shell.execute_reply":"2022-12-13T21:05:55.446058Z"},"trusted":true},"execution_count":34,"outputs":[]},{"cell_type":"markdown","source":"Por fim, vamos reorganizar as colunas e conferir o resultado final.","metadata":{}},{"cell_type":"code","source":"vaccines = vaccines[['date', 'country', 'population', 'total', 'one_shot', 'one_shot_perc', 'two_shots', 'two_shots_perc', 'three_shots', 'three_shots_perc', 'month', 'year']]","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:55.448491Z","iopub.execute_input":"2022-12-13T21:05:55.448775Z","iopub.status.idle":"2022-12-13T21:05:55.462364Z","shell.execute_reply.started":"2022-12-13T21:05:55.448749Z","shell.execute_reply":"2022-12-13T21:05:55.461429Z"},"trusted":true},"execution_count":35,"outputs":[]},{"cell_type":"code","source":"vaccines.tail()","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:55.463449Z","iopub.execute_input":"2022-12-13T21:05:55.464183Z","iopub.status.idle":"2022-12-13T21:05:55.486469Z","shell.execute_reply.started":"2022-12-13T21:05:55.464149Z","shell.execute_reply":"2022-12-13T21:05:55.485328Z"},"trusted":true},"execution_count":36,"outputs":[{"execution_count":36,"output_type":"execute_result","data":{"text/plain":"          date country  population      total   one_shot  one_shot_perc  \\\n360 2021-12-27  Brazil   215313504  329011365  165952037         0.7707   \n361 2021-12-28  Brazil   215313504  329861730  166062249         0.7713   \n362 2021-12-29  Brazil   215313504  330718457  166143380         0.7716   \n363 2021-12-30  Brazil   215313504  331164041  166185628         0.7718   \n364 2021-12-31  Brazil   215313504  331273910  166195505         0.7719   \n\n     two_shots  two_shots_perc  three_shots  three_shots_perc    month  year  \n360  142764283          0.6631     25218893            0.1171  2021-12  2021  \n361  142965728          0.6640     25758909            0.1196  2021-12  2021  \n362  143282084          0.6655     26219623            0.1218  2021-12  2021  \n363  143398692          0.6660     26507937            0.1231  2021-12  2021  \n364  143436012          0.6662     26571077            0.1234  2021-12  2021  ","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>date</th>\n      <th>country</th>\n      <th>population</th>\n      <th>total</th>\n      <th>one_shot</th>\n      <th>one_shot_perc</th>\n      <th>two_shots</th>\n      <th>two_shots_perc</th>\n      <th>three_shots</th>\n      <th>three_shots_perc</th>\n      <th>month</th>\n      <th>year</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>360</th>\n      <td>2021-12-27</td>\n      <td>Brazil</td>\n      <td>215313504</td>\n      <td>329011365</td>\n      <td>165952037</td>\n      <td>0.7707</td>\n      <td>142764283</td>\n      <td>0.6631</td>\n      <td>25218893</td>\n      <td>0.1171</td>\n      <td>2021-12</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>361</th>\n      <td>2021-12-28</td>\n      <td>Brazil</td>\n      <td>215313504</td>\n      <td>329861730</td>\n      <td>166062249</td>\n      <td>0.7713</td>\n      <td>142965728</td>\n      <td>0.6640</td>\n      <td>25758909</td>\n      <td>0.1196</td>\n      <td>2021-12</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>362</th>\n      <td>2021-12-29</td>\n      <td>Brazil</td>\n      <td>215313504</td>\n      <td>330718457</td>\n      <td>166143380</td>\n      <td>0.7716</td>\n      <td>143282084</td>\n      <td>0.6655</td>\n      <td>26219623</td>\n      <td>0.1218</td>\n      <td>2021-12</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>363</th>\n      <td>2021-12-30</td>\n      <td>Brazil</td>\n      <td>215313504</td>\n      <td>331164041</td>\n      <td>166185628</td>\n      <td>0.7718</td>\n      <td>143398692</td>\n      <td>0.6660</td>\n      <td>26507937</td>\n      <td>0.1231</td>\n      <td>2021-12</td>\n      <td>2021</td>\n    </tr>\n    <tr>\n      <th>364</th>\n      <td>2021-12-31</td>\n      <td>Brazil</td>\n      <td>215313504</td>\n      <td>331273910</td>\n      <td>166195505</td>\n      <td>0.7719</td>\n      <td>143436012</td>\n      <td>0.6662</td>\n      <td>26571077</td>\n      <td>0.1234</td>\n      <td>2021-12</td>\n      <td>2021</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{}}]},{"cell_type":"markdown","source":"### 2.3.3 Carregamento\n","metadata":{}},{"cell_type":"markdown","source":"Com os dados manipulados, vamos persisti-lo em disco, fazer o seu download e carrega-lo no Google Data Studio.","metadata":{}},{"cell_type":"code","source":"vaccines.to_csv('./covid-vaccines.csv', sep=',', index=False)","metadata":{"execution":{"iopub.status.busy":"2022-12-13T21:05:55.487716Z","iopub.execute_input":"2022-12-13T21:05:55.488127Z","iopub.status.idle":"2022-12-13T21:05:55.498924Z","shell.execute_reply.started":"2022-12-13T21:05:55.488095Z","shell.execute_reply":"2022-12-13T21:05:55.498069Z"},"trusted":true},"execution_count":37,"outputs":[]},{"cell_type":"markdown","source":"# 3. Exploração Interativa de Dados","metadata":{}},{"cell_type":"markdown","source":"## 3.1 KPIs","metadata":{}},{"cell_type":"markdown","source":"O dashboard de dados contem os seguintes indicadores chaves de desempenho (*key performance indicator* ou KPI) consolidados:","metadata":{}},{"cell_type":"markdown","source":"1. Casos e mortes nas 24 horas;\n1. Média móvel (7 dias) de casos e mortes;\n1. Tendência de casos e mortes;\n1. Proporção de vacinados com 1ª, 2ª e 3ª doses.","metadata":{}},{"cell_type":"markdown","source":"## 3.2 EDA","metadata":{}},{"cell_type":"markdown","source":"O dashboard de dados contem os seguintes gráficos para a análise exploratória de dados (*exploratory data analysis*\nou EDA) interativa:","metadata":{}},{"cell_type":"markdown","source":"1. Distribuição do números de casos e mortes ao longo do tempo;\n1. Distribuição da média móvel (7 dias) do números de casos e mortes ao longo do tempo;\n1. Distribuição geográfica dos casos por estado por dia.","metadata":{}}]}