get_covid19_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Covid19:\n",
    "    def get_lookup_table(self):\n",
    "        uid_iso_fips_lookup_table = pd.read_csv(\"https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/UID_ISO_FIPS_LookUp_Table.csv\")\n",
    "        uid_iso_fips_lookup_table['Country_Region'] = uid_iso_fips_lookup_table['Country_Region'].str.replace('*', '')\n",
    "        uid_iso_fips_lookup_table['Combined_Key'] = uid_iso_fips_lookup_table['Combined_Key'].str.replace('*', '')\n",
    "        uid_iso_fips_lookup_table['Population'] = uid_iso_fips_lookup_table['Population'].astype('Int64')\n",
    "        split_series = uid_iso_fips_lookup_table['Combined_Key'].str.split(', ')\n",
    "        counties = []\n",
    "        states = []\n",
    "        for lst in split_series:\n",
    "            if len(lst) == 1:\n",
    "                counties.append(np.nan)\n",
    "                states.append(np.nan)\n",
    "            elif len(lst) == 2:\n",
    "                counties.append(np.nan)\n",
    "                states.append(lst[0])\n",
    "            elif len(lst) == 3:\n",
    "                counties.append(lst[0])\n",
    "                states.append(lst[1])\n",
    "        uid_iso_fips_lookup_table['Admin2'] = counties\n",
    "        uid_iso_fips_lookup_table['Province_State'] = states\n",
    "        uid_iso_fips_lookup_table = uid_iso_fips_lookup_table[['UID', 'Combined_Key',\n",
    "                                                               'iso2', 'iso3',\n",
    "                                                               'Country_Region', 'Province_State', 'Admin2',\n",
    "                                                               'Lat', 'Long_', 'Population']]\n",
    "        return uid_iso_fips_lookup_table\n",
    "\n",
    "    def get_daily_report(self, report_date):\n",
    "        self._report_date = report_date\n",
    "        try:\n",
    "            daily_report = pd.read_csv(\"https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/{}.csv\".format(report_date))\n",
    "            daily_report['Country_Region'] = daily_report['Country_Region'].str.replace('*', '')\n",
    "            daily_report['Combined_Key'] = daily_report['Combined_Key'].str.replace('*', '')\n",
    "            daily_report = daily_report.drop(labels=['Active', 'Lat', 'Long_', 'FIPS', 'Admin2', 'Province_State', 'Country_Region'], axis=1)\n",
    "            return daily_report[['Combined_Key', 'Last_Update', 'Confirmed', 'Deaths', 'Recovered', 'Incident_Rate', 'Case_Fatality_Ratio']]\n",
    "        except:\n",
    "            print(\"Wrong format or unavailable report date: {}.\".format(report_date))\n",
    "            print(\"Expecting mm-dd-yyyy format.\")\n",
    "    def get_time_series(self):\n",
    "        time_series_confirmed = pd.read_csv(\"https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv\")\n",
    "        time_series_deathes = pd.read_csv(\"https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv\")\n",
    "        #time_series_recovered = pd.read_csv(\"https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_recovered_global.csv\")\n",
    "        time_series_confirmed['Province/State'] = time_series_confirmed['Province/State'].fillna(time_series_confirmed['Country/Region'])\n",
    "        time_series_deathes['Province/State'] = time_series_deathes['Province/State'].fillna(time_series_deathes['Country/Region'])\n",
    "        #time_series_recovered['Province/State'] = time_series_recovered['Province/State'].fillna(time_series_recovered['Country/Region'])\n",
    "        time_series_confirmed = time_series_confirmed.drop(labels=['Lat', 'Long'], axis=1)\n",
    "        time_series_deathes = time_series_deathes.drop(labels=['Lat', 'Long'], axis=1)\n",
    "        #time_series_recovered = time_series_recovered.drop(labels=['Lat', 'Long'], axis=1)\n",
    "        time_series_confirmed_long = pd.melt(time_series_confirmed, id_vars=['Province/State', 'Country/Region'], var_name='Date', value_name='Confirmed')\n",
    "        time_series_deathes_long = pd.melt(time_series_deathes, id_vars=['Province/State', 'Country/Region'], var_name='Date', value_name='Deaths')\n",
    "        #time_series_recovered_long = pd.melt(time_series_recovered, id_vars=['Province/State', 'Country/Region'], var_name='Date', value_name='Recovered')\n",
    "        time_series = time_series_confirmed_long\n",
    "        time_series['Deaths'] = time_series_deathes_long['Deaths']\n",
    "        #time_series['Recovered'] = time_series_recovered_long['Recovered']\n",
    "        time_series['Date'] = pd.to_datetime(time_series['Date'])\n",
    "        time_series = time_series[time_series['Date'] <= pd.to_datetime(self._report_date)]\n",
    "        date_series = time_series['Date'].dt.strftime('%Y-%m-%d')\n",
    "        time_series = time_series.drop('Date', axis=1)\n",
    "        time_series['Date'] = date_series\n",
    "        time_series['Country/Region'] = time_series['Country/Region'].str.replace('*', '')\n",
    "        groupby_date_country = time_series.groupby(['Date', 'Country/Region'])\n",
    "        time_series = groupby_date_country[['Confirmed', 'Deaths']].sum().reset_index()\n",
    "        time_series.columns = ['Date', 'Country_Region', 'Confirmed', 'Deaths']\n",
    "        confirmed_shifted = time_series.groupby('Country_Region')['Confirmed'].shift(1, fill_value=0)\n",
    "        deaths_shifted = time_series.groupby('Country_Region')['Deaths'].shift(1, fill_value=0)\n",
    "        daily_cases = time_series['Confirmed'] - confirmed_shifted\n",
    "        daily_deaths = time_series['Deaths'] - deaths_shifted\n",
    "        n_cols = time_series.shape[1]\n",
    "        time_series.insert(n_cols, 'Daily_Cases', daily_cases)\n",
    "        n_cols = time_series.shape[1]\n",
    "        time_series.insert(n_cols, 'Daily_Deaths', daily_deaths)\n",
    "        return time_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "covid19 = Covid19()\n",
    "lookup_table = covid19.get_lookup_table()\n",
    "daily_report = covid19.get_daily_report('03-31-2021')\n",
    "time_series = covid19.get_time_series()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>Combined_Key</th>\n",
       "      <th>iso2</th>\n",
       "      <th>iso3</th>\n",
       "      <th>Country_Region</th>\n",
       "      <th>Province_State</th>\n",
       "      <th>Admin2</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AF</td>\n",
       "      <td>AFG</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33.939110</td>\n",
       "      <td>67.709953</td>\n",
       "      <td>38928341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>Albania</td>\n",
       "      <td>AL</td>\n",
       "      <td>ALB</td>\n",
       "      <td>Albania</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41.153300</td>\n",
       "      <td>20.168300</td>\n",
       "      <td>2877800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>DZ</td>\n",
       "      <td>DZA</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.033900</td>\n",
       "      <td>1.659600</td>\n",
       "      <td>43851043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>AD</td>\n",
       "      <td>AND</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42.506300</td>\n",
       "      <td>1.521800</td>\n",
       "      <td>77265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>24</td>\n",
       "      <td>Angola</td>\n",
       "      <td>AO</td>\n",
       "      <td>AGO</td>\n",
       "      <td>Angola</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-11.202700</td>\n",
       "      <td>17.873900</td>\n",
       "      <td>32866268</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4170</th>\n",
       "      <td>84056037</td>\n",
       "      <td>Sweetwater, Wyoming, US</td>\n",
       "      <td>US</td>\n",
       "      <td>USA</td>\n",
       "      <td>US</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Sweetwater</td>\n",
       "      <td>41.659439</td>\n",
       "      <td>-108.882788</td>\n",
       "      <td>42343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4171</th>\n",
       "      <td>84056039</td>\n",
       "      <td>Teton, Wyoming, US</td>\n",
       "      <td>US</td>\n",
       "      <td>USA</td>\n",
       "      <td>US</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton</td>\n",
       "      <td>43.935225</td>\n",
       "      <td>-110.589080</td>\n",
       "      <td>23464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4172</th>\n",
       "      <td>84056041</td>\n",
       "      <td>Uinta, Wyoming, US</td>\n",
       "      <td>US</td>\n",
       "      <td>USA</td>\n",
       "      <td>US</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Uinta</td>\n",
       "      <td>41.287818</td>\n",
       "      <td>-110.547578</td>\n",
       "      <td>20226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4173</th>\n",
       "      <td>84056043</td>\n",
       "      <td>Washakie, Wyoming, US</td>\n",
       "      <td>US</td>\n",
       "      <td>USA</td>\n",
       "      <td>US</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Washakie</td>\n",
       "      <td>43.904516</td>\n",
       "      <td>-107.680187</td>\n",
       "      <td>7805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4174</th>\n",
       "      <td>84056045</td>\n",
       "      <td>Weston, Wyoming, US</td>\n",
       "      <td>US</td>\n",
       "      <td>USA</td>\n",
       "      <td>US</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston</td>\n",
       "      <td>43.839612</td>\n",
       "      <td>-104.567488</td>\n",
       "      <td>6927</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4175 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           UID             Combined_Key iso2 iso3 Country_Region  \\\n",
       "0            4              Afghanistan   AF  AFG    Afghanistan   \n",
       "1            8                  Albania   AL  ALB        Albania   \n",
       "2           12                  Algeria   DZ  DZA        Algeria   \n",
       "3           20                  Andorra   AD  AND        Andorra   \n",
       "4           24                   Angola   AO  AGO         Angola   \n",
       "...        ...                      ...  ...  ...            ...   \n",
       "4170  84056037  Sweetwater, Wyoming, US   US  USA             US   \n",
       "4171  84056039       Teton, Wyoming, US   US  USA             US   \n",
       "4172  84056041       Uinta, Wyoming, US   US  USA             US   \n",
       "4173  84056043    Washakie, Wyoming, US   US  USA             US   \n",
       "4174  84056045      Weston, Wyoming, US   US  USA             US   \n",
       "\n",
       "     Province_State      Admin2        Lat       Long_  Population  \n",
       "0               NaN         NaN  33.939110   67.709953    38928341  \n",
       "1               NaN         NaN  41.153300   20.168300     2877800  \n",
       "2               NaN         NaN  28.033900    1.659600    43851043  \n",
       "3               NaN         NaN  42.506300    1.521800       77265  \n",
       "4               NaN         NaN -11.202700   17.873900    32866268  \n",
       "...             ...         ...        ...         ...         ...  \n",
       "4170        Wyoming  Sweetwater  41.659439 -108.882788       42343  \n",
       "4171        Wyoming       Teton  43.935225 -110.589080       23464  \n",
       "4172        Wyoming       Uinta  41.287818 -110.547578       20226  \n",
       "4173        Wyoming    Washakie  43.904516 -107.680187        7805  \n",
       "4174        Wyoming      Weston  43.839612 -104.567488        6927  \n",
       "\n",
       "[4175 rows x 10 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lookup_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Combined_Key</th>\n",
       "      <th>Last_Update</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "      <th>Incident_Rate</th>\n",
       "      <th>Case_Fatality_Ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2021-04-01 04:27:05</td>\n",
       "      <td>56454</td>\n",
       "      <td>2484</td>\n",
       "      <td>51550.0</td>\n",
       "      <td>145.020308</td>\n",
       "      <td>4.400043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2021-04-01 04:27:05</td>\n",
       "      <td>125157</td>\n",
       "      <td>2235</td>\n",
       "      <td>91271.0</td>\n",
       "      <td>4349.051359</td>\n",
       "      <td>1.785757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2021-04-01 04:27:05</td>\n",
       "      <td>117192</td>\n",
       "      <td>3093</td>\n",
       "      <td>81538.0</td>\n",
       "      <td>267.250200</td>\n",
       "      <td>2.639259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>2021-04-01 04:27:05</td>\n",
       "      <td>12010</td>\n",
       "      <td>115</td>\n",
       "      <td>11315.0</td>\n",
       "      <td>15543.907332</td>\n",
       "      <td>0.957535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Angola</td>\n",
       "      <td>2021-04-01 04:27:05</td>\n",
       "      <td>22311</td>\n",
       "      <td>537</td>\n",
       "      <td>20493.0</td>\n",
       "      <td>67.884191</td>\n",
       "      <td>2.406884</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3976</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2021-04-01 04:27:05</td>\n",
       "      <td>2603</td>\n",
       "      <td>35</td>\n",
       "      <td>2359.0</td>\n",
       "      <td>2.674171</td>\n",
       "      <td>1.344602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3977</th>\n",
       "      <td>West Bank and Gaza</td>\n",
       "      <td>2021-04-01 04:27:05</td>\n",
       "      <td>242353</td>\n",
       "      <td>2627</td>\n",
       "      <td>215429.0</td>\n",
       "      <td>4750.700590</td>\n",
       "      <td>1.083956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3978</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>2021-04-01 04:27:05</td>\n",
       "      <td>4357</td>\n",
       "      <td>888</td>\n",
       "      <td>1676.0</td>\n",
       "      <td>14.608076</td>\n",
       "      <td>20.380996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3979</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>2021-04-01 04:27:05</td>\n",
       "      <td>88418</td>\n",
       "      <td>1208</td>\n",
       "      <td>84592.0</td>\n",
       "      <td>480.951978</td>\n",
       "      <td>1.366238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3980</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2021-04-01 04:27:05</td>\n",
       "      <td>36882</td>\n",
       "      <td>1523</td>\n",
       "      <td>34686.0</td>\n",
       "      <td>248.147623</td>\n",
       "      <td>4.129386</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3981 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Combined_Key          Last_Update  Confirmed  Deaths  Recovered  \\\n",
       "0            Afghanistan  2021-04-01 04:27:05      56454    2484    51550.0   \n",
       "1                Albania  2021-04-01 04:27:05     125157    2235    91271.0   \n",
       "2                Algeria  2021-04-01 04:27:05     117192    3093    81538.0   \n",
       "3                Andorra  2021-04-01 04:27:05      12010     115    11315.0   \n",
       "4                 Angola  2021-04-01 04:27:05      22311     537    20493.0   \n",
       "...                  ...                  ...        ...     ...        ...   \n",
       "3976             Vietnam  2021-04-01 04:27:05       2603      35     2359.0   \n",
       "3977  West Bank and Gaza  2021-04-01 04:27:05     242353    2627   215429.0   \n",
       "3978               Yemen  2021-04-01 04:27:05       4357     888     1676.0   \n",
       "3979              Zambia  2021-04-01 04:27:05      88418    1208    84592.0   \n",
       "3980            Zimbabwe  2021-04-01 04:27:05      36882    1523    34686.0   \n",
       "\n",
       "      Incident_Rate  Case_Fatality_Ratio  \n",
       "0        145.020308             4.400043  \n",
       "1       4349.051359             1.785757  \n",
       "2        267.250200             2.639259  \n",
       "3      15543.907332             0.957535  \n",
       "4         67.884191             2.406884  \n",
       "...             ...                  ...  \n",
       "3976       2.674171             1.344602  \n",
       "3977    4750.700590             1.083956  \n",
       "3978      14.608076            20.380996  \n",
       "3979     480.951978             1.366238  \n",
       "3980     248.147623             4.129386  \n",
       "\n",
       "[3981 rows x 7 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_report"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Daily_Cases</th>\n",
       "      <th>Daily_Deaths</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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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>83515</th>\n",
       "      <td>2021-03-31</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2603</td>\n",
       "      <td>35</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83516</th>\n",
       "      <td>2021-03-31</td>\n",
       "      <td>West Bank and Gaza</td>\n",
       "      <td>242353</td>\n",
       "      <td>2627</td>\n",
       "      <td>2288</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83517</th>\n",
       "      <td>2021-03-31</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>4357</td>\n",
       "      <td>888</td>\n",
       "      <td>110</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83518</th>\n",
       "      <td>2021-03-31</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>88418</td>\n",
       "      <td>1208</td>\n",
       "      <td>219</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83519</th>\n",
       "      <td>2021-03-31</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>36882</td>\n",
       "      <td>1523</td>\n",
       "      <td>43</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>83520 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Date      Country_Region  Confirmed  Deaths  Daily_Cases  \\\n",
       "0      2020-01-22         Afghanistan          0       0            0   \n",
       "1      2020-01-22             Albania          0       0            0   \n",
       "2      2020-01-22             Algeria          0       0            0   \n",
       "3      2020-01-22             Andorra          0       0            0   \n",
       "4      2020-01-22              Angola          0       0            0   \n",
       "...           ...                 ...        ...     ...          ...   \n",
       "83515  2021-03-31             Vietnam       2603      35            9   \n",
       "83516  2021-03-31  West Bank and Gaza     242353    2627         2288   \n",
       "83517  2021-03-31               Yemen       4357     888          110   \n",
       "83518  2021-03-31              Zambia      88418    1208          219   \n",
       "83519  2021-03-31            Zimbabwe      36882    1523           43   \n",
       "\n",
       "       Daily_Deaths  \n",
       "0                 0  \n",
       "1                 0  \n",
       "2                 0  \n",
       "3                 0  \n",
       "4                 0  \n",
       "...             ...  \n",
       "83515             0  \n",
       "83516            13  \n",
       "83517             6  \n",
       "83518             6  \n",
       "83519             3  \n",
       "\n",
       "[83520 rows x 6 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#lookup_table.to_csv('lookup_table.csv', index=False)\n",
    "#daily_report.to_csv('daily_report.csv', index=False)\n",
    "#time_series.to_csv('time_series.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('covid19.db')\n",
    "lookup_table.to_sql('lookup_table', conn, if_exists='replace', index=False)\n",
    "daily_report.to_sql('daily_report', conn, if_exists='replace', index=False)\n",
    "time_series.to_sql('time_series', conn, if_exists='replace', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python Data Analysis",
   "language": "python",
   "name": "pyda"
  },
  "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}