pivot(): Long to Wide

Data usually arrives in long format: one row per (season, team) fact. That is great for storing and grouping, but hard to read — to compare MI, CSK and RCB season by season, you want one row per season and one column per team. Turning row values into column headers is called pivoting (in notebook 11: "Pivot = data ko rotate / reshape karna").

import pandas as pd

long = pd.DataFrame({
    "season": [2022, 2022, 2022, 2023, 2023, 2023, 2024, 2024, 2024],
    "team":   ["MI", "CSK", "RCB", "MI", "CSK", "RCB", "MI", "CSK", "RCB"],
    "wins":   [4, 4, 8, 8, 10, 7, 4, 7, 7],
})
print(long)

   season team  wins
0    2022   MI     4
1    2022  CSK     4
2    2022  RCB     8
3    2023   MI     8
4    2023  CSK    10
5    2023  RCB     7
6    2024   MI     4
7    2024  CSK     7
8    2024  RCB     7

Definition

pivot() reshapes a DataFrame from long to wide format: the unique values of the index column become the row labels, the unique values of the columns column become the column labels, and the values column fills the cells. It only rearranges data — it does no aggregation, so every (index, columns) pair must be unique.

• Long format — one row per observation; a "key" column (team) says what the number in wins belongs to. Easy to filter/group.
• Wide format — one row per entity (season), one column per category (team). Easy to read and compare, like an Excel sheet.
• No aggregation — pivot only moves values. If two rows want to land in the same cell, it does not know which to keep and raises an error.

Wide vs long diagram

 LONG (9 rows)                       WIDE (3 rows)
 season team wins                    team    CSK  MI  RCB
 2022   MI    4    ---pivot--->      season
 2022   CSK   4    index=season      2022      4   4    8
 2022   RCB   8    columns=team      2023     10   8    7
 2023   MI    8    values=wins       2024      7   4    7
 ...
                   <---melt-----     (see the melt topic)

Syntax

df.pivot(index=None, columns, values=None)
pd.pivot(df, index=None, columns, values=None)    # same thing as a function

Example — season × team wins

w = long.pivot(index="season", columns="team", values="wins")
print(w)

team    CSK  MI  RCB
season
2022      4   4    8
2023     10   8    7
2024      7   4    7

How it works: pandas finds the unique seasons (rows) and unique teams (columns, sorted), then drops each wins value into its (season, team) cell. The word team above the headers is the columns' name and season is the index name — they remember where the labels came from. Swap index and columns to transpose the view (one row per team).

Example — leaving out values

long2 = long.assign(points=long["wins"] * 2)
print(long2.pivot(index="season", columns="team"))

       wins        points
team    CSK MI RCB    CSK  MI RCB
season
2022      4  4   8      8   8  16
2023     10  8   7     20  16  14
2024      7  4   7     14   8  14

How it works: without values, every remaining column is pivoted, giving two-level column labels (("wins", "CSK"), ("points", "MI")…). Select one block with result["wins"].

Example — the duplicate-entries error

dup = pd.DataFrame({
    "player": ["Kohli", "Kohli", "Kohli", "Rohit", "Rohit"],
    "venue":  ["Chinnaswamy", "Chinnaswamy", "Wankhede", "Wankhede", "Chinnaswamy"],
    "runs":   [82, 47, 61, 55, 30],
})
dup.pivot(index="player", columns="venue", values="runs")

ValueError: Index contains duplicate entries, cannot reshape

How it works: Kohli played twice at Chinnaswamy (82 and 47). Both rows want the single cell (Kohli, Chinnaswamy), and pivot refuses to guess. Find the culprits with dup.duplicated(subset=["player", "venue"], keep=False). The fix is pivot_table(), which combines duplicates with an aggregate (sum, mean…) — next topic.

Example — missing combinations become NaN

part = pd.DataFrame({"season": [2023, 2023, 2024], "team": ["MI", "CSK", "MI"], "wins": [8, 10, 4]})
print(part.pivot(index="season", columns="team", values="wins"))

team     CSK   MI
season
2023    10.0  8.0
2024     NaN  4.0

There is no row for (2024, CSK), so that cell is NaN — and because NaN is a float, the whole column turns into floats (10.0). Use .fillna(0) if a missing combination really means zero.

Getting back a flat table

The pivot result has season as its index and a columns name team. To export or merge it, flatten it:

w2 = w.reset_index()
w2.columns.name = None
print(w2)

   season  CSK  MI  RCB
0    2022    4   4    8
1    2023   10   8    7
2    2024    7   4    7

Tip: pivoting is also the classic step before plotting — notebook 11 does pd.pivot(flight, index="year", columns="month", values="passengers").plot() to draw one line per month. (Plotting is not available in the browser playground.)

Common mistakes

• ❌ Using pivot on data with repeated (index, columns) pairs → ValueError: Index contains duplicate entries. ✅ Use pivot_table(..., aggfunc="sum") or remove duplicates first.
• ❌ Forgetting index= — every original row becomes its own row and the table fills with NaN. ✅ Always pass index, columns and values.
• ❌ Passing positional arguments (df.pivot("season", "team", "wins")). ✅ Since pandas 2 the arguments are keyword-only: pivot(index=..., columns=..., values=...).
• ❌ Being surprised that ints became floats. ✅ Missing combinations insert NaN (a float); fillna(0).astype(int) if appropriate.

Interview tip

Q: Why does pivot() raise "Index contains duplicate entries"? A: pivot only reshapes and never aggregates, so each (index, columns) pair must appear once. When a pair repeats, it cannot decide which value to put in the cell. Use pivot_table() with an aggfunc, or deduplicate first.