Merging and Joining

In one line: merge is a SQL join on columns — and if you are not passing validate=, you are one duplicate key away from a silently wrong answer.

The syntax

df.merge(other, on="gene", how="left")
df.merge(other, left_on="gene_id", right_on="id", how="inner")
df.merge(other, on=["chrom", "pos"], how="outer")
df.merge(other, left_index=True, right_index=True, how="inner")
df.join(other, how="left")                # index-based shorthand

The join types

how	Keeps
"inner"	keys in both (default)
"left"	all of left, NaN where right has no match
"right"	all of right
"outer"	all keys from either side
"cross"	every combination (no key)

For annotation, left join is almost always right: keep every one of your variants/genes, and get NaN where the annotation is missing. An inner join silently drops your unannotated rows, which is how a "clean" result set quietly loses 30% of the data.

validate= — the most important argument

df.merge(other, on="gene", how="left", validate="many_to_one")

Options: "one_to_one", "one_to_many", "many_to_one", "many_to_many". pandas raises if the actual key cardinality does not match.

Why this matters. If the right-hand key is not unique, a left join multiplies rows:

variants          # 1,000,000 rows
annotations       # gene → description, but 50 genes appear twice
merged            # 1,000,050 rows — and 50 variants are now counted twice

No error. No warning. Every downstream count, mean and test is now subtly wrong. validate="many_to_one" turns this into an exception at the point of the mistake.

Make it a habit: every merge gets a validate=. If you do not know the cardinality, that itself is the problem.

indicator= — did it match?

merged = df.merge(annot, on="gene", how="left", indicator=True)
merged["_merge"].value_counts()
# both          950000
# left_only      50000     ← 50k variants with no annotation

Combined with validate, this is a complete join audit in two arguments.

Suffixes

df.merge(other, on="gene", suffixes=("_tumour", "_normal"))

Overlapping non-key column names get suffixed. The default ("_x", "_y") is uninformative — always set it explicitly, or you will be reading af_x in three weeks wondering which file it came from.

concat vs merge

• merge — combine columns by matching keys (horizontal, key-based). See below.
• concat — stack frames (vertical) or glue columns positionally/by index. See Concat.

The ID mismatch problem

The most common real-world join failure in bioinformatics is not the join mechanics — it is that the keys do not match.

# version suffixes
"ENSG00000141510.16"  vs  "ENSG00000141510"
df["gene_id"] = df["gene_id"].str.split(".").str[0]

# chromosome naming
"chr1" (UCSC)  vs  "1" (Ensembl)
df["chrom"] = df["chrom"].str.removeprefix("chr")

# gene symbols vs IDs, and symbol aliases
"TP53" vs "P53" vs "ENSG00000141510"

# case and whitespace
df["gene"] = df["gene"].str.strip().str.upper()

# Excel damage — genuinely a documented problem in genomics
"SEPT9" → "9-Sep",  "MARCH1" → "1-Mar"

That last one is real: HGNC renamed several genes (SEPT→SEPTIN, MARCH→MARCHF) in 2020 specifically because Excel kept corrupting them. If a collaborator sends you a gene list as .xlsx, check for date-shaped values.

Always check the overlap before you join:

left_keys  = set(df["gene_id"])
right_keys = set(annot["gene_id"])
print(f"left only:  {len(left_keys - right_keys):,}")
print(f"right only: {len(right_keys - left_keys):,}")
print(f"shared:     {len(left_keys & right_keys):,}")

If "shared" is zero or suspiciously small, it is a formatting mismatch, not biology.

merge_asof — nearest-key joins

For "find the closest preceding record", which comes up with genomic positions and time series:

pd.merge_asof(
    variants.sort_values("pos"),
    genes.sort_values("start"),
    left_on="pos", right_on="start",
    by="chrom",                     # match within chromosome
    direction="backward",           # nearest gene start at or before the variant
)

Both frames must be sorted on the key. by= restricts matching to within groups, which is exactly what you want for chromosomes.

This is nearest, not overlapping. For true interval overlap ("which gene contains this variant"), pandas is the wrong tool — use pyranges or bioframe. See Applied - GFF and Genomic Intervals.

Checking the join afterwards

before = len(df)
merged = df.merge(annot, on="gene", how="left", validate="many_to_one", indicator=True)
assert len(merged) == before, f"row count changed: {before} → {len(merged)}"
print(merged["_merge"].value_counts())

Three lines. They will catch more bugs than any amount of careful reading.

Bioinformatics examples

# annotate DE results with gene info
de = de.merge(gene_annot, left_index=True, right_on="gene_id",
              how="left", validate="one_to_one")

# add population frequencies to a variant call set
variants = variants.merge(
    gnomad[["chrom", "pos", "ref", "alt", "af", "af_popmax"]],
    on=["chrom", "pos", "ref", "alt"], how="left", validate="many_to_one",
)
print(f"{variants['af'].isna().mean():.1%} not in gnomAD")

# attach sample metadata to a long expression table
long = long.merge(metadata, on="sample_id", how="left", validate="many_to_one")

# tumour vs normal, same gene
paired = tumour.merge(normal, on="gene", suffixes=("_tumour", "_normal"))
paired["delta"] = paired["expr_tumour"] - paired["expr_normal"]

Common mistakes

• No validate=. Row multiplication, silently.
• Inner join where you meant left, dropping unannotated rows.
• Key format mismatch — version suffixes, chr prefix, case, whitespace.
• Not checking _merge counts after a left join.
• Default ("_x", "_y") suffixes.
• Merging on a float column. Floating-point equality is unreliable.
• Merging on a categorical with different categories — degrades to object or raises.
• Forgetting merge_asof needs sorted input.
• Using merge_asof for interval overlap. It finds the nearest key, not containment.

See also

Concat · groupby · Index Objects · Missing Data in pandas · Applied - Annotating a Variant Table · Applied - GFF and Genomic Intervals