如何为重复id_user分配基于多数集群的new cluster

用SQL实现 #

先统计每个用户各集群的出现次数，给每个用户的集群按次数排序，取排名第一的作为众数集群，再关联回原表得到结果：

-- 统计集群次数并排序
WITH cluster_counts AS (
    SELECT 
        id_user,
        cluster,
        COUNT(*) AS cnt,
        -- 按次数降序排名，次数相同的排名一致
        RANK() OVER (PARTITION BY id_user ORDER BY COUNT(*) DESC) AS rnk
    FROM your_table
    GROUP BY id_user, cluster
)
-- 关联原表获取新集群
SELECT 
    t.id_user,
    t.cluster,
    cc.cluster AS new_cluster
FROM your_table t
JOIN cluster_counts cc 
    ON t.id_user = cc.id_user 
    AND cc.rnk = 1;

如果遇到多个集群出现次数相同的情况（比如某用户有2个a和2个b），RANK()会返回多个排名1的结果，此时可以改用ROW_NUMBER()取第一个出现的集群，或者根据业务需求保留多个结果。

用Python Pandas实现 #

通过分组计算每个用户集群的众数，再映射回原表：

import pandas as pd

# 示例数据
df = pd.DataFrame({
    'id_user': [1,1,2,2,2],
    'cluster': ['a','a','b','b','a']
})

# 定义众数获取函数，取第一个众数（处理多众数场景）
def get_user_mode(cluster_series):
    return cluster_series.mode().iloc[0]

# 计算每个用户的众数集群
user_mode_map = df.groupby('id_user')['cluster'].apply(get_user_mode).reset_index(name='new_cluster')

# 合并回原表
result_df = df.merge(user_mode_map, on='id_user', how='left')
print(result_df)

运行后会直接输出符合要求的结果，若需处理多众数场景，可修改get_user_mode函数返回所有众数或其他规则的取值。

用Excel实现 #

通过辅助列逐步计算：

1. 计算集群出现次数：在D2单元格输入公式=COUNTIFS($A:$A,A2,$B:$B,B2)，下拉填充到所有行，得到每个(id_user, cluster)组合的出现次数。
2. 计算用户最大次数：在E2单元格输入公式=MAXIFS($D:$D,$A:$A,A2)，下拉填充，得到每个用户的集群最高出现次数。
3. 获取新集群：在F2单元格输入公式=INDEX($B:$B,MATCH(A2&"|"&E2,$A:$A&"|"&$D:$D,0))，下拉填充。如果是Excel 365，可简化为=TAKE(SORT(FILTER($B:$B,$A:$A=A2),COUNTIFS($A:$A,A2,$B:$B,$B:$B),-1),1)。

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