covid_viz.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import tqdm.notebook\n",
    "import util"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "## Load data downloaded from CSSE repository\n",
    "- [COVID-19 Data Repository by the Center for Systems Science and Engineering (CSSE)](https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data/csse_covid_19_daily_reports)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1025"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_list = util.get_data_list('data/')\n",
    "len(data_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 1024/1024 [01:25<00:00, 12.01it/s]\n"
     ]
    }
   ],
   "source": [
    "df_all = util.extract_confirmed(data_list[0])\n",
    "for filepath in tqdm.tqdm(data_list[1:]):\n",
    "    df = util.extract_confirmed(filepath)\n",
    "    df_all = pd.merge(df_all, df, on='Country_Region', how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "## Process data"
   ]
  },
  {
   "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>Country_Region</th>\n",
       "      <th>2022.01.21</th>\n",
       "      <th>2020.02.26</th>\n",
       "      <th>2022.01.20</th>\n",
       "      <th>2020.02.27</th>\n",
       "      <th>2021.07.04</th>\n",
       "      <th>2021.07.05</th>\n",
       "      <th>2022.02.12</th>\n",
       "      <th>2022.02.13</th>\n",
       "      <th>2021.12.31</th>\n",
       "      <th>...</th>\n",
       "      <th>2021.10.12</th>\n",
       "      <th>2021.07.29</th>\n",
       "      <th>2021.09.30</th>\n",
       "      <th>2021.07.28</th>\n",
       "      <th>2022.03.03</th>\n",
       "      <th>2022.03.02</th>\n",
       "      <th>2021.05.26</th>\n",
       "      <th>2021.05.27</th>\n",
       "      <th>2021.06.15</th>\n",
       "      <th>2021.06.14</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>159516.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>159303.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>124748.0</td>\n",
       "      <td>125937.0</td>\n",
       "      <td>170152.0</td>\n",
       "      <td>170604.0</td>\n",
       "      <td>158084.0</td>\n",
       "      <td>...</td>\n",
       "      <td>155599.0</td>\n",
       "      <td>145996.0</td>\n",
       "      <td>155174.0</td>\n",
       "      <td>145552.0</td>\n",
       "      <td>174214.0</td>\n",
       "      <td>174073.0</td>\n",
       "      <td>67743.0</td>\n",
       "      <td>68366.0</td>\n",
       "      <td>93272.0</td>\n",
       "      <td>91458.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>244182.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>241512.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>132535.0</td>\n",
       "      <td>132537.0</td>\n",
       "      <td>267551.0</td>\n",
       "      <td>268008.0</td>\n",
       "      <td>210224.0</td>\n",
       "      <td>...</td>\n",
       "      <td>175664.0</td>\n",
       "      <td>132999.0</td>\n",
       "      <td>170131.0</td>\n",
       "      <td>132952.0</td>\n",
       "      <td>271825.0</td>\n",
       "      <td>271825.0</td>\n",
       "      <td>132244.0</td>\n",
       "      <td>132264.0</td>\n",
       "      <td>132469.0</td>\n",
       "      <td>132461.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>232325.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>230470.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>141471.0</td>\n",
       "      <td>141966.0</td>\n",
       "      <td>261226.0</td>\n",
       "      <td>261752.0</td>\n",
       "      <td>218432.0</td>\n",
       "      <td>...</td>\n",
       "      <td>204790.0</td>\n",
       "      <td>168668.0</td>\n",
       "      <td>203359.0</td>\n",
       "      <td>167131.0</td>\n",
       "      <td>265130.0</td>\n",
       "      <td>265079.0</td>\n",
       "      <td>127646.0</td>\n",
       "      <td>127926.0</td>\n",
       "      <td>134115.0</td>\n",
       "      <td>133742.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>33025.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32201.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13918.0</td>\n",
       "      <td>13918.0</td>\n",
       "      <td>37140.0</td>\n",
       "      <td>37140.0</td>\n",
       "      <td>23740.0</td>\n",
       "      <td>...</td>\n",
       "      <td>15307.0</td>\n",
       "      <td>14655.0</td>\n",
       "      <td>15222.0</td>\n",
       "      <td>14586.0</td>\n",
       "      <td>38342.0</td>\n",
       "      <td>38249.0</td>\n",
       "      <td>13671.0</td>\n",
       "      <td>13682.0</td>\n",
       "      <td>13828.0</td>\n",
       "      <td>13826.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Angola</td>\n",
       "      <td>95676.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95220.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>39230.0</td>\n",
       "      <td>39300.0</td>\n",
       "      <td>98514.0</td>\n",
       "      <td>98514.0</td>\n",
       "      <td>81593.0</td>\n",
       "      <td>...</td>\n",
       "      <td>61794.0</td>\n",
       "      <td>42486.0</td>\n",
       "      <td>56583.0</td>\n",
       "      <td>42288.0</td>\n",
       "      <td>98746.0</td>\n",
       "      <td>98746.0</td>\n",
       "      <td>33338.0</td>\n",
       "      <td>33607.0</td>\n",
       "      <td>36921.0</td>\n",
       "      <td>36790.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242</th>\n",
       "      <td>Curacao</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>Cape Verde</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>East Timor</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>St. Martin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>Republic of Ireland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>247 rows × 1026 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Country_Region  2022.01.21  2020.02.26  2022.01.20  2020.02.27  \\\n",
       "0            Afghanistan    159516.0         5.0    159303.0         5.0   \n",
       "1                Albania    244182.0         NaN    241512.0         NaN   \n",
       "2                Algeria    232325.0         1.0    230470.0         1.0   \n",
       "3                Andorra     33025.0         NaN     32201.0         NaN   \n",
       "4                 Angola     95676.0         NaN     95220.0         NaN   \n",
       "..                   ...         ...         ...         ...         ...   \n",
       "242              Curacao         NaN         NaN         NaN         NaN   \n",
       "243           Cape Verde         NaN         NaN         NaN         NaN   \n",
       "244           East Timor         NaN         NaN         NaN         NaN   \n",
       "245           St. Martin         NaN         NaN         NaN         NaN   \n",
       "246  Republic of Ireland         NaN         NaN         NaN         NaN   \n",
       "\n",
       "     2021.07.04  2021.07.05  2022.02.12  2022.02.13  2021.12.31  ...  \\\n",
       "0      124748.0    125937.0    170152.0    170604.0    158084.0  ...   \n",
       "1      132535.0    132537.0    267551.0    268008.0    210224.0  ...   \n",
       "2      141471.0    141966.0    261226.0    261752.0    218432.0  ...   \n",
       "3       13918.0     13918.0     37140.0     37140.0     23740.0  ...   \n",
       "4       39230.0     39300.0     98514.0     98514.0     81593.0  ...   \n",
       "..          ...         ...         ...         ...         ...  ...   \n",
       "242         NaN         NaN         NaN         NaN         NaN  ...   \n",
       "243         NaN         NaN         NaN         NaN         NaN  ...   \n",
       "244         NaN         NaN         NaN         NaN         NaN  ...   \n",
       "245         NaN         NaN         NaN         NaN         NaN  ...   \n",
       "246         NaN         NaN         NaN         NaN         NaN  ...   \n",
       "\n",
       "     2021.10.12  2021.07.29  2021.09.30  2021.07.28  2022.03.03  2022.03.02  \\\n",
       "0      155599.0    145996.0    155174.0    145552.0    174214.0    174073.0   \n",
       "1      175664.0    132999.0    170131.0    132952.0    271825.0    271825.0   \n",
       "2      204790.0    168668.0    203359.0    167131.0    265130.0    265079.0   \n",
       "3       15307.0     14655.0     15222.0     14586.0     38342.0     38249.0   \n",
       "4       61794.0     42486.0     56583.0     42288.0     98746.0     98746.0   \n",
       "..          ...         ...         ...         ...         ...         ...   \n",
       "242         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "243         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "244         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "245         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "246         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "\n",
       "     2021.05.26  2021.05.27  2021.06.15  2021.06.14  \n",
       "0       67743.0     68366.0     93272.0     91458.0  \n",
       "1      132244.0    132264.0    132469.0    132461.0  \n",
       "2      127646.0    127926.0    134115.0    133742.0  \n",
       "3       13671.0     13682.0     13828.0     13826.0  \n",
       "4       33338.0     33607.0     36921.0     36790.0  \n",
       "..          ...         ...         ...         ...  \n",
       "242         NaN         NaN         NaN         NaN  \n",
       "243         NaN         NaN         NaN         NaN  \n",
       "244         NaN         NaN         NaN         NaN  \n",
       "245         NaN         NaN         NaN         NaN  \n",
       "246         NaN         NaN         NaN         NaN  \n",
       "\n",
       "[247 rows x 1026 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all"
   ]
  },
  {
   "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>Country_Region</th>\n",
       "      <th>2020.01.22</th>\n",
       "      <th>2020.01.23</th>\n",
       "      <th>2020.01.24</th>\n",
       "      <th>2020.01.25</th>\n",
       "      <th>2020.01.26</th>\n",
       "      <th>2020.01.27</th>\n",
       "      <th>2020.01.28</th>\n",
       "      <th>2020.01.29</th>\n",
       "      <th>2020.01.30</th>\n",
       "      <th>...</th>\n",
       "      <th>2022.11.02</th>\n",
       "      <th>2022.11.03</th>\n",
       "      <th>2022.11.04</th>\n",
       "      <th>2022.11.05</th>\n",
       "      <th>2022.11.06</th>\n",
       "      <th>2022.11.07</th>\n",
       "      <th>2022.11.08</th>\n",
       "      <th>2022.11.09</th>\n",
       "      <th>2022.11.10</th>\n",
       "      <th>2022.11.11</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>203265.0</td>\n",
       "      <td>203395.0</td>\n",
       "      <td>203497.0</td>\n",
       "      <td>203574.0</td>\n",
       "      <td>203681.0</td>\n",
       "      <td>203829.0</td>\n",
       "      <td>203942.0</td>\n",
       "      <td>204094.0</td>\n",
       "      <td>204287.0</td>\n",
       "      <td>204392.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>332996.0</td>\n",
       "      <td>332996.0</td>\n",
       "      <td>333027.0</td>\n",
       "      <td>333046.0</td>\n",
       "      <td>333055.0</td>\n",
       "      <td>333058.0</td>\n",
       "      <td>333071.0</td>\n",
       "      <td>333088.0</td>\n",
       "      <td>333103.0</td>\n",
       "      <td>333125.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>270840.0</td>\n",
       "      <td>270847.0</td>\n",
       "      <td>270856.0</td>\n",
       "      <td>270862.0</td>\n",
       "      <td>270873.0</td>\n",
       "      <td>270881.0</td>\n",
       "      <td>270891.0</td>\n",
       "      <td>270906.0</td>\n",
       "      <td>270917.0</td>\n",
       "      <td>270924.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46664.0</td>\n",
       "      <td>46664.0</td>\n",
       "      <td>46664.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Angola</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242</th>\n",
       "      <td>Curacao</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>Cape Verde</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>East Timor</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>St. Martin</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>Republic of Ireland</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>247 rows × 1026 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Country_Region  2020.01.22  2020.01.23  2020.01.24  2020.01.25  \\\n",
       "0            Afghanistan         0.0         0.0         0.0         0.0   \n",
       "1                Albania         0.0         0.0         0.0         0.0   \n",
       "2                Algeria         0.0         0.0         0.0         0.0   \n",
       "3                Andorra         0.0         0.0         0.0         0.0   \n",
       "4                 Angola         0.0         0.0         0.0         0.0   \n",
       "..                   ...         ...         ...         ...         ...   \n",
       "242              Curacao         0.0         0.0         0.0         0.0   \n",
       "243           Cape Verde         0.0         0.0         0.0         0.0   \n",
       "244           East Timor         0.0         0.0         0.0         0.0   \n",
       "245           St. Martin         0.0         0.0         0.0         0.0   \n",
       "246  Republic of Ireland         0.0         0.0         0.0         0.0   \n",
       "\n",
       "     2020.01.26  2020.01.27  2020.01.28  2020.01.29  2020.01.30  ...  \\\n",
       "0           0.0         0.0         0.0         0.0         0.0  ...   \n",
       "1           0.0         0.0         0.0         0.0         0.0  ...   \n",
       "2           0.0         0.0         0.0         0.0         0.0  ...   \n",
       "3           0.0         0.0         0.0         0.0         0.0  ...   \n",
       "4           0.0         0.0         0.0         0.0         0.0  ...   \n",
       "..          ...         ...         ...         ...         ...  ...   \n",
       "242         0.0         0.0         0.0         0.0         0.0  ...   \n",
       "243         0.0         0.0         0.0         0.0         0.0  ...   \n",
       "244         0.0         0.0         0.0         0.0         0.0  ...   \n",
       "245         0.0         0.0         0.0         0.0         0.0  ...   \n",
       "246         0.0         0.0         0.0         0.0         0.0  ...   \n",
       "\n",
       "     2022.11.02  2022.11.03  2022.11.04  2022.11.05  2022.11.06  2022.11.07  \\\n",
       "0      203265.0    203395.0    203497.0    203574.0    203681.0    203829.0   \n",
       "1      332996.0    332996.0    333027.0    333046.0    333055.0    333058.0   \n",
       "2      270840.0    270847.0    270856.0    270862.0    270873.0    270881.0   \n",
       "3       46588.0     46588.0     46588.0     46588.0     46588.0     46588.0   \n",
       "4      103131.0    103131.0    103131.0    103131.0    103131.0    103131.0   \n",
       "..          ...         ...         ...         ...         ...         ...   \n",
       "242         0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "243         0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "244         0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "245         0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "246         0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "\n",
       "     2022.11.08  2022.11.09  2022.11.10  2022.11.11  \n",
       "0      203942.0    204094.0    204287.0    204392.0  \n",
       "1      333071.0    333088.0    333103.0    333125.0  \n",
       "2      270891.0    270906.0    270917.0    270924.0  \n",
       "3       46588.0     46664.0     46664.0     46664.0  \n",
       "4      103131.0    103131.0    103131.0    103131.0  \n",
       "..          ...         ...         ...         ...  \n",
       "242         0.0         0.0         0.0         0.0  \n",
       "243         0.0         0.0         0.0         0.0  \n",
       "244         0.0         0.0         0.0         0.0  \n",
       "245         0.0         0.0         0.0         0.0  \n",
       "246         0.0         0.0         0.0         0.0  \n",
       "\n",
       "[247 rows x 1026 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df_all.copy().fillna(0)\n",
    "df = df.reindex(sorted(df_all.columns[1:]), axis=1)\n",
    "df.insert(\n",
    "        loc=0, \n",
    "        column='Country_Region', \n",
    "        value=df_all['Country_Region']\n",
    "    )\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('intermediate/processed_data.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "## Retrieve country flags\n",
    "- [countryflags](https://www.countryflags.com/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('intermediate/processed_data.csv', index_col=0)"
   ]
  },
  {
   "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>Country_Region</th>\n",
       "      <th>flag_url</th>\n",
       "      <th>2020.01.22</th>\n",
       "      <th>2020.01.23</th>\n",
       "      <th>2020.01.24</th>\n",
       "      <th>2020.01.25</th>\n",
       "      <th>2020.01.26</th>\n",
       "      <th>2020.01.27</th>\n",
       "      <th>2020.01.28</th>\n",
       "      <th>2020.01.29</th>\n",
       "      <th>...</th>\n",
       "      <th>2022.11.02</th>\n",
       "      <th>2022.11.03</th>\n",
       "      <th>2022.11.04</th>\n",
       "      <th>2022.11.05</th>\n",
       "      <th>2022.11.06</th>\n",
       "      <th>2022.11.07</th>\n",
       "      <th>2022.11.08</th>\n",
       "      <th>2022.11.09</th>\n",
       "      <th>2022.11.10</th>\n",
       "      <th>2022.11.11</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/afghanista...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>203265.0</td>\n",
       "      <td>203395.0</td>\n",
       "      <td>203497.0</td>\n",
       "      <td>203574.0</td>\n",
       "      <td>203681.0</td>\n",
       "      <td>203829.0</td>\n",
       "      <td>203942.0</td>\n",
       "      <td>204094.0</td>\n",
       "      <td>204287.0</td>\n",
       "      <td>204392.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/albania/fl...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>332996.0</td>\n",
       "      <td>332996.0</td>\n",
       "      <td>333027.0</td>\n",
       "      <td>333046.0</td>\n",
       "      <td>333055.0</td>\n",
       "      <td>333058.0</td>\n",
       "      <td>333071.0</td>\n",
       "      <td>333088.0</td>\n",
       "      <td>333103.0</td>\n",
       "      <td>333125.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/algeria/fl...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>270840.0</td>\n",
       "      <td>270847.0</td>\n",
       "      <td>270856.0</td>\n",
       "      <td>270862.0</td>\n",
       "      <td>270873.0</td>\n",
       "      <td>270881.0</td>\n",
       "      <td>270891.0</td>\n",
       "      <td>270906.0</td>\n",
       "      <td>270917.0</td>\n",
       "      <td>270924.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/andorra/fl...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46664.0</td>\n",
       "      <td>46664.0</td>\n",
       "      <td>46664.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Angola</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/angola/fla...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242</th>\n",
       "      <td>Curacao</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/curacao/fl...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>Cape Verde</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/cape-verde...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>East Timor</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/east-timor...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>St. Martin</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/st.-martin...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>Republic of Ireland</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/republic-o...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>247 rows × 1027 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Country_Region                                           flag_url  \\\n",
       "0            Afghanistan  https://cdn.countryflags.com/thumbs/afghanista...   \n",
       "1                Albania  https://cdn.countryflags.com/thumbs/albania/fl...   \n",
       "2                Algeria  https://cdn.countryflags.com/thumbs/algeria/fl...   \n",
       "3                Andorra  https://cdn.countryflags.com/thumbs/andorra/fl...   \n",
       "4                 Angola  https://cdn.countryflags.com/thumbs/angola/fla...   \n",
       "..                   ...                                                ...   \n",
       "242              Curacao  https://cdn.countryflags.com/thumbs/curacao/fl...   \n",
       "243           Cape Verde  https://cdn.countryflags.com/thumbs/cape-verde...   \n",
       "244           East Timor  https://cdn.countryflags.com/thumbs/east-timor...   \n",
       "245           St. Martin  https://cdn.countryflags.com/thumbs/st.-martin...   \n",
       "246  Republic of Ireland  https://cdn.countryflags.com/thumbs/republic-o...   \n",
       "\n",
       "     2020.01.22  2020.01.23  2020.01.24  2020.01.25  2020.01.26  2020.01.27  \\\n",
       "0           0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "1           0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "2           0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "3           0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "4           0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "..          ...         ...         ...         ...         ...         ...   \n",
       "242         0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "243         0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "244         0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "245         0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "246         0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "\n",
       "     2020.01.28  2020.01.29  ...  2022.11.02  2022.11.03  2022.11.04  \\\n",
       "0           0.0         0.0  ...    203265.0    203395.0    203497.0   \n",
       "1           0.0         0.0  ...    332996.0    332996.0    333027.0   \n",
       "2           0.0         0.0  ...    270840.0    270847.0    270856.0   \n",
       "3           0.0         0.0  ...     46588.0     46588.0     46588.0   \n",
       "4           0.0         0.0  ...    103131.0    103131.0    103131.0   \n",
       "..          ...         ...  ...         ...         ...         ...   \n",
       "242         0.0         0.0  ...         0.0         0.0         0.0   \n",
       "243         0.0         0.0  ...         0.0         0.0         0.0   \n",
       "244         0.0         0.0  ...         0.0         0.0         0.0   \n",
       "245         0.0         0.0  ...         0.0         0.0         0.0   \n",
       "246         0.0         0.0  ...         0.0         0.0         0.0   \n",
       "\n",
       "     2022.11.05  2022.11.06  2022.11.07  2022.11.08  2022.11.09  2022.11.10  \\\n",
       "0      203574.0    203681.0    203829.0    203942.0    204094.0    204287.0   \n",
       "1      333046.0    333055.0    333058.0    333071.0    333088.0    333103.0   \n",
       "2      270862.0    270873.0    270881.0    270891.0    270906.0    270917.0   \n",
       "3       46588.0     46588.0     46588.0     46588.0     46664.0     46664.0   \n",
       "4      103131.0    103131.0    103131.0    103131.0    103131.0    103131.0   \n",
       "..          ...         ...         ...         ...         ...         ...   \n",
       "242         0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "243         0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "244         0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "245         0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "246         0.0         0.0         0.0         0.0         0.0         0.0   \n",
       "\n",
       "     2022.11.11  \n",
       "0      204392.0  \n",
       "1      333125.0  \n",
       "2      270924.0  \n",
       "3       46664.0  \n",
       "4      103131.0  \n",
       "..          ...  \n",
       "242         0.0  \n",
       "243         0.0  \n",
       "244         0.0  \n",
       "245         0.0  \n",
       "246         0.0  \n",
       "\n",
       "[247 rows x 1027 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flag_urls_series = df['Country_Region'].apply(util.make_flag_url)\n",
    "\n",
    "df_flags = df.copy()\n",
    "df_flags.insert(1, 'flag_url', flag_urls_series)\n",
    "df_flags.loc[df['Country_Region'] == 'Others', 'flag_url'] = '' # Remove others' flag url\n",
    "df_flags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_flags.to_csv('intermediate/result_flags.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "## Retrieve conitnent and population data\n",
    "- [Coronavirus Pandemic Data](https://ourworldindata.org/coronavirus)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>iso_code</th>\n",
       "      <th>continent</th>\n",
       "      <th>location</th>\n",
       "      <th>date</th>\n",
       "      <th>total_cases</th>\n",
       "      <th>new_cases</th>\n",
       "      <th>new_cases_smoothed</th>\n",
       "      <th>total_deaths</th>\n",
       "      <th>new_deaths</th>\n",
       "      <th>new_deaths_smoothed</th>\n",
       "      <th>...</th>\n",
       "      <th>male_smokers</th>\n",
       "      <th>handwashing_facilities</th>\n",
       "      <th>hospital_beds_per_thousand</th>\n",
       "      <th>life_expectancy</th>\n",
       "      <th>human_development_index</th>\n",
       "      <th>population</th>\n",
       "      <th>excess_mortality_cumulative_absolute</th>\n",
       "      <th>excess_mortality_cumulative</th>\n",
       "      <th>excess_mortality</th>\n",
       "      <th>excess_mortality_cumulative_per_million</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AFG</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2020-02-24</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.746</td>\n",
       "      <td>0.5</td>\n",
       "      <td>64.83</td>\n",
       "      <td>0.511</td>\n",
       "      <td>41128772.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AFG</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2020-02-25</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.746</td>\n",
       "      <td>0.5</td>\n",
       "      <td>64.83</td>\n",
       "      <td>0.511</td>\n",
       "      <td>41128772.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AFG</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2020-02-26</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.746</td>\n",
       "      <td>0.5</td>\n",
       "      <td>64.83</td>\n",
       "      <td>0.511</td>\n",
       "      <td>41128772.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AFG</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2020-02-27</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.746</td>\n",
       "      <td>0.5</td>\n",
       "      <td>64.83</td>\n",
       "      <td>0.511</td>\n",
       "      <td>41128772.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AFG</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2020-02-28</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.746</td>\n",
       "      <td>0.5</td>\n",
       "      <td>64.83</td>\n",
       "      <td>0.511</td>\n",
       "      <td>41128772.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 67 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  iso_code continent     location        date  total_cases  new_cases  \\\n",
       "0      AFG      Asia  Afghanistan  2020-02-24          5.0        5.0   \n",
       "1      AFG      Asia  Afghanistan  2020-02-25          5.0        0.0   \n",
       "2      AFG      Asia  Afghanistan  2020-02-26          5.0        0.0   \n",
       "3      AFG      Asia  Afghanistan  2020-02-27          5.0        0.0   \n",
       "4      AFG      Asia  Afghanistan  2020-02-28          5.0        0.0   \n",
       "\n",
       "   new_cases_smoothed  total_deaths  new_deaths  new_deaths_smoothed  ...  \\\n",
       "0                 NaN           NaN         NaN                  NaN  ...   \n",
       "1                 NaN           NaN         NaN                  NaN  ...   \n",
       "2                 NaN           NaN         NaN                  NaN  ...   \n",
       "3                 NaN           NaN         NaN                  NaN  ...   \n",
       "4                 NaN           NaN         NaN                  NaN  ...   \n",
       "\n",
       "   male_smokers  handwashing_facilities  hospital_beds_per_thousand  \\\n",
       "0           NaN                  37.746                         0.5   \n",
       "1           NaN                  37.746                         0.5   \n",
       "2           NaN                  37.746                         0.5   \n",
       "3           NaN                  37.746                         0.5   \n",
       "4           NaN                  37.746                         0.5   \n",
       "\n",
       "   life_expectancy  human_development_index  population  \\\n",
       "0            64.83                    0.511  41128772.0   \n",
       "1            64.83                    0.511  41128772.0   \n",
       "2            64.83                    0.511  41128772.0   \n",
       "3            64.83                    0.511  41128772.0   \n",
       "4            64.83                    0.511  41128772.0   \n",
       "\n",
       "   excess_mortality_cumulative_absolute  excess_mortality_cumulative  \\\n",
       "0                                   NaN                          NaN   \n",
       "1                                   NaN                          NaN   \n",
       "2                                   NaN                          NaN   \n",
       "3                                   NaN                          NaN   \n",
       "4                                   NaN                          NaN   \n",
       "\n",
       "   excess_mortality  excess_mortality_cumulative_per_million  \n",
       "0               NaN                                      NaN  \n",
       "1               NaN                                      NaN  \n",
       "2               NaN                                      NaN  \n",
       "3               NaN                                      NaN  \n",
       "4               NaN                                      NaN  \n",
       "\n",
       "[5 rows x 67 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load Our World In Data (OWID)\n",
    "## It's a large file so I didn't upload to Github.\n",
    "owid = pd.read_csv('owid-covid-data.csv')\n",
    "owid.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>continent</th>\n",
       "      <th>Country_Region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Asia</td>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Europe</td>\n",
       "      <td>Albania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Africa</td>\n",
       "      <td>Algeria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Europe</td>\n",
       "      <td>Andorra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Africa</td>\n",
       "      <td>Angola</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>Oceania</td>\n",
       "      <td>Wallis and Futuna</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>Africa</td>\n",
       "      <td>Western Sahara</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>Asia</td>\n",
       "      <td>Yemen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233</th>\n",
       "      <td>Africa</td>\n",
       "      <td>Zambia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234</th>\n",
       "      <td>Africa</td>\n",
       "      <td>Zimbabwe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>235 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    continent     Country_Region\n",
       "0        Asia        Afghanistan\n",
       "1      Europe            Albania\n",
       "2      Africa            Algeria\n",
       "3      Europe            Andorra\n",
       "4      Africa             Angola\n",
       "..        ...                ...\n",
       "230   Oceania  Wallis and Futuna\n",
       "231    Africa     Western Sahara\n",
       "232      Asia              Yemen\n",
       "233    Africa             Zambia\n",
       "234    Africa           Zimbabwe\n",
       "\n",
       "[235 rows x 2 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Process data\n",
    "df_cont = owid.copy()[['continent', 'location']]\n",
    "df_cont = (\n",
    "    df_cont\n",
    "    [~df_cont['continent'].isna()]\n",
    "    .drop_duplicates('location')\n",
    "    .rename(columns={'location': 'Country_Region'})\n",
    "    .reset_index(drop=True)\n",
    ")\n",
    "\n",
    "# To keep 'United States' name consistent\n",
    "df_cont.loc[df_cont['Country_Region'] == 'United States', 'Country_Region'] = 'US'\n",
    "df_cont"
   ]
  },
  {
   "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>continent</th>\n",
       "      <th>flag_url</th>\n",
       "      <th>2020.01.22</th>\n",
       "      <th>2020.01.23</th>\n",
       "      <th>2020.01.24</th>\n",
       "      <th>2020.01.25</th>\n",
       "      <th>2020.01.26</th>\n",
       "      <th>2020.01.27</th>\n",
       "      <th>2020.01.28</th>\n",
       "      <th>2020.01.29</th>\n",
       "      <th>...</th>\n",
       "      <th>2022.11.02</th>\n",
       "      <th>2022.11.03</th>\n",
       "      <th>2022.11.04</th>\n",
       "      <th>2022.11.05</th>\n",
       "      <th>2022.11.06</th>\n",
       "      <th>2022.11.07</th>\n",
       "      <th>2022.11.08</th>\n",
       "      <th>2022.11.09</th>\n",
       "      <th>2022.11.10</th>\n",
       "      <th>2022.11.11</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country_Region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Afghanistan</th>\n",
       "      <td>Asia</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/afghanista...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>203265.0</td>\n",
       "      <td>203395.0</td>\n",
       "      <td>203497.0</td>\n",
       "      <td>203574.0</td>\n",
       "      <td>203681.0</td>\n",
       "      <td>203829.0</td>\n",
       "      <td>203942.0</td>\n",
       "      <td>204094.0</td>\n",
       "      <td>204287.0</td>\n",
       "      <td>204392.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Albania</th>\n",
       "      <td>Europe</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/albania/fl...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>332996.0</td>\n",
       "      <td>332996.0</td>\n",
       "      <td>333027.0</td>\n",
       "      <td>333046.0</td>\n",
       "      <td>333055.0</td>\n",
       "      <td>333058.0</td>\n",
       "      <td>333071.0</td>\n",
       "      <td>333088.0</td>\n",
       "      <td>333103.0</td>\n",
       "      <td>333125.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Algeria</th>\n",
       "      <td>Africa</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/algeria/fl...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>270840.0</td>\n",
       "      <td>270847.0</td>\n",
       "      <td>270856.0</td>\n",
       "      <td>270862.0</td>\n",
       "      <td>270873.0</td>\n",
       "      <td>270881.0</td>\n",
       "      <td>270891.0</td>\n",
       "      <td>270906.0</td>\n",
       "      <td>270917.0</td>\n",
       "      <td>270924.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andorra</th>\n",
       "      <td>Europe</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/andorra/fl...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46588.0</td>\n",
       "      <td>46664.0</td>\n",
       "      <td>46664.0</td>\n",
       "      <td>46664.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Angola</th>\n",
       "      <td>Africa</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/angola/fla...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "      <td>103131.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Venezuela</th>\n",
       "      <td>South America</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/venezuela/...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>545926.0</td>\n",
       "      <td>545951.0</td>\n",
       "      <td>545963.0</td>\n",
       "      <td>546047.0</td>\n",
       "      <td>546110.0</td>\n",
       "      <td>546110.0</td>\n",
       "      <td>546122.0</td>\n",
       "      <td>546202.0</td>\n",
       "      <td>546202.0</td>\n",
       "      <td>546262.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vietnam</th>\n",
       "      <td>Asia</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/vietnam/fl...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>11504091.0</td>\n",
       "      <td>11504910.0</td>\n",
       "      <td>11505249.0</td>\n",
       "      <td>11505608.0</td>\n",
       "      <td>11505849.0</td>\n",
       "      <td>11506214.0</td>\n",
       "      <td>11506656.0</td>\n",
       "      <td>11507124.0</td>\n",
       "      <td>11507540.0</td>\n",
       "      <td>11508170.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yemen</th>\n",
       "      <td>Asia</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/yemen/flag...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>11944.0</td>\n",
       "      <td>11944.0</td>\n",
       "      <td>11945.0</td>\n",
       "      <td>11945.0</td>\n",
       "      <td>11945.0</td>\n",
       "      <td>11945.0</td>\n",
       "      <td>11945.0</td>\n",
       "      <td>11945.0</td>\n",
       "      <td>11945.0</td>\n",
       "      <td>11945.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zambia</th>\n",
       "      <td>Africa</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/zambia/fla...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>333684.0</td>\n",
       "      <td>333685.0</td>\n",
       "      <td>333685.0</td>\n",
       "      <td>333685.0</td>\n",
       "      <td>333685.0</td>\n",
       "      <td>333685.0</td>\n",
       "      <td>333685.0</td>\n",
       "      <td>333685.0</td>\n",
       "      <td>333685.0</td>\n",
       "      <td>333685.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zimbabwe</th>\n",
       "      <td>Africa</td>\n",
       "      <td>https://cdn.countryflags.com/thumbs/zimbabwe/f...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>257893.0</td>\n",
       "      <td>257893.0</td>\n",
       "      <td>257893.0</td>\n",
       "      <td>257893.0</td>\n",
       "      <td>257893.0</td>\n",
       "      <td>257893.0</td>\n",
       "      <td>257893.0</td>\n",
       "      <td>257893.0</td>\n",
       "      <td>257893.0</td>\n",
       "      <td>257893.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>199 rows × 1027 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    continent  \\\n",
       "Country_Region                  \n",
       "Afghanistan              Asia   \n",
       "Albania                Europe   \n",
       "Algeria                Africa   \n",
       "Andorra                Europe   \n",
       "Angola                 Africa   \n",
       "...                       ...   \n",
       "Venezuela       South America   \n",
       "Vietnam                  Asia   \n",
       "Yemen                    Asia   \n",
       "Zambia                 Africa   \n",
       "Zimbabwe               Africa   \n",
       "\n",
       "                                                         flag_url  2020.01.22  \\\n",
       "Country_Region                                                                  \n",
       "Afghanistan     https://cdn.countryflags.com/thumbs/afghanista...         0.0   \n",
       "Albania         https://cdn.countryflags.com/thumbs/albania/fl...         0.0   \n",
       "Algeria         https://cdn.countryflags.com/thumbs/algeria/fl...         0.0   \n",
       "Andorra         https://cdn.countryflags.com/thumbs/andorra/fl...         0.0   \n",
       "Angola          https://cdn.countryflags.com/thumbs/angola/fla...         0.0   \n",
       "...                                                           ...         ...   \n",
       "Venezuela       https://cdn.countryflags.com/thumbs/venezuela/...         0.0   \n",
       "Vietnam         https://cdn.countryflags.com/thumbs/vietnam/fl...         0.0   \n",
       "Yemen           https://cdn.countryflags.com/thumbs/yemen/flag...         0.0   \n",
       "Zambia          https://cdn.countryflags.com/thumbs/zambia/fla...         0.0   \n",
       "Zimbabwe        https://cdn.countryflags.com/thumbs/zimbabwe/f...         0.0   \n",
       "\n",
       "                2020.01.23  2020.01.24  2020.01.25  2020.01.26  2020.01.27  \\\n",
       "Country_Region                                                               \n",
       "Afghanistan            0.0         0.0         0.0         0.0         0.0   \n",
       "Albania                0.0         0.0         0.0         0.0         0.0   \n",
       "Algeria                0.0         0.0         0.0         0.0         0.0   \n",
       "Andorra                0.0         0.0         0.0         0.0         0.0   \n",
       "Angola                 0.0         0.0         0.0         0.0         0.0   \n",
       "...                    ...         ...         ...         ...         ...   \n",
       "Venezuela              0.0         0.0         0.0         0.0         0.0   \n",
       "Vietnam                2.0         2.0         2.0         2.0         2.0   \n",
       "Yemen                  0.0         0.0         0.0         0.0         0.0   \n",
       "Zambia                 0.0         0.0         0.0         0.0         0.0   \n",
       "Zimbabwe               0.0         0.0         0.0         0.0         0.0   \n",
       "\n",
       "                2020.01.28  2020.01.29  ...  2022.11.02  2022.11.03  \\\n",
       "Country_Region                          ...                           \n",
       "Afghanistan            0.0         0.0  ...    203265.0    203395.0   \n",
       "Albania                0.0         0.0  ...    332996.0    332996.0   \n",
       "Algeria                0.0         0.0  ...    270840.0    270847.0   \n",
       "Andorra                0.0         0.0  ...     46588.0     46588.0   \n",
       "Angola                 0.0         0.0  ...    103131.0    103131.0   \n",
       "...                    ...         ...  ...         ...         ...   \n",
       "Venezuela              0.0         0.0  ...    545926.0    545951.0   \n",
       "Vietnam                2.0         2.0  ...  11504091.0  11504910.0   \n",
       "Yemen                  0.0         0.0  ...     11944.0     11944.0   \n",
       "Zambia                 0.0         0.0  ...    333684.0    333685.0   \n",
       "Zimbabwe               0.0         0.0  ...    257893.0    257893.0   \n",
       "\n",
       "                2022.11.04  2022.11.05  2022.11.06  2022.11.07  2022.11.08  \\\n",
       "Country_Region                                                               \n",
       "Afghanistan       203497.0    203574.0    203681.0    203829.0    203942.0   \n",
       "Albania           333027.0    333046.0    333055.0    333058.0    333071.0   \n",
       "Algeria           270856.0    270862.0    270873.0    270881.0    270891.0   \n",
       "Andorra            46588.0     46588.0     46588.0     46588.0     46588.0   \n",
       "Angola            103131.0    103131.0    103131.0    103131.0    103131.0   \n",
       "...                    ...         ...         ...         ...         ...   \n",
       "Venezuela         545963.0    546047.0    546110.0    546110.0    546122.0   \n",
       "Vietnam         11505249.0  11505608.0  11505849.0  11506214.0  11506656.0   \n",
       "Yemen              11945.0     11945.0     11945.0     11945.0     11945.0   \n",
       "Zambia            333685.0    333685.0    333685.0    333685.0    333685.0   \n",
       "Zimbabwe          257893.0    257893.0    257893.0    257893.0    257893.0   \n",
       "\n",
       "                2022.11.09  2022.11.10  2022.11.11  \n",
       "Country_Region                                      \n",
       "Afghanistan       204094.0    204287.0    204392.0  \n",
       "Albania           333088.0    333103.0    333125.0  \n",
       "Algeria           270906.0    270917.0    270924.0  \n",
       "Andorra            46664.0     46664.0     46664.0  \n",
       "Angola            103131.0    103131.0    103131.0  \n",
       "...                    ...         ...         ...  \n",
       "Venezuela         546202.0    546202.0    546262.0  \n",
       "Vietnam         11507124.0  11507540.0  11508170.0  \n",
       "Yemen              11945.0     11945.0     11945.0  \n",
       "Zambia            333685.0    333685.0    333685.0  \n",
       "Zimbabwe          257893.0    257893.0    257893.0  \n",
       "\n",
       "[199 rows x 1027 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_flags = pd.read_csv('intermediate/result_flags.csv', index_col=0)\n",
    "df_flags_cont = df_cont.set_index('Country_Region').join(df_flags.set_index('Country_Region'), how='inner')\n",
    "df_flags_cont"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_flags_cont.to_csv('out/result_flags_cont.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Completed! \n",
    "### It's time for visualization!\n",
    "1. Go to [Flourish](https://flourish.studio/).\n",
    "2. Sign in and choose 'Bar chart race'.\n",
    "3. Upload `result_flags_cont.csv`.\n",
    "4. Modify the options on the right side bar as you like."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "There's another Way to get CSSE data: Use [Akshare datasets](https://www.akshare.xyz/tutorial.html#id1)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Province/State</th>\n",
       "      <th>Country/Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>1/22/20</th>\n",
       "      <th>1/23/20</th>\n",
       "      <th>1/24/20</th>\n",
       "      <th>1/25/20</th>\n",
       "      <th>1/26/20</th>\n",
       "      <th>1/27/20</th>\n",
       "      <th>...</th>\n",
       "      <th>11/7/22</th>\n",
       "      <th>11/8/22</th>\n",
       "      <th>11/9/22</th>\n",
       "      <th>11/10/22</th>\n",
       "      <th>11/11/22</th>\n",
       "      <th>11/12/22</th>\n",
       "      <th>11/13/22</th>\n",
       "      <th>11/14/22</th>\n",
       "      <th>11/15/22</th>\n",
       "      <th>11/16/22</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>203829</td>\n",
       "      <td>203942</td>\n",
       "      <td>204094</td>\n",
       "      <td>204287</td>\n",
       "      <td>204392</td>\n",
       "      <td>204417</td>\n",
       "      <td>204510</td>\n",
       "      <td>204610</td>\n",
       "      <td>204724</td>\n",
       "      <td>204820</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>333058</td>\n",
       "      <td>333071</td>\n",
       "      <td>333088</td>\n",
       "      <td>333103</td>\n",
       "      <td>333125</td>\n",
       "      <td>333138</td>\n",
       "      <td>333156</td>\n",
       "      <td>333161</td>\n",
       "      <td>333197</td>\n",
       "      <td>333215</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>270881</td>\n",
       "      <td>270891</td>\n",
       "      <td>270906</td>\n",
       "      <td>270917</td>\n",
       "      <td>270924</td>\n",
       "      <td>270929</td>\n",
       "      <td>270939</td>\n",
       "      <td>270952</td>\n",
       "      <td>270969</td>\n",
       "      <td>270981</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>46588</td>\n",
       "      <td>46588</td>\n",
       "      <td>46664</td>\n",
       "      <td>46664</td>\n",
       "      <td>46664</td>\n",
       "      <td>46664</td>\n",
       "      <td>46664</td>\n",
       "      <td>46664</td>\n",
       "      <td>46664</td>\n",
       "      <td>46824</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>103131</td>\n",
       "      <td>103131</td>\n",
       "      <td>103131</td>\n",
       "      <td>103131</td>\n",
       "      <td>103131</td>\n",
       "      <td>103131</td>\n",
       "      <td>103131</td>\n",
       "      <td>103131</td>\n",
       "      <td>103131</td>\n",
       "      <td>103131</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 1034 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Province/State Country/Region       Lat       Long  1/22/20  1/23/20  \\\n",
       "0            NaN    Afghanistan  33.93911  67.709953        0        0   \n",
       "1            NaN        Albania  41.15330  20.168300        0        0   \n",
       "2            NaN        Algeria  28.03390   1.659600        0        0   \n",
       "3            NaN        Andorra  42.50630   1.521800        0        0   \n",
       "4            NaN         Angola -11.20270  17.873900        0        0   \n",
       "\n",
       "   1/24/20  1/25/20  1/26/20  1/27/20  ...  11/7/22  11/8/22  11/9/22  \\\n",
       "0        0        0        0        0  ...   203829   203942   204094   \n",
       "1        0        0        0        0  ...   333058   333071   333088   \n",
       "2        0        0        0        0  ...   270881   270891   270906   \n",
       "3        0        0        0        0  ...    46588    46588    46664   \n",
       "4        0        0        0        0  ...   103131   103131   103131   \n",
       "\n",
       "   11/10/22  11/11/22  11/12/22  11/13/22  11/14/22  11/15/22  11/16/22  \n",
       "0    204287    204392    204417    204510    204610    204724    204820  \n",
       "1    333103    333125    333138    333156    333161    333197    333215  \n",
       "2    270917    270924    270929    270939    270952    270969    270981  \n",
       "3     46664     46664     46664     46664     46664     46664     46824  \n",
       "4    103131    103131    103131    103131    103131    103131    103131  \n",
       "\n",
       "[5 rows x 1034 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import akshare\n",
    "data_ak = akshare.covid_19_csse_global_confirmed()\n",
    "data_ak.head()"
   ]
  }
 ],
 "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.9.5 (default, May  4 2021, 03:36:27) \n[Clang 12.0.0 (clang-1200.0.32.29)]"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "397704579725e15f5c7cb49fe5f0341eb7531c82d19f2c29d197e8b64ab5776b"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}