Python Pandas：指定分组下重排DataFrame行的问题求助

需求说明 #

• 针对DataFrame中main_group为['CITRUS','TOM/CAP']的Category-main_group组合，按sub_group与Type的组合交替重排行数据
• 其他分组（如MANGOES、ONION）保持原顺序

报错情况 #

尝试代码：

to_sort = ['CITRUS', 'TOM/CAP']
out = (df.assign(sub_order=df.groupby(['Type', 'sub_group'], group_keys=False, sort=False).cumcount()).apply(lambda g: g.sort_values(by=['Category', 'main_group', 'Type', 'sub_order']) if g.name[1] in to_sort else g))

报错信息：NameError: name 'to_sort' is not defined

输入DataFrame #

Category    main_group      sub_group       Item            Type        item_order      row
Fruit       CITRUS          KIWI FRUIT      GreenKiwi       Loose           1           row_1
Fruit       CITRUS          KIWI FRUIT      GoldKiwi        Loose           2           row_1
Fruit       CITRUS          KIWI FRUIT      OtherKiwi       Loose           3           row_1
Fruit       CITRUS          KIWI FRUIT      PP GreenKiwi    PP              4           row_1
Fruit       CITRUS          KIWI FRUIT      PP GoldKiwi     PP              5           row_1
Fruit       CITRUS          KIWI FRUIT      PP OtherKiwi    PP              6           row_1
Fruit       CITRUS          MANDARIN        MandarinAfourer Loose           7           row_1
Fruit       CITRUS          MANDARIN        MandarinTangold Loose           8           row_1
Fruit       CITRUS          MANDARIN        PP Mandarin     PP              9           row_1
Fruit       MANGOES         NECTARINES      NectaYellow     Loose           10          row_1
Fruit       MANGOES         NECTARINES      NectaWhite      Loose           11          row_1
Fruit       MANGOES         PEACHES         PeachYellow     Loose           12          row_1
Fruit       MANGOES         PEACHES         PeachWhite      Loose           13          row_1
Vegg        TOM/CAP         TOMATO          Tomato Truss    Loose           14          row_2
Vegg        TOM/CAP         TOMATO          Tomato Roma     Loose           15          row_2
Vegg        TOM/CAP         CUCUMBER        Capsicum Mini   Loose           16          row_2
Vegg        TOM/CAP         CUCUMBER        Capsicum Red    Loose           17          row_2
Vegg        ONION           ONION           Onion Red       Loose           18          row_2
Vegg        ONION           ONION           PP Onion        PP              19          row_2

预期输出 #

Category    main_group      sub_group       Item            Type        item_order      old_item_order      row
Fruit       CITRUS          KIWI FRUIT      GreenKiwi       Loose           1               1               row_1
Fruit       CITRUS          MANDARIN        MandarinAfourer Loose           2               7               row_1
Fruit       CITRUS          KIWI FRUIT      GoldKiwi        Loose           3               2               row_1
Fruit       CITRUS          MANDARIN        MandarinTangold Loose           4               8               row_1
Fruit       CITRUS          KIWI FRUIT      OtherKiwi       Loose           5               3               row_1
Fruit       CITRUS          KIWI FRUIT      PP GreenKiwi    PP              6               4               row_1
Fruit       CITRUS          MANDARIN        PP Mandarin     PP              7               9               row_1
Fruit       CITRUS          KIWI FRUIT      PP GoldKiwi     PP              8               5               row_1
Fruit       CITRUS          KIWI FRUIT      PP OtherKiwi    PP              9               6               row_1
Fruit       MANGOES         NECTARINES      NectaYellow     Loose           10              10              row_1
Fruit       MANGOES         NECTARINES      NectaWhite      Loose           11              11              row_1
Fruit       MANGOES         PEACHES         PeachYellow     Loose           12              12              row_1
Fruit       MANGOES         PEACHES         PeachWhite      Loose           13              13              row_1
Vegg        TOM/CAP         TOMATO          Tomato Truss    Loose           14              14              row_2
Vegg        TOM/CAP         CUCUMBER        Capsicum Mini   Loose           15              16              row_2
Vegg        TOM/CAP         TOMATO          Tomato Roma     Loose           16              15              row_2
Vegg        TOM/CAP         CUCUMBER        Capsicum Red    Loose           17              17              row_2
Vegg        ONION           ONION           Onion Red       Loose           18              18              row_2
Vegg        ONION           ONION           PP Onion        PP              19              19              row_2

解决方案 #

报错原因 #

原代码中apply的lambda函数无法访问外部to_sort变量，且分组逻辑错误——未按Category-main_group维度分组处理。

正确代码 #

import pandas as pd

# 保留原序号用于对比
df['old_item_order'] = df['item_order']

to_sort = ['CITRUS', 'TOM/CAP']

def process_group(group):
    # 判断当前分组是否需要排序
    if group['main_group'].iloc[0] in to_sort:
        # 给每个Type-sub_group组合内的行分配序号
        group['sub_order'] = group.groupby(['Type', 'sub_group'], sort=False).cumcount()
        # 按Type、序号排序，实现sub_group交替排列
        sorted_group = group.sort_values(by=['Type', 'sub_order', 'sub_group'])
        # 重新生成item_order
        sorted_group['item_order'] = range(1, len(sorted_group)+1)
        return sorted_group.drop('sub_order', axis=1)
    else:
        # 非目标分组保持原顺序
        return group

# 按Category和main_group分组处理
result = df.groupby(['Category', 'main_group'], group_keys=False).apply(process_group)

# 重置索引并调整列顺序匹配预期输出
result = result.reset_index(drop=True)
result = result[['Category', 'main_group', 'sub_group', 'Item', 'Type', 'item_order', 'old_item_order', 'row']]

代码说明 #

1. 先复制原item_order为old_item_order，保留原始顺序标记
2. 按Category-main_group分组，针对每个分组判断是否属于目标排序组
3. 对目标分组，通过Type-sub_group分组生成序号，再按序号排序实现交替排列
4. 重新生成item_order并调整列顺序，最终输出与预期一致的结果

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