Derived Columns
Overview
The column that answers your question usually is not in the file.
What You Will Learn in This Lesson
By the end of this lesson, you will be able to:
- Build a column from arithmetic between other columns.
- Divide a column by one of its own summaries.
- Use
assignwhen you want to keep a chain going. - Say why a rate is usually more comparable than a total.
Why This Matters
Files hold what was recorded; questions are about rates, shares and differences. "Which region sells most" and "which region sells most per head" are different questions with different answers, and only one of them is in the file.
Step 1: Arithmetic between columns
Columns combine row by row, so a rate, a total or a difference is one expression.
Two columns in, one out
The alignment is by index, so the rows always line up with themselves.
import pandas as pd
df = pd.DataFrame({"sales": [100.0, 90.0], "people": [4, 3]})
df["per_person"] = df["sales"] / df["people"]
print(df)A column against its own total
Dividing a column by its sum broadcasts the single total across every row, giving each row's share.
import pandas as pd
df = pd.DataFrame({"amount": [25.0, 75.0]})
df["share"] = df["amount"] / df["amount"].sum() * 100
print(df)Why the rate is the fairer comparison
The biggest total is often just the biggest region. Per head, the ranking can reverse entirely — and that reversal is usually the finding.
import pandas as pd
df = pd.DataFrame({"region": ["A", "B"], "sales": [1000.0, 300.0], "people": [500, 50]})
df["per_person"] = df["sales"] / df["people"]
print(df)Mini Practice #1: Total against rate
Try It YourselfAdd a per_person column, then print the region with the highest total and the region with the highest rate. They are not the same.
Step 2: assign keeps the chain going
assign returns a new table with the column added, which is useful when you are building a pipeline rather than editing a variable.
One expression, several steps
Each step hands a table to the next, and nothing in between needs a name. It also leaves the original alone, which the plain assignment form does not.
import pandas as pd
df = pd.DataFrame({"sales": [1000.0, 300.0], "people": [500, 50]})
result = (df
.assign(per_person=lambda d: d["sales"] / d["people"])
.sort_values("per_person", ascending=False))
print(result)
print(list(df.columns)) # untouchedThe lambda receives the table as it is at that point in the chain, which is what lets a later assign use a column an earlier one just made.
Mini Practice #2: A chain with assign
Try It YourselfBuild the same per-person column with assign, sort by it, and confirm the original table is unchanged.
Step 3: When to Use It
Arithmetic across columns
Ratios, differences and percentages are one vectorised expression.
df["gap"] = df["target"] - df["score"]Labels from a condition
np.where or a mapping turns a number or code into the category a reader understands.
When Not to Use It
Do not derive a column with a Python loop over rows. A vectorised expression is shorter, faster, and handles missing values consistently.
End-of-Lesson Exercises
Exercise 1: Each row as a share of the total
Write percent_of_total(df) returning each row's amount as a percentage of the column total, rounded to one decimal place.
Divide the column by its own sum and multiply by 100. The division broadcasts the single total across every row.
Exercise 2: The best rate, not the biggest total
Write best_rate(df) returning the region with the highest sales per person, where the table has region, sales and people columns.
Build the rate as a column, then use idxmax to get the row label of the highest and read the region off it. Ranking on sales alone is what the third hidden case catches.