R语言按Group分组条件删除行：多重复组仅删除Status为No的行

核心处理逻辑分为两步：首先统计每个Group分组的行数，再过滤掉*「Group行数大于1且Status等于No」*的行，其余行全部保留即可得到目标结果。 #

1. Python Pandas 实现方案 #

import pandas as pd

# 构造测试数据集
df = pd.DataFrame({
    "Group": ["Blue", "Red", "Red", "Yellow", "Yellow", "Purple"],
    "Status": ["No", "No", "Yes", "No", "Yes", "No"],
    "From": [1994, 1994, 1998, 1994, 2015, 1994],
    "To": [2000, 1997, 2002, 2014, 2021, 1997]
})

# 核心过滤逻辑
df = df[~((df.groupby('Group')['Group'].transform('count') > 1) & (df['Status'] == 'No'))]
print(df)

2. R dplyr 实现方案 #

library(dplyr)

# 构造测试数据集
df <- tibble(
  Group = c("Blue", "Red", "Red", "Yellow", "Yellow", "Purple"),
  Status = c("No", "No", "Yes", "No", "Yes", "No"),
  From = c(1994, 1994, 1998, 1994, 2015, 1994),
  To = c(2000, 1997, 2002, 2014, 2021, 1997)
)

# 核心过滤逻辑
df <- df %>% 
  group_by(Group) %>% 
  filter(!(n() > 1 & Status == "No")) %>% 
  ungroup()
print(df)

3. SQL 实现方案 #

假设存储数据的表名为group_data：

SELECT t1.*
FROM group_data t1
JOIN (
    SELECT `Group`, COUNT(*) as group_cnt
    FROM group_data
    GROUP BY `Group`
) t2 ON t1.`Group` = t2.`Group`
WHERE NOT (t2.group_cnt > 1 AND t1.Status = 'No');

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