Pandas DataFrames: Filtering, Grouping, and Merging Data
Most real data analysis in pandas comes down to three moves. You filter a table down to the rows you care about, you group it to compute totals, averages and counts per category, and you merge it with other tables to bring in related information. Get comfortable with those three and you can answer the large majority of everyday questions: revenue per product last quarter, customers who haven't ordered, how each region did against target.
This guide works through all three on a small, realistic dataset, with the real output at every step so you can see exactly what each operation returns. It also covers the gotchas that trip people up: operator precedence in filters, missing values silently dropping out of groups, and merges that quietly multiply rows.
The examples were run with pandas 3.0 on Python 3.13. If you're brand new to pandas, how to use Python for data analysis is a gentler starting point.
The Sample Data
Two tables: orders, and the customers who placed them.
# data.py
import pandas as pd
pd.set_option("display.width", 120)
pd.set_option("display.max_columns", None)
orders = pd.DataFrame(
{
"order_id": [101, 102, 103, 104, 105, 106, 107, 108],
"customer_id": [1, 2, 1, 3, 4, 2, 5, 1],
"region": ["North", "South", "North", "East", "West", "South", "East", None],
"product": ["Mug", "Lamp", "Pen", "Mug", "Lamp", "Mug", "Pen", "Lamp"],
"quantity": [2, 1, 10, 4, 1, 3, 25, 2],
"unit_price": [12.0, 65.0, 1.5, 12.0, 65.0, 12.0, 1.5, 60.0],
"order_date": pd.to_datetime(
["2026-01-05", "2026-01-17", "2026-02-02", "2026-02-14",
"2026-02-20", "2026-03-03", "2026-03-09", "2026-03-21"]
),
}
)
orders["revenue"] = orders["quantity"] * orders["unit_price"]
customers = pd.DataFrame(
{
"customer_id": [1, 2, 3, 4, 6],
"name": ["Ada", "Grace", "Linus", "Margaret", "Guido"],
"segment": ["Retail", "Business", "Retail", "Business", "Retail"],
}
)
The two set_option calls stop pandas from truncating columns with ... when printing. Note that the data is deliberately a little messy, as real data is: order 108 has no region, customer 5 placed an order but isn't in the customers table, and customer 6 (Guido) exists but never ordered.
print(orders)
order_id customer_id region product quantity unit_price order_date revenue
0 101 1 North Mug 2 12.0 2026-01-05 24.0
1 102 2 South Lamp 1 65.0 2026-01-17 65.0
2 103 1 North Pen 10 1.5 2026-02-02 15.0
3 104 3 East Mug 4 12.0 2026-02-14 48.0
4 105 4 West Lamp 1 65.0 2026-02-20 65.0
5 106 2 South Mug 3 12.0 2026-03-03 36.0
6 107 5 East Pen 25 1.5 2026-03-09 37.5
7 108 1 NaN Lamp 2 60.0 2026-03-21 120.0
In pandas 3, text columns like region and product get the dedicated str dtype by default rather than the old catch-all object dtype; you'll see it in orders.dtypes.
Filtering Rows
Boolean Masks
A comparison on a column doesn't return filtered data. It returns a boolean Series, one True/False per row:
print(orders["quantity"] > 5)
0 False
1 False
2 True
3 False
4 False
5 False
6 True
7 False
Name: quantity, dtype: bool
Passing that Series back into the DataFrame keeps the rows where it's True:
print(orders[orders["quantity"] > 5])
order_id customer_id region product quantity unit_price order_date revenue
2 103 1 North Pen 10 1.5 2026-02-02 15.0
6 107 5 East Pen 25 1.5 2026-03-09 37.5
Notice the index (2 and 6) is preserved. Filtering doesn't renumber rows, which is useful for tracing results back to the original data. Call .reset_index(drop=True) if you want a fresh 0-based index.
Combining Conditions
Combine masks with & (and), | (or), and ~ (not). Two rules always apply:
- Use
&,|,~, not Python'sand,or,not. The keywords try to turn a whole Series into a singleTrueorFalseand raiseValueError: The truth value of a Series is ambiguous. - Wrap each condition in parentheses.
&binds more tightly than==, so without parentheses the expression is evaluated in the wrong order.
mugs = orders[(orders["product"] == "Mug") & (orders["quantity"] >= 3)]
print(mugs[["order_id", "product", "quantity"]])
order_id product quantity
3 104 Mug 4
5 106 Mug 3
Handy Filtering Methods
Several Series methods produce masks for common cases:
# Membership in a list
orders[orders["region"].isin(["North", "East"])]
# Inclusive range (works for numbers and dates)
orders[orders["order_date"].between("2026-02-01", "2026-02-28")]
# String tests via the .str accessor
orders[orders["product"].str.startswith("L")]
# Missing values
orders[orders["region"].isna()]
# Negation: everything except mugs
orders[~orders["product"].isin(["Mug"])]
For the date range, the result is orders 103, 104 and 105. Comparing a datetime column against strings like "2026-02-01" works because pandas parses them as timestamps.
Missing values deserve attention. NaN never equals anything, including itself, so orders["region"] == None doesn't find order 108. Always use .isna() and .notna().
.loc: Filter Rows and Pick Columns Together
.loc[rows, columns] takes a row mask and a column list in one step:
print(orders.loc[orders["revenue"] > 50, ["order_id", "product", "revenue"]])
order_id product revenue
1 102 Lamp 65.0
4 105 Lamp 65.0
7 108 Lamp 120.0
.loc is also the correct way to modify filtered rows, which matters a lot in pandas 3 (see the Copy-on-Write section below).
.query(): Filters as Strings
For longer conditions, query() is often more readable. Column names are used directly, and @ refers to Python variables:
min_rev = 40
print(orders.query("product == 'Lamp' and revenue > @min_rev")[["order_id", "revenue"]])
order_id revenue
1 102 65.0
4 105 65.0
7 108 120.0
Inside query() you can use and, or, and not, because pandas parses the string itself. It's purely a style choice; use whichever reads better.
Grouping and Aggregating
groupby() follows a pattern often called split-apply-combine: split the rows into groups by one or more keys, apply a function to each group, and combine the results into a new table.
One Key, One Aggregate
print(orders.groupby("product")["revenue"].sum())
product
Lamp 250.0
Mug 108.0
Pen 52.5
Name: revenue, dtype: float64
The group keys become the index of the result. Chain .sort_values(ascending=False) to rank them:
print(orders.groupby("product")["quantity"].sum().sort_values(ascending=False))
product
Pen 35
Mug 9
Lamp 4
Name: quantity, dtype: int64
Built-in aggregations include sum, mean, median, min, max, count (non-missing values), size (all rows), nunique, std, first, and last. For a quick count per category, orders["product"].value_counts() is a shortcut.
Several Aggregates at Once
Pass a list to agg():
print(orders.groupby("product")["revenue"].agg(["count", "sum", "mean"]))
count sum mean
product
Lamp 3 250.0 83.333333
Mug 3 108.0 36.000000
Pen 2 52.5 26.250000
Named Aggregation
When you need different aggregations of different columns, named aggregation gives you clean column names in one call. Each keyword is an output column, and its value is a (column, function) pair:
summary = orders.groupby("product").agg(
orders=("order_id", "count"),
units=("quantity", "sum"),
revenue=("revenue", "sum"),
avg_price=("unit_price", "mean"),
)
print(summary)
orders units revenue avg_price
product
Lamp 3 4 250.0 63.333333
Mug 3 9 108.0 12.000000
Pen 2 35 52.5 1.500000
This is the form I reach for most. It's explicit and produces a table that's ready to export or plot. If you'd rather keep the key as a regular column, pass as_index=False to groupby() (or call .reset_index() on the result).
Grouping by Several Keys
print(orders.groupby(["region", "product"])["revenue"].sum())
region product
East Mug 48.0
Pen 37.5
North Mug 24.0
Pen 15.0
South Lamp 65.0
Mug 36.0
West Lamp 65.0
Name: revenue, dtype: float64
The result has a two-level MultiIndex. To turn the inner level into columns, a cross-tab layout, use unstack():
print(orders.groupby(["region", "product"])["revenue"].sum().unstack(fill_value=0))
product Lamp Mug Pen
region
East 0.0 48.0 37.5
North 0.0 24.0 15.0
South 65.0 36.0 0.0
West 65.0 0.0 0.0
pivot_table() does the same thing in one call, and margins=True adds totals:
print(
orders.pivot_table(
index="region", columns="product", values="revenue",
aggfunc="sum", fill_value=0, margins=True,
)
)
product Lamp Mug Pen All
region
East 0.0 48.0 37.5 85.5
North 0.0 24.0 15.0 39.0
South 65.0 36.0 0.0 101.0
West 65.0 0.0 0.0 65.0
All 130.0 108.0 52.5 290.5
Watch Out: Missing Keys Disappear
Look at the grand total in that table: 290.5. Now add up the revenue column: it's 410.5. Order 108, the 120.0 Lamp sale with no region, has vanished.
By default, groupby() and pivot_table() drop rows whose group key is missing. That's one of the most common sources of totals that don't reconcile. Keep them with dropna=False:
print(orders.groupby("region", dropna=False)["revenue"].sum())
region
East 85.5
North 39.0
South 101.0
West 65.0
NaN 120.0
Name: revenue, dtype: float64
Or fill the gap first, which often reads better in a report: orders["region"].fillna("Unknown").
transform: Group Results on Every Row
agg() returns one row per group. Sometimes you want the group statistic attached to each original row, for example each order's share of its product's total revenue. That's transform(), which returns a result the same length as the input:
product_total = orders.groupby("product")["revenue"].transform("sum")
orders["share_of_product"] = orders["revenue"] / product_total
print(orders[["order_id", "product", "revenue", "share_of_product"]].round(2))
order_id product revenue share_of_product
0 101 Mug 24.0 0.22
1 102 Lamp 65.0 0.26
2 103 Pen 15.0 0.29
3 104 Mug 48.0 0.44
4 105 Lamp 65.0 0.26
5 106 Mug 36.0 0.33
6 107 Pen 37.5 0.71
7 108 Lamp 120.0 0.48
transform is also how you'd fill missing values with a group mean, or compute a value's difference from its group average.
filter: Keep Whole Groups
groupby().filter() keeps or drops entire groups based on a condition. Here, only customers with at least two orders:
repeat = orders.groupby("customer_id").filter(lambda g: len(g) >= 2)
print(repeat[["order_id", "customer_id"]])
order_id customer_id
0 101 1
1 102 2
2 103 1
5 106 2
7 108 1
Grouping by Time
To group by calendar period, derive a period from the date column, or use resample() on a datetime column:
monthly = orders.groupby(orders["order_date"].dt.to_period("M"))["revenue"].sum()
print(monthly)
order_date
2026-01 89.0
2026-02 128.0
2026-03 193.5
Freq: M, Name: revenue, dtype: float64
orders.resample("MS", on="order_date")["revenue"].sum() gives the same totals labelled by each month's start date, and also includes months with no orders as zero rows, which matters for charts.
Merging DataFrames
merge() joins two DataFrames on one or more key columns, like a SQL JOIN. It's how you bring customer names and segments into the orders table.
Inner Join (the Default)
small = orders[["order_id", "customer_id", "product", "revenue"]]
print(small.merge(customers, on="customer_id"))
order_id customer_id product revenue name segment
0 101 1 Mug 24.0 Ada Retail
1 102 2 Lamp 65.0 Grace Business
2 103 1 Pen 15.0 Ada Retail
3 104 3 Mug 48.0 Linus Retail
4 105 4 Lamp 65.0 Margaret Business
5 106 2 Mug 36.0 Grace Business
6 108 1 Lamp 120.0 Ada Retail
An inner join keeps only keys present in both tables. Order 107 is gone because customer 5 isn't in customers, and Guido doesn't appear because he has no orders. Silently losing order 107 is often not what you want.
Left Join
how="left" keeps every row from the left table and fills in what it can:
print(small.merge(customers, on="customer_id", how="left"))
order_id customer_id product revenue name segment
0 101 1 Mug 24.0 Ada Retail
1 102 2 Lamp 65.0 Grace Business
2 103 1 Pen 15.0 Ada Retail
3 104 3 Mug 48.0 Linus Retail
4 105 4 Lamp 65.0 Margaret Business
5 106 2 Mug 36.0 Grace Business
6 107 5 Pen 37.5 NaN NaN
7 108 1 Lamp 120.0 Ada Retail
For "enrich this table with details from a lookup table", a left join is almost always what you want. Every order stays, and unmatched ones are visible as NaN.
The four join types:
how= | Keeps | SQL equivalent |
|---|---|---|
"inner" (default) | Keys in both tables | INNER JOIN |
"left" | All rows of the left table | LEFT JOIN |
"right" | All rows of the right table | RIGHT JOIN |
"outer" | All keys from either table | FULL OUTER JOIN |
Finding Mismatches with indicator=True
indicator=True adds a _merge column saying where each row came from. Combined with an outer join, it's a quick data-quality check:
result = small.merge(customers, on="customer_id", how="outer", indicator=True)
print(result[["order_id", "customer_id", "name", "_merge"]])
order_id customer_id name _merge
0 101.0 1 Ada both
1 103.0 1 Ada both
2 108.0 1 Ada both
3 102.0 2 Grace both
4 106.0 2 Grace both
5 104.0 3 Linus both
6 105.0 4 Margaret both
7 107.0 5 NaN left_only
8 NaN 6 Guido right_only
Two things to notice. left_only and right_only rows point straight at the orphans. And order_id turned into floats (101.0): the integer column needed to hold a NaN for Guido's row, and NumPy-backed integers can't. If that matters, convert back afterwards or use the nullable Int64 dtype.
The same trick gives you an anti-join, "customers who never ordered":
ordered = orders[["customer_id"]].drop_duplicates()
m = customers.merge(ordered, on="customer_id", how="left", indicator=True)
print(m[m["_merge"] == "left_only"].drop(columns="_merge"))
customer_id name segment
4 6 Guido Retail
Different Key Names and Overlapping Columns
When the key has different names in each table, use left_on and right_on:
c2 = customers.rename(columns={"customer_id": "id"})
small.merge(c2, left_on="customer_id", right_on="id", how="left")
Both key columns are kept; drop the redundant one afterwards.
When both tables have non-key columns with the same name, pandas adds suffixes (_x, _y by default). Choose meaningful ones:
targets = pd.DataFrame({"product": ["Mug", "Lamp", "Pen"], "revenue": [100.0, 300.0, 50.0]})
actual = orders.groupby("product", as_index=False)["revenue"].sum()
comparison = actual.merge(targets, on="product", suffixes=("_actual", "_target"))
comparison["hit_target"] = comparison["revenue_actual"] >= comparison["revenue_target"]
print(comparison)
product revenue_actual revenue_target hit_target
0 Lamp 250.0 300.0 False
1 Mug 108.0 100.0 True
2 Pen 52.5 50.0 True
Guard Against Row Explosion with validate
If the lookup table accidentally contains a duplicate key, a merge duplicates every matching row on the other side. Your 8 orders become 10, and every total built on them is now wrong. Nothing warns you.
The validate argument turns your assumption into a check:
dup = pd.concat([customers, customers.iloc[[0]]]) # Ada appears twice
small.merge(dup, on="customer_id", validate="many_to_one")
# MergeError: Merge keys are not unique in right dataset; not a many-to-one merge
Options are "one_to_one", "one_to_many", "many_to_one", and "many_to_many". Use it on any merge where you know the expected relationship; it costs almost nothing and catches a whole class of silent bugs. Comparing len() before and after a left join is a good habit too.
Stacking Tables with concat
merge combines tables side by side by key. To stack tables with the same columns on top of each other (monthly exports, files from several stores), use pd.concat():
jan = orders[orders["order_date"].dt.month == 1][["order_id", "revenue"]]
feb = orders[orders["order_date"].dt.month == 2][["order_id", "revenue"]]
print(pd.concat([jan, feb], ignore_index=True))
order_id revenue
0 101 24.0
1 102 65.0
2 103 15.0
3 104 48.0
4 105 65.0
ignore_index=True builds a fresh index instead of keeping duplicate labels from each input.
Copy-on-Write: Modifying Filtered Data Safely
pandas 3 makes Copy-on-Write the only behavior: any DataFrame or Series you get from filtering or selecting behaves as an independent copy. That removes the old, confusing SettingWithCopyWarning, but it also means one common pattern never works:
# Chained assignment: does NOT change orders
orders[orders["quantity"] > 5]["unit_price"] = 1.25
pandas 3 raises a ChainedAssignmentError warning here, and orders is left unchanged, because the first [...] produced a copy and the assignment went to that copy. Do the selection and assignment in a single .loc call instead:
orders.loc[orders["quantity"] > 5, "unit_price"] = 1.25
And if you take a filtered subset and modify it, you're modifying only the subset, which is usually what you meant:
big = orders[orders["quantity"] > 5]
big["flag"] = True # changes big only; orders is untouched
Putting It Together
Method chaining lets you express a whole analysis as one readable pipeline. Revenue per customer since February, including orders from unknown customers:
report = (
orders.merge(customers, on="customer_id", how="left", validate="many_to_one")
.assign(name=lambda d: d["name"].fillna("Unknown"))
.query("order_date >= '2026-02-01'")
.groupby(["segment", "name"], dropna=False, as_index=False)
.agg(orders=("order_id", "count"), revenue=("revenue", "sum"))
.sort_values("revenue", ascending=False)
)
print(report)
segment name orders revenue
2 Retail Ada 2 135.0
1 Business Margaret 1 65.0
3 Retail Linus 1 48.0
4 NaN Unknown 1 37.5
0 Business Grace 1 36.0
Each step returns a new DataFrame: left-join the lookup (validated), fill missing names, filter by date, group (keeping the missing segment), aggregate with named columns, and sort. assign() with a lambda refers to the DataFrame as it exists at that point in the chain.
All of these operations are vectorized, running in compiled code over whole columns at once, which is why pandas is fast even though you're writing Python. NumPy fundamentals explains what's happening underneath, and if your data outgrows pandas, Polars vs pandas covers the main alternative.
Conclusion
Filtering, grouping and merging cover most of what you'll do with DataFrames. Filter with boolean masks combined with &, | and ~ (in parentheses), or with query(), and use .loc whenever you modify the rows you selected. Group with groupby().agg() and named aggregation, reach for transform() when you need group results on each row, and remember that missing keys are dropped unless you pass dropna=False. Merge with an explicit how, check for orphans with indicator=True, and use validate so duplicate keys can't silently inflate your numbers.
