["python","sorting","pandas"]
sorting by a custom list in pandas

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.
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)