08-Organizing data with DataJoint.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Organizing data into a data pipeline with DataJoint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we are going to take a very quick look at how you can create, organize, and even run computations inside a **data pipeline** built using a Python (and MATLAB) package called **DataJoint**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NOTE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook is kept intentionally succinct and a lot of details about DataJoint is explained only minimally. Interested students who wish to learn more about DataJoint are referred to the [tutorials](https://tutorials.datajoint.io) as well as on [DataJoint two-days workshop](https://github.com/datajoint/neuronex_workshop_2018) with accompanying videos."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once you have successfully installed DataJoint, you can go ahead and import the package just like for NumPy and Matplotlib. The convention is to import `datajoint as dj`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datajoint as dj\n",
    "from getpass import getpass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.config['database.user'] = 'eywalker' # be sure to replace this with your username\n",
    "dj.config['database.host'] = 'tutorial-db.datajoint.io'\n",
    "dj.config['database.password'] = getpass('Enter your password: ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.conn()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# So... What is a data pipeline?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A data pipeline is a listing or a \"map\" of various \"things\" that you work with in a project, with line connecting things to each other to indicate their dependecies. The \"things\" may include anything from mouse, experimenter, equipment, to experiment session, trial, two-photon scans, electric activities, to receptive fields, neuronal spikes, to figures for a publication! \n",
    "\n",
    "\n",
    "A data pipeline gives you a framework to:\n",
    "\n",
    "1. define these \"things\" as tables in which you can store the information about them\n",
    "2. define the relationships (in particular the dependencies) between the \"things\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's go ahead and build together a pipeline from scratch to better understand what a data pipeline is all about."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Building our first pipeline: single electrode recording from mouse"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's build a pipeline to collect, store and process data and analysis for our hypothetical single electrode recording study in mice. To help us understand the project better, here is a brief description:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> * Your lab houses many **mice**, and each mouse is identified by a unique ID. You also want to keep track of information about each mouse such as their date of birth and gender.\n",
    "> * As a hard working neuroscientist, you perform experiments every day, sometimes working with more than one mouse in a day! However, on an any given day, a mouse undergoes at most one recording session.\n",
    "> * For each **experimental session**, you would like to record what mouse you worked with and when you performed the experiment. You would also like to keep track of other helpful information such as the experimental setup you worked on.\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",
    "> * Neuron's activities are recorded as raw traces. **Neuron's spikes** needs to be detected for further analysis to be performed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just by going though the description, we can start to identify **things** that we might want to store and represent in our data pipeline:\n",
    "\n",
    "* mouse\n",
    "* experimental session\n",
    "* neuron\n",
    "* spikes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In DataJoint data pipeline, we represent these **things** as **tables**. Different *kinds* of entities become distinct tables, and each row of the table is a single example (instance) of the category of entity. \n",
    "\n",
    "For example, if we have a `Mouse` table, then each row in the mouse table represents a single mouse!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When constructing such table, we need to figure out what it would take to **uniquely identify** each entry. For example, in constructing a table for mice, we might conclude that each mouse can be uniquely identified by knowing its **mouse ID** - a unique ID number assigned to each mouse in the lab. The mouse ID is then a column in the table or an **attribute** that can be used to **uniquely identify** each mouse. Such attribute is called the **primary key** of the table.\n",
    "\n",
    "| mouse_id* |\n",
    "|:--------:|\n",
    "| 11234    |\n",
    "| 11432    |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have successfully identified the primary key of the table, we can now think about what other columns, or **non-primary key attributes** that we would want to include in the table. These are additional information **about each entry in the table that we want to store**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the case of mouse, what other information about the mouse you might want to store? Based on the project description, we would probably want to store information such as the mouse's **date of birth** and **gender**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| mouse_id* | dob        | sex |\n",
    "|:--------:|------------|--------|\n",
    "| 11234    | 2017-11-17 | M      |\n",
    "| 11432    | 2018-03-04 | F      |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have an idea on how to represent information about mouse, let's create the table using **DataJoint**!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating Mouse table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One or more tables can be grouped together into a **schema**, allowing you to keep related entities together for conceptual clarity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "schema = dj.schema('{}_pipeline'.format(dj.config['database.user']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "schema"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In DataJoint, you define each table as a class, and provide the table definition (e.g. attribute definitions) as the `definition` string property. The class will inherit from the `dj.Manual` class provided by DataJoint."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@schema\n",
    "class Mouse(dj.Manual):\n",
    "    definition = \"\"\"\n",
    "    # Experimental animals\n",
    "    mouse_id             : int                          # Unique animal ID\n",
    "    ---\n",
    "    dob=null             : date                         # date of birth\n",
    "    sex=\"unknown\"        : enum('M','F','unknown')      # sex\n",
    "    \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at our brand new table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once created you can **insert** data into the table. Below I insert information about multiple mice, using various methods of insertion:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert one a time\n",
    "Mouse.insert1((0, '2017-03-01', 'M'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# or insert in batches\n",
    "data = [\n",
    "  (1, '2016-11-19', 'M'),\n",
    "  (2, '2016-11-20', 'unknown'),\n",
    "  (5, '2016-12-25', 'F')\n",
    "]\n",
    "\n",
    "# now insert all at once\n",
    "Mouse.insert(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# inserting as dictionary\n",
    "data = {\n",
    "    'mouse_id': 100,\n",
    "    'dob': '2017-05-12',\n",
    "    'sex': 'F'\n",
    "}\n",
    "\n",
    "Mouse.insert1(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# inserting list of dictionaries\n",
    "\n",
    "data = [\n",
    "  {'mouse_id': 10, 'dob': '2017-01-01', 'sex': 'F'},\n",
    "  {'mouse_id': 11, 'dob': '2017-01-03', 'sex': 'F'},\n",
    "]\n",
    "\n",
    "# insert them all\n",
    "Mouse.insert(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now have multilpe mice in our table!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data integrity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataJoint checks for data integrity, and ensures that you don't insert a duplicate by mistake. When you try to insert an **already existing mouse**, DataJoint will trigger an error!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse.insert1(\n",
    "{'mouse_id': 0,\n",
    " 'dob': '2018-01-01',\n",
    " 'sex': 'M',\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fetching all data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course, information contained in the table can be **fetched** easily for you to work with:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = Mouse.fetch() # get all mouse!!\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also get specific attributes at a time!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# only get the sex of all mouse\n",
    "Mouse.fetch('sex')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DataJoint query language"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataJoint offers very powerful yet intuitive **querying** syntax that let's you select exactly the data you want before you fetch it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Restricting by attribute value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **restriction** operation, `&`, let's you specify the criteria to narrow down the table on the left."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All male mice (`sex = \"M\"'`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & 'sex = \"M\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mouse that is born **after 2017-01-01**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & 'dob > \"2017-01-01\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can easily combine multiple restrictions to narrow down the entities based on multiple attributes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's find all mouse that **is not male** AND **born after 2017-01-01**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & 'sex != \"M\"' & 'dob > \"2017-01-01\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In fact you can **build up query** by chaining together query results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "female_mice = Mouse & 'sex = \"F\"'\n",
    "female_mice"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "and among these mice, find ones with birthday after  \"2017-01-02\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "female_mice & 'dob > \"2017-01-02\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fetch query results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once you have successfully narrowed down to the entities you want, you can fetch the query results just by calling fetch on it!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All male mouse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "male_mouse = Mouse & 'sex = \"M\"'\n",
    "male_mouse"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fetch it!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "male_mouse.fetch()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "or all in one step"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(Mouse & 'sex = \"M\"').fetch()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Building pipeline with dependencies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now take a look at representing an **experimental session**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As with mouse, we should think about **what information (i.e. attributes) is needed to uniquely identify an experimental session**. Here is the relevant section of the project description:\n",
    "\n",
    "> * As a hard working neuroscientist, you perform experiments every day, sometimes working with **more than one mouse in a day**! However, on an any given day, **a mouse undergoes at most one recording session**.\n",
    "> * For each experimental session, you would like to record **what mouse you worked with** and **when you performed the experiment**. You would also like to keep track of other helpful information such as the **experimental setup** you worked on."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Based on the above, it appears that you need to know:\n",
    "\n",
    "* the date of the session\n",
    "* the mouse you recorded from in that session\n",
    "\n",
    "to uniquely identify a single experimental session."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that, to uniquely identify an experimental session (or simply a **session**), we need to know the mouse that the session was about. In other words, a session cannot existing without a corresponding mouse! \n",
    "\n",
    "With **mouse** already represented as a table in our pipeline, we say that the session **depends on** the mouse! We would graphically represent this in an **entity relationship diagram (ERD)** by drawing the line between two tables, with the one below (**session**) dependeing on the one above (**mouse**)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![mouse_sessions](images/mouse_session.svg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In DataJoint, you declare that **session** depends on the mouse, and you specify any additional attribute(s) necessary to uniquely identify a session, such as the **session date**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@schema\n",
    "class 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",
    "    \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can visualize the dependencies of entities (tables) you defined so far by calling `dj.ERD` function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.ERD(schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below we insert a multiple sessions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# session for mouse_id=0\n",
    "data = {\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)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# another session for mouse_id=0 but on a different date\n",
    "data = {\n",
    "  'mouse_id': 0,\n",
    "  'session_date': '2017-05-19',\n",
    "  'experiment_setup': 100,\n",
    "  'experimenter': 'Jacob Reimer'\n",
    "}\n",
    "\n",
    "# insert them all\n",
    "Session.insert1(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# another session done on a different mouse\n",
    "data = {\n",
    "  'mouse_id': 5,\n",
    "  'session_date': '2017-01-05',\n",
    "  'experiment_setup': 101,\n",
    "  'experimenter': 'Jacob Reimer'\n",
    "}\n",
    "\n",
    "# insert them all\n",
    "Session.insert1(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Trying to add information (e.g. a session) for a mouse that doesn't exist will trigger an error!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bad_data = {\n",
    "    'mouse_id': 9999,  # this mouse doesn't exist!\n",
    "    'session_date': '2017-05-15',\n",
    "    'experiment_setup': 0,\n",
    "    'experimenter': 'Edgar Y. Walker'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "Session.insert1(bad_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Querying with multiple tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have two tables, we can perform more exciting queries!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All mouse that has a session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & Session"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find sessions performed on a male mouse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Session & (Mouse & 'sex = \"M\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes you want to see information from multiple tables combined together to be viewed (and queried!) simultaneously. You can do this using the join `*` operator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse * 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": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = np.load('data/data_0_2017-05-15.npy')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Look at its content..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "...and check the shape of the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So this particular file contains a NumPy array of length 1000. This represents a (simulated) recording of raw electric activity from a single neuron over 1000 time bins."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.plot(data)"
   ]
  },
  {
   "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": [
    "@schema\n",
    "class Neuron(dj.Imported):\n",
    "    definition = \"\"\"\n",
    "    -> Session\n",
    "    ---\n",
    "    activity: longblob    # electric activity of the neuron\n",
    "    \"\"\""
   ]
  },
  {
   "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": [
    "We defined `activity` as a `longblob` so that it can store a NumPy array holding the electric activity over time. This NumPy 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": [
    "## Automatically importing data for all sessions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because `Neuron` depends on `Session`, it understands that there may be a `Neuron` per `Session`. Using this information, DataJoint can trigger importing of a `Neuron`'s data from the data file **for each entry in the `Session`**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We define how to **load a Neuron's information for each Session** inside a special method called `make`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@schema\n",
    "class Neuron(dj.Imported):\n",
    "    definition = \"\"\"\n",
    "    -> Session\n",
    "    ---\n",
    "    activity: longblob    # electric activity of the neuron\n",
    "    \"\"\"\n",
    "    def make(self, key):\n",
    "        # key points to a single entry in the Session table\n",
    "        \n",
    "        # use key dictionary to determine the data file path\n",
    "        data_file = \"data/data_{mouse_id}_{session_date}.npy\".format(**key)\n",
    "\n",
    "        data = np.load(data_file)\n",
    "\n",
    "        # add the loaded data as the \"activity\" column\n",
    "        key['activity'] = data\n",
    "\n",
    "        # insert the key into self\n",
    "        self.insert1(key)\n",
    "\n",
    "        print('Populated a neuron for mouse_id={mouse_id} on session_date={session_date}'.format(**key))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now you can **trigger `make` to be run for all `Session`** by simply calling `populate` method!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Neuron.populate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Session()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Neuron()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataJoint has successfully discovered data for all three sessions and **populated** `Neuron` table with the content of the data files!"
   ]
  },
  {
   "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! "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In DataJoint, you can also represent **things** you computed as entries in a table. Here the key is that you think more in terms of **what** rather than **how**!"
   ]
  },
  {
   "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": [
    "Let's go ahead and work out the definition of the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@schema\n",
    "class 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",
    "    \"\"\""
   ]
  },
  {
   "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": [
    "@schema\n",
    "class 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",
    "    def make(self, key):\n",
    "        # each key corresponds to a Neuron\n",
    "        \n",
    "        # get the activity data for the specific neuron\n",
    "        activity = (Neuron() & key).fetch1('activity') \n",
    "\n",
    "        # compute various statistics on activity\n",
    "        key['mean'] = activity.mean()   # compute mean\n",
    "        key['stdev'] = activity.std()   # compute standard deviation\n",
    "        key['max'] = activity.max()     # compute max\n",
    "        self.insert1(key)\n",
    "        print('Computed statistics for mouse_id {mouse_id} session_date {session_date}'.format(**key))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's go ahead and populate the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ActivityStatistics.populate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ActivityStatistics()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Voila!! You have computed statistics for each neuron activity!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Spike detection (Bonus)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using a similar mechanism, we can implement `Spikes` table that performs rudimentary **spike detection** on the data found in the `Neuron`'s activity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# special syntax to get only the primary keys for all entries\n",
    "keys = Neuron.fetch('KEY')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fetch all activities - returned as NumPy array of NumPy arrays\n",
    "activities = (Neuron & keys).fetch('activity')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, axs = plt.subplots(1, len(activities), figsize=(16, 4))\n",
    "for activity, ax in zip(activities, axs.ravel()):\n",
    "    ax.plot(activity)\n",
    "    ax.set_xlabel('Time')\n",
    "    ax.set_ylabel('Activity')\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": [
    "key = keys[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "activity = (Neuron & key).fetch1('activity')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.plot(activity)\n",
    "plt.xlabel('Time')\n",
    "plt.ylabel('Activity')\n",
    "plt.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 = (activity > threshold).astype(np.int)   \n",
    "\n",
    "plt.plot(activity)\n",
    "plt.plot(above_thrs)\n",
    "plt.xlabel('Time')\n",
    "plt.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": [
    "rising = (np.diff(above_thrs) > 0).astype(np.int)   # find rising edge of crossing threshold\n",
    "spikes = np.hstack((0, rising))    # prepend 0 to account for shortening due to np.diff\n",
    "\n",
    "plt.plot(activity)\n",
    "plt.plot(above_thrs)\n",
    "plt.plot(np.where(spikes>0), 1,  'ro'); # plot only spike points\n",
    "plt.xlabel('Time')\n",
    "plt.xlim([0, 300])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, let's also compute the spike counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "count = spikes.sum()   # 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   # try experimenting with this\n",
    "\n",
    "# find activity above threshold\n",
    "above_thrs = (activity > threshold).astype(np.int) \n",
    "\n",
    "rising = (np.diff(above_thrs) > 0).astype(np.int)   # find rising edge of crossing threshold\n",
    "spikes = np.hstack((0, rising))    # prepend 0 to account for shortening due to np.diff\n",
    "\n",
    "count = spikes.sum()   # compute total spike counts\n",
    "\n",
    "\n",
    "plt.plot(activity)\n",
    "plt.plot(above_thrs)\n",
    "plt.plot(np.where(spikes>0), 1,  'ro'); # plot only spike points\n",
    "plt.xlabel('Time')\n",
    "plt.title('Total spike counts: {}'.format(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": [
    "In DataJoint we house things like parameters in a **look up table**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@schema\n",
    "class 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",
    "    \"\"\""
   ]
  },
  {
   "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": [
    "@schema\n",
    "class Spikes(dj.Computed):\n",
    "    definition = \"\"\"\n",
    "    -> Neuron\n",
    "    -> SpikeDetectionParam\n",
    "    ---\n",
    "    spikes: longblob     # detected spikes\n",
    "    count: int           # total number of detected spikes\n",
    "    \"\"\""
   ]
  },
  {
   "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": [
    "@schema\n",
    "class 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",
    "    def make(self, key):\n",
    "        print('Populating for: ', key)\n",
    "\n",
    "        activity = (Neuron() & key).fetch1('activity')\n",
    "        threshold = (SpikeDetectionParam() & key).fetch1('threshold')\n",
    "\n",
    "        above_thrs = (activity > threshold).astype(np.int)   # find activity above threshold\n",
    "        rising = (np.diff(above_thrs) > 0).astype(np.int)   # find rising edge of crossing threshold\n",
    "        spikes = np.hstack((0, rising))    # prepend 0 to account for shortening due to np.diff\n",
    "\n",
    "        count = spikes.sum()   # compute total spike counts\n",
    "        print('Detected {} spikes!\\n'.format(count))\n",
    "\n",
    "        # save results and insert\n",
    "        key['spikes'] = spikes\n",
    "        key['count'] = count\n",
    "        self.insert1(key)"
   ]
  },
  {
   "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()"
   ]
  },
  {
   "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": [
    "# Summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Congratulations! You have successfully implemented a data pipeline starting with data about experimental subjects (`Mouse`), the experimental sessions (`Session`), recorded data (`Neuron`), and we even added tables for computational results (`ActivityStatistics` and `Spikes`)!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.ERD(schema)"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}