New ECOS REST service.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The FWS ECOS folks put out a new REST service that provides a whole lot more than the very old xquery service that we based some work on in the past. It should enable us to replace everything we are currently doing and add more functionality within the pysppin project. This notebook explores the dynamics of the new service.\n",
    "\n",
    "The [new service](https://ecos.fws.gov/ecp/report/ad-hoc-documentation?catalogId=species&reportId=species#document) is based on a tool called pullReports that seems to essentially just expose certain related tables from the relational database backend for the ECOS ESA system. It returns a data structure that is reasonable enough but that could be optimized for pulling immediately into things like dataframes for processing.\n",
    "\n",
    "The following codeblocks access the query service and reformat the data into an array of key value pairs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Simple example query for listed species\n",
    "listed_spp = requests.get(\"https://ecos.fws.gov/ecp/pullreports/catalog/species/report/species/export?filter=%2Fspecies%40status_category%20%3D%20%27Listed%27&format=json\").json()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'tablePath': '/species', 'displayName': 'Common Name', 'id': 'cn'},\n",
       " {'tablePath': '/species', 'displayName': 'Scientific Name', 'id': 'sn'},\n",
       " {'tablePath': '/species',\n",
       "  'displayName': 'ESA Listing Status',\n",
       "  'id': 'status'},\n",
       " {'tablePath': '/species', 'displayName': 'Entity Description', 'id': 'desc'},\n",
       " {'tablePath': '/species',\n",
       "  'displayName': 'ESA Listing Date',\n",
       "  'id': 'listing_date'}]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Column definition from meta section\n",
    "listed_spp[\"meta\"][\"columns\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2403"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Number of data records\n",
    "len(listed_spp[\"data\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[\"Abbott's booby\",\n",
       " {'value': 'Papasula (=Sula) abbotti',\n",
       "  'url': 'https://ecos.fws.gov/ecp/species/1470'},\n",
       " 'Endangered',\n",
       " 'Wherever found',\n",
       " '06-14-1976']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example record\n",
    "listed_spp[\"data\"][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This set of functions pulls together the column names as keys with the values from the data records to return a new array that can be readily pulled into various types of dataframes. This is pretty clunky at this point, and more experimentation is needed to run through all the various data that can be returned and deal with any other types of data structures. I'm not entirely sure why they decided to put the species scientific name together with an identifier/URL into the one \"column\" for sn, but I broke that out here into separate data fields in order to keep as simple a data structure as possible. I'm sure there are also better ways to do this than with a loop, I just didn't have time to figure that out right away. I added an option to use either the displayName or id from the column name metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def build_data_dict(record, column_meta, field_name_type=\"displayName\"):\n",
    "    new_record = dict()\n",
    "\n",
    "    for index, value in enumerate(record):\n",
    "        column_name = column_meta[index][field_name_type]\n",
    "        if type(value) == dict:\n",
    "            for k in value.keys():\n",
    "                new_record[f\"{column_name}_{k}\"] = value[k]\n",
    "        elif type(value) == str:\n",
    "            new_record[column_name] = value\n",
    "\n",
    "    return new_record\n",
    "\n",
    "def build_data_list(record_list, column_meta, field_name_type=\"displayName\"):\n",
    "    dataset = [build_data_dict(r, column_meta, field_name_type) for r in record_list]\n",
    "    return dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Build an array of key/value pair objects\n",
    "listed_spp_array = build_data_list(listed_spp[\"data\"], listed_spp[\"meta\"][\"columns\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Common Name': \"Abbott's booby\",\n",
       "  'Scientific Name_value': 'Papasula (=Sula) abbotti',\n",
       "  'Scientific Name_url': 'https://ecos.fws.gov/ecp/species/1470',\n",
       "  'ESA Listing Status': 'Endangered',\n",
       "  'Entity Description': 'Wherever found',\n",
       "  'ESA Listing Date': '06-14-1976'},\n",
       " {'Common Name': 'Aboriginal Prickly-apple',\n",
       "  'Scientific Name_value': 'Harrisia (=Cereus) aboriginum (=gracilis)',\n",
       "  'Scientific Name_url': 'https://ecos.fws.gov/ecp/species/2833',\n",
       "  'ESA Listing Status': 'Endangered',\n",
       "  'Entity Description': 'Wherever found',\n",
       "  'ESA Listing Date': '11-25-2013'},\n",
       " {'Common Name': 'Acklins ground iguana',\n",
       "  'Scientific Name_value': 'Cyclura rileyi nuchalis',\n",
       "  'Scientific Name_url': 'https://ecos.fws.gov/ecp/species/27',\n",
       "  'ESA Listing Status': 'Threatened',\n",
       "  'Entity Description': 'Wherever found',\n",
       "  'ESA Listing Date': '06-22-1983'},\n",
       " {'Common Name': 'Acuna Cactus',\n",
       "  'Scientific Name_value': 'Echinomastus erectocentrus var. acunensis',\n",
       "  'Scientific Name_url': 'https://ecos.fws.gov/ecp/species/5785',\n",
       "  'ESA Listing Status': 'Endangered',\n",
       "  'Entity Description': 'Wherever found',\n",
       "  'ESA Listing Date': '10-31-2013'},\n",
       " {'Common Name': 'Addax',\n",
       "  'Scientific Name_value': 'Addax nasomaculatus',\n",
       "  'Scientific Name_url': 'https://ecos.fws.gov/ecp/species/1486',\n",
       "  'ESA Listing Status': 'Endangered',\n",
       "  'Entity Description': 'Wherever found',\n",
       "  'ESA Listing Date': '09-02-2005'}]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show some of the data\n",
    "listed_spp_array[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Put the data into a Pandas dataframe\n",
    "df_listed_spp = pd.DataFrame(listed_spp_array)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Common Name</th>\n",
       "      <th>ESA Listing Date</th>\n",
       "      <th>ESA Listing Status</th>\n",
       "      <th>Entity Description</th>\n",
       "      <th>Scientific Name_url</th>\n",
       "      <th>Scientific Name_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Abbott's booby</td>\n",
       "      <td>06-14-1976</td>\n",
       "      <td>Endangered</td>\n",
       "      <td>Wherever found</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/1470</td>\n",
       "      <td>Papasula (=Sula) abbotti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Aboriginal Prickly-apple</td>\n",
       "      <td>11-25-2013</td>\n",
       "      <td>Endangered</td>\n",
       "      <td>Wherever found</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/2833</td>\n",
       "      <td>Harrisia (=Cereus) aboriginum (=gracilis)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Acklins ground iguana</td>\n",
       "      <td>06-22-1983</td>\n",
       "      <td>Threatened</td>\n",
       "      <td>Wherever found</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/27</td>\n",
       "      <td>Cyclura rileyi nuchalis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Acuna Cactus</td>\n",
       "      <td>10-31-2013</td>\n",
       "      <td>Endangered</td>\n",
       "      <td>Wherever found</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/5785</td>\n",
       "      <td>Echinomastus erectocentrus var. acunensis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Addax</td>\n",
       "      <td>09-02-2005</td>\n",
       "      <td>Endangered</td>\n",
       "      <td>Wherever found</td>\n",
       "      <td>https://ecos.fws.gov/ecp/species/1486</td>\n",
       "      <td>Addax nasomaculatus</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Common Name ESA Listing Date ESA Listing Status  \\\n",
       "0            Abbott's booby       06-14-1976         Endangered   \n",
       "1  Aboriginal Prickly-apple       11-25-2013         Endangered   \n",
       "2     Acklins ground iguana       06-22-1983         Threatened   \n",
       "3              Acuna Cactus       10-31-2013         Endangered   \n",
       "4                     Addax       09-02-2005         Endangered   \n",
       "\n",
       "  Entity Description                    Scientific Name_url  \\\n",
       "0     Wherever found  https://ecos.fws.gov/ecp/species/1470   \n",
       "1     Wherever found  https://ecos.fws.gov/ecp/species/2833   \n",
       "2     Wherever found    https://ecos.fws.gov/ecp/species/27   \n",
       "3     Wherever found  https://ecos.fws.gov/ecp/species/5785   \n",
       "4     Wherever found  https://ecos.fws.gov/ecp/species/1486   \n",
       "\n",
       "                       Scientific Name_value  \n",
       "0                   Papasula (=Sula) abbotti  \n",
       "1  Harrisia (=Cereus) aboriginum (=gracilis)  \n",
       "2                    Cyclura rileyi nuchalis  \n",
       "3  Echinomastus erectocentrus var. acunensis  \n",
       "4                        Addax nasomaculatus  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show what it looks like\n",
    "df_listed_spp.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Scientific Name_url</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ESA Listing Status</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Endangered</th>\n",
       "      <td>1850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Experimental Population, Non-Essential</th>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Similarity of Appearance (Threatened)</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Threatened</th>\n",
       "      <td>476</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        Scientific Name_url\n",
       "ESA Listing Status                                         \n",
       "Endangered                                             1850\n",
       "Experimental Population, Non-Essential                   65\n",
       "Similarity of Appearance (Threatened)                    12\n",
       "Threatened                                              476"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# List numbers of records by listing status values\n",
    "df_listed_spp[[\"ESA Listing Status\",\"Scientific Name_url\"]].groupby(\"ESA Listing Status\").count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Next Steps\n",
    "This could dramatically simplify what we did in the ecos module of the [pysppin](https://github.com/usgs-biolab/pysppin) project, likely doing away with the clunky way we had of scraping stuff from web pages we couldn't otherwise get and addding a whole lot more information to what we can pull back. The simple restructuring method here will likely work throughout, but we need to check through all the other types of data reponses when the related tables are added in to see if there are any other data structures that need to be handled. We should stick closely to how the pullReports service operates and set something up to accept the various query parameters as logical API routes and controls via the Python abstraction."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}