Mathew K Analytics

Lesson 2 · Data Mining Full Course

Data Mining Full Course Part 2 (2 Hours): Data Cleaning, Preprocessing & PCA in Python

All 5 lesson notebooks from Part 2 of the Data Mining Full Course in Python, in teaching order. Each lesson starts with a heading; the timestamps below jump…

📓 Full notebook

Download .ipynb

Data Mining Full Course, Part 2: Data Cleaning, Preprocessing and PCA#

All 5 lesson notebooks from Part 2 of the Data Mining Full Course in Python, in teaching order. Each lesson starts with a heading; the timestamps below jump to it in the video.

  • 0:00 Lesson 9: Data Cleaning- Handling Missing Values, Noise, and Outliers
  • 0:23:53 Lesson 10: Data Transformation- Normalization, Encoding, Feature Scaling
  • 0:47:14 Lesson 11: Data Integration and Feature Engineering
  • 1:10:04 Lesson 12: Dimensionality Reduction with PCA
  • 1:33:56 Lesson 13: Hands-on Preprocessing with Titanic and Iris Datasets

Data files: save the datasets from the lesson page in the same folder as this notebook. Files the lessons create are written to that folder too.

Data Mining 09: Data Cleaning- Handling Missing Values, Noise, and Outliers#

In this lesson you'll be able to:

  • Uncover missing values disguised as zeros, and measure them
  • Choose between dropping and filling, and see what each costs
  • Detect outliers with the IQR rule and z-scores, then cap them
  • Smooth noisy values by binning
  • Wrap every step into one reusable cleaning function Dataset: Pima Indians diabetes, 768 women (data/mining/pima_diabetes.csv)

Warm-up: explore before you clean#

Lesson 08 explored a dataset step by step. Start the same way here.

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
df = pd.read_csv("pima_diabetes.csv")
print(df.shape)
df.head()
(768, 9)
pregnancies glucose blood_pressure skin_thickness insulin bmi diabetes_pedigree age outcome
0 6 148 72 35 0 33.6 0.627 50 1
1 1 85 66 29 0 26.6 0.351 31 0
2 8 183 64 0 0 23.3 0.672 32 1
3 1 89 66 23 94 28.1 0.167 21 0
4 0 137 40 35 168 43.1 2.288 33 1
df.describe().round(1).loc[["mean", "min", "max"]]
pregnancies glucose blood_pressure skin_thickness insulin bmi diabetes_pedigree age outcome
mean 3.8 120.9 69.1 20.5 79.8 32.0 0.5 33.2 0.3
min 0.0 0.0 0.0 0.0 0.0 0.0 0.1 21.0 0.0
max 17.0 199.0 122.0 99.0 846.0 67.1 2.4 81.0 1.0

Find missing values, including disguised ones#

Problem: blanks are easy to count, but zeros can hide missing data too.

print(df.isna().sum().sum())
0

Quick check: can a living patient have a BMI or blood pressure of 0?

cols = ["glucose", "blood_pressure", "skin_thickness", "insulin",
        "bmi"]
(df[cols] == 0).sum()
glucose             5
blood_pressure     35
skin_thickness    227
insulin           374
bmi                11
dtype: int64

Zeros are real in pregnancies and outcome, so leave those columns alone.

df[cols] = df[cols].replace(0, np.nan)
missing = df.isna().sum()
missing[missing > 0]
glucose             5
blood_pressure     35
skin_thickness    227
insulin           374
bmi                11
dtype: int64
share = df.isna().mean().sort_values()
ax = share[share > 0].plot(kind="barh", figsize=(7, 3.5))
ax.set_title("Share of values missing per column")
ax.set_xlabel("Share missing")
ax.set_ylabel("Column")
print(round(df.isna().any(axis=1).mean(), 2))
0.49
No description has been provided for this image
import seaborn as sns
plt.figure(figsize=(7, 4))
sns.heatmap(df[cols].isna().head(80), cbar=False, cmap="Greys")
plt.title("Where the gaps are: first 80 patients")
plt.xlabel("Measurement")
plt.ylabel("Patient row")
plt.show()
No description has been provided for this image
gap = df["insulin"].isna()
gap.groupby(df["outcome"]).mean().round(3)
outcome
0    0.472
1    0.515
Name: insulin, dtype: float64

Your turn#

How many patients are missing BOTH skin_thickness and insulin?

both = df["skin_thickness"].isna() & df["insulin"].isna()
print(both.sum())
227

Drop or fill? Strategies for missing values#

Problem: every strategy has a cost. Measure it before you choose.

dropped = df.dropna()
print(len(df), len(dropped))
print(round(df["outcome"].mean(), 3),
      round(dropped["outcome"].mean(), 3))
768 392
0.349 0.332
thin = df.drop(columns=["insulin", "skin_thickness"]).dropna()
print(thin.shape)
(724, 7)
filled = df.copy()
medians = filled[cols].median()
filled[cols] = filled[cols].fillna(medians)
print(medians)
print(filled.isna().sum().sum())
glucose           117.0
blood_pressure     72.0
skin_thickness     29.0
insulin           125.0
bmi                32.3
dtype: float64
0
before = df["insulin"].std()
after = filled["insulin"].std()
print(round(before, 1), round(after, 1))
print((filled["insulin"] == medians["insulin"]).sum())
118.8 86.4
378
fig, axes = plt.subplots(1, 2, figsize=(10, 3.5), sharey=True)
df["insulin"].plot(kind="hist", bins=40, ax=axes[0])
filled["insulin"].plot(kind="hist", bins=40, ax=axes[1])
axes[0].set_title("Insulin, gaps left out")
axes[1].set_title("Insulin, gaps filled with the median")
for ax in axes:
    ax.set_xlabel("Insulin (mu U/ml)")
axes[0].set_ylabel("Patients")
No description has been provided for this image
age_band = pd.cut(df["age"], bins=[20, 30, 40, 50, 81],
                  labels=["21-30", "31-40", "41-50", "51+"])
by_age = df.groupby(age_band, observed=True)["blood_pressure"]
by_age.agg(["size", "count", "median"])
size count median
age
21-30 417 395 68.0
31-40 157 149 74.0
41-50 113 109 78.0
51+ 81 80 78.0
bp = df["blood_pressure"].fillna(by_age.transform("median"))
print(bp.isna().sum())
print(bp[df["blood_pressure"].isna()].value_counts())
0
blood_pressure
68.0    22
74.0     8
78.0     5
Name: count, dtype: int64

Common mistake: converting a column with gaps to whole numbers

df["glucose"].astype(int)
---------------------------------------------------------------------------
IntCastingNaNError                        Traceback (most recent call last)
Cell In[18], line 1
----> 1 df["glucose"].astype(int)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py:6529, in NDFrame.astype(self, dtype, copy, errors)
   6525     results = [ser.astype(dtype, errors=errors) for _, ser in self.items()]
   6527 else:
   6528     # else, only a single dtype is given
-> 6529     new_data = self._mgr.astype(dtype=dtype, errors=errors)
   6530     res = self._constructor_from_mgr(new_data, axes=new_data.axes)
   6531     return res.__finalize__(self, method="astype")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\managers.py:627, in BaseBlockManager.astype(self, dtype, errors)
    626 def astype(self, dtype, errors: str = "raise") -> Self:
--> 627     return self.apply("astype", dtype=dtype, errors=errors)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\managers.py:445, in BaseBlockManager.apply(self, f, align_keys, **kwargs)
    443         applied = b.apply(f, **kwargs)
    444     else:
--> 445         applied = getattr(b, f)(**kwargs)
    446     result_blocks = extend_blocks(applied, result_blocks)
    448 out = type(self).from_blocks(result_blocks, [ax.view() for ax in self.axes])

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\blocks.py:607, in Block.astype(self, dtype, errors, squeeze)
    604         raise ValueError("Can not squeeze with more than one column.")
    605     values = values[0, :]  # type: ignore[call-overload]
--> 607 new_values = astype_array_safe(values, dtype, errors=errors)
    609 new_values = maybe_coerce_values(new_values)
    611 refs = None

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\dtypes\astype.py:240, in astype_array_safe(values, dtype, copy, errors)
    237     dtype = dtype.numpy_dtype
    239 try:
--> 240     new_values = astype_array(values, dtype, copy=copy)
    241 except (ValueError, TypeError):
    242     # e.g. _astype_nansafe can fail on object-dtype of strings
    243     #  trying to convert to float
    244     if errors == "ignore":

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\dtypes\astype.py:185, in astype_array(values, dtype, copy)
    182     values = values.astype(dtype, copy=copy)
    184 else:
--> 185     values = _astype_nansafe(values, dtype, copy=copy)
    187 # in pandas we don't store numpy str dtypes, so convert to object
    188 if isinstance(dtype, np.dtype) and issubclass(values.dtype.type, str):

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\dtypes\astype.py:102, in _astype_nansafe(arr, dtype, copy, skipna)
     97     return lib.ensure_string_array(
     98         arr, skipna=skipna, convert_na_value=False
     99     ).reshape(shape)
    101 elif np.issubdtype(arr.dtype, np.floating) and dtype.kind in "iu":
--> 102     return _astype_float_to_int_nansafe(arr, dtype, copy)
    104 elif arr.dtype == object:
    105     # if we have a datetime/timedelta array of objects
    106     # then coerce to datetime64[ns] and use DatetimeArray.astype
    108     if lib.is_np_dtype(dtype, "M"):

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\dtypes\astype.py:146, in _astype_float_to_int_nansafe(values, dtype, copy)
    142 """
    143 astype with a check preventing converting NaN to a meaningless integer value.
    144 """
    145 if not np.isfinite(values).all():
--> 146     raise IntCastingNaNError(
    147         "Cannot convert non-finite values (NA or inf) to integer."
    148         "Replace or remove non-finite values or cast to an integer type"
    149         "that supports these values (e.g. 'Int64')"
    150     )
    151 if dtype.kind == "u":
    152     # GH#45151
    153     if not (values >= 0).all():

IntCastingNaNError: Cannot convert non-finite values (NA or inf) to integer.Replace or remove non-finite values or cast to an integer typethat supports these values (e.g. 'Int64')
glucose = df["glucose"].astype("Int64")
print(glucose.dtype, glucose.isna().sum())
glucose[glucose.isna()]
Int64 5
75     <NA>
182    <NA>
342    <NA>
349    <NA>
502    <NA>
Name: glucose, dtype: Int64
from sklearn.impute import KNNImputer
knn = KNNImputer(n_neighbors=5)
knn_vals = knn.fit_transform(df[cols])
knn_df = pd.DataFrame(knn_vals, columns=cols)
print(round(knn_df["insulin"].std(), 1))
98.5

Your turn#

Compare the BMI median of filled and knn_df. Which moved more?

print(df["bmi"].median(), filled["bmi"].median(),
      knn_df["bmi"].median())
32.3 32.3 32.3

Detect outliers with the IQR rule and z-scores#

Problem: a few extreme values can drag means and models off course.

ax = sns.boxplot(data=df[["insulin"]], orient="h")
ax.set_title("Measured insulin: box and outliers")
ax.set_xlabel("Insulin (mu U/ml)")
No description has been provided for this image

IQR rule: an outlier lies more than 1.5 IQR below Q1 or above Q3.

ins = df["insulin"]
q1, q3 = ins.quantile([0.25, 0.75])
iqr = q3 - q1
low, high = q1 - 1.5 * iqr, q3 + 1.5 * iqr
print(q1, q3, iqr)
print(low, high)
76.25 190.0 113.75
-94.375 360.625

Common mistake: using or between two conditions

outliers = df[ins < low or ins > high]
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_27876\720841507.py in ?()
----> 1 outliers = df[ins < low or ins > high]

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py in ?(self)
   1499     @final
   1500     def __bool__(self) -> NoReturn:
-> 1501         raise ValueError(
   1502             f"The truth value of a {type(self).__name__} is ambiguous. "
   1503             "Use a.empty, a.bool(), a.item(), a.any() or a.all()."
   1504         )

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().
outliers = df[(ins < low) | (ins > high)]
print(len(outliers))
outliers["insulin"].describe().round(1)
24
count     24.0
mean     509.2
std      116.5
min      370.0
25%      433.8
50%      482.5
75%      543.5
max      846.0
Name: insulin, dtype: float64
z = (ins - ins.mean()) / ins.std()
print((z.abs() > 3).sum())
print(df.loc[z.abs() > 3, "insulin"].min())
8
540.0

Quick check: which rule flags more outliers here, and why?

print(round(ins.skew(), 2))
print(round(ins.mean(), 1), round(ins.median(), 1))
2.17
155.5 125.0

Your turn#

Use the IQR rule to count BMI outliers. Print the fences too.

b1, b3 = df["bmi"].quantile([0.25, 0.75])
b_low, b_high = b1 - 1.5 * (b3 - b1), b3 + 1.5 * (b3 - b1)
print(round(b_low, 2), round(b_high, 2))
print(((df["bmi"] < b_low) | (df["bmi"] > b_high)).sum())
13.85 50.25
8
df["insulin_cap"] = ins.clip(upper=high)
df[["insulin", "insulin_cap"]].describe().round(1)
insulin insulin_cap
count 394.0 394.0
mean 155.5 146.5
std 118.8 90.3
min 14.0 14.0
25% 76.2 76.2
50% 125.0 125.0
75% 190.0 190.0
max 846.0 360.6
f_q1, f_q3 = filled["insulin"].quantile([0.25, 0.75])
print(f_q3 - f_q1, q3 - q1)
5.75 113.75

Smooth noise by binning#

Problem: tiny random wobbles in measurements hide the real pattern.

Smoothing by bin means:

  1. Sort values into equal-frequency bins
  2. Replace each value with its bin's mean

Common mistake: a label count that doesn't match the bins

pd.qcut(filled["bmi"], q=4, labels=["low", "mid", "high"])
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[31], line 1
----> 1 pd.qcut(filled["bmi"], q=4, labels=["low", "mid", "high"])

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\tile.py:384, in qcut(x, q, labels, retbins, precision, duplicates)
    380     quantiles = q
    382 bins = x_idx.to_series().dropna().quantile(quantiles)
--> 384 fac, bins = _bins_to_cuts(
    385     x_idx,
    386     Index(bins),
    387     labels=labels,
    388     precision=precision,
    389     include_lowest=True,
    390     duplicates=duplicates,
    391 )
    393 return _postprocess_for_cut(fac, bins, retbins, original)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\tile.py:536, in _bins_to_cuts(x_idx, bins, right, labels, precision, include_lowest, duplicates, ordered)
    531     raise ValueError(
    532         "labels must be unique if ordered=True; pass ordered=False "
    533         "for duplicate labels"
    534     )
    535 elif len(labels) != len(bins) - 1:
--> 536     raise ValueError(
    537         "Bin labels must be one fewer than the number of bin edges"
    538     )
    540 if not isinstance(getattr(labels, "dtype", None), CategoricalDtype):
    541     labels = Categorical(
    542         labels,
    543         categories=labels if len(set(labels)) == len(labels) else None,
    544         ordered=ordered,
    545     )

ValueError: Bin labels must be one fewer than the number of bin edges
names = ["low", "mid", "high", "very high"]
filled["bmi_bin"] = pd.qcut(filled["bmi"], q=4, labels=names)
filled["bmi_bin"].value_counts(sort=False)
bmi_bin
low          193
mid          198
high         189
very high    188
Name: count, dtype: int64
by_bin = filled.groupby("bmi_bin", observed=True)["bmi"]
filled["bmi_smooth"] = by_bin.transform("mean").round(1)
filled[["bmi", "bmi_bin", "bmi_smooth"]].head()
bmi bmi_bin bmi_smooth
0 33.6 high 34.3
1 26.6 low 24.2
2 23.3 low 24.2
3 28.1 mid 30.0
4 43.1 very high 41.6
rate = filled.groupby("bmi_bin", observed=True)["outcome"].mean()
rate.round(3)
bmi_bin
low          0.114
mid          0.354
high         0.450
very high    0.484
Name: outcome, dtype: float64

Your turn#

Make 4 equal-WIDTH bins of bmi with pd.cut. How full is each bin?

wide = pd.cut(filled["bmi"], bins=4)
wide.value_counts(sort=False)
bmi
(18.151, 30.425]    310
(30.425, 42.65]     394
(42.65, 54.875]      60
(54.875, 67.1]        4
Name: count, dtype: int64

Mini-project: does cleaning change the story?#

Build one cleaning function, save the clean file, and compare raw vs clean.

def clean(raw):
    out = raw.copy()
    out[cols] = out[cols].replace(0, np.nan)
    q1, q3 = out["insulin"].quantile([0.25, 0.75])
    cap = q3 + 1.5 * (q3 - q1)
    out[cols] = out[cols].fillna(out[cols].median())
    out["insulin"] = out["insulin"].clip(upper=cap)
    return out
raw = pd.read_csv("pima_diabetes.csv")
tidy = clean(raw)
print(tidy.isna().sum().sum(), (tidy[cols] == 0).sum().sum())
0 0
import os
tidy.to_csv("m09_pima_clean.csv", index=False)
back = pd.read_csv("m09_pima_clean.csv")
print(back.shape)
(768, 9)
show = ["glucose", "blood_pressure", "insulin", "bmi"]
raw_means = raw.groupby("outcome")[show].mean()
clean_means = back.groupby("outcome")[show].mean()
pd.concat({"raw": raw_means, "clean": clean_means}).round(1)
glucose blood_pressure insulin bmi
outcome
raw 0 110.0 68.2 68.8 30.3
1 141.3 70.8 100.3 35.1
clean 0 110.7 70.9 125.1 30.9
1 142.1 75.1 156.4 35.4
raw_gap = raw_means.loc[1] - raw_means.loc[0]
clean_gap = clean_means.loc[1] - clean_means.loc[0]
pd.DataFrame({"raw_gap": raw_gap, "clean_gap": clean_gap}).round(1)
raw_gap clean_gap
glucose 31.3 31.4
blood_pressure 2.6 4.2
insulin 31.5 31.3
bmi 4.8 4.5

Recap#

  • isna() only sees blanks; check for impossible zeros too
  • Dropping rows is simple but can throw away half the data
  • Median filling keeps rows but shrinks spread; KNN fills smarter
  • IQR fences and z-scores flag outliers; clip caps them
  • Binning by bin means smooths noise; wrap it all in one function

Challenges#

  1. Count blood_pressure outliers with the IQR rule, then cap them.
  2. Compare KNN and median filling for skin_thickness spread.
  3. Smooth glucose by bin means with 5 equal-frequency bins.

What's next#

Lesson 10: Data Transformation- Normalization, Encoding, Feature Scaling Put clean columns on the same scale and turn text into numbers.

Data Mining 10: Data Transformation: Normalization, Encoding, Feature Scaling#

In this lesson you'll be able to:

  • Explain why features on different scales mislead a model
  • Rescale columns with min-max, standard and robust scalers
  • Tame skewed columns with a log transform
  • Encode categories with one-hot and ordinal encoders
  • Fit transformations on training data only, inside a pipeline Datasets: California housing districts (data/pandas/housing.csv) and diamonds (data/pandas/diamonds.csv)

Warm-up: clean before you transform#

Lesson 09 handled missing values. One column here still has gaps.

import pandas as pd
import numpy as np
df = pd.read_csv("housing.csv")
print(df.shape)
df.isna().sum()
(20640, 10)
longitude               0
latitude                0
housing_median_age      0
total_rooms             0
total_bedrooms        207
population              0
households              0
median_income           0
median_house_value      0
ocean_proximity         0
dtype: int64
fill = df["total_bedrooms"].median()
df["total_bedrooms"] = df["total_bedrooms"].fillna(fill)
print(fill, df["total_bedrooms"].isna().sum())
435.0 0

Spot the scale problem#

Problem: some columns are in the thousands, others are tiny.

cols = ["housing_median_age", "total_rooms", "population",
        "median_income", "median_house_value"]
df[cols].describe().loc[["min", "max", "mean"]].round(1)
housing_median_age total_rooms population median_income median_house_value
min 1.0 2.0 3.0 0.5 14999.0
max 52.0 39320.0 35682.0 15.0 500001.0
mean 28.6 2635.8 1425.5 3.9 206855.8

Quick check: two districts differ by 1 in income and 100 in rooms. Which gap matters more to a distance?

feats = ["total_rooms", "median_income"]
a, b = df.loc[0, feats], df.loc[3, feats]
gaps = (a - b) ** 2
print(gaps.round(4))
print("share from rooms:", round(gaps.iloc[0] / gaps.sum(), 4))
total_rooms      155236.0000
median_income         7.1937
dtype: float64
share from rooms: 1.0

Normalise to the 0-1 range#

Min-max scaling: (x - min) / (max - min). Every value lands between 0 and 1.

rooms = df["total_rooms"]
rooms_mm = (rooms - rooms.min()) / (rooms.max() - rooms.min())
print(rooms.head(3).to_list())
print(rooms_mm.head(3).round(4).to_list())
print(rooms_mm.min(), rooms_mm.max())
[880.0, 7099.0, 1467.0]
[0.0223, 0.1805, 0.0373]
0.0 1.0

Common mistake: passing a single column (a Series) to a scikit-learn scaler.

from sklearn.preprocessing import MinMaxScaler
mm = MinMaxScaler()
mm.fit_transform(df["total_rooms"])
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[7], line 3
      1 from sklearn.preprocessing import MinMaxScaler
      2 mm = MinMaxScaler()
----> 3 mm.fit_transform(df["total_rooms"])

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\_set_output.py:327, in _wrap_method_output.<locals>.wrapped(self, X, *args, **kwargs)
    325 @wraps(f)
    326 def wrapped(self, X, *args, **kwargs):
--> 327     data_to_wrap = f(self, X, *args, **kwargs)
    328     if isinstance(data_to_wrap, tuple):
    329         # only wrap the first output for cross decomposition
    330         return_tuple = (
    331             _wrap_data_with_container(method, data_to_wrap[0], X, self),
    332             *data_to_wrap[1:],
    333         )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\base.py:969, in TransformerMixin.fit_transform(self, X, y, **fit_params)
    954         warnings.warn(
    955             (
    956                 f"This object ({self.__class__.__name__}) has a `transform`"
   (...)
    964             UserWarning,
    965         )
    967 if y is None:
    968     # fit method of arity 1 (unsupervised transformation)
--> 969     return self.fit(X, **fit_params).transform(X)
    970 else:
    971     # fit method of arity 2 (supervised transformation)
    972     return self.fit(X, y, **fit_params).transform(X)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\preprocessing\_data.py:473, in MinMaxScaler.fit(self, X, y)
    471 # Reset internal state before fitting
    472 self._reset()
--> 473 return self.partial_fit(X, y)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\base.py:1403, in _fit_context.<locals>.decorator.<locals>.wrapper(estimator, *args, **kwargs)
   1393     estimator._validate_params()
   1395 with (
   1396     config_context(
   1397         skip_parameter_validation=(
   (...)
   1401     callback_management_context(estimator),
   1402 ):
-> 1403     return fit_method(estimator, *args, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\preprocessing\_data.py:513, in MinMaxScaler.partial_fit(self, X, y)
    510 xp, _ = get_namespace(X)
    512 first_pass = not hasattr(self, "n_samples_seen_")
--> 513 X = validate_data(
    514     self,
    515     X,
    516     reset=first_pass,
    517     dtype=_array_api.supported_float_dtypes(xp, device=device(X)),
    518     ensure_all_finite="allow-nan",
    519 )
    521 device_ = device(X)
    522 feature_range = (
    523     xp.asarray(feature_range[0], dtype=X.dtype, device=device_),
    524     xp.asarray(feature_range[1], dtype=X.dtype, device=device_),
    525 )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\validation.py:3041, in validate_data(_estimator, X, y, reset, validate_separately, skip_check_array, **check_params)
   3039         out = X, y
   3040 elif not no_val_X and no_val_y:
-> 3041     out = check_array(X, input_name="X", **check_params)
   3042 elif no_val_X and not no_val_y:
   3043     out = _check_y(y, **check_params)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\validation.py:1076, in check_array(array, accept_sparse, accept_large_sparse, dtype, order, copy, force_writeable, ensure_all_finite, ensure_non_negative, ensure_2d, allow_nd, ensure_min_samples, ensure_min_features, estimator, input_name)
   1069         else:
   1070             msg = (
   1071                 f"Expected 2D array, got 1D array instead:\narray={array}.\n"
   1072                 "Reshape your data either using array.reshape(-1, 1) if "
   1073                 "your data has a single feature or array.reshape(1, -1) "
   1074                 "if it contains a single sample."
   1075             )
-> 1076         raise ValueError(msg)
   1078 if dtype_numeric and hasattr(array.dtype, "kind") and array.dtype.kind in "USV":
   1079     raise ValueError(
   1080         "dtype='numeric' is not compatible with arrays of bytes/strings."
   1081         "Convert your data to numeric values explicitly instead."
   1082     )

ValueError: Expected a 2-dimensional container but got <class 'pandas.Series'> instead. Pass a DataFrame containing a single row (i.e. single sample) or a single column (i.e. single feature) instead.
scaled = mm.fit_transform(df[["total_rooms"]])
print(scaled[:3].ravel().round(4))
print(mm.data_min_, mm.data_max_)
[0.0223 0.1805 0.0373]
[2.] [39320.]
mm_all = MinMaxScaler().fit_transform(df[feats])
a2, b2 = mm_all[0], mm_all[3]
gaps2 = (a2 - b2) ** 2
print("share from rooms:", round(gaps2[0] / gaps2.sum(), 4))
share from rooms: 0.0029

Standardise, resist outliers, tame skew#

Standard scaling: (x - mean) / std. Robust scaling uses the median and IQR.

pop = df["population"]
print("max:", pop.max(), "median:", pop.median())
pop_mm = (pop - pop.min()) / (pop.max() - pop.min())
print("median after min-max:", round(pop_mm.median(), 4))
max: 35682.0 median: 1166.0
median after min-max: 0.0326
from sklearn.preprocessing import StandardScaler, RobustScaler
std = StandardScaler().fit_transform(df[["population"]])
rob = RobustScaler().fit_transform(df[["population"]])
print("standard mean, std:", std.mean().round(3), std.std().round(3))
print("standard median:", np.median(std).round(3))
print("robust median:", np.median(rob).round(3))
standard mean, std: -0.0 1.0
standard median: -0.229
robust median: 0.0
comp = pd.DataFrame({"min-max": pop_mm.to_numpy(),
                     "standard": std.ravel(),
                     "robust": rob.ravel()})
comp.describe().loc[["min", "50%", "max"]].round(2)
min-max standard robust
min 0.00 -1.26 -1.24
50% 0.03 -0.23 0.00
max 1.00 30.25 36.80

Your turn: standardise median_income by hand with the mean and std, then check the first value against StandardScaler.

inc = df["median_income"]
z = (inc - inc.mean()) / inc.std(ddof=0)
z_sk = StandardScaler().fit_transform(df[["median_income"]])
print(round(z.iloc[0], 4), z_sk[0, 0].round(4))
2.3448 2.3448

A long right tail hides most districts in one bar. log1p pulls the tail in.

print("skew before:", round(pop.skew(), 2))
log_pop = np.log1p(pop)
print("skew after:", round(log_pop.skew(), 2))
skew before: 4.94
skew after: -1.04
import matplotlib.pyplot as plt
fig, axes = plt.subplots(1, 2, figsize=(10, 4))
axes[0].hist(pop, bins=50)
axes[0].set_title("Population (raw)")
axes[0].set_xlabel("People in district")
axes[1].hist(log_pop, bins=50)
axes[1].set_title("Population (log1p)")
axes[1].set_xlabel("log(1 + people)")
axes[0].set_ylabel("Number of districts")
plt.show()
No description has been provided for this image

Encode categories as numbers#

Models need numbers. One-hot gives each category its own 0/1 column.

df["ocean_proximity"].value_counts()
ocean_proximity
<1H OCEAN     9136
INLAND        6551
NEAR OCEAN    2658
NEAR BAY      2290
ISLAND           5
Name: count, dtype: int64
dummies = pd.get_dummies(df["ocean_proximity"], dtype=int)
print(dummies.shape)
dummies.head(3)
(20640, 5)
<1H OCEAN INLAND ISLAND NEAR BAY NEAR OCEAN
0 0 0 0 1 0
1 0 0 0 1 0
2 0 0 0 1 0

Your turn: add the dummies to df with the prefix ocean, and count the columns.

ocean = pd.get_dummies(df["ocean_proximity"], prefix="ocean",
                       dtype=int)
wide = df.join(ocean)
print(df.shape[1], wide.shape[1])
print(wide.columns[-5:].to_list())
10 15
['ocean_<1H OCEAN', 'ocean_INLAND', 'ocean_ISLAND', 'ocean_NEAR BAY', 'ocean_NEAR OCEAN']

Common mistake: fitting an encoder on data that is missing a rare category, then meeting that category later.

from sklearn.preprocessing import OneHotEncoder
island = df["ocean_proximity"] == "ISLAND"
seen = df.loc[~island, ["ocean_proximity"]]
ohe = OneHotEncoder().fit(seen)
ohe.transform(df.loc[island, ["ocean_proximity"]])
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[19], line 5
      3 seen = df.loc[~island, ["ocean_proximity"]]
      4 ohe = OneHotEncoder().fit(seen)
----> 5 ohe.transform(df.loc[island, ["ocean_proximity"]])

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\_set_output.py:327, in _wrap_method_output.<locals>.wrapped(self, X, *args, **kwargs)
    325 @wraps(f)
    326 def wrapped(self, X, *args, **kwargs):
--> 327     data_to_wrap = f(self, X, *args, **kwargs)
    328     if isinstance(data_to_wrap, tuple):
    329         # only wrap the first output for cross decomposition
    330         return_tuple = (
    331             _wrap_data_with_container(method, data_to_wrap[0], X, self),
    332             *data_to_wrap[1:],
    333         )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\preprocessing\_encoders.py:1051, in OneHotEncoder.transform(self, X)
   1046     warn_on_unknown = self.drop is not None and self.handle_unknown in {
   1047         "ignore",
   1048         "infrequent_if_exist",
   1049     }
   1050     handle_unknown = self.handle_unknown
-> 1051 X_int, X_mask = self._transform(
   1052     X,
   1053     handle_unknown=handle_unknown,
   1054     ensure_all_finite="allow-nan",
   1055     warn_on_unknown=warn_on_unknown,
   1056 )
   1058 n_samples, n_features = X_int.shape
   1060 if self._drop_idx_after_grouping is not None:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\preprocessing\_encoders.py:221, in _BaseEncoder._transform(self, X, handle_unknown, ensure_all_finite, warn_on_unknown, ignore_category_indices)
    216 if handle_unknown == "error":
    217     msg = (
    218         "Found unknown categories {0} in column {1}"
    219         " during transform".format(diff, i)
    220     )
--> 221     raise ValueError(msg)
    222 else:
    223     if warn_on_unknown:

ValueError: Found unknown categories ['ISLAND'] in column 0 during transform
ohe = OneHotEncoder(handle_unknown="ignore", sparse_output=False)
ohe.fit(seen)
new = ohe.transform(df.loc[island, ["ocean_proximity"]])
print(ohe.get_feature_names_out())
print(new[:2])
['ocean_proximity_<1H OCEAN' 'ocean_proximity_INLAND'
 'ocean_proximity_NEAR BAY' 'ocean_proximity_NEAR OCEAN']
[[0. 0. 0. 0.]
 [0. 0. 0. 0.]]

Keep the order with ordinal encoding#

Some categories have a natural order. Encode that order explicitly.

gems = pd.read_csv("diamonds.csv")
gems[["carat", "cut", "price"]].head(3)
carat cut price
0 0.23 Ideal 326
1 0.21 Premium 326
2 0.23 Good 327

Quick check: if we sort the cut names alphabetically and number them, where does Ideal land?

alpha = sorted(gems["cut"].unique())
print(dict(zip(alpha, range(5))))
{'Fair': 0, 'Good': 1, 'Ideal': 2, 'Premium': 3, 'Very Good': 4}
from sklearn.preprocessing import OrdinalEncoder
order = ["Fair", "Good", "Very Good", "Premium", "Ideal"]
oe_cut = OrdinalEncoder(categories=[order])
gems["cut_code"] = oe_cut.fit_transform(gems[["cut"]]).ravel()
gems.groupby("cut_code")["cut"].first()
cut_code
0.0         Fair
1.0         Good
2.0    Very Good
3.0      Premium
4.0        Ideal
Name: cut, dtype: str
by_cut = gems.groupby("cut_code")[["carat", "price"]].mean()
by_cut.round(2)
carat price
cut_code
0.0 1.05 4358.76
1.0 0.85 3928.86
2.0 0.81 3981.76
3.0 0.89 4584.26
4.0 0.70 3457.54

Fit on training data only#

Scalers learn from data. Let them learn from the training rows alone.

Quick check: if the scaler is fitted on training rows, will the scaled test column have a mean of exactly 0?

from sklearn.model_selection import train_test_split
train, test = train_test_split(df, test_size=0.2, random_state=42)
sc = StandardScaler().fit(train[["median_income"]])
test_z = sc.transform(test[["median_income"]])
print(sc.mean_.round(3), test_z.mean().round(3))
[3.881] -0.026
from sklearn.compose import ColumnTransformer
num_cols = ["housing_median_age", "total_rooms", "total_bedrooms",
            "population", "households", "median_income"]
prep = ColumnTransformer([
    ("num", StandardScaler(), num_cols),
    ("cat", OneHotEncoder(handle_unknown="ignore"),
     ["ocean_proximity"]),
])
X_train = prep.fit_transform(train)
print(X_train.shape)
print(prep.get_feature_names_out()[[0, 6, 10]])
(16512, 11)
['num__housing_median_age' 'cat__ocean_proximity_<1H OCEAN'
 'cat__ocean_proximity_NEAR OCEAN']

Your turn: transform the test rows with prep and check the shape.

X_test = prep.transform(test)
print(X_test.shape)
print(X_test[:, :6].mean(axis=0).round(2))
(4128, 11)
[ 0.01 -0.01 -0.02 -0.   -0.01 -0.03]

Mini-project: does scaling change a model?#

Predict house value with k-nearest neighbours, with and without scaling.

from sklearn.neighbors import KNeighborsRegressor
from sklearn.pipeline import make_pipeline
from sklearn.metrics import mean_absolute_error
feats = num_cols + ["longitude", "latitude"]
target = "median_house_value"
raw = KNeighborsRegressor().fit(train[feats], train[target])
pred_raw = raw.predict(test[feats])
mae_raw = mean_absolute_error(test[target], pred_raw)
print(f"raw MAE: ${mae_raw:,.0f}")
raw MAE: $78,209
knn = make_pipeline(StandardScaler(), KNeighborsRegressor())
knn.fit(train[feats], train[target])
pred_sc = knn.predict(test[feats])
mae_sc = mean_absolute_error(test[target], pred_sc)
print(f"scaled MAE: ${mae_sc:,.0f}")
scaled MAE: $41,992
one = test.iloc[[0]]
print("actual:", one[target].iloc[0])
print("raw kNN:", raw.predict(one[feats])[0])
print("scaled kNN:", knn.predict(one[feats])[0])
actual: 47700.0
raw kNN: 175300.0
scaled kNN: 50680.0
from sklearn.metrics import r2_score
r2_raw = r2_score(test[target], pred_raw)
r2_sc = r2_score(test[target], pred_sc)
print(round(r2_raw, 3), round(r2_sc, 3))
0.226 0.705
maes = pd.Series({"Raw": mae_raw, "Scaled": mae_sc})
ax = maes.plot(kind="bar", rot=0, figsize=(6, 4))
ax.set_title("kNN error on test districts")
ax.set_xlabel("Features")
ax.set_ylabel("Mean absolute error ($)")
print(f"error cut by {1 - mae_sc / mae_raw:.0%}")
error cut by 46%
No description has been provided for this image

Recap#

  • Distance-based models are dominated by large-scale columns
  • Min-max maps to 0-1; standard gives mean 0, std 1; robust resists outliers
  • log1p pulls in long right tails
  • One-hot for unordered categories, OrdinalEncoder with an explicit order
  • Fit on training rows only: use ColumnTransformer and pipelines

Challenges#

  1. Swap StandardScaler for MinMaxScaler in the kNN pipeline. Which wins?
  2. Add log1p of population as a feature and re-measure the error.
  3. Ordinal-encode diamond clarity (I1 worst, IF best).

What's next#

Lesson 11: Data Integration and Feature Engineering Combine data from several sources and build new, more useful features.

Data Mining 11: Data Integration and Feature Engineering#

In this lesson you'll be able to:

  • Stack paged API responses into one dataset
  • Flatten nested JSON records into a table
  • Match schemas and merge in a lookup table safely
  • Engineer date, ratio, band and group features
  • Package features in a function and reuse it on new data Data: Superstore orders as a saved API response (data/pandas/superstore_api_page1-3.json) and as a CSV export (data/pandas/superstore.csv)

Warm-up: encode a category#

Lesson 10 turned categories into numbers. Here it is on the store data.

import pandas as pd
shop = pd.read_csv("superstore.csv")
print(shop.shape)
pd.get_dummies(shop["Ship Mode"], dtype=int).sum()
(8399, 21)
Delivery Truck    1146
Express Air        983
Regular Air       6270
dtype: int64
money = shop[["Sales", "Shipping Cost", "Profit"]]
money.describe().loc[["min", "50%", "max"]].round(2)
Sales Shipping Cost Profit
min 2.24 0.49 -14140.70
50% 449.42 6.07 -1.50
max 89061.05 164.73 27220.69

Stack the pages of an API response#

Problem: an API sends 100 orders per page. We need all of them in one place.

import json
with open("superstore_api_page1.json") as f:
    page = json.load(f)
print(list(page.keys()))
print(page["total_pages"], page["total_orders"], page["next"])
['page', 'per_page', 'total_pages', 'total_orders', 'next', 'results']
3 300 superstore_api_page2.json

Quick check: three pages of up to 100 orders. How many orders should we end up with?

orders = []
for i in range(1, 4):
    name = f"superstore_api_page{i}.json"
    with open(name) as f:
        orders += json.load(f)["results"]
print(len(orders))
300
ids = [o["order_id"] for o in orders]
print(len(ids), len(set(ids)))
300 300
first = orders[0]
print(list(first.keys()))
print(first["customer"])
print(len(first["items"]), "item(s)")
['order_id', 'order_date', 'priority', 'customer', 'shipping', 'items']
{'name': 'Matt Collister', 'segment': 'Home Office', 'location': {'province': 'Quebec', 'region': 'Quebec'}}
1 item(s)

Flatten nested records into a table#

json_normalize turns nested dictionaries into columns, and lists into rows.

flat = pd.json_normalize(orders)
print(flat.shape)
print(flat.columns.to_list())
(300, 11)
['order_id', 'order_date', 'priority', 'items', 'customer.name', 'customer.segment', 'customer.location.province', 'customer.location.region', 'shipping.mode', 'shipping.ship_date', 'shipping.cost']

Common mistake: passing a nested field to meta as a dotted string.

items = pd.json_normalize(orders, record_path="items",
                          meta=["order_id", "customer.name"])
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\json\_normalize.py:485, in json_normalize.<locals>._pull_field(js, spec, extract_record)
    484             raise KeyError(field)
--> 485         result = result[field]
    486 else:

KeyError: 'customer.name'

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
Cell In[9], line 1
----> 1 items = pd.json_normalize(orders, record_path="items",
      2                           meta=["order_id", "customer.name"])

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\json\_normalize.py:625, in json_normalize(data, record_path, meta, meta_prefix, record_prefix, errors, sep, max_level)
    622                 meta_vals[key].append(meta_val)
    623             records.extend(recs)
--> 625 _recursive_extract(data, record_path, {}, level=0)
    627 result = DataFrame(records)
    629 if record_prefix is not None:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\json\_normalize.py:621, in json_normalize.<locals>._recursive_extract(data, path, seen_meta, level)
    619         meta_val = seen_meta[key]
    620     else:
--> 621         meta_val = _pull_field(obj, val[level:])
    622     meta_vals[key].append(meta_val)
    623 records.extend(recs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\json\_normalize.py:497, in json_normalize.<locals>._pull_field(js, spec, extract_record)
    495         return np.nan
    496     else:
--> 497         raise KeyError(
    498             f"Key {e} not found. To replace missing values of {e} with "
    499             f"np.nan, pass in errors='ignore'"
    500         ) from e
    502 return result

KeyError: "Key 'customer.name' not found. To replace missing values of 'customer.name' with np.nan, pass in errors='ignore'"
meta = ["order_id", "order_date", ["customer", "name"],
        ["customer", "segment"], ["shipping", "mode"],
        ["shipping", "ship_date"]]
items = pd.json_normalize(orders, record_path="items", meta=meta)
print(items.shape)
items.iloc[0]
(477, 14)
product               Safco Industrial Wire Shelving
category                             Office Supplies
sub_category                  Storage & Organization
quantity                                           9
unit_price                                     95.99
discount                                        0.08
sales                                         872.48
profit                                       -342.91
order_id                                       13729
order_date                                  1/1/2009
customer.name                         Matt Collister
customer.segment                         Home Office
shipping.mode                            Express Air
shipping.ship_date                          1/3/2009
Name: 0, dtype: object

Quick check: 300 orders became 477 item rows. Why?

per_order = items["order_id"].value_counts()
per_order.value_counts().sort_index()
count
1    176
2     84
3     29
4      9
5      2
Name: count, dtype: int64

Match schemas and merge a lookup table#

The API lacks each product's container and base margin. The CSV export has them.

schema = {"customer.name": "customer",
          "customer.segment": "segment",
          "shipping.mode": "ship_mode",
          "shipping.ship_date": "ship_date"}
items = items.rename(columns=schema)
print(items.columns.to_list())
['product', 'category', 'sub_category', 'quantity', 'unit_price', 'discount', 'sales', 'profit', 'order_id', 'order_date', 'customer', 'segment', 'ship_mode', 'ship_date']
cols = ["Product Name", "Product Container", "Product Base Margin"]
catalog = shop[cols].drop_duplicates()
catalog.columns = ["product", "container", "base_margin"]
print(len(catalog), catalog["product"].nunique())
1289 1263
in_csv = items["order_id"].isin(shop["Order ID"])
print(in_csv.mean())
1.0

Common mistake: merging without checking that the lookup keys are unique.

items.merge(catalog, on="product", how="left",
            validate="many_to_one")
---------------------------------------------------------------------------
MergeError                                Traceback (most recent call last)
Cell In[15], line 1
----> 1 items.merge(catalog, on="product", how="left",
      2             validate="many_to_one")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:12902, in DataFrame.merge(self, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
  12898 self._check_copy_deprecation(copy)
  12900 from pandas.core.reshape.merge import merge
> 12902 return merge(
  12903     self,
  12904     right,
  12905     how=how,
  12906     on=on,
  12907     left_on=left_on,
  12908     right_on=right_on,
  12909     left_index=left_index,
  12910     right_index=right_index,
  12911     sort=sort,
  12912     suffixes=suffixes,
  12913     indicator=indicator,
  12914     validate=validate,
  12915 )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\merge.py:385, in merge(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
    371     return _cross_merge(
    372         left_df,
    373         right_df,
   (...)
    382         validate=validate,
    383     )
    384 else:
--> 385     op = _MergeOperation(
    386         left_df,
    387         right_df,
    388         how=how,
    389         on=on,
    390         left_on=left_on,
    391         right_on=right_on,
    392         left_index=left_index,
    393         right_index=right_index,
    394         sort=sort,
    395         suffixes=suffixes,
    396         indicator=indicator,
    397         validate=validate,
    398     )
    399     return op.get_result()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\merge.py:1037, in _MergeOperation.__init__(self, left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, indicator, validate)
   1033 # If argument passed to validate,
   1034 # check if columns specified as unique
   1035 # are in fact unique.
   1036 if validate is not None:
-> 1037     self._validate_validate_kwd(validate)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\merge.py:2019, in _MergeOperation._validate_validate_kwd(self, validate)
   2017 elif validate in ["many_to_one", "m:1"]:
   2018     if not right_unique:
-> 2019         raise MergeError(
   2020             "Merge keys are not unique in right dataset; "
   2021             "not a many-to-one merge\n"
   2022             f"{right_error_msg(right_join_index)}"
   2023         )
   2025 elif validate in ["many_to_many", "m:m"]:
   2026     pass

MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

Duplicates in right:
                                       product
                     Prang Drawing Pencil Set
    Fellowes Basic 104-Key Keyboard, Platinum
Bretford CR4500 Series Slim Rectangular Table
     Global Adaptabilitiesª Conference Tables
                  Microsoft Internet Keyboard ...
dupes = catalog[catalog["product"].duplicated(keep=False)]
dupes.sort_values("product").head(4)
product container base_margin
981 Adesso Programmable 142-Key Keyboard Small Box 0.79
2183 Adesso Programmable 142-Key Keyboard Small Box 0.74
471 Bevis Round Bullnose 29" High Table Top Jumbo Box 0.61
2472 Bevis Round Bullnose 29" High Table Top Jumbo Box 0.65
catalog = catalog.drop_duplicates("product")
enriched = items.merge(catalog, on="product", how="left",
                       validate="many_to_one", indicator=True)
print(len(items), len(enriched))
enriched["_merge"].value_counts()
477 477
_merge
both          397
left_only      80
right_only      0
Name: count, dtype: int64
import os
path = "m11_items.csv"
enriched.drop(columns="_merge").to_csv(path, index=False)
items = pd.read_csv(path)
print(items.shape)
(477, 16)

Your turn: bring the order-level shipping cost from flat onto each item, and check the row count.

costs = flat[["order_id", "shipping.cost"]]
costs = costs.rename(columns={"shipping.cost": "order_ship_cost"})
items = items.merge(costs, on="order_id", how="left",
                    validate="many_to_one")
print(items.shape, items["order_ship_cost"].isna().sum())
(477, 17) 0

Engineer features from dates#

A date hides several useful features: gaps, months, weekdays.

Common mistake: doing date maths on dates stored as text.

items["ship_date"] - items["order_date"]
---------------------------------------------------------------------------
ArrowNotImplementedError                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\arrow\array.py:1097, in ArrowExtensionArray._evaluate_op_method(self, other, op, arrow_funcs)
   1096 try:
-> 1097     result = pc_func(self._pa_array, other)
   1098 except pa.ArrowNotImplementedError as err:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pyarrow\compute.py:255, in _make_generic_wrapper.<locals>.wrapper(memory_pool, *args)
    254     return Expression._call(func_name, list(args))
--> 255 return func.call(args, None, memory_pool)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pyarrow\_compute.pyx:405, in pyarrow._compute.Function.call()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pyarrow\error.pxi:155, in pyarrow.lib.pyarrow_internal_check_status()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pyarrow\error.pxi:92, in pyarrow.lib.check_status()

ArrowNotImplementedError: Function 'subtract_checked' has no kernel matching input types (large_string, large_string)

The above exception was the direct cause of the following exception:

TypeError                                 Traceback (most recent call last)
Cell In[20], line 1
----> 1 items["ship_date"] - items["order_date"]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\common.py:85, in _unpack_zerodim_and_defer.<locals>.new_method(self, other)
     82     other = sanitize_array(other, None)
     83     other = ensure_wrapped_if_datetimelike(other)
---> 85 return method(self, other)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arraylike.py:198, in OpsMixin.__sub__(self, other)
    196 @unpack_zerodim_and_defer("__sub__")
    197 def __sub__(self, other):
--> 198     return self._arith_method(other, operator.sub)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\series.py:6751, in Series._arith_method(self, other, op)
   6749 def _arith_method(self, other, op):
   6750     self, other = self._align_for_op(other)
-> 6751     return base.IndexOpsMixin._arith_method(self, other, op)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\base.py:1644, in IndexOpsMixin._arith_method(self, other, op)
   1641     rvalues = np.arange(rvalues.start, rvalues.stop, rvalues.step)
   1643 with np.errstate(all="ignore"):
-> 1644     result = ops.arithmetic_op(lvalues, rvalues, op)
   1646 return self._construct_result(result, name=res_name, other=other)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\array_ops.py:279, in arithmetic_op(left, right, op)
    270 right = ensure_wrapped_if_datetimelike(right)
    272 if (
    273     should_extension_dispatch(left, right)
    274     or isinstance(right, (Timedelta, BaseOffset, Timestamp))
   (...)
    277     # Timedelta/Timestamp and other custom scalars are included in the check
    278     # because numexpr will fail on it, see GH#31457
--> 279     res_values = op(left, right)
    280 else:
    281     # TODO we should handle EAs consistently and move this check before the if/else
    282     # (https://github.com/pandas-dev/pandas/issues/41165)
    283     # error: Argument 2 to "_bool_arith_check" has incompatible type
    284     # "Union[ExtensionArray, ndarray[Any, Any]]"; expected "ndarray[Any, Any]"
    285     _bool_arith_check(op, left, right)  # type: ignore[arg-type]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\common.py:85, in _unpack_zerodim_and_defer.<locals>.new_method(self, other)
     82     other = sanitize_array(other, None)
     83     other = ensure_wrapped_if_datetimelike(other)
---> 85 return method(self, other)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arraylike.py:198, in OpsMixin.__sub__(self, other)
    196 @unpack_zerodim_and_defer("__sub__")
    197 def __sub__(self, other):
--> 198     return self._arith_method(other, operator.sub)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\arrow\array.py:1158, in ArrowExtensionArray._arith_method(self, other, op)
   1154 if pa.types.is_string(self._pa_array.type) or pa.types.is_large_string(
   1155     self._pa_array.type
   1156 ):
   1157     try:
-> 1158         result = self._evaluate_op_method(other, op, ARROW_ARITHMETIC_FUNCS)
   1159     except (pa.ArrowInvalid, pa.ArrowTypeError):
   1160         result = self._str_arith_method_object_fallback(other, op)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\arrow\array.py:1099, in ArrowExtensionArray._evaluate_op_method(self, other, op, arrow_funcs)
   1097     result = pc_func(self._pa_array, other)
   1098 except pa.ArrowNotImplementedError as err:
-> 1099     raise TypeError(self._op_method_error_message(other_original, op)) from err
   1100 return self._from_pyarrow_array(result)

TypeError: operation 'sub' not supported for dtype 'str' with dtype 'str'
fmt = "%m/%d/%Y"
for col in ["order_date", "ship_date"]:
    items[col] = pd.to_datetime(items[col], format=fmt)
gap = items["ship_date"] - items["order_date"]
items["days_to_ship"] = gap.dt.days
items["days_to_ship"].describe().round(2)
count    477.00
mean       1.86
std        1.63
min        0.00
25%        1.00
50%        2.00
75%        2.00
max        9.00
Name: days_to_ship, dtype: float64
items["month"] = items["order_date"].dt.month
print(items["month"].unique())
items["weekday"] = items["order_date"].dt.day_name()
items["weekday"].value_counts().head(3)
[1]
weekday
Monday       86
Wednesday    75
Thursday     73
Name: count, dtype: int64
per_day = items["days_to_ship"].value_counts().sort_index()
ax = per_day.plot(kind="bar", rot=0, figsize=(8, 4))
ax.set_title("API order lines by days to ship")
ax.set_xlabel("Days from order to shipping")
ax.set_ylabel("Order lines")
print(per_day.idxmax(), per_day.max())
2 175
No description has been provided for this image
by_mode = items.groupby("ship_mode")["days_to_ship"]
by_mode.agg(["size", "mean"]).round(2)
size mean
ship_mode
Delivery Truck 95 1.87
Express Air 46 1.91
Regular Air 336 1.84

Build ratio, band and group features#

Ratios compare columns, bands simplify them, and groups add context.

items["margin"] = items["profit"] / items["sales"]
items["margin"].describe().round(3)
count    477.000
mean      -0.130
std        0.547
min       -7.004
25%       -0.338
50%       -0.026
75%        0.214
max        0.594
Name: margin, dtype: float64
print(items["discount"].min(), items["discount"].max())
edges = [0, 0.03, 0.07, 0.25]
items["disc_band"] = pd.cut(items["discount"], bins=edges,
                             labels=["low", "mid", "high"],
                             include_lowest=True)
items["disc_band"].value_counts()
0.0 0.1
disc_band
low     187
mid     155
high    135
Name: count, dtype: int64
by_band = items.groupby("disc_band", observed=True)["margin"]
by_band.median().round(3)
disc_band
low    -0.014
mid    -0.028
high   -0.033
Name: margin, dtype: float64
by_cust = items.groupby("customer")["product"]
items["cust_lines"] = by_cust.transform("size")
items[["customer", "cust_lines"]].head(3)
customer cust_lines
0 Matt Collister 1
1 Jessica Myrick 1
2 Susan MacKendrick 3

Your turn: add order_total (the sum of sales in each order) and each item's share of it.

by_order = items.groupby("order_id")["sales"]
items["order_total"] = by_order.transform("sum")
items["share"] = items["sales"] / items["order_total"]
items.sort_values("order_total", ascending=False)[
    ["order_id", "sales", "order_total", "share"]].head(3).round(2)
order_id sales order_total share
68 59270 2138.63 29694.92 0.07
67 59270 25312.00 29694.92 0.85
69 59270 1597.02 29694.92 0.05

Mini-project: which feature flags loss-making lines?#

Reuse our features on all 8,399 CSV lines and find the strongest warning sign.

def add_features(d):
    d = d.copy()
    for col in ["order_date", "ship_date"]:
        d[col] = pd.to_datetime(d[col], format=fmt)
    d["days_to_ship"] = (d["ship_date"] - d["order_date"]).dt.days
    d["disc_band"] = pd.cut(d["discount"], bins=edges,
                            labels=["low", "mid", "high"],
                            include_lowest=True)
    d["ship_share"] = d["shipping_cost"] / d["sales"]
    return d
csv_schema = {"Order Date": "order_date", "Ship Date": "ship_date",
              "Discount": "discount", "Sales": "sales",
              "Shipping Cost": "shipping_cost", "Profit": "profit"}
full = add_features(shop.rename(columns=csv_schema))
full["loss"] = full["profit"] < 0
print(full.shape, round(full["loss"].mean(), 3))
(8399, 25) 0.508

Why not use margin here? It is built from profit, so it would leak the answer.

Quick check: which will track losses more closely, raw shipping cost or shipping cost as a share of the sale?

check = ["shipping_cost", "discount", "days_to_ship", "ship_share"]
full[check].corrwith(full["loss"]).round(3)
shipping_cost    0.148
discount         0.071
days_to_ship    -0.018
ship_share       0.352
dtype: float64
labels = ["Q1", "Q2", "Q3", "Q4"]
quart = pd.qcut(full["ship_share"], 4, labels=labels)
loss_rate = full.groupby(quart, observed=True)["loss"].mean()
loss_rate.round(3)
ship_share
Q1    0.114
Q2    0.306
Q3    0.697
Q4    0.913
Name: loss, dtype: float64
by_disc = full.groupby("disc_band", observed=True)["loss"].mean()
by_disc.round(3)
disc_band
low     0.469
mid     0.507
high    0.561
Name: loss, dtype: float64
cut_off = full["ship_share"].quantile(0.75)
print(f"top quarter: shipping above {cut_off:.1%} of the sale")
top quarter: shipping above 3.2% of the sale
ax = loss_rate.plot(kind="bar", rot=0, figsize=(7, 4))
ax.set_title("Loss-making lines by shipping share of sale")
ax.set_xlabel("Shipping cost / sales (quarters, low to high)")
ax.set_ylabel("Share of lines with a loss")
print(f"Q1: {loss_rate.iloc[0]:.0%}  Q4: {loss_rate.iloc[-1]:.0%}")
Q1: 11%  Q4: 91%
No description has been provided for this image

Recap#

  • Collect paged API results in a loop, then flatten with json_normalize
  • Nested meta fields are lists of keys, not dotted strings
  • Agree on one schema, then merge with validate and indicator
  • Convert text dates with to_datetime before doing date maths
  • Ratios, bands and group features often beat raw columns

Challenges#

  1. Add an order-level feature: the number of items in each order.
  2. Find the loss rate by ship mode and by discount band.
  3. Add a 'weekend order' flag and check whether it matters.

What's next#

Lesson 12: Dimensionality Reduction with PCA Many features? PCA squeezes them into a few that keep most of the information.

Data Mining 12: Dimensionality Reduction with PCA#

In this lesson you'll be able to:

  • Explain why many correlated features are a problem
  • Describe what PCA does, in plain words and one picture
  • Scale features and fit PCA with scikit-learn
  • Choose how many components to keep from explained variance
  • Read components and use PCA inside a model pipeline Datasets: wine chemistry (data/mining/wine.csv) and breast cancer diagnostics (data/mining/breast_cancer.csv)

Warm-up: engineer one feature#

Lesson 11 built new features. Each one adds a column, and columns add up.

import pandas as pd
import numpy as np
wine = pd.read_csv("wine.csv")
print(wine.shape)
wine["cultivar"].value_counts()
(178, 14)
cultivar
class_1    71
class_0    59
class_2    48
Name: count, dtype: int64
ratio = wine["flavanoids"] / wine["total_phenols"]
print(ratio.groupby(wine["cultivar"]).mean().round(2))
cultivar
class_0    1.05
class_1    0.92
class_2    0.48
dtype: float64

See why too many features hurt#

Problem: 13 features are hard to plot, and many say the same thing.

X = wine.drop(columns="cultivar")
y = wine["cultivar"]
print(X.shape[1], "features")
13 features

Quick check: how many different two-feature scatter plots would we need to see every pair?

n = X.shape[1]
print(n * (n - 1) // 2)
78
corr = X.corr().abs()
upper = np.triu(np.ones(corr.shape, dtype=bool), k=1)
pairs = corr.where(upper).stack()
pairs.sort_values(ascending=False).head(3).round(2)
total_phenols  flavanoids                      0.86
flavanoids     od280/od315_of_diluted_wines    0.79
total_phenols  od280/od315_of_diluted_wines    0.70
dtype: float64
import matplotlib.pyplot as plt
import seaborn as sns
fig, ax = plt.subplots(figsize=(8, 6))
sns.heatmap(X.corr(), cmap="coolwarm", center=0, ax=ax)
ax.set_title("Wine: correlation between 13 features")
ax.set_xlabel("Feature")
ax.set_ylabel("Feature")
No description has been provided for this image

Picture PCA with two features#

PCA finds new axes: the direction with the most spread first, then the next, at right angles.

from sklearn.preprocessing import StandardScaler
two = ["total_phenols", "flavanoids"]
Z2 = StandardScaler().fit_transform(wine[two])
print(Z2[:3].round(2))
[[0.81 1.03]
 [0.57 0.73]
 [0.81 1.22]]
from sklearn.decomposition import PCA
pca2 = PCA().fit(Z2)
print(pca2.explained_variance_ratio_.round(3))
print(pca2.components_.round(2))
[0.932 0.068]
[[ 0.71  0.71]
 [-0.71  0.71]]
fig, ax = plt.subplots(figsize=(6, 6))
ax.scatter(Z2[:, 0], Z2[:, 1], alpha=0.6)
d = pca2.components_[0] * 3
ax.annotate("", xy=d, xytext=-d, arrowprops={"color": "red"})
ax.set_title("First principal component of two wine features")
ax.set_xlabel("Total phenols (scaled)")
ax.set_ylabel("Flavanoids (scaled)")
plt.show()
No description has been provided for this image
s1 = pca2.transform(Z2)[:3, 0]
by_hand = Z2[:3] @ pca2.components_[0]
print(s1.round(3), by_hand.round(3))
[1.304 0.921 1.432] [1.304 0.921 1.432]

Your turn: keep only PC1 of these two features. What share of their spread survives?

one = PCA(n_components=1).fit(Z2)
print(one.transform(Z2).shape)
print(one.explained_variance_ratio_.round(3))
(178, 1)
[0.932]

Scale first, then fit PCA#

PCA chases spread. Unscaled, the biggest numbers win.

print(X.var().sort_values().round(3).tail(3))
alcalinity_of_ash       11.153
magnesium              203.989
proline              99166.717
dtype: float64
raw_pca = PCA().fit(X)
print(raw_pca.explained_variance_ratio_[:3].round(3))
[0.998 0.002 0.   ]

Quick check: one component keeps almost everything. Good news, or a warning sign?

w1 = pd.Series(raw_pca.components_[0], index=X.columns)
print(w1.abs().sort_values(ascending=False).head(3).round(3))
print(X["proline"].var().round(0), X["hue"].var().round(3))
proline              1.000
magnesium            0.018
alcalinity_of_ash    0.005
dtype: float64
99167.0 0.052
Z = StandardScaler().fit_transform(X)
pca = PCA().fit(Z)
ratios = pca.explained_variance_ratio_
print(ratios[:3].round(3))
[0.362 0.192 0.111]

Choose how many components to keep#

Keep enough components to explain most of the variance, often 90 or 95 percent.

cum = np.cumsum(ratios)
print(cum.round(3))
print(np.argmax(cum >= 0.9) + 1)
[0.362 0.554 0.665 0.736 0.802 0.851 0.893 0.92  0.942 0.962 0.979 0.992
 1.   ]
8
k = np.arange(1, len(ratios) + 1)
fig, ax = plt.subplots(figsize=(8, 4))
ax.bar(k, ratios, label="each component")
ax.plot(k, cum, marker="o", color="red", label="cumulative")
ax.axhline(0.9, linestyle="--", color="grey")
ax.set_title("Wine: variance explained by each component")
ax.set_xlabel("Principal component")
ax.set_ylabel("Share of variance")
ax.legend()
No description has been provided for this image

Common mistake: asking for more components than there are features.

PCA(n_components=20).fit(Z)
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[19], line 1
----> 1 PCA(n_components=20).fit(Z)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\base.py:1403, in _fit_context.<locals>.decorator.<locals>.wrapper(estimator, *args, **kwargs)
   1393     estimator._validate_params()
   1395 with (
   1396     config_context(
   1397         skip_parameter_validation=(
   (...)
   1401     callback_management_context(estimator),
   1402 ):
-> 1403     return fit_method(estimator, *args, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\decomposition\_pca.py:440, in PCA.fit(self, X, y)
    422 @_fit_context(prefer_skip_nested_validation=True)
    423 def fit(self, X, y=None):
    424     """Fit the model with X.
    425 
    426     Parameters
   (...)
    438         Returns the instance itself.
    439     """
--> 440     self._fit(X)
    441     return self

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\decomposition\_pca.py:540, in PCA._fit(self, X)
    538 # Call different fits for either full or truncated SVD
    539 if self._fit_svd_solver in ("full", "covariance_eigh"):
--> 540     return self._fit_full(X, n_components, xp, is_array_api_compliant)
    541 elif self._fit_svd_solver in ["arpack", "randomized"]:
    542     return self._fit_truncated(X, n_components, xp)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\decomposition\_pca.py:554, in PCA._fit_full(self, X, n_components, xp, is_array_api_compliant)
    550         raise ValueError(
    551             "n_components='mle' is only supported if n_samples >= n_features"
    552         )
    553 elif not 0 <= n_components <= min(n_samples, n_features):
--> 554     raise ValueError(
    555         f"n_components={n_components} must be between 0 and "
    556         f"min(n_samples, n_features)={min(n_samples, n_features)} with "
    557         f"svd_solver={self._fit_svd_solver!r}"
    558     )
    560 self.mean_ = xp.mean(X, axis=0)
    561 # When X is a scipy sparse matrix, self.mean_ is a numpy matrix, so we need
    562 # to transform it to a 1D array. Note that this is not the case when X
    563 # is a scipy sparse array.
    564 # TODO: remove the following two lines when scikit-learn only depends
    565 # on scipy versions that no longer support scipy.sparse matrices.

ValueError: n_components=20 must be between 0 and min(n_samples, n_features)=13 with svd_solver='covariance_eigh'
pca95 = PCA(n_components=0.95).fit(Z)
print(pca95.n_components_)
print(pca95.explained_variance_ratio_.sum().round(3))
10
0.962
Z8 = PCA(n_components=8).fit_transform(Z)
print(Z.shape, "->", Z8.shape)
(178, 13) -> (178, 8)

Your turn: how many components keep at least 80 percent of the wine variance? Let PCA tell you.

p80 = PCA(n_components=0.8).fit(Z)
print(p80.n_components_, p80.explained_variance_ratio_.sum().round(3))
5 0.802

Read the components and plot the wines#

Each component is a weighted mix of the original features.

w = pd.DataFrame(pca.components_[:2].T, index=X.columns,
                 columns=["PC1", "PC2"])
top = w["PC1"].abs().sort_values(ascending=False).index[:4]
w.loc[top].round(2)
PC1 PC2
flavanoids 0.42 -0.00
total_phenols 0.39 0.07
od280/od315_of_diluted_wines 0.38 -0.16
proanthocyanins 0.31 0.04
top2 = w["PC2"].abs().sort_values(ascending=False).index[:3]
w.loc[top2].round(2)
PC1 PC2
color_intensity -0.09 0.53
alcohol 0.14 0.48
proline 0.29 0.36
pcs = PCA(n_components=2).fit_transform(Z)
scores = pd.DataFrame(pcs, columns=["PC1", "PC2"])
scores["cultivar"] = y
scores.groupby("cultivar")[["PC1", "PC2"]].mean().round(2)
PC1 PC2
cultivar
class_0 2.28 0.97
class_1 -0.04 -1.64
class_2 -2.75 1.24
fig, ax = plt.subplots(figsize=(7, 5))
for name, grp in scores.groupby("cultivar"):
    ax.scatter(grp["PC1"], grp["PC2"], label=name, alpha=0.7)
ax.set_title("178 wines: 13 measurements squeezed into 2")
ax.set_xlabel(f"PC1 ({ratios[0]:.0%} of variance)")
ax.set_ylabel(f"PC2 ({ratios[1]:.0%} of variance)")
ax.legend(title="Cultivar")
No description has been provided for this image

A new wine arrives: scale and project it with what we learned, never refit.

from sklearn.pipeline import make_pipeline
new = X.tail(3)
proj = make_pipeline(StandardScaler(), PCA(n_components=2))
proj.fit(X.iloc[:-3])
print(y.tail(3).to_list())
print(proj.transform(new).round(2))
['class_2', 'class_2', 'class_2']
[[-2.61  2.95]
 [-2.34  2.46]
 [-3.15  2.99]]
known = proj.transform(X.iloc[:-3])
ref = pd.DataFrame(known, columns=["PC1", "PC2"])
ref.groupby(y.iloc[:-3].to_numpy()).mean().round(2)
PC1 PC2
class_0 2.28 0.91
class_1 -0.15 -1.61
class_2 -2.75 1.34

Your turn: rebuild the 13 features from 2 components with inverse_transform. How much error is left?

p2 = PCA(n_components=2).fit(Z)
back = p2.inverse_transform(p2.transform(Z))
err = ((Z - back) ** 2).mean()
print(err.round(3), (1 - p2.explained_variance_ratio_.sum()).round(3))
0.446 0.446

Mini-project: fewer features, same accuracy?#

Classify 569 tumours from 30 measurements, with and without PCA.

cancer = pd.read_csv("breast_cancer.csv")
Xc = cancer.drop(columns="diagnosis")
yc = cancer["diagnosis"]
print(Xc.shape, yc.value_counts().to_dict())
(569, 30) {'benign': 357, 'malignant': 212}
cp = PCA().fit(StandardScaler().fit_transform(Xc))
print(np.cumsum(cp.explained_variance_ratio_)[:3].round(3))
[0.443 0.632 0.726]
from sklearn.model_selection import train_test_split
Xtr, Xte, ytr, yte = train_test_split(Xc, yc, test_size=0.25,
                                      stratify=yc, random_state=42)
print(len(Xtr), len(Xte))
426 143
from sklearn.linear_model import LogisticRegression
from sklearn.model_selection import cross_val_score
lr = LogisticRegression(max_iter=1000)
full_model = make_pipeline(StandardScaler(), lr)
pca_model = make_pipeline(StandardScaler(), PCA(n_components=0.95),
                          LogisticRegression(max_iter=1000))
for name, m in [("all 30", full_model), ("PCA 95%", pca_model)]:
    cv = cross_val_score(m, Xtr, ytr, cv=5).mean()
    print(name, "CV accuracy:", round(cv, 3))
all 30 CV accuracy: 0.977
PCA 95% CV accuracy: 0.979
sizes = [1, 2, 3, 5, 10, 20]
cv_acc = {}
for c in sizes:
    m = make_pipeline(StandardScaler(), PCA(n_components=c),
                      LogisticRegression(max_iter=1000))
    cv_acc[c] = cross_val_score(m, Xtr, ytr, cv=5).mean()
cv_acc = pd.Series(cv_acc)
print(cv_acc.round(3))
1     0.911
2     0.948
3     0.951
5     0.969
10    0.979
20    0.977
dtype: float64
ax = cv_acc.plot(marker="o", figsize=(7, 4))
ax.set_title("Tumour classifier: accuracy vs PCA components")
ax.set_xlabel("Components kept (of 30)")
ax.set_ylabel("Cross-validated accuracy")
No description has been provided for this image
pca_model.fit(Xtr, ytr)
print("components kept:", pca_model[1].n_components_)
print("test accuracy:", round(pca_model.score(Xte, yte), 3))
full_model.fit(Xtr, ytr)
print("all 30 test:", round(full_model.score(Xte, yte), 3))
components kept: 10
test accuracy: 0.972
all 30 test: 0.965

Quick check: for a cancer screen, which mistake is worse: a false alarm or a missed tumour?

from sklearn.metrics import confusion_matrix
pred = pca_model.predict(Xte)
labels = ["benign", "malignant"]
cm = confusion_matrix(yte, pred, labels=labels)
pd.DataFrame(cm, index=labels, columns=labels)
benign malignant
benign 90 0
malignant 4 49

Recap#

  • Correlated features repeat information; PCA combines them
  • PCA finds new axes ordered by how much spread they capture
  • Always scale first, or the biggest units take over
  • Pick components with the cumulative explained variance
  • Put PCA inside a pipeline and judge it on held-out data

Challenges#

  1. Plot the breast cancer tumours on PC1 and PC2, coloured by diagnosis.
  2. Try n_components of 2, 5 and 10 in the pipeline. Where does accuracy level off?
  3. Find the three features with the biggest PC1 weights for the cancer data.

What's next#

Lesson 13: Hands-on Preprocessing with Titanic and Iris Datasets Clean, transform, engineer and reduce, from raw file to model-ready table.

Data Mining 13: Hands-on Preprocessing with Titanic and Iris Datasets#

In this lesson you'll be able to:

  • Audit a raw table for gaps, odd values and text columns
  • Split first, then fill missing values without leaking test data
  • Encode text columns and engineer new features
  • Scale measurements so they share one yardstick
  • Wrap every step in one reusable pipeline Datasets: Titanic passengers (data/pandas/titanic.csv) and Iris flowers (data/pandas/iris.csv)

Warm-up: PCA on the Iris flowers#

Lesson 12 squeezed four measurements into two components.

import pandas as pd
from sklearn.preprocessing import StandardScaler
from sklearn.decomposition import PCA
iris = pd.read_csv("iris.csv")
features = iris.drop(columns="species")
scaled = StandardScaler().fit_transform(features)
pca = PCA(n_components=2).fit(scaled)
print(pca.explained_variance_ratio_.round(3))
[0.73  0.229]
print(pca.explained_variance_ratio_.sum().round(3))
0.958

Audit the raw Titanic data#

Problem: find out what is broken before fixing anything.

titanic = pd.read_csv("titanic.csv")
print(titanic.shape)
titanic.head(3)
(891, 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
titanic.dtypes
PassengerId      int64
Survived         int64
Pclass           int64
Name               str
Sex                str
Age            float64
SibSp            int64
Parch            int64
Ticket             str
Fare           float64
Cabin              str
Embarked           str
dtype: object
missing = titanic.isna().sum()
missing[missing > 0]
Age         177
Cabin       687
Embarked      2
dtype: int64
share = titanic.isna().mean() * 100
share.round(1).sort_values(ascending=False).head(3)
Cabin       77.1
Age         19.9
Embarked     0.2
dtype: float64

Quick check: how many passengers have a recorded cabin?

print(titanic["Cabin"].notna().sum())
204
titanic[["Age", "Fare"]].describe().round(2)
Age Fare
count 714.00 891.00
mean 29.70 32.20
std 14.53 49.69
min 0.42 0.00
25% 20.12 7.91
50% 28.00 14.45
75% 38.00 31.00
max 80.00 512.33
zero_fare = titanic[titanic["Fare"] == 0]
print(len(zero_fare))
zero_fare[["Name", "Pclass", "Ticket"]].head(4)
15
Name Pclass Ticket
179 Leonard, Mr. Lionel 3 LINE
263 Harrison, Mr. William 1 112059
271 Tornquist, Mr. William Henry 3 LINE
277 Parkes, Mr. Francis "Frank" 2 239853

Split first, then fill the gaps#

Problem: fill missing values without peeking at the test rows.

from sklearn.model_selection import train_test_split
X = titanic.drop(columns="Survived")
y = titanic["Survived"]
X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.2, stratify=y, random_state=42)
print(X_train.shape, X_test.shape)
print(y_train.mean().round(3), y_test.mean().round(3))
(712, 11) (179, 11)
0.383 0.385

Common mistake: handing a model columns that still have gaps.

from sklearn.linear_model import LogisticRegression
cols = ["Age", "Fare"]
model = LogisticRegression()
model.fit(X_train[cols], y_train)
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[12], line 4
      2 cols = ["Age", "Fare"]
      3 model = LogisticRegression()
----> 4 model.fit(X_train[cols], y_train)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\base.py:1403, in _fit_context.<locals>.decorator.<locals>.wrapper(estimator, *args, **kwargs)
   1393     estimator._validate_params()
   1395 with (
   1396     config_context(
   1397         skip_parameter_validation=(
   (...)
   1401     callback_management_context(estimator),
   1402 ):
-> 1403     return fit_method(estimator, *args, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\linear_model\_logistic.py:1437, in LogisticRegression.fit(self, X, y, sample_weight)
   1434 if self.n_jobs is not None:
   1435     warnings.warn(msg, category=FutureWarning)
-> 1437 X, y = validate_data(
   1438     self,
   1439     X,
   1440     y,
   1441     accept_sparse="csr",
   1442     dtype=[xp.float64, xp.float32],
   1443     order="C",
   1444     accept_large_sparse=solver not in ["liblinear", "sag", "saga"],
   1445 )
   1446 n_features = X.shape[1]
   1447 check_classification_targets(y)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\validation.py:3058, in validate_data(_estimator, X, y, reset, validate_separately, skip_check_array, **check_params)
   3056         y = check_array(y, input_name="y", **check_y_params)
   3057     else:
-> 3058         X, y = check_X_y(X, y, **check_params)
   3059     out = X, y
   3061 if not no_val_X and check_params.get("ensure_2d", True):

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\validation.py:1330, in check_X_y(X, y, accept_sparse, accept_large_sparse, dtype, order, copy, force_writeable, ensure_all_finite, ensure_2d, allow_nd, multi_output, ensure_min_samples, ensure_min_features, y_numeric, estimator)
   1325         estimator_name = _check_estimator_name(estimator)
   1326     raise ValueError(
   1327         f"{estimator_name} requires y to be passed, but the target y is None"
   1328     )
-> 1330 X = check_array(
   1331     X,
   1332     accept_sparse=accept_sparse,
   1333     accept_large_sparse=accept_large_sparse,
   1334     dtype=dtype,
   1335     order=order,
   1336     copy=copy,
   1337     force_writeable=force_writeable,
   1338     ensure_all_finite=ensure_all_finite,
   1339     ensure_2d=ensure_2d,
   1340     allow_nd=allow_nd,
   1341     ensure_min_samples=ensure_min_samples,
   1342     ensure_min_features=ensure_min_features,
   1343     estimator=estimator,
   1344     input_name="X",
   1345 )
   1347 y = _check_y(y, multi_output=multi_output, y_numeric=y_numeric, estimator=estimator)
   1349 check_consistent_length(X, y)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\validation.py:1090, in check_array(array, accept_sparse, accept_large_sparse, dtype, order, copy, force_writeable, ensure_all_finite, ensure_non_negative, ensure_2d, allow_nd, ensure_min_samples, ensure_min_features, estimator, input_name)
   1084     raise ValueError(
   1085         f"Found array with dim {array.ndim},"
   1086         f" while dim <= 2 is required{context}."
   1087     )
   1089 if ensure_all_finite:
-> 1090     _assert_all_finite(
   1091         array,
   1092         input_name=input_name,
   1093         estimator_name=estimator_name,
   1094         allow_nan=ensure_all_finite == "allow-nan",
   1095     )
   1097 if copy:
   1098     if _is_numpy_namespace(xp):
   1099         # only make a copy if `array` and `array_orig` may share memory`

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\validation.py:137, in _assert_all_finite(X, allow_nan, msg_dtype, estimator_name, input_name)
    134 if first_pass_isfinite:
    135     return
--> 137 _assert_all_finite_element_wise(
    138     X,
    139     xp=xp,
    140     allow_nan=allow_nan,
    141     msg_dtype=msg_dtype,
    142     estimator_name=estimator_name,
    143     input_name=input_name,
    144 )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\validation.py:186, in _assert_all_finite_element_wise(X, xp, allow_nan, msg_dtype, estimator_name, input_name)
    169 if estimator_name and input_name == "X" and has_nan_error:
    170     # Improve the error message on how to handle missing values in
    171     # scikit-learn.
    172     msg_err += (
    173         f"\n{estimator_name} does not accept missing values"
    174         " encoded as NaN natively. For supervised learning, you might want"
   (...)
    184         "#estimators-that-handle-nan-values"
    185     )
--> 186 raise ValueError(msg_err)

ValueError: Input X contains NaN.
LogisticRegression does not accept missing values encoded as NaN natively. For supervised learning, you might want to consider sklearn.ensemble.HistGradientBoostingClassifier and Regressor which accept missing values encoded as NaNs natively. Alternatively, it is possible to preprocess the data, for instance by using an imputer transformer in a pipeline or drop samples with missing values. See https://scikit-learn.org/stable/modules/impute.html You can find a list of all estimators that handle NaN values at the following page: https://scikit-learn.org/stable/modules/impute.html#estimators-that-handle-nan-values
from sklearn.impute import SimpleImputer
imputer = SimpleImputer(strategy="median")
train_num = imputer.fit_transform(X_train[cols])
test_num = imputer.transform(X_test[cols])
print(imputer.statistics_)
[28.5    14.4542]
model.fit(train_num, y_train)
print(round(model.score(test_num, y_test), 3))
0.603
filled_age = pd.Series(train_num[:, 0])
print((filled_age == 28.5).sum())
139

Quick check: is one median age fair to every class?

class_age = X_train.groupby("Pclass")["Age"].median()
class_age
Pclass
1    38.0
2    29.0
3    24.0
Name: Age, dtype: float64
train = X_train.copy()
test = X_test.copy()
train_fill = train["Pclass"].map(class_age)
train["Age"] = train["Age"].fillna(train_fill)
test_fill = test["Pclass"].map(class_age)
test["Age"] = test["Age"].fillna(test_fill)
print(train["Age"].isna().sum(), test["Age"].isna().sum())
0 0
port = train["Embarked"].mode()[0]
train["Embarked"] = train["Embarked"].fillna(port)
test["Embarked"] = test["Embarked"].fillna(port)
print(port)
S
train["HasCabin"] = train["Cabin"].notna().astype(int)
test["HasCabin"] = test["Cabin"].notna().astype(int)
y_train.groupby(train["HasCabin"]).mean().round(3)
HasCabin
0    0.292
1    0.700
Name: Survived, dtype: float64

Turn text columns into numbers#

Problem: models and scalers only understand numbers.

Common mistake: scaling a table that still holds words.

scaler = StandardScaler()
scaler.fit(train[["Age", "Fare", "Sex"]])
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_28028\2067528980.py in ?()
      1 scaler = StandardScaler()
----> 2 scaler.fit(train[["Age", "Fare", "Sex"]])

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\preprocessing\_data.py in ?(self, X, y, sample_weight)
    924             Fitted scaler.
    925         """
    926         # Reset internal state before fitting
    927         self._reset()
--> 928         return self.partial_fit(X, y, sample_weight)

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\base.py in ?(estimator, *args, **kwargs)
   1399                     )
   1400                 ),
   1401                 callback_management_context(estimator),
   1402             ):
-> 1403                 return fit_method(estimator, *args, **kwargs)

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\preprocessing\_data.py in ?(self, X, y, sample_weight)
    961             Fitted scaler.
    962         """
    963         xp, _, X_device = get_namespace_and_device(X)
    964         first_call = not hasattr(self, "n_samples_seen_")
--> 965         X = validate_data(
    966             self,
    967             X,
    968             accept_sparse=("csr", "csc"),

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\validation.py in ?(_estimator, X, y, reset, validate_separately, skip_check_array, **check_params)
   3037             out = y
   3038         else:
   3039             out = X, y
   3040     elif not no_val_X and no_val_y:
-> 3041         out = check_array(X, input_name="X", **check_params)
   3042     elif no_val_X and not no_val_y:
   3043         out = _check_y(y, **check_params)
   3044     else:

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\validation.py in ?(array, accept_sparse, accept_large_sparse, dtype, order, copy, force_writeable, ensure_all_finite, ensure_non_negative, ensure_2d, allow_nd, ensure_min_samples, ensure_min_features, estimator, input_name)
   1035                         )
   1036                     array = xp.astype(array, dtype, copy=False)
   1037                 else:
   1038                     array = _asarray_with_order(array, order=order, dtype=dtype, xp=xp)
-> 1039             except ComplexWarning as complex_warning:
   1040                 raise ValueError(
   1041                     "Complex data not supported\n{}\n".format(array)
   1042                 ) from complex_warning

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\_array_api.py in ?(array, dtype, order, copy, xp, device)
    979         # Use NumPy API to support order
    980         if copy is True:
    981             array = numpy.array(array, order=order, dtype=dtype)
    982         else:
--> 983             array = numpy.asarray(array, order=order, dtype=dtype)
    984 
    985         # At this point array is a NumPy ndarray. We convert it to an array
    986         # container that is consistent with the input's namespace.

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py in ?(self, dtype, copy)
   2023             )
   2024         values = self._values
   2025         if copy is None:
   2026             # Note: branch avoids `copy=None` for NumPy 1.x support
-> 2027             arr = np.asarray(values, dtype=dtype)
   2028         else:
   2029             arr = np.array(values, dtype=dtype, copy=copy)
   2030 

ValueError: could not convert string to float: 'male'
sex_map = {"male": 0, "female": 1}
train["IsFemale"] = train["Sex"].map(sex_map)
test["IsFemale"] = test["Sex"].map(sex_map)
train[["Sex", "IsFemale"]].head(3)
Sex IsFemale
692 male 0
481 male 0
527 male 0
y_train.groupby(train["IsFemale"]).mean().round(3)
IsFemale
0    0.185
1    0.743
Name: Survived, dtype: float64

Ports have no natural order, so each port gets its own 0/1 column.

from sklearn.preprocessing import OneHotEncoder
enc = OneHotEncoder(handle_unknown="ignore", sparse_output=False)
enc.fit(train[["Embarked"]])
print(enc.get_feature_names_out())
enc.transform(test[["Embarked"]])[:3]
['Embarked_C' 'Embarked_Q' 'Embarked_S']
array([[0., 0., 1.],
       [0., 0., 1.],
       [1., 0., 0.]])

Quick check: what does pd.get_dummies make from 3 test rows?

pd.get_dummies(test["Embarked"].head(3), dtype=int)
C S
565 0 1
160 0 1
553 1 0

Engineer features that carry signal#

Problem: useful patterns hide inside raw columns.

train["FamilySize"] = train["SibSp"] + train["Parch"] + 1
test["FamilySize"] = test["SibSp"] + test["Parch"] + 1
rate = y_train.groupby(train["FamilySize"]).mean()
rate.round(2).head(6)
FamilySize
1    0.30
2    0.55
3    0.62
4    0.73
5    0.21
6    0.06
Name: Survived, dtype: float64
import matplotlib.pyplot as plt
ax = rate.plot(kind="bar", color="steelblue")
ax.set_title("Survival rate by family size (training rows)")
ax.set_xlabel("Family size")
ax.set_ylabel("Share who survived")
plt.show()
No description has been provided for this image
pattern = r",\s*([^.]+)\."
train["Title"] = train["Name"].str.extract(pattern)[0]
test["Title"] = test["Name"].str.extract(pattern)[0]
train["Title"].value_counts().head(6)
Title
Mr        412
Miss      141
Mrs       107
Master     31
Dr          6
Rev         5
Name: count, dtype: int64
common = ["Mr", "Miss", "Mrs", "Master"]
keep = train["Title"].isin(common)
train["Title"] = train["Title"].where(keep, "Rare")
keep_test = test["Title"].isin(common)
test["Title"] = test["Title"].where(keep_test, "Rare")
train["Title"].value_counts()
Title
Mr        412
Miss      141
Mrs       107
Master     31
Rare       21
Name: count, dtype: int64

Your turn: find the survival rate for each title in the training rows.

by_title = y_train.groupby(train["Title"]).mean()
by_title.round(2).sort_values()
Title
Mr        0.15
Rare      0.48
Master    0.55
Miss      0.70
Mrs       0.79
Name: Survived, dtype: float64

Scale and split the Iris measurements#

Problem: put features on one yardstick and keep classes balanced.

print(iris.isna().sum().sum())
0
features.agg(["min", "max", "std"]).round(2)
sepal_length sepal_width petal_length petal_width
min 4.30 2.00 1.00 0.10
max 7.90 4.40 6.90 2.50
std 0.83 0.44 1.77 0.76
ax = features.plot(kind="box", figsize=(8, 4))
ax.set_title("Iris measurements before scaling")
ax.set_ylabel("Centimetres")
plt.show()
No description has been provided for this image
species = iris["species"]
Xi_train, Xi_test, yi_train, yi_test = train_test_split(
    features, species, test_size=0.2, stratify=species,
    random_state=42)
yi_test.value_counts()
species
setosa        10
virginica     10
versicolor    10
Name: count, dtype: int64

Quick check: after scaling, what mean will each training column have?

iris_scaler = StandardScaler().fit(Xi_train)
train_sc = pd.DataFrame(iris_scaler.transform(Xi_train),
                        columns=features.columns)
train_sc.agg(["mean", "std"]).round(2)
sepal_length sepal_width petal_length petal_width
mean -0.0 0.0 0.0 0.0
std 1.0 1.0 1.0 1.0
test_sc = pd.DataFrame(iris_scaler.transform(Xi_test),
                       columns=features.columns)
test_sc.agg(["mean", "std"]).round(2)
sepal_length sepal_width petal_length petal_width
mean 0.01 0.10 -0.03 -0.04
std 0.94 0.86 1.01 1.02
ax = train_sc.plot(kind="box", figsize=(8, 4))
ax.set_title("Iris measurements after standard scaling")
ax.set_ylabel("Standard deviations from the mean")
plt.show()
No description has been provided for this image

Your turn: turn the species names into numbers 0, 1 and 2.

from sklearn.preprocessing import LabelEncoder
le = LabelEncoder().fit(yi_train)
print(le.classes_)
pd.Series(le.transform(yi_test)).value_counts().sort_index()
['setosa' 'versicolor' 'virginica']
0    10
1    10
2    10
Name: count, dtype: int64

Mini-project: does preprocessing pay off?#

Bundle every Titanic step into one pipeline, then score it on the test set.

def add_features(df):
    out = df.copy()
    out["FamilySize"] = out["SibSp"] + out["Parch"] + 1
    out["HasCabin"] = out["Cabin"].notna().astype(int)
    title = out["Name"].str.extract(pattern)[0]
    out["Title"] = title.where(title.isin(common), "Rare")
    return out
from sklearn.compose import ColumnTransformer
from sklearn.pipeline import make_pipeline
num_cols = ["Age", "Fare", "FamilySize", "HasCabin", "Pclass"]
cat_cols = ["Sex", "Embarked", "Title"]
num_steps = make_pipeline(SimpleImputer(strategy="median"),
                          StandardScaler())
cat_steps = make_pipeline(SimpleImputer(strategy="most_frequent"),
                          OneHotEncoder(handle_unknown="ignore"))
prep = ColumnTransformer([("num", num_steps, num_cols),
                          ("cat", cat_steps, cat_cols)])
clf = make_pipeline(prep, LogisticRegression(max_iter=1000))
clf.fit(add_features(X_train), y_train)
acc = clf.score(add_features(X_test), y_test)
print(round(acc, 3))
0.827
five = X_test.head(5)
guess = clf.predict(add_features(five))
pd.DataFrame({"guess": guess, "actual": y_test.head(5)})
guess actual
565 0 0
160 0 0
553 0 1
860 0 0
241 1 1
import os
clean = prep.transform(add_features(X_train))
names = prep.get_feature_names_out()
clean_df = pd.DataFrame(clean, columns=names)
clean_df.to_csv("m13_titanic_train_clean.csv",
                index=False)
back = pd.read_csv("m13_titanic_train_clean.csv")
print(back.shape)
print(int(back.isna().sum().sum()))
back.iloc[:3, :6].round(2)
(712, 15)
0
num__Age num__Fare num__FamilySize num__HasCabin num__Pclass cat__Sex_female
0 -0.08 0.51 -0.56 -0.54 0.83 0.0
1 -0.08 -0.66 -0.56 -0.54 -0.37 0.0
2 -0.08 3.96 -0.56 1.86 -1.57 0.0
gain = acc - model.score(test_num, y_test)
print(f"Pipeline: {acc:.1%}, gain: {gain * 100:.1f} points")
Pipeline: 82.7%, gain: 22.3 points

Recap#

  • Audit first: gaps, odd values like zero fares, and text columns
  • Split before you learn any fill value or scale
  • Map two-level text, one-hot encode unordered categories
  • Engineered features like Title and FamilySize carry real signal
  • A ColumnTransformer pipeline repeats every step safely on new data

Challenges#

  1. Fill Age with the median for each Title instead of each class.
  2. Add IsAlone (FamilySize == 1) to the pipeline. Does accuracy move?
  3. Run PCA on the scaled Iris training data and plot the two components.

What's next#

Lesson 14: Decision Trees - ID3, C4.5, CART Our first real classifier, built on the data we just cleaned.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.