pythonpython-3.xpandasdataframeindexing

How can I sort order of index based on my preference in multi-index pandas dataframes


I have a pandas dataframe df. It has multi-index with Gx.Region and Scenario_Model. The Scenario_Model index is ordered in alphabetical order des, pes, tes. When I plot it, it comes in the same order. However, I want to reorder it as pes, tes and des, and plot it accordingly. Is it possible to achieve it in Python pandas dataframe?

    dict = {('Value', 2023, 'BatteryStorage'): {('Central Africa', 'des'): 0.0,
  ('Central Africa', 'pes'): 0.0,
  ('Central Africa', 'tes'): 0.0,
  ('Eastern Africa', 'des'): 0.0,
  ('Eastern Africa', 'pes'): 0.0,
  ('Eastern Africa', 'tes'): 0.0,
  ('North Africa', 'des'): 0.0,
  ('North Africa', 'pes'): 0.0,
  ('North Africa', 'tes'): 0.0,
  ('Southern Africa', 'des'): 504.0,
  ('Southern Africa', 'pes'): 100.0,
  ('Southern Africa', 'tes'): 360.0,
  ('West Africa', 'des'): 0.0,
  ('West Africa', 'pes'): 0.0,
  ('West Africa', 'tes'): 0.0},
 ('Value', 2023, 'Biomass PP'): {('Central Africa', 'des'): 0.0,
  ('Central Africa', 'pes'): 0.0,
  ('Central Africa', 'tes'): 0.0,
  ('Eastern Africa', 'des'): 40,
  ('Eastern Africa', 'pes'): 10,
  ('Eastern Africa', 'tes'): 50,
  ('North Africa', 'des'): 0.0,
  ('North Africa', 'pes'): 0.0,
  ('North Africa', 'tes'): 0.0,
  ('Southern Africa', 'des'): 90.0,
  ('Southern Africa', 'pes'): 43.0,
  ('Southern Africa', 'tes'): 50.0,
  ('West Africa', 'des'): 200.0,
  ('West Africa', 'pes'): 150.0,
  ('West Africa', 'tes'): 100}}

df_sample = pd.DataFrame.from_dict(dict)
df_sample.plot(kind = "bar",
               stacked = True)

enter image description here


Solution

  • A quick an easy approach, if you know the categories, would be to reindex:

    (df_sample.reindex(['pes', 'tes', 'des'], level=1)
              .plot(kind='bar', stacked=True)
    )
    

    A more canonical (but more complex) approach would be to make the second level an ordered Categorical:

    order = pd.CategoricalDtype(['pes', 'tes', 'des'], ordered=True)
    
    (df_sample
     .set_axis(pd.MultiIndex.from_frame(df_sample.index.to_frame()
                                                 .astype({1: order}),
                                        names=[None, None]))
     .sort_index()
     .plot(kind='bar', stacked=True)
    )
    

    Output:

    enter image description here