求每行指定时间范围内Value列的最大值（无循环实现）

需求说明 #

对每行数据，计算所有timestamp介于当前行timestamp_Post和timestamp_Pre之间的记录的Value列最大值，作为该行的Max_value。需处理Value、timestamp_Pre、timestamp_Post字段的空值情况：若timestamp_Pre或timestamp_Post为空，则无符合条件的记录，Max_value设为NA；若符合条件的记录中Value全为空，Max_value也设为NA。

输入数据 #

ID          timestamp  Value    timestamp_Pre   timestamp_Post
aa  2023-1-5 06:33:27     33    2023-1-5 06:33:20    2023-1-5 06:33:10
aa  2023-1-5 06:33:33     22    2023-1-5 06:33:30    2023-1-5 06:33:27
aa  2023-1-5 06:33:39     44    2023-1-5 06:33:33    2023-1-5 06:33:28
aa  2023-1-5 06:33:45     67    2023-1-5 06:33:39    2023-1-5 06:33:26
aa  2023-1-5 06:33:51     99    NA                   2023-1-5 06:33:44
aa  2023-1-5 06:33:57     88    2023-1-5 06:33:44    NA

期望输出 #

ID          timestamp  Value    timestamp_Pre   timestamp_Post      Max_value
aa  2023-1-5 06:33:27     33    2023-1-5 06:33:20    2023-1-5 06:33:10  NA
aa  2023-1-5 06:33:33     22    2023-1-5 06:33:30    2023-1-5 06:33:27  33
aa  2023-1-5 06:33:39     44    2023-1-5 06:33:33    2023-1-5 06:33:28  22
aa  2023-1-5 06:33:45     67    2023-1-5 06:33:39    2023-1-5 06:33:26  44
aa  2023-1-5 06:33:51     99    NA                   2023-1-5 06:33:44  NA
aa  2023-1-5 06:33:57     88    2023-1-5 06:33:44    NA             NA

现有问题代码 #

以下代码返回Max_Value列全为空，原因是逻辑错误：它逐行比较当前行自己的timestamp与自己的timestamp_Pre/timestamp_Post，而非匹配其他行的timestamp：

m=(df['timestamp']<df['timestamp_Pre']) & (df['timestamp']>df['timestamp_Post']) 
df['Max_Value']=df.where(m).groupby('id')['value'].transform('max')

已实现的循环方案 #

通过for循环可实现需求，但效率较低，需要无循环的向量化解决方案：

for i in range(len(df)):
    df_temp=df[(df['timestamp']<=df['timestamp_Pre'].iloc[i]) & (df['timestamp']>=df['timestamp_Post'].iloc[i])]
    df.iloc[i]['Max_value']=df_temp['Value'].max()

无循环的向量化解决方案 #

利用numpy广播实现向量化计算，避免循环，效率更高：

步骤1：转换时间列为datetime类型 #

首先确保所有时间字段为datetime格式，否则无法进行比较：

import pandas as pd
import numpy as np

# 初始化输入数据的DataFrame
df = pd.DataFrame({
    'ID': ['aa']*6,
    'timestamp': ['2023-1-5 06:33:27', '2023-1-5 06:33:33', '2023-1-5 06:33:39', '2023-1-5 06:33:45', '2023-1-5 06:33:51', '2023-1-5 06:33:57'],
    'Value': [33,22,44,67,99,88],
    'timestamp_Pre': ['2023-1-5 06:33:20', '2023-1-5 06:33:30', '2023-1-5 06:33:33', '2023-1-5 06:33:39', pd.NA, '2023-1-5 06:33:44'],
    'timestamp_Post': ['2023-1-5 06:33:10', '2023-1-5 06:33:27', '2023-1-5 06:33:28', '2023-1-5 06:33:26', '2023-1-5 06:33:44', pd.NA]
})

# 转换时间列格式
df['timestamp'] = pd.to_datetime(df['timestamp'])
df['timestamp_Pre'] = pd.to_datetime(df['timestamp_Pre'])
df['timestamp_Post'] = pd.to_datetime(df['timestamp_Post'])

步骤2：生成匹配掩码矩阵 #

通过numpy广播生成布尔矩阵，其中mask[i,j]表示第j行的timestamp是否符合第i行的时间范围要求：

# 提取时间数组并变形，实现广播匹配
timestamps = df['timestamp'].values[:, np.newaxis]  # 变形为(6,1)，与(6,)的pre/post广播
pre_times = df['timestamp_Pre'].values
post_times = df['timestamp_Post'].values

# 生成初始掩码：j行timestamp在i行的post和pre之间
mask = (timestamps >= post_times) & (timestamps <= pre_times)

# 处理空值：若pre或post为空，对应位置掩码设为False
mask = np.where(pd.isna(post_times)[:, np.newaxis], False, mask)
mask = np.where(pd.isna(pre_times)[:, np.newaxis], False, mask)

步骤3：计算每行的最大值 #

利用掩码筛选符合条件的Value，计算每行的最大值：

# 处理Value空值：替换为负无穷，不影响max计算
values = df['Value'].values.copy()
values[pd.isna(values)] = -np.inf

# 计算每行的最大值，无符合条件则设为NA
max_values = np.max(values * mask, axis=1)
max_values[max_values == -np.inf] = np.nan

# 将结果添加到原DataFrame
df['Max_value'] = max_values

运行结果 #

执行后得到的DataFrame与期望输出完全一致。

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