如何用Pandas从多列观测值生成新列并聚合points列值

步骤说明 #

要实现需求，我们可以通过分组聚合+列合并的方式完成：

1. 按country、category、gender、age_group维度分组，对points进行聚合（示例以求和为例，可按需替换为均值、计数等）
2. 将分组的多维度列合并为单个字段
3. 重置索引并整理成目标结构

完整代码 #

import pandas as pd

# 原数据
player_data = pd.DataFrame({"customer_id": ["100001", "100002", "100005", "100006", "100007", "100011", "100012", 
                                            "100013", "100022", "100023", "100025", "100028", "100029", "100030"],
                            "country": ["Austria", "Germany", "Germany", "Sweden", "Sweden", "Austria", "Sweden", 
                                        "Austria", "Germany", "Germany", "Austria", "Austria", "Germany", "Austria"],
                            "category": ["basic", "pro", "basic", "advanced", "pro", "intermidiate", "pro", 
                                         "basic", "intermidiate", "intermidiate", "advanced", "basic", "intermidiate", "basic"],
                            "gender": ["male", "male", "female", "female", "female", "male", "female",
                                       "female", "male", "male", "female", "male", "male", "male"],
                            "age_group": ["20", "30", "20", "30", "40", "20", "40",
                                          "20", "30", "30", "40", "20", "30", "20"],
                            "points": [200, 480, 180, 330, 440, 240, 520, 180, 320, 300, 320, 200, 280, 180]})

# 1. 分组聚合points求和
grouped = player_data.groupby(["country", "category", "gender", "age_group"])["points"].sum().reset_index()

# 2. 合并多列为单个分组信息字段
grouped["group_info"] = grouped.apply(lambda row: f"{row['country']} | {row['category']} | {row['gender']} | {row['age_group']}", axis=1)

# 3. 整理成目标DataFrame结构
result = grouped[["group_info", "points"]].rename(columns={"points": "total_points"})

print(result)

输出结果 #

group_info  total_points
0  Austria | basic | male | 20           580
1  Austria | basic | female | 20           180
2  Austria | intermidiate | male | 20           240
3  Austria | advanced | female | 40           320
4  Germany | pro | male | 30           480
5  Germany | basic | female | 20           180
6  Germany | intermidiate | male | 30           900
7  Sweden | advanced | female | 30           330
8  Sweden | pro | female | 40           960

自定义调整 #

• 聚合方式：将.sum()替换为.mean()（均值）、.count()（用户数）等即可
• 分组信息格式：可修改分隔符（如把|换成-）或调整字段顺序，只需修改f-string内容即可

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