{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "(target-dj-querying-data)=\n", "# DataJoint pipeline: Querying data\n", "\n", ":::{important}\n", "This guide assumes you have a [DataJoint pipeline deployed](target-dj-pipeline-deployment) with [data already ingested](target-dj-data-ingestion-processing).\n", ":::\n", "\n", "This guide provides examples of how to query data from the [Aeon DataJoint pipeline](target-aeon-dj-pipeline) using DataJoint's various [query operators](datajoint:docs/core/datajoint-python/0.14/query/operators/), such as restriction (`&`, `-`), projection (`proj`), joining (`*`), and aggregation (`aggr`). These together enable powerful data manipulations and flexible data analysis workflows.\n", "\n", ":::{note}\n", "The examples here use the [Single mouse in a foraging assay](sample-data-single-mouse-foraging:) dataset for the experiment named `social0.2-aeon3`. \n", "If you are using a different dataset, be sure to replace the experiment name and parameters in the code below accordingly.\n", ":::" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from aeon.dj_pipeline import acquisition, tracking\n", "from aeon.dj_pipeline.analysis import block_analysis" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Acquisition data" ] }, { "cell_type": "markdown", "metadata": { "vscode": { "languageId": "plaintext" } }, "source": [ "The [DataJoint acquisition module](target-aeon-dj-pipeline-acquisition-tables) manages raw data collected during experiments. \n", "We will start by exploring the `acquisition.Chunk` table, which stores metadata about discrete time {term}`chunks ` and the associated raw data files for each experiment." ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " A recording period corresponds to a 1-hour data acquisition\n", "
\n", " \n", " \n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
\n", "

experiment_name

\n", " e.g exp0-aeon3\n", "
\n", "

chunk_start

\n", " datetime of the start of a given acquisition chunk\n", "
\n", "

chunk_end

\n", " datetime of the end of a given acquisition chunk\n", "
\n", "

directory_type

\n", " \n", "
\n", "

epoch_start

\n", " \n", "
social0.2-aeon32024-03-02 12:00:002024-03-02 13:00:00raw2024-03-01 16:46:12
social0.2-aeon32024-03-02 13:00:002024-03-02 14:00:00raw2024-03-01 16:46:12
\n", " \n", "

Total: 2

\n", " " ], "text/plain": [ "*experiment_na *chunk_start chunk_end directory_type epoch_start \n", "+------------+ +------------+ +------------+ +------------+ +------------+\n", "social0.2-aeon 2024-03-02 12: 2024-03-02 13: raw 2024-03-01 16:\n", "social0.2-aeon 2024-03-02 13: 2024-03-02 14: raw 2024-03-01 16:\n", " (Total: 2)" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "acquisition.Chunk()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The command above retrieves all records for all experiments from the `Chunk` table.\n", "Since we only ingested the sample data containing a 2-hour snippet of experiment `social0.2-aeon3`, the query returns the two records corresponding to each hour of the experiment.\n", "In most cases, you would have multiple experiments in the database and the query would return more records.\n", "Thus, it is often more efficient to apply specific criteria to filter the data." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Restricting data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "For example, we can use the [restriction operator `&`](datajoint:docs/core/datajoint-python/0.14/query/operators/#restriction) with a restriction key to specify the `experiment_name` (e.g. `social0.2-aeon3`) for which we want to retrieve the {term}`chunks `. " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " A recording period corresponds to a 1-hour data acquisition\n", "
\n", " \n", " \n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
\n", "

experiment_name

\n", " e.g exp0-aeon3\n", "
\n", "

chunk_start

\n", " datetime of the start of a given acquisition chunk\n", "
\n", "

chunk_end

\n", " datetime of the end of a given acquisition chunk\n", "
\n", "

directory_type

\n", " \n", "
\n", "

epoch_start

\n", " \n", "
social0.2-aeon32024-03-02 12:00:002024-03-02 13:00:00raw2024-03-01 16:46:12
social0.2-aeon32024-03-02 13:00:002024-03-02 14:00:00raw2024-03-01 16:46:12
\n", " \n", "

Total: 2

\n", " " ], "text/plain": [ "*experiment_na *chunk_start chunk_end directory_type epoch_start \n", "+------------+ +------------+ +------------+ +------------+ +------------+\n", "social0.2-aeon 2024-03-02 12: 2024-03-02 13: raw 2024-03-01 16:\n", "social0.2-aeon 2024-03-02 13: 2024-03-02 14: raw 2024-03-01 16:\n", " (Total: 2)" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "experiment_key = {\"experiment_name\": \"social0.2-aeon3\"}\n", "acquisition.Chunk & experiment_key" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Tracking data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The [DataJoint tracking module](target-aeon-dj-pipeline-tracking-tables) manages the position tracking data produced by different tracking software.\n", "Here we continue using the same restriction key to explore tracking data associated with the experiment `social0.2-aeon3`. \n", "\n", "The `SLEAPTracking` table stores the [SLEAP](sleap:) tracking data of the subject(s) in the experiment for each chunk of video recorded from a particular [camera device](target-module-camera)." ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " Tracked objects position data from a particular VideoSource for multi-animal experiment using the SLEAP tracking method per chunk\n", "
\n", " \n", " \n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
\n", "

experiment_name

\n", " e.g exp0-aeon3\n", "
\n", "

chunk_start

\n", " datetime of the start of a given acquisition chunk\n", "
\n", "

device_serial_number

\n", " \n", "
\n", "

spinnaker_video_source_install_time

\n", " time of the spinnaker_video_source placed and started operation at this position\n", "
\n", "

tracking_paramset_id

\n", " \n", "
social0.2-aeon32024-03-02 12:00:00230329092024-03-01 16:46:121
social0.2-aeon32024-03-02 13:00:00230329092024-03-01 16:46:121
\n", " \n", "

Total: 2

\n", " " ], "text/plain": [ "*experiment_na *chunk_start *device_serial *spinnaker_vid *tracking_para\n", "+------------+ +------------+ +------------+ +------------+ +------------+\n", "social0.2-aeon 2024-03-02 12: 23032909 2024-03-01 16: 1 \n", "social0.2-aeon 2024-03-02 13: 23032909 2024-03-01 16: 1 \n", " (Total: 2)" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "tracking.SLEAPTracking & experiment_key" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The `PoseIdentity` table contains information that identifies each subject and records the body part used as the anchor point in the [SLEAP top-down-id-model network](sleap:develop/api/sleap.nn.config.model.html#sleap.nn.config.model.MultiClassTopDownConfig)." ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", "
\n", " \n", " \n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
\n", "

experiment_name

\n", " e.g exp0-aeon3\n", "
\n", "

chunk_start

\n", " datetime of the start of a given acquisition chunk\n", "
\n", "

device_serial_number

\n", " \n", "
\n", "

spinnaker_video_source_install_time

\n", " time of the spinnaker_video_source placed and started operation at this position\n", "
\n", "

tracking_paramset_id

\n", " \n", "
\n", "

identity_idx

\n", " \n", "
\n", "

identity_name

\n", " \n", "
\n", "

identity_likelihood

\n", " \n", "
\n", "

anchor_part

\n", " the name of the point used as anchor node for this class\n", "
social0.2-aeon32024-03-02 12:00:00230329092024-03-01 16:46:1210BAA-1104045=BLOB=centroid
social0.2-aeon32024-03-02 12:00:00230329092024-03-01 16:46:1211BAA-1104047=BLOB=centroid
social0.2-aeon32024-03-02 13:00:00230329092024-03-01 16:46:1210BAA-1104045=BLOB=centroid
social0.2-aeon32024-03-02 13:00:00230329092024-03-01 16:46:1211BAA-1104047=BLOB=centroid
\n", " \n", "

Total: 4

\n", " " ], "text/plain": [ "*experiment_na *chunk_start *device_serial *spinnaker_vid *tracking_para *identity_idx identity_name identity_l anchor_part \n", "+------------+ +------------+ +------------+ +------------+ +------------+ +------------+ +------------+ +--------+ +------------+\n", "social0.2-aeon 2024-03-02 12: 23032909 2024-03-01 16: 1 0 BAA-1104045 =BLOB= centroid \n", "social0.2-aeon 2024-03-02 12: 23032909 2024-03-01 16: 1 1 BAA-1104047 =BLOB= centroid \n", "social0.2-aeon 2024-03-02 13: 23032909 2024-03-01 16: 1 0 BAA-1104045 =BLOB= centroid \n", "social0.2-aeon 2024-03-02 13: 23032909 2024-03-01 16: 1 1 BAA-1104047 =BLOB= centroid \n", " (Total: 4)" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "tracking.SLEAPTracking.PoseIdentity & experiment_key" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The `Part` table contains the x, y coordinates of tracked body parts for each video frame.\n", "In this dataset, we only tracked each subject's `centroid`." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", "
\n", " \n", " \n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
\n", "

experiment_name

\n", " e.g exp0-aeon3\n", "
\n", "

chunk_start

\n", " datetime of the start of a given acquisition chunk\n", "
\n", "

device_serial_number

\n", " \n", "
\n", "

spinnaker_video_source_install_time

\n", " spinnaker_video_source time of placement and start operation\n", "
\n", "

tracking_paramset_id

\n", " \n", "
\n", "

identity_idx

\n", " \n", "
\n", "

part_name

\n", " \n", "
\n", "

sample_count

\n", " number of data points acquired from this stream for a given chunk\n", "
\n", "

x

\n", " \n", "
\n", "

y

\n", " \n", "
\n", "

likelihood

\n", " \n", "
\n", "

timestamps

\n", " \n", "
social0.2-aeon32024-03-02 12:00:00230329092024-03-01 16:46:1210anchor_centroid19570=BLOB==BLOB==BLOB==BLOB=
social0.2-aeon32024-03-02 12:00:00230329092024-03-01 16:46:1210centroid19570=BLOB==BLOB==BLOB==BLOB=
social0.2-aeon32024-03-02 12:00:00230329092024-03-01 16:46:1211anchor_centroid40429=BLOB==BLOB==BLOB==BLOB=
social0.2-aeon32024-03-02 12:00:00230329092024-03-01 16:46:1211centroid40429=BLOB==BLOB==BLOB==BLOB=
social0.2-aeon32024-03-02 13:00:00230329092024-03-01 16:46:1210anchor_centroid19654=BLOB==BLOB==BLOB==BLOB=
social0.2-aeon32024-03-02 13:00:00230329092024-03-01 16:46:1210centroid19654=BLOB==BLOB==BLOB==BLOB=
social0.2-aeon32024-03-02 13:00:00230329092024-03-01 16:46:1211anchor_centroid40342=BLOB==BLOB==BLOB==BLOB=
social0.2-aeon32024-03-02 13:00:00230329092024-03-01 16:46:1211centroid40342=BLOB==BLOB==BLOB==BLOB=
\n", " \n", "

Total: 8

\n", " " ], "text/plain": [ "*experiment_name *chunk_start *device_serial_number *spinnaker_video_source_install_time *tracking_paramset_id *identity_idx *part_name sample_count x y likelihood timestamps\n", "+-----------------+ +---------------------+ +----------------------+ +-------------------------------------+ +----------------------+ +--------------+ +-----------------+ +--------------+ +--------+ +--------+ +--------+ +--------+\n", "social0.2-aeon3 2024-03-02 12:00:00 23032909 2024-03-01 16:46:12 1 0 anchor_centroid 19570 =BLOB= =BLOB= =BLOB= =BLOB= \n", "social0.2-aeon3 2024-03-02 12:00:00 23032909 2024-03-01 16:46:12 1 0 centroid 19570 =BLOB= =BLOB= =BLOB= =BLOB= \n", "social0.2-aeon3 2024-03-02 12:00:00 23032909 2024-03-01 16:46:12 1 1 anchor_centroid 40429 =BLOB= =BLOB= =BLOB= =BLOB= \n", "social0.2-aeon3 2024-03-02 12:00:00 23032909 2024-03-01 16:46:12 1 1 centroid 40429 =BLOB= =BLOB= =BLOB= =BLOB= \n", "social0.2-aeon3 2024-03-02 13:00:00 23032909 2024-03-01 16:46:12 1 0 anchor_centroid 19654 =BLOB= =BLOB= =BLOB= =BLOB= \n", "social0.2-aeon3 2024-03-02 13:00:00 23032909 2024-03-01 16:46:12 1 0 centroid 19654 =BLOB= =BLOB= =BLOB= =BLOB= \n", "social0.2-aeon3 2024-03-02 13:00:00 23032909 2024-03-01 16:46:12 1 1 anchor_centroid 40342 =BLOB= =BLOB= =BLOB= =BLOB= \n", "social0.2-aeon3 2024-03-02 13:00:00 23032909 2024-03-01 16:46:12 1 1 centroid 40342 =BLOB= =BLOB= =BLOB= =BLOB= \n", " (Total: 8)" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "tracking.SLEAPTracking.Part & experiment_key" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Block-level data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The [DataJoint block analysis module](target-aeon-dj-pipeline-analysis-tables) contains tables that aggregate experimental events into defined time {term}`blocks `.\n", "This allows for higher-level analyses, such as behavioural trends over extended periods.\n", "\n", "### Restricting data\n", "Using another restriction operator `&`, we can further filter the `Block` and `BlockAnalysis` tables to only include blocks longer than 1 hour for the experiment `social0.2-aeon3`." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", "
\n", " \n", " \n", " \n", "\n", "\n", "\n", "
\n", "

experiment_name

\n", " e.g exp0-aeon3\n", "
\n", "

block_start

\n", " \n", "
\n", "

block_end

\n", " \n", "
\n", "

block_duration_hr

\n", " (hour)\n", "
social0.2-aeon32024-03-02 12:00:002024-03-02 14:00:002.000
\n", " \n", "

Total: 1

\n", " " ], "text/plain": [ "*experiment_name *block_start block_end block_duration_hr \n", "+-----------------+ +---------------------+ +---------------------+ +-------------------+\n", "social0.2-aeon3 2024-03-02 12:00:00 2024-03-02 14:00:00 2.000 \n", " (Total: 1)" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "block_analysis.Block & experiment_key & \"block_duration_hr > 1\"" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", "
\n", " \n", " \n", " \n", "\n", "\n", "\n", "\n", "
\n", "

experiment_name

\n", " e.g exp0-aeon3\n", "
\n", "

block_start

\n", " \n", "
\n", "

block_duration

\n", " (hour)\n", "
\n", "

patch_count

\n", " number of patches in the block\n", "
\n", "

subject_count

\n", " number of subjects in the block\n", "
social0.2-aeon32024-03-02 12:00:001.9989132
\n", " \n", "

Total: 1

\n", " " ], "text/plain": [ "*experiment_name *block_start block_duration patch_count subject_count \n", "+-----------------+ +---------------------+ +----------------+ +-------------+ +---------------+\n", "social0.2-aeon3 2024-03-02 12:00:00 1.99891 3 2 \n", " (Total: 1)" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "block_analysis.BlockAnalysis & experiment_key & \"block_duration > 1\"" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can also use the SQL `LIKE` operator to filter records based on a pattern match. \n", "Here, we filter the `Block` table by `block_start` to only include blocks that started on `2024-03-02`." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", "
\n", " \n", " \n", " \n", "\n", "\n", "\n", "
\n", "

experiment_name

\n", " e.g exp0-aeon3\n", "
\n", "

block_start

\n", " \n", "
\n", "

block_end

\n", " \n", "
\n", "

block_duration_hr

\n", " (hour)\n", "
social0.2-aeon32024-03-02 12:00:002024-03-02 14:00:002.000
\n", " \n", "

Total: 1

\n", " " ], "text/plain": [ "*experiment_name *block_start block_end block_duration_hr \n", "+-----------------+ +---------------------+ +---------------------+ +-------------------+\n", "social0.2-aeon3 2024-03-02 12:00:00 2024-03-02 14:00:00 2.000 \n", " (Total: 1)" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "block_analysis.Block & experiment_key & 'block_start LIKE \"%2024-03-02%\"'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Using primary keys\n", "\n", "We can [`fetch()`](datajoint:docs/core/datajoint-python/0.14/query/fetch/#fetch) the [primary keys](datajoint:docs/core/datajoint-python/0.14/design/tables/primary/) of the blocks matching the above query expression as a list of dictionaries." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[{'experiment_name': 'social0.2-aeon3',\n", " 'block_start': datetime.datetime(2024, 3, 2, 12, 0)}]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "block_key = (\n", " block_analysis.Block & experiment_key & 'block_start LIKE \"%2024-03-02%\"'\n", ").fetch(\"KEY\")\n", "block_key\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The primary keys can then be used to retrieve specific block-level records in other [analysis tables that reference the `Block` table](target-aeon-dj-pipeline-analysis-fig).\n", "\n", "For instance, we can use the primary keys to retrieve the `BlockAnalysis` records associated with the blocks that started on `2024-03-02`." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", "
\n", " \n", " \n", " \n", "\n", "\n", "\n", "\n", "
\n", "

experiment_name

\n", " e.g exp0-aeon3\n", "
\n", "

block_start

\n", " \n", "
\n", "

block_duration

\n", " (hour)\n", "
\n", "

patch_count

\n", " number of patches in the block\n", "
\n", "

subject_count

\n", " number of subjects in the block\n", "
social0.2-aeon32024-03-02 12:00:001.9989132
\n", " \n", "

Total: 1

\n", " " ], "text/plain": [ "*experiment_name *block_start block_duration patch_count subject_count \n", "+-----------------+ +---------------------+ +----------------+ +-------------+ +---------------+\n", "social0.2-aeon3 2024-03-02 12:00:00 1.99891 3 2 \n", " (Total: 1)" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "block_analysis.BlockAnalysis & block_key" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can also retrieve subject-specific analyses (`BlockSubjectAnalysis`), such as subjects' food patch preference (`BlockSubjectAnalysis.Preference`) and their interaction with the food patches (`BlockSubjectAnalysis.Patch`) for the same blocks." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " Measure of preference for a particular patch from a particular subject\n", "
\n", " \n", " \n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
\n", "

experiment_name

\n", " e.g exp0-aeon3\n", "
\n", "

block_start

\n", " \n", "
\n", "

patch_name

\n", " e.g. Patch1, Patch2\n", "
\n", "

subject_name

\n", " \n", "
\n", "

cumulative_preference_by_wheel

\n", " \n", "
\n", "

cumulative_preference_by_time

\n", " \n", "
\n", "

running_preference_by_time

\n", " \n", "
\n", "

running_preference_by_wheel

\n", " \n", "
\n", "

final_preference_by_wheel

\n", " cumulative_preference_by_wheel at the end of the block\n", "
\n", "

final_preference_by_time

\n", " cumulative_preference_by_time at the end of the block\n", "
social0.2-aeon32024-03-02 12:00:00Patch1BAA-1104045=BLOB==BLOB==BLOB==BLOB=0.00.0363886
social0.2-aeon32024-03-02 12:00:00Patch1BAA-1104047=BLOB==BLOB==BLOB==BLOB=0.1051980.160914
social0.2-aeon32024-03-02 12:00:00Patch2BAA-1104045=BLOB==BLOB==BLOB==BLOB=0.02312140.302103
social0.2-aeon32024-03-02 12:00:00Patch2BAA-1104047=BLOB==BLOB==BLOB==BLOB=0.3521640.368887
social0.2-aeon32024-03-02 12:00:00Patch3BAA-1104045=BLOB==BLOB==BLOB==BLOB=1.008540.661508
social0.2-aeon32024-03-02 12:00:00Patch3BAA-1104047=BLOB==BLOB==BLOB==BLOB=0.5426370.470199
\n", " \n", "

Total: 6

\n", " " ], "text/plain": [ "*experiment_name *block_start *patch_name *subject_name cumulative cumulative running_pr running_pr final_preference_by_wheel final_preference_by_time \n", "+-----------------+ +---------------------+ +------------+ +--------------+ +--------+ +--------+ +--------+ +--------+ +---------------------------+ +--------------------------+\n", "social0.2-aeon3 2024-03-02 12:00:00 Patch1 BAA-1104045 =BLOB= =BLOB= =BLOB= =BLOB= 0.0 0.0363886 \n", "social0.2-aeon3 2024-03-02 12:00:00 Patch1 BAA-1104047 =BLOB= =BLOB= =BLOB= =BLOB= 0.105198 0.160914 \n", "social0.2-aeon3 2024-03-02 12:00:00 Patch2 BAA-1104045 =BLOB= =BLOB= =BLOB= =BLOB= 0.0231214 0.302103 \n", "social0.2-aeon3 2024-03-02 12:00:00 Patch2 BAA-1104047 =BLOB= =BLOB= =BLOB= =BLOB= 0.352164 0.368887 \n", "social0.2-aeon3 2024-03-02 12:00:00 Patch3 BAA-1104045 =BLOB= =BLOB= =BLOB= =BLOB= 1.00854 0.661508 \n", "social0.2-aeon3 2024-03-02 12:00:00 Patch3 BAA-1104047 =BLOB= =BLOB= =BLOB= =BLOB= 0.542637 0.470199 \n", " (Total: 6)" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "block_analysis.BlockSubjectAnalysis.Preference & block_key" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", "
\n", " \n", " \n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
\n", "

experiment_name

\n", " e.g exp0-aeon3\n", "
\n", "

block_start

\n", " \n", "
\n", "

patch_name

\n", " e.g. Patch1, Patch2\n", "
\n", "

subject_name

\n", " \n", "
\n", "

in_patch_timestamps

\n", " timestamps when a subject is at a specific patch\n", "
\n", "

in_patch_time

\n", " total seconds spent in this patch for this block\n", "
\n", "

in_patch_rfid_timestamps

\n", " in_patch_timestamps based on RFID\n", "
\n", "

pellet_count

\n", " \n", "
\n", "

pellet_timestamps

\n", " \n", "
\n", "

patch_threshold

\n", " patch threshold value at each pellet delivery\n", "
\n", "

wheel_cumsum_distance_travelled

\n", " wheel's cumulative distance travelled\n", "
social0.2-aeon32024-03-02 12:00:00Patch1BAA-1104045=BLOB=12.18=BLOB=0=BLOB==BLOB==BLOB=
social0.2-aeon32024-03-02 12:00:00Patch1BAA-1104047=BLOB=300.22=BLOB=0=BLOB==BLOB==BLOB=
social0.2-aeon32024-03-02 12:00:00Patch2BAA-1104045=BLOB=101.12=BLOB=0=BLOB==BLOB==BLOB=
social0.2-aeon32024-03-02 12:00:00Patch2BAA-1104047=BLOB=688.24=BLOB=9=BLOB==BLOB==BLOB=
social0.2-aeon32024-03-02 12:00:00Patch3BAA-1104045=BLOB=221.42=BLOB=0=BLOB==BLOB==BLOB=
social0.2-aeon32024-03-02 12:00:00Patch3BAA-1104047=BLOB=877.26=BLOB=35=BLOB==BLOB==BLOB=
\n", " \n", "

Total: 6

\n", " " ], "text/plain": [ "*experiment_name *block_start *patch_name *subject_name in_patch_t in_patch_time in_patch_r pellet_count pellet_tim patch_thre wheel_cums\n", "+-----------------+ +---------------------+ +------------+ +--------------+ +--------+ +---------------+ +--------+ +--------------+ +--------+ +--------+ +--------+\n", "social0.2-aeon3 2024-03-02 12:00:00 Patch1 BAA-1104045 =BLOB= 12.18 =BLOB= 0 =BLOB= =BLOB= =BLOB= \n", "social0.2-aeon3 2024-03-02 12:00:00 Patch1 BAA-1104047 =BLOB= 300.22 =BLOB= 0 =BLOB= =BLOB= =BLOB= \n", "social0.2-aeon3 2024-03-02 12:00:00 Patch2 BAA-1104045 =BLOB= 101.12 =BLOB= 0 =BLOB= =BLOB= =BLOB= \n", "social0.2-aeon3 2024-03-02 12:00:00 Patch2 BAA-1104047 =BLOB= 688.24 =BLOB= 9 =BLOB= =BLOB= =BLOB= \n", "social0.2-aeon3 2024-03-02 12:00:00 Patch3 BAA-1104045 =BLOB= 221.42 =BLOB= 0 =BLOB= =BLOB= =BLOB= \n", "social0.2-aeon3 2024-03-02 12:00:00 Patch3 BAA-1104047 =BLOB= 877.26 =BLOB= 35 =BLOB= =BLOB= =BLOB= \n", " (Total: 6)" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# based on subject position\n", "block_analysis.BlockSubjectAnalysis.Patch & block_key" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Joining data\n", "\n", "To obtain a comprehensive view of the patch-preference interactions, we can join the `Patch` and `Preference` part tables using the [join operator `*`](datajoint:docs/core/datajoint-python/0.14/query/operators/#join-compatibility)." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", "
\n", " \n", " \n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
\n", "

experiment_name

\n", " e.g exp0-aeon3\n", "
\n", "

block_start

\n", " \n", "
\n", "

patch_name

\n", " e.g. Patch1, Patch2\n", "
\n", "

subject_name

\n", " \n", "
\n", "

in_patch_timestamps

\n", " timestamps when a subject is at a specific patch\n", "
\n", "

in_patch_time

\n", " total seconds spent in this patch for this block\n", "
\n", "

in_patch_rfid_timestamps

\n", " in_patch_timestamps based on RFID\n", "
\n", "

pellet_count

\n", " \n", "
\n", "

pellet_timestamps

\n", " \n", "
\n", "

patch_threshold

\n", " patch threshold value at each pellet delivery\n", "
\n", "

wheel_cumsum_distance_travelled

\n", " wheel's cumulative distance travelled\n", "
\n", "

cumulative_preference_by_wheel

\n", " \n", "
\n", "

cumulative_preference_by_time

\n", " \n", "
\n", "

running_preference_by_time

\n", " \n", "
\n", "

running_preference_by_wheel

\n", " \n", "
\n", "

final_preference_by_wheel

\n", " cumulative_preference_by_wheel at the end of the block\n", "
\n", "

final_preference_by_time

\n", " cumulative_preference_by_time at the end of the block\n", "
social0.2-aeon32024-03-02 12:00:00Patch1BAA-1104045=BLOB=12.18=BLOB=0=BLOB==BLOB==BLOB==BLOB==BLOB==BLOB==BLOB=0.00.0363886
social0.2-aeon32024-03-02 12:00:00Patch1BAA-1104047=BLOB=300.22=BLOB=0=BLOB==BLOB==BLOB==BLOB==BLOB==BLOB==BLOB=0.1051980.160914
social0.2-aeon32024-03-02 12:00:00Patch2BAA-1104045=BLOB=101.12=BLOB=0=BLOB==BLOB==BLOB==BLOB==BLOB==BLOB==BLOB=0.02312140.302103
social0.2-aeon32024-03-02 12:00:00Patch2BAA-1104047=BLOB=688.24=BLOB=9=BLOB==BLOB==BLOB==BLOB==BLOB==BLOB==BLOB=0.3521640.368887
social0.2-aeon32024-03-02 12:00:00Patch3BAA-1104045=BLOB=221.42=BLOB=0=BLOB==BLOB==BLOB==BLOB==BLOB==BLOB==BLOB=1.008540.661508
social0.2-aeon32024-03-02 12:00:00Patch3BAA-1104047=BLOB=877.26=BLOB=35=BLOB==BLOB==BLOB==BLOB==BLOB==BLOB==BLOB=0.5426370.470199
\n", " \n", "

Total: 6

\n", " " ], "text/plain": [ "*experiment_name *block_start *patch_name *subject_name in_patch_t in_patch_time in_patch_r pellet_count pellet_tim patch_thre wheel_cums cumulative cumulative running_pr running_pr final_preference_by_wheel final_preference_by_time \n", "+-----------------+ +---------------------+ +------------+ +--------------+ +--------+ +---------------+ +--------+ +--------------+ +--------+ +--------+ +--------+ +--------+ +--------+ +--------+ +--------+ +---------------------------+ +--------------------------+\n", "social0.2-aeon3 2024-03-02 12:00:00 Patch1 BAA-1104045 =BLOB= 12.18 =BLOB= 0 =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= 0.0 0.0363886 \n", "social0.2-aeon3 2024-03-02 12:00:00 Patch1 BAA-1104047 =BLOB= 300.22 =BLOB= 0 =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= 0.105198 0.160914 \n", "social0.2-aeon3 2024-03-02 12:00:00 Patch2 BAA-1104045 =BLOB= 101.12 =BLOB= 0 =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= 0.0231214 0.302103 \n", "social0.2-aeon3 2024-03-02 12:00:00 Patch2 BAA-1104047 =BLOB= 688.24 =BLOB= 9 =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= 0.352164 0.368887 \n", "social0.2-aeon3 2024-03-02 12:00:00 Patch3 BAA-1104045 =BLOB= 221.42 =BLOB= 0 =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= 1.00854 0.661508 \n", "social0.2-aeon3 2024-03-02 12:00:00 Patch3 BAA-1104047 =BLOB= 877.26 =BLOB= 35 =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= =BLOB= 0.542637 0.470199 \n", " (Total: 6)" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "block_analysis.BlockSubjectAnalysis.Patch * block_analysis.BlockSubjectAnalysis.Preference & block_key" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Fetching and inspecting data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Finally, data can be fetched as a [pandas DataFrame](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html) using [`fetch()`](datajoint:docs/core/datajoint-python/0.14/query/fetch/#usage-with-pandas) with the `format=\"frame\"` argument." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
experiment_nameblock_startpatch_namepellet_countpellet_timestampswheel_cumsum_distance_travelledwheel_timestampspatch_thresholdpatch_threshold_timestampspatch_ratepatch_offset
0social0.2-aeon32024-03-02 12:00:00Patch10[][-0.0, 0.0015340744203575785, 0.00153407442035...[2024-03-02T12:00:00.000000000, 2024-03-02T12:...[][]0.010075.0
1social0.2-aeon32024-03-02 12:00:00Patch29[2024-03-02T12:24:33.777504000, 2024-03-02T12:...[-0.0, 0.004602223261073846, -0.0, 0.001534074...[2024-03-02T12:00:00.000000000, 2024-03-02T12:...[364.05544835561926, 164.93215066599433, 485.7...[2024-03-02T12:24:09.392000000, 2024-03-02T12:...0.002075.0
2social0.2-aeon32024-03-02 12:00:00Patch335[2024-03-02T12:20:17.155488000, 2024-03-02T12:...[-0.0, 0.003068148840714713, 0.004602223261073...[2024-03-02T12:00:00.000000000, 2024-03-02T12:...[153.60720025718558, 166.6737735450297, 327.20...[2024-03-02T12:20:01.725984000, 2024-03-02T12:...0.003375.0
\n", "
" ], "text/plain": [ " experiment_name block_start patch_name pellet_count \\\n", "0 social0.2-aeon3 2024-03-02 12:00:00 Patch1 0 \n", "1 social0.2-aeon3 2024-03-02 12:00:00 Patch2 9 \n", "2 social0.2-aeon3 2024-03-02 12:00:00 Patch3 35 \n", "\n", " pellet_timestamps \\\n", "0 [] \n", "1 [2024-03-02T12:24:33.777504000, 2024-03-02T12:... \n", "2 [2024-03-02T12:20:17.155488000, 2024-03-02T12:... \n", "\n", " wheel_cumsum_distance_travelled \\\n", "0 [-0.0, 0.0015340744203575785, 0.00153407442035... \n", "1 [-0.0, 0.004602223261073846, -0.0, 0.001534074... \n", "2 [-0.0, 0.003068148840714713, 0.004602223261073... \n", "\n", " wheel_timestamps \\\n", "0 [2024-03-02T12:00:00.000000000, 2024-03-02T12:... \n", "1 [2024-03-02T12:00:00.000000000, 2024-03-02T12:... \n", "2 [2024-03-02T12:00:00.000000000, 2024-03-02T12:... \n", "\n", " patch_threshold \\\n", "0 [] \n", "1 [364.05544835561926, 164.93215066599433, 485.7... \n", "2 [153.60720025718558, 166.6737735450297, 327.20... \n", "\n", " patch_threshold_timestamps patch_rate patch_offset \n", "0 [] 0.0100 75.0 \n", "1 [2024-03-02T12:24:09.392000000, 2024-03-02T12:... 0.0020 75.0 \n", "2 [2024-03-02T12:20:01.725984000, 2024-03-02T12:... 0.0033 75.0 " ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "block_patch_data = (\n", " (block_analysis.BlockAnalysis.Patch & block_key).fetch(format=\"frame\").reset_index()\n", ")\n", "block_patch_data" ] } ], "metadata": { "kernelspec": { "display_name": "aeon", "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.11.11" } }, "nbformat": 4, "nbformat_minor": 4 }