Cleaning & joining

Profiling data before you touch it

Imagine a colleague hands you a spreadsheet of 50,000 online orders and says “tell me last month’s revenue by country.” Your instinct might be to start fixing things immediately. Resist it. The first job in cleaning is not to change anything — it is to look. We call this careful first look profiling: counting the rows, checking that each column holds the kind of value you expect, and noticing what looks wrong. Cleaning blind is how you quietly corrupt data; profiling first is how you clean with your eyes open.

Recall the shape we want from the previous guide: a tidy table where each row is one observation and each column is one variable. Profiling is checking how far your real table is from that ideal. For every column you ask three questions: What type is this (a number, a date, a category, free text)? What range or set of values does it actually contain? And how much is missing? You are building a mental picture before you make a single edit.

df.shape                              # (rows, columns)
df.info()                             # types + non-null counts
df.describe()                         # min, max, mean, quartiles
df['country'].value_counts(dropna=False)
df.isna().sum()                       # missing values per column

Missing data: why it’s missing matters

Almost every real dataset has holes — blank cells, NULLs, a placeholder like “N/A” or the sneaky 0 that really means “nobody filled this in.” It is tempting to treat all gaps the same way. But the single most important question about a missing value is not how to fill it; it is why is it missing? The answer decides whether ignoring it is harmless or quietly poisons your conclusions.

Statisticians sort missingness into three plain-language cases. First, missing completely at random: a lab machine randomly dropped some readings — the gaps have nothing to do with anything, so the rows you keep are still a fair sample. Second, missing at random (a misleading name): whether a value is missing depends on other columns you did record. For example, younger users skip the “income” box more often, but within each age group the missingness is random. Third, missing not at random: the value is missing because of the value itself — high earners decline to state their income. This last case is the dangerous one.

So before you do anything, count and locate the gaps, and ask whether the pattern of missingness is plausibly random. A column that is 2% blank at random is a minor nuisance; a column that is 40% blank, with the blanks concentrated in your most important customers, is a finding in itself — and a warning that any analysis of that column rests on shaky ground.

Imputation, carefully

Sometimes you cannot just drop the gaps — you need a complete table for a model or a chart. Filling a missing value with a reasonable guess is called imputation. The simplest version is to replace blanks with the column’s center. For a skewed column like income, the median (the middle value when sorted) is a safer center than the mean, because one billionaire would drag the mean up but barely move the median.

Recall that the mean adds everything up and divides by the count:

The mean of a column. It is pulled toward extreme values; the median is not. That is why we usually impute skewed columns with the median.

There is a small habit that keeps you honest: before you fill a column, add a new true/false column that records where the value was missing. That way the model — and future-you — can still see which rows were guessed, and you can check whether “was-missing” itself predicts the outcome. Mean and median imputation are fine starting points; fancier methods (predict the missing value from the other columns, or generate several plausible fills and combine them) exist, but the rule is the same: never impute and then talk as if the filled numbers were measured.

median = df['income'].median()
df['income_missing'] = df['income'].isna()   # remember where
df['income'] = df['income'].fillna(median)   # then fill

Outliers: error or signal?

An outlier is a value that sits far away from the rest of the data. In our orders table you might see an age of 999, an order amount of -50, or a single purchase of $250,000. The beginner’s mistake is to delete anything extreme on sight. The right question is the same as with missing data: why is it there? Some outliers are simply errors — 999 is an impossible age, almost certainly a placeholder; a negative amount is a refund miscoded as a sale. Those you fix or remove. But other outliers are real and important — that $250,000 order might be your biggest, most valuable customer. Deleting it would erase exactly the signal you most want to study.

To spot candidates systematically, two simple rules help. The first uses quartiles. Sort the data and find Q1 (the value a quarter of the way up) and Q3 (three-quarters of the way up); the gap between them, the interquartile range, is the middle 50% of the data. A common convention flags anything more than 1.5 interquartile ranges outside that middle band:

The 1.5×IQR fences. Values outside this range are flagged for a look — flagged, not automatically deleted. This is exactly what a boxplot’s whiskers draw.

The second rule uses the standard deviation, a measure of typical spread. A value’s z-score is how many standard deviations it sits from the mean; a z beyond about 3 is unusual for roughly bell-shaped data. Be careful, though: the mean and standard deviation are themselves dragged around by the very outliers you are hunting, so the quartile rule is often the steadier choice.

The z-score: distance from the mean, measured in standard deviations (s). Useful, but sensitive to the outliers it’s meant to detect.

Deduplication and keys

Duplicates are rows that appear more than once when they shouldn’t. They sneak in when a form is submitted twice, when two systems are merged, or when a join (next section) misfires. They are dangerous precisely because they look like ordinary data: 1,000 real orders plus 50 accidental copies will simply report as 1,050 orders, and your revenue total will be 5% too high without any visible error.

The tool that prevents this is a key. A primary key is a column (or a set of columns) whose value uniquely identifies each row — order_id for orders, customer_id for customers. If a primary key is doing its job, no two rows share the same value. So the cleanest test for duplicates is simply: does any key value appear more than once? Note that “duplicate” can mean two things — an exact copy of an entire row, or two rows that share a key but disagree elsewhere (the same order_id with two different amounts). The second kind is worse, because now you must decide which version is correct.

df.duplicated(subset=['order_id']).sum()        # how many repeats?
df = df.drop_duplicates(subset=['order_id'], keep='last')

1. Decide what makes two rows “the same” — the full row, or a key like order_id.
2. Count the duplicates first, so you know the size of the problem before you delete anything.
3. If duplicate keys disagree on other columns, pick a rule for which to keep (latest timestamp, most complete row) and write it down.
4. After removing duplicates, re-check that the key is now unique — your guarantee that it worked.

Joins: inner, left, and the row-explosion trap

Real analysis almost always needs to combine tables. Your orders table has a customer_id but not the customer’s country; a separate customers table has the country. Stitching them together by matching customer_id is called a join (or a merge). The shared column you match on is the join key. The whole operation lives or dies on that key being clean — which is why we covered keys and duplicates first.

The type of join decides what happens to rows that don’t find a match. An inner join keeps only rows that match on both sides — orders whose customer exists in the customer table, and nothing else. A left join keeps every row from the left (your orders) and attaches customer info where it matches, leaving blanks where it doesn’t. For “enrich my orders with country, and don’t lose any orders,” the left join is usually what you want — losing rows silently is one of the most common ways a join corrupts an analysis. An inner join would quietly drop every order whose customer_id is missing from the customers table.

Now the trap that catches even experienced analysts: the row explosion. A join is safe and predictable when the key is unique on at least one side (each order matches exactly one customer — “many-to-one”). But suppose the customers table accidentally lists the same customer_id twice. Now each matching order pairs with both customer rows, and your order count doubles for those customers — revenue inflates, and the duplication looks like real data. Mathematically, the number of output rows for a key is the product of how many times it appears on each side:

Output rows of a join, summed over each key k: L_k copies on the left times R_k on the right. If both sides have duplicates (2×2), one key alone yields 4 rows — that is the explosion.

SELECT o.order_id, o.amount, c.country
FROM orders AS o
LEFT JOIN customers AS c
  ON o.customer_id = c.customer_id;

before = len(orders)
out = orders.merge(customers, on='customer_id',
                   how='left', validate='many_to_one')
assert len(out) == before        # left join must not change row count

A cleaning workflow you can trust

Each technique above is useful alone, but their real power is in a disciplined order. Cleaning by hand — clicking around a spreadsheet, deleting a row here, typing a fix there — feels fast and is a disaster: you cannot remember what you did, you cannot redo it next month, and nobody can check you. The cure is reproducible cleaning: every change lives in a script, so the path from raw data to clean data is written down, repeatable, and reviewable.

A workflow that follows naturally from this whole guide:

1. Profile first. Load the raw data read-only and look — types, ranges, missing counts, key uniqueness — before changing anything.
2. Fix structure and types. Get to a tidy table (one row per observation), and make each column the right type (dates as dates, numbers as numbers).
3. Handle missing data — deciding per column whether to drop, flag, or impute, and recording why.
4. Investigate outliers — flag automatically, judge manually, fix the errors and keep the real ones.
5. Deduplicate and confirm each key is unique before you rely on it.
6. Join with guardrails — check the row count before and after, and validate the join type.
7. Save the clean table to a NEW file, and keep the script under version control so the whole path is reproducible.

Notice the order is not arbitrary: you profile before you change, you deduplicate before you join (so duplicates can’t explode), and you save to a new file so raw stays raw. Cleaning is not glamorous and it is not the part people put in the slide deck — but a model or chart built on dirty data is confidently wrong, which is the worst kind of wrong. Do this part well, write it down so it runs again at the press of a button, and everything downstream gets easier and more trustworthy.