COVID-19 Data Cleaning .ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Coronavirus(COVID-19) Data Cleaning(Worldwide) & Bangladesh\n",
    "- Confirmed Cases \n",
    "- Deaths Cases \n",
    "- Recovered Cases \n",
    "![Coronavirus](./img/corona.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Library Import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "from datetime import datetime, timedelta\n",
    "import matplotlib as plt \n",
    "import seaborn as sns "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring Raw Data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Loading cumulative raw data \n",
    "raw_confirmed = pd.read_csv(\"https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv\")\n",
    "raw_recovered   = pd.read_csv(\"https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_recovered_global.csv\") \n",
    "raw_deaths = pd.read_csv(\"https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The Shape of Confirmed is: (262, 79)\n",
      "The Shape of Recovered is: (248, 79)\n",
      "The Shape of Deaths is: (262, 79)\n"
     ]
    }
   ],
   "source": [
    "# Shape of cumulative data\n",
    "print(f\"The Shape of Confirmed is: {raw_confirmed.shape}\") \n",
    "print(f\"The Shape of Recovered is: {raw_recovered.shape}\")\n",
    "print(f\"The Shape of Deaths is: {raw_deaths.shape}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Province/State</th>\n",
       "      <th>Country/Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>1/22/20</th>\n",
       "      <th>1/23/20</th>\n",
       "      <th>1/24/20</th>\n",
       "      <th>1/25/20</th>\n",
       "      <th>1/26/20</th>\n",
       "      <th>1/27/20</th>\n",
       "      <th>...</th>\n",
       "      <th>3/27/20</th>\n",
       "      <th>3/28/20</th>\n",
       "      <th>3/29/20</th>\n",
       "      <th>3/30/20</th>\n",
       "      <th>3/31/20</th>\n",
       "      <th>4/1/20</th>\n",
       "      <th>4/2/20</th>\n",
       "      <th>4/3/20</th>\n",
       "      <th>4/4/20</th>\n",
       "      <th>4/5/20</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33.0000</td>\n",
       "      <td>65.0000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>110</td>\n",
       "      <td>110</td>\n",
       "      <td>120</td>\n",
       "      <td>170</td>\n",
       "      <td>174</td>\n",
       "      <td>237</td>\n",
       "      <td>273</td>\n",
       "      <td>281</td>\n",
       "      <td>299</td>\n",
       "      <td>349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>41.1533</td>\n",
       "      <td>20.1683</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>186</td>\n",
       "      <td>197</td>\n",
       "      <td>212</td>\n",
       "      <td>223</td>\n",
       "      <td>243</td>\n",
       "      <td>259</td>\n",
       "      <td>277</td>\n",
       "      <td>304</td>\n",
       "      <td>333</td>\n",
       "      <td>361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>28.0339</td>\n",
       "      <td>1.6596</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>409</td>\n",
       "      <td>454</td>\n",
       "      <td>511</td>\n",
       "      <td>584</td>\n",
       "      <td>716</td>\n",
       "      <td>847</td>\n",
       "      <td>986</td>\n",
       "      <td>1171</td>\n",
       "      <td>1251</td>\n",
       "      <td>1320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>42.5063</td>\n",
       "      <td>1.5218</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>267</td>\n",
       "      <td>308</td>\n",
       "      <td>334</td>\n",
       "      <td>370</td>\n",
       "      <td>376</td>\n",
       "      <td>390</td>\n",
       "      <td>428</td>\n",
       "      <td>439</td>\n",
       "      <td>466</td>\n",
       "      <td>501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>-11.2027</td>\n",
       "      <td>17.8739</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 79 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Province/State Country/Region      Lat     Long  1/22/20  1/23/20  1/24/20  \\\n",
       "0            NaN    Afghanistan  33.0000  65.0000        0        0        0   \n",
       "1            NaN        Albania  41.1533  20.1683        0        0        0   \n",
       "2            NaN        Algeria  28.0339   1.6596        0        0        0   \n",
       "3            NaN        Andorra  42.5063   1.5218        0        0        0   \n",
       "4            NaN         Angola -11.2027  17.8739        0        0        0   \n",
       "\n",
       "   1/25/20  1/26/20  1/27/20  ...  3/27/20  3/28/20  3/29/20  3/30/20  \\\n",
       "0        0        0        0  ...      110      110      120      170   \n",
       "1        0        0        0  ...      186      197      212      223   \n",
       "2        0        0        0  ...      409      454      511      584   \n",
       "3        0        0        0  ...      267      308      334      370   \n",
       "4        0        0        0  ...        4        5        7        7   \n",
       "\n",
       "   3/31/20  4/1/20  4/2/20  4/3/20  4/4/20  4/5/20  \n",
       "0      174     237     273     281     299     349  \n",
       "1      243     259     277     304     333     361  \n",
       "2      716     847     986    1171    1251    1320  \n",
       "3      376     390     428     439     466     501  \n",
       "4        7       8       8       8      10      14  \n",
       "\n",
       "[5 rows x 79 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Examine first few rows of raw_confirmed \n",
    "raw_confirmed.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Province/State</th>\n",
       "      <th>Country/Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>1/22/20</th>\n",
       "      <th>1/23/20</th>\n",
       "      <th>1/24/20</th>\n",
       "      <th>1/25/20</th>\n",
       "      <th>1/26/20</th>\n",
       "      <th>1/27/20</th>\n",
       "      <th>...</th>\n",
       "      <th>3/24/20</th>\n",
       "      <th>3/25/20</th>\n",
       "      <th>3/26/20</th>\n",
       "      <th>3/27/20</th>\n",
       "      <th>3/28/20</th>\n",
       "      <th>3/29/20</th>\n",
       "      <th>3/30/20</th>\n",
       "      <th>3/31/20</th>\n",
       "      <th>4/1/20</th>\n",
       "      <th>4/2/20</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33.0000</td>\n",
       "      <td>65.0000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>41.1533</td>\n",
       "      <td>20.1683</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "      <td>33</td>\n",
       "      <td>44</td>\n",
       "      <td>52</td>\n",
       "      <td>67</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>28.0339</td>\n",
       "      <td>1.6596</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>24</td>\n",
       "      <td>65</td>\n",
       "      <td>29</td>\n",
       "      <td>29</td>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "      <td>37</td>\n",
       "      <td>46</td>\n",
       "      <td>61</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>42.5063</td>\n",
       "      <td>1.5218</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>-11.2027</td>\n",
       "      <td>17.8739</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 76 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Province/State Country/Region      Lat     Long  1/22/20  1/23/20  1/24/20  \\\n",
       "0            NaN    Afghanistan  33.0000  65.0000        0        0        0   \n",
       "1            NaN        Albania  41.1533  20.1683        0        0        0   \n",
       "2            NaN        Algeria  28.0339   1.6596        0        0        0   \n",
       "3            NaN        Andorra  42.5063   1.5218        0        0        0   \n",
       "4            NaN         Angola -11.2027  17.8739        0        0        0   \n",
       "\n",
       "   1/25/20  1/26/20  1/27/20  ...  3/24/20  3/25/20  3/26/20  3/27/20  \\\n",
       "0        0        0        0  ...        1        2        2        2   \n",
       "1        0        0        0  ...       10       17       17       31   \n",
       "2        0        0        0  ...       24       65       29       29   \n",
       "3        0        0        0  ...        1        1        1        1   \n",
       "4        0        0        0  ...        0        0        0        0   \n",
       "\n",
       "   3/28/20  3/29/20  3/30/20  3/31/20  4/1/20  4/2/20  \n",
       "0        2        2        2        5       5      10  \n",
       "1       31       33       44       52      67      76  \n",
       "2       31       31       37       46      61      61  \n",
       "3        1        1       10       10      10      10  \n",
       "4        0        0        0        1       1       1  \n",
       "\n",
       "[5 rows x 76 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Examine first few rows of raw_recovered \n",
    "raw_recovered.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Province/State</th>\n",
       "      <th>Country/Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>1/22/20</th>\n",
       "      <th>1/23/20</th>\n",
       "      <th>1/24/20</th>\n",
       "      <th>1/25/20</th>\n",
       "      <th>1/26/20</th>\n",
       "      <th>1/27/20</th>\n",
       "      <th>...</th>\n",
       "      <th>3/24/20</th>\n",
       "      <th>3/25/20</th>\n",
       "      <th>3/26/20</th>\n",
       "      <th>3/27/20</th>\n",
       "      <th>3/28/20</th>\n",
       "      <th>3/29/20</th>\n",
       "      <th>3/30/20</th>\n",
       "      <th>3/31/20</th>\n",
       "      <th>4/1/20</th>\n",
       "      <th>4/2/20</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33.0000</td>\n",
       "      <td>65.0000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>41.1533</td>\n",
       "      <td>20.1683</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>28.0339</td>\n",
       "      <td>1.6596</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>19</td>\n",
       "      <td>21</td>\n",
       "      <td>25</td>\n",
       "      <td>26</td>\n",
       "      <td>29</td>\n",
       "      <td>31</td>\n",
       "      <td>35</td>\n",
       "      <td>44</td>\n",
       "      <td>58</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>42.5063</td>\n",
       "      <td>1.5218</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>-11.2027</td>\n",
       "      <td>17.8739</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 76 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Province/State Country/Region      Lat     Long  1/22/20  1/23/20  1/24/20  \\\n",
       "0            NaN    Afghanistan  33.0000  65.0000        0        0        0   \n",
       "1            NaN        Albania  41.1533  20.1683        0        0        0   \n",
       "2            NaN        Algeria  28.0339   1.6596        0        0        0   \n",
       "3            NaN        Andorra  42.5063   1.5218        0        0        0   \n",
       "4            NaN         Angola -11.2027  17.8739        0        0        0   \n",
       "\n",
       "   1/25/20  1/26/20  1/27/20  ...  3/24/20  3/25/20  3/26/20  3/27/20  \\\n",
       "0        0        0        0  ...        1        2        4        4   \n",
       "1        0        0        0  ...        5        5        6        8   \n",
       "2        0        0        0  ...       19       21       25       26   \n",
       "3        0        0        0  ...        1        1        3        3   \n",
       "4        0        0        0  ...        0        0        0        0   \n",
       "\n",
       "   3/28/20  3/29/20  3/30/20  3/31/20  4/1/20  4/2/20  \n",
       "0        4        4        4        4       4       6  \n",
       "1       10       10       11       15      15      16  \n",
       "2       29       31       35       44      58      86  \n",
       "3        3        6        8       12      14      15  \n",
       "4        0        2        2        2       2       2  \n",
       "\n",
       "[5 rows x 76 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Examine first few rows of raw_recovered \n",
    "raw_deaths.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Un-Pivoting the data \n",
    "raw_confirmed2 = pd.melt(raw_confirmed, id_vars=['Province/State', 'Country/Region', 'Lat', 'Long'], var_name=['Date'])\n",
    "raw_recovered2 = pd.melt(raw_recovered, id_vars=['Province/State', 'Country/Region', 'Lat', 'Long'], var_name=['Date'])\n",
    "raw_deaths2 = pd.melt(raw_deaths, id_vars=['Province/State', 'Country/Region', 'Lat', 'Long'], var_name=['Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Province/State</th>\n",
       "      <th>Country/Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>Date</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33.000000</td>\n",
       "      <td>65.000000</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>41.153300</td>\n",
       "      <td>20.168300</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>28.033900</td>\n",
       "      <td>1.659600</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>42.506300</td>\n",
       "      <td>1.521800</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>-11.202700</td>\n",
       "      <td>17.873900</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18571</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Botswana</td>\n",
       "      <td>-22.328500</td>\n",
       "      <td>24.684900</td>\n",
       "      <td>4/2/20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18572</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Burundi</td>\n",
       "      <td>-3.373100</td>\n",
       "      <td>29.918900</td>\n",
       "      <td>4/2/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18573</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sierra Leone</td>\n",
       "      <td>8.460555</td>\n",
       "      <td>-11.779889</td>\n",
       "      <td>4/2/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18574</td>\n",
       "      <td>Bonaire, Sint Eustatius and Saba</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>12.178400</td>\n",
       "      <td>-68.238500</td>\n",
       "      <td>4/2/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18575</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Malawi</td>\n",
       "      <td>-13.254308</td>\n",
       "      <td>34.301525</td>\n",
       "      <td>4/2/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>18576 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Province/State Country/Region        Lat       Long  \\\n",
       "0                                   NaN    Afghanistan  33.000000  65.000000   \n",
       "1                                   NaN        Albania  41.153300  20.168300   \n",
       "2                                   NaN        Algeria  28.033900   1.659600   \n",
       "3                                   NaN        Andorra  42.506300   1.521800   \n",
       "4                                   NaN         Angola -11.202700  17.873900   \n",
       "...                                 ...            ...        ...        ...   \n",
       "18571                               NaN       Botswana -22.328500  24.684900   \n",
       "18572                               NaN        Burundi  -3.373100  29.918900   \n",
       "18573                               NaN   Sierra Leone   8.460555 -11.779889   \n",
       "18574  Bonaire, Sint Eustatius and Saba    Netherlands  12.178400 -68.238500   \n",
       "18575                               NaN         Malawi -13.254308  34.301525   \n",
       "\n",
       "          Date  value  \n",
       "0      1/22/20      0  \n",
       "1      1/22/20      0  \n",
       "2      1/22/20      0  \n",
       "3      1/22/20      0  \n",
       "4      1/22/20      0  \n",
       "...        ...    ...  \n",
       "18571   4/2/20      1  \n",
       "18572   4/2/20      0  \n",
       "18573   4/2/20      0  \n",
       "18574   4/2/20      0  \n",
       "18575   4/2/20      0  \n",
       "\n",
       "[18576 rows x 6 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show after un-pivoting\n",
    "raw_confirmed2\n",
    "raw_recovered2\n",
    "raw_deaths2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The Shape of Confirmed is: (18576, 6)\n",
      "The Shape of Recovered is: (17568, 6)\n",
      "The Shape of Deaths is: (18576, 6)\n"
     ]
    }
   ],
   "source": [
    "# Now take a look at the shape of data \n",
    "print(f\"The Shape of Confirmed is: {raw_confirmed2.shape}\")\n",
    "print(f\"The Shape of Recovered is: {raw_recovered2.shape}\") \n",
    "print(f\"The Shape of Deaths is: {raw_deaths2.shape}\") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Province/State</th>\n",
       "      <th>Country/Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>Date</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33.0000</td>\n",
       "      <td>65.0000</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>41.1533</td>\n",
       "      <td>20.1683</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>28.0339</td>\n",
       "      <td>1.6596</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>42.5063</td>\n",
       "      <td>1.5218</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>-11.2027</td>\n",
       "      <td>17.8739</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Province/State Country/Region      Lat     Long     Date  value\n",
       "0            NaN    Afghanistan  33.0000  65.0000  1/22/20      0\n",
       "1            NaN        Albania  41.1533  20.1683  1/22/20      0\n",
       "2            NaN        Algeria  28.0339   1.6596  1/22/20      0\n",
       "3            NaN        Andorra  42.5063   1.5218  1/22/20      0\n",
       "4            NaN         Angola -11.2027  17.8739  1/22/20      0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Examine first few rows of new dataframe \n",
    "raw_confirmed2.head()\n",
    "raw_recovered2.head() \n",
    "raw_deaths2.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert date \n",
    "raw_confirmed2['Date'] = pd.to_datetime(raw_confirmed2['Date']) \n",
    "raw_recovered2['Date'] = pd.to_datetime(raw_recovered2['Date']) \n",
    "raw_deaths2['Date'] = pd.to_datetime(raw_deaths2['Date'])  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Ranaming the values \n",
    "raw_confirmed2.columns = raw_confirmed2.columns.str.replace('value', 'Confirmed')\n",
    "raw_recovered2.columns = raw_recovered2.columns.str.replace('value', 'Recovered')\n",
    "raw_deaths2.columns = raw_deaths2.columns.str.replace('value', 'Deaths')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Province/State</th>\n",
       "      <th>Country/Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>Date</th>\n",
       "      <th>Deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33.0000</td>\n",
       "      <td>65.0000</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>41.1533</td>\n",
       "      <td>20.1683</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>28.0339</td>\n",
       "      <td>1.6596</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>42.5063</td>\n",
       "      <td>1.5218</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>-11.2027</td>\n",
       "      <td>17.8739</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Province/State Country/Region      Lat     Long       Date  Deaths\n",
       "0            NaN    Afghanistan  33.0000  65.0000 2020-01-22       0\n",
       "1            NaN        Albania  41.1533  20.1683 2020-01-22       0\n",
       "2            NaN        Algeria  28.0339   1.6596 2020-01-22       0\n",
       "3            NaN        Andorra  42.5063   1.5218 2020-01-22       0\n",
       "4            NaN         Angola -11.2027  17.8739 2020-01-22       0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# After renaming \n",
    "raw_confirmed2.head()\n",
    "raw_recovered2.head() \n",
    "raw_deaths2.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Province/State    12816\n",
       "Country/Region        0\n",
       "Lat                   0\n",
       "Long                  0\n",
       "Date                  0\n",
       "Confirmed             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Handling missing values of confirmed \n",
    "raw_confirmed2.isnull().sum() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Province/State    12888\n",
       "Country/Region        0\n",
       "Lat                   0\n",
       "Long                  0\n",
       "Date                  0\n",
       "Recovered             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Handling missing values of recovered \n",
    "raw_recovered2.isnull().sum() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Province/State    12816\n",
       "Country/Region        0\n",
       "Lat                   0\n",
       "Long                  0\n",
       "Date                  0\n",
       "Deaths                0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Handling missing values of deaths \n",
    "raw_deaths2.isnull().sum() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filling missing values \n",
    "raw_confirmed2['Province/State'].fillna(raw_confirmed2['Country/Region'], inplace=True)\n",
    "raw_recovered2['Province/State'].fillna(raw_recovered2['Country/Region'], inplace=True)\n",
    "raw_deaths2['Province/State'].fillna(raw_deaths2['Country/Region'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape of Confirmed is: (18576, 6)\n",
      "Shape of Recovered is: (17568, 6)\n",
      "Shape of Deaths is: (18576, 6)\n"
     ]
    }
   ],
   "source": [
    "# Printing shapes before joining \n",
    "print(f\"Shape of Confirmed is: {raw_confirmed2.shape}\")\n",
    "print(f\"Shape of Recovered is: {raw_recovered2.shape}\")\n",
    "print(f\"Shape of Deaths is: {raw_deaths2.shape}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Province/State</th>\n",
       "      <th>Country/Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>Date</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33.0000</td>\n",
       "      <td>65.0000</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Albania</td>\n",
       "      <td>Albania</td>\n",
       "      <td>41.1533</td>\n",
       "      <td>20.1683</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>28.0339</td>\n",
       "      <td>1.6596</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>42.5063</td>\n",
       "      <td>1.5218</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Angola</td>\n",
       "      <td>Angola</td>\n",
       "      <td>-11.2027</td>\n",
       "      <td>17.8739</td>\n",
       "      <td>2020-01-22</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": [
       "  Province/State Country/Region      Lat     Long       Date  Confirmed  \\\n",
       "0    Afghanistan    Afghanistan  33.0000  65.0000 2020-01-22        0.0   \n",
       "1        Albania        Albania  41.1533  20.1683 2020-01-22        0.0   \n",
       "2        Algeria        Algeria  28.0339   1.6596 2020-01-22        0.0   \n",
       "3        Andorra        Andorra  42.5063   1.5218 2020-01-22        0.0   \n",
       "4         Angola         Angola -11.2027  17.8739 2020-01-22        0.0   \n",
       "\n",
       "   Deaths  Recovered  \n",
       "0     0.0        0.0  \n",
       "1     0.0        0.0  \n",
       "2     0.0        0.0  \n",
       "3     0.0        0.0  \n",
       "4     0.0        0.0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Confirmed with Deaths\n",
    "full_join = raw_confirmed2.merge(raw_deaths2[['Province/State','Country/Region','Date','Deaths']], \n",
    "                                      how = 'outer', \n",
    "                                      left_on = ['Province/State','Country/Region','Date'], \n",
    "                                      right_on = ['Province/State', 'Country/Region','Date'])\n",
    "\n",
    "\n",
    "# full join with Recovered\n",
    "full_join = full_join.merge(raw_recovered2[['Province/State','Country/Region','Date','Recovered']], \n",
    "                                      how = 'outer', \n",
    "                                      left_on = ['Province/State','Country/Region','Date'], \n",
    "                                      right_on = ['Province/State', 'Country/Region','Date'])\n",
    "\n",
    "full_join.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape of First Join:  (18648, 8)\n",
      "Shape of Second Join:  (18648, 8)\n"
     ]
    }
   ],
   "source": [
    "print(\"Shape of First Join: \", full_join.shape)\n",
    "print(\"Shape of Second Join: \", full_join.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Province/State       0\n",
       "Country/Region       0\n",
       "Lat                 72\n",
       "Long                72\n",
       "Date                 0\n",
       "Confirmed           72\n",
       "Deaths              72\n",
       "Recovered         1080\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check missing values \n",
    "full_join.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fill missing value and convert to integer\n",
    "full_join = full_join.astype({'Confirmed':'int64', 'Deaths':'int64', 'Recovered':'int64'})\n",
    "full_join.loc[:, 'Confirmed'].fillna(value=0, inplace=True)\n",
    "full_join.loc[:, 'Deaths'].fillna(value=0, inplace=True)\n",
    "full_join.loc[:, 'Recovered'].fillna(value=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Province/State       0\n",
       "Country/Region       0\n",
       "Lat                 72\n",
       "Long                72\n",
       "Date                 0\n",
       "Confirmed           72\n",
       "Deaths              72\n",
       "Recovered         1080\n",
       "dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_join.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Province/State</th>\n",
       "      <th>Country/Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>Date</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33.0000</td>\n",
       "      <td>65.0000</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Albania</td>\n",
       "      <td>Albania</td>\n",
       "      <td>41.1533</td>\n",
       "      <td>20.1683</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>28.0339</td>\n",
       "      <td>1.6596</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>42.5063</td>\n",
       "      <td>1.5218</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Angola</td>\n",
       "      <td>Angola</td>\n",
       "      <td>-11.2027</td>\n",
       "      <td>17.8739</td>\n",
       "      <td>2020-01-22</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": [
       "  Province/State Country/Region      Lat     Long       Date  Confirmed  \\\n",
       "0    Afghanistan    Afghanistan  33.0000  65.0000 2020-01-22        0.0   \n",
       "1        Albania        Albania  41.1533  20.1683 2020-01-22        0.0   \n",
       "2        Algeria        Algeria  28.0339   1.6596 2020-01-22        0.0   \n",
       "3        Andorra        Andorra  42.5063   1.5218 2020-01-22        0.0   \n",
       "4         Angola         Angola -11.2027  17.8739 2020-01-22        0.0   \n",
       "\n",
       "   Deaths  Recovered  \n",
       "0     0.0        0.0  \n",
       "1     0.0        0.0  \n",
       "2     0.0        0.0  \n",
       "3     0.0        0.0  \n",
       "4     0.0        0.0  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Store clean data \n",
    "cleaned_data = full_join\n",
    "cleaned_data.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export \n",
    "cleaned_data.to_csv(\"world_data/covid-19_cleaned_data.csv(updated)\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Take a look at new dataset \n",
    "df = pd.read_csv(\"world_data/covid-19_cleaned_data.csv(updated)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Province/State</th>\n",
       "      <th>Country/Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>Date</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Thailand</td>\n",
       "      <td>Thailand</td>\n",
       "      <td>15.0000</td>\n",
       "      <td>101.0000</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Japan</td>\n",
       "      <td>Japan</td>\n",
       "      <td>36.0000</td>\n",
       "      <td>138.0000</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>1.2833</td>\n",
       "      <td>103.8333</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Nepal</td>\n",
       "      <td>Nepal</td>\n",
       "      <td>28.1667</td>\n",
       "      <td>84.2500</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>112.5000</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Province/State Country/Region      Lat      Long        Date  Confirmed  \\\n",
       "0       Thailand       Thailand  15.0000  101.0000  2020-01-22          2   \n",
       "1          Japan          Japan  36.0000  138.0000  2020-01-22          2   \n",
       "2      Singapore      Singapore   1.2833  103.8333  2020-01-22          0   \n",
       "3          Nepal          Nepal  28.1667   84.2500  2020-01-22          0   \n",
       "4       Malaysia       Malaysia   2.5000  112.5000  2020-01-22          0   \n",
       "\n",
       "   Deaths  Recovered  \n",
       "0       0          0  \n",
       "1       0          0  \n",
       "2       0          0  \n",
       "3       0          0  \n",
       "4       0          0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-08</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2020-03-09</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2020-03-10</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2020-03-11</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2020-03-12</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>2020-03-13</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>2020-03-14</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>2020-03-15</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>2020-03-16</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>2020-03-17</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>2020-03-18</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>2020-03-19</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>2020-03-20</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>2020-03-21</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>2020-03-22</td>\n",
       "      <td>27</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>2020-03-23</td>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>2020-03-24</td>\n",
       "      <td>39</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>2020-03-25</td>\n",
       "      <td>39</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date  Confirmed  Deaths  Recovered\n",
       "0   2020-03-08          3       0          0\n",
       "1   2020-03-09          3       0          0\n",
       "2   2020-03-10          3       0          0\n",
       "3   2020-03-11          3       0          0\n",
       "4   2020-03-12          3       0          0\n",
       "5   2020-03-13          3       0          0\n",
       "6   2020-03-14          3       0          0\n",
       "7   2020-03-15          5       0          0\n",
       "8   2020-03-16          8       0          0\n",
       "9   2020-03-17         10       0          0\n",
       "10  2020-03-18         14       1          3\n",
       "11  2020-03-19         17       1          3\n",
       "12  2020-03-20         20       1          3\n",
       "13  2020-03-21         25       1          3\n",
       "14  2020-03-22         27       2          3\n",
       "15  2020-03-23         33       2          3\n",
       "16  2020-03-24         39       3          5\n",
       "17  2020-03-25         39       4          5"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bd = pd.read_csv(\"./bd_data/COVID-19_in_bd.csv\")\n",
    "df_bd"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}