ITIS.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "radio-timing",
   "metadata": {},
   "source": [
    "# Integrated Taxonomic Information System (ITIS) - 26-Feb-2021\n",
    "\n",
    "- [Database dumps](https://www.itis.gov/downloads/index.html)\n",
    "- [Web services](https://www.itis.gov/web_service.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "equipped-roller",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3\n",
    "from tqdm.notebook import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "appropriate-content",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = sqlite3.connect(\"data/ITIS.sqlite\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "swedish-addition",
   "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>kingdom_id</th>\n",
       "      <th>kingdom_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>Plantae</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   kingdom_id kingdom_name\n",
       "0           3      Plantae"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"SELECT kingdom_id, kingdom_name from kingdoms WHERE kingdom_name = (?)\", con, \n",
    "                  params=('Plantae',))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "graphic-softball",
   "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>kingdom_id</th>\n",
       "      <th>rank_id</th>\n",
       "      <th>rank_name</th>\n",
       "      <th>dir_parent_rank_id</th>\n",
       "      <th>req_parent_rank_id</th>\n",
       "      <th>update_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>Kingdom</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>Subkingdom</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>25</td>\n",
       "      <td>Infrakingdom</td>\n",
       "      <td>20</td>\n",
       "      <td>10</td>\n",
       "      <td>2012-03-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>27</td>\n",
       "      <td>Superdivision</td>\n",
       "      <td>25</td>\n",
       "      <td>10</td>\n",
       "      <td>2014-08-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "      <td>Division</td>\n",
       "      <td>27</td>\n",
       "      <td>10</td>\n",
       "      <td>2014-08-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3</td>\n",
       "      <td>40</td>\n",
       "      <td>Subdivision</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3</td>\n",
       "      <td>45</td>\n",
       "      <td>Infradivision</td>\n",
       "      <td>40</td>\n",
       "      <td>30</td>\n",
       "      <td>2012-03-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3</td>\n",
       "      <td>50</td>\n",
       "      <td>Superclass</td>\n",
       "      <td>45</td>\n",
       "      <td>30</td>\n",
       "      <td>2012-03-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3</td>\n",
       "      <td>60</td>\n",
       "      <td>Class</td>\n",
       "      <td>50</td>\n",
       "      <td>30</td>\n",
       "      <td>2012-03-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3</td>\n",
       "      <td>70</td>\n",
       "      <td>Subclass</td>\n",
       "      <td>60</td>\n",
       "      <td>60</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3</td>\n",
       "      <td>80</td>\n",
       "      <td>Infraclass</td>\n",
       "      <td>70</td>\n",
       "      <td>60</td>\n",
       "      <td>2014-08-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>90</td>\n",
       "      <td>Superorder</td>\n",
       "      <td>80</td>\n",
       "      <td>60</td>\n",
       "      <td>2014-08-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3</td>\n",
       "      <td>100</td>\n",
       "      <td>Order</td>\n",
       "      <td>90</td>\n",
       "      <td>60</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>3</td>\n",
       "      <td>110</td>\n",
       "      <td>Suborder</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3</td>\n",
       "      <td>140</td>\n",
       "      <td>Family</td>\n",
       "      <td>110</td>\n",
       "      <td>100</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>3</td>\n",
       "      <td>150</td>\n",
       "      <td>Subfamily</td>\n",
       "      <td>140</td>\n",
       "      <td>140</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>3</td>\n",
       "      <td>160</td>\n",
       "      <td>Tribe</td>\n",
       "      <td>150</td>\n",
       "      <td>140</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>3</td>\n",
       "      <td>170</td>\n",
       "      <td>Subtribe</td>\n",
       "      <td>160</td>\n",
       "      <td>140</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>3</td>\n",
       "      <td>180</td>\n",
       "      <td>Genus</td>\n",
       "      <td>170</td>\n",
       "      <td>140</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>3</td>\n",
       "      <td>190</td>\n",
       "      <td>Subgenus</td>\n",
       "      <td>180</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>3</td>\n",
       "      <td>200</td>\n",
       "      <td>Section</td>\n",
       "      <td>190</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>3</td>\n",
       "      <td>210</td>\n",
       "      <td>Subsection</td>\n",
       "      <td>200</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>3</td>\n",
       "      <td>220</td>\n",
       "      <td>Species</td>\n",
       "      <td>210</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>3</td>\n",
       "      <td>230</td>\n",
       "      <td>Subspecies</td>\n",
       "      <td>220</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>3</td>\n",
       "      <td>240</td>\n",
       "      <td>Variety</td>\n",
       "      <td>220</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>3</td>\n",
       "      <td>250</td>\n",
       "      <td>Subvariety</td>\n",
       "      <td>240</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>3</td>\n",
       "      <td>260</td>\n",
       "      <td>Form</td>\n",
       "      <td>220</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>3</td>\n",
       "      <td>270</td>\n",
       "      <td>Subform</td>\n",
       "      <td>260</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    kingdom_id  rank_id      rank_name  dir_parent_rank_id  \\\n",
       "0            3       10        Kingdom                  10   \n",
       "1            3       20     Subkingdom                  10   \n",
       "2            3       25   Infrakingdom                  20   \n",
       "3            3       27  Superdivision                  25   \n",
       "4            3       30       Division                  27   \n",
       "5            3       40    Subdivision                  30   \n",
       "6            3       45  Infradivision                  40   \n",
       "7            3       50     Superclass                  45   \n",
       "8            3       60          Class                  50   \n",
       "9            3       70       Subclass                  60   \n",
       "10           3       80     Infraclass                  70   \n",
       "11           3       90     Superorder                  80   \n",
       "12           3      100          Order                  90   \n",
       "13           3      110       Suborder                 100   \n",
       "14           3      140         Family                 110   \n",
       "15           3      150      Subfamily                 140   \n",
       "16           3      160          Tribe                 150   \n",
       "17           3      170       Subtribe                 160   \n",
       "18           3      180          Genus                 170   \n",
       "19           3      190       Subgenus                 180   \n",
       "20           3      200        Section                 190   \n",
       "21           3      210     Subsection                 200   \n",
       "22           3      220        Species                 210   \n",
       "23           3      230     Subspecies                 220   \n",
       "24           3      240        Variety                 220   \n",
       "25           3      250     Subvariety                 240   \n",
       "26           3      260           Form                 220   \n",
       "27           3      270        Subform                 260   \n",
       "\n",
       "    req_parent_rank_id update_date  \n",
       "0                   10  1996-06-13  \n",
       "1                   10  1996-06-13  \n",
       "2                   10  2012-03-19  \n",
       "3                   10  2014-08-20  \n",
       "4                   10  2014-08-20  \n",
       "5                   30  1996-06-13  \n",
       "6                   30  2012-03-19  \n",
       "7                   30  2012-03-19  \n",
       "8                   30  2012-03-19  \n",
       "9                   60  1996-06-13  \n",
       "10                  60  2014-08-20  \n",
       "11                  60  2014-08-20  \n",
       "12                  60  1996-06-13  \n",
       "13                 100  1996-06-13  \n",
       "14                 100  1996-06-13  \n",
       "15                 140  1996-06-13  \n",
       "16                 140  1996-06-13  \n",
       "17                 140  1996-06-13  \n",
       "18                 140  1996-06-13  \n",
       "19                 180  1996-06-13  \n",
       "20                 180  1996-06-13  \n",
       "21                 180  1996-06-13  \n",
       "22                 180  1996-06-13  \n",
       "23                 180  1996-06-13  \n",
       "24                 180  1996-06-13  \n",
       "25                 180  1996-06-13  \n",
       "26                 180  1996-06-13  \n",
       "27                 180  1996-06-13  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_taxon_unit_types = pd.read_sql_query(\"\"\"SELECT * from taxon_unit_types \n",
    "                                           WHERE kingdom_id = (?) \n",
    "                                           ORDER BY rank_id ASC\"\"\", \n",
    "                                        con, params=(3,))\n",
    "df_taxon_unit_types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "realistic-encyclopedia",
   "metadata": {},
   "outputs": [],
   "source": [
    "taxonomy_group_ranges = {\n",
    "    (10, 27): ('Kingdom', 10),\n",
    "    (27, 50): ('Division', 30),\n",
    "    (50, 90): ('Class', 60),\n",
    "    (90, 140): ('Order', 100),\n",
    "    (140, 160): ('Family', 140),\n",
    "    (160, 180): ('Tribe', 160),\n",
    "    (180, 200): ('Genus', 180),\n",
    "    (200, 220): ('Section', 200),\n",
    "    (220, 240): ('Species', 220),\n",
    "    (240, 260): ('Variety', 240),\n",
    "    (260, 271): ('Form', 260),\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "acoustic-wright",
   "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>kingdom_id</th>\n",
       "      <th>rank_id</th>\n",
       "      <th>rank_name</th>\n",
       "      <th>dir_parent_rank_id</th>\n",
       "      <th>req_parent_rank_id</th>\n",
       "      <th>update_date</th>\n",
       "      <th>rank_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>Kingdom</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>Subkingdom</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>25</td>\n",
       "      <td>Infrakingdom</td>\n",
       "      <td>20</td>\n",
       "      <td>10</td>\n",
       "      <td>2012-03-19</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>27</td>\n",
       "      <td>Superdivision</td>\n",
       "      <td>25</td>\n",
       "      <td>10</td>\n",
       "      <td>2014-08-20</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "      <td>Division</td>\n",
       "      <td>27</td>\n",
       "      <td>10</td>\n",
       "      <td>2014-08-20</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3</td>\n",
       "      <td>40</td>\n",
       "      <td>Subdivision</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3</td>\n",
       "      <td>45</td>\n",
       "      <td>Infradivision</td>\n",
       "      <td>40</td>\n",
       "      <td>30</td>\n",
       "      <td>2012-03-19</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3</td>\n",
       "      <td>50</td>\n",
       "      <td>Superclass</td>\n",
       "      <td>45</td>\n",
       "      <td>30</td>\n",
       "      <td>2012-03-19</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3</td>\n",
       "      <td>60</td>\n",
       "      <td>Class</td>\n",
       "      <td>50</td>\n",
       "      <td>30</td>\n",
       "      <td>2012-03-19</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3</td>\n",
       "      <td>70</td>\n",
       "      <td>Subclass</td>\n",
       "      <td>60</td>\n",
       "      <td>60</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3</td>\n",
       "      <td>80</td>\n",
       "      <td>Infraclass</td>\n",
       "      <td>70</td>\n",
       "      <td>60</td>\n",
       "      <td>2014-08-20</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>90</td>\n",
       "      <td>Superorder</td>\n",
       "      <td>80</td>\n",
       "      <td>60</td>\n",
       "      <td>2014-08-20</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3</td>\n",
       "      <td>100</td>\n",
       "      <td>Order</td>\n",
       "      <td>90</td>\n",
       "      <td>60</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>3</td>\n",
       "      <td>110</td>\n",
       "      <td>Suborder</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3</td>\n",
       "      <td>140</td>\n",
       "      <td>Family</td>\n",
       "      <td>110</td>\n",
       "      <td>100</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>3</td>\n",
       "      <td>150</td>\n",
       "      <td>Subfamily</td>\n",
       "      <td>140</td>\n",
       "      <td>140</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>3</td>\n",
       "      <td>160</td>\n",
       "      <td>Tribe</td>\n",
       "      <td>150</td>\n",
       "      <td>140</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>3</td>\n",
       "      <td>170</td>\n",
       "      <td>Subtribe</td>\n",
       "      <td>160</td>\n",
       "      <td>140</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>3</td>\n",
       "      <td>180</td>\n",
       "      <td>Genus</td>\n",
       "      <td>170</td>\n",
       "      <td>140</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>3</td>\n",
       "      <td>190</td>\n",
       "      <td>Subgenus</td>\n",
       "      <td>180</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>3</td>\n",
       "      <td>200</td>\n",
       "      <td>Section</td>\n",
       "      <td>190</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>3</td>\n",
       "      <td>210</td>\n",
       "      <td>Subsection</td>\n",
       "      <td>200</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>3</td>\n",
       "      <td>220</td>\n",
       "      <td>Species</td>\n",
       "      <td>210</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>3</td>\n",
       "      <td>230</td>\n",
       "      <td>Subspecies</td>\n",
       "      <td>220</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>3</td>\n",
       "      <td>240</td>\n",
       "      <td>Variety</td>\n",
       "      <td>220</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>3</td>\n",
       "      <td>250</td>\n",
       "      <td>Subvariety</td>\n",
       "      <td>240</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>3</td>\n",
       "      <td>260</td>\n",
       "      <td>Form</td>\n",
       "      <td>220</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>3</td>\n",
       "      <td>270</td>\n",
       "      <td>Subform</td>\n",
       "      <td>260</td>\n",
       "      <td>180</td>\n",
       "      <td>1996-06-13</td>\n",
       "      <td>260</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    kingdom_id  rank_id      rank_name  dir_parent_rank_id  \\\n",
       "0            3       10        Kingdom                  10   \n",
       "1            3       20     Subkingdom                  10   \n",
       "2            3       25   Infrakingdom                  20   \n",
       "3            3       27  Superdivision                  25   \n",
       "4            3       30       Division                  27   \n",
       "5            3       40    Subdivision                  30   \n",
       "6            3       45  Infradivision                  40   \n",
       "7            3       50     Superclass                  45   \n",
       "8            3       60          Class                  50   \n",
       "9            3       70       Subclass                  60   \n",
       "10           3       80     Infraclass                  70   \n",
       "11           3       90     Superorder                  80   \n",
       "12           3      100          Order                  90   \n",
       "13           3      110       Suborder                 100   \n",
       "14           3      140         Family                 110   \n",
       "15           3      150      Subfamily                 140   \n",
       "16           3      160          Tribe                 150   \n",
       "17           3      170       Subtribe                 160   \n",
       "18           3      180          Genus                 170   \n",
       "19           3      190       Subgenus                 180   \n",
       "20           3      200        Section                 190   \n",
       "21           3      210     Subsection                 200   \n",
       "22           3      220        Species                 210   \n",
       "23           3      230     Subspecies                 220   \n",
       "24           3      240        Variety                 220   \n",
       "25           3      250     Subvariety                 240   \n",
       "26           3      260           Form                 220   \n",
       "27           3      270        Subform                 260   \n",
       "\n",
       "    req_parent_rank_id update_date  rank_group  \n",
       "0                   10  1996-06-13          10  \n",
       "1                   10  1996-06-13          10  \n",
       "2                   10  2012-03-19          10  \n",
       "3                   10  2014-08-20          30  \n",
       "4                   10  2014-08-20          30  \n",
       "5                   30  1996-06-13          30  \n",
       "6                   30  2012-03-19          30  \n",
       "7                   30  2012-03-19          60  \n",
       "8                   30  2012-03-19          60  \n",
       "9                   60  1996-06-13          60  \n",
       "10                  60  2014-08-20          60  \n",
       "11                  60  2014-08-20         100  \n",
       "12                  60  1996-06-13         100  \n",
       "13                 100  1996-06-13         100  \n",
       "14                 100  1996-06-13         140  \n",
       "15                 140  1996-06-13         140  \n",
       "16                 140  1996-06-13         160  \n",
       "17                 140  1996-06-13         160  \n",
       "18                 140  1996-06-13         180  \n",
       "19                 180  1996-06-13         180  \n",
       "20                 180  1996-06-13         200  \n",
       "21                 180  1996-06-13         200  \n",
       "22                 180  1996-06-13         220  \n",
       "23                 180  1996-06-13         220  \n",
       "24                 180  1996-06-13         240  \n",
       "25                 180  1996-06-13         240  \n",
       "26                 180  1996-06-13         260  \n",
       "27                 180  1996-06-13         260  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_taxon_unit_groups = df_taxon_unit_types.rank_id.map(lambda rank_id: [value[1] for ((min_, max_), value) in taxonomy_group_ranges.items() if (rank_id >= min_ and rank_id < max_)][0])\n",
    "df_taxon_unit_groups.name = 'rank_group'\n",
    "df_taxon_unit_types = df_taxon_unit_types.join(df_taxon_unit_groups)\n",
    "df_taxon_unit_types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "nervous-tonight",
   "metadata": {},
   "outputs": [],
   "source": [
    "import graphviz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "existing-beaver",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.40.1 (20161225.0304)\n",
       " -->\n",
       "<!-- Title: Taxonomy Pages: 1 -->\n",
       "<svg width=\"370pt\" height=\"1804pt\"\n",
       " viewBox=\"0.00 0.00 370.00 1804.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 1800)\">\n",
       "<title>Taxonomy</title>\n",
       "<polygon fill=\"#ffffff\" stroke=\"transparent\" points=\"-4,4 -4,-1800 366,-1800 366,4 -4,4\"/>\n",
       "<g id=\"clust1\" class=\"cluster\">\n",
       "<title>cluster_Kingdom</title>\n",
       "<polygon fill=\"none\" stroke=\"#000000\" stroke-dasharray=\"1,5\" points=\"10,-1592 10,-1788 146,-1788 146,-1592 10,-1592\"/>\n",
       "</g>\n",
       "<g id=\"clust2\" class=\"cluster\">\n",
       "<title>cluster_Division</title>\n",
       "<polygon fill=\"none\" stroke=\"#000000\" stroke-dasharray=\"1,5\" points=\"10,-1304 10,-1572 146,-1572 146,-1304 10,-1304\"/>\n",
       "</g>\n",
       "<g id=\"clust3\" class=\"cluster\">\n",
       "<title>cluster_Class</title>\n",
       "<polygon fill=\"none\" stroke=\"#000000\" stroke-dasharray=\"1,5\" points=\"22,-1016 22,-1284 134,-1284 134,-1016 22,-1016\"/>\n",
       "</g>\n",
       "<g id=\"clust4\" class=\"cluster\">\n",
       "<title>cluster_Order</title>\n",
       "<polygon fill=\"none\" stroke=\"#000000\" stroke-dasharray=\"1,5\" points=\"20,-800 20,-996 136,-996 136,-800 20,-800\"/>\n",
       "</g>\n",
       "<g id=\"clust5\" class=\"cluster\">\n",
       "<title>cluster_Family</title>\n",
       "<polygon fill=\"none\" stroke=\"#000000\" stroke-dasharray=\"1,5\" points=\"22,-656 22,-780 134,-780 134,-656 22,-656\"/>\n",
       "</g>\n",
       "<g id=\"clust6\" class=\"cluster\">\n",
       "<title>cluster_Tribe</title>\n",
       "<polygon fill=\"none\" stroke=\"#000000\" stroke-dasharray=\"1,5\" points=\"30,-512 30,-636 126,-636 126,-512 30,-512\"/>\n",
       "</g>\n",
       "<g id=\"clust7\" class=\"cluster\">\n",
       "<title>cluster_Genus</title>\n",
       "<polygon fill=\"none\" stroke=\"#000000\" stroke-dasharray=\"1,5\" points=\"25,-368 25,-492 131,-492 131,-368 25,-368\"/>\n",
       "</g>\n",
       "<g id=\"clust8\" class=\"cluster\">\n",
       "<title>cluster_Section</title>\n",
       "<polygon fill=\"none\" stroke=\"#000000\" stroke-dasharray=\"1,5\" points=\"21,-224 21,-348 135,-348 135,-224 21,-224\"/>\n",
       "</g>\n",
       "<g id=\"clust9\" class=\"cluster\">\n",
       "<title>cluster_Species</title>\n",
       "<polygon fill=\"none\" stroke=\"#000000\" stroke-dasharray=\"1,5\" points=\"8,-80 8,-204 124,-204 124,-80 8,-80\"/>\n",
       "</g>\n",
       "<g id=\"clust10\" class=\"cluster\">\n",
       "<title>cluster_Variety</title>\n",
       "<polygon fill=\"none\" stroke=\"#000000\" stroke-dasharray=\"1,5\" points=\"132,-8 132,-132 246,-132 246,-8 132,-8\"/>\n",
       "</g>\n",
       "<g id=\"clust11\" class=\"cluster\">\n",
       "<title>cluster_Form</title>\n",
       "<polygon fill=\"none\" stroke=\"#000000\" stroke-dasharray=\"1,5\" points=\"254,-8 254,-132 354,-132 354,-8 254,-8\"/>\n",
       "</g>\n",
       "<!-- Kingdom -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>Kingdom</title>\n",
       "<ellipse fill=\"#d3d3d3\" stroke=\"#000000\" stroke-width=\"2\" cx=\"78\" cy=\"-1762\" rx=\"44.393\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-1758.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Kingdom</text>\n",
       "</g>\n",
       "<!-- Subkingdom -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>Subkingdom</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"78\" cy=\"-1690\" rx=\"55.7903\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-1686.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Subkingdom</text>\n",
       "</g>\n",
       "<!-- Kingdom&#45;&gt;Subkingdom -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>Kingdom&#45;&gt;Subkingdom</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-1743.8314C78,-1736.131 78,-1726.9743 78,-1718.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-1718.4132 78,-1708.4133 74.5001,-1718.4133 81.5001,-1718.4132\"/>\n",
       "</g>\n",
       "<!-- Infrakingdom -->\n",
       "<g id=\"node3\" class=\"node\">\n",
       "<title>Infrakingdom</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"78\" cy=\"-1618\" rx=\"59.5901\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-1614.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Infrakingdom</text>\n",
       "</g>\n",
       "<!-- Subkingdom&#45;&gt;Infrakingdom -->\n",
       "<g id=\"edge2\" class=\"edge\">\n",
       "<title>Subkingdom&#45;&gt;Infrakingdom</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-1671.8314C78,-1664.131 78,-1654.9743 78,-1646.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-1646.4132 78,-1636.4133 74.5001,-1646.4133 81.5001,-1646.4132\"/>\n",
       "</g>\n",
       "<!-- Superdivision -->\n",
       "<g id=\"node4\" class=\"node\">\n",
       "<title>Superdivision</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"78\" cy=\"-1546\" rx=\"59.5901\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-1542.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Superdivision</text>\n",
       "</g>\n",
       "<!-- Infrakingdom&#45;&gt;Superdivision -->\n",
       "<g id=\"edge3\" class=\"edge\">\n",
       "<title>Infrakingdom&#45;&gt;Superdivision</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-1599.8314C78,-1592.131 78,-1582.9743 78,-1574.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-1574.4132 78,-1564.4133 74.5001,-1574.4133 81.5001,-1574.4132\"/>\n",
       "</g>\n",
       "<!-- Division -->\n",
       "<g id=\"node5\" class=\"node\">\n",
       "<title>Division</title>\n",
       "<ellipse fill=\"#d3d3d3\" stroke=\"#000000\" stroke-width=\"2\" cx=\"78\" cy=\"-1474\" rx=\"40.8928\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-1470.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Division</text>\n",
       "</g>\n",
       "<!-- Superdivision&#45;&gt;Division -->\n",
       "<g id=\"edge4\" class=\"edge\">\n",
       "<title>Superdivision&#45;&gt;Division</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-1527.8314C78,-1520.131 78,-1510.9743 78,-1502.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-1502.4132 78,-1492.4133 74.5001,-1502.4133 81.5001,-1502.4132\"/>\n",
       "</g>\n",
       "<!-- Subdivision -->\n",
       "<g id=\"node6\" class=\"node\">\n",
       "<title>Subdivision</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"78\" cy=\"-1402\" rx=\"52.7911\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-1398.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Subdivision</text>\n",
       "</g>\n",
       "<!-- Division&#45;&gt;Subdivision -->\n",
       "<g id=\"edge5\" class=\"edge\">\n",
       "<title>Division&#45;&gt;Subdivision</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-1455.8314C78,-1448.131 78,-1438.9743 78,-1430.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-1430.4132 78,-1420.4133 74.5001,-1430.4133 81.5001,-1430.4132\"/>\n",
       "</g>\n",
       "<!-- Infradivision -->\n",
       "<g id=\"node7\" class=\"node\">\n",
       "<title>Infradivision</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"78\" cy=\"-1330\" rx=\"55.7903\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-1326.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Infradivision</text>\n",
       "</g>\n",
       "<!-- Subdivision&#45;&gt;Infradivision -->\n",
       "<g id=\"edge6\" class=\"edge\">\n",
       "<title>Subdivision&#45;&gt;Infradivision</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-1383.8314C78,-1376.131 78,-1366.9743 78,-1358.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-1358.4132 78,-1348.4133 74.5001,-1358.4133 81.5001,-1358.4132\"/>\n",
       "</g>\n",
       "<!-- Superclass -->\n",
       "<g id=\"node8\" class=\"node\">\n",
       "<title>Superclass</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"78\" cy=\"-1258\" rx=\"48.1917\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-1254.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Superclass</text>\n",
       "</g>\n",
       "<!-- Infradivision&#45;&gt;Superclass -->\n",
       "<g id=\"edge7\" class=\"edge\">\n",
       "<title>Infradivision&#45;&gt;Superclass</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-1311.8314C78,-1304.131 78,-1294.9743 78,-1286.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-1286.4132 78,-1276.4133 74.5001,-1286.4133 81.5001,-1286.4132\"/>\n",
       "</g>\n",
       "<!-- Class -->\n",
       "<g id=\"node9\" class=\"node\">\n",
       "<title>Class</title>\n",
       "<ellipse fill=\"#d3d3d3\" stroke=\"#000000\" stroke-width=\"2\" cx=\"78\" cy=\"-1186\" rx=\"29.795\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-1182.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Class</text>\n",
       "</g>\n",
       "<!-- Superclass&#45;&gt;Class -->\n",
       "<g id=\"edge8\" class=\"edge\">\n",
       "<title>Superclass&#45;&gt;Class</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-1239.8314C78,-1232.131 78,-1222.9743 78,-1214.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-1214.4132 78,-1204.4133 74.5001,-1214.4133 81.5001,-1214.4132\"/>\n",
       "</g>\n",
       "<!-- Subclass -->\n",
       "<g id=\"node10\" class=\"node\">\n",
       "<title>Subclass</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"78\" cy=\"-1114\" rx=\"41.6928\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-1110.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Subclass</text>\n",
       "</g>\n",
       "<!-- Class&#45;&gt;Subclass -->\n",
       "<g id=\"edge9\" class=\"edge\">\n",
       "<title>Class&#45;&gt;Subclass</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-1167.8314C78,-1160.131 78,-1150.9743 78,-1142.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-1142.4132 78,-1132.4133 74.5001,-1142.4133 81.5001,-1142.4132\"/>\n",
       "</g>\n",
       "<!-- Infraclass -->\n",
       "<g id=\"node11\" class=\"node\">\n",
       "<title>Infraclass</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"78\" cy=\"-1042\" rx=\"44.6926\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-1038.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Infraclass</text>\n",
       "</g>\n",
       "<!-- Subclass&#45;&gt;Infraclass -->\n",
       "<g id=\"edge10\" class=\"edge\">\n",
       "<title>Subclass&#45;&gt;Infraclass</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-1095.8314C78,-1088.131 78,-1078.9743 78,-1070.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-1070.4132 78,-1060.4133 74.5001,-1070.4133 81.5001,-1070.4132\"/>\n",
       "</g>\n",
       "<!-- Superorder -->\n",
       "<g id=\"node12\" class=\"node\">\n",
       "<title>Superorder</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"78\" cy=\"-970\" rx=\"50.0912\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-966.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Superorder</text>\n",
       "</g>\n",
       "<!-- Infraclass&#45;&gt;Superorder -->\n",
       "<g id=\"edge11\" class=\"edge\">\n",
       "<title>Infraclass&#45;&gt;Superorder</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-1023.8314C78,-1016.131 78,-1006.9743 78,-998.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-998.4132 78,-988.4133 74.5001,-998.4133 81.5001,-998.4132\"/>\n",
       "</g>\n",
       "<!-- Order -->\n",
       "<g id=\"node13\" class=\"node\">\n",
       "<title>Order</title>\n",
       "<ellipse fill=\"#d3d3d3\" stroke=\"#000000\" stroke-width=\"2\" cx=\"78\" cy=\"-898\" rx=\"31.3957\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-894.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Order</text>\n",
       "</g>\n",
       "<!-- Superorder&#45;&gt;Order -->\n",
       "<g id=\"edge12\" class=\"edge\">\n",
       "<title>Superorder&#45;&gt;Order</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-951.8314C78,-944.131 78,-934.9743 78,-926.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-926.4132 78,-916.4133 74.5001,-926.4133 81.5001,-926.4132\"/>\n",
       "</g>\n",
       "<!-- Suborder -->\n",
       "<g id=\"node14\" class=\"node\">\n",
       "<title>Suborder</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"78\" cy=\"-826\" rx=\"42.7926\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-822.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Suborder</text>\n",
       "</g>\n",
       "<!-- Order&#45;&gt;Suborder -->\n",
       "<g id=\"edge13\" class=\"edge\">\n",
       "<title>Order&#45;&gt;Suborder</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-879.8314C78,-872.131 78,-862.9743 78,-854.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-854.4132 78,-844.4133 74.5001,-854.4133 81.5001,-854.4132\"/>\n",
       "</g>\n",
       "<!-- Family -->\n",
       "<g id=\"node15\" class=\"node\">\n",
       "<title>Family</title>\n",
       "<ellipse fill=\"#d3d3d3\" stroke=\"#000000\" stroke-width=\"2\" cx=\"78\" cy=\"-754\" rx=\"36.2938\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-750.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Family</text>\n",
       "</g>\n",
       "<!-- Suborder&#45;&gt;Family -->\n",
       "<g id=\"edge14\" class=\"edge\">\n",
       "<title>Suborder&#45;&gt;Family</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-807.8314C78,-800.131 78,-790.9743 78,-782.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-782.4132 78,-772.4133 74.5001,-782.4133 81.5001,-782.4132\"/>\n",
       "</g>\n",
       "<!-- Subfamily -->\n",
       "<g id=\"node16\" class=\"node\">\n",
       "<title>Subfamily</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"78\" cy=\"-682\" rx=\"48.1917\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-678.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Subfamily</text>\n",
       "</g>\n",
       "<!-- Family&#45;&gt;Subfamily -->\n",
       "<g id=\"edge15\" class=\"edge\">\n",
       "<title>Family&#45;&gt;Subfamily</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-735.8314C78,-728.131 78,-718.9743 78,-710.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-710.4132 78,-700.4133 74.5001,-710.4133 81.5001,-710.4132\"/>\n",
       "</g>\n",
       "<!-- Tribe -->\n",
       "<g id=\"node17\" class=\"node\">\n",
       "<title>Tribe</title>\n",
       "<ellipse fill=\"#d3d3d3\" stroke=\"#000000\" stroke-width=\"2\" cx=\"78\" cy=\"-610\" rx=\"29.795\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-606.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Tribe</text>\n",
       "</g>\n",
       "<!-- Subfamily&#45;&gt;Tribe -->\n",
       "<g id=\"edge16\" class=\"edge\">\n",
       "<title>Subfamily&#45;&gt;Tribe</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-663.8314C78,-656.131 78,-646.9743 78,-638.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-638.4132 78,-628.4133 74.5001,-638.4133 81.5001,-638.4132\"/>\n",
       "</g>\n",
       "<!-- Subtribe -->\n",
       "<g id=\"node18\" class=\"node\">\n",
       "<title>Subtribe</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"78\" cy=\"-538\" rx=\"40.0939\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-534.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Subtribe</text>\n",
       "</g>\n",
       "<!-- Tribe&#45;&gt;Subtribe -->\n",
       "<g id=\"edge17\" class=\"edge\">\n",
       "<title>Tribe&#45;&gt;Subtribe</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-591.8314C78,-584.131 78,-574.9743 78,-566.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-566.4132 78,-556.4133 74.5001,-566.4133 81.5001,-566.4132\"/>\n",
       "</g>\n",
       "<!-- Genus -->\n",
       "<g id=\"node19\" class=\"node\">\n",
       "<title>Genus</title>\n",
       "<ellipse fill=\"#d3d3d3\" stroke=\"#000000\" stroke-width=\"2\" cx=\"78\" cy=\"-466\" rx=\"33.2948\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-462.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Genus</text>\n",
       "</g>\n",
       "<!-- Subtribe&#45;&gt;Genus -->\n",
       "<g id=\"edge18\" class=\"edge\">\n",
       "<title>Subtribe&#45;&gt;Genus</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-519.8314C78,-512.131 78,-502.9743 78,-494.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-494.4132 78,-484.4133 74.5001,-494.4133 81.5001,-494.4132\"/>\n",
       "</g>\n",
       "<!-- Subgenus -->\n",
       "<g id=\"node20\" class=\"node\">\n",
       "<title>Subgenus</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"78\" cy=\"-394\" rx=\"44.6926\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-390.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Subgenus</text>\n",
       "</g>\n",
       "<!-- Genus&#45;&gt;Subgenus -->\n",
       "<g id=\"edge19\" class=\"edge\">\n",
       "<title>Genus&#45;&gt;Subgenus</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-447.8314C78,-440.131 78,-430.9743 78,-422.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-422.4132 78,-412.4133 74.5001,-422.4133 81.5001,-422.4132\"/>\n",
       "</g>\n",
       "<!-- Section -->\n",
       "<g id=\"node21\" class=\"node\">\n",
       "<title>Section</title>\n",
       "<ellipse fill=\"#d3d3d3\" stroke=\"#000000\" stroke-width=\"2\" cx=\"78\" cy=\"-322\" rx=\"37.0935\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-318.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Section</text>\n",
       "</g>\n",
       "<!-- Subgenus&#45;&gt;Section -->\n",
       "<g id=\"edge20\" class=\"edge\">\n",
       "<title>Subgenus&#45;&gt;Section</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-375.8314C78,-368.131 78,-358.9743 78,-350.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-350.4132 78,-340.4133 74.5001,-350.4133 81.5001,-350.4132\"/>\n",
       "</g>\n",
       "<!-- Subsection -->\n",
       "<g id=\"node22\" class=\"node\">\n",
       "<title>Subsection</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"78\" cy=\"-250\" rx=\"49.2915\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-246.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Subsection</text>\n",
       "</g>\n",
       "<!-- Section&#45;&gt;Subsection -->\n",
       "<g id=\"edge21\" class=\"edge\">\n",
       "<title>Section&#45;&gt;Subsection</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-303.8314C78,-296.131 78,-286.9743 78,-278.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-278.4132 78,-268.4133 74.5001,-278.4133 81.5001,-278.4132\"/>\n",
       "</g>\n",
       "<!-- Species -->\n",
       "<g id=\"node23\" class=\"node\">\n",
       "<title>Species</title>\n",
       "<ellipse fill=\"#d3d3d3\" stroke=\"#000000\" stroke-width=\"2\" cx=\"78\" cy=\"-178\" rx=\"37.8943\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"78\" y=\"-174.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Species</text>\n",
       "</g>\n",
       "<!-- Subsection&#45;&gt;Species -->\n",
       "<g id=\"edge22\" class=\"edge\">\n",
       "<title>Subsection&#45;&gt;Species</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M78,-231.8314C78,-224.131 78,-214.9743 78,-206.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"81.5001,-206.4132 78,-196.4133 74.5001,-206.4133 81.5001,-206.4132\"/>\n",
       "</g>\n",
       "<!-- Subspecies -->\n",
       "<g id=\"node24\" class=\"node\">\n",
       "<title>Subspecies</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"66\" cy=\"-106\" rx=\"50.0912\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"66\" y=\"-102.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Subspecies</text>\n",
       "</g>\n",
       "<!-- Species&#45;&gt;Subspecies -->\n",
       "<g id=\"edge23\" class=\"edge\">\n",
       "<title>Species&#45;&gt;Subspecies</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M74.9719,-159.8314C73.6885,-152.131 72.1624,-142.9743 70.7361,-134.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"74.1653,-133.7018 69.0689,-124.4133 67.2606,-134.8526 74.1653,-133.7018\"/>\n",
       "</g>\n",
       "<!-- Variety -->\n",
       "<g id=\"node25\" class=\"node\">\n",
       "<title>Variety</title>\n",
       "<ellipse fill=\"#d3d3d3\" stroke=\"#000000\" stroke-width=\"2\" cx=\"183\" cy=\"-106\" rx=\"37.0935\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"183\" y=\"-102.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Variety</text>\n",
       "</g>\n",
       "<!-- Species&#45;&gt;Variety -->\n",
       "<g id=\"edge24\" class=\"edge\">\n",
       "<title>Species&#45;&gt;Variety</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M99.7561,-163.0816C115.1017,-152.5588 135.8583,-138.3258 152.8055,-126.7048\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"155.0039,-129.4412 161.2718,-120.8993 151.0451,-123.6681 155.0039,-129.4412\"/>\n",
       "</g>\n",
       "<!-- Form -->\n",
       "<g id=\"node27\" class=\"node\">\n",
       "<title>Form</title>\n",
       "<ellipse fill=\"#d3d3d3\" stroke=\"#000000\" stroke-width=\"2\" cx=\"298\" cy=\"-106\" rx=\"30.5947\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"298\" y=\"-102.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Form</text>\n",
       "</g>\n",
       "<!-- Species&#45;&gt;Form -->\n",
       "<g id=\"edge26\" class=\"edge\">\n",
       "<title>Species&#45;&gt;Form</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M113.0369,-171.1626C148.3504,-163.6884 204.0505,-150.2929 250,-132 255.7537,-129.7094 261.6974,-126.8942 267.3593,-123.9715\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"269.3958,-126.8499 276.5385,-119.0247 266.075,-120.6877 269.3958,-126.8499\"/>\n",
       "</g>\n",
       "<!-- Subvariety -->\n",
       "<g id=\"node26\" class=\"node\">\n",
       "<title>Subvariety</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"189\" cy=\"-34\" rx=\"48.9926\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"189\" y=\"-30.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Subvariety</text>\n",
       "</g>\n",
       "<!-- Variety&#45;&gt;Subvariety -->\n",
       "<g id=\"edge25\" class=\"edge\">\n",
       "<title>Variety&#45;&gt;Subvariety</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M184.5141,-87.8314C185.1558,-80.131 185.9188,-70.9743 186.6319,-62.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"190.1229,-62.6694 187.4656,-52.4133 183.1471,-62.088 190.1229,-62.6694\"/>\n",
       "</g>\n",
       "<!-- Subform -->\n",
       "<g id=\"node28\" class=\"node\">\n",
       "<title>Subform</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"304\" cy=\"-34\" rx=\"42.4939\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"304\" y=\"-30.3\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">Subform</text>\n",
       "</g>\n",
       "<!-- Form&#45;&gt;Subform -->\n",
       "<g id=\"edge27\" class=\"edge\">\n",
       "<title>Form&#45;&gt;Subform</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M299.5141,-87.8314C300.1558,-80.131 300.9188,-70.9743 301.6319,-62.4166\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"305.1229,-62.6694 302.4656,-52.4133 298.1471,-62.088 305.1229,-62.6694\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x7f8cfc3e6fd0>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d = graphviz.Digraph('Taxonomy')\n",
    "d.attr(rankdir='TB')\n",
    "\n",
    "groups = {}\n",
    "\n",
    "for row_id, row in df_taxon_unit_types.iterrows():\n",
    "    if row.rank_group not in groups:\n",
    "        rank_group_row = df_taxon_unit_types[df_taxon_unit_types.rank_id == row.rank_group].iloc[0]\n",
    "        groups[row.rank_group] = graphviz.Digraph(f'cluster_{rank_group_row.rank_name}')\n",
    "        groups[row.rank_group].attr(style='dotted')\n",
    "    \n",
    "    s = groups[row.rank_group]\n",
    "    s.node(row.rank_name, style='bold,filled' if row.rank_id == row.rank_group else '')\n",
    "    \n",
    "    if row.dir_parent_rank_id == row.rank_id:\n",
    "        continue\n",
    "    \n",
    "    dir_parent_row = df_taxon_unit_types[df_taxon_unit_types.rank_id == row.dir_parent_rank_id].iloc[0]\n",
    "    s.edge(dir_parent_row.rank_name, row.rank_name)\n",
    "\n",
    "for group_id, graph in groups.items():\n",
    "    d.subgraph(graph)\n",
    "    \n",
    "d"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "interracial-holder",
   "metadata": {},
   "source": [
    "To get a starting point into the hierarchy of entries, we need to find the TSN for the kingdom. We can look this up from the `longnames` table by specifying the name `Plantae`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "bigger-remedy",
   "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>tsn</th>\n",
       "      <th>completename</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>202345</th>\n",
       "      <td>202422</td>\n",
       "      <td>Plantae</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           tsn completename\n",
       "202345  202422      Plantae"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_complete_names = pd.read_sql_query(\"SELECT * from longnames\", con)\n",
    "df_complete_names[df_complete_names.completename == 'Plantae']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "employed-lithuania",
   "metadata": {},
   "source": [
    "The hierarchy dataset gives us relations between TSN. For TSN `202422` (Plantae) we also find that there is no \"parent\" TSN, and that the level is `0`, indicating a root node:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "instant-likelihood",
   "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>hierarchy_string</th>\n",
       "      <th>TSN</th>\n",
       "      <th>Parent_TSN</th>\n",
       "      <th>level</th>\n",
       "      <th>ChildrenCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>202422</td>\n",
       "      <td>202422</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>165892</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  hierarchy_string     TSN  Parent_TSN  level  ChildrenCount\n",
       "0           202422  202422           0      0         165892"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_hierarchy = pd.read_sql_query(\"SELECT * from hierarchy\", con)\n",
    "df_hierarchy[df_hierarchy.TSN == 202422]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bridal-curve",
   "metadata": {},
   "source": [
    "Finding all entries with the parent TSN of `202422` we find `846491`(Biliphyta) and `954898`(Viridiplantae):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "visible-trouble",
   "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>hierarchy_string</th>\n",
       "      <th>TSN</th>\n",
       "      <th>Parent_TSN</th>\n",
       "      <th>level</th>\n",
       "      <th>ChildrenCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>202422-846491</td>\n",
       "      <td>846491</td>\n",
       "      <td>202422</td>\n",
       "      <td>1</td>\n",
       "      <td>2666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2295</th>\n",
       "      <td>202422-954898</td>\n",
       "      <td>954898</td>\n",
       "      <td>202422</td>\n",
       "      <td>1</td>\n",
       "      <td>163222</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     hierarchy_string     TSN  Parent_TSN  level  ChildrenCount\n",
       "1       202422-846491  846491      202422      1           2666\n",
       "2295    202422-954898  954898      202422      1         163222"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_hierarchy[df_hierarchy.Parent_TSN == 202422]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "looking-thong",
   "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>tsn</th>\n",
       "      <th>completename</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>556620</th>\n",
       "      <td>846491</td>\n",
       "      <td>Biliphyta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>665027</th>\n",
       "      <td>954898</td>\n",
       "      <td>Viridiplantae</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           tsn   completename\n",
       "556620  846491      Biliphyta\n",
       "665027  954898  Viridiplantae"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_complete_names[df_complete_names.tsn.isin([846491, 954898])]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fluid-momentum",
   "metadata": {},
   "source": [
    "Vernaculars are _common names_ for entries. Note that not all TSN have vernacular names (e.g. `846491`, algae):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "changed-oriental",
   "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>tsn</th>\n",
       "      <th>vernacular_name</th>\n",
       "      <th>language</th>\n",
       "      <th>approved_ind</th>\n",
       "      <th>update_date</th>\n",
       "      <th>vern_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>126130</th>\n",
       "      <td>954898</td>\n",
       "      <td>green plants</td>\n",
       "      <td>English</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-09-25</td>\n",
       "      <td>167904</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           tsn vernacular_name language approved_ind update_date  vern_id\n",
       "126130  954898    green plants  English            N  2018-09-25   167904"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_vernaculars = pd.read_sql_query(\"SELECT * from vernaculars\", con)\n",
    "df_vernaculars[df_vernaculars.tsn.isin([846491, 954898])]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "adult-going",
   "metadata": {},
   "source": [
    "Note also that for some TSN we may also have _multiple_ vernacular names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ignored-wages",
   "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>tsn</th>\n",
       "      <th>vernacular_name</th>\n",
       "      <th>language</th>\n",
       "      <th>approved_ind</th>\n",
       "      <th>update_date</th>\n",
       "      <th>vern_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50</td>\n",
       "      <td>bactéries</td>\n",
       "      <td>French</td>\n",
       "      <td>N</td>\n",
       "      <td>2015-03-02</td>\n",
       "      <td>159941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50</td>\n",
       "      <td>bacteria</td>\n",
       "      <td>English</td>\n",
       "      <td>N</td>\n",
       "      <td>2015-03-02</td>\n",
       "      <td>159942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50</td>\n",
       "      <td>bacterias</td>\n",
       "      <td>Spanish</td>\n",
       "      <td>N</td>\n",
       "      <td>2015-03-02</td>\n",
       "      <td>159943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>50</td>\n",
       "      <td>bactérias</td>\n",
       "      <td>Portuguese</td>\n",
       "      <td>N</td>\n",
       "      <td>2015-03-02</td>\n",
       "      <td>159944</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tsn vernacular_name    language approved_ind update_date  vern_id\n",
       "0   50       bactéries      French            N  2015-03-02   159941\n",
       "1   50        bacteria     English            N  2015-03-02   159942\n",
       "2   50       bacterias     Spanish            N  2015-03-02   159943\n",
       "3   50       bactérias  Portuguese            N  2015-03-02   159944"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_vernaculars[df_vernaculars.tsn == 50]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "extra-duration",
   "metadata": {},
   "source": [
    "The `taxonomic_units` table gives a little bit more insight into the relations. We find (again) that the parent TSN of both `846491` and `954898` is `202422` (Plantae), that the taxonomic rank of both entries is `20` (subkingdom), and that the [taxonomic status](https://www.itis.gov/glossary.html#taxstatus) (`n_usage`) is `accepted` (vs. `not accepted`):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "considerable-loading",
   "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>tsn</th>\n",
       "      <th>unit_ind1</th>\n",
       "      <th>unit_name1</th>\n",
       "      <th>unit_ind2</th>\n",
       "      <th>unit_name2</th>\n",
       "      <th>unit_ind3</th>\n",
       "      <th>unit_name3</th>\n",
       "      <th>unit_ind4</th>\n",
       "      <th>unit_name4</th>\n",
       "      <th>unnamed_taxon_ind</th>\n",
       "      <th>...</th>\n",
       "      <th>initial_time_stamp</th>\n",
       "      <th>parent_tsn</th>\n",
       "      <th>taxon_author_id</th>\n",
       "      <th>hybrid_author_id</th>\n",
       "      <th>kingdom_id</th>\n",
       "      <th>rank_id</th>\n",
       "      <th>update_date</th>\n",
       "      <th>uncertain_prnt_ind</th>\n",
       "      <th>n_usage</th>\n",
       "      <th>complete_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>846491</td>\n",
       "      <td>None</td>\n",
       "      <td>Biliphyta</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>2012-03-29 09:35:26</td>\n",
       "      <td>202422.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>2014-12-22</td>\n",
       "      <td>No</td>\n",
       "      <td>accepted</td>\n",
       "      <td>Biliphyta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>954898</td>\n",
       "      <td>None</td>\n",
       "      <td>Viridiplantae</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>2014-12-22 14:36:34</td>\n",
       "      <td>202422.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>2018-09-25</td>\n",
       "      <td>No</td>\n",
       "      <td>accepted</td>\n",
       "      <td>Viridiplantae</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      tsn unit_ind1     unit_name1 unit_ind2 unit_name2 unit_ind3 unit_name3  \\\n",
       "2  846491      None      Biliphyta      None       None      None       None   \n",
       "4  954898      None  Viridiplantae      None       None      None       None   \n",
       "\n",
       "  unit_ind4 unit_name4 unnamed_taxon_ind  ...   initial_time_stamp parent_tsn  \\\n",
       "2      None       None                 N  ...  2012-03-29 09:35:26   202422.0   \n",
       "4      None       None                 N  ...  2014-12-22 14:36:34   202422.0   \n",
       "\n",
       "  taxon_author_id hybrid_author_id kingdom_id  rank_id update_date  \\\n",
       "2               0                0          3       20  2014-12-22   \n",
       "4               0                0          3       20  2018-09-25   \n",
       "\n",
       "   uncertain_prnt_ind   n_usage  complete_name  \n",
       "2                  No  accepted      Biliphyta  \n",
       "4                  No  accepted  Viridiplantae  \n",
       "\n",
       "[2 rows x 26 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_taxonomic_units = pd.read_sql_query(\"SELECT * from taxonomic_units WHERE kingdom_id = (?)\", con,\n",
    "                                      params = (3,))\n",
    "df_taxonomic_units[df_taxonomic_units.tsn.isin([846491, 954898])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "flying-greenhouse",
   "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>tsn</th>\n",
       "      <th>unit_ind1</th>\n",
       "      <th>unit_name1</th>\n",
       "      <th>unit_ind2</th>\n",
       "      <th>unit_name2</th>\n",
       "      <th>unit_ind3</th>\n",
       "      <th>unit_name3</th>\n",
       "      <th>unit_ind4</th>\n",
       "      <th>unit_name4</th>\n",
       "      <th>unnamed_taxon_ind</th>\n",
       "      <th>...</th>\n",
       "      <th>initial_time_stamp</th>\n",
       "      <th>parent_tsn</th>\n",
       "      <th>taxon_author_id</th>\n",
       "      <th>hybrid_author_id</th>\n",
       "      <th>kingdom_id</th>\n",
       "      <th>rank_id</th>\n",
       "      <th>update_date</th>\n",
       "      <th>uncertain_prnt_ind</th>\n",
       "      <th>n_usage</th>\n",
       "      <th>complete_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>42835</th>\n",
       "      <td>502333</td>\n",
       "      <td>None</td>\n",
       "      <td>Epipremnum</td>\n",
       "      <td>None</td>\n",
       "      <td>pinnatum</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1996-11-04 17:57:08</td>\n",
       "      <td>500265.0</td>\n",
       "      <td>46787</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>220</td>\n",
       "      <td>2011-03-22</td>\n",
       "      <td>No</td>\n",
       "      <td>accepted</td>\n",
       "      <td>Epipremnum pinnatum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44374</th>\n",
       "      <td>503872</td>\n",
       "      <td>None</td>\n",
       "      <td>Monstera</td>\n",
       "      <td>None</td>\n",
       "      <td>deliciosa</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1996-11-04 17:57:08</td>\n",
       "      <td>500426.0</td>\n",
       "      <td>41479</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>220</td>\n",
       "      <td>2011-03-22</td>\n",
       "      <td>No</td>\n",
       "      <td>accepted</td>\n",
       "      <td>Monstera deliciosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47264</th>\n",
       "      <td>506762</td>\n",
       "      <td>None</td>\n",
       "      <td>Monstera</td>\n",
       "      <td>None</td>\n",
       "      <td>adansonii</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1996-11-04 17:57:08</td>\n",
       "      <td>500426.0</td>\n",
       "      <td>42941</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>220</td>\n",
       "      <td>2011-03-22</td>\n",
       "      <td>No</td>\n",
       "      <td>accepted</td>\n",
       "      <td>Monstera adansonii</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61611</th>\n",
       "      <td>521112</td>\n",
       "      <td>None</td>\n",
       "      <td>Scindapsus</td>\n",
       "      <td>None</td>\n",
       "      <td>aureus</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>1996-11-04 17:57:08</td>\n",
       "      <td>0.0</td>\n",
       "      <td>140631</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>220</td>\n",
       "      <td>2011-03-22</td>\n",
       "      <td>No</td>\n",
       "      <td>not accepted</td>\n",
       "      <td>Scindapsus aureus</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          tsn unit_ind1  unit_name1 unit_ind2 unit_name2 unit_ind3 unit_name3  \\\n",
       "42835  502333      None  Epipremnum      None   pinnatum      None       None   \n",
       "44374  503872      None    Monstera      None  deliciosa      None       None   \n",
       "47264  506762      None    Monstera      None  adansonii      None       None   \n",
       "61611  521112      None  Scindapsus      None     aureus      None       None   \n",
       "\n",
       "      unit_ind4 unit_name4 unnamed_taxon_ind  ...   initial_time_stamp  \\\n",
       "42835      None       None                 N  ...  1996-11-04 17:57:08   \n",
       "44374      None       None                 N  ...  1996-11-04 17:57:08   \n",
       "47264      None       None                 N  ...  1996-11-04 17:57:08   \n",
       "61611      None       None              None  ...  1996-11-04 17:57:08   \n",
       "\n",
       "      parent_tsn taxon_author_id hybrid_author_id kingdom_id  rank_id  \\\n",
       "42835   500265.0           46787                0          3      220   \n",
       "44374   500426.0           41479                0          3      220   \n",
       "47264   500426.0           42941                0          3      220   \n",
       "61611        0.0          140631                0          3      220   \n",
       "\n",
       "      update_date  uncertain_prnt_ind       n_usage        complete_name  \n",
       "42835  2011-03-22                  No      accepted  Epipremnum pinnatum  \n",
       "44374  2011-03-22                  No      accepted   Monstera deliciosa  \n",
       "47264  2011-03-22                  No      accepted   Monstera adansonii  \n",
       "61611  2011-03-22                  No  not accepted    Scindapsus aureus  \n",
       "\n",
       "[4 rows x 26 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_taxonomic_units[df_taxonomic_units.tsn.isin([503872, 506762, \n",
    "                                                502333, 521112])]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "detected-pottery",
   "metadata": {},
   "source": [
    "Note here that the TSN `521112` (Scindapsus aureus) does not have an accepted name and that the `unaccept_reason` column indicates that the name is a synonym for _something else_:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "duplicate-roots",
   "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>61611</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>tsn</th>\n",
       "      <td>521112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind1</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name1</th>\n",
       "      <td>Scindapsus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind2</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name2</th>\n",
       "      <td>aureus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind3</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name3</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind4</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name4</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unnamed_taxon_ind</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name_usage</th>\n",
       "      <td>not accepted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unaccept_reason</th>\n",
       "      <td>synonym</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>credibility_rtng</th>\n",
       "      <td>TWG standards met</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>completeness_rtng</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>currency_rating</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>phylo_sort_seq</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>initial_time_stamp</th>\n",
       "      <td>1996-11-04 17:57:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>parent_tsn</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>taxon_author_id</th>\n",
       "      <td>140631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hybrid_author_id</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kingdom_id</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rank_id</th>\n",
       "      <td>220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>update_date</th>\n",
       "      <td>2011-03-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>uncertain_prnt_ind</th>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>n_usage</th>\n",
       "      <td>not accepted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>complete_name</th>\n",
       "      <td>Scindapsus aureus</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  61611\n",
       "tsn                              521112\n",
       "unit_ind1                          None\n",
       "unit_name1                   Scindapsus\n",
       "unit_ind2                          None\n",
       "unit_name2                       aureus\n",
       "unit_ind3                          None\n",
       "unit_name3                         None\n",
       "unit_ind4                          None\n",
       "unit_name4                         None\n",
       "unnamed_taxon_ind                  None\n",
       "name_usage                 not accepted\n",
       "unaccept_reason                 synonym\n",
       "credibility_rtng      TWG standards met\n",
       "completeness_rtng                  None\n",
       "currency_rating                    None\n",
       "phylo_sort_seq                        0\n",
       "initial_time_stamp  1996-11-04 17:57:08\n",
       "parent_tsn                          0.0\n",
       "taxon_author_id                  140631\n",
       "hybrid_author_id                      0\n",
       "kingdom_id                            3\n",
       "rank_id                             220\n",
       "update_date                  2011-03-22\n",
       "uncertain_prnt_ind                   No\n",
       "n_usage                    not accepted\n",
       "complete_name         Scindapsus aureus"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_taxonomic_units[df_taxonomic_units.tsn == 521112].transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "unlike-alias",
   "metadata": {},
   "source": [
    "The `synonym_links` table shows that for TSN `521112` we have the accepted TSN `502333`, which is the _Epipremnum pinnatum_ shown in the table above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "shared-bearing",
   "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>tsn</th>\n",
       "      <th>tsn_accepted</th>\n",
       "      <th>update_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>70274</th>\n",
       "      <td>521112</td>\n",
       "      <td>502333</td>\n",
       "      <td>1996-11-05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          tsn  tsn_accepted update_date\n",
       "70274  521112        502333  1996-11-05"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_synonym_links = pd.read_sql_query(\"SELECT * from synonym_links\", con)\n",
    "df_synonym_links[df_synonym_links.tsn == 521112]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "prompt-rolling",
   "metadata": {},
   "source": [
    "According to the documentation, reasons for rejection in the _Plantae_ kingdom are:\n",
    "\n",
    "- database artifact\n",
    "- homonym (illegitimate)\n",
    "- horticultural\n",
    "- invalidly published, nomen nudum\n",
    "- invalidly published, other\n",
    "- misapplied\n",
    "- orthographic variant (misspelling)\n",
    "- other, see comments\n",
    "- pro parte\n",
    "- rejected name\n",
    "- superfluous renaming (illegitimate)\n",
    "- synonym\n",
    "- unspecified in provided data\n",
    "\n",
    "The following summary shows the actutal value distribution:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "furnished-power",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "unaccept_reason\n",
       "synonym                                61219\n",
       "other, see comments                    22504\n",
       "database artifact                       1940\n",
       "orthographic variant (misspelling)      1770\n",
       "superfluous renaming (illegitimate)      630\n",
       "misapplied                               601\n",
       "invalidly published, nomen nudum         550\n",
       "invalidly published, other               444\n",
       "homonym (illegitimate)                   430\n",
       "pro parte                                 86\n",
       "rejected name                             55\n",
       "horticultural                             16\n",
       "Name: unaccept_reason, dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_taxonomic_units.groupby(by='unaccept_reason')['unaccept_reason'].count().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "behavioral-bathroom",
   "metadata": {},
   "source": [
    "For TSN `894311` we get \"_Ceropegia somalensis_ f. _somalensis_\" which is rejected with the reason `other, see comments`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "compressed-aurora",
   "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>165726</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>tsn</th>\n",
       "      <td>894311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind1</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name1</th>\n",
       "      <td>Ceropegia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind2</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name2</th>\n",
       "      <td>somalensis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind3</th>\n",
       "      <td>f.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name3</th>\n",
       "      <td>somalensis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind4</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name4</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unnamed_taxon_ind</th>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name_usage</th>\n",
       "      <td>not accepted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unaccept_reason</th>\n",
       "      <td>other, see comments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>credibility_rtng</th>\n",
       "      <td>TWG standards met</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>completeness_rtng</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>currency_rating</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>phylo_sort_seq</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>initial_time_stamp</th>\n",
       "      <td>2012-05-31 10:59:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>parent_tsn</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>taxon_author_id</th>\n",
       "      <td>76184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hybrid_author_id</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kingdom_id</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rank_id</th>\n",
       "      <td>260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>update_date</th>\n",
       "      <td>2012-05-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>uncertain_prnt_ind</th>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>n_usage</th>\n",
       "      <td>not accepted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>complete_name</th>\n",
       "      <td>Ceropegia somalensis f. somalensis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                165726\n",
       "tsn                                             894311\n",
       "unit_ind1                                         None\n",
       "unit_name1                                   Ceropegia\n",
       "unit_ind2                                         None\n",
       "unit_name2                                  somalensis\n",
       "unit_ind3                                           f.\n",
       "unit_name3                                  somalensis\n",
       "unit_ind4                                         None\n",
       "unit_name4                                        None\n",
       "unnamed_taxon_ind                                    N\n",
       "name_usage                                not accepted\n",
       "unaccept_reason                    other, see comments\n",
       "credibility_rtng                     TWG standards met\n",
       "completeness_rtng                                 None\n",
       "currency_rating                                   None\n",
       "phylo_sort_seq                                       0\n",
       "initial_time_stamp                 2012-05-31 10:59:49\n",
       "parent_tsn                                         0.0\n",
       "taxon_author_id                                  76184\n",
       "hybrid_author_id                                     0\n",
       "kingdom_id                                           3\n",
       "rank_id                                            260\n",
       "update_date                                 2012-05-31\n",
       "uncertain_prnt_ind                                  No\n",
       "n_usage                                   not accepted\n",
       "complete_name       Ceropegia somalensis f. somalensis"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_taxonomic_units[df_taxonomic_units.tsn == 894311].transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "scenic-calibration",
   "metadata": {},
   "source": [
    "Looking up the comment, we find the detail \"obsolete autonym\" (indicating that \"somalensis f. somalensis\" is a problem):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "underlying-suspension",
   "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>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>comment_id</th>\n",
       "      <td>21842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>commentator</th>\n",
       "      <td>S.N. Alexander, ITIS Data Development Specialist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>comment_detail</th>\n",
       "      <td>obsolete autonym</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>comment_time_stamp</th>\n",
       "      <td>2011-06-29 08:41:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>update_date</th>\n",
       "      <td>2017-04-25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                   0\n",
       "comment_id                                                     21842\n",
       "commentator         S.N. Alexander, ITIS Data Development Specialist\n",
       "comment_detail                                      obsolete autonym\n",
       "comment_time_stamp                               2011-06-29 08:41:44\n",
       "update_date                                               2017-04-25"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "    SELECT comments.* \n",
    "    FROM \n",
    "        comments, \n",
    "        tu_comments_links \n",
    "    WHERE \n",
    "        tu_comments_links.tsn = (?) \n",
    "    AND comments.comment_id = tu_comments_links.comment_id\n",
    "    \"\"\", con, params=(894311,)).transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "durable-azerbaijan",
   "metadata": {},
   "source": [
    "Looking up any synonyms, we find that _Ceropegia somalensis_ is the accepted name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "similar-theater",
   "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>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>tsn</th>\n",
       "      <td>894060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind1</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name1</th>\n",
       "      <td>Ceropegia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind2</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name2</th>\n",
       "      <td>somalensis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind3</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name3</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind4</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name4</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unnamed_taxon_ind</th>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name_usage</th>\n",
       "      <td>accepted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unaccept_reason</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>credibility_rtng</th>\n",
       "      <td>TWG standards met</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>completeness_rtng</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>currency_rating</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>phylo_sort_seq</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>initial_time_stamp</th>\n",
       "      <td>2012-05-31 10:58:51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>parent_tsn</th>\n",
       "      <td>893788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>taxon_author_id</th>\n",
       "      <td>76184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hybrid_author_id</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kingdom_id</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rank_id</th>\n",
       "      <td>220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>update_date</th>\n",
       "      <td>2012-05-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>uncertain_prnt_ind</th>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>n_usage</th>\n",
       "      <td>accepted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>complete_name</th>\n",
       "      <td>Ceropegia somalensis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       0\n",
       "tsn                               894060\n",
       "unit_ind1                           None\n",
       "unit_name1                     Ceropegia\n",
       "unit_ind2                           None\n",
       "unit_name2                    somalensis\n",
       "unit_ind3                           None\n",
       "unit_name3                          None\n",
       "unit_ind4                           None\n",
       "unit_name4                          None\n",
       "unnamed_taxon_ind                      N\n",
       "name_usage                      accepted\n",
       "unaccept_reason                     None\n",
       "credibility_rtng       TWG standards met\n",
       "completeness_rtng                   None\n",
       "currency_rating                     None\n",
       "phylo_sort_seq                         0\n",
       "initial_time_stamp   2012-05-31 10:58:51\n",
       "parent_tsn                        893788\n",
       "taxon_author_id                    76184\n",
       "hybrid_author_id                       0\n",
       "kingdom_id                             3\n",
       "rank_id                              220\n",
       "update_date                   2012-05-31\n",
       "uncertain_prnt_ind                    No\n",
       "n_usage                         accepted\n",
       "complete_name       Ceropegia somalensis"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "    SELECT taxonomic_units.* \n",
    "    FROM \n",
    "        taxonomic_units, \n",
    "        synonym_links \n",
    "    WHERE \n",
    "        synonym_links.tsn = (?) \n",
    "    AND taxonomic_units.tsn = synonym_links.tsn_accepted\n",
    "    \"\"\", con, params=(894311,)).transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "popular-cleanup",
   "metadata": {},
   "source": [
    "Conversely we find that for TSN `894060` (Ceropegia somalensis) there are two synonyms pointing to it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "accomplished-friday",
   "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>tsn</th>\n",
       "      <td>894310</td>\n",
       "      <td>894311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind1</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name1</th>\n",
       "      <td>Ceropegia</td>\n",
       "      <td>Ceropegia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind2</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name2</th>\n",
       "      <td>somalensis</td>\n",
       "      <td>somalensis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind3</th>\n",
       "      <td>f.</td>\n",
       "      <td>f.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name3</th>\n",
       "      <td>erostrata</td>\n",
       "      <td>somalensis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_ind4</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_name4</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unnamed_taxon_ind</th>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name_usage</th>\n",
       "      <td>not accepted</td>\n",
       "      <td>not accepted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unaccept_reason</th>\n",
       "      <td>synonym</td>\n",
       "      <td>other, see comments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>credibility_rtng</th>\n",
       "      <td>TWG standards met</td>\n",
       "      <td>TWG standards met</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>completeness_rtng</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>currency_rating</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>phylo_sort_seq</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>initial_time_stamp</th>\n",
       "      <td>2012-05-31 10:59:49</td>\n",
       "      <td>2012-05-31 10:59:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>parent_tsn</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>taxon_author_id</th>\n",
       "      <td>162379</td>\n",
       "      <td>76184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hybrid_author_id</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kingdom_id</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rank_id</th>\n",
       "      <td>260</td>\n",
       "      <td>260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>update_date</th>\n",
       "      <td>2012-05-31</td>\n",
       "      <td>2012-05-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>uncertain_prnt_ind</th>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>n_usage</th>\n",
       "      <td>not accepted</td>\n",
       "      <td>not accepted</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>complete_name</th>\n",
       "      <td>Ceropegia somalensis f. erostrata</td>\n",
       "      <td>Ceropegia somalensis f. somalensis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    0  \\\n",
       "tsn                                            894310   \n",
       "unit_ind1                                        None   \n",
       "unit_name1                                  Ceropegia   \n",
       "unit_ind2                                        None   \n",
       "unit_name2                                 somalensis   \n",
       "unit_ind3                                          f.   \n",
       "unit_name3                                  erostrata   \n",
       "unit_ind4                                        None   \n",
       "unit_name4                                       None   \n",
       "unnamed_taxon_ind                                   N   \n",
       "name_usage                               not accepted   \n",
       "unaccept_reason                               synonym   \n",
       "credibility_rtng                    TWG standards met   \n",
       "completeness_rtng                                None   \n",
       "currency_rating                                  None   \n",
       "phylo_sort_seq                                      0   \n",
       "initial_time_stamp                2012-05-31 10:59:49   \n",
       "parent_tsn                                          0   \n",
       "taxon_author_id                                162379   \n",
       "hybrid_author_id                                    0   \n",
       "kingdom_id                                          3   \n",
       "rank_id                                           260   \n",
       "update_date                                2012-05-31   \n",
       "uncertain_prnt_ind                                 No   \n",
       "n_usage                                  not accepted   \n",
       "complete_name       Ceropegia somalensis f. erostrata   \n",
       "\n",
       "                                                     1  \n",
       "tsn                                             894311  \n",
       "unit_ind1                                         None  \n",
       "unit_name1                                   Ceropegia  \n",
       "unit_ind2                                         None  \n",
       "unit_name2                                  somalensis  \n",
       "unit_ind3                                           f.  \n",
       "unit_name3                                  somalensis  \n",
       "unit_ind4                                         None  \n",
       "unit_name4                                        None  \n",
       "unnamed_taxon_ind                                    N  \n",
       "name_usage                                not accepted  \n",
       "unaccept_reason                    other, see comments  \n",
       "credibility_rtng                     TWG standards met  \n",
       "completeness_rtng                                 None  \n",
       "currency_rating                                   None  \n",
       "phylo_sort_seq                                       0  \n",
       "initial_time_stamp                 2012-05-31 10:59:49  \n",
       "parent_tsn                                           0  \n",
       "taxon_author_id                                  76184  \n",
       "hybrid_author_id                                     0  \n",
       "kingdom_id                                           3  \n",
       "rank_id                                            260  \n",
       "update_date                                 2012-05-31  \n",
       "uncertain_prnt_ind                                  No  \n",
       "n_usage                                   not accepted  \n",
       "complete_name       Ceropegia somalensis f. somalensis  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "    SELECT taxonomic_units.* \n",
    "    FROM \n",
    "        taxonomic_units, \n",
    "        synonym_links \n",
    "    WHERE \n",
    "        synonym_links.tsn_accepted = (?) \n",
    "    AND taxonomic_units.tsn = synonym_links.tsn\n",
    "    \"\"\", con, params=(894060,)).transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "unnecessary-belgium",
   "metadata": {},
   "source": [
    "Lastly, here is the distribution of `accepted` vs. `not accepted`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "established-niagara",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name_usage\n",
       "not accepted    90245\n",
       "accepted        75642\n",
       "Name: name_usage, dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_taxonomic_units.groupby(by='name_usage')['name_usage'].count().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "accomplished-ecology",
   "metadata": {},
   "source": [
    "To get the authors for each taxon, we can look up the `taxon_author_id` referenced in the `taxonomic_units` table within the `taxon_authors_lkp` table. For _Ceropegia somalensis_ above this gives _Ceropegia somalensis Chiov._"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "negative-permit",
   "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>taxon_author_id</th>\n",
       "      <th>taxon_author</th>\n",
       "      <th>update_date</th>\n",
       "      <th>kingdom_id</th>\n",
       "      <th>short_author</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22311</th>\n",
       "      <td>76184</td>\n",
       "      <td>Chiov.</td>\n",
       "      <td>2000-03-15</td>\n",
       "      <td>3</td>\n",
       "      <td>Chiov</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       taxon_author_id taxon_author update_date  kingdom_id short_author\n",
       "22311            76184       Chiov.  2000-03-15           3        Chiov"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_taxon_authors = pd.read_sql_query(\"SELECT * from taxon_authors_lkp WHERE kingdom_id = (?)\", con,\n",
    "                                      params = (3,))\n",
    "df_taxon_authors[df_taxon_authors.taxon_author_id == 76184]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "flexible-offering",
   "metadata": {},
   "source": [
    "# Transferring to Dgraph\n",
    "\n",
    "After starting Dgraph using `docker-compose up`, you can open up the Ratel frontend at http://localhost:8000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "registered-sterling",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pydgraph\n",
    "\n",
    "client_stub = pydgraph.DgraphClientStub('localhost:9080')\n",
    "client = pydgraph.DgraphClient(client_stub)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "stock-samba",
   "metadata": {},
   "outputs": [],
   "source": [
    "# client.login('groot', 'password')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sporting-motion",
   "metadata": {},
   "source": [
    "To get into a clean state, we can now drop all data including schema from the Dgraph instance."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "constant-kitchen",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "op = pydgraph.Operation(drop_all=True)\n",
    "client.alter(op)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "intellectual-expansion",
   "metadata": {},
   "outputs": [],
   "source": [
    "schema = \"\"\"\n",
    "type Rank {\n",
    "    rank.itis_rank_id\n",
    "    rank.itis_rank_id_group\n",
    "    rank.contains_rank\n",
    "}\n",
    "\n",
    "type Named {\n",
    "    named.has_name\n",
    "}\n",
    "\n",
    "type TaxonAuthor {\n",
    "    author.itis_id\n",
    "    author.name\n",
    "    author.name_short\n",
    "}\n",
    "\n",
    "type TaxonomicUnit {\n",
    "    tu.tsn\n",
    "    tu.author\n",
    "    tu.parent_of\n",
    "}\n",
    "\n",
    "type Name {\n",
    "    name.name\n",
    "}\n",
    "\n",
    "type AuthorName {\n",
    "    name.name\n",
    "}\n",
    "\n",
    "type CompleteName {\n",
    "    name.name\n",
    "}\n",
    "\n",
    "type AcceptedName {\n",
    "    name.name\n",
    "}\n",
    "\n",
    "type Synonym {\n",
    "    synonym.for\n",
    "    name.name\n",
    "}\n",
    "\n",
    "name.name: string @index(fulltext) @lang .\n",
    "\n",
    "rank.name: string @index(exact) @lang .\n",
    "rank.itis_rank_id: int @index(int) .\n",
    "rank.itis_rank_id_group: int @index(int) .\n",
    "rank.contains_rank: [uid] .\n",
    "\n",
    "author.itis_id: int .\n",
    "author.name: string .\n",
    "author.name_short: string .\n",
    "\n",
    "tu.tsn: int @index(int) .\n",
    "tu.complete_name: string @index(fulltext) .\n",
    "tu.author: uid @reverse .\n",
    "tu.parent_of: [uid] @reverse .\n",
    "\n",
    "named.has_name: [uid] @reverse .\n",
    "\n",
    "synonym.for: uid @reverse .\n",
    "\"\"\"\n",
    "op = pydgraph.Operation(schema=schema, run_in_background=False)\n",
    "client.alter(op)\n",
    "\n",
    "for row_id, row in df_taxon_unit_types.iterrows():\n",
    "    schema = f\"\"\"\n",
    "    type Rank{row.rank_name} {{\n",
    "        rank.name\n",
    "    }}\n",
    "    \"\"\"\n",
    "    op = pydgraph.Operation(schema=schema, run_in_background=False)\n",
    "    client.alter(op)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "weighted-walker",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "58805ea6109b4b648c35c014ad529d36",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/45234 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "author_uids = {}\n",
    "for row_id, row in tqdm(df_taxon_authors.iterrows(), total=len(df_taxon_authors)):\n",
    "    txn = client.txn(read_only=False)\n",
    "    try:   \n",
    "        rank = {\n",
    "            'uid': f'_:{row.taxon_author}',\n",
    "            'author.name': row.taxon_author,\n",
    "            'author.name_short': row.short_author,\n",
    "            'author.itis_id': row.taxon_author_id,\n",
    "            'dgraph.type': [\n",
    "                'TaxonAuthor',\n",
    "                'Named'\n",
    "            ],\n",
    "            'named.has_name': {\n",
    "                'name.name': row.taxon_author,\n",
    "                'dgraph.type': [\n",
    "                    'AuthorName',\n",
    "                    'Name'\n",
    "                ]\n",
    "            }\n",
    "        }\n",
    "        uid = txn.mutate(set_obj=rank).uids[row.taxon_author]\n",
    "        author_uids[row.taxon_author_id] = uid\n",
    "        txn.commit()\n",
    "    finally:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "relevant-smart",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45234"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(author_uids)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "false-settlement",
   "metadata": {},
   "source": [
    "If we ensure values are sorted by rank ID, the higher categories will always exist when we want to create a link."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "classical-trick",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_taxonomic_units = df_taxonomic_units.sort_values(by='rank_id', ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "limiting-harvey",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ad0642b35e084d49bf9a39eefdcc780f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/165887 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "unit_uids = {}\n",
    "for row_id, row in tqdm(df_taxonomic_units.iterrows(), total=len(df_taxonomic_units)):\n",
    "\n",
    "    rank = df_taxon_unit_types[df_taxon_unit_types.rank_id == row.rank_id].iloc[0].rank_name\n",
    "    \n",
    "    type_ = ['TaxonomicUnit', f'Rank{rank}']\n",
    "    if row.name_usage == 'accepted':\n",
    "        type_.append('AcceptedName')\n",
    "    if row.unaccept_reason == 'synonym':\n",
    "        type_.append('Synonym')\n",
    "\n",
    "    txn = client.txn(read_only=False)\n",
    "    try:\n",
    "        unit = {\n",
    "            'uid': f'_:{row.complete_name}',\n",
    "            'tu.tsn': row.tsn,\n",
    "            'tu.complete_name': row.complete_name,\n",
    "            \n",
    "            # TODO: Add synonym links\n",
    "            # TODO: Add vernacular names\n",
    "            \n",
    "            # TODO: Add rank group\n",
    "            'rank.itis_rank_id': row.rank_id,\n",
    "\n",
    "            'dgraph.type': type_\n",
    "        }\n",
    "        \n",
    "        author_id = row.taxon_author_id if row.taxon_author_id > 0 else row.hybrid_author_id\n",
    "        if author_id in author_uids:\n",
    "            unit['tu.author'] = {\n",
    "                'uid': author_uids[author_id]\n",
    "            }\n",
    "        \n",
    "        uid = txn.mutate(set_obj=unit).uids[row.complete_name]\n",
    "        unit_uids[row.tsn] = uid\n",
    "        txn.commit()\n",
    "    finally:\n",
    "        txn.discard()\n",
    "        \n",
    "    txn = client.txn(read_only=False)\n",
    "    try:\n",
    "        if row.parent_tsn > 0:\n",
    "            parent_child = {\n",
    "                'uid': unit_uids[row.parent_tsn],\n",
    "                'tu.parent_of': {\n",
    "                    'uid': uid\n",
    "                }\n",
    "            }\n",
    "            txn.mutate(set_obj=parent_child)\n",
    "\n",
    "        txn.commit()\n",
    "    finally:\n",
    "        txn.discard()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 353,
   "id": "psychological-potato",
   "metadata": {},
   "outputs": [],
   "source": [
    "rank_uids = {}\n",
    "try:   \n",
    "    for row_id, row in tqdm(df_taxon_unit_types.iterrows(), total=len(df_taxon_unit_types)):\n",
    "        txn = client.txn(read_only=False)\n",
    "        rank = {\n",
    "            'uid': f'_:{row.rank_name}',\n",
    "            'rank.name': row.rank_name,\n",
    "            'rank.name@en': row.rank_name,\n",
    "            'rank.itis_rank_id': row.rank_id,\n",
    "            'rank.itis_rank_id_group': row.rank_group,\n",
    "            'dgraph.type': [\n",
    "                'Rank',\n",
    "                f'Rank{row.rank_name}'\n",
    "            ]\n",
    "        }\n",
    "        uid = txn.mutate(set_obj=rank).uids[row.rank_name]\n",
    "        rank_uids[row.rank_name] = uid\n",
    "        txn.commit()\n",
    "finally:\n",
    "    #txn.discard()\n",
    "    pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 340,
   "id": "permanent-leader",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Kingdom': '0x28a9',\n",
       " 'Subkingdom': '0x28aa',\n",
       " 'Infrakingdom': '0x28ab',\n",
       " 'Superdivision': '0x28ac',\n",
       " 'Division': '0x28ad',\n",
       " 'Subdivision': '0x28ae',\n",
       " 'Infradivision': '0x28af',\n",
       " 'Superclass': '0x28b0',\n",
       " 'Class': '0x28b1',\n",
       " 'Subclass': '0x28b2',\n",
       " 'Infraclass': '0x28b3',\n",
       " 'Superorder': '0x28b4',\n",
       " 'Order': '0x28b5',\n",
       " 'Suborder': '0x28b6',\n",
       " 'Family': '0x28b7',\n",
       " 'Subfamily': '0x28b8',\n",
       " 'Tribe': '0x28b9',\n",
       " 'Subtribe': '0x28ba',\n",
       " 'Genus': '0x28bb',\n",
       " 'Subgenus': '0x28bc',\n",
       " 'Section': '0x28bd',\n",
       " 'Subsection': '0x28be',\n",
       " 'Species': '0x28bf',\n",
       " 'Subspecies': '0x28c0',\n",
       " 'Variety': '0x28c1',\n",
       " 'Subvariety': '0x28c2',\n",
       " 'Form': '0x28c3',\n",
       " 'Subform': '0x28c4'}"
      ]
     },
     "execution_count": 340,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rank_uids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 354,
   "id": "dried-bhutan",
   "metadata": {},
   "outputs": [],
   "source": [
    "txn = client.txn(read_only=False)\n",
    "try:   \n",
    "    for row_id, row in df_taxon_unit_types.iterrows():\n",
    "        if row.dir_parent_rank_id == row.rank_id:\n",
    "            continue\n",
    "    \n",
    "        dir_parent_row = df_taxon_unit_types[df_taxon_unit_types.rank_id == row.dir_parent_rank_id].iloc[0]\n",
    "        \n",
    "        rank = {\n",
    "            'uid': rank_uids[dir_parent_row.rank_name],\n",
    "            'rank.contains_rank': {\n",
    "                'uid': rank_uids[row.rank_name]\n",
    "            }\n",
    "        }\n",
    "        txn.mutate(set_obj=rank)\n",
    "    txn.commit()\n",
    "finally:\n",
    "    txn.discard()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "suspended-grave",
   "metadata": {},
   "source": [
    "Example query at this point:\n",
    "\n",
    "```dgraph\n",
    "{\n",
    "  q(func: type(RankKingdom)) @recurse {\n",
    "    rank.name\n",
    "    rank.contains_rank\n",
    "  }\n",
    "}\n",
    "```"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:plants] *",
   "language": "python",
   "name": "conda-env-plants-py"
  },
  "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}