Completed-03-Design Patterns and Advanced Queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Design patterns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have completed a relatively simple but fully functional pipeline, let's revisit some design patterns that were used in your pipeline and will commonly occur in the future pipeline you would design."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As always, let's start by importing DataJoint package. Also, import all the tables by importing them from the `workshop.session3` module."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datajoint as dj\n",
    "from workshop.session3 import schema, Mouse, Session, Neuron, ActivityStatistics, SpikeDetectionParam, Spikes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are also going to take a look at another pre-defined pipeline, defined in the `workshop.calcium` module."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import workshop.calcium as ca"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are going to learn a lot more about this pipeline in a session tomorrow, but for now, we are going to take a look at the ERD and compare it to our pipeline."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Experimental subjects as the root of your schema"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at our pipeline's ERD again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.ERD(schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's also plot the ERD for the `ca` module."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.ERD(ca)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that while the calcium pipeline is quite a bit more complex than ours, both piplines start with a `Mouse` table at the very top!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When designing data pipelines, we tend to see this pattern of **a pipeline starting with the experimental subject** over and over. If you think about it, this makes sense as our experiments pretty much ultimately depends on the experimental subjects, and the identity of our experimental subjects typically do not depend on other information."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## One-to-many vs one-to-one relationships"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's compare the relationships seen between:\n",
    "1. `Mouse` table and `Session` table, to\n",
    "2. `Session` table and `Neuron` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.ERD(Mouse) + Session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.ERD(Session) + Neuron"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that `Session` and `Neuron` tables are connected by a thicker line which indicates that the parent table (`Session`) fully identifies the child table (`Neuron`). We call this relationship **one-to-one** - for a single session there exists at most one neuron."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In contrast, `Mouse` and `Session` tables are connected by a thin line, indicating that `Mouse` alone does **not** uniquely identify an entry in `Session` - in other words, `Session` table has one or more additional primary key attributes that are needed together with mouse identity to uniquely identify a single session.\n",
    "\n",
    "Having designed the table, we know that you need to know both the **mouse** and the **session_date** to uniquely identify a single session. Because of this, you can have more than one session for a mouse as long as they differ in the `session_date`. As you might have guessed, we call this kind of relationship **one-to-many** - for a single mouse, we may have any number of sessions. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Knowing this, can you identify which relationships in the `calcium` pipelines are **one-to-one** vs **one-to-many**?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lookup tables to hold parameters for analysis/processing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In our pipeline, we have defined `SpikeDetectionParam` as a `Lookup` table to hold parameters for the spike detection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.ERD(SpikeDetectionParam) + Neuron + Spikes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Whenever you define a computed table (such as `Spikes`, detecting and storing spikes), you will often see that the computation depends on one or more **parameters** that can strongly influence the result of your computations - and therefore the resulting entries in the computed table.\n",
    "\n",
    "Example of such paramters include:\n",
    "* spike detection threshold during spike detection\n",
    "* spike detection method\n",
    "* segmentation method for fluorescent image segmentation\n",
    "* ridge regression regularizer weight"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you implement your `Computed` table's `make` using a fixed value of a parameter, you will have to delete the table entry and recompute everytime you want to try out new value for the parameter. Not only is this cumbersome, but it makes it difficult for you to do side-by-side comparisons of the results based on different parameter values.\n",
    "\n",
    "It is therefore recommended for you to \"break-out\" the parameters for your computation into a separate `Lookup` table and have your computed table depend on it. This allows you to compute and store the computation results **for every possible combination of data and parameter**, just like we saw it for the case of `Spikes` table!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Breaking tables apart"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Early in your data pipeline design experience, you may have an urge to define *a bit table* that contains all information in one place. For example, you might want to define a table that effectively combines our `Mouse`, `Session` and `Neuron` table! Your table may look something like:\n",
    "\n",
    "| mouse_id | dob        | sex     | session_date | experiment_setup | experimenter    | activity |\n",
    "|:--------:|------------|---------|--------------|------------------|-----------------|----------|\n",
    "| 0        | 2017-03-01 | M       | 2017-05-15   | 0                | Edgar Y. Walker | =BLOB=   |\n",
    "| 0        | 2017-03-01 | M       | 2017-05-19   | 0                | Edgar Y. Walker | =BLOB=   |\n",
    "| 1        | 2016-11-19 | M       |              |                  |                 |          |\n",
    "| 2        | 2016-11-20 | unknown |              |                  |                 |          |\n",
    "| 5        | 2016-12-15 | F       | 2017-01-05   | 1                | Fabian Sinz     | =BLOB=   |\n",
    "| 10       | 2017-01-01 | F       |              |                  |                 |          |\n",
    "| 11       | 2017-01-03 | F       |              |                  |                 |          |\n",
    "| 100      | 2017-05-12 | F       | 2017-05-25   | 100              | Jacob Reimer    | =BLOB=   |\n",
    "| 100      | 2017-05-12 | F       | 2017-06-01   | 1                | Jacob Reimer    | =BLOB=   |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While you may first think that this is better because **everything is present in one place**, you will quickly notice that this kind of table harbors many issues:\n",
    "\n",
    "1. notice that you now have columns with a lot of duplication. For example, the `dob` and `sex` columns gets repeated for every recording session you perform on that animal.\n",
    "2. some rows now have \"empty\" columns (`session_date`, etc) to indicate that the animal does not have a session recorded yet.\n",
    "3. It is no longer clear **what exactly each row represents**. Not every row corresponds to a valid experiment session because some animals lack session. Nor is the table a good listing of the animals due to duplication.\n",
    "\n",
    "Hopefully you can see that working with tables like above ends up requiring additional parsing to be done before you can get to the information you actually want - and that's not fun!\n",
    "\n",
    "Also note that this kind of tables necessitates you to think in terms of individual attributes (columns), and you can easily get lost in the mess."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One key design principle for DataJoint data pipeline is to make sure that each table concretely represent a category of \"things\" of \"entities\". \n",
    "\n",
    "This allows you to identify each *row* of a table as representing an well-defined entity - such as mouse, session, neuron, or spikes. When you form queries, you also don't have to think about the columns much but think much more in terms of the entities you work with - something you do naturally already!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Joining tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the first session, we saw that we can actually combine multiple tables into one using the **join** `*` operator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse * Session * Neuron"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You might wonder what is different between this joined table and the big table above. If you look carefully, the result of joins do not contain \"missing\" entries. For example, `mouse_id = 1` is no where to be found.\n",
    "\n",
    "The best way to understand this is to see the result of the join operations are representing **all valid combinations** of entities. Any single entry in the resulting table represents a valid combination of mouse, session and neuron - and there is no missing information. Also note that each such combination is fully identifiable by the combination of the primary key attributes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Query challenges"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now you are armed with a deeper appreciation of the pipeline you have already created, let's learn to dissect and explore your pipelines by learning to build queries to answer questions. We will start simple, and build up in the query complexity!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Which spike set had spike counts of 25 or more?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Spikes & 'count >= 25'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Which neurons had spike counts of 25 or more?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Neuron & (Spikes & 'count >= 25')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Which mouse had neurons with spike counts of 25 or more?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & (Spikes & 'count >= 25')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What are the statistics for neurons with spike counts of 25 or more?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ActivityStatistics & (Spikes & 'count >= 25')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What are the statistics for neurons from female mice?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ActivityStatistics & (Mouse & 'sex = \"F\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Show me spikes for mouse born after April 10, 2017 using threshold larger than 0.5."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Spikes & (Mouse & 'dob > \"2017-04-10\"') & (SpikeDetectionParam & 'threshold > 0.5')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Show me spikes for mouse born after April 10, 2017 using threshold larger than 0.5. Include the DOB and actual threshold in the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(Spikes * Mouse * SpikeDetectionParam) & 'dob > \"2017-04-10\"' & 'threshold > 0.5'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Multiple values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can check that an attribute values **matches one of many values** using `table & 'attr in (\"a\", \"b\", \"c\")'`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find all mouse that is either male or unknown"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & 'sex in (\"M\", \"unknown\")'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using pattern matching"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### All mouse born in 2016"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & 'dob like \"2016%%\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### All neurons recorded in May"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Neuron & (Session & 'session_date like \"%%-05-%%\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using antijoin `-`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**antijoin** operator `-` finds all entries in the left tables that does **not** match restrictions on the right."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List all mouse that doesn't have experiment sessions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse - Session"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using non-primary key attributes from two tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sessions recorded on mouse that was at least 15 days old"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hint: use `DATEDIFF` function to calculate difference between dates in number of days."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse * Session & 'DATEDIFF(session_date, dob) >= 15'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sessions recorded on mouse that less than 15 days old"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse * Session & 'DATEDIFF(session_date, dob) < 15'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### All mouse that has been recorded by Jacob Reimer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & (Session & 'experimenter = \"Jacob Reimer\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Advanced: computing new column on the fly"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes, we would want to compute a new column value for use in subsequent query. For example, you might want to find out the age of all mouse and query based on it.\n",
    "\n",
    "You can achieve this using the projection operator `proj` to compute a new field."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Age of mouse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse.proj(age=\"DATEDIFF(NOW(), dob)\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}