Python实现DataFrame中连续3个及以上数字匹配查询

需求：现有两个pandas DataFrame，需从df1的数字序列中，筛选出df2里包含连续3个及以上匹配数字的行。示例中df1的序列为[2,4,6,8,9,10]，df2包含多条数据，目标结果是包含连续匹配数字8、9、10的Id为6的行。

初始化数据 #

import pandas as pd 

# 初始化df1：单一行的数字序列
cols = ['Num1','Num2','Num3','Num4','Num5','Num6']
df1 = pd.DataFrame([[2,4,6,8,9,10]], columns=cols)

# 初始化df2：包含Id和数字序列的多行数据
df2 = pd.DataFrame([[1,1,2,4,5,6,8],
                    [2,5,6,20,22,23,34],
                    [3,8,12,13,34,45,46],
                    [4,9,10,14,29,32,33],
                    [5,1,22,13,23,33,35],
                    [6,1,6,7,8,9,10],
                    [7,0,2,3,5,6,8]], 
                   columns=['Id','Num1','Num2','Num3','Num4','Num5','Num6'])

实现逻辑代码 #

# 提取df1的数字序列为列表
df1_sequence = df1.iloc[0].tolist()

# 生成df1中所有长度≥3的连续子序列
min_length = 3
target_subsequences = []
for i in range(len(df1_sequence) - min_length + 1):
    # 先加入长度为3的子序列
    target_subsequences.append(tuple(df1_sequence[i:i+min_length]))
    # 再加入更长的连续子序列（长度4、5、6）
    for l in range(min_length+1, len(df1_sequence)-i+1):
        target_subsequences.append(tuple(df1_sequence[i:i+l]))

# 定义函数：检查一行的数字序列是否包含目标连续子序列
def has_matching_subsequence(row):
    row_sequence = row[1:].tolist()  # 跳过Id列，提取数字部分
    # 生成当前行所有长度≥3的连续子序列
    row_subsequences = []
    for i in range(len(row_sequence) - min_length + 1):
        for l in range(min_length, len(row_sequence)-i+1):
            row_subsequences.append(tuple(row_sequence[i:i+l]))
    # 检查是否存在匹配的子序列
    return any(subseq in target_subsequences for subseq in row_subsequences)

# 筛选df2中符合条件的行
results = df2[df2.apply(has_matching_subsequence, axis=1)]

print(results)

输出结果 #

Id  Num1  Num2  Num3  Num4  Num5  Num6
5   6     1     6     7     8     9    10

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