Pandas Pivot Tables, Reshaping, Strings and apply

By now you can filter, group, join and handle dates. This lesson rounds out your pandas toolkit with the techniques that turn a correct analysis into a presentable one. Pivot tables reshape grouped results into the matrix layouts that managers expect: cities down the side, months across the top. Melt and stack go the other way, converting wide tables to the "long" shape that plotting libraries prefer. The .str accessor cleans text at scale, categoricals save memory and control sort order, and apply handles the odd cases that no built-in covers.

We finish with a modern habit that separates tidy analysts from messy ones: writing the analysis as a method chain (a pipeline of steps) so that the whole transformation from raw data to final table reads top to bottom, can be re-run in one go, and has no confusing intermediate variables.

Worked examples

Example 1: pivot_table

Question: Orders by city (rows) and month (columns).

import pandas as pd
import numpy as np

orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
stores = pd.read_csv("stores.csv")
o = orders.merge(stores[["store_id", "city"]], on="store_id")
o["month"] = o["order_date"].dt.to_period("M").astype(str)

pt = o.pivot_table(index="city", columns="month", values="order_id", aggfunc="count", fill_value=0)
print(pt.shape)
print(pt.iloc[:4, -4:])

pt2 = o.pivot_table(index="city", values="delivery_minutes", aggfunc=["mean", "median", "count"]).round(1)
print(pt2.head(3))

totals = o.pivot_table(index="city", columns="order_status", values="order_id", aggfunc="count", margins=True, margins_name="Total")
print(totals.tail(2))

(8, 18)
month      2026-03  2026-04  2026-05  2026-06
city                                         
Ahmedabad       36       35       36       45
Bengaluru      135       99      130      115
Chennai         54       59       45       43
Delhi          122      125      133      130
                      mean           median            count
          delivery_minutes delivery_minutes delivery_minutes
city                                                        
Ahmedabad             23.9             24.0              369
Bengaluru             23.8             24.0             1212
Chennai               23.0             23.0              430
order_status  Cancelled  Delivered  Returned  Total
city                                               
Pune                 35        467        19    521
Total               370       5391       239   6000

How it works: pivot_table(index, columns, values, aggfunc) is a groupby followed by unstack, in one call. fill_value=0 replaces empty cells, aggfunc can be a list (giving multi-level columns), and margins=True appends row and column totals. It is the pandas equivalent of an Excel pivot table.

Reading the result: The matrix has 8 cities and 18 months. The grand total in the margins row is 6,000, equal to the number of orders.

Example 2: Percentages and crosstab

Question: What share of each city's orders falls in each payment method?

ct = pd.crosstab(o["city"], o["payment_method"])
print(ct.head(3))

share = pd.crosstab(o["city"], o["payment_method"], normalize="index") * 100
print(share.round(1).head(3))
print(share.sum(axis=1).round(6).unique())

overall = pd.crosstab(o["payment_method"], o["order_status"], normalize="all") * 100
print(overall.round(2))
print((pd.crosstab(o["city"], o["order_status"], normalize="columns") * 100).round(1).iloc[:2])

payment_method  COD  Card  UPI  Wallet
city                                  
Ahmedabad        66    81  215      31
Bengaluru       211   280  720      90
Chennai          76    88  251      38
payment_method   COD  Card   UPI  Wallet
city                                    
Ahmedabad       16.8  20.6  54.7     7.9
Bengaluru       16.2  21.5  55.3     6.9
Chennai         16.8  19.4  55.4     8.4
[100.]
order_status    Cancelled  Delivered  Returned
payment_method                                
COD                  1.08      14.52      0.67
Card                 1.23      18.43      0.75
UPI                  3.37      49.83      2.30
Wallet               0.48       7.07      0.27
order_status  Cancelled  Delivered  Returned
city                                        
Ahmedabad           6.5        6.5       7.1
Bengaluru          24.1       21.4      23.4

How it works: crosstab counts the combinations of two categorical columns. normalize="index" makes each row sum to 1 (so cells are shares within a city), "columns" normalises each column, and "all" makes the whole table sum to 1. Multiplying by 100 turns shares into percentages.

Reading the result: Each city's payment shares sum to 100 percent. The most used method in Ahmedabad is UPI at 54.7%.

Example 3: Wide to long with melt

Question: Turn the monthly matrix into a long table with one row per city and month.

wide = pt.reset_index()
print(wide.shape)

long = wide.melt(id_vars="city", var_name="month", value_name="orders")
print(long.shape, long.columns.tolist())
print(long.sort_values(["city", "month"]).head(3).to_string(index=False))

back = long.pivot(index="city", columns="month", values="orders")
print(back.shape, (back.values == pt.values).all())

(8, 19)
(144, 3) ['city', 'month', 'orders']
     city   month  orders
Ahmedabad 2025-01       9
Ahmedabad 2025-02       7
Ahmedabad 2025-03      10
(8, 18) True

How it works: melt unpivots: columns other than id_vars are stacked into two columns, one holding the old column name (var_name) and one holding the values (value_name). pivot reverses it. The long ("tidy") shape, where each row is one observation, is what seaborn, plotly and groupby prefer; the wide shape is what humans like to read.

Reading the result: The wide table had 18 month columns; the long table has 144 rows (8 cities × 18 months). Pivoting back restored the original values exactly.

Example 4: stack, unstack and multi-level columns

Question: Work with a table that has two header levels.

multi = o.groupby(["city", "delivery_slot"]).agg(orders=("order_id", "count"), avg=("delivery_minutes", "mean")).round(1)
wide2 = multi.unstack("delivery_slot")
print(wide2.columns.nlevels, wide2.shape)
print(wide2["orders"].head(2))

flat = wide2.copy()
flat.columns = [f"{a}_{b.split(' ')[0].lower()}" for a, b in flat.columns]
print(flat.columns.tolist()[:4])

back = wide2["orders"].stack()
print(back.head(3).to_dict())

2 (8, 8)
delivery_slot  Evening (4-8)  Midday (10-2)  Morning (6-10)  Night (8-11)
city                                                                     
Ahmedabad                148             64              94            87
Bengaluru                507            223             279           292
['orders_evening', 'orders_midday', 'orders_morning', 'orders_night']
{('Ahmedabad', 'Evening (4-8)'): 148, ('Ahmedabad', 'Midday (10-2)'): 64, ('Ahmedabad', 'Morning (6-10)'): 94}

How it works: unstack moves an index level into the columns, producing two header levels (the measure and the slot). Select one top-level group with wide2["orders"]. To flatten the headers into plain strings, rebuild columns with a comprehension. stack is the inverse of unstack.

Reading the result: The unstacked table has 2 column levels and 8 columns. Flat column names such as orders_evening are easier to use in later code.

Example 5: Binning with cut and qcut

Question: Group customers by order count into fixed bands and into equal-sized groups.

per_cust = o.groupby("customer_id").size().rename("orders")

bands = pd.cut(per_cust, bins=[0, 2, 5, 10, 20, 1000], labels=["1-2", "3-5", "6-10", "11-20", "21+"])
print(bands.value_counts().sort_index().to_dict())

quartiles = pd.qcut(per_cust, q=4, labels=["Q1 low", "Q2", "Q3", "Q4 high"])
print(quartiles.value_counts().sort_index().to_dict())
print(per_cust.groupby(quartiles, observed=True).agg(["min", "max", "sum"]))

print(pd.qcut(per_cust.rank(method="first"), 4, labels=False).value_counts().sort_index().to_dict())

{'1-2': 71, '3-5': 185, '6-10': 194, '11-20': 84, '21+': 54}
{'Q1 low': 196, 'Q2': 117, 'Q3': 137, 'Q4 high': 138}
         min  max   sum
orders                 
Q1 low     1    4   549
Q2         5    6   642
Q3         7   10  1128
Q4 high   11  232  3681
{0: 147, 1: 147, 2: 147, 3: 147}

How it works: pd.cut uses edges you choose (equal width or custom), while pd.qcut puts roughly equal numbers of rows into each bin (quantiles). Heavily repeated values can make quantile edges collide; ranking first (rank(method="first")) forces equal-sized groups. The last example shows that trick.

Reading the result: The top quartile of customers (by order count) places 61% of all orders. That kind of concentration is common in retail.

Example 6: The str accessor

Question: Pull structured pieces out of text.

customers = pd.read_csv("customers.csv")

parts = customers["name"].str.split(" ", n=1, expand=True)
parts.columns = ["first", "last"]
print(parts.head(3).to_string(index=False))

customers["email_user"] = customers["email"].str.extract(r"^([a-z]+)\.")
customers["email_digits"] = customers["email"].str.extract(r"(\d+)@").astype(int)
print(customers[["email", "email_user", "email_digits"]].head(2).to_string(index=False))

print(customers["name"].str.len().describe()[["min", "max"]].to_dict())
print(customers["name"].str.replace(r"[aeiou]", "", regex=True).head(2).tolist())
print(customers["city"].str.upper().str[:3].value_counts().head(3).to_dict())

 first   last
  Isha  Joshi
 Priya Sharma
Suresh  Singh
                    email email_user  email_digits
  isha.joshi1@example.com       isha             1
priya.sharma2@example.com      priya             2
{'min': 8.0, 'max': 18.0}
['Ish Jsh', 'Pry Shrm']
{'DEL': 124, 'BEN': 124, 'MUM': 118}

How it works: split(..., expand=True) returns a DataFrame with one column per piece. str.extract uses a regular expression with a capture group (the brackets) to pull out a part of each string. Other staples are str.len, str.replace, str.cat, str.zfill and slicing with str[:3].

Reading the result: There are 20 distinct surnames among 600 customers; the most common is Chopra with 37 customers.

Example 7: Categorical dtype

Question: Make weekdays sort in calendar order and save memory.

days = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]
o["weekday"] = pd.Categorical(o["order_date"].dt.day_name(), categories=days, ordered=True)

print(o["weekday"].dtype)
print(o.groupby("weekday", observed=True).size().to_dict())
print((o["weekday"] >= "Saturday").sum())

before = o["payment_method"].memory_usage(deep=True)
after = o["payment_method"].astype("category").memory_usage(deep=True)
print(before > after, round(before / after, 1))
print(o["payment_method"].astype("category").cat.categories.tolist())

category
{'Monday': 796, 'Tuesday': 771, 'Wednesday': 769, 'Thursday': 776, 'Friday': 767, 'Saturday': 1042, 'Sunday': 1079}
2121
True 49.6
['COD', 'Card', 'UPI', 'Wallet']

How it works: A categorical column stores each distinct value once and keeps small integer codes per row. That saves a lot of memory on columns with few distinct values, and with ordered=True the categories have a meaningful order, so sorting and comparisons (>= "Saturday") follow the calendar instead of the alphabet.

Reading the result: The categorical version is about 50 times smaller in memory, and groupby results now appear in calendar order from Monday to Sunday.

Example 8: map, where and apply

Question: Apply custom logic, and see why row-wise apply is slow.

import time

fee_by_slot = {"Morning (6-10)": 15, "Midday (10-2)": 10, "Evening (4-8)": 25, "Night (8-11)": 20}
print(sorted(o["delivery_slot"].unique()))
o["slot_fee"] = o["delivery_slot"].map(fee_by_slot).fillna(15)

def surge(row):
    return row["slot_fee"] * (1.5 if row["is_peak"] else 1)

o["is_peak"] = o["order_date"].dt.dayofweek >= 5
t0 = time.perf_counter(); a = o.apply(surge, axis=1); t_apply = time.perf_counter() - t0
t0 = time.perf_counter(); b = o["slot_fee"] * np.where(o["is_peak"], 1.5, 1); t_vec = time.perf_counter() - t0

print(np.allclose(a, b), t_vec < t_apply)
print(b.groupby(o["delivery_slot"]).mean().round(2).to_dict())
print(o["delivery_minutes"].where(o["delivery_minutes"] < 40, 40).max())

['Evening (4-8)', 'Midday (10-2)', 'Morning (6-10)', 'Night (8-11)']
True True
{'Evening (4-8)': 29.3, 'Midday (10-2)': 11.77, 'Morning (6-10)': 17.64, 'Night (8-11)': 23.7}
40.0

How it works: map with a dictionary is the fast way to translate values. apply(func, axis=1) calls a Python function for every row, which is flexible but slow (thousands of times slower than vectorised code on big tables). The vectorised np.where version gives identical results. Series.where(cond, other) keeps values where the condition holds and replaces the rest, here capping times at 40.

Reading the result: The row-wise and vectorised versions agree (True), and the vectorised one is faster (True). Use apply only when you cannot express the logic with column operations.

Example 9: Method chaining

Question: Express a whole analysis as one readable pipeline.

result = (
    orders
    .query("order_status == 'Delivered'")
    .merge(stores[["store_id", "city"]], on="store_id")
    .assign(
        month=lambda d: d["order_date"].dt.to_period("M"),
        late=lambda d: d["delivery_minutes"] > 30,
    )
    .groupby(["city", "month"], as_index=False)
    .agg(orders=("order_id", "count"), late_pct=("late", lambda s: s.mean() * 100))
    .query("orders >= 30")
    .sort_values("late_pct", ascending=False)
    .head(5)
    .round({"late_pct": 1})
)
print(result.to_string(index=False))

     city   month  orders  late_pct
  Kolkata 2026-05      40      27.5
   Mumbai 2025-08      49      26.5
Ahmedabad 2026-04      31      25.8
     Pune 2026-01      39      25.6
Bengaluru 2025-10      59      23.7

How it works: Each method returns a new DataFrame that the next step uses, so the whole analysis reads like a recipe from top to bottom, with no throwaway variables. assign creates columns (using lambda d: to refer to the current table), query filters, and merge, groupby, sort_values and head do the rest. Wrapping the chain in brackets lets you break lines freely.

Reading the result: The five city-months with the highest late share (among those with at least 30 delivered orders) start with Kolkata in 2026-05 at 27.5% late.

Example 10: pipe and a reusable pipeline

Question: Package steps as functions and chain them with pipe.

def only_delivered(d):
    return d[d["order_status"] == "Delivered"]

def add_city(d, stores=stores):
    return d.merge(stores[["store_id", "city"]], on="store_id", validate="many_to_one")

def add_flags(d, late_after=30):
    return d.assign(late=d["delivery_minutes"] > late_after)

def city_report(d):
    return (
        d.groupby("city")
        .agg(orders=("order_id", "count"), late_pct=("late", "mean"))
        .assign(late_pct=lambda r: (r["late_pct"] * 100).round(1))
        .sort_values("late_pct", ascending=False)
    )

report = orders.pipe(only_delivered).pipe(add_city).pipe(add_flags, late_after=25).pipe(city_report)
print(report.head(3))
print(len(report), report["orders"].sum())
print(orders.pipe(only_delivered).pipe(add_city).pipe(add_flags).pipe(city_report)["late_pct"].max())

           orders  late_pct
city                       
Ahmedabad     352      41.2
Bengaluru    1156      40.0
Kolkata       358      39.9
8 5391
17.3

How it works: pipe(func, *args) passes the DataFrame as the first argument to your function, letting you chain your own named steps. Small functions are easy to test and reuse, and parameters such as late_after make the pipeline configurable. Changing the late threshold from 30 to 25 minutes was a one-argument change.

Reading the result: With a 25-minute threshold, Ahmedabad has the highest late share at 41.2%. The pipeline covered 5,391 delivered orders across 8 cities.

Common mistakes

1. Using a pivot table with duplicate index/column pairs and no aggfunc, which raises an error or averages silently.

2. Forgetting observed=True when grouping by a categorical column, creating empty groups.

3. Using apply(axis=1) on big tables when vectorised code exists.

4. Chaining with a variable that has been modified in between, so the result depends on the order you ran cells.

5. Normalising a crosstab along the wrong axis.

o.pivot(index="city", columns="month", values="order_id")

Traceback (most recent call last):
  File "example.py", line 1, in <module>
    o.pivot(index="city", columns="month", values="order_id")
    ~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "example.py", line 10988, in pivot
    return pivot(self, index=index, columns=columns, values=values)
  File "example.py", line 910, in pivot
    result = cast("DataFrame", indexed.unstack(columns_listlike))  # type: ignore[arg-type]
                               ~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^
  File "example.py", line 4501, in unstack
    return unstack(self, level, fill_value, sort)
  File "example.py", line 595, in unstack
    return _unstack_extension_series(obj, level, fill_value, sort=sort)
  File "example.py", line 648, in _unstack_extension_series
    result = df.unstack(level=level, fill_value=fill_value, sort=sort)
  File "example.py", line 11581, in unstack
    result = unstack(self, level, fill_value, sort)
  File "example.py", line 583, in unstack
    return _unstack_frame(obj, level, fill_value=fill_value, sort=sort)
  File "example.py", line 606, in _unstack_frame
    unstacker = _Unstacker(
        obj.index, level=level, constructor=obj._constructor, sort=sort
    )
  File "example.py", line 176, in __init__
    self._make_selectors()
    ~~~~~~~~~~~~~~~~~~~~^^
  File "example.py", line 232, in _make_selectors
    raise ValueError("Index contains duplicate entries, cannot reshape")
ValueError: Index contains duplicate entries, cannot reshape

Practice questions

Practice 1 (Easy): Payment by status pivot

Build a crosstab of payment method versus order status with row percentages.

tab = pd.crosstab(o["payment_method"], o["order_status"], normalize="index") * 100
print(tab.round(1))

order_status    Cancelled  Delivered  Returned
payment_method                                
COD                   6.7       89.2       4.1
Card                  6.0       90.3       3.7
UPI                   6.1       89.8       4.1
Wallet                6.2       90.4       3.4

Practice 2 (Medium): Long format for plotting

Convert the delivered-orders-per-city-per-month pivot into long format, sorted by month and city, and give the first rows.

d = o[o["order_status"] == "Delivered"]
p = d.pivot_table(index="city", columns="month", values="order_id", aggfunc="count", fill_value=0)
long = p.reset_index().melt(id_vars="city", var_name="month", value_name="delivered").sort_values(["month", "city"])
print(long.head(3).to_string(index=False))
print(long["delivered"].sum() == len(d))

     city   month  delivered
Ahmedabad 2025-01          8
Bengaluru 2025-01         18
  Chennai 2025-01          0
True

Practice 3 (Hard): Customer value segments

Compute the total spend per customer (quantity × unit price of delivered orders), split customers into four equal groups with qcut, and report each group's customer count and share of total spend.

items = pd.read_csv("order_items.csv")
delivered_ids = orders.loc[orders["order_status"] == "Delivered", ["order_id", "customer_id"]]
spend = (
    items.merge(delivered_ids, on="order_id")
    .assign(value=lambda d: d["quantity"] * d["unit_price"])
    .groupby("customer_id")["value"].sum()
)
seg = pd.qcut(spend, 4, labels=["Q1", "Q2", "Q3", "Q4"])
out = spend.groupby(seg, observed=True).agg(customers="count", spend="sum")
out["share_pct"] = (out["spend"] / out["spend"].sum() * 100).round(1)
print(out.round(0))

       customers      spend  share_pct
value                                 
Q1           146   177300.0        5.0
Q2           146   400979.0       11.0
Q3           146   687744.0       20.0
Q4           146  2267811.0       64.0

Key takeaways

• pivot_table and crosstab build summary matrices; normalize turns counts into shares.
• melt and pivot convert between long and wide; stack and unstack move index levels.
• cut bins by value edges; qcut bins into equal-sized groups.
• The .str accessor, map and where handle text and conditional logic at scale.
• Categoricals save memory and give ordered sorting.
• Avoid row-wise apply; vectorise instead.
• Method chains and pipe turn an analysis into a readable, repeatable pipeline.