pandas / Intermediate: grouping, joins and temporal quality / Lesson 9

Concat, reshape, pivot and melt

Preserve observation identity while combining axes or changing between wide and long representations.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Distinguish concatenation from key-based merging.
Explain pivot uniqueness and explicit aggregation during reshaping.

Stacking rows is not matching entities

concat combines objects along an axis and aligns the other axis under its options. It does not automatically match business entities as a merge would. Stacking monthly extracts can preserve repeated index labels, and ignore_index creates a new positional axis rather than validating item identity. Along columns, index alignment can introduce missing cells if inputs refer to different rows. Define whether the operation appends observations or joins attributes, then choose the appropriate API. Keep source or partition provenance when overlapping extracts can contain the same events. A clean sequential index cannot prove the combined business records are unique.

Reshaping changes where identity is represented

A wide table can hold one item row with separate January and February quantity columns. Melting it produces one row per item-month observation, with month becoming a value in a column. The row count increases without creating new source measurements; the representation changed. Choose id_vars and value_vars explicitly so identifiers or unrelated numeric fields are not accidentally melted as measures. Preserve units in the measure contract, especially if source columns represent different units or concepts. A single value column is meaningful only when the melted fields are comparable or carry a separate variable/unit interpretation.

Check identity pairs and a conserved total

The illustrative program melts two item rows and two month columns into four observations, then pivots them back using the unique item-month pairs. The total quantity remains eighteen, and I2/February remains six. These invariants describe the declared comparable units. They do not prove every reshape preserves totals: dropping missing values, aggregating duplicates or selecting different value columns can change coverage. The expected assertions were not executed in pandas. Test the set of identity pairs as well as count and sum, because a missing observation and an extra duplicate can cancel in a superficial row-count comparison.

python Illustrative wide/long round-trip invariants

import pandas as pd

wide = pd.DataFrame({'item': ['I1', 'I2'], 'jan': [3, 4], 'feb': [5, 6]})
long = wide.melt(id_vars=['item'], value_vars=['jan', 'feb'], var_name='month', value_name='units')
restored = long.pivot(index='item', columns='month', values='units')
assert len(long) == 4
assert long['units'].sum() == 18
assert restored.loc['I2', 'feb'] == 6
print(len(long), int(long['units'].sum()))

Expected output

4 18

Expected output is 4 observations and total 18 for unique comparable item-month cells; pandas was not run.

A duplicate pair requires a business decision

pivot requires a unique value for each selected index/column combination and raises an error when duplicates make that representation ambiguous. pivot_table aggregates and has an aggregation contract; silently replacing pivot with pivot_table can conceal a grain defect, especially if the default mean is not the intended measure. Choose sum, mean or another operation explicitly only after deciding what duplicated observations mean. A many-measure pivot can create hierarchical columns, changing downstream selection and export schema. Name and validate that structure rather than flattening labels with an ambiguous separator that can collide with real field names.

Model observation expansion without an engine

The standard-library list comprehension expands the same item-month observations and checks four records and total eighteen. It does not emulate pandas index names, categorical handling, sorting or missing-cell construction. A robust reshape review also tests a missing month, a duplicated item-month pair and a column whose name resembles an identifier. This lesson uses in-memory synthetic data only and writes no external table. pandas concat, melt, pivot and pivot_table remain unexecuted examples or documented concepts. Preserve those evidence boundaries while using the plain-record result to state the intended observation-level invariant independently.

python Offline item-month observation expansion

wide = [{'item': 'I1', 'jan': 3, 'feb': 5}, {'item': 'I2', 'jan': 4, 'feb': 6}]
long = [{'item': row['item'], 'month': month, 'units': row[month]} for row in wide for month in ['jan', 'feb']]
assert len(long) == 4
assert sum(row['units'] for row in long) == 18
print(len(long), sum(row['units'] for row in long))

Expected output

4 18

The model checks source observation count and sum only, independently of pandas reshape mechanics.

Common errors and fixes

concat is expected to match descriptions by item key automatically.

Why it happens: Axis concatenation was confused with a relational merge.
How to fix it: State the combination contract and use explicit key-based merging when needed.
Prevent it next time: Check identities and axes before combining objects.

Index contains duplicate entries, cannot reshape

Why it happens: The selected pivot index/column pair identifies multiple source values.
How to fix it: Investigate grain and choose rejection or an explicit justified aggregation.
Prevent it next time: Assert pair uniqueness before pivot.

Replacing pivot with pivot_table silently changes totals.

Why it happens: An implicit aggregation answered a different business question.
How to fix it: Choose the aggregation from measure semantics and reconcile coverage.
Prevent it next time: Test conservation invariants only where the chosen reshape should preserve them.

Check your understanding

1. Why can melting two rows into four rows be correct without duplicating measurements?

Show worked solution

Each wide row contains two separate month measurements. Long form represents each item-month observation as its own row. The appropriate uniqueness key therefore changes from item to item plus month. Verify those pairs and the selected measure columns instead of interpreting every row-count increase as accidental duplication.

2. Should a pivot duplicate error always be repaired by taking the mean?