如何在Pandas中对DataFrame进行多列自定义排序？

我有如下结构的Pandas DataFrame：

+-----------+-----------------+---------+
| JOB-NAME  |  Status         | SLA     |
+-----------+-----------------+---------+
| job_1     | YET_TO_START    | ---     |
| job_3     | COMPLETED       | MET     |
| job_4     | RUNNING         | MET     |
| job_2     | YET_TO_START    | LATE    |
| job_6     | RUNNING         | LATE    |
| job_5     | FAILED          | LATE    |
| job_7     | YET_TO_START    | ---     |
| job_8     | COMPLETED       | NOT_MET |
+-----------+-----------------+---------+

需要按Status和SLA列进行自定义排序，规则如下：

• Status优先级：FAILED > YET_TO_START > RUNNING > COMPLETED
• SLA优先级：LATE > --- > NOT_MET > MET

期望得到的排序结果：

+-----------+-----------------+---------+
| JOB-NAME  |  Status         | SLA     |
+-----------+-----------------+---------+
| job_5     | FAILED          | LATE    |
| job_2     | YET_TO_START    | LATE    |
| job_1     | YET_TO_START    | ---     |
| job_7     | YET_TO_START    | ---     |
| job_6     | RUNNING         | LATE    |
| job_4     | RUNNING         | MET     |
| job_8     | COMPLETED       | NOT_MET |
| job_3     | COMPLETED       | MET     |
+-----------+-----------------+---------+

我已经实现了单列（Status）的自定义排序，代码如下：

sort_order_dict = {"FAILED":0, "YET_TO_START":1, "RUNNING":2, "COMPLETED":3}
joined_df = joined_df.sort_values(by=['status'], key=lambda x: x.map(sort_order_dict))

但无法实现多列的自定义排序，现有的方案仅支持单列，寻求多列自定义排序的解决方法。

解决方案 #

你可以为每一列分别定义排序优先级字典，然后在sort_values的key参数中生成对应每一列的排序键值列表，同时指定排序的列顺序即可实现多列自定义排序。

实现代码 #

# 定义各列的自定义排序优先级字典
status_sort_order = {"FAILED": 0, "YET_TO_START": 1, "RUNNING": 2, "COMPLETED": 3}
sla_sort_order = {"LATE": 0, "---": 1, "NOT_MET": 2, "MET": 3}

# 执行多列自定义排序
joined_df = joined_df.sort_values(
    by=['Status', 'SLA'],
    key=lambda df: [
        df['Status'].map(status_sort_order),
        df['SLA'].map(sla_sort_order)
    ]
)

代码说明 #

1. 为Status和SLA列分别创建排序字典，字典值越小代表该类别优先级越高；
2. sort_values的by参数指定排序的层级顺序：先按Status排序，同Status的行再按SLA排序；
3. key参数通过列表返回每一列对应的排序键值，Pandas会按照这个列表的顺序依次进行排序；
4. 执行后即可得到符合预期的排序结果。

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