如何按条件排序DataFrame：按model分组，slot升序且Total行置每组末尾

原始DataFrame #

Env location     lob grid  row     server    model  make slot ports connected disabled
0    Prod      USA  Market  AB3  bc2  Server123  Hitachi  Stor    1   3.0       1.0      5.0
1    Prod      USA  Market  AB3  bc2  Server123  Hitachi  Stor    2   2.0       3.0      3.0
2    Prod      USA  Market  AB3  bc2  Server123  Hitachi  Stor    3   0.0       0.0      2.0
3    Prod      USA  Market  AB3  bc2  Server123  Hitachi  Stor    4   8.0       7.0      1.0
4   Total      USA  Market  AB3  bc2  Server123  Hitachi  Stor    4   13.0      11.0     11.0
                                                                                           
6     Dev     EMEA    Ins.  AB6  bc4  Serverabc      IBM   Mfa    10   6.0       5.0      0.0
7     Dev     EMEA    Ins.  AB6  bc4  Serverabc      IBM   Mfa    1   8.0       4.0      0.0
8     Dev     EMEA    Ins.  AB6  bc4  Serverabc      IBM   Mfa    2  12.0       4.0      0.0
9     Dev     EMEA    Ins.  AB6  bc4  Serverabc      IBM   Mfa    3  10.0       2.0      0.0
8     Dev     EMEA    Ins.  AB6  bc4  Serverabc      IBM   Mfa    8  12.0       4.0      0.0
9     Dev     EMEA    Ins.  AB6  bc4  Serverabc      IBM   Mfa    9  10.0       2.0      0.0
10  Total     EMEA    Ins.  AB6  bc4  Serverabc      IBM   Mfa    7   36.0      15.0      0.0

需求说明 #

• 按model字段分组
• 每组内非Total行按slot列升序排列（如IBM组的slot10需排在非Total行最后）
• 每组的Total行必须放在该组末尾

用户尝试的代码（无法满足需求） #

df.sort(['model', 'slot'], ascending=[True, False])

正确解决方案 #

要实现需求，需要新增辅助列标记Total行，再结合多字段排序：

1. 添加辅助列is_total，Total行标记为1，其他行标记为0：

df['is_total'] = df['Env'].apply(lambda x: 1 if x == 'Total' else 0)

2. 按model、is_total、slot三个字段排序：

• model升序，保证同组数据聚合在一起
• is_total升序，让非Total行（标记为0）排在Total行（标记为1）之前
• slot升序，实现非Total行内按slot从小到大排列

df_sorted = df.sort_values(by=['model', 'is_total', 'slot'], ascending=[True, True, True])

3. （可选）若不需要保留辅助列，可删除：

df_sorted = df_sorted.drop('is_total', axis=1)

执行后得到的结果完全符合需求：

Env location     lob grid  row     server    model  make slot ports connected disabled
    0    Prod      USA  Market  AB3  bc2  Server123  Hitachi  Stor    1   3.0       1.0      5.0
    1    Prod      USA  Market  AB3  bc2  Server123  Hitachi  Stor    2   2.0       3.0      3.0
    2    Prod      USA  Market  AB3  bc2  Server123  Hitachi  Stor    3   0.0       0.0      2.0
    3    Prod      USA  Market  AB3  bc2  Server123  Hitachi  Stor    4   8.0       7.0      1.0
    4   Total      USA  Market  AB3  bc2  Server123  Hitachi  Stor    4   13.0      11.0     11.0
                                                                                               
    7     Dev     EMEA    Ins.  AB6  bc4  Serverabc      IBM   Mfa    1   8.0       4.0      0.0
    8     Dev     EMEA    Ins.  AB6  bc4  Serverabc      IBM   Mfa    2  12.0       4.0      0.0
    9     Dev     EMEA    Ins.  AB6  bc4  Serverabc      IBM   Mfa    3  10.0       2.0      0.0
    8     Dev     EMEA    Ins.  AB6  bc4  Serverabc      IBM   Mfa    8  12.0       4.0      0.0
    9     Dev     EMEA    Ins.  AB6  bc4  Serverabc      IBM   Mfa    9  10.0       2.0      0.0
         Dev     EMEA    Ins.  AB6  bc4  Serverabc      IBM   Mfa    10   6.0       5.0      0.0
    10  Total     EMEA    Ins.  AB6  bc4  Serverabc      IBM   Mfa    7   36.0      15.0      0.0

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