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)