如何用Pandas实现当前行min_time与上一行max_time的差值计算？

核心实现思路 #

要完成这个需求，核心是借助pandas的shift()函数实现列偏移，结合时间类型转换完成计算，具体步骤如下：

• 先将字符串格式的时间列转为pandas可计算的datetime类型
• 用shift(1)将max_time列下移一行，让当前行的min_time与上一行的max_time对齐
• 计算两者的时间差，再处理空值并按需格式化结果

完整代码示例 #

import pandas as pd

# 初始化示例数据
data = pd.DataFrame()
data['datetime'] = ['18-6-22 8:22:22', '18-6-22 8:22:23', '18-6-22 8:22:24', '18-6-22 8:22:25', '18-6-22 8:22:26', '18-6-22 11:22:27']
data['min_time'] = ['18-6-22 8:22:22', '18-6-22 8:22:23', '18-6-22 8:22:24', '18-6-22 8:22:25', '18-6-22 8:22:26', '18-6-22 11:22:27']
data['max_time'] = ['18-6-22 8:22:22', '18-6-22 8:22:23', '18-6-22 8:22:24', '18-6-22 8:22:25', '18-6-22 8:22:26', '18-6-22 11:22:27']

# 1. 转换时间列为datetime类型
data['min_time'] = pd.to_datetime(data['min_time'], format='%y-%m-%d %H:%M:%S')
data['max_time'] = pd.to_datetime(data['max_time'], format='%y-%m-%d %H:%M:%S')

# 2. 计算当前行min_time与上一行max_time的时间差
data['t_diff_time'] = data['min_time'] - data['max_time'].shift(1)

# 3. 处理空值并格式化为示例样式
def format_timedelta(td):
    if pd.isna(td):
        return 0
    days = td.days
    hours, rem = divmod(td.seconds, 3600)
    minutes, seconds = divmod(rem, 60)
    return f'{days} Day {hours}:{minutes}:{seconds}'

data['t_diff_time'] = data['t_diff_time'].apply(format_timedelta)

print(data)

运行结果 #

datetime           min_time           max_time    t_diff_time
0  18-6-22 8:22:22 2018-06-22 08:22:22 2018-06-22 08:22:22              0
1  18-6-22 8:22:23 2018-06-22 08:22:23 2018-06-22 08:22:23  0 Day 0:0:1
2  18-6-22 8:22:24 2018-06-22 08:22:24 2018-06-22 08:22:24  0 Day 0:0:1
3  18-6-22 8:22:25 2018-06-22 08:22:25 2018-06-22 08:22:25  0 Day 0:0:1
4  18-6-22 8:22:26 2018-06-22 08:22:26 2018-06-22 08:22:26  0 Day 0:0:1
5  18-6-22 11:22:27 2018-06-22 11:22:27 2018-06-22 11:22:27  0 Day 3:0:1

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