如何在Site_ID内跨layer_type筛选首个带前置presence记录的treatment？

针对入侵物种数据的处理需求，我们可以用Python的pandas库高效实现目标——找出每个站点中首个（按时间顺序）有前置presence polygon记录的treatment polygon事件。

步骤说明与代码实现 #

首先准备示例数据：

import pandas as pd

# 构造示例数据
data = [
    ["A", "treatment polygon", "2017-09-01"],
    ["A", "presence polygon", "2019-06-30"],
    ["A", "treatment polygon", "2019-10-18"],
    ["A", "treatment polygon", "2021-07-15"],
    ["A", "treatment polygon", "2020-09-05"],
    ["B", "presence polygon", "2018-06-01"],
    ["B", "treatment polygon", "2019-05-13"],
    ["B", "presence polygon", "2020-05-01"]
]

df = pd.DataFrame(data, columns=["Site_ID", "layer_type", "date"])
# 将日期转为datetime类型，方便后续比较
df["date"] = pd.to_datetime(df["date"])

1. 按站点和日期排序

先对每个站点的记录按日期升序排列，确保时间顺序正确：

df = df.sort_values(by=["Site_ID", "date"]).reset_index(drop=True)

2. 标记目标处理事件

按站点分组，为每条treatment记录判断是否有前置presence记录，并标记首个符合条件的事件：

def mark_target_treatment(group):
    # 提取当前站点所有presence的日期
    presence_dates = group[group["layer_type"] == "presence polygon"]["date"]
    # 标记每条treatment是否有前置presence
    group["has_prior_presence"] = group.apply(
        lambda row: (row["layer_type"] == "treatment polygon") and 
                    (any(presence_dates < row["date"])),
        axis=1
    )
    # 找到首个符合条件的treatment的索引
    target_idx = group[(group["layer_type"] == "treatment polygon") & group["has_prior_presence"]].index.min()
    # 新增字段标记目标处理
    group["is_target_treatment"] = False
    if pd.notna(target_idx):
        group.loc[target_idx, "is_target_treatment"] = True
    return group

# 应用分组函数
result_df = df.groupby("Site_ID").apply(mark_target_treatment).reset_index(drop=True)

3. 查看结果

输出的result_df包含两个新增字段：

• has_prior_presence：标记该treatment是否存在前置presence记录
• is_target_treatment：标记是否为当前站点首个符合条件的treatment

打印结果示例：

Site_ID          layer_type       date  has_prior_presence  is_target_treatment
0       A  treatment polygon 2017-09-01               False                False
1       A   presence polygon 2019-06-30               False                False
2       A  treatment polygon 2019-10-18                True                 True
3       A  treatment polygon 2020-09-05                True                False
4       A  treatment polygon 2021-07-15                True                False
5       B   presence polygon 2018-06-01               False                False
6       B  treatment polygon 2019-05-13                True                 True
7       B   presence polygon 2020-05-01               False                False

4. 可选：提取目标处理及之后的记录

如果需要剔除目标处理之前的数据，可执行以下筛选：

def filter_after_target(group):
    target_date = group[group["is_target_treatment"]]["date"].min()
    if pd.notna(target_date):
        return group[group["date"] >= target_date]
    return group

filtered_df = result_df.groupby("Site_ID").apply(filter_after_target).reset_index(drop=True)

关键逻辑说明 #

• 分组处理确保每个站点独立计算，避免跨站点干扰
• 通过日期对比精准判断treatment是否有前置presence记录
• 利用索引最小值定位首个符合条件的treatment，完成标记

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