sorting by a custom list in pandas

Question

After reading through: http://pandas.pydata.org/pandas-docs/version/0.13.1/generated/pandas.DataFrame.sort.html

I still can't seem to figure out how to sort a column by a custom list. Obviously, the default sort is alphabetical. I'll give an example. Here is my (very abridged) dataframe:

             Player      Year   Age   Tm     G
2967     Cedric Hunter   1991    27  CHH     6
5335     Maurice Baker   2004    25  VAN     7
13950    Ratko Varda     2001    22  TOT     60
6141     Ryan Bowen      2009    34  OKC     52
6169     Adrian Caldwell 1997    31  DAL     81

I want to be able to sort by Player, Year and then Tm. The default sort by Player and Year is fine for me, in normal order. However, I do not want Team sorted alphabetically b/c I want TOT always at the top.

Here is the list I created:

sorter = ['TOT', 'ATL', 'BOS', 'BRK', 'CHA', 'CHH', 'CHI', 'CLE', 'DAL', 'DEN',
   'DET', 'GSW', 'HOU', 'IND', 'LAC', 'LAL', 'MEM', 'MIA', 'MIL',
   'MIN', 'NJN', 'NOH', 'NOK', 'NOP', 'NYK', 'OKC', 'ORL', 'PHI',
   'PHO', 'POR', 'SAC', 'SAS', 'SEA', 'TOR', 'UTA', 'VAN',
   'WAS', 'WSB']

After reading through the link above, I thought this would work but it didn't:

df.sort(['Player', 'Year', 'Tm'], ascending = [True, True, sorter])

It still has ATL at the top, meaning that it sorted alphabetically and not according to my custom list. Any help would really be greatly appreciated, I just can't figure this out.

Is there a compelling reason not to stick an extra column on the `DataFrame` with the index of your team sorter? — Raman Shah, May 05 '14 at 22:42
no compelling reason, was curious why mine didn't work though — itjcms18, May 05 '14 at 22:51
Somehow related, for `value_counts()` : https://stackoverflow.com/q/43855474/812102. — Skippy le Grand Gourou, Mar 04 '21 at 09:06

score 129 · Answer 1 · edited Feb 13 '23 at 18:12

The below answer is an old answer. It still works. Anyhow, another very elegant solution has been posted below , using the key argument.

I just discovered that with pandas 15.1 it is possible to use categorical series (https://pandas.pydata.org/docs/user_guide/categorical.html)

As for your example, lets define the same data-frame and sorter:

import pandas as pd

data = {
    'id': [2967, 5335, 13950, 6141, 6169],
    'Player': ['Cedric Hunter', 'Maurice Baker', 
               'Ratko Varda' ,'Ryan Bowen' ,'Adrian Caldwell'],
    'Year': [1991, 2004, 2001, 2009, 1997],
    'Age': [27, 25, 22, 34, 31],
    'Tm': ['CHH', 'VAN', 'TOT', 'OKC', 'DAL'],
    'G': [6, 7, 60, 52, 81]
}

# Create DataFrame
df = pd.DataFrame(data)

# Define the sorter
sorter = ['TOT', 'ATL', 'BOS', 'BRK', 'CHA', 'CHH', 'CHI', 'CLE', 'DAL', 'DEN',
          'DET', 'GSW', 'HOU', 'IND', 'LAC', 'LAL', 'MEM', 'MIA', 'MIL',
          'MIN', 'NJN', 'NOH', 'NOK', 'NOP', 'NYK', 'OKC', 'ORL', 'PHI',
          'PHO', 'POR', 'SAC', 'SAS', 'SEA', 'TOR', 'UTA', 'VAN', 'WAS', 'WSB']

With the data-frame and sorter, which is a category-order, we can do the following in pandas 15.1:

# Convert Tm-column to category and in set the sorter as categories hierarchy
# You could also do both lines in one just appending the cat.set_categories()
df.Tm = df.Tm.astype("category")
df.Tm = df.Tm.cat.set_categories(sorter)

print(df.Tm)
Out[48]: 
0    CHH
1    VAN
2    TOT
3    OKC
4    DAL
Name: Tm, dtype: category
Categories (38, object): [TOT < ATL < BOS < BRK ... UTA < VAN < WAS < WSB]

df.sort_values(["Tm"])  ## 'sort' changed to 'sort_values'
Out[49]: 
   Age   G           Player   Tm  Year     id
2   22  60      Ratko Varda  TOT  2001  13950
0   27   6    Cedric Hunter  CHH  1991   2967
4   31  81  Adrian Caldwell  DAL  1997   6169
3   34  52       Ryan Bowen  OKC  2009   6141
1   25   7    Maurice Baker  VAN  2004   5335

Nice! I haven't seen the category type of pandas in action yet, it looks very useful — cd98, Dec 02 '14 at 18:22
Merging the two lines, to have something like `df.Tm.astype('...').cat.set_cat...` didn't work for me. Had to type it out as you have it here — raphael, Nov 08 '17 at 17:24
the values in the df that are not in the sorter are replaced by nans.... — Claudiu Creanga, Jan 15 '19 at 15:32
I don't understand why, but I had to remove inplace=True and reassign the column, to make it work. — Fabio Filippi, Sep 13 '19 at 14:20
@fabiofili2pi - inplace=True has been depreciated `df.Tm = f.Tm.cat.set_categories(sorter)` would be the correct syntax now — June Skeeter, Mar 27 '22 at 19:53

Guillaume Jacquenot · Accepted Answer · 2020-08-15T05:51:55.437

Below is an example that performs lexicographic sort on a dataframe. The idea is to create an numerical index based on the specific sort. Then to perform a numerical sort based on the index. A column is added to the dataframe to do so, and is then removed.

import pandas as pd

# Create DataFrame
df = pd.DataFrame(
{'id':[2967, 5335, 13950, 6141, 6169],
    'Player': ['Cedric Hunter', 'Maurice Baker',
               'Ratko Varda' ,'Ryan Bowen' ,'Adrian Caldwell'],
    'Year': [1991, 2004, 2001, 2009, 1997],
    'Age': [27, 25, 22, 34, 31],
    'Tm': ['CHH' ,'VAN' ,'TOT' ,'OKC', 'DAL'],
    'G': [6, 7, 60, 52, 81]})

# Define the sorter
sorter = ['TOT', 'ATL', 'BOS', 'BRK', 'CHA', 'CHH', 'CHI', 'CLE', 'DAL','DEN',
          'DET', 'GSW', 'HOU', 'IND', 'LAC', 'LAL', 'MEM', 'MIA', 'MIL',
          'MIN', 'NJN', 'NOH', 'NOK', 'NOP', 'NYK', 'OKC', 'ORL', 'PHI',
          'PHO', 'POR', 'SAC', 'SAS', 'SEA', 'TOR', 'UTA', 'VAN',
          'WAS', 'WSB']

# Create the dictionary that defines the order for sorting
sorterIndex = dict(zip(sorter, range(len(sorter))))

# Generate a rank column that will be used to sort
# the dataframe numerically
df['Tm_Rank'] = df['Tm'].map(sorterIndex)

# Here is the result asked with the lexicographic sort
# Result may be hard to analyze, so a second sorting is
# proposed next
## NOTE: 
## Newer versions of pandas use 'sort_values' instead of 'sort'
df.sort_values(['Player', 'Year', 'Tm_Rank'],
        ascending = [True, True, True], inplace = True)
df.drop('Tm_Rank', 1, inplace = True)
print(df)

# Here is an example where 'Tm' is sorted first, that will 
# give the first row of the DataFrame df to contain TOT as 'Tm'
df['Tm_Rank'] = df['Tm'].map(sorterIndex)
## NOTE: 
## Newer versions of pandas use 'sort_values' instead of 'sort'
df.sort_values(['Tm_Rank', 'Player', 'Year'],
        ascending = [True , True, True], inplace = True)
df.drop('Tm_Rank', 1, inplace = True)
print(df)

It would be faster to generate the ordering column using map as this uses cython so `df['Tm_Rank'] = df['Tm'].map(sorterIndex)`, then order using this and then drop — EdChum, May 05 '14 at 22:54
awesome, thanks. do you know why setting ascending equal to a list doesn't work? am i reading the documentation wrong? — itjcms18, May 06 '14 at 20:11
This worked for me - the answer from @dmeu left blanks in the sorted column for some reason. Thanks. (Also sort is now called sort values) — DavidC, Aug 17 '18 at 17:00

kai · Answer 3 · 2022-09-23T08:46:56.093

53

df1 = df.set_index('Tm')
df1.loc[sorter]

as @kstajer commented, after pandas 1.0.0, use reindex instead:

df1.reindex(sorter)

edited Sep 23 '22 at 08:46

answered Jan 16 '21 at 12:55

kai

1,640
18
11

1

This is the best concise and effective solution. – Fernando Ortega Jul 12 '21 at 08:26
concise and effective... +1. – Mr. Unnormalized Posterior Oct 08 '21 at 09:27
1

(+1) I had to use `df.set_index('Tm', drop= False)` to prevent dropping the selected column. (I don't use pandas very often - is it just me or pandas' default options are often counter to common sense?) – dariober Oct 20 '21 at 12:34
3

This should be the accepted answer – Iván Sánchez Mar 07 '22 at 14:35
Total best answer. I had to use df.reset_index() to get the get the column out of index – Masih May 02 '22 at 15:17
KeyError: "Passing list-likes to .loc or [] with any missing labels is no longer supported." Instead of `df1.loc[sorter]` use `df1.reindex(sorter)`. Here is more info: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#deprecate-loc-reindex-listlike – kstajer Sep 22 '22 at 08:11

score 20 · Answer 4 · answered May 16 '22 at 09:18

20

Since version 1.1.0 you can use the key attribute to sort values:

df.sort_values(by="Tm", key=lambda column: column.map(lambda e: sorter.index(e)), inplace=True)

answered May 16 '22 at 09:18

Marius DKM

305
3
7

4

Nice one, thanks! I referenced your answer from my (old) answer. This is now the far better option. – dmeu Sep 20 '22 at 14:30

Arleg · Answer 5 · 2020-08-20T15:19:18.873

According to pandas 1.1.0 documentation, it has become possible to sort with key parameter like in sorted function (finally!). Here how we can sort by Tm

import pandas as pd


data = {
    'id': [2967, 5335, 13950, 6141, 6169],
    'Player': ['Cedric Hunter', 'Maurice Baker', 
               'Ratko Varda' ,'Ryan Bowen' ,'Adrian Caldwell'],
    'Year': [1991, 2004, 2001, 2009, 1997],
    'Age': [27, 25, 22, 34, 31],
    'Tm': ['CHH', 'VAN', 'TOT', 'OKC', 'DAL'],
    'G': [6, 7, 60, 52, 81]
}

# Create DataFrame
df = pd.DataFrame(data)


def tm_sorter(column):
    """Sort function"""
    teams = ['TOT', 'ATL', 'BOS', 'BRK', 'CHA', 'CHH', 'CHI', 'CLE', 'DAL', 'DEN',
       'DET', 'GSW', 'HOU', 'IND', 'LAC', 'LAL', 'MEM', 'MIA', 'MIL',
       'MIN', 'NJN', 'NOH', 'NOK', 'NOP', 'NYK', 'OKC', 'ORL', 'PHI',
       'PHO', 'POR', 'SAC', 'SAS', 'SEA', 'TOR', 'UTA', 'VAN',
       'WAS', 'WSB']
    correspondence = {team: order for order, team in enumerate(teams)}
    return column.map(correspondence)

df.sort_values(by='Tm', key=tm_sorter)

Sadly, it looks like we can use this feature only in sorting by 1 column (list with keys is not acceptable). It can be circumvented by groupby

df.sort_values(['Player', 'Year']) \
  .groupby(['Player', 'Year']) \
  .apply(lambda x: x.sort_values(by='Tm', key=tm_sorter)) \
  .reset_index(drop=True)

If you know how to use key in sort_values with multiple columns, tell me please

To clarify, you can still sort your dataframe by multiple columns (i.e. `df.sort_values(by=['col1', 'col2'], key=mysort)`) but the `key` function will only receive one column at a time. I suppose if your key function is inline you could access the full dataframe object if, for example, you wanted one column to sort based on a comparison to another. I like your solution with `map`, but wanted to note the same can be done with `cat = pd.Categorical(column, categories=teams, ordered=True)` and then `return pd.Series(cat)`. — totalhack, Sep 15 '20 at 12:26

score 16 · Answer 6 · answered Jan 25 '21 at 16:33

16

This does the job in just a couple of lines

# Create a dummy df with the required list and the col name to sort on
dummy = pd.Series(sort_list, name = col_name).to_frame()

# Use left merge on the dummy to return a sorted df
sorted_df = pd.merge(dummy, df, on = col_name, how = 'left')

answered Jan 25 '21 at 16:33

G-Higgins

161
1
3

Excellent concise solution – lys Mar 21 '21 at 00:39

score 11 · Answer 7 · answered Nov 12 '19 at 20:17

Setting the index then DataFrame.loc is useful when you need to order by a single custom list. Because loc will create NaN rows for values in sorter that aren't in the DataFrame we'll first find the intersection. This prevents any unwanted upcasting. Any rows with values not in the list are removed.

true_sort = [s for s in sorter if s in df.Tm.unique()]
df = df.set_index('Tm').loc[true_sort].reset_index()

    Tm     id           Player  Year  Age   G
0  TOT  13950      Ratko Varda  2001   22  60
1  CHH   2967    Cedric Hunter  1991   27   6
2  DAL   6169  Adrian Caldwell  1997   31  81
3  OKC   6141       Ryan Bowen  2009   34  52
4  VAN   5335    Maurice Baker  2004   25   7

Starting Data:

print(df)
      id           Player  Year  Age   Tm   G
0   2967    Cedric Hunter  1991   27  CHH   6
1   5335    Maurice Baker  2004   25  VAN   7
2  13950      Ratko Varda  2001   22  TOT  60
3   6141       Ryan Bowen  2009   34  OKC  52
4   6169  Adrian Caldwell  1997   31  DAL  81

sorter = ['TOT', 'ATL', 'BOS', 'BRK', 'CHA', 'CHH', 'CHI', 'CLE', 'DAL', 'DEN',
          'DET', 'GSW', 'HOU', 'IND', 'LAC', 'LAL', 'MEM', 'MIA', 'MIL',
          'MIN', 'NJN', 'NOH', 'NOK', 'NOP', 'NYK', 'OKC', 'ORL', 'PHI',
          'PHO', 'POR', 'SAC', 'SAS', 'SEA', 'TOR', 'UTA', 'VAN', 'WAS', 'WSB']

scign · Answer 8 · 2021-03-14T21:07:25.647

Partial solution for those only interested in sorting by categorical columns:

You can do this with a helper function that creates a sort order mapper from a custom list.

This example only includes values from one column, however it could be extended to include other columns by creating a custom order list that includes values that occur in all columns. Naturally, since you must construct your custom list with all possible values in your sort field, this is good mostly for categorical sorting and would not be suitable for continuous variables (unless the possible values are known up front) and columns with a very high cardinality.

import pandas as pd

# set up a dummy dataframe
df = pd.DataFrame({'a':list('abcde'), 'b':range(5)})

# helper function
def make_sorter(l):
    """
    Create a dict from the list to map to 0..len(l)
    Returns a mapper to map a series to this custom sort order
    """
    sort_order = {k:v for k,v in zip(l, range(len(l)))}
    return lambda s: s.map(lambda x: sort_order[x])

# define a custom sort order
my_order = list('bdeca')

df.sort_values('a', key=make_sorter(my_order))

   a b
1  b 1
3  d 3
4  e 4
2  c 2
0  a 0

With OP's data:

df = pd.DataFrame({
    'id':[2967, 5335, 13950, 6141, 6169],
    'Player': ['Cedric Hunter', 'Maurice Baker',
               'Ratko Varda' ,'Ryan Bowen' ,'Adrian Caldwell'],
    'Year': [1991, 2004, 2001, 2009, 1997],
    'Age': [27, 25, 22, 34, 31],
    'Tm': ['CHH' ,'VAN' ,'TOT' ,'OKC', 'DAL'],
    'G': [6, 7, 60, 52, 81]
})

# Define the sorter
sorter = [
    'TOT', 'ATL', 'BOS', 'BRK', 'CHA', 'CHH', 'CHI', 'CLE', 'DAL',
    'DEN', 'DET', 'GSW', 'HOU', 'IND', 'LAC', 'LAL', 'MEM', 'MIA',
    'MIL', 'MIN', 'NJN', 'NOH', 'NOK', 'NOP', 'NYK', 'OKC', 'ORL',
    'PHI', 'PHO', 'POR', 'SAC', 'SAS', 'SEA', 'TOR', 'UTA', 'VAN',
    'WAS', 'WSB'
]

df.sort_values('Tm', key=make_sorter(sorter))

      id           Player  Year  Age   Tm   G
2  13950      Ratko Varda  2001   22  TOT  60
0   2967    Cedric Hunter  1991   27  CHH   6
4   6169  Adrian Caldwell  1997   31  DAL  81
3   6141       Ryan Bowen  2009   34  OKC  52
1   5335    Maurice Baker  2004   25  VAN   7

Mithril · Answer 9 · 2019-07-27T09:59:51.977

My idea is generate sort number by index, then merge sort number into original dataframe

import pandas as pd

df = pd.DataFrame(
{'id':[2967, 5335, 13950, 6141, 6169],\
 'Player': ['Cedric Hunter', 'Maurice Baker' ,\
            'Ratko Varda' ,'Ryan Bowen' ,'Adrian Caldwell'],\
 'Year': [1991 ,2004 ,2001 ,2009 ,1997],\
 'Age': [27 ,25 ,22 ,34 ,31],\
 'Tm':['CHH' ,'VAN' ,'TOT' ,'OKC' ,'DAL'],\
 'G':[6 ,7 ,60 ,52 ,81]})

sorter = ['TOT', 'ATL', 'BOS', 'BRK', 'CHA', 'CHH', 'CHI', 'CLE', 'DAL', 'DEN',
   'DET', 'GSW', 'HOU', 'IND', 'LAC', 'LAL', 'MEM', 'MIA', 'MIL',
   'MIN', 'NJN', 'NOH', 'NOK', 'NOP', 'NYK', 'OKC', 'ORL', 'PHI',
   'PHO', 'POR', 'SAC', 'SAS', 'SEA', 'TOR', 'UTA', 'VAN',
   'WAS', 'WSB']

x = pd.DataFrame({'Tm': sorter})
x.index = x.index.set_names('number')
x = x.reset_index()

df = pd.merge(df, x, how='left', on='Tm')

df.sort_values(['Player', 'Year', 'number'], \
        ascending = [True, True, True], inplace = True)
df.drop('number', 1, inplace = True)

sorting by a custom list in pandas

9 Answers9

Linked