如何用pandas groupby聚合DataFrame行，合并duration与description列

原始数据 #

import pandas as pd
from datetime import date, timedelta

df = pd.DataFrame(
    (
        (date(2023, 2, 27), timedelta(hours=0.5), "project A", "planning"),
        (date(2023, 2, 27), timedelta(hours=1), "project A", "planning"),
        (date(2023, 2, 27), timedelta(hours=1.5), "project A", "execution"),
        (date(2023, 2, 27), timedelta(hours=0.25), "project B", "planning"),
        (date(2023, 2, 28), timedelta(hours=3), "project A", "wrapup"),
        (date(2023, 2, 28), timedelta(hours=3), "project B", "execution"),
        (date(2023, 2, 28), timedelta(hours=2), "project B", "miscellaneous"),
    ),
    columns=("date", "duration", "project", "description"),
)
print(df)

输出：

date        duration    project    description
0  2023-02-27 0 days 00:30:00  project A       planning
1  2023-02-27 0 days 01:00:00  project A       planning
2  2023-02-27 0 days 01:30:00  project A      execution
3  2023-02-27 0 days 00:15:00  project B       planning
4  2023-02-28 0 days 03:00:00  project A         wrapup
5  2023-02-28 0 days 03:00:00  project B      execution
6  2023-02-28 0 days 02:00:00  project B  miscellaneous

预期聚合结果 #

按date和project分组后，需得到如下格式的结果：

result = pd.DataFrame(
    (
        (
            date(2023, 2, 27),
            "project A",
            timedelta(hours=3),
            "planning (1.5), execution (1.5)",
        ),
        (date(2023, 2, 27), "project B", timedelta(hours=0.25), "planning"),
        (date(2023, 2, 28), "project A", timedelta(hours=3), "wrapup"),
        (
            date(2023, 2, 28),
            "project B",
            timedelta(hours=5),
            "execution (3), miscellaneous (2)",
        ),
    ),
    columns=("date", "project", "duration", "description"),
)
print(result)

输出：

date    project        duration                       description
0  2023-02-27  project A 0 days 03:00:00   planning (1.5), execution (1.5)
1  2023-02-27  project B 0 days 00:15:00                          planning
2  2023-02-28  project A 0 days 03:00:00                            wrapup
3  2023-02-28  project B 0 days 05:00:00  execution (3), miscellaneous (2)

核心问题 #

• duration字段的聚合可直接通过groupby.sum()实现：

  df.groupby(by=["date", "project"])["duration"].sum().to_frame().reset_index()
  

• 难点在description字段：需要先在每个date+project分组内，按描述类别汇总对应时长，再格式化为指定字符串并拼接。

解决方案 #

方法一：分步聚合拼接 #

1. 先将时长转换为小时数，方便后续格式化：

   df["hours"] = df["duration"].dt.total_seconds() / 3600
   

2. 三层分组计算每个描述对应的总时长：

   desc_agg = df.groupby(["date", "project", "description"])["hours"].sum().reset_index()
   

3. 格式化描述字符串并按主分组拼接：

   desc_agg["formatted_desc"] = desc_agg.apply(
       lambda x: f"{x['description']} ({x['hours']})" if x['hours'] != 0 else x['description'],
       axis=1
   )
   desc_final = desc_agg.groupby(["date", "project"])["formatted_desc"].apply(", ".join).reset_index(name="description")
   

4. 合并时长总和与格式化后的描述：

   duration_total = df.groupby(["date", "project"])["duration"].sum().reset_index()
   result = pd.merge(duration_total, desc_final, on=["date", "project"])
   

方法二：自定义函数一次性聚合 #

直接在date+project分组内完成描述的聚合逻辑：

def aggregate_description(group):
    # 转换为小时数并按描述分组求和
    hour_groups = (group["duration"].dt.total_seconds() / 3600).groupby(group["description"]).sum()
    # 格式化每个条目
    formatted = []
    for desc, hours in hour_groups.items():
        formatted.append(f"{desc} ({hours})" if hours != 0 else desc)
    return ", ".join(formatted)

# 同时聚合时长和描述
aggregated = df.groupby(["date", "project"]).agg(
    duration=("duration", "sum"),
    description=("description", aggregate_description)
).reset_index()

两种方法均可得到预期结果，方法一步骤清晰易调试，方法二更紧凑高效。

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