Process Work Plan Species to BIS.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook works over the Excel spreadsheet of FWS ESA Work Plan Species to put the data into our working Biogeographic Information System database (currently a MongoDB database). That turns the records into a form that we can process through the Taxa Information Registry to assemble attributes for further analysis and reporting. The spreadseet in Excel format was put together by USGS Ecosystems personnel. FWS has this list on a [web site](https://www.fws.gov/endangered/what-we-do/listing-workplan.html) and in a [PDF document](https://www.fws.gov/endangered/esa-library/pdf/Listing%207-Year%20Workplan%20Sept%202016.pdf) with some additional information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from bis2 import dd\n",
    "from IPython.display import display\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A couple of new functions to move into the BIS somewhere."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def packageESASpeciesRow(row):\n",
    "    submittedData = {}\n",
    "    submittedData[\"Scientific Name\"] = row[\"Scientific Name (Revised List)\"]\n",
    "    submittedData[\"Species Record Reference\"] = row[\"ScientificNameLink\"] \n",
    "    submittedData[\"Common Name\"] = row[\"Species Name (Common)\"]\n",
    "    submittedData[\"Grouping\"] = row[\"Grouping\"]\n",
    "    submittedData[\"Lead FWS Region\"] = row[\"Lead FWS Regional Office\"]\n",
    "    submittedData[\"Species Range\"] = row[\"Species Range\"]\n",
    "    return submittedData\n",
    "\n",
    "def lookupState(stateAbbr):\n",
    "    import us\n",
    "    try:\n",
    "        return {\"name\":us.states.mapping('abbr', 'name')[stateAbbr],\"fips\":us.states.mapping('abbr', 'fips')[stateAbbr]}\n",
    "    except:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read the spreadsheet from the Excel file into a Pandas dataframe. I extracted out the ECOS link from the hyperlink on scientific name into its own attribute but kept the spreadsheet intact otherwise."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Grouping</th>\n",
       "      <th>Species Name (Common)</th>\n",
       "      <th>Scientific Name (Revised List)</th>\n",
       "      <th>Lead FWS Regional Office</th>\n",
       "      <th>Species Range</th>\n",
       "      <th>ScientificNameLink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>streamside salamander</td>\n",
       "      <td>Ambystoma barbouri</td>\n",
       "      <td>R4</td>\n",
       "      <td>AL, KY, OH, TN, WV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/9776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Boreal toad (Eastern population)</td>\n",
       "      <td>Anaxyrus boreas boreas</td>\n",
       "      <td>R6</td>\n",
       "      <td>CO, ID, NM, NV, UT, WY</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/1114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Inyo Mountains slender salamander</td>\n",
       "      <td>Batrachoseps campi</td>\n",
       "      <td>R8</td>\n",
       "      <td>CA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/2095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>lesser slender salamander</td>\n",
       "      <td>Batrachoseps minor</td>\n",
       "      <td>R8</td>\n",
       "      <td>CA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/9277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>relictual slender salamander</td>\n",
       "      <td>Batrachoseps relictus</td>\n",
       "      <td>R8</td>\n",
       "      <td>CA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/7408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Kern Plateau salamander</td>\n",
       "      <td>Batrachoseps robustus</td>\n",
       "      <td>R8</td>\n",
       "      <td>CA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/9274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Kern Canyon slender salamander</td>\n",
       "      <td>Batrachoseps simatus</td>\n",
       "      <td>R8</td>\n",
       "      <td>CA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/5736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Oregon slender salamander</td>\n",
       "      <td>Batrachoseps wrighti</td>\n",
       "      <td>R1</td>\n",
       "      <td>OR, WA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Arizona toad</td>\n",
       "      <td>Bufo microscaphus microscaphus</td>\n",
       "      <td>R2</td>\n",
       "      <td>AZ, CA, NM, NV, UT</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/2077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>hellbender</td>\n",
       "      <td>Cryptobranchus alleganiensis</td>\n",
       "      <td>R3</td>\n",
       "      <td>AL, AR, GA, IL, IN, KY, MD, MO, MS, NC, NY, OH...</td>\n",
       "      <td>https://ecos.fws.gov/ecp0/profile/speciesProfi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Cascade Caverns salamander</td>\n",
       "      <td>Eurycea latitans</td>\n",
       "      <td>R2</td>\n",
       "      <td>TX</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/4624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Texas salamander</td>\n",
       "      <td>Eurycea nana</td>\n",
       "      <td>R2</td>\n",
       "      <td>TX</td>\n",
       "      <td>https://ecos.fws.gov/ecp0/profile/speciesProfi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Blanco blind salamander</td>\n",
       "      <td>Eurycea robusta</td>\n",
       "      <td>R2</td>\n",
       "      <td>TX</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Comal Springs salamander</td>\n",
       "      <td>Eurycea sp.</td>\n",
       "      <td>R2</td>\n",
       "      <td>TX</td>\n",
       "      <td>https://ecos.fws.gov/ecp0/profile/speciesProfi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Comal blind salamander</td>\n",
       "      <td>Eurycea tridentifera</td>\n",
       "      <td>R2</td>\n",
       "      <td>TX</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/2890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Oklahoma salamander</td>\n",
       "      <td>Eurycea tynerensis</td>\n",
       "      <td>R4</td>\n",
       "      <td>OK, MO, AR</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/4096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Berry Cave Salamander</td>\n",
       "      <td>Gyrinophilus gulolineatus</td>\n",
       "      <td>R4</td>\n",
       "      <td>TN</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/4043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Tennessee Cave salamander</td>\n",
       "      <td>Gyrinophilus palleucus</td>\n",
       "      <td>R4</td>\n",
       "      <td>AL, GA, TN</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/5131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>West Virginia spring salamander</td>\n",
       "      <td>Gyrinophilus subterraneus</td>\n",
       "      <td>R5</td>\n",
       "      <td>WV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/1835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Georgia blind salamander</td>\n",
       "      <td>Haideotriton wallacei</td>\n",
       "      <td>R4</td>\n",
       "      <td>FL, GA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/8465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>limestone salamander</td>\n",
       "      <td>Hydromantes brunus</td>\n",
       "      <td>R8</td>\n",
       "      <td>CA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/2773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Shasta salamander</td>\n",
       "      <td>Hydromantes shastae</td>\n",
       "      <td>R8</td>\n",
       "      <td>CA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/6607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Neuse River waterdog</td>\n",
       "      <td>Necturus lewisi</td>\n",
       "      <td>R4</td>\n",
       "      <td>NC</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/6772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>striped newt</td>\n",
       "      <td>Notophthalmus perstriatus</td>\n",
       "      <td>R4</td>\n",
       "      <td>FL, GA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/5856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Peaks of Otter salamander</td>\n",
       "      <td>Plethodon hubrichti</td>\n",
       "      <td>R5</td>\n",
       "      <td>VA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/3529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Illinois chorus frog</td>\n",
       "      <td>Pseudacris illinoensis</td>\n",
       "      <td>R3</td>\n",
       "      <td>AR, IL, MO</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>foothill yellow-legged frog</td>\n",
       "      <td>Rana boylii</td>\n",
       "      <td>R8</td>\n",
       "      <td>CA, OR</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/5133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Cascades frog</td>\n",
       "      <td>Rana cascadae</td>\n",
       "      <td>R1</td>\n",
       "      <td>OR, WA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/7827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Cascade torrent salamander</td>\n",
       "      <td>Rhyacotriton cascadae</td>\n",
       "      <td>R1</td>\n",
       "      <td>OR, WA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/1375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Amphibians</td>\n",
       "      <td>Columbia torrent salamander</td>\n",
       "      <td>Rhyacotriton kezeri</td>\n",
       "      <td>R1</td>\n",
       "      <td>OR, WA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/3376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>333</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Butterfield pyrg</td>\n",
       "      <td>Pyrgulopsis lata</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/8987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>334</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Hardy pyrg</td>\n",
       "      <td>Pyrgulopsis marcida</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/8989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>335</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Pahranagat pebblesnail</td>\n",
       "      <td>Pyrgulopsis merriami</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/4778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336</th>\n",
       "      <td>Snails</td>\n",
       "      <td>distal-gland springsnail</td>\n",
       "      <td>Pyrgulopsis nanus</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/5868</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>337</th>\n",
       "      <td>Snails</td>\n",
       "      <td>neritiform Steptoe Ranch pyrg</td>\n",
       "      <td>Pyrgulopsis neritella</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/8991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>338</th>\n",
       "      <td>Snails</td>\n",
       "      <td>sub-globose Steptoe Ranch pyrg</td>\n",
       "      <td>Pyrgulopsis orbiculata</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/8992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>339</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Ozark pyrg</td>\n",
       "      <td>Pyrgulopsis ozarkensis (Marstonia ozarkensis)</td>\n",
       "      <td>R4</td>\n",
       "      <td>MO, AR</td>\n",
       "      <td>https://ecos.fws.gov/ecp0/profile/speciesProfi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340</th>\n",
       "      <td>Snails</td>\n",
       "      <td>bifid duct pyrg</td>\n",
       "      <td>Pyrgulopsis peculiaris</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV, UT</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/8994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>341</th>\n",
       "      <td>Snails</td>\n",
       "      <td>median-gland Nevada pyrg</td>\n",
       "      <td>Pyrgulopsis pisteri</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/2997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342</th>\n",
       "      <td>Snails</td>\n",
       "      <td>flat-topped Steptoe pyrg</td>\n",
       "      <td>Pyrgulopsis planulata</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/8995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343</th>\n",
       "      <td>Snails</td>\n",
       "      <td>White River Valley pyrg</td>\n",
       "      <td>Pyrgulopsis sathos</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/8996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>344</th>\n",
       "      <td>Snails</td>\n",
       "      <td>sub-globose snake pyrg</td>\n",
       "      <td>Pyrgulopsis saxatilis</td>\n",
       "      <td>R6</td>\n",
       "      <td>UT</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/8997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>345</th>\n",
       "      <td>Snails</td>\n",
       "      <td>northern Steptoe pyrg</td>\n",
       "      <td>Pyrgulopsis serrata</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/8998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>346</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Sterile Basin pyrg</td>\n",
       "      <td>Pyrgulopsis sterilis</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/8999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>347</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Lake Valley pyrg</td>\n",
       "      <td>Pyrgulopsis sublata</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/9000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>348</th>\n",
       "      <td>Snails</td>\n",
       "      <td>southern Steptoe pyrg</td>\n",
       "      <td>Pyrgulopsis sulcata</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/9001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>349</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Southeast Nevada pyrg</td>\n",
       "      <td>Pyrgulopsis turbatrix</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/9002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>350</th>\n",
       "      <td>Snails</td>\n",
       "      <td>San Xavier talussnail</td>\n",
       "      <td>Sonorella eremita</td>\n",
       "      <td>R2</td>\n",
       "      <td>AZ</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/1015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>351</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Pinaleno talussnail</td>\n",
       "      <td>Sonorella grahamensis</td>\n",
       "      <td>R2</td>\n",
       "      <td>AZ</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/7840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>352</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Wet Canyon talussnail</td>\n",
       "      <td>Sonorella macrophallus</td>\n",
       "      <td>R2</td>\n",
       "      <td>AZ</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/4377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>353</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Shasta chaparral</td>\n",
       "      <td>Trilobopsis roperi</td>\n",
       "      <td>R8</td>\n",
       "      <td>CA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/8849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>354</th>\n",
       "      <td>Snails</td>\n",
       "      <td>sportinggoods tryonia</td>\n",
       "      <td>Tryonia angulata</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/6645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355</th>\n",
       "      <td>Snails</td>\n",
       "      <td>grated tryonia</td>\n",
       "      <td>Tryonia clathrata</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Point of Rocks tryonia</td>\n",
       "      <td>Tryonia elata</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/7837</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>Snails</td>\n",
       "      <td>minute tryonia</td>\n",
       "      <td>Tryonia ericae</td>\n",
       "      <td>R8</td>\n",
       "      <td>NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/2556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Quitobaquito tryonia</td>\n",
       "      <td>Tryonia quitobaquitae</td>\n",
       "      <td>R2</td>\n",
       "      <td>AZ</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/4521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Amargosa tryonia</td>\n",
       "      <td>Tryonia variegata</td>\n",
       "      <td>R8</td>\n",
       "      <td>CA, NV</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/6906</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Hoko vertigo</td>\n",
       "      <td>Vertigo sp.</td>\n",
       "      <td>R1</td>\n",
       "      <td>WA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/8761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Big Bar hesperian</td>\n",
       "      <td>Vespericola pressleyi</td>\n",
       "      <td>R8</td>\n",
       "      <td>CA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/8850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>Snails</td>\n",
       "      <td>Shasta hesperian</td>\n",
       "      <td>Vespericola shasta</td>\n",
       "      <td>R8</td>\n",
       "      <td>CA</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/8896</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>363 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Grouping              Species Name (Common)  \\\n",
       "0    Amphibians              streamside salamander   \n",
       "1    Amphibians   Boreal toad (Eastern population)   \n",
       "2    Amphibians  Inyo Mountains slender salamander   \n",
       "3    Amphibians          lesser slender salamander   \n",
       "4    Amphibians       relictual slender salamander   \n",
       "5    Amphibians            Kern Plateau salamander   \n",
       "6    Amphibians     Kern Canyon slender salamander   \n",
       "7    Amphibians          Oregon slender salamander   \n",
       "8    Amphibians                       Arizona toad   \n",
       "9    Amphibians                         hellbender   \n",
       "10   Amphibians         Cascade Caverns salamander   \n",
       "11   Amphibians                   Texas salamander   \n",
       "12   Amphibians            Blanco blind salamander   \n",
       "13   Amphibians           Comal Springs salamander   \n",
       "14   Amphibians             Comal blind salamander   \n",
       "15   Amphibians                Oklahoma salamander   \n",
       "16   Amphibians              Berry Cave Salamander   \n",
       "17   Amphibians          Tennessee Cave salamander   \n",
       "18   Amphibians    West Virginia spring salamander   \n",
       "19   Amphibians           Georgia blind salamander   \n",
       "20   Amphibians               limestone salamander   \n",
       "21   Amphibians                  Shasta salamander   \n",
       "22   Amphibians               Neuse River waterdog   \n",
       "23   Amphibians                       striped newt   \n",
       "24   Amphibians          Peaks of Otter salamander   \n",
       "25   Amphibians               Illinois chorus frog   \n",
       "26   Amphibians        foothill yellow-legged frog   \n",
       "27   Amphibians                      Cascades frog   \n",
       "28   Amphibians         Cascade torrent salamander   \n",
       "29   Amphibians        Columbia torrent salamander   \n",
       "..          ...                                ...   \n",
       "333      Snails                   Butterfield pyrg   \n",
       "334      Snails                         Hardy pyrg   \n",
       "335      Snails             Pahranagat pebblesnail   \n",
       "336      Snails           distal-gland springsnail   \n",
       "337      Snails      neritiform Steptoe Ranch pyrg   \n",
       "338      Snails     sub-globose Steptoe Ranch pyrg   \n",
       "339      Snails                         Ozark pyrg   \n",
       "340      Snails                    bifid duct pyrg   \n",
       "341      Snails           median-gland Nevada pyrg   \n",
       "342      Snails           flat-topped Steptoe pyrg   \n",
       "343      Snails            White River Valley pyrg   \n",
       "344      Snails             sub-globose snake pyrg   \n",
       "345      Snails              northern Steptoe pyrg   \n",
       "346      Snails                 Sterile Basin pyrg   \n",
       "347      Snails                   Lake Valley pyrg   \n",
       "348      Snails              southern Steptoe pyrg   \n",
       "349      Snails              Southeast Nevada pyrg   \n",
       "350      Snails              San Xavier talussnail   \n",
       "351      Snails                Pinaleno talussnail   \n",
       "352      Snails              Wet Canyon talussnail   \n",
       "353      Snails                   Shasta chaparral   \n",
       "354      Snails              sportinggoods tryonia   \n",
       "355      Snails                     grated tryonia   \n",
       "356      Snails             Point of Rocks tryonia   \n",
       "357      Snails                     minute tryonia   \n",
       "358      Snails               Quitobaquito tryonia   \n",
       "359      Snails                   Amargosa tryonia   \n",
       "360      Snails                       Hoko vertigo   \n",
       "361      Snails                  Big Bar hesperian   \n",
       "362      Snails                   Shasta hesperian   \n",
       "\n",
       "                    Scientific Name (Revised List) Lead FWS Regional Office  \\\n",
       "0                               Ambystoma barbouri                       R4   \n",
       "1                           Anaxyrus boreas boreas                       R6   \n",
       "2                               Batrachoseps campi                       R8   \n",
       "3                               Batrachoseps minor                       R8   \n",
       "4                            Batrachoseps relictus                       R8   \n",
       "5                            Batrachoseps robustus                       R8   \n",
       "6                             Batrachoseps simatus                       R8   \n",
       "7                             Batrachoseps wrighti                       R1   \n",
       "8                   Bufo microscaphus microscaphus                       R2   \n",
       "9                     Cryptobranchus alleganiensis                       R3   \n",
       "10                                Eurycea latitans                       R2   \n",
       "11                                    Eurycea nana                       R2   \n",
       "12                                 Eurycea robusta                       R2   \n",
       "13                                     Eurycea sp.                       R2   \n",
       "14                            Eurycea tridentifera                       R2   \n",
       "15                              Eurycea tynerensis                       R4   \n",
       "16                       Gyrinophilus gulolineatus                       R4   \n",
       "17                          Gyrinophilus palleucus                       R4   \n",
       "18                       Gyrinophilus subterraneus                       R5   \n",
       "19                           Haideotriton wallacei                       R4   \n",
       "20                              Hydromantes brunus                       R8   \n",
       "21                             Hydromantes shastae                       R8   \n",
       "22                                 Necturus lewisi                       R4   \n",
       "23                       Notophthalmus perstriatus                       R4   \n",
       "24                             Plethodon hubrichti                       R5   \n",
       "25                          Pseudacris illinoensis                       R3   \n",
       "26                                     Rana boylii                       R8   \n",
       "27                                   Rana cascadae                       R1   \n",
       "28                           Rhyacotriton cascadae                       R1   \n",
       "29                             Rhyacotriton kezeri                       R1   \n",
       "..                                             ...                      ...   \n",
       "333                               Pyrgulopsis lata                       R8   \n",
       "334                            Pyrgulopsis marcida                       R8   \n",
       "335                           Pyrgulopsis merriami                       R8   \n",
       "336                              Pyrgulopsis nanus                       R8   \n",
       "337                          Pyrgulopsis neritella                       R8   \n",
       "338                         Pyrgulopsis orbiculata                       R8   \n",
       "339  Pyrgulopsis ozarkensis (Marstonia ozarkensis)                       R4   \n",
       "340                         Pyrgulopsis peculiaris                       R8   \n",
       "341                            Pyrgulopsis pisteri                       R8   \n",
       "342                          Pyrgulopsis planulata                       R8   \n",
       "343                             Pyrgulopsis sathos                       R8   \n",
       "344                          Pyrgulopsis saxatilis                       R6   \n",
       "345                            Pyrgulopsis serrata                       R8   \n",
       "346                           Pyrgulopsis sterilis                       R8   \n",
       "347                            Pyrgulopsis sublata                       R8   \n",
       "348                            Pyrgulopsis sulcata                       R8   \n",
       "349                          Pyrgulopsis turbatrix                       R8   \n",
       "350                              Sonorella eremita                       R2   \n",
       "351                          Sonorella grahamensis                       R2   \n",
       "352                         Sonorella macrophallus                       R2   \n",
       "353                             Trilobopsis roperi                       R8   \n",
       "354                               Tryonia angulata                       R8   \n",
       "355                              Tryonia clathrata                       R8   \n",
       "356                                  Tryonia elata                       R8   \n",
       "357                                 Tryonia ericae                       R8   \n",
       "358                          Tryonia quitobaquitae                       R2   \n",
       "359                              Tryonia variegata                       R8   \n",
       "360                                    Vertigo sp.                       R1   \n",
       "361                          Vespericola pressleyi                       R8   \n",
       "362                             Vespericola shasta                       R8   \n",
       "\n",
       "                                         Species Range  \\\n",
       "0                                   AL, KY, OH, TN, WV   \n",
       "1                               CO, ID, NM, NV, UT, WY   \n",
       "2                                                   CA   \n",
       "3                                                   CA   \n",
       "4                                                   CA   \n",
       "5                                                   CA   \n",
       "6                                                   CA   \n",
       "7                                               OR, WA   \n",
       "8                                   AZ, CA, NM, NV, UT   \n",
       "9    AL, AR, GA, IL, IN, KY, MD, MO, MS, NC, NY, OH...   \n",
       "10                                                  TX   \n",
       "11                                                  TX   \n",
       "12                                                  TX   \n",
       "13                                                  TX   \n",
       "14                                                  TX   \n",
       "15                                          OK, MO, AR   \n",
       "16                                                  TN   \n",
       "17                                          AL, GA, TN   \n",
       "18                                                  WV   \n",
       "19                                              FL, GA   \n",
       "20                                                  CA   \n",
       "21                                                  CA   \n",
       "22                                                  NC   \n",
       "23                                              FL, GA   \n",
       "24                                                  VA   \n",
       "25                                          AR, IL, MO   \n",
       "26                                              CA, OR   \n",
       "27                                              OR, WA   \n",
       "28                                              OR, WA   \n",
       "29                                              OR, WA   \n",
       "..                                                 ...   \n",
       "333                                                 NV   \n",
       "334                                                 NV   \n",
       "335                                                 NV   \n",
       "336                                                 NV   \n",
       "337                                                 NV   \n",
       "338                                                 NV   \n",
       "339                                             MO, AR   \n",
       "340                                             NV, UT   \n",
       "341                                                 NV   \n",
       "342                                                 NV   \n",
       "343                                                 NV   \n",
       "344                                                 UT   \n",
       "345                                                 NV   \n",
       "346                                                 NV   \n",
       "347                                                 NV   \n",
       "348                                                 NV   \n",
       "349                                                 NV   \n",
       "350                                                 AZ   \n",
       "351                                                 AZ   \n",
       "352                                                 AZ   \n",
       "353                                                 CA   \n",
       "354                                                 NV   \n",
       "355                                                 NV   \n",
       "356                                                 NV   \n",
       "357                                                 NV   \n",
       "358                                                 AZ   \n",
       "359                                             CA, NV   \n",
       "360                                                 WA   \n",
       "361                                                 CA   \n",
       "362                                                 CA   \n",
       "\n",
       "                                    ScientificNameLink  \n",
       "0                https://ecos.fws.gov/ecp/species/9776  \n",
       "1                https://ecos.fws.gov/ecp/species/1114  \n",
       "2                https://ecos.fws.gov/ecp/species/2095  \n",
       "3                https://ecos.fws.gov/ecp/species/9277  \n",
       "4                https://ecos.fws.gov/ecp/species/7408  \n",
       "5                https://ecos.fws.gov/ecp/species/9274  \n",
       "6                https://ecos.fws.gov/ecp/species/5736  \n",
       "7                 https://ecos.fws.gov/ecp/species/913  \n",
       "8                https://ecos.fws.gov/ecp/species/2077  \n",
       "9    https://ecos.fws.gov/ecp0/profile/speciesProfi...  \n",
       "10               https://ecos.fws.gov/ecp/species/4624  \n",
       "11   https://ecos.fws.gov/ecp0/profile/speciesProfi...  \n",
       "12                 https://ecos.fws.gov/ecp/species/29  \n",
       "13   https://ecos.fws.gov/ecp0/profile/speciesProfi...  \n",
       "14               https://ecos.fws.gov/ecp/species/2890  \n",
       "15               https://ecos.fws.gov/ecp/species/4096  \n",
       "16               https://ecos.fws.gov/ecp/species/4043  \n",
       "17               https://ecos.fws.gov/ecp/species/5131  \n",
       "18               https://ecos.fws.gov/ecp/species/1835  \n",
       "19               https://ecos.fws.gov/ecp/species/8465  \n",
       "20               https://ecos.fws.gov/ecp/species/2773  \n",
       "21               https://ecos.fws.gov/ecp/species/6607  \n",
       "22               https://ecos.fws.gov/ecp/species/6772  \n",
       "23               https://ecos.fws.gov/ecp/species/5856  \n",
       "24               https://ecos.fws.gov/ecp/species/3529  \n",
       "25                https://ecos.fws.gov/ecp/species/455  \n",
       "26               https://ecos.fws.gov/ecp/species/5133  \n",
       "27               https://ecos.fws.gov/ecp/species/7827  \n",
       "28               https://ecos.fws.gov/ecp/species/1375  \n",
       "29               https://ecos.fws.gov/ecp/species/3376  \n",
       "..                                                 ...  \n",
       "333              https://ecos.fws.gov/ecp/species/8987  \n",
       "334              https://ecos.fws.gov/ecp/species/8989  \n",
       "335              https://ecos.fws.gov/ecp/species/4778  \n",
       "336              https://ecos.fws.gov/ecp/species/5868  \n",
       "337              https://ecos.fws.gov/ecp/species/8991  \n",
       "338              https://ecos.fws.gov/ecp/species/8992  \n",
       "339  https://ecos.fws.gov/ecp0/profile/speciesProfi...  \n",
       "340              https://ecos.fws.gov/ecp/species/8994  \n",
       "341              https://ecos.fws.gov/ecp/species/2997  \n",
       "342              https://ecos.fws.gov/ecp/species/8995  \n",
       "343              https://ecos.fws.gov/ecp/species/8996  \n",
       "344              https://ecos.fws.gov/ecp/species/8997  \n",
       "345              https://ecos.fws.gov/ecp/species/8998  \n",
       "346              https://ecos.fws.gov/ecp/species/8999  \n",
       "347              https://ecos.fws.gov/ecp/species/9000  \n",
       "348              https://ecos.fws.gov/ecp/species/9001  \n",
       "349              https://ecos.fws.gov/ecp/species/9002  \n",
       "350              https://ecos.fws.gov/ecp/species/1015  \n",
       "351              https://ecos.fws.gov/ecp/species/7840  \n",
       "352              https://ecos.fws.gov/ecp/species/4377  \n",
       "353              https://ecos.fws.gov/ecp/species/8849  \n",
       "354              https://ecos.fws.gov/ecp/species/6645  \n",
       "355               https://ecos.fws.gov/ecp/species/303  \n",
       "356              https://ecos.fws.gov/ecp/species/7837  \n",
       "357              https://ecos.fws.gov/ecp/species/2556  \n",
       "358              https://ecos.fws.gov/ecp/species/4521  \n",
       "359              https://ecos.fws.gov/ecp/species/6906  \n",
       "360              https://ecos.fws.gov/ecp/species/8761  \n",
       "361              https://ecos.fws.gov/ecp/species/8850  \n",
       "362              https://ecos.fws.gov/ecp/species/8896  \n",
       "\n",
       "[363 rows x 6 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = pd.read_excel(\"FWS ESA Work Plan Species list for CSS.xlsx\", sheet_name='Sheet1')\n",
    "display (df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get the collection we want to put these data into from the Data Distillery MongoDB instance."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bisDB = dd.getDB(\"bis\")\n",
    "esaWPSpecies = bisDB[\"FWS ESA Work Plan Species\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insert the original submitted data into the MongoDB collection as a data structure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertManyResult at 0x109974990>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fwsESASpeciesList = []\n",
    "\n",
    "for index,row in df.iterrows():\n",
    "    speciesRecord = {}\n",
    "    speciesRecord[\"Submitted Data\"] = packageESASpeciesRow(row)\n",
    "    speciesRecord[\"Processing Metadata\"] = {\"Date Created from Source\":datetime.utcnow().isoformat()}\n",
    "    fwsESASpeciesList.append(speciesRecord)\n",
    "\n",
    "esaWPSpecies.delete_many({})\n",
    "esaWPSpecies.insert_many(fwsESASpeciesList)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Parse out the range places (state abbreviations and other place names) into their own data structure for later processing and comparison."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "for record in esaWPSpecies.find():\n",
    "    fwsRange = {\"US States\":[],\"US State List\":[],\"Other Places\":[]}\n",
    "    for rangePlace in record[\"Submitted Data\"][\"Species Range\"].replace(\", \",\",\").split(\",\"):\n",
    "        rangeState = lookupState(rangePlace)\n",
    "        if rangeState is None:\n",
    "            fwsRange[\"Other Places\"].append({rangePlace:{}})\n",
    "        else:\n",
    "            fwsRange[\"US States\"].append({rangePlace:rangeState})\n",
    "            fwsRange[\"US State List\"].append(rangePlace)\n",
    "\n",
    "    if len(fwsRange[\"Other Places\"]) == 0:\n",
    "        del fwsRange[\"Other Places\"]\n",
    "\n",
    "    esaWPSpecies.update_one({\"_id\":record[\"_id\"]},{\"$set\":{\"FWS Range\":fwsRange}})\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show what the final data structure looks like at this point."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'FWS Range': {'US State List': ['AL', 'KY', 'OH', 'TN', 'WV'],\n",
       "  'US States': [{'AL': {'fips': '01', 'name': 'Alabama'}},\n",
       "   {'KY': {'fips': '21', 'name': 'Kentucky'}},\n",
       "   {'OH': {'fips': '39', 'name': 'Ohio'}},\n",
       "   {'TN': {'fips': '47', 'name': 'Tennessee'}},\n",
       "   {'WV': {'fips': '54', 'name': 'West Virginia'}}]},\n",
       " 'Processing Metadata': {'Date Created from Source': '2018-02-21T13:38:13.076667'},\n",
       " 'Submitted Data': {'Common Name': 'streamside salamander',\n",
       "  'Grouping': 'Amphibians',\n",
       "  'Lead FWS Region': 'R4',\n",
       "  'Scientific Name': 'Ambystoma barbouri',\n",
       "  'Species Range': 'AL, KY, OH, TN, WV',\n",
       "  'Species Record Reference': 'https://ecos.fws.gov/ecp/species/9776'},\n",
       " '_id': ObjectId('5a8d76450601ba07d55ba317')}"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(esaWPSpecies.find_one())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}