table-consolidation-and-denormalization
License: restricted — no clear open-source license detected for the underlying tool; verify licensing before commercial use or redistribution.
Summary
Consolidate normalized organism, structure, and reference tables into a single denormalized curated table by performing sequential left-join operations and appending external annotations (e.g., NP-classifier), while preserving all original rows and generating derived dictionaries for each entity type. This skill is essential when integrating multi-source biological and chemical data into a unified structure-organism-reference knowledgebase.
When to use
When you have cleaned, normalized organism, structure, and reference tables from separate cleaning pipelines (e.g., after 2_curating stage) and must integrate them into a single queryable table while maintaining referential integrity and generating lookup dictionaries. Specifically triggered when you possess cleaned organism data, translated/cleaned structure data, cleaned reference data, and external structural annotations (e.g., NP-classifier), and your goal is to produce a denormalized curated table (e.g., interim/tables/3_curated/table.tsv.gz) that preserves all original rows from the source table and enables downstream validation and analysis.
When NOT to use
- Input tables are already denormalized or merged; consolidation would introduce redundancy or lose reference integrity.
- Original table identifiers are not available or have been lost; you cannot preserve row lineage and validation status.
- Cleaning and translation pipelines have not been completed; tables contain inconsistent or unvalidated entries that would propagate downstream.
Inputs
- interim/tables/0_original/table.tsv.gz (original integrated table with record identifiers and lineage)
- interim/tables/2_cleaned/organism/cleaned.tsv.gz (cleaned organism data)
- interim/tables/1_translated/structure/final.tsv.gz (final structure data)
- interim/tables/2_cleaned/structure/named.tsv.gz (named structure data)
- interim/dictionaries/structure/npclassifier/smiles_np_classified.tsv.gz (NP-classifier structure annotations)
- interim/tables/2_cleaned/reference/cleaned.tsv.gz (cleaned reference data)
Outputs
- interim/tables/3_curated/table.tsv.gz (denormalized curated structure-organism-reference table with all rows preserved and validated entries flagged)
- interim/dictionary/organism/dictionary.tsv.gz (derived organism lookup dictionary)
- interim/dictionary/organism/metadata.tsv.gz (organism metadata)
- interim/dictionary/structure/dictionary.tsv.gz (derived structure lookup dictionary)
- interim/dictionary/structure/metadata.tsv.gz (structure metadata)
- interim/dictionary/reference/dictionaryOrganism.tsv.gz (reference-to-organism mapping dictionary)
- interim/dictionary/reference/metadata.tsv.gz (reference metadata)
How to apply
Load the original integrated table (interim/tables/0_original/table.tsv.gz) to anchor all row identifiers and preserve lineage. Sequentially load cleaned organism data (interim/tables/2_cleaned/organism/cleaned.tsv.gz), final structure data (interim/tables/1_translated/structure/final.tsv.gz), named structure data (interim/tables/2_cleaned/structure/named.tsv.gz), NP-classifier annotations (interim/dictionaries/structure/npclassifier/smiles_np_classified.tsv.gz), and cleaned reference data (interim/tables/2_cleaned/reference/cleaned.tsv.gz) using data.table or equivalent. Perform left-join operations in sequence: join organisms by organism_id, structures by structure_id, references by reference_id, and append NP-classifier annotations by SMILES or structure identifier. Extract unique organism, structure, and reference entries to generate derived dictionaries (organism/dictionary.tsv.gz, structure/dictionary.tsv.gz, reference/dictionaryOrganism.tsv.gz) and corresponding metadata tables. Consolidate all columns into a single denormalized table, compress to interim/tables/3_curated/table.tsv.gz, and flag validated entries while ensuring all rows from the original table are preserved.
Related tools
- R (Execute 1_integrating.R script for join operations and denormalization logic) — https://github.com/lotusnprod/lotus-processor
- data.table (Perform efficient left-join operations on cleaned and translated tables in R)
- gzip/compression utilities (Compress consolidated table to interim/tables/3_curated/table.tsv.gz)
Examples
Rscript 1_integrating.R --original interim/tables/0_original/table.tsv.gz --organism interim/tables/2_cleaned/organism/cleaned.tsv.gz --structure interim/tables/1_translated/structure/final.tsv.gz --reference interim/tables/2_cleaned/reference/cleaned.tsv.gz --npclassifier interim/dictionaries/structure/npclassifier/smiles_np_classified.tsv.gz --output interim/tables/3_curated/table.tsv.gz
Evaluation signals
- All rows from interim/tables/0_original/table.tsv.gz are preserved in the output table; row count matches or exceeds original count (accounting for splits/denormalization).
- All organism_id, structure_id, and reference_id foreign keys are populated post-join with no unexpected nulls (unless intentional for unmatched rows in outer joins).
- Derived dictionaries (organism, structure, reference) contain unique entries corresponding to joined data; no orphaned or duplicate entries in dictionaries.
- Validated entries are flagged with consistent, boolean validation status column; distribution of validated vs. unvalidated rows is documented.
- Output file (interim/tables/3_curated/table.tsv.gz) decompresses without corruption; column schema matches documented specification (all organism, structure, reference, and NP-classifier columns present).
Limitations
- Row count may increase if 1:many or m:m joins occur (e.g., one organism mapped to multiple structures); document join cardinality and verify denormalization is intentional.
- NP-classifier annotations are appended by SMILES or structure identifier; missing or incorrectly formatted SMILES values will result in annotation gaps; validate SMILES format before join.
- Lineage and validation status depend on correct preservation of original table identifiers; if primary keys are corrupted or renamed during cleaning, traceability is lost.
- No changelog is documented; version history and updates to cleaning, translation, or annotation pipelines are not tracked, complicating reproducibility and debugging.
Evidence
- [methods] Consolidation via sequential joins and dictionary extraction: "Perform left join operations in sequence: join organisms by organism_id, join structure data by structure_id, join reference data by reference_id, and append NP-classifier annotations by SMILES or"
- [methods] Input table inventory and sources: "Load cleaned organism data from interim/tables/2_cleaned/organism/cleaned.tsv.gz, final structure data from interim/tables/1_translated/structure/final.tsv.gz, named structure data from"
- [methods] Lineage preservation and original table role: "Load the original integrated table from interim/tables/0_original/table.tsv.gz to preserve original record identifiers and lineage."
- [methods] Dictionary derivation procedure: "Generate derived organism dictionary (interim/dictionary/organism/dictionary.tsv.gz) and metadata (interim/dictionary/organism/metadata.tsv.gz) by extracting unique organism entries and their"
- [methods] Output specification and row preservation: "Consolidate all columns into a single denormalized table and compress to interim/tables/3_curated/table.tsv.gz, ensuring all rows from the original table are preserved and validated entries are"
1---2name: table-consolidation-and-denormalization3description: Use when when you have cleaned, normalized organism, structure, and reference tables from separate cleaning pipelines (e.g., after 2_curating stage) and must integrate them into a single queryable table while maintaining referential integrity and generating lookup dictionaries.4license: CC-BY-4.05---67# table-consolidation-and-denormalization89> **License: restricted** — no clear open-source license detected for the underlying tool; verify licensing before commercial use or redistribution. <!-- asb-license-banner -->10## Summary1112Consolidate normalized organism, structure, and reference tables into a single denormalized curated table by performing sequential left-join operations and appending external annotations (e.g., NP-classifier), while preserving all original rows and generating derived dictionaries for each entity type. This skill is essential when integrating multi-source biological and chemical data into a unified structure-organism-reference knowledgebase.1314## When to use1516When you have cleaned, normalized organism, structure, and reference tables from separate cleaning pipelines (e.g., after 2_curating stage) and must integrate them into a single queryable table while maintaining referential integrity and generating lookup dictionaries. Specifically triggered when you possess cleaned organism data, translated/cleaned structure data, cleaned reference data, and external structural annotations (e.g., NP-classifier), and your goal is to produce a denormalized curated table (e.g., interim/tables/3_curated/table.tsv.gz) that preserves all original rows from the source table and enables downstream validation and analysis.1718## When NOT to use1920- Input tables are already denormalized or merged; consolidation would introduce redundancy or lose reference integrity.21- Original table identifiers are not available or have been lost; you cannot preserve row lineage and validation status.22- Cleaning and translation pipelines have not been completed; tables contain inconsistent or unvalidated entries that would propagate downstream.2324## Inputs2526- interim/tables/0_original/table.tsv.gz (original integrated table with record identifiers and lineage)27- interim/tables/2_cleaned/organism/cleaned.tsv.gz (cleaned organism data)28- interim/tables/1_translated/structure/final.tsv.gz (final structure data)29- interim/tables/2_cleaned/structure/named.tsv.gz (named structure data)30- interim/dictionaries/structure/npclassifier/smiles_np_classified.tsv.gz (NP-classifier structure annotations)31- interim/tables/2_cleaned/reference/cleaned.tsv.gz (cleaned reference data)3233## Outputs3435- interim/tables/3_curated/table.tsv.gz (denormalized curated structure-organism-reference table with all rows preserved and validated entries flagged)36- interim/dictionary/organism/dictionary.tsv.gz (derived organism lookup dictionary)37- interim/dictionary/organism/metadata.tsv.gz (organism metadata)38- interim/dictionary/structure/dictionary.tsv.gz (derived structure lookup dictionary)39- interim/dictionary/structure/metadata.tsv.gz (structure metadata)40- interim/dictionary/reference/dictionaryOrganism.tsv.gz (reference-to-organism mapping dictionary)41- interim/dictionary/reference/metadata.tsv.gz (reference metadata)4243## How to apply4445Load the original integrated table (interim/tables/0_original/table.tsv.gz) to anchor all row identifiers and preserve lineage. Sequentially load cleaned organism data (interim/tables/2_cleaned/organism/cleaned.tsv.gz), final structure data (interim/tables/1_translated/structure/final.tsv.gz), named structure data (interim/tables/2_cleaned/structure/named.tsv.gz), NP-classifier annotations (interim/dictionaries/structure/npclassifier/smiles_np_classified.tsv.gz), and cleaned reference data (interim/tables/2_cleaned/reference/cleaned.tsv.gz) using data.table or equivalent. Perform left-join operations in sequence: join organisms by organism_id, structures by structure_id, references by reference_id, and append NP-classifier annotations by SMILES or structure identifier. Extract unique organism, structure, and reference entries to generate derived dictionaries (organism/dictionary.tsv.gz, structure/dictionary.tsv.gz, reference/dictionaryOrganism.tsv.gz) and corresponding metadata tables. Consolidate all columns into a single denormalized table, compress to interim/tables/3_curated/table.tsv.gz, and flag validated entries while ensuring all rows from the original table are preserved.4647## Related tools4849- **R** (Execute 1_integrating.R script for join operations and denormalization logic) — https://github.com/lotusnprod/lotus-processor50- **data.table** (Perform efficient left-join operations on cleaned and translated tables in R)51- **gzip/compression utilities** (Compress consolidated table to interim/tables/3_curated/table.tsv.gz)5253## Examples5455```56Rscript 1_integrating.R --original interim/tables/0_original/table.tsv.gz --organism interim/tables/2_cleaned/organism/cleaned.tsv.gz --structure interim/tables/1_translated/structure/final.tsv.gz --reference interim/tables/2_cleaned/reference/cleaned.tsv.gz --npclassifier interim/dictionaries/structure/npclassifier/smiles_np_classified.tsv.gz --output interim/tables/3_curated/table.tsv.gz57```5859## Evaluation signals6061- All rows from interim/tables/0_original/table.tsv.gz are preserved in the output table; row count matches or exceeds original count (accounting for splits/denormalization).62- All organism_id, structure_id, and reference_id foreign keys are populated post-join with no unexpected nulls (unless intentional for unmatched rows in outer joins).63- Derived dictionaries (organism, structure, reference) contain unique entries corresponding to joined data; no orphaned or duplicate entries in dictionaries.64- Validated entries are flagged with consistent, boolean validation status column; distribution of validated vs. unvalidated rows is documented.65- Output file (interim/tables/3_curated/table.tsv.gz) decompresses without corruption; column schema matches documented specification (all organism, structure, reference, and NP-classifier columns present).6667## Limitations6869- Row count may increase if 1:many or m:m joins occur (e.g., one organism mapped to multiple structures); document join cardinality and verify denormalization is intentional.70- NP-classifier annotations are appended by SMILES or structure identifier; missing or incorrectly formatted SMILES values will result in annotation gaps; validate SMILES format before join.71- Lineage and validation status depend on correct preservation of original table identifiers; if primary keys are corrupted or renamed during cleaning, traceability is lost.72- No changelog is documented; version history and updates to cleaning, translation, or annotation pipelines are not tracked, complicating reproducibility and debugging.7374## Evidence7576- [methods] Consolidation via sequential joins and dictionary extraction: "Perform left join operations in sequence: join organisms by organism_id, join structure data by structure_id, join reference data by reference_id, and append NP-classifier annotations by SMILES or"77- [methods] Input table inventory and sources: "Load cleaned organism data from interim/tables/2_cleaned/organism/cleaned.tsv.gz, final structure data from interim/tables/1_translated/structure/final.tsv.gz, named structure data from"78- [methods] Lineage preservation and original table role: "Load the original integrated table from interim/tables/0_original/table.tsv.gz to preserve original record identifiers and lineage."79- [methods] Dictionary derivation procedure: "Generate derived organism dictionary (interim/dictionary/organism/dictionary.tsv.gz) and metadata (interim/dictionary/organism/metadata.tsv.gz) by extracting unique organism entries and their"80- [methods] Output specification and row preservation: "Consolidate all columns into a single denormalized table and compress to interim/tables/3_curated/table.tsv.gz, ensuring all rows from the original table are preserved and validated entries are"
