Metadata column profiling across datasets

metadata-column-profiling-across-datasets

Summary

Systematically identify and standardize database-ID columns (e.g., HMDB, PubChem, KEGG) across heterogeneous metabolomics datasets by combining name-based pattern matching on column headers with value-format validation on column entries. This enables harmonized curation and deposition of metabolite annotations to the Metabolomics Workbench.

When to use

You are curating metabolomics datasets with variable column naming conventions and need to detect, normalize, and populate standardized database-ID columns (HMDB_ID, PubChem_ID, KEGG_ID, etc.) across many mwTab files before deposition. Use this skill when column headers are heterogeneous (e.g., 'm/z', 'moverz', 'mz' for the same semantic field) and when values require format-based validation (e.g., distinguishing HMDB IDs from free-text descriptions by regex pattern).

When NOT to use

• Input is a pre-curated dataset where all database-ID columns already follow a single, uniform naming and formatting standard across all files.
• Database-ID values in the dataset are unstructured free-text descriptions with no consistent pattern or prefix (e.g., user-written notes) — regex-based ValueMatcher will not reliably extract them.
• You need to infer metabolite identity de novo from mass or spectral data rather than normalize existing database-ID annotations.

Inputs

• mwTab format file with metabolite metadata section (column headers and rows)
• lists of alternative column name variants for each database ID type (e.g., HMDB, PubChem, KEGG)
• modular regular expressions for database-ID value formats
• pandas DataFrame or tabular data extracted from mwTab file

Outputs

• column_finders dictionary with standardized column names as keys and ColumnFinder instances as values
• populated and normalized database-ID columns in the mwTab file
• validation report indicating which columns matched and which values were successfully formatted/normalized
• mwTab file with standardized database-ID column structure ready for deposition

How to apply

Construct a ColumnFinder registry by defining paired NameMatcher and ValueMatcher objects for each target database-ID column. For each ColumnFinder: (1) define a NameMatcher with lists of alternative column names and normalized variants using make_list_regex to create case-insensitive regex patterns; (2) define a ValueMatcher with modular regular expressions encoding the expected format of database-ID values (e.g., 'HMDB' prefix in mixed case, numeric PubChem CID patterns, KEGG compound identifiers); (3) validate the NameMatcher.dict_match method on test metabolite metadata headers to confirm pattern matching; (4) validate the ValueMatcher.series_match method on test database-ID values to confirm format detection and any normalization (e.g., stripping prefixes, standardizing case). Assemble all ColumnFinder instances into a column_finders dictionary keyed by standardized column name, then apply it to each dataset's column headers and values in sequence.

Related tools

• mwtab (Python library providing MWTabFile class for reading/writing mwTab format, housing ColumnFinder, NameMatcher, and ValueMatcher classes; provides pandas DataFrame access to metadata sections for column matching) — https://github.com/MoseleyBioinformaticsLab/mwtab
• pandas (Tabular data manipulation; series_match method operates on pandas Series objects to validate and normalize database-ID values across rows)
• Python 3.6+ (Runtime environment for mwtab and regex pattern compilation via make_list_regex)

Examples

from mwtab.column_finders import NameMatcher, ValueMatcher, ColumnFinder; nm_hmdb = NameMatcher(['HMDB', 'hmdb_id']); vm_hmdb = ValueMatcher(r'^[Hh][Mm][Dd][Bb]\d{7}$'); cf_hmdb = ColumnFinder(nm_hmdb, vm_hmdb, 'HMDB_ID'); column_finders = {'HMDB_ID': cf_hmdb}; matches = cf_hmdb.name_matcher.dict_match(mwfile.metadata.columns); normalized_values = cf_hmdb.value_matcher.series_match(mwfile.metadata[matches[0]])

Evaluation signals

• NameMatcher.dict_match successfully identifies all expected column variants (e.g., 'm/z', 'moverz', 'mz') in test headers with no false negatives
• ValueMatcher.series_match correctly validates format of known-good database-ID values (true positives) and rejects malformed entries (true negatives); check consistency of prefix stripping and case normalization
• column_finders dictionary contains exactly one ColumnFinder per standardized database-ID column name (HMDB_ID, PubChem_ID, KEGG_ID, etc.); no duplicate or missing keys
• After applying ColumnFinder across a batch of mwTab files, each file's metadata section has standardized database-ID columns populated and normalized; inspect a sample of values to confirm formatting is uniform (e.g., no leading/trailing whitespace, consistent case, prefixes removed if applicable)
• Validation report shows consistent match rates across similar files; sharp drop in matches for a file indicates potential schema drift or format anomaly requiring manual review

Limitations

• NameMatcher and ValueMatcher rely on pre-defined patterns; if a dataset uses a novel column name or database-ID format not in the make_list_regex dictionary or regex, the column will be missed.
• ValueMatcher.series_match cannot distinguish between valid IDs and incidental matches of the regex pattern in free-text fields; manual curation is needed if value semantics are ambiguous.
• The skill assumes metabolite metadata are organized as tabular rows in mwTab files; it does not handle nested or hierarchical metadata structures.
• No changelog was found in the mwtab repository, making it difficult to track how ColumnFinder behavior may have evolved or whether edge cases in pattern matching have been documented.

Evidence

• [other] how ColumnFinder logic combines NameMatcher and ValueMatcher: "NameMatcher uses its dict_match method to perform column-name matching operations, while ValueMatcher uses its series_match method to match column values, with both matcher types employed together in"
• [other] NameMatcher construction with regex patterns: "Define NameMatcher with lists of alternative column names and normalized variants (e.g., 'm/z', 'moverz', 'mz') using make_list_regex to construct case-insensitive patterns."
• [other] ValueMatcher construction with database-ID patterns: "Define ValueMatcher with modular regular expressions for database-ID value formats (e.g., HMDB IDs with optional 'HMDB' prefix in mixed case, PubChem CID numeric patterns, KEGG compound identifiers)."
• [other] ColumnFinder assembly and validation workflow: "Create ColumnFinder instances for each standard database-ID column (e.g., 'HMDB_ID', 'PubChem_ID', 'KEGG_ID') pairing a NameMatcher and ValueMatcher with the standard_name attribute. Assemble"
• [intro] mwtab as platform for this skill: "The mwtab package is a Python library that facilitates reading and writing files in mwTab format used by the Metabolomics Workbench_ for archival of Mass Spectrometry (MS) and Nuclear"
• [other] pandas role in value matching: "All ValueMatcher attributes are strings and all are used in its only method, series_match."