9

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!

tobsecret
  • 2,442
  • 15
  • 26
Boomshakalaka
  • 521
  • 1
  • 6
  • 19
  • Answered here in the best way: https://stackoverflow.com/questions/40570679/reorder-multi-indexed-dataframe-columns-based-on-reference?rq=1 – bonney Feb 06 '20 at 03:53

1 Answers1

20

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
piRSquared
  • 285,575
  • 57
  • 475
  • 624