Mathew K Analytics

Lesson 54 · Pandas Full Course

Prepare Data for scikit-learn with Pandas: Pipelines Done Right | Pandas Course #54

In this lesson you'll be able to: Turn a DataFrame into the X and y that scikit-learn expects Fill missing values using training data only, with no leakage…

📓 Full notebook

Download .ipynb

Pandas 54: Preparing Data for scikit-learn Models#

In this lesson you'll be able to:

  • Turn a DataFrame into the X and y that scikit-learn expects
  • Fill missing values using training data only, with no leakage
  • One-hot encode text columns and keep train and test aligned
  • Scale numbers and wrap every step in a Pipeline
  • Read a fitted model's coefficients back into pandas Dataset: the Titanic passenger list (data/pandas/titanic.csv)

Warm-up: split before anything else#

Lesson 53 split data into train and test sets. Same idea, same data.

import pandas as pd
df = pd.read_csv("data/pandas/titanic.csv")
print(df.shape)
df["Survived"].value_counts(normalize=True).round(3)
(891, 12)
Survived
0    0.616
1    0.384
Name: proportion, dtype: float64
from sklearn.model_selection import train_test_split

train, test = train_test_split(df, test_size=0.2, random_state=42,
                               stratify=df["Survived"])
print(train.shape, test.shape)
(712, 12) (179, 12)

Shape the table into X and y#

Problem: a model needs feature columns and one target column.

features = ["Pclass", "Sex", "Age", "SibSp", "Parch", "Fare",
            "Embarked"]
X = df[features]
y = df["Survived"]
print(X.shape, y.shape)
(891, 7) (891,)

Quick check: why leave out Name, Ticket and PassengerId?

df[["PassengerId", "Name", "Ticket"]].nunique()
PassengerId    891
Name           891
Ticket         681
dtype: int64
X.dtypes
Pclass        int64
Sex             str
Age         float64
SibSp         int64
Parch         int64
Fare        float64
Embarked        str
dtype: object
X.isna().sum()
Pclass        0
Sex           0
Age         177
SibSp         0
Parch         0
Fare          0
Embarked      2
dtype: int64

Common mistake: fitting a model on the raw table

from sklearn.linear_model import LogisticRegression
LogisticRegression().fit(X, y)
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_2152\3486342647.py in ?()
      1 from sklearn.linear_model import LogisticRegression
----> 2 LogisticRegression().fit(X, y)

~\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\linear_model\_logistic.py in ?(self, X, y, sample_weight)
   1433         )
   1434         if self.n_jobs is not None:
   1435             warnings.warn(msg, category=FutureWarning)
   1436 
-> 1437         X, y = validate_data(
   1438             self,
   1439             X,
   1440             y,

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\validation.py in ?(_estimator, X, y, reset, validate_separately, skip_check_array, **check_params)
   3054             if "estimator" not in check_y_params:
   3055                 check_y_params = {**default_check_params, **check_y_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
   3060 
   3061     if not no_val_X and check_params.get("ensure_2d", True):

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\validation.py in ?(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)
   1326         raise ValueError(
   1327             f"{estimator_name} requires y to be passed, but the target y is None"
   1328         )
   1329 
-> 1330     X = check_array(
   1331         X,
   1332         accept_sparse=accept_sparse,
   1333         accept_large_sparse=accept_large_sparse,

~\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'
X_num = X.select_dtypes(include="number")
print(X_num.columns.tolist())
print(X_num.isna().any().to_dict())
['Pclass', 'Age', 'SibSp', 'Parch', 'Fare']
{'Pclass': False, 'Age': True, 'SibSp': False, 'Parch': False, 'Fare': False}

Fill gaps with training statistics only#

Problem: test rows must not influence anything the model learns.

X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.2, random_state=42, stratify=y)
print(X_train.shape, X_test.shape)
print(X_train["Age"].isna().sum(), X_test["Age"].isna().sum())
(712, 7) (179, 7)
137 40
age_fill = X_train["Age"].median()
print(age_fill, df["Age"].median())
28.5 28.0

Quick check: the test rows have their own median. Why not use it?

print(X_test["Age"].median())
27.0
X_train = X_train.assign(Age=X_train["Age"].fillna(age_fill))
X_test = X_test.assign(Age=X_test["Age"].fillna(age_fill))
print(X_train["Age"].isna().sum(), X_test["Age"].isna().sum())
0 0

Your turn#

Fill the missing Embarked ports with the most common training port.

port_fill = X_train["Embarked"].mode()[0]
X_train = X_train.assign(Embarked=X_train["Embarked"]
                         .fillna(port_fill))
X_test = X_test.assign(Embarked=X_test["Embarked"]
                       .fillna(port_fill))
print(port_fill, X_train.isna().sum().sum())
S 0

Encode text columns as numbers#

Problem: Sex and Embarked are words, and models need numbers.

cats = ["Sex", "Embarked"]
X_train_enc = pd.get_dummies(X_train, columns=cats,
                             drop_first=True, dtype=int)
X_train_enc.head()
Pclass Age SibSp Parch Fare Sex_male Embarked_Q Embarked_S
692 3 28.5 0 0 56.4958 1 0 1
481 2 28.5 0 0 0.0000 1 0 1
527 1 28.5 0 0 221.7792 1 0 1
855 3 18.0 0 1 9.3500 0 0 1
801 2 31.0 1 1 26.2500 0 0 1
X_test_enc = pd.get_dummies(X_test, columns=cats,
                            drop_first=True, dtype=int)
print(list(X_train_enc.columns) == list(X_test_enc.columns))
True

Your turn#

Pclass is a number, but is it really a quantity? One-hot encode it too.

alt = pd.get_dummies(X_train, columns=cats + ["Pclass"],
                     drop_first=True, dtype=int)
print(X_train_enc.shape[1], alt.shape[1])
alt.filter(like="Pclass").head(3)
8 9
Pclass_2 Pclass_3
692 0 1
481 1 0
527 0 0
model = LogisticRegression(max_iter=1000)
model.fit(X_train_enc, y_train)
print(round(model.score(X_test_enc, y_test), 3))
0.804

Common mistake: encoding a few new rows on their own

new = X_test.head(3)
new_enc = pd.get_dummies(new, columns=cats, drop_first=True,
                         dtype=int)
model.predict(new_enc)
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[19], line 4
      1 new = X_test.head(3)
      2 new_enc = pd.get_dummies(new, columns=cats, drop_first=True,
      3                          dtype=int)
----> 4 model.predict(new_enc)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\linear_model\_base.py:414, in LinearClassifierMixin.predict(self, X)
    412 check_same_namespace(X, self, attribute="coef_", method="predict")
    413 xp, _, device_ = get_namespace_and_device(X)
--> 414 scores = self.decision_function(X)
    415 if len(scores.shape) == 1:
    416     indices = xp.astype(scores > 0, indexing_dtype(xp))

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\linear_model\_base.py:389, in LinearClassifierMixin.decision_function(self, X)
    386 xp, _ = get_namespace(X)
    387 check_same_namespace(X, self, attribute="coef_", method="decision_function")
--> 389 X = validate_data(self, X, accept_sparse="csr", reset=False)
    390 coef_T = self.coef_.T if self.coef_.ndim == 2 else self.coef_
    391 scores = safe_sparse_dot(X, coef_T, dense_output=True) + self.intercept_

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\validation.py:3016, in validate_data(_estimator, X, y, reset, validate_separately, skip_check_array, **check_params)
   2932 def validate_data(
   2933     _estimator,
   2934     /,
   (...)
   2940     **check_params,
   2941 ):
   2942     """Validate input data and set or check feature names and counts of the input.
   2943 
   2944     This helper function should be used in an estimator that requires input
   (...)
   3014         validated.
   3015     """
-> 3016     _check_feature_names(_estimator, X, reset=reset)
   3017     tags = get_tags(_estimator)
   3018     if y is None and tags.target_tags.required:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\validation.py:2868, in _check_feature_names(estimator, X, reset)
   2865 if not missing_names and not unexpected_names:
   2866     message += "Feature names must be in the same order as they were in fit.\n"
-> 2868 raise ValueError(message)

ValueError: The feature names should match those that were passed during fit.
Feature names seen at fit time, yet now missing:
- Embarked_Q
- Sex_male
print(new_enc.columns.tolist())
full = pd.get_dummies(new, columns=cats, dtype=int)
fixed = full.reindex(columns=X_train_enc.columns, fill_value=0)
model.predict(fixed)
['Pclass', 'Age', 'SibSp', 'Parch', 'Fare', 'Embarked_S']
array([0, 0, 0])

Scale numbers onto a common footing#

Problem: Fare runs into hundreds while Parch stays below 7.

num = ["Age", "Fare", "SibSp", "Parch", "Pclass"]
X_train_enc[num].describe().loc[["mean", "std", "max"]].round(1)
Age Fare SibSp Parch Pclass
mean 29.6 31.8 0.5 0.4 2.3
std 13.0 48.1 1.1 0.8 0.8
max 80.0 512.3 8.0 6.0 3.0
from sklearn.preprocessing import StandardScaler
scaler = StandardScaler().set_output(transform="pandas")
train_sc = scaler.fit_transform(X_train_enc[num])
test_sc = scaler.transform(X_test_enc[num])
train_sc.head().round(2)
Age Fare SibSp Parch Pclass
692 -0.08 0.51 -0.47 -0.47 0.83
481 -0.08 -0.66 -0.47 -0.47 -0.37
527 -0.08 3.96 -0.47 -0.47 -1.57
855 -0.89 -0.47 -0.47 0.73 0.83
801 0.11 -0.12 0.48 0.73 -0.37
train_all = X_train_enc.copy()
train_all[num] = train_sc
test_all = X_test_enc.copy()
test_all[num] = test_sc
scaled_model = LogisticRegression().fit(train_all, y_train)
print(round(scaled_model.score(test_all, y_test), 3))
0.804

Quick check: the training means are now 0. Will the test means be 0 too?

means = pd.DataFrame({"train": train_sc.mean(),
                      "test": test_sc.mean()})
means.round(2)
train test
Age 0.0 -0.04
Fare -0.0 0.04
SibSp -0.0 0.14
Parch -0.0 -0.05
Pclass -0.0 -0.00

Common mistake: transforming with a scaler that was never fitted

fresh = StandardScaler()
fresh.transform(X_test_enc[num])
---------------------------------------------------------------------------
NotFittedError                            Traceback (most recent call last)
Cell In[25], line 2
      1 fresh = StandardScaler()
----> 2 fresh.transform(X_test_enc[num])

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\_data.py:1108, in StandardScaler.transform(self, X, copy)
   1093 """Perform standardization by centering and scaling.
   1094 
   1095 Parameters
   (...)
   1105     Transformed array.
   1106 """
   1107 xp, _, X_device = get_namespace_and_device(X)
-> 1108 check_is_fitted(self)
   1110 copy = copy if copy is not None else self.copy
   1111 X = validate_data(
   1112     self,
   1113     X,
   (...)
   1119     ensure_all_finite="allow-nan",
   1120 )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\validation.py:1721, in check_is_fitted(estimator, attributes, msg, all_or_any)
   1718     return
   1720 if not _is_fitted(estimator, attributes, all_or_any):
-> 1721     raise NotFittedError(msg % {"name": type(estimator).__name__})

NotFittedError: This StandardScaler instance is not fitted yet. Call 'fit' with appropriate arguments before using this estimator.
fresh.fit(X_train_enc[num])
print(fresh.mean_.round(2))
[29.56 31.82  0.49  0.39  2.31]

Wrap every step in a Pipeline#

Problem: many manual steps are easy to forget or run out of order.

from sklearn.compose import ColumnTransformer
from sklearn.impute import SimpleImputer
from sklearn.pipeline import make_pipeline
from sklearn.preprocessing import OneHotEncoder
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),
                          ("cat", cat_steps, cats)])
pipe = make_pipeline(prep, LogisticRegression(max_iter=1000))
raw_train, raw_test = train_test_split(
    X, test_size=0.2, random_state=42, stratify=y)
pipe.fit(raw_train, y_train)
print(round(pipe.score(raw_test, y_test), 3))
0.804
pipe.predict(X_test.head(3))
array([0, 0, 0])
prepared = pipe[0].transform(raw_test.head(3))
cols = pipe[0].get_feature_names_out()
pd.DataFrame(prepared, columns=cols, index=raw_test.index[:3]).round(2).T
565 160 553
num__Age -0.43 1.11 -0.58
num__Fare -0.16 -0.33 -0.51
num__SibSp 1.42 -0.47 -0.47
num__Parch -0.47 0.73 -0.47
num__Pclass 0.83 0.83 0.83
cat__Sex_female 0.00 0.00 0.00
cat__Sex_male 1.00 1.00 1.00
cat__Embarked_C 0.00 0.00 1.00
cat__Embarked_Q 0.00 0.00 0.00
cat__Embarked_S 1.00 1.00 0.00

Common mistake: a column name with a typo inside the pipeline

bad = ColumnTransformer([("num", num_steps, ["age", "Fare"])])
bad.fit(raw_train)
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\_indexing.py:411, in _get_column_indices(X, key)
    410 try:
--> 411     return [column_names.index(col) for col in selected_columns]
    412 except ValueError as e:

ValueError: 'age' is not in list

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

ValueError                                Traceback (most recent call last)
Cell In[32], line 2
      1 bad = ColumnTransformer([("num", num_steps, ["age", "Fare"])])
----> 2 bad.fit(raw_train)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\compose\_column_transformer.py:924, in ColumnTransformer.fit(self, X, y, **params)
    921 _raise_for_params(params, self, "fit")
    922 # we use fit_transform to make sure to set sparse_output_ (for which we
    923 # need the transformed data) to have consistent output type in predict
--> 924 self.fit_transform(X, y=y, **params)
    925 return self

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: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\compose\_column_transformer.py:969, in ColumnTransformer.fit_transform(self, X, y, **params)
    966 self._validate_transformers()
    967 n_samples = _num_samples(X)
--> 969 self._validate_column_callables(X)
    970 self._validate_remainder(X)
    972 # ``params`` is empty unless routing is enabled (guaranteed by
    973 # ``_raise_for_params`` above), so the call below produces a properly
    974 # shaped empty routing in the disabled case.

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\compose\_column_transformer.py:524, in ColumnTransformer._validate_column_callables(self, X)
    522         columns = columns(X)
    523     all_columns.append(columns)
--> 524     transformer_to_input_indices[name] = _get_column_indices(X, columns)
    526 self._columns = all_columns
    527 self._transformer_to_input_indices = transformer_to_input_indices

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sklearn\utils\_indexing.py:415, in _get_column_indices(X, key)
    413 missing = {*selected_columns} - {*column_names}
    414 msg = f"Some column names are not columns of the dataframe: {missing}"
--> 415 raise ValueError(msg) from e

ValueError: Some column names are not columns of the dataframe: {'age'}
good = ColumnTransformer([("num", num_steps, ["Age", "Fare"])])
print(good.fit_transform(raw_train).shape)
(712, 2)

Your turn#

Score the pipeline with 5-fold cross-validation on the training rows.

from sklearn.model_selection import cross_val_score
cv = cross_val_score(pipe, raw_train, y_train, cv=5)
print(cv.round(3), round(cv.mean(), 3))
[0.79  0.762 0.803 0.817 0.81 ] 0.796

Mini-project: what drives the model's predictions?#

Read the fitted coefficients back into a pandas Series and rank them.

names = pipe[0].get_feature_names_out()
coefs = pipe[-1].coef_[0]
weights = pd.Series(coefs, index=names).round(2)
weights.sort_values()
cat__Sex_male     -1.32
num__Pclass       -0.92
num__Age          -0.50
cat__Embarked_S   -0.36
num__SibSp        -0.26
num__Parch        -0.07
cat__Embarked_C    0.03
num__Fare          0.10
cat__Embarked_Q    0.30
cat__Sex_female    1.30
dtype: float64
top = weights.abs().sort_values(ascending=False).head(3)
print(top)
cat__Sex_male      1.32
cat__Sex_female    1.30
num__Pclass        0.92
dtype: float64
sample = X_test.head(5)
probs = pipe.predict_proba(sample)[:, 1].round(2)
sample.assign(chance=probs, actual=y_test.head(5))
Pclass Sex Age SibSp Parch Fare Embarked chance actual
565 3 male 24.0 2 0 24.1500 S 0.07 0
160 3 male 44.0 0 1 16.1000 S 0.05 0
553 3 male 22.0 0 0 7.2250 C 0.16 1
860 3 male 41.0 2 0 14.1083 S 0.04 0
241 3 female 28.5 1 0 15.5000 Q 0.67 1
import matplotlib.pyplot as plt
ax = weights.sort_values().plot(kind="barh", figsize=(7, 4))
ax.set_title("What pushes predicted Titanic survival up or down")
ax.set_xlabel("Coefficient (scaled features)")
ax.set_ylabel("Feature")
plt.show()
No description has been provided for this image
right = pipe.predict(raw_test) == y_test
check = pd.DataFrame({"Sex": raw_test["Sex"], "right": right})
check.groupby("Sex")["right"].agg(["size", "mean"]).round(3)
size mean
Sex
female 61 0.803
male 118 0.805
base = y_test.value_counts(normalize=True).max()
acc = pipe.score(raw_test, y_test)
print(f"Baseline {base:.0%}, model {acc:.0%}")
Baseline 61%, model 80%

Recap#

  • Split X and y first; learn every statistic from training rows
  • Fill gaps with training medians and modes, never test values
  • Encode text with get_dummies; reindex new rows to train columns
  • Fit scalers on train, only transform test
  • A Pipeline with ColumnTransformer does it all in one object

Challenges#

  1. Add family_size = SibSp + Parch + 1 and re-score the pipeline.
  2. Swap StandardScaler for MinMaxScaler. Does accuracy change?
  3. Drop Fare from the features and compare cross-validation scores.

What's next#

Lesson 55: Clustering and Outlier Detection Group similar rows with no target column, and flag the odd ones out.

Found this useful?

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