Pandas pivot(): Reshape Unique Combinations
Use pivot() to reshape records into a matrix without aggregation. Check that row-column combinations are unique, choose the value fields and distinguish absent records from unknown values.
Reshape records without aggregation 🔝
pivot() moves values into a matrix using index and column keys. It does not add, average or deduplicate records. Each index-column combination must occur at most once, even if repeated rows have identical values. Use pivot_table() when the report needs an explicit aggregation. columns is required; index defaults to the existing index and values defaults to the remaining fields. Use keyword arguments.
Create the five-row sales example 🔝
This synthetic table contains one record per product-store combination. It is suitable for demonstrating pivot; a transaction extract with repeated product-store sales would need a different key or an aggregation. Compare the schema before using a file from the sample DataFrames page.
import pandas as pd
import numpy as np
def sales_data():
return pd.DataFrame({"sale_id": [1, 2, 4, 6, 7], "c_id": [2, 2, 4, 3, 2], "p_id": [3, 4, 2, 3, 2], "product": ["Monitor", "CPU", "RAM", "Monitor", "RAM"], "qty": [2, 1, 2, 2, 3], "store": ["ABC", "DEF", "DEF", "DEF", "ABC"]})
sales = sales_data()
print(sales)
assert not sales.duplicated(["product", "store"]).any()Expected output
sale_id c_id p_id product qty store
0 1 2 3 Monitor 2 ABC
1 2 2 4 CPU 1 DEF
2 4 4 2 RAM 2 DEF
3 6 3 3 Monitor 2 DEF
4 7 2 2 RAM 3 ABCOmit values to reshape all remaining fields 🔝
The top-level pd.pivot() and DataFrame.pivot() forms can both be used. With values omitted, sale_id, c_id, p_id and qty become the outer column level; store becomes the inner level. These are moved values, not totals.
sales = sales_data()
wide = pd.pivot(sales, index=["product"], columns=["store"])
print(wide)
print("Column levels:", wide.columns.nlevels)
assert wide.columns.nlevels == 2
assert wide.loc["RAM", ("qty", "ABC")] == 3Expected output
sale_id c_id p_id qty
store ABC DEF ABC DEF ABC DEF ABC DEF
product
CPU NaN 2.0 NaN 2.0 NaN 4.0 NaN 1.0
Monitor 1.0 6.0 2.0 3.0 3.0 3.0 2.0 2.0
RAM 7.0 4.0 2.0 4.0 2.0 2.0 3.0 2.0
Column levels: 2Reshape quantities into a product-store matrix 🔝
Specify values="qty" to keep only the quantity measure. CPU has no ABC observation in this sample, so that cell is missing. The recorded quantities still total ten after reshaping.
sales = sales_data()
wide = sales.pivot(index="product", columns="store", values="qty")
print(wide)
assert wide.shape == (3, 2)
assert wide.loc["Monitor", "ABC"] == 2
assert pd.isna(wide.loc["CPU", "ABC"])
assert wide.sum().sum() == sales["qty"].sum() == 10Expected output
store ABC DEF
product
CPU NaN 1.0
Monitor 2.0 2.0
RAM 3.0 2.0Select multiple value columns 🔝
A list of value columns creates hierarchical columns. Select the outer measure level before working with one measure. IDs can be shown for reference but should not be summed as quantities.
sales = sales_data()
wide = sales.pivot(index="product", columns="store", values=["qty", "sale_id"])
print(wide)
print("Quantity matrix:")
print(wide["qty"])
assert wide.loc["RAM", ("sale_id", "ABC")] == 7Expected output
qty sale_id
store ABC DEF ABC DEF
product
CPU NaN 1.0 NaN 2.0
Monitor 2.0 2.0 1.0 6.0
RAM 3.0 2.0 7.0 4.0
Quantity matrix:
store ABC DEF
product
CPU NaN 1.0
Monitor 2.0 2.0
RAM 3.0 2.0Use the existing index when index is omitted 🔝
First set the intended row labels. Omitting index then uses those labels. Duplicate labels alone are allowed if each label-store combination is unique.
sales = sales_data()
indexed = sales.set_index("product")
wide = indexed.pivot(columns="store", values="qty")
print(wide)
assert wide.equals(sales.pivot(index="product", columns="store", values="qty"))Expected output
store ABC DEF
product
CPU NaN 1.0
Monitor 2.0 2.0
RAM 3.0 2.0Identify repeated combinations before pivoting 🔝
Use duplicated(..., keep=False) to see every involved record. Do not remove legitimate transactions to make pivot succeed. A duplicate combination raises ValueError; inspect the records and choose the intended report rule.
sales = sales_data()
extra = pd.DataFrame({"sale_id": [8], "c_id": [3], "p_id": [2], "product": ["RAM"], "qty": [4], "store": ["ABC"]})
transactions = pd.concat([sales, extra], ignore_index=True)
conflicts = transactions.loc[transactions.duplicated(["product", "store"], keep=False)]
print(conflicts)
try:
transactions.pivot(index="product", columns="store", values="qty")
except ValueError:
print("Repeated product-store combinations require a reporting decision.")
assert conflicts["sale_id"].tolist() == [7, 8]Expected output
sale_id c_id p_id product qty store
4 7 2 2 RAM 3 ABC
5 8 3 2 RAM 4 ABC
Repeated product-store combinations require a reporting decision.Choose pivot_table() when transactions should be summed 🔝
If the business question is total units per product-store pair, aggregate with sum. Another valid approach is groupby().sum() followed by pivot on the unique summary keys. Do not rely on pivot_table’s default aggregation.
aggregated = transactions.pivot_table(index="product", columns="store", values="qty", aggfunc="sum", observed=True)
summary = transactions.groupby(["product", "store"], as_index=False)["qty"].sum()
reshaped = summary.pivot(index="product", columns="store", values="qty")
print(aggregated)
assert aggregated.equals(reshaped)
assert aggregated.loc["RAM", "ABC"] == 7
assert aggregated.sum().sum() == 14Expected output
store ABC DEF
product
CPU NaN 1.0
Monitor 2.0 2.0
RAM 7.0 2.0Distinguish an absent combination from an unknown value 🔝
Missing cells can represent no source record or a source record with an unknown quantity. Keep an observation matrix alongside the value matrix if this difference matters. Filling both with zero would hide unknown quantities.
records = pd.DataFrame({"product": ["Pen", "Pen", "Book"], "store": ["A", "B", "A"], "qty": [2, None, 5]})
values = records.pivot(index="product", columns="store", values="qty")
present = records.assign(observed=True).pivot(index="product", columns="store", values="observed").notna()
print("Values:")
print(values)
print("Source record present:")
print(present)
assert pd.isna(values.loc["Pen", "B"]) and present.loc["Pen", "B"]
assert pd.isna(values.loc["Book", "B"]) and not present.loc["Book", "B"]Expected output
Values:
store A B
product
Book 5.0 NaN
Pen 2.0 NaN
Source record present:
store A B
product
Book True False
Pen True TrueBuild a fixed-layout daily revenue matrix 🔝
The synthetic input has one known daily revenue per date-product combination. Verify keys and measures before reshaping. The report’s rule is that absent combinations mean no recorded revenue within this complete extract; unknown amounts must be reviewed separately.
daily = pd.DataFrame({"date": ["2026-09-01", "2026-09-01", "2026-09-02", "2026-09-03"], "product": ["Pen", "Book", "Pen", "Book"], "revenue": [10, 30, 15, 20]})
daily["date"] = pd.to_datetime(daily["date"], format="%Y-%m-%d")
assert not daily.duplicated(["date", "product"]).any()
assert daily[["date", "product", "revenue"]].notna().all().all()
assert daily["revenue"].ge(0).all()
matrix = daily.pivot(index="date", columns="product", values="revenue")
dates = pd.date_range("2026-09-01", "2026-09-03", freq="D")
report = matrix.reindex(index=dates, columns=["Pen", "Book"]).fillna(0)
report.index.name = "date"
report.columns.name = "product"
print(report)
assert report.sum().sum() == daily["revenue"].sum() == 75
assert report.loc[pd.Timestamp("2026-09-02"), "Book"] == 0Expected output
product Pen Book
date
2026-09-01 10.0 30.0
2026-09-02 15.0 0.0
2026-09-03 0.0 20.0Reconcile and export the matrix 🔝
Use an explicit row and column order for a repeatable report. reset_index() moves date labels into a regular column before CSV export. For plots, follow bar charts; for spreadsheet export use to_excel().
print("Revenue by product:")
print(report.sum())
print("Revenue by date:")
print(report.sum(axis=1))
export = report.reset_index()
print(export.to_csv(index=False).rstrip())
assert report["Pen"].sum() == 25
assert report["Book"].sum() == 50
# Optional Colab export:
# export.to_csv("daily_product_revenue.csv", index=False)Expected output
Revenue by product:
product
Pen 25.0
Book 50.0
dtype: float64
Revenue by date:
date
2026-09-01 40.0
2026-09-02 15.0
2026-09-03 20.0
Freq: D, dtype: float64
date,Pen,Book
2026-09-01,10.0,30.0
2026-09-02,15.0,0.0
2026-09-03,0.0,20.0Common questions 🔝
Why does pivot raise ValueError? More than one record has the same index-column pair. Can I pass aggfunc? Use pivot_table() for aggregation. Why do columns have two levels? Multiple value fields were selected or values was omitted. Why do integers display as floats? Missing cells can require a dtype that supports missing values. Should every missing cell become zero? Only if the source and reporting rule support that interpretation. Can I use several index or column fields? Yes; pass lists and check uniqueness across the combined key. How do I return to long form? Use melt() for a suitable flat matrix and define how to handle missing combinations. For record selection, see str.contains(); for duplicate review, see duplicated().
Exercise: reshape temperature observations 🔝
Create observations for day 1/station A: 20, day 1/station B: 22 and day 2/station A: 19. Pivot days into rows and stations into columns. Keep the unobserved day 2/station B reading missing; do not turn it into zero.
Exercise solution 🔝
A missing reading is not a temperature of zero. Check the observed cells and the missing combination separately.
readings = pd.DataFrame({"day": [1, 1, 2], "station": ["A", "B", "A"], "temperature": [20, 22, 19]})
answer = readings.pivot(index="day", columns="station", values="temperature")
print(answer)
assert answer.loc[1, "A"] == 20
assert answer.loc[1, "B"] == 22
assert answer.loc[2, "A"] == 19
assert pd.isna(answer.loc[2, "B"])Expected output
station A B
day
1 20.0 22.0
2 19.0 NaNPractice in Google Colab 🔝
Open in Google Colab View on GitHub
Run the examples in order, change keys and observations, then inspect the reshaped matrix and duplicate checks. Save your own copy to keep edits. All sample tables are included in the notebook.
Continue with the Data Cleaning hub, where(), loc, at, NumPy where(), fillna() and iloc.
Reference: Pandas DataFrame.pivot documentation.
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Subscribe to our YouTube Channel here
Python Video Tutorials