notebooks/dataprep/01c-CNCBStrain.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load SARS-CoV-2 Virus Strain Metadata from CNCB\n",
    "**[Work in progress]**\n",
    "\n",
    "This notebook downloads and standardizes viral strain data from CNCB for ingestion into a Knowledge Graph.\n",
    "\n",
    "Data source: [China National Center for Bioinformation, 2019 Novel Coronavirus Resource (2019nCoVR)](https://bigd.big.ac.cn/ncov/release_genome)\n",
    "\n",
    "Author: Peter Rose (pwrose@ucsd.edu)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import shutil\n",
    "import glob\n",
    "import ftplib\n",
    "import re\n",
    "import requests\n",
    "import json\n",
    "import dateutil\n",
    "import pandas as pd\n",
    "import numpy as np\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": [],
   "source": [
    "# Pangolin lineage (https://github.com/cov-lineages/pangolin)\n",
    "source = 'CNCB'\n",
    "software = 'pangolin v.3.0.3'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadata_url = \"https://bigd.big.ac.cn/ncov/genome/export/meta\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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": [
    "# Path will take care of handling operating system differences.\n",
    "NEO4J_IMPORT = Path(os.getenv('NEO4J_IMPORT'))\n",
    "print(NEO4J_IMPORT)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Download strain metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df = pd.read_excel(metadata_url, dtype='str')\n",
    "df = pd.read_csv(metadata_url, sep='\\t', dtype='str')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of strains: 1905434\n"
     ]
    }
   ],
   "source": [
    "print(\"Total number of strains:\", df.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Virus Strain Name</th>\n",
       "      <th>Accession ID</th>\n",
       "      <th>Data Source</th>\n",
       "      <th>Related ID</th>\n",
       "      <th>Lineage</th>\n",
       "      <th>Nuc.Completeness</th>\n",
       "      <th>Sequence Length</th>\n",
       "      <th>Sequence Quality</th>\n",
       "      <th>Quality Assessment</th>\n",
       "      <th>Host</th>\n",
       "      <th>Sample Collection Date</th>\n",
       "      <th>Location</th>\n",
       "      <th>Originating Lab</th>\n",
       "      <th>Submission Date</th>\n",
       "      <th>Submitting Lab</th>\n",
       "      <th>Create Time</th>\n",
       "      <th>Last Update Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BetaCoV/Wuhan/HBCDC-HB-01/2019</td>\n",
       "      <td>NMDC60013088-01</td>\n",
       "      <td>NMDC</td>\n",
       "      <td>EPI_ISL_402132</td>\n",
       "      <td>B</td>\n",
       "      <td>Complete</td>\n",
       "      <td>29848</td>\n",
       "      <td>High</td>\n",
       "      <td>0/0/-/-/-</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>China / Hubei</td>\n",
       "      <td>Hubei Provincial Center for Disease Control an...</td>\n",
       "      <td>2020-01-19</td>\n",
       "      <td>Hubei Provincial Center for Disease Control an...</td>\n",
       "      <td>2020-01-20 12:04:48</td>\n",
       "      <td>2021-03-16 10:46:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>hCoV-19/Thailand/74/2020</td>\n",
       "      <td>EPI_ISL_403963</td>\n",
       "      <td>GISAID</td>\n",
       "      <td></td>\n",
       "      <td>B</td>\n",
       "      <td>Complete</td>\n",
       "      <td>29859</td>\n",
       "      <td>High</td>\n",
       "      <td>0/0/-/-/-</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2020-01-13</td>\n",
       "      <td>Thailand/ Nonthaburi Province</td>\n",
       "      <td>Department of Medical Sciences, Ministry of Pu...</td>\n",
       "      <td>2020-01-17</td>\n",
       "      <td>Department of Medical Sciences, Ministry of Pu...</td>\n",
       "      <td>2020-01-20 12:04:48</td>\n",
       "      <td>2021-03-16 10:46:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>hCoV-19/Thailand/61/2020</td>\n",
       "      <td>EPI_ISL_403962</td>\n",
       "      <td>GISAID</td>\n",
       "      <td></td>\n",
       "      <td>B</td>\n",
       "      <td>Complete</td>\n",
       "      <td>29848</td>\n",
       "      <td>High</td>\n",
       "      <td>0/0/-/-/-</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>Thailand/ Nonthaburi Province</td>\n",
       "      <td>Department of Medical Sciences, Ministry of Pu...</td>\n",
       "      <td>2020-01-17</td>\n",
       "      <td>Department of Medical Sciences, Ministry of Pu...</td>\n",
       "      <td>2020-01-20 12:04:48</td>\n",
       "      <td>2021-03-16 10:46:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BetaCoV/Wuhan/IVDC-HB-04/2020</td>\n",
       "      <td>NMDC60013085-01</td>\n",
       "      <td>NMDC</td>\n",
       "      <td>EPI_ISL_402120</td>\n",
       "      <td>B</td>\n",
       "      <td>Complete</td>\n",
       "      <td>29896</td>\n",
       "      <td>High</td>\n",
       "      <td>0/0/-/-/-</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>China / Hubei / Wuhan</td>\n",
       "      <td>National Institute for Viral Disease Control a...</td>\n",
       "      <td>2020-01-11</td>\n",
       "      <td>National Institute for Viral Disease Control a...</td>\n",
       "      <td>2020-01-20 12:04:48</td>\n",
       "      <td>2021-03-16 10:46:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BetaCoV/Wuhan/IVDC-HB-01/2019</td>\n",
       "      <td>NMDC60013084-01</td>\n",
       "      <td>NMDC</td>\n",
       "      <td>EPI_ISL_402119</td>\n",
       "      <td>B</td>\n",
       "      <td>Complete</td>\n",
       "      <td>29891</td>\n",
       "      <td>High</td>\n",
       "      <td>0/0/-/-/-</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>China / Hubei / Wuhan</td>\n",
       "      <td>National Institute for Viral Disease Control a...</td>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>National Institute for Viral Disease Control a...</td>\n",
       "      <td>2020-01-20 12:04:48</td>\n",
       "      <td>2021-03-16 10:46:59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Virus Strain Name     Accession ID Data Source  \\\n",
       "0  BetaCoV/Wuhan/HBCDC-HB-01/2019  NMDC60013088-01        NMDC   \n",
       "1        hCoV-19/Thailand/74/2020   EPI_ISL_403963      GISAID   \n",
       "2        hCoV-19/Thailand/61/2020   EPI_ISL_403962      GISAID   \n",
       "3   BetaCoV/Wuhan/IVDC-HB-04/2020  NMDC60013085-01        NMDC   \n",
       "4   BetaCoV/Wuhan/IVDC-HB-01/2019  NMDC60013084-01        NMDC   \n",
       "\n",
       "       Related ID Lineage Nuc.Completeness Sequence Length Sequence Quality  \\\n",
       "0  EPI_ISL_402132       B         Complete           29848             High   \n",
       "1                       B         Complete           29859             High   \n",
       "2                       B         Complete           29848             High   \n",
       "3  EPI_ISL_402120       B         Complete           29896             High   \n",
       "4  EPI_ISL_402119       B         Complete           29891             High   \n",
       "\n",
       "  Quality Assessment          Host Sample Collection Date  \\\n",
       "0          0/0/-/-/-  Homo sapiens             2019-12-30   \n",
       "1          0/0/-/-/-  Homo sapiens             2020-01-13   \n",
       "2          0/0/-/-/-  Homo sapiens             2020-01-08   \n",
       "3          0/0/-/-/-  Homo sapiens             2020-01-01   \n",
       "4          0/0/-/-/-  Homo sapiens             2019-12-30   \n",
       "\n",
       "                        Location  \\\n",
       "0                  China / Hubei   \n",
       "1  Thailand/ Nonthaburi Province   \n",
       "2  Thailand/ Nonthaburi Province   \n",
       "3          China / Hubei / Wuhan   \n",
       "4          China / Hubei / Wuhan   \n",
       "\n",
       "                                     Originating Lab Submission Date  \\\n",
       "0  Hubei Provincial Center for Disease Control an...      2020-01-19   \n",
       "1  Department of Medical Sciences, Ministry of Pu...      2020-01-17   \n",
       "2  Department of Medical Sciences, Ministry of Pu...      2020-01-17   \n",
       "3  National Institute for Viral Disease Control a...      2020-01-11   \n",
       "4  National Institute for Viral Disease Control a...      2020-01-10   \n",
       "\n",
       "                                      Submitting Lab          Create Time  \\\n",
       "0  Hubei Provincial Center for Disease Control an...  2020-01-20 12:04:48   \n",
       "1  Department of Medical Sciences, Ministry of Pu...  2020-01-20 12:04:48   \n",
       "2  Department of Medical Sciences, Ministry of Pu...  2020-01-20 12:04:48   \n",
       "3  National Institute for Viral Disease Control a...  2020-01-20 12:04:48   \n",
       "4  National Institute for Viral Disease Control a...  2020-01-20 12:04:48   \n",
       "\n",
       "      Last Update Time  \n",
       "0  2021-03-16 10:46:59  \n",
       "1  2021-03-16 10:46:59  \n",
       "2  2021-03-16 10:46:59  \n",
       "3  2021-03-16 10:46:59  \n",
       "4  2021-03-16 10:46:59  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Assign identifiers, aliases, and assign compact identifiers (CURIES)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://registry.identifiers.org/registry/insdc\n",
    "insdc_pattern = re.compile('^([A-Z]\\d{5}|[A-Z]{2}\\d{6}|[A-Z]{4}\\d{8}|[A-J][A-Z]{2}\\d{5})(\\.\\d+)?$')\n",
    "# https://registry.identifiers.org/registry/refseq\n",
    "refseq_pattern = re.compile('^(((AC|AP|NC|NG|NM|NP|NR|NT|NW|XM|XP|XR|YP|ZP)_\\d+)|(NZ\\_[A-Z]{2,4}\\d+))(\\.\\d+)?$')\n",
    "epi_pattern = re.compile('^EPI_ISL_\\d+$')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def assign_curie(id):\n",
    "    id = id.strip()\n",
    "    if len(id) > 0:\n",
    "        if epi_pattern.match(id) != None:\n",
    "            return 'https://www.gisaid.org/' + id\n",
    "        elif refseq_pattern.match(id) != None:\n",
    "            return 'refseq:' + id\n",
    "        elif insdc_pattern.match(id) != None:\n",
    "            return 'insdc:' + id\n",
    "        else:\n",
    "            # TODO are URIs available for these cases?\n",
    "            return id\n",
    "    else:\n",
    "        return id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def assign_curies(ids):\n",
    "    return [assign_curie(id) for id in ids.split(',')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_gisaid_id(ids):\n",
    "    for id in ids:\n",
    "        if id.startswith('https://www.gisaid.org/'):\n",
    "            return id\n",
    "        \n",
    "    return ''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Rename and concatenate fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Related ID'] = df['Related ID'].str.replace('-', '')\n",
    "\n",
    "# combine all ids into an accession column and assign curies\n",
    "df['accessions'] = df['Accession ID'] + df['Related ID'].apply(lambda s: ',' + s if len(s) > 0 else s)\n",
    "df['accessions'] = df['accessions'].apply(assign_curies)\n",
    "df['gisaidId'] = df['accessions'].apply(get_gisaid_id)\n",
    "df['accessions'] = df['accessions'].apply(lambda x: ';'.join(x))\n",
    "df['accession'] = df['Accession ID'].apply(lambda s: assign_curie(s))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={'Data Source': 'source'}, inplace=True)\n",
    "df.rename(columns={'Sequence Length': 'sequenceLength'}, inplace=True)\n",
    "df.rename(columns={'Sequence Quality': 'sequenceQuality'}, inplace=True)\n",
    "df.rename(columns={'Quality Assessment': 'qualityAssessment'}, inplace=True)\n",
    "df.rename(columns={'Originating Lab': 'originatingLab'}, inplace=True)\n",
    "df.rename(columns={'Virus Strain Name': 'name'}, inplace=True)\n",
    "df.rename(columns={'Sample Collection Date':'collectionDate'},inplace=True)\n",
    "df.rename(columns={'Location':'location'}, inplace=True)\n",
    "df.rename(columns={'Lineage': 'lineage'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['lineage'] = df['lineage'].str.replace('-', '')\n",
    "df['lineage'] = df['lineage'].str.replace('None', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['B' '' 'B.1' ... 'C.36.3' 'P.4' 'A.29']\n"
     ]
    }
   ],
   "source": [
    "print(df['lineage'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remove invalid collection date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>name</th>\n",
       "      <th>Accession ID</th>\n",
       "      <th>source</th>\n",
       "      <th>Related ID</th>\n",
       "      <th>lineage</th>\n",
       "      <th>Nuc.Completeness</th>\n",
       "      <th>sequenceLength</th>\n",
       "      <th>sequenceQuality</th>\n",
       "      <th>qualityAssessment</th>\n",
       "      <th>Host</th>\n",
       "      <th>collectionDate</th>\n",
       "      <th>location</th>\n",
       "      <th>originatingLab</th>\n",
       "      <th>Submission Date</th>\n",
       "      <th>Submitting Lab</th>\n",
       "      <th>Create Time</th>\n",
       "      <th>Last Update Time</th>\n",
       "      <th>accessions</th>\n",
       "      <th>gisaidId</th>\n",
       "      <th>accession</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [name, Accession ID, source, Related ID, lineage, Nuc.Completeness, sequenceLength, sequenceQuality, qualityAssessment, Host, collectionDate, location, originatingLab, Submission Date, Submitting Lab, Create Time, Last Update Time, accessions, gisaidId, accession]\n",
       "Index: []"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query(\"collectionDate == '2020-00-00'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['collectionDate'] = df['collectionDate'].apply(lambda d: '' if d == '2020-00-00' else d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['collectionDate'] = df['collectionDate'].apply(lambda d: dateutil.parser.parse(d) if len(d) > 0 else '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>name</th>\n",
       "      <th>Accession ID</th>\n",
       "      <th>source</th>\n",
       "      <th>Related ID</th>\n",
       "      <th>lineage</th>\n",
       "      <th>Nuc.Completeness</th>\n",
       "      <th>sequenceLength</th>\n",
       "      <th>sequenceQuality</th>\n",
       "      <th>qualityAssessment</th>\n",
       "      <th>Host</th>\n",
       "      <th>collectionDate</th>\n",
       "      <th>location</th>\n",
       "      <th>originatingLab</th>\n",
       "      <th>Submission Date</th>\n",
       "      <th>Submitting Lab</th>\n",
       "      <th>Create Time</th>\n",
       "      <th>Last Update Time</th>\n",
       "      <th>accessions</th>\n",
       "      <th>gisaidId</th>\n",
       "      <th>accession</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Wuhan-Hu-1</td>\n",
       "      <td>MN908947</td>\n",
       "      <td>GenBank</td>\n",
       "      <td>NC_045512,EPI_ISL_402125</td>\n",
       "      <td>B.1</td>\n",
       "      <td>Complete</td>\n",
       "      <td>29903</td>\n",
       "      <td>High</td>\n",
       "      <td>0/0/-/-/-</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>China / Hubei / Wuhan</td>\n",
       "      <td>Shanghai Public Health Clinical Center &amp; Schoo...</td>\n",
       "      <td>2020-01-17</td>\n",
       "      <td>Shanghai Public Health Clinical Center &amp; Schoo...</td>\n",
       "      <td>2020-01-20 12:04:48</td>\n",
       "      <td>2021-03-16 10:46:59</td>\n",
       "      <td>insdc:MN908947;refseq:NC_045512;https://www.gi...</td>\n",
       "      <td>https://www.gisaid.org/EPI_ISL_402125</td>\n",
       "      <td>insdc:MN908947</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         name Accession ID   source                Related ID lineage  \\\n",
       "7  Wuhan-Hu-1     MN908947  GenBank  NC_045512,EPI_ISL_402125     B.1   \n",
       "\n",
       "  Nuc.Completeness sequenceLength sequenceQuality qualityAssessment  \\\n",
       "7         Complete          29903            High         0/0/-/-/-   \n",
       "\n",
       "           Host collectionDate               location  \\\n",
       "7  Homo sapiens     2019-12-31  China / Hubei / Wuhan   \n",
       "\n",
       "                                      originatingLab Submission Date  \\\n",
       "7  Shanghai Public Health Clinical Center & Schoo...      2020-01-17   \n",
       "\n",
       "                                      Submitting Lab          Create Time  \\\n",
       "7  Shanghai Public Health Clinical Center & Schoo...  2020-01-20 12:04:48   \n",
       "\n",
       "      Last Update Time                                         accessions  \\\n",
       "7  2021-03-16 10:46:59  insdc:MN908947;refseq:NC_045512;https://www.gi...   \n",
       "\n",
       "                                gisaidId       accession  \n",
       "7  https://www.gisaid.org/EPI_ISL_402125  insdc:MN908947  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['accessions'].str.contains('refseq:NC_045512')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>name</th>\n",
       "      <th>Accession ID</th>\n",
       "      <th>source</th>\n",
       "      <th>Related ID</th>\n",
       "      <th>lineage</th>\n",
       "      <th>Nuc.Completeness</th>\n",
       "      <th>sequenceLength</th>\n",
       "      <th>sequenceQuality</th>\n",
       "      <th>qualityAssessment</th>\n",
       "      <th>Host</th>\n",
       "      <th>collectionDate</th>\n",
       "      <th>location</th>\n",
       "      <th>originatingLab</th>\n",
       "      <th>Submission Date</th>\n",
       "      <th>Submitting Lab</th>\n",
       "      <th>Create Time</th>\n",
       "      <th>Last Update Time</th>\n",
       "      <th>accessions</th>\n",
       "      <th>gisaidId</th>\n",
       "      <th>accession</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BetaCoV/Wuhan/HBCDC-HB-01/2019</td>\n",
       "      <td>NMDC60013088-01</td>\n",
       "      <td>NMDC</td>\n",
       "      <td>EPI_ISL_402132</td>\n",
       "      <td>B</td>\n",
       "      <td>Complete</td>\n",
       "      <td>29848</td>\n",
       "      <td>High</td>\n",
       "      <td>0/0/-/-/-</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>China / Hubei</td>\n",
       "      <td>Hubei Provincial Center for Disease Control an...</td>\n",
       "      <td>2020-01-19</td>\n",
       "      <td>Hubei Provincial Center for Disease Control an...</td>\n",
       "      <td>2020-01-20 12:04:48</td>\n",
       "      <td>2021-03-16 10:46:59</td>\n",
       "      <td>NMDC60013088-01;https://www.gisaid.org/EPI_ISL...</td>\n",
       "      <td>https://www.gisaid.org/EPI_ISL_402132</td>\n",
       "      <td>NMDC60013088-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>hCoV-19/Thailand/74/2020</td>\n",
       "      <td>EPI_ISL_403963</td>\n",
       "      <td>GISAID</td>\n",
       "      <td></td>\n",
       "      <td>B</td>\n",
       "      <td>Complete</td>\n",
       "      <td>29859</td>\n",
       "      <td>High</td>\n",
       "      <td>0/0/-/-/-</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2020-01-13</td>\n",
       "      <td>Thailand/ Nonthaburi Province</td>\n",
       "      <td>Department of Medical Sciences, Ministry of Pu...</td>\n",
       "      <td>2020-01-17</td>\n",
       "      <td>Department of Medical Sciences, Ministry of Pu...</td>\n",
       "      <td>2020-01-20 12:04:48</td>\n",
       "      <td>2021-03-16 10:46:59</td>\n",
       "      <td>https://www.gisaid.org/EPI_ISL_403963</td>\n",
       "      <td>https://www.gisaid.org/EPI_ISL_403963</td>\n",
       "      <td>https://www.gisaid.org/EPI_ISL_403963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>hCoV-19/Thailand/61/2020</td>\n",
       "      <td>EPI_ISL_403962</td>\n",
       "      <td>GISAID</td>\n",
       "      <td></td>\n",
       "      <td>B</td>\n",
       "      <td>Complete</td>\n",
       "      <td>29848</td>\n",
       "      <td>High</td>\n",
       "      <td>0/0/-/-/-</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>Thailand/ Nonthaburi Province</td>\n",
       "      <td>Department of Medical Sciences, Ministry of Pu...</td>\n",
       "      <td>2020-01-17</td>\n",
       "      <td>Department of Medical Sciences, Ministry of Pu...</td>\n",
       "      <td>2020-01-20 12:04:48</td>\n",
       "      <td>2021-03-16 10:46:59</td>\n",
       "      <td>https://www.gisaid.org/EPI_ISL_403962</td>\n",
       "      <td>https://www.gisaid.org/EPI_ISL_403962</td>\n",
       "      <td>https://www.gisaid.org/EPI_ISL_403962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BetaCoV/Wuhan/IVDC-HB-04/2020</td>\n",
       "      <td>NMDC60013085-01</td>\n",
       "      <td>NMDC</td>\n",
       "      <td>EPI_ISL_402120</td>\n",
       "      <td>B</td>\n",
       "      <td>Complete</td>\n",
       "      <td>29896</td>\n",
       "      <td>High</td>\n",
       "      <td>0/0/-/-/-</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>China / Hubei / Wuhan</td>\n",
       "      <td>National Institute for Viral Disease Control a...</td>\n",
       "      <td>2020-01-11</td>\n",
       "      <td>National Institute for Viral Disease Control a...</td>\n",
       "      <td>2020-01-20 12:04:48</td>\n",
       "      <td>2021-03-16 10:46:59</td>\n",
       "      <td>NMDC60013085-01;https://www.gisaid.org/EPI_ISL...</td>\n",
       "      <td>https://www.gisaid.org/EPI_ISL_402120</td>\n",
       "      <td>NMDC60013085-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BetaCoV/Wuhan/IVDC-HB-01/2019</td>\n",
       "      <td>NMDC60013084-01</td>\n",
       "      <td>NMDC</td>\n",
       "      <td>EPI_ISL_402119</td>\n",
       "      <td>B</td>\n",
       "      <td>Complete</td>\n",
       "      <td>29891</td>\n",
       "      <td>High</td>\n",
       "      <td>0/0/-/-/-</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>China / Hubei / Wuhan</td>\n",
       "      <td>National Institute for Viral Disease Control a...</td>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>National Institute for Viral Disease Control a...</td>\n",
       "      <td>2020-01-20 12:04:48</td>\n",
       "      <td>2021-03-16 10:46:59</td>\n",
       "      <td>NMDC60013084-01;https://www.gisaid.org/EPI_ISL...</td>\n",
       "      <td>https://www.gisaid.org/EPI_ISL_402119</td>\n",
       "      <td>NMDC60013084-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             name     Accession ID  source      Related ID  \\\n",
       "0  BetaCoV/Wuhan/HBCDC-HB-01/2019  NMDC60013088-01    NMDC  EPI_ISL_402132   \n",
       "1        hCoV-19/Thailand/74/2020   EPI_ISL_403963  GISAID                   \n",
       "2        hCoV-19/Thailand/61/2020   EPI_ISL_403962  GISAID                   \n",
       "3   BetaCoV/Wuhan/IVDC-HB-04/2020  NMDC60013085-01    NMDC  EPI_ISL_402120   \n",
       "4   BetaCoV/Wuhan/IVDC-HB-01/2019  NMDC60013084-01    NMDC  EPI_ISL_402119   \n",
       "\n",
       "  lineage Nuc.Completeness sequenceLength sequenceQuality qualityAssessment  \\\n",
       "0       B         Complete          29848            High         0/0/-/-/-   \n",
       "1       B         Complete          29859            High         0/0/-/-/-   \n",
       "2       B         Complete          29848            High         0/0/-/-/-   \n",
       "3       B         Complete          29896            High         0/0/-/-/-   \n",
       "4       B         Complete          29891            High         0/0/-/-/-   \n",
       "\n",
       "           Host collectionDate                       location  \\\n",
       "0  Homo sapiens     2019-12-30                  China / Hubei   \n",
       "1  Homo sapiens     2020-01-13  Thailand/ Nonthaburi Province   \n",
       "2  Homo sapiens     2020-01-08  Thailand/ Nonthaburi Province   \n",
       "3  Homo sapiens     2020-01-01          China / Hubei / Wuhan   \n",
       "4  Homo sapiens     2019-12-30          China / Hubei / Wuhan   \n",
       "\n",
       "                                      originatingLab Submission Date  \\\n",
       "0  Hubei Provincial Center for Disease Control an...      2020-01-19   \n",
       "1  Department of Medical Sciences, Ministry of Pu...      2020-01-17   \n",
       "2  Department of Medical Sciences, Ministry of Pu...      2020-01-17   \n",
       "3  National Institute for Viral Disease Control a...      2020-01-11   \n",
       "4  National Institute for Viral Disease Control a...      2020-01-10   \n",
       "\n",
       "                                      Submitting Lab          Create Time  \\\n",
       "0  Hubei Provincial Center for Disease Control an...  2020-01-20 12:04:48   \n",
       "1  Department of Medical Sciences, Ministry of Pu...  2020-01-20 12:04:48   \n",
       "2  Department of Medical Sciences, Ministry of Pu...  2020-01-20 12:04:48   \n",
       "3  National Institute for Viral Disease Control a...  2020-01-20 12:04:48   \n",
       "4  National Institute for Viral Disease Control a...  2020-01-20 12:04:48   \n",
       "\n",
       "      Last Update Time                                         accessions  \\\n",
       "0  2021-03-16 10:46:59  NMDC60013088-01;https://www.gisaid.org/EPI_ISL...   \n",
       "1  2021-03-16 10:46:59              https://www.gisaid.org/EPI_ISL_403963   \n",
       "2  2021-03-16 10:46:59              https://www.gisaid.org/EPI_ISL_403962   \n",
       "3  2021-03-16 10:46:59  NMDC60013085-01;https://www.gisaid.org/EPI_ISL...   \n",
       "4  2021-03-16 10:46:59  NMDC60013084-01;https://www.gisaid.org/EPI_ISL...   \n",
       "\n",
       "                                gisaidId  \\\n",
       "0  https://www.gisaid.org/EPI_ISL_402132   \n",
       "1  https://www.gisaid.org/EPI_ISL_403963   \n",
       "2  https://www.gisaid.org/EPI_ISL_403962   \n",
       "3  https://www.gisaid.org/EPI_ISL_402120   \n",
       "4  https://www.gisaid.org/EPI_ISL_402119   \n",
       "\n",
       "                               accession  \n",
       "0                        NMDC60013088-01  \n",
       "1  https://www.gisaid.org/EPI_ISL_403963  \n",
       "2  https://www.gisaid.org/EPI_ISL_403962  \n",
       "3                        NMDC60013085-01  \n",
       "4                        NMDC60013084-01  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Assign taxonomy ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read Organism reference dictionary\n",
    "organism_to_id = dict()\n",
    "data = pd.read_csv(\"../../reference_data/OrganismDictionary.csv\", comment='#')\n",
    "for index, row in data.iterrows():\n",
    "    organism_to_id[row['organism']] = row['taxonomyId']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'human': 'taxonomy:9606', 'homo sapiens': 'taxonomy:9606', 'gorilla gorilla gorilla': 'taxonomy:9595', 'chlorocebus sabaeus': 'taxonomy:60711', 'mus musculus': 'taxonomy:10090', 'rhinolophus affinis': 'taxonomy:59477 ', 'rhinolophus malayanus': 'taxonomy:608659', 'rhinolophus shameli': 'taxonomy:608708', 'mustela lutreola': 'taxonomy:9666', 'mustela putorius furo': 'taxonomy:9669', 'mink': 'taxonomy:9666', 'panthera tigris jacksoni': 'taxonomy:419130', 'rhinolophus sp. (bat)': 'taxonomy:49442', 'rhinolophus bat': 'taxonomy:49442', 'manis pentadactyla': 'taxonomy:143292', 'rhinolophus sinicus': 'taxonomy:89399', 'rhinolophus stheno': 'taxonomy:430491', 'rhinolophus pusillus': 'taxonomy:159858', 'bat': 'taxonomy:49442', 'manis javanica': 'taxonomy:9974', 'palm civet': 'taxonomy:71116', 'canine': 'taxonomy:9608', 'canis lupus familiaris': 'taxonomy:9615', 'felis catus': 'taxonomy:9685', 'neovison vison': 'taxonomy:452646', 'mesocricetus auratus': 'taxonomy:10036', 'panthera leo': 'taxonomy:9689', 'panthera tigris': 'taxonomy:9694', 'prionailurus bengalensis euptilurus': 'taxonomy:300877', 'environment': 'taxonomy:151659', 'environmental': 'taxonomy:151659'}\n"
     ]
    }
   ],
   "source": [
    "print(organism_to_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['taxonomy:9606', 'taxonomy:59477 ', 'taxonomy:151659',\n",
       "       'taxonomy:9974', 'taxonomy:9615', 'taxonomy:9685', 'unknown',\n",
       "       'taxonomy:608659', 'taxonomy:9666', 'Vero cell culture',\n",
       "       'taxonomy:10090', 'taxonomy:452646', 'taxonomy:9689',\n",
       "       'taxonomy:9694', 'taxonomy:10036', 'taxonomy:60711',\n",
       "       'taxonomy:608708', 'taxonomy:9595', 'taxonomy:9669',\n",
       "       'taxonomy:419130', 'taxonomy:143292', 'taxonomy:49442',\n",
       "       'taxonomy:300877', 'taxonomy:9608', 'taxonomy:89399',\n",
       "       'taxonomy:430491', 'taxonomy:159858', 'Feline'], dtype=object)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# assign taxonomy id to host\n",
    "df['host'] = df['Host'].str.strip()\n",
    "df['hostTaxonomyId'] = df['host'].apply(lambda s: organism_to_id.get(s.lower(), s))\n",
    "df['hostTaxonomyId'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['taxonomyId'] = 'taxonomy:2697049' # SARS-CoV-2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Standardize location information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[['loc0', 'loc1', 'loc2', 'loc3']] = df['location'].str.split('/', n=3, expand=True)\n",
    "# strip white space\n",
    "df = df.apply(lambda x: x.str.strip() if x.dtype == \"object\" else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['origLocation'] = df[['loc0', 'loc1', 'loc2', 'loc3']].apply(\n",
    "    lambda x: ','.join(x.dropna().astype(str)),\n",
    "    axis=1\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save strain metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "strains = df[['name', 'accession', 'accessions', 'gisaidId', 'source', 'taxonomyId', 'hostTaxonomyId', \n",
    "              'lineage', 'sequenceLength', 'sequenceQuality', 'qualityAssessment', 'collectionDate',\n",
    "              'location', 'origLocation', 'originatingLab']].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "strains.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Number of strains:',strains.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "strains.to_csv(NEO4J_IMPORT / \"01c-CNCBStrainPre.csv\", index=False)"
   ]
  },
  {
   "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.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}