Table of Contents
- groupby — Split, Apply, Combine
- merge and join — Combining DataFrames
- pivot_table — Reshape Long to Wide
- melt — Reshape Wide to Long
- stack and unstack — Multi-Index Reshaping
- apply — Custom Row/Column Operations
- pipe — Clean Method Chaining
- Real-World Pattern: Complete Data Reshaping Pipeline
- Common Mistakes
- Interview Questions
- Wrapping Up
Our File Formats post covered reading and writing data. Our ETL Patterns post covered pipeline architecture. This post goes deep into pandas itself — the operations you use every day to aggregate, combine, reshape, and transform DataFrames. If you can groupby, merge, pivot, and melt fluently, you can handle any data transformation a pipeline throws at you.
Analogy — A Swiss Army knife. pandas is the Swiss Army knife of Python data engineering. groupby is the blade (cut data into groups and summarize). merge is the screwdriver (join two pieces together). pivot_table is the bottle opener (reshape from long to wide). melt is the can opener (reshape from wide to long). apply is the custom tool (do anything the built-in tools cannot). pipe is the handle that holds them all together in a clean chain. This post teaches you every tool on the knife.
groupby — Split, Apply, Combine
groupby() is the most important pandas operation for data engineers. It splits a DataFrame into groups based on one or more columns, applies a function to each group, and combines the results. This is the pandas equivalent of SQL’s GROUP BY.
Analogy — Sorting mail at a post office. The mail carrier (groupby) sorts letters into bins by zip code (group column). Each bin is processed independently: count letters (count), weigh the bin (sum), find the heaviest letter (max). The results are assembled into a summary report.
Basic groupby with Aggregation
import pandas as pd
df = pd.DataFrame({
"region": ["Ontario", "Quebec", "Ontario", "Alberta", "Quebec", "Ontario"],
"product": ["Widget", "Widget", "Gadget", "Widget", "Gadget", "Widget"],
"amount": [100, 200, 150, 300, 250, 175],
"quantity": [10, 20, 15, 30, 25, 18]
})
# Single aggregation
revenue_by_region = df.groupby("region")["amount"].sum()
# Ontario 425
# Quebec 450
# Alberta 300
# Multiple aggregations on one column
df.groupby("region")["amount"].agg(["sum", "mean", "count", "min", "max"])
# Group by multiple columns
df.groupby(["region", "product"])["amount"].sum()
# Named aggregations (the cleanest syntax -- use this in production)
summary = df.groupby("region").agg(
total_revenue=("amount", "sum"),
avg_order=("amount", "mean"),
total_units=("quantity", "sum"),
order_count=("amount", "count"),
max_order=("amount", "max")
).reset_index()groupby transform — Broadcast Results Back
transform() applies a function to each group but returns a result the same size as the input — one value per row, not per group. This is how you add “group-level” columns without losing row-level detail.
# Add group-level stats as new columns (without losing rows)
df["region_total"] = df.groupby("region")["amount"].transform("sum")
df["region_avg"] = df.groupby("region")["amount"].transform("mean")
df["pct_of_region"] = df["amount"] / df["region_total"] * 100
# Normalize within each group (z-score)
df["amount_zscore"] = df.groupby("region")["amount"].transform(
lambda x: (x - x.mean()) / x.std()
)
# Rank within each group
df["rank_in_region"] = df.groupby("region")["amount"].transform("rank", ascending=False)groupby filter — Keep or Drop Entire Groups
filter() keeps or removes entire groups based on a condition applied to the group.
# Keep only regions with total revenue > 400
high_revenue = df.groupby("region").filter(lambda g: g["amount"].sum() > 400)
# Keep only regions with more than 2 orders
active_regions = df.groupby("region").filter(lambda g: len(g) >= 2)groupby apply — Custom Group Operations
apply() runs any function on each group. Use it when built-in aggregations and transforms are not enough.
# Custom function: return top 2 orders per region
def top_n(group, n=2):
return group.nlargest(n, "amount")
top_orders = df.groupby("region").apply(top_n, n=2).reset_index(drop=True)
# Custom function: compute a derived metric per group
def revenue_concentration(group):
# What % of revenue comes from the top product?
top_product_rev = group.groupby("product")["amount"].sum().max()
total_rev = group["amount"].sum()
return pd.Series({"concentration": top_product_rev / total_rev * 100})
df.groupby("region").apply(revenue_concentration)merge and join — Combining DataFrames
merge() is the pandas equivalent of SQL JOINs. It combines two DataFrames based on shared columns (keys). Every data engineer uses merge daily to join fact tables with dimension tables, combine data from different sources, or enrich records with lookup values.
All Join Types
orders = pd.DataFrame({
"order_id": [1, 2, 3, 4],
"customer_id": [101, 102, 103, 104],
"amount": [100, 200, 150, 300]
})
customers = pd.DataFrame({
"customer_id": [101, 102, 105],
"name": ["Alice", "Bob", "Eve"],
"region": ["Ontario", "Quebec", "Alberta"]
})
# INNER JOIN -- only matching rows from both sides
inner = pd.merge(orders, customers, on="customer_id", how="inner")
# Rows: 101 (Alice), 102 (Bob) -- 103, 104 dropped (no customer match), 105 dropped (no order)
# LEFT JOIN -- all rows from left, matching from right (NaN where no match)
left = pd.merge(orders, customers, on="customer_id", how="left")
# Rows: 101, 102, 103 (NaN name/region), 104 (NaN name/region)
# RIGHT JOIN -- all rows from right, matching from left
right = pd.merge(orders, customers, on="customer_id", how="right")
# Rows: 101, 102, 105 (NaN order_id/amount)
# OUTER JOIN -- all rows from both sides
outer = pd.merge(orders, customers, on="customer_id", how="outer")
# All 5 rows: 101, 102, 103, 104, 105
# CROSS JOIN -- every row from left paired with every row from right
cross = pd.merge(orders, customers, how="cross")
# 4 orders x 3 customers = 12 rowsmerge on Different Column Names
# When key columns have different names
orders = pd.DataFrame({"order_id": [1, 2], "cust_id": [101, 102], "amount": [100, 200]})
customers = pd.DataFrame({"customer_id": [101, 102], "name": ["Alice", "Bob"]})
merged = pd.merge(orders, customers, left_on="cust_id", right_on="customer_id")merge with Suffixes
# When both DataFrames have columns with the same name
jan_sales = pd.DataFrame({"product": ["Widget", "Gadget"], "revenue": [1000, 2000]})
feb_sales = pd.DataFrame({"product": ["Widget", "Gadget"], "revenue": [1200, 1800]})
comparison = pd.merge(jan_sales, feb_sales, on="product", suffixes=("_jan", "_feb"))
comparison["growth"] = comparison["revenue_feb"] - comparison["revenue_jan"]concat — Stacking DataFrames
concat() stacks DataFrames vertically (row-wise) or horizontally (column-wise). Use it to combine data from multiple files, time periods, or sources that share the same schema.
# Vertical stack (row-wise) -- like UNION ALL in SQL
q1 = pd.DataFrame({"product": ["Widget"], "revenue": [1000]})
q2 = pd.DataFrame({"product": ["Widget"], "revenue": [1200]})
q3 = pd.DataFrame({"product": ["Widget"], "revenue": [1100]})
full_year = pd.concat([q1, q2, q3], ignore_index=True)
# Combine files from a directory
from pathlib import Path
files = Path("data/").glob("*.csv")
combined = pd.concat([pd.read_csv(f) for f in files], ignore_index=True)
# Horizontal stack (column-wise)
names = pd.DataFrame({"name": ["Alice", "Bob"]})
ages = pd.DataFrame({"age": [30, 25]})
combined = pd.concat([names, ages], axis=1)pivot_table — Reshape Long to Wide
pivot_table() reshapes data from long format (many rows) to wide format (more columns). It is the pandas equivalent of SQL’s PIVOT or Excel’s PivotTable. Unlike pivot(), pivot_table() handles duplicate entries by aggregating them.
Analogy — A spreadsheet report. Your raw data has one row per transaction (long format). Your manager wants a report with regions as rows, months as columns, and revenue as values (wide format). pivot_table() creates that report.
sales = pd.DataFrame({
"region": ["Ontario", "Ontario", "Quebec", "Quebec", "Ontario", "Quebec"],
"month": ["Jan", "Feb", "Jan", "Feb", "Jan", "Feb"],
"revenue": [100, 120, 200, 180, 150, 220],
"units": [10, 12, 20, 18, 15, 22]
})
# Basic pivot: regions as rows, months as columns, revenue as values
pivot = sales.pivot_table(index="region", columns="month", values="revenue", aggfunc="sum")
# Feb Jan
# Ontario 120 250
# Quebec 400 200
# Multiple values
pivot = sales.pivot_table(index="region", columns="month",
values=["revenue", "units"], aggfunc="sum")
# Multiple aggregations
pivot = sales.pivot_table(index="region", columns="month", values="revenue",
aggfunc=["sum", "mean", "count"])
# Add totals row and column
pivot = sales.pivot_table(index="region", columns="month", values="revenue",
aggfunc="sum", margins=True, margins_name="Total")
# Fill missing values
pivot = sales.pivot_table(index="region", columns="month", values="revenue",
aggfunc="sum", fill_value=0)melt — Reshape Wide to Long
melt() is the reverse of pivot — it converts wide format (columns as categories) to long format (rows as categories). This is essential for preparing data for visualization libraries and for normalizing denormalized reports.
Analogy — Unpacking a gift basket. A wide DataFrame is like a basket with items labeled on top (column headers). Melt takes each item out, puts it on its own row with a label (variable) and a value. The basket contents become a tidy list.
# Wide format (from a report or Excel)
report = pd.DataFrame({
"product": ["Widget", "Gadget"],
"Q1_revenue": [1000, 2000],
"Q2_revenue": [1200, 1800],
"Q3_revenue": [1100, 2200],
"Q4_revenue": [1400, 1900]
})
# Melt to long format
long = report.melt(
id_vars=["product"], # Columns to keep as-is
value_vars=["Q1_revenue", "Q2_revenue", "Q3_revenue", "Q4_revenue"], # Columns to unpivot
var_name="quarter", # Name for the new "variable" column
value_name="revenue" # Name for the new "value" column
)
# product quarter revenue
# 0 Widget Q1_revenue 1000
# 1 Gadget Q1_revenue 2000
# 2 Widget Q2_revenue 1200
# ...
# Clean the quarter column
long["quarter"] = long["quarter"].str.replace("_revenue", "")stack and unstack — Multi-Index Reshaping
stack() moves column levels to row index (wide to long). unstack() moves row index levels to columns (long to wide). These are more granular than pivot/melt and work directly with multi-index DataFrames.
# Create a multi-index DataFrame from pivot_table
pivot = sales.pivot_table(index="region", columns="month", values="revenue", aggfunc="sum")
# stack: columns become rows (wide to long)
stacked = pivot.stack()
# region month
# Ontario Feb 120
# Jan 250
# Quebec Feb 400
# Jan 200
# unstack: rows become columns (long to wide)
unstacked = stacked.unstack() # Back to the pivot layoutapply — Custom Row/Column Operations
apply() runs any function across rows or columns. Use it when no built-in pandas method exists for your transformation. But be cautious — apply is slow because it processes one row at a time in Python (no vectorization).
# Row-wise apply (axis=1): each row passed as a Series
df["full_label"] = df.apply(
lambda row: f"{row['region']}_{row['product']}_{row['amount']}", axis=1
)
# Column-wise apply (axis=0): each column passed as a Series
col_stats = df[["amount", "quantity"]].apply(
lambda col: pd.Series({
"range": col.max() - col.min(),
"cv": col.std() / col.mean() # Coefficient of variation
})
)
# When to use apply vs vectorized operations
# SLOW (apply -- processes row by row in Python)
df["discount"] = df.apply(lambda r: r["amount"] * 0.1 if r["quantity"] > 20 else 0, axis=1)
# FAST (vectorized -- processes entire column at once in C)
import numpy as np
df["discount"] = np.where(df["quantity"] > 20, df["amount"] * 0.1, 0)pipe — Clean Method Chaining
pipe() passes a DataFrame through a function, enabling clean method chains. Instead of nesting function calls or using temporary variables, you chain transformations in a readable sequence.
Analogy — An assembly line conveyor belt. Each station (function) receives the product (DataFrame) from the previous station, does its work, and passes it to the next. pipe() is the conveyor belt that connects the stations.
def clean_columns(df):
df = df.copy()
df.columns = df.columns.str.strip().str.lower().str.replace(r"[^a-z0-9]+", "_", regex=True)
return df
def remove_duplicates(df, subset):
return df.drop_duplicates(subset=subset)
def add_metadata(df, source):
df = df.copy()
df["_source"] = source
return df
def filter_valid(df, min_amount=0):
return df[df["amount"] > min_amount]
# Method chaining with pipe -- reads top to bottom
result = (
pd.read_csv("orders.csv")
.pipe(clean_columns)
.pipe(remove_duplicates, subset=["order_id"])
.pipe(filter_valid, min_amount=10)
.pipe(add_metadata, source="vendor_csv")
)
# Clean, readable, testable -- each function is independentReal-World Pattern: Complete Data Reshaping Pipeline
import pandas as pd
# Raw data: one row per transaction
raw = pd.DataFrame({
"date": pd.date_range("2026-01-01", periods=12, freq="MS"),
"region": ["Ontario", "Quebec"] * 6,
"product": ["Widget", "Widget", "Gadget", "Gadget"] * 3,
"revenue": [100, 200, 150, 250, 120, 220, 160, 280, 130, 240, 170, 260],
"units": [10, 20, 15, 25, 12, 22, 16, 28, 13, 24, 17, 26]
})
# Step 1: Aggregate by region and product
summary = raw.groupby(["region", "product"]).agg(
total_revenue=("revenue", "sum"),
avg_revenue=("revenue", "mean"),
total_units=("units", "sum")
).reset_index()
# Step 2: Pivot for a management report (regions as rows, products as columns)
report = summary.pivot_table(
index="region", columns="product",
values="total_revenue", aggfunc="sum",
margins=True, margins_name="Grand Total"
)
# Step 3: Monthly trend in long format for charting
monthly = raw.groupby(["date", "region"]).agg(
revenue=("revenue", "sum")
).reset_index()
# Step 4: Wide format for Excel export
monthly_wide = monthly.pivot_table(
index="region", columns="date", values="revenue"
)
monthly_wide.to_excel("monthly_revenue_by_region.xlsx")Common Mistakes
1. Forgetting reset_index() after groupby. After groupby().agg(), the group columns become the index, not regular columns. Downstream operations (merge, to_sql, to_parquet) expect a flat DataFrame. Always call .reset_index() after aggregation unless you specifically need a multi-index.
2. Using apply() when a vectorized operation exists. apply(lambda r: r["a"] + r["b"], axis=1) processes one row at a time in Python. df["a"] + df["b"] processes the entire column in C. The vectorized version is 10-100x faster. Reserve apply() for operations that genuinely have no vectorized equivalent.
3. Using merge() without specifying how. The default is how="inner", which silently drops rows that do not match. If you expect all rows from the left DataFrame to appear in the output, use how="left" explicitly. Always verify the row count after merge.
4. Confusing pivot() and pivot_table(). pivot() requires unique index-column combinations and raises an error on duplicates. pivot_table() handles duplicates by aggregating them with aggfunc. For data engineering, always use pivot_table() because real data almost always has duplicates.
5. Modifying DataFrames in place inside pipe chains. Functions passed to pipe() should return a new DataFrame, not modify the input with inplace=True. In-place modifications break the chain and create hidden side effects.
6. Using concat() without ignore_index=True. Without it, concatenated DataFrames keep their original indexes, leading to duplicate index values. This causes confusing behavior with .loc[] and merge operations. Always set ignore_index=True for vertical concatenation.
7. Not using named aggregations in groupby. df.groupby("region").agg({"amount": ["sum", "mean"]}) creates a multi-level column index that is hard to work with. Named aggregations (total=("amount", "sum")) create flat, readable column names.
8. Melting without specifying id_vars. If you call melt() without id_vars, all columns become value columns and you lose the identifier that links each value to its row. Always specify which columns should stay as identifiers and which should be unpivoted.
Interview Questions
Q: What is the difference between agg(), transform(), and apply() in pandas groupby? A: agg() returns one row per group (summary statistics — like SQL GROUP BY). transform() returns one value per original row (broadcasts the group result back to each member — like a window function in SQL). apply() runs any custom function on each group and returns whatever the function returns (most flexible but slowest). Use agg() for summaries, transform() for adding group-level columns without losing rows, and apply() for custom logic that neither can handle.
Q: How does merge() differ from concat() in pandas? A: merge() combines DataFrames horizontally based on shared key columns (like SQL JOIN). concat() stacks DataFrames vertically (like SQL UNION ALL) or horizontally by position (axis=1). Use merge() when combining related data from different tables (orders + customers). Use concat() when combining data with the same schema from different sources (January file + February file).
Q: What is the difference between pivot() and pivot_table()? A: pivot() reshapes data by assigning one column as the new index, another as new columns, and a third as values. It requires unique index-column combinations and raises an error on duplicates. pivot_table() does the same but handles duplicates by aggregating them with a function (sum, mean, count). For data engineering, always use pivot_table() because real data almost always has duplicate combinations.
Q: What is method chaining with pipe() and why is it useful? A: pipe() passes a DataFrame through a function and returns the result, enabling chains like df.pipe(clean).pipe(transform).pipe(validate). It is useful because each function is independent and testable, the chain reads top-to-bottom (not inside-out like nested calls), and you can insert or remove steps without restructuring the code. It is the pandas equivalent of Unix pipes (cat file | grep pattern | sort).
Q: When should you avoid apply() in pandas? A: Avoid apply() whenever a vectorized alternative exists. df.apply(lambda r: r["a"] <em> r["b"], axis=1) is 10-100x slower than df["a"] </em> df["b"]. Common vectorized replacements: np.where() for conditional logic, str accessor for string operations, arithmetic operators for math, and pd.to_datetime() for date parsing. Use apply() only for operations that genuinely require row-level Python logic with no vectorized equivalent.
Q: How do you add a column showing each row’s percentage of its group’s total? A: Use transform() to get the group total, then divide: df["pct_of_group"] = df["amount"] / df.groupby("region")["amount"].transform("sum") * 100. This keeps all original rows intact (unlike agg(), which would collapse to one row per group) and adds the percentage as a new column. This is the pandas equivalent of amount / SUM(amount) OVER(PARTITION BY region) in SQL.
Q: How would you combine 200 CSV files with the same schema into one DataFrame? A: Use Path.glob() to list the files and pd.concat() to stack them: combined = pd.concat([pd.read_csv(f) for f in Path("data/").glob("*.csv")], ignore_index=True). Set ignore_index=True to avoid duplicate index values. For very large file sets, process in batches or use ThreadPoolExecutor to read files concurrently before concatenating.
Wrapping Up
pandas is the workhorse of Python data engineering. groupby for aggregation, merge for joining, pivot_table for reshaping to wide format, melt for reshaping to long format, transform for broadcasting group stats, apply for custom logic, and pipe for clean method chains. Master these seven operations and you can handle any data transformation without reaching for SQL or Spark.
Related posts: — File Formats (CSV, JSON, Parquet, Excel) — ETL Patterns (extract, transform, load) — SQL Joins (INNER, LEFT, FULL, CROSS, SELF) — SQL Window Functions
Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.