Data Analysis Workflows in Pandas

An analysis isn't just individual operations — it's a sequence of steps that transforms raw data into insight. This file covers the patterns that structure real analytical work: the method chain, pivot tables, apply functions, and time series operations.

Learning Objectives

• Write readable Pandas pipelines using method chaining
• Apply functions element-wise and group-wise with .apply()
• Use .map() for column-level transformations
• Create pivot tables with pd.pivot_table()
• Perform basic time series analysis

---

Method Chaining — The Pandas Way

Instead of writing a series of assignments, chain operations together. The result is readable like a sentence.

import pandas as pd

# Without chaining — hard to read
df2 = df[df["status"] == "completed"]
df3 = df2[["customer_id", "total", "order_date"]]
df4 = df3.sort_values("total", ascending=False)
df5 = df4.head(10)

# With chaining — one readable pipeline
top_completed = (
    df
    .query("status == 'completed'")
    [["customer_id", "total", "order_date"]]
    .sort_values("total", ascending=False)
    .head(10)
    .reset_index(drop=True)
)

The full analyst pipeline as a method chain

customer_report = (
    pd.read_csv("orders.csv", parse_dates=["order_date"])
    .query("status == 'completed'")
    .assign(
        total=lambda df: df["quantity"] * df["unit_price"],
        month=lambda df: df["order_date"].dt.to_period("M"),
    )
    .groupby("customer_id")
    .agg(
        orders=("order_id", "count"),
        revenue=("total", "sum"),
        last_order=("order_date", "max"),
    )
    .reset_index()
    .sort_values("revenue", ascending=False)
    .assign(rank=lambda df: range(1, len(df) + 1))
    .set_index("rank")
)

For mid-level analysts

Use .assign() inside chains to add computed columns without breaking the chain. df.assign(col=lambda df: df["a"] * df["b"]) is cleaner than df["col"] = df["a"] * df["b"] when you're chaining.

---

.apply() — Row-wise and Column-wise Functions

.apply() applies a function to each row or column.

On a Series (column) — element-wise

# Simple: use vectorised operations when possible
df["total"] = df["quantity"] * df["unit_price"]   # fast

# For complex logic, use apply
def order_label(row):
    if row["total"] > 300:
        return "Premium"
    elif row["total"] > 100:
        return "Standard"
    else:
        return "Budget"

df["label"] = df.apply(order_label, axis=1)  # axis=1 = apply to each row

apply can be slow on large DataFrames

.apply(func, axis=1) runs Python code in a loop — it's much slower than vectorised Pandas operations. For simple conditions, use np.where or pd.cut instead.

import numpy as np

# Fast: np.where for binary conditions
df["high_value"] = np.where(df["total"] > 200, "High", "Normal")

# Fast: pd.cut for binning/buckets
df["price_band"] = pd.cut(
    df["total"],
    bins=[0, 50, 200, 500, float("inf")],
    labels=["Budget", "Standard", "Premium", "Enterprise"],
    right=False
)

On a DataFrame — column-wise summary

# Apply a function to each column
df[["quantity", "unit_price", "total"]].apply(["min", "max", "mean", "std"])

---

.map() — Lookup and Replace

.map() applies a function or mapping dictionary to a Series.

# Map codes to labels
status_labels = {
    "completed": "Fulfilled",
    "pending": "In Progress",
    "cancelled": "Lost Sale",
    "refunded": "Returned",
}
df["status_label"] = df["status"].map(status_labels)

# Map customer_id to segment from another DataFrame
customer_segments = customers.set_index("customer_id")["segment"]
df["segment"] = df["customer_id"].map(customer_segments)

# Map with a function
df["email_domain"] = df["email"].map(lambda e: e.split("@")[1])

---

Pivot Tables

pd.pivot_table() reshapes data — rows become columns, aggregation is applied.

# Revenue by month and category
pivot = pd.pivot_table(
    df[df["status"] == "completed"],
    values="total",
    index="month",
    columns="category",
    aggfunc="sum",
    fill_value=0,
    margins=True,           # add row/column totals
    margins_name="Total",
)
print(pivot)

Sample output:

category	Books	Electronics	Furniture	Kitchen	Total
2024-01	149.97	298.00	389.00	0.00	836.97
2024-02	249.95	0.00	0.00	179.98	429.93
Total	399.92	298.00	389.00	179.98	1266.90

# Count of orders per customer segment and month
count_pivot = pd.pivot_table(
    df,
    values="order_id",
    index=df["order_date"].dt.month_name(),
    columns="status",
    aggfunc="count",
    fill_value=0,
)

---

Time Series Analysis

Pandas has excellent built-in time series support.

# Set date as index for time-series operations
ts = (
    df[df["status"] == "completed"]
    .assign(total=df["quantity"] * df["unit_price"])
    .groupby("order_date")["total"]
    .sum()
    .sort_index()
)

# Resample to monthly
monthly = ts.resample("ME").sum()   # 'ME' = month end
weekly  = ts.resample("W").sum()

# Rolling average
rolling_7d = ts.rolling(window=7).mean()
rolling_28d = ts.rolling(window=28).mean()

# Month-over-month change
monthly_pct_change = monthly.pct_change() * 100

# Shift — compare to previous period
monthly_lag = monthly.shift(1)         # same as previous month
yoy = monthly / monthly.shift(12) - 1 # year-over-year

import matplotlib.pyplot as plt

fig, axes = plt.subplots(2, 1, figsize=(12, 8))

# Daily revenue with rolling average
axes[0].plot(ts, alpha=0.4, label="Daily")
axes[0].plot(rolling_7d, label="7-day MA")
axes[0].plot(rolling_28d, label="28-day MA")
axes[0].set_title("Daily Revenue with Moving Averages")
axes[0].legend()
axes[0].grid(True, alpha=0.3)

# Month-over-month change
axes[1].bar(monthly_pct_change.index, monthly_pct_change, color="teal", alpha=0.7)
axes[1].axhline(y=0, color="red", linewidth=1)
axes[1].set_title("Month-over-Month Revenue Change (%)")

plt.tight_layout()
plt.show()

---

Handling Missing Values

# Detect
print(df.isnull().sum())                    # missing per column
print(df.isnull().mean() * 100)             # missing percentage per column

# Drop
df_clean = df.dropna()                      # drop rows with ANY NaN
df_clean = df.dropna(subset=["email"])      # drop rows where email is NaN
df_clean = df.dropna(thresh=5)              # keep rows with at least 5 non-NaN values

# Fill
df["discount_pct"] = df["discount_pct"].fillna(0)      # fill with 0
df["category"] = df["category"].fillna("Unknown")      # fill with string
df["revenue"] = df["revenue"].fillna(df["revenue"].mean())  # fill with mean
df["price"] = df["price"].fillna(method="ffill")        # forward fill

# Interpolate (time series)
df["daily_revenue"] = df["daily_revenue"].interpolate(method="linear")

---

Practice Exercises

Warm-up 1. Use a method chain to: load a CSV, filter to completed orders, add a total column, sort by total descending, and show the top 5 rows. 2. Use .map() to add a status_label column using the mapping: completed→Fulfilled, pending→In Progress, cancelled→Lost Sale. 3. Create a pivot table showing order count by month (rows) and status (columns).

Main 4. Use pd.cut to create an order_size column: Budget (<£50), Standard (£50-£200), Premium (>£200). 5. Resample daily order data to monthly and plot the monthly revenue trend. 6. Calculate the 7-day rolling average revenue and plot it alongside the daily data.

Stretch 7. Write a complete pipeline: load → filter → assign totals → pivot by month/category → export to Excel (use df.to_excel()). 8. Build a time-series anomaly detector: flag days where revenue is more than 2 standard deviations below the 28-day rolling mean.

---

← GroupBy and Merge · Next: Mini Project →