Conditional Columns and Bucketing in pandas: Creating New Fields with np.where, cut, and map

Introduction

Imagine you're working with a sales dataset and your manager asks: "Can you flag which orders are high-value? And can you group customers by their total spending tier — low, medium, and high? Oh, and can you map each product category code to a human-readable label?" Three different questions, but they all share the same root challenge: you need to create a new column based on existing data.

This is one of the most common tasks in data analysis, and it's where a lot of people reach for the wrong tool. They write a slow Python loop, or they try to adapt a formula they remember from Excel, and it works — barely — until the dataset has a million rows and everything grinds to a halt. pandas has purpose-built tools for exactly these situations: np.where for simple yes/no conditions, pd.cut (and pd.qcut) for numeric bucketing, and .map() for label substitution from a dictionary. Each one is fast, readable, and built for this job.

By the end of this lesson, you'll be able to create conditional and derived columns confidently, choose the right tool for each scenario, and produce analysis-ready datasets without writing a single slow loop.

What you'll learn:

• How to create binary flag columns using np.where with a single condition
• How to chain multiple conditions with np.where and np.select for complex logic
• How to bucket numeric data into labeled ranges using pd.cut and pd.qcut
• How to substitute values using dictionary .map() for code-to-label translation
• When to use each approach — and common mistakes that will cost you hours

---

Prerequisites

You should be comfortable loading 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 boolean filtering — knowing how a condition like df['revenue'] > 1000 produces a True/False Series will make np.where click immediately. The Selecting and Filtering Data in pandas: loc, iloc, and Boolean Masks lesson covers this well.

---

The Dataset We'll Use

Throughout this lesson we'll work with a fictional (but realistic) e-commerce orders dataset. Let's build it so you can follow along directly in a Jupyter notebook or VS Code:

import pandas as pd
import numpy as np

data = {
    'order_id': [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010],
    'customer_id': ['C01', 'C02', 'C03', 'C01', 'C04', 'C02', 'C05', 'C03', 'C04', 'C05'],
    'category_code': ['ELEC', 'FURN', 'APPL', 'ELEC', 'CLTH', 'FURN', 'APPL', 'CLTH', 'ELEC', 'FURN'],
    'revenue': [1250.00, 340.00, 875.50, 2100.00, 45.00, 980.00, 620.00, 130.00, 3400.00, 760.00],
    'units_sold': [5, 2, 7, 8, 1, 4, 3, 2, 12, 6],
    'region': ['North', 'South', 'East', 'North', 'West', 'South', 'East', 'West', 'North', 'South'],
    'is_returned': [False, False, True, False, False, True, False, False, False, True],
}

df = pd.DataFrame(data)
print(df.head())

This gives us orders with revenue, product categories (as codes), regions, and a return flag. Perfect for demonstrating all three tools.

---

Creating Binary Flags with np.where

np.where is the pandas-friendly equivalent of Excel's =IF() function. The syntax is straightforward:

np.where(condition, value_if_true, value_if_false)

It evaluates the condition across every row simultaneously — no loop required — and returns a new array you can assign directly as a column.

A Simple Flag Column

Let's flag high-value orders. We'll define "high-value" as revenue over $1,000:

df['is_high_value'] = np.where(df['revenue'] > 1000, 'High Value', 'Standard')
print(df[['order_id', 'revenue', 'is_high_value']])

Output:

   order_id  revenue  is_high_value
0      1001  1250.00     High Value
1      1002   340.00       Standard
2      1003   875.50       Standard
3      1004  2100.00     High Value
4      1005    45.00       Standard
5      1006   980.00       Standard
6      1007   620.00       Standard
7      1008   130.00       Standard
8      1009  3400.00     High Value
9      1010   760.00       Standard

Three rows are flagged correctly. The values can be strings, numbers, booleans — whatever makes sense for your downstream analysis.

Combining Multiple Conditions

What if you need more nuance? Say you want to flag orders that are both high-value and from the North region. You combine conditions using & (and) or | (or), and you must wrap each condition in parentheses:

df['priority_flag'] = np.where(
    (df['revenue'] > 1000) & (df['region'] == 'North'),
    'Priority',
    'Normal'
)
print(df[['order_id', 'revenue', 'region', 'priority_flag']])

When You Have More Than Two Outcomes: np.select

np.where is binary — it handles true and false, full stop. When you need three or more outcomes, reach for np.select. It works like a series of elif statements:

conditions = [
    df['revenue'] >= 2000,
    (df['revenue'] >= 500) & (df['revenue'] < 2000),
    df['revenue'] < 500
]

choices = ['Platinum', 'Gold', 'Silver']

df['tier_label'] = np.select(conditions, choices, default='Unknown')
print(df[['order_id', 'revenue', 'tier_label']])

Output:

   order_id  revenue tier_label
0      1001  1250.00       Gold
1      1002   340.00     Silver
2      1003   875.50       Gold
3      1004  2100.00   Platinum
4      1005    45.00     Silver
5      1006   980.00       Gold
6      1007   620.00       Gold
7      1008   130.00     Silver
8      1009  3400.00   Platinum
9      1010   760.00       Gold

The default parameter handles anything that doesn't match — a crucial safety net for real-world data that sometimes surprises you.

---

Bucketing Numeric Data with pd.cut

np.where and np.select work great when you're writing the logic yourself. But when your goal is to divide a continuous numeric range into named buckets — think age groups, spending tiers, score bands — pd.cut is the cleaner, more readable choice.

The idea is simple: you hand pandas a column of numbers and a list of bin edges, and it figures out which bin each value falls into.

Basic Bucketing with pd.cut

bins = [0, 250, 750, 1500, float('inf')]
labels = ['Low', 'Medium', 'High', 'Premium']

df['revenue_bucket'] = pd.cut(df['revenue'], bins=bins, labels=labels)
print(df[['order_id', 'revenue', 'revenue_bucket']])

Output:

   order_id  revenue revenue_bucket
0      1001  1250.00           High
1      1002   340.00         Medium
2      1003   875.50           High
3      1004  2100.00        Premium
4      1005    45.00            Low
5      1006   980.00           High
6      1007   620.00         Medium
7      1008   130.00            Low
8      1009  3400.00        Premium
9      1010   760.00           High

A few things to understand about how pd.cut works:

• Bin edges are exclusive on the left, inclusive on the right by default. So a value of exactly 250 falls into the "Low" bucket (0, 250], not Medium.
• float('inf') is the conventional way to say "everything above this threshold" in the top bucket.
• The labels list must have exactly one fewer element than the bins list — because four edges define three intervals, five edges define four intervals, and so on.

Checking the Result Type

The revenue_bucket column is actually a pandas Categorical dtype, not a plain string. That's usually good — it preserves the order of categories and keeps memory usage low. But if you need to use it as a plain string (for export or concatenation), convert it:

df['revenue_bucket'] = df['revenue_bucket'].astype(str)

Equal-Frequency Buckets with pd.qcut

pd.cut divides data by value ranges — you define where the edges are. pd.qcut divides by frequency — it figures out edges that put an equal number of rows in each bucket. This is useful when your data is skewed and you want balanced groups:

df['revenue_quartile'] = pd.qcut(df['revenue'], q=4, labels=['Q1', 'Q2', 'Q3', 'Q4'])
print(df[['order_id', 'revenue', 'revenue_quartile']])

q=4 creates quartiles. You could use q=10 for deciles, q=3 for tertiles, and so on. The tradeoff is that the bucket boundaries are data-driven and less intuitive to explain to stakeholders — "Q3 means revenue between $765 and $1,215" is harder to communicate than "High means over $1,500."

---

Substituting Values with .map()

The third tool in this toolkit handles a different problem entirely: you have a column of codes or abbreviations and you want to translate them into readable labels. Think product category codes, country abbreviations, status codes, or department IDs.

.map() accepts a dictionary and replaces each value in the column with the corresponding dictionary value. It's the pandas equivalent of VLOOKUP in Excel, but faster and more readable.

Dictionary Mapping

Our dataset has a category_code column with values like 'ELEC', 'FURN', 'APPL', 'CLTH'. Let's map those to full names:

category_labels = {
    'ELEC': 'Electronics',
    'FURN': 'Furniture',
    'APPL': 'Appliances',
    'CLTH': 'Clothing'
}

df['category_name'] = df['category_code'].map(category_labels)
print(df[['order_id', 'category_code', 'category_name']])

Output:

   order_id category_code category_name
0      1001          ELEC   Electronics
1      1002          FURN     Furniture
2      1003          APPL    Appliances
3      1004          ELEC   Electronics
4      1005          CLTH      Clothing
5      1006          FURN     Furniture
6      1007          APPL    Appliances
7      1008          CLTH      Clothing
8      1009          ELEC   Electronics
9      1010          FURN     Furniture

Clean and instant. The dictionary lives in your code (or can be loaded from a config file or reference table), and .map() does the substitution across all rows at once.

What Happens to Unmatched Values

If a value in your column doesn't exist as a key in the dictionary, .map() returns NaN for that row — not an error. This is important to know because it fails silently:

# Introduce an unknown code
df_test = df.copy()
df_test.loc[0, 'category_code'] = 'UNKN'
df_test['category_name'] = df_test['category_code'].map(category_labels)
print(df_test[['order_id', 'category_code', 'category_name']].head(2))

Output:

   order_id category_code category_name
0      1001          UNKN           NaN
1      1002          FURN     Furniture

Using .map() for Numeric Transformations

.map() also accepts a function, not just a dictionary, but for numeric transformations you're usually better served by vectorized operations or np.where. The dictionary form is where .map() truly shines — treating it as a lookup table is its killer use case, especially when you're joining data from multiple sources and need to enrich a code column with descriptive labels.

---

Putting It All Together

Here's a complete workflow that creates five new columns using all the tools from this lesson:

import pandas as pd
import numpy as np

# (re-create the base DataFrame from the top of this lesson)
data = {
    'order_id': [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010],
    'customer_id': ['C01', 'C02', 'C03', 'C01', 'C04', 'C02', 'C05', 'C03', 'C04', 'C05'],
    'category_code': ['ELEC', 'FURN', 'APPL', 'ELEC', 'CLTH', 'FURN', 'APPL', 'CLTH', 'ELEC', 'FURN'],
    'revenue': [1250.00, 340.00, 875.50, 2100.00, 45.00, 980.00, 620.00, 130.00, 3400.00, 760.00],
    'units_sold': [5, 2, 7, 8, 1, 4, 3, 2, 12, 6],
    'region': ['North', 'South', 'East', 'North', 'West', 'South', 'East', 'West', 'North', 'South'],
    'is_returned': [False, False, True, False, False, True, False, False, False, True],
}
df = pd.DataFrame(data)

# 1. Binary flag with np.where
df['is_high_value'] = np.where(df['revenue'] > 1000, True, False)

# 2. Multi-condition tier with np.select
conditions = [
    df['revenue'] >= 2000,
    (df['revenue'] >= 500) & (df['revenue'] < 2000),
    df['revenue'] < 500
]
choices = ['Platinum', 'Gold', 'Silver']
df['tier'] = np.select(conditions, choices, default='Unknown')

# 3. Fixed-width buckets with pd.cut
bins = [0, 250, 750, 1500, float('inf')]
labels = ['Low', 'Medium', 'High', 'Premium']
df['revenue_bucket'] = pd.cut(df['revenue'], bins=bins, labels=labels)

# 4. Label substitution with .map()
category_labels = {'ELEC': 'Electronics', 'FURN': 'Furniture',
                   'APPL': 'Appliances', 'CLTH': 'Clothing'}
df['category_name'] = df['category_code'].map(category_labels)

# 5. Derived column: revenue per unit
df['revenue_per_unit'] = df['revenue'] / df['units_sold']

print(df[['order_id', 'revenue', 'is_high_value', 'tier',
          'revenue_bucket', 'category_name', 'revenue_per_unit']].to_string())

This enriched DataFrame is ready for groupby analysis, reporting, or export. Notice we also added a simple derived column (revenue_per_unit) with plain arithmetic — a reminder that sometimes you don't need any special function at all.

---

Hands-On Exercise

Work through these tasks using the dataset from this lesson. Each one builds on what you've learned:

Task 1: Create a column called return_risk using np.where. Flag orders as 'At Risk' if units_sold is greater than 6, and 'Normal' otherwise. Print the result.

Task 2: Use np.select to create a region_group column that maps:

• North → 'Central Zone'
• South or East → 'Eastern Zone'
• West → 'Western Zone'

Task 3: Use pd.cut to bucket units_sold into three bins: 1–3 units ('Small'), 4–7 units ('Medium'), 8+ units ('Large'). Check whether any rows fall outside your bins and end up as NaN.

Task 4: Build a dictionary that maps each region to a two-letter abbreviation (e.g., 'North': 'NO') and use .map() to create a region_code column. Deliberately leave out one region from the dictionary and observe what .map() returns for those rows.

Task 5 (stretch): Combine what you built. Use groupby on revenue_bucket and tier together and compute the mean revenue_per_unit per group. What pattern do you notice?

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting parentheses in compound conditions np.where(df['revenue'] > 1000 & df['region'] == 'North', ...) is a bug. Always: np.where((df['revenue'] > 1000) & (df['region'] == 'North'), ...).

Mistake 2: Wrong number of labels in pd.cut pd.cut(df['revenue'], bins=[0, 500, 1000, float('inf')], labels=['Low', 'High']) will raise a ValueError because 3 bins need exactly 2 labels. Count your edges, subtract one, that's how many labels you need.

Mistake 3: Values at the exact lower bound falling outside the bin By default, pd.cut uses (left, right] intervals — the left edge is excluded. If your data has a value of exactly 0 and your lowest bin starts at 0, that row becomes NaN. Use include_lowest=True to make the first bin include its left edge: pd.cut(df['revenue'], bins=bins, labels=labels, include_lowest=True).

Mistake 4: Silently losing data with .map() As noted above, unmatched keys return NaN without any error or warning. After any .map() call on real data, run df['new_col'].isna().sum() to check how many rows didn't match. If the number is unexpectedly high, inspect df['source_col'].unique() against your dictionary keys.

Mistake 5: Modifying the original DataFrame unexpectedly If you copy a DataFrame with df2 = df and then add columns to df2, you're actually modifying df too (they point to the same object). Always use df2 = df.copy() when you want an independent copy. This matters especially in exercises and notebooks where you're experimenting. You can learn more about this and other data quality gotchas in the lesson on cleaning messy data with pandas.

---

Summary & Next Steps

You now have three powerful tools for creating new columns based on existing data:

Tool	Best For	Analogy
np.where	Binary (yes/no) conditions	Excel =IF()
np.select	Three or more outcome conditions	Excel =IFS()
pd.cut	Bucketing numbers into fixed ranges	Excel =IFS() on value ranges
pd.qcut	Equal-frequency buckets / percentiles	Quartile/decile ranking
.map()	Code-to-label substitution	Excel =VLOOKUP()

The pattern they all share: define your logic once, apply it to an entire column at once, no loops required. This is what makes pandas fast on large datasets — it processes whole arrays at the C level, not row by row in Python. For a deeper understanding of why this matters and how to write even faster pandas code, see the lesson on vectorization instead of apply and loops.

From here, the natural next step is using the columns you've just created in aggregations. A bucketed revenue_bucket column becomes far more powerful when you feed it into a groupby — you can compute average revenue per tier, count orders by category, or build crosstabs across two categorical columns. The lesson on grouping and aggregating in pandas picks up exactly where this one leaves off. If you're building these transformations as part of a larger data pipeline, the lesson on building a reusable ETL pipeline in pandas shows how to wrap these column-creation steps into functions you can call on any new data that arrives.