如何用Pandas Transform优化分组求列唯一值平均值并赋值至原表

需要在分组条件下计算某列唯一值的平均值，并将结果赋值到原数据表中。当前方法需先创建存储唯一值的新DataFrame，再将结果合并到主表，想问能否用Pandas的transform方法实现更简洁的处理？

具体需求：

• 计算所有用户下counts列唯一值的整体平均值
• 按Category列分组，计算对应分组下counts列唯一值的平均值

原可复现代码：

import pandas as pd

df1 = pd.DataFrame({'user_id':['A','A','A', 'B','B','B', 'D','D','D', 'E','E'],
                'Category':['ABC','ABC','ABC','ABC','ABC','ABC','XYZ','XYZ','XYZ','XYZ','XYZ'],
                'counts':[3,3,3,2,2,1,2,1,2,2,2]})

df1_unique = df1.drop_duplicates(["user_id","Category",'counts'], keep='first')

df1["Overall_average__Unique_Counts"] = df1_unique["Overall_average__Unique_Counts"]= df1_unique['counts'].mean()

df1_unique["Categorywise_average_Unique_counts"] = df1_unique.groupby(["Category"])['counts'].transform('mean')

df2 = pd.merge(df1,df1_unique[["user_id","Category","counts","Categorywise_average_Unique_counts"]],on=["user_id","Category","counts"],how='left')

原代码运行结果：

user_id Category  counts  Overall_average__Unique_Counts  
0        A      ABC       3                            1.83   
1        A      ABC       3                            1.83   
2        A      ABC       3                            1.83   
3        B      ABC       2                            1.83   
4        B      ABC       2                            1.83   
5        B      ABC       1                            1.83   
6        D      XYZ       2                            1.83   
7        D      XYZ       1                            1.83   
8        D      XYZ       2                            1.83   
9        E      XYZ       2                            1.83   
10       E      XYZ       2                            1.83   

    Categorywise_average_Unique_counts  
0                                 2.00  
1                                 2.00  
2                                 2.00  
3                                 2.00  
4                                 2.00  
5                                 2.00  
6                                 1.67  
7                                 1.67  
8                                 1.67  
9                                 1.67  
10                                1.67  

完全可以用transform结合Pandas的分组、去重逻辑简化操作，无需额外创建冗余中间表，以下是两种优化方案：

方案1：链式操作+merge #

通过链式调用实现去重、分组计算，再合并回原表，代码更紧凑：

import pandas as pd

df1 = pd.DataFrame({'user_id':['A','A','A', 'B','B','B', 'D','D','D', 'E','E'],
                'Category':['ABC','ABC','ABC','ABC','ABC','ABC','XYZ','XYZ','XYZ','XYZ','XYZ'],
                'counts':[3,3,3,2,2,1,2,1,2,2,2]})

# 计算整体唯一值平均值并赋值
overall_mean = df1.drop_duplicates(["user_id", "Category", "counts"])['counts'].mean().round(2)
df1['Overall_average__Unique_Counts'] = overall_mean

# 计算分组唯一值平均值并关联回原表
df1 = df1.merge(
    df1.drop_duplicates(["user_id", "Category", "counts"])
       .assign(Categorywise_average_Unique_counts=lambda x: x.groupby('Category')['counts'].transform('mean').round(2))
       [["user_id", "Category", "counts", "Categorywise_average_Unique_counts"]],
    on=["user_id", "Category", "counts"],
    how='left'
)

print(df1)

方案2：字典映射（更高效） #

先通过分组计算得到分组唯一值平均值的字典，再直接映射到原表，避免merge操作：

import pandas as pd

df1 = pd.DataFrame({'user_id':['A','A','A', 'B','B','B', 'D','D','D', 'E','E'],
                'Category':['ABC','ABC','ABC','ABC','ABC','ABC','XYZ','XYZ','XYZ','XYZ','XYZ'],
                'counts':[3,3,3,2,2,1,2,1,2,2,2]})

# 整体唯一值平均值
overall_mean = df1.drop_duplicates(["user_id", "Category", "counts"])['counts'].mean().round(2)
df1['Overall_average__Unique_Counts'] = overall_mean

# 分组唯一值平均值：先去重再分组计算，转字典后映射
cat_unique_mean = df1.drop_duplicates(["user_id", "Category", "counts"]).groupby('Category')['counts'].mean().round(2).to_dict()
df1['Categorywise_average_Unique_counts'] = df1['Category'].map(cat_unique_mean)

print(df1)

两种方案的运行结果均与原代码一致，但代码更简洁高效，减少了中间变量的冗余。

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