Skip to main content
Pandas · lesson 10 of 13

Beginner

5

Intermediate

6

Advanced

2
Open the Pandas page →
Pandas intermediate Lesson 10 of 13

Pandas Data Transformation

Reshape Pandas data with melt, pivot, stack, and explode, transform values with map, apply, and .str, and measure how much slower row-wise apply really is.

By SkillByExample · Updated

Real-World Scenario

A quarterly report arrives with one column per quarter, but the plotting code needs one row per company and quarter. Meanwhile the feature pipeline needs lists unpacked, codes mapped to names, and fields pulled out of free-text addresses. All of these are reshaping or transforming.

Wide to Long: melt

melt keeps some columns as identifiers and stacks the rest into name/value pairs.

import pandas as pd

wide = pd.DataFrame({
    "company": ["Acme", "Globex"],
    "Q1": [120, 85],
    "Q2": [135, 92],
    "Q3": [128, 88],
})

long = wide.melt(id_vars="company", var_name="quarter", value_name="revenue")
print(long)
print(long.groupby("quarter")["revenue"].sum())
$ python melt.py
  company quarter  revenue
0    Acme      Q1      120
1  Globex      Q1       85
2    Acme      Q2      135
3  Globex      Q2       92
4    Acme      Q3      128
5  Globex      Q3       88
quarter
Q1    205
Q2    227
Q3    216
Name: revenue, dtype: int64

Two rows with three quarter columns became six rows. The old column names now live in the quarter column as data, which is what lets a single groupby("quarter") total every quarter — something the wide layout would need a separate expression for per column.

Long to Wide: pivot and pivot_table

pivot is the inverse of melt, as long as every index/column pair is unique.

import pandas as pd

long = pd.DataFrame({
    "company": ["Acme", "Acme", "Globex", "Acme"],
    "quarter": ["Q1", "Q2", "Q1", "Q1"],
    "revenue": [120, 135, 85, 10],
})

try:
    long.pivot(index="company", columns="quarter", values="revenue")
except ValueError as e:
    print("ValueError:", e)

print(long.pivot_table(index="company", columns="quarter",
                       values="revenue", aggfunc="sum"))
$ python pivot.py
ValueError: Index contains duplicate entries, cannot reshape
quarter     Q1     Q2
company              
Acme     130.0  135.0
Globex    85.0    NaN

Acme has two Q1 rows (a late 10 was booked separately), so pivot has two values for one cell and refuses. pivot_table resolves duplicates with its aggfunc, giving Acme a Q1 of 130. Globex never reported Q2, so that cell is NaN.

stack and unstack

stack moves the column labels into the row index; unstack moves an index level back out.

import pandas as pd

sales = pd.DataFrame(
    [[5, 3], [2, 7]],
    index=pd.Index(["North", "South"], name="region"),
    columns=pd.Index(["Widget", "Gadget"], name="product"),
)

stacked = sales.stack()
print(stacked)
print(stacked.unstack(level="region"))
$ python stack.py
region  product
North   Widget     5
        Gadget     3
South   Widget     2
        Gadget     7
dtype: int64
region   North  South
product              
Widget       5      2
Gadget       3      7

After stack the table is a Series with a two-level (region, product) index — one value per row. Unstacking the region level instead of the product level transposes the original: the regions become columns.

Unpacking Lists with explode

When a cell holds a list, explode gives each element its own row.

import pandas as pd

students = pd.DataFrame({
    "name":   ["Alice", "Bob", "Carol"],
    "scores": [[88, 92, 76], [71, 85], []],
})

long = students.explode("scores")
print(long)
print(long.dtypes)
print(long.groupby("name")["scores"].mean())
$ python explode.py
    name scores
0  Alice     88
0  Alice     92
0  Alice     76
1    Bob     71
1    Bob     85
2  Carol    NaN
name         str
scores    object
dtype: object
name
Alice    85.333333
Bob           78.0
Carol          NaN
Name: scores, dtype: object

Alice’s three scores became three rows sharing index 0, and Carol’s empty list became a single NaN row rather than disappearing. The exploded column is still object, because it came from Python lists — and so is the result of the mean, which prints 85.333333 and 78.0 in different formats. Convert with pd.to_numeric before doing arithmetic on exploded values.

map: Substituting Values

map with a dict replaces each value by lookup — and turns anything missing from the dict into NaN.

import pandas as pd

country = pd.Series(["US", "UK", "DE", "US"])
names = {"US": "United States", "UK": "United Kingdom"}

print(country.map(names))
print(country.replace(names))
print(country.map(names).fillna(country))
$ python map.py
0     United States
1    United Kingdom
2               NaN
3     United States
dtype: str
0     United States
1    United Kingdom
2                DE
3     United States
dtype: str
0     United States
1    United Kingdom
2                DE
3     United States
dtype: str

DE wasn’t in the dict, so map quietly produced NaN. replace leaves unmatched values alone, and map(...).fillna(original) does the same explicitly. After any map, a quick isna().sum() catches codes your lookup table forgot.

apply with axis=1 Versus Vectorized Code

Row-wise apply runs a Python function per row. The vectorized version computes the same column in one operation.

import timeit
import numpy as np
import pandas as pd

rng = np.random.default_rng(0)
df = pd.DataFrame({"salary": rng.integers(30000, 150000, 100_000),
                   "country": rng.choice(["US", "UK"], 100_000)})

def tax(row):
    return row["salary"] * (0.35 if row["country"] == "US" else 0.30)

slow = df.apply(tax, axis=1)
fast = df["salary"] * np.where(df["country"] == "US", 0.35, 0.30)
print("same result:", np.allclose(slow, fast))

t_apply = min(timeit.repeat(lambda: df.apply(tax, axis=1), number=1, repeat=5))
t_vec = min(timeit.repeat(lambda: df["salary"] * np.where(df["country"] == "US", 0.35, 0.30),
                          number=1, repeat=5))
print(f"apply: {t_apply:.3f} s  vectorized: {t_vec:.4f} s  ratio: {t_apply / t_vec:.0f}x")
$ python apply_speed.py
same result: True
apply: 0.346 s  vectorized: 0.0050 s  ratio: 69x

Both produce identical numbers, but the timings — best of five runs on one machine, so yours will differ — are far apart. apply(axis=1) builds a Series for every one of the 100,000 rows and calls tax on it; np.where and the multiplication each run once over whole arrays. Across several runs on this machine the ratio stayed between about 67x and 73x — the exact figure moves, the order of magnitude does not.

Extracting Fields with .str

str.extract pulls regex groups out of text into new columns.

import pandas as pd

addresses = pd.Series([
    "123 Main St, New York, NY 10001",
    "456 Oak Ave, Chicago, IL 60601",
    "789 Pine Rd, Austin, TX",
])

parts = addresses.str.extract(r",\s*(?P<city>[^,]+),\s*(?P<state>[A-Z]{2})\s*(?P<zip>\d{5})?$")
print(parts)
print(addresses.str.contains("NY", regex=False))
$ python extract.py
       city state    zip
0  New York    NY  10001
1   Chicago    IL  60601
2    Austin    TX    NaN
0     True
1    False
2    False
dtype: bool

Named groups (?P<name>...) become column names, so one call returns a three-column DataFrame. The Austin address has no ZIP code, and because that group is optional (?), the row still matches and gets NaN for zip alone instead of losing the city and state too.

Practice

  1. In melt.py, melt the wide table and then turn it back into the original wide layout.
Solution 
import pandas as pd

wide = pd.DataFrame({"company": ["Acme", "Globex"], "Q1": [120, 85], "Q2": [135, 92]})
long = wide.melt(id_vars="company", var_name="quarter", value_name="revenue")
back = long.pivot(index="company", columns="quarter", values="revenue").reset_index()
back.columns.name = None
print(back)
$ python practice_roundtrip.py
  company   Q1   Q2
0    Acme  120  135
1  Globex   85   92

Each (company, quarter) pair is unique after a melt, so plain pivot works. Clearing columns.name removes the leftover quarter header label.

  1. In map.py, map the codes to full names and print how many codes were unmapped.
Solution 
import pandas as pd

country = pd.Series(["US", "UK", "DE", "US", "FR"])
names = {"US": "United States", "UK": "United Kingdom"}
mapped = country.map(names)
print("unmapped:", mapped.isna().sum(), sorted(country[mapped.isna()].unique()))
$ python practice_unmapped.py
unmapped: 2 ['DE', 'FR']

Listing the unmapped codes, not just counting them, tells you exactly which entries the lookup table needs.

  1. In explode.py, compute each student’s average score as a real float, with Carol’s missing average shown as NaN.
Solution 
import pandas as pd

students = pd.DataFrame({"name": ["Alice", "Bob", "Carol"],
                         "scores": [[88, 92, 76], [71, 85], []]})
long = students.explode("scores")
long["scores"] = pd.to_numeric(long["scores"])
print(long.dtypes)
print(long.groupby("name")["scores"].mean().round(1))
$ python practice_explode.py
name          str
scores    float64
dtype: object
name
Alice    85.3
Bob      78.0
Carol     NaN
Name: scores, dtype: float64

pd.to_numeric converts the exploded object column to float64, and the mean skips Carol’s NaN, leaving her average NaN.

Frequently Asked Questions

When should I use melt versus pivot in Pandas?
melt turns wide data into long data: many value columns become two columns, one holding the old column name and one holding the value. pivot does the reverse. Long data is what groupby, plotting libraries, and most modelling code expect; wide data is easier for people to read.
Why does pivot raise 'Index contains duplicate entries'?
pivot places exactly one value in each cell, so it fails when two rows share the same index and column pair. pivot_table accepts duplicates and combines them with an aggregation function such as sum or mean. Use pivot_table whenever the long data might contain repeats.
Why did Series.map() produce NaN values?
When you pass a dict to map, any value that is not a key in the dict becomes NaN. That is often a silent data loss. Check for NaN after mapping, or use replace, which leaves unmatched values unchanged.
Is DataFrame.apply with axis=1 slow?
Yes. apply with axis=1 calls a Python function once per row and builds a Series for each call, so it is typically tens to hundreds of times slower than the equivalent vectorized column expression. Express the logic with column arithmetic, np.where, or np.select when you can, and keep apply for logic that genuinely cannot be vectorized.