如何按ACQ/REL递归配对重组Pandas DataFrame？

问题描述 #

现有一个包含ACQ/REL递归配对的Pandas DataFrame，数据示例如下：

import pandas as pd

data = [
    ['2023-06-05 16:51:27.561','ACQ','location'],    
    ['2023-06-05 16:51:27.564','ACQ','location'],
    ['2023-06-05 16:51:27.567','ACQ','location'],
    ['2023-06-05 16:51:27.571','REL','location'],
    ['2023-06-05 16:51:27.573','REL','location'],
    ['2023-06-05 16:51:27.587','REL','location'],
    ['2023-06-05 16:51:28.559','ACQ','location'],
    ['2023-06-05 16:51:28.561','ACQ','location'],
    ['2023-06-05 16:51:28.563','ACQ','location'],
    ['2023-06-05 16:51:28.566','REL','location'],
    ['2023-06-05 16:51:28.569','REL','location'],
    ['2023-06-05 16:51:28.575','REL','location']
]

df = pd.DataFrame(data,columns=['ts','action','name'])

需要将数据按ACQ/REL对重组，以外层ACQ/REL对为组（即最早的ACQ对应最晚的REL，次早的ACQ对应次晚的REL），得到如下格式的输出（每组配对数量不固定）：

0   2023-06-05 16:51:27.561    ACQ  location
5   2023-06-05 16:51:27.587    REL  location
1   2023-06-05 16:51:27.564    ACQ  location
4   2023-06-05 16:51:27.573    REL  location
2   2023-06-05 16:51:27.567    ACQ  location
3   2023-06-05 16:51:27.571    REL  location
6   2023-06-05 16:51:28.559    ACQ  location
11  2023-06-05 16:51:28.575    REL  location
7   2023-06-05 16:51:28.561    ACQ  location
10  2023-06-05 16:51:28.569    REL  location
8   2023-06-05 16:51:28.563    ACQ  location
9   2023-06-05 16:51:28.566    REL  location

解决方案 #

可以通过以下步骤实现需求：

• 标记大组：识别连续的ACQ批次和对应的REL批次，为每一轮ACQ/REL分配唯一组ID
• 排序配对：对每个组内的ACQ按时间升序排列，REL按时间降序排列，实现外层配对逻辑
• 拼接合并：将每个组内的ACQ和REL一一配对后拼接，再合并所有组的结果

具体代码实现如下：

# 1. 生成大组ID：当action从REL切换到ACQ时，组ID加1
df['group'] = ((df['action'] == 'ACQ') & (df['action'].shift() == 'REL')).cumsum()
# 第一个ACQ组的组ID初始化为0
df.loc[df['action'] == 'ACQ', 'group'] = df.loc[df['action'] == 'ACQ', 'group'].ffill().astype(int)

# 2. 拆分ACQ和REL分组，分别排序
acq_groups = df[df['action'] == 'ACQ'].sort_values(['group', 'ts']).groupby('group')
rel_groups = df[df['action'] == 'REL'].sort_values(['group', 'ts'], ascending=[True, False]).groupby('group')

# 3. 遍历每个组，拼接ACQ和REL配对，再合并所有结果
result_list = []
for (group_id, acq_df), (_, rel_df) in zip(acq_groups, rel_groups):
    # 将ACQ和REL按顺序一一配对拼接
    paired = pd.concat([acq_df, rel_df], axis=0).reset_index(drop=True)
    result_list.append(paired)

final_result = pd.concat(result_list).reset_index(drop=True)

# 查看最终结果
print(final_result)

代码说明 #

• 组ID生成：通过判断action的切换点（REL→ACQ）来累加组ID，确保同一轮的ACQ和REL属于同一个组；对第一个ACQ组手动填充初始ID，避免缺失。
• 排序逻辑：ACQ按时间升序保留原始顺序，REL按时间降序排列，这样就能实现"最早ACQ对应最晚REL"的外层配对效果。
• 拼接逻辑：每个组内的ACQ和REL按顺序交替拼接（ACQ1→RELn→ACQ2→RELn-1...），最后合并所有组得到目标格式。

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