How to sort a multi-level pandas data-frame by a particular column?

Question

I would like to sort a multi-index pandas dataframe by a column, but do not want the entire dataframe to be sorted at once. But rather would like to sort by one of the indices. Here is an example of what I mean: Below is an example of a multi-index dataframe.

first  second
bar    one       0.361041
       two       0.476720
baz    one       0.565781
       two       0.848519
foo    one       0.405524
       two       0.882497
qux    one       0.488229
       two       0.303862

What I want to do is to get the following:

first  second
bar    one       0.476720
       two       0.361041
baz    one       0.848519
       two       0.565781
foo    one       0.882497
       two       0.405524
qux    one       0.488229
       two       0.303862

These are generated by hand to show what I want. Notice that the second dataframe is not completely sorted. But within each multi-index, it is sorted in descending order. I have a large dataframe. Is there a simpler way of do it (such as a function) instead of grouping the dataframe based on the indices, and then concatenate the individually sorted dataframes.

Answer 1

TO BE EDITTED

There would definitely be more optimal ways for addressing this problem. But this is a simple approach.

import pandas as pd
df = pd.DataFrame({'first': ['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux'],
                    'second': ['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two'], 
                    'value': [0.361041, 0.476720, 0.565781, 0.848519, 0.405524, 0.882497, 0.488229, 0.303862]})

    first   second  value
    0   bar one 0.361041
    1   bar two 0.476720
    2   baz one 0.565781
    3   baz two 0.848519
    4   foo one 0.405524
    5   foo two 0.882497
    6   qux one 0.488229
    7   qux two 0.303862

import itertools
#storing unique values of the first column.
unique = list(df['first'].unique())
#unique
#The following line, gives the row indices of the dataframe with specific value on the index ('first') column.
indices =[df.index[df['first'] == unique[i]].tolist() for i in range(len(unique))]
#Then this line sorts each pair (it can be more than 2) of the elements from the value column.
sorted_values = []
for [i,j] in indices:
    sorted_values.append(sorted([df['value'].iloc[i],df['value'].iloc[j]]))

#The following line returns the row index of a specific value of 'value' column.
#df['value']
[df['value'] == 0.361041].index.tolist()
#This ravels the sorted_list and gives the goal order of the values in 'value' column
values = []
values = list(itertools.chain(*sorted_values))
#values

#output
[0.361041, 0.47672, 0.565781, 0.848519, 0.405524, 0.882497, 0.303862, 0.488229]

Now that we have the actual order of elements in the value column, we can rebuild the whole dataframe based on this column.

However, I've done ascending ordering instead of your desired descending one.

Answer 2

Here is one approach...

import pandas as pd
df = pd.DataFrame({'first': ['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux'],
                    'second': ['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two'], 
                    'value': [0.361041, 0.476720, 0.565781, 0.848519, 0.405524, 0.882497, 0.488229, 0.303862]})
df = df.set_index(['first', 'second'])

Let us say, sort (descending) by level 1 which is column 'second'.

df.sort_index(level=[1],ascending=[False])

Check out more on Pandas sort here

Answer 3

# Sort within a group/level
df.reset_index(inplace=True)
df = df.sort_values(by=['first', 'values'], ascending=False)
df.set_index(['first', 'second'], inplace=True)

# Result
                values
first second          
qux   one     0.488229
      two     0.303862
foo   two     0.882497
      one     0.405524
baz   two     0.848519
      one     0.565781
bar   two     0.476720
      one     0.361041