Learn how to derive new columns in pandas using three essential tools: np.where for conditional logic, pd.cut for numeric binning, and .map() for lookup-style translation. By the end, you'll be able to transform raw transactional data into analysis-ready features without writing a single loop.
You've loaded your sales data into a DataFrame. The raw numbers are there — revenue, units sold, customer IDs — but your manager wants a report that flags high-value orders, buckets customers into tiers, and labels each region by its sales rep. None of that information lives in the source data. You need to derive it.
This is one of the most common real-world data tasks there is: taking existing columns and computing new ones based on rules, thresholds, or lookup tables. In Excel, you'd reach for IF(), IFS(), or VLOOKUP(). In SQL, you'd write a CASE WHEN expression. In pandas, you have three elegant, production-ready tools that cover almost every situation: np.where, pd.cut, and .map(). By the end of this lesson, you'll know exactly when to reach for each one and how to chain them together.
What you'll learn:
np.where (the pandas IF statement)pd.cut.map()Before working through this lesson, you should be comfortable loading data into a DataFrame and understanding its basic structure. If you're new to pandas, start with Your First pandas DataFrame: Loading CSV and Excel Files and Exploring Data. You should also understand how boolean masks work in pandas — if filtering with conditions feels unfamiliar, review Selecting and Filtering Data in pandas: loc, iloc, and Boolean Masks first.
Throughout this lesson we'll work with a fictional e-commerce orders dataset. Let's build it directly in Python so you can follow along without needing to download anything:
import pandas as pd
import numpy as np
data = {
"order_id": [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010],
"customer_id": ["C001", "C002", "C003", "C001", "C004", "C005", "C002", "C006", "C003", "C007"],
"region": ["North", "South", "East", "West", "North", "East", "South", "West", "North", "East"],
"revenue": [245.50, 89.00, 1420.75, 310.00, 55.25, 875.00, 2100.00, 430.00, 120.50, 680.00],
"units_sold": [3, 1, 12, 4, 1, 8, 18, 5, 2, 7],
"returned": [False, False, True, False, False, False, False, True, False, False],
}
orders = pd.DataFrame(data)
print(orders)
This gives you a ten-row DataFrame with revenue figures ranging from under $60 to over $2,000, a mix of regions, and a boolean returned column. Everything we build in this lesson derives from these raw inputs.
Before jumping into conditional logic, let's cover the baseline: adding a column that's a straightforward mathematical transformation of existing data. You do this by assigning a new column name on the left side of an equals sign:
# Calculate revenue per unit
orders["revenue_per_unit"] = orders["revenue"] / orders["units_sold"]
print(orders[["order_id", "revenue", "units_sold", "revenue_per_unit"]].round(2))
pandas applies that division across every row simultaneously — no loops needed. This is called vectorized computation, and it's dramatically faster than iterating row by row. If you want to understand why this matters at scale, Writing Fast pandas Code: Vectorization Instead of apply and Loops covers the mechanics in depth.
This works beautifully for arithmetic, but what about logic? What if you want to flag orders as "high value" or "standard" based on whether revenue exceeds $500? That's where np.where comes in.
Think of np.where as a vectorized IF statement. In Excel you'd write =IF(A2 > 500, "High Value", "Standard") and drag it down the column. np.where does the same thing for all rows at once, in one line.
The syntax is:
np.where(condition, value_if_true, value_if_false)
Three arguments: a boolean condition, what to return when it's True, and what to return when it's False. Let's use it:
orders["order_tier"] = np.where(orders["revenue"] > 500, "High Value", "Standard")
print(orders[["order_id", "revenue", "order_tier"]])
Output:
order_id revenue order_tier
0 1001 245.50 Standard
1 1002 89.00 Standard
2 1003 1420.75 High Value
3 1004 310.00 Standard
4 1005 55.25 Standard
5 1006 875.00 High Value
6 1007 2100.00 High Value
7 1008 430.00 Standard
8 1009 120.50 Standard
9 1010 680.00 High Value
Clean, readable, and instantaneous on even millions of rows.
Tip
The condition argument accepts any boolean Series — you can use comparisons (>, <, ==, !=), string checks, .isin() calls, or even the result of another np.where. If you've been writing Python boolean masks before, you already know how to write the condition.
What if you have three tiers — "Premium," "Standard," and "Budget"? You nest np.where calls, placing one inside the value_if_false slot of another, exactly the way you'd nest IF() in Excel:
orders["order_tier"] = np.where(
orders["revenue"] > 1000,
"Premium",
np.where(
orders["revenue"] > 300,
"Standard",
"Budget"
)
)
print(orders[["order_id", "revenue", "order_tier"]])
pandas evaluates from the outside in: first it checks if revenue is over $1,000. If yes, assign "Premium." If no, move to the inner check: over $300 gets "Standard," everything else gets "Budget."
Warning
Nesting more than two or three np.where calls gets hard to read quickly. If you find yourself with four or more tiers, pd.cut is almost always the better tool — and that's exactly what we'll cover next.
You can combine multiple conditions using & (and) and | (or), with each condition in parentheses:
# Flag orders that are high value AND were NOT returned
orders["reliable_revenue"] = np.where(
(orders["revenue"] > 500) & (orders["returned"] == False),
"Confirmed High Value",
"Other"
)
This creates a "Confirmed High Value" label only for orders where both conditions are satisfied simultaneously.
pd.cut solves a specific but extremely common problem: you have a continuous numeric variable — revenue, age, score, distance — and you want to group it into labeled buckets. The critical distinction from np.where is that pd.cut is designed for ordered ranges where the bins cover the entire numeric space without gaps or overlaps.
Think of it as the pandas equivalent of this Excel formula: =IFS(A2<100,"Low", A2<500,"Medium", A2>=500,"High") — except pandas handles the boundary logic for you automatically.
orders["revenue_bucket"] = pd.cut(
orders["revenue"],
bins=[0, 200, 600, 2200],
labels=["Low", "Medium", "High"]
)
print(orders[["order_id", "revenue", "revenue_bucket"]])
Output:
order_id revenue revenue_bucket
0 1001 245.50 Medium
1 1002 89.00 Low
2 1003 1420.75 High
3 1004 310.00 Medium
4 1005 55.25 Low
5 1006 875.00 High
6 1007 2100.00 High
7 1008 430.00 Medium
8 1009 120.50 Low
9 1010 680.00 High
The bins argument defines the edges of your intervals. With four edges, you get three bins: (0, 200], (200, 600], (600, 2200]. The labels argument assigns a human-readable name to each bin. Crucially, labels must have exactly one fewer entry than bins.
Note
By default, pd.cut creates intervals that include the right edge and exclude the left edge — so a revenue of exactly 200.00 would fall in the "Low" bucket, not "Medium." If you want to include the left edge instead, pass right=False to the function.
pd.cut with explicit bins values creates equal-width intervals — the range of each bucket is the same. But sometimes you want equal-frequency bins, where each bucket contains roughly the same number of rows. That's what pd.qcut does:
# Quartile-based bucketing
orders["revenue_quartile"] = pd.qcut(
orders["revenue"],
q=4,
labels=["Q1", "Q2", "Q3", "Q4"]
)
print(orders[["order_id", "revenue", "revenue_quartile"]])
pd.qcut takes q as the number of quantiles rather than explicit bin edges. Use pd.cut when the threshold values themselves are meaningful (e.g., "under $200 is low margin"). Use pd.qcut when you care more about relative ranking (e.g., "top 25% of orders").
The column created by pd.cut uses a special pandas data type called Categorical. This has some powerful advantages — it preserves the order of your labels, uses less memory than storing strings, and integrates nicely with groupby operations. You can verify this:
print(orders["revenue_bucket"].dtype)
# category
print(orders["revenue_bucket"].cat.categories)
# Index(['Low', 'Medium', 'High'], dtype='object')
Key insight
Because pd.cut produces a Categorical column with an inherent order, you can sort by it, group by it in groupby operations, and compare categories naturally. This makes it significantly more useful than storing the same strings in a plain object column.
Here's a different kind of derived column: you don't have a condition or a range — you have a set of specific values that each need to translate to something else. Each region code maps to a sales rep name. Each customer ID maps to a company name. Each product code maps to a category.
In Excel, this is VLOOKUP. In SQL, it's a JOIN to a reference table. In pandas, the cleanest solution is .map().
Pass a Python dictionary to .map(), and pandas replaces every value in the column with its corresponding dictionary value:
region_to_rep = {
"North": "Alice Chen",
"South": "Marcus Webb",
"East": "Priya Nair",
"West": "Jordan Bell",
}
orders["sales_rep"] = orders["region"].map(region_to_rep)
print(orders[["order_id", "region", "sales_rep"]])
Output:
order_id region sales_rep
0 1001 North Alice Chen
1 1002 South Marcus Webb
2 1003 East Priya Nair
3 1004 West Jordan Bell
...
Every "North" becomes "Alice Chen," every "South" becomes "Marcus Webb," and so on. The mapping is applied to all rows at once — no loops, no apply(), just a clean vectorized lookup.
If your lookup table lives in another DataFrame (which is common in real work), you can use a pandas Series as the map source instead of a dictionary. The Series index acts as the lookup key:
# Imagine this came from a separate reference table
rep_quota = pd.Series({
"Alice Chen": 120000,
"Marcus Webb": 95000,
"Priya Nair": 115000,
"Jordan Bell": 105000,
})
# Map rep quota through the sales_rep column we just created
orders["rep_quota"] = orders["sales_rep"].map(rep_quota)
print(orders[["order_id", "sales_rep", "rep_quota"]])
This is effectively a two-step VLOOKUP: first mapping regions to reps, then mapping reps to quotas. It's readable, composable, and fast.
Warning
If a value in your column doesn't exist as a key in your dictionary or index in your Series, .map() returns NaN for that row. This is intentional — it tells you something didn't match rather than silently dropping data. Always check for NaN values after a .map() call to catch mismatches in your lookup table. See Cleaning Messy Data with pandas: Missing Values, Duplicates, and Data Types for strategies to handle those NaNs.
There's a closely related method called .replace() that also swaps values. The key difference: .map() replaces only values that exist in your dictionary and returns NaN for anything else, while .replace() leaves unmatched values unchanged. For true lookup-style translation, .map() is the right choice because unmatched values being silently preserved is usually a bug, not a feature.
Let's build a full analyst-grade derived-column pipeline on our orders data. We'll add four new columns using each technique we've covered:
import pandas as pd
import numpy as np
# --- Original data ---
data = {
"order_id": [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010],
"customer_id": ["C001", "C002", "C003", "C001", "C004", "C005", "C002", "C006", "C003", "C007"],
"region": ["North", "South", "East", "West", "North", "East", "South", "West", "North", "East"],
"revenue": [245.50, 89.00, 1420.75, 310.00, 55.25, 875.00, 2100.00, 430.00, 120.50, 680.00],
"units_sold": [3, 1, 12, 4, 1, 8, 18, 5, 2, 7],
"returned": [False, False, True, False, False, False, False, True, False, False],
}
orders = pd.DataFrame(data)
# 1. Simple arithmetic column
orders["revenue_per_unit"] = (orders["revenue"] / orders["units_sold"]).round(2)
# 2. np.where: flag orders that are premium AND not returned
orders["premium_confirmed"] = np.where(
(orders["revenue"] > 500) & (~orders["returned"]),
True,
False
)
# 3. pd.cut: bucket revenue into Low / Medium / High
orders["revenue_tier"] = pd.cut(
orders["revenue"],
bins=[0, 200, 700, float("inf")],
labels=["Low", "Medium", "High"]
)
# 4. .map(): translate region to sales rep
region_to_rep = {
"North": "Alice Chen",
"South": "Marcus Webb",
"East": "Priya Nair",
"West": "Jordan Bell",
}
orders["sales_rep"] = orders["region"].map(region_to_rep)
# Review the result
print(orders[["order_id", "revenue", "revenue_per_unit",
"premium_confirmed", "revenue_tier", "sales_rep"]])
In under fifteen lines of transformation code, you've turned a flat transaction table into an analysis-ready dataset with financial ratios, quality flags, categorical tiers, and human-readable labels. This is the kind of preparatory work that feeds directly into groupby aggregations and visualizations.
Work through these three tasks using the orders DataFrame from above. Write your code before checking the solutions.
Task 1: Create a column called "bulk_order" that contains True if units_sold is greater than or equal to 8, and False otherwise. Use np.where.
Task 2: Create a column called "unit_tier" that bins units_sold into three categories: "Small" (1–3 units), "Medium" (4–9 units), and "Large" (10+ units). Use pd.cut with explicit bin edges.
Task 3: Create a dictionary that maps each customer ID to a loyalty level: C001 → "Gold", C002 → "Silver", C003 → "Gold", C004 → "Bronze", C005 → "Silver", C006 → "Bronze", C007 → "Silver". Map this to a new "loyalty_level" column.
# Task 1
orders["bulk_order"] = np.where(orders["units_sold"] >= 8, True, False)
# Task 2
orders["unit_tier"] = pd.cut(
orders["units_sold"],
bins=[0, 3, 9, float("inf")],
labels=["Small", "Medium", "Large"]
)
# Task 3
loyalty_map = {
"C001": "Gold", "C002": "Silver", "C003": "Gold",
"C004": "Bronze", "C005": "Silver", "C006": "Bronze",
"C007": "Silver"
}
orders["loyalty_level"] = orders["customer_id"].map(loyalty_map)
"My np.where column is all True or all False"
You likely forgot parentheses around individual conditions when combining them. orders["revenue"] > 500 & orders["returned"] == False is silently misread by Python due to operator precedence. Always wrap each condition: (orders["revenue"] > 500) & (orders["returned"] == False).
"pd.cut raises a ValueError about labels"
The number of labels must be exactly one less than the number of bin edges. If you have edges [0, 200, 600, 2200] (four values), you need exactly three labels. Count your edges, subtract one, count your labels — they must match.
"My .map() result has a lot of NaN values"
A value in your column doesn't appear in your dictionary. Print orders["region"].unique() to see every distinct value in the column and compare it against your dictionary keys. Spelling differences, extra spaces, and capitalization mismatches are the most common culprits. Use string methods like .str.strip() and .str.title() to normalize values before mapping.
"I modified a column and got a SettingWithCopyWarning"
This warning appears when you're assigning to a DataFrame that might be a slice of another. Always create your derived columns on the original DataFrame or on an explicit copy made with .copy(). This is a common pandas gotcha that trips up even experienced users.
"My pd.cut column won't sort correctly"
If you see buckets sorting alphabetically ("High" before "Low") instead of in the correct tier order, make sure you created the column with pd.cut and didn't subsequently cast it to a string. The Categorical dtype carries the order; plain strings don't. If you accidentally stringified it, re-run pd.cut with the ordered=True default intact.
You now have three reliable tools for adding derived columns to any pandas DataFrame:
| Tool | Use When |
|---|---|
np.where |
You need a conditional if/else or if/else-if result |
pd.cut / pd.qcut |
You're binning a numeric column into ordered ranges |
.map() |
You're translating specific values via a lookup table |
These techniques are foundational to almost every data analysis pipeline. You'll use them before aggregating, before visualizing, before exporting, and before building reports. They transform raw transactional data into analytically useful features.
From here, the natural next step is to use these new columns in aggregations — grouping by revenue_tier or sales_rep to compute totals and averages. That workflow is covered in depth in Grouping and Aggregating in pandas: groupby as the PivotTable Replacement. If you're building this into a repeatable report, you'll eventually want to wrap these transformations into reusable functions, which is covered in Structuring a Reusable Data Analysis Project: Functions, Modules, Notebooks, and Scripts.