基于AC27/AC28等状态的DataFrame行过滤与Flag_Status赋值问题

核心思路 #

要避免两个场景结果互相覆盖，需先按im_id分组，分别验证每个组是否符合场景条件，再优先处理条件更严格的场景（场景2），最后给对应记录精准赋值，确保逻辑独立不冲突。

步骤实现 #

1. 准备示例数据 #

import pandas as pd

# 构造示例DataFrame
data = {
    'im_id': ['A', 'A', 'A', 'B', 'B', 'B', 'B', 'C', 'C'],
    'sub_status': ['AC27', 'AC28', 'CC38', 'AC27', 'AC28', 'AC29', 'AC01', 'AC27', 'AC29'],
    'status_date': ['2023-01-01', '2023-01-02', '2023-01-03', '2023-02-01', '2023-02-02', '2023-02-03', '2023-02-04', '2023-03-01', '2023-03-02']
}
DF = pd.DataFrame(data)
DF['status_date'] = pd.to_datetime(DF['status_date'])
# 初始化Flag列
DF['Flag_Status'] = None

2. 分组处理逻辑 #

定义分组处理函数，分别校验两个场景的条件，再精准赋值：

def process_single_im(group):
    # 校验场景1：存在AC27、AC28，无CC38，且AC28在AC27之后
    has_ac27 = 'AC27' in group['sub_status'].values
    has_ac28 = 'AC28' in group['sub_status'].values
    no_cc38 = 'CC38' not in group['sub_status'].values
    scenario1_ok = False
    
    if has_ac27 and has_ac28 and no_cc38:
        ac27_time = group[group['sub_status'] == 'AC27']['status_date'].iloc[0]
        ac28_time = group[group['sub_status'] == 'AC28']['status_date'].iloc[0]
        if ac28_time > ac27_time:
            scenario1_ok = True
    
    # 校验场景2：存在AC27、AC28、AC29，无AC01，且顺序为AC27→AC28→AC29
    has_ac29 = 'AC29' in group['sub_status'].values
    no_ac01 = 'AC01' not in group['sub_status'].values
    scenario2_ok = False
    
    if has_ac27 and has_ac28 and has_ac29 and no_ac01:
        ac27_time = group[group['sub_status'] == 'AC27']['status_date'].iloc[0]
        ac28_time = group[group['sub_status'] == 'AC28']['status_date'].iloc[0]
        ac29_time = group[group['sub_status'] == 'AC29']['status_date'].iloc[0]
        if ac28_time > ac27_time and ac29_time > ac28_time:
            scenario2_ok = True
    
    # 赋值：优先处理场景2，避免覆盖
    if scenario2_ok:
        group.loc[group['sub_status'].isin(['AC27', 'AC28', 'AC29']), 'Flag_Status'] = 'No Infusion Only'
    elif scenario1_ok:
        group.loc[group['sub_status'].isin(['AC27', 'AC28']), 'Flag_Status'] = 'Cancel Not Available'
    
    return group

# 应用分组处理
DF = DF.groupby('im_id').apply(process_single_im).reset_index(drop=True)

3. 最终结果 #

执行后DF的Flag_Status列会按要求生成：

• im_id A的AC27、AC28记录：Cancel Not Available
• im_id B的AC27、AC28、AC29记录：No Infusion Only
• 其余不符合条件的记录：保持None

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