PythonMastery
intermediate 20 min read · lesson 4 of 9 in Data Science & ML

Data Cleaning & Wrangling

1 · The lesson

read

Courses often don't say this clearly: cleaning usually takes more of a data science project than the model does. The model at the end (gradient boosting, a transformer, or anything else) is only one part. Much of the work is fixing the data the source system gave you: nulls in columns the schema said were NOT NULL, date strings in five different formats, the same customer entered as "Acme Corp", "acme corp." and "ACME Corp" with two spaces.

This lesson gives you a cleaning checklist: six kinds of problem and the pandas operations that fix them. It also shows how to build a single idempotent clean(df) -> df pipeline that you can safely run again and again.

1. The Cleaning Checklist

Before any modelling, check every dataset for these six kinds of problem, in this order:

1. Missing values: what is NaN, and what looks present but is really a sentinel (-1, "N/A", 9999)?
2. Duplicates: exact duplicate rows and rows with a duplicated key.
3. Wrong types: numbers stored as strings, booleans as "Y"/"N", categories as object dtype.
4. Outliers: flag them with IQR or z-scores, then decide for each column whether to clip, drop or keep them.
5. Inconsistent categories: "USA", "U.S.A.", "us", " usa " all meaning the same country.
6. Date parsing: mixed formats, timezones, two-digit years.

Run them in that order. Step 1 is about finding the missing values. Fill them in only after you remove duplicates: filling first wastes effort, and the duplicates would skew the values you fill with. Fix types before outliers, because converting types after fixing outliers can cut off decimals in a float→int conversion you didn't expect. The order matters.

python
import pandas as pd
import numpy as np

df = pd.DataFrame({
    "id":    [1, 2, 2, 3, 4, 5],
    "email": ["A@x.com", " b@x.com ", " b@x.com ", "c@x", None, "e@x.com"],
    "age":   ["25", "30", "30", "-1", "200", "40"],
    "joined":["2026-01-05", "01/06/2026", "01/06/2026", "2026-02-30", None, "2026-04-01"],
})
df.info()

We'll come back to this dataset in Section 8.

2. Missing Values: Look First, Decide Second

The first command in any cleaning session:

python
df.isna().sum().sort_values(ascending=False)
# email     1
# joined    1
# age       0  ← suspicious: see Section 2.1
# id        0
+ setup added so this can run · re-runs 1 earlier block on this page first
# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')

# Earlier blocks on this page that this one builds on, re-run quietly first.
try:
    import pandas as pd
    import numpy as np
except Exception:
    pass
_pm_earlier = [
    'import pandas as pd\nimport numpy as np\n\ndf = pd.DataFrame({\n    "id":    [1, 2, 2, 3, 4, 5],\n    "email": ["A@x.com", " b@x.com ", " b@x.com ", "c@x", None, "e@x.com"],\n    "age":   ["25", "30", "30", "-1", "200", "40"],\n    "joined":["2026-01-05", "01/06/2026", "01/06/2026", "2026-02-30", None, "2026-04-01"],\n})\ndf.info()',
]
def _pm_run_earlier(_srcs, _g):
    import sys as _s, io as _io, warnings as _w
    for _src in _srcs:
        _o, _e = _s.stdout, _s.stderr
        _s.stdout = _s.stderr = _io.StringIO()
        try:
            with _w.catch_warnings():
                _w.simplefilter('ignore')
                exec(compile(_src, '<earlier block>', 'exec'), _g)
        except (Exception, SystemExit):
            pass
        finally:
            _s.stdout, _s.stderr = _o, _e
    if 'matplotlib.pyplot' in _s.modules:
        _s.modules['matplotlib.pyplot'].close('all')   # their plots belong to their own blocks
_pm_run_earlier(_pm_earlier, globals())
del _pm_earlier, _pm_run_earlier

isna() only catches actual NaN/None. Sentinel values, such as -1 for a missing age, 9999 for a missing zip code or "N/A" as a string, count as "present" until you convert them:

python
df["age"] = df["age"].replace({"-1": np.nan, "N/A": np.nan})
+ setup added so this can run · re-runs 2 earlier blocks on this page first
# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')
np = _AutoMock('np')

# Earlier blocks on this page that this one builds on, re-run quietly first.
try:
    import pandas as pd
    import numpy as np
except Exception:
    pass
_pm_earlier = [
    'import pandas as pd\nimport numpy as np\n\ndf = pd.DataFrame({\n    "id":    [1, 2, 2, 3, 4, 5],\n    "email": ["A@x.com", " b@x.com ", " b@x.com ", "c@x", None, "e@x.com"],\n    "age":   ["25", "30", "30", "-1", "200", "40"],\n    "joined":["2026-01-05", "01/06/2026", "01/06/2026", "2026-02-30", None, "2026-04-01"],\n})\ndf.info()',
    'df.isna().sum().sort_values(ascending=False)\n# email     1\n# joined    1\n# age       0  ← suspicious: see Section 2.1\n# id        0',
]
def _pm_run_earlier(_srcs, _g):
    import sys as _s, io as _io, warnings as _w
    for _src in _srcs:
        _o, _e = _s.stdout, _s.stderr
        _s.stdout = _s.stderr = _io.StringIO()
        try:
            with _w.catch_warnings():
                _w.simplefilter('ignore')
                exec(compile(_src, '<earlier block>', 'exec'), _g)
        except (Exception, SystemExit):
            pass
        finally:
            _s.stdout, _s.stderr = _o, _e
    if 'matplotlib.pyplot' in _s.modules:
        _s.modules['matplotlib.pyplot'].close('all')   # their plots belong to their own blocks
_pm_run_earlier(_pm_earlier, globals())
del _pm_earlier, _pm_run_earlier

Always do a value_counts(dropna=False) on suspect columns before treating them as numeric. If the minimum age is -1, and nobody can be -1 years old, then -1 is a sentinel.

Imputation strategies

StrategyWhen
Drop rows (df.dropna(subset=[...]))Only a tiny fraction is missing, and losing those rows doesn't matter.
Drop columns (df.drop(columns=...))A column is >50% missing and you can't explain why.
Mean / median fillNumeric, missing-at-random, distribution roughly symmetric (mean) or skewed (median).
Mode fillLow-cardinality categorical.
Forward fill (df.ffill())Time-series where "last known value" is the right default.
Model-based (sklearn.impute.IterativeImputer)Many features missing in patterns, want to use other columns to predict.
python
df["age"] = pd.to_numeric(df["age"], errors="coerce")
df["age"] = df["age"].fillna(df["age"].median())

# Time-series forward fill
ts = pd.Series([1.0, np.nan, np.nan, 4.0])
ts.ffill()                              # 1, 1, 1, 4

# Model-based: uses other columns to estimate each missing value
from sklearn.experimental import enable_iterative_imputer  # noqa: F401
from sklearn.impute import IterativeImputer
imp = IterativeImputer(random_state=0)
# imp.fit_transform(df[numeric_cols])
+ setup added so this can run · re-runs 3 earlier blocks on this page first
# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')
pd = _AutoMock('pd')
np = _AutoMock('np')

# Earlier blocks on this page that this one builds on, re-run quietly first.
try:
    import pandas as pd
    import numpy as np
except Exception:
    pass
_pm_earlier = [
    'import pandas as pd\nimport numpy as np\n\ndf = pd.DataFrame({\n    "id":    [1, 2, 2, 3, 4, 5],\n    "email": ["A@x.com", " b@x.com ", " b@x.com ", "c@x", None, "e@x.com"],\n    "age":   ["25", "30", "30", "-1", "200", "40"],\n    "joined":["2026-01-05", "01/06/2026", "01/06/2026", "2026-02-30", None, "2026-04-01"],\n})\ndf.info()',
    'df.isna().sum().sort_values(ascending=False)\n# email     1\n# joined    1\n# age       0  ← suspicious: see Section 2.1\n# id        0',
    'df["age"] = df["age"].replace({"-1": np.nan, "N/A": np.nan})',
]
def _pm_run_earlier(_srcs, _g):
    import sys as _s, io as _io, warnings as _w
    for _src in _srcs:
        _o, _e = _s.stdout, _s.stderr
        _s.stdout = _s.stderr = _io.StringIO()
        try:
            with _w.catch_warnings():
                _w.simplefilter('ignore')
                exec(compile(_src, '<earlier block>', 'exec'), _g)
        except (Exception, SystemExit):
            pass
        finally:
            _s.stdout, _s.stderr = _o, _e
    if 'matplotlib.pyplot' in _s.modules:
        _s.modules['matplotlib.pyplot'].close('all')   # their plots belong to their own blocks
_pm_run_earlier(_pm_earlier, globals())
del _pm_earlier, _pm_run_earlier

Never use mean fill just because it is the default. Every imputation adds an assumption to your data. Write it down.

3. Duplicates: Exact and Key-Level

duplicated() marks every repeat after the first as a duplicate. Add subset to remove duplicates by business key instead of by the full row:

python
df.duplicated().sum()                                  # rows that exactly repeat an earlier row
df.duplicated(subset=["id"], keep="first").sum()       # repeated ids, not counting the first of each
df.duplicated(subset=["id"], keep=False).sum()         # ALL rows whose id appears more than once

# Drop, keeping the first occurrence
df = df.drop_duplicates(subset=["id"], keep="first")

Choosing keep="first" (the default) or "last" is a business decision: which version of the duplicate is the correct one? Often last if rows arrive in update order, or first if you trust the original. keep=False doesn't remove anything. It marks every row that is part of a duplicate, so you can inspect them all. Do this when you don't trust the data yet.

4. Type Cleanup

object is the dtype pandas uses when it has no better type. It usually means strings, but it also covers mixed types. Convert to real dtypes:

python
df["age"]    = pd.to_numeric(df["age"], errors="coerce")  # values that fail to parse become NaN, no error
df["count"]  = df["count"].astype("int64")                # only after you're sure no NaN
df["active"] = df["active"].map({"Y": True, "N": False})  # explicit mapping, no "truthy" guessing
df["plan"]   = df["plan"].astype("category")              # few distinct strings, so use category
+ setup added so this can run · defines df, pd
# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')
pd = _AutoMock('pd')

Two reasons to convert low-cardinality strings to category:


  • Memory: a million-row column with three plan tiers becomes a tiny int-coded array.

  • Meaning: later code (groupby, plots) treats it as a set of categories, not as free text.

errors="coerce" is very useful for type conversions. It returns NaN for values it can't parse, instead of raising an error. You can then count them and decide what to do.

5. Outliers: Detection First, Action Second

Two standard detectors:

python
# IQR rule: works for any distribution and resists outliers
q1, q3 = df["age"].quantile([0.25, 0.75])
iqr = q3 - q1
lo, hi = q1 - 1.5 * iqr, q3 + 1.5 * iqr
outliers = df[(df["age"] < lo) | (df["age"] > hi)]

# Z-score: assumes the data is roughly normal
from scipy import stats
z = np.abs(stats.zscore(df["age"].dropna()))
# rows with |z| > 3 are conventionally "outliers"
+ setup added so this can run · re-runs 6 earlier blocks on this page first
# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')
np = _AutoMock('np')
pd = _AutoMock('pd')

# Earlier blocks on this page that this one builds on, re-run quietly first.
try:
    import pandas as pd
    import numpy as np
    from sklearn.experimental import enable_iterative_imputer
    from sklearn.impute import IterativeImputer
except Exception:
    pass
_pm_earlier = [
    'import pandas as pd\nimport numpy as np\n\ndf = pd.DataFrame({\n    "id":    [1, 2, 2, 3, 4, 5],\n    "email": ["A@x.com", " b@x.com ", " b@x.com ", "c@x", None, "e@x.com"],\n    "age":   ["25", "30", "30", "-1", "200", "40"],\n    "joined":["2026-01-05", "01/06/2026", "01/06/2026", "2026-02-30", None, "2026-04-01"],\n})\ndf.info()',
    'df.isna().sum().sort_values(ascending=False)\n# email     1\n# joined    1\n# age       0  ← suspicious: see Section 2.1\n# id        0',
    'df["age"] = df["age"].replace({"-1": np.nan, "N/A": np.nan})',
    'df["age"] = pd.to_numeric(df["age"], errors="coerce")\ndf["age"] = df["age"].fillna(df["age"].median())\n\n# Time-series forward fill\nts = pd.Series([1.0, np.nan, np.nan, 4.0])\nts.ffill()                              # 1, 1, 1, 4\n\n# Model-based: uses other columns to estimate each missing value\nfrom sklearn.experimental import enable_iterative_imputer  # noqa: F401\nfrom sklearn.impute import IterativeImputer\nimp = IterativeImputer(random_state=0)\n# imp.fit_transform(df[numeric_cols])',
    'df.duplicated().sum()                                  # rows that exactly repeat an earlier row\ndf.duplicated(subset=["id"], keep="first").sum()       # repeated ids, not counting the first of each\ndf.duplicated(subset=["id"], keep=False).sum()         # ALL rows whose id appears more than once\n\n# Drop, keeping the first occurrence\ndf = df.drop_duplicates(subset=["id"], keep="first")',
    'df["age"]    = pd.to_numeric(df["age"], errors="coerce")  # values that fail to parse become NaN, no error\ndf["count"]  = df["count"].astype("int64")                # only after you\'re sure no NaN\ndf["active"] = df["active"].map({"Y": True, "N": False})  # explicit mapping, no "truthy" guessing\ndf["plan"]   = df["plan"].astype("category")              # few distinct strings, so use category',
]
def _pm_run_earlier(_srcs, _g):
    import sys as _s, io as _io, warnings as _w
    for _src in _srcs:
        _o, _e = _s.stdout, _s.stderr
        _s.stdout = _s.stderr = _io.StringIO()
        try:
            with _w.catch_warnings():
                _w.simplefilter('ignore')
                exec(compile(_src, '<earlier block>', 'exec'), _g)
        except (Exception, SystemExit):
            pass
        finally:
            _s.stdout, _s.stderr = _o, _e
    if 'matplotlib.pyplot' in _s.modules:
        _s.modules['matplotlib.pyplot'].close('all')   # their plots belong to their own blocks
_pm_run_earlier(_pm_earlier, globals())
del _pm_earlier, _pm_run_earlier

What to do with them depends on what the data is about, not on statistics:

ActionWhen
Clip (df["age"].clip(lower=lo, upper=hi))The value is implausible but the row is otherwise useful.
DropClearly bad data: a typo, a sensor failure, a test row.
Flag (df["age_outlier"] = ...)Model might benefit from a "this was extreme" feature.
KeepA 200-year-old person in a census is a data error, but a 200-year-old company is real.

"Outlier" does not mean "wrong". A few CEOs who earn 1000× the median are not noise. They are a real and important part of the income distribution.

6. Inconsistent Categories

Free-text category columns are where most dirty data is found. Audit with value_counts:

python
df["country"].value_counts(dropna=False)
# USA      3120
# us        412
# U.S.A.    198
#  usa       45      ← leading space
# United States  12
# NaN        3
+ setup added so this can run · defines df
# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')

Five spellings, one country. Normalise with .str accessors:

python
df["country"] = (
    df["country"]
      .str.strip()
      .str.lower()
      .str.replace(r"\.", "", regex=True)
)
# now: usa, us, united states. Merge them with a map
df["country"] = df["country"].replace({
    "us": "usa",
    "united states": "usa",
    "u s a": "usa",
})
+ setup added so this can run · defines df
# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')

For messy strings with many rare variants, such as vendor names and product titles, use fuzzy matching:

python
from rapidfuzz import process, fuzz, utils
choices = ["Apple Inc.", "Microsoft Corp.", "Alphabet Inc."]
process.extractOne("appel inc", choices, scorer=fuzz.ratio, processor=utils.default_process)
# ('Apple Inc.', 88.88888888888889, 0)   because default_process lowercases and removes punctuation first

rapidfuzz is a fast fuzzywuzzy replacement. Use it to map free-text values to a controlled vocabulary, then store the cleaned-up standard form.

7. Date Parsing: A Constant Problem

Mixed date formats are normal, not rare:

python
s = pd.Series(["2026-01-05", "01/06/2026", "Feb 30, 2026", None])

# Coerce: values that fail to parse become NaT (Not-a-Time)
pd.to_datetime(s, errors="coerce")
# 0   2026-01-05
# 1          NaT       ← pandas 2 takes the format from the first value. Add format="mixed" (and dayfirst=) for mixed input
# 2          NaT
# 3          NaT
+ setup added so this can run · re-runs 6 earlier blocks on this page first
# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

pd = _AutoMock('pd')
df = _AutoMock('df')
np = _AutoMock('np')

# Earlier blocks on this page that this one builds on, re-run quietly first.
try:
    import pandas as pd
    import numpy as np
    from sklearn.experimental import enable_iterative_imputer
    from sklearn.impute import IterativeImputer
except Exception:
    pass
_pm_earlier = [
    'import pandas as pd\nimport numpy as np\n\ndf = pd.DataFrame({\n    "id":    [1, 2, 2, 3, 4, 5],\n    "email": ["A@x.com", " b@x.com ", " b@x.com ", "c@x", None, "e@x.com"],\n    "age":   ["25", "30", "30", "-1", "200", "40"],\n    "joined":["2026-01-05", "01/06/2026", "01/06/2026", "2026-02-30", None, "2026-04-01"],\n})\ndf.info()',
    'df.isna().sum().sort_values(ascending=False)\n# email     1\n# joined    1\n# age       0  ← suspicious: see Section 2.1\n# id        0',
    'df["age"] = df["age"].replace({"-1": np.nan, "N/A": np.nan})',
    'df["age"] = pd.to_numeric(df["age"], errors="coerce")\ndf["age"] = df["age"].fillna(df["age"].median())\n\n# Time-series forward fill\nts = pd.Series([1.0, np.nan, np.nan, 4.0])\nts.ffill()                              # 1, 1, 1, 4\n\n# Model-based: uses other columns to estimate each missing value\nfrom sklearn.experimental import enable_iterative_imputer  # noqa: F401\nfrom sklearn.impute import IterativeImputer\nimp = IterativeImputer(random_state=0)\n# imp.fit_transform(df[numeric_cols])',
    'df.duplicated().sum()                                  # rows that exactly repeat an earlier row\ndf.duplicated(subset=["id"], keep="first").sum()       # repeated ids, not counting the first of each\ndf.duplicated(subset=["id"], keep=False).sum()         # ALL rows whose id appears more than once\n\n# Drop, keeping the first occurrence\ndf = df.drop_duplicates(subset=["id"], keep="first")',
    'df["age"]    = pd.to_numeric(df["age"], errors="coerce")  # values that fail to parse become NaN, no error\ndf["count"]  = df["count"].astype("int64")                # only after you\'re sure no NaN\ndf["active"] = df["active"].map({"Y": True, "N": False})  # explicit mapping, no "truthy" guessing\ndf["plan"]   = df["plan"].astype("category")              # few distinct strings, so use category',
]
def _pm_run_earlier(_srcs, _g):
    import sys as _s, io as _io, warnings as _w
    for _src in _srcs:
        _o, _e = _s.stdout, _s.stderr
        _s.stdout = _s.stderr = _io.StringIO()
        try:
            with _w.catch_warnings():
                _w.simplefilter('ignore')
                exec(compile(_src, '<earlier block>', 'exec'), _g)
        except (Exception, SystemExit):
            pass
        finally:
            _s.stdout, _s.stderr = _o, _e
    if 'matplotlib.pyplot' in _s.modules:
        _s.modules['matplotlib.pyplot'].close('all')   # their plots belong to their own blocks
_pm_run_earlier(_pm_earlier, globals())
del _pm_earlier, _pm_run_earlier

Two problems that pandas can't detect for you:

  • Day-vs-month ambiguity: 01/06/2026 is either 1 June or 6 January depending on locale. Pass dayfirst=True (UK/EU) or dayfirst=False (US) explicitly. Don't guess.
  • Timezones: pd.to_datetime("2026-01-05") is timezone-naïve. pd.to_datetime("2026-01-05", utc=True) is UTC-aware. Mixing naïve and aware in the same column raises errors later. Pick one and use it everywhere. See datetime for the details.

For known formats, pass format=. It is faster and removes the ambiguity:

python
pd.to_datetime(s, format="%Y-%m-%d", errors="coerce")
+ setup added so this can run · re-runs 7 earlier blocks on this page first
# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

s = _AutoMock('s')
pd = _AutoMock('pd')
df = _AutoMock('df')
np = _AutoMock('np')

# Earlier blocks on this page that this one builds on, re-run quietly first.
try:
    import pandas as pd
    import numpy as np
    from sklearn.experimental import enable_iterative_imputer
    from sklearn.impute import IterativeImputer
except Exception:
    pass
_pm_earlier = [
    'import pandas as pd\nimport numpy as np\n\ndf = pd.DataFrame({\n    "id":    [1, 2, 2, 3, 4, 5],\n    "email": ["A@x.com", " b@x.com ", " b@x.com ", "c@x", None, "e@x.com"],\n    "age":   ["25", "30", "30", "-1", "200", "40"],\n    "joined":["2026-01-05", "01/06/2026", "01/06/2026", "2026-02-30", None, "2026-04-01"],\n})\ndf.info()',
    'df.isna().sum().sort_values(ascending=False)\n# email     1\n# joined    1\n# age       0  ← suspicious: see Section 2.1\n# id        0',
    'df["age"] = df["age"].replace({"-1": np.nan, "N/A": np.nan})',
    'df["age"] = pd.to_numeric(df["age"], errors="coerce")\ndf["age"] = df["age"].fillna(df["age"].median())\n\n# Time-series forward fill\nts = pd.Series([1.0, np.nan, np.nan, 4.0])\nts.ffill()                              # 1, 1, 1, 4\n\n# Model-based: uses other columns to estimate each missing value\nfrom sklearn.experimental import enable_iterative_imputer  # noqa: F401\nfrom sklearn.impute import IterativeImputer\nimp = IterativeImputer(random_state=0)\n# imp.fit_transform(df[numeric_cols])',
    'df.duplicated().sum()                                  # rows that exactly repeat an earlier row\ndf.duplicated(subset=["id"], keep="first").sum()       # repeated ids, not counting the first of each\ndf.duplicated(subset=["id"], keep=False).sum()         # ALL rows whose id appears more than once\n\n# Drop, keeping the first occurrence\ndf = df.drop_duplicates(subset=["id"], keep="first")',
    'df["age"]    = pd.to_numeric(df["age"], errors="coerce")  # values that fail to parse become NaN, no error\ndf["count"]  = df["count"].astype("int64")                # only after you\'re sure no NaN\ndf["active"] = df["active"].map({"Y": True, "N": False})  # explicit mapping, no "truthy" guessing\ndf["plan"]   = df["plan"].astype("category")              # few distinct strings, so use category',
    's = pd.Series(["2026-01-05", "01/06/2026", "Feb 30, 2026", None])\n\n# Coerce: values that fail to parse become NaT (Not-a-Time)\npd.to_datetime(s, errors="coerce")\n# 0   2026-01-05\n# 1          NaT       ← pandas 2 takes the format from the first value. Add format="mixed" (and dayfirst=) for mixed input\n# 2          NaT\n# 3          NaT',
]
def _pm_run_earlier(_srcs, _g):
    import sys as _s, io as _io, warnings as _w
    for _src in _srcs:
        _o, _e = _s.stdout, _s.stderr
        _s.stdout = _s.stderr = _io.StringIO()
        try:
            with _w.catch_warnings():
                _w.simplefilter('ignore')
                exec(compile(_src, '<earlier block>', 'exec'), _g)
        except (Exception, SystemExit):
            pass
        finally:
            _s.stdout, _s.stderr = _o, _e
    if 'matplotlib.pyplot' in _s.modules:
        _s.modules['matplotlib.pyplot'].close('all')   # their plots belong to their own blocks
_pm_run_earlier(_pm_earlier, globals())
del _pm_earlier, _pm_run_earlier

8. Column Renaming: Choose One Convention

Mixed CamelCase, snake_case, and "Customer Name" columns are an easy mistake to avoid. Rename them when you first load the data:

python
import re

def snake(name: str) -> str:
    name = re.sub(r"(?<=[a-z0-9])(?=[A-Z])", "_", name)  # camelCase becomes camel_Case (ACRONYMS stay whole)
    name = re.sub(r"[^\w]+", "_", name).strip("_")        # punctuation becomes _
    return re.sub(r"_+", "_", name).lower()               # collapse runs of _

df.columns = [snake(c) for c in df.columns]
# "Customer Name"  becomes "customer_name"
# "OrderID"        becomes "order_id"
# "amount (USD)"   becomes "amount_usd"
+ setup added so this can run · re-runs 9 earlier blocks on this page first
# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')
np = _AutoMock('np')
pd = _AutoMock('pd')

# Earlier blocks on this page that this one builds on, re-run quietly first.
try:
    import pandas as pd
    import numpy as np
    from sklearn.experimental import enable_iterative_imputer
    from sklearn.impute import IterativeImputer
    from scipy import stats
except Exception:
    pass
_pm_earlier = [
    'import pandas as pd\nimport numpy as np\n\ndf = pd.DataFrame({\n    "id":    [1, 2, 2, 3, 4, 5],\n    "email": ["A@x.com", " b@x.com ", " b@x.com ", "c@x", None, "e@x.com"],\n    "age":   ["25", "30", "30", "-1", "200", "40"],\n    "joined":["2026-01-05", "01/06/2026", "01/06/2026", "2026-02-30", None, "2026-04-01"],\n})\ndf.info()',
    'df.isna().sum().sort_values(ascending=False)\n# email     1\n# joined    1\n# age       0  ← suspicious: see Section 2.1\n# id        0',
    'df["age"] = df["age"].replace({"-1": np.nan, "N/A": np.nan})',
    'df["age"] = pd.to_numeric(df["age"], errors="coerce")\ndf["age"] = df["age"].fillna(df["age"].median())\n\n# Time-series forward fill\nts = pd.Series([1.0, np.nan, np.nan, 4.0])\nts.ffill()                              # 1, 1, 1, 4\n\n# Model-based: uses other columns to estimate each missing value\nfrom sklearn.experimental import enable_iterative_imputer  # noqa: F401\nfrom sklearn.impute import IterativeImputer\nimp = IterativeImputer(random_state=0)\n# imp.fit_transform(df[numeric_cols])',
    'df.duplicated().sum()                                  # rows that exactly repeat an earlier row\ndf.duplicated(subset=["id"], keep="first").sum()       # repeated ids, not counting the first of each\ndf.duplicated(subset=["id"], keep=False).sum()         # ALL rows whose id appears more than once\n\n# Drop, keeping the first occurrence\ndf = df.drop_duplicates(subset=["id"], keep="first")',
    'df["age"]    = pd.to_numeric(df["age"], errors="coerce")  # values that fail to parse become NaN, no error\ndf["count"]  = df["count"].astype("int64")                # only after you\'re sure no NaN\ndf["active"] = df["active"].map({"Y": True, "N": False})  # explicit mapping, no "truthy" guessing\ndf["plan"]   = df["plan"].astype("category")              # few distinct strings, so use category',
    '# IQR rule: works for any distribution and resists outliers\nq1, q3 = df["age"].quantile([0.25, 0.75])\niqr = q3 - q1\nlo, hi = q1 - 1.5 * iqr, q3 + 1.5 * iqr\noutliers = df[(df["age"] < lo) | (df["age"] > hi)]\n\n# Z-score: assumes the data is roughly normal\nfrom scipy import stats\nz = np.abs(stats.zscore(df["age"].dropna()))\n# rows with |z| > 3 are conventionally "outliers"',
    'df["country"].value_counts(dropna=False)\n# USA      3120\n# us        412\n# U.S.A.    198\n#  usa       45      ← leading space\n# United States  12\n# NaN        3',
    'df["country"] = (\n    df["country"]\n      .str.strip()\n      .str.lower()\n      .str.replace(r"\\.", "", regex=True)\n)\n# now: usa, us, united states. Merge them with a map\ndf["country"] = df["country"].replace({\n    "us": "usa",\n    "united states": "usa",\n    "u s a": "usa",\n})',
]
def _pm_run_earlier(_srcs, _g):
    import sys as _s, io as _io, warnings as _w
    for _src in _srcs:
        _o, _e = _s.stdout, _s.stderr
        _s.stdout = _s.stderr = _io.StringIO()
        try:
            with _w.catch_warnings():
                _w.simplefilter('ignore')
                exec(compile(_src, '<earlier block>', 'exec'), _g)
        except (Exception, SystemExit):
            pass
        finally:
            _s.stdout, _s.stderr = _o, _e
    if 'matplotlib.pyplot' in _s.modules:
        _s.modules['matplotlib.pyplot'].close('all')   # their plots belong to their own blocks
_pm_run_earlier(_pm_earlier, globals())
del _pm_earlier, _pm_run_earlier

Use snake_case everywhere. Then df.col_name attribute access works, your SQL exports don't need quoting, and later code never has to guess the name.

9. A Unified Cleaning Pipeline

Put all the cleaning in one function, not in 30 notebook cells that you have to run again in the right order.

python
def clean(df: pd.DataFrame) -> pd.DataFrame:
    """Single source of truth for cleaning. Idempotent."""
    df = df.copy()                                       # never mutate the input
    before = len(df)

    # 1. column names
    df.columns = [snake(c) for c in df.columns]

    # 2. placeholder values (sentinels) become NaN
    df["age"] = df["age"].replace({-1: np.nan, "N/A": np.nan})

    # 3. types
    df["age"]    = pd.to_numeric(df["age"], errors="coerce")
    df["joined"] = pd.to_datetime(df["joined"], errors="coerce")

    # 4. remove duplicates
    df = df.drop_duplicates(subset=["id"], keep="last")

    # 5. impute
    df["age"] = df["age"].fillna(df["age"].median())

    # 6. text cleanup
    df["email"] = df["email"].str.strip().str.lower()

    print(f"clean(): {before} rows in, {len(df)} rows out")        # audit trail
    return df
+ setup added so this can run · re-runs 12 earlier blocks on this page first
# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

pd = _AutoMock('pd')
def snake(*_a, **_kw):
    print('-> snake() called')
    return _AutoMock('snake()')
np = _AutoMock('np')
df = _AutoMock('df')
s = _AutoMock('s')

# Earlier blocks on this page that this one builds on, re-run quietly first.
try:
    import pandas as pd
    import numpy as np
    from sklearn.experimental import enable_iterative_imputer
    from sklearn.impute import IterativeImputer
    from scipy import stats
    import re
except Exception:
    pass
_pm_earlier = [
    'import pandas as pd\nimport numpy as np\n\ndf = pd.DataFrame({\n    "id":    [1, 2, 2, 3, 4, 5],\n    "email": ["A@x.com", " b@x.com ", " b@x.com ", "c@x", None, "e@x.com"],\n    "age":   ["25", "30", "30", "-1", "200", "40"],\n    "joined":["2026-01-05", "01/06/2026", "01/06/2026", "2026-02-30", None, "2026-04-01"],\n})\ndf.info()',
    'df.isna().sum().sort_values(ascending=False)\n# email     1\n# joined    1\n# age       0  ← suspicious: see Section 2.1\n# id        0',
    'df["age"] = df["age"].replace({"-1": np.nan, "N/A": np.nan})',
    'df["age"] = pd.to_numeric(df["age"], errors="coerce")\ndf["age"] = df["age"].fillna(df["age"].median())\n\n# Time-series forward fill\nts = pd.Series([1.0, np.nan, np.nan, 4.0])\nts.ffill()                              # 1, 1, 1, 4\n\n# Model-based: uses other columns to estimate each missing value\nfrom sklearn.experimental import enable_iterative_imputer  # noqa: F401\nfrom sklearn.impute import IterativeImputer\nimp = IterativeImputer(random_state=0)\n# imp.fit_transform(df[numeric_cols])',
    'df.duplicated().sum()                                  # rows that exactly repeat an earlier row\ndf.duplicated(subset=["id"], keep="first").sum()       # repeated ids, not counting the first of each\ndf.duplicated(subset=["id"], keep=False).sum()         # ALL rows whose id appears more than once\n\n# Drop, keeping the first occurrence\ndf = df.drop_duplicates(subset=["id"], keep="first")',
    'df["age"]    = pd.to_numeric(df["age"], errors="coerce")  # values that fail to parse become NaN, no error\ndf["count"]  = df["count"].astype("int64")                # only after you\'re sure no NaN\ndf["active"] = df["active"].map({"Y": True, "N": False})  # explicit mapping, no "truthy" guessing\ndf["plan"]   = df["plan"].astype("category")              # few distinct strings, so use category',
    '# IQR rule: works for any distribution and resists outliers\nq1, q3 = df["age"].quantile([0.25, 0.75])\niqr = q3 - q1\nlo, hi = q1 - 1.5 * iqr, q3 + 1.5 * iqr\noutliers = df[(df["age"] < lo) | (df["age"] > hi)]\n\n# Z-score: assumes the data is roughly normal\nfrom scipy import stats\nz = np.abs(stats.zscore(df["age"].dropna()))\n# rows with |z| > 3 are conventionally "outliers"',
    'df["country"].value_counts(dropna=False)\n# USA      3120\n# us        412\n# U.S.A.    198\n#  usa       45      ← leading space\n# United States  12\n# NaN        3',
    'df["country"] = (\n    df["country"]\n      .str.strip()\n      .str.lower()\n      .str.replace(r"\\.", "", regex=True)\n)\n# now: usa, us, united states. Merge them with a map\ndf["country"] = df["country"].replace({\n    "us": "usa",\n    "united states": "usa",\n    "u s a": "usa",\n})',
    's = pd.Series(["2026-01-05", "01/06/2026", "Feb 30, 2026", None])\n\n# Coerce: values that fail to parse become NaT (Not-a-Time)\npd.to_datetime(s, errors="coerce")\n# 0   2026-01-05\n# 1          NaT       ← pandas 2 takes the format from the first value. Add format="mixed" (and dayfirst=) for mixed input\n# 2          NaT\n# 3          NaT',
    'pd.to_datetime(s, format="%Y-%m-%d", errors="coerce")',
    'import re\n\ndef snake(name: str) -> str:\n    name = re.sub(r"(?<=[a-z0-9])(?=[A-Z])", "_", name)  # camelCase becomes camel_Case (ACRONYMS stay whole)\n    name = re.sub(r"[^\\w]+", "_", name).strip("_")        # punctuation becomes _\n    return re.sub(r"_+", "_", name).lower()               # collapse runs of _\n\ndf.columns = [snake(c) for c in df.columns]\n# "Customer Name"  becomes "customer_name"\n# "OrderID"        becomes "order_id"\n# "amount (USD)"   becomes "amount_usd"',
]
def _pm_run_earlier(_srcs, _g):
    import sys as _s, io as _io, warnings as _w
    for _src in _srcs:
        _o, _e = _s.stdout, _s.stderr
        _s.stdout = _s.stderr = _io.StringIO()
        try:
            with _w.catch_warnings():
                _w.simplefilter('ignore')
                exec(compile(_src, '<earlier block>', 'exec'), _g)
        except (Exception, SystemExit):
            pass
        finally:
            _s.stdout, _s.stderr = _o, _e
    if 'matplotlib.pyplot' in _s.modules:
        _s.modules['matplotlib.pyplot'].close('all')   # their plots belong to their own blocks
_pm_run_earlier(_pm_earlier, globals())
del _pm_earlier, _pm_run_earlier

Two design properties to aim for:

  • Idempotent: clean(clean(df)) equals clean(df). Run it twice, and you get the same result. This works because .replace, .fillna and .str.lower are idempotent themselves: stripping a string that is already stripped changes nothing.
  • Auditable: print or log row counts (and dropped-row reasons) every time. If cleaning drops rows without telling you, you won't notice when it goes wrong.

For larger projects, replace print with logging and emit per-step metrics. If a cleaning pipeline doesn't log what it does, a small question like "why did the data look strange on Monday?" can take three days to answer.

10. Common Mistakes

1. Silent row drops
df.dropna() with no subset= removes every row with any NaN, without any message. You only find out when your "100k row dataset" becomes 12k rows. Always pass subset= and always log the count.

2. Filling missing with 0 when 0 is meaningful
For "clicks", 0 is a valid value. If you fill missing values with 0, "didn't click" and "we didn't record this row" look the same. Use NaN and let the model treat missing as missing, or add a clicks_missing flag column.

3. Outlier clipping without domain check
A 200-year-old human is a data error. A 200-year-old oak tree is not. Clip only when you know the value is impossible.

4. Inferring date formats
pd.to_datetime(s) on mixed dd/mm and mm/dd data can read day and month the wrong way round, without any warning. Specify format= or dayfirst=. Never rely on pandas guessing the format in production code.

5. Treating clean once as clean forever
Upstream data changes. Re-run your clean() on every refresh, and assert invariants afterwards (assert df["age"].between(0, 120).all()). The first time an assertion fails, you'll be glad it's there.

6. Mutating the input DataFrame
def clean(df): df["x"] = ...; return df modifies the caller's DataFrame too. Call df = df.copy() first.

🎯 Your Turn: Clean a Users Dataset

You're given a dirty users export. Build clean_users(df) that returns a cleaned, deduplicated, type-correct DataFrame and prints a row-count audit.

Required cleanup:


  • Emails: strip whitespace, lowercase, drop rows where the email is None or empty.

  • Names: strip whitespace, title-case.

  • Age: -1 is a sentinel for missing → NaN. Fill remaining NaN with the median.

  • Joined date: mixed YYYY-MM-DD and DD/MM/YYYY formats. Pass dayfirst=True and coerce bad parses.

  • Dedupe: by id, keeping the last occurrence (assume rows arrive in update order).

  • Audit: print rows before/after.

python
raw = pd.DataFrame({
    "id":     [1, 2, 2, 3, 4, 5, 5],
    "Email":  [" A@x.com ", "b@x.com", "b@x.com", None, "d@x.com", "E@x.com", "e@x.com"],
    "Name":   ["  surya ", "ravi", "ravi", "anya", "  ", "kiran", "kiran"],
    "age":    [25, 30, 30, -1, 200, 40, 41],
    "joined": ["2026-01-05", "06/01/2026", "06/01/2026", "2026-02-15",
               "31/03/2026", None, "2026-04-02"],
})

# clean_users(raw) returns a cleaned DataFrame with 4 rows and sensible dtypes
+ setup added so this can run · re-runs 13 earlier blocks on this page first
# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

pd = _AutoMock('pd')
df = _AutoMock('df')
np = _AutoMock('np')
s = _AutoMock('s')
def snake(*_a, **_kw):
    print('-> snake() called')
    return _AutoMock('snake()')

# Earlier blocks on this page that this one builds on, re-run quietly first.
try:
    import pandas as pd
    import numpy as np
    from sklearn.experimental import enable_iterative_imputer
    from sklearn.impute import IterativeImputer
    from scipy import stats
    import re
except Exception:
    pass
_pm_earlier = [
    'import pandas as pd\nimport numpy as np\n\ndf = pd.DataFrame({\n    "id":    [1, 2, 2, 3, 4, 5],\n    "email": ["A@x.com", " b@x.com ", " b@x.com ", "c@x", None, "e@x.com"],\n    "age":   ["25", "30", "30", "-1", "200", "40"],\n    "joined":["2026-01-05", "01/06/2026", "01/06/2026", "2026-02-30", None, "2026-04-01"],\n})\ndf.info()',
    'df.isna().sum().sort_values(ascending=False)\n# email     1\n# joined    1\n# age       0  ← suspicious: see Section 2.1\n# id        0',
    'df["age"] = df["age"].replace({"-1": np.nan, "N/A": np.nan})',
    'df["age"] = pd.to_numeric(df["age"], errors="coerce")\ndf["age"] = df["age"].fillna(df["age"].median())\n\n# Time-series forward fill\nts = pd.Series([1.0, np.nan, np.nan, 4.0])\nts.ffill()                              # 1, 1, 1, 4\n\n# Model-based: uses other columns to estimate each missing value\nfrom sklearn.experimental import enable_iterative_imputer  # noqa: F401\nfrom sklearn.impute import IterativeImputer\nimp = IterativeImputer(random_state=0)\n# imp.fit_transform(df[numeric_cols])',
    'df.duplicated().sum()                                  # rows that exactly repeat an earlier row\ndf.duplicated(subset=["id"], keep="first").sum()       # repeated ids, not counting the first of each\ndf.duplicated(subset=["id"], keep=False).sum()         # ALL rows whose id appears more than once\n\n# Drop, keeping the first occurrence\ndf = df.drop_duplicates(subset=["id"], keep="first")',
    'df["age"]    = pd.to_numeric(df["age"], errors="coerce")  # values that fail to parse become NaN, no error\ndf["count"]  = df["count"].astype("int64")                # only after you\'re sure no NaN\ndf["active"] = df["active"].map({"Y": True, "N": False})  # explicit mapping, no "truthy" guessing\ndf["plan"]   = df["plan"].astype("category")              # few distinct strings, so use category',
    '# IQR rule: works for any distribution and resists outliers\nq1, q3 = df["age"].quantile([0.25, 0.75])\niqr = q3 - q1\nlo, hi = q1 - 1.5 * iqr, q3 + 1.5 * iqr\noutliers = df[(df["age"] < lo) | (df["age"] > hi)]\n\n# Z-score: assumes the data is roughly normal\nfrom scipy import stats\nz = np.abs(stats.zscore(df["age"].dropna()))\n# rows with |z| > 3 are conventionally "outliers"',
    'df["country"].value_counts(dropna=False)\n# USA      3120\n# us        412\n# U.S.A.    198\n#  usa       45      ← leading space\n# United States  12\n# NaN        3',
    'df["country"] = (\n    df["country"]\n      .str.strip()\n      .str.lower()\n      .str.replace(r"\\.", "", regex=True)\n)\n# now: usa, us, united states. Merge them with a map\ndf["country"] = df["country"].replace({\n    "us": "usa",\n    "united states": "usa",\n    "u s a": "usa",\n})',
    's = pd.Series(["2026-01-05", "01/06/2026", "Feb 30, 2026", None])\n\n# Coerce: values that fail to parse become NaT (Not-a-Time)\npd.to_datetime(s, errors="coerce")\n# 0   2026-01-05\n# 1          NaT       ← pandas 2 takes the format from the first value. Add format="mixed" (and dayfirst=) for mixed input\n# 2          NaT\n# 3          NaT',
    'pd.to_datetime(s, format="%Y-%m-%d", errors="coerce")',
    'import re\n\ndef snake(name: str) -> str:\n    name = re.sub(r"(?<=[a-z0-9])(?=[A-Z])", "_", name)  # camelCase becomes camel_Case (ACRONYMS stay whole)\n    name = re.sub(r"[^\\w]+", "_", name).strip("_")        # punctuation becomes _\n    return re.sub(r"_+", "_", name).lower()               # collapse runs of _\n\ndf.columns = [snake(c) for c in df.columns]\n# "Customer Name"  becomes "customer_name"\n# "OrderID"        becomes "order_id"\n# "amount (USD)"   becomes "amount_usd"',
    'def clean(df: pd.DataFrame) -> pd.DataFrame:\n    """Single source of truth for cleaning. Idempotent."""\n    df = df.copy()                                       # never mutate the input\n    before = len(df)\n\n    # 1. column names\n    df.columns = [snake(c) for c in df.columns]\n\n    # 2. placeholder values (sentinels) become NaN\n    df["age"] = df["age"].replace({-1: np.nan, "N/A": np.nan})\n\n    # 3. types\n    df["age"]    = pd.to_numeric(df["age"], errors="coerce")\n    df["joined"] = pd.to_datetime(df["joined"], errors="coerce")\n\n    # 4. remove duplicates\n    df = df.drop_duplicates(subset=["id"], keep="last")\n\n    # 5. impute\n    df["age"] = df["age"].fillna(df["age"].median())\n\n    # 6. text cleanup\n    df["email"] = df["email"].str.strip().str.lower()\n\n    print(f"clean(): {before} rows in, {len(df)} rows out")        # audit trail\n    return df',
]
def _pm_run_earlier(_srcs, _g):
    import sys as _s, io as _io, warnings as _w
    for _src in _srcs:
        _o, _e = _s.stdout, _s.stderr
        _s.stdout = _s.stderr = _io.StringIO()
        try:
            with _w.catch_warnings():
                _w.simplefilter('ignore')
                exec(compile(_src, '<earlier block>', 'exec'), _g)
        except (Exception, SystemExit):
            pass
        finally:
            _s.stdout, _s.stderr = _o, _e
    if 'matplotlib.pyplot' in _s.modules:
        _s.modules['matplotlib.pyplot'].close('all')   # their plots belong to their own blocks
_pm_run_earlier(_pm_earlier, globals())
del _pm_earlier, _pm_run_earlier

Skeleton:

python
import pandas as pd
import numpy as np

def clean_users(df: pd.DataFrame) -> pd.DataFrame:
    df = df.copy()
    before = len(df)

    # TODO 1: snake_case the column names
    # TODO 2: for email, strip, lowercase, turn "" into NaN, drop rows with no email
    # TODO 3: for name, strip spaces and use title case
    # TODO 4: for age, replace -1 with NaN, then fill NaN with the median
    # TODO 5: for joined, use to_datetime with dayfirst=True, errors="coerce"
    # TODO 6: drop repeated ids with keep="last"

    print(f"clean_users: {before} rows in, {len(df)} rows out")
    return df
Hint 1: Empty strings aren't NaN After .str.strip() a whitespace-only email like " " becomes "", and isna() does not count "" as missing. Convert with df["email"].replace("", np.nan) before dropping.
Hint 2: Order matters for dedupe vs impute Dedupe before imputing the median age. Otherwise the duplicate rows skew the median you fill with.
Show full solution
python
import pandas as pd
import numpy as np
import re

def snake(name: str) -> str:
    name = re.sub(r"(?<=[a-z0-9])(?=[A-Z])", "_", name)
    name = re.sub(r"[^\w]+", "_", name).strip("_")
    return re.sub(r"_+", "_", name).lower()

def clean_users(df: pd.DataFrame) -> pd.DataFrame:
    """Clean a users dataset. Idempotent."""
    df = df.copy()
    before = len(df)

    # 1. column names to snake_case
    df.columns = [snake(c) for c in df.columns]

    # 2. email: strip, lowercase, drop missing
    df["email"] = (
        df["email"].astype("string").str.strip().str.lower().replace("", np.nan)
    )
    df = df.dropna(subset=["email"])

    # 3. name: strip and title-case
    df["name"] = df["name"].astype("string").str.strip().str.title().replace("", np.nan)

    # 4. remove duplicates before filling missing values
    df = df.drop_duplicates(subset=["id"], keep="last")

    # 5. age: -1 becomes NaN, then fill with the median
    df["age"] = df["age"].replace(-1, np.nan)
    df["age"] = df["age"].fillna(df["age"].median()).astype("int64")

    # 6. joined date: mixed formats, day first (UK/EU style)
    df["joined"] = pd.to_datetime(df["joined"], format="mixed", dayfirst=True, errors="coerce")

    print(f"clean_users: {before} rows in, {len(df)} rows out")
    return df


raw = pd.DataFrame({
    "id":     [1, 2, 2, 3, 4, 5, 5],
    "Email":  [" A@x.com ", "b@x.com", "b@x.com", None, "d@x.com", "E@x.com", "e@x.com"],
    "Name":   ["  surya ", "ravi", "ravi", "anya", "  ", "kiran", "kiran"],
    "age":    [25, 30, 30, -1, 200, 40, 41],
    "joined": ["2026-01-05", "06/01/2026", "06/01/2026", "2026-02-15",
               "31/03/2026", None, "2026-04-02"],
})

print(clean_users(raw))
# clean_users: 7 rows in, 4 rows out
#    id    email   name  age     joined
# 0   1  a@x.com  Surya   25 2026-01-05
# 2   2  b@x.com   Ravi   30 2026-01-06
# 4   4  d@x.com   <NA>  200 2026-03-31
# 6   5  e@x.com  Kiran   41 2026-04-02

What the solution gets right:

  • Idempotent: running it again on the cleaned output changes nothing. .str.lower() on an already-lowercased string is unchanged, and replace(-1, NaN) on data with no -1 does nothing.
  • Order: names are normalised, then duplicates are removed by id, then age median computed from the post-dedup distribution (so duplicates don't bias the median).
  • Sentinel handled: a -1 age becomes missing and is filled with the median (in this sample the -1 row is dropped earlier, for having no email). The 200 is kept as a real age. That is a deliberate choice, and in a real project you would raise it in review.
  • Auditable: the before → after print is the record you will want when someone asks why the row count looks strange this week.

In a real project, you'd parameterise the dayfirst flag per source, log to a real logger, and assert post-conditions (assert df["age"].between(0, 120).all()). The pattern is the same, with more checks.

What You Learned

  • The six-step checklist: missing values, duplicates, types, outliers, categories, dates, run in order.
  • Look before fixing: isna().sum(), value_counts(dropna=False), describe() first.
  • Sentinels (-1, 9999, "N/A") are missing values in disguise. Convert them with replace() before treating columns as numeric.
  • Imputation is a decision, not a default. Mean, median, mode, ffill and model-based filling each add an assumption.
  • Dedupe by business key with drop_duplicates(subset=, keep=). keep=False lets you inspect duplicates without dropping them.
  • pd.to_numeric / to_datetime with errors="coerce" turns bad parses into NaN/NaT instead of exceptions.
  • Outliers: IQR or z-score for detection, then clip / drop / flag / keep as a domain decision.
  • String normalisation with .str accessors, and fuzzy matching with rapidfuzz for messy categories with many rare variants.
  • Snake_case all the columns when you load the data, so later code is simpler.
  • One idempotent clean(df) function with a row-count audit is better than thirty separate notebook cells.

Next: Feature Engineering: turn a clean DataFrame into columns that a model can learn from.

Practice this

on practicepython.in

Short exercises that run in your browser and tell you what your code did, as well as whether a test passed.

Found this useful? Share it with a friend who’s learning Python.