CHOOSE, SWITCH and IFS Equivalents in pandas

Spreadsheets pick one of several results in three different ways. CHOOSE(2, "Low", "Medium", "High") picks by position. SWITCH(A2, "N", "North", "S", "South", "Other") picks by matching a value, with a default. IFS(B2>=90, "A", B2>=75, "B", B2>=50, "C", TRUE, "Fail") picks by testing conditions in order and taking the first that is true. Deeply nested IF formulas do the same job less readably. In pandas, writing these with apply and a Python if chain works but runs row by row; each has a vectorised equivalent that is clearer and much faster. This page translates all three, adds banding with pd.cut for the common range case, deals with what happens when nothing matches — where the tools differ in important ways — and checks every translation against the spreadsheet formulas. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas numpy openpyxl

reps.xlsx lists eight sales reps with a one-letter region code (including an unknown "X"), a priority number from 1 to 4 (where 4 is out of range for a three-item list), and a score out of 100. LibreOffice (soffice) recalculates the equivalent spreadsheet formulas in Step 1 for comparison.

Step 1 — The spreadsheet formulas

Write the three formulas for every row and recalculate, so each pandas version has a reference answer. SWITCH and IFS are newer functions, stored in the file with the _xlfn. prefix:

import subprocess
from openpyxl import load_workbook

wb = load_workbook("reps.xlsx")
ws = wb.active
ws["E1"], ws["F1"], ws["G1"] = "CHOOSE", "SWITCH", "IFS"
for r in range(2, ws.max_row + 1):
    ws[f"E{r}"] = f'=CHOOSE(C{r},"Urgent","Normal","Low")'
    ws[f"F{r}"] = f'=_xlfn.SWITCH(B{r},"N","North","S","South","E","East","W","West","Unknown")'
    ws[f"G{r}"] = f'=_xlfn.IFS(D{r}>=90,"A",D{r}>=75,"B",D{r}>=50,"C",TRUE,"Fail")'
wb.save("reps_formulas.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "reps_formulas.xlsx"], check=True, capture_output=True)
calc = load_workbook("calc/reps_formulas.xlsx", data_only=True).active
excel = [[c.value for c in row] for row in calc.iter_rows(min_row=2, min_col=5, max_col=7)]
for row, res in zip(calc.iter_rows(min_row=2, max_col=4, values_only=True), excel):
    print(row, "→", res)

The spreadsheet shows the behaviours to reproduce. CHOOSE returns an error for Grace, whose priority 4 has no fourth item. SWITCH falls through to its default "Unknown" for Farid's code "X". IFS tests the score thresholds in order, so 93 is an A, 75 a B — the boundary belongs to the higher band because >= is used — and anything below 50 reaches the final TRUE condition and gets "Fail". Writing the _xlfn. prefix matters: without it the spreadsheet does not recognise the newer functions and shows #NAME?, as explained in Fix #NAME? Errors for New Excel Functions Written by Python.

Step 2 — CHOOSE: pick by position

CHOOSE uses a 1-based position into a list. The pandas equivalent maps positions to values with a dictionary — which also makes the out-of-range case explicit:

import pandas as pd

reps = pd.read_excel("reps.xlsx")
labels = ["Urgent", "Normal", "Low"]
choose_map = {i + 1: v for i, v in enumerate(labels)}
reps["CHOOSE"] = reps["Priority"].map(choose_map)
print(reps[["Rep", "Priority", "CHOOSE"]].to_string(index=False))

Series.map with a dictionary of position → value gives "Urgent" for 1, "Normal" for 2, "Low" for 3 — and NaN for Grace's 4, where Excel showed an error. A missing value is the honest pandas result: it can be counted, filtered, or filled with fillna("Check priority"). Converting the list into a dictionary keyed from 1 avoids the off-by-one trap of indexing a Python list (which starts at 0) with Excel's 1-based numbers. numpy.choose also exists but raises an error for any out-of-range position, which is rarely what a report wants.

Step 3 — SWITCH: pick by value, with a default

SWITCH compares one expression with a list of values. In pandas that is map with a dictionary, followed by fillna for the default:

REGIONS = {"N": "North", "S": "South", "E": "East", "W": "West"}
reps["SWITCH"] = reps["Region code"].map(REGIONS).fillna("Unknown")
print(reps[["Rep", "Region code", "SWITCH"]].to_string(index=False))

Codes found in the dictionary are translated; Farid's "X" is not, so map gives NaN and fillna supplies the default — exactly SWITCH's last argument. The dictionary is easier to maintain than a long formula: it can live in a settings file or be read from a mapping sheet, and adding a region is one entry. If no default is wanted, leave out fillna and the unknown codes stay visible as missing values — often better than a catch-all label that hides data problems. Mapping tables at scale are covered in Standardise Category Values with a Mapping Table.

Step 4 — IFS: ordered conditions with numpy.select

IFS takes the first condition that is true. numpy.select does the same for whole columns: a list of conditions, a list of matching results, and a default for rows where none is true:

import numpy as np

conditions = [reps["Score"] >= 90, reps["Score"] >= 75, reps["Score"] >= 50]
grades = ["A", "B", "C"]
reps["IFS"] = np.select(conditions, grades, default="Fail")
print(reps[["Rep", "Score", "IFS"]].to_string(index=False))

Order matters exactly as in IFS: a score of 93 satisfies all three conditions, and select takes the first, "A". Writing the conditions from the highest threshold down keeps them simple — each one only needs a lower bound because the rows above it have already been claimed. default plays the role of the final TRUE, "Fail" pair. Conditions can be any boolean expressions — combinations of columns, text tests, dates — which makes np.select the general replacement for nested IFs; see also Excel IF Formulas as pandas Conditional Columns.

Step 5 — Bands with pd.cut, and the cross-check

When the conditions are simply consecutive numeric ranges, pd.cut expresses them as bin edges, which is shorter and harder to get wrong:

reps["cut"] = pd.cut(reps["Score"], bins=[-np.inf, 50, 75, 90, np.inf], labels=["Fail", "C", "B", "A"], right=False)

ours = reps[["CHOOSE", "SWITCH", "IFS"]].astype(object).where(reps[["CHOOSE", "SWITCH", "IFS"]].notna(), None).values.tolist()
for name, i in (("CHOOSE", 0), ("SWITCH", 1), ("IFS", 2)):
    same = [(o[i] == e[i]) or (o[i] is None and isinstance(e[i], str) and e[i].startswith("#")) for o, e in zip(ours, excel)]
    print(f"{name:6} matches spreadsheet on {sum(same)}/{len(same)} rows")
print("pd.cut agrees with IFS:", (reps["cut"].astype(str) == reps["IFS"]).all())

right=False makes each bin include its left edge — [75, 90) — so a score of exactly 75 is a B and exactly 90 an A, matching the >= tests. The comparison confirms all three translations agree with the spreadsheet on every row, treating Grace's CHOOSE error as equivalent to a missing value, and that pd.cut gives the same grades as IFS. Banding in general is covered in Bin Values into Ranges with pandas cut.

Performance

All of these are vectorised: they work on whole columns at once. The row-by-row alternative — df.apply(...) with an if/elif chain inside — gives the same answers but calls Python once per row. Timing both on a larger table makes the difference concrete:

import time

big = pd.DataFrame({"Score": np.random.default_rng(0).integers(0, 101, 200_000)})

def grade(s):
    if s >= 90:
        return "A"
    elif s >= 75:
        return "B"
    elif s >= 50:
        return "C"
    return "Fail"

t0 = time.perf_counter(); slow = big["Score"].apply(grade); t_apply = time.perf_counter() - t0
t0 = time.perf_counter()
fast = np.select([big["Score"] >= 90, big["Score"] >= 75, big["Score"] >= 50], ["A", "B", "C"], default="Fail")
t_select = time.perf_counter() - t0
print(f"apply: {t_apply:.3f}s | np.select: {t_select:.3f}s | same result: {(slow.values == fast).all()}")

On 200,000 rows the vectorised version was about ten times faster in this run — and that is the favourable case for apply, a single column; a row-wise df.apply(..., axis=1) over several columns is far slower still. The vectorised code is also easier to test, because the rules are visible as a list of conditions rather than buried in a function. Reach for apply only when a rule genuinely cannot be expressed as column operations.

Common pitfalls

• 0-based versus 1-based positions. Excel's CHOOSE counts from 1; build the mapping from 1.
• Forgetting the default. map leaves unmatched values as NaN; add fillna if SWITCH had a default.
• Conditions in the wrong order. np.select takes the first true condition, like IFS.
• Boundary values. Decide whether 75 is a B or a C, and use >= or right=False consistently.
• apply with if-chains. Slow and hard to test; use map, np.select or pd.cut.

Conclusion

Excel's three multi-way choice functions map onto three vectorised pandas tools. CHOOSE by position becomes map with a dictionary keyed from 1, SWITCH by value becomes map with a dictionary plus fillna for the default, and IFS with ordered conditions becomes numpy.select with a default — or pd.cut when the conditions are consecutive ranges. Decide deliberately what an unmatched value should become, keep boundaries consistent with the spreadsheet, and confirm the translation against the original formulas.

Frequently asked questions

What is the pandas equivalent of Excel's IFS? numpy.select, which takes a list of boolean conditions, a list of results and a default, and returns the result of the first true condition for each row.

How do I replicate SWITCH in pandas? Use Series.map with a dictionary of values to results, then fillna with the default result for values not in the dictionary.

How do I replicate CHOOSE in pandas? Map the position column with a dictionary from 1-based positions to values. Positions outside the list become NaN, where Excel would show an error.

Should I use apply with if statements instead? It works but runs row by row and is much slower on large data. map, numpy.select and pandas.cut are vectorised.

Related

• Up one level: Excel Formula Equivalents in pandas — spreadsheet functions in Python.
• IFERROR and ISNA Equivalents in pandas — handling the no-match cases.
• XLOOKUP Equivalent in pandas — choosing by lookup table.
• Replace Values in an Excel Column with pandas — map versus replace.