⚙️ Setup¶
import numpy as np
import pandas as pd
import re
print("Ready.")
Ready.
df = pd.DataFrame({
"customer_id":[1,1,2,3,3],
"name":["Ana","Ana","Ben","Cy","Cy"],
"spend":[50,50,30,20,75],
})
before = len(df)
no_exact = df.drop_duplicates()
one_per = no_exact.drop_duplicates(subset="customer_id", keep="last")
print(f"start: {before} rows")
print(f"after exact dedup: {len(no_exact)} rows")
print(f"one row per customer (keep last): {len(one_per)} rows")
print(f"removed total: {before - len(one_per)} rows")
start: 5 rows after exact dedup: 4 rows one row per customer (keep last): 3 rows removed total: 2 rows
Answer: The identical row drops first (5 to 4 rows), then deduping on customer_id with keep="last" collapses customer 3's two records to the later one (spend 75), leaving 3 rows, 2 removed in all. Use subset= for a key and keep= to choose which record survives; always log the count removed.
country = pd.Series(["USA","usa","U.S.A."," United States ","UK","uk","U.K.","United States"])
clean = country.str.strip().str.lower().str.replace(".","",regex=False)
mapping = {"usa":"United States","us":"United States","united states":"United States",
"uk":"United Kingdom","uk ":"United Kingdom","united kingdom":"United Kingdom"}
clean = clean.replace(mapping)
print(clean.value_counts())
print(f"\n{country.nunique()} raw labels -> {clean.nunique()} canonical values")
United States 5 United Kingdom 3 Name: count, dtype: int64 8 raw labels -> 2 canonical values
Answer: Strip whitespace, lowercase, drop the periods, then map the variants to canonical names. The seven raw labels collapse to United States and United Kingdom. The recipe is always discover (value_counts) to define canonical to map (replace) to verify (value_counts again).
price = pd.Series(["1,250","$90","2000","oops","$1,500"])
num = pd.to_numeric(price.str.replace(r"[\$,]","",regex=True), errors="coerce")
print(num)
print(f"\nparsed: {num.notna().sum()}, coerced to NaN: {num.isna().sum()}")
0 1250.0 1 90.0 2 2000.0 3 NaN 4 1500.0 dtype: float64 parsed: 4, coerced to NaN: 1
Answer: Strip the $ and , with a regex, then pd.to_numeric(..., errors="coerce"). Four values parse; "oops" becomes NaN (1 coerced). The key habit: errors="coerce" silently creates missing values, so always count .isna().sum() afterward so the loss is not invisible. Those NaNs are now a missing-data problem (Chapter 20).
def lev_ratio(a, b):
m, n = len(a), len(b); d = list(range(n + 1))
for i in range(1, m + 1):
prev, d[0] = d[0], i
for j in range(1, n + 1):
cur = d[j]; d[j] = min(d[j]+1, d[j-1]+1, prev + (a[i-1]!=b[j-1])); prev = cur
return 1 - d[n] / max(m, n, 1)
names = ["Acme Inc","Acme, Inc.","Acme Incorporated","Beta LLC"]
norm = [re.sub(r"[^a-z0-9 ]","",s.lower()).strip() for s in names] # standardize first
for i in range(len(names)):
for j in range(i+1, len(names)):
r = lev_ratio(norm[i], norm[j])
flag = " <- likely same" if r >= 0.85 else ""
print(f"{names[i]!r:20} ~ {names[j]!r:20} -> {r:.2f}{flag}")
'Acme Inc' ~ 'Acme, Inc.' -> 1.00 <- likely same 'Acme Inc' ~ 'Acme Incorporated' -> 0.47 'Acme Inc' ~ 'Beta LLC' -> 0.25 'Acme, Inc.' ~ 'Acme Incorporated' -> 0.47 'Acme, Inc.' ~ 'Beta LLC' -> 0.25 'Acme Incorporated' ~ 'Beta LLC' -> 0.12
Answer: After normalizing (lowercase, strip punctuation), "Acme Inc" and "Acme, Inc." score very high (~0.9+) and are clearly the same company; "Acme Incorporated" is closer to them than to Beta but may fall under a strict 0.85 threshold, which is exactly why you review fuzzy matches rather than auto-merge. "Beta LLC" matches nothing. A looser threshold catches more true dupes (recall) but risks false merges (precision).
df = pd.DataFrame({
"id":[1,2,2,4],
"age":[30,-5,45,130],
"status":["active","inactive","pending","active"],
})
VALID = {"active","inactive"}
def validate(df):
issues = []
if not df["id"].is_unique: issues.append(f"duplicate id: {df['id'][df['id'].duplicated()].tolist()}")
if not df["age"].between(0,120).all(): issues.append(f"age out of range: {df.loc[~df['age'].between(0,120),'age'].tolist()}")
bad = df.loc[~df["status"].isin(VALID),"status"].tolist()
if bad: issues.append(f"invalid status: {bad}")
return issues
v = validate(df)
print("PASS ✅" if not v else f"FAIL ❌: {len(v)} rule(s):")
for x in v: print(" •", x)
FAIL ❌: 3 rule(s): • duplicate id: [2] • age out of range: [-5, 130] • invalid status: ['pending']
import matplotlib.pyplot as plt
ages = df["age"].tolist()
colors = ["#059669" if 0 <= a <= 120 else "#e11d48" for a in ages]
fig, ax = plt.subplots(figsize=(6, 3))
ax.bar(range(len(ages)), ages, color=colors)
ax.axhspan(0, 120, color="#059669", alpha=0.08)
ax.set(title="Age validation: red bars fall outside the valid 0-120 range", xlabel="row", ylabel="age")
plt.tight_layout(); plt.show()
Answer: The frame fails all three rules: a duplicate id (2), ages -5 and 130 outside 0-120, and an invalid status "pending". A validate() that returns a list of violations turns cleaning into a repeatable, testable contract you run on ingest and again after cleaning. In a real pipeline you would reach for a schema tool like pandera or Great Expectations to declare these rules.