data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Source"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://github.com/CSSEGISandData/COVID-19"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "# ================\n",
    "\n",
    "# for date and time opeations\n",
    "from datetime import datetime, timedelta\n",
    "# for file and folder operations\n",
    "import os\n",
    "# for regular expression opeations\n",
    "import re\n",
    "# for listing files in a folder\n",
    "import glob\n",
    "# for getting web contents\n",
    "import requests \n",
    "# storing and analysing data\n",
    "import pandas as pd\n",
    "# for scraping web contents\n",
    "from bs4 import BeautifulSoup\n",
    "# to download data\n",
    "import wget\n",
    "# numerical analysis\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Downloading data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "'rm' is not recognized as an internal or external command,\n",
      "operable program or batch file.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100% [............................................................................] 181209 / 181209"
     ]
    }
   ],
   "source": [
    "# remove all existing csv files\n",
    "! rm *.csv\n",
    "\n",
    "# urls of the files\n",
    "urls = ['https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv', \n",
    "        'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv',\n",
    "        'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_recovered_global.csv']\n",
    "\n",
    "# download files\n",
    "for url in urls:\n",
    "    filename = wget.download(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dataset\n",
    "# ======\n",
    "\n",
    "conf_df = pd.read_csv('time_series_covid19_confirmed_global.csv')\n",
    "deaths_df = pd.read_csv('time_series_covid19_deaths_global.csv')\n",
    "recv_df = pd.read_csv('time_series_covid19_recovered_global.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# conf_df.head()\n",
    "# deaths_df.head()\n",
    "# recv_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# conf_df.columns\n",
    "# deaths_df.columns\n",
    "# recv_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# conf_df.columns[4:]\n",
    "# deaths_df.columns[4:]\n",
    "# recv_df.columns[4:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merging dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(50008, 6)\n",
      "(50008, 6)\n",
      "(47376, 6)\n"
     ]
    }
   ],
   "source": [
    "# extract dates\n",
    "dates = conf_df.columns[4:]\n",
    "\n",
    "# melt dataframes into longer format\n",
    "# ==================================\n",
    "conf_df_long = conf_df.melt(id_vars=['Province/State', 'Country/Region', 'Lat', 'Long'], \n",
    "                            value_vars=dates, var_name='Date', value_name='Confirmed')\n",
    "\n",
    "deaths_df_long = deaths_df.melt(id_vars=['Province/State', 'Country/Region', 'Lat', 'Long'], \n",
    "                            value_vars=dates, var_name='Date', value_name='Deaths')\n",
    "\n",
    "recv_df_long = recv_df.melt(id_vars=['Province/State', 'Country/Region', 'Lat', 'Long'], \n",
    "                            value_vars=dates, var_name='Date', value_name='Recovered')\n",
    "\n",
    "recv_df_long = recv_df_long[recv_df_long['Country/Region']!='Canada']\n",
    "\n",
    "print(conf_df_long.shape)\n",
    "print(deaths_df_long.shape)\n",
    "print(recv_df_long.shape)"
   ]
  },
  {
   "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>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.93911</td>\n",
       "      <td>67.709953</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.15330</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.03390</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.50630</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.20270</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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Province/State Country/Region       Lat       Long     Date  Confirmed  \\\n",
       "0            NaN    Afghanistan  33.93911  67.709953  1/22/20          0   \n",
       "1            NaN        Albania  41.15330  20.168300  1/22/20          0   \n",
       "2            NaN        Algeria  28.03390   1.659600  1/22/20          0   \n",
       "3            NaN        Andorra  42.50630   1.521800  1/22/20          0   \n",
       "4            NaN         Angola -11.20270  17.873900  1/22/20          0   \n",
       "\n",
       "   Deaths  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  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge dataframes\n",
    "# ================\n",
    "\n",
    "full_table = pd.merge(left=conf_df_long, right=deaths_df_long, how='left',\n",
    "                      on=['Province/State', 'Country/Region', 'Date', 'Lat', 'Long'])\n",
    "full_table = pd.merge(left=full_table, right=recv_df_long, how='left',\n",
    "                      on=['Province/State', 'Country/Region', 'Date', 'Lat', 'Long'])\n",
    "\n",
    "full_table.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert to proper date format\n",
    "full_table['Date'] = pd.to_datetime(full_table['Date'])\n",
    "\n",
    "# fill na with 0\n",
    "full_table['Recovered'] = full_table['Recovered'].fillna(0)\n",
    "\n",
    "# convert to int datatype\n",
    "full_table['Recovered'] = full_table['Recovered'].astype('int')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fixing Country names\n",
    "# ====================\n",
    "\n",
    "# renaming countries, regions, provinces\n",
    "full_table['Country/Region'] = full_table['Country/Region'].replace('Korea, South', 'South Korea')\n",
    "\n",
    "# Greenland\n",
    "full_table.loc[full_table['Province/State']=='Greenland', 'Country/Region'] = 'Greenland'\n",
    "\n",
    "# Mainland china to China\n",
    "full_table['Country/Region'] = full_table['Country/Region'].replace('Mainland China', 'China')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# removing\n",
    "# =======\n",
    "\n",
    "# removing canada's recovered values\n",
    "full_table = full_table[full_table['Province/State'].str.contains('Recovered')!=True]\n",
    "\n",
    "# removing county wise data to avoid double counting\n",
    "full_table = full_table[full_table['Province/State'].str.contains(',')!=True]"
   ]
  },
  {
   "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>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "      <th>Active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14404</th>\n",
       "      <td>Newfoundland and Labrador</td>\n",
       "      <td>Canada</td>\n",
       "      <td>53.135500</td>\n",
       "      <td>-57.660400</td>\n",
       "      <td>2020-03-16</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44499</th>\n",
       "      <td>Tianjin</td>\n",
       "      <td>China</td>\n",
       "      <td>39.305400</td>\n",
       "      <td>117.323000</td>\n",
       "      <td>2020-07-07</td>\n",
       "      <td>199</td>\n",
       "      <td>3</td>\n",
       "      <td>194</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5966</th>\n",
       "      <td>St Martin</td>\n",
       "      <td>France</td>\n",
       "      <td>18.070800</td>\n",
       "      <td>-63.050100</td>\n",
       "      <td>2020-02-13</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28933</th>\n",
       "      <td></td>\n",
       "      <td>Sweden</td>\n",
       "      <td>60.128161</td>\n",
       "      <td>18.643501</td>\n",
       "      <td>2020-05-09</td>\n",
       "      <td>25921</td>\n",
       "      <td>3220</td>\n",
       "      <td>0</td>\n",
       "      <td>22701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33773</th>\n",
       "      <td>Falkland Islands (Malvinas)</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>-51.796300</td>\n",
       "      <td>-59.523600</td>\n",
       "      <td>2020-05-27</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32289</th>\n",
       "      <td></td>\n",
       "      <td>Eswatini</td>\n",
       "      <td>-26.522500</td>\n",
       "      <td>31.465900</td>\n",
       "      <td>2020-05-22</td>\n",
       "      <td>225</td>\n",
       "      <td>2</td>\n",
       "      <td>119</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Province/State  Country/Region        Lat        Long  \\\n",
       "14404    Newfoundland and Labrador          Canada  53.135500  -57.660400   \n",
       "44499                      Tianjin           China  39.305400  117.323000   \n",
       "5966                     St Martin          France  18.070800  -63.050100   \n",
       "28933                                       Sweden  60.128161   18.643501   \n",
       "33773  Falkland Islands (Malvinas)  United Kingdom -51.796300  -59.523600   \n",
       "32289                                     Eswatini -26.522500   31.465900   \n",
       "\n",
       "            Date  Confirmed  Deaths  Recovered  Active  \n",
       "14404 2020-03-16          1       0          0       1  \n",
       "44499 2020-07-07        199       3        194       2  \n",
       "5966  2020-02-13          0       0          0       0  \n",
       "28933 2020-05-09      25921    3220          0   22701  \n",
       "33773 2020-05-27         13       0         13       0  \n",
       "32289 2020-05-22        225       2        119     104  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Active Case = confirmed - deaths - recovered\n",
    "full_table['Active'] = full_table['Confirmed'] - full_table['Deaths'] - full_table['Recovered']\n",
    "\n",
    "# filling missing values \n",
    "# ======================\n",
    "# fill missing province/state value with ''\n",
    "full_table[['Province/State']] = full_table[['Province/State']].fillna('')\n",
    "# fill missing numerical values with 0\n",
    "cols = ['Confirmed', 'Deaths', 'Recovered', 'Active']\n",
    "full_table[cols] = full_table[cols].fillna(0)\n",
    "\n",
    "# fixing datatypes\n",
    "full_table['Recovered'] = full_table['Recovered'].astype(int)\n",
    "\n",
    "# random rows\n",
    "full_table.sample(6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Fixing off data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# new values\n",
    "feb_12_conf = {'Hubei' : 34874}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# function to change value\n",
    "def change_val(date, ref_col, val_col, dtnry):\n",
    "    for key, val in dtnry.items():\n",
    "        full_table.loc[(full_table['Date']==date) & (full_table[ref_col]==key), val_col] = val"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# changing values\n",
    "change_val('2/12/20', 'Province/State', 'Confirmed', feb_12_conf)"
   ]
  },
  {
   "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",
       "      <th>Active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5648</th>\n",
       "      <td>Hubei</td>\n",
       "      <td>China</td>\n",
       "      <td>30.9756</td>\n",
       "      <td>112.2707</td>\n",
       "      <td>2020-02-12</td>\n",
       "      <td>34874</td>\n",
       "      <td>1068</td>\n",
       "      <td>2686</td>\n",
       "      <td>29612</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Province/State Country/Region      Lat      Long       Date  Confirmed  \\\n",
       "5648          Hubei          China  30.9756  112.2707 2020-02-12      34874   \n",
       "\n",
       "      Deaths  Recovered  Active  \n",
       "5648    1068       2686   29612  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checking values\n",
    "full_table[(full_table['Date']=='2/12/20') & (full_table['Province/State']=='Hubei')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Ship\n",
    "# ====\n",
    "\n",
    "# ship rows containing ships with COVID-19 reported cases\n",
    "ship_rows = full_table['Province/State'].str.contains('Grand Princess') | \\\n",
    "            full_table['Province/State'].str.contains('Diamond Princess') | \\\n",
    "            full_table['Country/Region'].str.contains('Diamond Princess') | \\\n",
    "            full_table['Country/Region'].str.contains('MS Zaandam')\n",
    "\n",
    "# ship\n",
    "ship = full_table[ship_rows]\n",
    "\n",
    "# Latest cases from the ships\n",
    "ship_latest = ship[ship['Date']==max(ship['Date'])]\n",
    "# ship_latest.style.background_gradient(cmap='Pastel1_r')\n",
    "\n",
    "# skipping rows with ships info\n",
    "full_table = full_table[~(ship_rows)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# WHO Region"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://en.wikipedia.org/wiki/WHO_regions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "who_region = {}\n",
    "\n",
    "# African Region AFRO\n",
    "afro = \"Algeria, Angola, Cabo Verde, Eswatini, Sao Tome and Principe, Benin, South Sudan, Western Sahara, Congo (Brazzaville), Congo (Kinshasa), Cote d'Ivoire, Botswana, Burkina Faso, Burundi, Cameroon, Cape Verde, Central African Republic, Chad, Comoros, Ivory Coast, Democratic Republic of the Congo, Equatorial Guinea, Eritrea, Ethiopia, Gabon, Gambia, Ghana, Guinea, Guinea-Bissau, Kenya, Lesotho, Liberia, Madagascar, Malawi, Mali, Mauritania, Mauritius, Mozambique, Namibia, Niger, Nigeria, Republic of the Congo, Rwanda, São Tomé and Príncipe, Senegal, Seychelles, Sierra Leone, Somalia, South Africa, Swaziland, Togo, Uganda, Tanzania, Zambia, Zimbabwe\"\n",
    "afro = [i.strip() for i in afro.split(',')]\n",
    "for i in afro:\n",
    "    who_region[i] = 'Africa'\n",
    "    \n",
    "# Region of the Americas PAHO\n",
    "paho = 'Antigua and Barbuda, Argentina, Bahamas, Barbados, Belize, Bolivia, Brazil, Canada, Chile, Colombia, Costa Rica, Cuba, Dominica, Dominican Republic, Ecuador, El Salvador, Grenada, Guatemala, Guyana, Haiti, Honduras, Jamaica, Mexico, Nicaragua, Panama, Paraguay, Peru, Saint Kitts and Nevis, Saint Lucia, Saint Vincent and the Grenadines, Suriname, Trinidad and Tobago, United States, US, Uruguay, Venezuela'\n",
    "paho = [i.strip() for i in paho.split(',')]\n",
    "for i in paho:\n",
    "    who_region[i] = 'Americas'\n",
    "\n",
    "# South-East Asia Region SEARO\n",
    "searo = 'Bangladesh, Bhutan, North Korea, India, Indonesia, Maldives, Myanmar, Burma, Nepal, Sri Lanka, Thailand, Timor-Leste'\n",
    "searo = [i.strip() for i in searo.split(',')]\n",
    "for i in searo:\n",
    "    who_region[i] = 'South-East Asia'\n",
    "\n",
    "# European Region EURO\n",
    "euro = 'Albania, Andorra, Greenland, Kosovo, Holy See, Liechtenstein, Armenia, Czechia, Austria, Azerbaijan, Belarus, Belgium, Bosnia and Herzegovina, Bulgaria, Croatia, Cyprus, Czech Republic, Denmark, Estonia, Finland, France, Georgia, Germany, Greece, Hungary, Iceland, Ireland, Israel, Italy, Kazakhstan, Kyrgyzstan, Latvia, Lithuania, Luxembourg, Malta, Monaco, Montenegro, Netherlands, North Macedonia, Norway, Poland, Portugal, Moldova, Romania, Russia, San Marino, Serbia, Slovakia, Slovenia, Spain, Sweden, Switzerland, Tajikistan, Turkey, Turkmenistan, Ukraine, United Kingdom, Uzbekistan'\n",
    "euro = [i.strip() for i in euro.split(',')]\n",
    "for i in euro:\n",
    "    who_region[i] = 'Europe'\n",
    "\n",
    "# Eastern Mediterranean Region EMRO\n",
    "emro = 'Afghanistan, Bahrain, Djibouti, Egypt, Iran, Iraq, Jordan, Kuwait, Lebanon, Libya, Morocco, Oman, Pakistan, Palestine, West Bank and Gaza, Qatar, Saudi Arabia, Somalia, Sudan, Syria, Tunisia, United Arab Emirates, Yemen'\n",
    "emro = [i.strip() for i in emro.split(',')]\n",
    "for i in emro:\n",
    "    who_region[i] = 'Eastern Mediterranean'\n",
    "\n",
    "# Western Pacific Region WPRO\n",
    "wpro = 'Australia, Brunei, Cambodia, China, Cook Islands, Fiji, Japan, Kiribati, Laos, Malaysia, Marshall Islands, Micronesia, Mongolia, Nauru, New Zealand, Niue, Palau, Papua New Guinea, Philippines, South Korea, Samoa, Singapore, Solomon Islands, Taiwan, Taiwan*, Tonga, Tuvalu, Vanuatu, Vietnam'\n",
    "wpro = [i.strip() for i in wpro.split(',')]\n",
    "for i in wpro:\n",
    "    who_region[i] = 'Western Pacific'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([], dtype=object)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add 'WHO Region' column\n",
    "full_table['WHO Region'] = full_table['Country/Region'].map(who_region)\n",
    "\n",
    "# find missing values\n",
    "full_table[full_table['WHO Region'].isna()]['Country/Region'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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",
       "      <th>WHO Region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>35463</th>\n",
       "      <td></td>\n",
       "      <td>Costa Rica</td>\n",
       "      <td>9.7489</td>\n",
       "      <td>-83.7534</td>\n",
       "      <td>2020-06-03</td>\n",
       "      <td>1157</td>\n",
       "      <td>10</td>\n",
       "      <td>685</td>\n",
       "      <td>462</td>\n",
       "      <td>Americas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40415</th>\n",
       "      <td>British Virgin Islands</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>18.4207</td>\n",
       "      <td>-64.6400</td>\n",
       "      <td>2020-06-21</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35552</th>\n",
       "      <td></td>\n",
       "      <td>North Macedonia</td>\n",
       "      <td>41.6086</td>\n",
       "      <td>21.7453</td>\n",
       "      <td>2020-06-03</td>\n",
       "      <td>2492</td>\n",
       "      <td>145</td>\n",
       "      <td>1605</td>\n",
       "      <td>742</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20679</th>\n",
       "      <td></td>\n",
       "      <td>Slovakia</td>\n",
       "      <td>48.6690</td>\n",
       "      <td>19.6990</td>\n",
       "      <td>2020-04-08</td>\n",
       "      <td>682</td>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>664</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26581</th>\n",
       "      <td></td>\n",
       "      <td>Burma</td>\n",
       "      <td>21.9162</td>\n",
       "      <td>95.9560</td>\n",
       "      <td>2020-04-30</td>\n",
       "      <td>151</td>\n",
       "      <td>6</td>\n",
       "      <td>27</td>\n",
       "      <td>118</td>\n",
       "      <td>South-East Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28038</th>\n",
       "      <td>French Polynesia</td>\n",
       "      <td>France</td>\n",
       "      <td>-17.6797</td>\n",
       "      <td>149.4068</td>\n",
       "      <td>2020-05-06</td>\n",
       "      <td>60</td>\n",
       "      <td>0</td>\n",
       "      <td>54</td>\n",
       "      <td>6</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Province/State   Country/Region      Lat      Long       Date  \\\n",
       "35463                               Costa Rica   9.7489  -83.7534 2020-06-03   \n",
       "40415  British Virgin Islands   United Kingdom  18.4207  -64.6400 2020-06-21   \n",
       "35552                          North Macedonia  41.6086   21.7453 2020-06-03   \n",
       "20679                                 Slovakia  48.6690   19.6990 2020-04-08   \n",
       "26581                                    Burma  21.9162   95.9560 2020-04-30   \n",
       "28038        French Polynesia           France -17.6797  149.4068 2020-05-06   \n",
       "\n",
       "       Confirmed  Deaths  Recovered  Active       WHO Region  \n",
       "35463       1157      10        685     462         Americas  \n",
       "40415          8       1          7       0           Europe  \n",
       "35552       2492     145       1605     742           Europe  \n",
       "20679        682       2         16     664           Europe  \n",
       "26581        151       6         27     118  South-East Asia  \n",
       "28038         60       0         54       6           Europe  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Cleaning data\n",
    "# =============\n",
    "\n",
    "# fixing Country values\n",
    "full_table.loc[full_table['Province/State']=='Greenland', 'Country/Region'] = 'Greenland'\n",
    "\n",
    "# Active Case = confirmed - deaths - recovered\n",
    "full_table['Active'] = full_table['Confirmed'] - full_table['Deaths'] - full_table['Recovered']\n",
    "\n",
    "# replacing Mainland china with just China\n",
    "full_table['Country/Region'] = full_table['Country/Region'].replace('Mainland China', 'China')\n",
    "\n",
    "# filling missing values \n",
    "full_table[['Province/State']] = full_table[['Province/State']].fillna('')\n",
    "full_table[['Confirmed', 'Deaths', 'Recovered', 'Active']] = full_table[['Confirmed', 'Deaths', 'Recovered', 'Active']].fillna(0)\n",
    "\n",
    "# fixing datatypes\n",
    "full_table['Recovered'] = full_table['Recovered'].astype(int)\n",
    "\n",
    "full_table.sample(6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Saving final data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save data\n",
    "full_table.to_csv('covid_19_clean_complete.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Full Grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\imdevskp\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:4: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  after removing the cwd from sys.path.\n",
      "C:\\Users\\imdevskp\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:7: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  import sys\n"
     ]
    },
    {
     "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</td>\n",
       "      <td>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</td>\n",
       "      <td>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</td>\n",
       "      <td>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</td>\n",
       "      <td>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</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date Country/Region  Confirmed  Deaths  Recovered  Active  New cases  \\\n",
       "0 2020-01-22    Afghanistan          0       0          0       0          0   \n",
       "1 2020-01-22        Albania          0       0          0       0          0   \n",
       "2 2020-01-22        Algeria          0       0          0       0          0   \n",
       "3 2020-01-22        Andorra          0       0          0       0          0   \n",
       "4 2020-01-22         Angola          0       0          0       0          0   \n",
       "\n",
       "   New deaths  New recovered  \n",
       "0           0              0  \n",
       "1           0              0  \n",
       "2           0              0  \n",
       "3           0              0  \n",
       "4           0              0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Grouped by day, country\n",
    "# =======================\n",
    "\n",
    "full_grouped = full_table.groupby(['Date', 'Country/Region'])['Confirmed', 'Deaths', 'Recovered', 'Active'].sum().reset_index()\n",
    "\n",
    "# new cases ======================================================\n",
    "temp = full_grouped.groupby(['Country/Region', 'Date', ])['Confirmed', 'Deaths', 'Recovered']\n",
    "temp = temp.sum().diff().reset_index()\n",
    "\n",
    "mask = temp['Country/Region'] != temp['Country/Region'].shift(1)\n",
    "\n",
    "temp.loc[mask, 'Confirmed'] = np.nan\n",
    "temp.loc[mask, 'Deaths'] = np.nan\n",
    "temp.loc[mask, 'Recovered'] = np.nan\n",
    "\n",
    "# renaming columns\n",
    "temp.columns = ['Country/Region', 'Date', 'New cases', 'New deaths', 'New recovered']\n",
    "# =================================================================\n",
    "\n",
    "# merging new values\n",
    "full_grouped = pd.merge(full_grouped, temp, on=['Country/Region', 'Date'])\n",
    "\n",
    "# filling na with 0\n",
    "full_grouped = full_grouped.fillna(0)\n",
    "\n",
    "# fixing data types\n",
    "cols = ['New cases', 'New deaths', 'New recovered']\n",
    "full_grouped[cols] = full_grouped[cols].astype('int')\n",
    "\n",
    "full_grouped['New cases'] = full_grouped['New cases'].apply(lambda x: 0 if x<0 else x)\n",
    "\n",
    "full_grouped.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([], dtype=object)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add 'WHO Region' column\n",
    "full_grouped['WHO Region'] = full_grouped['Country/Region'].map(who_region)\n",
    "\n",
    "# find missing values\n",
    "full_grouped[full_grouped['WHO Region'].isna()]['Country/Region'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save as .csv file\n",
    "full_grouped.to_csv('full_grouped.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Day wise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\imdevskp\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:5: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  \"\"\"\n"
     ]
    },
    {
     "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",
       "      <th>Active</th>\n",
       "      <th>New cases</th>\n",
       "      <th>New deaths</th>\n",
       "      <th>New recovered</th>\n",
       "      <th>Deaths / 100 Cases</th>\n",
       "      <th>Recovered / 100 Cases</th>\n",
       "      <th>Deaths / 100 Recovered</th>\n",
       "      <th>No. of countries</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>555</td>\n",
       "      <td>17</td>\n",
       "      <td>28</td>\n",
       "      <td>510</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.06</td>\n",
       "      <td>5.05</td>\n",
       "      <td>60.71</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>654</td>\n",
       "      <td>18</td>\n",
       "      <td>30</td>\n",
       "      <td>606</td>\n",
       "      <td>99</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2.75</td>\n",
       "      <td>4.59</td>\n",
       "      <td>60.00</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-24</td>\n",
       "      <td>941</td>\n",
       "      <td>26</td>\n",
       "      <td>36</td>\n",
       "      <td>879</td>\n",
       "      <td>287</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>2.76</td>\n",
       "      <td>3.83</td>\n",
       "      <td>72.22</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-25</td>\n",
       "      <td>1434</td>\n",
       "      <td>42</td>\n",
       "      <td>39</td>\n",
       "      <td>1353</td>\n",
       "      <td>493</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>2.93</td>\n",
       "      <td>2.72</td>\n",
       "      <td>107.69</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-26</td>\n",
       "      <td>2118</td>\n",
       "      <td>56</td>\n",
       "      <td>52</td>\n",
       "      <td>2010</td>\n",
       "      <td>684</td>\n",
       "      <td>14</td>\n",
       "      <td>13</td>\n",
       "      <td>2.64</td>\n",
       "      <td>2.46</td>\n",
       "      <td>107.69</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  Confirmed  Deaths  Recovered  Active  New cases  New deaths  \\\n",
       "0 2020-01-22        555      17         28     510          0           0   \n",
       "1 2020-01-23        654      18         30     606         99           1   \n",
       "2 2020-01-24        941      26         36     879        287           8   \n",
       "3 2020-01-25       1434      42         39    1353        493          16   \n",
       "4 2020-01-26       2118      56         52    2010        684          14   \n",
       "\n",
       "   New recovered  Deaths / 100 Cases  Recovered / 100 Cases  \\\n",
       "0              0                3.06                   5.05   \n",
       "1              2                2.75                   4.59   \n",
       "2              6                2.76                   3.83   \n",
       "3              3                2.93                   2.72   \n",
       "4             13                2.64                   2.46   \n",
       "\n",
       "   Deaths / 100 Recovered  No. of countries  \n",
       "0                   60.71                 6  \n",
       "1                   60.00                 8  \n",
       "2                   72.22                 9  \n",
       "3                  107.69                11  \n",
       "4                  107.69                13  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Day wise\n",
    "# ========\n",
    "\n",
    "# table\n",
    "day_wise = full_grouped.groupby('Date')['Confirmed', 'Deaths', 'Recovered', \n",
    "                                        'Active', 'New cases', 'New deaths', 'New recovered'].sum().reset_index()\n",
    "\n",
    "# number cases per 100 cases\n",
    "day_wise['Deaths / 100 Cases'] = round((day_wise['Deaths']/day_wise['Confirmed'])*100, 2)\n",
    "day_wise['Recovered / 100 Cases'] = round((day_wise['Recovered']/day_wise['Confirmed'])*100, 2)\n",
    "day_wise['Deaths / 100 Recovered'] = round((day_wise['Deaths']/day_wise['Recovered'])*100, 2)\n",
    "\n",
    "# no. of countries\n",
    "day_wise['No. of countries'] = full_grouped[full_grouped['Confirmed']!=0] \\\n",
    "                                    .groupby('Date')['Country/Region'] \\\n",
    "                                    .unique() \\\n",
    "                                    .apply(len)\\\n",
    "                                    .values\n",
    "\n",
    "# fillna by 0\n",
    "cols = ['Deaths / 100 Cases', 'Recovered / 100 Cases', 'Deaths / 100 Recovered']\n",
    "day_wise[cols] = day_wise[cols].fillna(0)\n",
    "\n",
    "day_wise.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save as .csv file\n",
    "day_wise.to_csv('day_wise.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Country wise latest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(187, 9)\n",
      "(187, 8)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\imdevskp\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:14: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  \n"
     ]
    },
    {
     "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>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",
       "      <th>Deaths / 100 Cases</th>\n",
       "      <th>Recovered / 100 Cases</th>\n",
       "      <th>Deaths / 100 Recovered</th>\n",
       "      <th>Confirmed last week</th>\n",
       "      <th>1 week change</th>\n",
       "      <th>1 week % increase</th>\n",
       "      <th>WHO Region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>36263</td>\n",
       "      <td>1269</td>\n",
       "      <td>25198</td>\n",
       "      <td>9796</td>\n",
       "      <td>106</td>\n",
       "      <td>10</td>\n",
       "      <td>18</td>\n",
       "      <td>3.50</td>\n",
       "      <td>69.49</td>\n",
       "      <td>5.04</td>\n",
       "      <td>35526</td>\n",
       "      <td>737</td>\n",
       "      <td>2.07</td>\n",
       "      <td>Eastern Mediterranean</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>4880</td>\n",
       "      <td>144</td>\n",
       "      <td>2745</td>\n",
       "      <td>1991</td>\n",
       "      <td>117</td>\n",
       "      <td>6</td>\n",
       "      <td>63</td>\n",
       "      <td>2.95</td>\n",
       "      <td>56.25</td>\n",
       "      <td>5.25</td>\n",
       "      <td>4171</td>\n",
       "      <td>709</td>\n",
       "      <td>17.00</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>27973</td>\n",
       "      <td>1163</td>\n",
       "      <td>18837</td>\n",
       "      <td>7973</td>\n",
       "      <td>616</td>\n",
       "      <td>8</td>\n",
       "      <td>749</td>\n",
       "      <td>4.16</td>\n",
       "      <td>67.34</td>\n",
       "      <td>6.17</td>\n",
       "      <td>23691</td>\n",
       "      <td>4282</td>\n",
       "      <td>18.07</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>907</td>\n",
       "      <td>52</td>\n",
       "      <td>803</td>\n",
       "      <td>52</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5.73</td>\n",
       "      <td>88.53</td>\n",
       "      <td>6.48</td>\n",
       "      <td>884</td>\n",
       "      <td>23</td>\n",
       "      <td>2.60</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Angola</td>\n",
       "      <td>950</td>\n",
       "      <td>41</td>\n",
       "      <td>242</td>\n",
       "      <td>667</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.32</td>\n",
       "      <td>25.47</td>\n",
       "      <td>16.94</td>\n",
       "      <td>749</td>\n",
       "      <td>201</td>\n",
       "      <td>26.84</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Country/Region  Confirmed  Deaths  Recovered  Active  New cases  New deaths  \\\n",
       "0    Afghanistan      36263    1269      25198    9796        106          10   \n",
       "1        Albania       4880     144       2745    1991        117           6   \n",
       "2        Algeria      27973    1163      18837    7973        616           8   \n",
       "3        Andorra        907      52        803      52         10           0   \n",
       "4         Angola        950      41        242     667         18           1   \n",
       "\n",
       "   New recovered  Deaths / 100 Cases  Recovered / 100 Cases  \\\n",
       "0             18                3.50                  69.49   \n",
       "1             63                2.95                  56.25   \n",
       "2            749                4.16                  67.34   \n",
       "3              0                5.73                  88.53   \n",
       "4              0                4.32                  25.47   \n",
       "\n",
       "   Deaths / 100 Recovered  Confirmed last week  1 week change  \\\n",
       "0                    5.04                35526            737   \n",
       "1                    5.25                 4171            709   \n",
       "2                    6.17                23691           4282   \n",
       "3                    6.48                  884             23   \n",
       "4                   16.94                  749            201   \n",
       "\n",
       "   1 week % increase             WHO Region  \n",
       "0               2.07  Eastern Mediterranean  \n",
       "1              17.00                 Europe  \n",
       "2              18.07                 Africa  \n",
       "3               2.60                 Europe  \n",
       "4              26.84                 Africa  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Country wise\n",
    "# ============\n",
    "\n",
    "full_grouped['Date'] = pd.to_datetime(full_grouped['Date'])\n",
    "\n",
    "# getting latest values\n",
    "country_wise = full_grouped[full_grouped['Date']==max(full_grouped['Date'])] \\\n",
    "                    .reset_index(drop=True) \\\n",
    "                    .drop('Date', axis=1)\n",
    "\n",
    "print(country_wise.shape)\n",
    "\n",
    "# group by country\n",
    "country_wise = country_wise.groupby('Country/Region')['Confirmed', 'Deaths', \n",
    "                                                      'Recovered', 'Active', \n",
    "                                                      'New cases', 'New deaths', 'New recovered'].sum().reset_index()\n",
    "print(country_wise.shape)\n",
    "\n",
    "\n",
    "# per 100 cases\n",
    "country_wise['Deaths / 100 Cases'] = round((country_wise['Deaths']/country_wise['Confirmed'])*100, 2)\n",
    "country_wise['Recovered / 100 Cases'] = round((country_wise['Recovered']/country_wise['Confirmed'])*100, 2)\n",
    "country_wise['Deaths / 100 Recovered'] = round((country_wise['Deaths']/country_wise['Recovered'])*100, 2)\n",
    "\n",
    "cols = ['Deaths / 100 Cases', 'Recovered / 100 Cases', 'Deaths / 100 Recovered']\n",
    "country_wise[cols] = country_wise[cols].fillna(0)\n",
    "\n",
    "\n",
    "# 1 week increase and % change\n",
    "# ============================\n",
    "\n",
    "today = full_grouped[full_grouped['Date']==max(full_grouped['Date'])] \\\n",
    "            .reset_index(drop=True) \\\n",
    "            .drop('Date', axis=1)[['Country/Region', 'Confirmed']]\n",
    "\n",
    "last_week = full_grouped[full_grouped['Date']==max(full_grouped['Date'])-timedelta(days=7)] \\\n",
    "                .reset_index(drop=True) \\\n",
    "                .drop('Date', axis=1)[['Country/Region', 'Confirmed']]\n",
    "\n",
    "temp = pd.merge(today, last_week, on='Country/Region', suffixes=(' today', ' last week'))\n",
    "temp['1 week change'] = temp['Confirmed today'] - temp['Confirmed last week']\n",
    "temp = temp[['Country/Region', 'Confirmed last week', '1 week change']]\n",
    "\n",
    "country_wise = pd.merge(country_wise, temp, on='Country/Region')\n",
    "country_wise['1 week % increase'] = round(country_wise['1 week change']/country_wise['Confirmed last week']*100, 2)\n",
    "country_wise.head()\n",
    "\n",
    "country_wise['WHO Region'] = country_wise['Country/Region'].map(who_region)\n",
    "country_wise[country_wise['WHO Region'].isna()]['Country/Region'].unique()\n",
    "\n",
    "country_wise.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save as .csv file\n",
    "country_wise.to_csv('country_wise_latest.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Country wise data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # china\n",
    "# # =====\n",
    "\n",
    "# china_province_wise = full_table[full_table['Country/Region']=='China']\n",
    "# china_province_wise['Province/State'].unique()\n",
    "# china_province_wise.to_csv('china_province_wise.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Australia\n",
    "# # =========\n",
    "\n",
    "# australia_state_wise = full_table[full_table['Country/Region']=='Australia']\n",
    "# australia_state_wise['Province/State'].unique()\n",
    "# australia_state_wise.to_csv('australia_state_wise.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Canada\n",
    "# # ======\n",
    "\n",
    "# canada_state_wise = full_table[full_table['Country/Region']=='Canada']\n",
    "# canada_state_wise['Province/State'].unique()\n",
    "# canada_state_wise.to_csv('canada_state_wise.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# USA data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100% [..........................................................................] 1761292 / 1761292"
     ]
    }
   ],
   "source": [
    "# download data\n",
    "# =============\n",
    "\n",
    "# urls of the files\n",
    "urls = ['https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_US.csv', \n",
    "        'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_US.csv']\n",
    "\n",
    "# download files\n",
    "for url in urls:\n",
    "    filename = wget.download(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read data\n",
    "# =========\n",
    "\n",
    "us_conf_df = pd.read_csv('time_series_covid19_confirmed_US.csv')\n",
    "us_deaths_df = pd.read_csv('time_series_covid19_deaths_US.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ids\n",
    "ids = us_conf_df.columns[0:11]\n",
    "# dates\n",
    "us_dates = us_conf_df.columns[11:]\n",
    "\n",
    "# melt to longer format\n",
    "us_conf_df_long = us_conf_df.melt(id_vars=ids, value_vars=us_dates, var_name='Date', value_name='Confirmed')\n",
    "us_deaths_df_long = us_deaths_df.melt(id_vars=ids, value_vars=us_dates, var_name='Date', value_name='Deaths')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>UID</th>\n",
       "      <th>iso2</th>\n",
       "      <th>iso3</th>\n",
       "      <th>code3</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>Admin2</th>\n",
       "      <th>Province_State</th>\n",
       "      <th>Country_Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>Combined_Key</th>\n",
       "      <th>Date</th>\n",
       "      <th>Confirmed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16</td>\n",
       "      <td>AS</td>\n",
       "      <td>ASM</td>\n",
       "      <td>16</td>\n",
       "      <td>60.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>American Samoa</td>\n",
       "      <td>US</td>\n",
       "      <td>-14.271000</td>\n",
       "      <td>-170.132000</td>\n",
       "      <td>American Samoa, US</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>316</td>\n",
       "      <td>GU</td>\n",
       "      <td>GUM</td>\n",
       "      <td>316</td>\n",
       "      <td>66.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Guam</td>\n",
       "      <td>US</td>\n",
       "      <td>13.444300</td>\n",
       "      <td>144.793700</td>\n",
       "      <td>Guam, US</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>580</td>\n",
       "      <td>MP</td>\n",
       "      <td>MNP</td>\n",
       "      <td>580</td>\n",
       "      <td>69.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Northern Mariana Islands</td>\n",
       "      <td>US</td>\n",
       "      <td>15.097900</td>\n",
       "      <td>145.673900</td>\n",
       "      <td>Northern Mariana Islands, US</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>63072001</td>\n",
       "      <td>PR</td>\n",
       "      <td>PRI</td>\n",
       "      <td>630</td>\n",
       "      <td>72001.0</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>US</td>\n",
       "      <td>18.180117</td>\n",
       "      <td>-66.754367</td>\n",
       "      <td>Adjuntas, Puerto Rico, US</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>63072003</td>\n",
       "      <td>PR</td>\n",
       "      <td>PRI</td>\n",
       "      <td>630</td>\n",
       "      <td>72003.0</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>US</td>\n",
       "      <td>18.360255</td>\n",
       "      <td>-67.175131</td>\n",
       "      <td>Aguada, Puerto Rico, US</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        UID iso2 iso3  code3     FIPS    Admin2            Province_State  \\\n",
       "0        16   AS  ASM     16     60.0       NaN            American Samoa   \n",
       "1       316   GU  GUM    316     66.0       NaN                      Guam   \n",
       "2       580   MP  MNP    580     69.0       NaN  Northern Mariana Islands   \n",
       "3  63072001   PR  PRI    630  72001.0  Adjuntas               Puerto Rico   \n",
       "4  63072003   PR  PRI    630  72003.0    Aguada               Puerto Rico   \n",
       "\n",
       "  Country_Region        Lat       Long_                  Combined_Key  \\\n",
       "0             US -14.271000 -170.132000            American Samoa, US   \n",
       "1             US  13.444300  144.793700                      Guam, US   \n",
       "2             US  15.097900  145.673900  Northern Mariana Islands, US   \n",
       "3             US  18.180117  -66.754367     Adjuntas, Puerto Rico, US   \n",
       "4             US  18.360255  -67.175131       Aguada, Puerto Rico, US   \n",
       "\n",
       "      Date  Confirmed  \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  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first few rows\n",
    "us_conf_df_long.head()"
   ]
  },
  {
   "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>UID</th>\n",
       "      <th>iso2</th>\n",
       "      <th>iso3</th>\n",
       "      <th>code3</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>Admin2</th>\n",
       "      <th>Province_State</th>\n",
       "      <th>Country_Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>Combined_Key</th>\n",
       "      <th>Date</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16</td>\n",
       "      <td>AS</td>\n",
       "      <td>ASM</td>\n",
       "      <td>16</td>\n",
       "      <td>60.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>American Samoa</td>\n",
       "      <td>US</td>\n",
       "      <td>-14.271000</td>\n",
       "      <td>-170.132000</td>\n",
       "      <td>American Samoa, US</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>316</td>\n",
       "      <td>GU</td>\n",
       "      <td>GUM</td>\n",
       "      <td>316</td>\n",
       "      <td>66.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Guam</td>\n",
       "      <td>US</td>\n",
       "      <td>13.444300</td>\n",
       "      <td>144.793700</td>\n",
       "      <td>Guam, US</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>580</td>\n",
       "      <td>MP</td>\n",
       "      <td>MNP</td>\n",
       "      <td>580</td>\n",
       "      <td>69.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Northern Mariana Islands</td>\n",
       "      <td>US</td>\n",
       "      <td>15.097900</td>\n",
       "      <td>145.673900</td>\n",
       "      <td>Northern Mariana Islands, US</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>63072001</td>\n",
       "      <td>PR</td>\n",
       "      <td>PRI</td>\n",
       "      <td>630</td>\n",
       "      <td>72001.0</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>US</td>\n",
       "      <td>18.180117</td>\n",
       "      <td>-66.754367</td>\n",
       "      <td>Adjuntas, Puerto Rico, US</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>63072003</td>\n",
       "      <td>PR</td>\n",
       "      <td>PRI</td>\n",
       "      <td>630</td>\n",
       "      <td>72003.0</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>US</td>\n",
       "      <td>18.360255</td>\n",
       "      <td>-67.175131</td>\n",
       "      <td>Aguada, Puerto Rico, US</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        UID iso2 iso3  code3     FIPS    Admin2            Province_State  \\\n",
       "0        16   AS  ASM     16     60.0       NaN            American Samoa   \n",
       "1       316   GU  GUM    316     66.0       NaN                      Guam   \n",
       "2       580   MP  MNP    580     69.0       NaN  Northern Mariana Islands   \n",
       "3  63072001   PR  PRI    630  72001.0  Adjuntas               Puerto Rico   \n",
       "4  63072003   PR  PRI    630  72003.0    Aguada               Puerto Rico   \n",
       "\n",
       "  Country_Region        Lat       Long_                  Combined_Key  \\\n",
       "0             US -14.271000 -170.132000            American Samoa, US   \n",
       "1             US  13.444300  144.793700                      Guam, US   \n",
       "2             US  15.097900  145.673900  Northern Mariana Islands, US   \n",
       "3             US  18.180117  -66.754367     Adjuntas, Puerto Rico, US   \n",
       "4             US  18.360255  -67.175131       Aguada, Puerto Rico, US   \n",
       "\n",
       "      Date  Confirmed  Deaths  \n",
       "0  1/22/20          0       0  \n",
       "1  1/22/20          0       0  \n",
       "2  1/22/20          0       0  \n",
       "3  1/22/20          0       0  \n",
       "4  1/22/20          0       0  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# concatenate dataframes\n",
    "us_full_table = pd.concat([us_conf_df_long, us_deaths_df_long[['Deaths']]], axis=1)\n",
    "\n",
    "#first few rows\n",
    "us_full_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save as .csv file\n",
    "us_full_table.to_csv('usa_county_wise.csv', 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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}