02-Imported and Computed Tables.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Julia and DataJoint\n",
    "\n",
    "This notebook is a minimal translation into Julia from the Python tutorial notebook [02-Imported and Computed Tables\n",
    "](../02-Imported%20and%20Computed%20Tables.ipynb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preliminary Set up\n",
    "\n",
    "This first cell below simply recapitulates what was done in tutorials [00-ConnectingToDatabase.ipynb](./00-ConnectingToDatabase.ipynb) and [01-Getting_started_with_DataJoint.ipynb](./01-Getting%20started%20with%20DataJoint.ipynb). If you haven't gone through those yet, go read them first.\n",
    "\n",
    "If you have already gone through those previous tutorials, then you can just run the cell below to set up and then proceed to the rest of the tutorial"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "###########################\n",
    "#\n",
    "# This code starts from scratch to connect to the database and set up and populate some tables, \n",
    "# to be used in the Julia tutorial Jupyter notebook \"02-Imported and Computed Tables.ipynb\"\n",
    "#\n",
    "# We assume that you have an appropriately configures dj_local_conf.json file in your working directory\n",
    "#\n",
    "###########################\n",
    "\n",
    "\n",
    "using PyCall; println(\"... Loaded PyCall\")\n",
    "cd(\"/Users/carlos/Github/datajoint/neuronex_workshop_2018/julia\")  # Insert your own working directory here! \n",
    "# Your directory should have the DataJoint2Julia directory, with the corresponding module contents in it.\n",
    "\n",
    "push!(LOAD_PATH, \".\")\n",
    "using DataJoint2Julia; println(\"... Loaded DataJoint2Julia\")\n",
    "\n",
    "# RUN THE FOLLOWING ONLY ONCE:  Next time tou start up tou won't need it, the info will be saved in the local file\n",
    "#\n",
    "# dj.config.__setitem__(\"database.host\", \"datajoint00.pni.princeton.edu\")\n",
    "# dj.config.__setitem__(\"database.user\", \"YOUR PU ID\")\n",
    "# dj.config.__setitem__(\"database.password\", \"YOUR PU PASSWORD\")\n",
    "# dj.config.save_local()\n",
    "\n",
    "dj.conn(reset=true)\n",
    "\n",
    "schema_name = \"brody_tutorial3\"\n",
    "schema = dj.schema(schema_name) # , py\"locals()\")\n",
    "\n",
    "\n",
    "@pydef mutable struct Mouse <: dj.Manual\n",
    "    definition = \"\"\"\n",
    "      mouse_id: int                  # unique mouse id\n",
    "      ---\n",
    "      dob: date                      # mouse date of birth\n",
    "      sex: enum('M', 'F', 'U')       # sex of mouse - Male, Female, or Unknown/Unclassified\n",
    "      \"\"\"\n",
    "end\n",
    "    \n",
    "# Julia doesn't have decorators, so we do the @schema decoration as follows:\n",
    "Mouse = d2jDecorate(Mouse, schema)\n",
    "\n",
    "mouse = Mouse()\n",
    "println(\"... Defined Mouse\")\n",
    "\n",
    "mouse.insert([\n",
    "        Dict(\"mouse_id\"=>0,   \"dob\"=>\"2010-01-01\", \"sex\"=>\"M\"), \n",
    "        Dict(\"mouse_id\"=>1,   \"dob\"=>\"2020-01-01\", \"sex\"=>\"M\"), \n",
    "        Dict(\"mouse_id\"=>2,   \"dob\"=>\"2020-01-02\", \"sex\"=>\"F\"),\n",
    "        Dict(\"mouse_id\"=>3,   \"dob\"=>\"2020-01-03\", \"sex\"=>\"U\"),\n",
    "        Dict(\"mouse_id\"=>5,   \"dob\"=>\"2020-01-05\", \"sex\"=>\"M\"), \n",
    "        Dict(\"mouse_id\"=>6,   \"dob\"=>\"2020-01-05\", \"sex\"=>\"F\"),\n",
    "        Dict(\"mouse_id\"=>7,   \"dob\"=>\"2020-01-05\", \"sex\"=>\"F\"),\n",
    "        Dict(\"mouse_id\"=>8,   \"dob\"=>\"2018-01-05\", \"sex\"=>\"U\"),\n",
    "        Dict(\"mouse_id\"=>100, \"dob\"=>\"2017-01-05\", \"sex\"=>\"F\")\n",
    "        ], skip_duplicates=true)\n",
    "\n",
    "\n",
    "@pydef mutable struct Session <: dj.Manual\n",
    "    definition = \"\"\"\n",
    "    # Experiment session\n",
    "    -> Mouse\n",
    "    session_date               : date                         # date\n",
    "    ---\n",
    "    experiment_setup           : int                          # experiment setup ID\n",
    "    experimenter               : varchar(100)                 # experimenter name\n",
    "    \"\"\"\n",
    "end\n",
    "Session = d2jDecorate(Session, schema)\n",
    "\n",
    "session = Session()\n",
    "println(\"... Defined Session\")\n",
    "\n",
    "data = Dict(\n",
    "  \"mouse_id\" => 0,\n",
    "  \"session_date\" => \"2017-05-15\",\n",
    "  \"experiment_setup\" => 0,\n",
    "  \"experimenter\" => \"Edgar Y. Walker\"\n",
    ")\n",
    "\n",
    "session.insert1(data, skip_duplicates=true)\n",
    "\n",
    "\n",
    "data = [\n",
    "    Dict(\n",
    "  \"mouse_id\" => 0,\n",
    "  \"session_date\" => \"2017-05-19\",\n",
    "  \"experiment_setup\" => 0,\n",
    "  \"experimenter\" => \"Boaty McBoatFace\"\n",
    "    ), \n",
    "    Dict(\n",
    "  \"mouse_id\" => 100,\n",
    "  \"session_date\" => \"2017-05-25\",\n",
    "  \"experiment_setup\" => 0,\n",
    "  \"experimenter\" => \"Boaty McBoatFace\"\n",
    "    ),\n",
    "    Dict(\n",
    "  \"mouse_id\" => 5,\n",
    "  \"session_date\" => \"2017-01-05\",\n",
    "  \"experiment_setup\" => 0,\n",
    "  \"experimenter\" => \"Boaty McBoatFace\"\n",
    "    )\n",
    "\n",
    "    ]\n",
    "\n",
    "session.insert(data, skip_duplicates=true)\n",
    "\n",
    "session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.ERD(schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working with automated computations: Imported and Computed tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Welcome back! In this session, we are going to continue working with the pipeline for the mouse electrophysiology example. \n",
    "\n",
    "In this session, we will learn to:\n",
    "\n",
    "* import neuron activity data from data files into an `Imported` table\n",
    "* compute various statistics for each neuron by defining a `Computed` table\n",
    "* define a `Lookup` table to store parameters for computation\n",
    "* define another `Computed` table to perform spike detection and store the detected spikes\n",
    "* automatically trigger computations for all missing entries with `populate`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First things first, run the **SETUP** cell above, which will connect us to the database, define our `schema` and set up our `Mouse` and `Session` tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mouse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Importing data from data files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Recall from the project description\n",
    "> * In each experimental session, you record electrical activity from a single neuron. You use recording equipment that produces separate data files for each neuron you recorded.\n",
    "\n",
    "Our recording equipment produces a data file for each neuron recorded. Since we record from one neuron per session, there should be one data file for each session."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the `data` directory, you will find `.npy` (saved NumPy array) files with names like `data_100_2017-05-25.npy`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you might have guessed, these are the data for the recording sessions in the `Session` table, and each file are named according to the `mouse_id` and `session_date` - the attributes of the primary keys - in the format `data_{mouse_id}_{session_date}.npy`.\n",
    "\n",
    "So `data_100_2017-05-25.npy` is the data for session identified by `mouse_id = 100` and `session_date = \"2017-05-25\"`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Looking at the data "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a quick peak at the data file content."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let's pick a session to load the data for. To do this we are going to first fetch the **primary key attributes** of `Session` as a list of dictionaries. We make use of the special `jfetch('KEY')` syntax to achieve this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nkeys = session.jfetch(\"KEY\")\n",
    "nkeys"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Any item in this list of keys can be used to uniquely identify a single session!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session & nkeys[2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take the first key, and generate the file name that corresponds to this session. Remember the `data_{mouse_id}_{session_date}.npy` filename convention!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "key = nkeys[1]\n",
    "key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filename = \"../data/data_$(key[\"mouse_id\"])_$(key[\"session_date\"]).npy\"\n",
    "filename"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, let's load the file.  We'll use the NPZ package to read Python NumPy files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "using NPZ\n",
    "\n",
    "data = npzread(filename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "size(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So this particular file contains an array of 1000 floating point numbers. This represents a (simulated) recording of raw electric activity from a single neuron over 1000 time bins."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Defining the table for recorded neurons"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now would like to have all these recorded `Neuron` represented and stored in our data pipeline.\n",
    "\n",
    "Since we only record a single neuron from each session, a `Neuron` can be uniquely identified by knowing the `Session` it was recorded in. For each `Neuron`, we want to store the neural activity found in the data file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@pydef mutable struct Neuron <: dj.Imported\n",
    "    definition = \"\"\"\n",
    "    -> Session\n",
    "    ---\n",
    "    activity: longblob    # electric activity of the neuron\n",
    "    \"\"\"\n",
    "end\n",
    "Neuron = d2jDecorate(Neuron, schema)\n",
    "    \n",
    "neuron = Neuron()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We defined `activity` as a `longblob` so that it can store a numeric array holding the electric activity over time. This numeric array will be imported from the file corresponding to each neuron."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that our `Neuron` class inherits from `dj.Imported` instaed of `dj.Manual` like others. This is because **this table's content will depend on data imported from an external file**. The `Manual` vs `Imported` are said to specify the **tier of the table**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataJoint table tiers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In DataJoint, the tier of the table indicates **the nature of the data and the data source for the table**. So far we have encountered two table tiers: `Manual` and `Imported`, and we will encounter the two other major tiers in this session. \n",
    "\n",
    "DataJoint tables in `Manual` tier, or simply **Manual tables** indicate that its contents are **manually** entered by either experimenters or a recording system, and its content **do not depend on external data files or other tables**. This is the most basic table type you will encounter, especially as the tables at the beggining of the pipeline. In the ERD, `Manual` tables are depicted by green rectangles.\n",
    "\n",
    "On the other hand, **Imported tables** are understood to pull data (or *import* data) from external data files, and come equipped with functionalities to perform this importing process automatically, as we will see shortly! In the ERD, `Imported` tables are depicted by blue ellipses."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check the state of our pipeline."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.ERD(schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing data into the `Imported` table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rather than filling out the content of the table manually using `insert1` or `insert` methods, we are going to make use of the `make` and `populate` logic that comes with `Imported` tables to automatically figure out what needs to be imported and perform the import!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `make` and `populate` methods"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`Imported` table comes with a special method called `populate`. Let's try calling it. This will cause an error, which we will explain below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "neuron.populate()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that `populate` call complained that a method called `make` is not implemented. Let me show a simple `make` method that will help elucidate what this is all about."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@pydef mutable struct Neuron <: dj.Imported\n",
    "    definition = \"\"\"\n",
    "    -> Session\n",
    "    ---\n",
    "    activity: longblob    # electric activity of the neuron\n",
    "    \"\"\"\n",
    "    \n",
    "    function make(self, key)\n",
    "        println(\"key is \", key)\n",
    "    end\n",
    "end\n",
    "\n",
    "Neuron = d2jDecorate(Neuron, schema)\n",
    "\n",
    "neuron = Neuron()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's call `populate` again!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "neuron.populate()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When you call `populate` on an `Imported` table, this triggers DataJoint to look up all tables that the `Imported` table depends on.\n",
    "\n",
    "For **every unique combination of entries in the depended or \"parent\" tables**, DataJoint calls `make` function, passing in the primary key of the parent(s)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because `Neuron` depends on `Session`, `Neuron`'s `make` method was called for each entry of `Session`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that `make` only receives the *primary key attributes* of `Session` (`mouse_id` and `session_date`) but not the other attributes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Implementing `make`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have a better understanding of `make`, let's implement `make` to perform the importing of data from file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "using NPZ\n",
    "\n",
    "@pydef mutable struct Neuron <: dj.Imported\n",
    "    definition = \"\"\"\n",
    "    -> Session\n",
    "    ---\n",
    "    activity: longblob    # electric activity of the neuron\n",
    "    \"\"\"\n",
    "    \n",
    "    function make(self, key)\n",
    "        filename = \"../data/data_$(key[\"mouse_id\"])_$(key[\"session_date\"]).npy\"\n",
    "        println(\"filename is \", filename)\n",
    "        key[\"activity\"] = npzread(filename)\n",
    "        self.insert1(key)\n",
    "        println(\"    data from file inserted.\")\n",
    "    end\n",
    "end\n",
    "Neuron = d2jDecorate(Neuron, schema)\n",
    "\n",
    "neuron = Neuron()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that we added the missing attribute information `activity` into the `key` dictionary, and finally **inserted the entry** into `self` = `Neuron` table. The `make` method's job is to create and insert a new entry corresponding to the `key` into this table!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, let's go ahead and call `populate` to actually populate the `Neuron` table, filling it with data loaded from data files!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "neuron.populate()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What happens if we call `neuron.populate` again?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "neuron.populate()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's right - nothing! This makes sense, because we have imported `Neuron` for all entries in `Session` and nothing is left to be imported."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# If you want to empty the table Neuron, so\n",
    "# as to see the effect of populating it anew again, run the followin:\n",
    "#\n",
    "# neuron.delete()\n",
    "# "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now what happens if we insert a new entry into `Session`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.insert1(Dict(\n",
    "    \"mouse_id\" => 100,\n",
    "    \"session_date\" => \"2017-06-01\",\n",
    "    \"experiment_setup\" => 1,\n",
    "    \"experimenter\"=> \"Jacob Reimer\"\n",
    "    ), skip_duplicates=true)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can find all `Session` without corresponding `Neuron` entry with the **antijoin operator** `-`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# select all Session entries *without* a corresponding entry in Neuron\n",
    "session - neuron"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "neuron.populate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "neuron"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Computations in data pipeline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have successfully imported all data we have into our pipeline, it's time for us to start analyzing them! \n",
    "\n",
    "When you perform computations in the DataJoint data pipeline, you focus and design tables in terms of **what** is it that you are computing rather than the **how**. You should think in terms of the \"things\" that you are computing!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's say that we want to compute the satististics, such as mean, standard deviation, and maximum value of each of our neuron's activity traces. Hence we want to compute the neuron's **activity statistics** for each neuron!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So the new \"thing\" or entity here is `ActivityStatistics`, where each entry corresponds to the statistics of a single `Neuron`. Let's start designing the table, paying special attention to the dependencies."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Statistics of neuron activities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we create the table to store the statistics, let's think about how we might go about computing interesting statistics for a single neuron.\n",
    "\n",
    "Let's start by fetching one neuron to work with."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "neuron = Neuron()\n",
    "mykeys = neuron.jfetch(\"KEY\")\n",
    "\n",
    "# pick one key\n",
    "key = mykeys[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "neuron & key"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets go ahead and grab the `activity` data stored as numpy array. As we learned in the last session, we can `fetch` it! "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "activity = (neuron & key).jfetch(\"activity\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's a bit subtle, but `jfetch` returns an array of the attribute (one element per fetched row), even if the attribute contains just one numeric array. So here, we actually got an array containing a numeric array. We can of course just index into it,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "activity[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "but if we knew that there was only one item, we can use `jfetch1` instead to save some trouble"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "activity = (neuron & key).jfetch1(\"activity\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can compute some statistics:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "using Statistics\n",
    "\n",
    "mean(activity)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "std(activity)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "maximum(activity)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This gives us a good idea on how to:\n",
    "1. fetch the activity of a neuron knowing its primary key, and\n",
    "2. compute interesting statistics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Armed with this knowledge, let's go ahead and define the table for `ActivityStatistics`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Defining `ActivityStatistics` table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's go ahead and work out the definition of the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@pydef mutable struct ActivityStatistics <: dj.Computed\n",
    "    definition = \"\"\"\n",
    "    -> Neuron\n",
    "    ---\n",
    "    mean: float    # mean activity\n",
    "    stdev: float   # standard deviation of activity\n",
    "    max: float     # maximum activity\n",
    "    \"\"\"\n",
    "end\n",
    "\n",
    "\n",
    "ActivityStatistics = d2jDecorate(ActivityStatistics, schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Did you notice that we are now inheriting from `dj.Computed`?  `Computed` is yet another table tier that signifies that **the entries of this table are computed using data in other tables**. `Computed` tables are represented as red circles in the ERD."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.ERD(schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just like the `Imported` tables, `Computed` tables make use of the same `make` and `populate` logic for defining new entries in the table. Let's go ahead and implement `make` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "activity = d2j((Neuron() & key).jfetch1(\"activity\"))\n",
    "using Statistics\n",
    "println(\"std is \", std(activity))\n",
    "\n",
    "println(\"Computed statistics for mouse_id \", key[\"mouse_id\"], \", session_date \", key[\"session_date\"])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "using Statistics\n",
    "\n",
    "function Activity_Statistics_make(self, key)\n",
    "\n",
    "end\n",
    "\n",
    "@pydef mutable struct ActivityStatistics <: dj.Computed\n",
    "    definition = \"\"\"\n",
    "    -> Neuron\n",
    "    ---\n",
    "    mean: float    # mean activity\n",
    "    stdev: float   # standard deviation of activity\n",
    "    max: float     # maximum activity\n",
    "    \"\"\"\n",
    "    \n",
    "    function make(self, key)\n",
    "            activity = d2j((Neuron() & key).jfetch1(\"activity\"))    # fetch activity as Float64 array        \n",
    "            # Note that we used py\"Neuron\"() instead of the variable neuron so as not to depend on that \n",
    "            # variable having been instantiated. We do depend on that table having been defined in the Python\n",
    "            # environment, that's inevitable.\n",
    "\n",
    "            # compute various statistics on activity\n",
    "            key[\"mean\"]  =  mean(activity)               # compute mean\n",
    "            key[\"stdev\"] =  std(activity)                # compute standard deviation\n",
    "            key[\"max\"]   =  maximum(activity)            # compute max\n",
    "            self.insert1(key)\n",
    "            println(\"Computed statistics for mouse_id \", key[\"mouse_id\"], \", session_date \", key[\"session_date\"])    \n",
    "    end\n",
    "end\n",
    "\n",
    "ActivityStatistics = d2jDecorate(ActivityStatistics, schema)\n",
    "astats = ActivityStatistics()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's go ahead and populate the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "astats.populate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "astats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Voila!! You have computed statistics for each neuron activity!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Spike detection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's go ahead and tackle a more challenging computation. While having raw neural traces in itself can be quite interesting, nothing is as exciting as spikes! Let's take a look at the neurons activities and plot them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get all keys\n",
    "nkeys = neuron.jfetch(\"KEY\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fetch all activities - returned as an array of numeric arrays\n",
    "activities = d2j((neuron & nkeys).jfetch(\"activity\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "using PyPlot \n",
    "\n",
    "fig, axs = subplots(1, length(activities), figsize=(16, 4))\n",
    "for pair in zip(activities, axs[:])\n",
    "    activity = pair[1]\n",
    "    ax       = pair[2]\n",
    "    ax.plot(activity')\n",
    "    ax.set_xlabel(\"Time\")\n",
    "    ax.set_ylabel(\"Activity\")\n",
    "end\n",
    "\n",
    "fig.tight_layout()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now focus on one trace instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "activity = (Neuron() & key).jfetch1(\"activity\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plot(activity)\n",
    "xlabel(\"Time\")\n",
    "ylabel(\"Activity\")\n",
    "xlim([0, 300])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Perhaps we can use threshold to detect when a spike occurs. Threshold of `0.5` may be a good start."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "threshold = 0.5\n",
    "\n",
    "# find activity above threshold\n",
    "above_thrs = convert(Array{Int64}, activity .> threshold)  \n",
    "\n",
    "plot(activity)\n",
    "plot(above_thrs)\n",
    "xlabel(\"Time\")\n",
    "ylabel(\"Activity\")\n",
    "xlim([0, 300])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We want to find out **when** it crossed the threshold. That is, find time bins where `above_thrs` goes from 0 (`False`) to 1 (`True`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spikes = findall(diff(above_thrs) .> 0)   # find places where above_thrs took a step up\n",
    "\n",
    "plot(activity)\n",
    "plot(above_thrs)\n",
    "plot(spikes, ones(size(spikes)), \"ro\")  # a red point at the time of each spike\n",
    "xlim([0, 300])\n",
    "\n",
    "xlabel(\"Time\")\n",
    "ylabel(\"Activity\")\n",
    "xlim([0, 300])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, let's also compute the spike counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "count = length(spikes)   # compute total spike counts\n",
    "count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is our complete spike detection algorithm:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "threshold = 0.5\n",
    "\n",
    "# find activity above threshold\n",
    "above_thrs = convert(Array{Int64}, activity .> threshold)  \n",
    "\n",
    "spikes = findall(diff(above_thrs) .> 0)   # find places where above_thrs took a step up\n",
    "\n",
    "plot(activity)\n",
    "plot(above_thrs)\n",
    "plot(spikes, ones(size(spikes)), \"ro\")  # a red point at the time of each spike\n",
    "xlim([0, 300])\n",
    "\n",
    "xlabel(\"Time\")\n",
    "ylabel(\"Activity\")\n",
    "xlim([0, 300])\n",
    "\n",
    "count = length(spikes)\n",
    "title(\"Total spike counts: $count\");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now notice that the exact spikes you detect depend on the value of the `threshold`. Therefore, the `threshold` is a parameter for our spike detection computation. Rather than fixing the value of the threshold, we might want to try different values and see what works well.\n",
    "\n",
    "In other words, you want to compute `Spikes` for a **combination** of `Neuron`s and different `threshold` values. To do this while still taking advantage of the `make` and `populate` logic, you would want to define a table to house parameters for spike detection in a `Lookup` table!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Parameter `Lookup` table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's define `SpikeDetectionParam` table to hold different parameter configuration for our spike detection algorithm. We are going to define this table as a `Lookup` table, rather than a `Manual` table. By now, you know that `Lookup` must be yet another **table tier** in DataJoint. `Lookup` tables are depicted by gray boxes in the ERD.\n",
    "\n",
    "This tier indicates that the table will contain information:\n",
    "* that will be referenced by other tables\n",
    "* that doesn't change much - usually contains a few pre-known entries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@pydef mutable struct SpikeDetectionParam <: dj.Lookup\n",
    "    definition = \"\"\"\n",
    "    sdp_id: int      # unique id for spike detection parameter set\n",
    "    ---\n",
    "    threshold: float   # threshold for spike detection\n",
    "    \"\"\"\n",
    "end\n",
    "\n",
    "SpikeDetectionParam = d2jDecorate(SpikeDetectionParam, schema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.ERD(schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Defining `Spikes` table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's take everything together and define the `Spikes` table. Here each entry of the table will be *a set of spikes* for a single neuron, using a particular value of the `SpikeDetectionParam`. In other words, any particular entry of the `Spikes` table is determined by **a combination of a neuron and spike detection parameters**.\n",
    "\n",
    "We capture this by depending on both `Neuron` and `SpikeDetectionParam`. For each spike set, we want to store the detected spikes and the total number of spikes. The table definition will look something like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@pydef mutable struct Spikes <: dj.Computed\n",
    "    definition = \"\"\"\n",
    "    -> Neuron\n",
    "    -> SpikeDetectionParam\n",
    "    ---\n",
    "    spikes: longblob     # detected spikes\n",
    "    count: int           # total number of detected spikes\n",
    "    \"\"\"\n",
    "end\n",
    "\n",
    "Spikes = d2jDecorate(Spikes, schema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.ERD(schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the ERD, we see that `Spikes` is a computed table (red circle) that depends on **both Neuron and SpikeDetectionParam**. Finally, let's go ahead and implement the `make` method for the `Spikes` table. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@pydef mutable struct Spikes <: dj.Computed\n",
    "    definition = \"\"\"\n",
    "    -> Neuron\n",
    "    -> SpikeDetectionParam\n",
    "    ---\n",
    "    spikes: longblob     # detected spikes\n",
    "    count: int           # total number of detected spikes\n",
    "    \"\"\"\n",
    "\n",
    "    function make(self, key)\n",
    "        println(\"Populating for: \", key)\n",
    "\n",
    "        activity = (Neuron() & key).fetch1(\"activity\")\n",
    "        threshold = (SpikeDetectionParam() & key).fetch1(\"threshold\")\n",
    "\n",
    "        above_thrs = convert(Array{Int64}, activity > threshold)  \n",
    "        spikes = findall(diff(above_thrs) .> 0)   # find places where above_thrs took a step up\n",
    "\n",
    "        count = length(spikes)   # compute total spike counts\n",
    "        println(\"Detected $count spikes!\")\n",
    "\n",
    "        # save results and insert\n",
    "        key[\"spikes\"] = spikes\n",
    "        key[\"count\"]  = count\n",
    "        self.insert1(key)\n",
    "    end\n",
    "end\n",
    "\n",
    "Spikes = d2jDecorate(Spikes, schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The implementation of the spike detection is pretty much what we had above, except that we now fetch the value of `threshold` from the `SpikeDetectionParam` table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looking at the `Spikes` table, we see that it indeed inherits the primary key attributes from **both Neuron (`mouse_id`, `session_date`) and SpikeDetectionParam (`sdp_id`)**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spikes = Spikes()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Populating `Spikes` table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are now ready to populate! When we call `populate` on `Spikes`, DataJoint will automatically call `make` on **every valid combination of the parent tables - Neuron and SpikeDetectionParam**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spikes.populate()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hm... `populate` doesn't seem to be doing anything... What could be the cause?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looking at `SpikeDetectionParam` reveals the issue:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SpikeDetectionParam()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's right! We have not added a detection parameter set yet. Let's go ahead and add one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SpikeDetectionParam.insert1((0, 0.5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SpikeDetectionParam()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we should really be ready to perform the computation..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spikes.populate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spikes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "...and we now have spike detection running!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Trying out other parameter values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see how different thresholds affect the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SpikeDetectionParam.insert1((1, 0.9))  # add another threshold"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SpikeDetectionParam()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spikes.populate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spikes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can see that the results of spike detection under different parameter settings can live happily next to each other, without any confusion as to what is what."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deleting entries \"upstream\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's say that we decided that we don't like the first spike threshold of `0.5`. While there is really nothing wrong keeping those results around, you might decide that you'd rather delete all computations performed with that threshold to keep your tables clean."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While you can restrict `Spikes` table to the specific parameter id (i.e. `sdp_id = 0`) and delete the entries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(spikes & \"sdp_id = 0\").delete()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can simply delete the unwanted paramter from the `SpikeDetectionParam` table, and let DataJoint cascade the deletion:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SpikeDetectionParam() & \"sdp_id = 0\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(SpikeDetectionParam() & \"sdp_id = 0\").delete()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spikes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Congratulations! You have successfully extended your pipeline with a table to represent recorded data (`Neuron` as `Imported` table), tables that performs and represents computation results (`ActivityStatistics` and `Spikes` as `Computed` tables) and a table to hold computation parameters (`SpikeDetectionParam` as `Lookup` table)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.ERD(schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our pipeline is still fairly simple but completely capable of handling analysis!\n",
    "\n",
    "In the next session, we are going to revisit some of the **design patterns** that were used when designing our pipeline. We will also tackle some more query challenges to horn in our DataJoint querying skills."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Inserting arbitrary data types into the database\n",
    "\n",
    "Complicated data types get inserted as MariaDB \"BLOB\"s.  Let's define an example table `Doodle` that we'll use to demonstrate that. To do it. you have to set the following flag in config:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.config.__setitem__(\"enable_python_native_blobs\", true)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@pydef mutable struct Doodle <: dj.Manual\n",
    "    definition = \"\"\"\n",
    "    id :       int\n",
    "    ---\n",
    "    activity: longblob    # electric activity of the neuron\n",
    "    \"\"\"\n",
    "end\n",
    "\n",
    "Doodle = d2jDecorate(Doodle, schema)\n",
    "\n",
    "doodle = Doodle()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now insert a dictionary, whose entries are all kinds of things-- strings, arrays, other integers..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "doodle.insert1(Dict(\"id\"=>2, \"activity\"=> [\"this\", 20, randn(1,5), Dict(\"bauble\"=>\"trash\")]), skip_duplicates=true)\n",
    "doodle"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`jfetch()` converts the BLOB back into the appropriate data type:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x = doodle.jfetch()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "println(x[1])\n",
    "for i=1:length(x[2])\n",
    "    println(x[2][i])\n",
    "end"
   ]
  }
 ],
 "metadata": {
  "@webio": {
   "lastCommId": null,
   "lastKernelId": null
  },
  "kernelspec": {
   "display_name": "Julia 1.0.5",
   "language": "julia",
   "name": "julia-1.0"
  },
  "language_info": {
   "file_extension": ".jl",
   "mimetype": "application/julia",
   "name": "julia",
   "version": "1.0.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}