Exploring the IBL Data Pipeline
Here we will introduce some useful DataJoint tools and concepts to help you explore the IBL data pipeline. Before proceeding make sure that you have installed the IBL python environment and set up your Datajoint credentials
A few definitions
First of all, let’s define a few basic definitions:
Table - collection of rows and columns that contain data
Schema - a collection of related tables
Module - script where schema and associated tables are defined
Pipeline - collection of schemas
Example nomenclature would be to say that we want to get data from the Subject table stored in the ibl_subjects schema which are together defined in the subject module in the IBL pipeline.
In practice, modules are often referred to as schemas and so we would refer to the subject module as the subject schema
Let’s now proceed by importing Datajoint and some schemas from the IBL pipeline
[1]:
import datajoint as dj
dj.config['display.limit'] = 5
from ibl_pipeline import reference, subject, action, acquisition, data, behavior
from ibl_pipeline.analyses import behavior as behavior_analyses
Connecting mayofaulkner@datajoint.internationalbrainlab.org:3306
C:\Users\Mayo\iblenv\IBL-pipeline\ibl_pipeline\behavior.py:14: UserWarning: ONE not installed, cannot use populate
warnings.warn('ONE not installed, cannot use populate')
1. Browsing schemas
The IBL pipeline contains a number of different schemas. To browse which schemas are available, we can use the dj.list_schemas() command,
[2]:
dj.list_schemas()
Out[2]:
['group_shared_anneurai_analyses',
'group_shared_anneurai_psytrack',
'group_shared_brainwidemap_analyses',
'group_shared_brainwidemap_analysis',
'group_shared_brainwidemap_priors',
'group_shared_end_criteria',
'group_shared_ephys',
'group_shared_sfndata',
'group_shared_testing',
'group_shared_tutorial',
'group_shared_wheel',
'group_shared_wheel_moves',
'ibl_acquisition',
'ibl_action',
'ibl_alyxraw',
'ibl_analyses_behavior',
'ibl_analyses_ephys',
'ibl_behavior',
'ibl_data',
'ibl_debug',
'ibl_ephys',
'ibl_histology',
'ibl_ibl_acquisition',
'ibl_ibl_action',
'ibl_ibl_alyxraw',
'ibl_ibl_analyses_behavior',
'ibl_ibl_behavior',
'ibl_ibl_data',
'ibl_ibl_ingest_acquisition',
'ibl_ibl_ingest_action',
'ibl_ibl_ingest_data',
'ibl_ibl_ingest_job',
'ibl_ibl_ingest_reference',
'ibl_ibl_ingest_subject',
'ibl_ibl_reference',
'ibl_ibl_subject',
'ibl_ingest_acquisition',
'ibl_ingest_action',
'ibl_ingest_data',
'ibl_ingest_ephys',
'ibl_ingest_histology',
'ibl_ingest_job',
'ibl_ingest_qc',
'ibl_ingest_reference',
'ibl_ingest_subject',
'ibl_jaib1_tutorial',
'ibl_patch',
'ibl_plotting_behavior',
'ibl_plotting_ephys',
'ibl_plotting_histology',
'ibl_public',
'ibl_qc',
'ibl_reference',
'ibl_storage',
'ibl_subject',
'ibl_update',
'user_mayofaulkner_tutorial']
Major schemas include:
Meta data from Alyx:
ibl_reference, ibl_subject, ibl_action, ibl_acquisition, and ibl_dataImported data from FlatIron:
ibl_behavior and ibl_ephysComputed analzyed results:
ibl_analyses_behavior2. Browsing tables in a schemas
We can see what tables are defined in a schema using the dj.Diagram command. For example, to see the tables defined in the subject schema we can type,
dj.Diagram(subject)
Note
For more information about the what the different colours and line types mean, please refer to this more comprehensive tutorial
We can also use the following code snippet to list the tables that are defined in a schema
[3]:
from datajoint.user_tables import UserTable
import inspect
def list_tables(schema):
for k in dir(schema):
t = getattr(schema, k)
if inspect.isclass(t) and issubclass(t, UserTable):
print(k)
[4]:
list_tables(subject)
Allele
AlleleSequence
BreedingPair
CageType
Caging
Death
Enrichment
Food
GenotypeTest
Housing
Implant
Line
LineAllele
Litter
LitterSubject
Sequence
Source
Species
Strain
Subject
SubjectCullMethod
SubjectHousing
SubjectLab
SubjectProject
SubjectUser
UserHistory
Weaning
Zygosity
3. Getting the detailed definition of a table
To find out details about a table, we can use the describe method
[5]:
subject.Subject.describe()
subject_uuid : uuid
---
subject_nickname : varchar(255) # nickname
sex : enum('M','F','U') # sex
subject_birth_date=null : date # birth date
ear_mark=null : varchar(255) # ear mark
-> [nullable] subject.Line.proj(subject_line="line_name")
-> [nullable] subject.Source.proj(subject_source="source_name")
protocol_number : tinyint # protocol number
subject_description=null : varchar(1024)
subject_ts=CURRENT_TIMESTAMP : timestamp
-> [nullable] subject.Strain.proj(subject_strain="strain_name")
Out[5]:
'subject_uuid : uuid \n---\nsubject_nickname : varchar(255) # nickname\nsex : enum(\'M\',\'F\',\'U\') # sex\nsubject_birth_date=null : date # birth date\near_mark=null : varchar(255) # ear mark\n-> [nullable] subject.Line.proj(subject_line="line_name")\n-> [nullable] subject.Source.proj(subject_source="source_name")\nprotocol_number : tinyint # protocol number\nsubject_description=null : varchar(1024) \nsubject_ts=CURRENT_TIMESTAMP : timestamp \n-> [nullable] subject.Strain.proj(subject_strain="strain_name")\n'
4. Browsing data in tables - queries
Query all subjects
[6]:
subject.Subject()
Out[6]:
| subject_uuid | subject_nickname nickname | sex sex | subject_birth_date birth date | ear_mark ear mark | subject_line name | subject_source name of source | protocol_number protocol number | subject_description | subject_ts | subject_strain |
|---|---|---|---|---|---|---|---|---|---|---|
| 0026c82d-39e4-4c6b-acb3-303eb4b24f05 | IBL_32 | M | 2018-04-23 | None | C57BL/6NCrl | Charles River | 1 | None | 2019-08-06 21:30:42 | C57BL/6N |
| 00778394-c956-408d-8a6c-ca3b05a611d5 | KS019 | F | 2019-04-30 | None | C57BL/6J | None | 2 | None | 2019-08-13 17:07:33 | C57BL/6J |
| 00c60db3-74c3-4ee2-9df9-2c84acf84e92 | ibl_witten_10 | F | 2018-11-13 | notag | C57BL/6J | Jax | 3 | None | 2019-09-25 01:33:25 | C57BL/6J |
| 0124f697-16ce-4f59-b87c-e53fcb3a27ac | 6867 | M | 2018-06-25 | None | Thy1-GCaMP6s | CCU - Margarida colonies | 1 | None | 2019-09-25 01:33:25 | C57BL/6J |
| 019444e5-2192-4953-855d-084226fb965a | dop_48 | M | 2021-11-23 | 709 | Dat-Cre | Princeton | 2 | None | 2022-04-15 03:37:28 | C57BL/6J |
...
Total: 1318
Restriction &: filtering data
Restriction: Query one subject
[7]:
# restrict by string
subject.Subject & 'subject_nickname="ibl_witten_10"'
Out[7]:
| subject_uuid | subject_nickname nickname | sex sex | subject_birth_date birth date | ear_mark ear mark | subject_line name | subject_source name of source | protocol_number protocol number | subject_description | subject_ts | subject_strain |
|---|---|---|---|---|---|---|---|---|---|---|
| 00c60db3-74c3-4ee2-9df9-2c84acf84e92 | ibl_witten_10 | F | 2018-11-13 | notag | C57BL/6J | Jax | 3 | None | 2019-09-25 01:33:25 | C57BL/6J |
Total: 1
[8]:
# restrict by dictionary
from uuid import UUID
subject.Subject & {'subject_uuid': UUID('00c60db3-74c3-4ee2-9df9-2c84acf84e92')}
Out[8]:
| subject_uuid | subject_nickname nickname | sex sex | subject_birth_date birth date | ear_mark ear mark | subject_line name | subject_source name of source | protocol_number protocol number | subject_description | subject_ts | subject_strain |
|---|---|---|---|---|---|---|---|---|---|---|
| 00c60db3-74c3-4ee2-9df9-2c84acf84e92 | ibl_witten_10 | F | 2018-11-13 | notag | C57BL/6J | Jax | 3 | None | 2019-09-25 01:33:25 | C57BL/6J |
Total: 1
Restriction: Query all male subjects
[9]:
subject.Subject & {'sex': 'm'}
Out[9]:
| subject_uuid | subject_nickname nickname | sex sex | subject_birth_date birth date | ear_mark ear mark | subject_line name | subject_source name of source | protocol_number protocol number | subject_description | subject_ts | subject_strain |
|---|---|---|---|---|---|---|---|---|---|---|
| 0026c82d-39e4-4c6b-acb3-303eb4b24f05 | IBL_32 | M | 2018-04-23 | None | C57BL/6NCrl | Charles River | 1 | None | 2019-08-06 21:30:42 | C57BL/6N |
| 0124f697-16ce-4f59-b87c-e53fcb3a27ac | 6867 | M | 2018-06-25 | None | Thy1-GCaMP6s | CCU - Margarida colonies | 1 | None | 2019-09-25 01:33:25 | C57BL/6J |
| 019444e5-2192-4953-855d-084226fb965a | dop_48 | M | 2021-11-23 | 709 | Dat-Cre | Princeton | 2 | None | 2022-04-15 03:37:28 | C57BL/6J |
| 019a22c1-b944-4494-9e38-0e45ae6697bf | SWC_022 | M | 2019-06-18 | NA (Front HP) | C57BL/6J | Charles River | 4 | ID: 990762 | 2019-09-25 01:33:25 | C57BL/6J |
| 01a17d5f-6517-42ec-9dad-9ab24eecb6a9 | PL031 | M | 2021-11-12 | Black front | None | Charles River | 3 | None | 2021-12-18 02:16:54 | C57BL/6J |
...
Total: 692
Restriction: Query subjects born after a date
[10]:
subject.Subject & 'subject_birth_date > "2019-01-01"'
Out[10]:
| subject_uuid | subject_nickname nickname | sex sex | subject_birth_date birth date | ear_mark ear mark | subject_line name | subject_source name of source | protocol_number protocol number | subject_description | subject_ts | subject_strain |
|---|---|---|---|---|---|---|---|---|---|---|
| 00778394-c956-408d-8a6c-ca3b05a611d5 | KS019 | F | 2019-04-30 | None | C57BL/6J | None | 2 | None | 2019-08-13 17:07:33 | C57BL/6J |
| 019444e5-2192-4953-855d-084226fb965a | dop_48 | M | 2021-11-23 | 709 | Dat-Cre | Princeton | 2 | None | 2022-04-15 03:37:28 | C57BL/6J |
| 019a22c1-b944-4494-9e38-0e45ae6697bf | SWC_022 | M | 2019-06-18 | NA (Front HP) | C57BL/6J | Charles River | 4 | ID: 990762 | 2019-09-25 01:33:25 | C57BL/6J |
| 01a17d5f-6517-42ec-9dad-9ab24eecb6a9 | PL031 | M | 2021-11-12 | Black front | None | Charles River | 3 | None | 2021-12-18 02:16:54 | C57BL/6J |
| 01be78e7-8741-4b40-bd64-79ed745431b5 | CSHL057 | M | 2019-10-15 | L | C57BL/6J | Jax | 3 | None | 2019-12-12 04:43:42 | C57BL/6J |
...
Total: 884
Restriction: Query subjects within a range of dates
[11]:
subject.Subject & 'subject_birth_date between "2019-01-01" and "2019-04-01"'
Out[11]:
| subject_uuid | subject_nickname nickname | sex sex | subject_birth_date birth date | ear_mark ear mark | subject_line name | subject_source name of source | protocol_number protocol number | subject_description | subject_ts | subject_strain |
|---|---|---|---|---|---|---|---|---|---|---|
| 077d4b11-c784-4cb9-983c-5a596815434f | ZM_1735 | F | 2019-02-27 | None | C57BL/6J | CCU - Vivarium colonies | 3 | None | 2019-08-06 21:30:42 | C57BL/6J |
| 088b6898-0a86-435e-b91f-eab829a846f6 | SWC_005 | M | 2019-02-05 | NA (Rear HP) | C57BL/6J | Charles River | 4 | ID: 919175 | 2019-09-25 01:33:26 | C57BL/6J |
| 09629112-d258-4f00-86f0-e9374dcb7cb3 | ZM_1887 | F | 2019-03-04 | None | C57BL/6J | Jax | 3 | Black tail tip | 2019-08-06 21:30:42 | C57BL/6J |
| 09da30a5-53cd-4f69-85d9-1accb3fa523b | CSHL028 | M | 2019-02-19 | RL | C57BL/6J | None | 1 | None | 2019-09-25 01:33:26 | C57BL/6J |
| 0d219ef3-6282-4602-b99f-a40d06d99bb2 | SWC_002 | F | 2019-01-19 | L (Front HP) | C57BL/6J | Charles River | 4 | ID: 919178 | 2019-09-25 01:33:26 | C57BL/6J |
...
Total: 78
Restriction: Query subjects on multiple attributes
[12]:
subject.Subject & 'subject_birth_date > "2019-01-01"' & 'sex="M"'
Out[12]:
| subject_uuid | subject_nickname nickname | sex sex | subject_birth_date birth date | ear_mark ear mark | subject_line name | subject_source name of source | protocol_number protocol number | subject_description | subject_ts | subject_strain |
|---|---|---|---|---|---|---|---|---|---|---|
| 019444e5-2192-4953-855d-084226fb965a | dop_48 | M | 2021-11-23 | 709 | Dat-Cre | Princeton | 2 | None | 2022-04-15 03:37:28 | C57BL/6J |
| 019a22c1-b944-4494-9e38-0e45ae6697bf | SWC_022 | M | 2019-06-18 | NA (Front HP) | C57BL/6J | Charles River | 4 | ID: 990762 | 2019-09-25 01:33:25 | C57BL/6J |
| 01a17d5f-6517-42ec-9dad-9ab24eecb6a9 | PL031 | M | 2021-11-12 | Black front | None | Charles River | 3 | None | 2021-12-18 02:16:54 | C57BL/6J |
| 01be78e7-8741-4b40-bd64-79ed745431b5 | CSHL057 | M | 2019-10-15 | L | C57BL/6J | Jax | 3 | None | 2019-12-12 04:43:42 | C57BL/6J |
| 02bc9e07-8a94-4ddf-ae93-cb9fe60ebecf | FMR031 | M | 2021-11-16 | None | B6.129P2-Fmr1 |
None | 1 | None | 2022-01-19 05:04:26 | B6;129P2 |
...
Total: 529
Restriction: Query subjects restricted by other tables
[13]:
# subjects with Behavioural sessions
subject.Subject & acquisition.Session
Out[13]:
| subject_uuid | subject_nickname nickname | sex sex | subject_birth_date birth date | ear_mark ear mark | subject_line name | subject_source name of source | protocol_number protocol number | subject_description | subject_ts | subject_strain |
|---|---|---|---|---|---|---|---|---|---|---|
| 00778394-c956-408d-8a6c-ca3b05a611d5 | KS019 | F | 2019-04-30 | None | C57BL/6J | None | 2 | None | 2019-08-13 17:07:33 | C57BL/6J |
| 00c60db3-74c3-4ee2-9df9-2c84acf84e92 | ibl_witten_10 | F | 2018-11-13 | notag | C57BL/6J | Jax | 3 | None | 2019-09-25 01:33:25 | C57BL/6J |
| 019444e5-2192-4953-855d-084226fb965a | dop_48 | M | 2021-11-23 | 709 | Dat-Cre | Princeton | 2 | None | 2022-04-15 03:37:28 | C57BL/6J |
| 019a22c1-b944-4494-9e38-0e45ae6697bf | SWC_022 | M | 2019-06-18 | NA (Front HP) | C57BL/6J | Charles River | 4 | ID: 990762 | 2019-09-25 01:33:25 | C57BL/6J |
| 01a17d5f-6517-42ec-9dad-9ab24eecb6a9 | PL031 | M | 2021-11-12 | Black front | None | Charles River | 3 | None | 2021-12-18 02:16:54 | C57BL/6J |
...
Total: 1077
[14]:
# subjects without Behavioural sessions
subject.Subject - acquisition.Session
Out[14]:
| subject_uuid | subject_nickname nickname | sex sex | subject_birth_date birth date | ear_mark ear mark | subject_line name | subject_source name of source | protocol_number protocol number | subject_description | subject_ts | subject_strain |
|---|---|---|---|---|---|---|---|---|---|---|
| 0026c82d-39e4-4c6b-acb3-303eb4b24f05 | IBL_32 | M | 2018-04-23 | None | C57BL/6NCrl | Charles River | 1 | None | 2019-08-06 21:30:42 | C57BL/6N |
| 0124f697-16ce-4f59-b87c-e53fcb3a27ac | 6867 | M | 2018-06-25 | None | Thy1-GCaMP6s | CCU - Margarida colonies | 1 | None | 2019-09-25 01:33:25 | C57BL/6J |
| 033e34f1-e352-42bb-97c0-d0faaa0f47ee | human0200 | U | 1900-01-01 | None | None | None | 1 | None | 2019-10-05 04:37:24 | Homo sapiens |
| 034c07c5-69b0-48c7-ab3e-e491e4dbb725 | IBL_25 | M | 2018-04-23 | None | C57BL/6NCrl | Charles River | 1 | None | 2019-08-06 21:30:42 | C57BL/6N |
| 03956b1f-6836-4c5f-9dea-b57b5292b177 | 6870 | M | 2018-06-25 | None | Thy1-GCaMP6s | CCU - Margarida colonies | 1 | None | 2019-09-25 01:33:26 | C57BL/6J |
...
Total: 241
Join *: gather information from different tables
[15]:
subject.Subject * acquisition.Session
Out[15]:
| subject_uuid | session_start_time start time | subject_nickname nickname | sex sex | subject_birth_date birth date | ear_mark ear mark | subject_line name | subject_source name of source | protocol_number protocol number | subject_description | subject_ts | subject_strain | session_uuid | session_number number | session_end_time end time | session_lab name of lab | session_location name of the location | task_protocol | session_type type | session_narrative | session_ts |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 00778394-c956-408d-8a6c-ca3b05a611d5 | 2019-08-07 10:49:41 | KS019 | F | 2019-04-30 | None | C57BL/6J | None | 2 | None | 2019-08-13 17:07:33 | C57BL/6J | 0955e23e-90cf-4250-a092-82fabdf67a24 | 1 | 2019-08-07 11:23:14 | cortexlab | _iblrig_cortexlab_behavior_3 | _iblrig_tasks_habituationChoiceWorld5.2.5 | Experiment | None | 2019-08-13 17:26:00 |
| 00778394-c956-408d-8a6c-ca3b05a611d5 | 2019-08-08 15:32:40 | KS019 | F | 2019-04-30 | None | C57BL/6J | None | 2 | None | 2019-08-13 17:07:33 | C57BL/6J | 996167fa-07bc-4afe-b7b9-4aee0ba75c1f | 1 | 2019-08-08 16:19:23 | cortexlab | _iblrig_cortexlab_behavior_3 | _iblrig_tasks_habituationChoiceWorld5.2.5 | Experiment | None | 2019-08-13 17:26:01 |
| 00778394-c956-408d-8a6c-ca3b05a611d5 | 2019-08-09 10:37:49 | KS019 | F | 2019-04-30 | None | C57BL/6J | None | 2 | None | 2019-08-13 17:07:33 | C57BL/6J | 13e38e6c-0cd6-41f9-a2ab-8d6021fea035 | 1 | 2019-08-09 11:40:12 | cortexlab | _iblrig_cortexlab_behavior_3 | _iblrig_tasks_habituationChoiceWorld5.2.7 | Experiment | None | 2019-08-13 17:26:00 |
| 00778394-c956-408d-8a6c-ca3b05a611d5 | 2019-08-10 11:24:59 | KS019 | F | 2019-04-30 | None | C57BL/6J | None | 2 | None | 2019-08-13 17:07:33 | C57BL/6J | fb9bdf18-76be-452b-ac4e-21d5de3a6f9f | 1 | 2019-08-10 12:11:04 | cortexlab | _iblrig_cortexlab_behavior_3 | _iblrig_tasks_trainingChoiceWorld5.2.7 | Experiment | None | 2019-08-13 17:26:06 |
| 00778394-c956-408d-8a6c-ca3b05a611d5 | 2019-08-12 09:21:03 | KS019 | F | 2019-04-30 | None | C57BL/6J | None | 2 | None | 2019-08-13 17:07:33 | C57BL/6J | d47e9a4c-18dc-4d4d-991c-d30059ec2cbd | 1 | 2019-08-12 10:07:19 | cortexlab | _iblrig_cortexlab_behavior_3 | _iblrig_tasks_trainingChoiceWorld5.2.7 | Experiment | None | 2019-08-14 12:37:53 |
...
Total: 51591
Projection .proj(): focus on attributes of interest
[16]:
subject.Subject.proj()
Out[16]:
| subject_uuid |
|---|
| 01a17d5f-6517-42ec-9dad-9ab24eecb6a9 |
| 01f6648a-dc2e-4de9-934d-a7e92c2e0cd8 |
| 0264ae54-44fd-43a8-b93c-42f43a377f0a |
| 031c2ecf-455f-40c8-9167-7b456b1222e7 |
| 033e34f1-e352-42bb-97c0-d0faaa0f47ee |
...
Total: 1318
[17]:
subject.Subject.proj('subject_birth_date', 'sex')
Out[17]:
| subject_uuid | sex sex | subject_birth_date birth date |
|---|---|---|
| 0026c82d-39e4-4c6b-acb3-303eb4b24f05 | M | 2018-04-23 |
| 00778394-c956-408d-8a6c-ca3b05a611d5 | F | 2019-04-30 |
| 00c60db3-74c3-4ee2-9df9-2c84acf84e92 | F | 2018-11-13 |
| 0124f697-16ce-4f59-b87c-e53fcb3a27ac | M | 2018-06-25 |
| 019444e5-2192-4953-855d-084226fb965a | M | 2021-11-23 |
...
Total: 1318
rename attribute with proj()
[18]:
subject.Subject.proj('sex', dob='subject_birth_date')
Out[18]:
| subject_uuid | sex sex | dob birth date |
|---|---|---|
| 0026c82d-39e4-4c6b-acb3-303eb4b24f05 | M | 2018-04-23 |
| 00778394-c956-408d-8a6c-ca3b05a611d5 | F | 2019-04-30 |
| 00c60db3-74c3-4ee2-9df9-2c84acf84e92 | F | 2018-11-13 |
| 0124f697-16ce-4f59-b87c-e53fcb3a27ac | M | 2018-06-25 |
| 019444e5-2192-4953-855d-084226fb965a | M | 2021-11-23 |
...
Total: 1318
perform simple computations with proj
Example 1: Get the date of a session
[19]:
sessions_with_date = acquisition.Session.proj(session_date='date(session_start_time)')
[20]:
sessions_with_date
Out[20]:
| subject_uuid | session_start_time start time | session_date calculated attribute |
|---|---|---|
| 037d8473-8309-4d7a-96fb-f943be8e8df3 | 2018-10-26 19:58:10 | 2018-10-26 |
| 037d8473-8309-4d7a-96fb-f943be8e8df3 | 2018-10-27 15:34:14 | 2018-10-27 |
| 037d8473-8309-4d7a-96fb-f943be8e8df3 | 2018-10-28 15:48:32 | 2018-10-28 |
| 037d8473-8309-4d7a-96fb-f943be8e8df3 | 2018-10-29 14:44:19 | 2018-10-29 |
| 037d8473-8309-4d7a-96fb-f943be8e8df3 | 2018-10-30 15:01:57 | 2018-10-30 |
...
Total: 51591
Example 2: Get the age of the animal at each session
[21]:
# First get the date of birth and the session date into the same query
q = subject.Subject * acquisition.Session
[22]:
# Then compute the age
q_with_age = q.proj(age='datediff(session_start_time, subject_birth_date)')
q_with_age
Out[22]:
| subject_uuid | session_start_time start time | age calculated attribute |
|---|---|---|
| 00778394-c956-408d-8a6c-ca3b05a611d5 | 2019-08-07 10:49:41 | 99 |
| 00778394-c956-408d-8a6c-ca3b05a611d5 | 2019-08-08 15:32:40 | 100 |
| 00778394-c956-408d-8a6c-ca3b05a611d5 | 2019-08-09 10:37:49 | 101 |
| 00778394-c956-408d-8a6c-ca3b05a611d5 | 2019-08-10 11:24:59 | 102 |
| 00778394-c956-408d-8a6c-ca3b05a611d5 | 2019-08-12 09:21:03 | 104 |
...
Total: 51591
Aggregation .aggr(): simple computation of one table against another table
Example: How many sessions has each subject done so far?
[23]:
subject.Subject.aggr(acquisition.Session, 'subject_nickname', n='count(*)')
Out[23]:
| subject_uuid | subject_nickname nickname | n calculated attribute |
|---|---|---|
| 00778394-c956-408d-8a6c-ca3b05a611d5 | KS019 | 103 |
| 00c60db3-74c3-4ee2-9df9-2c84acf84e92 | ibl_witten_10 | 55 |
| 019444e5-2192-4953-855d-084226fb965a | dop_48 | 33 |
| 019a22c1-b944-4494-9e38-0e45ae6697bf | SWC_022 | 47 |
| 01a17d5f-6517-42ec-9dad-9ab24eecb6a9 | PL031 | 95 |
...
Total: 1077
5. Fetching data
Fetch all fields: fetch()
[24]:
# fetch all data from a table
subjs = subject.Subject.fetch()
subjs[:5]
Out[24]:
array([(UUID('0026c82d-39e4-4c6b-acb3-303eb4b24f05'), 'IBL_32', 'M', datetime.date(2018, 4, 23), None, 'C57BL/6NCrl', 'Charles River', 1, None, datetime.datetime(2019, 8, 6, 21, 30, 42), 'C57BL/6N'),
(UUID('00778394-c956-408d-8a6c-ca3b05a611d5'), 'KS019', 'F', datetime.date(2019, 4, 30), None, 'C57BL/6J', None, 2, None, datetime.datetime(2019, 8, 13, 17, 7, 33), 'C57BL/6J'),
(UUID('00c60db3-74c3-4ee2-9df9-2c84acf84e92'), 'ibl_witten_10', 'F', datetime.date(2018, 11, 13), 'notag', 'C57BL/6J', 'Jax', 3, None, datetime.datetime(2019, 9, 25, 1, 33, 25), 'C57BL/6J'),
(UUID('0124f697-16ce-4f59-b87c-e53fcb3a27ac'), '6867', 'M', datetime.date(2018, 6, 25), None, 'Thy1-GCaMP6s', 'CCU - Margarida colonies', 1, None, datetime.datetime(2019, 9, 25, 1, 33, 25), 'C57BL/6J'),
(UUID('019444e5-2192-4953-855d-084226fb965a'), 'dop_48', 'M', datetime.date(2021, 11, 23), '709', 'Dat-Cre', 'Princeton', 2, None, datetime.datetime(2022, 4, 15, 3, 37, 28), 'C57BL/6J')],
dtype=[('subject_uuid', 'O'), ('subject_nickname', 'O'), ('sex', 'O'), ('subject_birth_date', 'O'), ('ear_mark', 'O'), ('subject_line', 'O'), ('subject_source', 'O'), ('protocol_number', '<i8'), ('subject_description', 'O'), ('subject_ts', 'O'), ('subject_strain', 'O')])
[25]:
subjs['subject_uuid'][:5]
Out[25]:
array([UUID('0026c82d-39e4-4c6b-acb3-303eb4b24f05'),
UUID('00778394-c956-408d-8a6c-ca3b05a611d5'),
UUID('00c60db3-74c3-4ee2-9df9-2c84acf84e92'),
UUID('0124f697-16ce-4f59-b87c-e53fcb3a27ac'),
UUID('019444e5-2192-4953-855d-084226fb965a')], dtype=object)
[26]:
subjs['subject_birth_date'][:5]
Out[26]:
array([datetime.date(2018, 4, 23), datetime.date(2019, 4, 30),
datetime.date(2018, 11, 13), datetime.date(2018, 6, 25),
datetime.date(2021, 11, 23)], dtype=object)
[27]:
# fetch as a list of dictionaries
subjs_dict = subject.Subject.fetch(as_dict=True)
subjs_dict[:5]
Out[27]:
[{'subject_uuid': UUID('0026c82d-39e4-4c6b-acb3-303eb4b24f05'),
'subject_nickname': 'IBL_32',
'sex': 'M',
'subject_birth_date': datetime.date(2018, 4, 23),
'ear_mark': None,
'subject_line': 'C57BL/6NCrl',
'subject_source': 'Charles River',
'protocol_number': 1,
'subject_description': None,
'subject_ts': datetime.datetime(2019, 8, 6, 21, 30, 42),
'subject_strain': 'C57BL/6N'},
{'subject_uuid': UUID('00778394-c956-408d-8a6c-ca3b05a611d5'),
'subject_nickname': 'KS019',
'sex': 'F',
'subject_birth_date': datetime.date(2019, 4, 30),
'ear_mark': None,
'subject_line': 'C57BL/6J',
'subject_source': None,
'protocol_number': 2,
'subject_description': None,
'subject_ts': datetime.datetime(2019, 8, 13, 17, 7, 33),
'subject_strain': 'C57BL/6J'},
{'subject_uuid': UUID('00c60db3-74c3-4ee2-9df9-2c84acf84e92'),
'subject_nickname': 'ibl_witten_10',
'sex': 'F',
'subject_birth_date': datetime.date(2018, 11, 13),
'ear_mark': 'notag',
'subject_line': 'C57BL/6J',
'subject_source': 'Jax',
'protocol_number': 3,
'subject_description': None,
'subject_ts': datetime.datetime(2019, 9, 25, 1, 33, 25),
'subject_strain': 'C57BL/6J'},
{'subject_uuid': UUID('0124f697-16ce-4f59-b87c-e53fcb3a27ac'),
'subject_nickname': '6867',
'sex': 'M',
'subject_birth_date': datetime.date(2018, 6, 25),
'ear_mark': None,
'subject_line': 'Thy1-GCaMP6s',
'subject_source': 'CCU - Margarida colonies',
'protocol_number': 1,
'subject_description': None,
'subject_ts': datetime.datetime(2019, 9, 25, 1, 33, 25),
'subject_strain': 'C57BL/6J'},
{'subject_uuid': UUID('019444e5-2192-4953-855d-084226fb965a'),
'subject_nickname': 'dop_48',
'sex': 'M',
'subject_birth_date': datetime.date(2021, 11, 23),
'ear_mark': '709',
'subject_line': 'Dat-Cre',
'subject_source': 'Princeton',
'protocol_number': 2,
'subject_description': None,
'subject_ts': datetime.datetime(2022, 4, 15, 3, 37, 28),
'subject_strain': 'C57BL/6J'}]
[28]:
# fetch as pandas dataframe
subjs_df = subject.Subject.fetch(format='frame')
subjs_df[:5]
Out[28]:
| subject_nickname | sex | subject_birth_date | ear_mark | subject_line | subject_source | protocol_number | subject_description | subject_ts | subject_strain | |
|---|---|---|---|---|---|---|---|---|---|---|
| subject_uuid | ||||||||||
| 0026c82d-39e4-4c6b-acb3-303eb4b24f05 | IBL_32 | M | 2018-04-23 | None | C57BL/6NCrl | Charles River | 1 | None | 2019-08-06 21:30:42 | C57BL/6N |
| 00778394-c956-408d-8a6c-ca3b05a611d5 | KS019 | F | 2019-04-30 | None | C57BL/6J | None | 2 | None | 2019-08-13 17:07:33 | C57BL/6J |
| 00c60db3-74c3-4ee2-9df9-2c84acf84e92 | ibl_witten_10 | F | 2018-11-13 | notag | C57BL/6J | Jax | 3 | None | 2019-09-25 01:33:25 | C57BL/6J |
| 0124f697-16ce-4f59-b87c-e53fcb3a27ac | 6867 | M | 2018-06-25 | None | Thy1-GCaMP6s | CCU - Margarida colonies | 1 | None | 2019-09-25 01:33:25 | C57BL/6J |
| 019444e5-2192-4953-855d-084226fb965a | dop_48 | M | 2021-11-23 | 709 | Dat-Cre | Princeton | 2 | None | 2022-04-15 03:37:28 | C57BL/6J |
[29]:
# fetch the primary key
pk = subject.Subject.fetch('KEY')
pk[:5]
Out[29]:
[{'subject_uuid': UUID('01a17d5f-6517-42ec-9dad-9ab24eecb6a9')},
{'subject_uuid': UUID('01f6648a-dc2e-4de9-934d-a7e92c2e0cd8')},
{'subject_uuid': UUID('0264ae54-44fd-43a8-b93c-42f43a377f0a')},
{'subject_uuid': UUID('031c2ecf-455f-40c8-9167-7b456b1222e7')},
{'subject_uuid': UUID('033e34f1-e352-42bb-97c0-d0faaa0f47ee')}]
[30]:
# fetch specific attributes
dob, sex = subject.Subject.fetch('subject_birth_date', 'sex')
dob[:5]
Out[30]:
array([datetime.date(2018, 4, 23), datetime.date(2019, 4, 30),
datetime.date(2018, 11, 13), datetime.date(2018, 6, 25),
datetime.date(2021, 11, 23)], dtype=object)
[31]:
info = subject.Subject.fetch('subject_birth_date', 'sex', as_dict=True)
info[:5]
Out[31]:
[{'sex': 'M', 'subject_birth_date': datetime.date(2018, 4, 23)},
{'sex': 'F', 'subject_birth_date': datetime.date(2019, 4, 30)},
{'sex': 'F', 'subject_birth_date': datetime.date(2018, 11, 13)},
{'sex': 'M', 'subject_birth_date': datetime.date(2018, 6, 25)},
{'sex': 'M', 'subject_birth_date': datetime.date(2021, 11, 23)}]
Fetch data from only one entry: fetch1
[32]:
ibl_witten_10 = (subject.Subject & {'subject_nickname': 'ibl_witten_10'}).fetch1('KEY')
[33]:
ibl_witten_10
Out[33]:
{'subject_uuid': UUID('00c60db3-74c3-4ee2-9df9-2c84acf84e92')}
[34]:
IBL_10 = (subject.Subject & {'subject_nickname': 'IBL_10'}).fetch1()
[35]:
IBL_10
Out[35]:
{'subject_uuid': UUID('7c751b49-55a6-4eac-9bdb-367faf2a18ee'),
'subject_nickname': 'IBL_10',
'sex': 'M',
'subject_birth_date': datetime.date(2018, 4, 2),
'ear_mark': None,
'subject_line': 'C57BL/6NCrl',
'subject_source': 'Charles River',
'protocol_number': 1,
'subject_description': None,
'subject_ts': datetime.datetime(2019, 8, 6, 21, 30, 42),
'subject_strain': 'C57BL/6N'}