如何在Pandas中按ID和approved状态保留带2秒容忍度的最大值？

以下是基于Pandas的实现步骤，完全匹配你给出的需求效果：

步骤1：数据准备与时间转换 #

首先将time列转换为秒数，方便后续时间差计算：

import pandas as pd

# 加载数据（如果是从CSV读取，替换为pd.read_csv("你的文件路径")）
data = [
    [1, "0:00", 10, False],
    [1, "0:01", 20, True],
    [1, "0:02", 30, True],
    [1, "0:03", 20, True],
    [1, "0:04", 40, False],
    [1, "0:05", 35, False],
    [1, "0:06", 60, False],
    [2, "0:07", 20, True],
    [2, "0:08", 30, True],
    [2, "0:09", 50, False],
    [2, "0:10", 45, False],
    [2, "0:11", 70, False],
    [2, "0:12", 62, False]
]
df = pd.DataFrame(data, columns=["id", "time", "value", "approved"])

# 转换time列为秒数
df["time_sec"] = df["time"].str.split(":").apply(lambda x: int(x[0])*60 + int(x[1]))

步骤2：按ID分组并划分数据块 #

以approved状态变化、时间间隔超过2秒作为新数据块的划分依据：

def create_blocks(group):
    # 标记状态变化
    group["status_change"] = group["approved"] != group["approved"].shift()
    # 标记时间间隔超过2秒的情况
    group["time_diff"] = group["time_sec"] - group["time_sec"].shift()
    group["time_diff_exceed"] = group["time_diff"] > 2
    # 生成数据块编号
    group["block"] = (group["status_change"] | group["time_diff_exceed"].fillna(False)).cumsum()
    return group

df = df.groupby("id").apply(create_blocks).reset_index(drop=True)

步骤3：分类型处理数据块 #

针对approved=True和approved=False的数据块分别计算最大值与对应时间：

# 标记每个数据块的approved状态
block_status = df.groupby(["id", "block"])["approved"].first().reset_index()
block_status.columns = ["id", "block", "block_approved"]
df = df.merge(block_status, on=["id", "block"], how="left")

# 处理approved=True的块：计算累计最大值及对应最早出现时间
def process_true_blocks(group):
    group["cum_max"] = group["value"].cummax()
    first_occur = group.drop_duplicates("cum_max", keep="first").set_index("cum_max")["time"]
    group["max_approved"] = group["cum_max"]
    group["max_time"] = group["cum_max"].map(first_occur)
    return group

# 处理approved=False的块：取块内第一个记录的数值与时间（首块特殊处理为NaN）
def process_false_blocks(group):
    first_val = group.iloc[0]["value"]
    first_t = group.iloc[0]["time"]
    if group["block"].iloc[0] == 1:
        group["max_approved"] = None
        group["max_time"] = None
    else:
        group["max_approved"] = first_val
        group["max_time"] = first_t
    return group

# 分组处理后合并结果
df_true = df[df["block_approved"]].groupby(["id", "block"]).apply(process_true_blocks).reset_index(drop=True)
df_false = df[~df["block_approved"]].groupby(["id", "block"]).apply(process_false_blocks).reset_index(drop=True)
df_result = pd.concat([df_true, df_false]).sort_values(["id", "time_sec"]).reset_index(drop=True)

步骤4：整理最终结果 #

保留需要的列并输出：

df_result = df_result[["id", "time", "value", "approved", "max_approved", "max_time"]]
print(df_result.to_string(index=False))

最终输出效果 #

id  time  value  approved  max_approved max_time
  1  0:00     10     False           NaN      NaN
  1  0:01     20      True          20.0     0:01
  1  0:02     30      True          30.0     0:02
  1  0:03     20      True          30.0     0:02
  1  0:04     40     False          40.0     0:04
  1  0:05     35     False          40.0     0:04
  1  0:06     60     False          40.0     0:04
  2  0:07     20      True          20.0     0:07
  2  0:08     30      True          30.0     0:08
  2  0:09     50     False          50.0     0:09
  2  0:10     45     False          50.0     0:09
  2  0:11     70     False          50.0     0:09
  2  0:12     62     False          50.0     0:09

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