如何移除无测试#0的个体数据及序列中断后的行？

问题背景 #

现有不同个体的测试编号数据（编号范围0-3），原始数据如下：

ID            Nb_Test
A1            0
A1            1
A1            2
A2            1
A2            2
A2            3
A3            0
A3            1
A3            3

需要完成两个处理步骤：

• 移除所有未包含测试编号0的个体（比如A2无0，直接剔除全部数据）
• 对保留的个体，移除其测试序列中断后的所有数据（比如A3的序列0→1→3，1到3出现中断，需剔除3及之后的数据）

最终期望结果：

ID            Nb_Test
A1            0
A1            1
A1            2
A3            0
A3            1

解决方案 #

方法一：SQL实现 #

通过CTE分步处理，先筛选有效个体，再检测序列中断点，最后保留有效数据：

WITH valid_ids AS (
    -- 筛选出包含测试0的个体ID
    SELECT DISTINCT ID
    FROM test_data
    WHERE Nb_Test = 0
),
gap_detection AS (
    -- 计算每个个体测试编号的相邻差值，判断是否中断
    SELECT 
        t.ID,
        t.Nb_Test,
        t.Nb_Test - LAG(t.Nb_Test) OVER (PARTITION BY t.ID ORDER BY t.Nb_Test) AS gap
    FROM test_data t
    JOIN valid_ids v ON t.ID = v.ID
),
max_valid_test AS (
    -- 确定每个个体的最大有效测试编号（中断前的最后一个编号）
    SELECT 
        ID,
        COALESCE(
            MIN(CASE WHEN gap != 1 THEN Nb_Test END) - 1,
            MAX(Nb_Test)
        ) AS max_test
    FROM gap_detection
    GROUP BY ID
)
-- 筛选出每个个体不超过最大有效编号的数据
SELECT t.ID, t.Nb_Test
FROM test_data t
JOIN max_valid_test m ON t.ID = m.ID
WHERE t.Nb_Test <= m.max_test
ORDER BY t.ID, t.Nb_Test;

方法二：Python Pandas实现 #

利用分组和差值计算来过滤数据：

import pandas as pd

# 构造原始数据
df = pd.DataFrame({
    'ID': ['A1', 'A1', 'A1', 'A2', 'A2', 'A2', 'A3', 'A3', 'A3'],
    'Nb_Test': [0, 1, 2, 1, 2, 3, 0, 1, 3]
})

# 第一步：保留包含测试0的个体
valid_ids = df[df['Nb_Test'] == 0]['ID'].unique()
df = df[df['ID'].isin(valid_ids)]

# 第二步：过滤每个个体的中断后数据
def keep_continuous(group):
    group_sorted = group.sort_values('Nb_Test')
    # 计算相邻测试编号的差值
    gaps = group_sorted['Nb_Test'].diff().dropna()
    # 找到第一个中断的位置
    first_break = gaps[gaps > 1].index.min()
    if pd.notna(first_break):
        return group_sorted.loc[:first_break - 1]
    return group_sorted

# 分组处理并整理结果
result = df.groupby('ID').apply(keep_continuous).reset_index(drop=True)
print(result)

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