python - 重新排列数据透视表中的顺序

Question

我在 pandas 中有以下数据透视表:

   Division             BU/CF        Allocation Key CurrentHC 

0  Central Functions     A            NEF           3 
1                        B            NEF           2 
2                        C            EXP           1 
3                                     NEF           4 
4                        D            NEF           3 
5  Xerxes                E            NLE           4 
6                        F            NLE           1 
7                        G            NLE           1 
8                        H            NLE           5 

Python 显然按字母顺序对 除法和 BU/CF 进行排序。如何将我自己的订单应用到数据透视表。

期望的输出:

   Division              BU/CF       Allocation Key CurrentHC 
0  Central Functions     D            NEF           3 
1                        B            NEF           2 
2                        C            EXP           1 
3                                     NEF           4 
4                        A            NEF           3 
5  Xerxes                E            NLE           4 
6                        H            NLE           5 
7                        G            NLE           1 
8                        F            NLE           1 

我用来创建数据透视表的代码:

#Create full report pivot 
report_pivot = pd.pivot_table(full_report, index=["Division","BU/CF", "Allocation Key"], 
                       values=["Previous HC", "New Hire", "Resigned", "In", "Out", "Current HC", "Delta"], 

                       fill_value=0) 

我设法通过这样做重新排列列:

# Reorderr columns 
cols = [ "Previous HC", "New Hire", "Resigned", "In", "Out","Delta", "Current HC"] 
report_pivot = report_pivot[cols] 

索引有类似的方法吗？具体来说是“BU/CF”

*为了简化上表，我排除了除当前 HC 之外的其他列

Answer 1

你可以这样做:

In [62]: sort_map = {
   ....:  'E': 10,
   ....:  'H': 20,
   ....:  'G': 30,
   ....:  'F': 40,
   ....: }

In [63]: df.loc[df['Division'] == 'Xerxes', 'BU/CF'].map(sort_map)
Out[63]:
5    10
6    40
7    30
8    20
Name: BU/CF, dtype: int64

In [64]: idx = df.loc[df['Division'] == 'Xerxes', 'BU/CF'].map(sort_map).sort_values().index

In [65]: idx
Out[65]: Int64Index([5, 8, 7, 6], dtype='int64')

In [66]: df[df['Division'] == 'Xerxes'].reindex(idx)
Out[66]:
  Division BU/CF AllocationKey  CurrentHC
5   Xerxes     E           NLE          4
8   Xerxes     H           NLE          5
7   Xerxes     G           NLE          1
6   Xerxes     F           NLE          1

<小时/>

更新:

从 Pandas 0.20.1 the .ix indexer is deprecated, in favor of the more strict .iloc and .loc indexers 开始。