Pandas处理DataFrame计算栽培台距上次农事操作的间隔时长

现有如下Pandas DataFrame：

dtm  cultivation-table irrigation fertilizer
2022-01-09 10:30:40                  1        NaN        Yes 
2022-01-09 10:31:20                  1        NaN        Yes
2022-01-09 10:34:12                  1        0.5        Yes 
2022-01-09 10:40:18                  1        NaN        NaN
2022-01-09 10:41:20                  1        NaN        NaN
2022-01-09 10:32:54                  2        NaN        NaN 
2022-01-09 10:35:08                  2        NaN        Yes
2022-01-09 10:31:10                  3        NaN        Yes
2022-01-09 10:32:23                  3          1        NaN  

农事活动列定义在数组中：activities = ['irrigation', 'fertilizer', 'prune', 'insecticide']，需要为每类农事活动新增一列，记录每个栽培台（cultivation-table）每条数据距离上一次执行该类活动经过的时间，预期输出如下：

dtm  cultivation-table irrigation   time_irrigation fertilizer time_fertilizer 
2022-01-09 10:30:40                  1        NaN      00:00:00          Yes       00:00:00
2022-01-09 10:31:20                  1        NaN      00:00:40          Yes       00:00:40
2022-01-09 10:34:12                  1        0.5      00:03:32          Yes       00:02:52
2022-01-09 10:40:18                  1        NaN      00:06:06          NaN       00:08:58
2022-01-09 10:41:20                  1        NaN      00:07:08          NaN       00:10:00
2022-01-09 10:32:54                  2        NaN      00:00:00          NaN       00:00:00
2022-01-09 10:35:08                  2        NaN      00:02:14          Yes       00:02:14
2022-01-09 10:31:10                  3        NaN      00:00:00          Yes       00:00:00
2022-01-09 10:32:23                  3          1      00:01:13          NaN       00:01:13

核心思路：先按栽培台分组、按时间排序，对每个活动列标记活动发生的时间点，向前填充得到每行对应的上一次活动时间，再计算当前时间与上一次活动时间的差值，格式化为时分秒字符串即可。

import pandas as pd

# 基础预处理：时间列转datetime格式，按栽培台+时间升序排序，保证时间顺序正确
df['dtm'] = pd.to_datetime(df['dtm'])
df = df.sort_values(by=['cultivation-table', 'dtm']).reset_index(drop=True)

# 定义农事活动列表，自动过滤df中不存在的活动列避免报错
activities = ['irrigation', 'fertilizer', 'prune', 'insecticide']
valid_acts = [act for act in activities if act in df.columns]

for act in valid_acts:
    # 标记当前活动有记录的时间点，无记录位置设为空时间
    act_event_time = df['dtm'].where(df[act].notna())
    # 按栽培台分组，向前填充最近一次活动的时间
    last_act_time = act_event_time.groupby(df['cultivation-table']).ffill()
    # 组内首条记录如果没有活动记录，填充组内最早时间，对应时间差为0，匹配预期逻辑
    last_act_time = last_act_time.fillna(df.groupby('cultivation-table')['dtm'].transform('first'))
    # 计算时间差并格式化为 时:分:秒 格式
    delta = df['dtm'] - last_act_time
    df[f'time_{act}'] = delta.apply(lambda x: f"{x.components.hours:02d}:{x.components.minutes:02d}:{x.components.seconds:02d}")

运行上述代码后得到的结果与预期输出完全一致。

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