Data Manipulation

Data manipulation (also called data wrangling or munging) is the process of reshaping, combining, aggregating and deriving from a dataset until it answers the question at hand. This lesson applies the Unit 1 concepts — cleaning, transformation, aggregation — using Pandas in a single realistic workflow.

The Dataset

import pandas as pd
import numpy as np

np.random.seed(42)
n = 500

orders = pd.DataFrame({
    "order_id":   range(1001, 1001 + n),
    "customer":   np.random.choice([f"CUST{i:03d}" for i in range(1, 81)], n),
    "order_date": pd.to_datetime("2026-01-01") + pd.to_timedelta(
                      np.random.randint(0, 210, n), unit="D"),
    "category":   np.random.choice(["Electronics","Clothing","Books","Home","Sports"],
                                   n, p=[0.3, 0.25, 0.15, 0.2, 0.1]),
    "quantity":   np.random.randint(1, 6, n),
    "unit_price": np.round(np.random.uniform(200, 8000, n), 2),
    "city":       np.random.choice(["Delhi","Noida","Gurgaon","Ghaziabad"], n,
                                   p=[0.4, 0.25, 0.25, 0.1]),
    "rating":     np.random.choice([1,2,3,4,5,np.nan], n, p=[.05,.08,.17,.35,.30,.05]),
})
orders.loc[orders.sample(20, random_state=1).index, "unit_price"] = np.nan   # inject gaps

print(orders.shape)          # (500, 8)
print(orders.head())

1. Cleaning

print("Missing values:\n", orders.isnull().sum())
print("Duplicates:", orders.duplicated().sum())

clean = orders.copy()

# Median imputation for price (skew-safe), category-wise for extra accuracy
clean["unit_price"] = clean.groupby("category")["unit_price"].transform(
    lambda s: s.fillna(s.median())
)

# Rating missing genuinely means "not rated" — flag it rather than inventing a value
clean["was_rated"] = clean["rating"].notna().astype(int)
clean["rating"] = clean["rating"].fillna(0)

print("\nRemaining nulls:", clean.isnull().sum().sum())      # 0

2. Deriving New Columns

clean["revenue"] = (clean["quantity"] * clean["unit_price"]).round(2)

# Date parts
clean["month"] = clean["order_date"].dt.to_period("M").astype(str)
clean["weekday"] = clean["order_date"].dt.day_name()
clean["is_weekend"] = clean["order_date"].dt.dayofweek >= 5
clean["week"] = clean["order_date"].dt.isocalendar().week

# Binning a continuous variable (Unit 1 discretization)
clean["price_band"] = pd.cut(
    clean["unit_price"],
    bins=[0, 1000, 3000, 6000, np.inf],
    labels=["Budget", "Mid", "Premium", "Luxury"],
)

# Conditional logic
clean["order_size"] = np.select(
    [clean["revenue"] > 15000, clean["revenue"] > 5000],
    ["Large", "Medium"],
    default="Small",
)

print(clean[["order_id","category","revenue","price_band","order_size","weekday"]].head())

3. Aggregation and Grouping

# Single-key aggregation
print(clean.groupby("category").agg(
    orders=("order_id", "count"),
    revenue=("revenue", "sum"),
    avg_order=("revenue", "mean"),
    avg_rating=("rating", lambda s: s[s > 0].mean()),
).round(2).sort_values("revenue", ascending=False))

# Multi-key
by_cat_city = clean.groupby(["category", "city"])["revenue"].sum().round(0)
print(by_cat_city.head(10))

# Unstack turns the inner index level into columns
print(by_cat_city.unstack(fill_value=0))

# Customer-level aggregation — RFM analysis, a classic marketing technique
snapshot = clean["order_date"].max() + pd.Timedelta(days=1)

rfm = clean.groupby("customer").agg(
    recency=("order_date", lambda d: (snapshot - d.max()).days),
    frequency=("order_id", "count"),
    monetary=("revenue", "sum"),
).round(2)

# Score each dimension 1-4 by quartile
rfm["R_score"] = pd.qcut(rfm["recency"], 4, labels=[4, 3, 2, 1]).astype(int)
rfm["F_score"] = pd.qcut(rfm["frequency"].rank(method="first"), 4, labels=[1,2,3,4]).astype(int)
rfm["M_score"] = pd.qcut(rfm["monetary"], 4, labels=[1, 2, 3, 4]).astype(int)
rfm["RFM_total"] = rfm[["R_score", "F_score", "M_score"]].sum(axis=1)

rfm["segment"] = pd.cut(rfm["RFM_total"], bins=[0, 5, 8, 10, 12],
                        labels=["At Risk", "Needs Attention", "Loyal", "Champions"])

print(rfm.sort_values("RFM_total", ascending=False).head(8))
print("\nSegment sizes:\n", rfm["segment"].value_counts())

4. Pivot Tables

pivot = clean.pivot_table(
    values="revenue", index="category", columns="city",
    aggfunc="sum", margins=True, margins_name="Total", fill_value=0,
).round(0)
print(pivot)

# Multiple metrics at once
multi = clean.pivot_table(
    values=["revenue", "quantity"], index="category",
    columns="is_weekend", aggfunc={"revenue": "sum", "quantity": "mean"},
).round(2)
print(multi)

# Percentage of the total
pct = clean.pivot_table(values="revenue", index="category", columns="city", aggfunc="sum")
print((pct / pct.sum().sum() * 100).round(2))

5. Time-Series Manipulation

ts = clean.set_index("order_date").sort_index()

monthly = ts.resample("ME").agg(
    revenue=("revenue", "sum"),
    orders=("order_id", "count"),
).round(0)
monthly["avg_order_value"] = (monthly["revenue"] / monthly["orders"]).round(2)
monthly["mom_growth_pct"] = (monthly["revenue"].pct_change() * 100).round(2)
monthly["cumulative"] = monthly["revenue"].cumsum()
print(monthly)

# Rolling window — smooths daily noise
daily = ts.resample("D")["revenue"].sum()
smoothed = pd.DataFrame({
    "daily": daily,
    "ma_7": daily.rolling(7).mean().round(0),
    "ma_30": daily.rolling(30).mean().round(0),
})
print(smoothed.tail(10))

# Lag features — essential for forecasting models
monthly["prev_month"] = monthly["revenue"].shift(1)
monthly["yoy_placeholder"] = monthly["revenue"].shift(12)

6. Reshaping — Wide vs Long

# LONG (tidy) format — one row per observation
long = clean.groupby(["month", "category"])["revenue"].sum().reset_index()
print(long.head())

# WIDE format — categories become columns
wide = long.pivot(index="month", columns="category", values="revenue").round(0)
print(wide)

# Back to long with melt()
back_to_long = wide.reset_index().melt(
    id_vars="month", var_name="category", value_name="revenue"
)
print(back_to_long.head())

# stack / unstack on a MultiIndex
multi_idx = clean.groupby(["category", "city"])["revenue"].sum()
print(multi_idx.unstack().round(0))       # city becomes columns
print(multi_idx.unstack().stack().head()) # and back again

7. Ranking and Window Functions

cat_totals = clean.groupby("category")["revenue"].sum().reset_index()

cat_totals["rank"] = cat_totals["revenue"].rank(ascending=False, method="dense").astype(int)
cat_totals["pct_of_total"] = (cat_totals["revenue"] / cat_totals["revenue"].sum() * 100).round(2)
cat_totals = cat_totals.sort_values("rank")
cat_totals["cumulative_pct"] = cat_totals["pct_of_total"].cumsum().round(2)
print(cat_totals)
# The cumulative % column implements PARETO (80/20) analysis:
# find where cumulative_pct crosses 80 to identify the vital few categories.

# Top-N within each group
top_per_city = (clean.sort_values("revenue", ascending=False)
                     .groupby("city")
                     .head(2)[["city", "order_id", "category", "revenue"]])
print(top_per_city.sort_values("city"))

# Rank within group
clean["rank_in_category"] = clean.groupby("category")["revenue"].rank(
    ascending=False, method="dense"
).astype(int)

8. Method Chaining — Clean, Readable Pipelines

report = (
    orders
    .assign(unit_price=lambda d: d.groupby("category")["unit_price"]
                                  .transform(lambda s: s.fillna(s.median())))
    .assign(revenue=lambda d: (d["quantity"] * d["unit_price"]).round(2))
    .query("revenue > 1000")
    .groupby(["city", "category"], as_index=False)
    .agg(orders=("order_id", "count"), revenue=("revenue", "sum"))
    .assign(avg_order=lambda d: (d["revenue"] / d["orders"]).round(2))
    .sort_values("revenue", ascending=False)
    .head(10)
    .reset_index(drop=True)
)
print(report)

Why chain? No intermediate variables to keep track of, each step reads top-to-bottom in execution order, and the original DataFrame is never mutated.

9. Performance Tips

import time

big = pd.DataFrame({"a": np.random.randn(500_000), "b": np.random.randn(500_000)})

# SLOW — row-wise apply
t0 = time.time()
_ = big.apply(lambda r: r["a"] + r["b"], axis=1)
slow = time.time() - t0

# FAST — vectorised
t0 = time.time()
_ = big["a"] + big["b"]
fast = time.time() - t0

print(f"apply(axis=1): {slow:.4f}s")
print(f"Vectorised:    {fast:.4f}s  ({slow/fast:.0f}x faster)")

10. Exporting the Results

with pd.ExcelWriter("sales_analysis.xlsx", engine="openpyxl") as writer:
    clean.to_excel(writer, sheet_name="Clean Data", index=False)
    cat_totals.to_excel(writer, sheet_name="Category Summary", index=False)
    monthly.to_excel(writer, sheet_name="Monthly Trend")
    rfm.head(50).to_excel(writer, sheet_name="RFM Segments")
    pivot.to_excel(writer, sheet_name="Pivot")

print("Multi-sheet analysis workbook written.")

The next two lessons turn these tables into charts with Matplotlib and Seaborn.