使用contains函数关联表时的字符串匹配问题求助

问题1：拆分col2逐个匹配col1 #

原contains(LOWER("col1"), LOWER("col2"))是整串匹配，无法识别col2中单个单词与col1元素的匹配。解决思路是拆分col2为单词，逐个与col1的元素匹配：

1. 先解析col1的JSON格式字符串为列表，转成小写集合（提升匹配效率）
2. 将col2拆分为独立单词，转小写后逐个检查是否存在于col1的集合中

用Python pandas实现的代码示例：

import pandas as pd
import json

def check_match(row):
    # 解析col1为小写集合
    col1_items = json.loads(row['col1'])
    col1_set = {item.lower() for item in col1_items}
    # 拆分col2为单词并转小写
    col2_words = row['col2'].lower().split()
    # 判断是否有匹配项
    return any(word in col1_set for word in col2_words)

# 测试数据
df = pd.DataFrame({
    'col1': ['["Red","Blue","Green","yes","purple","car","yellow"]'],
    'col2': ['This Is Not Yellow']
})

df['match_result'] = df.apply(check_match, axis=1)
print(df['match_result'])  # 输出 True

问题2：处理复数形式的匹配 #

针对col2中单词的复数/变形问题，提供两种实用方案：

方案1：自定义替换字典 #

适合规则明确的场景，手动维护复数到单数的映射关系：

plural_map = {
    'houses': 'house',
    'cars': 'car',
    'boxes': 'box',
    'potatoes': 'potato'
}

def normalize_word(word):
    return plural_map.get(word.lower(), word.lower())

def check_match_with_plural(row):
    col1_set = {item.lower() for item in json.loads(row['col1'])}
    col2_words = row['col2'].split()
    normalized_words = [normalize_word(word) for word in col2_words]
    return any(word in col1_set for word in normalized_words)

# 测试数据
df2 = pd.DataFrame({
    'col1': ['["House","brick","purple","blue"]'],
    'col2': ['Very big houses']
})

df2['match_result'] = df2.apply(check_match_with_plural, axis=1)
print(df2['match_result'])  # 输出 True

方案2：NLP词形还原 #

用NLTK的词形还原工具自动处理复数、时态等变形，覆盖更多场景：

import nltk
from nltk.stem import WordNetLemmatizer
# 首次运行需下载依赖资源
nltk.download('wordnet')
nltk.download('averaged_perceptron_tagger')

lemmatizer = WordNetLemmatizer()

def get_pos_tag(tag):
    # 映射词性标签，提升词形还原准确性
    if tag.startswith('J'):
        return nltk.corpus.wordnet.ADJ
    elif tag.startswith('V'):
        return nltk.corpus.wordnet.VERB
    elif tag.startswith('N'):
        return nltk.corpus.wordnet.NOUN
    else:
        return nltk.corpus.wordnet.NOUN

def lemmatize_word(word):
    pos = nltk.pos_tag([word])[0][1]
    return lemmatizer.lemmatize(word.lower(), get_pos_tag(pos))

def check_match_with_lemm(row):
    col1_set = {item.lower() for item in json.loads(row['col1'])}
    col2_words = row['col2'].split()
    lemmatized_words = [lemmatize_word(word) for word in col2_words]
    return any(word in col1_set for word in lemmatized_words)

# 测试示例
df2['match_result_lemm'] = df2.apply(check_match_with_lemm, axis=1)
print(df2['match_result_lemm'])  # 输出 True

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