Reshape Tables with Pivot, Melt, and Explicit Aggregation

Module 4: Combining and Summarizing Data · Lecture 16 of 32

Long tables are convenient for grouping and plotting; wide tables are convenient for comparison. Move between them while preserving the grain and choosing explicitly what happens when a cell has several observations.

What you will learn

Distinguish pivot from pivot_table.
Use melt to return a summary to tidy long form.
Choose when an absent combination can be displayed as zero.

Understand the problem

Long and wide are representations

A long channel-by-month table contains one row per month-channel combination with a net amount. A wide table puts channels in columns and months in rows. Neither shape is automatically more correct. The representation should serve the next operation while retaining the keys needed to interpret each value.

Pivot requires uniqueness

pivot rearranges values and expects a unique observation for each index-column pair. Raw orders contain many orders for the same month and channel, so direct pivoting is ambiguous. pivot_table performs an explicit aggregation such as sum first. Its default aggregation may not match your business metric, so always name aggfunc.

Missing cells are a policy

A month-channel combination absent from a complete transaction population can reasonably display zero revenue. If an export is incomplete, filling the cell with zero hides missing evidence. Reindexing to expected periods is useful only after coverage is established. Keep the distinction between zero activity and unavailable data visible in reports.

Melt restores variable names as data

melt turns channel columns into channel values and moves their amounts into one value column. This tidy form is easier to join to channel metadata or feed into a plotting function. Preserve identifiers explicitly with id_vars, and give the resulting columns meaningful names. Check totals across a reshape just as you would after a join.

Run the worked example

Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 16 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.

from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT

orders = load_orders()
local = orders['ordered_at'].dt.tz_convert('Asia/Kolkata')
table = orders.assign(month=local.dt.strftime('%Y-%m'))
wide = table.pivot_table(index='month', columns='channel', values='net_paise',
                         aggfunc='sum', fill_value=0, observed=True)
long = wide.reset_index().melt(id_vars='month', var_name='channel', value_name='net_paise')
print(wide.to_string())
assert int(wide.to_numpy().sum()) == int(orders['net_paise'].sum())
assert int(long['net_paise'].sum()) == int(orders['net_paise'].sum())
assert not long.duplicated(['month', 'channel']).any()

Verified output

channel    email  organic     paid  unknown
month                                      
2026-01  2891410  2626730  2986750   159800
2026-02  1761450  2749500  2238840        0
2026-03  3392420  2934800  2370720        0

Walk through the result

The month is derived in the declared business timezone before reshaping. Each wide cell sums order-level net revenue. fill_value=0 is defensible for this known synthetic reporting window, but it would need coverage evidence with a real source. The long result has one row per displayed month-channel combination, including explicit zero cells created by the wide representation.

Common mistakes and diagnosis

Do not use pivot_table's default mean when the report promises total revenue. Flattening a MultiIndex without preserving column meaning can make a result hard to interpret. Do not sum every numeric column after a melt if some columns are ratios or denominators. A reshape does not remove the need for the original metric contract.

Practice assignment

Create a month-by-status table of order counts using aggfunc='size'. Melt it back to long form and verify that counts reconcile to all accepted orders. Explain why a mean of units would answer a different question.

Worked solution and explanation

Add the following code below the worked example, or run the matching file in the solutions folder. Compare the result with the business definition before trusting a passing assertion.

counts = table.pivot_table(index='month', columns='status', aggfunc='size', fill_value=0, observed=True)
tidy_counts = counts.reset_index().melt(id_vars='month', var_name='status', value_name='orders')
assert int(tidy_counts['orders'].sum()) == len(orders)
assert not tidy_counts.duplicated(['month', 'status']).any()

Check your understanding

When should pivot fail instead of aggregating automatically?

Answer guide

When your intended input grain promises one observation per index-column pair. A duplicate in that setting is evidence of a broken contract and should be investigated. If multiple observations are expected, choose pivot_table with an explicit aggregation and document what the resulting cell represents.

Reference and next step

pandas reshaping guide

Download the complete course kit: datasets, all examples, solutions, and capstone.

Return to the full Data Analysis with Python and pandas curriculum.

Course index · Previous · Next