Position: Data Analysis / Bioinformatics (Post-Bachelor)
Duration: 2 days Difficulty: Intermediate

---

Overview

You are tasked with reproducing a data integration and analysis pipeline for Microelectrode Array (MEA) recordings. This is a real workflow used in lab research to analyze network burst dynamics in neuronal cultures.

Your goal is to:

1. Discover and load JSON network analysis files from a nested directory structure
2. Extract metrics from the raw JSON data (network bursts, superbursts, burstlets)
3. Parse metadata from file paths using pattern matching
4. Integrate reference data from an Excel file containing experimental annotations
5. Merge and transform datasets to create a unified analysis table
6. Produce the final output as an Excel file with fields and Plot the graphs.

---

Project Structure

You will be provided with a folder containing:

project_folder/
│   ├── CDKL5/          # Experiment name
│   │   ├── CDKL5_T1.xlsx           # Reference metadata (Excel)
│   │   │   ├── Date(240520)/
│   │   │   │   ├── ChipID1
│   │   │   │   │   ├── Network/
│   │   │   │   │   │   ├── RunID/
│   │   │   │   │   │   │   │── well001/
│   │   │   │   │   │        └── network_results.json
│   │   │   │   │   │   │   │── well001/
│   │   │   │   │   │        └── network_results.json
│   │   │   │   │       └──...
│   │   │   │   ├── ChipID2/
│   │   │   │   └── ...
│   │   │   └── ...
│   ├── Other_Experiment/
│   └── ...

Unzip the 240520.zip

I have zipped to save space .. it will extract to almost 13GBs.

Key observations:

• The directory structure contains information about the projectname/date/chipname/network/runid/wellid/
• Project folder contains .xlsx reference file (contains assay type, plating date, neuron source, etc.)
• Each well has a network_results.json file containing burst metrics

---

Data Structures

1. Directory Path Structure

File paths follow a standardized pattern:

.../AnalyzedData/{Project}/{Date}/{Chip_ID}/Network/{Run_ID}/{Well_ID}/network_results.json

Example:

.../AnalyzedData/JGA_CSB3_010825_VD/250108/M07999/Network/000052/well001/network_results.json

Extract from path:

• Project: JGA_CSB3_010825_VD
• Date: 250108 (YYMMDD format)
• Chip_ID: M07999
• Run_ID: 000052 (6-digit zero-padded integer)
• Well_ID: well001 (well + 3-digit zero-padded integer)

2. JSON File Structure (network_results.json)

Each JSON file contains burst analysis results with this structure: Inspect the network json by your own.

{
  "n_units": 64,
  "network_bursts": {
    "metrics": {
      "count": 245,
      "rate": 2.1,
      "duration": {"mean": 0.432, "std": 0.156},
      "inter_event_interval": {"mean": 0.876, "std": 0.234},
      "spikes_per_burst": {"mean": 156.2, "std": 45.3},
      "participation": {"mean": 0.82, "std": 0.12},
      "burst_peak": {"mean": 45.3, "std": 12.1},
      "peak_synchrony": {"mean": 0.67, "std": 0.15},
      "synchrony_energy": {...}
    },
    "events": [
      {
        "duration_s": 0.421,
        "total_spikes": 152,
        "peak_synchrony": 0.68,
        "synchrony_energy": 234.5,
        "fragment_count": 3
      },
      ...
    ]
  },
  "superbursts": {...},
  "burstlets": {...}
}

Burst types:

• network_bursts (nb): Large coordinated bursts across the network
• superbursts (sb): High-intensity bursts (subset of network bursts)
• burstlets (bl): Small bursts with less network participation

3. Reference Excel File

The .xlsx file contains experimental metadata:

Date	ID	Run #	Wells_Recorded	Neuron Source	Assay	DIV
1/8/2025	M07999	1	well001, well002, well003	iPSC-derived, iPSC-derived, iPSC-derived	Network today	...
1/8/2025	M07999	2	well001, well004	Control, Control	Neuronal Units 9	...

Key notes:

• ID is the CHIPID
• Wells_Recorded is a comma-separated string (needs splitting)
• Neuron Source is also comma-separated (needs splitting) and must align with wells
• Assay categorizes the experiment type
• DIV may be empty (you'll calculate it)

HINT: you can extract the networkjsons from the filepath and then also the chip info from the path to combine with the above reff file

Workflow Steps

Step 1: Load and Parse File List

• recursively find all network_results.json files in the AnalyzedData folder
• Create a DataFrame with file paths
• Extract metadata from paths into columns: Project, Date, Chip_ID, RunID, Well

Deliverable: DataFrame with ~500-2000 rows (one per JSON file)

---

Step 2: Extract Metrics from JSON Files

• For each JSON file, load and parse the burst data
• Extract Just from the dictionary "network_bursts" -> "metrics" count, rate, duration(mean),burst_peak(mean)

Deliverable: DataFrame with added JSON metrics as columns

---

Step 3: Parse and Transform Reference Metadata

• Load the .xlsx refference file
• Split comma-separated strings in Wells_Recorded and Neuron Source { these two have one to one alignment. }
• Convert well numbering: Excel is 1-indexed, JSON is 0-indexed
  • Excel "1" → "well000"
  • Excel "2" → "well001"
  • etc.
• Standardize date format to YYMMDD (e.g., "1/8/2025" → "250108")
• Zero-pad Run # to 6 digits (e.g., "1" → "000001")

Deliverable: Clean metadata DataFrame with columns: Date, ID, Run #, Well, NeuronType, Assay, DIV

---

Step 4: Merge Datasets

• Before merging: Verify no duplicates exist:

  • JSON DataFrame should be unique on [ID, Date, Run #, Well]
  • Reference DataFrame should also be unique on [ID, Date, Run #, Well]
  • Use assertions to catch issues

• Merge type: Left join (keep all JSON records, add metadata where available)

• Merge keys: [ID, Date, Run #, Well]

Deliverable: Merged DataFrame with all metrics + metadata columns

---

Step 5: Export Results

• Save the final merged DataFrame as an Excel file: mea_combined_metrics.xlsx
• Save one sheet per assay type (if analyzing separately)
• Ensure all data types are appropriate (dates as dates, numbers as numbers)

Deliverable: Excel file with complete metrics and metadata

---

Step 6: Plot bar Graphs comparing different NeuronSource (HET vs HOM) of the metrics in STEP 2

• Use the meaplotter.py functions(feel free to modify it)

Deliverable: SVG file bar plots.

Good luck!