←Back to the bookChapter 18 · The Data-Cleaning Mindset

Part IV · Preparing Data  |  Chapter 18

The Data-Cleaning Mindset 🐍 Notebook

Five demos that build the habit of inspecting before you trust: read the dtypes, scan the ranges, surface inconsistent categories, find the hidden missing codes and duplicates, and print a quick data-quality report.

Author: John Fisher  ·  Statistics, Data Science and AI: A Visual Handbook  ·  2026

🎯 What you'll build in this notebook

#	Demo	Idea it builds
1	Look before you trust	info / dtypes / describe surface the obvious problems
2	Categories in disguise	value_counts exposes the "Male/male/M" tangle
3	Hidden missingness	the -99 sentinel that isna() never sees
4	Dupes & impossible values	duplicated() and range checks
5	A data-quality report	one printout that audits every column

This chapter is about finding problems. The fixes (dedup, missing data) are the next two chapters.

---

⚙️ Setup & a deliberately messy dataset

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

NAVY="#0a1230"; INK="#1a2138"; INK_SOFT="#4a5578"
CYAN="#0891b2"; PURPLE="#7c3aed"; AMBER="#d97706"; GREEN="#059669"; PINK="#db2777"; BLUE="#2563eb"; GRID="#e6e9f2"
plt.rcParams.update({"figure.facecolor":"white","axes.facecolor":"white","figure.dpi":110,"font.size":11,
   "axes.edgecolor":GRID,"axes.grid":True,"grid.color":GRID,"axes.axisbelow":True,"axes.spines.top":False,
   "axes.spines.right":False,"axes.titlesize":12.5,"axes.titleweight":"bold","axes.titlecolor":INK,"legend.frameon":False})

df = pd.DataFrame({
    "customer_id": ["001","002","003","003","005","006","007","008"],   # leading zeros -> stored as text; a dup id
    "name":        ["Ana"," Ben","CARLA","CARLA","Dan","Eve","Finn ","Gail"],  # whitespace + casing
    "gender":      ["F","male","Female","Female","M","f","Male","F"],    # inconsistent categories
    "age":         [34, 29, 200, 41, -99, 52, 0, 27],                    # impossible(200), sentinel(-99), ambiguous(0)
    "signup":      ["2021-03-01","03/04/2021","2021/05/12","2021/05/12", # mixed date formats, stored as text
                    "2021-07-30","2021-08-15","2021-09-01","2021-10-10"],
    "spend":       ["1,200","350","2,000","2,000","75","410","90","1,500"],  # number stored as text with commas
})
df

	customer_id	name	gender	age	signup	spend
0	001	Ana	F	34	2021-03-01	1,200
1	002	Ben	male	29	03/04/2021	350
2	003	CARLA	Female	200	2021/05/12	2,000
3	003	CARLA	Female	41	2021/05/12	2,000
4	005	Dan	M	-99	2021-07-30	75
5	006	Eve	f	52	2021-08-15	410
6	007	Finn	Male	0	2021-09-01	90
7	008	Gail	F	27	2021-10-10	1,500

DEMO 1 · LOOK BEFORE YOU TRUST

🔍 info, dtypes, describe

The first move on any dataset is to look at it. info() shows every column is text (object), even age and spend. describe() on the numbers exposes an age that runs from -99 to 200, both impossible for a real person.

df.info()
print("\n-- dtypes --"); print(df.dtypes)
print("\n-- describe(age) --"); print(df["age"].describe())
print("\nRed flags: spend & customer_id are TEXT, and age spans -99 to 200.")

<class 'pandas.DataFrame'>
RangeIndex: 8 entries, 0 to 7
Data columns (total 6 columns):
 #   Column       Non-Null Count  Dtype
---  ------       --------------  -----
 0   customer_id  8 non-null      str  
 1   name         8 non-null      str  
 2   gender       8 non-null      str  
 3   age          8 non-null      int64
 4   signup       8 non-null      str  
 5   spend        8 non-null      str  
dtypes: int64(1), str(5)
memory usage: 516.0 bytes

-- dtypes --
customer_id      str
name             str
gender           str
age            int64
signup           str
spend            str
dtype: object

-- describe(age) --
count      8.00000
mean      35.50000
std       81.79242
min      -99.00000
25%       20.25000
50%       31.50000
75%       43.75000
max      200.00000
Name: age, dtype: float64

Red flags: spend & customer_id are TEXT, and age spans -99 to 200.

DEMO 2 · CATEGORIES IN DISGUISE

🏷️ value_counts on every category

A categorical column can hide a dozen spellings of the same value. value_counts() lists them all. Here one variable, gender, shows up as six different labels that really mean two.

print(df["gender"].value_counts())
print(f"\n{df['gender'].nunique()} distinct labels for what should be 2 categories.")
print("F / f / Female  -> female ;  M / male / Male -> male")

# whitespace hides in names, too
ws = df["name"].str.contains(r"^\s|\s$", regex=True).sum()
print(f"\nnames with leading/trailing whitespace: {ws}  (e.g. ' Ben', 'Finn ')")

gender
F         2
Female    2
male      1
M         1
f         1
Male      1
Name: count, dtype: int64

6 distinct labels for what should be 2 categories.
F / f / Female  -> female ;  M / male / Male -> male

names with leading/trailing whitespace: 2  (e.g. ' Ben', 'Finn ')

DEMO 3 · HIDDEN MISSINGNESS

🕳️ The sentinel isna() never sees

Not all missing data looks missing. Legacy systems often store "unknown" as -99, 9999, or 0. Pandas counts none of these as NaN, so a naive mean is silently wrong. You have to know the codes and look for them.

print("explicit NaNs per column:"); print(df.isna().sum())

sentinel = (df["age"] == -99).sum()
print(f"\n-99 sentinels in age that isna() MISSED: {sentinel}")
print(f"naive mean age (with -99): {df['age'].mean():.1f}   <- nonsense")
print(f"mean age treating -99 as missing: {df.loc[df['age']!=-99, 'age'].mean():.1f}  (still has the 200 problem!)")

# a missingness chart, counting -99 as missing for age
miss = df.isna().sum().astype(float)
miss["age"] += (df["age"] == -99).sum()
fig, ax = plt.subplots(figsize=(8, 3.6))
colors = [AMBER if v>0 else CYAN for v in miss.values]
ax.bar(miss.index, miss.values, color=colors, edgecolor="white")
ax.set_title("Missing values per column (counting hidden -99)"); ax.set_ylabel("count")
ax.set_ylim(0, max(miss.max(),1)*1.4); ax.grid(axis="x", visible=False)
for i,v in enumerate(miss.values): ax.text(i, v+0.05, int(v), ha="center", fontweight="bold", color=INK)
plt.tight_layout(); plt.show()

explicit NaNs per column:
customer_id    0
name           0
gender         0
age            0
signup         0
spend          0
dtype: int64

-99 sentinels in age that isna() MISSED: 1
naive mean age (with -99): 35.5   <- nonsense
mean age treating -99 as missing: 54.7  (still has the 200 problem!)

DEMO 4 · DUPES & IMPOSSIBLE VALUES

👯 Duplicated rows and out-of-range checks

Two more quick audits: duplicated() flags repeated rows and repeated keys, and a simple range rule flags values that cannot be real. Note these are DETECTED here; deciding what to do is the next chapters and a judgment call.

print(f"exact duplicate rows : {df.duplicated().sum()}")
print(f"duplicate customer_id: {df['customer_id'].duplicated().sum()}  (id 003 appears twice)")

impossible = ((df["age"] < 0) | (df["age"] > 120)).sum()
print(f"\nages outside 0..120 : {impossible}  -> {list(df.loc[(df['age']<0)|(df['age']>120), 'age'])}")
print("age=0 is ambiguous: a newborn, or a default for missing? Only domain knowledge decides.")

exact duplicate rows : 0
duplicate customer_id: 1  (id 003 appears twice)

ages outside 0..120 : 2  -> [200, -99]
age=0 is ambiguous: a newborn, or a default for missing? Only domain knowledge decides.

DEMO 5 · A DATA-QUALITY REPORT

📋 One printout that audits every column

Wrap the checks into a small report you can run on any new dataset. It is the cleaning mindset in code: inspect type, completeness, uniqueness, and obvious range problems before trusting a single number.

def quality_report(frame, missing_codes=(-99, 9999)):
    rows = []
    for col in frame.columns:
        s = frame[col]
        hidden = s.isin(missing_codes).sum() if s.dtype != object else 0
        rows.append({
            "column": col,
            "dtype": str(s.dtype),
            "n_unique": s.nunique(),
            "explicit_NaN": int(s.isna().sum()),
            "hidden_missing": int(hidden),
            "duplicated_vals": int(s.duplicated().sum()),
        })
    return pd.DataFrame(rows)

report = quality_report(df)
print(report.to_string(index=False))
print("\nRun this first on every dataset. The mindset: audit, then clean (on a copy, in a script), then validate.")

     column dtype  n_unique  explicit_NaN  hidden_missing  duplicated_vals
customer_id   str         7             0               0                1
       name   str         7             0               0                1
     gender   str         6             0               0                2
        age int64         8             0               1                0
     signup   str         7             0               0                1
      spend   str         7             0               0                1

Run this first on every dataset. The mindset: audit, then clean (on a copy, in a script), then validate.

🔎 Real-World Example: Auditing a Messy Table

The cleaning mindset starts with an audit, profile the data and list what is wrong before fixing anything. Here we run that first pass on a deliberately messy customer table: count the duplicate rows and the missing values, scan the age range for impossible values, and see how many ways one city got spelled. The output is a to-do list, not a fix.

# --- Real-World beat: profile a messy table before touching it ---
BASE = "https://raw.githubusercontent.com/johnfisher-ai/Statistics-Data-Science-AI-Visual-Book/main/data/"
try:    raw = pd.read_excel("../../data/the-data-cleaning-mindset--messy_customers.xlsx", sheet_name="Data")
except FileNotFoundError: raw = pd.read_excel(BASE+"the-data-cleaning-mindset--messy_customers.xlsx", sheet_name="Data")
print(f"shape: {raw.shape[0]} rows x {raw.shape[1]} columns")
print(f"duplicate rows: {raw.duplicated().sum()}")
print(f"impossible ages (age < 0 or age > 120): {((raw.age < 0) | (raw.age > 120)).sum()}   [min {raw.age.min():.0f}, max {raw.age.max():.0f}]")
print(f"distinct spellings of city: {raw.city.nunique()}   (should be 5)")
print("missing values per column:"); print(raw.isna().sum().to_string())
miss = raw.isna().sum(); miss = miss[miss > 0].sort_values()
fig, ax = plt.subplots(figsize=(8,3.4))
ax.barh(miss.index, miss.values, color=PINK, alpha=0.85, edgecolor="white")
for i, v in enumerate(miss.values): ax.text(v, i, f" {v}", va="center", fontsize=10)
ax.set_title("Data-quality audit: missing values per column"); ax.set_xlabel("missing rows")
plt.tight_layout(); plt.show()

shape: 212 rows x 5 columns
duplicate rows: 12
impossible ages (age < 0 or age > 120): 2   [min 0, max 999]
distinct spellings of city: 20   (should be 5)
missing values per column:
customer_id     0
city           11
age            15
signup_date     0
total_spend     9

---

🎓 Recap

• Look before you trust: info(), dtypes, and describe() surface wrong types and impossible values fast.
• value_counts() exposes inconsistent categories; check for hidden whitespace too.
• Hidden missing codes (-99, 9999, 0) slip past isna() and poison naive statistics.
• duplicated() and simple range checks catch repeated rows and impossible values.
• Wrap it all in a quality report: audit first, then clean on a copy, in a script, and validate.

Statistics, Data Science and AI: A Visual Handbook · © 2026 John Fisher

←Back to the bookChapter 18 · The Data-Cleaning Mindset