notebooks/dataprep/00b-NCBITaxonomy.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NCBI Taxonomy\n",
    "**[Work in progress]**\n",
    "\n",
    "This notebook downloads the NCBI taxonomy, including the taxonomy id, scientific name, and synonymes.\n",
    "\n",
    "Bacteria, Invertebrates, Phages, Plants and Fungi, and Synthetic and Chimeric are currently excluded.\n",
    "\n",
    "https://www.ncbi.nlm.nih.gov/pmc/articles/PMC7408187/\n",
    "\n",
    "Data source: [NCBI](https://www.ncbi.nlm.nih.gov)\n",
    "\n",
    "Author: Peter Rose (pwrose@ucsd.edu)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "from functools import reduce"
   ]
  },
  {
   "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": [
    "# 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": [
    "### Import NCBI Taxonomy Names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = ['id', 'name', 'nameCategory']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "names = pd.read_csv(NEO4J_IMPORT / 'cache/ncbi_taxonomy' / 'names.dmp', sep='\\t\\|\\t', engine='python', \n",
    "                    usecols=[0,1,3], names=columns, header=None, dtype='str')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/peter/.conda/envs/covid-19-community/lib/python3.7/site-packages/ipykernel_launcher.py:1: FutureWarning: The default value of regex will change from True to False in a future version.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "names['nameCategory'] = names['nameCategory'].str.replace('\\t\\|', '')\n",
    "names.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of taxonomyIds: 2295525\n"
     ]
    }
   ],
   "source": [
    "print('Number of taxonomyIds:', len(names['id'].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of taxonomyIds: ['synonym' 'scientific name' 'blast name' 'genbank common name' 'in-part'\n",
      " 'authority' 'type material' 'equivalent name' 'includes' 'common name'\n",
      " 'genbank synonym' 'acronym' 'genbank acronym']\n"
     ]
    }
   ],
   "source": [
    "print('Number of taxonomyIds:', names['nameCategory'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "sci_name = names.query(\"nameCategory == 'scientific name'\").copy()\n",
    "sci_name.rename(columns={'name': 'scientificName'}, inplace=True)\n",
    "sci_name = sci_name[['id', 'scientificName']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "scientific names: 2295525\n"
     ]
    }
   ],
   "source": [
    "print('scientific names:', sci_name.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "names1 = names.merge(sci_name, on='id', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>nameCategory</th>\n",
       "      <th>scientificName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>all</td>\n",
       "      <td>synonym</td>\n",
       "      <td>root</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>root</td>\n",
       "      <td>scientific name</td>\n",
       "      <td>root</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Bacteria</td>\n",
       "      <td>scientific name</td>\n",
       "      <td>Bacteria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>bacteria</td>\n",
       "      <td>blast name</td>\n",
       "      <td>Bacteria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>eubacteria</td>\n",
       "      <td>genbank common name</td>\n",
       "      <td>Bacteria</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id        name         nameCategory scientificName\n",
       "0  1         all              synonym           root\n",
       "1  1        root      scientific name           root\n",
       "2  2    Bacteria      scientific name       Bacteria\n",
       "3  2    bacteria           blast name       Bacteria\n",
       "4  2  eubacteria  genbank common name       Bacteria"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "names1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "names2 = names1.groupby(['id', 'scientificName'])['name'].apply(list).reset_index(name='synonymes')"
   ]
  },
  {
   "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>id</th>\n",
       "      <th>scientificName</th>\n",
       "      <th>synonymes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>root</td>\n",
       "      <td>[all, root]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>Cellvibrio</td>\n",
       "      <td>[Cellvibrio (ex Winogradsky 1929) Blackall et ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100</td>\n",
       "      <td>Ancylobacter aquaticus</td>\n",
       "      <td>[Ancylobacter aquaticus (Orskov 1928) Raj 1983...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100000</td>\n",
       "      <td>Herbaspirillum sp. BA12</td>\n",
       "      <td>[Herbaspirillum sp. BA12, Herbispirillum sp. B...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000000</td>\n",
       "      <td>Microbacterium sp. 6.11-VPa</td>\n",
       "      <td>[Microbacterium sp. 6.11-VPa]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id               scientificName  \\\n",
       "0        1                         root   \n",
       "1       10                   Cellvibrio   \n",
       "2      100       Ancylobacter aquaticus   \n",
       "3   100000      Herbaspirillum sp. BA12   \n",
       "4  1000000  Microbacterium sp. 6.11-VPa   \n",
       "\n",
       "                                           synonymes  \n",
       "0                                        [all, root]  \n",
       "1  [Cellvibrio (ex Winogradsky 1929) Blackall et ...  \n",
       "2  [Ancylobacter aquaticus (Orskov 1928) Raj 1983...  \n",
       "3  [Herbaspirillum sp. BA12, Herbispirillum sp. B...  \n",
       "4                      [Microbacterium sp. 6.11-VPa]  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "names2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "names2['name'] = names2['scientificName']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "names2['id'] = 'taxonomy:' + names2['id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "names2['synonymes'] = names2['synonymes'].apply(lambda x: ';'.join(x))"
   ]
  },
  {
   "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>id</th>\n",
       "      <th>scientificName</th>\n",
       "      <th>synonymes</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>taxonomy:1</td>\n",
       "      <td>root</td>\n",
       "      <td>all;root</td>\n",
       "      <td>root</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>taxonomy:10</td>\n",
       "      <td>Cellvibrio</td>\n",
       "      <td>Cellvibrio (ex Winogradsky 1929) Blackall et a...</td>\n",
       "      <td>Cellvibrio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>taxonomy:100</td>\n",
       "      <td>Ancylobacter aquaticus</td>\n",
       "      <td>Ancylobacter aquaticus (Orskov 1928) Raj 1983;...</td>\n",
       "      <td>Ancylobacter aquaticus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>taxonomy:100000</td>\n",
       "      <td>Herbaspirillum sp. BA12</td>\n",
       "      <td>Herbaspirillum sp. BA12;Herbispirillum sp. BA12</td>\n",
       "      <td>Herbaspirillum sp. BA12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>taxonomy:1000000</td>\n",
       "      <td>Microbacterium sp. 6.11-VPa</td>\n",
       "      <td>Microbacterium sp. 6.11-VPa</td>\n",
       "      <td>Microbacterium sp. 6.11-VPa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>taxonomy:1000001</td>\n",
       "      <td>Mycobacterium sp. 1.1-VEs</td>\n",
       "      <td>Mycobacterium sp. 1.1-VEs</td>\n",
       "      <td>Mycobacterium sp. 1.1-VEs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>taxonomy:1000002</td>\n",
       "      <td>Mycobacterium sp. 1.12-VEs</td>\n",
       "      <td>Mycobacterium sp. 1.12-VEs</td>\n",
       "      <td>Mycobacterium sp. 1.12-VEs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>taxonomy:1000003</td>\n",
       "      <td>Nocardia sp. 3.2-VPr</td>\n",
       "      <td>Nocardia sp. 3.2-VPr</td>\n",
       "      <td>Nocardia sp. 3.2-VPr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>taxonomy:1000004</td>\n",
       "      <td>Polaromonas sp. 7.23-VPa</td>\n",
       "      <td>Polaromonas sp. 7.23-VPa</td>\n",
       "      <td>Polaromonas sp. 7.23-VPa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>taxonomy:1000005</td>\n",
       "      <td>Promicromonospora sp. 10.25-Bb</td>\n",
       "      <td>Promicromonospora sp. 10.25-Bb</td>\n",
       "      <td>Promicromonospora sp. 10.25-Bb</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 id                  scientificName  \\\n",
       "0        taxonomy:1                            root   \n",
       "1       taxonomy:10                      Cellvibrio   \n",
       "2      taxonomy:100          Ancylobacter aquaticus   \n",
       "3   taxonomy:100000         Herbaspirillum sp. BA12   \n",
       "4  taxonomy:1000000     Microbacterium sp. 6.11-VPa   \n",
       "5  taxonomy:1000001       Mycobacterium sp. 1.1-VEs   \n",
       "6  taxonomy:1000002      Mycobacterium sp. 1.12-VEs   \n",
       "7  taxonomy:1000003            Nocardia sp. 3.2-VPr   \n",
       "8  taxonomy:1000004        Polaromonas sp. 7.23-VPa   \n",
       "9  taxonomy:1000005  Promicromonospora sp. 10.25-Bb   \n",
       "\n",
       "                                           synonymes  \\\n",
       "0                                           all;root   \n",
       "1  Cellvibrio (ex Winogradsky 1929) Blackall et a...   \n",
       "2  Ancylobacter aquaticus (Orskov 1928) Raj 1983;...   \n",
       "3    Herbaspirillum sp. BA12;Herbispirillum sp. BA12   \n",
       "4                        Microbacterium sp. 6.11-VPa   \n",
       "5                          Mycobacterium sp. 1.1-VEs   \n",
       "6                         Mycobacterium sp. 1.12-VEs   \n",
       "7                               Nocardia sp. 3.2-VPr   \n",
       "8                           Polaromonas sp. 7.23-VPa   \n",
       "9                     Promicromonospora sp. 10.25-Bb   \n",
       "\n",
       "                             name  \n",
       "0                            root  \n",
       "1                      Cellvibrio  \n",
       "2          Ancylobacter aquaticus  \n",
       "3         Herbaspirillum sp. BA12  \n",
       "4     Microbacterium sp. 6.11-VPa  \n",
       "5       Mycobacterium sp. 1.1-VEs  \n",
       "6      Mycobacterium sp. 1.12-VEs  \n",
       "7            Nocardia sp. 3.2-VPr  \n",
       "8        Polaromonas sp. 7.23-VPa  \n",
       "9  Promicromonospora sp. 10.25-Bb  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "names2.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import NCBI Taxonomy Nodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "node_columns = ['id', 'parentId', 'rank', 'divisionId']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "nodes = pd.read_csv(NEO4J_IMPORT / 'cache/ncbi_taxonomy' / 'nodes.dmp', sep='\\t\\|\\t', engine='python', \n",
    "                    usecols=[0,1,2,4], names=node_columns, header=None, dtype='str')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of relationships: 2295525\n"
     ]
    }
   ],
   "source": [
    "print('Number of relationships:', nodes.shape[0])"
   ]
  },
  {
   "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>id</th>\n",
       "      <th>parentId</th>\n",
       "      <th>rank</th>\n",
       "      <th>divisionId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>no rank</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>131567</td>\n",
       "      <td>superkingdom</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>335928</td>\n",
       "      <td>genus</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>species</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>32199</td>\n",
       "      <td>species</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id parentId          rank divisionId\n",
       "0  1        1       no rank          8\n",
       "1  2   131567  superkingdom          0\n",
       "2  6   335928         genus          0\n",
       "3  7        6       species          0\n",
       "4  9    32199       species          0"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "division_columns = ['divisionId', 'division']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "divisions = pd.read_csv(NEO4J_IMPORT / 'cache/ncbi_taxonomy' / 'division.dmp', sep='\\t\\|\\t', engine='python', \n",
    "                    usecols=[0,2], names=division_columns, header=None, dtype='str')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>divisionId</th>\n",
       "      <th>division</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Bacteria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Invertebrates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Mammals</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Phages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Plants and Fungi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>Primates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>Rodents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>Synthetic and Chimeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>Unassigned</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>Viruses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>Vertebrates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>Environmental samples</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   divisionId                division\n",
       "0           0                Bacteria\n",
       "1           1           Invertebrates\n",
       "2           2                 Mammals\n",
       "3           3                  Phages\n",
       "4           4        Plants and Fungi\n",
       "5           5                Primates\n",
       "6           6                 Rodents\n",
       "7           7  Synthetic and Chimeric\n",
       "8           8              Unassigned\n",
       "9           9                 Viruses\n",
       "10         10             Vertebrates\n",
       "11         11   Environmental samples"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "divisions.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "nodes = nodes.merge(divisions, on='divisionId', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2295525, 5)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>parentId</th>\n",
       "      <th>rank</th>\n",
       "      <th>divisionId</th>\n",
       "      <th>division</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>no rank</td>\n",
       "      <td>8</td>\n",
       "      <td>Unassigned</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>131567</td>\n",
       "      <td>superkingdom</td>\n",
       "      <td>0</td>\n",
       "      <td>Bacteria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>335928</td>\n",
       "      <td>genus</td>\n",
       "      <td>0</td>\n",
       "      <td>Bacteria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>species</td>\n",
       "      <td>0</td>\n",
       "      <td>Bacteria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>32199</td>\n",
       "      <td>species</td>\n",
       "      <td>0</td>\n",
       "      <td>Bacteria</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id parentId          rank divisionId    division\n",
       "0  1        1       no rank          8  Unassigned\n",
       "1  2   131567  superkingdom          0    Bacteria\n",
       "2  6   335928         genus          0    Bacteria\n",
       "3  7        6       species          0    Bacteria\n",
       "4  9    32199       species          0    Bacteria"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Restrict taxonomies\n",
    "Mammals, Primates, Rodents, Viruses, and unclassified environmental samples (taxonomyID: 151659)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "nodes = nodes[nodes['divisionId'].isin(['2','5','6','8','9','10']) | (nodes['id'] == '151659')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "nodes['id'] = 'taxonomy:' + nodes['id']\n",
    "nodes['parentId'] = 'taxonomy:' + nodes['parentId']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(294338, 5)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "nodes = nodes.merge(names2, on='id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "nodes.to_csv(NEO4J_IMPORT / '00b-NCBITaxonomy.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of nodes 294338\n"
     ]
    }
   ],
   "source": [
    "print('Number of nodes', nodes.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>parentId</th>\n",
       "      <th>rank</th>\n",
       "      <th>divisionId</th>\n",
       "      <th>division</th>\n",
       "      <th>scientificName</th>\n",
       "      <th>synonymes</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>taxonomy:1</td>\n",
       "      <td>taxonomy:1</td>\n",
       "      <td>no rank</td>\n",
       "      <td>8</td>\n",
       "      <td>Unassigned</td>\n",
       "      <td>root</td>\n",
       "      <td>all;root</td>\n",
       "      <td>root</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>taxonomy:2387</td>\n",
       "      <td>taxonomy:28384</td>\n",
       "      <td>no rank</td>\n",
       "      <td>8</td>\n",
       "      <td>Unassigned</td>\n",
       "      <td>transposons</td>\n",
       "      <td>transposons;Transposon</td>\n",
       "      <td>transposons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>taxonomy:2388</td>\n",
       "      <td>taxonomy:2387</td>\n",
       "      <td>species</td>\n",
       "      <td>8</td>\n",
       "      <td>Unassigned</td>\n",
       "      <td>Transposon Tn-cam204</td>\n",
       "      <td>Transposon Tn-cam204</td>\n",
       "      <td>Transposon Tn-cam204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>taxonomy:2389</td>\n",
       "      <td>taxonomy:2387</td>\n",
       "      <td>species</td>\n",
       "      <td>8</td>\n",
       "      <td>Unassigned</td>\n",
       "      <td>Transposon Tn10</td>\n",
       "      <td>Transposon Tn10</td>\n",
       "      <td>Transposon Tn10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>taxonomy:2390</td>\n",
       "      <td>taxonomy:2387</td>\n",
       "      <td>species</td>\n",
       "      <td>8</td>\n",
       "      <td>Unassigned</td>\n",
       "      <td>Transposon Tn1331</td>\n",
       "      <td>Transposon Tn1331</td>\n",
       "      <td>Transposon Tn1331</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id        parentId     rank divisionId    division  \\\n",
       "0     taxonomy:1      taxonomy:1  no rank          8  Unassigned   \n",
       "1  taxonomy:2387  taxonomy:28384  no rank          8  Unassigned   \n",
       "2  taxonomy:2388   taxonomy:2387  species          8  Unassigned   \n",
       "3  taxonomy:2389   taxonomy:2387  species          8  Unassigned   \n",
       "4  taxonomy:2390   taxonomy:2387  species          8  Unassigned   \n",
       "\n",
       "         scientificName               synonymes                  name  \n",
       "0                  root                all;root                  root  \n",
       "1           transposons  transposons;Transposon           transposons  \n",
       "2  Transposon Tn-cam204    Transposon Tn-cam204  Transposon Tn-cam204  \n",
       "3       Transposon Tn10         Transposon Tn10       Transposon Tn10  \n",
       "4     Transposon Tn1331       Transposon Tn1331     Transposon Tn1331  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes.head()"
   ]
  },
  {
   "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}