covid-19-data-prep.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "448a724c",
   "metadata": {
    "papermill": {
     "duration": 0.004494,
     "end_time": "2024-03-19T18:52:01.193870",
     "exception": false,
     "start_time": "2024-03-19T18:52:01.189376",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "I wanted to make a Dashboard for the COVID-19 epidemic because I was mainly interesed in the spread of the disease and its lethality in different countries.\n",
    "\n",
    "Python was used to clean the data and to query smaller datasets for visualizations in Tableau.\n",
    "The data came from Our World in Data and it was a fairly clean dataset and needed only a little bit of cleaning, exploration and preparation.\n",
    "You can find the Tableau Dashboard here: https://public.tableau.com/app/profile/mario.boha./vizzes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4801a623",
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5",
    "papermill": {
     "duration": 0.003637,
     "end_time": "2024-03-19T18:52:01.201558",
     "exception": false,
     "start_time": "2024-03-19T18:52:01.197921",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "30650150",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-19T18:52:01.211134Z",
     "iopub.status.busy": "2024-03-19T18:52:01.210767Z",
     "iopub.status.idle": "2024-03-19T18:52:02.220903Z",
     "shell.execute_reply": "2024-03-19T18:52:02.219604Z"
    },
    "papermill": {
     "duration": 1.017985,
     "end_time": "2024-03-19T18:52:02.223356",
     "exception": false,
     "start_time": "2024-03-19T18:52:01.205371",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80090592",
   "metadata": {
    "papermill": {
     "duration": 0.004369,
     "end_time": "2024-03-19T18:52:02.232559",
     "exception": false,
     "start_time": "2024-03-19T18:52:02.228190",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Importing the dataset and data cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ce6a7ea0",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-19T18:52:02.242712Z",
     "iopub.status.busy": "2024-03-19T18:52:02.242103Z",
     "iopub.status.idle": "2024-03-19T18:52:04.795479Z",
     "shell.execute_reply": "2024-03-19T18:52:04.794076Z"
    },
    "papermill": {
     "duration": 2.561614,
     "end_time": "2024-03-19T18:52:04.798222",
     "exception": false,
     "start_time": "2024-03-19T18:52:02.236608",
     "status": "completed"
    },
    "tags": []
   },
   "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>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-01-05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</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-01-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</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-01-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</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-01-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</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-01-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</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>"
      ],
      "text/plain": [
       "  iso_code continent     location        date  total_cases  new_cases  \\\n",
       "0      AFG      Asia  Afghanistan  2020-01-05          NaN        0.0   \n",
       "1      AFG      Asia  Afghanistan  2020-01-06          NaN        0.0   \n",
       "2      AFG      Asia  Afghanistan  2020-01-07          NaN        0.0   \n",
       "3      AFG      Asia  Afghanistan  2020-01-08          NaN        0.0   \n",
       "4      AFG      Asia  Afghanistan  2020-01-09          NaN        0.0   \n",
       "\n",
       "   new_cases_smoothed  total_deaths  new_deaths  new_deaths_smoothed  ...  \\\n",
       "0                 NaN           NaN         0.0                  NaN  ...   \n",
       "1                 NaN           NaN         0.0                  NaN  ...   \n",
       "2                 NaN           NaN         0.0                  NaN  ...   \n",
       "3                 NaN           NaN         0.0                  NaN  ...   \n",
       "4                 NaN           NaN         0.0                  NaN  ...   \n",
       "\n",
       "   male_smokers  handwashing_facilities  hospital_beds_per_thousand  \\\n",
       "0           NaN                  37.746                         0.5   \n",
       "1           NaN                  37.746                         0.5   \n",
       "2           NaN                  37.746                         0.5   \n",
       "3           NaN                  37.746                         0.5   \n",
       "4           NaN                  37.746                         0.5   \n",
       "\n",
       "   life_expectancy  human_development_index  population  \\\n",
       "0            64.83                    0.511  41128772.0   \n",
       "1            64.83                    0.511  41128772.0   \n",
       "2            64.83                    0.511  41128772.0   \n",
       "3            64.83                    0.511  41128772.0   \n",
       "4            64.83                    0.511  41128772.0   \n",
       "\n",
       "   excess_mortality_cumulative_absolute  excess_mortality_cumulative  \\\n",
       "0                                   NaN                          NaN   \n",
       "1                                   NaN                          NaN   \n",
       "2                                   NaN                          NaN   \n",
       "3                                   NaN                          NaN   \n",
       "4                                   NaN                          NaN   \n",
       "\n",
       "   excess_mortality  excess_mortality_cumulative_per_million  \n",
       "0               NaN                                      NaN  \n",
       "1               NaN                                      NaN  \n",
       "2               NaN                                      NaN  \n",
       "3               NaN                                      NaN  \n",
       "4               NaN                                      NaN  \n",
       "\n",
       "[5 rows x 67 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('/kaggle/input/covid-19-our-world-in-data-till-2024/owid-covid-data.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "83a6f67e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-19T18:52:04.808709Z",
     "iopub.status.busy": "2024-03-19T18:52:04.808367Z",
     "iopub.status.idle": "2024-03-19T18:52:04.815788Z",
     "shell.execute_reply": "2024-03-19T18:52:04.814760Z"
    },
    "papermill": {
     "duration": 0.014864,
     "end_time": "2024-03-19T18:52:04.817658",
     "exception": false,
     "start_time": "2024-03-19T18:52:04.802794",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['iso_code', 'continent', 'location', 'date', 'total_cases', 'new_cases',\n",
       "       'new_cases_smoothed', 'total_deaths', 'new_deaths',\n",
       "       'new_deaths_smoothed', 'total_cases_per_million',\n",
       "       'new_cases_per_million', 'new_cases_smoothed_per_million',\n",
       "       'total_deaths_per_million', 'new_deaths_per_million',\n",
       "       'new_deaths_smoothed_per_million', 'reproduction_rate', 'icu_patients',\n",
       "       'icu_patients_per_million', 'hosp_patients',\n",
       "       'hosp_patients_per_million', 'weekly_icu_admissions',\n",
       "       'weekly_icu_admissions_per_million', 'weekly_hosp_admissions',\n",
       "       'weekly_hosp_admissions_per_million', 'total_tests', 'new_tests',\n",
       "       'total_tests_per_thousand', 'new_tests_per_thousand',\n",
       "       'new_tests_smoothed', 'new_tests_smoothed_per_thousand',\n",
       "       'positive_rate', 'tests_per_case', 'tests_units', 'total_vaccinations',\n",
       "       'people_vaccinated', 'people_fully_vaccinated', 'total_boosters',\n",
       "       'new_vaccinations', 'new_vaccinations_smoothed',\n",
       "       'total_vaccinations_per_hundred', 'people_vaccinated_per_hundred',\n",
       "       'people_fully_vaccinated_per_hundred', 'total_boosters_per_hundred',\n",
       "       'new_vaccinations_smoothed_per_million',\n",
       "       'new_people_vaccinated_smoothed',\n",
       "       'new_people_vaccinated_smoothed_per_hundred', 'stringency_index',\n",
       "       'population_density', 'median_age', 'aged_65_older', 'aged_70_older',\n",
       "       'gdp_per_capita', 'extreme_poverty', 'cardiovasc_death_rate',\n",
       "       'diabetes_prevalence', 'female_smokers', 'male_smokers',\n",
       "       'handwashing_facilities', 'hospital_beds_per_thousand',\n",
       "       'life_expectancy', 'human_development_index', 'population',\n",
       "       'excess_mortality_cumulative_absolute', 'excess_mortality_cumulative',\n",
       "       'excess_mortality', 'excess_mortality_cumulative_per_million'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4c5ecb45",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-19T18:52:04.828230Z",
     "iopub.status.busy": "2024-03-19T18:52:04.827753Z",
     "iopub.status.idle": "2024-03-19T18:52:04.848984Z",
     "shell.execute_reply": "2024-03-19T18:52:04.847912Z"
    },
    "papermill": {
     "duration": 0.029488,
     "end_time": "2024-03-19T18:52:04.851547",
     "exception": false,
     "start_time": "2024-03-19T18:52:04.822059",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#There are a lot of columns that are not necesarry so we choose a few \n",
    "columns = ['continent', 'location', 'date', \n",
    "           'total_cases', 'new_cases','total_deaths', 'new_deaths', 'new_cases_smoothed', 'new_deaths_smoothed',\n",
    "           'total_vaccinations', 'people_vaccinated', 'people_fully_vaccinated', 'new_vaccinations', 'new_vaccinations_smoothed',\n",
    "           'population']\n",
    "df = df[columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "eade46b3",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-19T18:52:04.861764Z",
     "iopub.status.busy": "2024-03-19T18:52:04.861451Z",
     "iopub.status.idle": "2024-03-19T18:52:04.928892Z",
     "shell.execute_reply": "2024-03-19T18:52:04.926778Z"
    },
    "papermill": {
     "duration": 0.075322,
     "end_time": "2024-03-19T18:52:04.931464",
     "exception": false,
     "start_time": "2024-03-19T18:52:04.856142",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 380519 entries, 0 to 380518\n",
      "Data columns (total 15 columns):\n",
      " #   Column                     Non-Null Count   Dtype  \n",
      "---  ------                     --------------   -----  \n",
      " 0   continent                  362259 non-null  object \n",
      " 1   location                   380519 non-null  object \n",
      " 2   date                       380519 non-null  object \n",
      " 3   total_cases                341501 non-null  float64\n",
      " 4   new_cases                  369504 non-null  float64\n",
      " 5   total_deaths               319445 non-null  float64\n",
      " 6   new_deaths                 369785 non-null  float64\n",
      " 7   new_cases_smoothed         368274 non-null  float64\n",
      " 8   new_deaths_smoothed        368555 non-null  float64\n",
      " 9   total_vaccinations         82950 non-null   float64\n",
      " 10  people_vaccinated          78867 non-null   float64\n",
      " 11  people_fully_vaccinated    75735 non-null   float64\n",
      " 12  new_vaccinations           68722 non-null   float64\n",
      " 13  new_vaccinations_smoothed  189527 non-null  float64\n",
      " 14  population                 380519 non-null  float64\n",
      "dtypes: float64(12), object(3)\n",
      "memory usage: 43.5+ MB\n"
     ]
    }
   ],
   "source": [
    "# There are many NAN values referring to the values at the begining of the pandemic. Those will be changed into 0. \n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "fb392217",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-19T18:52:04.942239Z",
     "iopub.status.busy": "2024-03-19T18:52:04.941863Z",
     "iopub.status.idle": "2024-03-19T18:52:04.982865Z",
     "shell.execute_reply": "2024-03-19T18:52:04.980662Z"
    },
    "papermill": {
     "duration": 0.04893,
     "end_time": "2024-03-19T18:52:04.985275",
     "exception": false,
     "start_time": "2024-03-19T18:52:04.936345",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Asia' nan 'Europe' 'Africa' 'Oceania' 'North America' 'South America']\n",
      "['Afghanistan' 'Africa' 'Albania' 'Algeria' 'American Samoa' 'Andorra'\n",
      " 'Angola' 'Anguilla' 'Antigua and Barbuda' 'Argentina' 'Armenia' 'Aruba'\n",
      " 'Asia' 'Australia' 'Austria' 'Azerbaijan' 'Bahamas' 'Bahrain'\n",
      " 'Bangladesh' 'Barbados' 'Belarus' 'Belgium' 'Belize' 'Benin' 'Bermuda'\n",
      " 'Bhutan' 'Bolivia' 'Bonaire Sint Eustatius and Saba'\n",
      " 'Bosnia and Herzegovina' 'Botswana' 'Brazil' 'British Virgin Islands'\n",
      " 'Brunei' 'Bulgaria' 'Burkina Faso' 'Burundi' 'Cambodia' 'Cameroon'\n",
      " 'Canada' 'Cape Verde' 'Cayman Islands' 'Central African Republic' 'Chad'\n",
      " 'Chile' 'China' 'Colombia' 'Comoros' 'Congo' 'Cook Islands' 'Costa Rica'\n",
      " \"Cote d'Ivoire\" 'Croatia' 'Cuba' 'Curacao' 'Cyprus' 'Czechia'\n",
      " 'Democratic Republic of Congo' 'Denmark' 'Djibouti' 'Dominica'\n",
      " 'Dominican Republic' 'Ecuador' 'Egypt' 'El Salvador' 'England'\n",
      " 'Equatorial Guinea' 'Eritrea' 'Estonia' 'Eswatini' 'Ethiopia' 'Europe'\n",
      " 'European Union' 'Faeroe Islands' 'Falkland Islands' 'Fiji' 'Finland'\n",
      " 'France' 'French Guiana' 'French Polynesia' 'Gabon' 'Gambia' 'Georgia'\n",
      " 'Germany' 'Ghana' 'Gibraltar' 'Greece' 'Greenland' 'Grenada' 'Guadeloupe'\n",
      " 'Guam' 'Guatemala' 'Guernsey' 'Guinea' 'Guinea-Bissau' 'Guyana' 'Haiti'\n",
      " 'High income' 'Honduras' 'Hong Kong' 'Hungary' 'Iceland' 'India'\n",
      " 'Indonesia' 'Iran' 'Iraq' 'Ireland' 'Isle of Man' 'Israel' 'Italy'\n",
      " 'Jamaica' 'Japan' 'Jersey' 'Jordan' 'Kazakhstan' 'Kenya' 'Kiribati'\n",
      " 'Kosovo' 'Kuwait' 'Kyrgyzstan' 'Laos' 'Latvia' 'Lebanon' 'Lesotho'\n",
      " 'Liberia' 'Libya' 'Liechtenstein' 'Lithuania' 'Low income'\n",
      " 'Lower middle income' 'Luxembourg' 'Macao' 'Madagascar' 'Malawi'\n",
      " 'Malaysia' 'Maldives' 'Mali' 'Malta' 'Marshall Islands' 'Martinique'\n",
      " 'Mauritania' 'Mauritius' 'Mayotte' 'Mexico' 'Micronesia (country)'\n",
      " 'Moldova' 'Monaco' 'Mongolia' 'Montenegro' 'Montserrat' 'Morocco'\n",
      " 'Mozambique' 'Myanmar' 'Namibia' 'Nauru' 'Nepal' 'Netherlands'\n",
      " 'New Caledonia' 'New Zealand' 'Nicaragua' 'Niger' 'Nigeria' 'Niue'\n",
      " 'North America' 'North Korea' 'North Macedonia' 'Northern Cyprus'\n",
      " 'Northern Ireland' 'Northern Mariana Islands' 'Norway' 'Oceania' 'Oman'\n",
      " 'Pakistan' 'Palau' 'Palestine' 'Panama' 'Papua New Guinea' 'Paraguay'\n",
      " 'Peru' 'Philippines' 'Pitcairn' 'Poland' 'Portugal' 'Puerto Rico' 'Qatar'\n",
      " 'Reunion' 'Romania' 'Russia' 'Rwanda' 'Saint Barthelemy' 'Saint Helena'\n",
      " 'Saint Kitts and Nevis' 'Saint Lucia' 'Saint Martin (French part)'\n",
      " 'Saint Pierre and Miquelon' 'Saint Vincent and the Grenadines' 'Samoa'\n",
      " 'San Marino' 'Sao Tome and Principe' 'Saudi Arabia' 'Scotland' 'Senegal'\n",
      " 'Serbia' 'Seychelles' 'Sierra Leone' 'Singapore'\n",
      " 'Sint Maarten (Dutch part)' 'Slovakia' 'Slovenia' 'Solomon Islands'\n",
      " 'Somalia' 'South Africa' 'South America' 'South Korea' 'South Sudan'\n",
      " 'Spain' 'Sri Lanka' 'Sudan' 'Suriname' 'Sweden' 'Switzerland' 'Syria'\n",
      " 'Taiwan' 'Tajikistan' 'Tanzania' 'Thailand' 'Timor' 'Togo' 'Tokelau'\n",
      " 'Tonga' 'Trinidad and Tobago' 'Tunisia' 'Turkey' 'Turkmenistan'\n",
      " 'Turks and Caicos Islands' 'Tuvalu' 'Uganda' 'Ukraine'\n",
      " 'United Arab Emirates' 'United Kingdom' 'United States'\n",
      " 'United States Virgin Islands' 'Upper middle income' 'Uruguay'\n",
      " 'Uzbekistan' 'Vanuatu' 'Vatican' 'Venezuela' 'Vietnam' 'Wales'\n",
      " 'Wallis and Futuna' 'Western Sahara' 'World' 'Yemen' 'Zambia' 'Zimbabwe']\n"
     ]
    }
   ],
   "source": [
    "# However, the NAN values in the 'continent' colum are because there are referring to categories in the 'location' column that are \n",
    "# grouped into broader categories like: World, Europe, European Union etc.\n",
    "\n",
    "print(df['continent'].unique())\n",
    "print(df['location'].unique())\n",
    "\n",
    "#In the future data prep for visualization we will take into account these categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "0491b681",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-19T18:52:04.996014Z",
     "iopub.status.busy": "2024-03-19T18:52:04.995679Z",
     "iopub.status.idle": "2024-03-19T18:52:05.021074Z",
     "shell.execute_reply": "2024-03-19T18:52:05.019011Z"
    },
    "papermill": {
     "duration": 0.0332,
     "end_time": "2024-03-19T18:52:05.023345",
     "exception": false,
     "start_time": "2024-03-19T18:52:04.990145",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "columns_to_fill = ['total_cases', 'new_cases','total_deaths', 'new_deaths', 'new_cases_smoothed', 'new_deaths_smoothed',\n",
    "           'total_vaccinations', 'people_vaccinated', 'people_fully_vaccinated', 'new_vaccinations', 'new_vaccinations_smoothed',\n",
    "           'population']\n",
    "\n",
    "df[columns_to_fill] = df[columns_to_fill].fillna(0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2e2e42e1",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-19T18:52:05.034519Z",
     "iopub.status.busy": "2024-03-19T18:52:05.034176Z",
     "iopub.status.idle": "2024-03-19T18:52:05.084066Z",
     "shell.execute_reply": "2024-03-19T18:52:05.083210Z"
    },
    "papermill": {
     "duration": 0.058138,
     "end_time": "2024-03-19T18:52:05.086378",
     "exception": false,
     "start_time": "2024-03-19T18:52:05.028240",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 380519 entries, 0 to 380518\n",
      "Data columns (total 15 columns):\n",
      " #   Column                     Non-Null Count   Dtype  \n",
      "---  ------                     --------------   -----  \n",
      " 0   continent                  362259 non-null  object \n",
      " 1   location                   380519 non-null  object \n",
      " 2   date                       380519 non-null  object \n",
      " 3   total_cases                380519 non-null  float64\n",
      " 4   new_cases                  380519 non-null  float64\n",
      " 5   total_deaths               380519 non-null  float64\n",
      " 6   new_deaths                 380519 non-null  float64\n",
      " 7   new_cases_smoothed         380519 non-null  float64\n",
      " 8   new_deaths_smoothed        380519 non-null  float64\n",
      " 9   total_vaccinations         380519 non-null  float64\n",
      " 10  people_vaccinated          380519 non-null  float64\n",
      " 11  people_fully_vaccinated    380519 non-null  float64\n",
      " 12  new_vaccinations           380519 non-null  float64\n",
      " 13  new_vaccinations_smoothed  380519 non-null  float64\n",
      " 14  population                 380519 non-null  float64\n",
      "dtypes: float64(12), object(3)\n",
      "memory usage: 43.5+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4faa19c1",
   "metadata": {
    "papermill": {
     "duration": 0.004236,
     "end_time": "2024-03-19T18:52:05.095424",
     "exception": false,
     "start_time": "2024-03-19T18:52:05.091188",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Preparing the datasets for Tableau"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "dbd070fc",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-19T18:52:05.107351Z",
     "iopub.status.busy": "2024-03-19T18:52:05.106618Z",
     "iopub.status.idle": "2024-03-19T18:52:05.144934Z",
     "shell.execute_reply": "2024-03-19T18:52:05.144048Z"
    },
    "papermill": {
     "duration": 0.04645,
     "end_time": "2024-03-19T18:52:05.146851",
     "exception": false,
     "start_time": "2024-03-19T18:52:05.100401",
     "status": "completed"
    },
    "tags": []
   },
   "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>total_world_cases</th>\n",
       "      <th>total_world_deaths</th>\n",
       "      <th>total_world_death_perc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>774767539.0</td>\n",
       "      <td>7037370.0</td>\n",
       "      <td>0.90832</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   total_world_cases  total_world_deaths  total_world_death_perc\n",
       "1        774767539.0           7037370.0                 0.90832"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "world = df[df['location'] == 'World']\n",
    "data1= {'total_world_cases' : world['new_cases'].sum(),\n",
    "     'total_world_deaths' : world['new_deaths'].sum(),\n",
    "     'total_world_death_perc' : (world['new_deaths'].sum())/(world['new_cases'].sum())*100}\n",
    "\n",
    "df1 = pd.DataFrame(data1, index=[1])\n",
    "df1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1fd91775",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-19T18:52:05.159126Z",
     "iopub.status.busy": "2024-03-19T18:52:05.158155Z",
     "iopub.status.idle": "2024-03-19T18:52:05.298128Z",
     "shell.execute_reply": "2024-03-19T18:52:05.296779Z"
    },
    "papermill": {
     "duration": 0.148063,
     "end_time": "2024-03-19T18:52:05.300063",
     "exception": false,
     "start_time": "2024-03-19T18:52:05.152000",
     "status": "completed"
    },
    "tags": []
   },
   "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>continets_total_cases</th>\n",
       "      <th>continets_total_deaths</th>\n",
       "      <th>remainder</th>\n",
       "      <th>lethality</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>continent</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Africa</th>\n",
       "      <td>13140355.0</td>\n",
       "      <td>259089.0</td>\n",
       "      <td>12881266.0</td>\n",
       "      <td>1.971705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Asia</th>\n",
       "      <td>301411932.0</td>\n",
       "      <td>1636732.0</td>\n",
       "      <td>299775200.0</td>\n",
       "      <td>0.543022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Europe</th>\n",
       "      <td>252250030.0</td>\n",
       "      <td>2098464.0</td>\n",
       "      <td>250151566.0</td>\n",
       "      <td>0.831898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North America</th>\n",
       "      <td>124524988.0</td>\n",
       "      <td>1654898.0</td>\n",
       "      <td>122870090.0</td>\n",
       "      <td>1.328969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oceania</th>\n",
       "      <td>14766548.0</td>\n",
       "      <td>32087.0</td>\n",
       "      <td>14734461.0</td>\n",
       "      <td>0.217295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South America</th>\n",
       "      <td>68672936.0</td>\n",
       "      <td>1356087.0</td>\n",
       "      <td>67316849.0</td>\n",
       "      <td>1.974704</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               continets_total_cases  continets_total_deaths    remainder  \\\n",
       "continent                                                                   \n",
       "Africa                    13140355.0                259089.0   12881266.0   \n",
       "Asia                     301411932.0               1636732.0  299775200.0   \n",
       "Europe                   252250030.0               2098464.0  250151566.0   \n",
       "North America            124524988.0               1654898.0  122870090.0   \n",
       "Oceania                   14766548.0                 32087.0   14734461.0   \n",
       "South America             68672936.0               1356087.0   67316849.0   \n",
       "\n",
       "               lethality  \n",
       "continent                 \n",
       "Africa          1.971705  \n",
       "Asia            0.543022  \n",
       "Europe          0.831898  \n",
       "North America   1.328969  \n",
       "Oceania         0.217295  \n",
       "South America   1.974704  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "continents = df.dropna()\n",
    "continets_total_cases = continents.groupby('continent')['new_cases'].sum()\n",
    "continets_total_deaths = continents.groupby('continent')['new_deaths'].sum()\n",
    "remainder= continets_total_cases - continets_total_deaths\n",
    "data2 = {'continets_total_cases' : continets_total_cases,\n",
    "        'continets_total_deaths' : continets_total_deaths,\n",
    "        'remainder' : remainder,\n",
    "        'lethality': (continets_total_deaths/continets_total_cases)*100}\n",
    "df2 = pd.DataFrame(data2, index=None)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "68bbb5f5",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-19T18:52:05.313641Z",
     "iopub.status.busy": "2024-03-19T18:52:05.311647Z",
     "iopub.status.idle": "2024-03-19T18:52:05.414579Z",
     "shell.execute_reply": "2024-03-19T18:52:05.413284Z"
    },
    "papermill": {
     "duration": 0.111478,
     "end_time": "2024-03-19T18:52:05.416776",
     "exception": false,
     "start_time": "2024-03-19T18:52:05.305298",
     "status": "completed"
    },
    "tags": []
   },
   "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>population</th>\n",
       "      <th>total cases</th>\n",
       "      <th>infected population</th>\n",
       "      <th>total deaths</th>\n",
       "      <th>lethality</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>location</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Afghanistan</th>\n",
       "      <td>41128772.0</td>\n",
       "      <td>231990.0</td>\n",
       "      <td>0.564058</td>\n",
       "      <td>7982.0</td>\n",
       "      <td>3.440666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Albania</th>\n",
       "      <td>2842318.0</td>\n",
       "      <td>334863.0</td>\n",
       "      <td>11.781335</td>\n",
       "      <td>3605.0</td>\n",
       "      <td>1.076560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Algeria</th>\n",
       "      <td>44903228.0</td>\n",
       "      <td>272010.0</td>\n",
       "      <td>0.605769</td>\n",
       "      <td>6881.0</td>\n",
       "      <td>2.529686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>American Samoa</th>\n",
       "      <td>44295.0</td>\n",
       "      <td>8359.0</td>\n",
       "      <td>18.871204</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.406747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andorra</th>\n",
       "      <td>79843.0</td>\n",
       "      <td>48015.0</td>\n",
       "      <td>60.136768</td>\n",
       "      <td>159.0</td>\n",
       "      <td>0.331147</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                population  total cases  infected population  total deaths  \\\n",
       "location                                                                     \n",
       "Afghanistan     41128772.0     231990.0             0.564058        7982.0   \n",
       "Albania          2842318.0     334863.0            11.781335        3605.0   \n",
       "Algeria         44903228.0     272010.0             0.605769        6881.0   \n",
       "American Samoa     44295.0       8359.0            18.871204          34.0   \n",
       "Andorra            79843.0      48015.0            60.136768         159.0   \n",
       "\n",
       "                lethality  \n",
       "location                   \n",
       "Afghanistan      3.440666  \n",
       "Albania          1.076560  \n",
       "Algeria          2.529686  \n",
       "American Samoa   0.406747  \n",
       "Andorra          0.331147  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states_total_cases = continents.groupby('location')['new_cases'].sum()\n",
    "population = continents.groupby('location')['population'].max()\n",
    "population_infected = (states_total_cases/population)*100\n",
    "states_total_deaths = continents.groupby('location')['new_deaths'].sum()\n",
    "data3 = {'population' : population,\n",
    "        'total cases' : states_total_cases,\n",
    "        'infected population' : population_infected,\n",
    "        'total deaths' : states_total_deaths,\n",
    "         'lethality' : (states_total_deaths/states_total_cases)*100\n",
    "        }\n",
    "df3 = pd.DataFrame(data3)\n",
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a20b3722",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-19T18:52:05.429306Z",
     "iopub.status.busy": "2024-03-19T18:52:05.428960Z",
     "iopub.status.idle": "2024-03-19T18:52:06.045139Z",
     "shell.execute_reply": "2024-03-19T18:52:06.043909Z"
    },
    "papermill": {
     "duration": 0.625545,
     "end_time": "2024-03-19T18:52:06.047938",
     "exception": false,
     "start_time": "2024-03-19T18:52:05.422393",
     "status": "completed"
    },
    "tags": []
   },
   "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>location</th>\n",
       "      <th>date</th>\n",
       "      <th>total cases</th>\n",
       "      <th>new_cases_smoothed</th>\n",
       "      <th>total_deaths</th>\n",
       "      <th>new_deaths_smoothed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      location        date  total cases  new_cases_smoothed  total_deaths  \\\n",
       "0  Afghanistan  2020-01-05          0.0                 0.0           0.0   \n",
       "1  Afghanistan  2020-01-06          0.0                 0.0           0.0   \n",
       "2  Afghanistan  2020-01-07          0.0                 0.0           0.0   \n",
       "3  Afghanistan  2020-01-08          0.0                 0.0           0.0   \n",
       "4  Afghanistan  2020-01-09          0.0                 0.0           0.0   \n",
       "\n",
       "   new_deaths_smoothed  \n",
       "0                  0.0  \n",
       "1                  0.0  \n",
       "2                  0.0  \n",
       "3                  0.0  \n",
       "4                  0.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_cases = continents.groupby(['location', 'date'])['total_cases'].max()\n",
    "new_cases_smoothed = continents.groupby(['location', 'date'])['new_cases_smoothed'].max()\n",
    "total_deaths = continents.groupby(['location', 'date'])['total_deaths'].max()\n",
    "new_deaths_smoothed = continents.groupby(['location', 'date'])['new_deaths_smoothed'].max()\n",
    "\n",
    "data4 = {'total cases' : total_cases,\n",
    "         'new_cases_smoothed' : new_cases_smoothed,\n",
    "         'total_deaths': total_deaths,\n",
    "         'new_deaths_smoothed' : new_deaths_smoothed,\n",
    "         }\n",
    "df4 = pd.DataFrame(data4)\n",
    "df4.reset_index(drop=False, inplace=True)\n",
    "df4.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb201dbb",
   "metadata": {
    "papermill": {
     "duration": 0.005006,
     "end_time": "2024-03-19T18:52:06.059388",
     "exception": false,
     "start_time": "2024-03-19T18:52:06.054382",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "These four tables were later used to make a Dashboard in Tableau"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "751a899c",
   "metadata": {
    "papermill": {
     "duration": 0.004891,
     "end_time": "2024-03-19T18:52:06.069367",
     "exception": false,
     "start_time": "2024-03-19T18:52:06.064476",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kaggle": {
   "accelerator": "none",
   "dataSources": [
    {
     "datasetId": 4597513,
     "sourceId": 7842008,
     "sourceType": "datasetVersion"
    }
   ],
   "dockerImageVersionId": 30664,
   "isGpuEnabled": false,
   "isInternetEnabled": true,
   "language": "python",
   "sourceType": "notebook"
  },
  "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.10.13"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 8.027806,
   "end_time": "2024-03-19T18:52:06.595365",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2024-03-19T18:51:58.567559",
   "version": "2.5.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}