Advanced Pandas Operations

Real-World Scenario

An analyst builds a monthly sales report from raw transaction data. Instead of 20 lines of assignments and intermediate DataFrames, they write a 10-step method chain that reads like English: load → validate → clean → enrich → aggregate → format. The next analyst can follow the logic without a word of documentation.

Method Chaining

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)
n   = 10_000

raw_df = pd.DataFrame({
    "transaction_id": range(n),
    "customer_id":    rng.integers(1, 1000, n),
    "product":        rng.choice(["laptop", "phone", "tablet", "monitor", "keyboard"], n),
    "category":       rng.choice(["Electronics", "electronics", "ELECTRONICS"], n),
    "amount":         rng.lognormal(4, 1, n).round(2),
    "quantity":       rng.integers(1, 5, n),
    "date":           pd.date_range("2024-01-01", periods=n, freq="h"),
    "region":         rng.choice(["NA", "EU", "APAC", None], n, p=[0.4, 0.3, 0.25, 0.05]),
})

# Readable method chain — each step is a transformation
monthly_report = (
    raw_df
    # 1. Validate: remove invalid amounts
    .query("amount > 0 and quantity > 0")
    # 2. Clean: standardize text
    .assign(category=lambda df: df["category"].str.upper())
    # 3. Fill missing values
    .assign(region=lambda df: df["region"].fillna("Unknown"))
    # 4. Feature engineering
    .assign(
        revenue=lambda df: df["amount"] * df["quantity"],
        month=lambda df: df["date"].dt.to_period("M"),
    )
    # 5. Aggregate: monthly revenue per region
    .groupby(["month", "region"])
    .agg(
        total_revenue=("revenue", "sum"),
        n_transactions=("transaction_id", "count"),
        avg_order=("revenue", "mean"),
        unique_customers=("customer_id", "nunique"),
    )
    .round(2)
    # 6. Compute derived metrics
    .assign(revenue_per_customer=lambda df: df["total_revenue"] / df["unique_customers"])
    # 7. Sort
    .sort_values(["month", "total_revenue"], ascending=[True, False])
    .reset_index()
)

print(monthly_report.head(10).to_string(index=False))

pipe() for Custom Functions

import pandas as pd
import numpy as np

def remove_outliers(df: pd.DataFrame, col: str, n_std: float = 3.0) -> pd.DataFrame:
    """Remove rows where col is more than n_std standard deviations from the mean."""
    mean, std = df[col].mean(), df[col].std()
    return df[(df[col] - mean).abs() <= n_std * std]

def add_percentile_rank(df: pd.DataFrame, col: str) -> pd.DataFrame:
    """Add a percentile rank column."""
    return df.assign(**{f"{col}_pct": df[col].rank(pct=True)})

def log_shape(df: pd.DataFrame, label: str = "") -> pd.DataFrame:
    """Log DataFrame shape (useful for debugging pipelines)."""
    print(f"[{label}] shape: {df.shape}")
    return df

# Use pipe() to insert custom functions into a method chain
rng = np.random.default_rng(42)
df  = pd.DataFrame({
    "amount": np.concatenate([rng.normal(100, 20, 980), rng.normal(1000, 100, 20)]),
    "category": rng.choice(["A", "B", "C"], 1000),
})

result = (
    df
    .pipe(log_shape, "raw")
    .pipe(remove_outliers, "amount", n_std=2.5)
    .pipe(log_shape, "after outlier removal")
    .pipe(add_percentile_rank, "amount")
    .groupby("category")
    .agg(
        mean_amount=("amount", "mean"),
        mean_pct=("amount_pct", "mean"),
        count=("amount", "count"),
    )
    .round(3)
)
print(result)

MultiIndex

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)

# Create hierarchical index
years      = [2022, 2023, 2024]
quarters   = ["Q1", "Q2", "Q3", "Q4"]
products   = ["laptop", "phone", "tablet"]

idx = pd.MultiIndex.from_product([years, quarters, products],
                                   names=["year", "quarter", "product"])
df  = pd.DataFrame({
    "revenue": rng.integers(50_000, 500_000, len(idx)),
    "units":   rng.integers(100, 5000, len(idx)),
}, index=idx)

# Access by level — much more powerful than boolean indexing
print("All 2024 data:")
print(df.loc[2024].head())

print("\n2023 Q2 laptop:")
print(df.loc[(2023, "Q2", "laptop")])

# Cross-section: all products for a given year/quarter
print("\nAll products in 2024 Q4:")
print(df.xs((2024, "Q4"), level=["year", "quarter"]))

# Aggregate at a level
annual_revenue = df["revenue"].groupby(level="year").sum()
print(f"\nAnnual revenue:\n{annual_revenue}")

# Stack / unstack: pivot MultiIndex axes
pivot = df["revenue"].unstack(level="product")  # products become columns
print(f"\nPivoted (products as columns):\n{pivot.head()}")

# Reset a MultiIndex to flat columns
flat_df = df.reset_index()
print(f"\nFlat DataFrame:\n{flat_df.head()}")

Window Functions

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)
dates = pd.date_range("2023-01-01", periods=365, freq="D")
df = pd.DataFrame({
    "date":    dates,
    "sales":   rng.lognormal(6, 0.5, 365).round(2),
    "visitors": rng.integers(100, 1000, 365),
}).set_index("date")

# Rolling: fixed-size window
df["sales_7d_avg"]  = df["sales"].rolling(7,  min_periods=1).mean()
df["sales_30d_avg"] = df["sales"].rolling(30, min_periods=1).mean()
df["sales_7d_std"]  = df["sales"].rolling(7,  min_periods=1).std()

# Expanding: cumulative (grows from start)
df["cumulative_sales"]   = df["sales"].expanding().sum()
df["running_avg_sales"]  = df["sales"].expanding().mean()

# Exponential weighted: more weight on recent values
df["ewm_sales"] = df["sales"].ewm(span=7, adjust=False).mean()

# Detect anomalies: z-score using 30-day rolling stats
df["zscore"] = (
    (df["sales"] - df["sales_30d_avg"]) /
    df["sales"].rolling(30, min_periods=1).std()
)
anomalies = df[df["zscore"].abs() > 2.5]
print(f"Anomaly days detected: {len(anomalies)}")

# Rank within rolling window
df["sales_rank_30d"] = df["sales"].rolling(30, min_periods=1).rank(pct=True)

# Percentage change
df["sales_wow"] = df["sales"].pct_change(7).round(4)   # week-over-week
df["sales_mom"] = df["sales"].pct_change(30).round(4)  # month-over-month

print(df[["sales", "sales_7d_avg", "sales_30d_avg", "zscore", "sales_wow"]].tail(10).round(3))

eval() and query() for Performance

import pandas as pd
import numpy as np
import timeit

rng = np.random.default_rng(42)
n   = 1_000_000

df = pd.DataFrame({
    "a": rng.uniform(0, 100, n),
    "b": rng.uniform(0, 100, n),
    "c": rng.uniform(0, 100, n),
    "d": rng.integers(0, 5, n),
})

# eval(): compute new column expression without temp arrays
# ~2x faster than df["a"] + df["b"] on large DataFrames (avoids intermediate copies)
df_eval = df.eval("result = a * b + c ** 2 - a / (b + 1)")
t_eval  = timeit.timeit(lambda: df.eval("result = a * b + c ** 2"), number=10) / 10

df_py   = df.copy()
df_py["result"] = df["a"] * df["b"] + df["c"] ** 2
t_py    = timeit.timeit(
    lambda: df["a"] * df["b"] + df["c"] ** 2, number=10
) / 10

print(f"Python expression: {t_py*1000:.1f}ms")
print(f"eval():            {t_eval*1000:.1f}ms")

# query(): readable boolean filtering with variable injection
threshold = 50.0
result = df.query("a > @threshold and b < 30 and d in [1, 2, 3]")
print(f"\nFiltered rows: {len(result):,} / {len(df):,}")

# Multi-condition with query() vs traditional boolean indexing
t_query = timeit.timeit(
    lambda: df.query("a > 50 and b < 30 and c > 20"), number=20
) / 20
t_bool  = timeit.timeit(
    lambda: df[(df["a"] > 50) & (df["b"] < 30) & (df["c"] > 20)], number=20
) / 20
print(f"Boolean index: {t_bool*1000:.1f}ms")
print(f"query():       {t_query*1000:.1f}ms")