Flattening ASM to 2D CSV

Converting hierarchical ASM JSON to flat 2D tables for LIMS import, spreadsheet analysis, or data engineering pipelines.

Why Flatten?

ASM is semantically rich but hierarchical. Many systems need flat tables:

• LIMS import (Benchling, STARLIMS, LabWare)
• Excel/CSV analysis
• Database loading
• Quick visual inspection

Flattening Strategy

Core Principle

Each measurement becomes one row. Metadata is repeated per row.

What's Excluded

The flattening intentionally omits top-level ASM metadata such as:

• $asm.manifest (model version, schema URIs)
• Root-level fields outside the technique aggregate document

This keeps the output focused on experimental data. If you need schema version tracking for compliance or audit purposes, consider storing the original ASM JSON alongside the flattened CSV, or modify the flattening script to include these fields.

Hierarchy to Columns

ASM Hierarchy                    → Flat Column
─────────────────────────────────────────────────
device-system-document.
  device-identifier              → instrument_serial_number
  model-number                   → instrument_model
  
measurement-aggregate-document.
  analyst                        → analyst
  measurement-time               → measurement_datetime
  
measurement-document[].
  sample-identifier              → sample_id
  viable-cell-density.value      → viable_cell_density
  viable-cell-density.unit       → viable_cell_density_unit
  viability.value                → viability_percent

Column Naming Convention

Use snake_case with descriptive suffixes:

Example: Cell Counting

ASM Input (simplified)

{
  "cell-counting-aggregate-document": {
    "device-system-document": {
      "device-identifier": "VCB001",
      "model-number": "Vi-CELL BLU"
    },
    "cell-counting-document": [{
      "measurement-aggregate-document": {
        "analyst": "jsmith",
        "measurement-time": "2024-01-15T10:30:00Z",
        "measurement-document": [
          {
            "sample-identifier": "Sample_A",
            "viable-cell-density": {"value": 2500000, "unit": "(cell/mL)"},
            "viability": {"value": 95.2, "unit": "%"}
          },
          {
            "sample-identifier": "Sample_B",
            "viable-cell-density": {"value": 1800000, "unit": "(cell/mL)"},
            "viability": {"value": 88.7, "unit": "%"}
          }
        ]
      }
    }]
  }
}

Flattened Output

sample_id,viable_cell_density,viable_cell_density_unit,viability_percent,analyst,measurement_datetime,instrument_serial_number,instrument_model
Sample_A,2500000,(cell/mL),95.2,jsmith,2024-01-15T10:30:00Z,VCB001,Vi-CELL BLU
Sample_B,1800000,(cell/mL),88.7,jsmith,2024-01-15T10:30:00Z,VCB001,Vi-CELL BLU

Example: Plate Reader

ASM Input (simplified)

{
  "plate-reader-aggregate-document": {
    "plate-reader-document": [{
      "measurement-aggregate-document": {
        "plate-identifier": "ELISA_001",
        "measurement-document": [
          {"well-location": "A1", "absorbance": {"value": 0.125, "unit": "mAU"}},
          {"well-location": "A2", "absorbance": {"value": 0.892, "unit": "mAU"}},
          {"well-location": "A3", "absorbance": {"value": 1.456, "unit": "mAU"}}
        ]
      }
    }]
  }
}

Flattened Output

plate_id,well_position,absorbance,absorbance_unit
ELISA_001,A1,0.125,mAU
ELISA_001,A2,0.892,mAU
ELISA_001,A3,1.456,mAU

Handling Data Cubes

Data cubes (time series, spectra) need special handling:

Option 1: Expand to rows

Each point becomes a row:

sample_id,time_seconds,absorbance
Sample_A,0,0.100
Sample_A,60,0.125
Sample_A,120,0.150

Option 2: Wide format

Measurements as columns:

sample_id,abs_0s,abs_60s,abs_120s
Sample_A,0.100,0.125,0.150

Option 3: JSON array in cell

Keep as array (some systems support this):

sample_id,absorbance_timeseries
Sample_A,"[0.100,0.125,0.150]"

Standard Column Sets by Technique

Cell Counting

sample_id, viable_cell_density, viable_cell_density_unit, total_cell_count,
viability_percent, average_cell_diameter, average_cell_diameter_unit,
analyst, measurement_datetime, instrument_serial_number

Spectrophotometry

sample_id, wavelength_nm, absorbance, pathlength_cm, concentration,
concentration_unit, a260_a280_ratio, a260_a230_ratio,
analyst, measurement_datetime, instrument_serial_number

Plate Reader / ELISA

plate_id, well_position, sample_type, sample_id, absorbance, absorbance_unit,
concentration, concentration_unit, dilution_factor, cv_percent,
analyst, measurement_datetime, instrument_serial_number

qPCR

sample_id, target_name, well_position, ct_value, ct_mean, ct_sd,
quantity, quantity_unit, amplification_efficiency,
analyst, measurement_datetime, instrument_serial_number

Python Implementation

import json
import pandas as pd

def flatten_asm(asm_dict, technique="cell-counting"):
    """
    Flatten ASM JSON to pandas DataFrame.
    
    Args:
        asm_dict: Parsed ASM JSON
        technique: ASM technique type
        
    Returns:
        pandas DataFrame with one row per measurement
    """
    rows = []
    
    # Get aggregate document
    agg_key = f"{technique}-aggregate-document"
    agg_doc = asm_dict.get(agg_key, {})
    
    # Extract device info
    device = agg_doc.get("device-system-document", {})
    device_info = {
        "instrument_serial_number": device.get("device-identifier"),
        "instrument_model": device.get("model-number")
    }
    
    # Get technique documents
    doc_key = f"{technique}-document"
    for doc in agg_doc.get(doc_key, []):
        meas_agg = doc.get("measurement-aggregate-document", {})
        
        # Extract common metadata
        common = {
            "analyst": meas_agg.get("analyst"),
            "measurement_datetime": meas_agg.get("measurement-time"),
            **device_info
        }
        
        # Extract each measurement
        for meas in meas_agg.get("measurement-document", []):
            row = {**common}
            
            # Flatten measurement fields
            for key, value in meas.items():
                if isinstance(value, dict) and "value" in value:
                    # Value datum pattern
                    col = key.replace("-", "_")
                    row[col] = value["value"]
                    if "unit" in value:
                        row[f"{col}_unit"] = value["unit"]
                else:
                    row[key.replace("-", "_")] = value
            
            rows.append(row)
    
    return pd.DataFrame(rows)

# Usage
with open("asm_output.json") as f:
    asm = json.load(f)

df = flatten_asm(asm, "cell-counting")
df.to_csv("flattened_output.csv", index=False)

LIMS Import Considerations

When importing flattened data into a LIMS:

• Match column names to your LIMS schema field names
• Use ISO 8601 date format for timestamps
• Ensure sample IDs match existing LIMS sample identifiers
• Check if your LIMS expects units in separate columns or embedded in values