user308827 user308827 - 3 months ago 18
Python Question

Reorder pandas groupby dataframe

I have the following groupby dataframe in pandas

Crop Region
maize_1 Temperate 30.0
Tropical 46.0
maize_2 Tropical 77.5
Temperate 13.5
soybean_1 Temperate 18.5
Tropical 35.0


How can I sort it so that in the 'Region' Column, Temperate preceedes Tropical?

-- EDIT: expected answer is

Crop Region
maize_1 Temperate 30.0
Tropical 46.0
maize_2 Temperate 13.5
Tropical 77.5
soybean_1 Temperate 18.5
Tropical 35.0

Answer

Setup

idx = pd.MultiIndex.from_tuples([('maize_1', 'Temperate'), ('maize_1', 'Tropical'),
                                 ('maize_2', 'Tropical'), ('maize_2', 'Temperate'),
                                 ('soybean_1', 'Temperate'), ('soybean_1', 'Tropical')],
                               names=['Crop', 'Region'])
s = pd.Series([30., 46., 77.5, 13.5, 18.5, 34.], idx)

s

Crop       Region   
maize_1    Temperate    30.0
           Tropical     46.0
maize_2    Tropical     77.5
           Temperate    13.5
soybean_1  Temperate    18.5
           Tropical     34.0
dtype: float64

Solution

IIUC you want to sort by 'Region' and leave 'Crop' alone.

s.unstack().sort_index(1).stack()

Crop       Region   
maize_1    Temperate    30.0
           Tropical     46.0
maize_2    Temperate    13.5
           Tropical     77.5
soybean_1  Temperate    18.5
           Tropical     34.0
dtype: float64

You can also, sort the index as is but it will also sort 'Crop'. It so happens your 'Crop's are already in order. But if they weren't, this solution would preserve that.

Comments