Most data analysis in Python runs through three pandas operations: grouping, merging and reshaping. The Visual Handbook’s chapter on Python for data analysis teaches all three on an online store’s order log.1 Each one has a default that can change a result without raising an error, and each has an argument that turns the surprise into a check.
Defaults that change your numbers silently
The code below shows all three on tables small enough to check by eye. It runs as written on pandas 3.0.
import pandas as pd
# 1. groupby leaves out rows whose key is missing
orders = pd.DataFrame({"region": ["East", "West", None], "revenue": [100, 200, 300]})
orders.groupby("region")["revenue"].sum() # East 100, West 200, and the 300 is gone
orders.groupby("region", dropna=False)["revenue"].sum() # keeps a missing-region group of 300
# 2. merge multiplies rows when a key repeats where you expected it once
rates = pd.DataFrame({"region": ["East", "East", "West"], "rate": [0.10, 0.20, 0.30]})
orders.merge(rates, on="region", how="left") # 4 rows from 3 orders
orders.merge(rates, on="region", how="left", validate="many_to_one") # raises MergeError
# 3. pivot_table averages duplicate cells unless you choose the function
sales = pd.DataFrame({"store": ["A", "A", "B"], "month": ["Jan"] * 3, "units": [10, 30, 5]})
sales.pivot_table(index="store", columns="month", values="units") # A shows 20.0
sales.pivot_table(index="store", columns="month", values="units", aggfunc="sum") # A shows 40Grouping drops rows whose key is missing by default, so the group totals stop adding up to the column total.2 Pass dropna=False when a missing key should appear as a group of its own, which it usually should in a report.
A merge returns one row for every matching pair of keys. When a lookup table repeats a key that should be unique, every order that matches it is duplicated, and every total built on the result grows with it. The validate argument states the relationship you expect, such as "many_to_one", and raises an error when the data disagrees, while indicator=True adds a column that shows which rows found a match.3
pivot_table combines cells that share an index and a column with the mean by default, so two orders of 10 and 30 units appear as 20.4 Name the function with aggfunc, or use pivot, which raises an error when it meets duplicates.
None of the three raises a warning, because each behaves exactly as documented. The damage shows up later, as a total that no longer matches the source system or a dashboard that drifts after a supplier adds a duplicate row to a reference file.
The pattern repeats across all three operations. pandas picks a reasonable default, the default hides a problem in the data, and one argument brings the problem into view, at the cost of a few characters. A plain check catches what the arguments miss, because comparing the row count and the column total before and after each step shows at once when a step has added or lost data.
What to do next
- Analyst. Check that group totals add back to the column total after every
groupby, and passdropna=Falsewhenever missing keys matter. - Developer. Give every
mergeavalidateargument, and useindicator=Truewhile you develop to see which rows failed to match. - Analyst and Developer. Name
aggfuncin everypivot_table, and count rows before and after each merge and reshape in your tests.
Go deeper
Chapter 151 of the Visual Handbook covers Python, NumPy, pandas, reshaping and charts across five notebooks, from a raw order log to a finished chart. Its fourth notebook works through groupby, merges, pivot_table and melt on real data.
Found this useful? Follow me on LinkedIn for the next one.
Working on this? Connect and tell me what you are seeing.
Read chapter 151 of the Visual Handbook and run its reshaping notebook yourself.