Fixing Inconsistent Data & Detecting Outliers
Press Next → or use ← → arrow keys
The Hidden Cost of Inconsistent Data
A null announces itself — isnull() finds it. An inconsistent value looks perfectly valid, passes every check, and quietly splinters one real category into dozens.
Six Faces of Inconsistency
Run df['col'].value_counts() on every categorical column. If the unique count doesn't match your domain knowledge, an inconsistency is hiding in plain sight.
Fixing String Inconsistencies
df['city'] = (df['city']
.str.lower() # Mumbai → mumbai
.str.strip() # " mumbai " → mumbai
.str.replace(r'\s+', ' ', regex=True)) # collapse inner spaces
city_map = {'mum':'mumbai', 'bombay':'mumbai', 'blr':'bengaluru', 'madras':'chennai'}
df['city'] = df['city'].replace(city_map)
df['city'].nunique() # 847 → 28 ✅Fuzzy Matching & Why Order Matters
from rapidfuzz import process, fuzz
canonical = ['mumbai', 'delhi', 'bengaluru', 'hyderabad', 'chennai']
def fuzzy_fix(val, choices, threshold=80):
hit = process.extractOne(val, choices, scorer=fuzz.ratio)
return hit[0] if hit and hit[1] >= threshold else val
df['city'] = df['city'].apply(lambda x: fuzzy_fix(x, canonical))Each step exposes matches for the next. Fuzzy-match before lowercasing and "Mumbai" and "mumbai" look different; map before stripping and " mum " never hits the dictionary.
Fixing Date & Time Inconsistencies
A retail team merged POS (DD/MM/YYYY), web (YYYY-MM-DD) and ERP (DD-Mon-YY). Stored as strings, they sorted alphabetically — scrambling six months of history. One to_datetime() fixed it.
| raw | source |
|---|---|
| 12/03/2024 | POS |
| 2024-03-15 | Web |
| 20-Mar-24 | ERP |
| March 22, 2024 | Manual |
| 20240328 | API |
| clean | dtype |
|---|---|
| 2024-03-12 | datetime64 |
| 2024-03-15 | datetime64 |
| 2024-03-20 | datetime64 |
| 2024-03-22 | datetime64 |
| 2024-03-28 | datetime64 |
df['order_date'] = pd.to_datetime(
df['order_date'],
dayfirst=True, # DD/MM first
errors='coerce') # bad → NaT
failed = df['order_date'].isnull().sum()
# mine features from clean dates
df['month'] = df['order_date'].dt.month
df['dow'] = df['order_date'].dt.day_name()
df['quarter'] = df['order_date'].dt.quarter
df['wknd'] = df['order_date'].dt.dayofweek >= 5Unparseable dates become NaT instead of crashing — audit those rows, don't lose the run.
Units, Encodings & Type Fixes
# boolean / yes-no chaos → real booleans
yes_no = {'yes':True,'y':True,'1':True,'true':True,'no':False,'n':False,'0':False}
df['is_returned'] = df['is_returned'].astype('str').str.lower().str.strip().map(yes_no)
# fake NA strings pandas misses
df = df.replace(['na','n/a','none','null','-','','unknown'], np.nan)
# unify currency (USD → INR) and weight (kg → g)
df.loc[df['currency']=='USD', 'amount'] *= 83.5; df['currency'] = 'INR'
df.loc[df['unit'].str.lower()=='kg', 'weight'] *= 1000; df['unit'] = 'g'Stop them before they enter the frame: pd.read_csv('f.csv', na_values=['NA','N/A','none','-','unknown','']). A fake NA that survives as a string corrupts every null count and fill downstream.
Outliers — Investigate Before You Delete
Three Ways to Spot an Outlier
IQR casts the widest single-column net, Z-score flags only extreme univariate values, and Isolation Forest sees combinations. Pick by distribution and dimensionality, not habit.
IQR Method — Fences & Flags
Q1, Q3 = df['purchase_amount'].quantile([0.25, 0.75])
IQR = Q3 - Q1
lo, hi = Q1 - 1.5*IQR, Q3 + 1.5*IQR
df['is_outlier'] = (df['purchase_amount'] < lo) | (df['purchase_amount'] > hi)
# INSPECT before treating — never blind-delete
df[df['is_outlier']].sort_values('purchase_amount', ascending=False).head(20)Z-Score & the Robust Modified Z
from scipy import stats
# standard z — assumes normality
z = np.abs(stats.zscore(
df['purchase_amount'].dropna()))
df['is_outlier_z'] = z > 3
# modified z — uses median + MAD
med = df['purchase_amount'].median()
mad = (df['purchase_amount']-med).abs().median()
mz = 0.6745*(df['purchase_amount']-med)/mad
df['is_outlier_modz'] = mz.abs() > 3.5Standard Z uses the mean and σ — both inflated by the very outliers you're trying to find, so extreme points can mask themselves. The modified Z swaps in the median and MAD (median absolute deviation), which outliers can't distort. Prefer it for skewed data.
Reach for Z-score only once a histogram or Q–Q plot confirms the column is roughly normal. Otherwise the IQR method is the safer default.
Isolation Forest — Catching Joint Anomalies
from sklearn.ensemble import \
IsolationForest
X = df[['age','purchase_amount',
'rating','delivery_days']].dropna()
iso = IsolationForest(
contamination=0.05,
n_estimators=200, random_state=42)
df['is_outlier_iso'] = \
iso.fit_predict(X) == -1
df['anomaly_score'] = \
iso.score_samples(X)The red point sits in the normal range on every single axis, so IQR and Z-score wave it through. Only a multivariate method sees that the combination is impossible.
Four Ways to Treat an Outlier
| Strategy | How | When | Risk |
|---|---|---|---|
| Remove | df[~mask] | Confirmed data-entry errors only | high |
| Cap / Winsorise | .clip(lo, hi) | Legit extremes; linear models | medium |
| Transform | np.log1p(x) | Right-skewed long tails | low |
| Keep + scale | RobustScaler | Legit extremes; tree models | low |
Deletion throws away information forever and is only safe for values that cannot be real (age 300, negative price). For everything else, cap, transform, or keep — and always .head(20) the flagged rows first.
Log Transform — Taming the Long Tail
df['purchase_log'] = np.log1p(df['purchase_amount']) # log(1+x), safe at 0
df['purchase_sqrt'] = np.sqrt(df['purchase_amount']) # moderate skew
from scipy.stats import boxcox
df['purchase_bc'], lam = boxcox(df['purchase_amount'] + 1) # best λ automaticallyRobustScaler vs StandardScaler
from sklearn.preprocessing import RobustScaler, StandardScaler
df['robust'] = RobustScaler().fit_transform(df[['purchase_amount']]) # median + IQR ✓
df['std'] = StandardScaler().fit_transform(df[['purchase_amount']]) # mean + σ ✗A Reusable Fix-It Template
def fix_inconsistencies(df, string_maps=None):
df = df.copy()
for col in df.select_dtypes(['object','category']).columns:
df[col] = df[col].str.lower().str.strip()
if string_maps and col in string_maps:
df[col] = df[col].replace(string_maps[col])
return df
def detect_and_treat_outliers(df, cols, method='iqr', treatment='cap'):
df, report = df.copy(), {}
for col in cols:
s = df[col].dropna()
if method == 'iqr':
Q1, Q3 = s.quantile([0.25, 0.75]); IQR = Q3 - Q1
lo, hi = Q1 - 1.5*IQR, Q3 + 1.5*IQR
else:
lo, hi = s.mean() - 3*s.std(), s.mean() + 3*s.std()
mask = (df[col] < lo) | (df[col] > hi)
report[col] = {'n': int(mask.sum()), 'lo': lo, 'hi': hi}
df[col+'_outlier'] = mask
if treatment == 'cap': df[col] = df[col].clip(lo, hi)
elif treatment == 'log': df[col] = np.log1p(df[col].clip(lower=0))
return df, reportCommon Pitfalls & Their Fixes
| Symptom | The trap | The fix |
|---|---|---|
| Hundreds of phantom categories | Mapping before lower/strip | lower → strip → de-space → map → fuzzy |
| Dates sort wrong | Kept as strings | to_datetime(errors='coerce') |
| Nulls miscounted | Fake "N/A" strings | na_values=[…] at load |
| VIP silently deleted | Auto-remove IQR outliers | Inspect .head(20) first |
| Extreme point hides itself | Standard Z on skewed data | Modified Z (median + MAD) or IQR |
| Scaled features crushed flat | StandardScaler with outliers | RobustScaler |
| Joint anomaly missed | Only per-column checks | Isolation Forest on all features |
The Golden Rules — 1 to 4
value_counts() on every categorical column — 847 cities when you expect 28 is a signal, not a detail.errors='coerce' on dates — unparseable values become NaT to audit, not an exception that halts the run.na_values=[…] — string "N/A" corrupts every null count downstream.The Golden Rules — 5 to 8 & Takeaway
df[df['is_outlier']].sort_values(...).head(20) — your most important rows are often your most extreme.Inconsistencies and outliers are different problems with one cure: systematic investigation before treatment. Run value_counts() on every column, inspect every flagged outlier, and your models come out more accurate, more robust and more trustworthy.
🔧 End of tutorial · Press ← to review, or click Restart