Pandas分类类型无法正确排序MultiIndex的问题解决

导出的CSV数据 #

Year,Decision,Residency,Class,Count
2019,Applied,Resident,Freshmen,1143
2019,Applied,Resident,Transfer,404
2019,Applied," ",Grad/Postbacc,418
2019,Applied,Non-Resident,Freshmen,1371
2019,Applied,Non-Resident,Transfer,371
2019,Admitted,Resident,Freshmen,918
2019,Admitted,Resident,Transfer,358
2019,Admitted," ",Grad/Postbacc,311
2019,Admitted,Non-Resident,Freshmen,1048
2019,Admitted,Non-Resident,Transfer,313
2020,Applied,Resident,Freshmen,1094
2020,Applied,Resident,Transfer,406
2020,Applied," ",Grad/Postbacc,374
2020,Applied,Non-Resident,Freshmen,1223
2020,Applied,Non-Resident,Transfer,356
2020,Admitted,Resident,Freshmen,1003
2020,Admitted,Resident,Transfer,354
2020,Admitted," ",Grad/Postbacc,282
2020,Admitted,Non-Resident,Freshmen,1090
2020,Admitted,Non-Resident,Transfer,288

编写的转换代码 #

import pandas as pd
import numpy as np

data = pd.read_csv("Data.csv")
# 分类设置
data["Class"]     = pd.Categorical(data["Class"],["Freshmen","Transfer","Grad/Postbacc","Grand"],ordered=True)
data["Decision"]  = pd.Categorical(data["Decision"],["Applied","Admitted"],ordered=True)
data["Residency"] = pd.Categorical(data["Residency"],["Resident","Non-Resident"],ordered=True)

# 计算各Class的小计
tmp = data.groupby(["Year","Class","Decision"],sort=False).sum("Count")
tmp["Residency"] = "Total"
tmp.reset_index(inplace=True)
tmp = pd.concat([data,tmp],ignore_index=True)

# 计算总计
tmp2 = data.groupby(["Year","Decision"],sort=False).sum("Count")
tmp2["Class"]     = "Grand"
tmp2["Residency"] = "Total"
tmp2.reset_index(inplace=True)
tmp = pd.concat([tmp,tmp2],ignore_index=True)

# 生成交叉表
tmp = pd.crosstab(index=[tmp["Year"],tmp["Class"],tmp["Residency"]],
                  columns=[tmp["Decision"]],
                  values=tmp["Count"],
                  aggfunc="sum")
tmp = tmp.loc[~(tmp==0).all(axis=1)]
tmp["%"] = np.round(100*tmp["Admitted"]/tmp["Applied"],1)
tmp = tmp.stack().unstack(["Year","Decision"])
print(tmp)

实际输出结果 #

Year                          2019                   2020               
Decision                   Applied Admitted     % Applied Admitted     %
Class         Residency                                                 
Freshmen      Non-Resident  1371.0   1048.0  76.4  1223.0   1090.0  89.1
              Resident      1143.0    918.0  80.3  1094.0   1003.0  91.7
              Total         2514.0   1966.0  78.2  2317.0   2093.0  90.3
Grad/Postbacc Total          418.0    311.0  74.4   374.0    282.0  75.4
Grand         Total         3707.0   2948.0  79.5  3453.0   3017.0  87.4
Transfer      Non-Resident   371.0    313.0  84.4   356.0    288.0  80.9
              Resident       404.0    358.0  88.6   406.0    354.0  87.2
              Total          775.0    671.0  86.6   762.0    642.0  84.3

期望输出结果 #

Year                          2019                   2020               
Decision                   Applied Admitted     % Applied Admitted     %
Class         Residency                                                 
Freshmen      Resident      1143.0    918.0  80.3  1094.0   1003.0  91.7
              Non-Resident  1371.0   1048.0  76.4  1223.0   1090.0  89.1
              Total         2514.0   1966.0  78.2  2317.0   2093.0  90.3
Transfer      Resident       404.0    358.0  88.6   406.0    354.0  87.2
              Non-Resident   371.0    313.0  84.4   356.0    288.0  80.9
              Total          775.0    671.0  86.6   762.0    642.0  84.3
Grad/Postbacc Total          418.0    311.0  74.4   374.0    282.0  75.4
Grand         Total         3707.0   2948.0  79.5  3453.0   3017.0  87.4

原因分析 #

1. 新增值不在分类范畴：后续添加的Total（Residency列）、Grand（Class列）未包含在初始分类定义中，这些值被归为未定义分类项，破坏了有序分类的排序规则。
2. crosstab默认排序逻辑：pd.crosstab默认会对索引按字典序排序，即使原数据是有序分类，当存在分类外的值时，原分类顺序会被覆盖，导致输出顺序混乱。

修复方案 #

步骤1：更新分类定义，包含汇总值 #

在初始设置分类时，把后续要用到的Total和Grand加入对应列的分类列表，确保顺序符合预期：

# 替换原始数据中的空格Residency为Total
data["Residency"] = data["Residency"].str.strip().replace("", "Total")
# 分类设置，加入汇总值
data["Class"]     = pd.Categorical(data["Class"],["Freshmen","Transfer","Grad/Postbacc","Grand"],ordered=True)
data["Decision"]  = pd.Categorical(data["Decision"],["Applied","Admitted"],ordered=True)
# 加入Total到Residency分类，保持Resident -> Non-Resident -> Total的顺序
data["Residency"] = pd.Categorical(data["Residency"],["Resident","Non-Resident","Total"],ordered=True)

步骤2：禁用crosstab的默认排序 #

在生成交叉表时添加sort=False参数，保留原分类的顺序：

# 生成交叉表时禁用排序
tmp = pd.crosstab(index=[tmp["Year"],tmp["Class"],tmp["Residency"]],
                  columns=[tmp["Decision"]],
                  values=tmp["Count"],
                  aggfunc="sum",
                  sort=False)

步骤3：按分类顺序重新排序索引 #

最后对结果的多索引按Class和Residency的分类顺序重新排序，确保完全匹配期望：

tmp = tmp.sort_index(level=["Class","Residency"])

完整修复后的代码 #

import pandas as pd
import numpy as np

data = pd.read_csv("Data.csv")
# 替换原始数据中的空格Residency为Total
data["Residency"] = data["Residency"].str.strip().replace("", "Total")
# 分类设置，加入汇总值
data["Class"]     = pd.Categorical(data["Class"],["Freshmen","Transfer","Grad/Postbacc","Grand"],ordered=True)
data["Decision"]  = pd.Categorical(data["Decision"],["Applied","Admitted"],ordered=True)
data["Residency"] = pd.Categorical(data["Residency"],["Resident","Non-Resident","Total"],ordered=True)

# 计算各Class的小计
tmp = data.groupby(["Year","Class","Decision"],sort=False).sum("Count")
tmp["Residency"] = "Total"
tmp.reset_index(inplace=True)
tmp = pd.concat([data,tmp],ignore_index=True)

# 计算总计
tmp2 = data.groupby(["Year","Decision"],sort=False).sum("Count")
tmp2["Class"]     = "Grand"
tmp2["Residency"] = "Total"
tmp2.reset_index(inplace=True)
tmp = pd.concat([tmp,tmp2],ignore_index=True)

# 生成交叉表，禁用排序
tmp = pd.crosstab(index=[tmp["Year"],tmp["Class"],tmp["Residency"]],
                  columns=[tmp["Decision"]],
                  values=tmp["Count"],
                  aggfunc="sum",
                  sort=False)
tmp = tmp.loc[~(tmp==0).all(axis=1)]
tmp["%"] = np.round(100*tmp["Admitted"]/tmp["Applied"],1)
tmp = tmp.stack().unstack(["Year","Decision"])
# 按分类顺序重新排序索引
tmp = tmp.sort_index(level=["Class","Residency"])
print(tmp)

最终效果 #

运行修复后的代码，输出会完全匹配期望结果，分类顺序保持Freshmen -> Transfer -> Grad/Postbacc -> Grand，每个Class下的Residency顺序为Resident -> Non-Resident -> Total。

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