如何在Pandas的reviews列中检索output列的指定子串

需求说明 #

现有包含reviews、summary及output1至output40列的Pandas DataFrame，需实现以下逻辑：

• 对每行的所有非空output列，提取其中的核心子串（例如从Quality: love the wand (positive)中提取love the wand）
• 检查每个提取出的核心子串是否存在于对应行的reviews列字符串中
• 将检查结果（所有核心子串都存在则为True，否则False）存入Check列

示例DataFrame #

Check  index                                            reviews                                            summary                                            output1                                           output2  ... output35 output36 output37 output38 output39 output40
0  True      1  After realizing my old mascara had a petroleum...  Output: Quality: love the wand (positive); Len...                  Quality: love the wand (positive)  Lengthening: lengthens my lashes well (positive)  ...     None     None     None     None     None     None
1  True      2  Best mascara I’ve ever used. Makes my non-exis...  Output: Makes lashes visible (positive); Non-e...                    Makes lashes visible (positive)              Non-existent Asian lashes (negative)  ...     None     None     None     None     None     None
2  True      3  I've never had a mascara that made my lashes l...               Output: Look: long lashes (positive)                       Look: long lashes (positive)                                              None  ...     None     None     None     None     None     None
3  True      4  It is clump and smudge-free with an awesome la...  Output: Clump-free (positive); Smudge-proof (p...                              Clump-free (positive)                           Smudge-proof (positive)  ...     None     None     None     None     None     None
4  True      5  And I’m going to buy it again, and again, and ...  Output: Quality: impressed by a mascara before...  Quality: impressed by a mascara before (positive)            Extends Lashes: huge impact (positive)  ...     None     None     None     None     None     None

核心子串提取规则 #

核心子串的提取逻辑：

1. 移除字符串开头的XXX: 前缀（例如Quality: ）
2. 移除字符串结尾的 (positive)或 (negative)后缀
3. 处理后得到的中间部分即为核心子串

现有代码问题 #

原代码存在以下缺陷：

• 直接使用summary分割后的内容进行检查，未提取核心子串，导致匹配逻辑错误
• 语法错误：df.apply的lambda表达式后多了一个括号
• 未遍历实际的output1至output40列，而是依赖summary列，与需求不符

正确实现代码 #

import pandas as pd

# 读取DataFrame
df = pd.read_excel("ilia.xlsx")
# 初始化Check列为True
df["Check"] = True

# 定义核心子串提取函数
def extract_core_substring(s):
    if pd.isna(s):
        return None
    # 移除开头的XXX: 前缀
    if ": " in s:
        s = s.split(": ", 1)[1]
    # 移除结尾的(positive)/(negative)后缀
    if " (" in s:
        s = s.rsplit(" (", 1)[0]
    # 去除首尾空格
    return s.strip()

# 定义每行的检查函数
def check_row(row):
    reviews = row["reviews"]
    # 获取所有output列的列名
    output_cols = [col for col in df.columns if col.startswith("output")]
    # 遍历所有output列
    for col in output_cols:
        output_val = row[col]
        if pd.isna(output_val):
            continue
        # 提取核心子串
        core_str = extract_core_substring(output_val)
        if not core_str:
            continue
        # 检查核心子串是否在reviews中
        if core_str not in reviews:
            return False
    # 所有非空output列的核心子串都存在则返回True
    return True

# 应用检查函数到每一行
df["Check"] = df.apply(check_row, axis=1)

# 输出结果并保存
print(df.head())
df.to_excel("foo.xlsx", index=False)

代码说明 #

1. 提取函数：extract_core_substring处理单个output值，按规则提取核心子串，空值直接返回None
2. 行检查函数：check_row遍历当前行的所有output列，逐一提取核心子串并检查是否在reviews中，只要有一个不存在就返回False
3. 批量应用：通过df.apply将检查逻辑应用到每一行，更新Check列的值

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