DataFrame.pivot raises ValueError: Index contains duplicate entries, cannot reshape when two or more rows share the same index/columns pair, because pivot only reshapes and has no rule for combining values. Fix it by deciding what the duplicates are: separate sub-records (use pivot_table with an explicit aggfunc, such as "sum" for counts), accidental re-ingested copies (drop_duplicates first), or revisions (keep the latest row, then pivot). Don't just swap in pivot_table: its default aggfunc is "mean". The pandas pivot reference documents the ValueError, and the pivot_table reference shows aggfunc="mean" and, since pandas 3.0.0, observed=True by default. Every snippet below was run on pandas 3.0.6.

All data in this post is simulated (NumPy default_rng(20261120)): eight placeholder tickers S01–S08, ten business days, three news sources, Poisson mention counts. No real stock or real feed is involved. Tested with Python 3.13.5, pandas 3.0.6, NumPy 2.5.5 and matplotlib 3.11.2.

Q1. What exactly triggers the error?

Any (index, columns) pair that appears more than once. Three rows are enough:

import numpy as np import pandas as pd tiny = pd.DataFrame({ "date": ["2026-03-02", "2026-03-02", "2026-03-02"], "stock": ["S01", "S01", "S02"], "source": ["portal", "forum", "portal"], "mentions": [12, 5, 7], }) tiny.pivot(index="date", columns="stock", values="mentions") # ValueError: Index contains duplicate entries, cannot reshape 

S01 on 2026-03-02 has two rows (portal and forum). pivot would need to put both numbers in one cell, so it stops.

Now a bigger long table, the shape you get from almost any news-count loader: one row per date, stock, and source. Each stock also belongs to a group (board_A / board_B), the kind of mapping you would pull from concept-to-stock membership lists. I add two realistic problems on purpose: a loader re-run that appended two days twice, and a two-day outage where S07 has no rows at all.

rng = np.random.default_rng(20261120) dates = pd.bdate_range("2026-03-02", periods=10) stocks = [f"S{i:02d}" for i in range(1, 9)] board = {s: ("board_A" if i < 4 else "board_B") for i, s in enumerate(stocks)} sources = ["portal", "forum", "app"] rows = [] for d in dates: for s in stocks: for src in sources: if rng.random() < 0.15: # some source/day cells are simply empty continue rows.append((d, s, board[s], src, int(rng.poisson(20 if src == "portal" else 8)))) long = pd.DataFrame(rows, columns=["date", "stock", "board", "source", "mentions"]) # feed outage: no rows at all for S07 on two days long = long[~((long["stock"] == "S07") & long["date"].isin(dates[[5, 6]]))] # a loader re-run appended two days a second time rerun = long[long["date"].isin(dates[[3, 7]])] long = pd.concat([long, rerun], ignore_index=True) print(len(long), len(rerun)) # 238 38 

Q2. How do I find the rows that cause it?

Use duplicated on the pivot key with keep=False (marks every member of a duplicate group), or count group sizes:

key = ["date", "stock"] dup_mask = long.duplicated(subset=key, keep=False) print(dup_mask.sum(), "of", len(long)) # 234 of 238 sizes = long.groupby(key).size() print((sizes > 1).sum(), "of", len(sizes)) # 74 of 78 keys print(sizes[sizes > 1].head(3)) # date stock # 2026-03-02 S01 3 # S02 3 # S03 3 # are any of them exact copies across *all* columns? print(long.duplicated(keep=False).sum()) # 76 

Two different stories hide in those numbers. Most keys repeat because there are several sources per stock per day; that is expected. But 76 rows are identical in every column, and those come from the re-run.

Q3. Can I just switch to pivot_table?

It will run, but the default aggfunc is "mean", which is rarely what you want for counts:

wide_mean = long.pivot_table(index="date", columns="stock", values="mentions") wide_sum_raw = long.pivot_table(index="date", columns="stock", values="mentions", aggfunc="sum") print(wide_mean.loc[dates[0], "S01"]) # 11.666666666666666 print(wide_sum_raw.loc[dates[0], "S01"]) # 35.0 print(round(wide_mean.sum().sum(), 1), # 946.3 wide_sum_raw.sum().sum(), # 2869.0 long["mentions"].sum()) # 2869 

The mean pivot reports about a third of the real daily volume, since most cells average three sources. No warning, no error, just a smaller number. Always pass aggfunc explicitly, and sanity-check that the pivot total equals the raw column total when you sum.

Q4. What if the duplicates are accidental copies?

Then aggregating hides the bug: aggfunc="sum" happily adds the copies. Drop them on the natural key of a row first. Here a row is unique per date, stock, and source:

clean = long.drop_duplicates(subset=["date", "stock", "source"], keep="last") wide = clean.pivot_table(index="date", columns="stock", values="mentions", aggfunc="sum") print(len(clean), wide.sum().sum()) # 200 2415.0 print(wide_sum_raw.sum().sum() - wide.sum().sum()) # 454.0 print((wide_sum_raw.sum() / wide.sum() - 1).round(3)) # S01 0.179 S02 0.140 S03 0.167 S04 0.221 # S05 0.151 S06 0.266 S07 0.264 S08 0.134 

Two re-run days inflated ten-day totals by 13–27% depending on the stock, so any comparison across stocks would shift too. Deduplicate on the natural key, not on all columns, if a later load can carry a corrected value for the same row; keep="last" then keeps the newer one, assuming rows are appended in load order.

Grouped bar chart of simulated 10-day mention totals for eight placeholder stocks S01 to S08 under three pivots: grey bars for pivot_table with the default mean (about 98 to 130), red bars for aggfunc sum on raw rows including two re-ingested days (273 to 424), and blue bars for aggfunc sum after drop_duplicates (216 to 344).

Q5. Is groupby().unstack() any different from pivot_table?

For a single value column and one aggregation, no. They give the same frame:

alt = clean.groupby(key)["mentions"].sum().unstack("stock") print(alt.equals(wide)) # True print(int(wide.isna().sum().sum())) # 2 -> the S07 outage wide0 = clean.pivot_table(index="date", columns="stock", values="mentions", aggfunc="sum", fill_value=0) print(round(wide["S07"].mean(), 2), round(wide0["S07"].mean(), 2)) # 27.0 21.6 print(wide.loc[dates[4]:dates[7], ["S06", "S07"]]) # S06 S07 # 2026-03-06 15.0 46.0 # 2026-03-09 38.0 NaN # 2026-03-10 37.0 NaN # 2026-03-11 53.0 22.0 

Note the two NaN cells. They mean "no rows arrived", not "nobody mentioned S07". fill_value=0 turns the outage into two quiet days and drags the S07 average from 27.0 to 21.6. Keep NaN until you know a zero is real.

Q6. Why is a stock on my watchlist missing from the columns?

Pivoting only creates columns for keys that occur. If you make stock categorical, pandas 3.0 changed the default to observed=True, so unused categories are dropped as well. observed=False brings them back, but with aggfunc="sum" empty groups are filled with 0:

watch = pd.CategoricalDtype(stocks + ["S09"]) # S09 has no rows at all cat = clean.astype({"stock": watch}) w_obs = cat.pivot_table(index="date", columns="stock", values="mentions", aggfunc="sum") w_all = cat.pivot_table(index="date", columns="stock", values="mentions", aggfunc="sum", observed=False) print(list(w_obs.columns)) # S01 ... S08 print(list(w_all.columns)) # S01 ... S09 print(w_all.loc[dates[5]:dates[6], ["S07", "S09"]]) # S07 S09 # 2026-03-09 0 0 # 2026-03-10 0 0 # keeps missing as missing w_re = wide.reindex(columns=stocks + ["S09"]) print(int(w_re["S09"].isna().sum())) # 10 

observed=False also rewrote the S07 outage as zeros. If "no data" and "zero" must stay different, pivot on the observed keys and reindex to the full watchlist afterwards.

Two side-by-side heatmaps of simulated daily mentions, 10 dates by 9 placeholder stocks. Left, pivot_table plus reindex: S07 on 03-09 and 03-10 and the whole S09 column are grey cells labelled NaN. Right, categorical stock with observed=False: the same cells show a red 0, so the outage and the stock with no rows look like zero-mention days.

Q7. My duplicates are revisions of the same number. Which one wins?

Then don't aggregate at all. Sort by load time, keep the last row per key, and the plain pivot works:

snap = pd.DataFrame({ "date": ["2026-03-02"] * 3 + ["2026-03-03"] * 2, "stock": ["S01", "S01", "S02", "S01", "S02"], "ingested_at": pd.to_datetime(["2026-03-02 18:00", "2026-03-03 09:00", "2026-03-02 18:00", "2026-03-03 18:00", "2026-03-03 18:00"]), "mentions": [40, 43, 18, 37, 21], }) latest = (snap.sort_values("ingested_at") .drop_duplicates(subset=["date", "stock"], keep="last")) print(latest.pivot(index="date", columns="stock", values="mentions")) # stock S01 S02 # date # 2026-03-02 43 18 # 2026-03-03 37 21 

Summing here would report 83 for S01 on 03-02, a number that never existed. Using pivot after deduplication is a good habit: if a duplicate slips through later, you get the ValueError instead of a silent sum.

Q8. How do I pivot several value columns at group level?

Pass a list to values; you get a two-level column index you can slice with xs. Here neg is a simulated count of negative-tone mentions, roughly the same layout as a board-level heat chart with volume and tone side by side:

clean2 = clean.assign(neg=lambda d: (d["mentions"] * 0.2).round().astype(int)) board_wide = clean2.pivot_table(index="date", columns="board", values=["mentions", "neg"], aggfunc="sum") print(board_wide.head(3)) # mentions neg # board board_A board_B board_A board_B # date # 2026-03-02 143 76 28 15 # 2026-03-03 136 130 27 26 # 2026-03-04 140 119 27 24 print(board_wide.xs("board_A", axis=1, level="board").head(2)) # mentions neg # 2026-03-02 143 28 # 2026-03-03 136 27 

Q9. Can I wrap all of this in one reusable function?

Yes. This helper refuses silent fixes: it drops exact copies on a row key, sums, checks totals, and reindexes to a watchlist without inventing zeros.

def pivot_counts(df, index, columns, values, row_key, watchlist=None, keep="last"): """Long count table -> wide table. NaN means 'no rows arrived'.""" n_before = len(df) df = df.drop_duplicates(subset=row_key, keep=keep) dropped = n_before - len(df) wide = df.pivot_table(index=index, columns=columns, values=values, aggfunc="sum", observed=True) # totals must survive the reshape assert np.isclose(wide.sum().sum(), df[values].sum()), "total changed" if watchlist is not None: extra = sorted(set(wide.columns) - set(watchlist)) if extra: print("not on watchlist:", extra) wide = wide.reindex(columns=list(watchlist)) report = { "rows_in": n_before, "copies_dropped": dropped, "missing_cells": int(wide.isna().sum().sum()), "all_missing_columns": wide.columns[wide.isna().all()].tolist(), } return wide, report wide_h, report = pivot_counts(long, index="date", columns="stock", values="mentions", row_key=["date", "stock", "source"], watchlist=stocks + ["S09"]) print(report) # {'rows_in': 238, 'copies_dropped': 38, 'missing_cells': 12, # 'all_missing_columns': ['S09']} print(wide_h.drop(columns="S09").equals(wide)) # True 

The report makes the outage (2 cells) and the empty watchlist column (10 cells) visible instead of folding them into zeros.

To redraw the first chart on your own data:

import matplotlib.pyplot as plt totals = pd.DataFrame({ "default mean": wide_mean.sum(), "sum, raw rows": wide_sum_raw.sum(), "sum, deduplicated": wide.sum(), }) ax = totals.plot.bar(figsize=(10, 5), width=0.8, color=["#9aa5b1", "#d9534f", "#2b7bb9"], rot=0) for c in ax.containers: ax.bar_label(c, fmt="%.0f", fontsize=7) ax.set_ylabel("10-day total mentions") ax.set_title("One long table, three pivots") plt.Source:https://dev.to/dustinliang/pandas-pivot-index-contains-duplicate-entries-9-questions-answered-on-a-long-mentions-table-4pbo

Related posts