Dynamic Routing Metadata

import pandas as pd 
import numpy as np
from datetime import datetime, date

from aind_data_access_api.document_db import MetadataDbClient

API_GATEWAY_HOST = "api.allenneuraldynamics.org"
DATABASE = 'metadata_index'
COLLECTION = 'data_assets'

docdb_api_client = MetadataDbClient(
   host=API_GATEWAY_HOST,
    version="v2",
   database=DATABASE,
   collection=COLLECTION,
)
print(docdb_api_client._base_url)

https://api.allenneuraldynamics.org/v2/metadata_index/data_assets

aggregate = [
  {
    "$match": {
      "data_description.project_name": "Dynamic Routing", 
      "data_description.data_level": "derived", 
      "processing.data_processes": {
        "$elemMatch": {
          "process_type": "File format conversion",
          "start_date_time": {"$regex": "^2026-08-04"}
        }
      }
    }
  },
  {
    "$project": {
      "name": 1, 
      "subject_id": "$data_description.subject_id",
      "genotype": "$subject.subject_details.genotype", 
      "date_of_birth": "$subject.subject_details.date_of_birth", 
      "sex": "$subject.subject_details.sex",  
      "session_start_time": "$acquisition.acquisition_start_time",
      "session_end_time": "$acquisition.acquisition_end_time", 
      "stimulus_epochs": "$acquisition.stimulus_epochs",
      "project_name": "$data_description.project_name", 
      "modality": "$data_description.modalities.name", 
      "targeted_structure": "$acquisition.data_streams.configurations.probes.primary_targeted_structure.name" 
    }
  },
]
    
records = docdb_api_client.aggregate_docdb_records(pipeline=aggregate)

# Extract performance metrics in Python
for r in records:
    dr = next((e for e in r.get("stimulus_epochs", []) if e.get("stimulus_name") == "DynamicRouting1"), None)
    r["dr_performance"] = dr["performance_metrics"] if dr else None

Return these records into a dataframe and reorganize some things:

df = pd.DataFrame(records)
df['session_date'] = df.apply(lambda x: datetime.fromisoformat(x['session_start_time']).date(), axis=1)
df['session_start_time'] = df.apply(lambda x: datetime.fromisoformat(x['session_start_time']).time(), axis=1)
df['session_end_time'] = df.apply(lambda x: datetime.fromisoformat(x['session_end_time']).time(), axis=1)
df['date_of_birth'] = df.apply(lambda x: datetime.strptime(x['date_of_birth'], '%Y-%m-%d').date(), axis=1)
df['age'] = df.apply(lambda x: (x['session_date'] - x['date_of_birth']).days, axis=1)

df['trials_total'] = df['dr_performance'].apply(lambda x: x['trials_total'] if x else None)
df['trials_rewarded'] = df['dr_performance'].apply(lambda x: x['trials_rewarded'] if x else None)
df['reward_rate'] = df['trials_rewarded'] / df['trials_total']
df['reward_consumed_mL'] = df['dr_performance'].apply(lambda x: x['reward_consumed_during_epoch'] if x else None)
df['block_metrics'] = df['dr_performance'].apply(lambda x: x['output_parameters']['block_metrics'] if x else None)
df['mean_dprime_same_modal'] = df['block_metrics'].apply(
    lambda x: pd.Series([b['dprime_same_modal'] for b in x.values()]).mean() if x else None
)
df['mean_dprime_other_modal'] = df['block_metrics'].apply(
    lambda x: pd.Series([b['dprime_other_modal_go'] for b in x.values()]).mean() if x else None
)

order = ['project_name', '_id', 'name', 'subject_id', 'genotype', 'date_of_birth', 'age', 'sex',
         'modality', 'session_date', 'session_start_time', 'session_end_time', 'targeted_structure',
         'trials_total', 'trials_rewarded', 'reward_rate', 'reward_consumed_mL',
         'mean_dprime_same_modal', 'mean_dprime_other_modal']
df = df[order].sort_values(by='subject_id')
df

	project_name	_id	name	subject_id	genotype	date_of_birth	age	sex	modality	session_date	session_start_time	session_end_time	targeted_structure	trials_total	trials_rewarded	reward_rate	reward_consumed_mL	mean_dprime_same_modal	mean_dprime_other_modal
2	Dynamic Routing	66dc0f20-45dc-4a65-ac3a-0a04ac0e1df4	ecephys_662892_2023-08-24_14-28-28_nwb_2026-08...	662892	Sst-IRES-Cre/wt;Ai32(RCL-ChR2(H134R)_EYFP)/wt	2022-12-24	243	Female	[Extracellular electrophysiology, Behavior, Be...	2023-08-24	14:28:28	16:28:14.588745	[[[Piriform area], [root]]]	476	123	0.258403	3.8400000000000016	2.152875	2.209537
6	Dynamic Routing	0f42545e-0e5f-4dfc-9777-8070c1e69ffd	ecephys_664851_2023-11-16_12-54-53_nwb_2026-08...	664851	Pvalb-IRES-Cre/wt;Ai32(RCL-ChR2(H134R)_EYFP)/wt	2023-01-09	311	Female	[Extracellular electrophysiology, Behavior, Be...	2023-11-16	12:54:53	14:46:25.481437	[[[Secondary motor area], [Field CA1], [Subicu...	527	139	0.263757	4.35	3.133879	2.593231
3	Dynamic Routing	468a63e1-6e2f-494e-8448-f8bf07afcad5	ecephys_667252_2023-09-28_15-00-38_nwb_2026-08...	667252	wt/wt	2023-01-27	244	Female	[Extracellular electrophysiology, Behavior, Be...	2023-09-28	15:00:38	17:03:03.333877	[[[Secondary motor area], [Primary motor area]...	486	123	0.253086	3.7800000000000002	3.261005	2.260717
10	Dynamic Routing	b0377871-8621-45b2-8c31-a3957d669787	ecephys_708016_2024-04-29_12-59-12_nwb_2026-08...	708016	Vip-IRES-Cre/wt;Ai32(RCL-ChR2(H134R)_EYFP)/wt	2023-10-18	194	Male	[Extracellular electrophysiology, Behavior, Be...	2024-04-29	12:59:12	15:00:31.755703	[[[Secondary motor area], [Primary somatosenso...	533	129	0.242026	4.049999999999999	3.027970	2.665116
4	Dynamic Routing	1055573d-b0f0-4b31-a648-131ccddff1c8	ecephys_713655_2024-08-09_10-41-47_nwb_2026-08...	713655	Sst-IRES-Cre/wt;Ai32(RCL-ChR2(H134R)_EYFP)/wt	2023-11-23	260	Male	[Extracellular electrophysiology, Behavior, Be...	2024-08-09	10:41:47	12:49:52.580059	[[[Anterodorsal nucleus], [Supplemental somato...	515	129	0.250485	4.02	2.841083	3.002527
8	Dynamic Routing	6baeab62-02b8-4de7-8ca4-2ab5da7e0f20	ecephys_714748_2024-06-24_12-52-23_nwb_2026-08...	714748	Vip-IRES-Cre/wt;Ai32(RCL-ChR2(H134R)_EYFP)/wt	2023-12-01	206	Male	[Extracellular electrophysiology, Behavior, Be...	2024-06-24	12:52:23	14:59:27.907510	[[[Supplemental somatosensory area], [Secondar...	550	134	0.243636	4.229999999999999	3.329563	2.693473
9	Dynamic Routing	5a958ba6-ebe7-400a-8021-3230170e6638	ecephys_715710_2024-07-16_12-58-34_nwb_2026-08...	715710	Sst-IRES-Cre/wt;Ai32(RCL-ChR2(H134R)_EYFP)/wt	2023-12-07	222	Male	[Extracellular electrophysiology, Behavior, Be...	2024-07-16	12:58:34	15:05:31.280219	[[[Supplemental somatosensory area], [Midbrain...	548	131	0.239051	4.049999999999999	2.032598	1.928238
7	Dynamic Routing	6774fb39-4c19-4ba2-99f5-2609fb1b9d77	ecephys_741137_2024-10-10_13-15-50_nwb_2026-08...	741137	wt/wt	2024-03-19	205	Male	[Extracellular electrophysiology, Behavior, Be...	2024-10-10	13:15:50	15:19:11.275413	[[[Retrosplenial area, dorsal part], [Suppleme...	543	124	0.228361	5.2	3.113432	2.623589
0	Dynamic Routing	c5bc8e0e-81ee-4e52-a7b6-90089b0dfc5f	ecephys_742903_2024-10-23_14-12-23_nwb_2026-08...	742903	Vip-IRES-Cre/wt;Ai32(RCL-ChR2(H134R)_EYFP)/wt	2024-05-16	160	Female	[Extracellular electrophysiology, Behavior, Be...	2024-10-23	14:12:23	16:15:54.652646	[[[Primary somatosensory area], [Caudoputamen]...	489	124	0.253579	5.2	3.177007	2.342171
1	Dynamic Routing	7f603b00-8b9f-4a66-8b9d-1d9ed27b40d4	ecephys_743199_2024-12-05_12-42-34_nwb_2026-08...	743199	VGAT-ChR2-YFP/wt	2024-05-18	201	Female	[Extracellular electrophysiology, Behavior, Be...	2024-12-05	12:42:34	14:39:42.445214	[[[Periaqueductal gray], [Red nucleus], [Red n...	490	132	0.269388	5.36	2.008863	2.318989
5	Dynamic Routing	6c727fa9-0bcc-42ea-8ac2-6ea4fd82ebce	ecephys_759434_2025-02-04_12-27-22_nwb_2026-08...	759434	VGAT-ChR2-YFP/wt	2024-08-11	177	Male	[Extracellular electrophysiology, Behavior, Be...	2025-02-04	12:27:22	14:24:31.241622	[[[Caudoputamen], [Primary somatosensory area]...	545	142	0.260550	5.76	3.145629	3.099293

print(len(df))

11