SCAN, REDUCE, MAP and BYROW Equivalents in pandas

LAMBDA turned Excel formulas into small programs, and its helper functions are where that shows most: SCAN walks down a column carrying a running value, REDUCE does the same but returns only the final value, MAP applies a calculation to every element, and BYROW and BYCOL produce one result per row or column. A workbook built around them — a stock balance that may not go negative, a fee schedule that compounds, a per-row validation — has to be translated when it becomes a scheduled Python job. Most helpers map to vectorised pandas operations that are faster and simpler than the formula. One pattern does not: a running value whose next step depends on the previous result in a non-linear way, which needs a genuine loop. This page shows which is which. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas openpyxl

stock_moves.xlsx lists daily stock movements for one product: receipts are positive, sales negative. The LAMBDA helpers are Excel 365 functions; LibreOffice does not evaluate them (version 26.2 returned #NAME?), so the Excel behaviour described follows Microsoft's documentation, and each Python translation is checked against an independent calculation instead.

Step 1 — SCAN that is really a cumulative sum

=SCAN(0, C2:C9, LAMBDA(acc, x, acc + x)) returns the running total after each movement. That is cumsum:

import pandas as pd

moves = pd.read_excel("stock_moves.xlsx", parse_dates=["Date"])
moves["Balance"] = moves["Qty"].cumsum()
print(moves.to_string(index=False))

Most SCAN formulas in practice are one of the cumulative operations pandas already has: cumsum for running totals, cumprod for compounding, cummax and cummin for running extremes. The initial value of SCAN corresponds to adding a constant (opening + moves["Qty"].cumsum()). These are vectorised, so they run in compiled code and handle millions of rows instantly. The running balance goes negative on 4 October and again on 6 October: the sheet records more sales than stock, which is where the next step comes in.

Step 2 — SCAN with state: a balance that cannot go negative

Suppose sales beyond the stock on hand become back-orders and the balance should stop at zero: =SCAN(0, C2:C9, LAMBDA(acc, x, MAX(0, acc + x))). Each step now depends on the clipped previous result, so cumsum().clip(lower=0) is wrong — clipping after the fact does not carry the zero forward. The faithful equivalent is itertools.accumulate, the standard library's SCAN:

from itertools import accumulate

floored = list(accumulate(moves["Qty"], lambda acc, x: max(0, acc + x), initial=0))[1:]
moves["Balance (floored)"] = floored
moves["Naive clip"] = moves["Qty"].cumsum().clip(lower=0)
print(moves[["Date", "Qty", "Balance", "Balance (floored)", "Naive clip"]].to_string(index=False))

On 5 October the floored balance is 50 — the receipt arrives into an empty store — while clipping the plain cumulative sum shows 25, because it still remembers the 25 units sold that were never there. From then on the two disagree on every row. accumulate's initial argument plays the role of SCAN's first argument and adds one extra element at the start, which [1:] drops so that the result has one value per row, as SCAN does.

Step 3 — REDUCE is the last value of SCAN

REDUCE takes the same arguments as SCAN and returns only the final accumulated value. functools.reduce is the direct equivalent, and for plain sums an aggregation is simpler still:

from functools import reduce

closing = reduce(lambda acc, x: max(0, acc + x), moves["Qty"], 0)
print("closing stock:", closing, "| plain total:", moves["Qty"].sum())
assert closing == moves["Balance (floored)"].iloc[-1]

The closing stock of 70 equals the last value of the floored SCAN — a cheap consistency check worth keeping in tests whenever both appear. REDUCE formulas in spreadsheets often build up something richer than a number, such as a running text or an array of results; in Python, return a tuple or a small object from the lambda, or — usually clearer — write a plain loop with named variables:

stock, short = 0, 0
for qty in moves["Qty"]:
    stock += qty
    if stock < 0:
        short += -stock
        stock = 0
print(f"closing stock {stock}, units short (back-ordered) {short}")

The loop reports both results at once: 70 units in stock and 65 units that could not be supplied. When the state has more than one part, a loop with names is easier to read and review than a nested lambda in either language.

Step 4 — MAP is an element-wise expression

=MAP(C2:C9, LAMBDA(x, IF(x < 0, -x, 0))) returns the units sold per row (sales as positive numbers, receipts as zero). Element-wise calculations are what pandas does by default:

moves["Sold"] = (-moves["Qty"]).clip(lower=0)
moves["Received"] = moves["Qty"].clip(lower=0)
moves["Kind"] = moves["Qty"].map(lambda x: "in" if x > 0 else "out")
print(moves[["Qty", "Sold", "Received", "Kind"]].to_string(index=False))

Prefer the vectorised form (clip, arithmetic, np.where) over Series.map with a lambda: both give the same result, but the vectorised form is many times faster on large columns. map with a function is still the right tool for logic that has no vectorised counterpart, such as calling a lookup or parsing function per value. MAP with several arrays — MAP(A2:A9, B2:B9, LAMBDA(a, b, …)) — is simply an expression of two columns, such as df["A"] * df["B"].

Step 5 — BYROW and BYCOL

BYROW(range, LAMBDA(row, MAX(row))) returns one value per row, BYCOL one per column. On a wide table — weekly sales per product, say — they are aggregations along an axis:

grid = pd.DataFrame({"Mon": [12, 7, 0], "Tue": [9, 11, 4], "Wed": [15, 6, 2]}, index=["Drill", "Saw", "Rake"])
print(grid.assign(Peak=grid.max(axis=1), Total=grid.sum(axis=1)))       # BYROW
print(grid.agg(["sum", "mean"]))                                         # BYCOL
print(grid.apply(lambda row: (row > 5).sum(), axis=1).rename("Days over 5"))

max(axis=1) and sum(axis=1) are the vectorised BYROW; agg with a list gives several per-column results at once, the BYCOL equivalent. apply(..., axis=1) handles arbitrary row logic but runs in Python once per row, so keep it for logic that genuinely needs it. A sheet laid out like this often arrives in long form first; reshaping between the two is covered in Unpivot a Wide Excel Sheet with pandas melt.

Step 6 — Per group and back to Excel

Running balances in real data are per product or per account. SCAN cannot reset per group without extra conditions; in pandas the group-by makes it explicit:

multi = pd.concat([moves.assign(Product="Hose"), moves.assign(Product="Rake", Qty=moves["Qty"] // 2)])
multi["Balance"] = multi.groupby("Product")["Qty"].transform(
    lambda s: pd.Series(list(accumulate(s, lambda a, x: max(0, a + x), initial=0))[1:], index=s.index))
with pd.ExcelWriter("stock_balances.xlsx", engine="xlsxwriter", datetime_format="dd mmm") as xw:
    multi[["Product", "Date", "Type", "Qty", "Balance"]].to_excel(xw, index=False, sheet_name="Balances")
print(multi.groupby("Product")["Balance"].last())

transform runs the accumulation separately for each product and returns results aligned with the original rows, ready to write as a column. The output file holds values, so it opens correctly in every spreadsheet — unlike a workbook full of SCAN formulas, which only calculates in Excel 365. Running totals in general are covered in Running Totals and Year-over-Year Growth in pandas.

Common pitfalls

• Clipping a cumulative sum. cumsum().clip(0) is not a floored running balance; use accumulate.
• Forgetting initial. Without it, accumulate starts from the first element, not from SCAN's initial value.
• apply everywhere. Row-wise apply and map with lambdas are slow; reach for vectorised methods first.
• Ignoring groups. A running balance across products is meaningless; group before accumulating.
• Live LAMBDA formulas in shared files. They show #NAME? outside Excel 365; write the results as values.

Conclusion

Most LAMBDA helpers become one vectorised pandas call: SCAN with a plain step is cumsum, cumprod, cummax or cummin; REDUCE is an aggregation; MAP is an element-wise expression; BYROW and BYCOL are aggregations along an axis. The exception is a running value whose step depends non-linearly on the previous result — a balance floored at zero, a capped accrual — which only itertools.accumulate (or a loop) reproduces faithfully. Group with transform for per-entity balances, check REDUCE against the last SCAN value, and write the results as values so every spreadsheet can open them.

Frequently asked questions

What is the Python equivalent of Excel SCAN? itertools.accumulate(values, func, initial=start), dropping the first element so there is one result per row. For plain running sums, products, maxima or minima, pandas' cumsum, cumprod, cummax and cummin are simpler and faster.

Why doesn't cumsum().clip(lower=0) match my SCAN with MAX(0, ...)? Clipping after the cumulative sum does not feed the clipped value into the next step. The SCAN formula resets the balance to zero and continues from there, which requires an accumulation that applies the floor at each step, such as itertools.accumulate.

How do I translate Excel REDUCE to Python? Use functools.reduce(func, values, initial) for a single accumulated value, or an aggregation such as sum when the step is a plain addition. For several accumulated values, a loop with named variables is clearer.

What replaces BYROW in pandas? Row-wise aggregations such as df.max(axis=1) or df.sum(axis=1), or df.apply(func, axis=1) for custom logic that has no vectorised form.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions in pandas.
• Write LET and LAMBDA Formulas from Python — when the workbook should keep the formulas.
• Excel IF Formulas as pandas Conditional Columns — vectorised conditions for MAP-style logic.
• Calculate FIFO Inventory Valuation with Python — a stateful inventory calculation in practice.