notebooks/dataprep/02a-JHUCasesLocation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COVID-19 Case Counts from Johns Hopkins University"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**[Work in progress]**\n",
    "\n",
    "This notebook adds location information for case counts for ingestion into the Knowledge Graph.\n",
    "\n",
    "Data source: [COVID-19 Data Repository by the Center for Systems Science and Engineering (CSSE) at Johns Hopkins University](https://github.com/CSSEGISandData/COVID-19)\n",
    "\n",
    "Author: Peter Rose (pwrose@ucsd.edu)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from pathlib import Path\n",
    "import pandas as pd\n",
    "import dateutil"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.options.display.max_rows = None  # display all rows\n",
    "pd.options.display.max_columns = None  # display all columsns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/peter/Library/Application Support/Neo4j Desktop/Application/neo4jDatabases/database-19636412-9e74-4bac-8a4c-c6c8b49bb9d3/installation-4.1.0/import\n"
     ]
    }
   ],
   "source": [
    "NEO4J_IMPORT = Path(os.getenv('NEO4J_IMPORT'))\n",
    "print(NEO4J_IMPORT)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(NEO4J_IMPORT / \"02a-JHUCasesGlobal.csv\", dtype='str')\n",
    "df.fillna('', inplace=True)\n",
    "df['country'] = df['country'].str.strip()\n",
    "df['admin1'] = df['admin1'].str.strip()"
   ]
  },
  {
   "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</th>\n",
       "      <th>admin1</th>\n",
       "      <th>cummulativeConfirmed</th>\n",
       "      <th>date</th>\n",
       "      <th>cummulativeDeaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>China</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>China</td>\n",
       "      <td>Beijing</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>China</td>\n",
       "      <td>Chongqing</td>\n",
       "      <td>6</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>China</td>\n",
       "      <td>Fujian</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>China</td>\n",
       "      <td>Guangdong</td>\n",
       "      <td>26</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country     admin1 cummulativeConfirmed        date cummulativeDeaths\n",
       "0   China      Anhui                    1  2020-01-22                 0\n",
       "1   China    Beijing                   14  2020-01-22                 0\n",
       "2   China  Chongqing                    6  2020-01-22                 0\n",
       "3   China     Fujian                    1  2020-01-22                 0\n",
       "4   China  Guangdong                   26  2020-01-22                 0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Standardize country names to match GeoNames.org"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "ref = pd.read_csv(\"../../reference_data/SpecialLocations.csv\", comment='#', dtype='str')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert dataframe into a dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "ref['val'] = ref[['geoname', 'type']].values.tolist()\n",
    "name_list = ref[['name', 'val']].values.tolist()\n",
    "name_dict = {name: val for name, val in name_list}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def standardize_locations(row):\n",
    "    country = row['country']\n",
    "    admin1 = row['admin1']\n",
    "    cruiseship = ''\n",
    "    \n",
    "    cname = name_dict.get(country)\n",
    "    if cname != None:\n",
    "        if cname[1] == 'Country':\n",
    "            country = cname[0]\n",
    "        elif cname[1] == 'CruiseShip':\n",
    "            country = ''\n",
    "            admin1 = ''\n",
    "            cruiseship = cname[0]\n",
    "\n",
    "    aname = name_dict.get(admin1)    \n",
    "    if aname != None:\n",
    "        if aname[1] == 'Admin1':\n",
    "            admin1 = aname[0]\n",
    "        elif aname[1] == 'Country':\n",
    "            # dependent territories according to ISO are represented at the country level\n",
    "            admin1 = ''\n",
    "            country = aname[0]\n",
    "        elif aname[1] == 'CruiseShip':\n",
    "            country = ''\n",
    "            admin1 = ''\n",
    "            cruiseship = aname[0]\n",
    "\n",
    "            \n",
    "    return country, admin1, cruiseship"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[['country','admin1','cruiseship']] = df.apply(standardize_locations, axis=1, result_type=\"expand\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check that country names match GeoNames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries = pd.read_csv(NEO4J_IMPORT / \"00e-GeoNamesCountry.csv\")\n",
    "loc0 = df[['country']].copy()\n",
    "loc0 = loc0.merge(countries, left_on='country', right_on='name', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Country name mismatches:\n",
      "['' 'Bonaire, Saint Eustatius and Saba']\n"
     ]
    }
   ],
   "source": [
    "loc0.fillna('', inplace=True)\n",
    "loc0 = loc0.query(\"name == ''\")\n",
    "country_mismatches = loc0['country'].unique()\n",
    "print(\"Country name mismatches:\")\n",
    "print(country_mismatches)\n",
    "# TODO comma in string 'Bonaire, Saint Eustatius and Saba' is not handled properly"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check that admin1 names match GeoNames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "admin1 = pd.read_csv(NEO4J_IMPORT / \"00f-GeoNamesAdmin1.csv\")\n",
    "loc1 = df[['admin1']].copy()\n",
    "loc1 = loc1.query(\"admin1 != ''\")\n",
    "loc1 = loc1.merge(admin1, left_on='admin1', right_on='name', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Admin1 name mismatches:\n",
      "[]\n"
     ]
    }
   ],
   "source": [
    "loc1.fillna('', inplace=True)\n",
    "loc1 = loc1.query(\"name == ''\")\n",
    "loc1_mismatches = loc1['admin1'].unique()\n",
    "print(\"Admin1 name mismatches:\")\n",
    "print(loc1_mismatches)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Save cases for countries"
   ]
  },
  {
   "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>country</th>\n",
       "      <th>date</th>\n",
       "      <th>cummulativeConfirmed</th>\n",
       "      <th>cummulativeDeaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Macao</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Japan</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>South Korea</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Taiwan</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Thailand</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        country        date cummulativeConfirmed cummulativeDeaths\n",
       "15        Macao  2020-01-22                    1                 0\n",
       "24        Japan  2020-01-22                    2                 0\n",
       "25  South Korea  2020-01-22                    1                 0\n",
       "26       Taiwan  2020-01-22                    1                 0\n",
       "27     Thailand  2020-01-22                    2                 0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cases_country = df[(df['country'] != '') & (df['admin1'] == '')]\n",
    "cases_country = cases_country[['country', 'date', 'cummulativeConfirmed', 'cummulativeDeaths']]\n",
    "cases_country.to_csv(NEO4J_IMPORT / \"02a-JHUCasesGlobalCountry.csv\", index=False)\n",
    "cases_country.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Save cases for admin1 divisions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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</th>\n",
       "      <th>admin1</th>\n",
       "      <th>date</th>\n",
       "      <th>cummulativeConfirmed</th>\n",
       "      <th>cummulativeDeaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>China</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>China</td>\n",
       "      <td>Beijing</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>China</td>\n",
       "      <td>Chongqing</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>China</td>\n",
       "      <td>Fujian</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>China</td>\n",
       "      <td>Guangdong</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country     admin1        date cummulativeConfirmed cummulativeDeaths\n",
       "0   China      Anhui  2020-01-22                    1                 0\n",
       "1   China    Beijing  2020-01-22                   14                 0\n",
       "2   China  Chongqing  2020-01-22                    6                 0\n",
       "3   China     Fujian  2020-01-22                    1                 0\n",
       "4   China  Guangdong  2020-01-22                   26                 0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cases_admin1 = df[(df['country'] != '') & (df['admin1'] != '')]\n",
    "cases_admin1 = cases_admin1[['country', 'admin1', 'date', 'cummulativeConfirmed', 'cummulativeDeaths']]\n",
    "cases_admin1.to_csv(NEO4J_IMPORT / \"02a-JHUCasesGlobalAdmin1.csv\", index=False)\n",
    "cases_admin1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Save cases for cruise ships"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cruiseship</th>\n",
       "      <th>date</th>\n",
       "      <th>cummulativeConfirmed</th>\n",
       "      <th>cummulativeDeaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>865</th>\n",
       "      <td>Diamond Princess</td>\n",
       "      <td>2020-02-07</td>\n",
       "      <td>61</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>928</th>\n",
       "      <td>Diamond Princess</td>\n",
       "      <td>2020-02-08</td>\n",
       "      <td>61</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>991</th>\n",
       "      <td>Diamond Princess</td>\n",
       "      <td>2020-02-09</td>\n",
       "      <td>64</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1054</th>\n",
       "      <td>Diamond Princess</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>135</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1117</th>\n",
       "      <td>Diamond Princess</td>\n",
       "      <td>2020-02-11</td>\n",
       "      <td>135</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            cruiseship        date cummulativeConfirmed cummulativeDeaths\n",
       "865   Diamond Princess  2020-02-07                   61                 0\n",
       "928   Diamond Princess  2020-02-08                   61                 0\n",
       "991   Diamond Princess  2020-02-09                   64                 0\n",
       "1054  Diamond Princess  2020-02-10                  135                 0\n",
       "1117  Diamond Princess  2020-02-11                  135                 0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cases_cruiseship = df[df['cruiseship'] != '']\n",
    "cases_cruiseship = cases_cruiseship[['cruiseship', 'date', 'cummulativeConfirmed', 'cummulativeDeaths']]\n",
    "cases_cruiseship.to_csv(NEO4J_IMPORT / \"02a-JHUCasesGlobalCruiseShip.csv\", index=False)\n",
    "cases_cruiseship.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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}