在Pandas中按id分组计算时间列首尾差值并添加偏移列

我有如下DataFrame：

import pandas as pd
df = pd.DataFrame([["A","9:00 AM"],["A","11:12 AM"],["A","1:03 PM"],["B","9:00 AM"],["B","12:56 PM"],["B","1:07 PM"],
                   ["B","1:18 PM"]],columns=["id","time"])

对应表格：

id    time
A   09:00 AM
A   11:12 AM
A   01:03 PM
B   09:00 AM
B   12:56 PM
B   01:07 PM
B   01:18 PM

需要创建新列total_hrs，按id分组计算time列最后一个值与第一个值的时间差，再加上30分钟偏移量，将结果填充到对应id的所有行中。

示例：id为A时，01:03 PM与09:00 AM的差值是4小时3分钟，加上30分钟后变为4小时33分钟，对应行的total_hrs列都填04:33:00。

预期输出：

df_out = pd.DataFrame([["A","9:00 AM","04:33:00"],["A","11:12 AM","04:33:00"],["A","1:03 PM","04:33:00"],["B","9:00 AM","04:48:00"],
                       ["B","12:56 PM","04:48:00"],["B","1:07 PM","04:48:00"],["B","1:18 PM","04:48:00"]],columns=["id","time","total_hrs"])

对应表格：

id    time     total_hrs
A   09:00 AM    04:33:00
A   11:12 AM    04:33:00
A   01:03 PM    04:33:00
B   09:00 AM    04:48:00
B   12:56 PM    04:48:00
B   01:07 PM    04:48:00
B   01:18 PM    04:48:00

按以下步骤实现需求：

1. 转换时间格式：将字符串类型的time列转为pandas可计算的时间对象
2. 分组计算差值：按id分组，取每组首尾时间的差值，再加30分钟偏移
3. 格式化结果：将时间差转为HH:MM:SS格式的字符串
4. 映射回原表：把分组计算的结果填充到对应id的所有行

完整代码：

import pandas as pd

# 初始化DataFrame
df = pd.DataFrame([["A","9:00 AM"],["A","11:12 AM"],["A","1:03 PM"],["B","9:00 AM"],["B","12:56 PM"],["B","1:07 PM"],
                   ["B","1:18 PM"]],columns=["id","time"])

# 转换time列为可计算的时间类型
df['time_dt'] = pd.to_datetime(df['time'], format='%I:%M %p')

# 定义分组计算函数
def get_total_hrs(group):
    # 计算首尾时间差+30分钟
    time_diff = group['time_dt'].iloc[-1] - group['time_dt'].iloc[0]
    total = time_diff + pd.Timedelta(minutes=30)
    # 转为HH:MM:SS格式
    return str(total).split()[-1]

# 分组计算并填充到新列
df['total_hrs'] = df.groupby('id')['time_dt'].transform(get_total_hrs)

# 移除中间辅助列，保留原始time列格式
df.drop('time_dt', axis=1, inplace=True)

print(df)

运行后输出结果与预期完全一致：

id      time total_hrs
0  A  9:00 AM  04:33:00
1  A 11:12 AM  04:33:00
2  A  1:03 PM  04:33:00
3  B  9:00 AM  04:48:00
4  B 12:56 PM  04:48:00
5  B  1:07 PM  04:48:00
6  B  1:18 PM  04:48:00

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