如何高效对DataFrame中的逗号分隔过敏原变量执行mutate操作并去除多语言及大小写重复值（针对25万条数据集）

For a 250k-row dataset, the most efficient approach leverages pandas' vectorized operations (avoiding slow row-wise loops) to standardize allergens, remove duplicates, and consolidate multilingual terms. Here's a step-by-step solution:

Step 1: Define Allergen Mapping #

First, create a dictionary that maps all multilingual/case variants to a standard English term. This is critical for unifying terms like Milch, Lait, and milk into a single entry.

import pandas as pd

# Expand this to cover all unique terms in your dataset
allergen_mapping = {
    "milk": "milk",
    "milch": "milk",
    "lait": "milk",
    "soja": "soja",
    "almond": "almond",
    "nuts": "nuts",
    "wheat": "wheat",
    "cheese": "cheese",
    "butter": "butter",
    "cream": "cream"
}

Step 2: Process the DataFrame #

We'll use vectorized string operations, explode, and groupby to efficiently clean the data without looping through rows:

# Sample DataFrame (replace with your actual data)
df = pd.DataFrame({
    "ID": [1, 2, 3, 4],
    "Product": ["cow milk", "Almond milk", "Soja milk", "Fried Cheese"],
    "Allergens": [
        "Milk, Milch, Lait",
        "Milk, Milk, Almond, Nuts",
        "Soja, soja, lait, Lait, Milch",
        "WHEAT, MILK, MILK, MILK, WHEAT, WHEAT, MILK, CHEESE, MILK, CHEESE, CHEESE, MILK, CHEESE, CHEESE, MILK, CHEESE, WHEAT, BUTTER, CREAM"
    ]
})

# 1. Split allergen strings into individual entries, clean whitespace, and lowercase
df_exploded = df.assign(Allergens=df["Allergens"].str.split(", ")).explode("Allergens")
df_exploded["Allergens"] = df_exploded["Allergens"].str.strip().str.lower()

# 2. Map variants to standard terms
df_exploded["standard_allergen"] = df_exploded["Allergens"].map(allergen_mapping)

# 3. Deduplicate per product (keep only unique standard allergens)
df_clean = df_exploded.groupby(["ID", "Product"])["standard_allergen"].unique().reset_index()

# 4. Join back into a clean, comma-separated string (capitalize for readability)
df_clean["Allergens"] = df_clean["standard_allergen"].apply(
    lambda x: ", ".join([term.capitalize() for term in x if pd.notna(term)])
)

# Drop temporary column
df_clean = df_clean.drop("standard_allergen", axis=1)

Step 3: Verify the Result #

The output will match your expected format:

Key Efficiency Notes #

• Vectorized Operations: Using str.split, explode, and groupby avoids slow iterrows() or row-wise apply(), which is critical for handling 250k rows quickly.
• Mapping: The dictionary lookup is O(1) per entry, making this step extremely fast even for large datasets.
• Deduplication: groupby + unique() efficiently removes duplicates without redundant checks.

Pro Tip: Build a Complete Mapping #

To ensure you cover all terms in your dataset, first extract all unique allergen variants:

unique_allergens = df["Allergens"].str.split(", ").explode().str.strip().str.lower().unique()
print(unique_allergens)

Add any missing terms to your allergen_mapping to avoid NaN values in the cleaned data.

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