如何在Pandas中识别满足条件的前序行对应数据

需求说明 #

针对DataFrame中的每行数据，找到timestamp小于该行timestamp_Pre的前序行中，com_id最大的行对应的com_id和Value值，分别存入新列com_id_pre和value_pre。

输入示例 #

ID          timestamp  com_id   Value   timestamp_Pre
aa  2023-1-5 06:33:27      2    33  2023-1-5 06:33:20
aa  2023-1-5 06:33:33      3    22  2023-1-5 06:33:28
aa  2023-1-5 06:33:39      4    44  2023-1-5 06:33:28
aa  2023-1-5 06:33:45      NA   11  2023-1-5 06:33:35
aa  2023-1-5 06:33:51      NA   99  NA
aa  2023-1-5 06:33:57      NA   88  2023-1-5 06:33:44

输出示例 #

ID          timestamp  com_id   Value   timestamp_Pre   com_id_pre  value_pre
aa  2023-1-5 06:33:27      2    33  2023-1-5 06:33:20      NA   NA
aa  2023-1-5 06:33:33      3    22  2023-1-5 06:33:28       2   33
aa  2023-1-5 06:33:39      4    44  2023-1-5 06:33:28       2   33
aa  2023-1-5 06:33:45      NA   11  2023-1-5 06:33:35       3   22      
aa  2023-1-5 06:33:51      NA   99  NA                      NA  NA
aa  2023-1-5 06:33:57      NA   88  2023-1-5 06:33:44       4   44

问题分析 #

你之前尝试的代码无法实现需求，原因是：

• 第一种代码通过全局筛选+分组取最大，没有针对每行的timestamp_Pre做个性化筛选，无法匹配每行的时间条件。
• 第二种代码同样是全局取符合时间条件的最大值，没有逐行处理，导致所有行的结果都是同一个全局最大值，不符合需求。

解决方案 #

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

首先确保时间列是可比较的datetime格式：

import pandas as pd

# 初始化示例数据
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'],
    'com_id': [2,3,4,pd.NA,pd.NA,pd.NA],
    'Value': [33,22,44,11,99,88],
    'timestamp_Pre': ['2023-1-5 06:33:20', '2023-1-5 06:33:28', '2023-1-5 06:33:28', '2023-1-5 06:33:35', pd.NA, '2023-1-5 06:33:44']
})

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

步骤2：定义逐行处理函数 #

编写函数，针对每行筛选符合时间条件的行，提取com_id最大的记录：

def get_previous_max(row, df):
    # timestamp_Pre为空时直接返回NA
    if pd.isna(row['timestamp_Pre']):
        return pd.Series([pd.NA, pd.NA])
    # 筛选条件：timestamp小于当前行的timestamp_Pre，且com_id不为空
    mask = (df['timestamp'] < row['timestamp_Pre']) & (~df['com_id'].isna())
    filtered_rows = df[mask]
    # 无符合条件的行时返回NA
    if filtered_rows.empty:
        return pd.Series([pd.NA, pd.NA])
    # 找到com_id最大的行（若有多个相同最大值，取最早出现的）
    max_com_row = filtered_rows.loc[filtered_rows['com_id'].idxmax()]
    return pd.Series([max_com_row['com_id'], max_com_row['Value']])

# 应用函数到每行，生成新列
df[['com_id_pre', 'value_pre']] = df.apply(lambda x: get_previous_max(x, df), axis=1)

步骤3：验证结果 #

运行后即可得到符合输出示例的结果，其中：

• 第4行的timestamp_Pre为2023-1-5 06:33:35，筛选出timestamp小于该值且com_id非空的行是前两行，其中com_id最大的是3，对应Value为22，与示例一致。
• 第6行的timestamp_Pre为2023-1-5 06:33:44，筛选出的前序行中com_id最大的是4，对应Value为44，符合示例。

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