Reshaping with pivot and melt

Core Principle: Reshaping transforms DataFrames between wide formats (via pivot) for matrix computation and long formats (via melt) for grouped aggregation and statistical visualization.

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.

   gene   transcripts
0  TP53     NM_000546
0  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

Exercise

Write cpm_matrix(text). Stdin holds a wide count matrix: a header line whose first field names the identifier column and whose remaining fields are sample names, then one tab-separated row per gene. Gene names are unique. Return a list of strings.

Melt the matrix to long form, compute counts per million within each sample (count / sample_total * 1e6), then pivot back to the original wide shape. Return the header line unchanged, followed by one line per gene in the input row order, with each value rounded to three decimal places. A sample whose counts sum to zero has no meaningful library size: every one of its CPM values must be 0.0 rather than NaN or an infinity. A matrix with a header but no gene rows returns only the header line.