按组移除出现次数少于3次的行（Leader角色除外）

筛选规则明确 #

核心逻辑：按Person + Question组合统计Partner角色的出现次数，只要该组合的次数≥3，就保留这个组合下的所有行（包括Leader角色的行）；反之则整个组合的行全部移除。

实现方法 #

方法1：Excel操作

1. 新增辅助列（比如D列），输入公式：

   =COUNTIFS($A:$A,A2,$B:$B,B2,$C:$C,"Partner")
   

   下拉填充后，这列会显示当前行对应的Person+Question组合下Partner的总次数。
2. 对辅助列进行筛选，只保留值≥3的行，就是目标结果。

方法2：Python Pandas代码

如果用代码批量处理，直接按逻辑计算后筛选：

import pandas as pd

# 构造你的数据（实际使用时可替换为读取本地文件）
data = [
    ["Person A", "Question 1", "Leader"],
    ["Person A", "Question 2", "Leader"],
    ["Person A", "Question 3", "Leader"],
    ["Person A", "Question 1", "Partner"],
    ["Person A", "Question 1", "Partner"],
    ["Person A", "Question 1", "Partner"],
    ["Person A", "Question 2", "Partner"],
    ["Person A", "Question 2", "Partner"],
    ["Person A", "Question 2", "Partner"],
    ["Person A", "Question 3", "Partner"],
    ["Person A", "Question 3", "Partner"],
    ["Person A", "Question 3", "Partner"],
    ["Person B", "Question 1", "Leader"],
    ["Person B", "Question 2", "Leader"],
    ["Person B", "Question 3", "Leader"],
    ["Person B", "Question 1", "Partner"],
    ["Person B", "Question 2", "Partner"],
    ["Person B", "Question 2", "Partner"],
    ["Person B", "Question 3", "Partner"],
    ["Person B", "Question 3", "Partner"],
    ["Person B", "Question 3", "Partner"],
]
df = pd.DataFrame(data, columns=["Person", "Question", "Role"])

# 统计每个Person+Question组合的Partner次数
partner_counts = df[df["Role"] == "Partner"].groupby(["Person", "Question"]).size().reset_index(name="count")

# 合并次数到原表并筛选符合条件的行
result = df.merge(partner_counts, on=["Person", "Question"]).query("count >= 3").drop("count", axis=1)

print(result)

最终筛选结果 #

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