判断DataFrame列值是否存在于另一DataFrame多列（时间复杂度优化）

Hey there! Let's tackle this performance issue and fix a hidden logic bug in your original code at the same time.

First, let's break down what went wrong with your attempts:

• Your apply() approach is slow for large datasets because it relies on row-by-row Python loops, which can't match the speed of pandas' optimized vectorized operations.
• Your isin() attempt failed because df['A'].isin(df2[['B','C']]) checks if each value exists in the entire 2D DataFrame structure (not across the two columns as a single pool of values), hence the all-False result. Also, your original lambda has a bug: listb or listc returns the first non-empty list, so you were only checking against listb, not both columns!

The Fast, Vectorized Solution #

The core idea is to combine all values from df2's B and C columns into a single 1D collection, then use pandas' built-in isin() (a fast, vectorized operation) to check membership.

Method 1: Use a Set (Fastest for Lookups)

Set membership checks are O(1) thanks to hash tables, making this the quickest option for large datasets:

# Combine B and C columns into a single set of unique values
combined_values = set(df2['B'].tolist() + df2['C'].tolist())
# Or a more concise way to flatten all values:
# combined_values = set(df2.values.flatten())

# Apply the check to df['A']
df['test'] = df['A'].isin(combined_values)

Method 2: Reshape df2 with melt()

If you prefer staying strictly within pandas' API without converting to a Python set, use melt() to reshape df2 into a long-format Series, then keep only unique entries:

# Reshape df2 to get all B/C values in one column, then deduplicate
all_values = df2.melt(value_vars=['B', 'C'])['value'].unique()

# Check membership against the deduplicated values
df['test'] = df['A'].isin(all_values)

Test with Your Sample Data #

Using your provided df and df2, both methods will correctly set df['test'] to [True, False, True], matching your expected output.

Why This Is Faster #

• Vectorized operations like isin() run in optimized C-level code, avoiding the overhead of row-by-row Python loops (like apply()).
• Using a set or deduplicated values reduces the number of checks needed, especially if df2 has duplicate entries.

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