Search code examples
pandasmulti-index

How to re-order the multi-index columns using Pandas?

Table is shown here

code: 

dff = pd.DataFrame({'Country':['France']*4+['China']*4,
                   'Progress':['Develop','Middle','Operate','Start']*2,
                   'NumTrans':np.random.randint(100,900,8),
                   'TransValue':np.random.randint(10000,9999999,8)})

dff = dff.set_index(['Country','Progress']).T

Data and code are shown above.

I want to know is there any way to re-order the "Progress" as start-develop-middle-operate using Python.

I tried using map function and set each stage with a number, but cannot extract "Progress" from multi-index

Thanks!

Solution

  • reindex

    You can specify a level to reindex on

    cats = ['Start', 'Develop', 'Middle', 'Operate']
dff.reindex(cats, axis=1, level=1)

Country      France                              China                         
Progress      Start  Develop   Middle  Operate   Start Develop  Middle  Operate
NumTrans        772      832      494      793     750     722     818      684
TransValue  7363187  2578816  9764430  4863178  159777  840700  978816  9674337

    set_levels with CategoricalIndex

    You can define the order of the second level and then sort.

    lvl1 = dff.columns.levels[1]
cats = ['Start', 'Develop', 'Middle', 'Operate']
cati = pd.CategoricalIndex(
  lvl1,
  categories=cats,
  ordered=True
)
dff.columns.set_levels(
  cati, level=1, inplace=True  
)

dff.sort_index(1)

Country      China                            France                           
Progress     Start Develop  Middle  Operate    Start  Develop   Middle  Operate
NumTrans       750     722     818      684      772      832      494      793
TransValue  159777  840700  978816  9674337  7363187  2578816  9764430  4863178