判断Pandas DataFrame列是否包含列表元素的高效方法

Problem Overview #

You’re working with a large Pandas DataFrame (millions of rows) where one column contains comma-separated strings. Your goal is to add a boolean column that marks whether each string includes any element from a given list. Your initial regex approach works for small datasets, but you’re concerned about its efficiency at scale.

Original Regex Approach #

Here’s the method you’re currently using, which leverages regex pattern matching with str.contains():

import pandas as pd

df = pd.DataFrame({'A':['a,b,c','b,c,d,e','a,b,e,f','a,c,d,f']})
lst1 = ['w','x','y','z','b']

regex = '|'.join(['(?:{})'.format(i) for i in lst1])
df['B'] = df['A'].str.contains(regex)

Alternative: Set Intersection Method #

Another approach uses set operations for fast membership checks. Here’s how to implement it:

1. Convert your target list into a set (this enables O(1) lookup time)
2. Split each comma-separated string into a set of elements
3. Check if the intersection between the string’s set and the target set is non-empty

target_set = set(lst1)
df['B'] = df['A'].str.split(',').apply(lambda x: bool(set(x) & target_set))

Performance Comparison #

To put both methods to the test, we ran benchmarks on a 4 million-row DataFrame:

• Regex method: 1 loop, best of 3: 8.09 s per loop
• Set intersection method: 1 loop, best of 3: 8.36 s per loop

Interestingly, the performance is nearly identical! The regex approach’s speed can shift based on specific dataset factors like:

• The average length of the comma-separated strings
• The number of elements in your target list
• How frequently matches appear across the DataFrame

Key Takeaways #

• Both methods are robust for large datasets, with regex being slightly faster in the tested scenario
• If your data has extra-long strings or a very large target list, benchmark both approaches with your actual dataset to see which performs better
• Set operations can feel more intuitive if you want to avoid regex edge cases (like partial matches, though that’s not an issue here with exact element checks)

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