Pandas循环DataFrame列表计算：带连续DF依赖的ID筛选需求

需求说明 #

按顺序处理一组DataFrame，每个DF包含ID、i_f、r_f1、r_f2、r_f3列，处理规则如下：

• 第一个DataFrame：筛选满足i_f ≥ 0.95且r_f1 ≤ 0.3、r_f2 ≤ 0.3、r_f3 ≤ 0.3的ID，标记为合格（1），否则为0
• 后续每个DataFrame：
  1. 基础条件：i_f ≥ 0.95
  2. r列阈值规则：如果ID是前一个DF的合格ID，r_f1/r_f2/r_f3的阈值放宽到0.3333；其他ID的阈值仍为0.3
  3. 同时满足基础条件和对应r列阈值条件的ID，标记为当前DF的合格ID

示例数据 #

import pandas as pd

df1 = pd.DataFrame({'ID':[1,2,3],
                    'i_f':[0.967385562,0.869575345,1],
                    'r_f1':[0.18878187,0.327355797,0.100753051],
                    'r_f2':[0.047237449,0.056038276,0.189434048],
                    'r_f3':[0.095283998,0.2554309,0.138240321]})
df2 = pd.DataFrame({'ID':[1,2,3,4,5],
                    'i_f':[0.985,1,0.993297332,1,1],
                    'r_f1':[0.300009355,0.331788473,0.146077926,0.167329833,0.245227094],
                    'r_f2':[0.152293038,0.06668,0.196683885,0.101269411,0.02493159],
                    'r_f3':[0.111617815,0.042016,0.175285158,0.085330897,0.238370325]})
df_lst = [df1, df2]

解决方案代码 #

import pandas as pd

# 存储上一轮的合格ID集合
prev_qualified_ids = set()

for idx, df in enumerate(df_lst):
    # 基础条件：i_f >= 0.95
    mask_if = df['i_f'] >= 0.95
    
    # 根据是否是上一轮合格ID，设置r列的阈值
    if idx == 0:
        # 第一个DF，所有ID用0.3作为阈值
        threshold = 0.3
        mask_r = (df['r_f1'] <= threshold) & (df['r_f2'] <= threshold) & (df['r_f3'] <= threshold)
    else:
        # 后续DF，分两种阈值处理
        threshold_prev = 0.3333
        threshold_default = 0.3
        
        # 对每个r列，分别判断阈值条件
        mask_r1 = df.apply(lambda row: row['r_f1'] <= threshold_prev if row['ID'] in prev_qualified_ids else row['r_f1'] <= threshold_default, axis=1)
        mask_r2 = df.apply(lambda row: row['r_f2'] <= threshold_prev if row['ID'] in prev_qualified_ids else row['r_f2'] <= threshold_default, axis=1)
        mask_r3 = df.apply(lambda row: row['r_f3'] <= threshold_prev if row['ID'] in prev_qualified_ids else row['r_f3'] <= threshold_default, axis=1)
        mask_r = mask_r1 & mask_r2 & mask_r3
    
    # 合并条件，生成合格标记（1为合格，0为不合格）
    df['qualified'] = (mask_if & mask_r).astype(int)
    
    # 更新上一轮合格ID集合
    prev_qualified_ids = set(df[df['qualified'] == 1]['ID'].tolist())
    
    # 输出结果
    print(f"df{idx+1}的合格ID标记：{df['qualified'].tolist()}")

运行结果 #

df1的合格ID标记：[1, 0, 1]
df2的合格ID标记：[1, 0, 1, 1, 1]

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