# Multi Way Join Operations On Reference Tables

> 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.

- Skill: `holobiomicslab/multi-way-join-operations-on-reference-tables` (Agent Skill)
- Install (CLI): `npx skillmds@latest add holobiomicslab/multi-way-join-operations-on-reference-tables`
- Raw SKILL.md: https://api.skillmd.com/api/skills/holobiomicslab/multi-way-join-operations-on-reference-tables/raw
- Safety review: PASS (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- License: CC-BY-4.0
- Author: HolobiomicsLab (https://skillmd.com/u/holobiomicslab)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/holobiomicslab/multi-way-join-operations-on-reference-tables

---


# 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. <!-- asb-license-banner -->
## 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

- **R data.table** (Perform sequential left join operations on large compressed tabular data in memory) — https://github.com/lotusnprod/lotus-processor/wiki
- **1_integrating.R** (Main integration script orchestrating multi-way join operations and dictionary generation) — https://github.com/lotusnprod/lotus-processor
- **Make** (Orchestrate the 2_curating stage workflow including join operations) — https://github.com/lotusnprod/lotus-processor

## 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."