RugvAI Labs

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

Review

Why

Why This Matters

Every real-world ML dataset requires joining tables -- raw data never comes in a single flat file. User data lives in one table, transaction data in another, session logs in a third. The intro numpy-pandas lesson teaches iloc and groupby — this lesson teaches the join operations that show up in nearly every real EDA notebook
pivot and melt transform data between formats required by different ML algorithms -- some models need wide format (one row per user, features as columns), others need long format (one row per measurement). Knowing how to reshape data is the difference between being blocked by your dataset and controlling it
Time series features are in every ML problem -- even if your model is not a time series model, features like "purchases in the last 30 days", "days since last login", and "7-day rolling average engagement" are time series aggregates. They require proper datetime handling to compute correctly

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?

Merge / Join Workflow

📦Left Table (e.g., users)

📦Join Key (e.g., user_id)

📦Right Table (e.g., orders)

📦pd.merge() how=inner|left|right|outer

📦Merged Result (combined columns)

📦Validate df.shape before & after

📦

#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 index

Watch Out

Common Mistake

Many-to-many merges silently multiply rows. If BOTH tables have duplicate values in the join column, the result is a Cartesian product of the matching rows. Two tables each with 3 rows for user_id=101 produce 9 rows in the merge result — not 3, not 6. Always check df[join_col].is_unique before merging, and always check df.shape before and after.

pythonreference · read-only

# Defensive merge pattern
print(f"Before merge: users={len(users)}, orders={len(orders)}")
result = pd.merge(users, orders, on="user_id", how="left")
print(f"After merge: {len(result)} rows")
# If left join result > len(users), you have duplicate keys in orders

#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 columns

concat 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)

Watch Out

Watch out

Fix: Use .loc[] for explicit assignment or .copy() when creating a sub-DataFrame you intend to modify.

Getting SettingWithCopyWarning when modifying a DataFrame slice

When you write `df2 = df[df['col'] > 5]` and then `df2['new_col'] = 10`, pandas warns you because df2 might be a view (pointing to the same memory as df) or a copy. If it is a view, your assignment modifies the original df. If it is a copy, it does not. The behavior is unpredictable.

```python

WRONG: ambiguous — might modify df, might not

subset = df[df['tier'] == 'premium'] subset['discount'] = 0.1 # SettingWithCopyWarning!

RIGHT: explicit copy — never modifies original

subset = df[df['tier'] == 'premium'].copy() subset['discount'] = 0.1 # safe, independent DataFrame

RIGHT: modify original explicitly with .loc

df.loc[df['tier'] == 'premium', 'discount'] = 0.1 # clear intent ```

In pandas 3.0+, copy-on-write is the default, but for now always be explicit.

#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

Intuition

When to use apply() vs vectorized operations -- the decision framework.

Think of it like this: vectorized operations are a factory assembly line, and apply() is a craftsman working one piece at a time. The assembly line (vectorized) is 100x faster because it processes everything in bulk at the C/NumPy level. The craftsman (apply) is flexible and can handle complex, conditional logic that the assembly line cannot.

Use vectorized when: your operation can be expressed as simple math (df['col'] * 2 + 1), string methods (df['name'].str.upper()), datetime extraction (df['date'].dt.year), or conditional assignment (np.where(df['col'] > 0, 'positive', 'negative')).

Use apply() when: you need complex multi-column logic per row (if-elif chains across several columns), you are calling an external function that has no vectorized equivalent, or you are prototyping and readability matters more than speed. But always ask: "Can I rewrite this with np.where or pd.cut?" If yes, do that instead -- it will be orders of magnitude faster on large DataFrames.

#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 faster

Rule 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)

#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             2

When 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 average

#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")

#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().week

These 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))

Watch Out

Common Mistake

Target leakage in rolling features. When computing rolling features for training, make sure you are not including the current day's value in a feature that should only use past data.

pythonreference · read-only

# WRONG: rolling(7) includes today's sessions in the "past 7 days" feature
df["sessions_7d"] = df["sessions"].rolling(7).mean()

# RIGHT: shift(1) first, then roll — yesterday and 6 days before
df["sessions_7d_lag"] = df["sessions"].shift(1).rolling(7).mean()

#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

import pandas as pd
import numpy as np

# ── Simulated E-commerce Dataset ─────────────────────────────────────────────
np.random.seed(42)

# Users table
users = pd.DataFrame({
  "user_id": range(1, 101),
  "signup_date": pd.date_range("2023-01-01", periods=100, freq="3D"),
  "tier": np.random.choice(["free", "pro", "enterprise"], 100, p=[0.6, 0.3, 0.1]),
  "country": np.random.choice(["US", "UK", "CA", "AU"], 100, p=[0.5, 0.2, 0.2, 0.1]),
})

# Orders table (multiple orders per user)
n_orders = 500
orders = pd.DataFrame({
  "order_id": range(1, n_orders + 1),
  "user_id": np.random.randint(1, 101, n_orders),
  "order_date": pd.date_range("2023-06-01", periods=n_orders, freq="4h"),
  "amount": np.random.exponential(scale=85, size=n_orders).round(2),
  "category": np.random.choice(["electronics", "clothing", "food", "books"], n_orders),
})

print("Users shape:", users.shape)
print("Orders shape:", orders.shape)
print("\nUsers sample:")
print(users.head(3).to_string(index=False))
print("\nOrders sample:")
print(orders.head(3).to_string(index=False))

# ── TASK 1: Join users and orders ─────────────────────────────────────────────
# Left join: keep all users, attach their orders (NaN for users with no orders)
# Check: how many users never placed an order?

user_orders = None  # TODO: pd.merge(users, orders, on="user_id", how="left")
# print(f"\nUsers with no orders: {user_orders['order_id'].isna().sum()}")

# ── TASK 2: User-level aggregates ─────────────────────────────────────────────
# For each user, compute: total_orders, total_spend, avg_order_value
# Hint: groupby + agg on the orders table, then merge back to users

user_stats = None  # TODO
# Expected columns: user_id, total_orders, total_spend, avg_order_value

# ── TASK 3: Monthly revenue pivot table ───────────────────────────────────────
# Rows: month (resample orders to monthly)
# Columns: category
# Values: sum of amount

orders["month"] = orders["order_date"].dt.to_period("M").astype(str)
monthly_pivot = None  # TODO: pd.pivot_table(...)
# print("\nMonthly Revenue by Category:")
# print(monthly_pivot.head())

# ── TASK 4: Daily revenue with rolling 7-day average ─────────────────────────
daily_revenue = (
  orders.set_index("order_date")["amount"]
  .resample("D")
  .sum()
  .rename("daily_revenue")
  .to_frame()
)

# Add rolling 7-day average and 7-day lag features
daily_revenue["revenue_7d_avg"] = None  # TODO: .rolling(7).mean()
daily_revenue["revenue_lag1"] = None    # TODO: .shift(1)

print("\nDaily Revenue (first 10 rows):")
print(daily_revenue.head(10).to_string())

# ── CHALLENGE: Tier comparison ────────────────────────────────────────────────
# Merge user_stats back to users (once you compute it)
# Then compute average total_spend by tier and country
# Which combination of tier + country has the highest average spend?

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

EDA Explorer

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.

Your Reflection

Continue Learning

Discussion

Pandas Advanced: merge, pivot, apply, and Time Series

After this lesson, you will be able to:

Before You Start

#Merging DataFrames

#The Four Join Types

SQL equivalents

#Joining on Different Column Names

#Joining on Index

#Concatenation

concat vs merge

#apply() and Vectorized Operations

#apply() Along Rows and Columns

#apply() with Lambda on Columns

#When to Use apply() vs Vectorized Operations

Rule of thumb

#groupby.apply(): Custom Aggregation per Group

When to use `groupby.apply()` vs `groupby.agg()`

#Pivot Tables and Melt

#pd.pivot_table(): Aggregate by Two Dimensions

#pd.melt(): Wide to Long (Unpivot)

#Time Series

#Parsing and Creating Datetime Columns

#The .dt Accessor

#DatetimeIndex and Resampling

#Rolling Windows: Moving Averages

#Lag Features

#Practical Time Series EDA

#Full Workflow: EDA with merge, pivot, rolling, and lag

#Quick Check

Deep Dive

Related Lessons

Your Reflection

Continue Learning

Discussion

Pandas Advanced: merge, pivot, apply, and Time Series

#Merging DataFrames

#The Four Join Types

SQL equivalents

#Joining on Different Column Names

#Joining on Index

#Concatenation

concat vs merge

#apply() and Vectorized Operations

#apply() Along Rows and Columns

#apply() with Lambda on Columns

#When to Use apply() vs Vectorized Operations

Rule of thumb

#groupby.apply(): Custom Aggregation per Group

When to use groupby.apply() vs groupby.agg()

#Pivot Tables and Melt

#pd.pivot_table(): Aggregate by Two Dimensions

#pd.melt(): Wide to Long (Unpivot)

#Time Series

#Parsing and Creating Datetime Columns

#The .dt Accessor

#DatetimeIndex and Resampling

#Rolling Windows: Moving Averages

#Lag Features

#Practical Time Series EDA

#Full Workflow: EDA with merge, pivot, rolling, and lag

#Quick Check

Deep Dive

Related Lessons

When to use `groupby.apply()` vs `groupby.agg()`