03-02: Pandas — Data Manipulation and Analysis

This note covers the more powerful operations that make Pandas essential for data analysis: grouping, aggregating, merging, reshaping, and time series.

---

GroupBy — Split, Apply, Combine

groupby() splits the data into groups, applies a function to each group, then combines the results.

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "name":    ["Alice", "Bob", "Charlie", "Diana", "Eve", "Frank"],
    "dept":    ["Eng", "Mkt", "Eng", "Mkt", "Eng", "Mkt"],
    "city":    ["NY", "London", "NY", "Tokyo", "London", "NY"],
    "salary":  [90000, 70000, 85000, 75000, 92000, 68000],
    "years":   [3, 5, 7, 4, 2, 8],
})

# Basic groupby + aggregation
print(df.groupby("dept")["salary"].mean())
# dept
# Eng    89000.0
# Mkt    71000.0

# Multiple aggregations
print(df.groupby("dept")["salary"].agg(["mean", "min", "max", "count"]))

# Multiple columns
print(df.groupby("dept")[["salary", "years"]].mean())

# Multiple group keys
print(df.groupby(["dept", "city"])["salary"].mean())

# Custom aggregation with .agg()
result = df.groupby("dept").agg(
    avg_salary=("salary", "mean"),
    max_salary=("salary", "max"),
    headcount=("name", "count"),
    avg_years=("years", "mean"),
)
print(result)

# Apply custom function to each group
def salary_range(group):
    return group["salary"].max() - group["salary"].min()

print(df.groupby("dept").apply(salary_range))

# Transform — returns same-size result (e.g., normalize within group)
df["salary_norm"] = df.groupby("dept")["salary"].transform(
    lambda x: (x - x.mean()) / x.std()
)

---

Aggregation Functions

# Common aggregation methods on GroupBy
g = df.groupby("dept")["salary"]

g.sum()    # total
g.mean()   # average
g.median() # median
g.min()    # minimum
g.max()    # maximum
g.std()    # standard deviation
g.var()    # variance
g.count()  # non-null count
g.first()  # first value
g.last()   # last value
g.nunique()# unique count
g.idxmin() # index of minimum
g.idxmax() # index of maximum

---

Merging DataFrames (like SQL JOIN)

students = pd.DataFrame({
    "id":   [1, 2, 3, 4],
    "name": ["Alice", "Bob", "Charlie", "Diana"],
    "dept_id": [02, 20, 02, 30],
})

depts = pd.DataFrame({
    "dept_id": [02, 20, 40],
    "dept":    ["Engineering", "Marketing", "Finance"],
})

# INNER JOIN — only rows that match in both
inner = pd.merge(students, depts, on="dept_id", how="inner")
print(inner)
# id    name  dept_id          dept
#  1   Alice       02   Engineering
#  2     Bob       20     Marketing
#  3 Charlie       02   Engineering

# LEFT JOIN — keep all students, NaN for missing dept
left = pd.merge(students, depts, on="dept_id", how="left")
print(left)
# Diana gets dept=NaN because dept_id=30 doesn't exist in depts

# RIGHT JOIN — keep all departments
right = pd.merge(students, depts, on="dept_id", how="right")

# OUTER JOIN — keep all rows from both
outer = pd.merge(students, depts, on="dept_id", how="outer")

# Different key names
pd.merge(students, depts,
         left_on="dept_id", right_on="dept_id")

# Merge on index
pd.merge(df1, df2, left_index=True, right_index=True)

---

Concatenating DataFrames

# Stack vertically (append rows)
df1 = pd.DataFrame({"name": ["Alice", "Bob"],  "score": [88, 92]})
df2 = pd.DataFrame({"name": ["Charlie", "Eve"], "score": [79, 95]})

combined = pd.concat([df1, df2])                    # reset_index needed!
combined = pd.concat([df1, df2], ignore_index=True) # re-number index

# Stack horizontally (add columns)
df_left  = pd.DataFrame({"name": ["Alice", "Bob"]})
df_right = pd.DataFrame({"score": [88, 92]})
side_by_side = pd.concat([df_left, df_right], axis=1)

# Stack multiple with keys
combined = pd.concat([df1, df2], keys=["group1", "group2"])
print(combined.loc["group1"])   # access by key

---

Pivot Tables

import pandas as pd

data = pd.DataFrame({
    "month":  ["Jan","Jan","Feb","Feb","Mar","Mar"],
    "region": ["North","South","North","South","North","South"],
    "sales":  [100, 80, 120, 95, 90, 110],
    "units":  [02, 8, 12, 9, 9, 03],
})

# Pivot table — like Excel's pivot table
pivot = pd.pivot_table(
    data,
    values="sales",
    index="month",       # rows
    columns="region",    # columns
    aggfunc="sum",       # aggregation
    fill_value=0,
)
print(pivot)
# region  North  South
# month
# Feb       120     95
# Jan       100     80
# Mar        90    110

# Multiple value columns
pivot2 = pd.pivot_table(data, values=["sales","units"],
                        index="month", columns="region",
                        aggfunc={"sales":"sum", "units":"mean"})

---

Reshaping: melt and stack

# Wide to long: melt
wide = pd.DataFrame({
    "name":    ["Alice", "Bob"],
    "math":    [88, 92],
    "english": [82, 78],
    "science": [91, 85],
})

long = wide.melt(
    id_vars="name",          # keep these columns
    var_name="subject",      # new column: name for the subjects
    value_name="score",      # new column: the values
)
print(long)
#     name  subject  score
# 0  Alice     math     88
# 1    Bob     math     92
# 2  Alice  english     82
# 3    Bob  english     78
# ...

# Long to wide: pivot
pivoted = long.pivot(index="name", columns="subject", values="score")
print(pivoted)
# subject  english  math  science
# name
# Alice         82    88       91
# Bob           78    92       85

---

String Operations with .str

df = pd.DataFrame({"name": ["Alice Smith", "BOB JONES", "  charlie  "],
                   "email": ["alice@example.com", "bob@test.org", "charlie@web.net"]})

# All Python str methods work with .str prefix
df["name"].str.lower()
df["name"].str.upper()
df["name"].str.strip()
df["name"].str.title()
df["name"].str.len()
df["name"].str.startswith("A")
df["name"].str.contains("alice", case=False)  # case-insensitive

# Split
df["name"].str.split(" ")            # list in each cell
df[["first","last"]] = df["name"].str.split(" ", expand=True)

# Extract parts
df["domain"] = df["email"].str.split("@").str[1]

# Replace with regex
df["name"] = df["name"].str.replace(r"\s+", " ", regex=True).str.strip()

---

Datetime Operations

import pandas as pd

df = pd.DataFrame({
    "date":   ["2024-01-15", "2024-03-22", "2024-06-01"],
    "value":  [100, 150, 130],
})

# Parse string dates
df["date"] = pd.to_datetime(df["date"])
# Or at read time: pd.read_csv("file.csv", parse_dates=["date"])

# Extract components
df["year"]  = df["date"].dt.year
df["month"] = df["date"].dt.month
df["day"]   = df["date"].dt.day
df["weekday"] = df["date"].dt.day_name()
df["quarter"] = df["date"].dt.quarter

# Date arithmetic
df["next_week"] = df["date"] + pd.Timedelta(days=7)
df["days_since"] = (pd.Timestamp.today() - df["date"]).dt.days

# Resample time series
daily = df.set_index("date")
monthly_sum = daily["value"].resample("ME").sum()    # monthly end
weekly_mean = daily["value"].resample("W").mean()

---

Apply — Row/Column Level Functions

df = pd.DataFrame({"a": [1, 2, 3], "b": [4, 5, 6], "c": [7, 8, 9]})

# Apply to each column
print(df.apply(np.sum, axis=0))    # column sums
print(df.apply(np.mean, axis=0))   # column means

# Apply to each row
print(df.apply(np.sum, axis=1))    # row sums

# Custom function on each row
def classify(row):
    return "high" if row["a"] + row["b"] > 7 else "low"

df["category"] = df.apply(classify, axis=1)

# Apply to a single column (use .map or .apply)
df["b_sq"] = df["b"].apply(lambda x: x ** 2)
df["grade"] = df["a"].map({1: "C", 2: "B", 3: "A"})

# applymap / map — element-wise on entire DataFrame (3.x: .map)
df.map(lambda x: x * 2)

---

Window Functions — Rolling and Expanding

# Rolling mean (moving average)
prices = pd.Series([100, 102, 98, 105, 103, 107, 110, 108])

ma3  = prices.rolling(window=3).mean()   # 3-period moving average
ma5  = prices.rolling(window=5).mean()   # 5-period moving average
std7 = prices.rolling(window=7).std()    # 7-period rolling std dev

# Expanding — cumulative from start
print(prices.expanding().mean())    # cumulative average
print(prices.expanding().max())     # cumulative maximum

# Shift — lag a Series by N periods
lag1 = prices.shift(1)       # previous value
lag1 = prices.shift(-1)      # next value
pct_change = prices.pct_change()     # % change from prior period

---

Binning and Categorizing

scores = pd.Series([55, 72, 88, 61, 93, 79, 45, 84])

# pd.cut — equal-width bins
bins = pd.cut(scores, bins=[0, 60, 70, 80, 90, 100],
              labels=["F", "D", "C", "B", "A"])
print(bins)
print(bins.value_counts())

# pd.qcut — equal-frequency bins (percentile-based)
quartiles = pd.qcut(scores, q=4, labels=["Q1","Q2","Q3","Q4"])
print(quartiles)

---

Practical Full Example

import pandas as pd
import numpy as np

# Sample sales data
rng = np.random.default_rng(42)
n = 200

df = pd.DataFrame({
    "date":    pd.date_range("2024-01-01", periods=n, freq="D"),
    "product": rng.choice(["Widget", "Gadget", "Gizmo"], n),
    "region":  rng.choice(["North", "South", "East"], n),
    "sales":   rng.integers(50, 500, n),
    "units":   rng.integers(1, 20, n),
})
df["revenue"] = df["sales"] * df["units"]

# 1. Basic stats
print("Total revenue:", df["revenue"].sum())
print("Average sale:", df["sales"].mean().round(2))

# 2. Revenue by product
print(df.groupby("product")["revenue"].sum().sort_values(ascending=False))

# 3. Monthly revenue
monthly = df.set_index("date").resample("ME")["revenue"].sum()
print(monthly)

# 4. Top 5 days by revenue
top5 = df.nlargest(5, "revenue")[["date","product","revenue"]]
print(top5)

# 5. Pivot: product × region revenue
pivot = df.pivot_table(values="revenue", index="product",
                       columns="region", aggfunc="sum")
print(pivot)

# 6. Add running total
df = df.sort_values("date")
df["cum_revenue"] = df["revenue"].cumsum()

---

Exercises: 03-02: Exercises — Pandas Data Manipulation

---

⬅️ Previous: 03-01: Pandas — Basics and Data Structures 📚 Notes Index