{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# DataPath Example 4\n",
    "\n",
    "This notebook covers somewhat more advanced examples for using `DataPath`s. It assumes that you understand \n",
    "the concepts presented in the previous example notebooks.\n",
    "\n",
    "You should also read the ERMrest documentation and the derivapy wiki. There are more advanced concepts in this notebook that are demonstrated but not fully (re)explained here, as the concepts are explained in other documentation.\n",
    "\n",
    "## Exampe Data Model\n",
    "The examples require that you understand a little bit about the example catalog data model, which in this case manages data for biological experiments.\n",
    "\n",
    "### Key tables\n",
    "- `'dataset'` : represents a unit of data usually for a study or set of experiments;\n",
    "- `'biosample'` : a biosample (describes biological details of a specimen);\n",
    "- `'replicate'` : a replicate (describes both bio- and technical-replicates);\n",
    "- `'experiment'` : a bioassay (any type of experiment or assay; e.g., imaging, RNA-seq, ChIP-seq, etc.).\n",
    "\n",
    "### Relationships\n",
    "- `dataset <- biosample`: A dataset may have one to many biosamples. I.e., there is a \n",
    "  foreign key reference from biosample to dataset.\n",
    "- `dataset <- experiment`: A dataset may have one to many experiments. I.e., there \n",
    "  is a foreign key reference from experiment to dataset.\n",
    "- `experiment <- replicate`: An experiment may have one to many replicates. I.e., there is a\n",
    "  foreign key reference from replicate to experiment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import deriva modules and pandas DataFrame (for use in examples only)\n",
    "from deriva.core import ErmrestCatalog, get_credential\n",
    "from pandas import DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect with the deriva catalog\n",
    "protocol = 'https'\n",
    "hostname = 'www.facebase.org'\n",
    "catalog_number = 1\n",
    "credential = None\n",
    "# If you need to authenticate, use Deriva Auth agent and get the credential\n",
    "# credential = get_credential(hostname)\n",
    "catalog = ErmrestCatalog(protocol, hostname, catalog_number, credential)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the path builder interface for this catalog\n",
    "pb = catalog.getPathBuilder()\n",
    "\n",
    "# Get some local variable handles to tables for convenience\n",
    "dataset = pb.isa.dataset\n",
    "experiment = pb.isa.experiment\n",
    "biosample = pb.isa.biosample\n",
    "replicate = pb.isa.replicate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Implicit DataPaths\n",
    "**Proceed with caution**\n",
    "\n",
    "For compactness, `Table` objects (and `TableAlias` objects) provide `DataPath`-like methods. E.g., `link(...)`, `filter(...)`, and `entities(...)`, which will implicitly create `DataPath`s rooted at the table and return the newly created path. These operations `return` the new `DataPath` rather than mutating the `Table` (or `TableAlias`) objects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1049"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "entities = dataset.filter(dataset.released == True).entities()\n",
    "len(entities)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DataPath-like methods\n",
    "The `DataPath`-like methods on `Table`s are essentially \"wrapper\" functions over the implicitly generated `DataPath` rooted at the `Table` instance. The wrappers include, `link(...)`, `filter(...)`, `entities(...)`, `attributes(...)`, `aggregates(...)`, and `groupby(...)`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Attribute Examples"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: selecting all columns of a table instance\n",
    "Passing a table (or table instance) object to the `attributes(...)` method will project all (i.e., `*`) of its attributes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "14390\n",
      "https://www.facebase.org/ermrest/catalog/1/attribute/D:=isa:dataset/experiment:=isa:experiment/replicate:=isa:replicate/D:*\n"
     ]
    }
   ],
   "source": [
    "path = dataset.alias('D').path\n",
    "path.link(experiment).link(replicate)\n",
    "results = path.attributes(path.D)\n",
    "print(len(results))\n",
    "print(results.uri)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is important to remember that the `attributes(...)` method returns a result set based on the entity type of the last elmenent of the path. In this example that means the number of results will be determined by the number of unique rows in the replicate table instance in the path created above, as the last link method used the replicate table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: selecting from multiple table instances\n",
    "More than one table instance may be selected in this manner and it can be mixed and matched with columns from other tables instances. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "14390\n",
      "https://www.facebase.org/ermrest/catalog/1/attribute/D:=isa:dataset/experiment:=isa:experiment/replicate:=isa:replicate/D:*,experiment:experiment_type,replicate:*\n"
     ]
    }
   ],
   "source": [
    "results = path.attributes(path.D,\n",
    "                          path.experiment.experiment_type,\n",
    "                          path.replicate)\n",
    "print(len(results))\n",
    "print(results.uri)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " If you want to base the results on a different entity, you can introduce a table instance alias into the end of the path, before calling the attributes function.  In this case, even though we are asking for the same attributes, we are getting the set of datasets, not the set of replicates.  Also, since we are including the attributes from dataset in our query, we know that we will not be seeing any duplicate rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "273\n",
      "https://www.facebase.org/ermrest/catalog/1/attribute/D:=isa:dataset/experiment:=isa:experiment/replicate:=isa:replicate/$D/D:*,experiment:experiment_type,replicate:*\n"
     ]
    }
   ],
   "source": [
    "results = path.D.attributes(path.D,\n",
    "                            path.experiment.experiment_type,\n",
    "                            path.replicate)\n",
    "print(len(results))\n",
    "print(results.uri)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filtering Examples"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: filter on `null` attribute\n",
    "To test for a `null` attribute value, do an equality comparison against the `None` identity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.facebase.org/ermrest/catalog/1/entity/dataset:=isa:dataset/experiment:=isa:experiment/molecule_type::null::\n",
      "2398\n"
     ]
    }
   ],
   "source": [
    "path = dataset.link(experiment).filter(experiment.molecule_type == None)\n",
    "print(path.uri)\n",
    "print(len(path.entities()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: advanced text filters\n",
    "Deriva supports advanced text filters for regular expressions (`regexp`), case-instansitive regexp (`ciregexp`), and text search (`ts`). You may have to review your text and full-text indexes in your ERMrest catalog before using these features."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.facebase.org/ermrest/catalog/1/entity/dataset:=isa:dataset/description::ciregexp::palate\n",
      "88\n"
     ]
    }
   ],
   "source": [
    "path = dataset.filter(dataset.description.ciregexp('palate'))\n",
    "print(path.uri)\n",
    "print(len(path.entities()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: negate a filter\n",
    "Use the \"inverse\" ('`~`') operator to negate a filter. Negation works against simple comparison filters as demonstrated above as well as on logical operators to be discussed next. You must wrap the comparison or logical operators in an extra parens to use the negate operation, e.g., \"`~ (...)`\". "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.facebase.org/ermrest/catalog/1/entity/dataset:=isa:dataset/!(description::ciregexp::palate)\n",
      "957\n"
     ]
    }
   ],
   "source": [
    "path = dataset.filter( ~ (dataset.description.ciregexp('palate')) )\n",
    "print(path.uri)\n",
    "print(len(path.entities()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: filters with logical operators\n",
    "This example shows how to combine two comparisons with a conjuncting (i.e., `and` operator). Because Python's logical-and (`and`) keyword cannot be overloaded, we instead overload the bitwise-and (`&`) operator. This approach has become customary among many similar data access libraries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.facebase.org/ermrest/catalog/1/entity/dataset:=isa:dataset/biosample:=isa:biosample/(species=NCBITAXON%3A10090)&(anatomy=UBERON%3A0002490)\n"
     ]
    }
   ],
   "source": [
    "path = dataset.link(biosample).filter(\n",
    "    ((biosample.species == 'NCBITAXON:10090') & (biosample.anatomy == 'UBERON:0002490')))\n",
    "\n",
    "print(path.uri)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RID</th>\n",
       "      <th>dataset</th>\n",
       "      <th>local_identifier</th>\n",
       "      <th>species</th>\n",
       "      <th>specimen</th>\n",
       "      <th>gene</th>\n",
       "      <th>genotype</th>\n",
       "      <th>strain</th>\n",
       "      <th>mutation</th>\n",
       "      <th>stage</th>\n",
       "      <th>...</th>\n",
       "      <th>litter</th>\n",
       "      <th>collection_date</th>\n",
       "      <th>_keywords</th>\n",
       "      <th>RCB</th>\n",
       "      <th>RMB</th>\n",
       "      <th>RCT</th>\n",
       "      <th>RMT</th>\n",
       "      <th>cell_source</th>\n",
       "      <th>cell_characterization</th>\n",
       "      <th>treatment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1-4TT8</td>\n",
       "      <td>1-4TSP</td>\n",
       "      <td>scWFE18_S197</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNR</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/8ae274db-d033-47eb-bd3...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2018-12-11T03:42:13.870864+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1-8DVG</td>\n",
       "      <td>1-4TSP</td>\n",
       "      <td>scWFE18_S616</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNR</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-06-19</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/8ae274db-d033-47eb-bd3...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2020-02-13T16:45:26.843631+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1-8DVM</td>\n",
       "      <td>1-4TSP</td>\n",
       "      <td>scWFE18_S631</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNR</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-06-18</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/8ae274db-d033-47eb-bd3...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2020-02-13T17:03:19.454718+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1-C3PR</td>\n",
       "      <td>1-4TSP</td>\n",
       "      <td>scTFE18_S979</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNR</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV0</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-12-11</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/8ae274db-d033-47eb-bd3...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2020-03-17T16:51:19.48232+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1-C3PT</td>\n",
       "      <td>1-4TSP</td>\n",
       "      <td>scAFE18_S1075</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNR</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GT6</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-12-24</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/8ae274db-d033-47eb-bd3...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2020-03-17T16:52:41.937568+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>3Q1E</td>\n",
       "      <td>VHE</td>\n",
       "      <td>W8IF1SM</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNG</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>4/15/14WL1-1</td>\n",
       "      <td>2014-04-15</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/b506963e-d274-11e5-99f...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2018-03-16T19:03:38.228586+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>3Q1J</td>\n",
       "      <td>VHE</td>\n",
       "      <td>W8IF2SM</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNG</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>4/15/14WL1-2</td>\n",
       "      <td>2014-04-15</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/b506963e-d274-11e5-99f...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2018-03-16T19:03:38.228586+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>3Q1P</td>\n",
       "      <td>VHE</td>\n",
       "      <td>W8IF3SM</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNG</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>4/14/14WL1-6</td>\n",
       "      <td>2014-04-14</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/b506963e-d274-11e5-99f...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2018-03-16T19:03:38.228586+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>3Q1T</td>\n",
       "      <td>VHE</td>\n",
       "      <td>W8IF4SM</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNG</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>4/15/14WL1-7</td>\n",
       "      <td>2014-04-15</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/b506963e-d274-11e5-99f...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2018-03-16T19:03:38.228586+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>3Q1Y</td>\n",
       "      <td>VHE</td>\n",
       "      <td>W8IF5SM</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNG</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>4/28/14WL1-1</td>\n",
       "      <td>2014-04-28</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/b506963e-d274-11e5-99f...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2018-03-16T19:03:38.228586+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       RID dataset local_identifier          species         specimen  gene   \n",
       "0   1-4TT8  1-4TSP     scWFE18_S197  NCBITAXON:10090  FACEBASE:1-4GNR  None  \\\n",
       "1   1-8DVG  1-4TSP     scWFE18_S616  NCBITAXON:10090  FACEBASE:1-4GNR  None   \n",
       "2   1-8DVM  1-4TSP     scWFE18_S631  NCBITAXON:10090  FACEBASE:1-4GNR  None   \n",
       "3   1-C3PR  1-4TSP     scTFE18_S979  NCBITAXON:10090  FACEBASE:1-4GNR  None   \n",
       "4   1-C3PT  1-4TSP    scAFE18_S1075  NCBITAXON:10090  FACEBASE:1-4GNR  None   \n",
       "..     ...     ...              ...              ...              ...   ...   \n",
       "67    3Q1E     VHE          W8IF1SM  NCBITAXON:10090  FACEBASE:1-4GNG  None   \n",
       "68    3Q1J     VHE          W8IF2SM  NCBITAXON:10090  FACEBASE:1-4GNG  None   \n",
       "69    3Q1P     VHE          W8IF3SM  NCBITAXON:10090  FACEBASE:1-4GNG  None   \n",
       "70    3Q1T     VHE          W8IF4SM  NCBITAXON:10090  FACEBASE:1-4GNG  None   \n",
       "71    3Q1Y     VHE          W8IF5SM  NCBITAXON:10090  FACEBASE:1-4GNG  None   \n",
       "\n",
       "           genotype       strain mutation            stage  ...        litter   \n",
       "0   FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...          None  \\\n",
       "1   FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...          None   \n",
       "2   FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...          None   \n",
       "3   FACEBASE:1-4GV0  MGI:3028467     None  FACEBASE:1-4GJA  ...          None   \n",
       "4   FACEBASE:1-4GT6  MGI:3028467     None  FACEBASE:1-4GJA  ...          None   \n",
       "..              ...          ...      ...              ...  ...           ...   \n",
       "67  FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...  4/15/14WL1-1   \n",
       "68  FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...  4/15/14WL1-2   \n",
       "69  FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...  4/14/14WL1-6   \n",
       "70  FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...  4/15/14WL1-7   \n",
       "71  FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...  4/28/14WL1-1   \n",
       "\n",
       "   collection_date _keywords   \n",
       "0             None      None  \\\n",
       "1       2019-06-19      None   \n",
       "2       2019-06-18      None   \n",
       "3       2019-12-11      None   \n",
       "4       2019-12-24      None   \n",
       "..             ...       ...   \n",
       "67      2014-04-15      None   \n",
       "68      2014-04-15      None   \n",
       "69      2014-04-14      None   \n",
       "70      2014-04-15      None   \n",
       "71      2014-04-28      None   \n",
       "\n",
       "                                                  RCB   \n",
       "0   https://auth.globus.org/8ae274db-d033-47eb-bd3...  \\\n",
       "1   https://auth.globus.org/8ae274db-d033-47eb-bd3...   \n",
       "2   https://auth.globus.org/8ae274db-d033-47eb-bd3...   \n",
       "3   https://auth.globus.org/8ae274db-d033-47eb-bd3...   \n",
       "4   https://auth.globus.org/8ae274db-d033-47eb-bd3...   \n",
       "..                                                ...   \n",
       "67  https://auth.globus.org/b506963e-d274-11e5-99f...   \n",
       "68  https://auth.globus.org/b506963e-d274-11e5-99f...   \n",
       "69  https://auth.globus.org/b506963e-d274-11e5-99f...   \n",
       "70  https://auth.globus.org/b506963e-d274-11e5-99f...   \n",
       "71  https://auth.globus.org/b506963e-d274-11e5-99f...   \n",
       "\n",
       "                                                  RMB   \n",
       "0   https://auth.globus.org/bb256144-d274-11e5-adb...  \\\n",
       "1   https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "2   https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "3   https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "4   https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "..                                                ...   \n",
       "67  https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "68  https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "69  https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "70  https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "71  https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "\n",
       "                                 RCT                              RMT   \n",
       "0   2018-12-11T03:42:13.870864+00:00  2021-10-07T17:51:24.42557+00:00  \\\n",
       "1   2020-02-13T16:45:26.843631+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "2   2020-02-13T17:03:19.454718+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "3    2020-03-17T16:51:19.48232+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "4   2020-03-17T16:52:41.937568+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "..                               ...                              ...   \n",
       "67  2018-03-16T19:03:38.228586+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "68  2018-03-16T19:03:38.228586+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "69  2018-03-16T19:03:38.228586+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "70  2018-03-16T19:03:38.228586+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "71  2018-03-16T19:03:38.228586+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "\n",
       "   cell_source cell_characterization treatment  \n",
       "0         None                  None      None  \n",
       "1         None                  None      None  \n",
       "2         None                  None      None  \n",
       "3         None                  None      None  \n",
       "4         None                  None      None  \n",
       "..         ...                   ...       ...  \n",
       "67        None                  None      None  \n",
       "68        None                  None      None  \n",
       "69        None                  None      None  \n",
       "70        None                  None      None  \n",
       "71        None                  None      None  \n",
       "\n",
       "[72 rows x 25 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DataFrame(path.entities())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: combine conjunction and disjunctions in filters\n",
    "Similar to the prior example, the filters allow combining of conjunctive and disjunctive operators. Like the bitwise-and operator, we also overload the bitwise-or (` | `) operator because the logical-or (`or`) operatar cannot be overloaded."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.facebase.org/ermrest/catalog/1/entity/dataset:=isa:dataset/biosample:=isa:biosample/((species=NCBITAXON%3A10090)&(anatomy=UBERON%3A0002490));((specimen=FACEBASE%3A1-4GNR)&(stage=FACEBASE%3A1-4GJA))\n"
     ]
    }
   ],
   "source": [
    "path = dataset.link(biosample).filter(\n",
    "    ((biosample.species == 'NCBITAXON:10090') & (biosample.anatomy == 'UBERON:0002490')) |\n",
    "    ((biosample.specimen == 'FACEBASE:1-4GNR') & (biosample.stage == 'FACEBASE:1-4GJA')))\n",
    "\n",
    "print(path.uri)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RID</th>\n",
       "      <th>dataset</th>\n",
       "      <th>local_identifier</th>\n",
       "      <th>species</th>\n",
       "      <th>specimen</th>\n",
       "      <th>gene</th>\n",
       "      <th>genotype</th>\n",
       "      <th>strain</th>\n",
       "      <th>mutation</th>\n",
       "      <th>stage</th>\n",
       "      <th>...</th>\n",
       "      <th>litter</th>\n",
       "      <th>collection_date</th>\n",
       "      <th>_keywords</th>\n",
       "      <th>RCB</th>\n",
       "      <th>RMB</th>\n",
       "      <th>RCT</th>\n",
       "      <th>RMT</th>\n",
       "      <th>cell_source</th>\n",
       "      <th>cell_characterization</th>\n",
       "      <th>treatment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1-4TT8</td>\n",
       "      <td>1-4TSP</td>\n",
       "      <td>scWFE18_S197</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNR</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/8ae274db-d033-47eb-bd3...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2018-12-11T03:42:13.870864+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1-8DVG</td>\n",
       "      <td>1-4TSP</td>\n",
       "      <td>scWFE18_S616</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNR</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-06-19</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/8ae274db-d033-47eb-bd3...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2020-02-13T16:45:26.843631+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1-8DVM</td>\n",
       "      <td>1-4TSP</td>\n",
       "      <td>scWFE18_S631</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNR</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-06-18</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/8ae274db-d033-47eb-bd3...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2020-02-13T17:03:19.454718+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1-C3PR</td>\n",
       "      <td>1-4TSP</td>\n",
       "      <td>scTFE18_S979</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNR</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV0</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-12-11</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/8ae274db-d033-47eb-bd3...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2020-03-17T16:51:19.48232+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1-C3PT</td>\n",
       "      <td>1-4TSP</td>\n",
       "      <td>scAFE18_S1075</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNR</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GT6</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-12-24</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/8ae274db-d033-47eb-bd3...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2020-03-17T16:52:41.937568+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>3Q1P</td>\n",
       "      <td>VHE</td>\n",
       "      <td>W8IF3SM</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNG</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>4/14/14WL1-6</td>\n",
       "      <td>2014-04-14</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/b506963e-d274-11e5-99f...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2018-03-16T19:03:38.228586+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>3Q1T</td>\n",
       "      <td>VHE</td>\n",
       "      <td>W8IF4SM</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNG</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>4/15/14WL1-7</td>\n",
       "      <td>2014-04-15</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/b506963e-d274-11e5-99f...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2018-03-16T19:03:38.228586+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>3Q1Y</td>\n",
       "      <td>VHE</td>\n",
       "      <td>W8IF5SM</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNG</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>4/28/14WL1-1</td>\n",
       "      <td>2014-04-28</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/b506963e-d274-11e5-99f...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2018-03-16T19:03:38.228586+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>3V06</td>\n",
       "      <td>3TYP</td>\n",
       "      <td>scWCE18_S198</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNR</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>6/6/17WL1,L2,L3</td>\n",
       "      <td>2017-06-06</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/f8ae714f-6015-48da-971...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2018-05-02T18:27:13.657825+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>3V1P</td>\n",
       "      <td>3V0E</td>\n",
       "      <td>scWLE18_S199</td>\n",
       "      <td>NCBITAXON:10090</td>\n",
       "      <td>FACEBASE:1-4GNR</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GV2</td>\n",
       "      <td>MGI:3028467</td>\n",
       "      <td>None</td>\n",
       "      <td>FACEBASE:1-4GJA</td>\n",
       "      <td>...</td>\n",
       "      <td>6/6/17WL1,L2,L3</td>\n",
       "      <td>2017-06-06</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/8ae274db-d033-47eb-bd3...</td>\n",
       "      <td>https://auth.globus.org/bb256144-d274-11e5-adb...</td>\n",
       "      <td>2018-05-02T18:50:12.225872+00:00</td>\n",
       "      <td>2021-10-07T17:51:24.42557+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>76 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       RID dataset local_identifier          species         specimen  gene   \n",
       "0   1-4TT8  1-4TSP     scWFE18_S197  NCBITAXON:10090  FACEBASE:1-4GNR  None  \\\n",
       "1   1-8DVG  1-4TSP     scWFE18_S616  NCBITAXON:10090  FACEBASE:1-4GNR  None   \n",
       "2   1-8DVM  1-4TSP     scWFE18_S631  NCBITAXON:10090  FACEBASE:1-4GNR  None   \n",
       "3   1-C3PR  1-4TSP     scTFE18_S979  NCBITAXON:10090  FACEBASE:1-4GNR  None   \n",
       "4   1-C3PT  1-4TSP    scAFE18_S1075  NCBITAXON:10090  FACEBASE:1-4GNR  None   \n",
       "..     ...     ...              ...              ...              ...   ...   \n",
       "71    3Q1P     VHE          W8IF3SM  NCBITAXON:10090  FACEBASE:1-4GNG  None   \n",
       "72    3Q1T     VHE          W8IF4SM  NCBITAXON:10090  FACEBASE:1-4GNG  None   \n",
       "73    3Q1Y     VHE          W8IF5SM  NCBITAXON:10090  FACEBASE:1-4GNG  None   \n",
       "74    3V06    3TYP     scWCE18_S198  NCBITAXON:10090  FACEBASE:1-4GNR  None   \n",
       "75    3V1P    3V0E     scWLE18_S199  NCBITAXON:10090  FACEBASE:1-4GNR  None   \n",
       "\n",
       "           genotype       strain mutation            stage  ...   \n",
       "0   FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...  \\\n",
       "1   FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...   \n",
       "2   FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...   \n",
       "3   FACEBASE:1-4GV0  MGI:3028467     None  FACEBASE:1-4GJA  ...   \n",
       "4   FACEBASE:1-4GT6  MGI:3028467     None  FACEBASE:1-4GJA  ...   \n",
       "..              ...          ...      ...              ...  ...   \n",
       "71  FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...   \n",
       "72  FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...   \n",
       "73  FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...   \n",
       "74  FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...   \n",
       "75  FACEBASE:1-4GV2  MGI:3028467     None  FACEBASE:1-4GJA  ...   \n",
       "\n",
       "             litter collection_date _keywords   \n",
       "0              None            None      None  \\\n",
       "1              None      2019-06-19      None   \n",
       "2              None      2019-06-18      None   \n",
       "3              None      2019-12-11      None   \n",
       "4              None      2019-12-24      None   \n",
       "..              ...             ...       ...   \n",
       "71     4/14/14WL1-6      2014-04-14      None   \n",
       "72     4/15/14WL1-7      2014-04-15      None   \n",
       "73     4/28/14WL1-1      2014-04-28      None   \n",
       "74  6/6/17WL1,L2,L3      2017-06-06      None   \n",
       "75  6/6/17WL1,L2,L3      2017-06-06      None   \n",
       "\n",
       "                                                  RCB   \n",
       "0   https://auth.globus.org/8ae274db-d033-47eb-bd3...  \\\n",
       "1   https://auth.globus.org/8ae274db-d033-47eb-bd3...   \n",
       "2   https://auth.globus.org/8ae274db-d033-47eb-bd3...   \n",
       "3   https://auth.globus.org/8ae274db-d033-47eb-bd3...   \n",
       "4   https://auth.globus.org/8ae274db-d033-47eb-bd3...   \n",
       "..                                                ...   \n",
       "71  https://auth.globus.org/b506963e-d274-11e5-99f...   \n",
       "72  https://auth.globus.org/b506963e-d274-11e5-99f...   \n",
       "73  https://auth.globus.org/b506963e-d274-11e5-99f...   \n",
       "74  https://auth.globus.org/f8ae714f-6015-48da-971...   \n",
       "75  https://auth.globus.org/8ae274db-d033-47eb-bd3...   \n",
       "\n",
       "                                                  RMB   \n",
       "0   https://auth.globus.org/bb256144-d274-11e5-adb...  \\\n",
       "1   https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "2   https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "3   https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "4   https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "..                                                ...   \n",
       "71  https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "72  https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "73  https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "74  https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "75  https://auth.globus.org/bb256144-d274-11e5-adb...   \n",
       "\n",
       "                                 RCT                              RMT   \n",
       "0   2018-12-11T03:42:13.870864+00:00  2021-10-07T17:51:24.42557+00:00  \\\n",
       "1   2020-02-13T16:45:26.843631+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "2   2020-02-13T17:03:19.454718+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "3    2020-03-17T16:51:19.48232+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "4   2020-03-17T16:52:41.937568+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "..                               ...                              ...   \n",
       "71  2018-03-16T19:03:38.228586+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "72  2018-03-16T19:03:38.228586+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "73  2018-03-16T19:03:38.228586+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "74  2018-05-02T18:27:13.657825+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "75  2018-05-02T18:50:12.225872+00:00  2021-10-07T17:51:24.42557+00:00   \n",
       "\n",
       "   cell_source cell_characterization treatment  \n",
       "0         None                  None      None  \n",
       "1         None                  None      None  \n",
       "2         None                  None      None  \n",
       "3         None                  None      None  \n",
       "4         None                  None      None  \n",
       "..         ...                   ...       ...  \n",
       "71        None                  None      None  \n",
       "72        None                  None      None  \n",
       "73        None                  None      None  \n",
       "74        None                  None      None  \n",
       "75        None                  None      None  \n",
       "\n",
       "[76 rows x 25 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DataFrame(path.entities())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: filtering at different stages of the path\n",
    "Filtering a path does not have to be done at the end of a path. In fact, the initial intention of the ERMrest URI was to mimick \"RESTful\" semantics where a RESTful \"resource\" is identified, then filtered, then a \"sub-resource\" is identified, and then filtered, and so on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.facebase.org/ermrest/catalog/1/entity/dataset:=isa:dataset/release_date::geq::2017-01-01/experiment:=isa:experiment/experiment_type=OBI%3A0001271/replicate:=isa:replicate/bioreplicate_number=1\n"
     ]
    }
   ],
   "source": [
    "path = dataset.filter(dataset.release_date >= '2017-01-01') \\\n",
    "    .link(experiment).filter(experiment.experiment_type == 'OBI:0001271') \\\n",
    "    .link(replicate).filter(replicate.bioreplicate_number == 1)\n",
    "    \n",
    "print(path.uri)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>RID</th>\n",
       "      <th>dataset</th>\n",
       "      <th>biosample</th>\n",
       "      <th>bioreplicate_number</th>\n",
       "      <th>technical_replicate_number</th>\n",
       "      <th>RCB</th>\n",
       "      <th>RMB</th>\n",
       "      <th>RCT</th>\n",
       "      <th>RMT</th>\n",
       "      <th>experiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1-3T5A</td>\n",
       "      <td>1-3SWE</td>\n",
       "      <td>1-3T0A</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>https://auth.globus.org/f8ae714f-6015-48da-971...</td>\n",
       "      <td>https://auth.globus.org/f8ae714f-6015-48da-971...</td>\n",
       "      <td>2018-06-13T23:41:16.162698+00:00</td>\n",
       "      <td>2018-06-13T23:41:16.162698+00:00</td>\n",
       "      <td>1-3SZA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1-3T5E</td>\n",
       "      <td>1-3SWE</td>\n",
       "      <td>1-3T0E</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>https://auth.globus.org/f8ae714f-6015-48da-971...</td>\n",
       "      <td>https://auth.globus.org/f8ae714f-6015-48da-971...</td>\n",
       "      <td>2018-06-13T23:41:16.162698+00:00</td>\n",
       "      <td>2018-06-13T23:41:16.162698+00:00</td>\n",
       "      <td>1-3SZA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1-3T5J</td>\n",
       "      <td>1-3SWE</td>\n",
       "      <td>1-3T0J</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>https://auth.globus.org/f8ae714f-6015-48da-971...</td>\n",
       "      <td>https://auth.globus.org/f8ae714f-6015-48da-971...</td>\n",
       "      <td>2018-06-13T23:41:16.162698+00:00</td>\n",
       "      <td>2018-06-13T23:41:16.162698+00:00</td>\n",
       "      <td>1-3SZA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1-3T5P</td>\n",
       "      <td>1-3SWE</td>\n",
       "      <td>1-3T0P</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>https://auth.globus.org/f8ae714f-6015-48da-971...</td>\n",
       "      <td>https://auth.globus.org/f8ae714f-6015-48da-971...</td>\n",
       "      <td>2018-06-13T23:41:16.162698+00:00</td>\n",
       "      <td>2018-06-13T23:41:16.162698+00:00</td>\n",
       "      <td>1-3SZA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1-3T5T</td>\n",
       "      <td>1-3SWE</td>\n",
       "      <td>1-3T0T</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>https://auth.globus.org/f8ae714f-6015-48da-971...</td>\n",
       "      <td>https://auth.globus.org/f8ae714f-6015-48da-971...</td>\n",
       "      <td>2018-06-13T23:41:16.162698+00:00</td>\n",
       "      <td>2018-06-13T23:41:16.162698+00:00</td>\n",
       "      <td>1-3SZA</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>3TSJ</td>\n",
       "      <td>2ARP</td>\n",
       "      <td>30GP</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>https://auth.globus.org/b506963e-d274-11e5-99f...</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-04-11T19:28:24.259798+00:00</td>\n",
       "      <td>2018-06-06T01:44:38.502116+00:00</td>\n",
       "      <td>3422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>A-XH76</td>\n",
       "      <td>1-X42W</td>\n",
       "      <td>A-XH6P</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>https://auth.globus.org/93416595-2bc4-42a8-b5b...</td>\n",
       "      <td>https://auth.globus.org/93416595-2bc4-42a8-b5b...</td>\n",
       "      <td>2021-11-24T00:35:15.863829+00:00</td>\n",
       "      <td>2021-11-24T00:35:15.863829+00:00</td>\n",
       "      <td>A-XH72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>A-XH7R</td>\n",
       "      <td>1-X42W</td>\n",
       "      <td>A-XH6W</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>https://auth.globus.org/93416595-2bc4-42a8-b5b...</td>\n",
       "      <td>https://auth.globus.org/93416595-2bc4-42a8-b5b...</td>\n",
       "      <td>2021-11-24T00:38:56.917633+00:00</td>\n",
       "      <td>2021-11-24T00:38:56.917633+00:00</td>\n",
       "      <td>A-XH7M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>Z-2WMG</td>\n",
       "      <td>Z-2NMR</td>\n",
       "      <td>Z-2WMC</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>https://auth.globus.org/93416595-2bc4-42a8-b5b...</td>\n",
       "      <td>https://auth.globus.org/b506963e-d274-11e5-99f...</td>\n",
       "      <td>2022-03-25T23:21:09.358366+00:00</td>\n",
       "      <td>2022-04-04T19:24:52.771093+00:00</td>\n",
       "      <td>Z-2WM6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>363</th>\n",
       "      <td>Z-2WMM</td>\n",
       "      <td>Z-2NMR</td>\n",
       "      <td>Z-2WME</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>https://auth.globus.org/93416595-2bc4-42a8-b5b...</td>\n",
       "      <td>https://auth.globus.org/b506963e-d274-11e5-99f...</td>\n",
       "      <td>2022-03-25T23:22:19.554979+00:00</td>\n",
       "      <td>2022-04-04T19:26:09.037904+00:00</td>\n",
       "      <td>Z-2WMJ</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>364 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        RID dataset biosample  bioreplicate_number   \n",
       "0    1-3T5A  1-3SWE    1-3T0A                    1  \\\n",
       "1    1-3T5E  1-3SWE    1-3T0E                    1   \n",
       "2    1-3T5J  1-3SWE    1-3T0J                    1   \n",
       "3    1-3T5P  1-3SWE    1-3T0P                    1   \n",
       "4    1-3T5T  1-3SWE    1-3T0T                    1   \n",
       "..      ...     ...       ...                  ...   \n",
       "359    3TSJ    2ARP      30GP                    1   \n",
       "360  A-XH76  1-X42W    A-XH6P                    1   \n",
       "361  A-XH7R  1-X42W    A-XH6W                    1   \n",
       "362  Z-2WMG  Z-2NMR    Z-2WMC                    1   \n",
       "363  Z-2WMM  Z-2NMR    Z-2WME                    1   \n",
       "\n",
       "     technical_replicate_number   \n",
       "0                             1  \\\n",
       "1                             1   \n",
       "2                             1   \n",
       "3                             1   \n",
       "4                             1   \n",
       "..                          ...   \n",
       "359                           1   \n",
       "360                           1   \n",
       "361                           1   \n",
       "362                           1   \n",
       "363                           1   \n",
       "\n",
       "                                                   RCB   \n",
       "0    https://auth.globus.org/f8ae714f-6015-48da-971...  \\\n",
       "1    https://auth.globus.org/f8ae714f-6015-48da-971...   \n",
       "2    https://auth.globus.org/f8ae714f-6015-48da-971...   \n",
       "3    https://auth.globus.org/f8ae714f-6015-48da-971...   \n",
       "4    https://auth.globus.org/f8ae714f-6015-48da-971...   \n",
       "..                                                 ...   \n",
       "359  https://auth.globus.org/b506963e-d274-11e5-99f...   \n",
       "360  https://auth.globus.org/93416595-2bc4-42a8-b5b...   \n",
       "361  https://auth.globus.org/93416595-2bc4-42a8-b5b...   \n",
       "362  https://auth.globus.org/93416595-2bc4-42a8-b5b...   \n",
       "363  https://auth.globus.org/93416595-2bc4-42a8-b5b...   \n",
       "\n",
       "                                                   RMB   \n",
       "0    https://auth.globus.org/f8ae714f-6015-48da-971...  \\\n",
       "1    https://auth.globus.org/f8ae714f-6015-48da-971...   \n",
       "2    https://auth.globus.org/f8ae714f-6015-48da-971...   \n",
       "3    https://auth.globus.org/f8ae714f-6015-48da-971...   \n",
       "4    https://auth.globus.org/f8ae714f-6015-48da-971...   \n",
       "..                                                 ...   \n",
       "359                                               None   \n",
       "360  https://auth.globus.org/93416595-2bc4-42a8-b5b...   \n",
       "361  https://auth.globus.org/93416595-2bc4-42a8-b5b...   \n",
       "362  https://auth.globus.org/b506963e-d274-11e5-99f...   \n",
       "363  https://auth.globus.org/b506963e-d274-11e5-99f...   \n",
       "\n",
       "                                  RCT                               RMT   \n",
       "0    2018-06-13T23:41:16.162698+00:00  2018-06-13T23:41:16.162698+00:00  \\\n",
       "1    2018-06-13T23:41:16.162698+00:00  2018-06-13T23:41:16.162698+00:00   \n",
       "2    2018-06-13T23:41:16.162698+00:00  2018-06-13T23:41:16.162698+00:00   \n",
       "3    2018-06-13T23:41:16.162698+00:00  2018-06-13T23:41:16.162698+00:00   \n",
       "4    2018-06-13T23:41:16.162698+00:00  2018-06-13T23:41:16.162698+00:00   \n",
       "..                                ...                               ...   \n",
       "359  2018-04-11T19:28:24.259798+00:00  2018-06-06T01:44:38.502116+00:00   \n",
       "360  2021-11-24T00:35:15.863829+00:00  2021-11-24T00:35:15.863829+00:00   \n",
       "361  2021-11-24T00:38:56.917633+00:00  2021-11-24T00:38:56.917633+00:00   \n",
       "362  2022-03-25T23:21:09.358366+00:00  2022-04-04T19:24:52.771093+00:00   \n",
       "363  2022-03-25T23:22:19.554979+00:00  2022-04-04T19:26:09.037904+00:00   \n",
       "\n",
       "    experiment  \n",
       "0       1-3SZA  \n",
       "1       1-3SZA  \n",
       "2       1-3SZA  \n",
       "3       1-3SZA  \n",
       "4       1-3SZA  \n",
       "..         ...  \n",
       "359       3422  \n",
       "360     A-XH72  \n",
       "361     A-XH7M  \n",
       "362     Z-2WM6  \n",
       "363     Z-2WMJ  \n",
       "\n",
       "[364 rows x 10 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DataFrame(path.entities())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Linking Examples\n",
    "\n",
    "### Example: explicit column links\n",
    "Up until now, the examples have shown how to link entities via _implicit_ join predicates. That is, we knew there existed a foriegn key reference constraint between foreign keys of one entity and keys of another entity. We needed only to ask ERMrest to link the entities in order to get the linked set.\n",
    "\n",
    "The problem with implicit links is that it become _ambiguous_ if there are more than one foreign key reference between tables. To support these situations, ERMrest and the `DataPath`'s `link(...)` method can specify the columns to use for the link condition, explicitly.\n",
    "\n",
    "The structure of the `on` clause is:\n",
    "- an equality comparison operation where\n",
    "- the _left_ operand is a column of the _left_ table instance which is also the path _context_ before the link method is called, and\n",
    "- the _right_ operand is a column of the _right_ table instance which is the table _to be linked_ to the path."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.facebase.org/ermrest/catalog/1/entity/dataset:=isa:dataset/experiment:=(RID)=(isa:experiment:dataset)\n"
     ]
    }
   ],
   "source": [
    "path = dataset.link(experiment, on=(dataset.RID==experiment.dataset))\n",
    "print(path.uri)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**IMPORTANT** Not all tables are related by foreign key references. ERMrest does not allow arbitrary relational joins. Tables must be related by a foreign key reference in order to link them in a data path."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RID</th>\n",
       "      <th>dataset</th>\n",
       "      <th>local_identifier</th>\n",
       "      <th>experiment_type</th>\n",
       "      <th>molecule_type</th>\n",
       "      <th>strandedness</th>\n",
       "      <th>rnaseq_selection</th>\n",
       "      <th>target_of_assay</th>\n",
       "      <th>chromatin_modifier</th>\n",
       "      <th>transcription_factor</th>\n",
       "      <th>histone_modification</th>\n",
       "      <th>control_assay</th>\n",
       "      <th>protocol</th>\n",
       "      <th>RCB</th>\n",
       "      <th>RMB</th>\n",
       "      <th>RCT</th>\n",
       "      <th>RMT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1-3SD2</td>\n",
       "      <td>1-3SB2</td>\n",
       "      <td>3XhET4_pHsp68-lacZ-tdTomato</td>\n",
       "      <td>OBI:0002083</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>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/a1d30d14-b3b0-49de-854...</td>\n",
       "      <td>https://auth.globus.org/b506963e-d274-11e5-99f...</td>\n",
       "      <td>2018-06-08T19:40:48.98831+00:00</td>\n",
       "      <td>2018-11-28T00:54:17.199655+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1-3SHT</td>\n",
       "      <td>1-3SGA</td>\n",
       "      <td>3XcET4_pHsp68-lacZ-tdTomato</td>\n",
       "      <td>OBI:0002083</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>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/a1d30d14-b3b0-49de-854...</td>\n",
       "      <td>https://auth.globus.org/b506963e-d274-11e5-99f...</td>\n",
       "      <td>2018-06-08T21:29:49.540747+00:00</td>\n",
       "      <td>2018-11-28T00:54:17.199655+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1-3SNY</td>\n",
       "      <td>1-3SM2</td>\n",
       "      <td>3XhET7_pHsp68-lacZ-tdTomato</td>\n",
       "      <td>OBI:0002083</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>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://auth.globus.org/a1d30d14-b3b0-49de-854...</td>\n",
       "      <td>https://auth.globus.org/b506963e-d274-11e5-99f...</td>\n",
       "      <td>2018-06-08T21:47:53.103435+00:00</td>\n",
       "      <td>2018-11-28T00:54:17.199655+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      RID dataset             local_identifier experiment_type molecule_type   \n",
       "0  1-3SD2  1-3SB2  3XhET4_pHsp68-lacZ-tdTomato     OBI:0002083          None  \\\n",
       "1  1-3SHT  1-3SGA  3XcET4_pHsp68-lacZ-tdTomato     OBI:0002083          None   \n",
       "2  1-3SNY  1-3SM2  3XhET7_pHsp68-lacZ-tdTomato     OBI:0002083          None   \n",
       "\n",
       "  strandedness rnaseq_selection target_of_assay chromatin_modifier   \n",
       "0         None             None            None               None  \\\n",
       "1         None             None            None               None   \n",
       "2         None             None            None               None   \n",
       "\n",
       "  transcription_factor histone_modification control_assay protocol   \n",
       "0                 None                 None          None     None  \\\n",
       "1                 None                 None          None     None   \n",
       "2                 None                 None          None     None   \n",
       "\n",
       "                                                 RCB   \n",
       "0  https://auth.globus.org/a1d30d14-b3b0-49de-854...  \\\n",
       "1  https://auth.globus.org/a1d30d14-b3b0-49de-854...   \n",
       "2  https://auth.globus.org/a1d30d14-b3b0-49de-854...   \n",
       "\n",
       "                                                 RMB   \n",
       "0  https://auth.globus.org/b506963e-d274-11e5-99f...  \\\n",
       "1  https://auth.globus.org/b506963e-d274-11e5-99f...   \n",
       "2  https://auth.globus.org/b506963e-d274-11e5-99f...   \n",
       "\n",
       "                                RCT                               RMT  \n",
       "0   2018-06-08T19:40:48.98831+00:00  2018-11-28T00:54:17.199655+00:00  \n",
       "1  2018-06-08T21:29:49.540747+00:00  2018-11-28T00:54:17.199655+00:00  \n",
       "2  2018-06-08T21:47:53.103435+00:00  2018-11-28T00:54:17.199655+00:00  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DataFrame(path.entities().fetch(limit=3))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: explicit column links combined with table aliasing\n",
    "As usual, table instances are generated automatically unless we provide a table alias."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.facebase.org/ermrest/catalog/1/entity/dataset:=isa:dataset/S:=(RID)=(isa:biosample:dataset)\n"
     ]
    }
   ],
   "source": [
    "path = dataset.link(biosample.alias('S'), on=(dataset.RID==biosample.dataset))\n",
    "print(path.uri)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that we cannot use the alias right away in the `on` clause because it was not _bound_ to the path until _after_ the `link(...)` operation was performed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: links with \"outer join\" semantics\n",
    "Up until now, the examples have shown \"`link`s\" with _inner join_ semantics. _Outer join_ semantics can be expressed as part of explicit column links, and _only_ when using explicit column links.\n",
    "\n",
    "The `link(...)` method accepts a \"`join_type`\" parameter, i.e., \"`.link(... join_type=TYPE)`\", where _TYPE_ may be `'left'`, `'right'`, `'full'`, and defaults to `''` which indicates inner join type.\n",
    "\n",
    "By '`left`' outer joining in the link from `'dataset'` to `'experiment`' and to `'biosample'`, and then reseting the context of the path to `'dataset'`, the following path gives us a reference to `'dataset'` entities that _whether or not_ they have any experiments or biosamples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.facebase.org/ermrest/catalog/1/attribute/dataset:=isa:dataset/E:=left(RID)=(isa:experiment:dataset)/$dataset/S:=left(RID)=(isa:biosample:dataset)/$dataset/dataset:RID,dataset:title,E:experiment_type,S:species\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1049"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Notice in between `link`s that we have to reset the context back to `dataset` so that the\n",
    "# second join is also left joined from the dataset table instance.\n",
    "path = dataset.link(experiment.alias('E'), on=dataset.RID==experiment.dataset, join_type='left') \\\n",
    "              .dataset \\\n",
    "              .link(biosample.alias('S'), on=dataset.RID==biosample.dataset, join_type='left') \\\n",
    "\n",
    "# Notice that we have to perform the attribute fetch from the context of the `path.dataset`\n",
    "# table instance.\n",
    "results = path.dataset.attributes(path.dataset.RID, \n",
    "                                  path.dataset.title, \n",
    "                                  path.E.experiment_type, \n",
    "                                  path.S.species)\n",
    "\n",
    "print(results.uri)\n",
    "len(results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see above that we have a full set of datasets _whether or not_ they have any experiments with biosamples. For further evidence, we can convert to a DataFrame and look at a slice of its entries. Note that the biosample's 'species' and 'stage' attributes do not exist for some results (i.e., `NaN`) because those attributes did not exist for the join condition."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>RID</th>\n",
       "      <th>title</th>\n",
       "      <th>experiment_type</th>\n",
       "      <th>species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TZP</td>\n",
       "      <td>Gene expression microarray - mouse E10.5 maxil...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>VTG</td>\n",
       "      <td>Gene expression microarray - mouse E10.5 mandi...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>V4G</td>\n",
       "      <td>miRNAs expressed in the E13.5 mouse mandible</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TMP</td>\n",
       "      <td>miRNAs expressed in the 72hpf zebrafish head</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TVA</td>\n",
       "      <td>Gene expression microarray - mouse E10.5 media...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>V1C</td>\n",
       "      <td>Gene expression microarray - mouse E11.5 singl...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>V8A</td>\n",
       "      <td>Gene expression microarray - mouse E10.5 later...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>VM6</td>\n",
       "      <td>Gene expression microarray - mouse E10.5 media...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>VHR</td>\n",
       "      <td>Gene expression profiling of palatal and mandi...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>V9A</td>\n",
       "      <td>Gene expression microarray - mouse E10.5 olfac...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   RID                                              title experiment_type   \n",
       "0  TZP  Gene expression microarray - mouse E10.5 maxil...            None  \\\n",
       "1  VTG  Gene expression microarray - mouse E10.5 mandi...            None   \n",
       "2  V4G       miRNAs expressed in the E13.5 mouse mandible            None   \n",
       "3  TMP       miRNAs expressed in the 72hpf zebrafish head            None   \n",
       "4  TVA  Gene expression microarray - mouse E10.5 media...            None   \n",
       "5  V1C  Gene expression microarray - mouse E11.5 singl...            None   \n",
       "6  V8A  Gene expression microarray - mouse E10.5 later...            None   \n",
       "7  VM6  Gene expression microarray - mouse E10.5 media...            None   \n",
       "8  VHR  Gene expression profiling of palatal and mandi...            None   \n",
       "9  V9A  Gene expression microarray - mouse E10.5 olfac...            None   \n",
       "\n",
       "  species  \n",
       "0    None  \n",
       "1    None  \n",
       "2    None  \n",
       "3    None  \n",
       "4    None  \n",
       "5    None  \n",
       "6    None  \n",
       "7    None  \n",
       "8    None  \n",
       "9    None  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DataFrame(results)[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Faceting Examples\n",
    "You may have noticed that in the examples above, the 'species' and 'experiment_type' attributes are identifiers ('CURIE's to be precise). We may want to construct filters on our datasets based on these categories. This can be used for \"faceted search\" modes and can be useful even within the context of programmatic access to data in the catalog."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: faceting on \"related\" tables\n",
    "Let's say we want to find all of the biosamples in our catalog where their species are 'Mus musculus' and their age stage are 'E10.5'.\n",
    "\n",
    "We need to extend our understanding of the data model with the following tables that are related to '`biosample`'.\n",
    "- `isa.biosample.species -> vocab.species`: the biosample table has a foreign key reference to the '`species`' table.\n",
    "- `isa.biosample.stage -> vocab.stage`: the biosample table has a foreign key reference to the '`stage`' table.\n",
    "\n",
    "We may say that `species` and `stage` are _related_ to the `biosample` table in the sense that `biosample` has a direct foreign key relationship from it to them.\n",
    "\n",
    "For convenience, we will get local variables for the species and stage tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "species = pb.vocab.species\n",
    "stage = pb.vocab.stage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let's link samples with species and filter on the term \"Mus musculus\" (i.e., \"mouse\")."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.facebase.org/ermrest/catalog/1/entity/S:=isa:biosample/species:=vocab:species/name=Mus%20musculus\n"
     ]
    }
   ],
   "source": [
    "# Here we have to use the container `columns_definitions` because `name` is reserved\n",
    "path = biosample.alias('S').link(species).filter(species.column_definitions['name'] == 'Mus musculus')\n",
    "print(path.uri)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now the _context_ of the path is the `species` table instance, but we need to link from the `biosample` to the age `stage` table.\n",
    "\n",
    "To do so, we reference the `biosample` table instance, in this case using its alias `S`. Then we link off of that table instance which updates the `path` itself."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.facebase.org/ermrest/catalog/1/entity/S:=isa:biosample/species:=vocab:species/name=Mus%20musculus/$S/stage:=vocab:stage/name=E10.5\n"
     ]
    }
   ],
   "source": [
    "path.S.link(stage).filter(stage.column_definitions['name'] == 'E10.5')\n",
    "print(path.uri)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, the path _context_ is the age `stage` table instance, but we wanted to get the entities for the `biosample` table. To do so, again we will reference the `biosample` table instance by the alias `S` we used. From there, we will call the `entities(...)` method to get the samples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.facebase.org/ermrest/catalog/1/attribute/S:=isa:biosample/species:=vocab:species/name=Mus%20musculus/$S/stage:=vocab:stage/name=E10.5/$S/S:RID,S:collection_date,species:=species:name,species_uri:=species:uri,stage:=stage:name,stage_uri:=stage:uri\n"
     ]
    }
   ],
   "source": [
    "results = path.S.attributes(path.S.RID,\n",
    "                            path.S.collection_date,\n",
    "                            path.species.column_definitions['name'].alias('species'),\n",
    "                            path.species.column_definitions['uri'].alias('species_uri'),\n",
    "                            path.stage.column_definitions['name'].alias('stage'),\n",
    "                            path.stage.column_definitions['uri'].alias('stage_uri'))\n",
    "print(results.uri)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>RID</th>\n",
       "      <th>collection_date</th>\n",
       "      <th>species</th>\n",
       "      <th>species_uri</th>\n",
       "      <th>stage</th>\n",
       "      <th>stage_uri</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1-4TQ8</td>\n",
       "      <td>None</td>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>http://purl.obolibrary.org/obo/NCBITaxon_10090</td>\n",
       "      <td>E10.5</td>\n",
       "      <td>https://www.facebase.org/id/1-4GJ0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1-4TR6</td>\n",
       "      <td>None</td>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>http://purl.obolibrary.org/obo/NCBITaxon_10090</td>\n",
       "      <td>E10.5</td>\n",
       "      <td>https://www.facebase.org/id/1-4GJ0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1-4TRG</td>\n",
       "      <td>None</td>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>http://purl.obolibrary.org/obo/NCBITaxon_10090</td>\n",
       "      <td>E10.5</td>\n",
       "      <td>https://www.facebase.org/id/1-4GJ0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18-XYWP</td>\n",
       "      <td>None</td>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>http://purl.obolibrary.org/obo/NCBITaxon_10090</td>\n",
       "      <td>E10.5</td>\n",
       "      <td>https://www.facebase.org/id/1-4GJ0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18-XYWR</td>\n",
       "      <td>None</td>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>http://purl.obolibrary.org/obo/NCBITaxon_10090</td>\n",
       "      <td>E10.5</td>\n",
       "      <td>https://www.facebase.org/id/1-4GJ0</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>527</th>\n",
       "      <td>3-SHHP</td>\n",
       "      <td>None</td>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>http://purl.obolibrary.org/obo/NCBITaxon_10090</td>\n",
       "      <td>E10.5</td>\n",
       "      <td>https://www.facebase.org/id/1-4GJ0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>528</th>\n",
       "      <td>3T3P</td>\n",
       "      <td>None</td>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>http://purl.obolibrary.org/obo/NCBITaxon_10090</td>\n",
       "      <td>E10.5</td>\n",
       "      <td>https://www.facebase.org/id/1-4GJ0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529</th>\n",
       "      <td>3T3T</td>\n",
       "      <td>None</td>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>http://purl.obolibrary.org/obo/NCBITaxon_10090</td>\n",
       "      <td>E10.5</td>\n",
       "      <td>https://www.facebase.org/id/1-4GJ0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>530</th>\n",
       "      <td>3T3Y</td>\n",
       "      <td>None</td>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>http://purl.obolibrary.org/obo/NCBITaxon_10090</td>\n",
       "      <td>E10.5</td>\n",
       "      <td>https://www.facebase.org/id/1-4GJ0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>531</th>\n",
       "      <td>6-2NNR</td>\n",
       "      <td>None</td>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>http://purl.obolibrary.org/obo/NCBITaxon_10090</td>\n",
       "      <td>E10.5</td>\n",
       "      <td>https://www.facebase.org/id/1-4GJ0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>532 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         RID collection_date       species   \n",
       "0     1-4TQ8            None  Mus musculus  \\\n",
       "1     1-4TR6            None  Mus musculus   \n",
       "2     1-4TRG            None  Mus musculus   \n",
       "3    18-XYWP            None  Mus musculus   \n",
       "4    18-XYWR            None  Mus musculus   \n",
       "..       ...             ...           ...   \n",
       "527   3-SHHP            None  Mus musculus   \n",
       "528     3T3P            None  Mus musculus   \n",
       "529     3T3T            None  Mus musculus   \n",
       "530     3T3Y            None  Mus musculus   \n",
       "531   6-2NNR            None  Mus musculus   \n",
       "\n",
       "                                        species_uri  stage   \n",
       "0    http://purl.obolibrary.org/obo/NCBITaxon_10090  E10.5  \\\n",
       "1    http://purl.obolibrary.org/obo/NCBITaxon_10090  E10.5   \n",
       "2    http://purl.obolibrary.org/obo/NCBITaxon_10090  E10.5   \n",
       "3    http://purl.obolibrary.org/obo/NCBITaxon_10090  E10.5   \n",
       "4    http://purl.obolibrary.org/obo/NCBITaxon_10090  E10.5   \n",
       "..                                              ...    ...   \n",
       "527  http://purl.obolibrary.org/obo/NCBITaxon_10090  E10.5   \n",
       "528  http://purl.obolibrary.org/obo/NCBITaxon_10090  E10.5   \n",
       "529  http://purl.obolibrary.org/obo/NCBITaxon_10090  E10.5   \n",
       "530  http://purl.obolibrary.org/obo/NCBITaxon_10090  E10.5   \n",
       "531  http://purl.obolibrary.org/obo/NCBITaxon_10090  E10.5   \n",
       "\n",
       "                              stage_uri  \n",
       "0    https://www.facebase.org/id/1-4GJ0  \n",
       "1    https://www.facebase.org/id/1-4GJ0  \n",
       "2    https://www.facebase.org/id/1-4GJ0  \n",
       "3    https://www.facebase.org/id/1-4GJ0  \n",
       "4    https://www.facebase.org/id/1-4GJ0  \n",
       "..                                  ...  \n",
       "527  https://www.facebase.org/id/1-4GJ0  \n",
       "528  https://www.facebase.org/id/1-4GJ0  \n",
       "529  https://www.facebase.org/id/1-4GJ0  \n",
       "530  https://www.facebase.org/id/1-4GJ0  \n",
       "531  https://www.facebase.org/id/1-4GJ0  \n",
       "\n",
       "[532 rows x 6 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DataFrame(results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouping Examples\n",
    "Now support you would like to aggregate all of the vocabulary terms associated with a Dataset. Here, we examine what happens when you have a model such that `dataset <- dataset_VOCAB -> VOCAB` where `VOCAB` is a placeholder for a table that includes a vocabulary term set. These tables typically have a `name` column for the human-readable preferred label to go along with the formal URI or CURIE of the concept class."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.facebase.org/ermrest/catalog/1/attributegroup/dataset:=isa:dataset/dataset_species:=left(id)=(isa:dataset_species:dataset_id)/species:=vocab:species/$dataset/dataset_experiment_type:=left(id)=(isa:dataset_experiment_type:dataset_id)/experiment_type:=vocab:experiment_type/$dataset/RID;title,species:=array_d(species:name),experiment_type:=array_d(experiment_type:name)\n",
      "1048\n"
     ]
    }
   ],
   "source": [
    "# We need to import the `ArrayD` aggregate function for this example.\n",
    "from deriva.core.datapath import ArrayD\n",
    "\n",
    "# For convenience, get python objects for the additional tables.\n",
    "dataset_species = pb.isa.dataset_species\n",
    "dataset_experiment_type = pb.isa.dataset_experiment_type\n",
    "species = pb.vocab.species\n",
    "experiment_type = pb.vocab.experiment_type\n",
    "\n",
    "# Start by doing a couple left outer joins on the dataset-term association tables, then link\n",
    "# (i.e., inner join) the associated vocabulary term table, then reset the context back to the\n",
    "# dataset table.\n",
    "path = dataset.link(dataset_species, on=dataset.id==dataset_species.dataset_id, join_type='left') \\\n",
    "              .link(species) \\\n",
    "              .dataset \\\n",
    "              .link(dataset_experiment_type, on=dataset.id==dataset_experiment_type.dataset_id, join_type='left') \\\n",
    "              .link(experiment_type)\n",
    "\n",
    "# Again, notice that we reset the context to the `dataset` table alias so that we will retrieve \n",
    "# dataset entities based on the groupings to be defined next. For the groupby key we will use the\n",
    "# dataset.RID, but for this example any primary key would work. Then we will get aggregate arrays\n",
    "# of the linked vocabulary tables.\n",
    "results = path.dataset.groupby(dataset.RID).attributes(\n",
    "    dataset.title,\n",
    "    ArrayD(path.species.column_definitions['name']).alias('species'),\n",
    "    ArrayD(path.experiment_type.column_definitions['name']).alias('experiment_type')\n",
    ")\n",
    "\n",
    "#results = path.dataset.entities()\n",
    "print(results.uri)\n",
    "print(len(results))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RID</th>\n",
       "      <th>title</th>\n",
       "      <th>species</th>\n",
       "      <th>experiment_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1-3SB2</td>\n",
       "      <td>Activity of human neural crest enhancer near G...</td>\n",
       "      <td>[Mus musculus]</td>\n",
       "      <td>[enhancer activity detection by reporter gene ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1-3SGA</td>\n",
       "      <td>Activity of chimp neural crest enhancer near G...</td>\n",
       "      <td>[Mus musculus]</td>\n",
       "      <td>[enhancer activity detection by reporter gene ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1-3SM2</td>\n",
       "      <td>Activity of human neural crest enhancer near F...</td>\n",
       "      <td>[Mus musculus]</td>\n",
       "      <td>[enhancer activity detection by reporter gene ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1-3SQJ</td>\n",
       "      <td>Activity of chimp neural crest enhancer near F...</td>\n",
       "      <td>[Mus musculus]</td>\n",
       "      <td>[enhancer activity detection by reporter gene ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1-3SVP</td>\n",
       "      <td>FB0023_18mo male with hypertelorism_Candidate ...</td>\n",
       "      <td>[Homo sapiens]</td>\n",
       "      <td>[exome sequencing assay, genotyping assay, ima...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1-3SVT</td>\n",
       "      <td>FB0043_3 male cousins with natal teeth and ano...</td>\n",
       "      <td>[Homo sapiens]</td>\n",
       "      <td>[exome sequencing assay, genotyping assay, ima...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1-3SVY</td>\n",
       "      <td>FB0051_6 year old male with hypertelorism (mar...</td>\n",
       "      <td>[Homo sapiens]</td>\n",
       "      <td>[exome sequencing assay, genotyping assay, ima...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1-3SW2</td>\n",
       "      <td>FB0064_Male with Congenital craniosynostosis_C...</td>\n",
       "      <td>[Homo sapiens]</td>\n",
       "      <td>[exome sequencing assay, genotyping assay, ima...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1-3SW6</td>\n",
       "      <td>FB0115_11yo male with dysmorphic facial featur...</td>\n",
       "      <td>[Homo sapiens]</td>\n",
       "      <td>[exome sequencing assay, genotyping assay, ima...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1-3SWA</td>\n",
       "      <td>FB0122_29yo female with bilateral hearing loss...</td>\n",
       "      <td>[Homo sapiens]</td>\n",
       "      <td>[exome sequencing assay, genotyping assay, ima...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1-3SWE</td>\n",
       "      <td>Interpalatine suture, WT and Fgfr2+/S252W (Ape...</td>\n",
       "      <td>[Mus musculus]</td>\n",
       "      <td>[RNA-seq assay]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1-3SXY</td>\n",
       "      <td>Maxillary-Palatine Suture, WT and Fgfr2+/S252W...</td>\n",
       "      <td>[Mus musculus]</td>\n",
       "      <td>[RNA-seq assay]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1-3V7A</td>\n",
       "      <td>FB0011_9 year old female with single central m...</td>\n",
       "      <td>[Homo sapiens]</td>\n",
       "      <td>[exome sequencing assay, genotyping assay, ima...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1-3V7J</td>\n",
       "      <td>FB0015_Male with bilateral cleft lip and palate</td>\n",
       "      <td>[Homo sapiens]</td>\n",
       "      <td>[exome sequencing assay, genotyping assay, ima...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1-3V7P</td>\n",
       "      <td>FB0027_29 month old male_Multiple congenital a...</td>\n",
       "      <td>[Homo sapiens]</td>\n",
       "      <td>[exome sequencing assay, genotyping assay, ima...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1-3V8A</td>\n",
       "      <td>FB0039_\"Identical Male Twins\"_Treacher Collins...</td>\n",
       "      <td>[Homo sapiens]</td>\n",
       "      <td>[exome sequencing assay, genotyping assay, ima...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1-3V8Y</td>\n",
       "      <td>FB0041_8 yo male_Brachi-Oto-Renal (BOR) Syndrome</td>\n",
       "      <td>[Homo sapiens]</td>\n",
       "      <td>[genotyping assay, imaging assay, Morphometric...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1-3V9E</td>\n",
       "      <td>FB0049_17 month old male with bilateral macros...</td>\n",
       "      <td>[Homo sapiens]</td>\n",
       "      <td>[exome sequencing assay, genotyping assay, ima...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1-3VAT</td>\n",
       "      <td>FB0062_Proband Only Saliva_Blepharo-cheilo-don...</td>\n",
       "      <td>[Homo sapiens]</td>\n",
       "      <td>[exome sequencing assay, genotyping assay, ima...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1-3VBE</td>\n",
       "      <td>FB0124_Trio Saliva_5 mo male with Hallermann S...</td>\n",
       "      <td>[Homo sapiens]</td>\n",
       "      <td>[exome sequencing assay, genotyping assay, ima...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       RID                                              title         species   \n",
       "0   1-3SB2  Activity of human neural crest enhancer near G...  [Mus musculus]  \\\n",
       "1   1-3SGA  Activity of chimp neural crest enhancer near G...  [Mus musculus]   \n",
       "2   1-3SM2  Activity of human neural crest enhancer near F...  [Mus musculus]   \n",
       "3   1-3SQJ  Activity of chimp neural crest enhancer near F...  [Mus musculus]   \n",
       "4   1-3SVP  FB0023_18mo male with hypertelorism_Candidate ...  [Homo sapiens]   \n",
       "5   1-3SVT  FB0043_3 male cousins with natal teeth and ano...  [Homo sapiens]   \n",
       "6   1-3SVY  FB0051_6 year old male with hypertelorism (mar...  [Homo sapiens]   \n",
       "7   1-3SW2  FB0064_Male with Congenital craniosynostosis_C...  [Homo sapiens]   \n",
       "8   1-3SW6  FB0115_11yo male with dysmorphic facial featur...  [Homo sapiens]   \n",
       "9   1-3SWA  FB0122_29yo female with bilateral hearing loss...  [Homo sapiens]   \n",
       "10  1-3SWE  Interpalatine suture, WT and Fgfr2+/S252W (Ape...  [Mus musculus]   \n",
       "11  1-3SXY  Maxillary-Palatine Suture, WT and Fgfr2+/S252W...  [Mus musculus]   \n",
       "12  1-3V7A  FB0011_9 year old female with single central m...  [Homo sapiens]   \n",
       "13  1-3V7J    FB0015_Male with bilateral cleft lip and palate  [Homo sapiens]   \n",
       "14  1-3V7P  FB0027_29 month old male_Multiple congenital a...  [Homo sapiens]   \n",
       "15  1-3V8A  FB0039_\"Identical Male Twins\"_Treacher Collins...  [Homo sapiens]   \n",
       "16  1-3V8Y   FB0041_8 yo male_Brachi-Oto-Renal (BOR) Syndrome  [Homo sapiens]   \n",
       "17  1-3V9E  FB0049_17 month old male with bilateral macros...  [Homo sapiens]   \n",
       "18  1-3VAT  FB0062_Proband Only Saliva_Blepharo-cheilo-don...  [Homo sapiens]   \n",
       "19  1-3VBE  FB0124_Trio Saliva_5 mo male with Hallermann S...  [Homo sapiens]   \n",
       "\n",
       "                                      experiment_type  \n",
       "0   [enhancer activity detection by reporter gene ...  \n",
       "1   [enhancer activity detection by reporter gene ...  \n",
       "2   [enhancer activity detection by reporter gene ...  \n",
       "3   [enhancer activity detection by reporter gene ...  \n",
       "4   [exome sequencing assay, genotyping assay, ima...  \n",
       "5   [exome sequencing assay, genotyping assay, ima...  \n",
       "6   [exome sequencing assay, genotyping assay, ima...  \n",
       "7   [exome sequencing assay, genotyping assay, ima...  \n",
       "8   [exome sequencing assay, genotyping assay, ima...  \n",
       "9   [exome sequencing assay, genotyping assay, ima...  \n",
       "10                                    [RNA-seq assay]  \n",
       "11                                    [RNA-seq assay]  \n",
       "12  [exome sequencing assay, genotyping assay, ima...  \n",
       "13  [exome sequencing assay, genotyping assay, ima...  \n",
       "14  [exome sequencing assay, genotyping assay, ima...  \n",
       "15  [exome sequencing assay, genotyping assay, ima...  \n",
       "16  [genotyping assay, imaging assay, Morphometric...  \n",
       "17  [exome sequencing assay, genotyping assay, ima...  \n",
       "18  [exome sequencing assay, genotyping assay, ima...  \n",
       "19  [exome sequencing assay, genotyping assay, ima...  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DataFrame(results.fetch(limit=20))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}