Mathew K Analytics

Lesson 50 · Pandas Full Course

Encode Categorical Variables in Pandas: One-Hot, Ordinal and Label | Pandas Course #50

In this lesson you'll be able to: Spot categorical columns and explain why models need numbers Encode yes or no columns and labels with map and cat.codes…

📓 Full notebook

Download .ipynb

Pandas 50: Encoding Categorical Variables#

In this lesson you'll be able to:

  • Spot categorical columns and explain why models need numbers
  • Encode yes or no columns and labels with map and cat.codes
  • Keep the real order of ranked categories with ordinal encoding
  • One-hot encode with get_dummies and keep new data aligned
  • Tame high-cardinality columns with frequency and rare grouping Data: the Titanic passenger list and 53,940 diamonds (data/pandas/titanic.csv, diamonds.csv)

Warm-up: tables full of labels#

Lesson 49 flattened JSON into tables. Those tables are full of text labels.

import pandas as pd
df = pd.read_csv("data/pandas/titanic.csv")
records = df.head(2).to_dict("records")
back = pd.json_normalize(records)
back[["Name", "Sex", "Pclass", "Embarked"]]
Name Sex Pclass Embarked
0 Braund, Mr. Owen Harris male 3 S
1 Cumings, Mrs. John Bradley (Florence Briggs Th... female 1 C

Spot the categorical columns#

Problem: find the columns that hold labels, and see why models reject them.

labels = df.select_dtypes(exclude="number")
labels.nunique()
Name        891
Sex           2
Ticket      681
Cabin       147
Embarked      3
dtype: int64

Quick check: Pclass is stored as a number. Is it categorical?

df["Pclass"].value_counts().sort_index()
Pclass
1    216
2    184
3    491
Name: count, dtype: int64

Your turn#

List every column, numeric or not, with fewer than 10 distinct values.

few = df.nunique()
few[few < 10].sort_values()
Survived    2
Sex         2
Pclass      3
Embarked    3
SibSp       7
Parch       7
dtype: int64

Common mistake: giving a model raw text

from sklearn.linear_model import LogisticRegression
model = LogisticRegression()
model.fit(df[["Sex", "Pclass"]], df["Survived"])
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_13900\4178865787.py in ?()
      1 from sklearn.linear_model import LogisticRegression
      2 model = LogisticRegression()
----> 3 model.fit(df[["Sex", "Pclass"]], df["Survived"])

~\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 = df[["Pclass"]].assign(is_male=(df["Sex"] == "male").astype(int))
model.fit(X, df["Survived"])
print(round(model.score(X, df["Survived"]), 3))
0.787

Binary and label encoding#

Problem: turn two-value and few-value columns into simple numbers.

df["is_male"] = (df["Sex"] == "male").astype(int)
df[["Sex", "is_male"]].value_counts()
Sex     is_male
male    1          577
female  0          314
Name: count, dtype: int64
ports = {"S": 0, "C": 1, "Q": 2}
df["port_code"] = df["Embarked"].map(ports)
df["port_code"].value_counts(dropna=False)
port_code
0.0    644
1.0    168
2.0     77
NaN      2
Name: count, dtype: int64

Quick check: what does map do with a label that isn't in the dictionary?

short = {"S": 0, "C": 1}
print(df["Embarked"].map(short).isna().sum())
79
port_cat = df["Embarked"].astype("category")
print(port_cat.cat.categories.tolist())
port_cat.cat.codes.value_counts().sort_index()
['C', 'Q', 'S']
-1      2
 0    168
 1     77
 2    644
Name: count, dtype: int64
back_map = {num: port for port, num in ports.items()}
restored = df["port_code"].map(back_map)
print(restored.equals(df["Embarked"]))
print(back_map)
True
{0: 'S', 1: 'C', 2: 'Q'}

Your turn#

Encode Sex with cat.codes. Which number did male get, and why?

sex = df["Sex"].astype("category")
print(dict(enumerate(sex.cat.categories)))
{0: 'female', 1: 'male'}

Ordinal encoding: keep the real order#

Problem: some labels have a ranking, and the numbers must respect it.

gems = pd.read_csv("data/pandas/diamonds.csv")
print(gems.shape)
gems["cut"].value_counts()
(53940, 10)
cut
Ideal        21551
Premium      13791
Very Good    12082
Good          4906
Fair          1610
Name: count, dtype: int64
alpha = gems["cut"].astype("category")
dict(enumerate(alpha.cat.categories))
{0: 'Fair', 1: 'Good', 2: 'Ideal', 3: 'Premium', 4: 'Very Good'}

Common mistake: ranking an unordered category

(alpha > "Good").sum()
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[16], line 1
----> 1 (alpha > "Good").sum()

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:58, in OpsMixin.__gt__(self, other)
     56 @unpack_zerodim_and_defer("__gt__")
     57 def __gt__(self, other):
---> 58     return self._cmp_method(other, operator.gt)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\series.py:6735, in Series._cmp_method(self, other, op)
   6732 lvalues = self._values
   6733 rvalues = extract_array(other, extract_numpy=True, extract_range=True)
-> 6735 res_values = ops.comparison_op(lvalues, rvalues, op)
   6737 return self._construct_result(res_values, name=res_name, other=other)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\array_ops.py:341, in comparison_op(left, right, op)
    332         raise ValueError(
    333             "Lengths must match to compare", lvalues.shape, rvalues.shape
    334         )
    336 if should_extension_dispatch(lvalues, rvalues) or (
    337     (isinstance(rvalues, (Timedelta, BaseOffset, Timestamp)) or right is NaT)
    338     and lvalues.dtype != object
    339 ):
    340     # Call the method on lvalues
--> 341     res_values = op(lvalues, rvalues)
    343 # TODO: but not pd.NA?
    344 elif (is_scalar(rvalues) or rvalues_is_zerodim) and isna(rvalues):
    345     # numpy does not like comparisons vs None

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\arrays\categorical.py:138, in _cat_compare_op.<locals>.func(self, other)
    136 if not self.ordered:
    137     if opname in ["__lt__", "__gt__", "__le__", "__ge__"]:
--> 138         raise TypeError(
    139             "Unordered Categoricals can only compare equality or not"
    140         )
    141 if isinstance(other, Categorical):
    142     # Two Categoricals can only be compared if the categories are
    143     # the same (maybe up to ordering, depending on ordered)
    145     msg = "Categoricals can only be compared if 'categories' are the same."

TypeError: Unordered Categoricals can only compare equality or not
order = ["Fair", "Good", "Very Good", "Premium", "Ideal"]
cut_type = pd.CategoricalDtype(order, ordered=True)
gems["cut"] = gems["cut"].astype(cut_type)
print((gems["cut"] > "Good").sum())
print(dict(enumerate(gems["cut"].cat.categories)))
47424
{0: 'Fair', 1: 'Good', 2: 'Very Good', 3: 'Premium', 4: 'Ideal'}
gems["cut_code"] = gems["cut"].cat.codes
gems[["cut", "cut_code"]].drop_duplicates().sort_values("cut_code")
cut cut_code
8 Fair 0
2 Good 1
5 Very Good 2
1 Premium 3
0 Ideal 4

Quick check: better cuts should cost more. Do they?

by_cut = gems.groupby("cut", observed=True)
by_cut[["price", "carat"]].median()
price carat
cut
Fair 3282.0 1.00
Good 3050.5 0.82
Very Good 2648.0 0.71
Premium 3185.0 0.86
Ideal 1810.0 0.54
one_carat = gems[gems["carat"].between(0.9, 1.1)]
fair_test = one_carat.groupby("cut_code")["price"].median()
fair_test
cut_code
0    3730.0
1    4312.0
2    4691.0
3    4670.0
4    5220.0
Name: price, dtype: float64

Your turn#

Ordinal-encode clarity, worst to best: I1, SI2, SI1, VS2, VS1, VVS2, VVS1, IF.

grades = ["I1", "SI2", "SI1", "VS2", "VS1", "VVS2", "VVS1", "IF"]
clar = pd.CategoricalDtype(grades, ordered=True)
gems["clarity_code"] = gems["clarity"].astype(clar).cat.codes
gems.groupby("clarity_code")["carat"].median()
clarity_code
0    1.12
1    1.01
2    0.76
3    0.63
4    0.57
5    0.44
6    0.39
7    0.35
Name: carat, dtype: float64

One-hot encoding with get_dummies#

Problem: unordered labels need numbers that don't imply a ranking.

dummies = pd.get_dummies(df["Embarked"], prefix="port", dtype=int)
pd.concat([df["Embarked"], dummies], axis=1).head()
Embarked port_C port_Q port_S
0 S 0 0 1
1 C 1 0 0
2 S 0 0 1
3 S 0 0 1
4 S 0 0 1
full = pd.get_dummies(df["Embarked"], prefix="port", dtype=int,
                      dummy_na=True)
full.sum()
port_C      168
port_Q       77
port_S      644
port_nan      2
dtype: int64
slim = pd.get_dummies(df["Embarked"], prefix="port", dtype=int,
                      drop_first=True)
slim.head(3)
port_Q port_S
0 0 1
1 0 0
2 0 1

Common mistake: a column name with the wrong capital letter

pd.get_dummies(df, columns=["embarked"])
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
Cell In[25], line 1
----> 1 pd.get_dummies(df, columns=["embarked"])

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\encoding.py:171, in get_dummies(data, prefix, prefix_sep, dummy_na, columns, sparse, drop_first, dtype)
    169     raise TypeError("Input must be a list-like for parameter `columns`")
    170 else:
--> 171     data_to_encode = data[columns]
    173 # validate prefixes and separator to avoid silently dropping cols
    174 def check_len(item, name: str) -> None:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:4384, in DataFrame.__getitem__(self, key)
   4382     if is_iterator(key):
   4383         key = list(key)
-> 4384     indexer = self.columns._get_indexer_strict(key, "columns")[1]
   4386 # take() does not accept boolean indexers
   4387 if getattr(indexer, "dtype", None) == bool:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:6302, in Index._get_indexer_strict(self, key, axis_name)
   6299 else:
   6300     keyarr, indexer, new_indexer = self._reindex_non_unique(keyarr)
-> 6302 self._raise_if_missing(keyarr, indexer, axis_name)
   6304 keyarr = self.take(indexer)
   6305 if isinstance(key, Index):
   6306     # GH 42790 - Preserve name from an Index

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:6352, in Index._raise_if_missing(self, key, indexer, axis_name)
   6350 if nmissing:
   6351     if nmissing == len(indexer):
-> 6352         raise KeyError(f"None of [{key}] are in the [{axis_name}]")
   6354     not_found = list(ensure_index(key)[missing_mask.nonzero()[0]].unique())
   6355     raise KeyError(f"{not_found} not in index")

KeyError: "None of [Index(['embarked'], dtype='str')] are in the [columns]"
encoded = pd.get_dummies(df, columns=["Sex", "Embarked"], dtype=int)
print(encoded.shape)
print([c for c in encoded.columns if "_" in c][-5:])
(891, 17)
['Sex_female', 'Sex_male', 'Embarked_C', 'Embarked_Q', 'Embarked_S']
everything = pd.get_dummies(df)
print(everything.shape)
(891, 1733)

Your turn#

One-hot encode Pclass. Careful: it's stored as a number.

cls = pd.get_dummies(df[["Pclass"]], columns=["Pclass"], dtype=int)
cls.sum()
Pclass_1    216
Pclass_2    184
Pclass_3    491
dtype: int64

Keep new data aligned#

Problem: new data may lack some labels, or bring ones we've never seen.

train = pd.get_dummies(df["Embarked"], prefix="port", dtype=int)
batch = df[df["Embarked"] == "Q"].head(3)
new = pd.get_dummies(batch["Embarked"], prefix="port", dtype=int)
print(train.columns.tolist())
print(new.columns.tolist())
['port_C', 'port_Q', 'port_S']
['port_Q']
aligned = new.reindex(columns=train.columns, fill_value=0)
aligned
port_C port_Q port_S
5 0 1 0
16 0 1 0
22 0 1 0
from sklearn.preprocessing import OneHotEncoder
ports_seen = df[["Embarked"]].dropna()
enc = OneHotEncoder(sparse_output=False).fit(ports_seen)
print(enc.categories_)
[array(['C', 'Q', 'S'], dtype=object)]

Common mistake: a category the encoder has never seen

odd = pd.DataFrame({"Embarked": ["Unknown"]})
enc.transform(odd)
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[32], line 2
      1 odd = pd.DataFrame({"Embarked": ["Unknown"]})
----> 2 enc.transform(odd)

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 ['Unknown'] in column 0 during transform
safe = OneHotEncoder(sparse_output=False, handle_unknown="ignore")
safe.fit(ports_seen)
print(safe.transform(odd))
[[0. 0. 0.]]
names = safe.get_feature_names_out()
grid = safe.transform(df[["Embarked"]].fillna("Unknown").head(3))
pd.DataFrame(grid, columns=names)
Embarked_C Embarked_Q Embarked_S
0 0.0 0.0 1.0
1 1.0 0.0 0.0
2 0.0 0.0 1.0

Tame high-cardinality columns#

Problem: columns with hundreds of labels would explode into hundreds of columns.

counts = df["Ticket"].value_counts()
df["ticket_count"] = df["Ticket"].map(counts)
df["ticket_count"].value_counts().sort_index()
ticket_count
1    547
2    188
3     63
4     44
5     10
6     18
7     21
Name: count, dtype: int64
df["deck"] = df["Cabin"].str[0].fillna("Unknown")
df["deck"].value_counts()
deck
Unknown    687
C           59
B           47
D           33
E           32
A           15
F           13
G            4
T            1
Name: count, dtype: int64
deck_counts = df["deck"].value_counts()
rare = deck_counts[deck_counts < 20].index
df["deck"] = df["deck"].where(~df["deck"].isin(rare), "Other")
df["deck"].value_counts()
deck
Unknown    687
C           59
B           47
Other       33
D           33
E           32
Name: count, dtype: int64
deck_rate = df.groupby("deck")["Survived"].mean()
deck_rate.sort_values(ascending=False).round(2)
deck
D          0.76
E          0.75
B          0.74
C          0.59
Other      0.52
Unknown    0.30
Name: Survived, dtype: float64

Your turn#

Does ticket_count relate to survival? Show the survival rate for each count.

share = df.groupby("ticket_count")["Survived"].agg(["size", "mean"])
share.round(2)
size mean
ticket_count
1 547 0.30
2 188 0.57
3 63 0.70
4 44 0.50
5 10 0.00
6 18 0.00
7 21 0.24

Mini-project: which encoded feature tracks survival?#

Plan: build an all-numeric table, correlate with Survived, chart, test a model.

base = df[["Survived", "Pclass", "is_male", "ticket_count"]]
ports_1h = pd.get_dummies(df["Embarked"], prefix="port", dtype=int)
decks_1h = pd.get_dummies(df["deck"], prefix="deck", dtype=int)
features = pd.concat([base, ports_1h, decks_1h], axis=1)
print(features.shape)
print(features.dtypes.value_counts())
(891, 13)
int64    13
Name: count, dtype: int64
corr = features.corr()["Survived"].drop("Survived")
corr.sort_values().round(2)
is_male        -0.54
Pclass         -0.34
deck_Unknown   -0.32
port_S         -0.16
port_Q          0.00
ticket_count    0.04
deck_Other      0.05
deck_C          0.11
deck_E          0.15
deck_D          0.15
port_C          0.17
deck_B          0.18
Name: Survived, dtype: float64
import matplotlib.pyplot as plt
ax = corr.sort_values().plot(kind="barh", figsize=(7, 5))
ax.set_title("Correlation of encoded features with survival")
ax.set_xlabel("Correlation with Survived")
ax.set_ylabel("Feature")
plt.show()
No description has been provided for this image
from sklearn.model_selection import train_test_split
X = features.drop(columns="Survived")
y = features["Survived"]
X_tr, X_te, y_tr, y_te = train_test_split(X, y, test_size=0.25,
                                          random_state=42)
model = LogisticRegression(max_iter=1000).fit(X_tr, y_tr)
print(round(model.score(X_te, y_te), 3))
0.785
top = corr.abs().idxmax()
print(f"Strongest feature: {top} ({corr[top]:.2f})")
Strongest feature: is_male (-0.54)

Recap#

  • Models need numbers: find label columns with select_dtypes
  • Binary and label codes: comparisons, map and cat.codes
  • Ranked labels: an ordered CategoricalDtype keeps the true order
  • Unordered labels: get_dummies, then reindex new data to match
  • Many labels: frequency encoding and pooling rare values

Challenges#

  1. Ordinal-encode diamond color (J worst to D best) and compare median prices.
  2. Frequency-encode Cabin and compare survival for shared cabins.
  3. Add one-hot Pclass to the features and see if the model improves.

What's next#

Lesson 51: Scaling and Normalizing Data Put numeric features on comparable scales before modelling.

Found this useful?

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