Cleaning means making equal things equal

The idea

pandas does not understand your data. It compares values exactly, character by character. 'Dallas', 'dallas' and 'Dallas ' (with a trailing space) are three different values to it, and 'Dallas' and 'Boston' are no more different than those three are from each other.

That is the whole problem. Harbor & Pine has 2,018 customers and the people who typed them in were human. Cities have spaces and capitals in odd places. Some customers appear twice. Nine products have no recorded cost. Two dozen order lines have a quantity of zero or less. None of it raises an error: groupby, mean and merge all run and give you a number. The number is wrong because the groups or the rows were wrong.

Cleaning is therefore one idea in three forms: make values that mean the same thing look the same (text), decide what a missing value means (NaN), and decide which rows are really one entity (duplicates).

Why it exists

Real data comes from forms, exports, imports from other systems and people on a Friday afternoon. Nobody designed ' dallas '. If you skip cleaning, the report still renders, and that is the danger: a top-cities chart that says Dallas has 78 customers looks perfectly reasonable. A colleague repeats it in a meeting, and a month later a real count says 83.

Cleaning also has a cost. Every step is a decision about what the data means, and a decision can be wrong. The habit that makes it safe is to clean in code, never by editing the file by hand, so the steps are written down and can be rerun on next month's export.

How it works under the hood

Text: normalise before you compare

The .str accessor applies a text operation to every value of a column at once. The usual recipe is strip() for the ends, lower(), upper() or title() for capitals, and replace() for known variants. Missing values pass through untouched: .str.strip() on a NaN gives NaN, not an error and not the string 'nan'. A .str method on a column that is not text, such as numbers stored as text with a $, needs the symbol removed and astype(float) or pd.to_numeric afterwards.

Normalise a copy of the column, use it to group or compare, and keep the original if you need to show the original. Test it the way a person would: df.city.nunique() before and after tells you how many spellings you merged.

Missing values: skipped or propagated

NaN means "unknown". isna() finds it, fillna(x) replaces it, dropna() removes the row. Most pandas summaries skip NaN by default, so mean() of a column with gaps averages only what is there, and sum() treats gaps as nothing. Arithmetic between columns works differently: a NaN in unit_cost makes unit_price - unit_cost NaN on that row, and the margin for that product disappears.

So there are three honest options, and each makes a different claim. Leave it as NaN and the summary describes the rows that have data. dropna() does the same but makes it explicit. fillna() with a value claims you know what the number would have been. Use fillna(0) only when zero is truly what missing means, for example a missing discount_pct on an order line that had no discount. Everything else is a judgement, and the report should say which one you made.

Types: dates and numbers stored as text

A column of dates read as text sorts alphabetically and cannot be grouped by month. pd.to_datetime turns it into real timestamps, and then .dt.year, .dt.month and .dt.day_name() work. Add errors='coerce' to turn unparseable entries into NaT instead of stopping, then count the NaT values: they are the rows you still have to explain. pd.to_numeric(..., errors='coerce') does the same for numbers.

Duplicates: same entity, different spelling

df.duplicated() marks a row True when an earlier row has identical values in the columns you name. Identical is the key word, so duplicates written differently go through, unless you normalise first.

Choose the key that defines "the same" with care. Same email is a good key for customers. Same first name and city is not: two different people can share a first name and a city. Then decide what to keep with drop_duplicates(subset=..., keep='first'), and look at a few removed rows before you trust it.

Bad rows

A quantity of 0 or -2 is not a typo to fix, it is a row that does not describe a sale. Filter it out with a boolean mask, and count what you removed: (order_items.quantity <= 0).sum() is 24 rows. A cleaning step that silently removes rows is a source of mismatched totals, so a short note of what went and why belongs next to the result.

Common misconceptions

"pandas will treat Dallas and DALLAS as the same city"

It will not. String comparison is exact, in groupby, merge, isin, duplicated and ==. The fix is to normalise the text before the operation. When a count looks too small, run nunique() and value_counts() on the column and look at the tail: the variants are usually sitting there.

"fillna(0) is the safe way to get rid of NaN"

It is the way that hides the problem best. Zero is a number, and it takes part in every mean, sum and ratio that follows. A product with a missing cost does not cost nothing. Fill only with a value you can defend, otherwise leave the NaN visible and say that those rows are not included.

"drop_duplicates() removes the duplicate customers"

Only exact copies of the whole row. Two signups of the same person have different customer_id and signup_date, so drop_duplicates() with no arguments removes nothing. Name the columns that define the entity (subset=) after normalising them, and keep the earliest signup.