使用pandas结合value_counts与条件筛选分组指定行并保留全列

我有如下DataFrame：

a        c
1        EMP
1        y
1        y
1        z
2        z
2        z
2        EMP
2        z
2        a
2        a
3        EMP
3        EMP
3        k
4        EMP
4        EMP
4        EMP

需求：先按a列进行groupby，每个分组内对c列做value_counts统计：

• 若计数最多的c值不是EMP，则保留该值；
• 若计数最多的是EMP，则保留计数第二多的值；
• 若分组内只有EMP，则保留EMP（如a=4的情况）。

预期结果：

a        c
1        y
2        z
3        k
4        EMP

我尝试通过排序取首行实现，但无法在drop_duplicates中用lambda函数实现EMP的条件逻辑（该函数仅支持keep=first/last参数），尝试代码如下：

df = df.iloc[df.groupby(['a', 'c']).c.transform('size').mul(-1).argsort(kind='mergesort')]

补充问题：我的DataFrame还有约50列，希望最终结果保留所有列，对应所选行的数值；若选中的是EMP行，则取该值首次出现的行数据。请问有没有比创建函数字典传入agg更简便的方法？使用SQL实现也可。

Python 实现 #

可以通过分组内排序+筛选的方式实现，无需编写复杂的agg函数字典：

1. 先给每个分组内的c值计算出现次数，同时标记优先级（非EMP优先级更高）：

# 计算每个(a,c)组合的出现次数
df['count'] = df.groupby(['a', 'c'])['c'].transform('size')
# 给EMP设置优先级权重：非EMP排前，EMP排后
df['priority'] = df['c'].apply(lambda x: 0 if x != 'EMP' else 1)

2. 按规则排序：先按a分组，再按priority升序（非EMP在前）、count降序（次数多的在前），最后按原索引升序（保证EMP取首次出现的行）：

sorted_df = df.sort_values(by=['a', 'priority', 'count', df.index], ascending=[True, True, False, True])

3. 按a去重，保留每组第一行，最后清理临时列：

result = sorted_df.drop_duplicates(subset=['a'], keep='first').drop(columns=['count', 'priority'])

该方法既满足c值的选择逻辑，又完整保留所有原始列，代码简洁易维护。

SQL 实现 #

用窗口函数可以快速实现需求，假设表名为your_table：

WITH ranked_data AS (
    SELECT 
        *,
        -- 计算每个(a,c)组合的出现次数
        COUNT(*) OVER (PARTITION BY a, c) AS cnt,
        -- 按规则给每行排名：非EMP优先、次数多优先、首次出现优先
        ROW_NUMBER() OVER (
            PARTITION BY a 
            ORDER BY 
                CASE WHEN c != 'EMP' THEN 0 ELSE 1 END,
                cnt DESC,
                -- 这里替换为表中记录顺序的字段（如主键、创建时间），保证EMP取首次出现的行
                id ASC 
        ) AS rn
    FROM your_table
)
SELECT * 
FROM ranked_data 
WHERE rn = 1
ORDER BY a;

解释：

• 用COUNT(*)窗口函数统计每个分组内c值的出现次数；
• ROW_NUMBER()按规则生成排名，确保符合需求的行排第一；
• 筛选出每个a分组排名第一的行，即为最终结果。

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