1
$\begingroup$

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.

Thanks for your help.

| improve this question | | | | |
$\endgroup$
  • $\begingroup$ 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? $\endgroup$ – S van Balen Feb 27 at 9:07
0
$\begingroup$

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.

| improve this answer | | | | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.