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

I want to sort a multi-index pandas dataframe by a column but don't want the entire dataframe to be sorted at once. But rather want 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.

• I'm not sure if this is a sorting problem, or a data manipulation problem. Your third column doesn't match up anymore to the second one. Is that what you want, or is that a typo? – S van Balen Feb 27 '20 at 9:07

# 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.

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