如何合并DataFrame并添加聚合后的Question No.列

原始数据 #

给出两个初始DataFrame：

import pandas as pd

a = pd.DataFrame({"Question Skill": ["Algebra", "Patterns"],
              "Average": [56,76],
              "SD": [45,30]})

b = pd.DataFrame({"Question No.": [1, 2, 3, 4, 5],
                  "Question Skill": ['Algebra', 'Patterns', 'Algebra', 'Patterns', 'Patterns']})

需求 #

将DataFrame b 中的Question No.按Question Skill分组为列表，再与DataFrame a 合并，得到如下目标结果：

c = pd.DataFrame({"Question Skill": ["Algebra","Patterns"],
                  "Question No.": [[1,3],[2,4,5]],
                  "Average": [56,76],
                  "SD": [45,30]})

解决方案 #

实现步骤 #

• 对b按Question Skill分组，使用agg(list)将每组的Question No.聚合为列表
• 通过pd.merge()将聚合后的结果与a按共同列Question Skill合并

完整代码 #

import pandas as pd

# 初始化原始DataFrame
a = pd.DataFrame({"Question Skill": ["Algebra", "Patterns"],
              "Average": [56,76],
              "SD": [45,30]})

b = pd.DataFrame({"Question No.": [1, 2, 3, 4, 5],
                  "Question Skill": ['Algebra', 'Patterns', 'Algebra', 'Patterns', 'Patterns']})

# 分组聚合题目编号为列表
b_grouped = b.groupby("Question Skill")["Question No."].agg(list).reset_index()

# 合并得到目标DataFrame
c = pd.merge(a, b_grouped, on="Question Skill")

# 输出结果
print(c)

运行结果 #

Question Skill  Average  SD Question No.
0        Algebra       56  45       [1, 3]
1       Patterns       76  30    [2, 4, 5]

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