如何在Pandas透视表中保持原DataFrame排序顺序以绘制热力图

你的问题出在groupby默认会对分组键进行字母排序，覆盖了之前按Skill 1 Type排好的顺序。以下是几种可行的解决办法：

方法一：将Skill 1转为分类类型并指定顺序 #

先提取排序后Skill 1的唯一值顺序，将其设为分类类型，让groupby遵循分类的自定义顺序而非字母序：

import pandas as pd

simple_df = pd.DataFrame({'Skill 1': ['Python','Python','Python','Communication','Communication','Communication','Data Governance','Data Governance','Data Governance'], 
                          'Skill 2': ['Python','Communication','Data Governance','Python','Communication','Data Governance','Python','Communication','Data Governance'],
                          'Score':[1,0.9,0.4,0.9,1,0.4,0.4,0.4,1],
                          'Skill 1 Type':['Programming','Programming','Programming','Written','Written','Written','Cyber','Cyber','Cyber']})

# 按Skill 1 Type排序
simple_df = simple_df.sort_values(by=['Skill 1 Type'], ascending=True, na_position='first')

# 获取排序后Skill 1的唯一值顺序
skill1_order = simple_df['Skill 1'].unique()
# 将Skill 1转为分类类型，指定自定义顺序
simple_df['Skill 1'] = pd.Categorical(simple_df['Skill 1'], categories=skill1_order, ordered=True)

# 此时groupby会遵循分类顺序，即使sort=True也不会改变
test = simple_df.groupby(['Skill 1','Skill 2'], sort=True)['Score'].sum().unstack('Skill 2')

方法二：groupby时关闭排序，再重新索引 #

通过sort=False关闭groupby的默认排序，之后手动将结果行索引重置为目标顺序：

import pandas as pd

simple_df = pd.DataFrame({'Skill 1': ['Python','Python','Python','Communication','Communication','Communication','Data Governance','Data Governance','Data Governance'], 
                          'Skill 2': ['Python','Communication','Data Governance','Python','Communication','Data Governance','Python','Communication','Data Governance'],
                          'Score':[1,0.9,0.4,0.9,1,0.4,0.4,0.4,1],
                          'Skill 1 Type':['Programming','Programming','Programming','Written','Written','Written','Cyber','Cyber','Cyber']})

# 按Skill 1 Type排序
simple_df = simple_df.sort_values(by=['Skill 1 Type'], ascending=True, na_position='first')

# 获取目标顺序
skill1_order = simple_df['Skill 1'].unique()

# groupby时关闭默认排序
test = simple_df.groupby(['Skill 1','Skill 2'], sort=False)['Score'].sum().unstack('Skill 2')
# 重新索引行，匹配目标顺序
test = test.reindex(skill1_order)

方法三：使用pivot_table替代groupby+unstack #

pivot_table在设置sort=False时，会保留行标签在原数据中首次出现的顺序，更直接实现需求：

import pandas as pd

simple_df = pd.DataFrame({'Skill 1': ['Python','Python','Python','Communication','Communication','Communication','Data Governance','Data Governance','Data Governance'], 
                          'Skill 2': ['Python','Communication','Data Governance','Python','Communication','Data Governance','Python','Communication','Data Governance'],
                          'Score':[1,0.9,0.4,0.9,1,0.4,0.4,0.4,1],
                          'Skill 1 Type':['Programming','Programming','Programming','Written','Written','Written','Cyber','Cyber','Cyber']})

# 按Skill 1 Type排序
simple_df = simple_df.sort_values(by=['Skill 1 Type'], ascending=True, na_position='first')

# 使用pivot_table，sort=False保留原顺序
test = simple_df.pivot_table(index='Skill 1', columns='Skill 2', values='Score', aggfunc='sum', sort=False)

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