按学生分组统计不同进度等级活动次数的Pandas实现问题

现有数据 #

STUDENT_ID STUDENT_ACTIVITY_SESSION_ID NODE_NAME   ACTIVITY_NAME   prog_level
FredID  gobbledeegook1  Node1   MyActivity1 pass
FredID  gobbledeegook2  Node1   MyActivity1 pass
FredID  gobbledeegook3  Node2   MyActivity2 pass
JaniceID    gobbledeegook4  Node3   MyActivity3 stay
JaniceID    gobbledeegook5  Node3   MyActivity3 stay
JaniceID    gobbledeegook5  Node3   MyActivity3 fail

期望结果 #

STUDENT_ID attempts_pass   attempts_fail   attempts_stay
FredID  3       0       0
JaniceID    0       1       2

尝试过的方案及问题 #

方案1：循环分组生成列名 #

代码如下：

def std_attempts_by_prog_level(df):
    dict_fields = {}
    df_by_prog_level = df.groupby('prog_level')['STUDENT_ACTIVITY_SESSION_ID']
    for name, group in df_by_prog_level:
        x = group.count() 
        dict_fields["attempts_" + name] = x

    return pd.Series(dict_fields)   
  
df.groupby('STUDENT_ID').apply(std_attempts_by_prog_level).reset_index()

问题：运行结果需要二次透视处理，不符合预期格式。

方案2：透视表手动命名字段 #

代码如下：

df_temp=df.groupby(['STUDENT_ID', 'prog_level'],as_index=False)['STUDENT_ACTIVITY_SESSION_ID'].count().pivot(index='STUDENT_ID', columns='prog_level').rename({'cancel':'attempts_cancel', 'fail':'attempts_fail', 'in_progress':'attempts_in_progress', 'pass':'attempts_pass'}, axis=1)

print(df_temp.columns)

问题：生成了MultiIndex列，难以和其他学生统计指标合并：

MultiIndex([('STUDENT_ACTIVITY_SESSION_ID',      'attempts_cancel'),
            ('STUDENT_ACTIVITY_SESSION_ID',        'attempts_fail'),
            ('STUDENT_ACTIVITY_SESSION_ID', 'attempts_in_progress'),
            ('STUDENT_ACTIVITY_SESSION_ID',        'attempts_pass')],
           names=[None, 'prog_level'])

解决方案 #

方法1：用pivot_table一步到位 #

直接使用pivot_table聚合并设置列名，无需处理MultiIndex：

import pandas as pd

# 读取数据（示例）
data = [
    ["FredID", "gobbledeegook1", "Node1", "MyActivity1", "pass"],
    ["FredID", "gobbledeegook2", "Node1", "MyActivity1", "pass"],
    ["FredID", "gobbledeegook3", "Node2", "MyActivity2", "pass"],
    ["JaniceID", "gobbledeegook4", "Node3", "MyActivity3", "stay"],
    ["JaniceID", "gobbledeegook5", "Node3", "MyActivity3", "stay"],
    ["JaniceID", "gobbledeegook5", "Node3", "MyActivity3", "fail"]
]
df = pd.DataFrame(data, columns=["STUDENT_ID", "STUDENT_ACTIVITY_SESSION_ID", "NODE_NAME", "ACTIVITY_NAME", "prog_level"])

# 生成统计结果
result = pd.pivot_table(
    df,
    index="STUDENT_ID",
    columns="prog_level",
    values="STUDENT_ACTIVITY_SESSION_ID",
    aggfunc="count",
    fill_value=0  # 空值填充为0
)

# 重命名列，添加attempts_前缀
result.columns = [f"attempts_{col}" for col in result.columns]

# 重置索引，让STUDENT_ID成为普通列
result = result.reset_index()

print(result)

运行结果：

STUDENT_ID  attempts_fail  attempts_pass  attempts_stay
0     FredID              0              3              0
1   JaniceID              1              0              2

方法2：修正方案2的MultiIndex问题 #

如果坚持用原分组+透视的方式，可以合并MultiIndex层级，把列名改成单层：

# 先分组统计
df_grouped = df.groupby(["STUDENT_ID", "prog_level"])["STUDENT_ACTIVITY_SESSION_ID"].count().unstack(fill_value=0)

# 重命名列
df_grouped.columns = [f"attempts_{col}" for col in df_grouped.columns]

# 重置索引
df_grouped = df_grouped.reset_index()

print(df_grouped)

此方法同样能得到和方法1一致的结果，且列名是单层结构，方便和其他数据合并。

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