如何在Pandas中为DataFrame添加累计时间的start_time和end_time列

给定如下Pandas DataFrame：

import pandas as pd

df = pd.DataFrame([["X","0 min","30 mins"],["X","1 hour 1 min","20 mins"],["X","1 min","30 mins"],["X","41 mins","28 mins"],
                   ["Y","0 min","30 mins"],["Y","35 mins","25 mins"],["Y","1 hour 21 mins","30 mins"]],columns=["id","travel_time","dur"])

对应的表格：

需要新增start_time和end_time两列，规则如下：

• 每个id的初始基准时间为9:00 AM
• 每个id的首行start_time = 基准时间 + 当前行travel_time
• 后续行start_time = 上一行的end_time + 当前行travel_time
• end_time = 当前行start_time + 当前行dur

预期输出的DataFrame：

df_out = pd.DataFrame([["X","0 min","30 mins","9:00 AM","9:00 AM"],["X","1 hour 1 min","20 mins","10:01 AM","10:21 AM"],
                   ["X","1 min","30 mins","10:22 AM","10:52 AM"],["X","41 mins","28 mins","11:33 AM","12:01 PM"],
                   ["Y","0 min","30 mins","9:00 AM","9:00 AM"],["Y","35 mins","25 mins","9:35 AM","10:00 AM"],
                   ["Y","1 hour 21 mins","30 mins","11:21 AM","11:51 AM"]],columns=["id","travel_time","dur","start_time","end_time"])

对应的表格：

步骤1：定义时间字符串转Timedelta的函数 #

先实现一个函数，把"1 hour 1 min"、"30 mins"这类字符串转换成Pandas可计算的Timedelta类型：

def str_to_timedelta(time_str):
    parts = time_str.split()
    hours = 0
    minutes = 0
    for i in range(0, len(parts), 2):
        val = int(parts[i])
        unit = parts[i+1]
        if 'hour' in unit:
            hours += val
        elif 'min' in unit:
            minutes += val
    return pd.Timedelta(hours=hours, minutes=minutes)

步骤2：转换时间列为Timedelta类型 #

将原始数据中的travel_time和dur列转换为Timedelta，方便后续时间运算：

df['travel_delta'] = df['travel_time'].apply(str_to_timedelta)
df['dur_delta'] = df['dur'].apply(str_to_timedelta)

步骤3：按id分组计算累计时间并生成目标列 #

以9:00 AM为基准时间，按id分组计算累计的行程时间，再推导start_time和end_time：

# 定义基准时间
base_time = pd.to_datetime('9:00 AM')

# 按id分组，累计计算行程时间
df['cum_travel'] = df.groupby('id')['travel_delta'].cumsum()

# 计算start_time并格式化输出
df['start_time'] = (base_time + df['cum_travel']).dt.strftime('%I:%M %p').str.lstrip('0')

# 计算end_time并格式化输出
df['end_time'] = (pd.to_datetime(df['start_time']) + df['dur_delta']).dt.strftime('%I:%M %p').str.lstrip('0')

步骤4：清理临时列，得到最终结果 #

删除中间过程中生成的临时列，保留需要的字段：

df_final = df[['id', 'travel_time', 'dur', 'start_time', 'end_time']]

完整代码 #

import pandas as pd

def str_to_timedelta(time_str):
    parts = time_str.split()
    hours = 0
    minutes = 0
    for i in range(0, len(parts), 2):
        val = int(parts[i])
        unit = parts[i+1]
        if 'hour' in unit:
            hours += val
        elif 'min' in unit:
            minutes += val
    return pd.Timedelta(hours=hours, minutes=minutes)

# 加载原始数据
df = pd.DataFrame([["X","0 min","30 mins"],["X","1 hour 1 min","20 mins"],["X","1 min","30 mins"],["X","41 mins","28 mins"],
                   ["Y","0 min","30 mins"],["Y","35 mins","25 mins"],["Y","1 hour 21 mins","30 mins"]],columns=["id","travel_time","dur"])

# 转换时间列
df['travel_delta'] = df['travel_time'].apply(str_to_timedelta)
df['dur_delta'] = df['dur'].apply(str_to_timedelta)

# 计算目标列
base_time = pd.to_datetime('9:00 AM')
df['cum_travel'] = df.groupby('id')['travel_delta'].cumsum()
df['start_time'] = (base_time + df['cum_travel']).dt.strftime('%I:%M %p').str.lstrip('0')
df['end_time'] = (pd.to_datetime(df['start_time']) + df['dur_delta']).dt.strftime('%I:%M %p').str.lstrip('0')

# 整理最终结果
df_final = df[['id', 'travel_time', 'dur', 'start_time', 'end_time']]
print(df_final)

运行上述代码后，df_final即为符合预期的结果。

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