The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Count missing values | missing = df.isna().sum() | View examples |
| Select complete rows | complete = df.loc[df[['id', 'email']].notna().all(axis='columns')] | View examples |
| Drop incomplete records | clean = df.dropna(subset=['customer_id', 'ordered_at']) | View examples |
| Fill column defaults | clean = df.fillna({'country': 'unknown', 'quantity': 0}) | View examples |
| Forward-fill briefly | df['status'] = df['status'].ffill(limit=1) | View examples |
| Interpolate numeric gaps | df['reading'] = df['reading'].interpolate(limit_area='inside') | View examples |
| Replace sentinels | df['status'] = df['status'].replace({'N/A': pd.NA, '': pd.NA}) | View examples |
| Normalize text | df['email'] = df['email'].str.strip().str.lower() | View examples |
| Normalize categories | df['status'] = df['status'].replace({'in progress': 'active', 'open': 'active'}) | View examples |
| Parse numeric values | df['amount'] = pd.to_numeric(df['amount'], errors='coerce') | View examples |
| Parse exact dates | df['ordered_at'] = pd.to_datetime(df['ordered_at'], format='%Y-%m-%d', errors='coerce') | View examples |
| Use nullable dtypes | df = df.convert_dtypes() | View examples |
| Find values out of range | invalid = df.loc[df['quantity'].notna() & ~df['quantity'].between(1, 100)] | View examples |
| Find duplicate keys | duplicates = df.loc[df.duplicated('order_id', keep=False)] | View examples |
| Keep the latest record | latest = df.sort_values('updated_at').drop_duplicates('order_id', keep='last') | View examples |
Cleaning is a contract decision, not a sequence of unconditional replacements. Profile missingness and invalid values first, convert with explicit failure behavior, and preserve evidence when an imputation or deduplication rule could change meaning.
Step by step
Detailed examples
Profile missingness before changing data
isna recognizes pandas missing-value sentinels, but it does not treat arbitrary strings such as an empty string or 'N/A' as missing until they are normalized. Count missing values by column and identify incomplete required records before deciding whether to reject, repair, or retain them.
import pandas as pd
df = pd.DataFrame({
'id': [1, 2, 3],
'email': ['ada@example.com', pd.NA, 'sam@example.com'],
'phone': [pd.NA, '555-0102', pd.NA],
})
missing = df.isna().sum()
complete = df.loc[df[['id', 'email']].notna().all(axis='columns')]
print(missing.to_dict())
print(complete['id'].tolist()){'id': 0, 'email': 1, 'phone': 2}
[1, 3]Choose dropping, filling, or interpolation deliberately
Drop rows only when the missing fields make the record unusable. Fill values only when a documented default has valid domain meaning. Forward filling assumes ordering and continuity, while interpolation assumes a meaningful numeric scale; limit their reach so long or edge gaps remain visible instead of acquiring invented values.
import pandas as pd
df = pd.DataFrame({
'customer_id': [1, 2, 3, 4],
'ordered_at': ['2026-08-01', '2026-08-02', None, '2026-08-04'],
'country': ['BR', None, 'US', None],
'quantity': [2, None, 4, None],
'status': ['open', None, None, 'closed'],
'reading': [10.0, None, 14.0, None],
})
df['reading'] = df['reading'].interpolate(limit_area='inside')
clean = df.dropna(subset=['customer_id', 'ordered_at']).fillna({'country': 'unknown', 'quantity': 0})
clean['status'] = clean['status'].ffill(limit=1)
print(clean[['customer_id', 'country', 'quantity', 'status', 'reading']].to_dict('records'))[{'customer_id': 1, 'country': 'BR', 'quantity': 2.0, 'status': 'open', 'reading': 10.0}, {'customer_id': 2, 'country': 'unknown', 'quantity': 0.0, 'status': 'open', 'reading': 12.0}, {'customer_id': 4, 'country': 'unknown', 'quantity': 0.0, 'status': 'closed', 'reading': nan}]Note: The final reading remains missing because limit_area='inside' does not extrapolate beyond observed values.
Normalize sentinels, strings, and vocabularies
Convert source-specific sentinels to one missing-value representation before measuring completeness. Vectorized string accessors preserve missing values and avoid Python row loops. Category mappings should be explicit and reviewed; unmatched values remain unchanged with replace, making unexpected vocabulary visible for validation.
import pandas as pd
df = pd.DataFrame({
'email': [' ADA@EXAMPLE.COM ', 'lin@example.com', None],
'status': ['in progress', 'N/A', 'open'],
})
df['status'] = df['status'].replace({'N/A': pd.NA, '': pd.NA})
df['email'] = df['email'].str.strip().str.lower()
df['status'] = df['status'].replace({'in progress': 'active', 'open': 'active'})
display = df.astype(object).where(df.notna(), '<missing>')
print(display.to_dict('records'))[{'email': 'ada@example.com', 'status': 'active'}, {'email': 'lin@example.com', 'status': '<missing>'}, {'email': '<missing>', 'status': 'active'}]Note: The serialized representation of a missing scalar can vary by dtype; use isna to test missingness.
Convert types with observable failures
to_numeric and to_datetime with errors='coerce' make parsing failures measurable as missing values instead of silently preserving mixed object data. Use an exact format when the input contract defines one. convert_dtypes selects nullable pandas dtypes, but semantic constraints such as ranges, allowed values, and cross-field rules still require separate validation.
import pandas as pd
df = pd.DataFrame({
'amount': ['12.50', 'bad', '9.00'],
'quantity': ['2', '0', 'many'],
'ordered_at': ['2026-08-01', '08/02/2026', '2026-08-03'],
})
df['amount'] = pd.to_numeric(df['amount'], errors='coerce')
df['quantity'] = pd.to_numeric(df['quantity'], errors='coerce')
df['ordered_at'] = pd.to_datetime(df['ordered_at'], format='%Y-%m-%d', errors='coerce')
df = df.convert_dtypes()
invalid = df.loc[df['quantity'].notna() & ~df['quantity'].between(1, 100)]
print(df.isna().sum().to_dict())
print(invalid.index.tolist()){'amount': 1, 'quantity': 1, 'ordered_at': 1}
[1]Make duplicate resolution deterministic
duplicated with keep=False exposes every conflicting row so the conflict can be audited. If the business rule is to keep the latest record, parse and sort the timestamp first, then drop duplicates with an explicit key and keep policy. Equal timestamps need an additional stable tie-breaker; otherwise input order decides the winner.
import pandas as pd
df = pd.DataFrame({
'order_id': [10, 11, 10, 12],
'status': ['open', 'paid', 'paid', 'open'],
'updated_at': pd.to_datetime(['2026-08-01T10:00Z', '2026-08-01T11:00Z', '2026-08-01T12:00Z', '2026-08-01T09:00Z']),
})
duplicates = df.loc[df.duplicated('order_id', keep=False)]
latest = df.sort_values('updated_at').drop_duplicates('order_id', keep='last')
print(duplicates['order_id'].tolist())
print(latest.sort_values('order_id')[['order_id', 'status']].to_dict('records'))[10, 10]
[{'order_id': 10, 'status': 'paid'}, {'order_id': 11, 'status': 'paid'}, {'order_id': 12, 'status': 'open'}]Local code tester
Clean a small customer dataset
Experiment with missing-value, text-normalization, and numeric-conversion policies.
Press Run to load Python locally.
Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.