Feature Engineering · Notebook 2 companion articleData Understanding and Basic Cleaning
Notebook 1 ran into one data problem: eleven blank charges that turned out to be brand-new customers. This notebook makes that lesson the main topic. Using real passenger records with real gaps and extreme values, it measures what a careless fix actually costs, in years of age and in lost spread of a column.
Start hereIntroduction#
People often think of cleaning as the boring part you rush through before modeling. That's mostly because tutorials never show you any numbers. This article does. Filling a missing age with the column average sounds harmless, and sometimes it is. But later in this notebook, that same fix leaves the column's average three and a half years off in one test, and wipes out 18% of the column's spread in another. Pandas gives no warning in either case.
Notebook 1 ended on a warning. Eleven customers had a blank TotalCharges, and they were brand-new customers who hadn't been billed yet. Filling them with the median would have looked tidy and been wrong. That one case holds the main idea of this notebook: before you fix a problem in your data, find out why it's there.
Notebook 1 also set a rule: anything a transformer learns must come from training rows only, and breaking it is called data leakage. That rule comes back here too. This notebook covers eight ideas, in order:
- how to profile a table properly
- the three reasons a value can be missing
- how a missing value can itself be useful
- how to judge filling methods against the real values, not just a NaN count
- why the train/test leakage rule from notebook 1 also applies to imputers
- how to tell a data error from a real extreme value
- duplicate records that hide behind a unique ID
- the cost of one category typed four different ways
Why this notebook uses a second dataset#
The Telco data used across this series turns out to be a poor dataset for learning to clean data. Here's the proof.
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from sklearn.experimental import enable_iterative_imputer # required before IterativeImputer
from sklearn.impute import SimpleImputer, KNNImputer, IterativeImputer
from sklearn.linear_model import LogisticRegression
from sklearn.model_selection import StratifiedKFold, cross_val_score, train_test_split
from sklearn.pipeline import Pipeline, make_pipeline
from sklearn.preprocessing import StandardScaler
plt.style.use('seaborn-v0_8-whitegrid')
pd.set_option('display.max_columns', 30)
pd.set_option('display.width', 170)
RANDOM_STATE = 42
telco = pd.read_csv('datasets/telco_churn.csv')
telco['TotalCharges'] = pd.to_numeric(telco['TotalCharges'], errors='coerce')
print(f'Shape : {telco.shape}')
print(f'Missing values, all cols : {telco.isna().sum().sum()}')
print(f'Exact duplicate rows : {telco.duplicated().sum()}')
print()
# Count outliers using the two standard rules
for col in ['tenure', 'MonthlyCharges', 'TotalCharges']:
s = telco[col].dropna()
q1, q3 = s.quantile([0.25, 0.75])
iqr = q3 - q1
n_iqr = ((s < q1 - 1.5 * iqr) | (s > q3 + 1.5 * iqr)).sum()
n_z = (((s - s.mean()) / s.std()).abs() > 3).sum()
print(f'{col:16s} skew={s.skew():6.2f} IQR outliers={n_iqr:4d} |z|>3={n_z:4d}')
Shape : (7043, 21) Missing values, all cols : 11 Exact duplicate rows : 0 tenure skew= 0.24 IQR outliers= 0 |z|>3= 0 MonthlyCharges skew= -0.22 IQR outliers= 0 |z|>3= 0 TotalCharges skew= 0.96 IQR outliers= 0 |z|>3= 0
Eleven missing values, no duplicates and no outliers by either standard rule, in over seven thousand rows. Telco has clearly been tidied up before it was shared, so there's almost nothing left to practice on.
So most of this notebook uses a second dataset: the Titanic passenger list, 891 rows of raw historical records. Telco comes back in section 7, where it turns out to be hiding something after all.
titanic = pd.read_csv('datasets/titanic.csv')
print(f'Rows : {titanic.shape[0]}')
print(f'Columns : {titanic.shape[1]}')
titanic.head()
Rows : 891 Columns : 12
PassengerId Survived Pclass Name Sex Age SibSp Parch Ticket Fare Cabin Embarked 0 1 0 3 Braund, Mr. Owen Harris male 22.0 1 0 A/5 21171 7.2500 NaN S 1 2 1 1 Cumings, Mrs. John Bradley (Florence Briggs Th... female 38.0 1 0 PC 17599 71.2833 C85 C 2 3 1 3 Heikkinen, Miss. Laina female 26.0 0 0 STON/O2. 3101282 7.9250 NaN S 3 4 1 1 Futrelle, Mrs. Jacques Heath (Lily May Peel) female 35.0 1 0 113803 53.1000 C123 S 4 5 0 3 Allen, Mr. William Henry male 35.0 0 0 373450 8.0500 NaN S
Section 1Profiling before you change anything#
Look before you clean. Every fix erases the evidence of what was wrong
Once you fill a missing value, drop a row or overwrite an entry, the original data is gone. If your fix was wrong, the only way back is to reload the file. Profiling is the one time you see the data exactly as it arrived.
Three pandas commands do most of the profiling work. Each one has a blind spot.
titanic.info()
<class 'pandas.DataFrame'> RangeIndex: 891 entries, 0 to 890 Data columns (total 12 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 PassengerId 891 non-null int64 1 Survived 891 non-null int64 2 Pclass 891 non-null int64 3 Name 891 non-null str 4 Sex 891 non-null str 5 Age 714 non-null float64 6 SibSp 891 non-null int64 7 Parch 891 non-null int64 8 Ticket 891 non-null str 9 Fare 891 non-null float64 10 Cabin 204 non-null str 11 Embarked 889 non-null str dtypes: float64(2), int64(5), str(5) memory usage: 83.7 KB
titanic.describe()
PassengerId Survived Pclass Age SibSp Parch Fare count 891.000000 891.000000 891.000000 714.000000 891.000000 891.000000 891.000000 mean 446.000000 0.383838 2.308642 29.699118 0.523008 0.381594 32.204208 std 257.353842 0.486592 0.836071 14.526497 1.102743 0.806057 49.693429 min 1.000000 0.000000 1.000000 0.420000 0.000000 0.000000 0.000000 25% 223.500000 0.000000 2.000000 20.125000 0.000000 0.000000 7.910400 50% 446.000000 0.000000 3.000000 28.000000 0.000000 0.000000 14.454200 75% 668.500000 1.000000 3.000000 38.000000 1.000000 0.000000 31.000000 max 891.000000 1.000000 3.000000 80.000000 8.000000 6.000000 512.329200
People trust describe() too much. It has two obvious blind spots and one less obvious one. (It also treats number-coded categories like Pclass as amounts, which is why it reports a meaningless "average class" of 2.31.)
It skips every text column. Five of Titanic's twelve columns (Name, Sex, Ticket, Cabin and Embarked) don't appear at all. Any problem in them stays hidden unless you ask for them with describe(include='all').
The count row is its only sign of missing data, and it's easy to miss. Age shows 714 while the other numeric columns show 891. There's no warning or highlight, just a smaller number that most people skim past.
The third problem is harder to spot. In the Fare row, the mean is 32.20, the median is 14.45 and the max is 512.33. When the mean is more than twice the median, a few very large values are pulling it up. describe() shows you this but doesn't point it out. Section 6 looks at this column closely.
A dedicated missing-value report is a better way to find gaps than describe().
def missing_report(df):
# One row per column that has at least one missing value
missing = df.isna().sum()
report = pd.DataFrame({
'missing': missing,
'pct': (missing / len(df) * 100).round(1),
'dtype': df.dtypes.astype(str),
'n_unique': df.nunique(),
})
return report[report['missing'] > 0].sort_values('missing', ascending=False)
print(f'Columns with missing values: {(titanic.isna().sum() > 0).sum()} of {titanic.shape[1]}')
missing_report(titanic)
Columns with missing values: 3 of 12
missing pct dtype n_unique Cabin 687 77.1 str 147 Age 177 19.9 float64 88 Embarked 2 0.2 str 3
All the missing values are in three columns, and each looks different. Cabin is missing for 77.1% of rows, more than three out of four. Age is missing for 19.9%, which is enough to matter but small enough to fix. Embarked is missing for just 0.2%, only two rows, so almost any fix is fine. The percentage tells you how much attention a column needs. It doesn't tell you what to do. That's what section 2 is about.
Percentages show how much is missing, but not where. A chart shows both at a glance.
fig, axes = plt.subplots(1, 2, figsize=(14, 4.5))
# --- Left: how much is missing, per column ---
miss_pct = (titanic.isna().mean() * 100).sort_values()
miss_pct = miss_pct[miss_pct > 0]
axes[0].barh(miss_pct.index, miss_pct.values, color='#c4453c')
axes[0].set(xlabel='% of rows missing', title='How much is missing')
for i, v in enumerate(miss_pct.values):
axes[0].text(v + 1, i, f'{v:.1f}%', va='center', fontsize=9)
axes[0].set_xlim(0, 100)
# --- Right: WHERE it is missing (patterns are visible, randomness is not) ---
axes[1].imshow(titanic.isna().T, aspect='auto', cmap='binary', interpolation='nearest')
axes[1].set_yticks(range(titanic.shape[1]))
axes[1].set_yticklabels(titanic.columns, fontsize=8)
axes[1].set(xlabel='row number (original file order)', title='Where it is missing (black = absent)')
axes[1].grid(False)
plt.tight_layout()
plt.show()
The right chart is the one worth learning to read, because you can use this on any dataset. Cabin is mostly black with scattered gaps. Age has missing values spread evenly across the file.
Neither has a solid black block in one area, which you'd see if a sensor broke for a while or two different files were stuck together. A stripe across several columns at once would mean whole records arrived incomplete. None of that is here. What the chart can't show is whether the gaps depend on another column. Section 2 checks for that.
Section 2The three mechanisms behind missing data#
Terms that come up in interviews, shown here with measurable effects
These three types explain why a value is missing, not how much. Two columns can have the same percentage missing and still need completely different fixes, because the cause is different.
- MCAR, Missing Completely At Random. Whether a value is missing has nothing to do with anything. For example, a lab technician drops a tray of samples. The rows that remain are smaller but still a fair sample.
- MAR, Missing At Random. Whether a value is missing depends on data you can see. For example, older people skip the income question more often, you know everyone's age, and among people of the same age, the income itself doesn't change who skips. Since you can predict the gaps from a column you have, that column can help fill them.
- MNAR, Missing Not At Random. Whether a value is missing depends on the missing value itself. For example, high earners don't share their income, and their income is exactly what you're missing. Your other columns can't fully fix this, because the reason for the gap is the value itself.
The names look alike, so remember them with one question: what does the chance of being missing depend on? Nothing (MCAR), something you have (MAR), or the missing value itself (MNAR). The Titanic data gives real evidence for the first two.
# Is Cabin missing at random, or does it depend on something we can see?
cabin_by_class = pd.DataFrame({
'passengers': titanic.groupby('Pclass').size(),
'cabin_missing_pct': (titanic['Cabin'].isna().groupby(titanic['Pclass']).mean() * 100).round(1),
'age_missing_pct': (titanic['Age'].isna().groupby(titanic['Pclass']).mean() * 100).round(1),
})
print('Missingness by passenger class:')
print(cabin_by_class.to_string())
Missingness by passenger class:
passengers cabin_missing_pct age_missing_pct
Pclass
1 216 18.5 13.9
2 184 91.3 6.0
3 491 97.6 27.7That's clearly not random. A first class passenger has their cabin recorded 81.5% of the time (100% minus 18.5% missing). A third class passenger has it recorded only 2.4% of the time. So whether Cabin is missing depends mostly on Pclass, a column we already have.
The likely reason is how the records survived: the cabin numbers we have today come mostly from first class records, while third class cabins were rarely written down. That makes Cabin look like MAR. Age shows the same pattern, 27.7% missing in third class against 6.0% in second, but much weaker.
Knowing there's a link is useful, but it doesn't tell you how much harm ignoring it would do. To measure that, we use the 714 passengers whose Age we know. We hide some of those ages using each of the three types of missingness, then compare the results with the real values.
# The 714 passengers whose age IS recorded become our known population.
# We hide values from it, so we can compare against the truth afterwards.
known = titanic[titanic['Age'].notna()].copy().reset_index(drop=True)
true_age = known['Age'].values
n = len(known)
true_mean = true_age.mean()
rng = np.random.default_rng(RANDOM_STATE)
u = rng.random(n) # ONE random draw, reused for all three mechanisms
# Only the RULE for what gets hidden differs between these three.
p_mcar = np.full(n, 0.35) # depends on nothing
p_mar = known['Pclass'].map({1: 0.10, 2: 0.25, 3: 0.55}).values # depends on Pclass (observed)
p_mnar = np.where(true_age > np.median(true_age), 0.55, 0.12) # depends on Age itself (hidden)
print(f'Known population : {n} passengers')
print(f'True mean age : {true_mean:.3f} years')
Known population : 714 passengers True mean age : 29.699 years
Here's what makes the test fair. One set of 714 random numbers, u, is drawn once and reused for all three types. Only the rule for hiding values changes: a flat 35% for everyone under MCAR, a rate based on Pclass under MAR, and a rate based on Age itself under MNAR. Because the random numbers are the same, any difference in results comes from the rule alone.
mechanisms = {'MCAR': p_mcar, 'MAR': p_mar, 'MNAR': p_mnar}
masks = {}
print(f"{'mechanism':10s} {'% hidden':>9s} {'mean of what remains':>22s} {'bias':>9s}")
print('-' * 54)
for name, prob in mechanisms.items():
hidden = u < prob # same u every time, different threshold
masks[name] = hidden
observed_mean = true_age[~hidden].mean()
print(f'{name:10s} {hidden.mean():>8.1%} {observed_mean:>22.3f} {observed_mean - true_mean:>+9.3f}')
print('-' * 54)
print(f"{'(truth)':10s} {'0.0%':>9s} {true_mean:>22.3f} {0.0:>+9.3f}")
mechanism % hidden mean of what remains bias ------------------------------------------------------ MCAR 34.6% 29.760 +0.061 MAR 34.6% 31.291 +1.592 MNAR 30.5% 26.174 -3.526 ------------------------------------------------------ (truth) 0.0% 29.699 +0.000
Same random numbers, three rules, three very different results.
MCAR is harmless. The average is off by just +0.061 years (the true average is 29.699). About 34.6% of the column was removed, but the rest still represents everyone well. Under MCAR, dropping rows costs you data, so your estimates get less precise, but it doesn't push them in one direction.
MAR causes bias. The average is off by +1.592 years. Third class passengers were removed more often, and they tended to be younger, so the remaining average went up. Age itself was never used to pick who got hidden. The bias came entirely through Pclass, and the same column could have been used to correct it.
MNAR is the dangerous one. The average is off by −3.526 years. Here the rule looked at age directly and removed older passengers more often. The remaining average is wrong by over three and a half years, in a column with a standard deviation of 14.5. That's a quarter of a standard deviation of hidden bias, from a rule that's easy to imagine happening in real data.
What each type means for you:
- Under MCAR, dropping incomplete rows is safe, and filling with the mean doesn't shift the average (though it still shrinks the spread, as section 4 shows).
- Under MAR, dropping rows can add bias, but your other columns can help correct it. That's what KNN and Iterative imputers are for. Section 4 measures them.
- Under MNAR, no filling method can fully fix it, because the needed information was never collected. Your options are to add a missing-value flag so the model can at least use the pattern (section 3), model the missingness directly, collect better data, or clearly state the limitation.
Section 3Missingness can itself be information#
The habit is to treat every gap as damage. Sometimes the gap itself is useful
When you fill a gap, whatever that gap was telling you is lost. So before filling anything, check whether being missing means something.
titanic['cabin_missing'] = titanic['Cabin'].isna().astype(int)
titanic['age_missing'] = titanic['Age'].isna().astype(int)
survival = titanic.groupby('cabin_missing')['Survived'].agg(['mean', 'size'])
survival.index = ['Cabin recorded', 'Cabin missing']
survival.columns = ['survival_rate', 'passengers']
print(survival.assign(survival_rate=lambda d: (d['survival_rate'] * 100).round(1)).to_string())
survival_rate passengers Cabin recorded 66.7 204 Cabin missing 30.0 687
Passengers with a recorded cabin survived at 66.7%. Those with a blank cabin survived at 30.0%. An empty field predicts more than a two-fold difference in survival. Turn cabin_missing into a 0/1 feature, and a simple logistic regression can use it.
Before getting excited, ask: does cabin_missing tell the model anything Pclass doesn't? Section 2 showed the two are closely linked, since cabin records mostly depended on class.
print('Cabin recorded vs passenger class:')
print(pd.crosstab(titanic['Pclass'], titanic['Cabin'].isna(),
rownames=['Pclass'], colnames=['cabin_missing']).to_string())
cv = StratifiedKFold(n_splits=5, shuffle=True, random_state=RANDOM_STATE)
def auc(features, target=titanic['Survived']):
pipe = make_pipeline(SimpleImputer(strategy='median'), StandardScaler(),
LogisticRegression(max_iter=2000))
return cross_val_score(pipe, features, target, cv=cv, scoring='roc_auc')
print()
print(f"Pclass alone AUC = {auc(titanic[['Pclass']]).mean():.4f}")
print(f"cabin_missing alone AUC = {auc(titanic[['cabin_missing']]).mean():.4f}")
Cabin recorded vs passenger class: cabin_missing False True Pclass 1 176 40 2 16 168 3 12 479 Pclass alone AUC = 0.6813 cabin_missing alone AUC = 0.6369
On its own, cabin_missing scores an AUC of 0.6369. Pclass alone scores 0.6813. So the new feature is mostly a weaker copy of information the model already has. It might still help a little with a few cases, but don't expect much.
base = titanic[['Pclass', 'SibSp', 'Parch', 'Fare']].assign(
is_male=(titanic['Sex'] == 'male').astype(int))
with_indicators = base.assign(cabin_missing=titanic['cabin_missing'],
age_missing=titanic['age_missing'])
scores_base = auc(base)
scores_ind = auc(with_indicators)
print(f'without missing-indicators : AUC {scores_base.mean():.4f} folds {np.round(scores_base, 4)}')
print(f'with missing-indicators : AUC {scores_ind.mean():.4f} folds {np.round(scores_ind, 4)}')
print()
print(f'Gain : {scores_ind.mean() - scores_base.mean():+.4f}')
print(f'Folds improved : {(scores_ind > scores_base).sum()} of 5')
without missing-indicators : AUC 0.8347 folds [0.8409 0.8266 0.8125 0.8138 0.8797] with missing-indicators : AUC 0.8408 folds [0.8509 0.8394 0.8082 0.8209 0.8843] Gain : +0.0060 Folds improved : 4 of 5
The gain is +0.0060 AUC, and it wins 4 folds out of 5, not all 5. In notebook 1, the new churn features won all five folds, so this is weaker evidence. The gain comes from both flags together, and the main reason it's small is clear: cabin_missing is mostly Pclass in disguise, and Pclass is already in the model.
The technique isn't wrong. It just doesn't add much here. The habit to keep: check whether a new feature is really new, or a copy of something you already have. A feature closely linked to an existing one can only add what the existing one doesn't already cover.
Missing-value flags help most when the reason for the gap isn't already in your data. Examples: a blank income field on a loan application, a skipped medical question, or a sensor that goes silent only when it's broken. In those cases the flag carries information no other column has.
Scikit-learn has this built in. That's better than making the column by hand, because a hand-made column has to be remembered and recreated for every future prediction. A setting on the imputer is carried along automatically inside a pipeline.
Two details about the demo below. It fits on only eight rows, so the 35.0 it fills in is the median of those eight ages, not of the whole column. And add_indicator only adds flags for columns that had gaps during fit. Fare had none here, so it gets no flag column, even if a future row arrives with Fare missing.
demo = titanic[['Age', 'Fare']].head(8)
imputer = SimpleImputer(strategy='median', add_indicator=True)
filled = imputer.fit_transform(demo)
print('add_indicator=True appends a 0/1 column for each column that had missing values')
print()
print(pd.DataFrame(filled, columns=['Age', 'Fare', 'Age_was_missing']).round(2).to_string(index=False))
add_indicator=True appends a 0/1 column for each column that had missing values Age Fare Age_was_missing 22.0 7.25 0.0 38.0 71.28 0.0 26.0 7.92 0.0 35.0 53.10 0.0 35.0 8.05 0.0 35.0 8.46 1.0 54.0 51.86 0.0 2.0 21.08 0.0
Section 4Imputation strategies, scored against the truth#
A falling NaN count only proves the code ran. It doesn't tell you if the filled values are any good
Most tutorials show the NaN count before and after filling, and stop. That only proves the imputer ran. What matters is whether the filled values are close to the truth. Here we can check, because we still have the real ages in true_age. Using the MAR rule from section 2, we hide 247 real ages, fill them five different ways, and compare each guess with the real value.
We track two numbers for each method:
- RMSE against the real hidden ages. Lower means the guesses were closer.
- The column's standard deviation after filling, compared with the true 14.52. If an imputer puts many rows at the same few values, this number shrinks. That weakens every relationship the column is part of later, with no warning.
mask = masks['MAR'] # reuse the MAR mask from section 2
# Age with 247 values hidden, plus correlated columns the smart imputers can use
features = pd.DataFrame({
'Age': np.where(mask, np.nan, true_age),
'Pclass': known['Pclass'],
'SibSp': known['SibSp'],
'Parch': known['Parch'],
'Fare': known['Fare'],
'is_male': (known['Sex'] == 'male').astype(int),
})
print(f'Hidden values : {mask.sum()} of {n} ({mask.mean():.1%})')
print(f'True mean of the hidden ages : {true_age[mask].mean():.2f}')
print(f'True std of the full column : {true_age.std():.2f}')
features.head()
Hidden values : 247 of 714 (34.6%) True mean of the hidden ages : 26.69 True std of the full column : 14.52
Age Pclass SibSp Parch Fare is_male 0 22.0 3 1 0 7.2500 1 1 38.0 1 1 0 71.2833 0 2 26.0 3 0 0 7.9250 0 3 35.0 1 1 0 53.1000 0 4 NaN 3 0 0 8.0500 1
results = {}
# --- Univariate: one column, one statistic ---
results['mean'] = SimpleImputer(strategy='mean').fit_transform(features)[:, 0]
results['median'] = SimpleImputer(strategy='median').fit_transform(features)[:, 0]
# --- Multivariate: use the other columns ---
# KNN measures distance between rows, so the columns must be on a common scale first
scaler = StandardScaler().fit(features)
knn_scaled = KNNImputer(n_neighbors=5).fit_transform(scaler.transform(features))
results['KNN (k=5)'] = scaler.inverse_transform(knn_scaled)[:, 0]
results['Iterative'] = IterativeImputer(random_state=0, max_iter=20).fit_transform(features)[:, 0]
# --- Domain-driven: median age within each Pclass x Sex group ---
group_median = features.groupby(['Pclass', 'is_male'])['Age'].transform('median')
results['group median (Pclass x Sex)'] = np.where(mask, group_median, true_age)
print('Imputed with', len(results), 'strategies')
Imputed with 5 strategies
Here's what each of the five methods does.
Mean and median ignore all other columns and fill every gap with one number: the average or middle value of the known ages. They're fast, but they can't use anything the other columns know about a passenger.
KNN uses the other columns. For a passenger missing Age, it finds the five most similar passengers based on Pclass, SibSp, Parch, Fare and is_male, then averages their ages. "Similar" is measured by distance, so the code scales every column with StandardScaler first.
Without scaling, Fare, which goes into the hundreds, would dominate the distance, and is_male, which is only 0 or 1, would barely count. This is different from notebook 1's logistic regression. For distance-based methods, skipping scaling doesn't just slow things down. It gives wrong answers, because the column with the biggest numbers controls every comparison.
Iterative imputation (inspired by a method called MICE) goes further. It trains a small regression model to predict Age from the other columns and fills the gaps with its predictions. Then it does the same for every other column with gaps, feeding each improved estimate into the next round. After a few rounds, the estimates settle. (Here only Age has gaps, so there's just one column to predict.) It often beats KNN, because it models the relationship directly instead of copying from neighbors.
Group median is the simple option: the median age for each combination of Pclass and sex. No model, no distances, just six numbers from a table.
comparison = pd.DataFrame([
{
'strategy': name,
'RMSE': np.sqrt(np.mean((filled[mask] - true_age[mask]) ** 2)),
'mean_of_filled': filled[mask].mean(),
'std_after': filled.std(),
}
for name, filled in results.items()
]).sort_values('RMSE').reset_index(drop=True)
comparison['std_lost_pct'] = ((1 - comparison['std_after'] / true_age.std()) * 100).round(1)
print(f'True mean of hidden ages : {true_age[mask].mean():.2f}')
print(f'True std of column : {true_age.std():.2f}')
print()
print(comparison.round(3).to_string(index=False))
True mean of hidden ages : 26.69
True std of column : 14.52
strategy RMSE mean_of_filled std_after std_lost_pct
Iterative 11.999 27.243 12.416 14.5
KNN (k=5) 12.399 27.684 13.067 10.0
group median (Pclass x Sex) 12.926 26.413 12.282 15.4
median 14.158 30.000 11.864 18.3
mean 14.514 31.291 11.848 18.4There's more to learn from this table than just who won.
Iterative imputation wins with an RMSE of 11.999, and mean imputation is worst at 14.514. That's what you'd expect under MAR. The smarter methods can use Pclass, sex and Fare to guess that a third class man is probably younger than a first class woman. The plain mean has nothing to go on.
The simple group median nearly matches the fancy methods. Using the median age for each Pclass and sex group scores 12.926. That easily beats mean and median and comes close to KNN. It's two lines of pandas with no model, and on millions of rows it would run much faster. Try an obvious groupby before reaching for IterativeImputer.
Mean imputation removes 18.4% of the column's spread. The true standard deviation is 14.52, but after mean imputation it's 11.85, because 247 rows were all given the same number. Every relationship involving Age is now weaker than it should be, and nothing will warn you.
The average of the filled values tells a story too. The real average of the hidden ages is 26.69. Mean imputation fills them with 31.29, four and a half years too high. It used the average of the passengers who stayed visible, and those were already older because of the MAR rule. This is section 2's bias showing up as a real, wrong number.
fig, axes = plt.subplots(1, 3, figsize=(15, 4.2), sharey=True)
panels = [('mean', 'Mean imputation'), ('group median (Pclass x Sex)', 'Group median'),
('Iterative', 'Iterative imputation')]
for ax, (key, title) in zip(axes, panels):
ax.hist(true_age, bins=35, alpha=0.45, color='#7f7f7f', label='true distribution')
ax.hist(results[key], bins=35, alpha=0.6, color='#c4453c', label='after imputation')
ax.set(xlabel='Age', title=f'{title}\nRMSE {np.sqrt(np.mean((results[key][mask] - true_age[mask]) ** 2)):.2f}')
ax.legend(fontsize=8)
axes[0].set_ylabel('passengers')
plt.tight_layout()
plt.show()
The left chart shows what a NaN count can't. Mean imputation creates a spike of 247 passengers all at one age, which doesn't exist in the real data. A model reading this column will think that one age is hugely common.
The other two are better, but not perfect. Group median makes several smaller spikes instead of one, because it can only produce six values, one per Pclass and sex group. That's better than one spike but still lumpy. Iterative imputation predicts a separate value for each row, so it's not limited to six numbers. But it still leaves a bump near the middle, because regression predictions tend to cluster near the average and avoid extremes.
This applies beyond this one column: any method that fills in its single best guess pulls values toward the middle, so it shrinks the real spread of the data. Here the lost spread ranges from 10.0% for KNN to 18.4% for the plain mean. None of the five methods gets to zero.
Methods that add some random noise to each guess, such as IterativeImputer(sample_posterior=True), keep more of the spread. The trade-off is that each single filled value is a bit less accurate.
Which method to use when#
- Mean or median when very little data is missing, or you need a quick first baseline. Use the median for skewed columns and the mean for balanced ones.
- Mode, or a constant like
'Unknown', for category columns, since you can't average "Southampton." A constant is often better, because it keeps the fact that the value was missing instead of guessing a category that might be wrong. - A groupby median when you know a sensible grouping the value depends on. It often gives the best result for the least effort.
- KNN or Iterative when the gaps look like MAR, the columns are related, and the better accuracy is worth the extra computing time. Both need numeric columns, KNN also needs them on the same scale, and both slow down as rows grow.
- Leave it missing if your model handles NaN on its own. LightGBM, XGBoost, CatBoost and scikit-learn's
HistGradientBoostingClassifierall learn where to send missing values during training, which often works better than filling them yourself.
Category columns need the same care, since you can't take the average of a port.
# Categorical columns need a different treatment: there is no mean of 'Southampton'
embarked = titanic['Embarked']
print(f"Embarked missing: {embarked.isna().sum()} rows")
print(embarked.value_counts(dropna=False).to_string())
print()
mode_filled = embarked.fillna(embarked.mode()[0])
unknown_filled = embarked.fillna('Unknown')
print(f"Mode imputation -> both rows become '{embarked.mode()[0]}', the most common port")
print(f"Constant imputation-> both rows become 'Unknown', preserving that we did not know")
print()
print('Category counts after each choice:')
print(pd.DataFrame({'mode_filled': mode_filled.value_counts(),
'unknown_filled': unknown_filled.value_counts()}).fillna(0).astype(int).to_string())
Embarked missing: 2 rows
Embarked
S 644
C 168
Q 77
NaN 2
Mode imputation -> both rows become 'S', the most common port
Constant imputation-> both rows become 'Unknown', preserving that we did not know
Category counts after each choice:
mode_filled unknown_filled
Embarked
C 168 168
Q 77 77
S 646 644
Unknown 0 2Section 5Imputation is a transformer, so the leakage rule applies#
Filling gaps feels like cleaning, and cleaning feels like something you do before splitting. That's the trap
Notebook 1 set the rule for every transformer: everything it learns must come from training rows only. Imputation is the easiest place to break this rule. Filling gaps doesn't feel like modeling. It feels like tidying up, and it seems fine to tidy the whole dataset before splitting.
But the median used to fill gaps is learned from data, just like a scaler's mean. If that data includes test rows, it's the same leak as in notebook 1.
train, test = train_test_split(titanic, test_size=0.2, random_state=RANDOM_STATE,
stratify=titanic['Survived'])
print(f'Age median computed on FULL data : {titanic["Age"].median():.2f}')
print(f'Age median computed on TRAIN only : {train["Age"].median():.2f}')
print(f'They differ : {titanic["Age"].median() != train["Age"].median()}')
print()
print('Filling the whole column before splitting means every training row was filled')
print(f'using a number that partly describes the {len(test)} test passengers.')
Age median computed on FULL data : 28.00 Age median computed on TRAIN only : 28.50 They differ : True Filling the whole column before splitting means every training row was filled using a number that partly describes the 179 test passengers.
28.00 against 28.50 is a small gap, and as in notebook 1, the size doesn't matter. What matters is that it isn't zero. The full-data median includes the 179 passengers in the test set. If you fill the whole column before splitting, the training rows are cleaned using information from test data.
# The correct pattern: the imputer is a pipeline step, fitted inside fit()
model = Pipeline([
('impute', SimpleImputer(strategy='median', add_indicator=True)),
('scale', StandardScaler()),
('clf', LogisticRegression(max_iter=2000)),
])
feature_cols = ['Pclass', 'Age', 'SibSp', 'Parch', 'Fare']
X_train = train[feature_cols].assign(is_male=(train['Sex'] == 'male').astype(int))
X_test = test[feature_cols].assign(is_male=(test['Sex'] == 'male').astype(int))
model.fit(X_train, train['Survived'])
learned_median = model.named_steps['impute'].statistics_[feature_cols.index('Age')]
print(f'Median stored inside the fitted pipeline : {learned_median:.2f}')
print(f'That is the TRAIN median, not the full-data median ({titanic["Age"].median():.2f})')
print()
print(f'Test accuracy : {model.score(X_test, test["Survived"]):.4f}')
Median stored inside the fitted pipeline : 28.50 That is the TRAIN median, not the full-data median (28.00) Test accuracy : 0.8156
The pipeline stores 28.50, the training median, and uses that same number on the test rows. It never looks at the test data to decide what to fill in, just like the scaler in notebook 1.
You might wonder why section 4 fitted its imputers on all 714 rows. That was fine because it only measured how close the guesses were to the truth. No model was being tested. As soon as you judge a model's score, the imputer has to be fitted on training rows only.
There's a second reason this matters. In real use, a model often scores one new passenger, applicant or customer at a time. A single row has no column to take a median of. The imputer needs a number it already learned during training. Code like df['Age'].fillna(df['Age'].median()) can't do that. It only works when you have the whole file, not when a single record arrives on its own, which is what most deployed models deal with.
Section 6Outliers, and the question most tutorials skip#
Finding an outlier is easy. Deciding if it's an error or real is what matters
An outlier is a value far from the rest. Finding them is easy and every tutorial covers it. The harder question, and the one that decides what you should do, is often skipped: is this value wrong, or is it real?
The two need opposite fixes. A typo that records a salary as 2,000,000 instead of 200,000 should be fixed or removed, or one mistake can dominate the model. A real billionaire in an income dataset isn't a mistake. Deleting them means your data no longer represents the real population. Titanic's Fare column has real examples of both.
fare = titanic['Fare']
print(f'count : {fare.count()}')
print(f'mean : {fare.mean():.2f}')
print(f'median : {fare.median():.2f}')
print(f'std : {fare.std():.2f}')
print(f'min : {fare.min():.2f}')
print(f'max : {fare.max():.2f}')
print(f'skew : {fare.skew():.2f} (0 = symmetric, >1 = strong right tail)')
print()
print(f'Passengers who paid exactly 0 : {(fare == 0).sum()}')
count : 891 mean : 32.20 median : 14.45 std : 49.69 min : 0.00 max : 512.33 skew : 4.79 (0 = symmetric, >1 = strong right tail) Passengers who paid exactly 0 : 15
A mean of 32.20 against a median of 14.45, a max of 512.33 and a skew of 4.79 all point to the same thing: a long tail of high values. Fifteen passengers paid nothing at all. We'll come back to them.
fig, axes = plt.subplots(1, 2, figsize=(13, 4.2))
axes[0].boxplot(fare, vert=True, widths=0.5)
axes[0].set(ylabel='Fare (pounds)', title='Boxplot: everything past the whisker is flagged')
axes[0].set_xticks([])
axes[1].hist(fare, bins=60, color='#3b76af', edgecolor='white')
axes[1].set(xlabel='Fare (pounds)', ylabel='passengers',
title='Histogram: a long right tail, and a spike at zero')
axes[1].axvline(fare.median(), color='green', lw=2, label=f'median {fare.median():.2f}')
axes[1].axvline(fare.mean(), color='red', lw=2, ls='--', label=f'mean {fare.mean():.2f}')
axes[1].legend(fontsize=9)
plt.tight_layout()
plt.show()
That gap between the median and mean lines is the quickest visual check for skew. It's also a warning: any method based on the mean, like the plain z-score, will struggle on this column.
There are three common rules for finding outliers. Running all three on the same column shows how much they can disagree.
q1, q3 = fare.quantile([0.25, 0.75])
iqr = q3 - q1
iqr_lower, iqr_upper = q1 - 1.5 * iqr, q3 + 1.5 * iqr
iqr_flags = (fare < iqr_lower) | (fare > iqr_upper)
z_scores = ((fare - fare.mean()) / fare.std()).abs()
z_flags = z_scores > 3
# Modified z-score: median and MAD instead of mean and std, so outliers cannot inflate it
mad = (fare - fare.median()).abs().median()
modified_z = 0.6745 * (fare - fare.median()).abs() / mad
mad_flags = modified_z > 3
print(f'{"method":22s} {"threshold":>22s} {"flagged":>9s}')
print('-' * 55)
print(f'{"IQR (1.5 x IQR)":22s} {f"above {iqr_upper:.2f}":>22s} {iqr_flags.sum():>9d}')
print(f'{"z-score (|z| > 3)":22s} {f"above {fare.mean() + 3 * fare.std():.2f}":>22s} {z_flags.sum():>9d}')
print(f'{"modified z (MAD > 3)":22s} {f"MAD = {mad:.2f}":>22s} {mad_flags.sum():>9d}')
method threshold flagged ------------------------------------------------------- IQR (1.5 x IQR) above 65.63 116 z-score (|z| > 3) above 181.28 20 modified z (MAD > 3) MAD = 6.90 171
The same 891 fares, three standard methods, and between 20 and 171 flagged. Outlier detection clearly doesn't have one right answer.
The z-score flags only 20 passengers, far fewer than the IQR method's 116. The reason is called masking. The z-score uses the column's mean and standard deviation, and extreme values pull both of those up. That raises the bar for what counts as extreme, so the outliers slip under it.
without_extremes = fare[fare < 500] # drop just the three highest fares
print(f'Standard deviation WITH the three 512.33 fares : {fare.std():.2f}')
print(f'Standard deviation WITHOUT them : {without_extremes.std():.2f}')
print()
print(f'So the |z| > 3 cutoff sits at : {fare.mean() + 3 * fare.std():.2f}')
print(f'Remove those 3 rows and the cutoff drops to : {without_extremes.mean() + 3 * without_extremes.std():.2f}')
print()
print('Three rows out of 891 moved the detection threshold by 27 pounds.')
print('The outliers helped decide how tolerant the test that judges them would be.')
Standard deviation WITH the three 512.33 fares : 49.69 Standard deviation WITHOUT them : 41.18 So the |z| > 3 cutoff sits at : 181.28 Remove those 3 rows and the cutoff drops to : 154.11 Three rows out of 891 moved the detection threshold by 27 pounds. The outliers helped decide how tolerant the test that judges them would be.
Just three rows out of 891 moved the cutoff by 27 pounds. The outliers helped set a bar that let them pass.
That's why quartiles, the median and the modified z-score hold up better. The modified z-score uses the median and the median absolute deviation (MAD) instead of the mean and standard deviation. The 0.6745 in the code puts MAD on the same scale as a standard deviation. A few extreme values barely move them. The mean and standard deviation, on the other hand, get pulled toward the very values they're measuring.
Robust doesn't mean right, though. The modified z-score flagged the most fares, 171, because half of all fares sit within 6.90 of the median, so its scale is very tight. (The usual cutoff for it is 3.5. This notebook uses 3 to match the z-score.)
Detection gives you a list of suspects. The more important part is checking whether they're actually wrong.
print('=== The three highest fares ===')
print(titanic.nlargest(3, 'Fare')[['Name', 'Pclass', 'Ticket', 'Cabin', 'Fare', 'Survived']].to_string(index=False))
print()
print('=== Every passenger who paid nothing ===')
print(titanic[titanic['Fare'] == 0][['Name', 'Pclass', 'Ticket', 'Survived']].to_string(index=False))
=== The three highest fares ===
Name Pclass Ticket Cabin Fare Survived
Ward, Miss. Anna 1 PC 17755 NaN 512.3292 1
Cardeza, Mr. Thomas Drake Martinez 1 PC 17755 B51 B53 B55 512.3292 1
Lesurer, Mr. Gustave J 1 PC 17755 B101 512.3292 1
=== Every passenger who paid nothing ===
Name Pclass Ticket Survived
Leonard, Mr. Lionel 3 LINE 0
Harrison, Mr. William 1 112059 0
Tornquist, Mr. William Henry 3 LINE 1
Parkes, Mr. Francis "Frank" 2 239853 0
Johnson, Mr. William Cahoone Jr 3 LINE 0
Cunningham, Mr. Alfred Fleming 2 239853 0
Campbell, Mr. William 2 239853 0
Frost, Mr. Anthony Wood "Archie" 2 239854 0
Johnson, Mr. Alfred 3 LINE 0
Parr, Mr. William Henry Marsh 1 112052 0
Watson, Mr. Ennis Hastings 2 239856 0
Knight, Mr. Robert J 2 239855 0
Andrews, Mr. Thomas Jr 1 112050 0
Fry, Mr. Richard 1 112058 0
Reuchlin, Jonkheer. John George 1 19972 0Neither group turns out to be a data entry error.
The three highest fares all share one ticket, PC 17755. They're not three people who each paid 512 pounds. They're one group on a single booking, and Fare records what the ticket cost, not what each person paid. The outlier isn't a bad value. It shows the column measures something different from what you'd assume.
The zero fares are company staff traveling for free. Eight of them, with ticket numbers in a row, were a team from Harland & Wolff, the shipyard that built Titanic, on board to watch over its first voyage. They were led by Thomas Andrews, the ship's designer. Four have the ticket LINE, American Line seamen given free passage. The rest also worked for shipping companies. These people really paid nothing, so zero is the correct value.
Deleting either group would be a mistake. The zero fares are a real group, and removing them would erase these staff from a dataset about who survived. The high fares come from a misunderstanding of the column, and unlike the zero fares, that's something we can fix.
# Fare is per TICKET, so divide it by the number of people sharing that ticket
party_size = titanic.groupby('Ticket')['Ticket'].transform('size')
titanic['fare_per_person'] = titanic['Fare'] / party_size
print('Party sizes:')
print(party_size.value_counts().sort_index().rename_axis('people_on_ticket').to_string())
print()
print(titanic[titanic['Ticket'] == 'PC 17755'][['Name', 'Fare', 'fare_per_person']].to_string(index=False))
Party sizes:
people_on_ticket
1 547
2 188
3 63
4 44
5 10
6 18
7 21
Name Fare fare_per_person
Ward, Miss. Anna 512.3292 170.7764
Cardeza, Mr. Thomas Drake Martinez 512.3292 170.7764
Lesurer, Mr. Gustave J 512.3292 170.7764def count_iqr_outliers(s):
a, b = s.quantile([0.25, 0.75])
spread = b - a
return int(((s < a - 1.5 * spread) | (s > b + 1.5 * spread)).sum())
print(f'{"column":18s} {"max":>9s} {"skew":>7s} {"IQR outliers":>14s}')
print('-' * 52)
for label, s in [('Fare', titanic['Fare']), ('fare_per_person', titanic['fare_per_person'])]:
print(f'{label:18s} {s.max():>9.2f} {s.skew():>7.2f} {count_iqr_outliers(s):>14d}')
column max skew IQR outliers ---------------------------------------------------- Fare 512.33 4.79 116 fare_per_person 221.78 4.38 59
Once we understood what the column measured, about half the flagged outliers disappeared. The max dropped from 512.33 to 221.78 and the skew went down, without deleting any rows or picking any cutoff. We just calculated the number we should have had from the start. This may be the most useful lesson in outlier work: many outliers are definition problems, not data problems. Before capping or deleting anything, check that the column means what you think it means.
One limit: the party size here only counts people in these 891 rows. Some tickets were shared with passengers who are in the separate test file, so a few per-person fares are still too high. The fix is only as good as the data you count with.
Next, check whether this fix actually changes what a model reports.
shared = titanic[['Pclass', 'SibSp', 'Parch']].assign(
is_male=(titanic['Sex'] == 'male').astype(int))
with_raw_fare = shared.assign(Fare=titanic['Fare'])
with_per_person = shared.assign(Fare=titanic['fare_per_person'])
s_raw = auc(with_raw_fare)
s_pp = auc(with_per_person)
print(f'with raw Fare : AUC {s_raw.mean():.4f}')
print(f'with fare_per_person : AUC {s_pp.mean():.4f}')
print(f'Folds improved : {(s_pp > s_raw).sum()} of 5')
with raw Fare : AUC 0.8347 with fare_per_person : AUC 0.8352 Folds improved : 2 of 5
It barely changes anything, and the fix wins only 2 folds out of 5, which looks like noise. The fix was still right. Correcting a misunderstanding of a column is correct whether or not a score notices. Here, survival depended mostly on class and sex, and Fare was already mostly a stand-in for class, so there wasn't much room to improve.
For a different question, like predicting what each person paid, the same fix would matter a lot. A fix that doesn't change today's model can still stop you from drawing the wrong conclusion tomorrow.
Handling outliers that are real#
When a value is extreme but correct, you have four options, and they do different things.
p99 = fare.quantile(0.99)
treatments = pd.DataFrame({
'original': fare,
'capped_99th': fare.clip(upper=p99), # winsorising: pull extremes to a percentile
'log1p': np.log1p(fare), # compress the tail, log1p is safe at 0
})
summary = pd.DataFrame({
'max': treatments.max(),
'std': treatments.std(),
'skew': treatments.skew(),
'rows_kept': [len(fare), len(fare), len(fare)],
})
removed = fare[~iqr_flags]
summary.loc['iqr_removed'] = [removed.max(), removed.std(), removed.skew(), len(removed)]
print(f'99th percentile = {p99:.2f}')
print()
print(summary.round(2).to_string())
print()
print(f'Removal discards {len(fare) - len(removed)} of {len(fare)} rows ({1 - len(removed) / len(fare):.1%})')
99th percentile = 249.01
max std skew rows_kept
original 512.33 49.69 4.79 891.0
capped_99th 249.01 42.52 3.11 891.0
log1p 6.24 0.97 0.39 891.0
iqr_removed 65.00 13.58 1.43 775.0
Removal discards 116 of 891 rows (13.0%)fig, axes = plt.subplots(1, 3, figsize=(15, 4))
for ax, (col, title) in zip(axes, [('original', 'Original'),
('capped_99th', f'Capped at 99th pct ({p99:.0f})'),
('log1p', 'Log transformed')]):
ax.hist(treatments[col], bins=50, color='#3b76af', edgecolor='white')
ax.set(xlabel='Fare' if col != 'log1p' else 'log(1 + Fare)',
title=f'{title}\nskew {treatments[col].skew():.2f}')
axes[0].set_ylabel('passengers')
plt.tight_layout()
plt.show()
The log transform does best here. It brings skew from 4.79 down to 0.39, keeps every row and keeps each passenger's fare in the same order. Capping only gets to 3.11, because even the values below the 99th percentile still have a long tail.
- Capping (also called winsorising) keeps every row and limits the effect of extreme values. The cost is that it changes real values: three passengers who paid different amounts can end up with the same recorded fare. Use it when the extremes are believable but you don't want a few of them to dominate the model. The 99th percentile is learned from data, so like an imputer, it must be learned from training rows only.
- A log or square root transform squeezes the tail without deleting or changing the order of anything. It's usually the best first choice for skewed data like money, counts and durations. It can't handle negative values.
log1phandles zeros safely, which matters here because there are fifteen real zeros. Notebook 3 covers this transform in detail. - Removal is the bluntest option, and here it would throw away 13.0% of all passengers. Only use it for values you've confirmed are errors, not just extreme. And it only works on training data. When the model is in use, it still has to give an answer for an extreme new passenger. You can't delete them.
- Keeping the value and adding a flag column lets the model decide how much to trust extreme cases. This is often best when you're not sure if a value is real.
Don't apply any of these to every numeric column automatically just to make the notebook look tidy. Each one makes an assumption about what the data means, so choose it on purpose, one column at a time.
Section 7Duplicate records#
They make your data look bigger than it is and can put the same information in both training and test sets
drop_duplicates() easily handles rows that are exact copies. The harder case is a record that's the same in content but not in every character. That's where the "clean" Telco dataset turns out to be hiding something.
print('=== Titanic ===')
print(f'Exact duplicate rows : {titanic.duplicated().sum()}')
print(f'Ignoring PassengerId : {titanic.drop(columns=["PassengerId"]).duplicated().sum()}')
print()
print('=== Telco, which looked spotless at the top of this notebook ===')
print(f'Exact duplicate rows : {telco.duplicated().sum()}')
print(f'Ignoring customerID : {telco.drop(columns=["customerID"]).duplicated().sum()}')
=== Titanic === Exact duplicate rows : 0 Ignoring PassengerId : 0 === Telco, which looked spotless at the top of this notebook === Exact duplicate rows : 0 Ignoring customerID : 22
At the start of this notebook, Telco showed zero duplicates, and that was true. Every row is different because every row has a unique customerID. Remove that one column and 22 duplicate rows appear. They're identical in all twenty other columns and differ only in an ID the system assigned.
This is the most common way duplicate checks fail. An auto-generated ID, a load timestamp or a file name makes every row technically unique, so drop_duplicates() says the data is clean when it's actually full of repeats.
Why it matters: if one copy lands in the training set and another in the test set, the model is tested on a row it has already seen. The score looks better than it will be on truly new data. It's a quiet form of the leakage from notebook 1.
attributes = [c for c in telco.columns if c != 'customerID']
dupe_mask = telco.duplicated(subset=attributes, keep=False) # keep=False flags ALL copies
print(f'Rows involved in a duplicate group: {dupe_mask.sum()}')
print()
print('Two example groups:')
example = (telco[dupe_mask]
.sort_values(['tenure', 'MonthlyCharges', 'gender'])
[['customerID', 'gender', 'SeniorCitizen', 'tenure', 'MonthlyCharges', 'Contract', 'Churn']])
print(example.head(4).to_string(index=False))
Rows involved in a duplicate group: 42 Two example groups: customerID gender SeniorCitizen tenure MonthlyCharges Contract Churn 2866-IKBTM Female 0 1 19.55 Month-to-month No 8605-ITULD Female 0 1 19.55 Month-to-month No 7721-DVEKZ Female 0 1 19.65 Month-to-month No 0970-QXPXW Female 0 1 19.65 Month-to-month No
Two women, one month of tenure, the same monthly charges, the same contract, the same churn result and the same values everywhere else. Only the ID is different. Are they real duplicates? We can't be sure, and that's normal.
There are two possible explanations. One customer might have been entered twice with two IDs, which is a real duplicate. Or two different people might just match on all twenty columns. That's quite possible, since most of those columns only have two or three possible values, so there aren't that many different profiles.
With 7,043 rows and about twenty mostly yes/no columns, some matches by chance are expected. The sensible step is to flag the group, check how big it is (22 extra copies out of 7,043 rows is 0.3%), and ask whoever owns the data before deleting anything. Deleting real customers isn't clearly better than keeping a few accidental duplicates.
When you do remove duplicates, the keep setting decides which copy stays. That matters a lot when the copies aren't identical, for example when they show the same customer at two different times.
# A small worked example: the same customer, updated twice
records = pd.DataFrame({
'customer_id': [1, 1, 1, 2, 2],
'updated_on': pd.to_datetime(['2024-01-05', '2024-03-11', '2024-06-02', '2024-02-01', '2024-05-20']),
'plan': ['basic', 'basic', 'premium', 'premium', 'basic'],
})
print('All records:')
print(records.to_string(index=False))
print()
print("keep='first' (earliest, after sorting by date) - the ORIGINAL state:")
print(records.sort_values('updated_on').drop_duplicates('customer_id', keep='first').to_string(index=False))
print()
print("keep='last' (latest) - the CURRENT state:")
print(records.sort_values('updated_on').drop_duplicates('customer_id', keep='last').to_string(index=False))
All records:
customer_id updated_on plan
1 2024-01-05 basic
1 2024-03-11 basic
1 2024-06-02 premium
2 2024-02-01 premium
2 2024-05-20 basic
keep='first' (earliest, after sorting by date) - the ORIGINAL state:
customer_id updated_on plan
1 2024-01-05 basic
2 2024-02-01 premium
keep='last' (latest) - the CURRENT state:
customer_id updated_on plan
2 2024-05-20 basic
1 2024-06-02 premiumThe two settings disagree for both customers. Customer 1 is on basic under one and premium under the other. Customer 2 is the reverse. If you're predicting something about a customer now, you want the latest record, so sort by date and use keep='last'.
If you're rebuilding what was known at an earlier date, you want the record from that date. Taking the latest one would leak future information into a feature about the past. Notebook 5 covers this in more detail with date features.
The rule to remember: sort before removing duplicates. keep='first' keeps the first row in the DataFrame's current order. If you haven't sorted it, that order is just however the file happened to arrive. That's not a choice. It's an accident.
Section 8Inconsistent category labels#
When people type values instead of picking from a list, the same thing gets spelled several ways
New York, NY, new york and New York (with an extra space) are four different strings to pandas, but one city to everyone else. The damage is easy to miss. Each spelling becomes its own category, one-hot encoding creates four thin columns instead of one, and each has too few rows for a model to learn from.
Titanic and Telco are both already tidy here, so the example below is made up on purpose. It's still worth practicing, because real data from forms, call centers or shared spreadsheets almost always needs this.
# Constructed to look like data typed by several people over several years
messy = pd.DataFrame({
'customer': range(1, 13),
'city': ['New York', 'NY', 'new york', ' New York ', 'NEW YORK', 'new_york',
'Los Angeles', 'LA', 'los angeles', 'Chicago', 'chicago', 'Chicago '],
})
print(f'Distinct strings pandas sees: {messy["city"].nunique()}')
print(messy['city'].value_counts().to_string())
Distinct strings pandas sees: 12 city New York 1 NY 1 new york 1 New York 1 NEW YORK 1 new_york 1 Los Angeles 1 LA 1 los angeles 1 Chicago 1 chicago 1 Chicago 1
Pandas counts twelve different strings for what are clearly three cities. Fixing this well means splitting the work into two different kinds of steps.
# Step 1: normalise whitespace, case, and separators.
# This alone collapses most variants.
cleaned = (messy['city']
.str.strip() # remove leading/trailing spaces
.str.lower() # case-fold
.str.replace('_', ' ', regex=False)
.str.replace(r'\s+', ' ', regex=True)) # collapse repeated internal spaces
print(f'After normalising: {cleaned.nunique()} distinct values')
print(cleaned.value_counts().to_string())
After normalising: 5 distinct values city new york 5 chicago 3 los angeles 2 ny 1 la 1
Normalizing is simple and safe. Removing extra spaces, making everything the same case and cleaning up separators can be done on any text column without knowing what it contains. That fixed ten of the twelve variants. Only the two abbreviations, ny and la, were left separate from their full names.
# Step 2: a mapping for abbreviations, which no amount of string cleaning can resolve.
# 'ny' and 'new york' are only the same city if you know that.
aliases = {'ny': 'new york', 'la': 'los angeles', 'nyc': 'new york'}
canonical = cleaned.replace(aliases).str.title()
messy['city_clean'] = canonical
print(f'Final: {canonical.nunique()} distinct cities (from {messy["city"].nunique()} raw strings)')
print()
print(messy.to_string(index=False))
Final: 3 distinct cities (from 12 raw strings)
customer city city_clean
1 New York New York
2 NY New York
3 new york New York
4 New York New York
5 NEW YORK New York
6 new_york New York
7 Los Angeles Los Angeles
8 LA Los Angeles
9 los angeles Los Angeles
10 Chicago Chicago
11 chicago Chicago
12 Chicago ChicagoMapping abbreviations needs knowledge and carries risk. Only someone who knows the data can be sure NY means New York and not a typo for something else. Check this step carefully, because it can go wrong silently. A bad mapping doesn't cause an error. It just merges two different categories into one, which is worse than leaving them messy but separate.
A few habits to use on real, messier data:
- Print the value counts before and after each cleaning step. It's the quickest way to catch a mapping that merged too much.
- Look at the rare values. Categories that appear only once or twice are where typos hide.
- For a large, messy column, a fuzzy matching library like
rapidfuzzcan suggest groups of similar strings. Treat these as suggestions for a person to check, not final answers. Fuzzy matching will happily mergeSpringfield, ILwithSpringfield, MA. - Fix the problem at the source if you can. A dropdown on the data entry form prevents more of this than any amount of cleaning later.
The same idea applies to mixed units (pounds and kilograms in one column), mixed date formats and mixed codes for missing data. One column might use -1, 999, 'N/A' and an empty string, all meaning "missing." Notebook 1 already ran into a version of this: a blank string in TotalCharges that .info() counted as present because it wasn't technically null.
Wrap-upKey things to remember from this notebook#
Profile before you change anything, and don't rely on describe(). It skips every text column and only shows missing data through a count row that's easy to miss. Make a proper missing-value report and chart where the gaps are, because the pattern tells you more than a percentage.
Why data is missing decides what you can do about it. Hiding about a third of a column three ways, using the same random numbers, shifted the average by +0.061 years under MCAR, +1.592 under MAR and −3.526 under MNAR. Under MCAR, dropping rows costs data but not correctness. Under MAR, your other columns can fix the damage, which is what KNN and Iterative imputation are for. Under MNAR, nothing fully fixes it, and it's better to say so than to fill and hope.
A missing value can be useful information. Passengers with a recorded cabin survived at 66.7%, against 30.0% for those without. Adding missing-value flags gave only a small gain and won 4 of 5 folds, because cabin_missing mostly repeats Pclass. Always check whether a new feature is really new.
Judge imputers against the real values, not a falling NaN count. Iterative imputation won with an RMSE of 11.999 and the mean did worst at 14.514. But a simple median within Pclass and sex groups scored 12.926 with two lines of pandas and no model. Mean imputation also removed 18.4% of the column's spread by putting 247 passengers at one value, and no error message will tell you. Any method that fills in a single best guess shrinks the spread to some degree.
Imputers are transformers, so fit them on training data only. The Titanic age median is 28.00 on the full file and 28.50 on the training set. Also, a model scoring one row at a time has no column to take a median of, so that number must be learned in advance.
Outlier detection doesn't have one right answer. The same Fare column gave 116 flagged rows with IQR, 20 with the plain z-score and 171 with the modified z-score. The z-score flagged fewer because three extreme fares raised the standard deviation that sets its own cutoff. The cutoff is 181.28 with those fares and would be 154.11 without them. On skewed data, even the robust rules flag many ordinary values, so treat flags as a list to check.
What you do after detection matters more than detection. Titanic's highest fares weren't errors. They were one ticket shared by three people, and dividing by group size cut the outlier count about in half without deleting a row. The zero fares were shipyard and shipping-line staff who really paid nothing. Many outliers are definition problems, not data problems, so check what a column means before capping or deleting.
Duplicates hide behind unique IDs. Telco showed zero duplicate rows but had 22 once customerID was removed. Look for duplicates using the columns that describe the real thing, not the ones a system assigned. Duplicates split across training and test sets inflate your score. Always sort before using keep='first' or keep='last'.
Notebook 3 moves from fixing data to reshaping it: scaling, the log and power transforms previewed here, binning, and combined features that help simple models capture the curves from notebook 1.
This article goes with Notebook 2 of a feature engineering series built on the Telco Customer Churn and Titanic datasets. Every code cell above runs exactly as shown in the notebook, with its real output.