多条件下合并两个Pandas DataFrame的技术实现求助

样本数据 #

grpdf #

item        space   ID      row_no      item_order  aisle_side
Apple       0.25    3       Row 1           1           Front
Apple       0.25    3       Row 1           1           Front
Lime        0.125   3       Row 1           3           Front
Grape       0.25    3       Row 1           6           Front       
Grape       0.25    3       Row 1           6           Front
Kiwi        0.125   3       Row 1           7           Front
PP Orange   0.25    3       Row 1           8           RightEnd
PP Orange   0.25    3       Row 1           8           RightEnd
PP Kiwi     0.125   3       Row 1           9           Back
PP Apple    0.125   3       Row 1           10          Back

deddf #

Store   Aisle       TableNo     TableBit    dedicated_table     Direction   
11      33              1           1                           RightEnd    
11      33              1           2                           RightEnd    
11      33              2           1       Nuts                Front
11      33              2           2                           Front           
11      33              2           3                           Front           
11      33              3           1                           Front       
11      33              3           2                           Front       
11      33              3           3                           Front       
11      33              4           1                           Back        
11      33              4           2                           Back        
11      33              4           3                           Back

合并规则 #

1. 优先处理前缀为PP 的item，按item_order排序：
   1. 若该类item的aisle_side以End结尾，按出现次数匹配deddf中同Direction的行；
   2. 剩余PP 类item仅匹配deddf中Direction='Back'的行，按Aisle、TableNo、TableBit倒序；
2. grpdf中剩余item优先匹配deddf中Direction='Front'的行，按Aisle、TableNo、TableBit升序，无剩余则用Back方向；
3. 仅使用deddf中dedicated_table为空的行进行合并，有值的行保留原样。

尝试过的代码 #

idx = deddf[deddf['dedicated_table'] == ''].index
if (idx.nunique() == grpdf.shape[0]) and (idx.nunique()!=0) and (grpdf.shape[0]!=0):
    output = pd.concat([deddf, grpdf.set_axis(idx[:len(grpdf)])], axis=1)

解决方案 #

要实现上述规则，需要分步骤筛选、排序数据后再匹配合并，具体代码如下：

import pandas as pd

# 预处理：复制原始数据避免修改原表
grpdf_copy = grpdf.copy()
deddf_copy = deddf.copy()

# 1. 拆分deddf：专用行直接保留，可分配行用于匹配
dedicated_rows = deddf_copy[(deddf_copy['dedicated_table'].notna()) & (deddf_copy['dedicated_table'] != '')]
assignable_rows = deddf_copy[(deddf_copy['dedicated_table'].isna()) | (deddf_copy['dedicated_table'] == '')].copy()

# 2. 处理前缀为PP的item，按item_order排序
pp_items = grpdf_copy[grpdf_copy['item'].str.startswith('PP ')].sort_values('item_order').copy()

# 2.1 处理aisle_side以End结尾的PP item
pp_end_items = pp_items[pp_items['aisle_side'].str.endswith('End')]
matched_end_rows = []
for side, group in pp_end_items.groupby('aisle_side'):
    # 匹配对应Direction的可分配行
    direction = side
    available = assignable_rows[assignable_rows['Direction'] == direction].copy()
    take_rows = available.head(len(group))
    # 合并匹配行与item数据
    matched_end_rows.append(pd.concat([take_rows, group.reset_index(drop=True)], axis=1))
    # 移除已匹配的可分配行
    assignable_rows = assignable_rows.drop(take_rows.index)

# 2.2 处理剩余PP item：仅匹配Back方向，按指定字段倒序
remaining_pp = pp_items[~pp_items.index.isin(pp_end_items.index)]
back_assignable = assignable_rows[assignable_rows['Direction'] == 'Back'].sort_values(
    by=['Aisle', 'TableNo', 'TableBit'], ascending=False
)
matched_pp_back = pd.concat([back_assignable.head(len(remaining_pp)).reset_index(drop=True), 
                             remaining_pp.reset_index(drop=True)], axis=1)
assignable_rows = assignable_rows.drop(back_assignable.head(len(remaining_pp)).index)

# 3. 处理非PP类item：优先匹配Front方向升序行，不足补Back行
non_pp_items = grpdf_copy[~grpdf_copy.index.isin(pp_items.index)].copy()
front_assignable = assignable_rows[assignable_rows['Direction'] == 'Front'].sort_values(
    by=['Aisle', 'TableNo', 'TableBit'], ascending=True
)
# 先取Front行，不够用Back行补充
take_front = front_assignable.head(len(non_pp_items))
remaining_needed = len(non_pp_items) - len(take_front)
take_back = pd.DataFrame()
if remaining_needed > 0:
    back_remaining = assignable_rows[assignable_rows['Direction'] == 'Back'].sort_values(
        by=['Aisle', 'TableNo', 'TableBit'], ascending=True
    )
    take_back = back_remaining.head(remaining_needed)

# 合并非PP item的匹配结果
matched_non_pp = pd.concat([pd.concat([take_front, take_back]).reset_index(drop=True), 
                            non_pp_items.reset_index(drop=True)], axis=1)
assignable_rows = assignable_rows.drop(pd.concat([take_front, take_back]).index)

# 4. 合并所有结果：专用行 + 已匹配行 + 剩余可分配行（补充空值）
all_matched = pd.concat(matched_end_rows + [matched_pp_back, matched_non_pp], ignore_index=True)
# 给剩余可分配行补充grpdf的空列
remaining_assignable = assignable_rows.copy()
for col in grpdf_copy.columns:
    remaining_assignable[col] = None

# 生成最终结果，统一列顺序
resdf = pd.concat([dedicated_rows, all_matched, remaining_assignable], ignore_index=True)
resdf = resdf[deddf_copy.columns.tolist() + grpdf_copy.columns.tolist()]

代码说明 #

• 先拆分deddf为专用行和可分配行，专用行直接保留；
• 分批次处理PP类item：先匹配End结尾方向的行，再处理剩余PP item匹配倒序的Back行；
• 处理非PP item时优先使用升序的Front行，数量不足时用Back行补充；
• 最后合并所有部分，确保列顺序与原始表一致，剩余可分配行补充空值。

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