notebooks/dataprep/01d-Nextstrain.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load SARS-CoV-2 Strain Metadata from Nextstrain.org\n",
    "**[Work in progress]**\n",
    "\n",
    "This notebook downloads and standardizes SARS-CoV-2 strain metadata from [Nextstrain.org](https://nextstrain.org) for ingestion into a Knowledge Graph. \n",
    "\n",
    "This notebook uses a local copy of the [nextstrain_ncov_global_metadata.tsv](../../reference_data/nextstrain_ncov_global_metadata.tsv) file, since it must be manually downloaded from Nextstrain.org. It will not be updated daily.\n",
    "\n",
    "Additional information about these strains will be loaded later in the [01e-CNCBStrain.ipynb](01e-CNCBStrain.ipynb) notebook.\n",
    "\n",
    "Data source: [Nextstrain.org](https://nextstrain.org/ncov/)\n",
    "\n",
    "Author: Peter Rose (pwrose@ucsd.edu)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import csv\n",
    "import pandas as pd\n",
    "import dateutil\n",
    "from pathlib import Path"
   ]
  },
  {
   "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/com.Neo4j.Relate/data/dbmss/dbms-8bf637fc-0d20-4d9f-9c6f-f7e72e92a4da/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(\"../../reference_data/nextstrain_ncov_global_metadata.tsv\", sep = '\\t', dtype=str, error_bad_lines=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transform and standardize data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Graph databases don't have \"null\" values. By setting missing values to '', they will not be represented in the graph."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.replace('?', '', inplace=True)\n",
    "df.replace('Unknown', '', inplace=True)\n",
    "df.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Strain</th>\n",
       "      <th>GISAID Clade</th>\n",
       "      <th>Age</th>\n",
       "      <th>Clade</th>\n",
       "      <th>Country</th>\n",
       "      <th>Country of exposure</th>\n",
       "      <th>Admin Division</th>\n",
       "      <th>Division of exposure</th>\n",
       "      <th>gisaid_epi_isl</th>\n",
       "      <th>Host</th>\n",
       "      <th>Old Nextstrain clade</th>\n",
       "      <th>Originating Lab</th>\n",
       "      <th>Pangolin lineage</th>\n",
       "      <th>Submission Date</th>\n",
       "      <th>Region</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Emerging clade</th>\n",
       "      <th>Submitting Lab</th>\n",
       "      <th>url</th>\n",
       "      <th>Collection Data</th>\n",
       "      <th>Author</th>\n",
       "      <th>Location</th>\n",
       "      <th>genbank_accession</th>\n",
       "      <th>Region of exposure</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Guangdong/GD2020139-P0007/2020</td>\n",
       "      <td>L</td>\n",
       "      <td>53</td>\n",
       "      <td>19A</td>\n",
       "      <td>China</td>\n",
       "      <td>China</td>\n",
       "      <td>Guangdong</td>\n",
       "      <td>Guangdong</td>\n",
       "      <td>EPI_ISL_413882</td>\n",
       "      <td>Human</td>\n",
       "      <td></td>\n",
       "      <td>Guangdong Provincial Institution of Public Hea...</td>\n",
       "      <td>B</td>\n",
       "      <td>Older</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Male</td>\n",
       "      <td>19A</td>\n",
       "      <td>Guangdong Provincial Institution of Public Health</td>\n",
       "      <td></td>\n",
       "      <td>2020-02-02</td>\n",
       "      <td>Jing Lu et al (https://dx.doi.org/10.1016/j.ce...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Wuhan/HBCDC-HB-01/2019</td>\n",
       "      <td>L</td>\n",
       "      <td>49</td>\n",
       "      <td>19A</td>\n",
       "      <td>China</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>EPI_ISL_402132</td>\n",
       "      <td>Human</td>\n",
       "      <td></td>\n",
       "      <td>Wuhan Jinyintan Hospital</td>\n",
       "      <td>B</td>\n",
       "      <td>Older</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Female</td>\n",
       "      <td>19A</td>\n",
       "      <td>Hubei Provincial Center for Disease Control an...</td>\n",
       "      <td></td>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>Bin Fang et al (https://dx.doi.org/10.1101/202...</td>\n",
       "      <td>Wuhan</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Wuhan/WH01/2019</td>\n",
       "      <td>L</td>\n",
       "      <td>44</td>\n",
       "      <td>19A</td>\n",
       "      <td>China</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>EPI_ISL_406798</td>\n",
       "      <td>Human</td>\n",
       "      <td></td>\n",
       "      <td>General Hospital of Central Theater Command of...</td>\n",
       "      <td>B</td>\n",
       "      <td>Older</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Male</td>\n",
       "      <td>19A</td>\n",
       "      <td>BGI &amp; Institute of Microbiology, Chinese Acade...</td>\n",
       "      <td></td>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>Weijun Chen et al (https://dx.doi.org/10.1016/...</td>\n",
       "      <td>Wuhan</td>\n",
       "      <td>LR757998</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wuhan/WIV07/2019</td>\n",
       "      <td>L</td>\n",
       "      <td>56</td>\n",
       "      <td>19A</td>\n",
       "      <td>China</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>EPI_ISL_402130</td>\n",
       "      <td>Human</td>\n",
       "      <td></td>\n",
       "      <td>Wuhan Jinyintan Hospital</td>\n",
       "      <td>B</td>\n",
       "      <td>Older</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Male</td>\n",
       "      <td>19A</td>\n",
       "      <td>Wuhan Institute of Virology, Chinese Academy o...</td>\n",
       "      <td></td>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>Peng Zhou et al</td>\n",
       "      <td>Wuhan</td>\n",
       "      <td>MN996531</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Wuhan/IPBCAMS-WH-01/2019</td>\n",
       "      <td>L</td>\n",
       "      <td>65</td>\n",
       "      <td>19A</td>\n",
       "      <td>China</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>EPI_ISL_402123</td>\n",
       "      <td>Human</td>\n",
       "      <td></td>\n",
       "      <td>Institute of Pathogen Biology, Chinese Academy...</td>\n",
       "      <td>B</td>\n",
       "      <td>Older</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Male</td>\n",
       "      <td>19A</td>\n",
       "      <td>Institute of Pathogen Biology, Chinese Academy...</td>\n",
       "      <td></td>\n",
       "      <td>2019-12-24</td>\n",
       "      <td>Lili Ren et al A</td>\n",
       "      <td>Wuhan</td>\n",
       "      <td>MT019529</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           Strain GISAID Clade Age Clade Country  \\\n",
       "0  Guangdong/GD2020139-P0007/2020            L  53   19A   China   \n",
       "1          Wuhan/HBCDC-HB-01/2019            L  49   19A   China   \n",
       "2                 Wuhan/WH01/2019            L  44   19A   China   \n",
       "3                Wuhan/WIV07/2019            L  56   19A   China   \n",
       "4        Wuhan/IPBCAMS-WH-01/2019            L  65   19A   China   \n",
       "\n",
       "  Country of exposure Admin Division Division of exposure  gisaid_epi_isl  \\\n",
       "0               China      Guangdong            Guangdong  EPI_ISL_413882   \n",
       "1               China          Hubei                Hubei  EPI_ISL_402132   \n",
       "2               China          Hubei                Hubei  EPI_ISL_406798   \n",
       "3               China          Hubei                Hubei  EPI_ISL_402130   \n",
       "4               China          Hubei                Hubei  EPI_ISL_402123   \n",
       "\n",
       "    Host Old Nextstrain clade  \\\n",
       "0  Human                        \n",
       "1  Human                        \n",
       "2  Human                        \n",
       "3  Human                        \n",
       "4  Human                        \n",
       "\n",
       "                                     Originating Lab Pangolin lineage  \\\n",
       "0  Guangdong Provincial Institution of Public Hea...                B   \n",
       "1                           Wuhan Jinyintan Hospital                B   \n",
       "2  General Hospital of Central Theater Command of...                B   \n",
       "3                           Wuhan Jinyintan Hospital                B   \n",
       "4  Institute of Pathogen Biology, Chinese Academy...                B   \n",
       "\n",
       "  Submission Date Region     Sex Emerging clade  \\\n",
       "0           Older   Asia    Male            19A   \n",
       "1           Older   Asia  Female            19A   \n",
       "2           Older   Asia    Male            19A   \n",
       "3           Older   Asia    Male            19A   \n",
       "4           Older   Asia    Male            19A   \n",
       "\n",
       "                                      Submitting Lab url Collection Data  \\\n",
       "0  Guangdong Provincial Institution of Public Health          2020-02-02   \n",
       "1  Hubei Provincial Center for Disease Control an...          2019-12-30   \n",
       "2  BGI & Institute of Microbiology, Chinese Acade...          2019-12-26   \n",
       "3  Wuhan Institute of Virology, Chinese Academy o...          2019-12-30   \n",
       "4  Institute of Pathogen Biology, Chinese Academy...          2019-12-24   \n",
       "\n",
       "                                              Author Location  \\\n",
       "0  Jing Lu et al (https://dx.doi.org/10.1016/j.ce...            \n",
       "1  Bin Fang et al (https://dx.doi.org/10.1101/202...    Wuhan   \n",
       "2  Weijun Chen et al (https://dx.doi.org/10.1016/...    Wuhan   \n",
       "3                                    Peng Zhou et al    Wuhan   \n",
       "4                                   Lili Ren et al A    Wuhan   \n",
       "\n",
       "  genbank_accession Region of exposure  \n",
       "0                                       \n",
       "1                                       \n",
       "2          LR757998                     \n",
       "3          MN996531                     \n",
       "4          MT019529                     "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Apply Neo4j property naming conventions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={'Strain': 'name', 'Clade': 'clade', 'Age': 'age', 'Sex': 'sex'}, inplace=True)\n",
    "df.rename(columns={'Country of exposure': 'exposureCountry', 'Division of exposure': 'exposureAdmin1'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fix collection dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for consistency with NCBI, use lower case\n",
    "df['sex'] = df['sex'].str.lower()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create unique and interoperable identifiers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**id**: URI: [https://www.gisaid.org/](https://www.gisaid.org/help/publish-with-gisaid-references) (Global Initiative on Sharing All Influenza Data, GISAID)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['id'] = 'https://www.gisaid.org/' + df['gisaid_epi_isl']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save data for Knowledge Graph Import"
   ]
  },
  {
   "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>id</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>clade</th>\n",
       "      <th>exposureCountry</th>\n",
       "      <th>exposureAdmin1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://www.gisaid.org/EPI_ISL_413882</td>\n",
       "      <td>male</td>\n",
       "      <td>53</td>\n",
       "      <td>19A</td>\n",
       "      <td>China</td>\n",
       "      <td>Guangdong</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://www.gisaid.org/EPI_ISL_402132</td>\n",
       "      <td>female</td>\n",
       "      <td>49</td>\n",
       "      <td>19A</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://www.gisaid.org/EPI_ISL_406798</td>\n",
       "      <td>male</td>\n",
       "      <td>44</td>\n",
       "      <td>19A</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://www.gisaid.org/EPI_ISL_402130</td>\n",
       "      <td>male</td>\n",
       "      <td>56</td>\n",
       "      <td>19A</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://www.gisaid.org/EPI_ISL_402123</td>\n",
       "      <td>male</td>\n",
       "      <td>65</td>\n",
       "      <td>19A</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      id     sex age clade exposureCountry  \\\n",
       "0  https://www.gisaid.org/EPI_ISL_413882    male  53   19A           China   \n",
       "1  https://www.gisaid.org/EPI_ISL_402132  female  49   19A           China   \n",
       "2  https://www.gisaid.org/EPI_ISL_406798    male  44   19A           China   \n",
       "3  https://www.gisaid.org/EPI_ISL_402130    male  56   19A           China   \n",
       "4  https://www.gisaid.org/EPI_ISL_402123    male  65   19A           China   \n",
       "\n",
       "  exposureAdmin1  \n",
       "0      Guangdong  \n",
       "1          Hubei  \n",
       "2          Hubei  \n",
       "3          Hubei  \n",
       "4          Hubei  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[['id', 'sex', 'age', 'clade', 'exposureCountry', 'exposureAdmin1']]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(NEO4J_IMPORT / \"01d-Nextstrain.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5174, 6)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}