Pepkio Bio Table Matchmaker

Perform structure-preserving relational table joins and biological identifier matching across genomic, transcriptomic, and proteomic data tables using the Pepkio Tools API from Python environments.

What It Does

Biological datasets—such as differential RNA-seq expression matrices, LC-MS/MS proteomics tables, and qPCR plate maps—frequently rely on inconsistent accession numbers, Ensembl gene IDs with decimal versions, variable casing, or trailing whitespace. Merging these datasets in general-purpose spreadsheet software often leads to silent data corruption, such as automatic conversion of gene symbols (e.g. SEPT9) into calendar dates or unhandled one-to-many matches.

This package provides a programmatic Python interface to the Pepkio Tools engine powering the hosted Bio Table Matchmaker web tool. Execute left, inner, right, or full outer joins between CSV and TSV tables with automatic version-suffix stripping, case normalization, composite key matching, duplicate key handling, and column collision resolution.

Programmatic runs require a network connection and a Pepkio API key. Join operations and normalization logic are processed remotely via the API engine.

Features

• Relational join modes: left, inner, right, and full outer joins between delimited biological datasets
• Identifier normalization: strip Ensembl/NCBI decimal version tags (e.g., .15 from ENSG00000139618.15), normalize case, and trim whitespace
• Flexible key selection: match on single key columns or multi-column composite keys
• Duplicate key resolution: retain first match, retain last match, or generate Cartesian expansions
• Overlapping column resolution: configurable custom prefixes or suffixes for duplicate non-key headers
• Customizable null representations: format missing values as NA, NaN, or custom strings
• Manifest & example inspection: retrieve input schemas and examples without an API key using get_manifest() or CLI commands
• Configuration via PEPKIO_API_KEY and PEPKIO_API_BASE_URL

Installation

pip install pepkio-bio-table-matchmaker

Set an API key with tools:run scope before executing join operations:

export PEPKIO_API_KEY="your-key"

Obtain an API key in your Pepkio account API keys settings.

Quick Example

from pepkio_bio_table_matchmaker import PepkioClient

table_a_tsv = "ensembl_gene_id\tlog2FoldChange\tpvalue\nENSG00000141510.16\t2.45\t0.00012\nENSG00000139618.15\t-1.82\t0.00340\n"
table_b_tsv = "ensembl_id\tsymbol\tdescription\nENSG00000141510\tTP53\tTumor protein p53\nENSG00000139618\tBRCA2\tBRCA2 DNA repair associated\n"

with PepkioClient() as client:
    result = client.run(
        input={
            "tab": "matchmaker",
            "table_a_text": table_a_tsv,
            "table_b_text": table_b_tsv,
            "key_column_a": "ensembl_gene_id",
            "key_column_b": "ensembl_id",
            "join_type": "left",
            "trim_version_suffixes": True,
            "ignore_case": True,
        }
    )
    print("Execution Status:", result.status)
    print("Merged Row Count:", result.result.get("join_result", {}).get("row_count"))
    print("Run Permalink:", result.permalink)

CLI:

pepkio-bio-table-matchmaker run --example load_example

Manifest inspection (no API key required):

pepkio-bio-table-matchmaker manifest

Typical Use Cases

• Merging DESeq2 or edgeR differential gene expression tables with BioMart Ensembl annotations
• Aligning LC-MS/MS proteomics peptide/protein intensity tables with UniProt metadata
• Combining raw qPCR microplate reader Cq output tables with experimental plate maps
• Mapping single-cell cluster marker gene lists from Seurat or Scanpy to reference cell-type databases
• Cross-referencing VCF-derived genomic variant tables with patient clinical outcome matrices

Scientific Background

Relational set operations combine tables $A$ and $B$ over primary keys $K_A$ and $K_B$. Left outer joins ($A \bowtie_L B$) retain all records from $A$, filling missing attributes of $B$ with nulls ($\varnothing$), while inner joins ($A \bowtie B$) keep only exact key intersections.

Biological identifiers introduce key mismatches due to Ensembl versioning (e.g. ENSG00000141510.16 vs ENSG00000141510), case variations (TP53 vs tp53), or 1:N transcript-to-gene relationships. Automatic date parsing in standard spreadsheet software frequently corrupts gene symbols like SEPT9 or MARCH1. Normalizing identifiers prior to set joins ensures exact row alignment while preserving source data integrity.

Web Application

For researchers who prefer a graphical interface, an interactive Bio Table Matchmaker web tool is available.

The web application adds in-browser file upload (CSV/TSV), real-time visual join previews, interactive key column selection, match metric summaries, downloadable merged datasets, and shareable permalinks.

Documentation and Resources

Source code and issue tracking: github.com/pepkio/pepkio-bio-table-matchmaker

Web application: pepkio.com/tools/bio-table-matchmaker

About Pepkio

Pepkio develops software tools and provides bioinformatics analysis services for life science research. See pepkio.com for additional tools and services, or explore analysis services for custom bioinformatics workflows.

Keywords

bio table matchmaker, biological table joiner, CSV gene ID matcher, TSV table merger, Ensembl ID matcher, differential expression table join, RNA-seq data merger, bioinformatic table join, VLOOKUP biological alternative, gene identifier mapping, UniProt accession matchmaker, qPCR plate map merger, proteomics dataset alignment, left join biological tables, inner join bioinformatics, full outer join RNA-seq, Ensembl version suffix trimmer, case insensitive gene matching, biological tabular data integration, multi column key matcher, duplicate key resolution, bioinformatics CSV merger, gene symbol annotation join, DESeq2 result merger, edgeR table joiner, single cell marker table match, metabolomics peak table alignment, clinical genomic data joiner, pandas merge web tool, R merge alternative, zero data corruption table merger, reproducible biological data merging, laboratory data table join, biological key column matcher, identifier normalization tool, fast TSV merge software, tabular data matchmaker, multi-omics dataset integration, web-based bioinformatics table merger, programmatic table join API, python biological table client, pepkio bio matchmaker, automated gene annotation join, high-throughput table matching, reproducible bioinformatics workflow, metadata table integration