UNIQUE, SORT and FILTER Equivalents in pandas

Excel's dynamic array functions — UNIQUE, SORT, SORTBY and FILTER — made spreadsheets behave a little like pandas: one formula returns a whole list that spills into the cells below. Going the other way is therefore natural, and each has a close pandas counterpart. The details differ in ways that change results, though: pandas sorts text case-sensitively, UNIQUE's "exactly once" option has no direct keyword, and FILTER's fallback for an empty result has to be written by hand. This page, part of Excel Formula Equivalents in pandas, translates each function and the chain people build from them.

Prerequisites

pip install pandas openpyxl

invoices.xlsx has eight invoices for five customers, with inconsistent capitalisation — contoso and Contoso, adatum — which matters for both uniqueness and sorting.

Step 1 — UNIQUE: distinct values in order of appearance

Series.unique() returns each distinct value once, in the order it first appears — exactly what =UNIQUE(A2:A9) spills:

import pandas as pd

inv = pd.read_excel("invoices.xlsx")
print(inv["customer"].unique().tolist())
print(inv["customer"].drop_duplicates().tolist())

Both give the same list; drop_duplicates returns a Series (with the original row labels) rather than an array, which is handier for chaining. Note that contoso and Contoso are both there. pandas compares values exactly, while Excel's UNIQUE ignores case and would return only the first of the two spellings. To match Excel, compare on a normalised key and keep the first spelling:

first_spelling = inv.loc[~inv["customer"].str.lower().duplicated(), "customer"]
print(first_spelling.tolist())

Step 2 — UNIQUE across columns, and "exactly once"

For distinct combinations — =UNIQUE(A2:B9) over customer and region — use drop_duplicates with a subset. Excel's third argument, exactly_once=TRUE, returns only values that occur a single time; the pandas form is duplicated(keep=False), which marks every copy of a repeated value:

pairs = inv[["customer", "region"]].drop_duplicates()
print(pairs.to_string(index=False))

once = inv.loc[~inv["customer"].duplicated(keep=False), "customer"]
print("appear exactly once:", once.tolist())

Northwind appears in two regions, so it contributes two pairs. The exactly-once list is customers invoiced a single time — useful for spotting one-off buyers, or typos: contoso appears once because its capitalisation differs from Contoso, a data-quality signal worth acting on. Find Duplicate Rows in Excel with Python covers duplicate detection in depth.

Step 3 — SORT and SORTBY: mind the case

sort_values sorts by one or more columns, each ascending or descending, which covers both SORT and SORTBY. Text is where results differ from Excel:

names = inv["customer"].drop_duplicates()
print(names.sort_values().tolist())
print(names.sort_values(key=lambda s: s.str.lower()).tolist())

The first result puts every capitalised name before every lower-case one — Contoso, Fabrikam, Litware, Northwind, then adatum, contoso — because pandas compares character codes. Excel's SORT ignores case and would give adatum, then the two Contosos, then Fabrikam, Litware and Northwind. The key argument applies a function to the column before comparing, so lower-casing gives the Excel order while keeping the original values. For several sort columns with different directions — SORTBY by region ascending and amount descending — pass lists:

by = inv.sort_values(["region", "amount"], ascending=[True, False], kind="stable")
print(by.to_string(index=False))

kind="stable" keeps rows with equal keys in their original order, which is how Excel behaves and what readers expect when two invoices tie.

Step 4 — FILTER: boolean masks, and an empty result

=FILTER(A2:D9, D2:D9="open") keeps the rows where the condition is true. In pandas the condition is a boolean Series used as an index; several conditions combine with & (and) and | (or), each in brackets:

open_big = inv[(inv["status"] == "open") & (inv["amount"] >= 100)]
print(open_big.to_string(index=False))

south_or_void = inv[(inv["region"] == "South") | (inv["status"] == "void")]
print(len(south_or_void), "rows")

Excel's third argument, if_empty, supplies a value when nothing matches, because an empty spill is an error in a sheet. In pandas an empty result is simply an empty DataFrame, which is usually what code wants. For a report, check .empty and write the fallback yourself:

refunds = inv[inv["status"] == "refunded"]
message = "No refunds this period." if refunds.empty else f"{len(refunds)} refunds"
print(message)

FILTER against a list, and TAKE the first rows

Two more patterns come up constantly. The first filters rows whose value appears in another list — "invoices for our key accounts" — which in Excel is FILTER combined with ISNUMBER(MATCH(...)) or COUNTIF(...)>0. In pandas it is isin:

key_accounts = ["Northwind", "Fabrikam"]
print(inv[inv["customer"].isin(key_accounts)]["amount"].sum())
print(inv[~inv["customer"].isin(key_accounts)]["customer"].unique().tolist())

The key accounts' invoices total 560, and the negated mask lists everyone else. When the list comes from another sheet, read it into a Series and pass that — isin accepts any list-like, and is far faster than a MATCH per row.

The second takes the first rows of a sorted result — Excel 365's TAKE(SORT(...), 3). That is head, or nlargest when the sort is by a number:

print(inv.sort_values("amount", ascending=False).head(3)[["customer", "amount"]].to_string(index=False))
print(inv.nlargest(3, "amount")[["customer", "amount"]].to_string(index=False))

Both return Fabrikam's 300, adatum's 210 and Northwind's 120. nlargest does not need a full sort and is quicker on large tables; keep="all" on it includes rows tied with the last one, the choice Get the Top N Rows per Group in pandas discusses.

Step 5 — Chain them: SORT(UNIQUE(FILTER()))

The most common combination — "the sorted list of customers with open invoices" — is three nested functions in Excel and a readable chain in pandas:

open_customers = (inv.loc[inv["status"] == "open", "customer"]
                     .drop_duplicates()
                     .sort_values(key=lambda s: s.str.lower())
                     .tolist())
print(open_customers)

adatum, contoso, Fabrikam — filtered, de-duplicated and sorted case-insensitively. Written as a chain, each step can be commented out, printed or inspected on its own while debugging, which is the part nested spreadsheet formulas make hard. Normalise the spellings first (contoso → Contoso) if the list goes to readers; Standardise Category Values with a Mapping Table in pandas shows how.

Step 6 — Write the result, or write the formula

A computed list can be written as values, or — when readers should see it update as they edit the data — as the dynamic array formula itself. Both are a few lines:

from openpyxl import load_workbook

inv.to_excel("invoices_out.xlsx", index=False, sheet_name="Invoices")
wb = load_workbook("invoices_out.xlsx")
ws = wb.create_sheet("Open customers")
ws.append(["customer"])
for name in open_customers:
    ws.append([name])
wb.save("invoices_out.xlsx")
print(wb.sheetnames, [c.value for c in wb["Open customers"]["A"]])

Writing =SORT(UNIQUE(FILTER(...))) from Python instead needs the formulas stored with the right prefixes so Excel recognises them; Write Dynamic Array Formulas like FILTER and UNIQUE from Python covers that, and Fix #NAME? in Formulas Written from Python explains what happens without them.

Common pitfalls

• Case-sensitive sorting. pandas puts capitals first; use a lower-case key to match Excel.
• Case in UNIQUE. pandas keeps Contoso and contoso apart where Excel does not; normalise first.
• and/or in masks. Use & and |, with each condition in brackets.
• Assuming an empty FILTER errors. It returns an empty DataFrame; handle .empty for display.
• Filtering with == against a list. df[df.customer == ["A", "B"]] compares element by element and fails or misleads; use isin for membership.
• Chaining on a filtered slice and then assigning. Take a .copy() of a filtered frame before adding columns, so pandas does not warn about writing to a view and the original stays unchanged.

Performance notes

drop_duplicates, sort_values and boolean masks are all vectorised and handle millions of rows quickly. A key function adds one pass over the column, which is negligible; sorting on a precomputed lower-case column is marginally faster when the same order is needed repeatedly. isin builds a hash set from the list once, so filtering a million rows against a list of a thousand keys takes milliseconds — the pandas advantage over a MATCH evaluated for every row.

Conclusion

UNIQUE is unique() or drop_duplicates() (with subset for several columns and duplicated(keep=False) for "exactly once"), SORT and SORTBY are sort_values with lists of columns and directions — plus key=lambda s: s.str.lower() to ignore case as Excel does — and FILTER is a boolean mask, with .empty standing in for if_empty. Chain them for the SORT(UNIQUE(FILTER())) pattern, and write either the values or the dynamic array formula back to the workbook.

Frequently asked questions

What is the pandas equivalent of UNIQUE? Series.unique() or drop_duplicates() for a column, and df.drop_duplicates(subset=...) for combinations of columns. Both keep the order in which values first appear, as Excel's UNIQUE does.

How do I get values that appear exactly once, like UNIQUE(range, , TRUE)? Keep the rows that are not duplicated at all: s~s.duplicated(keep=False). keep=False marks every copy of a repeated value, so only the singletons remain.

Why does pandas sort "Banana" before "apple"? pandas compares strings by character code, and all capital letters come before lower-case ones. Excel's SORT ignores case. Pass key=lambda s: s.str.lower() to sort_values to sort the way Excel does.

What replaces FILTER's if_empty argument? A boolean mask returns an empty DataFrame when nothing matches, which is usually the right result in code. For a report cell, check .empty and write the fallback text yourself.

Related

• Up one level: Excel Formula Equivalents in pandas — every function, translated.
• Sort Excel Rows with Python Before Writing — sorting as part of a report.
• Count Distinct Values per Group in pandas — counting the unique values instead of listing them.
• TEXTJOIN Equivalent in pandas — joining a unique list into one cell.