Transforming columns

Intermediate

Introduction

Raw columns are rarely what an analysis needs. You compute totals from prices and quantities, translate codes into names, turn text into numbers, rename cryptic headers, and group ages into bands. pandas has a small set of methods that cover nearly all of this, and choosing the right one keeps code short and fast.

How it works

Arithmetic between columns works value by value, and assigning to a new name adds a column:

df = pd.DataFrame({"item": ["pen", "book", "lamp"], "price": [20, 350, 900], "qty": [3, 1, 2]})
df["total"] = df["price"] * df["qty"]
df["total"].tolist()                                     # [60, 350, 1800]
df2 = df.assign(total=lambda d: d["price"] * d["qty"])   # a new DataFrame; df is unchanged

assign does the same but returns a new table, which suits method chains (a later lesson).

map translates values through a dictionary. Any value missing from the dictionary becomes NaN, which is a frequent source of silent gaps, so check with isna().sum() afterwards:

df["item"].map({"pen": "small", "book": "medium"}).tolist()   # ['small', 'medium', nan]

apply calls a function on every value: df["item"].apply(str.upper). On a DataFrame, df.apply(f, axis=1) calls f once per row. apply is flexible but runs Python code for each value, so prefer vectorised arithmetic and the built-in methods whenever they can do the job.

astype changes a column's type: df["qty"].astype(float), or astype(str) to treat numbers as text. It fails on values that cannot be converted, such as "n/a" in a column you convert to int. rename(columns={"price": "cost"}) renames columns, and drop(columns=["label"]) removes them; both return new tables.

pd.cut sorts numbers into labelled bins:

ages = pd.Series([5, 18, 30, 64, 70])
pd.cut(ages, bins=[0, 17, 64, 120], labels=["child", "adult", "senior"]).tolist()
# ['child', 'adult', 'adult', 'adult', 'senior']

By default each interval includes its right edge and excludes its left: 18 falls in (17, 64], and 64 does too. A value exactly at the lowest edge, here 0, falls outside every bin and becomes NaN, unless include_lowest=True is given; right=False makes intervals include the left edge instead.

Example

# New columns from old ones: arithmetic, map, apply, rename and drop.
df = {"item": ["pen", "book", "lamp"], "price": [20, 350, 900], "qty": [3, 1, 2]}

df["total"] = [p * q for p, q in zip(df["price"], df["qty"])]     # df["price"] * df["qty"]
size = {"pen": "small", "book": "medium"}
df["size"] = [size.get(i) for i in df["item"]]                      # df["item"].map(size)
df["label"] = [i.upper() + "!" for i in df["item"]]                 # df["item"].apply(...)
df["price_k"] = [round(p / 1000, 2) for p in df["price"]]           # (df["price"] / 1000).round(2)

renamed = {("cost" if k == "price" else k): v for k, v in df.items()}   # rename(columns=...)
del renamed["label"]                                                   # drop(columns=["label"])
for name, values in renamed.items():
    print(name, values)
Open in compiler

Line by line

  1. total multiplies two columns position by position.
  2. size maps each item through a dictionary; the lamp is not in it, so it gets None (NaN).
  3. label applies a function to each value.
  4. price_k divides and rounds a whole column.
  5. Renaming changes a key and dropping removes one; the other columns are unchanged.

Expected result

item ['pen', 'book', 'lamp']
cost [20, 350, 900]
qty [3, 1, 2]
total [60, 350, 1800]
size ['small', 'medium', None]
price_k [0.02, 0.35, 0.9]

In the real world

Feature engineering for machine learning is mostly this lesson: ratios between columns, categories mapped to codes, ages and incomes binned into bands. Business reports compute margins, tax and totals the same way. The most common bug in such code is an unmapped category silently turning into NaN.

Common mistakes

  • Using apply for simple arithmetic, which is slower and harder to read than df["a"] * df["b"].
  • Missing values from map: any value not in the dictionary becomes NaN.
  • Converting with astype(int) a column that has NaN, which fails, because NaN is not an integer.
  • Expecting cut to include the lowest edge, which it does not unless include_lowest=True.

Best practices

  • Compute new columns with vectorised operations, and use apply only when nothing else fits.
  • After map, check how many values became NaN.
  • Give bins explicit labels, so reports read "adult" rather than "(17, 64]".

Summary

New columns come from vectorised arithmetic or assign. map translates values (unknown ones become NaN), apply runs a function on each value or row, astype changes types, and rename and drop tidy columns. cut puts numbers into labelled intervals that include their right edge by default.

Quick check

1. What does df['item'].map({'pen': 1}) give for 'book'?
2. Which is the best way to compute a total column?
3. With bins=[0, 17, 64], where does 17 fall?
4. What does df.rename(columns={'price': 'cost'}) return?

Key takeaways

  • Prefer vectorised arithmetic between columns to apply; it is clearer and much faster.
  • map with a dictionary gives NaN for values it does not know: check for them.
  • cut's intervals include their right edge by default, so the lowest edge itself falls outside.

Practice problems for this lesson

My notes

Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.