Advanced Pandas Operations

Real-World Scenario

A monthly report is built from raw transactions: filter bad rows, normalize text, add revenue, aggregate, rank. Written as one chain, each step reads as one line — and the next person can add or remove a step without untangling a dozen temporary variables.

Method Chaining

Each method returns a new DataFrame, so steps can follow one another in a single expression.

import pandas as pd

raw = pd.DataFrame({
    "customer": [1, 2, 1, 3, 2, 3],
    "category": ["books", "BOOKS", "Toys", "toys", "books", "Toys"],
    "amount":   [20.0, 35.0, -5.0, 50.0, 15.0, 30.0],
    "quantity": [1, 2, 1, 1, 3, 2],
})

report = (
    raw
    .query("amount > 0")
    .assign(category=lambda d: d["category"].str.lower(),
            revenue=lambda d: d["amount"] * d["quantity"])
    .groupby("category")
    .agg(revenue=("revenue", "sum"), customers=("customer", "nunique"))
    .sort_values("revenue", ascending=False)
)
print(report)

$ python chain.py
          revenue  customers
category                    
books       135.0          2
toys        110.0          1

The negative refund row is dropped first, then the mixed-case spellings (books/BOOKS, Toys/toys) collapse, so the grouping sees two categories instead of four. nunique counts distinct customers per category, which is different from counting rows.

Why assign() Needs a Lambda

A lambda inside assign receives the DataFrame at that step of the chain.

import pandas as pd

df = pd.DataFrame({"price": [10.0, 20.0], "qty": [3, 1]})

try:
    (df.assign(total=df["price"] * df["qty"])
       .assign(with_tax=df["total"] * 1.2))
except KeyError as e:
    print("KeyError:", e)

out = (df.assign(total=lambda d: d["price"] * d["qty"])
         .assign(with_tax=lambda d: d["total"] * 1.2))
print(out)

$ python assign_lambda.py
KeyError: 'total'
   price  qty  total  with_tax
0   10.0    3   30.0      36.0
1   20.0    1   20.0      24.0

In the first chain, df["total"] is evaluated before the chain runs, against the original df, which has no total column. The lambda version is evaluated later, against the intermediate DataFrame that the first assign produced, so total exists by then.

pipe(): Your Own Functions in a Chain

pipe(f, ...) calls f(df, ...) and continues the chain with its result.

import pandas as pd

def drop_extremes(df, col, upper):
    return df[df[col] <= upper]

def log_shape(df, label):
    print(f"[{label}] {df.shape}")
    return df

orders = pd.DataFrame({"amount": [12, 15, 9, 900, 14, 11],
                       "region": ["N", "S", "N", "S", "N", "S"]})

result = (orders
          .pipe(log_shape, "raw")
          .pipe(drop_extremes, "amount", upper=100)
          .pipe(log_shape, "clean")
          .groupby("region")["amount"].mean())
print(result)

$ python pipe.py
[raw] (6, 2)
[clean] (5, 2)
region
N    11.666667
S    13.000000
Name: amount, dtype: float64

The two log_shape lines print while the chain runs, showing that one row was removed between them. Without that step, the 900 order would have dragged the South average far above the North one; after it, the two regions are close.

Rolling Versus Exponential Windows

A step change shows how the two kinds of moving average react.

import pandas as pd

sales = pd.Series([100] * 5 + [200] * 5, name="sales")

out = pd.DataFrame({
    "sales": sales,
    "rolling_4": sales.rolling(4).mean(),
    "ewm_span4": sales.ewm(span=4, adjust=False).mean().round(1),
})
print(out)

$ python ewm.py
   sales  rolling_4  ewm_span4
0    100        NaN      100.0
1    100        NaN      100.0
2    100        NaN      100.0
3    100      100.0      100.0
4    100      100.0      100.0
5    200      125.0      140.0
6    200      150.0      164.0
7    200      175.0      178.4
8    200      200.0      187.0
9    200      200.0      192.2

Sales double at row 5. The 4-row rolling mean climbs in equal 25-unit steps and reaches 200 at row 8, once all four values in its window are new ones — it weights the last four values equally and ignores anything older. The exponential mean moves immediately but approaches 200 gradually, since every earlier 100 still carries a little weight.

Cross-Sections of a MultiIndex

xs selects every row that has a given value at one index level.

import pandas as pd

idx = pd.MultiIndex.from_product([[2023, 2024], ["Q1", "Q2"], ["laptop", "phone"]],
                                 names=["year", "quarter", "product"])
df = pd.DataFrame({"revenue": range(10, 90, 10)}, index=idx)

print(df.xs("phone", level="product"))
print(df.xs((2024, "Q2")))
print(df["revenue"].groupby(level="year").sum())

$ python xs.py
              revenue
year quarter         
2023 Q1            20
     Q2            40
2024 Q1            60
     Q2            80
         revenue
product         
laptop        70
phone         80
year
2023    100
2024    260
Name: revenue, dtype: int64

Selecting the inner product level with .loc would need slicing syntax; xs takes the level by name. Passing a tuple for the outer levels returns what is left beneath them. The MultiIndex lesson covers sorting, slicing, and the errors this indexing raises.

Does eval() Make It Faster?

eval evaluates an expression string over the columns. Whether it is faster depends on your environment, so measure it.

import importlib.util
import timeit
import numpy as np
import pandas as pd

rng = np.random.default_rng(42)
df = pd.DataFrame(rng.uniform(0, 100, (1_000_000, 3)), columns=["a", "b", "c"])

print("numexpr installed:", importlib.util.find_spec("numexpr") is not None)
same = np.allclose(df.eval("a * b + c ** 2"), df["a"] * df["b"] + df["c"] ** 2)
print("same result:", same)

t_plain = min(timeit.repeat(lambda: df["a"] * df["b"] + df["c"] ** 2, number=3, repeat=5)) / 3
t_eval = min(timeit.repeat(lambda: df.eval("a * b + c ** 2"), number=3, repeat=5)) / 3
print(f"plain: {t_plain * 1000:.1f} ms   eval: {t_eval * 1000:.1f} ms")

$ python eval_speed.py
numexpr installed: False
same result: True
plain: 5.7 ms   eval: 13.4 ms

This environment has no numexpr, so eval fell back to Pandas’ "python" engine — and took two to two-and-a-half times as long as the plain expression across runs here, not less. Pandas only hands the expression to numexpr when that library is installed, which is the setup the usual “eval is faster” advice assumes. These are best-of-five timings from one machine: run it on yours before choosing eval for speed.

Practice

1. Extend the chain in chain.py with an avg_per_customer column (revenue divided by customers), added with assign after the agg step.

import pandas as pd

raw = pd.DataFrame({
    "customer": [1, 2, 1, 3, 2, 3],
    "category": ["books", "BOOKS", "Toys", "toys", "books", "Toys"],
    "amount":   [20.0, 35.0, -5.0, 50.0, 15.0, 30.0],
    "quantity": [1, 2, 1, 1, 3, 2],
})
report = (raw.query("amount > 0")
             .assign(category=lambda d: d["category"].str.lower(),
                     revenue=lambda d: d["amount"] * d["quantity"])
             .groupby("category")
             .agg(revenue=("revenue", "sum"), customers=("customer", "nunique"))
             .assign(avg_per_customer=lambda d: (d["revenue"] / d["customers"]).round(2)))
print(report)

$ python practice_chain.py
          revenue  customers  avg_per_customer
category                                      
books       135.0          2              67.5
toys        110.0          1             110.0

2. In pipe.py, write a flag_large(df, col, threshold) function that adds a boolean is_large column instead of dropping rows, and use it with pipe.

import pandas as pd

def flag_large(df, col, threshold):
    return df.assign(is_large=df[col] > threshold)

orders = pd.DataFrame({"amount": [12, 15, 900, 14]})
print(orders.pipe(flag_large, "amount", threshold=100))

$ python practice_pipe.py
   amount  is_large
0      12     False
1      15     False
2     900      True
3      14     False

3. In ewm.py, add an ewm(span=2) column and compare how fast it reaches 200 with the span=4 version.

import pandas as pd

sales = pd.Series([100] * 5 + [200] * 5)
print(pd.DataFrame({
    "sales": sales,
    "span2": sales.ewm(span=2, adjust=False).mean().round(1),
    "span4": sales.ewm(span=4, adjust=False).mean().round(1),
}))

$ python practice_ewm.py
   sales  span2  span4
0    100  100.0  100.0
1    100  100.0  100.0
2    100  100.0  100.0
3    100  100.0  100.0
4    100  100.0  100.0
5    200  166.7  140.0
6    200  188.9  164.0
7    200  196.3  178.4
8    200  198.8  187.0
9    200  199.6  192.2