基于组内多行条件更新Pandas DataFrame的status列值问题

方法一：基于组内active ID集合匹配 #

先构建每个分组下的active ID集合，再逐行判断是否满足条件：

import pandas as pd

data = {'id': [1, 2, 3, 4, 5, 6, 7],
        'group_id': ['A', 'A', 'A', 'B', 'B', 'B', 'B'],
        'status': ['pending', 'active', 'pending', 'pending', 'active', 'pending', 'pending'],
        'related_id': [None, None, 1, None, None, 4, 4]}
df = pd.DataFrame(data)

# 1. 提取每个group下的active id集合
active_id_map = df[df['status'] == 'active'].groupby('group_id')['id'].apply(set).reset_index(name='active_ids')

# 2. 合并到原数据，让每行都能获取所在组的active id列表
df = df.merge(active_id_map, on='group_id', how='left')

# 3. 构建更新条件：当前status是pending，且related_id存在并属于本组的active id
update_mask = (df['status'] == 'pending') & \
              df['related_id'].notna() & \
              df.apply(lambda row: row['related_id'] in row['active_ids'], axis=1)

# 4. 更新status列
df.loc[update_mask, 'status'] = 'resolved'

# 清理临时生成的列
df.drop('active_ids', axis=1, inplace=True)

print(df)

方法二：基于(group_id, id)配对匹配（更高效） #

对于大数据集，避免逐行apply，通过配对组合判断，效率更高：

import pandas as pd

data = {'id': [1, 2, 3, 4, 5, 6, 7],
        'group_id': ['A', 'A', 'A', 'B', 'B', 'B', 'B'],
        'status': ['pending', 'active', 'pending', 'pending', 'active', 'pending', 'pending'],
        'related_id': [None, None, 1, None, None, 4, 4]}
df = pd.DataFrame(data)

# 1. 提取所有active行的(group_id, id)配对
active_pairs = set(df[df['status'] == 'active'][['group_id', 'id']].apply(tuple, axis=1))

# 2. 检查当前行的(group_id, related_id)是否在active配对中
df['valid_related'] = df[['group_id', 'related_id']].apply(tuple, axis=1).isin(active_pairs)

# 3. 构建更新条件
update_mask = (df['status'] == 'pending') & df['related_id'].notna() & df['valid_related']

# 4. 更新status
df.loc[update_mask, 'status'] = 'resolved'

# 清理临时列
df.drop('valid_related', axis=1, inplace=True)

print(df)

两种方法都能得到期望输出：

id group_id    status  related_id
0   1        A   pending        None
1   2        A    active        None
2   3        A  resolved           1
3   4        B   pending        None
4   5        B    active        None
5   6        B  resolved           4
6   7        B  resolved           4

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