Full-text search over a pandas DataFrame

If your data already lives in a DataFrame, you probably reach for str.contains first. It is fine for "does this cell contain this literal substring", but it breaks down the moment you want search: it does not rank results, it will not match wireless against Wireless unless you lower-case first, it cannot match word variants (run vs running), and it searches one column at a time. Whoosh gives you a proper inverted index with BM25 ranking across several columns at once, and it stays pure Python so it drops straight into a notebook or a small service.

pip install whoosh3 pandas

1. Build an index from a DataFrame

Define a schema that mirrors the columns you care about. Make text columns TEXT (analyzed and searchable), keep numbers as NUMERIC so you can filter and sort on them, and add a unique ID field that stores the DataFrame's row label. That id is the bridge back to your rows.

import pandas as pd
from whoosh.fields import Schema, TEXT, ID, NUMERIC
from whoosh.index import create_in
import tempfile

df = pd.DataFrame([
    {"title": "Wireless headphones", "body": "Over-ear bluetooth noise cancelling", "price": 199.0},
    {"title": "USB-C cable",         "body": "Fast charging braided nylon 2m",      "price": 9.99},
    {"title": "Mechanical keyboard", "body": "Hot-swappable RGB bluetooth wireless","price": 89.5},
    {"title": "Noise machine",       "body": "White noise sleep aid",               "price": 34.0},
])

schema = Schema(
    id=ID(stored=True, unique=True),
    title=TEXT(stored=True),
    body=TEXT(stored=True),
    price=NUMERIC(stored=True, numtype=float, sortable=True),
)

ix = create_in(tempfile.mkdtemp(), schema)
writer = ix.writer()
for row_id, row in df.iterrows():
    writer.add_document(
        id=str(row_id),
        title=row["title"],
        body=row["body"],
        price=float(row["price"]),
    )
writer.commit()

ID fields are stored as strings, so cast the row label with str(...) going in and int(...) coming back out (or keep string labels if your index is not the default RangeIndex).

2. Search across multiple columns and rank by relevance

Use a MultifieldParser to search title and body together. Whoosh scores every match with BM25 by default, so the most relevant rows come first.

from whoosh.qparser import MultifieldParser
from whoosh import scoring

with ix.searcher(weighting=scoring.BM25F()) as s:
    qp = MultifieldParser(["title", "body"], schema=ix.schema)
    q = qp.parse("wireless bluetooth")
    results = s.search(q, limit=5)
    for hit in results:
        print(round(hit.score, 3), hit["id"], hit["title"], "$" + str(hit["price"]))

2.954 0 Wireless headphones $199.0
2.918 2 Mechanical keyboard $89.5

Both rows matched wireless and bluetooth even though those words are split across the title and body columns — something a single-column str.contains can't do.

3. Map hits back to DataFrame rows

The searcher returns the stored fields, but the whole point of keeping the id is to jump back to the full row (including columns you never indexed). Collect the ids and hand them to df.loc:

with ix.searcher(weighting=scoring.BM25F()) as s:
    q = MultifieldParser(["title", "body"], schema=ix.schema).parse("wireless bluetooth")
    ids = [int(hit["id"]) for hit in s.search(q, limit=5)]

matched = df.loc[ids]   # rows in relevance order
print(matched)

df.loc[ids] preserves the ranked order of ids, so your results stay sorted by relevance, not by DataFrame order.

4. Combine text search with numeric column filters

Because price is a NUMERIC field, you can filter on it with a NumericRange query and combine it with the text query — the classic "matching products under $200" pattern.

from whoosh.query import NumericRange, And

with ix.searcher(weighting=scoring.BM25F()) as s:
    text_q  = MultifieldParser(["title", "body"], schema=ix.schema).parse("wireless")
    price_q = NumericRange("price", None, 200.0)   # price <= 200
    hits = s.search(And([text_q, price_q]), limit=10)
    print([(h["id"], h["title"], h["price"]) for h in hits])

You can also skip text entirely and use Whoosh purely as a filter/sort engine over numeric columns — search(NumericRange("price", None, 200.0), sortedby="price") — but its real strength is mixing free-text relevance with structured filters in one query.

5. Keep the index in sync when the DataFrame changes

You do not have to rebuild the whole index every time a row changes. Because id is unique=True, update_document atomically replaces the matching document (delete + add), so edits don't create duplicates:

writer = ix.writer()
writer.update_document(id="0", title="Wireless headphones (v2)", body="...", price=149.0)
writer.commit()

with ix.searcher() as s:
    print(s.doc_count())   # -> 1 for that id, not 2

Verified output: after updating row 0 the searcher returns the new title and price, and doc_count() stays at the expected value instead of doubling. For deletes, use writer.delete_by_term("id", "0"). For a large batch of edits, collect the changed row ids and loop over update_document inside a single writer/commit.

When to use this (and when not to)

• Use it when you need ranked relevance, multi-column search, stemming, phrase queries, or "did you mean" over tabular text — and you want it to run wherever pandas runs, with no search server.
• Skip it for one-off exact-substring lookups on a small frame; str.contains is simpler there.
• For millions of rows, build the index once and persist it to disk with open_dir instead of a temp dir, and index in batches.

The same to_dict("records") / unique-id pattern works for any tabular source — a CSV, a SQL query result, or a list of dicts from an API. See the analyzer recipes to control tokenization per column (accent folding, stemming, n-grams), and fuzzy search for typo-tolerant matching.