如何无需循环提取指定起始行后diff2列相邻为1的目标数据？

当然有，不管用SQL还是Python Pandas这类数据处理工具，都可以用窗口函数/移位函数实现，完全不需要循环，效率还更高。

核心思路 #

1. 先定位到SERIES = 'SERIE A' and rn='1'的起始行，只保留该行及之后的所有数据；
2. 对筛选后的范围，检查每一行的diff2是否为1，同时判断它的前一行或后一行的diff2是否也是1——只要满足其中一个条件，就属于要提取的相邻1的记录。

SQL实现示例（以常见关系型数据库为例） #

假设你的表名为your_table，且有一个能确定行顺序的列（比如自增id，必须保证行的顺序和你需求中的“相邻”一致）：

-- 第一步：找到起始行的标识
WITH start_row AS (
    SELECT id AS start_id
    FROM your_table
    WHERE SERIES = 'SERIE A' AND rn = '1'
),
-- 第二步：筛选出起始行及之后的所有数据
filtered_data AS (
    SELECT *
    FROM your_table
    WHERE id >= (SELECT start_id FROM start_row)
),
-- 第三步：获取每行的前/后行diff2值
adjacent_check AS (
    SELECT
        *,
        -- 取前一行的diff2
        LAG(diff2) OVER (ORDER BY id) AS prev_diff2,
        -- 取后一行的diff2
        LEAD(diff2) OVER (ORDER BY id) AS next_diff2
    FROM filtered_data
)
-- 最后筛选符合条件的行
SELECT *
FROM adjacent_check
WHERE diff2 = 1
AND (prev_diff2 = 1 OR next_diff2 = 1);

Python Pandas实现示例 #

如果用Pandas处理本地数据集：

import pandas as pd

# 假设df是你的原始数据表
# 1. 定位起始行的索引
start_idx = df[(df['SERIES'] == 'SERIE A') & (df['rn'] == '1')].index[0]
# 2. 截取起始行及之后的数据
filtered_df = df.loc[start_idx:]

# 3. 生成前一行/后一行的diff2列
filtered_df['prev_diff2'] = filtered_df['diff2'].shift(1)
filtered_df['next_diff2'] = filtered_df['diff2'].shift(-1)

# 4. 筛选目标数据
result = filtered_df[(filtered_df['diff2'] == 1) & 
                     ((filtered_df['prev_diff2'] == 1) | (filtered_df['next_diff2'] == 1))]

内容的提问来源于stack exchange，提问作者Osvaldo Assunção