How to pivot a table with duplicate key pairs in Pandas (with Code)

Editorial · Published 5 November 2026

The Short Answer (TL;DR)

Use pivot_table with an explicit aggregation function when repeated row-key and column-key pairs represent legitimate repeated measurements. Audit pair counts first and retain observed-value counts so an absent pair, an all-missing pair and a measured zero remain distinguishable.

import pandas as pd
df = pd.DataFrame({"site":["A","A","B"],"period":["am","am","am"],"value":[10.,20.,4.]})
print(df.pivot_table(index="site",columns="period",values="value",aggfunc="mean",observed=True,dropna=False))

Detailed Implementation

This example uses synthetic data and does not need a network connection. Install the recorded package versions in a separate Python environment before running it.

python -m pip install numpy==2.5.4 pandas==3.0.6

from pathlib import Path
import numpy as np
import pandas as pd

def pivot_replicates(frame):
    if not frame.columns.is_unique or not {"site","period","value"}.issubset(frame.columns) or frame.empty:
        raise ValueError("Provide nonempty records with unique site, period and value columns.")
    if frame[["site","period"]].isna().any().any():
        raise ValueError("This example requires nonmissing pair keys.")
    if not pd.api.types.is_numeric_dtype(frame["value"]) or pd.api.types.is_bool_dtype(frame["value"]):
        raise ValueError("Values must be numeric measurements in common units.")
    if not np.isfinite(frame["value"].dropna().to_numpy(dtype=float)).all():
        raise ValueError("Observed values must be finite.")
    kwargs = dict(index="site",columns="period",values="value",observed=True,dropna=False,sort=False)
    means = frame.pivot_table(aggfunc="mean",**kwargs)
    rows = frame.pivot_table(aggfunc="size",**kwargs).reindex_like(means).fillna(0).astype("int64")
    observed = frame.pivot_table(aggfunc="count",**kwargs).reindex_like(means).fillna(0).astype("int64")
    return means,rows,observed

# Repeated measurements are intentional in this synthetic table.
data = pd.DataFrame({"site":["A","A","A","B","B","B","B","C"],
    "period":["morning","morning","evening","morning","morning","evening","evening","morning"],
    "value":pd.array([10,20,0,None,None,6,18,4],dtype="Float64")})
means,rows,observed = pivot_replicates(data)
report = "means\n"+means.to_string(na_rep="NA")+"\nrows\n"+rows.to_string()+"\nobserved\n"+observed.to_string()+"\n"
Path("duplicate-pair-pivot.txt").write_text(report,encoding="utf-8")
print(report)

Pair means include A-morning 15, A-evening 0, B-evening 12 and C-morning 4. B-morning is all-missing with two rows; C-evening is absent with zero rows. The count tables account for eight rows and six observed values.

Are duplicate keys an error or repeated measurements?

A-morning contains 10 and 20, and B-evening contains 6 and 18. The example treats these as legitimate equally weighted replicates and reports means 15 and 12. Plain pivot rejects repeated pairs because it cannot choose a single value. If repetitions instead came from an accidental duplicate import or an invalid join, aggregation would hide that problem; repair the data contract before summarizing.

Why keep two count tables?

B-morning has two rows but no observed value, while C-evening has no rows at all. Both means are missing, but their row counts differ: two versus zero. A-evening is a measured zero with one observed value. Filling the mean table with zero would erase all three distinctions. Zero-filled row and observed counts are appropriate here because an absent pair contains zero records; the measurement means remain missing.

What does the aggregation preserve?

All eight source records are accounted for in the row-count table, and six observed measurements are counted. Each mean combines only observed values for its pair. The raw replicates are not recoverable from a mean and count alone, so keep the source table. Reordering the source rows changes display order when sort=False, but aligned pair means and counts stay the same in the validation.

Which policies are explicit?

The helper rejects missing keys, nonnumeric values, infinite observed values and empty input. It requests only observed categorical levels and retains all-missing measurement columns. Keys remain site and period; observations use a common measurement unit. Choose a different aggregation only when it answers the scientific question: sums suit additive quantities, while a mean gives every observed replicate equal weight. Repeated visits from one subject are not automatically independent replicates.

Does a pivot establish a group difference?

The table is a descriptive rearrangement with an aggregation step. It does not calculate standard errors, confidence intervals or a hypothesis test, and the number of records need not be the number of independent experimental units. Compare observed distributions and the measurement design before interpreting cell differences. A cell with two missing measurements contains no evidence that its unobserved mean equals zero.

Mathematical Context

For a site-period pair (a,b), let O_ab contain the records with observed values and let n_ab be their count. The reported mean is their sum divided by n_ab when n_ab is positive. Its units are the original measurement units. The row-count table also counts missing measurements, while the observed-count table gives the denominator of the mean. For A-morning, (10+20)/2=15; for B-evening, (6+18)/2=12. B-morning has no defined observed mean even though it contains two records.

\[ \bar{x}_{ab}=\frac{1}{n_{ab}}\sum_{i\in O_{ab}}x_i,\quad n_{ab}=|O_{ab}|>0 \]

Reproducibility

Executed with Python 3.12.14; numpy 2.5.4, pandas 3.0.6. Automated checks confirm the stated output properties.