multi-way-join-operations-on-reference-tables
License: restricted — no clear open-source license detected for the underlying tool; verify licensing before commercial use or redistribution.
Summary
Sequentially join cleaned organism, structure, and reference tables by their identifiers to consolidate multi-source entries into a unified denormalized curated table while preserving original record lineage. This skill is essential when integrating heterogeneous reference data sources into a single comprehensive structure-organism-reference resource.
When to use
Apply this skill when you have independently cleaned and validated organism, structure, and reference tables (each keyed by organism_id, structure_id, and reference_id respectively) and need to consolidate them into a single denormalized curated table that maintains traceability to the original integrated table. The trigger is the completion of parallel cleaning pipelines (1_cleaningOriginal.R, 3_cleaningTranslated.R, 4_cleaningTaxonomy.R) that produce separate interim/tables/2_cleaned/{organism,structure,reference}/cleaned.tsv.gz files.
When NOT to use
- Input tables are not yet cleaned or validated — apply cleaning and enrichment steps first (cleaningOriginal, cleaningTranslated, cleaningTaxonomy).
- Original record identifiers and lineage are not critical — use this skill only when audit trail preservation is required.
- Structure-organism pairs need further deduplication or sampling — apply this skill before validation and platinum-standard filtering.
Inputs
- interim/tables/0_original/table.tsv.gz (original integrated table with record identifiers)
- interim/tables/2_cleaned/organism/cleaned.tsv.gz (cleaned organism data)
- interim/tables/1_translated/structure/final.tsv.gz (translated structure data)
- interim/tables/2_cleaned/structure/named.tsv.gz (named structure data)
- interim/tables/2_cleaned/reference/cleaned.tsv.gz (cleaned reference data)
- interim/dictionaries/structure/npclassifier/smiles_np_classified.tsv.gz (NP-classifier annotations)
Outputs
- interim/tables/3_curated/table.tsv.gz (denormalized curated table with all consolidated columns)
- interim/dictionary/organism/dictionary.tsv.gz (derived organism dictionary)
- interim/dictionary/organism/metadata.tsv.gz (organism metadata)
- interim/dictionary/structure/dictionary.tsv.gz (derived structure 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 from interim/tables/0_original/table.tsv.gz to preserve record identifiers and lineage. Sequentially perform left join operations: join organism data (interim/tables/2_cleaned/organism/cleaned.tsv.gz) by organism_id, join translated and named structure data (interim/tables/1_translated/structure/final.tsv.gz and interim/tables/2_cleaned/structure/named.tsv.gz) by structure_id, join cleaned reference data (interim/tables/2_cleaned/reference/cleaned.tsv.gz) by reference_id, and append NP-classifier structure annotations (interim/dictionaries/structure/npclassifier/smiles_np_classified.tsv.gz) by SMILES or structure identifier. Ensure all rows from the original table are preserved (use left join throughout, not inner join) and flag validated entries. Consolidate all columns into a single denormalized table to facilitate downstream analysis and dictionary generation.
Related tools
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/2_cleaned/structure/named.tsv.gz --reference interim/tables/2_cleaned/reference/cleaned.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 curated table (row count invariant: output ≥ original).
- No null-key violations: every organism_id, structure_id, and reference_id in the original table finds a matching row in the respective cleaned tables or is explicitly flagged as unmatched.
- Derived dictionaries (organism, structure, reference) contain unique entries with consistent metadata cardinality; spot-check organism/structure/reference_id distribution across dictionary and main table.
- Output table schema is flat with all organism, structure, reference, and NP-classifier columns denormalized into a single row per structure-organism-reference triplet.
- Validation flags correctly identify rows where all three cleaned data sources provided complete matches versus partial or missing matches.
Limitations
- Join key collisions or duplicate identifiers in cleaned tables may cause cartesian product expansion; validate key uniqueness in input tables before joining.
- SMILES-based NP-classifier annotation appending may fail if SMILES standardization differs between source and classifier dictionary; apply consistent SMILES sanitization before join.
- Large denormalized output table may exceed memory in R if structure or reference dictionaries are very large; consider chunked processing or data.table by-reference operations.
- Left join preserves all original rows but may introduce NAs in organism, structure, or reference columns if cleaned tables are incomplete; document and flag such rows.
Evidence
- [methods] task_002_finding: "The 2_curating stage uses 1_integrating.R to consolidate entries through organism, structure, and reference subgraphs, producing an integrated curated table."
- [methods] task_002_workflow_step_3: "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] task_002_workflow_step_1: "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] task_002_workflow_step_2: "Load the original integrated table from interim/tables/0_original/table.tsv.gz to preserve original record identifiers and lineage."
- [methods] task_002_workflow_step_7: "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"
- [methods] lotus_data_scale: "588694 unique referenced structure-organism pairs (484174 in 3D|2D format)"
- [readme] readme_tools: "Please make sure to have Make installed."
1---2name: multi-way-join-operations-on-reference-tables3description: Use when you have independently cleaned and validated organism, structure, and reference tables (each keyed by organism_id, structure_id, and reference_id respectively) and need to consolidate them into a single denormalized curated table that maintains traceability to the original integrated table.4license: CC-BY-4.05---67# multi-way-join-operations-on-reference-tables89> **License: restricted** — no clear open-source license detected for the underlying tool; verify licensing before commercial use or redistribution. <!-- asb-license-banner -->10## Summary1112Sequentially join cleaned organism, structure, and reference tables by their identifiers to consolidate multi-source entries into a unified denormalized curated table while preserving original record lineage. This skill is essential when integrating heterogeneous reference data sources into a single comprehensive structure-organism-reference resource.1314## When to use1516Apply this skill when you have independently cleaned and validated organism, structure, and reference tables (each keyed by organism_id, structure_id, and reference_id respectively) and need to consolidate them into a single denormalized curated table that maintains traceability to the original integrated table. The trigger is the completion of parallel cleaning pipelines (1_cleaningOriginal.R, 3_cleaningTranslated.R, 4_cleaningTaxonomy.R) that produce separate interim/tables/2_cleaned/{organism,structure,reference}/cleaned.tsv.gz files.1718## When NOT to use1920- Input tables are not yet cleaned or validated — apply cleaning and enrichment steps first (cleaningOriginal, cleaningTranslated, cleaningTaxonomy).21- Original record identifiers and lineage are not critical — use this skill only when audit trail preservation is required.22- Structure-organism pairs need further deduplication or sampling — apply this skill before validation and platinum-standard filtering.2324## Inputs2526- interim/tables/0_original/table.tsv.gz (original integrated table with record identifiers)27- interim/tables/2_cleaned/organism/cleaned.tsv.gz (cleaned organism data)28- interim/tables/1_translated/structure/final.tsv.gz (translated structure data)29- interim/tables/2_cleaned/structure/named.tsv.gz (named structure data)30- interim/tables/2_cleaned/reference/cleaned.tsv.gz (cleaned reference data)31- interim/dictionaries/structure/npclassifier/smiles_np_classified.tsv.gz (NP-classifier annotations)3233## Outputs3435- interim/tables/3_curated/table.tsv.gz (denormalized curated table with all consolidated columns)36- interim/dictionary/organism/dictionary.tsv.gz (derived organism dictionary)37- interim/dictionary/organism/metadata.tsv.gz (organism metadata)38- interim/dictionary/structure/dictionary.tsv.gz (derived structure 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 from interim/tables/0_original/table.tsv.gz to preserve record identifiers and lineage. Sequentially perform left join operations: join organism data (interim/tables/2_cleaned/organism/cleaned.tsv.gz) by organism_id, join translated and named structure data (interim/tables/1_translated/structure/final.tsv.gz and interim/tables/2_cleaned/structure/named.tsv.gz) by structure_id, join cleaned reference data (interim/tables/2_cleaned/reference/cleaned.tsv.gz) by reference_id, and append NP-classifier structure annotations (interim/dictionaries/structure/npclassifier/smiles_np_classified.tsv.gz) by SMILES or structure identifier. Ensure all rows from the original table are preserved (use left join throughout, not inner join) and flag validated entries. Consolidate all columns into a single denormalized table to facilitate downstream analysis and dictionary generation.4647## Related tools4849- **R data.table** (Perform sequential left join operations on large compressed tabular data in memory) — https://github.com/lotusnprod/lotus-processor/wiki50- **1_integrating.R** (Main integration script orchestrating multi-way join operations and dictionary generation) — https://github.com/lotusnprod/lotus-processor51- **Make** (Orchestrate the 2_curating stage workflow including join operations) — https://github.com/lotusnprod/lotus-processor5253## 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/2_cleaned/structure/named.tsv.gz --reference interim/tables/2_cleaned/reference/cleaned.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 curated table (row count invariant: output ≥ original).62- No null-key violations: every organism_id, structure_id, and reference_id in the original table finds a matching row in the respective cleaned tables or is explicitly flagged as unmatched.63- Derived dictionaries (organism, structure, reference) contain unique entries with consistent metadata cardinality; spot-check organism/structure/reference_id distribution across dictionary and main table.64- Output table schema is flat with all organism, structure, reference, and NP-classifier columns denormalized into a single row per structure-organism-reference triplet.65- Validation flags correctly identify rows where all three cleaned data sources provided complete matches versus partial or missing matches.6667## Limitations6869- Join key collisions or duplicate identifiers in cleaned tables may cause cartesian product expansion; validate key uniqueness in input tables before joining.70- SMILES-based NP-classifier annotation appending may fail if SMILES standardization differs between source and classifier dictionary; apply consistent SMILES sanitization before join.71- Large denormalized output table may exceed memory in R if structure or reference dictionaries are very large; consider chunked processing or data.table by-reference operations.72- Left join preserves all original rows but may introduce NAs in organism, structure, or reference columns if cleaned tables are incomplete; document and flag such rows.7374## Evidence7576- [methods] task_002_finding: "The 2_curating stage uses 1_integrating.R to consolidate entries through organism, structure, and reference subgraphs, producing an integrated curated table."77- [methods] task_002_workflow_step_3: "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"78- [methods] task_002_workflow_step_1: "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"79- [methods] task_002_workflow_step_2: "Load the original integrated table from interim/tables/0_original/table.tsv.gz to preserve original record identifiers and lineage."80- [methods] task_002_workflow_step_7: "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"81- [methods] lotus_data_scale: "588694 unique referenced structure-organism pairs (484174 in 3D|2D format)"82- [readme] readme_tools: "Please make sure to have Make installed."
