tabular-data-tag-parsing
Summary
Parse tagged tabular files (Excel or CSV) into nested JSON by interpreting export tags (prefixed with # or #.) in column headers to map table names, record identifiers, and field names. This extraction step converts raw experimental metadata spreadsheets into a standardized, interoperable JSON representation suitable for downstream validation and format conversion.
When to use
You have raw tabular experimental data (CSV or Excel) with column headers annotated using MESSES tag syntax (#.id for record identifiers, #. for field values), and you need to transform it into a nested JSON structure that conforms to the Experiment Description Specification for validation and repository submission.
When NOT to use
- Input tabular data has no tagging layer or uses a different tagging convention incompatible with MESSES syntax
- Data is already in valid JSON format and only needs validation or conversion, not extraction
- Tabular data structure does not follow a record-based model (e.g., purely columnar, matrix-like experimental data without identifiable records or tables)
Inputs
- tagged tabular file (CSV or Excel format)
- column headers with MESSES export tags (#.id, #.)
- tabular rows with experimental metadata and measurements
Outputs
- nested JSON structure with table names as top-level keys
- records indexed by identifier within each table
- field key-value pairs within each record
- intermediate JSON representation conforming to Experiment Description Specification
How to apply
Read the tagged tabular file row-by-row and parse each column header to extract tag syntax: identify # or #. prefixes, then extract the table name and field name (or record identifier marker) from the tag string. Build a hierarchical JSON dictionary by iterating through rows: for each row, create or append records keyed by table name and record identifier (from columns marked #.id), populating field key-value pairs from columns marked #.. Validate that all tagged columns conform to expected syntax (either #.id or #.) and that no duplicate record identifiers exist within a single table before outputting the nested JSON. The MESSES extract command automates this process and produces intermediate JSON ready for downstream validation and conversion steps.
Related tools
- MESSES (Python package that automates tagged tabular extraction via the 'extract' command, handling tag parsing, JSON serialization, and validation against Experiment Description Specification) — https://github.com/MoseleyBioinformaticsLab/messes
- Python (Programming language for implementing tag parsing logic, JSON object construction, and row-by-row file iteration)
- jsonschema (Post-extraction validation tool for verifying the produced nested JSON against the Experiment Description Specification and Protocol Dependent Schema) — https://pypi.org/project/jsonschema/
Examples
messes extract your_data.csv --output your_data.json
Evaluation signals
- All tagged columns in the input are successfully parsed and no tag syntax errors or unrecognized prefixes remain in the output
- The output nested JSON structure has correct hierarchy: table names at top level, records keyed by identifier within each table, field key-value pairs within records
- No duplicate record identifiers exist within a single table (validate uniqueness of #.id values per table)
- The output JSON validates successfully against the Experiment Description Specification using jsonschema
- All rows from the input tabular file are represented in the output JSON with no data loss
Limitations
- Tag parsing assumes strict adherence to MESSES tag syntax (#.id and #.); malformed, missing, or non-standard tags will cause parsing errors or data loss
- The skill requires prior manual or automated tagging of the raw tabular data; untagged spreadsheets cannot be processed without first applying the tagging layer
- Duplicate record identifiers within a table will be flagged as an error; the user must resolve identifier conflicts in the source data before extraction succeeds
- The approach is specific to record-oriented (relational) data models; purely columnar or matrix-based experimental data (e.g., omics feature tables) may not map cleanly to the nested table-record-field structure
Evidence
- [other] Tagged tabular files are converted to nested JSON by mapping each table column with #.id tags to record names and each column with #. tags to field names and values, producing a JSON structure where table names contain records with field key-value pairs.: "Tagged tabular files are converted to nested JSON by mapping each table column with #.id tags to record names and each column with #. tags to field names and values, producing"
- [other] Extract tabular data into JSON using tags. This is done by adding a layer of tags on top of the data.: "The extract command of MESSES supports turning tabular data into JSON. This is done by adding a layer of tags on top of the data."
- [readme] Simply add a layer of tags to any tabular data and MESSES can transform it into an intermediate JSON representation: "Simply add a layer of tags to any tabular data and MESSES can transform it into an intermediate JSON representation and then convert it to any of the supported formats."
- [readme] MESSES breaks up the process into 3 steps: extract, validate, and convert. The extraction step adds a layer of tags to your raw tabular data, which may be automatable, and then extracts it into a JSONized form that it is more interoperable and more standardized.: "MESSES breaks up the process into 3 steps: extract, validate, and convert. The extraction step adds a layer of tags to your raw tabular data, which may be automatable, and then extracts it into a"
- [readme] The expected workflow is to use the "extract" command to transform your tabular data into JSON: "The expected workflow is to use the "extract" command to transform your tabular data into JSON, then use the "validate" command to validate the JSON based on your specific project schema"
1---2name: tabular-data-tag-parsing-23description: Use when you have raw tabular experimental data (CSV or Excel) with column headers annotated using MESSES tag syntax (#<table_name>.id for record identifiers, #.4license: CC-BY-4.05---67# tabular-data-tag-parsing89## Summary1011Parse tagged tabular files (Excel or CSV) into nested JSON by interpreting export tags (prefixed with # or #.) in column headers to map table names, record identifiers, and field names. This extraction step converts raw experimental metadata spreadsheets into a standardized, interoperable JSON representation suitable for downstream validation and format conversion.1213## When to use1415You have raw tabular experimental data (CSV or Excel) with column headers annotated using MESSES tag syntax (#<table_name>.id for record identifiers, #.<field_name> for field values), and you need to transform it into a nested JSON structure that conforms to the Experiment Description Specification for validation and repository submission.1617## When NOT to use1819- Input tabular data has no tagging layer or uses a different tagging convention incompatible with MESSES syntax20- Data is already in valid JSON format and only needs validation or conversion, not extraction21- Tabular data structure does not follow a record-based model (e.g., purely columnar, matrix-like experimental data without identifiable records or tables)2223## Inputs2425- tagged tabular file (CSV or Excel format)26- column headers with MESSES export tags (#<table_name>.id, #.<field_name>)27- tabular rows with experimental metadata and measurements2829## Outputs3031- nested JSON structure with table names as top-level keys32- records indexed by identifier within each table33- field key-value pairs within each record34- intermediate JSON representation conforming to Experiment Description Specification3536## How to apply3738Read the tagged tabular file row-by-row and parse each column header to extract tag syntax: identify # or #. prefixes, then extract the table name and field name (or record identifier marker) from the tag string. Build a hierarchical JSON dictionary by iterating through rows: for each row, create or append records keyed by table name and record identifier (from columns marked #<table_name>.id), populating field key-value pairs from columns marked #.<field_name>. Validate that all tagged columns conform to expected syntax (either #<table_name>.id or #.<field_name>) and that no duplicate record identifiers exist within a single table before outputting the nested JSON. The MESSES extract command automates this process and produces intermediate JSON ready for downstream validation and conversion steps.3940## Related tools4142- **MESSES** (Python package that automates tagged tabular extraction via the 'extract' command, handling tag parsing, JSON serialization, and validation against Experiment Description Specification) — https://github.com/MoseleyBioinformaticsLab/messes43- **Python** (Programming language for implementing tag parsing logic, JSON object construction, and row-by-row file iteration)44- **jsonschema** (Post-extraction validation tool for verifying the produced nested JSON against the Experiment Description Specification and Protocol Dependent Schema) — https://pypi.org/project/jsonschema/4546## Examples4748```49messes extract your_data.csv --output your_data.json50```5152## Evaluation signals5354- All tagged columns in the input are successfully parsed and no tag syntax errors or unrecognized prefixes remain in the output55- The output nested JSON structure has correct hierarchy: table names at top level, records keyed by identifier within each table, field key-value pairs within records56- No duplicate record identifiers exist within a single table (validate uniqueness of #<table_name>.id values per table)57- The output JSON validates successfully against the Experiment Description Specification using jsonschema58- All rows from the input tabular file are represented in the output JSON with no data loss5960## Limitations6162- Tag parsing assumes strict adherence to MESSES tag syntax (#<table_name>.id and #.<field_name>); malformed, missing, or non-standard tags will cause parsing errors or data loss63- The skill requires prior manual or automated tagging of the raw tabular data; untagged spreadsheets cannot be processed without first applying the tagging layer64- Duplicate record identifiers within a table will be flagged as an error; the user must resolve identifier conflicts in the source data before extraction succeeds65- The approach is specific to record-oriented (relational) data models; purely columnar or matrix-based experimental data (e.g., omics feature tables) may not map cleanly to the nested table-record-field structure6667## Evidence6869- [other] Tagged tabular files are converted to nested JSON by mapping each table column with #<table_name>.id tags to record names and each column with #.<field_name> tags to field names and values, producing a JSON structure where table names contain records with field key-value pairs.: "Tagged tabular files are converted to nested JSON by mapping each table column with #<table_name>.id tags to record names and each column with #.<field_name> tags to field names and values, producing"70- [other] Extract tabular data into JSON using tags. This is done by adding a layer of tags on top of the data.: "The extract command of MESSES supports turning tabular data into JSON. This is done by adding a layer of tags on top of the data."71- [readme] Simply add a layer of tags to any tabular data and MESSES can transform it into an intermediate JSON representation: "Simply add a layer of tags to any tabular data and MESSES can transform it into an intermediate JSON representation and then convert it to any of the supported formats."72- [readme] MESSES breaks up the process into 3 steps: extract, validate, and convert. The extraction step adds a layer of tags to your raw tabular data, which may be automatable, and then extracts it into a JSONized form that it is more interoperable and more standardized.: "MESSES breaks up the process into 3 steps: extract, validate, and convert. The extraction step adds a layer of tags to your raw tabular data, which may be automatable, and then extracts it into a"73- [readme] The expected workflow is to use the "extract" command to transform your tabular data into JSON: "The expected workflow is to use the "extract" command to transform your tabular data into JSON, then use the "validate" command to validate the JSON based on your specific project schema"
HolobiomicsLab