Pandas多列排序问题：内置字符串排序第一列+自定义规则排序第二列

嘿，我看到你在Pandas多列排序上卡壳了——想先让A列按默认字符串排序，再让B列按「东北、东南、西北、西南、Upper」的自定义顺序排列，对吧？我来帮你分析下问题出在哪，再给你靠谱的解决方案～

问题出在哪？ #

你之前的代码里，sort_values的key参数是作用于所有by指定的列的（也就是A列和sort_value列），但A列的内容根本不在你的sort_dicts字典里，这会导致A列的映射结果全是NaN，自然排序就出错啦！

解决方案 #

这里给你两种可行的方法，你可以根据自己的习惯选：

方法一：用辅助列+分类数据类型（直观好维护）

这种方式把排序逻辑拆解开，新手也能一眼看懂：

import pandas as pd

data={'A':['Ankang Shaanxi','Baoding Anguo','Baoding Anguo','Changsha Hunan','Ankang Shaanxi',
'Baoding Anguo','Baoding Anguo','Ankang Shaanxi','Luoyang Henan','Baoding Anguo',
'Changsha Hunan','Ankang Shaanxi','Ankang Shaanxi'],
'B':['Ankang Southeast','Baoding Anguo Northeast','Baoding Anguo Southeast','Changsha Hunan Bright','Ankang Northeast','Baoding Anguo Southwest','Baoding Anguo Upper','Ankang Southwest','Luoyang Henan Upper','Baoding Anguo Northwest','Changsha Hunan Lower','Ankang Southwest Upper','Ankang Northwest']}

df = pd.DataFrame(data)

# 1. 提取B列用于排序的关键词（取最后一个词）
df['sort_key'] = df['B'].apply(lambda x: x.split()[-1])

# 2. 定义你想要的自定义排序顺序
custom_order = ['Northeast', 'Southeast', 'Northwest', 'Southwest', 'Upper']

# 3. 把sort_key转换成分类类型，指定顺序——不在列表里的内容会自动排在最后
df['sort_key'] = pd.Categorical(df['sort_key'], categories=custom_order, ordered=True)

# 4. 执行排序：先按A列默认字符串排序，再按sort_key的自定义顺序排
sorted_df = df.sort_values(by=['A', 'sort_key'], ignore_index=True)

# 可选：删除辅助列，得到干净的结果
sorted_df = sorted_df.drop('sort_key', axis=1)

print(sorted_df)

方法二：直接在sort_values中对B列应用自定义规则（无需辅助列）

如果你不想多一个辅助列，可以用这种更紧凑的写法，只对B列应用自定义排序逻辑：

import pandas as pd

data={'A':['Ankang Shaanxi','Baoding Anguo','Baoding Anguo','Changsha Hunan','Ankang Shaanxi',
'Baoding Anguo','Baoding Anguo','Ankang Shaanxi','Luoyang Henan','Baoding Anguo',
'Changsha Hunan','Ankang Shaanxi','Ankang Shaanxi'],
'B':['Ankang Southeast','Baoding Anguo Northeast','Baoding Anguo Southeast','Changsha Hunan Bright','Ankang Northeast','Baoding Anguo Southwest','Baoding Anguo Upper','Ankang Southwest','Luoyang Henan Upper','Baoding Anguo Northwest','Changsha Hunan Lower','Ankang Southwest Upper','Ankang Northwest']}

df = pd.DataFrame(data)

# 定义自定义排序的映射字典，不在字典里的内容给个大值，让它们排在后面
custom_order_map = {'Northeast':0, 'Southeast':1, 'Northwest':2, 'Southwest':3, 'Upper':4}

# 排序：A列用默认字符串排序，B列提取最后一个词后用字典映射排序
sorted_df = df.sort_values(
    by=['A', 'B'],
    key=lambda col: col.map(lambda x: custom_order_map.get(x.split()[-1], 100)) if col.name == 'B' else col,
    ignore_index=True
)

print(sorted_df)

最终输出结果 #

两种方法都会得到你想要的排序效果：

A                       B
0   Ankang Shaanxi       Ankang Northeast
1   Ankang Shaanxi       Ankang Southeast
2   Ankang Shaanxi       Ankang Northwest
3   Ankang Shaanxi        Ankang Southwest
4   Ankang Shaanxi  Ankang Southwest Upper
5    Baoding Anguo  Baoding Anguo Northeast
6    Baoding Anguo  Baoding Anguo Southeast
7    Baoding Anguo  Baoding Anguo Northwest
8    Baoding Anguo  Baoding Anguo Southwest
9    Baoding Anguo      Baoding Anguo Upper
10  Changsha Hunan    Changsha Hunan Bright
11  Changsha Hunan     Changsha Hunan Lower
12   Luoyang Henan      Luoyang Henan Upper

备注：内容来源于stack exchange，提问作者张宇杰