Pandas Advanced: merge, pivot, apply, and Time Series
The difference between a junior and a senior data engineer is
.merge() semantics — knowing why your row count exploded after a "harmless" join. Pivot tables, groupby+apply, and time-series resampling are the bread and butter of every analytics team at Airbnb, Stripe, and Spotify. This is where Pandas stops being a CSV reader and starts being an analytical engine.Learning Objectives
After this lesson, you will be able to:
- Merge and join DataFrames correctly, understanding inner/left/right/outer semantics and diagnosing row-count explosions
- Apply functions across rows and columns using apply() and groupby.apply(), and know when to replace them with vectorized operations
- Create pivot tables with pd.pivot_table() using multiple aggregations and margins, and reverse them with pd.melt()
- Handle time series data with DatetimeIndex, resample() for frequency conversion, rolling windows, and shift()-based lag features
- Recognize and fix the SettingWithCopyWarning by using .loc[] or .copy() correctly
- Chain pandas operations for readable, production-quality EDA pipelines
Prerequisites
Before You Start
What Do You Think?
You run: users.merge(orders, on='user_id', how='inner'). The users table has 1,000 rows. The orders table has 5,000 rows (multiple orders per user). What is the shape of the result?
#Merging DataFrames
pd.merge() is the pandas equivalent of SQL JOIN. Understanding the four join types is essential because choosing the wrong one silently corrupts your dataset.pythonrunnable cell
import pandas as pd
# Sample data: users and their transactions
users = pd.DataFrame({
"user_id": [101, 102, 103, 104],
"name": ["Alice", "Bob", "Carol", "Dave"],
"tier": ["premium", "free", "premium", "free"],
})
orders = pd.DataFrame({
"order_id": [1, 2, 3, 4, 5, 6],
"user_id": [101, 101, 102, 103, 105, 105], # user 104 never ordered
"amount": [25.0, 89.5, 12.0, 45.0, 67.0, 33.5], # user 105 not in users
})#The Four Join Types
pythonreference · read-only
# INNER JOIN: only rows where user_id exists in BOTH tables
inner = pd.merge(users, orders, on="user_id", how="inner")
# Result: 4 rows (Alice×2, Bob×1, Carol×1 — Dave excluded, user 105 excluded)
# LEFT JOIN: all rows from users, matching orders where available
left = pd.merge(users, orders, on="user_id", how="left")
# Result: 5 rows (Alice×2, Bob×1, Carol×1, Dave×1 with NaN order columns)
# RIGHT JOIN: all rows from orders, matching users where available
right = pd.merge(users, orders, on="user_id", how="right")
# Result: 6 rows (orders for 101, 102, 103, 105×2 with NaN user columns)
# OUTER JOIN: all rows from both tables
outer = pd.merge(users, orders, on="user_id", how="outer")
# Result: 7 rows (Dave has NaN orders, user 105 has NaN user info)SQL equivalents
pandas how= | SQL |
|---|---|
inner | INNER JOIN |
left | LEFT JOIN |
right | RIGHT JOIN |
outer | FULL OUTER JOIN |
#Joining on Different Column Names
pythonreference · read-only
# When the join key has different names in each table
products = pd.DataFrame({"product_id": [1, 2, 3], "name": ["Widget", "Gadget", "Doohickey"]})
order_items = pd.DataFrame({"item_id": [1, 1, 2], "qty": [2, 1, 3]})
merged = pd.merge(products, order_items, left_on="product_id", right_on="item_id")#Joining on Index
pythonreference · read-only
# When the join key is the DataFrame index
users_indexed = users.set_index("user_id")
orders_with_user = orders.set_index("user_id")
result = users_indexed.join(orders_with_user, how="left")
# .join() defaults to joining on indexWhat Do You Think?
users has 4 rows and orders has 6. A LEFT join on user_id gives 5 rows. How does keeping 'all 4 users' produce 5?
#Concatenation
pd.concat() stacks DataFrames, either vertically (new rows) or horizontally (new columns).pythonreference · read-only
# Vertical concatenation: same columns, new rows
# Common use case: combine monthly data files
jan_data = pd.DataFrame({"user_id": [1, 2], "purchases": [5, 3], "month": ["Jan", "Jan"]})
feb_data = pd.DataFrame({"user_id": [2, 3], "purchases": [4, 7], "month": ["Feb", "Feb"]})
all_data = pd.concat([jan_data, feb_data], ignore_index=True)
# ignore_index=True resets the row index from 0 (otherwise you get [0,1,0,1])
# Result: 4 rows
# Horizontal concatenation: same rows, new columns
features_a = pd.DataFrame({"feature_1": [0.1, 0.5], "feature_2": [1.2, 0.8]})
features_b = pd.DataFrame({"feature_3": [3.1, 2.7], "feature_4": [0.3, 0.9]})
all_features = pd.concat([features_a, features_b], axis=1)
# Result: 2 rows, 4 columnsconcat vs merge
| Operation | Use When |
|---|---|
pd.concat(axis=0) | Stacking DataFrames with the same columns (e.g., monthly exports) |
pd.concat(axis=1) | Side-by-side join by position (same number of rows, aligned index) |
pd.merge() | Joining on a key column (SQL-style, handles different row counts) |
Quick check
`pd.concat([jan_data, feb_data])` without `ignore_index=True` gives row labels [0, 1, 0, 1]. Why does that matter later?
#apply() and Vectorized Operations
#apply() Along Rows and Columns
pythonreference · read-only
df = pd.DataFrame({
"math": [85, 72, 91, 68],
"english": [78, 88, 76, 95],
"science": [92, 81, 85, 73],
})
# Apply a function to each COLUMN (axis=0, the default)
col_means = df.apply("mean", axis=0)
# math 79.0
# english 84.25
# science 82.75
# Apply a function to each ROW (axis=1)
# axis=1 passes each row as a Series to the function
df["total_score"] = df.apply(lambda row: row.sum(), axis=1)
df["best_subject"] = df.apply(lambda row: row.idxmax(), axis=1)
# total_score: [255, 241, 252, 236]
# best_subject: ['science', 'english', 'math', 'english']
# Custom function on rows
def grade(row: pd.Series) -> str:
avg = row[["math", "english", "science"]].mean()
if avg >= 90:
return "A"
elif avg >= 80:
return "B"
elif avg >= 70:
return "C"
return "D"
df["grade"] = df.apply(grade, axis=1)#apply() with Lambda on Columns
pythonreference · read-only
# Transform a single column
df["math_scaled"] = df["math"].apply(lambda x: (x - 70) / 30)
# Equivalent (and faster) vectorized version:
df["math_scaled"] = (df["math"] - 70) / 30#When to Use apply() vs Vectorized Operations
pythonreference · read-only
import time
n = 1_000_000
df_large = pd.DataFrame({"value": range(n)})
# Method 1: apply() — interpreted Python, slow
start = time.time()
result1 = df_large["value"].apply(lambda x: x ** 2)
apply_time = time.time() - start
# Method 2: vectorized — C-level NumPy, fast
start = time.time()
result2 = df_large["value"] ** 2
vec_time = time.time() - start
print(f"apply(): {apply_time:.3f}s")
print(f"vectorized: {vec_time:.3f}s")
# apply(): ~0.5s
# vectorized: ~0.004s — 100x fasterRule of thumb
- Use vectorized operations whenever possible:
df["col"] + 1,df["col"].str.upper(),df["col"].dt.year - Use apply() when you need: complex multi-column logic per row, conditional logic that cannot be vectorized, or calling a function that does not have a vectorized equivalent
pythonreference · read-only
# Use apply() for complex row logic (no simple vectorized equivalent)
def compute_risk_score(row: pd.Series) -> float:
base = row["purchase_count"] * 0.3
if row["is_new_user"]:
base *= 0.5
if row["days_since_last_login"] > 30:
base *= 0.8
return round(base, 2)
df["risk_score"] = df.apply(compute_risk_score, axis=1)What Do You Think?
df['value'] ** 2 beat df['value'].apply(lambda x: x ** 2) by about 100x on a million rows. What is apply() actually spending that time on?
#groupby.apply(): Custom Aggregation per Group
groupby.apply() applies an arbitrary function to each group and concatenates the results. It is the escape hatch when the built-in aggregation functions (sum, mean, count) are not expressive enough.pythonrunnable cell
import pandas as pd
import numpy as np
# Sales data: multiple salespeople, multiple regions
sales = pd.DataFrame({
"salesperson": ["Alice", "Alice", "Alice", "Bob", "Bob", "Carol", "Carol", "Carol"],
"region": ["North", "South", "East", "North", "South", "North", "East", "South"],
"revenue": [1200, 950, 800, 1100, 700, 900, 1050, 850],
"quota": [1000, 900, 850, 1000, 800, 950, 1000, 800],
})
# Standard groupby.agg: one scalar per group
per_person = sales.groupby("salesperson")["revenue"].agg(["sum", "mean", "count"])
print(per_person)
# groupby.apply: arbitrary function that receives the GROUP as a DataFrame
def quota_attainment(group: pd.DataFrame) -> pd.Series:
"""Compute quota attainment stats for a group."""
total_revenue = group["revenue"].sum()
total_quota = group["quota"].sum()
attainment = total_revenue / total_quota
beat_quota = (group["revenue"] >= group["quota"]).sum()
return pd.Series({
"total_revenue": total_revenue,
"total_quota": total_quota,
"attainment_pct": round(attainment * 100, 1),
"regions_beat": beat_quota,
})
result = sales.groupby("salesperson").apply(quota_attainment, include_groups=False)
print(result)
# salesperson total_revenue total_quota attainment_pct regions_beat
# Alice 2950 2750 107.3 2
# Bob 1800 1800 100.0 1
# Carol 2800 2750 101.8 2When to use groupby.apply() vs groupby.agg()
| Use case | Recommended |
|---|---|
| Single scalar per group (sum, mean, max) | groupby.agg() — faster, vectorized |
| Multiple scalars with complex logic | groupby.apply() returning a pd.Series |
| DataFrame → DataFrame transformation per group | groupby.apply() returning a pd.DataFrame |
| Within-group ranking, normalization, or rolling | groupby.apply() or groupby.transform() |
pythonreference · read-only
# groupby.transform() — same as apply but preserves the original DataFrame shape
# Useful for creating group-level features without losing rows
# Normalize each salesperson's revenue relative to their own mean
sales["revenue_normalized"] = sales.groupby("salesperson")["revenue"].transform(
lambda x: (x - x.mean()) / x.std()
)
# Each row now has a z-score relative to that salesperson's average — not the global averageQuick check
`sales` has 8 rows and 3 salespeople. You group by salesperson and compute a z-score. How many rows come back from `transform()`, and how many from `agg()`?
#Pivot Tables and Melt
#pd.pivot_table(): Aggregate by Two Dimensions
A pivot table summarizes data by two categorical dimensions. The pandas equivalent of an Excel PivotTable.
pythonreference · read-only
# Sales data: region, product, and amount
sales = pd.DataFrame({
"region": ["North", "North", "South", "South", "East", "East", "North"],
"product": ["Widget", "Gadget", "Widget", "Widget", "Gadget", "Widget", "Gadget"],
"revenue": [1200, 800, 950, 1100, 700, 850, 600],
"units": [12, 8, 9, 11, 7, 8, 6],
})
# Total revenue by region (rows) × product (columns)
pivot = pd.pivot_table(
sales,
values="revenue",
index="region",
columns="product",
aggfunc="sum",
fill_value=0, # replace NaN with 0 where no data
)
# product Gadget Widget
# region
# East 700 850
# North 1400 1200
# South 0 2050
# Multiple aggregations
pivot_multi = pd.pivot_table(
sales,
values=["revenue", "units"],
index="region",
columns="product",
aggfunc={"revenue": "sum", "units": "mean"},
)
# Named aggregation with margins (totals row/column)
pivot_with_totals = pd.pivot_table(
sales,
values="revenue",
index="region",
columns="product",
aggfunc="sum",
margins=True, # adds "All" row and column
margins_name="Total",
)#pd.melt(): Wide to Long (Unpivot)
melt() is the inverse of pivot: it turns multiple columns into rows. Essential for reshaping wide-format datasets into the long format required by seaborn, many ML pipelines, and time-series models.pythonreference · read-only
# Wide format: one row per student, multiple score columns
wide_df = pd.DataFrame({
"student": ["Alice", "Bob", "Carol"],
"math": [85, 72, 91],
"english": [78, 88, 76],
"science": [92, 81, 85],
})
# Long format: one row per (student, subject) pair
long_df = pd.melt(
wide_df,
id_vars=["student"], # columns to keep as-is
value_vars=["math", "english", "science"], # columns to unpivot
var_name="subject", # name for the new "column names" column
value_name="score", # name for the new "values" column
)
# student subject score
# 0 Alice math 85
# 1 Bob math 72
# 2 Carol math 91
# 3 Alice english 78
# ...
# Common use case: seaborn expects long format for multi-category plots
import seaborn as sns
sns.barplot(data=long_df, x="subject", y="score", hue="student")Quick check
`wide_df` is 3 rows × 4 columns (student, math, english, science). After melting with `id_vars=['student']` and those three subjects, what shape is `long_df`?
#Time Series
Time series is the most common data structure in production ML — user behavior logs, sales data, sensor readings, financial data. pandas has first-class time series support.
#Parsing and Creating Datetime Columns
pythonrunnable cell
import pandas as pd
# Parse date strings to datetime
df = pd.DataFrame({
"date_str": ["2024-01-15", "2024-02-20", "2024-03-10", "2024-04-05"],
"sales": [1200, 950, 1800, 1350],
})
df["date"] = pd.to_datetime(df["date_str"])
# Handles many formats automatically: "Jan 15, 2024", "15/01/2024", "2024-01-15 14:30:00"
# Creating a date range
date_range = pd.date_range(start="2024-01-01", end="2024-12-31", freq="D") # daily
date_range = pd.date_range(start="2024-01-01", periods=52, freq="W") # 52 weeks
date_range = pd.date_range(start="2024-01-01", periods=12, freq="MS") # 12 months#The .dt Accessor
Once a column is datetime, the
.dt accessor exposes all date/time components:pythonreference · read-only
df["date"] = pd.to_datetime(df["date_str"])
df["year"] = df["date"].dt.year
df["month"] = df["date"].dt.month # 1-12
df["day"] = df["date"].dt.day
df["day_of_week"] = df["date"].dt.dayofweek # 0=Monday, 6=Sunday
df["day_name"] = df["date"].dt.day_name() # "Monday", "Tuesday", ...
df["is_weekend"] = df["date"].dt.dayofweek >= 5
df["quarter"] = df["date"].dt.quarter # 1-4
df["week_of_year"] = df["date"].dt.isocalendar().weekThese are standard ML features for any time-aware model.
#DatetimeIndex and Resampling
For time series operations, set the date column as the index:
pythonreference · read-only
# Daily sales data
daily_sales = pd.DataFrame({
"date": pd.date_range("2024-01-01", periods=365, freq="D"),
"sales": [100 + i % 30 + (i % 7) * 10 for i in range(365)],
})
daily_sales = daily_sales.set_index("date")
# Resample: aggregate to a lower frequency
weekly_sales = daily_sales.resample("W").sum() # weekly totals
monthly_sales = daily_sales.resample("ME").mean() # monthly averages
quarterly_max = daily_sales.resample("QE").max() # quarterly max
# Upsample: go to a higher frequency (backfill or interpolate)
hourly = daily_sales.resample("h").ffill() # fill forward
# Common frequency strings (pandas 2.2+): D (day), W (week), ME (month-end),
# QE (quarter-end), YE (year-end), h (hour), min (minute), s (second).
# Note: older codes M / Q / Y / T / S are deprecated in 2.2 — prefer the new ones.#Rolling Windows: Moving Averages
Rolling windows are essential for smoothing noisy time series and creating lag features for ML:
pythonreference · read-only
# 7-day rolling average (smooths daily noise)
daily_sales["sales_7d_avg"] = daily_sales["sales"].rolling(window=7).mean()
# 30-day rolling sum
daily_sales["sales_30d_sum"] = daily_sales["sales"].rolling(window=30).sum()
# Rolling standard deviation (volatility signal)
daily_sales["sales_7d_std"] = daily_sales["sales"].rolling(window=7).std()
# Exponential weighted moving average (more weight to recent values)
daily_sales["sales_ewm"] = daily_sales["sales"].ewm(span=7).mean()
# Note: first (window-1) rows will be NaN — rolling needs a full window
print(daily_sales.head(10))
# sales_7d_avg is NaN for rows 0-5, first valid value at row 6#Lag Features
Lag features are "what was the value N days ago?" — essential inputs for forecasting models:
pythonreference · read-only
# Create lag features for a user engagement dataset
df = pd.DataFrame({
"date": pd.date_range("2024-01-01", periods=30),
"user_id": [101] * 30,
"sessions": [5, 3, 7, 4, 6, 2, 8, 5, 3, 7, 4, 6, 2, 8, 5,
3, 7, 4, 6, 2, 8, 5, 3, 7, 4, 6, 2, 8, 5, 3],
})
df = df.set_index("date")
# Lag 1: yesterday's value
df["sessions_lag1"] = df["sessions"].shift(1)
# Lag 7: same day last week
df["sessions_lag7"] = df["sessions"].shift(7)
# Difference from yesterday (momentum feature)
df["sessions_diff1"] = df["sessions"].diff(1)
# Rolling + lag (30-day total from yesterday, avoids target leakage)
df["sessions_30d_sum_lag1"] = df["sessions"].shift(1).rolling(30).sum()
print(df.head(10))What Do You Think?
Sessions run 5, 3, 7, 4, 6. What do shift(1) and diff(1) give for the third row (7)?
#Practical Time Series EDA
pythonrunnable cell
# Full time series analysis workflow
import pandas as pd
import matplotlib.pyplot as plt
# Load and prepare
df = pd.read_csv("daily_orders.csv", parse_dates=["date"])
df = df.set_index("date").sort_index()
# Basic checks
print(f"Date range: {df.index.min()} to {df.index.max()}")
print(f"Missing dates: {pd.date_range(df.index.min(), df.index.max()).difference(df.index)}")
# Trend: monthly aggregation
monthly = df["orders"].resample("ME").sum()
# Seasonality: average by day of week
df["dow"] = df.index.dayofweek
dow_avg = df.groupby("dow")["orders"].mean()
print("Day of week averages:", dow_avg.to_dict())
# Smoothed trend
df["orders_7d"] = df["orders"].rolling(7).mean()
df["orders_30d"] = df["orders"].rolling(30).mean()#Full Workflow: EDA with merge, pivot, rolling, and lag
pythonplayground.py · Pyodide
Tests · Verify the left join gives >= 100 rows (every user appears). Check that your pivot table has column names matching the 4 categories. Verify revenue_lag1 is NaN for the first row and equals daily_revenue[0] for the second row.
#Quick Check
Quick Check1 / 4
You merge a users table (1,000 rows, unique user_id) with a transactions table (10,000 rows, multiple transactions per user) using how='left'. What determines the number of output rows?
Interactive Lab
Explore, filter, and visualize DataFrames interactively — practice the merge, pivot, and groupby operations from this lesson
pandas advanced operations (merge, pivot, apply, time series) are the daily tools of ML engineering. Every training dataset you build will use at least three of these. Next: Python for Machine Learning — connect all your Python and pandas skills to scikit-learn and the full ML workflow.