Record Grouping and Collation
Summary
This skill groups multiple table records into single dictionary entries by collating on a shared field value, enabling consolidation of repetitive experimental metadata into hierarchical structures suitable for repository submission.
When to use
Use this skill when you have multiple rows in a table that describe the same logical entity (e.g., multiple measurements from the same sample, or repeated attributes under a common identifier) and need to merge them into a single dictionary entry keyed by that shared field. This is particularly relevant when converting tabular experimental metadata (MS, NMR runs; sample properties) into JSONized format for deposition into repositories like Metabolomics Workbench.
When NOT to use
- Input records do not share a common field or the grouping semantics are unclear—collation requires all records in a group to relate to the same logical entity.
- Records within a group conflict on key values (i.e., the same header key would map to different values from different records in the group)—collation does not handle merging conflicts.
- The input is already a feature table or pre-aggregated matrix—collation is a reduction operation, not applicable to data already in the target format.
Inputs
- JSON table containing records with a shared field to collate on
- Matrix conversion directive specifying collate field, headers (key=value mappings), sort_by field list, sort_order, optional test filter, and optional exclusion_headers
Outputs
- List of dictionaries (JSON array of objects), one dictionary per collate group, with records merged and mapped according to headers specification
How to apply
Apply the matrix directive with the collate field parameter set to the name of the column whose values define grouping boundaries. First filter records using the test field (field=value syntax) if only a subset should be collated. Next, sort filtered records by fields listed in sort_by according to sort_order ('ascending' or 'descending') to establish a deterministic order within each group. Then iterate over groups—each group shares the same collate field value—and for each group, construct a dictionary by mapping table columns to key=value pairs using the headers specification (key=value pairs where keys/values reference record fields or literal strings). Finally, exclude any fields listed in exclusion_headers from the output. The result is a list of dictionaries, one per collate group, with all records in that group merged into single key=value mappings.
Related tools
Examples
messes convert mwtab your_data.json output_data --conversion-directive '{"collate": "sample_id", "headers": {"sample": "sample_name", "instrument": "instrument_type"}, "sort_by": ["run_order"], "sort_order": "ascending"}'
Evaluation signals
- Each output dictionary corresponds to exactly one unique value of the collate field; no collate value appears in more than one output dictionary.
- All records sharing the same collate field value are present in the corresponding output dictionary (group is complete and no records are dropped).
- Output dictionaries conform to the headers key=value mapping specification—no extra or missing keys, and values match expected record field references or literal strings.
- Records excluded by the test filter do not appear in any output dictionary.
- Records within each group are ordered according to sort_by and sort_order before collation (verifiable by inspecting intermediate group composition).
- Fields listed in exclusion_headers do not appear in any output dictionary key.
Limitations
- Collation assumes no conflicting values for the same key within a group; behavior is undefined if two records in the same group map the same header key to different values.
- Grouping is deterministic only if sort_by and sort_order are specified; without sorting, the order of records within a group (and thus the order of merging) may vary.
- Large tables with many records per collate group may produce very large dictionaries, potentially exceeding memory or serialization limits.
- The collate operation does not handle nested or hierarchical table structures; it works on flat record sets.
- If the collate field contains NULL or missing values, behavior depends on the implementation; records with missing collate keys may be grouped together or excluded.
Evidence
- [other] Core collation mechanism and purpose: "the collate field groups multiple records into single dictionaries based on a shared field value"
- [other] Workflow step: grouping and dictionary construction: "Group records by the collate field value, creating a dictionary per group. For each group, extract and map table columns to dictionary key=value pairs using the headers specification."
- [other] Preprocessing with filtering and sorting: "Apply the test filter to select only records matching the specified field=value condition. Sort the filtered records by the fields listed in sort_by according to sort_order."
- [other] Exclusion of specified headers: "The "exclusion_headers" field can then be used to exclude fields from being added."
- [intro] Matrix directive use case in tabular-to-JSON conversion: "The matrix directive assumes that you want to create a list of dictionaries (aka array of objects) from information in the input JSON, and that that information is contained within a single table."
- [readme] MESSES workflow context for metadata conversion: "MESSES (Metadata from Experimental SpreadSheets Extraction System) is a Python package that facilitates the conversion of tabular data into other formats"
1---2name: record-grouping-and-collation3description: Use when you have multiple rows in a table that describe the same logical entity (e.g., multiple measurements from the same sample, or repeated attributes under a common identifier) and need to merge them into a single dictionary entry keyed by that shared field.4license: CC-BY-4.05---67# Record Grouping and Collation89## Summary1011This skill groups multiple table records into single dictionary entries by collating on a shared field value, enabling consolidation of repetitive experimental metadata into hierarchical structures suitable for repository submission.1213## When to use1415Use this skill when you have multiple rows in a table that describe the same logical entity (e.g., multiple measurements from the same sample, or repeated attributes under a common identifier) and need to merge them into a single dictionary entry keyed by that shared field. This is particularly relevant when converting tabular experimental metadata (MS, NMR runs; sample properties) into JSONized format for deposition into repositories like Metabolomics Workbench.1617## When NOT to use1819- Input records do not share a common field or the grouping semantics are unclear—collation requires all records in a group to relate to the same logical entity.20- Records within a group conflict on key values (i.e., the same header key would map to different values from different records in the group)—collation does not handle merging conflicts.21- The input is already a feature table or pre-aggregated matrix—collation is a reduction operation, not applicable to data already in the target format.2223## Inputs2425- JSON table containing records with a shared field to collate on26- Matrix conversion directive specifying collate field, headers (key=value mappings), sort_by field list, sort_order, optional test filter, and optional exclusion_headers2728## Outputs2930- List of dictionaries (JSON array of objects), one dictionary per collate group, with records merged and mapped according to headers specification3132## How to apply3334Apply the matrix directive with the collate field parameter set to the name of the column whose values define grouping boundaries. First filter records using the test field (field=value syntax) if only a subset should be collated. Next, sort filtered records by fields listed in sort_by according to sort_order ('ascending' or 'descending') to establish a deterministic order within each group. Then iterate over groups—each group shares the same collate field value—and for each group, construct a dictionary by mapping table columns to key=value pairs using the headers specification (key=value pairs where keys/values reference record fields or literal strings). Finally, exclude any fields listed in exclusion_headers from the output. The result is a list of dictionaries, one per collate group, with all records in that group merged into single key=value mappings.3536## Related tools3738- **Python** (Language for implementing record iteration, grouping, filtering, and dictionary construction logic in MESSES convert command) — https://github.com/MoseleyBioinformaticsLab/messes39- **jsonschema** (Validates the output JSON dictionaries and the input conversion directive specification against the Experiment Description Specification schema) — https://pypi.org/project/jsonschema/4041## Examples4243```44messes convert mwtab your_data.json output_data --conversion-directive '{"collate": "sample_id", "headers": {"sample": "sample_name", "instrument": "instrument_type"}, "sort_by": ["run_order"], "sort_order": "ascending"}'45```4647## Evaluation signals4849- Each output dictionary corresponds to exactly one unique value of the collate field; no collate value appears in more than one output dictionary.50- All records sharing the same collate field value are present in the corresponding output dictionary (group is complete and no records are dropped).51- Output dictionaries conform to the headers key=value mapping specification—no extra or missing keys, and values match expected record field references or literal strings.52- Records excluded by the test filter do not appear in any output dictionary.53- Records within each group are ordered according to sort_by and sort_order before collation (verifiable by inspecting intermediate group composition).54- Fields listed in exclusion_headers do not appear in any output dictionary key.5556## Limitations5758- Collation assumes no conflicting values for the same key within a group; behavior is undefined if two records in the same group map the same header key to different values.59- Grouping is deterministic only if sort_by and sort_order are specified; without sorting, the order of records within a group (and thus the order of merging) may vary.60- Large tables with many records per collate group may produce very large dictionaries, potentially exceeding memory or serialization limits.61- The collate operation does not handle nested or hierarchical table structures; it works on flat record sets.62- If the collate field contains NULL or missing values, behavior depends on the implementation; records with missing collate keys may be grouped together or excluded.6364## Evidence6566- [other] Core collation mechanism and purpose: "the collate field groups multiple records into single dictionaries based on a shared field value"67- [other] Workflow step: grouping and dictionary construction: "Group records by the collate field value, creating a dictionary per group. For each group, extract and map table columns to dictionary key=value pairs using the headers specification."68- [other] Preprocessing with filtering and sorting: "Apply the test filter to select only records matching the specified field=value condition. Sort the filtered records by the fields listed in sort_by according to sort_order."69- [other] Exclusion of specified headers: "The "exclusion_headers" field can then be used to exclude fields from being added."70- [intro] Matrix directive use case in tabular-to-JSON conversion: "The matrix directive assumes that you want to create a list of dictionaries (aka array of objects) from information in the input JSON, and that that information is contained within a single table."71- [readme] MESSES workflow context for metadata conversion: "MESSES (Metadata from Experimental SpreadSheets Extraction System) is a Python package that facilitates the conversion of tabular data into other formats"
HolobiomicsLab 