如何删除数据集中ID重复且Category为Pre的冗余记录

需要实现的过滤逻辑如下：

1. 统计每个Id的出现次数
2. 若Id仅出现1次：无论Category是Pre还是Post，全部保留
3. 若Id出现≥2次：仅保留Category为Post的记录，删除同Id下Category为Pre的记录

1. SQL实现 #

假设存储数据的表名为weight_records，查询语句如下：

WITH id_count AS (
    SELECT Id, COUNT(*) as cnt
    FROM weight_records
    GROUP BY Id
)
SELECT t1.*
FROM weight_records t1
LEFT JOIN id_count t2 ON t1.Id = t2.Id
WHERE 
    t2.cnt = 1 
    OR 
    (t2.cnt > 1 AND t1.Category = 'Post')
ORDER BY t1.Id;

2. Python Pandas实现 #

import pandas as pd

# 构造原始数据集，实际使用时可替换为读取本地文件的代码
df = pd.DataFrame({
    'Id': [1,1,2,3,4,4,5],
    'Weight': [10.2,12.1,11.3,12.9,10.3,12.3,11.8],
    'Category': ['Pre','Post','Post','Pre','Post','Pre','Pre']
})

# 计算每个Id的出现次数
df['id_cnt'] = df.groupby('Id')['Id'].transform('count')

# 按规则过滤数据
result = df[((df['id_cnt'] == 1) | (df['Category'] == 'Post'))].drop('id_cnt', axis=1).reset_index(drop=True)

print(result)

3. Excel实现 #

• 步骤1：新增辅助列D，列名设为Id出现次数，D2单元格输入公式=COUNTIF(A:A,A2)，下拉填充所有行
• 步骤2：新增辅助列E，列名设为是否保留，E2单元格输入公式=IF(AND(D2>1,C2="Pre"),"否","是")，下拉填充所有行
• 步骤3：筛选E列值为是的行，复制导出即可得到最终结果

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