Reshaping Data

Difficulty: Intermediate

Reshaping data means transforming its layout between wide format and long format. In wide format, each variable or category gets its own column (e.g., separate columns for Math, Science, English scores). In long format, there is a single column for the variable name and another for the value (e.g., a 'Subject' column and a 'Score' column, with one row per student-subject combination). Different analyses and visualization tools require different formats, so being able to convert between them is a core data wrangling skill.

The melt() function converts wide format to long format. It takes a DataFrame with many columns and 'unpivots' it into fewer columns. You specify id_vars (columns to keep as-is), value_vars (columns to unpivot), and optionally var_name and value_name to control the names of the new columns. Melt is extremely useful when you receive data where categories are spread across columns (e.g., sales by month with a column for each month) and you need to normalize it for analysis or storage in a relational database.

The pivot() function does the reverse: it converts long format to wide format. You specify the index (row identifiers), columns (values that become column headers), and values (the data to fill the cells). Pivot requires that the combination of index and columns is unique; if there are duplicates, you must use pivot_table() instead. pivot_table() aggregates duplicate values using a function like mean, sum, or count, making it much more robust and similar to Excel pivot tables.

The stack() and unstack() methods work with MultiIndex DataFrames. stack() moves the innermost column level into the row index (wide to long), while unstack() moves the innermost row index level into columns (long to wide). These are most commonly used after a groupby() operation that produces a MultiIndex result, or when you need to restructure hierarchical data. For example, after grouping sales by both region and quarter, you might unstack the quarter level to get a wide table with one column per quarter.

Choosing the right format depends on your goal. Long format is preferred for most statistical analyses, machine learning pipelines, and libraries like seaborn that expect tidy data (one observation per row). Wide format is better for human readability, side-by-side comparison, and certain summary reports. A common workflow is to receive data in wide format, melt it to long format for analysis and visualization, perform computations, and then pivot it back to wide format for a final presentation or export.

Code examples

Melt: Wide to Long Format

import pandas as pd

df = pd.DataFrame({
    'Student': ['Alice', 'Bob'],
    'Math': [90, 85],
    'Science': [88, 92],
    'English': [95, 78]
})

print("Wide format:")
print(df)

long = pd.melt(df, id_vars=['Student'], value_vars=['Math', 'Science', 'English'],
               var_name='Subject', value_name='Score')
print("\nLong format:")
print(long)

melt() unpivots the three subject columns into a single 'Subject' column and a 'Score' column. Each student now has three rows (one per subject) instead of three columns. The id_vars parameter keeps the Student column as a row identifier.

Pivot: Long to Wide Format

import pandas as pd

long_df = pd.DataFrame({
    'City': ['Delhi', 'Delhi', 'Mumbai', 'Mumbai'],
    'Quarter': ['Q1', 'Q2', 'Q1', 'Q2'],
    'Revenue': [100, 150, 200, 250]
})

print("Long format:")
print(long_df)

wide = long_df.pivot(index='City', columns='Quarter', values='Revenue')
print("\nWide format:")
print(wide)

pivot() transforms the long DataFrame so that each unique Quarter value becomes its own column. The City column becomes the index. This only works when each City-Quarter combination is unique; otherwise use pivot_table().

Pivot Table with Aggregation

import pandas as pd

df = pd.DataFrame({
    'Region': ['East', 'East', 'West', 'West', 'East', 'West'],
    'Product': ['A', 'B', 'A', 'B', 'A', 'A'],
    'Sales': [100, 200, 150, 300, 120, 180]
})

print("Original data:")
print(df)

table = pd.pivot_table(df, values='Sales', index='Region',
                        columns='Product', aggfunc='sum')
print("\nPivot table (sum):")
print(table)

Unlike pivot(), pivot_table() handles duplicate combinations by aggregating them. East-A appears twice (100+120=220) and West-A appears twice (150+180=330). The aggfunc parameter supports 'sum', 'mean', 'count', 'min', 'max', and custom functions.

Stack and Unstack

import pandas as pd

df = pd.DataFrame({
    'City': ['Delhi', 'Delhi', 'Mumbai', 'Mumbai'],
    'Year': [2023, 2024, 2023, 2024],
    'Population': [20, 21, 22, 23]
})

# Set MultiIndex
df_indexed = df.set_index(['City', 'Year'])
print("MultiIndex DataFrame:")
print(df_indexed)

# Unstack: move Year from rows to columns
print("\nUnstacked (wide):")
print(df_indexed.unstack())

# Stack it back: move columns back to rows
print("\nStacked (long):")
print(df_indexed.unstack().stack())

unstack() takes the innermost row index level (Year) and converts it to column headers, creating a wide format. stack() reverses the operation, moving column levels back into the row index. These methods are useful for reshaping MultiIndex results from groupby operations.

Key points

Use melt() when you need to normalize wide data for analysis or when a plotting library requires long (tidy) format. Most seaborn plotting functions expect long format.
pivot() requires unique index-column pairs. If your data has duplicates, use pivot_table() with an appropriate aggregation function to avoid ValueError.
stack() and unstack() are most useful after groupby() operations that produce MultiIndex DataFrames. They complement reset_index() as ways to reshape hierarchical results.

Concepts covered

melt, pivot, pivot_table, stack, unstack