求助：基于Dataframe的STATUS列生成符合条件的标记列（Python）

首先准备示例数据并处理日期格式，确保时间顺序判断准确：

import pandas as pd

# 构建示例DataFrame
data = {
    'DATE': ['01/01/2022', '02/01/2022', '03/01/2022', '04/01/2022', '05/01/2022'],
    'CUSTOMER': ['A', 'A', 'A', 'A', 'A'],
    'STATUS': ['ACTIVATED', 'ACTIVE', 'INACTIVE', 'EXCEPTION', 'INACTIVATED']
}
df = pd.DataFrame(data)
# 转换日期列，避免字符串排序误差
df['DATE'] = pd.to_datetime(df['DATE'], format='%d/%m/%Y')

需求1：所有满足条件的行标记为1 #

按客户分组，判断每个客户是否存在ACTIVATED状态，且INACTIVATED出现在ACTIVATED之后，符合条件则该客户所有行标记为1：

def mark_all_valid_rows(group):
    # 检查是否有ACTIVATED记录
    has_activated = (group['STATUS'] == 'ACTIVATED').any()
    has_valid_inactivated = False
    if has_activated:
        # 获取最早的ACTIVATED日期
        first_activated = group[group['STATUS'] == 'ACTIVATED']['DATE'].min()
        # 检查是否有INACTIVATED出现在ACTIVATED之后
        inactivated_rows = group[group['STATUS'] == 'INACTIVATED']
        if not inactivated_rows.empty:
            has_valid_inactivated = (inactivated_rows['DATE'] > first_activated).any()
    # 为组内所有行生成标记
    return pd.Series([1 if has_activated and has_valid_inactivated else '' for _ in group], index=group.index)

df['MARK_ALL'] = df.groupby('CUSTOMER').apply(mark_all_valid_rows).droplevel(0)

需求2：仅INACTIVATED行标记为1 #

同样按客户分组，仅对满足前置条件的INACTIVATED行标记为1，其余行留空：

def mark_only_inactivated(group):
    has_activated = (group['STATUS'] == 'ACTIVATED').any()
    if not has_activated:
        return pd.Series(['']*len(group), index=group.index)
    first_activated = group[group['STATUS'] == 'ACTIVATED']['DATE'].min()
    # 生成标记：仅符合条件的INACTIVATED行标记1
    mask = (group['STATUS'] == 'INACTIVATED') & (group['DATE'] > first_activated)
    return pd.Series(['1' if val else '' for val in mask], index=group.index)

df['MARK_INACTIVATED_ONLY'] = df.groupby('CUSTOMER').apply(mark_only_inactivated).droplevel(0)

最终结果 #

执行上述代码后，DataFrame输出如下：

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