Covid-19 Time Series Data Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import tableauserverclient as TSC\n",
    "from pandleau import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "confirmed_df = pd.read_csv('time_series_covid19_confirmed_global.csv')\n",
    "deaths_df = pd.read_csv('time_series_covid19_deaths_global.csv')\n",
    "recovered_df = pd.read_csv('time_series_covid19_recovered_global.csv')"
   ]
  },
  {
   "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>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>5/22/20</th>\n",
       "      <th>5/23/20</th>\n",
       "      <th>5/24/20</th>\n",
       "      <th>5/25/20</th>\n",
       "      <th>5/26/20</th>\n",
       "      <th>5/27/20</th>\n",
       "      <th>5/28/20</th>\n",
       "      <th>5/29/20</th>\n",
       "      <th>5/30/20</th>\n",
       "      <th>5/31/20</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\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>9216</td>\n",
       "      <td>9998</td>\n",
       "      <td>10582</td>\n",
       "      <td>11173</td>\n",
       "      <td>11831</td>\n",
       "      <td>12456</td>\n",
       "      <td>13036</td>\n",
       "      <td>13659</td>\n",
       "      <td>14525</td>\n",
       "      <td>15205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\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>981</td>\n",
       "      <td>989</td>\n",
       "      <td>998</td>\n",
       "      <td>1004</td>\n",
       "      <td>1029</td>\n",
       "      <td>1050</td>\n",
       "      <td>1076</td>\n",
       "      <td>1099</td>\n",
       "      <td>1122</td>\n",
       "      <td>1137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\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>7918</td>\n",
       "      <td>8113</td>\n",
       "      <td>8306</td>\n",
       "      <td>8503</td>\n",
       "      <td>8697</td>\n",
       "      <td>8857</td>\n",
       "      <td>8997</td>\n",
       "      <td>9134</td>\n",
       "      <td>9267</td>\n",
       "      <td>9394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\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>762</td>\n",
       "      <td>762</td>\n",
       "      <td>762</td>\n",
       "      <td>763</td>\n",
       "      <td>763</td>\n",
       "      <td>763</td>\n",
       "      <td>763</td>\n",
       "      <td>764</td>\n",
       "      <td>764</td>\n",
       "      <td>764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\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>60</td>\n",
       "      <td>61</td>\n",
       "      <td>69</td>\n",
       "      <td>70</td>\n",
       "      <td>70</td>\n",
       "      <td>71</td>\n",
       "      <td>74</td>\n",
       "      <td>81</td>\n",
       "      <td>84</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 135 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  ...  5/22/20  5/23/20  5/24/20  5/25/20  \\\n",
       "0        0        0        0  ...     9216     9998    10582    11173   \n",
       "1        0        0        0  ...      981      989      998     1004   \n",
       "2        0        0        0  ...     7918     8113     8306     8503   \n",
       "3        0        0        0  ...      762      762      762      763   \n",
       "4        0        0        0  ...       60       61       69       70   \n",
       "\n",
       "   5/26/20  5/27/20  5/28/20  5/29/20  5/30/20  5/31/20  \n",
       "0    11831    12456    13036    13659    14525    15205  \n",
       "1     1029     1050     1076     1099     1122     1137  \n",
       "2     8697     8857     8997     9134     9267     9394  \n",
       "3      763      763      763      764      764      764  \n",
       "4       70       71       74       81       84       86  \n",
       "\n",
       "[5 rows x 135 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "confirmed_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "dates = confirmed_df.columns[4:]\n",
    "confirmed_df_long = confirmed_df.melt(\n",
    "    id_vars=['Province/State', 'Country/Region', 'Lat', 'Long'], \n",
    "    value_vars=dates, \n",
    "    var_name='Date', \n",
    "    value_name='Confirmed'\n",
    ")\n",
    "deaths_df_long = deaths_df.melt(\n",
    "    id_vars=['Province/State', 'Country/Region', 'Lat', 'Long'], \n",
    "    value_vars=dates, \n",
    "    var_name='Date', \n",
    "    value_name='Deaths'\n",
    ")\n",
    "recovered_df_long = recovered_df.melt(\n",
    "    id_vars=['Province/State', 'Country/Region', 'Lat', 'Long'], \n",
    "    value_vars=dates, \n",
    "    var_name='Date', \n",
    "    value_name='Recovered'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\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",
       "      <th>1</th>\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",
       "      <th>2</th>\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",
       "      <th>3</th>\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",
       "      <th>4</th>\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",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34841</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Sao Tome and Principe</td>\n",
       "      <td>0.186360</td>\n",
       "      <td>6.613081</td>\n",
       "      <td>5/31/20</td>\n",
       "      <td>483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34842</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>15.552727</td>\n",
       "      <td>48.516388</td>\n",
       "      <td>5/31/20</td>\n",
       "      <td>323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34843</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Comoros</td>\n",
       "      <td>-11.645500</td>\n",
       "      <td>43.333300</td>\n",
       "      <td>5/31/20</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34844</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Tajikistan</td>\n",
       "      <td>38.861034</td>\n",
       "      <td>71.276093</td>\n",
       "      <td>5/31/20</td>\n",
       "      <td>3930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34845</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Lesotho</td>\n",
       "      <td>-29.609988</td>\n",
       "      <td>28.233608</td>\n",
       "      <td>5/31/20</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>34846 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Province/State         Country/Region        Lat       Long     Date  \\\n",
       "0                NaN            Afghanistan  33.000000  65.000000  1/22/20   \n",
       "1                NaN                Albania  41.153300  20.168300  1/22/20   \n",
       "2                NaN                Algeria  28.033900   1.659600  1/22/20   \n",
       "3                NaN                Andorra  42.506300   1.521800  1/22/20   \n",
       "4                NaN                 Angola -11.202700  17.873900  1/22/20   \n",
       "...              ...                    ...        ...        ...      ...   \n",
       "34841            NaN  Sao Tome and Principe   0.186360   6.613081  5/31/20   \n",
       "34842            NaN                  Yemen  15.552727  48.516388  5/31/20   \n",
       "34843            NaN                Comoros -11.645500  43.333300  5/31/20   \n",
       "34844            NaN             Tajikistan  38.861034  71.276093  5/31/20   \n",
       "34845            NaN                Lesotho -29.609988  28.233608  5/31/20   \n",
       "\n",
       "       Confirmed  \n",
       "0              0  \n",
       "1              0  \n",
       "2              0  \n",
       "3              0  \n",
       "4              0  \n",
       "...          ...  \n",
       "34841        483  \n",
       "34842        323  \n",
       "34843        106  \n",
       "34844       3930  \n",
       "34845          2  \n",
       "\n",
       "[34846 rows x 6 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "confirmed_df_long"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "recovered_df_long = recovered_df_long[recovered_df_long['Country/Region']!='Canada']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merging confirmed_df_long and deaths_df_long\n",
    "full_table = confirmed_df_long.merge(\n",
    "  right=deaths_df_long, \n",
    "  how='left',\n",
    "  on=['Province/State', 'Country/Region', 'Date', 'Lat', 'Long']\n",
    ")\n",
    "# Merging full_table and recovered_df_long\n",
    "full_table = full_table.merge(\n",
    "  right=recovered_df_long, \n",
    "  how='left',\n",
    "  on=['Province/State', 'Country/Region', 'Date', 'Lat', 'Long']\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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",
       "      <th>0</th>\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",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\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",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\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",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\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",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\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",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34841</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Sao Tome and Principe</td>\n",
       "      <td>0.186360</td>\n",
       "      <td>6.613081</td>\n",
       "      <td>5/31/20</td>\n",
       "      <td>483</td>\n",
       "      <td>12</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34842</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>15.552727</td>\n",
       "      <td>48.516388</td>\n",
       "      <td>5/31/20</td>\n",
       "      <td>323</td>\n",
       "      <td>80</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34843</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Comoros</td>\n",
       "      <td>-11.645500</td>\n",
       "      <td>43.333300</td>\n",
       "      <td>5/31/20</td>\n",
       "      <td>106</td>\n",
       "      <td>2</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34844</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Tajikistan</td>\n",
       "      <td>38.861034</td>\n",
       "      <td>71.276093</td>\n",
       "      <td>5/31/20</td>\n",
       "      <td>3930</td>\n",
       "      <td>47</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34845</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Lesotho</td>\n",
       "      <td>-29.609988</td>\n",
       "      <td>28.233608</td>\n",
       "      <td>5/31/20</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>34846 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Province/State         Country/Region        Lat       Long     Date  \\\n",
       "0                NaN            Afghanistan  33.000000  65.000000  1/22/20   \n",
       "1                NaN                Albania  41.153300  20.168300  1/22/20   \n",
       "2                NaN                Algeria  28.033900   1.659600  1/22/20   \n",
       "3                NaN                Andorra  42.506300   1.521800  1/22/20   \n",
       "4                NaN                 Angola -11.202700  17.873900  1/22/20   \n",
       "...              ...                    ...        ...        ...      ...   \n",
       "34841            NaN  Sao Tome and Principe   0.186360   6.613081  5/31/20   \n",
       "34842            NaN                  Yemen  15.552727  48.516388  5/31/20   \n",
       "34843            NaN                Comoros -11.645500  43.333300  5/31/20   \n",
       "34844            NaN             Tajikistan  38.861034  71.276093  5/31/20   \n",
       "34845            NaN                Lesotho -29.609988  28.233608  5/31/20   \n",
       "\n",
       "       Confirmed  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  \n",
       "...          ...     ...        ...  \n",
       "34841        483      12       68.0  \n",
       "34842        323      80        NaN  \n",
       "34843        106       2       26.0  \n",
       "34844       3930      47        NaN  \n",
       "34845          2       0        1.0  \n",
       "\n",
       "[34846 rows x 8 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_table['Date'] = pd.to_datetime(full_table['Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33.000000</td>\n",
       "      <td>65.000000</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>41.153300</td>\n",
       "      <td>20.168300</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>28.033900</td>\n",
       "      <td>1.659600</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>42.506300</td>\n",
       "      <td>1.521800</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>-11.202700</td>\n",
       "      <td>17.873900</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34841</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Sao Tome and Principe</td>\n",
       "      <td>0.186360</td>\n",
       "      <td>6.613081</td>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>483</td>\n",
       "      <td>12</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34842</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>15.552727</td>\n",
       "      <td>48.516388</td>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>323</td>\n",
       "      <td>80</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34843</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Comoros</td>\n",
       "      <td>-11.645500</td>\n",
       "      <td>43.333300</td>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>106</td>\n",
       "      <td>2</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34844</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Tajikistan</td>\n",
       "      <td>38.861034</td>\n",
       "      <td>71.276093</td>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>3930</td>\n",
       "      <td>47</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34845</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Lesotho</td>\n",
       "      <td>-29.609988</td>\n",
       "      <td>28.233608</td>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>34846 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Province/State         Country/Region        Lat       Long       Date  \\\n",
       "0                NaN            Afghanistan  33.000000  65.000000 2020-01-22   \n",
       "1                NaN                Albania  41.153300  20.168300 2020-01-22   \n",
       "2                NaN                Algeria  28.033900   1.659600 2020-01-22   \n",
       "3                NaN                Andorra  42.506300   1.521800 2020-01-22   \n",
       "4                NaN                 Angola -11.202700  17.873900 2020-01-22   \n",
       "...              ...                    ...        ...        ...        ...   \n",
       "34841            NaN  Sao Tome and Principe   0.186360   6.613081 2020-05-31   \n",
       "34842            NaN                  Yemen  15.552727  48.516388 2020-05-31   \n",
       "34843            NaN                Comoros -11.645500  43.333300 2020-05-31   \n",
       "34844            NaN             Tajikistan  38.861034  71.276093 2020-05-31   \n",
       "34845            NaN                Lesotho -29.609988  28.233608 2020-05-31   \n",
       "\n",
       "       Confirmed  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  \n",
       "...          ...     ...        ...  \n",
       "34841        483      12       68.0  \n",
       "34842        323      80        NaN  \n",
       "34843        106       2       26.0  \n",
       "34844       3930      47        NaN  \n",
       "34845          2       0        1.0  \n",
       "\n",
       "[34846 rows x 8 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Province/State    24235\n",
       "Country/Region        0\n",
       "Lat                   0\n",
       "Long                  0\n",
       "Date                  0\n",
       "Confirmed             0\n",
       "Deaths                0\n",
       "Recovered          3668\n",
       "dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_table.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_table['Recovered'] = full_table['Recovered'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "ship_rows = full_table['Province/State'].str.contains('Grand Princess') | full_table['Province/State'].str.contains('Diamond Princess') | full_table['Country/Region'].str.contains('Diamond Princess') | full_table['Country/Region'].str.contains('MS Zaandam')\n",
    "full_ship = full_table[ship_rows]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_table = full_table[~(ship_rows)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Rakesh Raj\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "# Active Case = confirmed - deaths - recovered\n",
    "full_table['Active'] = full_table['Confirmed'] - full_table['Deaths'] - full_table['Recovered']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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",
       "      <th>Active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33.000000</td>\n",
       "      <td>65.000000</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>41.153300</td>\n",
       "      <td>20.168300</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>28.033900</td>\n",
       "      <td>1.659600</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>42.506300</td>\n",
       "      <td>1.521800</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>-11.202700</td>\n",
       "      <td>17.873900</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34841</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Sao Tome and Principe</td>\n",
       "      <td>0.186360</td>\n",
       "      <td>6.613081</td>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>483</td>\n",
       "      <td>12</td>\n",
       "      <td>68.0</td>\n",
       "      <td>403.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34842</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>15.552727</td>\n",
       "      <td>48.516388</td>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>323</td>\n",
       "      <td>80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>243.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34843</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Comoros</td>\n",
       "      <td>-11.645500</td>\n",
       "      <td>43.333300</td>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>106</td>\n",
       "      <td>2</td>\n",
       "      <td>26.0</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34844</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Tajikistan</td>\n",
       "      <td>38.861034</td>\n",
       "      <td>71.276093</td>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>3930</td>\n",
       "      <td>47</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3883.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34845</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Lesotho</td>\n",
       "      <td>-29.609988</td>\n",
       "      <td>28.233608</td>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>34322 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Province/State         Country/Region        Lat       Long       Date  \\\n",
       "0                NaN            Afghanistan  33.000000  65.000000 2020-01-22   \n",
       "1                NaN                Albania  41.153300  20.168300 2020-01-22   \n",
       "2                NaN                Algeria  28.033900   1.659600 2020-01-22   \n",
       "3                NaN                Andorra  42.506300   1.521800 2020-01-22   \n",
       "4                NaN                 Angola -11.202700  17.873900 2020-01-22   \n",
       "...              ...                    ...        ...        ...        ...   \n",
       "34841            NaN  Sao Tome and Principe   0.186360   6.613081 2020-05-31   \n",
       "34842            NaN                  Yemen  15.552727  48.516388 2020-05-31   \n",
       "34843            NaN                Comoros -11.645500  43.333300 2020-05-31   \n",
       "34844            NaN             Tajikistan  38.861034  71.276093 2020-05-31   \n",
       "34845            NaN                Lesotho -29.609988  28.233608 2020-05-31   \n",
       "\n",
       "       Confirmed  Deaths  Recovered  Active  \n",
       "0              0       0        0.0     0.0  \n",
       "1              0       0        0.0     0.0  \n",
       "2              0       0        0.0     0.0  \n",
       "3              0       0        0.0     0.0  \n",
       "4              0       0        0.0     0.0  \n",
       "...          ...     ...        ...     ...  \n",
       "34841        483      12       68.0   403.0  \n",
       "34842        323      80        0.0   243.0  \n",
       "34843        106       2       26.0    78.0  \n",
       "34844       3930      47        0.0  3883.0  \n",
       "34845          2       0        1.0     1.0  \n",
       "\n",
       "[34322 rows x 9 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Rakesh Raj\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "full_grouped = full_table.groupby(['Date', 'Country/Region'])['Confirmed', 'Deaths', 'Recovered', 'Active'].sum().reset_index()"
   ]
  },
  {
   "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>Date</th>\n",
       "      <th>Country/Region</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "      <th>Active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Albania</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Angola</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24361</th>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>West Bank and Gaza</td>\n",
       "      <td>448</td>\n",
       "      <td>3</td>\n",
       "      <td>372.0</td>\n",
       "      <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24362</th>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>Western Sahara</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24363</th>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>323</td>\n",
       "      <td>80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>243.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24364</th>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>1057</td>\n",
       "      <td>7</td>\n",
       "      <td>779.0</td>\n",
       "      <td>271.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24365</th>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>178</td>\n",
       "      <td>4</td>\n",
       "      <td>29.0</td>\n",
       "      <td>145.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>24366 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Date      Country/Region  Confirmed  Deaths  Recovered  Active\n",
       "0     2020-01-22         Afghanistan          0       0        0.0     0.0\n",
       "1     2020-01-22             Albania          0       0        0.0     0.0\n",
       "2     2020-01-22             Algeria          0       0        0.0     0.0\n",
       "3     2020-01-22             Andorra          0       0        0.0     0.0\n",
       "4     2020-01-22              Angola          0       0        0.0     0.0\n",
       "...          ...                 ...        ...     ...        ...     ...\n",
       "24361 2020-05-31  West Bank and Gaza        448       3      372.0    73.0\n",
       "24362 2020-05-31      Western Sahara          9       1        6.0     2.0\n",
       "24363 2020-05-31               Yemen        323      80        0.0   243.0\n",
       "24364 2020-05-31              Zambia       1057       7      779.0   271.0\n",
       "24365 2020-05-31            Zimbabwe        178       4       29.0   145.0\n",
       "\n",
       "[24366 rows x 6 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Rakesh Raj\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "# new cases \n",
    "temp = full_grouped.groupby(['Country/Region', 'Date', ])['Confirmed', 'Deaths', 'Recovered']\n",
    "temp = temp.sum().diff().reset_index()\n",
    "temp.to_csv(\"temp.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "mask = temp['Country/Region'] != temp['Country/Region'].shift(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         True\n",
       "1        False\n",
       "2        False\n",
       "3        False\n",
       "4        False\n",
       "         ...  \n",
       "24361    False\n",
       "24362    False\n",
       "24363    False\n",
       "24364    False\n",
       "24365    False\n",
       "Name: Country/Region, Length: 24366, dtype: bool"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mask"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp.loc[mask, 'Confirmed'] = np.nan\n",
    "temp.loc[mask, 'Deaths'] = np.nan\n",
    "temp.loc[mask, 'Recovered'] = np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       NaN\n",
       "131     NaN\n",
       "262     NaN\n",
       "393     NaN\n",
       "524     NaN\n",
       "         ..\n",
       "23711   NaN\n",
       "23842   NaN\n",
       "23973   NaN\n",
       "24104   NaN\n",
       "24235   NaN\n",
       "Name: Confirmed, Length: 186, dtype: float64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp.loc[mask, 'Confirmed']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>Country/Region</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",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2020-01-23</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-24</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-25</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-26</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24361</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2020-05-27</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24362</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2020-05-28</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24363</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2020-05-29</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24364</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2020-05-30</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24365</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>24366 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Country/Region       Date  Confirmed  Deaths  Recovered\n",
       "0        Afghanistan 2020-01-22        NaN     NaN        NaN\n",
       "1        Afghanistan 2020-01-23        0.0     0.0        0.0\n",
       "2        Afghanistan 2020-01-24        0.0     0.0        0.0\n",
       "3        Afghanistan 2020-01-25        0.0     0.0        0.0\n",
       "4        Afghanistan 2020-01-26        0.0     0.0        0.0\n",
       "...              ...        ...        ...     ...        ...\n",
       "24361       Zimbabwe 2020-05-27       76.0     0.0        0.0\n",
       "24362       Zimbabwe 2020-05-28       17.0     0.0        3.0\n",
       "24363       Zimbabwe 2020-05-29        0.0     0.0        0.0\n",
       "24364       Zimbabwe 2020-05-30       25.0     0.0        1.0\n",
       "24365       Zimbabwe 2020-05-31        4.0     0.0        0.0\n",
       "\n",
       "[24366 rows x 5 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp.columns = ['Country/Region', 'Date', 'New cases', 'New deaths', 'New recovered']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merging new values\n",
    "full_grouped = pd.merge(full_grouped, temp, on=['Country/Region', 'Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filling na with 0\n",
    "full_grouped = full_grouped.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fixing data types\n",
    "cols = ['New cases', 'New deaths', 'New recovered']\n",
    "full_grouped[cols] = full_grouped[cols].astype('int')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_grouped['New cases'] = full_grouped['New cases'].apply(lambda x: 0 if x<0 else x)\n",
    "full_grouped['New deaths'] = full_grouped['New deaths'].apply(lambda x: 0 if x<0 else x)\n",
    "full_grouped['New recovered'] = full_grouped['New recovered'].apply(lambda x: 0 if x<0 else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>Country/Region</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "      <th>Active</th>\n",
       "      <th>New cases</th>\n",
       "      <th>New deaths</th>\n",
       "      <th>New recovered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Albania</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Angola</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24361</th>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>West Bank and Gaza</td>\n",
       "      <td>448</td>\n",
       "      <td>3</td>\n",
       "      <td>372.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24362</th>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>Western Sahara</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24363</th>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>323</td>\n",
       "      <td>80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>243.0</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24364</th>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>1057</td>\n",
       "      <td>7</td>\n",
       "      <td>779.0</td>\n",
       "      <td>271.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24365</th>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>178</td>\n",
       "      <td>4</td>\n",
       "      <td>29.0</td>\n",
       "      <td>145.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>24366 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Date      Country/Region  Confirmed  Deaths  Recovered  Active  \\\n",
       "0     2020-01-22         Afghanistan          0       0        0.0     0.0   \n",
       "1     2020-01-22             Albania          0       0        0.0     0.0   \n",
       "2     2020-01-22             Algeria          0       0        0.0     0.0   \n",
       "3     2020-01-22             Andorra          0       0        0.0     0.0   \n",
       "4     2020-01-22              Angola          0       0        0.0     0.0   \n",
       "...          ...                 ...        ...     ...        ...     ...   \n",
       "24361 2020-05-31  West Bank and Gaza        448       3      372.0    73.0   \n",
       "24362 2020-05-31      Western Sahara          9       1        6.0     2.0   \n",
       "24363 2020-05-31               Yemen        323      80        0.0   243.0   \n",
       "24364 2020-05-31              Zambia       1057       7      779.0   271.0   \n",
       "24365 2020-05-31            Zimbabwe        178       4       29.0   145.0   \n",
       "\n",
       "       New cases  New deaths  New recovered  \n",
       "0              0           0              0  \n",
       "1              0           0              0  \n",
       "2              0           0              0  \n",
       "3              0           0              0  \n",
       "4              0           0              0  \n",
       "...          ...         ...            ...  \n",
       "24361          1           0              4  \n",
       "24362          0           0              0  \n",
       "24363         13           3              0  \n",
       "24364          0           0              0  \n",
       "24365          4           0              0  \n",
       "\n",
       "[24366 rows x 9 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_grouped.to_csv('COVID-19-time-series-clean-complete.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "processing table: 24366it [00:01, 14284.37it/s]\n"
     ]
    }
   ],
   "source": [
    "df = pandleau(full_grouped)\n",
    "df.to_tableau('mydata.hyper', add_index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}