Join Protein Sequences and Labels Safely with pandas

A sequence table and a label table can contain the same number of rows and still describe different sample orders. Attaching labels by row position can silently train a model on the wrong targets.

This tutorial joins two CSV files by sample_id, checks that every sample has exactly one record on each side, and writes a training table only after those checks pass. It follows the FASTA audit and complements the SHA-256 manifest tutorial: a checksum records which files you used; a join audit checks how their records line up.

1. Define the record you are matching

For this example, one sample_id identifies one sequence and one classification label. The two inputs must contain exactly these columns:

Identifiers are case-sensitive strings. 001 and 1 are different IDs. The script rejects surrounding whitespace in IDs instead of silently trimming it. It also allows a literal ID such as NA; blank fields are checked explicitly.

Choose this identity policy before joining real data. If a protein has measurements from several assays, protein_id alone may not identify a measurement. Use an appropriate measurement ID or an explicitly defined combination of protein, assay, and condition. Do not collapse repeated measurements just to make a one-to-one check pass.

2. Install and create the example files

Use a new working directory with Python 3.11 or newer. The example was tested with Python 3.14.7 and pandas 3.0.6 on macOS. The shell commands below use Bash or Zsh.

python3 -m venv .venv
source .venv/bin/activate
python -m pip install "pandas==3.0.6"

Download join_labels.py, or save the complete script below with that filename. Then create two small CSV files:

python join_labels.py --help
python - <<'PY'
from pathlib import Path
Path("sequences.csv").write_text(
    "sample_id,sequence\n001,ACDE\n002,FGHI\nNA,KLMN\n",
    encoding="utf-8",
)
Path("labels.csv").write_text(
    "sample_id,label\nNA,positive\n001,negative\n002,positive\n",
    encoding="utf-8",
)
PY
python join_labels.py sequences.csv labels.csv joined.csv

These are invented sequences and labels for demonstrating the join, not biological results. The labels are deliberately in a different order. Expected output:

OK: 3 sequences, 3 labels, 3 matched rows

The resulting joined.csv is:

sample_id,sequence,label
001,ACDE,negative
002,FGHI,positive
NA,KLMN,positive

The output follows the sequence table’s row order and attaches the correct label to each ID. Equal input lengths alone would not establish that alignment.

3. Read IDs deliberately and audit the join

"""Join tidy sequence and label CSVs by sample_id, with a strict 1:1 policy."""
import argparse
from pathlib import Path
import sys
import warnings

import pandas as pd


def read_table(path, value_column):
    with warnings.catch_warnings():
        warnings.simplefilter("error", pd.errors.ParserWarning)
        table = pd.read_csv(path, dtype="string", keep_default_na=False,
                            skip_blank_lines=False, index_col=False)
    columns = ["sample_id", value_column]
    if len(table.columns) != 2 or set(table.columns) != set(columns):
        raise ValueError(f"{path}: expected exactly {columns}")
    table = table[columns]
    if table.empty:
        raise ValueError(f"{path}: no data rows")
    for column in columns:
        values = table[column]
        if values.isna().any() or values.str.strip().eq("").any():
            raise ValueError(f"{path}: blank {column}")
    ids = table["sample_id"]
    if ids.ne(ids.str.strip()).any():
        raise ValueError(f"{path}: sample_id has surrounding whitespace")
    duplicates = ids[ids.duplicated(keep=False)].unique().tolist()
    if duplicates:
        raise ValueError(f"{path}: duplicate sample_id: {duplicates[:5]}")
    return table


def join_tables(sequences, labels):
    audit = sequences.merge(labels, on="sample_id", how="outer",
                            validate="one_to_one", indicator=True)
    missing = audit.loc[audit["_merge"].eq("left_only"), "sample_id"].tolist()
    extra = audit.loc[audit["_merge"].eq("right_only"), "sample_id"].tolist()
    if missing or extra:
        raise ValueError(f"ID mismatch: missing labels={missing[:5]}; "
                         f"labels without sequences={extra[:5]}")
    # Use the left join only after the full coverage audit has passed.
    return sequences.merge(labels, on="sample_id", how="left", sort=False,
                           validate="one_to_one")


def main():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("sequences", type=Path)
    parser.add_argument("labels", type=Path)
    parser.add_argument("output", type=Path)
    args = parser.parse_args()
    try:
        sequences = read_table(args.sequences, "sequence")
        labels = read_table(args.labels, "label")
        joined = join_tables(sequences, labels)
        # Exclusive creation preserves earlier outputs and the input files.
        with args.output.open("x", encoding="utf-8", newline="") as handle:
            joined.to_csv(handle, index=False, lineterminator="\n")
        print(f"OK: {len(joined)} sequences, {len(labels)} labels, "
              f"{len(joined)} matched rows")
        return 0
    except (OSError, ValueError, pd.errors.ParserError,
            pd.errors.ParserWarning, pd.errors.EmptyDataError,
            pd.errors.MergeError) as error:
        print(f"ERROR: {error}", file=sys.stderr)
        return 2


if __name__ == "__main__":
    sys.exit(main())

The read_csv documentation describes the parsing controls used here. dtype="string" preserves leading zeros, and keep_default_na=False retains literal tokens such as NA. The script then rejects empty fields itself. skip_blank_lines=False lets the checks catch blank records; parser warnings become errors so a field-count warning cannot pass unnoticed.

For this tutorial, labels stay as strings. If a source system uses NA, NULL, or another token to mean a missing label, add that rule explicitly before joining. A regression task also needs numeric conversion, finite-value checks, and any domain-specific range checks. Nonblank text is not sufficient target validation.

There are two stages to the merge. The outer audit uses validate="one_to_one" to require unique keys and indicator=True to expose IDs present on just one side. After that audit passes, a left join constructs the final table in sequence order. The merge API reference documents these options and warns that null join keys can match each other, which is why missing IDs are rejected before merging.

4. Make a missing record fail visibly

Create a second label file that lacks sample 002, leaving the original file intact:

python - <<'PY'
from pathlib import Path
Path("labels-missing.csv").write_text(
    "sample_id,label\nNA,positive\n001,negative\n",
    encoding="utf-8",
)
PY
python join_labels.py sequences.csv labels-missing.csv rejected.csv
printf 'exit code: %s\n' "$?"

Expected output:

ERROR: ID mismatch: missing labels=['002']; labels without sequences=[]
exit code: 2

The program does not create rejected.csv. It also rejects duplicate IDs, IDs found only in the label table, blank fields, and the wrong column schema. Error messages show at most five example IDs per category; they are diagnostic samples, not a full audit export.

A successful run exits with 0; validation or file errors exit with 2. Output uses exclusive creation, so running the successful command again with joined.csv produces File exists and preserves the existing result. Choose a new output name for a deliberate revision.

5. Avoid fixes that hide the problem

An inner join can discard unmatched records. A left join can retain sequence rows while leaving their labels missing. The outer audit makes both directions visible before producing the training table.

If an ID occurs twice in each input, a many-to-many join can produce four rows for that ID. The pandas merging guide explains this Cartesian expansion. Removing duplicates without checking their meaning can hide conflicting measurements or incorrectly merge distinct samples.

For real repeated measurements, first define the unit of analysis. A many-to-one join may be appropriate when several measurements refer to one sequence record, but that is a different data contract. Preserve measurement identifiers and define how repeats will be grouped during model evaluation.

This script reads both tables into memory and builds an intermediate audit table. It is intended for tidy tables that fit in memory. It checks record alignment, not sequence validity, assay correctness, or biological independence. Different sample IDs can still describe the same protein sequence.

6. Preserve alignment after the join

Save joined.csv with the source tables and include all three in your dataset checksum manifest. Record the join script and dependency version alongside the result.

When generating embeddings, preserve the sample ID associated with every vector row. If batching changes processing order, rebuild the label order from those saved IDs before fitting a model. A correct CSV join cannot recover an unlabeled embedding matrix whose row mapping has been lost.

Next, apply the grouped split policy. Unique join keys do not prevent the same sequence, related proteins, or repeated measurements from appearing across training and test sets.

7. Troubleshooting

The example passed checks for shuffled labels, leading-zero and literal NA IDs, duplicates on either side, unmatched IDs, blank values, whitespace, malformed rows, empty inputs, and overwrite protection. The tests also checked the exact output labels and row order.

Keep the key with the data

Join by a defined sample identifier, require the intended relationship, and inspect unmatched records before writing training input. Keep those identifiers through feature extraction and evaluation. That small amount of bookkeeping makes a wrong label assignment easier to catch before it becomes a model result.

The cover is conceptual artwork. pandas documentation checked September 27, 2026.