Python Pandas sorting by multiindex and column

Question

In Pandas 0.17 I try to sort by a specific column while maintaining the hierarchical index (A and B). B is a running number created when setting up the dataframe through concatenation. My data looks like this:

          C      D
A   B
bar one   shiny  10
    two   dull   5
    three glossy 8
foo one   dull   3
    two   shiny  9
    three matt   12

This is what I need:

          C      D
A   B
bar two   dull   5
    three glossy 8
    one   shiny  10
foo one   dull   3
    three matt   12
    two   shiny  9

Below is the code I am using and the result. Note: Pandas 0.17 alerts that dataframe.sort will be deprecated.

df.sort_values(by="C", ascending=True)
          C      D
A   B
bar two   dull   5
foo one   dull   3
bar three glossy 8
foo three matt   12
bar one   shiny  10
foo two   shiny  9

Adding .groupby produces the same result:

df.sort_values(by="C", ascending=True).groupby(axis=0, level=0, as_index=True)

Similarly, switching to sorting indices first, and then groupby the column is not fruitful:

df.sort_index(axis=0, level=0, as_index=True).groupby(C, as_index=True)

I am not certain about reindexing I need to keep the first index A, second index B can be reassigned, but does not have to. It would surprise me if there is not an easy solution; I guess I just don't find it. Any suggestions are appreciated.

Edit: In the meantime I dropped the second index B, reassigned first index A to be a column instead of an index sorted multiple columns, then re-indexed it:

df.index = df.index.droplevel(1)
df.reset_index(level=0, inplace=True)
df_sorted = df.sort_values(["A", "C"], ascending=[1,1]) #A is a column here, not an index.
df_reindexed = df_sorted.set_index("A")

Still very verbose.

chrisb · Accepted Answer · 2015-10-17 20:55:57Z

7

Feels like there could be a better way, but here's one approach:

In [163]: def sorter(sub_df):
     ...:     sub_df = sub_df.sort_values('C')
     ...:     sub_df.index = sub_df.index.droplevel(0)
     ...:     return sub_df

In [164]: df.groupby(level='A').apply(sorter)
Out[164]: 
                C   D
A   B                
bar two      dull   5
    three  glossy   8
    one     shiny  10
foo one      dull   3
    three    matt  12
    two     shiny   9

answered Oct 17, 2015 at 20:55

chrisb

52.7k8 gold badges73 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

raummensch Over a year ago

Your approach is more advanced than my intermediate solution, but I agree that there should be a better way.

G. Cheng · Accepted Answer · 2016-05-17 06:35:36Z

1

Based on chrisb's code:

Note that in my case, it's a Series not a DataFrame,

s.groupby(level='A', group_keys=False).apply(lambda x: x.sort_values(ascending=False))

answered May 17, 2016 at 6:35

G. Cheng

1,4241 gold badge12 silver badges11 bronze badges

Collectives™ on Stack Overflow

Python Pandas sorting by multiindex and column

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related