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
Connected to https://alyx.internationalbrainlab.org as mayo

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_end_criteria',
 'group_shared_ephys',
 'group_shared_sfndata',
 'group_shared_testing',
 '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_dj_acquisition',
 'ibl_dj_action',
 'ibl_dj_behavior',
 'ibl_dj_data',
 'ibl_dj_reference',
 'ibl_dj_subject',
 'ibl_ephys',
 'ibl_histology',
 'ibl_ingest_acquisition',
 'ibl_ingest_action',
 'ibl_ingest_data',
 'ibl_ingest_ephys',
 'ibl_ingest_histology',
 'ibl_ingest_job',
 'ibl_ingest_reference',
 'ibl_ingest_subject',
 'ibl_jaib1_tutorial',
 'ibl_patch',
 'ibl_plotting_behavior',
 'ibl_plotting_ephys',
 'ibl_public',
 '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_data
Imported data from FlatIron: ibl_behavior and ibl_ephys
Computed analzyed results: ibl_analyses_behavior

2. 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()

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
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

...

Total: 856

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
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
01be78e7-8741-4b40-bd64-79ed745431b5 CSHL057 M 2019-10-15 L C57BL/6J Jax 3 None 2019-12-12 04:43:42 C57BL/6J
030f3e05-9db0-46ba-a6ce-4274ff09b39e KS031 M 2019-12-10 None C57BL/6J None 3 None 2020-02-14 04:56:14 C57BL/6J

...

Total: 409

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
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
01be78e7-8741-4b40-bd64-79ed745431b5 CSHL057 M 2019-10-15 L C57BL/6J Jax 3 None 2019-12-12 04:43:42 C57BL/6J
030f3e05-9db0-46ba-a6ce-4274ff09b39e KS031 M 2019-12-10 None C57BL/6J None 3 None 2020-02-14 04:56:14 C57BL/6J
05ff9c40-a1c6-479c-9655-3125b41a0673 lic_1 M 2019-12-28 NA Dat-Cre None 3 None 2019-08-06 21:30:42 C57BL/6J

...

Total: 431

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

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
01be78e7-8741-4b40-bd64-79ed745431b5 CSHL057 M 2019-10-15 L C57BL/6J Jax 3 None 2019-12-12 04:43:42 C57BL/6J
030f3e05-9db0-46ba-a6ce-4274ff09b39e KS031 M 2019-12-10 None C57BL/6J None 3 None 2020-02-14 04:56:14 C57BL/6J
05ff9c40-a1c6-479c-9655-3125b41a0673 lic_1 M 2019-12-28 NA Dat-Cre None 3 None 2019-08-06 21:30:42 C57BL/6J
069f108c-c144-40c1-953d-2502950de79d CSK-scan-007 M 2019-06-13 None VGAT-ChR2 CSHL-Harris 3 clear skull cap for inhibition scan in IBL task 2019-08-30 04:24:54 B6.Cg

...

Total: 248

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
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
01be78e7-8741-4b40-bd64-79ed745431b5 CSHL057 M 2019-10-15 L C57BL/6J Jax 3 None 2019-12-12 04:43:42 C57BL/6J
02120449-9b19-4276-a434-513886c2fb19 ibl_witten_07 F 2018-11-13 None C57BL/6J Jax 3 None 2019-09-25 01:33:25 C57BL/6J

...

Total: 672

[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: 184

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: 26634

Projection .proj(): focus on attributes of interest

[16]:
subject.Subject.proj()
Out[16]:

subject_uuid

031c2ecf-455f-40c8-9167-7b456b1222e7
033e34f1-e352-42bb-97c0-d0faaa0f47ee
04631e68-7bae-484f-b7ff-bdbef89a5e28
05e81ab3-073f-4f5b-a755-63d096f035e8
07212dba-e186-4f21-ae90-51ec42f18e52

...

Total: 856

[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
019a22c1-b944-4494-9e38-0e45ae6697bf M 2019-06-18

...

Total: 856

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
019a22c1-b944-4494-9e38-0e45ae6697bf M 2019-06-18

...

Total: 856

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: 26634

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: 26634

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
019a22c1-b944-4494-9e38-0e45ae6697bf SWC_022 47
01be78e7-8741-4b40-bd64-79ed745431b5 CSHL057 12
02120449-9b19-4276-a434-513886c2fb19 ibl_witten_07 140

...

Total: 672

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('019a22c1-b944-4494-9e38-0e45ae6697bf'), 'SWC_022', 'M', datetime.date(2019, 6, 18), 'NA (Front HP)', 'C57BL/6J', 'Charles River', 4, 'ID: 990762', datetime.datetime(2019, 9, 25, 1, 33, 25), '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('019a22c1-b944-4494-9e38-0e45ae6697bf')], 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(2019, 6, 18)], 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('019a22c1-b944-4494-9e38-0e45ae6697bf'),
  'subject_nickname': 'SWC_022',
  'sex': 'M',
  'subject_birth_date': datetime.date(2019, 6, 18),
  'ear_mark': 'NA (Front HP)',
  'subject_line': 'C57BL/6J',
  'subject_source': 'Charles River',
  'protocol_number': 4,
  'subject_description': 'ID: 990762',
  'subject_ts': datetime.datetime(2019, 9, 25, 1, 33, 25),
  '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
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
[29]:
# fetch the primary key
pk = subject.Subject.fetch('KEY')
pk[:5]
Out[29]:
[{'subject_uuid': UUID('031c2ecf-455f-40c8-9167-7b456b1222e7')},
 {'subject_uuid': UUID('033e34f1-e352-42bb-97c0-d0faaa0f47ee')},
 {'subject_uuid': UUID('04631e68-7bae-484f-b7ff-bdbef89a5e28')},
 {'subject_uuid': UUID('05e81ab3-073f-4f5b-a755-63d096f035e8')},
 {'subject_uuid': UUID('07212dba-e186-4f21-ae90-51ec42f18e52')}]
[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(2019, 6, 18)], 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(2019, 6, 18)}]

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'}