Mathew K Analytics

Lesson 52 · Pandas Full Course

Feature Engineering with Pandas: Build Features That Boost Accuracy | Pandas Course #52

In this lesson you'll be able to: Build new features by combining columns with arithmetic Pull features out of text, and bin numbers into bands Create…

📓 Full notebook

Download .ipynb

Pandas 52: Feature Engineering with Pandas#

In this lesson you'll be able to:

  • Build new features by combining columns with arithmetic
  • Pull features out of text, and bin numbers into bands
  • Create group-aware features with groupby and transform
  • Extract hour, weekday and duration features from dates
  • Show that good features make a model more accurate Data: the Titanic passenger list, plus New York taxi trips for dates

Warm-up: scaling, from lesson 51#

Lesson 51 scaled numbers. Here it is on Titanic fares.

import pandas as pd
import numpy as np
df = pd.read_csv("data/pandas/titanic.csv")
print(df.shape)
df[["Name", "SibSp", "Parch", "Ticket", "Fare", "Cabin"]].head(3)
(891, 12)
Name SibSp Parch Ticket Fare Cabin
0 Braund, Mr. Owen Harris 1 0 A/5 21171 7.2500 NaN
1 Cumings, Mrs. John Bradley (Florence Briggs Th... 1 0 PC 17599 71.2833 C85
2 Heikkinen, Miss. Laina 0 0 STON/O2. 3101282 7.9250 NaN
fare_log = np.log1p(df["Fare"])
fare_z = (fare_log - fare_log.mean()) / fare_log.std()
print(round(df["Fare"].skew(), 2), round(fare_z.skew(), 2))
4.79 0.39

Combine columns with arithmetic#

Problem: the useful fact is spread across several columns.

df["family_size"] = df["SibSp"] + df["Parch"] + 1
df[["SibSp", "Parch", "family_size"]].head()
SibSp Parch family_size
0 1 0 2
1 1 0 2
2 0 0 1
3 1 0 2
4 0 0 1
df["is_alone"] = (df["family_size"] == 1).astype(int)
df.groupby("is_alone")["Survived"].agg(["size", "mean"]).round(3)
size mean
is_alone
0 354 0.506
1 537 0.304
by_fam = df.groupby("family_size")["Survived"]
by_fam.agg(["size", "mean"]).round(2)
size mean
family_size
1 537 0.30
2 161 0.55
3 102 0.58
4 29 0.72
5 15 0.20
6 22 0.14
7 12 0.33
8 6 0.00
11 7 0.00

Quick check: is the Fare column per person or per ticket?

sage = df[df["Name"].str.startswith("Sage,")]
sage[["Name", "Ticket", "Fare"]]
Name Ticket Fare
159 Sage, Master. Thomas Henry CA. 2343 69.55
180 Sage, Miss. Constance Gladys CA. 2343 69.55
201 Sage, Mr. Frederick CA. 2343 69.55
324 Sage, Mr. George John Jr CA. 2343 69.55
792 Sage, Miss. Stella Anna CA. 2343 69.55
846 Sage, Mr. Douglas Bullen CA. 2343 69.55
863 Sage, Miss. Dorothy Edith "Dolly" CA. 2343 69.55
df["ticket_size"] = df.groupby("Ticket")["Ticket"].transform("size")
df["fare_pp"] = df["Fare"] / df["ticket_size"]
df.loc[sage.index, ["Fare", "ticket_size", "fare_pp"]].round(2)
Fare ticket_size fare_pp
159 69.55 7 9.94
180 69.55 7 9.94
201 69.55 7 9.94
324 69.55 7 9.94
792 69.55 7 9.94
846 69.55 7 9.94
863 69.55 7 9.94

Your turn#

Compare median Fare and median fare_pp for each class.

cols = ["Fare", "fare_pp"]
df.groupby("Pclass")[cols].median().round(2)
Fare fare_pp
Pclass
1 60.29 35.25
2 14.25 13.00
3 8.05 7.85

Pull features out of text#

Problem: names and cabin codes hold facts a model can't read as text.

Common mistake: a regex with no capture group

df["Name"].str.extract(r",\s*[^.]+\.")
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[10], line 1
----> 1 df["Name"].str.extract(r",\s*[^.]+\.")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\strings\accessor.py:142, in forbid_nonstring_types.<locals>._forbid_nonstring_types.<locals>.wrapper(self, *args, **kwargs)
    137     msg = (
    138         f"Cannot use .str.{func_name} with values of "
    139         f"inferred dtype '{self._inferred_dtype}'."
    140     )
    141     raise TypeError(msg)
--> 142 return func(self, *args, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\strings\accessor.py:3425, in StringMethods.extract(self, pat, flags, expand)
   3423 regex = re.compile(pat, flags=flags)
   3424 if regex.groups == 0:
-> 3425     raise ValueError("pattern contains no capture groups")
   3427 if not expand and regex.groups > 1 and isinstance(self._data, ABCIndex):
   3428     raise ValueError("only one regex group is supported with Index")

ValueError: pattern contains no capture groups
df["title"] = df["Name"].str.extract(r",\s*([^.]+)\.")
df["title"].value_counts().head(8)
title
Mr        517
Miss      182
Mrs       125
Master     40
Dr          7
Rev         6
Major       2
Mlle        2
Name: count, dtype: int64
common = ["Mr", "Mrs", "Miss", "Master"]
df["title"] = df["title"].where(df["title"].isin(common), "Rare")
df.groupby("title")["Survived"].agg(["size", "mean"]).round(3)
size mean
title
Master 40 0.575
Miss 182 0.698
Mr 517 0.157
Mrs 125 0.792
Rare 27 0.444
df["deck"] = df["Cabin"].str[0]
df["has_cabin"] = df["Cabin"].notna().astype(int)
df.groupby("has_cabin")["Survived"].agg(["size", "mean"]).round(3)
size mean
has_cabin
0 687 0.300
1 204 0.667

Quick check: which deck letter is most common among recorded cabins?

df["deck"].value_counts().head(4)
deck
C    59
B    47
D    33
E    32
Name: count, dtype: int64
by_deck = df.groupby("deck")["Survived"].agg(["size", "mean"])
by_deck[by_deck["size"] >= 10].round(2)
size mean
deck
A 15 0.47
B 47 0.74
C 59 0.59
D 33 0.76
E 32 0.75
F 13 0.62
df["surname"] = df["Name"].str.split(",").str[0]
print(df["surname"].nunique())
df["surname"].value_counts().head(3)
667
surname
Andersson    9
Sage         7
Johnson      6
Name: count, dtype: int64

Bin numbers into bands#

Problem: the effect of age is not a straight line.

Common mistake: one label too many for pd.cut

edges = [0, 12, 18, 60, 80]
names = ["Child", "Teen", "Adult", "Senior", "Elder"]
pd.cut(df["Age"], bins=edges, labels=names)
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[17], line 3
      1 edges = [0, 12, 18, 60, 80]
      2 names = ["Child", "Teen", "Adult", "Senior", "Elder"]
----> 3 pd.cut(df["Age"], bins=edges, labels=names)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\tile.py:281, in cut(x, bins, right, labels, retbins, precision, include_lowest, duplicates, ordered)
    278     if not bins.is_monotonic_increasing:
    279         raise ValueError("bins must increase monotonically.")
--> 281 fac, bins = _bins_to_cuts(
    282     x_idx,
    283     bins,
    284     right=right,
    285     labels=labels,
    286     precision=precision,
    287     include_lowest=include_lowest,
    288     duplicates=duplicates,
    289     ordered=ordered,
    290 )
    292 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 = ["Child", "Teen", "Adult", "Senior"]
df["age_band"] = pd.cut(df["Age"], bins=edges, labels=names)
df["age_band"].value_counts(dropna=False)
age_band
Adult     553
NaN       177
Teen       70
Child      69
Senior     22
Name: count, dtype: int64
by_band = df.groupby("age_band", observed=True)["Survived"]
by_band.agg(["size", "mean"]).round(3)
size mean
age_band
Child 69 0.580
Teen 70 0.429
Adult 553 0.389
Senior 22 0.227
df["fare_q"] = pd.qcut(df["fare_pp"], q=4, labels=["Q1", "Q2",
                                                    "Q3", "Q4"])
df.groupby("fare_q", observed=True)["Survived"].mean().round(3)
fare_q
Q1    0.247
Q2    0.256
Q3    0.387
Q4    0.646
Name: Survived, dtype: float64

Your turn#

Bin family_size into Alone (1), Small (2-4) and Large (5+). Compare survival.

fam_names = ["Alone", "Small", "Large"]
df["family_band"] = pd.cut(df["family_size"], bins=[0, 1, 4, 11],
                           labels=fam_names)
df.groupby("family_band", observed=True)["Survived"].mean().round(3)
family_band
Alone    0.304
Small    0.579
Large    0.161
Name: Survived, dtype: float64

Group-aware features with transform#

Problem: a value means more when compared with similar rows.

df["age_missing"] = df["Age"].isna().astype(int)
title_med = df.groupby("title")["Age"].transform("median")
df["age_filled"] = df["Age"].fillna(title_med)
df.groupby("title")["age_filled"].median()
title
Master     3.5
Miss      21.0
Mr        30.0
Mrs       35.0
Rare      44.5
Name: age_filled, dtype: float64

Quick check: why fill by title rather than one overall median?

print(df["Age"].median())
print(df.loc[df["title"] == "Master", "Age"].max())
28.0
12.0
class_fare = df.groupby("Pclass")["fare_pp"].transform("median")
df["fare_vs_class"] = (df["fare_pp"] / class_fare).round(2)
df[["Pclass", "fare_pp", "fare_vs_class"]].head()
Pclass fare_pp fare_vs_class
0 3 7.2500 0.92
1 1 71.2833 2.02
2 3 7.9250 1.01
3 1 26.5500 0.75
4 3 8.0500 1.02
grp = df.groupby("Pclass")["fare_pp"]
df["fare_rank"] = grp.rank(pct=True).round(2)
df.groupby("Pclass")["fare_rank"].agg(["min", "max"])
min max
Pclass
1 0.01 1.00
2 0.02 0.99
3 0.01 1.00

Your turn#

Add 'age_vs_title': each age_filled minus its title's mean age_filled.

title_mean = df.groupby("title")["age_filled"].transform("mean")
df["age_vs_title"] = df["age_filled"] - title_mean
df[["title", "age_filled", "age_vs_title"]].head().round(1)
title age_filled age_vs_title
0 Mr 22.0 -9.8
1 Mrs 38.0 2.2
2 Miss 26.0 4.4
3 Mrs 35.0 -0.8
4 Mr 35.0 3.2

Features from dates and times#

Problem: a timestamp hides the hour, weekday and duration.

taxis = pd.read_csv("data/pandas/taxis.csv")
taxis[["pickup", "dropoff", "fare", "tip", "payment"]].head(3)
pickup dropoff fare tip payment
0 2019-03-23 20:21:09 2019-03-23 20:27:24 7.0 2.15 credit card
1 2019-03-04 16:11:55 2019-03-04 16:19:00 5.0 0.00 cash
2 2019-03-27 17:53:01 2019-03-27 18:00:25 7.5 2.36 credit card

Common mistake: using .dt on a column that is still text

taxis["pickup"].dt.hour
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In[28], line 1
----> 1 taxis["pickup"].dt.hour

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py:6194, in NDFrame.__getattr__(self, name)
   6187 if (
   6188     name not in self._internal_names_set
   6189     and name not in self._metadata
   6190     and name not in self._accessors
   6191     and self._info_axis._can_hold_identifiers_and_holds_name(name)
   6192 ):
   6193     return self[name]
-> 6194 return object.__getattribute__(self, name)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\accessor.py:230, in Accessor.__get__(self, obj, cls)
    227 if obj is None:
    228     # we're accessing the attribute of the class, i.e., Dataset.geo
    229     return self._accessor
--> 230 return self._accessor(obj)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\accessors.py:698, in CombinedDatetimelikeProperties.__new__(cls, data)
    695 elif isinstance(data.dtype, PeriodDtype):
    696     return PeriodProperties(data, orig)
--> 698 raise AttributeError("Can only use .dt accessor with datetimelike values")

AttributeError: Can only use .dt accessor with datetimelike values
for col in ["pickup", "dropoff"]:
    taxis[col] = pd.to_datetime(taxis[col])
taxis["hour"] = taxis["pickup"].dt.hour
taxis["weekday"] = taxis["pickup"].dt.day_name()
taxis["weekend"] = taxis["pickup"].dt.dayofweek >= 5
taxis[["pickup", "hour", "weekday", "weekend"]].head(3)
pickup hour weekday weekend
0 2019-03-23 20:21:09 20 Saturday True
1 2019-03-04 16:11:55 16 Monday False
2 2019-03-27 17:53:01 17 Wednesday False
trip = taxis["dropoff"] - taxis["pickup"]
taxis["minutes"] = trip.dt.total_seconds() / 60
taxis["tip_pct"] = taxis["tip"] / taxis["fare"] * 100
taxis[["minutes", "tip_pct"]].describe().round(1)
minutes tip_pct
count 6433.0 6433.0
mean 14.3 16.9
std 11.6 14.6
min 0.0 0.0
25% 6.5 0.0
50% 10.9 21.1
75% 18.5 28.3
max 107.7 93.3
per_hour = taxis["hour"].value_counts().sort_index()
ax = per_hour.plot(kind="bar", figsize=(9, 3.5), rot=0)
ax.set_title("Taxi pickups by hour of day")
ax.set_xlabel("Hour of pickup")
ax.set_ylabel("Number of trips")
No description has been provided for this image
taxis["weekday"].value_counts()
weekday
Friday       1115
Saturday     1046
Wednesday     966
Thursday      905
Sunday        868
Tuesday       825
Monday        708
Name: count, dtype: int64
taxis["rush"] = taxis["hour"].between(16, 19)
taxis.groupby("rush")["minutes"].median().round(1)
rush
False    10.8
True     11.2
Name: minutes, dtype: float64

Quick check: do cash riders really tip less?

taxis.groupby("payment")["tip_pct"].agg(["size", "median"])
size median
payment
cash 1812 0.0
credit card 4577 25.5
card = taxis[taxis["payment"] == "credit card"]
card.groupby("weekend")["tip_pct"].median().round(1)
weekend
False    25.6
True     25.4
Name: tip_pct, dtype: float64

Make the features model-ready#

Problem: models need numbers only, and no peeking at the answer.

feats = ["Pclass", "Sex", "title", "family_size", "is_alone",
         "fare_pp", "has_cabin", "age_filled", "age_missing"]
Xf = pd.get_dummies(df[feats], drop_first=True, dtype=int)
print(Xf.shape)
Xf.dtypes.value_counts()
(891, 12)
int64      10
float64     2
Name: count, dtype: int64
df["class_sex"] = df["Pclass"].astype(str) + "_" + df["Sex"]
df.groupby("class_sex")["Survived"].mean().round(2)
class_sex
1_female    0.97
1_male      0.37
2_female    0.92
2_male      0.16
3_female    0.50
3_male      0.14
Name: Survived, dtype: float64

Your turn#

Make an is_child flag (age_filled under 13). Survival by is_child and Sex?

df["is_child"] = (df["age_filled"] < 13).astype(int)
kids = df.groupby(["is_child", "Sex"])["Survived"].mean()
kids.unstack().round(2)
Sex female male
is_child
0 0.76 0.16
1 0.59 0.56

Leakage: never build a feature from the answer, like Survived.

Quick check: which single feature moves most with Survived?

corr = Xf.corrwith(df["Survived"])
corr.sort_values(key=abs, ascending=False).round(3).head(6)
title_Mr     -0.549
Sex_male     -0.543
title_Mrs     0.339
Pclass       -0.338
title_Miss    0.327
has_cabin     0.317
dtype: float64

Mini-project: do engineered features beat the raw columns?#

Plan: same model, raw features vs engineered features, scored 5 times.

from sklearn.linear_model import LogisticRegression
from sklearn.model_selection import cross_val_score
model = LogisticRegression(max_iter=1000)
raw = pd.get_dummies(df[["Pclass", "Sex", "Age", "Fare"]],
                     drop_first=True, dtype=int)
raw["Age"] = raw["Age"].fillna(raw["Age"].median())
y = df["Survived"]
raw_acc = cross_val_score(model, raw, y, cv=5).mean()
eng_acc = cross_val_score(model, Xf, y, cv=5).mean()
print(round(raw_acc, 3), round(eng_acc, 3))
0.787 0.825
no_title = Xf.drop(columns=Xf.filter(like="title_").columns)
no_title_acc = cross_val_score(model, no_title, y, cv=5).mean()
print(round(no_title_acc, 3))
0.794
import matplotlib.pyplot as plt
accs = pd.Series({"Raw columns": raw_acc, "Engineered": eng_acc})
ax = accs.plot(kind="bar", rot=0, figsize=(6, 3.5), ylim=(0.7, 0.85))
ax.set_title("Titanic survival model accuracy")
ax.set_xlabel("Feature set")
ax.set_ylabel("Mean accuracy, 5 folds")
No description has been provided for this image
extra = (eng_acc - raw_acc) * len(df)
print(f"About {extra:.0f} more of {len(df)} passengers called right")
About 34 more of 891 passengers called right

Recap#

  • Arithmetic combines columns: family size, fare per person
  • str.extract needs a capture group; pd.cut needs one label per gap
  • transform gives each row its group's figure
  • Convert to datetime, then use .dt for hours, weekdays, durations
  • Never build features from the answer

Challenges#

  1. Add a 'surname_size' feature: passengers sharing each surname.
  2. Use pd.qcut to split age_filled into 5 equal bands; compare survival.
  3. For taxis, find the median trip minutes for each pickup hour.

What's next#

Lesson 53: Splitting Data into Train-Test Sets Test models fairly on data they have never seen.

Found this useful?

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