CODEWITHSUNDEEP
pythonsortingpandas
Blue Moon
Blue Moon

Reputation: 4661

how to sort descending an alphanumeric pandas index.

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

Upvotes: 2

Views: 2479

Answers (2)

Romain
Romain

Reputation: 21928

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

Upvotes: 2

chrisb
chrisb

Reputation: 52276

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

Upvotes: 2

Related Questions