在数据透视表中重新排列顺序

alpenmilch411 发表于 Dev

alpenmilch411

我在熊猫中有下面的数据透视表：

   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”以外的其他列

最大容量

好吧，你可以做这样的事情：

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