How to reindex a pandas pivot table

pandas, pivot-table

Solution

Reindex only the relevant level (`level=0`):

import pandas as pd

idx = pd.MultiIndex(levels=[['April', 'February', 'March'], ['All', 'DHW', 'SH']],
                    labels=[[0, 0, 0, 1, 1, 2, 2, 2], [0, 1, 2, 0, 2, 0, 1, 2]],
                    names=['Time', 'type'])
df = pd.DataFrame([[38.190119, 65.789103, 27.598984],
                   [19.676627, 60.889196, 41.212569],
                   [47.342757, 61.335566, 13.992809],
                   [43.896487, 57.982944, 14.086457],
                   [40.864670, 50.567133, 9.702463],
                   [42.083836, 69.139818, 27.055982],
                   [18.908873, 62.936898, 44.028024],
                   [52.249342, 70.013904, 17.764563]],
                  columns=['AWRT', 'AWFT', 'AWDT'],
                  index=idx)

print(df)

#                     AWRT       AWFT       AWDT
# Time     type                                 
# April    All   38.190119  65.789103  27.598984
#          DHW   19.676627  60.889196  41.212569
#          SH    47.342757  61.335566  13.992809
# February All   43.896487  57.982944  14.086457
#          SH    40.864670  50.567133   9.702463
# March    All   42.083836  69.139818  27.055982
#          DHW   18.908873  62.936898  44.028024
#          SH    52.249342  70.013904  17.764563

print(df.reindex(['February', 'March', 'April'], level=0))

#                     AWRT       AWFT       AWDT
# Time     type                                 
# February All   43.896487  57.982944  14.086457
#          SH    40.864670  50.567133   9.702463
# March    All   42.083836  69.139818  27.055982
#          DHW   18.908873  62.936898  44.028024
#          SH    52.249342  70.013904  17.764563
# April    All   38.190119  65.789103  27.598984
#          DHW   19.676627  60.889196  41.212569
#          SH    47.342757  61.335566  13.992809

Problem

I created a pivot table which grouped by according to one of the columns and the month of the time index. This is the pivot table ``` AWRT AWFT AWDT Time type April All 38.190119 65.789103 27.598984 DHW 19.676627 60.889196 41.212569 SH 47.342757 61.335566 13.992809 February All 43.896487 57.982944 14.086457 SH 40.864670 50.567133 9.702463 March All 42.083836 69.139818 27.055982 DHW 18.908873 62.936898 44.028024 SH 52.249342 70.013904 17.764563 ``` Now I would like february march and april to appear in chronological, not alphabetical order trying to reindex this way: ``` new_index=[['February', 'March', 'April'], ['All', 'DHW', 'SH']] df1=df1.reindex(new_index) ``` I obtain the following, which is not a pivot table anymore: ``` AWRT AWFT AWDT Time type February All 43.896487 57.982944 14.086457 March DHW 18.908873 62.936898 44.028024 April SH 47.342757 61.335566 13.992809 ``` I also tried to directly access the labels of the pivot table index but I am told these are immutable. thanks in advance for your help

Original source