Pandas列子集匹配指定代码列表的高效实现方法

Hey there! I totally get the pain of dealing with slow row-wise operations on a huge DataFrame—nothing kills productivity like waiting for a loop to chug through millions of rows. Let's fix this with some vectorized pandas magic that'll speed things up drastically.

Why Your Current Method Is Slow #

If you're using iterrows() or a row-wise apply() to check each code list, you're forcing Python to loop through every row individually. This bypasses pandas' optimized C-based operations, which is a huge performance hit for large datasets.

Efficient Solutions #

First, start by converting your approved codes list into a set—set lookups are O(1) (instant) compared to list lookups which are O(n):

approved_codes = {'abc', 'def'}  # Use a set instead of a list!

Case 1: Your Column Contains Lists/Sets of Codes #

If your target column (let's call it code_collection) has rows that are iterables like lists or sets, use this exploded-groupby approach:

# Explode each list into individual rows
exploded_df = df.explode('code_collection')

# Check if each code is approved, then group back to original rows and check if any were approved
df['has_approved_code'] = exploded_df['code_collection'].isin(approved_codes).groupby(exploded_df.index).any()

This leverages pandas' optimized vectorized operations under the hood, avoiding Python-level loops entirely. It's way faster than row-wise apply() for large DataFrames.

Case 2: Your Column Contains Delimited Strings (e.g., "abc,xyz,ghi") #

If your codes are stored as a single string with separators, use str.contains() with a regex pattern:

# Create a regex pattern matching any approved code
pattern = '|'.join(approved_codes)

# Check if any approved code exists in the string
df['has_approved_code'] = df['code_string'].str.contains(pattern, regex=True, na=False)

This is also fully vectorized and will handle even massive datasets quickly.

Bonus: If You Must Use Apply (For Complex Logic) #

If you have more complex checks than just membership, stick to apply() but keep the approved codes as a set to speed up lookups:

# Still way faster than using a list for approved_codes
df['has_approved_code'] = df['code_collection'].apply(lambda codes: any(c in approved_codes for c in codes))

Note: This is still slower than the exploded method, but better than a row-wise loop with list lookups.

Performance Tips #

• Always use sets for membership checks instead of lists—this alone can speed up your existing code significantly.
• Avoid iterrows() at all costs for large DataFrames; it's the slowest possible way to iterate.
• For extremely large datasets (10M+ rows), consider using Dask DataFrames to parallelize operations across multiple cores.

Give these methods a try—you'll be shocked at how much faster your code runs compared to row-wise loops!

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