how to sort descending an alphanumeric pandas index.

Question

I have an pandas data frame that looks like:

df = DataFrame({'id':['a132','a132','b5789','b5789','c1112','c1112'], 'value':[0,0,0,0,0,0,]}) 

df = df.groupby('id').sum()

  value
id          
a132       0
b5789      0
c1112      0

I would like to sort it so that it looks like:

       value
id                
b5789      0
c1112      0
a132       0

which is looking at the number(although string) and sorting as descending

that requires me to make the mapping each time. Which requires me to look at the name. My real data is large and it changes every time. Moreover I need to set as an automatic job that runs everyday. Thanks for the attempt though. — Blue Moon
– Blue Moon, Commented Sep 11, 2015 at 17:46

Romain · Accepted Answer · 2015-09-11 20:58:07Z

2

A simple solution is to:

split the index to extract the number in a temporary key column
sort by this column descending
drop the temporary key column

df = DataFrame({'id':['a132','a132','b5789','b5789','c1112','c1112'], 'value':[0,0,0,0,0,0,]}) 

df = df.groupby('id').sum()

df['key'] = df.index
df['key'] = df['key'].str.split('(\d+)').str[1].astype(int)
df = df.sort('key', ascending=False).drop('key', axis=1)

# Result
       value
id          
b5789      0
c1112      0
a132       0

edited Sep 11, 2015 at 20:58

answered Sep 11, 2015 at 20:45

Romain

22.2k6 gold badges63 silver badges77 bronze badges

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

Comments

chrisb · Accepted Answer · 2015-09-11 18:31:29Z

2

Categoricals provide a reasonably easy way to define an arbitrary ordering

In [35]: df['id'] = df['id'].astype('category')

In [39]: df['id'] = (df['id'].cat.reorder_categories(
                         sorted(df['id'].cat.categories, key = lambda x: int(x[1:]), reverse=True)))
In [40]: df.groupby('id').sum()
Out[40]: 
       value
id          
b5789      0
c1112      0
a132       0

edited Sep 11, 2015 at 18:31

answered Sep 11, 2015 at 18:24

chrisb

52.7k8 gold badges73 silver badges70 bronze badges

Collectives™ on Stack Overflow

how to sort descending an alphanumeric pandas index.

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related