利用Pandas通过DataFrame自由文本字段实现跨表匹配查询

Hey there! I see you're trying to map free-text hotel reviews from df1 to the keywords, departments, and issue types in df2 but haven't had luck yet. Let's walk through some reliable solutions tailored to this exact problem—assuming your df1 has a column like Review for the text, and df2 has Keyword, Department, and Issue Type columns.

Method 1: Basic String Matching (Exact/Fuzzy) #

If your keywords are simple single words or phrases (like "dirty" or "front desk"), this straightforward Pandas approach will work. We'll map each keyword to its department/issue type, then check each review for matches.

First, set up a keyword-to-info mapping:

import pandas as pd

# Create a dictionary linking each keyword to its department and issue type
keyword_map = df2.set_index('Keyword')[['Department', 'Issue Type']].to_dict('index')

Then write a function to scan each review for matching keywords:

def find_matching_keywords(review):
    matches = []
    for keyword, details in keyword_map.items():
        # Use str.contains with case=False to ignore capitalization differences
        if pd.Series(review).str.contains(keyword, case=False).iloc[0]:
            matches.append({
                'Matched_Keyword': keyword,
                'Department': details['Department'],
                'Issue_Type': details['Issue Type']
            })
    # Return empty values if no matches found
    return matches if matches else [{'Matched_Keyword': None, 'Department': None, 'Issue_Type': None}]

# Apply the function to every review in df1
df1['Match_Details'] = df1['Review'].apply(find_matching_keywords)

# Expand nested matches into separate rows (great if one review hits multiple keywords)
df_matched = df1.explode('Match_Details').reset_index(drop=True)

# Split the match details into individual columns
df_matched = pd.concat([df_matched.drop('Match_Details', axis=1), df_matched['Match_Details'].apply(pd.Series)], axis=1)

Method 2: Regex Matching (For Precise Word Matching) #

If you want to avoid partial matches (e.g., not matching "dirtying" when your keyword is "dirty"), regex with word boundaries is the way to go. It also handles keyword variants better if you adjust the pattern.

First, add regex patterns to your df2:

import re

# Add a regex pattern column with word boundaries to match full words only
df2['Regex_Pattern'] = df2['Keyword'].apply(lambda x: fr'\b{re.escape(x.lower())}\b')

Then use the regex to match reviews:

def match_with_regex(review):
    review_lower = review.lower()
    matches = []
    for _, row in df2.iterrows():
        if pd.Series(review_lower).str.contains(row['Regex_Pattern']).iloc[0]:
            matches.append({
                'Matched_Keyword': row['Keyword'],
                'Department': row['Department'],
                'Issue_Type': row['Issue Type']
            })
    return matches if matches else [{'Matched_Keyword': None, 'Department': None, 'Issue_Type': None}]

# Apply and expand results like before
df1['Match_Details'] = df1['Review'].apply(match_with_regex)
df_matched = df1.explode('Match_Details').reset_index(drop=True)
df_matched = pd.concat([df_matched.drop('Match_Details', axis=1), df_matched['Match_Details'].apply(pd.Series)], axis=1)

Method 3: Handling Multiple Matches (Priority-Based) #

If a single review matches multiple keywords (e.g., "Sheets were dirty and the AC broke" matches both "dirty" and "broke"), you might want to prioritize more specific keywords. Just add a Priority column to df2 (lower numbers = higher priority), then sort and pick the top match:

# Sort df2 by priority first (assuming you have a 'Priority' column)
df2_sorted = df2.sort_values('Priority', ascending=True)

def match_with_priority(review):
    review_lower = review.lower()
    for _, row in df2_sorted.iterrows():
        if pd.Series(review_lower).str.contains(row['Regex_Pattern']).iloc[0]:
            return {
                'Matched_Keyword': row['Keyword'],
                'Department': row['Department'],
                'Issue_Type': row['Issue Type']
            }
    # Return empty if no matches
    return {'Matched_Keyword': None, 'Department': None, 'Issue_Type': None}

# Apply directly to get one match per review
df1 = pd.concat([df1, df1['Review'].apply(match_with_priority).apply(pd.Series)], axis=1)

Common Troubleshooting Tips #

If your previous attempts failed, check these common issues:

• Case Sensitivity: Reviews might use capitalized words while your keywords are lowercase—always add case=False or normalize text to lowercase.
• Special Characters: Keywords with regex special characters (like ., $, or *) need to be escaped with re.escape() (which we included in Method 2).
• Multi-Word Keywords: Make sure your matching logic preserves spaces (both str.contains and regex will handle this as long as you don't modify the keyword).

内容的提问来源于stack exchange，提问作者REdim.Learning