pandas: filter, group and validate a merge

Start with the row grain

Each row below is one fictional order. We exclude returned orders under this exercise rule. A real revenue definition also needs an agreed treatment of refunds, taxes and shipping. Run the code in a Python environment with pandas installed; this page does not execute Python.

import pandas as pd
orders = pd.DataFrame({
    'order_id': [101, 102, 103, 104],
    'customer_id': [1, 1, 2, 3],
    'amount_usd': [50, 30, 80, 20],
    'status': ['completed', 'returned', 'completed', 'completed']
})
assert orders['order_id'].is_unique
completed = orders.loc[orders['status'].eq('completed')].copy()
summary = (completed.groupby('customer_id', as_index=False)
    .agg(order_count=('order_id', 'size'), revenue_usd=('amount_usd', 'sum'))
    .sort_values(['revenue_usd', 'customer_id'], ascending=[False, True]))
assert summary['revenue_usd'].sum() == 150
print(summary.to_string(index=False))

Expected: customer 2 has one order worth $80, customer 1 one worth $50 and customer 3 one worth $20. The total is $150.

Join a lookup without losing an order

Run this after the preceding example. The customer lookup deliberately omits customer 3.

customers = pd.DataFrame({
    'customer_id': [1, 2], 'market': ['US', 'UK']
})
joined = completed.merge(customers, on='customer_id', how='left',
    validate='many_to_one', indicator=True)
assert len(joined) == len(completed)
assert joined['amount_usd'].sum() == completed['amount_usd'].sum()
print(joined.loc[joined['_merge'].eq('left_only'), 'order_id'].tolist())
# Expected: [104]

The left join retains order 104. validate checks the key relationship, while indicator identifies unmatched rows. They answer different questions. pandas can match null join keys to each other, unlike a usual SQL equality join; inspect missing keys explicitly.

Select the operation that matches the task

• loc selects labels or a boolean condition; iloc selects integer positions. Neither is universally safer: choose the intended meaning.
• agg reduces groups; transform can return a result aligned with the original rows.
• merge matches keys. concat combines objects along an axis and can align the other axis by labels.
• pivot requires unique index/column combinations. pivot_table aggregates repeated combinations using the function you choose.

Treat missing values as a decision

Inspect which fields are missing and why. An unknown amount should not become zero without a business rule. Dropping a small number of rows can still bias an analysis if those rows differ systematically. Use sum(min_count=1) when a group with no known amounts should remain missing.

Prefer clear built-in operations when they express the task, then profile performance on representative data. A vectorised-looking string operation is not a guarantee of compiled execution or lower memory use.

Try two failure cases

Duplicate customer 1 in the lookup: the validated merge should raise a MergeError. Remove customer 2 instead: the order must remain in the left join and appear in the unmatched report. Explain the checks before changing any data.