满足指定条件时移除Pandas DataFrame中含'fish'的行

问题 #

现有如下Pandas DataFrame：

import pandas as pd

data = {
    "doc_id": [33075059515, 33075059515, 53075235984, 28865465787, 28865465787, 28865465787, 87652548931, 87652548931],
    "source": ["AAP_news", "AAP_news", "Goa Herald", "Bulletin", "Bulletin", "Bulletin", "Bulletin", "Bulletin"],
    "sentence_number": [2, 2, 5, 4, 4, 3, 1, 1],
    "Species": ["fish", "snapper", "fish", "turtle", "shark", "fish", "fish", "turtle"],
    "Valve": ["30x", "30x", "50y", "50y", "50y", pd.NA, pd.NA, pd.NA],
    "Fretindex": ["SAR", "SAR", "SAR", "SAR", pd.NA, "SAR", pd.NA, pd.NA]
}
df = pd.DataFrame(data, index=[1,2,3,4,5,6,7,8])

需要实现的逻辑：如果某行Species是fish，且在同一doc_id+sentence_number+Valve+Fretindex分组里存在其他非fish的物种，就删除这行fish。

之前尝试了以下代码但未得到正确结果：

result = df[((result1.duplicated(subset = ["doc_id", "sentence_number", "Valve", "Fretindex"], keep=False)) & 
         (df['SPECIES'] == df['fish'] )
        ~df.duplicated(subset = ["doc_id", "sentence_number", "Valve", "Fretindex"], keep=False)]

期望得到的输出如下：

doc_id      source  sentence_number  Species Valve Fretindex
2  33075059515    AAP_news                2  snapper  30x       SAR
3  53075235984  Goa Herald                5     fish  50y       SAR
4  28865465787    Bulletin                4    turtle  50y       SAR
5  28865465787    Bulletin                4     shark  50y      <NA>
6  28865465787    Bulletin                3     fish  <NA>       SAR
8  87652548931    Bulletin                1    turtle  <NA>      <NA>

解决方案 #

直接通过分组标记+布尔索引就能实现需求，代码如下：

import pandas as pd

# 构建原始DataFrame（如果已有可跳过）
data = {
    "doc_id": [33075059515, 33075059515, 53075235984, 28865465787, 28865465787, 28865465787, 87652548931, 87652548931],
    "source": ["AAP_news", "AAP_news", "Goa Herald", "Bulletin", "Bulletin", "Bulletin", "Bulletin", "Bulletin"],
    "sentence_number": [2, 2, 5, 4, 4, 3, 1, 1],
    "Species": ["fish", "snapper", "fish", "turtle", "shark", "fish", "fish", "turtle"],
    "Valve": ["30x", "30x", "50y", "50y", "50y", pd.NA, pd.NA, pd.NA],
    "Fretindex": ["SAR", "SAR", "SAR", "SAR", pd.NA, "SAR", pd.NA, pd.NA]
}
df = pd.DataFrame(data, index=[1,2,3,4,5,6,7,8])

# 核心逻辑：按指定列分组，标记每个分组是否存在非fish的物种
has_non_fish = df.groupby(["doc_id", "sentence_number", "Valve", "Fretindex"])["Species"].transform(
    lambda x: (x != "fish").any()
)

# 筛选：要么不是fish，要么是fish但所在分组没有其他物种
filtered_df = df[~((df["Species"] == "fish") & has_non_fish)]

# 查看结果
print(filtered_df)

代码说明 #

1. 分组标记：用groupby+transform给每一行标记，它所在的分组里有没有非fish的物种。transform会把计算结果映射回每一行，保证长度和原DataFrame一致。
2. 布尔筛选：我们要排除的是「既是fish，且所在分组有其他物种」的行，所以用~取反，保留剩下的行。

之前代码的问题 #

你写的代码有几个明显错误：

• 未定义变量result1，应该用df
• 列名大小写错误：SPECIES应该是Species，df['fish']是错误写法，应该直接写字符串"fish"
• 逻辑运算符缺失：~df.duplicated(...)前面缺少|，导致逻辑判断不完整

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