Reshaping with pivot and melt

In one line: melt goes wide → long (for plotting and grouping), pivot goes long → wide (for matrices and human reading).

See Tidy Data for why the distinction exists.

melt: wide → long

# wide: gene | sample_1 | sample_2 | sample_3
long = wide.melt(
    id_vars="gene",                # columns to KEEP as identifiers
    var_name="sample",             # name for the old column headers
    value_name="count",            # name for the values
)
# long: gene | sample | count

wide.melt(id_vars=["gene", "chrom"])                  # several id columns
wide.melt(id_vars="gene", value_vars=["s1", "s2"])    # only some columns

Melt when: the column headers are actually values (sample names, timepoints, years), and you are about to plot, group, or run a statistical model.

pivot: long → wide

wide = long.pivot(index="gene", columns="sample", values="count")

pivot requires the index/columns combination to be unique. Duplicates raise ValueError: Index contains duplicate entries.

pivot_table: pivot with aggregation

wide = long.pivot_table(
    index="gene", columns="sample", values="count",
    aggfunc="mean",              # how to combine duplicates
    fill_value=0,
    margins=True,                # add row/column totals
)

pivot_table handles duplicates by aggregating them, which is both convenient and a trap: if you did not expect duplicates, it silently averages them instead of telling you something is wrong. Use pivot when the data should be unique — the error is information.

aggfunc="size" gives you a contingency table:

variants.pivot_table(index="gene", columns="consequence",
                     values="pos", aggfunc="size", fill_value=0)

pd.crosstab is the shorthand for exactly that:

pd.crosstab(variants["gene"], variants["consequence"])
pd.crosstab(variants["chrom"], variants["impact"], normalize="index")   # row proportions

stack and unstack

The MultiIndex-aware equivalents:

wide.stack()                       # columns → an index level (wide → long)
long_mi.unstack()                   # innermost index level → columns
long_mi.unstack(level="sample")
long_mi.unstack(fill_value=0)

stack on an expression matrix gives you a Series with a (gene, sample) MultiIndex — often more convenient than melt when you want to keep the labels as index rather than columns. See MultiIndex.

explode: one row per list element

Not wide/long exactly, but the third reshaping tool you will need constantly in genomics, because bio formats pack lists into single cells.

df = pd.DataFrame({"gene": ["TP53"], "transcripts": [["NM_000546", "NM_001126112"]]})
df.explode("transcripts")
# TP53 | NM_000546
# TP53 | NM_001126112

Combined with a string split, this untangles delimited fields:

variants.assign(csq=variants["consequence"].str.split("&")).explode("csq")

A variant annotated missense_variant&splice_region_variant becomes two rows, one per consequence, which is what you need before counting consequence types. See String Accessor.

The full VCF INFO untangling

The canonical bio reshaping problem, using all three tools:

info = (
    variants["INFO"]
      .str.split(";")                          # 'AF=0.3;DP=44' → ['AF=0.3', 'DP=44']
      .explode()                                # one key=value per row
      .str.split("=", n=1, expand=True)         # → two columns
      .rename(columns={0: "key", 1: "value"})
)
info["variant_idx"] = info.index
wide_info = info.pivot_table(index="variant_idx", columns="key",
                             values="value", aggfunc="first")
variants = variants.join(wide_info)

See Applied - Reading VCF with pandas for the complete treatment including type coercion.

Bioinformatics examples

# expression matrix → long, for seaborn
long = expr.reset_index().melt(id_vars="gene", var_name="sample", value_name="count")
long = long.merge(metadata, on="sample", how="left", validate="many_to_one")
sns.boxplot(data=long, x="condition", y="count", hue="tissue")

# long results → a wide matrix for clustering
mat = long.pivot(index="gene", columns="sample", values="log_cpm")
sns.clustermap(mat, z_score=0, cmap="vlag")

# contingency: consequence type by chromosome
pd.crosstab(variants["chrom"], variants["impact"], normalize="index")

# genotype matrix from long calls
gt = calls.pivot(index="variant_id", columns="sample_id", values="genotype")

# per-sample-per-metric QC summary
qc.pivot_table(index="sample", columns="metric", values="value")

Choosing a direction

Goal	Shape
seaborn plotting	long
groupby on sample/condition	long
Statistical modelling (formula-based)	long
Matrix maths: PCA, correlation, clustering	wide
Heatmap	wide
Human reading / a supplementary table	wide
Storage of a large matrix	wide (long repeats every label)

The practical rhythm: store wide, melt at the point of plotting, discard the long frame.

Common mistakes

• pivot on non-unique index/column pairs → ValueError. Use pivot_table, but first ask why there are duplicates.
• pivot_table silently averaging unexpected duplicates.
• Melting without id_vars, which melts your identifier columns too.
• Forgetting to reset_index() before melting an index-carrying matrix — the gene names are in the index, not a column, so id_vars="gene" raises KeyError.
• Long-format memory blowup. A 20,000 × 500 matrix is 10 million rows long, with the gene name repeated 500 times each.
• explode on a string instead of a list. Split first; explode on a plain string does nothing.
• fill_value=0 in a pivot where the true meaning of absent is "not measured", not "zero". That distinction matters in proteomics and scRNA-seq.

See also

Tidy Data · groupby · String Accessor · MultiIndex · Applied - Reading VCF with pandas · Applied - Heatmaps and Clustermaps