Pandas高效过滤DataFrame：多子串非正则不区分大小写匹配优化

Great question! When dealing with large DataFrames and multiple substrings, the naive approach of checking every substring against every row can get pretty slow—especially since you don't need to check all substrings once a match is found. Let's break down how to optimize this with short-circuit matching and some preprocessing tricks.

Key Issues with the Original Approach #

Your current method uses np.logical_or.reduce with a list of str.contains calls. This means:

• Every substring is checked against the entire column, even if a row already matched an earlier substring.
• For each substring, Pandas scans the full column, leading to O(n*m) time complexity (n = rows, m = substrings).

Optimized Solution: Short-Circuit Matching #

The goal is to stop checking substrings for a row as soon as we find a match. Here's how to do it efficiently:

Step 1: Preprocess for Case Insensitivity

First, convert both the target column and all substrings to lowercase (or uppercase) once. This avoids repeating case conversion for every match check, which saves a lot of overhead:

import pandas as pd

# Your input data
df = pd.DataFrame({'text': ['hello kdsj;af-!? world', 'test abc+dsfa?\\-', 'no match here', 'sdkaJg|dksaf-* example']})
lst = ['kdSj;af-!?', 'aBC+dsfa?\-', 'sdKaJg|dksaf-*']

# Preprocess: lowercase everything, handle NaNs to avoid errors
lower_substrings = [s.lower() for s in lst]
lower_text = df['text'].fillna('').str.lower()

Step 2: Apply Short-Circuit Matching with any()

Use apply with a lambda that checks if any substring exists in the row. The any() function short-circuits—it stops checking as soon as it finds a True value:

# Generate the mask with short-circuit logic
mask = lower_text.apply(lambda row: any(sub in row for sub in lower_substrings))

# Filter the DataFrame
df_filtered = df[mask]

Why This Is Faster #

• Per-row short-circuiting: For rows that match an early substring, we skip checking the remaining 99 substrings. This can cut down computation time drastically, especially if most matches happen in the first few substrings.
• Single case conversion: We only convert the column and substrings to lowercase once, instead of doing it for every str.contains call.

Bonus: Speed Up with Parallel Processing #

For extremely large DataFrames (1M+ rows), you can use the swifter library to automatically parallelize the apply operation. Swifter chooses the fastest execution method (vectorized, Dask, or regular apply) based on your data:

1. Install swifter first:

pip install swifter

2. Modify the mask generation:

import swifter

mask = lower_text.swifter.apply(lambda row: any(sub in row for sub in lower_substrings))

Performance Comparison #

Let's say 50% of your rows match the first substring. With the original method, those rows are still scanned 100 times (once per substring). With the optimized method, those rows are only scanned once—until the first match is found. This can lead to 10-100x faster execution depending on your data distribution.

内容的提问来源于stack exchange，提问作者jpp