如何用Pandas将非标准化商品标题匹配至正则关联的标准化信息？

Got it, let's walk through how to solve this problem step by step. We need to take the regex patterns from regex_titles, match them against product titles in raw_titles, and fill in the corresponding Manufacturer and Model values where matches occur.

Step 1: Define Sample Data #

First, let's recreate the sample DataFrames you provided:

import pandas as pd

# Raw titles DataFrame with unstandardized titles
raw_titles = pd.DataFrame({
    'Title': [
        'Apple iPad Air (3rd generation) - 64GB',
        'Philips Hue White Ambiance A19 LED Smart Bulbs',
        'Powerbeats Pro Totally Wireless Earphones'
    ],
    'Release Date': ['01/01/20', '08/12/20', '06/20/19']
})

# Regex rules DataFrame with standardized mappings
regex_titles = pd.DataFrame({
    'Regex': [
        r'ipad\s?air(?=.*(\b3\b|3rd\s?gen|2019))|\bair\s?3\b',
        r'hue(?=.*cher)',
        r'powerbeats\s?pro'
    ],
    'Manufacturer': ['Apple', 'Philips', 'Beats'],
    'Model': ['iPad Air (2019)', 'Hue White Ambiance Cher', 'Powerbeats Pro']
})

Step 2: Implement Matching Logic #

We'll start by adding empty columns for Manufacturer and Model, then iterate through each regex rule to check for matches. We use str.contains with case=False to replicate the case-insensitive /i modifier from your regex examples.

# Initialize empty result columns
raw_titles['Manufacturer'] = ''
raw_titles['Model'] = ''

# Loop through each regex rule and update matching rows
for _, rule in regex_titles.iterrows():
    # Create a mask for titles that match the current regex (case-insensitive)
    match_mask = raw_titles['Title'].str.contains(rule['Regex'], case=False, regex=True)
    
    # Optional: Only update rows that haven't been matched yet (prevents overwriting)
    # match_mask = match_mask & raw_titles['Manufacturer'].eq('')
    
    # Populate manufacturer and model for matching rows
    raw_titles.loc[match_mask, 'Manufacturer'] = rule['Manufacturer']
    raw_titles.loc[match_mask, 'Model'] = rule['Model']

Step 3: View the Final Result #

Running the code above will produce your desired output:

Key Notes #

• The optional line with raw_titles['Manufacturer'].eq('') ensures that if a title matches multiple regex rules, only the first match is kept (no overwrites). Remove it if you want later rules to take precedence.
• We use raw strings (r'') for regex patterns to avoid escaping issues in Python.
• str.contains handles regex matching efficiently, even for larger datasets.

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