Filtering, grouping, joining and reshaping are the four verbs of table manipulation. Learn them properly once and most analysis becomes a short pipeline of them rather than a loop.
Filtering
import numpy as np
import pandas as pd
rng = np.random.default_rng(3)
orders = pd.DataFrame({
'region': rng.choice(['North', 'South', 'East'], 12),
'channel': rng.choice(['web', 'phone'], 12),
'units': rng.integers(1, 100, 12),
'revenue': rng.normal(500, 150, 12).round(2),
})
print(orders[orders['units'] > 50].shape)
print(orders[(orders['units'] > 50) & (orders['channel'] == 'web')].shape)
print(orders[orders['region'].isin(['North', 'East'])].shape)
print(orders[~orders['region'].eq('South')].shape)
print(orders[orders['revenue'].between(400, 600)].shape)
print(orders.query('units > 50 and channel == "web"').shape)
Open this example in the editor and change it. Nothing you type is saved.
Three rules that save time:
- Parentheses around each comparison.
&binds tighter than>, soa > 1 & b < 2parses asa > (1 & b) < 2. &,|,~, neverand,or,not. The Python keywords want a single truth value and a Series has many.isinrather than a chain of|. It is shorter and it scales to a list from a variable.
query reads well for interactive work and is slower; the mask form is what belongs in a pipeline.
Grouping
import numpy as np
import pandas as pd
rng = np.random.default_rng(3)
orders = pd.DataFrame({
'region': rng.choice(['North', 'South', 'East'], 200),
'channel': rng.choice(['web', 'phone', 'store'], 200),
'units': rng.integers(1, 100, 200),
'revenue': rng.normal(500, 150, 200).round(2),
})
# One column, one function.
print(orders.groupby('region')['revenue'].sum().round(2))
print()
# Named aggregations: the readable form, and the one that names its
# own output columns.
summary = orders.groupby('region', as_index=False).agg(
orders=('units', 'size'),
units=('units', 'sum'),
revenue=('revenue', 'sum'),
mean_order=('revenue', 'mean'),
best=('revenue', 'max'),
)
print(summary.round(2))
print()
# Two keys gives a MultiIndex, which is often not what you want.
by_both = orders.groupby(['region', 'channel'])['revenue'].sum()
print(by_both.head(4))
print(type(by_both.index).__name__)
print()
print(by_both.reset_index().head(4))
Open this example in the editor and change it. Nothing you type is saved.
as_index=False or a .reset_index() keeps the grouping keys as ordinary columns, which is almost always what the next step wants.
Three group operations that are not aggregation:
import numpy as np
import pandas as pd
rng = np.random.default_rng(3)
orders = pd.DataFrame({
'region': rng.choice(['North', 'South'], 10),
'revenue': rng.integers(100, 900, 10),
})
# transform: one value per ROW, broadcast back from the group.
orders['region_total'] = orders.groupby('region')['revenue'].transform('sum')
orders['share'] = (orders['revenue'] / orders['region_total']).round(3)
print(orders.head(4))
print()
# filter: keep or drop whole GROUPS.
big = orders.groupby('region').filter(lambda g: g['revenue'].sum() > 2000)
print('regions kept:', sorted(big['region'].unique().tolist()))
print()
# rank within a group.
orders['rank_in_region'] = (
orders.groupby('region')['revenue'].rank(ascending=False, method='dense').astype(int))
print(orders.sort_values(['region', 'rank_in_region']).head(4))
Open this example in the editor and change it. Nothing you type is saved.
transform is the one people reach for last and should reach for first. It answers "what fraction of its region's revenue is this order" without a join.
Joining
import pandas as pd
orders = pd.DataFrame({
'order_id': [1, 2, 3, 4],
'region': ['North', 'South', 'East', 'Nowhere'],
'revenue': [500, 300, 700, 100],
})
owners = pd.DataFrame({
'region': ['North', 'South', 'East', 'West'],
'owner': ['Ann', 'Bo', 'Cy', 'Di'],
})
print(orders.merge(owners, on='region', how='inner')) # matches only
print()
print(orders.merge(owners, on='region', how='left')) # all orders
print()
print(orders.merge(owners, on='region', how='outer', indicator=True)
[['region', 'order_id', 'owner', '_merge']])
Open this example in the editor and change it. Nothing you type is saved.
how='left' keeps every row on the left and fills the right with NaN where there is no match - which is what you want when you are enriching a fact table from a lookup. indicator=True adds a _merge column saying where each row came from, and it is the fastest way to find keys that did not match.
Different column names, and an index join:
import pandas as pd
orders = pd.DataFrame({'region_code': ['N', 'S'], 'revenue': [500, 300]})
regions = pd.DataFrame({'code': ['N', 'S'], 'name': ['North', 'South']})
print(orders.merge(regions, left_on='region_code', right_on='code', how='left'))
print()
left = pd.DataFrame({'a': [1, 2]}, index=['x', 'y'])
right = pd.DataFrame({'b': [10, 20]}, index=['y', 'z'])
print(left.join(right, how='left')) # joins on the INDEX
print()
print(pd.concat([left, right], axis=1)) # also aligns on the index
Open this example in the editor and change it. Nothing you type is saved.
The merge that multiplies
import pandas as pd
orders = pd.DataFrame({'region': ['North', 'South'], 'revenue': [500, 300]})
owners = pd.DataFrame({
'region': ['North', 'North', 'South'], # North appears TWICE
'owner': ['Ann', 'Annabel', 'Bo'],
})
joined = orders.merge(owners, on='region', how='left')
print(joined)
print(f'{len(orders)} rows in, {len(joined)} rows out')
print(f'revenue before {orders["revenue"].sum()}, after {joined["revenue"].sum()}')
Open this example in the editor and change it. Nothing you type is saved.
Two rows became three and the total revenue went up by 500, because the North order matched two owners. Nothing warned you.
This is the single most common silent error in analysis, and there is a one-word fix:
import pandas as pd
orders = pd.DataFrame({'region': ['North', 'South'], 'revenue': [500, 300]})
owners = pd.DataFrame({'region': ['North', 'North', 'South'], 'owner': ['Ann', 'Annabel', 'Bo']})
try:
orders.merge(owners, on='region', how='left', validate='many_to_one')
except Exception as error:
print(type(error).__name__)
print(str(error)[:70])
Open this example in the editor and change it. Nothing you type is saved.
validate= takes 'one_to_one', 'one_to_many', 'many_to_one' or 'many_to_many' and raises when the data disagrees. Put it on every merge. It costs nothing and it turns a silent wrong total into an exception with a line number.
The other defence is to check the row count:
import pandas as pd
left = pd.DataFrame({'k': [1, 2], 'v': [10, 20]})
right = pd.DataFrame({'k': [1, 2], 'w': ['a', 'b']})
before = len(left)
joined = left.merge(right, on='k', how='left', validate='one_to_one')
assert len(joined) == before, f'{before} rows became {len(joined)}'
print('row count held at', len(joined))
Open this example in the editor and change it. Nothing you type is saved.
Reshaping between wide and long
import pandas as pd
wide = pd.DataFrame({
'region': ['North', 'South', 'East'],
'jan': [500, 300, 700],
'feb': [550, 280, 760],
'mar': [600, 310, 720],
})
print(wide)
print()
long = wide.melt(id_vars='region', var_name='month', value_name='revenue')
print(long.head(4))
print(long.shape)
print()
back = long.pivot(index='region', columns='month', values='revenue')
print(back)
Open this example in the editor and change it. Nothing you type is saved.
Long is for computing, wide is for reading. Grouping, plotting and modelling all want one row per observation; a report wants months across the top. melt goes one way and pivot the other.
pivot_table aggregates as it pivots, which is what you need when the index and columns do not uniquely identify a row:
import numpy as np
import pandas as pd
rng = np.random.default_rng(5)
orders = pd.DataFrame({
'region': rng.choice(['North', 'South'], 40),
'channel': rng.choice(['web', 'phone'], 40),
'revenue': rng.integers(100, 900, 40),
})
table = orders.pivot_table(
index='region', columns='channel', values='revenue',
aggfunc='sum', margins=True, margins_name='total',
)
print(table)
print()
print(orders.pivot_table(index='region', columns='channel',
values='revenue', aggfunc=['mean', 'count']).round(1))
Open this example in the editor and change it. Nothing you type is saved.
crosstab is the counting shorthand:
import numpy as np
import pandas as pd
rng = np.random.default_rng(5)
orders = pd.DataFrame({
'region': rng.choice(['North', 'South'], 40),
'channel': rng.choice(['web', 'phone'], 40),
})
print(pd.crosstab(orders['region'], orders['channel'], margins=True))
print()
print(pd.crosstab(orders['region'], orders['channel'], normalize='index').round(2))
Open this example in the editor and change it. Nothing you type is saved.
A worked example
import numpy as np
import pandas as pd
rng = np.random.default_rng(seed=21)
n = 600
orders = pd.DataFrame({
'order_id': np.arange(1, n + 1),
'region_code': rng.choice(['N', 'S', 'E', 'W'], n, p=[.4, .3, .2, .1]),
'channel': rng.choice(['web', 'phone', 'store'], n, p=[.6, .25, .15]),
'month': rng.choice(['2026-01', '2026-02', '2026-03'], n),
'units': rng.integers(1, 50, n),
'revenue': rng.normal(480, 160, n).round(2).clip(10),
})
regions = pd.DataFrame({
'region_code': ['N', 'S', 'E', 'W'],
'region': ['North', 'South', 'East', 'West'],
'owner': ['Ann', 'Bo', 'Cy', 'Di'],
})
targets = pd.DataFrame({
'region': ['North', 'South', 'East', 'West'],
'month': ['2026-01'] * 4,
'target': [90000, 70000, 45000, 25000],
})
# 1. Join the lookup, asserting the relationship.
enriched = orders.merge(regions, on='region_code', how='left', validate='many_to_one')
assert len(enriched) == len(orders), 'the merge multiplied rows'
assert enriched['region'].notna().all(), 'a region code had no name'
print(f'{len(enriched)} rows, {enriched["region"].nunique()} regions')
print()
# 2. Filter: web and phone only, and orders worth having.
considered = enriched[
enriched['channel'].isin(['web', 'phone']) & (enriched['revenue'] >= 100)
].copy()
print(f'{len(considered)} of {len(enriched)} rows considered')
print()
# 3. Group, with named aggregations.
by_region_month = (
considered.groupby(['region', 'month'], as_index=False)
.agg(orders=('order_id', 'size'),
units=('units', 'sum'),
revenue=('revenue', 'sum'),
mean_order=('revenue', 'mean'))
.round(2)
)
print(by_region_month.head(4))
print()
# 4. transform: each order's share of its region-month, no join needed.
considered['region_month_total'] = (
considered.groupby(['region', 'month'])['revenue'].transform('sum'))
# Stored unrounded; rounded only for display, so the shares still
# sum to exactly one within each group.
considered['share'] = considered['revenue'] / considered['region_month_total']
print(considered[['order_id', 'region', 'month', 'revenue', 'share']]
.head(3).round({'share': 4}))
print(f'shares sum to 1 per group: '
f'{bool(np.allclose(considered.groupby(["region", "month"])["share"].sum(), 1.0))}')
print()
# 5. Rank within group, and take the top 2 per region.
considered['rank'] = (considered.groupby('region')['revenue']
.rank(ascending=False, method='first').astype(int))
top = considered[considered['rank'] <= 2].sort_values(['region', 'rank'])
print(top[['region', 'rank', 'order_id', 'revenue']])
print()
# 6. Wide for the report.
wide = by_region_month.pivot(index='region', columns='month', values='revenue').round(0)
print(wide)
print()
# 7. Join the targets, keeping every region-month even where no target
# exists - and showing which those are.
with_targets = by_region_month.merge(
targets, on=['region', 'month'], how='left', validate='one_to_one', indicator=True)
with_targets['hit'] = with_targets['revenue'] >= with_targets['target']
print(with_targets[['region', 'month', 'revenue', 'target', 'hit', '_merge']].head(6))
print()
print('rows with no target:', int((with_targets['_merge'] == 'left_only').sum()))
Open this example in the editor and change it. Nothing you type is saved.
Four habits carry that pipeline. Every merge has a validate=. Every merge is followed by an assertion on the row count. transform answers a per-row question without a second join. And the wide pivot happens once, at the end, for presentation only.
When it goes wrong
| Symptom | Cause |
|---|---|
| The total went up after a join | Duplicate keys on the right; use validate= |
truth value of a Series is ambiguous |
and/or instead of the bitwise operators |
| The mask matched nothing | Missing parentheses around comparisons |
| The group keys became an index | Pass as_index=False or reset_index() |
| A per-row group value needs a join | Use transform instead |
pivot raised on duplicate entries |
Use pivot_table with an aggfunc |
| A left join lost rows | It cannot; the row count only grows or holds |
| Key types do not match | int against str; cast one before merging |
A check you can run
Add this line after every merge in your current analysis:
assert len(result) == len(left), f'{len(left)} rows became {len(result)}'
Open this example in the editor and change it. Nothing you type is saved.
Run the whole pipeline. If any assertion fires you have found a silent duplication, and the total you reported last month was wrong. In my experience roughly one analysis in three has one, and it is always in the join nobody thought about.