PandasHow-ToBeginner · 3 min read

How to Use Pivot in pandas: Syntax, Example, and Tips

Use DataFrame.pivot(index, columns, values) to reshape data by turning unique values from one column into new columns. Specify index for row labels, columns for new columns, and values for filling the table cells.

📐

Syntax

The pivot method reshapes a DataFrame by specifying three main parts:

index: Column to use for new row labels.
columns: Column to use for new column labels.
values: Column to fill the table cells with data.

All three arguments are column names from the original DataFrame.

python

DataFrame.pivot(index='row_label_column', columns='column_label_column', values='values_column')

💻

Example

This example shows how to reshape a simple sales data table where each row is a sale with a date, product, and amount. We pivot to get dates as rows, products as columns, and amounts as values.

python

import pandas as pd

data = {
    'Date': ['2024-01-01', '2024-01-01', '2024-01-02', '2024-01-02'],
    'Product': ['Apples', 'Oranges', 'Apples', 'Oranges'],
    'Amount': [10, 15, 20, 25]
}
df = pd.DataFrame(data)

pivoted = df.pivot(index='Date', columns='Product', values='Amount')
print(pivoted)

Output

Product Apples Oranges Date 2024-01-01 10 15 2024-01-02 20 25

⚠️

Common Pitfalls

One common mistake is having duplicate entries for the same index and columns pair, which causes pivot to raise an error. Use pivot_table with an aggregation function if duplicates exist.

Also, ensure the columns you specify exist in the DataFrame to avoid key errors.

python

import pandas as pd

data = {
    'Date': ['2024-01-01', '2024-01-01', '2024-01-01'],
    'Product': ['Apples', 'Apples', 'Oranges'],
    'Amount': [10, 12, 15]
}
df = pd.DataFrame(data)

# This will raise an error because of duplicate index-column pairs
try:
    df.pivot(index='Date', columns='Product', values='Amount')
except ValueError as e:
    print(f'Error: {e}')

# Correct way: use pivot_table with aggregation
pivot_table = df.pivot_table(index='Date', columns='Product', values='Amount', aggfunc='mean')
print(pivot_table)

Output

Error: Index contains duplicate entries, cannot reshape Product Apples Oranges Date 2024-01-01 11.0 15.0

📊

Quick Reference

Parameter	Description
index	Column to use for new row labels
columns	Column to use for new column labels
values	Column to fill the table cells
pivot	Use when no duplicate index-column pairs exist
pivot_table	Use with aggregation if duplicates exist

✅

Key Takeaways

Use DataFrame.pivot(index, columns, values) to reshape data by turning unique column values into new columns.

Ensure no duplicate pairs of index and columns exist; otherwise, pivot will raise an error.

Use pivot_table with an aggregation function to handle duplicates safely.

Specify existing column names correctly to avoid key errors.

Pivoting helps convert long data format into a wide format for easier analysis.