def from_lims(cls, ophys_experiment_id: int,
lims_db: PostgresQueryMixin) -> "OphysSessionId":
query = """
SELECT oe.ophys_session_id
FROM ophys_experiments oe
WHERE id = {};
""".format(ophys_experiment_id)
session_id = lims_db.fetchone(query, strict=False)
return cls(session_id=session_id)
def from_lims(
cls, db: PostgresQueryMixin,
ophys_experiment_id: Union[int, str]
) -> "SyncFile":
query = SYNC_FILE_QUERY_TEMPLATE.format(
ophys_experiment_id=ophys_experiment_id
)
filepath = db.fetchone(query, strict=True)
return cls(filepath=filepath)
def from_lims(cls, ophys_experiment_id: int,
lims_db: PostgresQueryMixin) -> "ExperimentContainerId":
query = """
SELECT visual_behavior_experiment_container_id
FROM ophys_experiments_visual_behavior_experiment_containers
WHERE ophys_experiment_id = {};
""".format(ophys_experiment_id)
container_id = lims_db.fetchone(query, strict=False)
return cls(experiment_container_id=container_id)
def from_lims(
cls, db: PostgresQueryMixin,
behavior_session_id: Union[int, str]
) -> "StimulusFile":
query = STIMULUS_FILE_QUERY_TEMPLATE.format(
behavior_session_id=behavior_session_id
)
filepath = db.fetchone(query, strict=True)
return cls(filepath=filepath)
def from_lims(cls, behavior_session_id: int,
lims_db: PostgresQueryMixin) -> "FullGenotype":
query = f"""
SELECT d.full_genotype
FROM behavior_sessions bs
JOIN donors d ON d.id=bs.donor_id
WHERE bs.id= {behavior_session_id};
"""
genotype = lims_db.fetchone(query, strict=True)
return cls(full_genotype=genotype)
def _get_targeted_structure_from_lims(ophys_experiment_id: int,
lims_db: PostgresQueryMixin) -> str:
query = """
SELECT st.acronym
FROM ophys_experiments oe
LEFT JOIN structures st ON st.id = oe.targeted_structure_id
WHERE oe.id = {};
""".format(ophys_experiment_id)
targeted_structure = lims_db.fetchone(query, strict=True)
return targeted_structure
def from_lims(cls, behavior_session_id: int,
lims_db: PostgresQueryMixin) -> "Equipment":
query = f"""
SELECT e.name AS device_name
FROM behavior_sessions bs
JOIN equipment e ON e.id = bs.equipment_id
WHERE bs.id = {behavior_session_id};
"""
equipment_name = lims_db.fetchone(query, strict=True)
return cls(equipment_name=equipment_name)
def from_lims(cls, ophys_experiment_id: int,
lims_db: PostgresQueryMixin) -> "ImagingDepth":
query = """
SELECT id.depth
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
LEFT JOIN imaging_depths id ON id.id = oe.imaging_depth_id
WHERE oe.id = {};
""".format(ophys_experiment_id)
imaging_depth = lims_db.fetchone(query, strict=True)
return cls(imaging_depth=imaging_depth)
def from_lims(cls, behavior_session_id: int,
lims_db: PostgresQueryMixin) -> "Sex":
query = f"""
SELECT g.name AS sex
FROM behavior_sessions bs
JOIN donors d ON bs.donor_id = d.id
JOIN genders g ON g.id = d.gender_id
WHERE bs.id = {behavior_session_id};
"""
sex = lims_db.fetchone(query, strict=True)
return cls(sex=sex)
def from_lims(cls, ophys_experiment_id: int,
lims_db: PostgresQueryMixin) -> "DateOfAcquisitionOphys":
query = f"""
SELECT os.date_of_acquisition
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
WHERE oe.id = {ophys_experiment_id};
"""
doa = lims_db.fetchone(query=query)
doa = cls._postprocess_lims_datetime(datetime=doa)
return DateOfAcquisitionOphys(date_of_acquisition=doa)
def from_lims(cls, behavior_session_id: int,
lims_db: PostgresQueryMixin) -> "Age":
query = f"""
SELECT a.name AS age
FROM behavior_sessions bs
JOIN donors d ON d.id = bs.donor_id
JOIN ages a ON a.id = d.age_id
WHERE bs.id = {behavior_session_id};
"""
age = lims_db.fetchone(query, strict=True)
age = cls._age_code_to_days(age=age)
return cls(age=age)
def from_lims(cls, behavior_session_id: int,
lims_db: PostgresQueryMixin) -> "DateOfAcquisition":
query = """
SELECT bs.date_of_acquisition
FROM behavior_sessions bs
WHERE bs.id = {};
""".format(behavior_session_id)
experiment_date = lims_db.fetchone(query, strict=True)
experiment_date = cls._postprocess_lims_datetime(
datetime=experiment_date)
return cls(date_of_acquisition=experiment_date)
def from_lims(cls, behavior_session_id: int,
lims_db: PostgresQueryMixin) -> "ForagingId":
query = f"""
SELECT
foraging_id
FROM
behavior_sessions
WHERE
behavior_sessions.id = {behavior_session_id};
"""
foraging_id = lims_db.fetchone(query, strict=True)
foraging_id = uuid.UUID(foraging_id)
return cls(foraging_id=foraging_id)
def from_lims(cls, db: PostgresQueryMixin,
ophys_experiment_id: Union[int, str]) -> "EyeTrackingFile":
query = f"""
SELECT wkf.storage_directory || wkf.filename AS eye_tracking_file
FROM ophys_experiments oe
LEFT JOIN well_known_files wkf ON wkf.attachable_id = oe.ophys_session_id
JOIN well_known_file_types wkft ON wkf.well_known_file_type_id = wkft.id
WHERE wkf.attachable_type = 'OphysSession'
AND wkft.name = 'EyeTracking Ellipses'
AND oe.id = {ophys_experiment_id};
""" # noqa E501
filepath = db.fetchone(query, strict=True)
return cls(filepath=filepath)
def from_lims(cls, ophys_experiment_id: int,
lims_db: PostgresQueryMixin) -> "ProjectCode":
query = f"""
SELECT projects.code AS project_code
FROM ophys_sessions
JOIN projects ON projects.id = ophys_sessions.project_id
WHERE ophys_sessions.id = (
SELECT oe.ophys_session_id
FROM ophys_experiments oe
WHERE oe.id = {ophys_experiment_id}
)
"""
project_code = lims_db.fetchone(query, strict=True)
return cls(project_code=project_code)
def from_lims(
cls, db: PostgresQueryMixin,
ophys_experiment_id: Union[int, str]
) -> "EventDetectionFile":
query = f'''
SELECT wkf.storage_directory || wkf.filename AS event_detection_filepath
FROM ophys_experiments oe
LEFT JOIN well_known_files wkf ON wkf.attachable_id = oe.id
JOIN well_known_file_types wkft ON wkf.well_known_file_type_id = wkft.id
WHERE wkft.name = 'OphysEventTraceFile'
AND oe.id = {ophys_experiment_id};
''' # noqa E501
filepath = safe_system_path(db.fetchone(query, strict=True))
return cls(filepath=filepath)
def from_lims(cls, db: PostgresQueryMixin,
ophys_experiment_id: Union[int, str]) -> "DemixFile":
query = """
SELECT wkf.storage_directory || wkf.filename AS demix_file
FROM ophys_experiments oe
JOIN well_known_files wkf ON wkf.attachable_id = oe.id
JOIN well_known_file_types wkft
ON wkft.id = wkf.well_known_file_type_id
WHERE wkf.attachable_type = 'OphysExperiment'
AND wkft.name = 'DemixedTracesFile'
AND oe.id = {};
""".format(ophys_experiment_id)
filepath = db.fetchone(query, strict=True)
return cls(filepath=filepath)
def from_lims(
cls, db: PostgresQueryMixin,
ophys_experiment_id: int
) -> "BehaviorSessionId":
query = f"""
SELECT bs.id
FROM ophys_experiments oe
-- every ophys_experiment should have an ophys_session
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
JOIN behavior_sessions bs ON os.id = bs.ophys_session_id
WHERE oe.id = {ophys_experiment_id};
"""
behavior_session_id = db.fetchone(query, strict=True)
return cls(behavior_session_id=behavior_session_id)
def from_lims(
cls, db: PostgresQueryMixin,
ophys_experiment_id: Union[int, str]
) -> "RigidMotionTransformFile":
query = """
SELECT wkf.storage_directory || wkf.filename AS transform_file
FROM ophys_experiments oe
JOIN well_known_files wkf ON wkf.attachable_id = oe.id
JOIN well_known_file_types wkft
ON wkft.id = wkf.well_known_file_type_id
WHERE wkf.attachable_type = 'OphysExperiment'
AND wkft.name = 'OphysMotionXyOffsetData'
AND oe.id = {};
""".format(ophys_experiment_id)
filepath = safe_system_path(db.fetchone(query, strict=True))
return cls(filepath=filepath)
def from_lims(cls, behavior_session_id: int,
lims_db: PostgresQueryMixin) -> "MouseId":
# TODO: Should this even be included?
# Found sometimes there were entries with NONE which is
# why they are filtered out; also many entries in the table
# match the donor_id, which is why used DISTINCT
query = f"""
SELECT DISTINCT(sp.external_specimen_name)
FROM behavior_sessions bs
JOIN donors d ON bs.donor_id=d.id
JOIN specimens sp ON sp.donor_id=d.id
WHERE bs.id={behavior_session_id}
AND sp.external_specimen_name IS NOT NULL;
"""
mouse_id = int(lims_db.fetchone(query, strict=True))
return cls(mouse_id=mouse_id)
class OphysLimsApi(CachedInstanceMethodMixin):
def __init__(self,
ophys_experiment_id: int,
lims_credentials: Optional[DbCredentials] = None):
self.ophys_experiment_id = ophys_experiment_id
if lims_credentials:
self.lims_db = PostgresQueryMixin(
dbname=lims_credentials.dbname,
user=lims_credentials.user,
host=lims_credentials.host,
password=lims_credentials.password,
port=lims_credentials.port)
else:
# Currying is equivalent to decorator syntactic sugar
self.lims_db = (credential_injector(LIMS_DB_CREDENTIAL_MAP)(
PostgresQueryMixin)())
def get_ophys_experiment_id(self):
return self.ophys_experiment_id
@memoize
def get_ophys_experiment_dir(self):
query = '''
SELECT oe.storage_directory
FROM ophys_experiments oe
WHERE oe.id = {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_nwb_filepath(self):
query = '''
SELECT wkf.storage_directory || wkf.filename AS nwb_file
FROM ophys_experiments oe
LEFT JOIN well_known_files wkf ON wkf.attachable_id=oe.id AND wkf.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'NWBOphys')
WHERE oe.id = {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_sync_file(self, ophys_experiment_id=None):
query = '''
SELECT sync.storage_directory || sync.filename AS sync_file
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
LEFT JOIN well_known_files sync ON sync.attachable_id=os.id AND sync.attachable_type = 'OphysSession' AND sync.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'OphysRigSync')
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_max_projection_file(self):
query = '''
SELECT wkf.storage_directory || wkf.filename AS maxint_file
FROM ophys_experiments oe
LEFT JOIN ophys_cell_segmentation_runs ocsr ON ocsr.ophys_experiment_id = oe.id AND ocsr.current = 't'
LEFT JOIN well_known_files wkf ON wkf.attachable_id=ocsr.id AND wkf.attachable_type = 'OphysCellSegmentationRun' AND wkf.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'OphysMaxIntImage')
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_max_projection(self, image_api=None):
if image_api is None:
image_api = ImageApi
maxInt_a13_file = self.get_max_projection_file()
pixel_size = self.get_surface_2p_pixel_size_um()
max_projection = mpimg.imread(maxInt_a13_file)
return ImageApi.serialize(max_projection,
[pixel_size / 1000., pixel_size / 1000.],
'mm')
@memoize
def get_targeted_structure(self):
query = '''
SELECT st.acronym
FROM ophys_experiments oe
LEFT JOIN structures st ON st.id=oe.targeted_structure_id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_imaging_depth(self):
query = '''
SELECT id.depth
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
LEFT JOIN imaging_depths id ON id.id=oe.imaging_depth_id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_stimulus_name(self):
query = '''
SELECT os.stimulus_name
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
stimulus_name = self.lims_db.fetchone(query, strict=False)
stimulus_name = 'Unknown' if stimulus_name is None else stimulus_name
return stimulus_name
@memoize
def get_experiment_date(self):
query = '''
SELECT os.date_of_acquisition
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
experiment_date = self.lims_db.fetchone(query, strict=True)
return pytz.utc.localize(experiment_date)
@memoize
def get_reporter_line(self):
query = '''
SELECT g.name as reporter_line
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
JOIN specimens sp ON sp.id=os.specimen_id
JOIN donors d ON d.id=sp.donor_id
JOIN donors_genotypes dg ON dg.donor_id=d.id
JOIN genotypes g ON g.id=dg.genotype_id
JOIN genotype_types gt ON gt.id=g.genotype_type_id AND gt.name = 'reporter'
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
result = self.lims_db.fetchall(query)
if result is None or len(result) < 1:
raise OneOrMoreResultExpectedError(
'Expected one or more, but received: {} from query'.format(
result))
return result
@memoize
def get_driver_line(self):
query = '''
SELECT g.name as driver_line
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
JOIN specimens sp ON sp.id=os.specimen_id
JOIN donors d ON d.id=sp.donor_id
JOIN donors_genotypes dg ON dg.donor_id=d.id
JOIN genotypes g ON g.id=dg.genotype_id
JOIN genotype_types gt ON gt.id=g.genotype_type_id AND gt.name = 'driver'
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
result = self.lims_db.fetchall(query)
if result is None or len(result) < 1:
raise OneOrMoreResultExpectedError(
'Expected one or more, but received: {} from query'.format(
result))
return result
@memoize
def get_external_specimen_name(self, ophys_experiment_id=None):
query = '''
SELECT sp.external_specimen_name
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
JOIN specimens sp ON sp.id=os.specimen_id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return int(self.lims_db.fetchone(query, strict=True))
@memoize
def get_full_genotype(self):
query = '''
SELECT d.full_genotype
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
JOIN specimens sp ON sp.id=os.specimen_id
JOIN donors d ON d.id=sp.donor_id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_equipment_id(self):
query = '''
SELECT e.name
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
JOIN equipment e ON e.id=os.equipment_id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_dff_file(self):
query = '''
SELECT dff.storage_directory || dff.filename AS dff_file
FROM ophys_experiments oe
LEFT JOIN well_known_files dff ON dff.attachable_id=oe.id AND dff.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'OphysDffTraceFile')
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_cell_roi_ids(self):
cell_specimen_table = self.get_cell_specimen_table()
assert cell_specimen_table.index.name == 'cell_specimen_id'
return cell_specimen_table['cell_roi_id'].values
@memoize
def get_objectlist_file(self):
query = '''
SELECT obj.storage_directory || obj.filename AS obj_file
FROM ophys_experiments oe
LEFT JOIN ophys_cell_segmentation_runs ocsr ON ocsr.ophys_experiment_id = oe.id AND ocsr.current = 't'
LEFT JOIN well_known_files obj ON obj.attachable_id=ocsr.id AND obj.attachable_type = 'OphysCellSegmentationRun' AND obj.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'OphysSegmentationObjects')
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_demix_file(self):
query = '''
SELECT wkf.storage_directory || wkf.filename AS demix_file
FROM ophys_experiments oe
LEFT JOIN well_known_files wkf ON wkf.attachable_id=oe.id AND wkf.attachable_type = 'OphysExperiment' AND wkf.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'DemixedTracesFile')
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_average_intensity_projection_image_file(self):
query = '''
SELECT avg.storage_directory || avg.filename AS avgint_file
FROM ophys_experiments oe
LEFT JOIN ophys_cell_segmentation_runs ocsr ON ocsr.ophys_experiment_id = oe.id AND ocsr.current = 't'
LEFT JOIN well_known_files avg ON avg.attachable_id=ocsr.id AND avg.attachable_type = 'OphysCellSegmentationRun' AND avg.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'OphysAverageIntensityProjectionImage')
WHERE oe.id = {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_rigid_motion_transform_file(self):
query = '''
SELECT tra.storage_directory || tra.filename AS transform_file
FROM ophys_experiments oe
LEFT JOIN well_known_files tra ON tra.attachable_id=oe.id AND tra.attachable_type = 'OphysExperiment' AND tra.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'OphysMotionXyOffsetData')
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_motion_corrected_image_stack_file(self):
query = f"""
select wkf.storage_directory || wkf.filename
from well_known_files wkf
join well_known_file_types wkft on wkft.id = wkf.well_known_file_type_id
where wkft.name = 'MotionCorrectedImageStack'
and wkf.attachable_id = {self.get_ophys_experiment_id()}
"""
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_foraging_id(self):
query = '''
SELECT os.foraging_id
FROM ophys_experiments oe
LEFT JOIN ophys_sessions os ON oe.ophys_session_id = os.id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
def get_raw_dff_data(self):
dff_path = self.get_dff_file()
with h5py.File(dff_path, 'r') as raw_file:
dff_traces = np.asarray(raw_file['data'])
return dff_traces
@memoize
def get_rig_name(self):
query = '''
select e.name as device_name
from ophys_experiments oe
join ophys_sessions os on os.id = oe.ophys_session_id
join equipment e on e.id = os.equipment_id
where oe.id = {}
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_field_of_view_shape(self):
query = '''
select {}
from ophys_experiments oe
where oe.id = {}
'''
X = {
c:
self.lims_db.fetchone(query.format('oe.movie_{}'.format(c),
self.get_ophys_experiment_id()),
strict=True)
for c in ['width', 'height']
}
return X
@memoize
def get_metadata(self):
metadata = {}
metadata['rig_name'] = self.get_rig_name()
metadata['sex'] = self.get_sex()
metadata['age'] = self.get_age()
metadata['excitation_lambda'] = 910.
metadata['emission_lambda'] = 520.
metadata['indicator'] = 'GCAMP6f'
metadata['field_of_view_width'] = self.get_field_of_view_shape(
)['width']
metadata['field_of_view_height'] = self.get_field_of_view_shape(
)['height']
return metadata
@memoize
def get_ophys_cell_segmentation_run_id(self):
query = '''
select oseg.id
from ophys_experiments oe
join ophys_cell_segmentation_runs oseg on oe.id = oseg.ophys_experiment_id
where oe.id = {} and oseg.current = 't'
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_raw_cell_specimen_table_dict(self):
ophys_cell_segmentation_run_id = self.get_ophys_cell_segmentation_run_id(
)
query = '''
select *
from cell_rois cr
where cr.ophys_cell_segmentation_run_id = {}
'''.format(ophys_cell_segmentation_run_id)
cell_specimen_table = pd.read_sql(
query,
self.lims_db.get_connection()).rename(columns={
'id': 'cell_roi_id',
'mask_matrix': 'image_mask'
})
cell_specimen_table.drop(
['ophys_experiment_id', 'ophys_cell_segmentation_run_id'],
inplace=True,
axis=1)
return cell_specimen_table.to_dict()
@memoize
def get_cell_specimen_table(self):
cell_specimen_table = pd.DataFrame.from_dict(
self.get_raw_cell_specimen_table_dict()).set_index(
'cell_roi_id').sort_index()
fov_width, fov_height = self.get_field_of_view_shape(
)['width'], self.get_field_of_view_shape()['height']
image_mask_list = []
for sub_mask in cell_specimen_table['image_mask'].values:
curr_roi = roi.create_roi_mask(fov_width,
fov_height, [(fov_width - 1), 0,
(fov_height - 1), 0],
roi_mask=np.array(sub_mask,
dtype=np.bool))
image_mask_list.append(curr_roi.get_mask_plane().astype(np.bool))
cell_specimen_table['image_mask'] = image_mask_list
cell_specimen_table = cell_specimen_table[sorted(
cell_specimen_table.columns)]
cell_specimen_table.index.rename('cell_roi_id', inplace=True)
cell_specimen_table.reset_index(inplace=True)
cell_specimen_table.set_index('cell_specimen_id', inplace=True)
return cell_specimen_table
@memoize
def get_surface_2p_pixel_size_um(self):
query = '''
SELECT sc.resolution
FROM ophys_experiments oe
JOIN scans sc ON sc.image_id=oe.ophys_primary_image_id
WHERE oe.id = {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_workflow_state(self):
query = '''
SELECT oe.workflow_state
FROM ophys_experiments oe
WHERE oe.id = {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_sex(self):
query = '''
SELECT g.name as sex
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
JOIN specimens sp ON sp.id=os.specimen_id
JOIN donors d ON d.id=sp.donor_id
JOIN genders g ON g.id=d.gender_id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_age(self):
query = '''
SELECT a.name as age
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
JOIN specimens sp ON sp.id=os.specimen_id
JOIN donors d ON d.id=sp.donor_id
JOIN ages a ON a.id=d.age_id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
class BehaviorDataLimsApi(CachedInstanceMethodMixin, BehaviorBase):
def __init__(self, behavior_session_id: int,
lims_credentials: Optional[DbCredentials] = None,
mtrain_credentials: Optional[DbCredentials] = None):
super().__init__()
if mtrain_credentials:
self.mtrain_db = PostgresQueryMixin(
dbname=mtrain_credentials.dbname, user=mtrain_credentials.user,
host=mtrain_credentials.host, port=mtrain_credentials.port,
password=mtrain_credentials.password)
else:
self.mtrain_db = (credential_injector(MTRAIN_DB_CREDENTIAL_MAP)
(PostgresQueryMixin)())
if lims_credentials:
self.lims_db = PostgresQueryMixin(
dbname=lims_credentials.dbname, user=lims_credentials.user,
host=lims_credentials.host, port=lims_credentials.port,
password=lims_credentials.password)
else:
self.lims_db = (credential_injector(LIMS_DB_CREDENTIAL_MAP)
(PostgresQueryMixin)())
self.behavior_session_id = behavior_session_id
ids = self._get_ids()
self.ophys_experiment_ids = ids.get("ophys_experiment_ids")
self.ophys_session_id = ids.get("ophys_session_id")
self.behavior_training_id = ids.get("behavior_training_id")
self.foraging_id = ids.get("foraging_id")
self.ophys_container_id = ids.get("ophys_container_id")
def _get_ids(self) -> Dict[str, Optional[Union[int, List[int]]]]:
"""Fetch ids associated with this behavior_session_id. If there is no
id, return None.
:returns: Dictionary of ids with the following keys:
behavior_training_id: int -- Only if was a training session
ophys_session_id: int -- None if have behavior_training_id
ophys_experiment_ids: List[int] -- only if have ophys_session_id
foraging_id: int
:rtype: dict
"""
# Get all ids from the behavior_sessions table
query = f"""
SELECT
ophys_session_id, behavior_training_id, foraging_id
FROM
behavior_sessions
WHERE
behavior_sessions.id = {self.behavior_session_id};
"""
ids_response = self.lims_db.select(query)
if len(ids_response) > 1:
raise OneResultExpectedError
ids_dict = ids_response.iloc[0].to_dict()
# Get additional ids if also an ophys session
# (experiment_id, container_id)
if ids_dict.get("ophys_session_id"):
oed_query = f"""
SELECT id
FROM ophys_experiments
WHERE ophys_session_id = {ids_dict["ophys_session_id"]};
"""
oed = self.lims_db.fetchall(oed_query)
if len(oed) == 0:
oed = None
container_query = f"""
SELECT DISTINCT
visual_behavior_experiment_container_id id
FROM
ophys_experiments_visual_behavior_experiment_containers
WHERE
ophys_experiment_id IN ({",".join(set(map(str, oed)))});
"""
try:
container_id = self.lims_db.fetchone(container_query, strict=True)
except OneResultExpectedError:
container_id = None
ids_dict.update({"ophys_experiment_ids": oed,
"ophys_container_id": container_id})
else:
ids_dict.update({"ophys_experiment_ids": None,
"ophys_container_id": None})
return ids_dict
def get_behavior_session_id(self) -> int:
"""Getter to be consistent with BehaviorOphysLimsApi."""
return self.behavior_session_id
def get_behavior_session_uuid(self) -> Optional[int]:
data = self._behavior_stimulus_file()
return data.get("session_uuid")
def get_behavior_stimulus_file(self) -> str:
"""Return the path to the StimulusPickle file for a session.
:rtype: str
"""
query = f"""
SELECT
stim.storage_directory || stim.filename AS stim_file
FROM
well_known_files stim
WHERE
stim.attachable_id = {self.behavior_session_id}
AND stim.attachable_type = 'BehaviorSession'
AND stim.well_known_file_type_id IN (
SELECT id
FROM well_known_file_types
WHERE name = 'StimulusPickle');
"""
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def _behavior_stimulus_file(self) -> pd.DataFrame:
"""Helper method to cache stimulus file in memory since it takes about
a second to load (and is used in many methods).
"""
return pd.read_pickle(self.get_behavior_stimulus_file())
def get_licks(self) -> pd.DataFrame:
"""Get lick data from pkl file.
This function assumes that the first sensor in the list of
lick_sensors is the desired lick sensor. If this changes we need
to update to get the proper line.
Since licks can occur outside of a trial context, the lick times
are extracted from the vsyncs and the frame number in `lick_events`.
Since we don't have a timestamp for when in "experiment time" the
vsync stream starts (from self.get_stimulus_timestamps), we compute
it by fitting a linear regression (frame number x time) for the
`start_trial` and `end_trial` events in the `trial_log`, to true
up these time streams.
:returns: pd.DataFrame -- A dataframe containing lick timestamps
"""
# Get licks from pickle file instead of sync
data = self._behavior_stimulus_file()
offset = frame_time_offset(data)
stimulus_timestamps = self.get_stimulus_timestamps() + offset
lick_frames = (data["items"]["behavior"]["lick_sensors"][0]
["lick_events"])
lick_times = [stimulus_timestamps[frame] for frame in lick_frames]
return pd.DataFrame({"time": lick_times})
def get_rewards(self) -> pd.DataFrame:
"""Get reward data from pkl file, based on pkl file timestamps
(not sync file).
:returns: pd.DataFrame -- A dataframe containing timestamps of
delivered rewards.
"""
data = self._behavior_stimulus_file()
# No sync timestamps to rebase on, so pass dummy rebase function
return get_rewards(data, lambda x: x)
def get_running_data_df(self) -> pd.DataFrame:
"""Get running speed data.
:returns: pd.DataFrame -- dataframe containing various signals used
to compute running speed.
"""
stimulus_timestamps = self.get_stimulus_timestamps()
data = self._behavior_stimulus_file()
return get_running_df(data, stimulus_timestamps)
def get_running_speed(self) -> RunningSpeed:
"""Get running speed using timestamps from
self.get_stimulus_timestamps.
NOTE: Do not correct for monitor delay.
:returns: RunningSpeed -- a NamedTuple containing the subject's
timestamps and running speeds (in cm/s)
"""
running_data_df = self.get_running_data_df()
if running_data_df.index.name != "timestamps":
raise DataFrameIndexError(
f"Expected index to be named 'timestamps' but got "
"'{running_data_df.index.name}'.")
return RunningSpeed(timestamps=running_data_df.index.values,
values=running_data_df.speed.values)
def get_stimulus_frame_rate(self) -> float:
stimulus_timestamps = self.get_stimulus_timestamps()
return np.round(1 / np.mean(np.diff(stimulus_timestamps)), 0)
def get_stimulus_presentations(self) -> pd.DataFrame:
"""Get stimulus presentation data.
NOTE: Uses timestamps that do not account for monitor delay.
:returns: pd.DataFrame --
Table whose rows are stimulus presentations
(i.e. a given image, for a given duration, typically 250 ms)
and whose columns are presentation characteristics.
"""
stimulus_timestamps = self.get_stimulus_timestamps()
data = self._behavior_stimulus_file()
raw_stim_pres_df = get_stimulus_presentations(
data, stimulus_timestamps)
# Fill in nulls for image_name
# This makes two assumptions:
# 1. Nulls in `image_name` should be "gratings_<orientation>"
# 2. Gratings are only present (or need to be fixed) when all
# values for `image_name` are null.
if pd.isnull(raw_stim_pres_df["image_name"]).all():
if ~pd.isnull(raw_stim_pres_df["orientation"]).all():
raw_stim_pres_df["image_name"] = (
raw_stim_pres_df["orientation"]
.apply(lambda x: f"gratings_{x}"))
else:
raise ValueError("All values for 'orentation' and 'image_name'"
" are null.")
stimulus_metadata_df = get_stimulus_metadata(data)
idx_name = raw_stim_pres_df.index.name
stimulus_index_df = (
raw_stim_pres_df
.reset_index()
.merge(stimulus_metadata_df.reset_index(), on=["image_name"])
.set_index(idx_name))
stimulus_index_df = (
stimulus_index_df[["image_set", "image_index", "start_time"]]
.rename(columns={"start_time": "timestamps"})
.sort_index()
.set_index("timestamps", drop=True))
stim_pres_df = raw_stim_pres_df.merge(
stimulus_index_df, left_on="start_time", right_index=True,
how="left")
if len(raw_stim_pres_df) != len(stim_pres_df):
raise ValueError("Length of `stim_pres_df` should not change after"
f" merge; was {len(raw_stim_pres_df)}, now "
f" {len(stim_pres_df)}.")
return stim_pres_df[sorted(stim_pres_df)]
def get_stimulus_templates(self) -> Dict[str, np.ndarray]:
"""Get stimulus templates (movies, scenes) for behavior session.
Returns
-------
Dict[str, np.ndarray]
A dictionary containing the stimulus images presented during the
session. Keys are data set names, and values are 3D numpy arrays.
"""
data = self._behavior_stimulus_file()
return get_stimulus_templates(data)
def get_stimulus_timestamps(self) -> np.ndarray:
"""Get stimulus timestamps (vsyncs) from pkl file.
NOTE: Located with behavior_session_id. Does not use the sync_file
which requires ophys_session_id.
Returns
-------
np.ndarray
Timestamps associated with stimulus presentations on the monitor
that do no account for monitor delay.
"""
data = self._behavior_stimulus_file()
vsyncs = data["items"]["behavior"]["intervalsms"]
return np.hstack((0, vsyncs)).cumsum() / 1000.0 # cumulative time
def get_task_parameters(self) -> dict:
"""Get task parameters from pkl file.
Returns
-------
dict
A dictionary containing parameters used to define the task runtime
behavior.
"""
data = self._behavior_stimulus_file()
return get_task_parameters(data)
def get_trials(self) -> pd.DataFrame:
"""Get trials from pkl file
Returns
-------
pd.DataFrame
A dataframe containing behavioral trial start/stop times,
and trial data
"""
licks = self.get_licks()
data = self._behavior_stimulus_file()
rewards = self.get_rewards()
stimulus_presentations = self.get_stimulus_presentations()
# Pass a dummy rebase function since we don't have two time streams
trial_df = get_trials(data, licks, rewards, stimulus_presentations,
lambda x: x)
return trial_df
@memoize
def get_birth_date(self) -> datetime.date:
"""Returns the birth date of the animal.
:rtype: datetime.date
"""
query = f"""
SELECT d.date_of_birth
FROM behavior_sessions bs
JOIN donors d on d.id = bs.donor_id
WHERE bs.id = {self.behavior_session_id}
"""
return self.lims_db.fetchone(query, strict=True).date()
@memoize
def get_sex(self) -> str:
"""Returns sex of the animal (M/F)
:rtype: str
"""
query = f"""
SELECT g.name AS sex
FROM behavior_sessions bs
JOIN donors d ON bs.donor_id = d.id
JOIN genders g ON g.id = d.gender_id
WHERE bs.id = {self.behavior_session_id};
"""
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_age(self) -> str:
"""Returns age code of the subject.
:rtype: str
"""
query = f"""
SELECT a.name AS age
FROM behavior_sessions bs
JOIN donors d ON d.id = bs.donor_id
JOIN ages a ON a.id = d.age_id
WHERE bs.id = {self.behavior_session_id};
"""
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_rig_name(self) -> str:
"""Returns the name of the experimental rig.
:rtype: str
"""
query = f"""
SELECT e.name AS device_name
FROM behavior_sessions bs
JOIN equipment e ON e.id = bs.equipment_id
WHERE bs.id = {self.behavior_session_id};
"""
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_stimulus_name(self) -> str:
"""Returns the name of the stimulus set used for the session.
:rtype: str
"""
query = f"""
SELECT stages.name
FROM behavior_sessions bs
JOIN stages ON stages.id = bs.state_id
WHERE bs.id = '{self.foraging_id}'
"""
return self.mtrain_db.fetchone(query, strict=True)
@memoize
def get_reporter_line(self) -> List[str]:
"""Returns the genotype name(s) of the reporter line(s).
:rtype: list
"""
query = f"""
SELECT g.name AS reporter_line
FROM behavior_sessions bs
JOIN donors d ON bs.donor_id=d.id
JOIN donors_genotypes dg ON dg.donor_id=d.id
JOIN genotypes g ON g.id=dg.genotype_id
JOIN genotype_types gt
ON gt.id=g.genotype_type_id AND gt.name = 'reporter'
WHERE bs.id={self.behavior_session_id};
"""
result = self.lims_db.fetchall(query)
if result is None or len(result) < 1:
raise OneOrMoreResultExpectedError(
f"Expected one or more, but received: '{result}' "
f"from query:\n'{query}'")
return result
@memoize
def get_driver_line(self) -> List[str]:
"""Returns the genotype name(s) of the driver line(s).
:rtype: list
"""
query = f"""
SELECT g.name AS driver_line
FROM behavior_sessions bs
JOIN donors d ON bs.donor_id=d.id
JOIN donors_genotypes dg ON dg.donor_id=d.id
JOIN genotypes g ON g.id=dg.genotype_id
JOIN genotype_types gt
ON gt.id=g.genotype_type_id AND gt.name = 'driver'
WHERE bs.id={self.behavior_session_id};
"""
result = self.lims_db.fetchall(query)
if result is None or len(result) < 1:
raise OneOrMoreResultExpectedError(
f"Expected one or more, but received: '{result}' "
f"from query:\n'{query}'")
return result
@memoize
def get_external_specimen_name(self) -> int:
"""Returns the LabTracks ID
:rtype: int
"""
# TODO: Should this even be included?
# Found sometimes there were entries with NONE which is
# why they are filtered out; also many entries in the table
# match the donor_id, which is why used DISTINCT
query = f"""
SELECT DISTINCT(sp.external_specimen_name)
FROM behavior_sessions bs
JOIN donors d ON bs.donor_id=d.id
JOIN specimens sp ON sp.donor_id=d.id
WHERE bs.id={self.behavior_session_id}
AND sp.external_specimen_name IS NOT NULL;
"""
return int(self.lims_db.fetchone(query, strict=True))
@memoize
def get_full_genotype(self) -> str:
"""Return the name of the subject's genotype
:rtype: str
"""
query = f"""
SELECT d.full_genotype
FROM behavior_sessions bs
JOIN donors d ON d.id=bs.donor_id
WHERE bs.id= {self.behavior_session_id};
"""
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_experiment_date(self) -> datetime:
"""Return timestamp the behavior stimulus file began recording in UTC
:rtype: datetime
"""
data = self._behavior_stimulus_file()
# Assuming file has local time of computer (Seattle)
tz = pytz.timezone("America/Los_Angeles")
return tz.localize(data["start_time"]).astimezone(pytz.utc)
def get_metadata(self) -> Dict[str, Any]:
"""Return metadata about the session.
:rtype: dict
"""
if self.get_behavior_session_uuid() is None:
bs_uuid = None
else:
bs_uuid = uuid.UUID(self.get_behavior_session_uuid())
metadata = {
"rig_name": self.get_rig_name(),
"sex": self.get_sex(),
"age": self.get_age(),
"ophys_experiment_id": self.ophys_experiment_ids,
"experiment_container_id": self.ophys_container_id,
"stimulus_frame_rate": self.get_stimulus_frame_rate(),
"session_type": self.get_stimulus_name(),
"experiment_datetime": self.get_experiment_date(),
"reporter_line": self.get_reporter_line(),
"driver_line": self.get_driver_line(),
"LabTracks_ID": self.get_external_specimen_name(),
"full_genotype": self.get_full_genotype(),
"behavior_session_uuid": bs_uuid,
"foraging_id": self.foraging_id,
"behavior_session_id": self.behavior_session_id,
"behavior_training_id": self.behavior_training_id,
}
return metadata
class BehaviorLimsApi:
def __init__(self, behavior_experiment_id: int,
lims_credentials: Optional[DbCredentials] = None):
"""
Notes
-----
- behavior_experiment_id is the same as behavior_session_id which is in lims
- behavior_experiment_id is associated with foraging_id in lims
- foraging_id in lims is the same as behavior_session_uuid in mtrain which is the same
as session_uuid in the pickle returned by behavior_stimulus_file
"""
self.behavior_experiment_id = behavior_experiment_id
if lims_credentials:
self.lims_db = PostgresQueryMixin(
dbname=lims_credentials.dbname, user=lims_credentials.user,
host=lims_credentials.host, password=lims_credentials.password,
port=lims_credentials.port)
else:
# Use default credentials from provider
# Currying is equivalent to decorator syntactic sugar
self.lims_db = (
credential_injector(LIMS_DB_CREDENTIAL_MAP)
(PostgresQueryMixin)())
def get_behavior_experiment_id(self):
return self.behavior_experiment_id
@memoize
def get_behavior_stimulus_file(self):
query = '''
SELECT stim.storage_directory || stim.filename AS stim_file
FROM behavior_sessions bs
LEFT JOIN well_known_files stim ON stim.attachable_id=bs.id AND stim.attachable_type = 'BehaviorSession' AND stim.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'StimulusPickle')
WHERE bs.id= {};
'''.format(self.get_behavior_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
def get_extended_trials(self):
filename = self.get_behavior_stimulus_file()
data = pd.read_pickle(filename)
return get_extended_trials(data)
@staticmethod
def foraging_id_to_behavior_session_id(foraging_id):
'''maps foraging_id to behavior_session_id'''
api = (credential_injector(LIMS_DB_CREDENTIAL_MAP)
(PostgresQueryMixin)())
query = '''select id from behavior_sessions where foraging_id = '{}';'''.format(
foraging_id)
return api.fetchone(query, strict=True)
@staticmethod
def behavior_session_id_to_foraging_id(behavior_session_id):
'''maps behavior_session_id to foraging_id'''
api = (credential_injector(LIMS_DB_CREDENTIAL_MAP)
(PostgresQueryMixin)())
query = '''select foraging_id from behavior_sessions where id = '{}';'''.format(
behavior_session_id)
return api.fetchone(query, strict=True)
@classmethod
def from_foraging_id(cls, foraging_id: str,
lims_credentials: Optional[DbCredentials] = None):
return cls(
behavior_experiment_id=cls.foraging_id_to_behavior_session_id(
foraging_id, lims_credentials))
def behavior_session_id_to_foraging_id(behavior_session_id):
'''maps behavior_session_id to foraging_id'''
api = PostgresQueryMixin()
query = '''select foraging_id from behavior_sessions where id = '{}';'''.format(
behavior_session_id)
return api.fetchone(query, strict=True)