How to Pivot Data in Python Using pandas

To pivot data in Python, use the pandas library's pivot() method, which reshapes data by turning unique values from one column into new columns. You specify the index, columns, and values to reorganize your data into a more readable format.

Syntax

The pivot() method in pandas has this syntax:

• index: Column to use as new row labels.
• columns: Column to use to make new columns.
• values: Column to fill the new table's values.

This method reshapes the data by spreading unique values from the columns parameter into new columns, indexed by index, filled with values.

python

DataFrame.pivot(index=None, columns=None, values=None)

Example

This example shows how to pivot a simple sales data table to see sales by product and region.

python

import pandas as pd

data = {
    'Date': ['2024-01-01', '2024-01-01', '2024-01-02', '2024-01-02'],
    'Product': ['Apples', 'Oranges', 'Apples', 'Oranges'],
    'Region': ['North', 'North', 'South', 'South'],
    'Sales': [100, 150, 200, 250]
}

df = pd.DataFrame(data)

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

Output

Product    Apples  Oranges
Date                      
2024-01-01    100      150
2024-01-02    200      250

Common Pitfalls

One common mistake is trying to pivot data where the combination of index and columns is not unique. This causes a ValueError because pandas doesn't know how to fill multiple values in one cell.

To fix this, use pivot_table() with an aggregation function like sum or mean to combine duplicates.

python

import pandas as pd

data = {
    'Date': ['2024-01-01', '2024-01-01', '2024-01-01'],
    'Product': ['Apples', 'Apples', 'Oranges'],
    'Sales': [100, 120, 150]
}

df = pd.DataFrame(data)

# This will raise an error because of duplicate index-column pairs
try:
    df.pivot(index='Date', columns='Product', values='Sales')
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='Sales', aggfunc='sum')
print(pivot_table)

Output

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

Quick Reference

Remember these tips when pivoting data:

• Use pivot() when each index-column pair is unique.
• Use pivot_table() with aggfunc to handle duplicates.
• index sets the new rows, columns sets new columns, and values fills the table.
• Pivoting helps transform long data into wide format for easier analysis.

Key Takeaways

Use pandas' pivot() to reshape data by setting index, columns, and values.

Ensure the index and columns combination is unique to avoid errors.

Use pivot_table() with aggregation to handle duplicate entries.

Pivoting transforms data from long to wide format for clearer insights.