nCoV_RR.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime\n",
    "t = datetime.datetime.now()\n",
    "folder = r'C:\\Users\\Ensheng\\Desktop\\Coronavirus\\Risk_Analysis\\\\'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1975\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>8: Infected</th>\n",
       "      <th>8: Routes</th>\n",
       "      <th>9: Infected</th>\n",
       "      <th>9: Routes</th>\n",
       "      <th>10: Infected</th>\n",
       "      <th>10: Routes</th>\n",
       "      <th>11: Infected</th>\n",
       "      <th>11: Routes</th>\n",
       "      <th>12: Infected</th>\n",
       "      <th>12: Routes</th>\n",
       "      <th>...</th>\n",
       "      <th>60: Infected</th>\n",
       "      <th>60: Routes</th>\n",
       "      <th>61: Infected</th>\n",
       "      <th>61: Routes</th>\n",
       "      <th>62: Infected</th>\n",
       "      <th>62: Routes</th>\n",
       "      <th>63: Infected</th>\n",
       "      <th>63: Routes</th>\n",
       "      <th>64: Infected</th>\n",
       "      <th>64: Routes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.064</td>\n",
       "      <td>WUH-&gt;PEK</td>\n",
       "      <td>0.100</td>\n",
       "      <td>WUH-&gt;PEK</td>\n",
       "      <td>0.092</td>\n",
       "      <td>WUH-&gt;PEK</td>\n",
       "      <td>0.084</td>\n",
       "      <td>WUH-&gt;PEK</td>\n",
       "      <td>0.172</td>\n",
       "      <td>WUH-&gt;PEK</td>\n",
       "      <td>...</td>\n",
       "      <td>43.892</td>\n",
       "      <td>WUH-&gt;PEK</td>\n",
       "      <td>47.668</td>\n",
       "      <td>WUH-&gt;PEK</td>\n",
       "      <td>51.884</td>\n",
       "      <td>WUH-&gt;PEK</td>\n",
       "      <td>62.956</td>\n",
       "      <td>WUH-&gt;PEK</td>\n",
       "      <td>68.956</td>\n",
       "      <td>WUH-&gt;PEK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.048</td>\n",
       "      <td>WUH-&gt;XMN</td>\n",
       "      <td>0.072</td>\n",
       "      <td>WUH-&gt;HAK</td>\n",
       "      <td>0.088</td>\n",
       "      <td>WUH-&gt;SHA</td>\n",
       "      <td>0.064</td>\n",
       "      <td>WUH-&gt;SHA</td>\n",
       "      <td>0.120</td>\n",
       "      <td>WUH-&gt;KMG</td>\n",
       "      <td>...</td>\n",
       "      <td>27.144</td>\n",
       "      <td>WUH-&gt;PVG</td>\n",
       "      <td>30.288</td>\n",
       "      <td>WUH-&gt;PVG</td>\n",
       "      <td>31.828</td>\n",
       "      <td>WUH-&gt;PVG</td>\n",
       "      <td>51.688</td>\n",
       "      <td>WUH-&gt;CAN</td>\n",
       "      <td>57.184</td>\n",
       "      <td>WUH-&gt;CAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.044</td>\n",
       "      <td>WUH-&gt;SHA</td>\n",
       "      <td>0.056</td>\n",
       "      <td>WUH-&gt;XMN</td>\n",
       "      <td>0.052</td>\n",
       "      <td>WUH-&gt;PVG</td>\n",
       "      <td>0.052</td>\n",
       "      <td>WUH-&gt;PVG</td>\n",
       "      <td>0.096</td>\n",
       "      <td>WUH-&gt;SYX</td>\n",
       "      <td>...</td>\n",
       "      <td>25.092</td>\n",
       "      <td>WUH-&gt;SHA</td>\n",
       "      <td>27.896</td>\n",
       "      <td>WUH-&gt;SHA</td>\n",
       "      <td>31.584</td>\n",
       "      <td>WUH-&gt;SHA</td>\n",
       "      <td>34.936</td>\n",
       "      <td>WUH-&gt;SZX</td>\n",
       "      <td>40.176</td>\n",
       "      <td>WUH-&gt;SZX</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 114 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   8: Infected 8: Routes  9: Infected 9: Routes  10: Infected 10: Routes  \\\n",
       "0        0.064  WUH->PEK        0.100  WUH->PEK         0.092   WUH->PEK   \n",
       "1        0.048  WUH->XMN        0.072  WUH->HAK         0.088   WUH->SHA   \n",
       "2        0.044  WUH->SHA        0.056  WUH->XMN         0.052   WUH->PVG   \n",
       "\n",
       "   11: Infected 11: Routes  12: Infected 12: Routes  ...  60: Infected  \\\n",
       "0         0.084   WUH->PEK         0.172   WUH->PEK  ...        43.892   \n",
       "1         0.064   WUH->SHA         0.120   WUH->KMG  ...        27.144   \n",
       "2         0.052   WUH->PVG         0.096   WUH->SYX  ...        25.092   \n",
       "\n",
       "  60: Routes  61: Infected 61: Routes  62: Infected 62: Routes  63: Infected  \\\n",
       "0   WUH->PEK        47.668   WUH->PEK        51.884   WUH->PEK        62.956   \n",
       "1   WUH->PVG        30.288   WUH->PVG        31.828   WUH->PVG        51.688   \n",
       "2   WUH->SHA        27.896   WUH->SHA        31.584   WUH->SHA        34.936   \n",
       "\n",
       "  63: Routes  64: Infected 64: Routes  \n",
       "0   WUH->PEK        68.956   WUH->PEK  \n",
       "1   WUH->CAN        57.184   WUH->CAN  \n",
       "2   WUH->SZX        40.176   WUH->SZX  \n",
       "\n",
       "[3 rows x 114 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "in_table = folder + r'routesAverage_2.5.xlsx'\n",
    "df = pd.read_excel(in_table)\n",
    "print(len(df))\n",
    "cut = 131 # 40 days: 27:83; 45d: 27:93; 50: 27:103\n",
    "df = df.iloc[:,17:cut] \n",
    "# df.count(axis='columns')\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### route risk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 266,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_infected = df[colist_infected]\n",
    "# df_dst = df[colist_dst]\n",
    "# df_infected_one = pd.DataFrame({'infected':df_infected.values.ravel(order='F')})\n",
    "# df_dst_one = pd.DataFrame({'dst':df_dst.values.ravel(order='F')})\n",
    "\n",
    "# df_updated = pd.concat([df_infected_one, df_dst_one], axis=1, sort=False)\n",
    "\n",
    "# df_rank = df_updated.groupby('dst').sum().reset_index()\n",
    "# df_rank = df_rank.sort_values(by='infected',ascending = False).reset_index(drop=True)\n",
    "# df_rank.head(5)\n",
    "\n",
    "# result = df_rank\n",
    "# output_csv = folder + 'output_route_' + str(cut) + '_' + t.strftime('%m%d%y%H%M') + '.csv'\n",
    "# result.to_csv(output_csv, index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### city risk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "scrolled": true
   },
   "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>8: Infected</th>\n",
       "      <th>8: Routes</th>\n",
       "      <th>9: Infected</th>\n",
       "      <th>9: Routes</th>\n",
       "      <th>10: Infected</th>\n",
       "      <th>10: Routes</th>\n",
       "      <th>11: Infected</th>\n",
       "      <th>11: Routes</th>\n",
       "      <th>12: Infected</th>\n",
       "      <th>12: Routes</th>\n",
       "      <th>...</th>\n",
       "      <th>60: Infected</th>\n",
       "      <th>60: Routes</th>\n",
       "      <th>61: Infected</th>\n",
       "      <th>61: Routes</th>\n",
       "      <th>62: Infected</th>\n",
       "      <th>62: Routes</th>\n",
       "      <th>63: Infected</th>\n",
       "      <th>63: Routes</th>\n",
       "      <th>64: Infected</th>\n",
       "      <th>64: Routes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.064</td>\n",
       "      <td>PEK</td>\n",
       "      <td>0.100</td>\n",
       "      <td>PEK</td>\n",
       "      <td>0.092</td>\n",
       "      <td>PEK</td>\n",
       "      <td>0.084</td>\n",
       "      <td>PEK</td>\n",
       "      <td>0.172</td>\n",
       "      <td>PEK</td>\n",
       "      <td>...</td>\n",
       "      <td>43.892</td>\n",
       "      <td>PEK</td>\n",
       "      <td>47.668</td>\n",
       "      <td>PEK</td>\n",
       "      <td>51.884</td>\n",
       "      <td>PEK</td>\n",
       "      <td>62.956</td>\n",
       "      <td>PEK</td>\n",
       "      <td>68.956</td>\n",
       "      <td>PEK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.048</td>\n",
       "      <td>XMN</td>\n",
       "      <td>0.072</td>\n",
       "      <td>HAK</td>\n",
       "      <td>0.088</td>\n",
       "      <td>SHA</td>\n",
       "      <td>0.064</td>\n",
       "      <td>SHA</td>\n",
       "      <td>0.120</td>\n",
       "      <td>KMG</td>\n",
       "      <td>...</td>\n",
       "      <td>27.144</td>\n",
       "      <td>PVG</td>\n",
       "      <td>30.288</td>\n",
       "      <td>PVG</td>\n",
       "      <td>31.828</td>\n",
       "      <td>PVG</td>\n",
       "      <td>51.688</td>\n",
       "      <td>CAN</td>\n",
       "      <td>57.184</td>\n",
       "      <td>CAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.044</td>\n",
       "      <td>SHA</td>\n",
       "      <td>0.056</td>\n",
       "      <td>XMN</td>\n",
       "      <td>0.052</td>\n",
       "      <td>PVG</td>\n",
       "      <td>0.052</td>\n",
       "      <td>PVG</td>\n",
       "      <td>0.096</td>\n",
       "      <td>SYX</td>\n",
       "      <td>...</td>\n",
       "      <td>25.092</td>\n",
       "      <td>SHA</td>\n",
       "      <td>27.896</td>\n",
       "      <td>SHA</td>\n",
       "      <td>31.584</td>\n",
       "      <td>SHA</td>\n",
       "      <td>34.936</td>\n",
       "      <td>SZX</td>\n",
       "      <td>40.176</td>\n",
       "      <td>SZX</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 114 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   8: Infected 8: Routes  9: Infected 9: Routes  10: Infected 10: Routes  \\\n",
       "0        0.064       PEK        0.100       PEK         0.092        PEK   \n",
       "1        0.048       XMN        0.072       HAK         0.088        SHA   \n",
       "2        0.044       SHA        0.056       XMN         0.052        PVG   \n",
       "\n",
       "   11: Infected 11: Routes  12: Infected 12: Routes  ...  60: Infected  \\\n",
       "0         0.084        PEK         0.172        PEK  ...        43.892   \n",
       "1         0.064        SHA         0.120        KMG  ...        27.144   \n",
       "2         0.052        PVG         0.096        SYX  ...        25.092   \n",
       "\n",
       "  60: Routes  61: Infected 61: Routes  62: Infected 62: Routes  63: Infected  \\\n",
       "0        PEK        47.668        PEK        51.884        PEK        62.956   \n",
       "1        PVG        30.288        PVG        31.828        PVG        51.688   \n",
       "2        SHA        27.896        SHA        31.584        SHA        34.936   \n",
       "\n",
       "  63: Routes  64: Infected 64: Routes  \n",
       "0        PEK        68.956        PEK  \n",
       "1        CAN        57.184        CAN  \n",
       "2        SZX        40.176        SZX  \n",
       "\n",
       "[3 rows x 114 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# filter out the destination\n",
    "cols = df.columns\n",
    "for j in range(len(df.columns)):\n",
    "    if j%2 == 1:\n",
    "        col_name = cols[j]\n",
    "        df[col_name] = df[col_name].str.slice(-3,) # export: stop=3; import: -3,\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# find column names\n",
    "colist_infected = []\n",
    "colist_dst = []\n",
    "for j in range(len(df.columns)):\n",
    "    col_name = cols[j]\n",
    "    if j%2 == 0:\n",
    "        colist_infected.append(col_name)\n",
    "    if j%2 == 1:        \n",
    "        colist_dst.append(col_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# multiple cols to one cols\n",
    "df_infected = df[colist_infected]\n",
    "df_dst = df[colist_dst]\n",
    "df_infected_one = pd.DataFrame({'infected':df_infected.values.ravel(order='F')})\n",
    "df_dst_one = pd.DataFrame({'dst':df_dst.values.ravel(order='F')})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge two cols\n",
    "df_updated = pd.concat([df_infected_one, df_dst_one], axis=1, sort=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "## make sure the relationship remains\n",
    "# result = df_updated\n",
    "# output_csv = folder + 'output_TEST_' + str(cut) + '_'  + t.strftime('%m%d%y%H%M') + '.csv'\n",
    "# result.to_csv(output_csv, index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>dst</th>\n",
       "      <th>infected</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PEK</td>\n",
       "      <td>773.196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PVG</td>\n",
       "      <td>439.156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SHA</td>\n",
       "      <td>420.116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CAN</td>\n",
       "      <td>405.608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>KMG</td>\n",
       "      <td>370.712</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   dst  infected\n",
       "0  PEK   773.196\n",
       "1  PVG   439.156\n",
       "2  SHA   420.116\n",
       "3  CAN   405.608\n",
       "4  KMG   370.712"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# groupby and sort\n",
    "df_rank = df_updated.groupby('dst').sum().reset_index()\n",
    "df_rank = df_rank.sort_values(by='infected',ascending = False).reset_index(drop=True)\n",
    "df_rank.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11846\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Code</th>\n",
       "      <th>Name</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>State Name</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Country</th>\n",
       "      <th>Country Name</th>\n",
       "      <th>Global Region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAA</td>\n",
       "      <td>Anaa</td>\n",
       "      <td>Anaa</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-17.35</td>\n",
       "      <td>-145.50</td>\n",
       "      <td>PF</td>\n",
       "      <td>French Polynesia</td>\n",
       "      <td>Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAB</td>\n",
       "      <td>Arrabury</td>\n",
       "      <td>Arrabury</td>\n",
       "      <td>QL</td>\n",
       "      <td>Queensland</td>\n",
       "      <td>-26.70</td>\n",
       "      <td>141.04</td>\n",
       "      <td>AU</td>\n",
       "      <td>Australia</td>\n",
       "      <td>Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAC</td>\n",
       "      <td>El Arish International</td>\n",
       "      <td>El Arish</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>31.08</td>\n",
       "      <td>33.83</td>\n",
       "      <td>EG</td>\n",
       "      <td>Egypt</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Code                    Name      City State  State Name  Latitude  \\\n",
       "0  AAA                    Anaa      Anaa                      -17.35   \n",
       "1  AAB                Arrabury  Arrabury    QL  Queensland    -26.70   \n",
       "2  AAC  El Arish International  El Arish                       31.08   \n",
       "\n",
       "   Longitude Country      Country Name Global Region  \n",
       "0    -145.50      PF  French Polynesia   Australasia  \n",
       "1     141.04      AU         Australia   Australasia  \n",
       "2      33.83      EG             Egypt        Africa  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import airport info\n",
    "in_table = r'C:\\Users\\Ensheng\\Desktop\\Coronavirus\\IATA\\IATA_airports.xlsx'\n",
    "df_iata = pd.read_excel(in_table)\n",
    "print(len(df_iata))\n",
    "df_iata.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "set()"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# make sure no one left behind\n",
    "set(df_rank['dst']) - set(df_iata['Code'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "scrolled": true
   },
   "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>dst</th>\n",
       "      <th>infected</th>\n",
       "      <th>Code</th>\n",
       "      <th>Name</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>State Name</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Country</th>\n",
       "      <th>Country Name</th>\n",
       "      <th>Global Region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PEK</td>\n",
       "      <td>773.196</td>\n",
       "      <td>PEK</td>\n",
       "      <td>Capital International</td>\n",
       "      <td>Beijing</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>40.07</td>\n",
       "      <td>116.60</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PVG</td>\n",
       "      <td>439.156</td>\n",
       "      <td>PVG</td>\n",
       "      <td>Pudong International</td>\n",
       "      <td>Shanghai</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>31.14</td>\n",
       "      <td>121.79</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SHA</td>\n",
       "      <td>420.116</td>\n",
       "      <td>SHA</td>\n",
       "      <td>Hongqiao International</td>\n",
       "      <td>Shanghai-Metro</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>31.20</td>\n",
       "      <td>121.34</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CAN</td>\n",
       "      <td>405.608</td>\n",
       "      <td>CAN</td>\n",
       "      <td>Baiyun International</td>\n",
       "      <td>Guangzhou</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>23.12</td>\n",
       "      <td>113.23</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>KMG</td>\n",
       "      <td>370.712</td>\n",
       "      <td>KMG</td>\n",
       "      <td>Changshui International</td>\n",
       "      <td>Kunming</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>25.10</td>\n",
       "      <td>102.94</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CTU</td>\n",
       "      <td>339.500</td>\n",
       "      <td>CTU</td>\n",
       "      <td>Shuangliu International</td>\n",
       "      <td>Chengdu</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>30.58</td>\n",
       "      <td>103.95</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SYX</td>\n",
       "      <td>323.492</td>\n",
       "      <td>SYX</td>\n",
       "      <td>Phoenix International</td>\n",
       "      <td>Sanya</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>18.30</td>\n",
       "      <td>109.41</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SZX</td>\n",
       "      <td>293.920</td>\n",
       "      <td>SZX</td>\n",
       "      <td>Bao'an International</td>\n",
       "      <td>Shenzhen</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>22.55</td>\n",
       "      <td>113.92</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>HAK</td>\n",
       "      <td>282.512</td>\n",
       "      <td>HAK</td>\n",
       "      <td>Meilan International</td>\n",
       "      <td>Haikou</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>19.94</td>\n",
       "      <td>110.46</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>XMN</td>\n",
       "      <td>270.460</td>\n",
       "      <td>XMN</td>\n",
       "      <td>Gaoqi International</td>\n",
       "      <td>Xiamen</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>24.47</td>\n",
       "      <td>118.12</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>HGH</td>\n",
       "      <td>227.440</td>\n",
       "      <td>HGH</td>\n",
       "      <td>Xiaoshan International</td>\n",
       "      <td>Hangzhou</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>30.24</td>\n",
       "      <td>120.44</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>CKG</td>\n",
       "      <td>218.924</td>\n",
       "      <td>CKG</td>\n",
       "      <td>Jiangbei International</td>\n",
       "      <td>Chongqing</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>29.50</td>\n",
       "      <td>106.36</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>TSN</td>\n",
       "      <td>197.012</td>\n",
       "      <td>TSN</td>\n",
       "      <td>Binhai International</td>\n",
       "      <td>Tianjin</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>39.12</td>\n",
       "      <td>117.35</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NNG</td>\n",
       "      <td>196.444</td>\n",
       "      <td>NNG</td>\n",
       "      <td>Wuxu International</td>\n",
       "      <td>Nanning</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>22.61</td>\n",
       "      <td>108.17</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>WNZ</td>\n",
       "      <td>195.168</td>\n",
       "      <td>WNZ</td>\n",
       "      <td>Yongqiang International</td>\n",
       "      <td>Wenzhou</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>27.95</td>\n",
       "      <td>120.63</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>XIY</td>\n",
       "      <td>194.348</td>\n",
       "      <td>XIY</td>\n",
       "      <td>Xianyang International</td>\n",
       "      <td>Xi'an</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>34.37</td>\n",
       "      <td>108.70</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>TAO</td>\n",
       "      <td>190.292</td>\n",
       "      <td>TAO</td>\n",
       "      <td>Liuting International</td>\n",
       "      <td>Qingdao</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>36.10</td>\n",
       "      <td>120.32</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>KWE</td>\n",
       "      <td>176.712</td>\n",
       "      <td>KWE</td>\n",
       "      <td>Longdongbao International</td>\n",
       "      <td>Guiyang</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>26.54</td>\n",
       "      <td>106.80</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>SHE</td>\n",
       "      <td>135.216</td>\n",
       "      <td>SHE</td>\n",
       "      <td>Taoxian International</td>\n",
       "      <td>Shenyang</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>41.80</td>\n",
       "      <td>123.45</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>URC</td>\n",
       "      <td>117.372</td>\n",
       "      <td>URC</td>\n",
       "      <td>Diwopu International</td>\n",
       "      <td>Urumqi</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>43.91</td>\n",
       "      <td>87.47</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>ENH</td>\n",
       "      <td>105.056</td>\n",
       "      <td>ENH</td>\n",
       "      <td>Xujiaping</td>\n",
       "      <td>Enshi</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>30.28</td>\n",
       "      <td>109.32</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>FOC</td>\n",
       "      <td>102.036</td>\n",
       "      <td>FOC</td>\n",
       "      <td>Changle International</td>\n",
       "      <td>Fuzhou</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>26.10</td>\n",
       "      <td>119.28</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>HRB</td>\n",
       "      <td>95.528</td>\n",
       "      <td>HRB</td>\n",
       "      <td>Taiping International</td>\n",
       "      <td>Harbin, P. R. China</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>45.62</td>\n",
       "      <td>126.25</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>DLC</td>\n",
       "      <td>91.796</td>\n",
       "      <td>DLC</td>\n",
       "      <td>Zhoushuizi International</td>\n",
       "      <td>Dalian</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>38.92</td>\n",
       "      <td>121.65</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>NGB</td>\n",
       "      <td>84.184</td>\n",
       "      <td>NGB</td>\n",
       "      <td>Lishe International</td>\n",
       "      <td>Ningbo</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>29.83</td>\n",
       "      <td>121.53</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>ZUH</td>\n",
       "      <td>73.308</td>\n",
       "      <td>ZUH</td>\n",
       "      <td>Sanzao International</td>\n",
       "      <td>Zhuhai</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>22.01</td>\n",
       "      <td>113.38</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>CGQ</td>\n",
       "      <td>69.324</td>\n",
       "      <td>CGQ</td>\n",
       "      <td>Longjia International</td>\n",
       "      <td>Changchun</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>43.92</td>\n",
       "      <td>125.33</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>HKG</td>\n",
       "      <td>69.216</td>\n",
       "      <td>HKG</td>\n",
       "      <td>Hong Kong Intl</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>22.30</td>\n",
       "      <td>113.92</td>\n",
       "      <td>HK</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>TYN</td>\n",
       "      <td>66.176</td>\n",
       "      <td>TYN</td>\n",
       "      <td>Wusu International</td>\n",
       "      <td>Taiyuan</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>37.83</td>\n",
       "      <td>112.56</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>LHW</td>\n",
       "      <td>61.344</td>\n",
       "      <td>LHW</td>\n",
       "      <td>Zhongchuan</td>\n",
       "      <td>Lanzhou</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>36.02</td>\n",
       "      <td>103.75</td>\n",
       "      <td>CN</td>\n",
       "      <td>China</td>\n",
       "      <td>Asia</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>636</th>\n",
       "      <td>TBS</td>\n",
       "      <td>0.004</td>\n",
       "      <td>TBS</td>\n",
       "      <td>Novo Alexeyevka International</td>\n",
       "      <td>Tbilisi</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>41.67</td>\n",
       "      <td>44.96</td>\n",
       "      <td>GE</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>637</th>\n",
       "      <td>TBU</td>\n",
       "      <td>0.004</td>\n",
       "      <td>TBU</td>\n",
       "      <td>Fua'Amotu</td>\n",
       "      <td>Tongatapu, Tonga Isl</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-21.24</td>\n",
       "      <td>-175.14</td>\n",
       "      <td>TO</td>\n",
       "      <td>Tonga</td>\n",
       "      <td>Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638</th>\n",
       "      <td>CME</td>\n",
       "      <td>0.004</td>\n",
       "      <td>CME</td>\n",
       "      <td>Ciudad del Carmen International</td>\n",
       "      <td>Ciudad del Carmen</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>18.65</td>\n",
       "      <td>-91.80</td>\n",
       "      <td>MX</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>639</th>\n",
       "      <td>TDX</td>\n",
       "      <td>0.004</td>\n",
       "      <td>TDX</td>\n",
       "      <td>Trat Airport</td>\n",
       "      <td>Trat</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>12.27</td>\n",
       "      <td>102.32</td>\n",
       "      <td>TH</td>\n",
       "      <td>Thailand</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>640</th>\n",
       "      <td>TGG</td>\n",
       "      <td>0.004</td>\n",
       "      <td>TGG</td>\n",
       "      <td>Sultan Mahmud</td>\n",
       "      <td>Kuala Terengganu</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>5.38</td>\n",
       "      <td>103.11</td>\n",
       "      <td>MY</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>641</th>\n",
       "      <td>THS</td>\n",
       "      <td>0.004</td>\n",
       "      <td>THS</td>\n",
       "      <td>Sukhothai Airport</td>\n",
       "      <td>Sukhothai</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>17.24</td>\n",
       "      <td>99.82</td>\n",
       "      <td>TH</td>\n",
       "      <td>Thailand</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>642</th>\n",
       "      <td>TLL</td>\n",
       "      <td>0.004</td>\n",
       "      <td>TLL</td>\n",
       "      <td>Lennart Meri</td>\n",
       "      <td>Tallinn</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>59.42</td>\n",
       "      <td>24.82</td>\n",
       "      <td>EE</td>\n",
       "      <td>Estonia</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643</th>\n",
       "      <td>SIP</td>\n",
       "      <td>0.004</td>\n",
       "      <td>SIP</td>\n",
       "      <td>Simferopol International</td>\n",
       "      <td>Simferopol</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>45.02</td>\n",
       "      <td>34.00</td>\n",
       "      <td>UA</td>\n",
       "      <td>Ukraine</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>644</th>\n",
       "      <td>SDF</td>\n",
       "      <td>0.004</td>\n",
       "      <td>SDF</td>\n",
       "      <td>International-Standiford Field</td>\n",
       "      <td>Louisville</td>\n",
       "      <td>KY</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>38.18</td>\n",
       "      <td>-85.74</td>\n",
       "      <td>US</td>\n",
       "      <td>United States</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>645</th>\n",
       "      <td>POZ</td>\n",
       "      <td>0.004</td>\n",
       "      <td>POZ</td>\n",
       "      <td>Lawica</td>\n",
       "      <td>Poznan</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>52.42</td>\n",
       "      <td>16.88</td>\n",
       "      <td>PL</td>\n",
       "      <td>Poland</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>646</th>\n",
       "      <td>ROC</td>\n",
       "      <td>0.004</td>\n",
       "      <td>ROC</td>\n",
       "      <td>Greater Rochester International</td>\n",
       "      <td>Rochester</td>\n",
       "      <td>NY</td>\n",
       "      <td>New York</td>\n",
       "      <td>43.12</td>\n",
       "      <td>-77.67</td>\n",
       "      <td>US</td>\n",
       "      <td>United States</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>647</th>\n",
       "      <td>PPQ</td>\n",
       "      <td>0.004</td>\n",
       "      <td>PPQ</td>\n",
       "      <td>Paraparaumu</td>\n",
       "      <td>Paraparaumu</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-40.90</td>\n",
       "      <td>174.98</td>\n",
       "      <td>NZ</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>648</th>\n",
       "      <td>PPS</td>\n",
       "      <td>0.004</td>\n",
       "      <td>PPS</td>\n",
       "      <td>Puerto Princesa International</td>\n",
       "      <td>Puerto Princesa</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>9.74</td>\n",
       "      <td>118.75</td>\n",
       "      <td>PH</td>\n",
       "      <td>Philippines</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>649</th>\n",
       "      <td>PQQ</td>\n",
       "      <td>0.004</td>\n",
       "      <td>PQQ</td>\n",
       "      <td>Wauchope</td>\n",
       "      <td>Port Macquarie</td>\n",
       "      <td>NS</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>-31.43</td>\n",
       "      <td>152.87</td>\n",
       "      <td>AU</td>\n",
       "      <td>Australia</td>\n",
       "      <td>Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650</th>\n",
       "      <td>PVD</td>\n",
       "      <td>0.004</td>\n",
       "      <td>PVD</td>\n",
       "      <td>T.F. Green State Apt</td>\n",
       "      <td>Providence</td>\n",
       "      <td>RI</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>41.73</td>\n",
       "      <td>-71.43</td>\n",
       "      <td>US</td>\n",
       "      <td>United States</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651</th>\n",
       "      <td>QKL</td>\n",
       "      <td>0.004</td>\n",
       "      <td>QKL</td>\n",
       "      <td>Cologne-Main Rr</td>\n",
       "      <td>Cologne-Main Rr</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50.90</td>\n",
       "      <td>7.18</td>\n",
       "      <td>DE</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>652</th>\n",
       "      <td>ENU</td>\n",
       "      <td>0.004</td>\n",
       "      <td>ENU</td>\n",
       "      <td>Akanu Ibiam International Airport</td>\n",
       "      <td>Enugu</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>6.48</td>\n",
       "      <td>7.57</td>\n",
       "      <td>NG</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>653</th>\n",
       "      <td>EBL</td>\n",
       "      <td>0.004</td>\n",
       "      <td>EBL</td>\n",
       "      <td>Erbil International Airport</td>\n",
       "      <td>Erbil</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>36.23</td>\n",
       "      <td>43.97</td>\n",
       "      <td>IQ</td>\n",
       "      <td>Iraq</td>\n",
       "      <td>Middle East</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>654</th>\n",
       "      <td>ROA</td>\n",
       "      <td>0.004</td>\n",
       "      <td>ROA</td>\n",
       "      <td>Regional (Woodrum Fld)</td>\n",
       "      <td>Roanoke</td>\n",
       "      <td>VA</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>37.32</td>\n",
       "      <td>-79.98</td>\n",
       "      <td>US</td>\n",
       "      <td>United States</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>655</th>\n",
       "      <td>ROT</td>\n",
       "      <td>0.004</td>\n",
       "      <td>ROT</td>\n",
       "      <td>Rotorua International</td>\n",
       "      <td>Rotorua</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-38.11</td>\n",
       "      <td>176.32</td>\n",
       "      <td>NZ</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>656</th>\n",
       "      <td>SDD</td>\n",
       "      <td>0.004</td>\n",
       "      <td>SDD</td>\n",
       "      <td>Mukanka</td>\n",
       "      <td>Lubango</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-14.92</td>\n",
       "      <td>13.58</td>\n",
       "      <td>AO</td>\n",
       "      <td>Angola</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>657</th>\n",
       "      <td>RST</td>\n",
       "      <td>0.004</td>\n",
       "      <td>RST</td>\n",
       "      <td>Rochester International</td>\n",
       "      <td>Rochester</td>\n",
       "      <td>MN</td>\n",
       "      <td>Minnesota</td>\n",
       "      <td>43.91</td>\n",
       "      <td>-92.50</td>\n",
       "      <td>US</td>\n",
       "      <td>United States</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658</th>\n",
       "      <td>RSU</td>\n",
       "      <td>0.004</td>\n",
       "      <td>RSU</td>\n",
       "      <td>Yeosu/Suncheon</td>\n",
       "      <td>Yeosu/Suncheon</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>34.84</td>\n",
       "      <td>127.62</td>\n",
       "      <td>KR</td>\n",
       "      <td>South Korea</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>659</th>\n",
       "      <td>RVN</td>\n",
       "      <td>0.004</td>\n",
       "      <td>RVN</td>\n",
       "      <td>Rovaniemi</td>\n",
       "      <td>Rovaniemi</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>66.56</td>\n",
       "      <td>25.83</td>\n",
       "      <td>FI</td>\n",
       "      <td>Finland</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>660</th>\n",
       "      <td>RZE</td>\n",
       "      <td>0.004</td>\n",
       "      <td>RZE</td>\n",
       "      <td>Jasionka</td>\n",
       "      <td>Rzeszow</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50.12</td>\n",
       "      <td>22.02</td>\n",
       "      <td>PL</td>\n",
       "      <td>Poland</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>661</th>\n",
       "      <td>SAT</td>\n",
       "      <td>0.004</td>\n",
       "      <td>SAT</td>\n",
       "      <td>San Antonio Intl</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>TX</td>\n",
       "      <td>Texas</td>\n",
       "      <td>29.53</td>\n",
       "      <td>-98.47</td>\n",
       "      <td>US</td>\n",
       "      <td>United States</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>662</th>\n",
       "      <td>SAW</td>\n",
       "      <td>0.004</td>\n",
       "      <td>SAW</td>\n",
       "      <td>Sabiha Gokcen</td>\n",
       "      <td>Sabiha Gokcen</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>40.90</td>\n",
       "      <td>29.31</td>\n",
       "      <td>TR</td>\n",
       "      <td>Turkey</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>663</th>\n",
       "      <td>SBW</td>\n",
       "      <td>0.004</td>\n",
       "      <td>SBW</td>\n",
       "      <td>Sibu</td>\n",
       "      <td>Sibu, Sarawak</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2.34</td>\n",
       "      <td>111.84</td>\n",
       "      <td>MY</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>664</th>\n",
       "      <td>SCE</td>\n",
       "      <td>0.004</td>\n",
       "      <td>SCE</td>\n",
       "      <td>State College</td>\n",
       "      <td>State College</td>\n",
       "      <td>PA</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>40.77</td>\n",
       "      <td>-77.88</td>\n",
       "      <td>US</td>\n",
       "      <td>United States</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>665</th>\n",
       "      <td>AAE</td>\n",
       "      <td>0.004</td>\n",
       "      <td>AAE</td>\n",
       "      <td>Rabah Bitat</td>\n",
       "      <td>Annaba</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>36.82</td>\n",
       "      <td>7.81</td>\n",
       "      <td>DZ</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>666 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     dst  infected Code                               Name  \\\n",
       "0    PEK   773.196  PEK              Capital International   \n",
       "1    PVG   439.156  PVG               Pudong International   \n",
       "2    SHA   420.116  SHA             Hongqiao International   \n",
       "3    CAN   405.608  CAN               Baiyun International   \n",
       "4    KMG   370.712  KMG            Changshui International   \n",
       "5    CTU   339.500  CTU            Shuangliu International   \n",
       "6    SYX   323.492  SYX              Phoenix International   \n",
       "7    SZX   293.920  SZX               Bao'an International   \n",
       "8    HAK   282.512  HAK               Meilan International   \n",
       "9    XMN   270.460  XMN                Gaoqi International   \n",
       "10   HGH   227.440  HGH             Xiaoshan International   \n",
       "11   CKG   218.924  CKG             Jiangbei International   \n",
       "12   TSN   197.012  TSN               Binhai International   \n",
       "13   NNG   196.444  NNG                 Wuxu International   \n",
       "14   WNZ   195.168  WNZ            Yongqiang International   \n",
       "15   XIY   194.348  XIY             Xianyang International   \n",
       "16   TAO   190.292  TAO              Liuting International   \n",
       "17   KWE   176.712  KWE          Longdongbao International   \n",
       "18   SHE   135.216  SHE              Taoxian International   \n",
       "19   URC   117.372  URC               Diwopu International   \n",
       "20   ENH   105.056  ENH                          Xujiaping   \n",
       "21   FOC   102.036  FOC              Changle International   \n",
       "22   HRB    95.528  HRB              Taiping International   \n",
       "23   DLC    91.796  DLC           Zhoushuizi International   \n",
       "24   NGB    84.184  NGB                Lishe International   \n",
       "25   ZUH    73.308  ZUH               Sanzao International   \n",
       "26   CGQ    69.324  CGQ              Longjia International   \n",
       "27   HKG    69.216  HKG                     Hong Kong Intl   \n",
       "28   TYN    66.176  TYN                 Wusu International   \n",
       "29   LHW    61.344  LHW                         Zhongchuan   \n",
       "..   ...       ...  ...                                ...   \n",
       "636  TBS     0.004  TBS      Novo Alexeyevka International   \n",
       "637  TBU     0.004  TBU                          Fua'Amotu   \n",
       "638  CME     0.004  CME    Ciudad del Carmen International   \n",
       "639  TDX     0.004  TDX                       Trat Airport   \n",
       "640  TGG     0.004  TGG                      Sultan Mahmud   \n",
       "641  THS     0.004  THS                  Sukhothai Airport   \n",
       "642  TLL     0.004  TLL                       Lennart Meri   \n",
       "643  SIP     0.004  SIP           Simferopol International   \n",
       "644  SDF     0.004  SDF     International-Standiford Field   \n",
       "645  POZ     0.004  POZ                             Lawica   \n",
       "646  ROC     0.004  ROC    Greater Rochester International   \n",
       "647  PPQ     0.004  PPQ                        Paraparaumu   \n",
       "648  PPS     0.004  PPS      Puerto Princesa International   \n",
       "649  PQQ     0.004  PQQ                           Wauchope   \n",
       "650  PVD     0.004  PVD               T.F. Green State Apt   \n",
       "651  QKL     0.004  QKL                    Cologne-Main Rr   \n",
       "652  ENU     0.004  ENU  Akanu Ibiam International Airport   \n",
       "653  EBL     0.004  EBL        Erbil International Airport   \n",
       "654  ROA     0.004  ROA             Regional (Woodrum Fld)   \n",
       "655  ROT     0.004  ROT              Rotorua International   \n",
       "656  SDD     0.004  SDD                            Mukanka   \n",
       "657  RST     0.004  RST            Rochester International   \n",
       "658  RSU     0.004  RSU                     Yeosu/Suncheon   \n",
       "659  RVN     0.004  RVN                          Rovaniemi   \n",
       "660  RZE     0.004  RZE                           Jasionka   \n",
       "661  SAT     0.004  SAT                   San Antonio Intl   \n",
       "662  SAW     0.004  SAW                      Sabiha Gokcen   \n",
       "663  SBW     0.004  SBW                               Sibu   \n",
       "664  SCE     0.004  SCE                      State College   \n",
       "665  AAE     0.004  AAE                        Rabah Bitat   \n",
       "\n",
       "                     City State       State Name  Latitude  Longitude Country  \\\n",
       "0                 Beijing                            40.07     116.60      CN   \n",
       "1                Shanghai                            31.14     121.79      CN   \n",
       "2          Shanghai-Metro                            31.20     121.34      CN   \n",
       "3               Guangzhou                            23.12     113.23      CN   \n",
       "4                 Kunming                            25.10     102.94      CN   \n",
       "5                 Chengdu                            30.58     103.95      CN   \n",
       "6                   Sanya                            18.30     109.41      CN   \n",
       "7                Shenzhen                            22.55     113.92      CN   \n",
       "8                  Haikou                            19.94     110.46      CN   \n",
       "9                  Xiamen                            24.47     118.12      CN   \n",
       "10               Hangzhou                            30.24     120.44      CN   \n",
       "11              Chongqing                            29.50     106.36      CN   \n",
       "12                Tianjin                            39.12     117.35      CN   \n",
       "13                Nanning                            22.61     108.17      CN   \n",
       "14                Wenzhou                            27.95     120.63      CN   \n",
       "15                  Xi'an                            34.37     108.70      CN   \n",
       "16                Qingdao                            36.10     120.32      CN   \n",
       "17                Guiyang                            26.54     106.80      CN   \n",
       "18               Shenyang                            41.80     123.45      CN   \n",
       "19                 Urumqi                            43.91      87.47      CN   \n",
       "20                  Enshi                            30.28     109.32      CN   \n",
       "21                 Fuzhou                            26.10     119.28      CN   \n",
       "22    Harbin, P. R. China                            45.62     126.25      CN   \n",
       "23                 Dalian                            38.92     121.65      CN   \n",
       "24                 Ningbo                            29.83     121.53      CN   \n",
       "25                 Zhuhai                            22.01     113.38      CN   \n",
       "26              Changchun                            43.92     125.33      CN   \n",
       "27              Hong Kong                            22.30     113.92      HK   \n",
       "28                Taiyuan                            37.83     112.56      CN   \n",
       "29                Lanzhou                            36.02     103.75      CN   \n",
       "..                    ...   ...              ...       ...        ...     ...   \n",
       "636               Tbilisi                            41.67      44.96      GE   \n",
       "637  Tongatapu, Tonga Isl                           -21.24    -175.14      TO   \n",
       "638     Ciudad del Carmen                            18.65     -91.80      MX   \n",
       "639                  Trat                            12.27     102.32      TH   \n",
       "640      Kuala Terengganu                             5.38     103.11      MY   \n",
       "641             Sukhothai                            17.24      99.82      TH   \n",
       "642               Tallinn                            59.42      24.82      EE   \n",
       "643            Simferopol                            45.02      34.00      UA   \n",
       "644            Louisville    KY         Kentucky     38.18     -85.74      US   \n",
       "645                Poznan                            52.42      16.88      PL   \n",
       "646             Rochester    NY         New York     43.12     -77.67      US   \n",
       "647           Paraparaumu                           -40.90     174.98      NZ   \n",
       "648       Puerto Princesa                             9.74     118.75      PH   \n",
       "649        Port Macquarie    NS  New South Wales    -31.43     152.87      AU   \n",
       "650            Providence    RI     Rhode Island     41.73     -71.43      US   \n",
       "651       Cologne-Main Rr                            50.90       7.18      DE   \n",
       "652                 Enugu                             6.48       7.57      NG   \n",
       "653                 Erbil                            36.23      43.97      IQ   \n",
       "654               Roanoke    VA         Virginia     37.32     -79.98      US   \n",
       "655               Rotorua                           -38.11     176.32      NZ   \n",
       "656               Lubango                           -14.92      13.58      AO   \n",
       "657             Rochester    MN        Minnesota     43.91     -92.50      US   \n",
       "658        Yeosu/Suncheon                            34.84     127.62      KR   \n",
       "659             Rovaniemi                            66.56      25.83      FI   \n",
       "660               Rzeszow                            50.12      22.02      PL   \n",
       "661           San Antonio    TX            Texas     29.53     -98.47      US   \n",
       "662         Sabiha Gokcen                            40.90      29.31      TR   \n",
       "663         Sibu, Sarawak                             2.34     111.84      MY   \n",
       "664         State College    PA     Pennsylvania     40.77     -77.88      US   \n",
       "665                Annaba                            36.82       7.81      DZ   \n",
       "\n",
       "      Country Name  Global Region  \n",
       "0            China           Asia  \n",
       "1            China           Asia  \n",
       "2            China           Asia  \n",
       "3            China           Asia  \n",
       "4            China           Asia  \n",
       "5            China           Asia  \n",
       "6            China           Asia  \n",
       "7            China           Asia  \n",
       "8            China           Asia  \n",
       "9            China           Asia  \n",
       "10           China           Asia  \n",
       "11           China           Asia  \n",
       "12           China           Asia  \n",
       "13           China           Asia  \n",
       "14           China           Asia  \n",
       "15           China           Asia  \n",
       "16           China           Asia  \n",
       "17           China           Asia  \n",
       "18           China           Asia  \n",
       "19           China           Asia  \n",
       "20           China           Asia  \n",
       "21           China           Asia  \n",
       "22           China           Asia  \n",
       "23           China           Asia  \n",
       "24           China           Asia  \n",
       "25           China           Asia  \n",
       "26           China           Asia  \n",
       "27       Hong Kong           Asia  \n",
       "28           China           Asia  \n",
       "29           China           Asia  \n",
       "..             ...            ...  \n",
       "636        Georgia           Asia  \n",
       "637          Tonga    Australasia  \n",
       "638         Mexico  North America  \n",
       "639       Thailand           Asia  \n",
       "640       Malaysia           Asia  \n",
       "641       Thailand           Asia  \n",
       "642        Estonia         Europe  \n",
       "643        Ukraine         Europe  \n",
       "644  United States  North America  \n",
       "645         Poland         Europe  \n",
       "646  United States  North America  \n",
       "647    New Zealand    Australasia  \n",
       "648    Philippines           Asia  \n",
       "649      Australia    Australasia  \n",
       "650  United States  North America  \n",
       "651        Germany         Europe  \n",
       "652        Nigeria         Africa  \n",
       "653           Iraq    Middle East  \n",
       "654  United States  North America  \n",
       "655    New Zealand    Australasia  \n",
       "656         Angola         Africa  \n",
       "657  United States  North America  \n",
       "658    South Korea           Asia  \n",
       "659        Finland         Europe  \n",
       "660         Poland         Europe  \n",
       "661  United States  North America  \n",
       "662         Turkey         Europe  \n",
       "663       Malaysia           Asia  \n",
       "664  United States  North America  \n",
       "665        Algeria         Africa  \n",
       "\n",
       "[666 rows x 12 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get airport info for the ranking list\n",
    "result = pd.merge(df_rank, df_iata, how='left', left_on='dst',right_on='Code')\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# output\n",
    "output_csv = folder + 'output_airport_' + str(cut) + '_'  + t.strftime('%m%d%y%H%M') + '.csv'\n",
    "result.to_csv(output_csv, index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "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>City</th>\n",
       "      <th>infected</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Beijing</td>\n",
       "      <td>46.016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Guangzhou</td>\n",
       "      <td>35.332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Shanghai</td>\n",
       "      <td>20.376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Shanghai-Metro</td>\n",
       "      <td>19.732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Shenzhen</td>\n",
       "      <td>19.704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Haikou</td>\n",
       "      <td>19.044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Sanya</td>\n",
       "      <td>17.772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Kunming</td>\n",
       "      <td>16.704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Chengdu</td>\n",
       "      <td>14.928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Xiamen</td>\n",
       "      <td>14.632</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             City  infected\n",
       "0         Beijing    46.016\n",
       "1       Guangzhou    35.332\n",
       "2        Shanghai    20.376\n",
       "3  Shanghai-Metro    19.732\n",
       "4        Shenzhen    19.704\n",
       "5          Haikou    19.044\n",
       "6           Sanya    17.772\n",
       "7         Kunming    16.704\n",
       "8         Chengdu    14.928\n",
       "9          Xiamen    14.632"
      ]
     },
     "execution_count": 275,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df_rank_city = result.groupby('City')['infected'].sum().reset_index()\n",
    "# df_rank_city = df_rank_city.sort_values(by='infected',ascending = False).reset_index(drop=True)\n",
    "# df_rank_city.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### country risk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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 Name</th>\n",
       "      <th>infected</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>China</td>\n",
       "      <td>6970.300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Thailand</td>\n",
       "      <td>116.612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>69.216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Taiwan</td>\n",
       "      <td>55.976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>South Korea</td>\n",
       "      <td>44.232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Macau</td>\n",
       "      <td>25.436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Singapore</td>\n",
       "      <td>20.164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Japan</td>\n",
       "      <td>19.400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>United States</td>\n",
       "      <td>14.292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Australia</td>\n",
       "      <td>9.084</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Country Name  infected\n",
       "0          China  6970.300\n",
       "1       Thailand   116.612\n",
       "2      Hong Kong    69.216\n",
       "3         Taiwan    55.976\n",
       "4    South Korea    44.232\n",
       "5          Macau    25.436\n",
       "6      Singapore    20.164\n",
       "7          Japan    19.400\n",
       "8  United States    14.292\n",
       "9      Australia     9.084"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_rank_country = result.groupby('Country Name')['infected'].sum().reset_index()\n",
    "df_rank_country = df_rank_country.sort_values(by='infected',ascending = False).reset_index(drop=True)\n",
    "df_rank_country.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = df_rank_country\n",
    "output_csv = folder + 'output_country_' + str(cut) + '_'  + t.strftime('%m%d%y%H%M') + '.csv'\n",
    "result.to_csv(output_csv, index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}