基于另一列聚合值从数据集中获取最大时长对应的活动

现有按ID分组的数据集：

ID, Activity, Duration
1, Reading,     20
1, Work,        40
1, Reading,     30
2, Home,        50
2, Writing,     30
2, Reading,     20
2, Writing,     30

需要新增一列Max_Activity，标识每个ID下总时长最高的活动：ID1的Reading总时长50分钟（20+30）为最高；ID2的Writing总时长60分钟（30+30）为最高。期望输出：

ID, Activity, Duration, Max_Activity
1, Reading, 20, Reading
1, Work,    40, Reading
1, Reading, 30, Reading
2, Home,    50, Writing
2, Writing, 30, Writing
2, Reading, 20, Writing
2, Writing, 30, Writing

解法1：SQL实现 #

先通过子查询计算每个ID下各活动的总时长，再定位每个ID对应最大总时长的活动，最后关联回原表：

WITH activity_totals AS (
    SELECT 
        ID,
        Activity,
        SUM(Duration) AS total_duration
    FROM your_table
    GROUP BY ID, Activity
),
max_activity_per_id AS (
    SELECT 
        ID,
        Activity AS Max_Activity
    FROM activity_totals
    WHERE (ID, total_duration) IN (
        SELECT ID, MAX(total_duration)
        FROM activity_totals
        GROUP BY ID
    )
)
SELECT 
    t.ID,
    t.Activity,
    t.Duration,
    m.Max_Activity
FROM your_table t
JOIN max_activity_per_id m ON t.ID = m.ID;

解法2：Python Pandas实现 #

利用分组聚合和合并操作完成需求：

import pandas as pd

# 读取原始数据（替换为你的数据路径或直接传入DataFrame）
df = pd.read_csv("your_data.csv")

# 计算每个ID+Activity的总时长
activity_totals = df.groupby(["ID", "Activity"])["Duration"].sum().reset_index()

# 筛选每个ID下总时长最高的活动
max_activity = activity_totals.loc[activity_totals.groupby("ID")["Duration"].idxmax(), ["ID", "Activity"]]
max_activity.rename(columns={"Activity": "Max_Activity"}, inplace=True)

# 合并回原表得到结果
result = pd.merge(df, max_activity, on="ID")
print(result)

解法3：Excel实现 #

1. 计算每个ID+Activity的总时长：在空白列（如D列）D2单元格输入公式，下拉填充：

   =SUMIFS($C$2:$C$8, $A$2:$A$8, A2, $B$2:$B$8, B2)
   

2. 获取每个ID的Max_Activity：在E列（目标列）E2单元格输入数组公式，按Ctrl+Shift+Enter确认后下拉填充：

   =INDEX($B$2:$B$8, MATCH(MAX(SUMIFS($C$2:$C$8, $A$2:$A$8, A2, $B$2:$B$8, $B$2:$B$8)), SUMIFS($C$2:$C$8, $A$2:$A$8, A2, $B$2:$B$8, $B$2:$B$8), 0))
   

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