透视表中的重新排列顺序

Question

我有以下熊猫数据透视表：

   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

---

更新：

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