EXACT and Case-Sensitive Comparisons in pandas

A report moved from Excel to pandas counts six different product codes where the spreadsheet always showed three. Nothing is broken; the two tools simply disagree about whether AB-100 and ab-100 are the same text. Excel's comparisons are case-insensitive: the = operator, COUNTIF, MATCH, XLOOKUP, VLOOKUP and UNIQUE all treat upper and lower case as equal, and only the EXACT function tells them apart. pandas does the opposite: ==, value_counts, nunique, merge and drop_duplicates all compare exactly, character for character. Neither is wrong, but a migration has to decide which behaviour the report needs and reproduce it on purpose. This page shows Excel's results for a small set of codes, then rebuilds each one in pandas — the case-insensitive ones with casefold and the case-sensitive EXACT with plain equality. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas openpyxl

The sample is six product codes as they might arrive from different systems: AB-100 in three spellings, one with a trailing space, and CD-200 in two. Beside them are eight Excel formulas that compare and count them. LibreOffice calculates the formulas so the pandas results can be checked against real spreadsheet behaviour.

Step 1 — What Excel says

soffice --headless --convert-to 'csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true' codes.xlsx > /dev/null 2>&1
cut -d, -f3- codes.csv

The results show Excel's rules plainly. "AB-100"="ab-100" is TRUE, while EXACT on the same pair is FALSE. COUNTIF for AB-100 finds three cells — AB-100, ab-100 and Ab-100 — but not *AB-100 * with its trailing space, because spaces are significant even when case is not. Counting case-sensitively needs SUMPRODUCT(--EXACT(...)), which finds one. MATCH and XLOOKUP for ab-100 return the first cell, AB-100, because it matches ignoring case. The wildcard COUNTIF for ab* finds four, including the one with the trailing space, since the wildcard absorbs it. And UNIQUE sees three distinct values.

Step 2 — pandas compares exactly by default

import pandas as pd

codes = pd.read_excel("codes.xlsx", usecols="A")["Code"]
print("== 'AB-100':", int((codes == "AB-100").sum()))
print("nunique():", codes.nunique())
print(codes.value_counts().to_dict())

Read into pandas, the same six cells give different answers. Equality with AB-100 matches one cell, not three; nunique reports six distinct codes, not three; and value_counts lists each spelling separately. That is pandas behaving like EXACT everywhere. For a column of identifiers where case carries meaning — some part numbers and most passwords and hashes do — that is right. For codes typed by people into different systems, it is usually not what the report intends, and the old spreadsheet's totals were relying on Excel's case-insensitivity without anyone noticing.

Step 3 — Excel's case-insensitive rules in pandas

key = codes.str.casefold()
target = "ab-100".casefold()

results = {
    '"AB-100"="ab-100"': "AB-100".casefold() == "ab-100".casefold(),
    'EXACT("AB-100","ab-100")': "AB-100" == "ab-100",
    'COUNTIF(...,"AB-100")': int((key == target).sum()),
    "SUMPRODUCT(--EXACT(...))": int((codes == "AB-100").sum()),
    'MATCH("ab-100",...,0)': int(key.eq(target).to_numpy().argmax()) + 1,
    'XLOOKUP("ab-100",...)': codes[key.eq(target)].iloc[0],
    'COUNTIF(...,"ab*")': int(key.str.startswith("ab").sum()),
    "ROWS(UNIQUE(...))": key.nunique(),
}
for formula, value in results.items():
    print(f"{formula:28} {value}")

The pattern is the same throughout: compare a folded version of the text to reproduce Excel, and the original text to reproduce EXACT. str.casefold() is the right tool for the folding — it is like lower() but designed for caseless comparison, so it also handles letters such as the German ß consistently. Every result now matches LibreOffice: equality true, EXACT false, three matches for COUNTIF, one for the case-sensitive count, position 1 for MATCH, AB-100 for XLOOKUP, four for the wildcard and three distinct values. The lookup returns the original spelling of the first match, as Excel does, because the boolean mask built on the folded key selects from the unfolded column.

Wildcards: COUNTIF patterns in pandas

COUNTIF also accepts wildcards: * for any run of characters, ? for exactly one, and ~ to escape a literal * or ?. The ab* example only needed startswith, but general patterns are easier to translate into a regular expression once and reuse:

import re

def excel_pattern(pattern):
    """Translate a COUNTIF wildcard pattern into an anchored, case-insensitive regex."""
    out, i = [], 0
    while i < len(pattern):
        ch = pattern[i]
        if ch == "~" and i + 1 < len(pattern):
            out.append(re.escape(pattern[i + 1])); i += 2; continue
        out.append(".*" if ch == "*" else "." if ch == "?" else re.escape(ch))
        i += 1
    return "^" + "".join(out) + "$"

for p in ["ab*", "??-100", "*200", "AB-100"]:
    print(f"{p:8} {int(codes.str.contains(excel_pattern(p), case=False, regex=True).sum())}")

The translation escapes every ordinary character, turns * and ? into their regex equivalents, honours the ~ escape, and anchors the pattern at both ends — COUNTIF matches the whole cell, not a part of it. case=False gives Excel's case-insensitivity. The counts follow Excel's rules: four for ab*, three for ??-100 (the trailing-space variant has one character too many), two for *200, and three for the plain AB-100, the same as the COUNTIF without wildcards.

Step 4 — Joins and duplicates

prices = pd.DataFrame({"Code": ["AB-100", "CD-200"], "Price": [12.5, 8.0]})
orders = pd.DataFrame({"Code": ["ab-100", "Ab-100", "CD-200", "cd-200"], "Qty": [3, 1, 5, 2]})

exact = orders.merge(prices, on="Code", how="left")
excel_like = (orders.assign(key=orders["Code"].str.casefold())
                    .merge(prices.assign(key=prices["Code"].str.casefold()).drop(columns="Code"), on="key", how="left")
                    .drop(columns="key"))
print("exact merge, unmatched:", int(exact["Price"].isna().sum()))
print("casefold merge, unmatched:", int(excel_like["Price"].isna().sum()))
print(orders.assign(key=orders["Code"].str.casefold()).drop_duplicates("key")["Code"].tolist())

The same choice affects joins. A VLOOKUP from an order list to a price list in Excel finds a price for ab-100 because it ignores case; a pandas merge on the raw codes does not, and three of the four orders come back without a price. Joining on a folded key column — and dropping it afterwards — reproduces the spreadsheet: every order priced. drop_duplicates on the folded key keeps the first spelling of each code, which mirrors UNIQUE. For merges where keys also differ in spacing or punctuation, see Merge on Keys That Differ in Case and Spacing with pandas.

Sorting follows the same split

Sorting is the other place the difference shows up. Excel's sort ignores case by default, so apple, Banana and cherry come out in dictionary order. pandas' sort_values compares the underlying character codes, in which every capital letter comes before every lower-case one, so Banana jumps ahead of apple. Passing a key gives the spreadsheet order:

fruit = pd.Series(["cherry", "Banana", "apple"])
print(fruit.sort_values().tolist(), fruit.sort_values(key=lambda s: s.str.casefold()).tolist())

The first list puts Banana first; the second, apple, Banana, cherry, matches Excel. The key function changes only what is compared, not what is returned, so the values keep their original spelling — the same principle as the lookups above.

Which behaviour should the report use?

Decide per column rather than globally. Identifiers that people type, such as product codes, customer references and email addresses, should usually be matched case-insensitively, as Excel did — and ideally normalised once on import so the question does not arise again. Identifiers generated by systems where case is significant, such as some SKUs, file paths on Linux, tokens and hashes, must be compared exactly. Trailing spaces are a separate problem: neither Excel nor pandas ignores them, so *AB-100 * matches nothing in either tool until it is stripped. Documenting the choice next to the code that makes it saves the next person from rediscovering why the counts differ from the old spreadsheet.

When the answer is "case-insensitive", the cleanest implementation is often not to fold at every comparison but to normalise the column once, right after reading: upper-case the codes, strip the spaces, and store that as the canonical value. Every later merge, value_counts and drop_duplicates then behaves like Excel without any special handling, and the output shows one consistent spelling instead of whichever variant happened to come first. Keep the original column alongside if anyone needs to trace a value back to the source file.

Common pitfalls

• Expecting pandas to ignore case. ==, merge and nunique are case-sensitive.
• Using lower() for comparison. casefold() is the caseless-comparison method.
• Returning the folded text. Select from the original column so lookups keep the source spelling.
• Forgetting spaces. Both tools treat a trailing space as part of the text; strip it.
• One rule for every column. Some identifiers are case-sensitive by design.

Conclusion

Excel ignores case in =, COUNTIF, MATCH, XLOOKUP, VLOOKUP and UNIQUE, and only EXACT compares it; pandas compares case everywhere. To reproduce a spreadsheet, compare and join on str.casefold() versions of the text and select results from the original column; to reproduce EXACT, use plain equality. Decide per column, and strip spaces separately, since neither tool ignores them.

Frequently asked questions

What is the pandas equivalent of Excel's EXACT? Plain equality, s == "AB-100", which compares case and every character.

How do I compare strings case-insensitively in pandas like Excel? Compare s.str.casefold() with the folded target, for example s.str.casefold() == "ab-100".casefold().

Is COUNTIF case-sensitive? No; COUNTIF ignores case. Count case-sensitively in Excel with SUMPRODUCT(--EXACT(range, value)).

Why does pandas count more unique values than Excel?nunique treats different capitalisations as different values while Excel's UNIQUE does not; use s.str.casefold().nunique() to match Excel.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions in pandas.
• COUNTIF and COUNTIFS Equivalent in pandas — conditional counts.
• XLOOKUP Equivalent in pandas — lookups in pandas.
• Fix KeyError from Spaces in Excel Column Names — invisible differences in text.