CODEWITHSUNDEEP
pythonsortingpandas
Brian Postow
Brian Postow

Reputation: 12187

How to sort rows in pandas with a non-standard order

I have a pandas dataframe, say:

df = pd.DataFrame ([['a', 3, 3], ['b', 2, 5], ['c', 4, 9], ['d', 1, 43]], columns = ['col 1' , 'col2', 'col 3'])

or:

  col 1  col2  col 3
0     a     3      3
1     b     2      5
2     c     4      9
3     d     1     43

If I want to sort by col2, I can use df.sort, and that will sort ascending and descending.

However, if I want to sort the rows so that col2 is: [4, 2, 1, 3], how would I do that?

Upvotes: 5

Views: 969

Answers (3)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210912

alternative solution:

In [409]: lst = [4, 2, 1, 3]

In [410]: srt = pd.Series(np.arange(len(lst)), index=lst)

In [411]: srt
Out[411]:
4    0
2    1
1    2
3    3
dtype: int32

In [412]: df.assign(x=df.col2.map(srt))
Out[412]:
  col 1  col2  col 3  x
0     a     3      3  3
1     b     2      5  1
2     c     4      9  0
3     d     1     43  2

In [413]: df.assign(x=df.col2.map(srt)).sort_values('x')
Out[413]:
  col 1  col2  col 3  x
2     c     4      9  0
1     b     2      5  1
3     d     1     43  2
0     a     3      3  3

In [414]: df.assign(x=df.col2.map(srt)).sort_values('x').drop('x',1)
Out[414]:
  col 1  col2  col 3
2     c     4      9
1     b     2      5
3     d     1     43
0     a     3      3

NOTE: i do like @chrisb's solution more - it's much more elegant and probably will work faster

Upvotes: 1

Merlin
Merlin

Reputation: 25659

Try this:

sortMap = {4:1, 2:2, 1:3,3:4 }
df["new"] = df2['col2'].map(sortMap)
df.sort_values('new', inplace=True)
df

   col1  col2  col3  new
2    c     4     9    1
1    b     2     5    2
3    d     1    43    3
0    a     3     3    4

alt method to create dict: 

ll      = [4, 2, 1, 3] 
sortMap = dict(zip(ll,range(len(ll))))

Upvotes: 5

chrisb
chrisb

Reputation: 52276

One way is to convert that column to a Categorical type, which can have an arbitrary ordering.

In [51]: df['col2'] = df['col2'].astype('category', categories=[4, 1, 2, 3], ordered=True)

In [52]: df.sort_values('col2')
Out[52]: 
  col 1 col2  col 3
2     c    4      9
3     d    1     43
1     b    2      5
0     a    3      3

Upvotes: 4

Related Questions