Real data arrives with missing values, wrong types and duplicates. The job is not to make them disappear but to decide, explicitly, what each one means - and to leave a record of the decision.
Missing is not one thing
import numpy as np
import pandas as pd
df = pd.DataFrame({
'id': [1, 2, 3, 4, 5],
'units': [120, np.nan, 143, 60, np.nan],
'discount': [0.0, 0.1, np.nan, 0.0, 0.0],
'note': ['ok', None, '', 'n/a', 'ok'],
})
print(df.isna().sum().to_dict())
print(df.isna().mean().round(2).to_dict()) # as a proportion
Open this example in the editor and change it. Nothing you type is saved.
Before filling anything, ask what the blank means. There are at least three answers, and they want different treatment:
- Not applicable. No discount exists for this row.
0is correct, and so is leaving it missing - but say which. - Not recorded. The value exists and nobody captured it. Filling it with a mean invents data; dropping the row loses the rest of it.
- Not yet. A delivery date for an order that has not shipped. Filling it is wrong at any value.
Notice also that '' and 'n/a' above are not missing as far as pandas is concerned. They are strings, and isna() says False:
import pandas as pd
import numpy as np
s = pd.Series(['ok', None, '', 'n/a', np.nan])
print(s.isna().tolist()) # the blanks pandas knows
print(s.replace(['', 'n/a'], np.nan).isna().tolist()) # after you say so
Open this example in the editor and change it. Nothing you type is saved.
Then choose, and say so
import numpy as np
import pandas as pd
df = pd.DataFrame({
'id': [1, 2, 3, 4, 5],
'units': [120.0, np.nan, 143.0, 60.0, np.nan],
'region': ['North', 'South', None, 'East', 'North'],
})
# Drop rows missing something essential.
essential = df.dropna(subset=['units'])
print('dropna on units:', len(essential), 'of', len(df))
# Fill, with a value that says "none", not a value that looks real.
filled = df.assign(region=df['region'].fillna('unknown'))
print(filled['region'].tolist())
# Fill forward, which is right for a time series and wrong otherwise.
readings = pd.Series([1.0, np.nan, np.nan, 4.0])
print(readings.ffill().tolist())
print(readings.interpolate().tolist())
# Keep a flag, so a model and a reader both know it was imputed.
marked = df.assign(
units_missing=df['units'].isna(),
units=df['units'].fillna(df['units'].median()),
)
print(marked[['units', 'units_missing']])
Open this example in the editor and change it. Nothing you type is saved.
The flag column is the habit worth adopting. Filling with the median is often reasonable; filling silently is not, because every downstream chart and model then treats an invention as a measurement.
Dropping needs the same care:
import numpy as np
import pandas as pd
df = pd.DataFrame({
'a': [1, np.nan, 3],
'b': [np.nan, np.nan, 3],
'c': [1, 2, 3],
})
print(len(df.dropna())) # 1 - any missing anywhere
print(len(df.dropna(how='all'))) # 3 - only rows that are entirely blank
print(len(df.dropna(subset=['a']))) # 2 - only the column that matters
print(df.dropna(axis=1, thresh=3).columns.tolist()) # columns with 3 non-null
Open this example in the editor and change it. Nothing you type is saved.
df.dropna() with no arguments drops a row if any column is missing, which on a wide table throws away nearly everything. Name the columns you actually need.
Dtypes that arrived wrong
import pandas as pd
df = pd.DataFrame({
'amount': ['1200', '950', '1,430', 'n/a', '600'],
'started': ['2026-01-15', '2026-01-16', 'not a date', '2026-02-01', '2026-02-03'],
'active': ['yes', 'no', 'yes', 'YES', 'no'],
})
print(df.dtypes.to_dict())
# to_numeric with errors='coerce' turns what cannot convert into NaN,
# which is the honest outcome - you then decide what to do with them.
df['amount'] = pd.to_numeric(df['amount'].str.replace(',', '', regex=False), errors='coerce')
df['started'] = pd.to_datetime(df['started'], errors='coerce')
df['active'] = df['active'].str.lower().map({'yes': True, 'no': False})
print(df.dtypes.to_dict())
print(df)
print('unconvertible amounts:', int(df['amount'].isna().sum()))
print('unparseable dates: ', int(df['started'].isna().sum()))
Open this example in the editor and change it. Nothing you type is saved.
errors='coerce' is the right default at a boundary: it converts what it can and marks the rest, so you find out how many rows were bad instead of the whole call raising on the first one.
map with a dictionary is better than replace for a known set of values, because anything not in the dictionary becomes NaN - so an unexpected value shows up rather than passing through.
Two dtypes worth choosing on purpose:
import pandas as pd
import numpy as np
n = 100_000
repeated = pd.Series(np.random.default_rng(0).choice(['North', 'South', 'East'], n))
as_object = repeated.memory_usage(deep=True)
as_category = repeated.astype('category').memory_usage(deep=True)
print(f'object: {as_object:>9,} bytes')
print(f'category: {as_category:>9,} bytes')
print('smaller:', as_category < as_object / 10)
# A nullable integer, so a column with blanks stays an integer.
counts = pd.Series([1, 2, None, 4])
print(counts.dtype) # float64 - the blank forced it
print(counts.astype('Int64').dtype) # Int64 - capital I, nullable
print(counts.astype('Int64').tolist())
Open this example in the editor and change it. Nothing you type is saved.
Duplicates
import pandas as pd
df = pd.DataFrame({
'order_id': [1, 2, 2, 3, 3],
'region': ['North', 'South', 'South', 'East', 'East'],
'units': [120, 95, 95, 60, 61],
'loaded_at': ['a', 'a', 'b', 'a', 'b'],
})
print('fully identical rows:', int(df.duplicated().sum()))
print('repeated order ids: ', int(df.duplicated(subset=['order_id']).sum()))
print()
# Which ones, so you can look at them.
print(df[df.duplicated(subset=['order_id'], keep=False)].sort_values('order_id'))
print()
# Keeping the last is a choice; say why.
deduped = df.sort_values('loaded_at').drop_duplicates(subset=['order_id'], keep='last')
print(deduped)
Open this example in the editor and change it. Nothing you type is saved.
Row 3 and row 4 share an order_id and disagree on units. That is not a duplicate export; it is a conflict, and dropping one silently picks a winner at random unless you sort first. Always look at keep=False before you drop anything.
Strings
import pandas as pd
names = pd.Series([' Ann ', 'BO', 'cy ', 'Ann', 'Dee-Ann'])
print(names.str.strip().str.title().tolist())
print(names.str.strip().str.lower().value_counts().to_dict())
print(names.str.contains('ann', case=False).tolist())
print(names.str.len().tolist())
# Splitting into columns.
full = pd.Series(['North/web', 'South/phone', 'East/store'])
print(full.str.split('/', expand=True).rename(columns={0: 'region', 1: 'channel'}))
# Extracting with a pattern, which returns NaN where it does not match.
codes = pd.Series(['ORD-1001', 'ORD-1002', 'invalid', 'ORD-1004'])
print(codes.str.extract(r'ORD-(?P<number>[0-9]+)'))
Open this example in the editor and change it. Nothing you type is saved.
The .str accessor applies a string method to every element and returns NaN for missing values rather than raising. Two habits: .str.strip() on anything from a spreadsheet, and .str.lower() before any comparison or grouping, because 'North' and 'north' are two groups otherwise.
A worked example
import io
import numpy as np
import pandas as pd
RAW = """order_id,placed_on,region,units,amount,status
1001,2026-01-15,North,120,"1,500.00",complete
1002,16/01/2026,south,95,1187.50,COMPLETE
1003,2026-01-16,North,,n/a,pending
1004,2026-02-01,East ,60,840.00,complete
1004,2026-02-01,East,60,840.00,complete
1005,not a date,West,-5,"2,100.00",cancelled
1006,2026-02-03,north,143,1573.00,Complete
"""
df = pd.read_csv(io.StringIO(RAW), dtype=str)
print('as loaded: every column is text,', df.shape)
print()
report = {}
# 1. Whitespace and case FIRST. Deduplicating before this step misses
# the pair that differ only by a trailing space - which is exactly
# the pair in this file.
df['region'] = df['region'].str.strip().str.title()
df['status'] = df['status'].str.strip().str.lower()
report['regions'] = sorted(df['region'].unique().tolist())
# 2. Now the exact duplicates are findable.
before = len(df)
df = df.drop_duplicates()
report['exact duplicates dropped'] = before - len(df)
# 3. Numbers that arrived as text, with thousands separators.
df['units'] = pd.to_numeric(df['units'], errors='coerce')
df['amount'] = pd.to_numeric(
df['amount'].str.replace(',', '', regex=False), errors='coerce')
report['unconvertible amounts'] = int(df['amount'].isna().sum())
# 4. Mixed date formats. Two passes: ISO first, then day-first for
# whatever is left, so neither format is misread as the other.
iso = pd.to_datetime(df['placed_on'], format='%Y-%m-%d', errors='coerce')
dayfirst = pd.to_datetime(df['placed_on'], format='%d/%m/%Y', errors='coerce')
df['placed_on'] = iso.fillna(dayfirst)
report['unparseable dates'] = int(df['placed_on'].isna().sum())
# 5. Values that are present and impossible.
negative = df['units'] < 0
report['negative units'] = int(negative.sum())
df.loc[negative, 'units'] = np.nan
# 6. A flag before filling, so nothing invented is mistaken for
# something measured.
df['units_imputed'] = df['units'].isna()
# round() because units are whole things; a median of 107.5 cannot be
# cast to an integer dtype, and silently keeping it a float would hide
# that the column is supposed to be countable.
df['units'] = df['units'].fillna(round(df['units'].median())).astype('Int64')
# 7. Categories for the columns with few values, and a real integer
# for the id now that it is known to be clean.
df['order_id'] = df['order_id'].astype('int64')
df['region'] = df['region'].astype('category')
df['status'] = df['status'].astype('category')
print(df)
print()
print(df.dtypes.to_dict())
print()
for key, value in report.items():
print(f'{key:26} {value}')
print()
# What is still unusable, named rather than silently dropped.
unusable = df[df['amount'].isna() | df['placed_on'].isna()]
print(f'{len(unusable)} row(s) still unusable:')
print(unusable[['order_id', 'placed_on', 'amount', 'status']])
print()
usable = df.dropna(subset=['amount', 'placed_on'])
print(f'{len(usable)} of {len(df)} rows usable, '
f'{usable["units_imputed"].sum()} with imputed units')
print(f'total amount {usable["amount"].sum():,.2f}')
Open this example in the editor and change it. Nothing you type is saved.
Six decisions are made there and every one is recorded. The duplicate drop is counted. The imputed values carry a flag. The negative units are turned into missing rather than kept or quietly zeroed. The unusable rows are printed rather than dropped in silence. A reader of the output can see exactly what happened to the seven input rows.
The two-pass date parsing is the piece worth stealing: a single to_datetime with dayfirst=True would read 2026-01-15 correctly by luck and 01/02/2026 as 1 February, which may be right or may be a month out. Parsing each format explicitly and combining with fillna removes the guess.
When it goes wrong
| Symptom | Cause |
|---|---|
dropna() removed almost every row |
It drops on any column; pass subset= |
| An integer column became a float | A missing value; use the nullable Int64 |
'North' and 'north' are two groups |
No .str.lower() before grouping |
'' was not counted as missing |
It is a string; replace it first |
| A date is a month out | Ambiguous format; parse each explicitly |
to_numeric raised on the first bad row |
Pass errors='coerce' |
| Dropping duplicates lost real data | The rows differed; inspect with keep=False |
| A model treats imputed values as real | Keep an _imputed flag column |
A check you can run
Take any cleaning script you have and add one line after every step:
import numpy as np
import pandas as pd
df = pd.DataFrame({'a': [1, np.nan, 3, 3], 'b': ['x', 'y', None, None]})
def audit(label, frame):
print(f'{label:<22} rows={len(frame):<4} missing={int(frame.isna().sum().sum())}')
audit('loaded', df)
df = df.drop_duplicates()
audit('deduplicated', df)
df = df.dropna(subset=['a'])
audit('dropped missing a', df)
df['b'] = df['b'].fillna('unknown')
audit('filled b', df)
Open this example in the editor and change it. Nothing you type is saved.
Run it. The row count and the missing-value count after each step is the audit trail, and the step where one of them moves unexpectedly is the bug. Most cleaning scripts lose rows somewhere nobody intended, and this is the cheapest way to find out where.