如何基于字典高效检查DataFrame两列匹配关系并生成布尔结果列

Hey there! Nested loops are definitely a performance killer when working with Pandas DataFrames—luckily, we can leverage Pandas' built-in vectorized operations to solve this way faster. Let's walk through two solid solutions for your use case.

Problem Recap #

You have a DataFrame with a fruit column (matching keys in your check_dict) and a feature column. You need to add a new column that flags whether each row's feature is in the corresponding list from check_dict.

Solution 1: map + apply (Simple & Readable) #

This approach is straightforward and great for smaller to medium-sized datasets. We first use map to pull the valid feature list for each fruit, then check if the row's feature is in that list.

import pandas as pd

# Your sample data
df_dict = {'fruit':['apple', 'apple', 'banana', 'banana', 'orange'], 
           'feature':['sweet', 'green', 'sweet', 'red', 'square']}
df = pd.DataFrame.from_dict(df_dict)
check_dict = {'apple': ['sweet', 'green'], 
              'banana':['sweet', 'yellow'], 
              'orange':['round', 'orange']}

# Step 1: Map each fruit to its valid feature list
df['valid_features'] = df['fruit'].map(check_dict)

# Step 2: Check if the row's feature is in the valid list
df['is_valid'] = df.apply(lambda row: row['feature'] in row['valid_features'], axis=1)

# Optional: Clean up the intermediate column
df = df.drop('valid_features', axis=1)

Solution 2: Explode Dictionary + Merge (Blazing Fast for Large Data) #

If you're working with a huge DataFrame, this method is even better. We convert your validation dictionary into a long-format DataFrame, then use Pandas' optimized merge operation to match valid pairs—no row-by-row loops needed.

import pandas as pd

# Your sample data (same as before)
df_dict = {'fruit':['apple', 'apple', 'banana', 'banana', 'orange'], 
           'feature':['sweet', 'green', 'sweet', 'red', 'square']}
df = pd.DataFrame.from_dict(df_dict)
check_dict = {'apple': ['sweet', 'green'], 
              'banana':['sweet', 'yellow'], 
              'orange':['round', 'orange']}

# Step 1: Convert check_dict to a long DataFrame
check_df = pd.DataFrame([
    (fruit, valid_feature) 
    for fruit, features in check_dict.items() 
    for valid_feature in features
], columns=['fruit', 'valid_feature'])

# Step 2: Merge with original DataFrame to find matches
merged_df = df.merge(
    check_df,
    how='left',
    left_on=['fruit', 'feature'],
    right_on=['fruit', 'valid_feature']
)

# Step 3: Flag valid rows (non-null means a match was found)
df['is_valid'] = merged_df['valid_feature'].notna()

What You'll Get #

After running either solution, your DataFrame will look like this:

Why These Are Better Than Loops #

• Vectorized operations (like map and merge) are implemented in optimized C code under the hood, so they're way faster than Python-level loops.
• Solution 2 scales especially well with large datasets because merge is designed to handle bulk matching efficiently.

内容的提问来源于stack exchange，提问作者Floris van Eijk