如何在Pandas中计算execution_size为NaN的行与前一个非NaN行的时间差

你需要给每个execution_size为NaN的行，计算它和最近的上一行execution_size不为NaN的行之间的时间差，用diff()确实没法直接实现，换用向前填充（ffill()）的思路就能搞定，步骤如下：

步骤1：确保时间列是datetime类型 #

首先得把timestamp列转换成Pandas的datetime格式，不然没法计算时间差：

import pandas as pd

# 构造示例数据
data = {
    'timestamp': [
        '2023-09-11 07:13:41.274903340',
        '2023-09-11 07:13:41.274913154',
        '2023-09-11 07:13:41.274918255',
        '2023-09-11 07:13:41.274953408',
        '2023-09-11 07:13:41.274957424',
        '2023-09-11 07:13:41.274962953',
        '2023-09-11 07:13:41.275002981',
        '2023-09-11 07:13:41.275027960',
        '2023-09-11 07:13:41.277197047',
        '2023-09-11 07:13:41.277207543'
    ],
    'execution_size': [None, 6.0, None, None, 6.0, None, 9.0, None, None, None]
}
df = pd.DataFrame(data, index=range(1, 11))

# 转换timestamp为datetime类型
df['timestamp'] = pd.to_datetime(df['timestamp'])

步骤2：填充最近的有效时间戳 #

用ffill()把每个NaN行对应的最近非NaN行的timestamp填充到新列里：

# 只保留execution_size非NaN的行的timestamp，然后向前填充
df['last_valid_timestamp'] = df.loc[df['execution_size'].notna(), 'timestamp'].ffill()

步骤3：计算时间差 #

直接用当前行的timestamp减去填充后的last_valid_timestamp，得到时间差：

df['time_diff'] = df['timestamp'] - df['last_valid_timestamp']

最终结果 #

处理后的关键列展示：

timestamp  execution_size      last_valid_timestamp       time_diff
1   2023-09-11 07:13:41.274903340             NaN                        NaT             NaT
2   2023-09-11 07:13:41.274913154             6.0 2023-09-11 07:13:41.274913154        0 days 00:00:00
3   2023-09-11 07:13:41.274918255             NaN 2023-09-11 07:13:41.274913154 0 days 00:00:00.000005101
4   2023-09-11 07:13:41.274953408             NaN 2023-09-11 07:13:41.274913154 0 days 00:00:00.000040254
5   2023-09-11 07:13:41.274957424             6.0 2023-09-11 07:13:41.274957424        0 days 00:00:00
6   2023-09-11 07:13:41.274962953             NaN 2023-09-11 07:13:41.274957424 0 days 00:00:00.000005529
7   2023-09-11 07:13:41.275002981             9.0 2023-09-11 07:13:41.275002981        0 days 00:00:00
8   2023-09-11 07:13:41.275027960             NaN 2023-09-11 07:13:41.275002981 0 days 00:00:00.000024979
9   2023-09-11 07:13:41.277197047             NaN 2023-09-11 07:13:41.275002981 0 days 00:00:00.002194066
10  2023-09-11 07:13:41.277207543             NaN 2023-09-11 07:13:41.275002981 0 days 00:00:00.002204562

说明 #

• 第一行因为没有上一个非NaN行，所以last_valid_timestamp和time_diff都是NaT（时间类型空值）
• 非NaN行的时间差为0，符合预期；所有NaN行都对应到了最近的上一个非NaN行的时间戳，计算出的差值完全匹配你的需求

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