Reshaping

Melt Wide to Long

Unpivot wide records (id + attribute columns) into long (id, variable, value) triples — one row per attribute per original record. With pandas, pd.melt does this in one call. Melt is the inverse of pivot.

By hand

With pandas

pd.melt(df, id_vars='id', value_vars=['a', 'b']) unpivots both attribute columns into variable and value rows. Pandas emits all rows for 'a' before all rows for 'b' (column-major order); the naive loop is row-major. Sorting makes both sides comparable.

naive.py 
# trace: ignore records
records = [
    {'id': 'p', 'a': 10, 'b': 20},
    {'id': 'q', 'a': 30, 'b': 40},
]
attrs = ['a', 'b']
long = []
for r in records:
    for attr in attrs:
        long.append((r['id'], attr, r[attr]))
print('RESULT:', long)
library.py 
import pandas as pd
from dalib.display import set_display
set_display()

records = [
    {'id': 'p', 'a': 10, 'b': 20},
    {'id': 'q', 'a': 30, 'b': 40},
]
df = pd.DataFrame(records)
melted = pd.melt(df, id_vars='id', value_vars=['a', 'b'])
result = list(zip(
    melted['id'].tolist(),
    melted['variable'].tolist(),
    melted['value'].tolist(),
))
print('columns:', melted.columns.tolist())
print('id:', melted['id'].tolist())
print('variable:', melted['variable'].tolist())
print('value:', melted['value'].tolist())
print('RESULT:', sorted(result))

columns: ['id', 'variable', 'value']
id: ['p', 'q', 'p', 'q']
variable: ['a', 'a', 'b', 'b']
value: [10, 30, 20, 40]
RESULT: [('p', 'a', 10), ('p', 'b', 20), ('q', 'a', 30), ('q', 'b', 40)]

Implementation notes

  • pd.melt stacks value_vars in column-major order: all rows for 'a' come before all rows for 'b'. The naive loop is row-major (all attributes of 'p' before all attributes of 'q'). Both represent the same data; sort to compare.
  • Omit value_vars to melt every column not in id_vars.
  • The generated column names default to 'variable' and 'value'; rename them with var_name='attr' and value_name='score'.
  • Cross-reference: pivot-table-simple (this chapter) for the inverse long-to-wide direction; flatten-nested (python-data-basics) for pure-Python unpivoting.