[FIXED] How to rearrange rows of a dataframe according to the values of a specific column

Issue

I am working on a dataframe that has a column named season. Each season has many matches. The order of season is to be rearranged. Season order is 2017,2008,2009,2010,2011,2012,2013,2014,2015,2016,2018,2019.

I want to bring all the rows of the season 2017 after 2016 season rows.

The data looks like this, (renamed id to match_id, few columns are shown here, in total there are 18)

    match_id    season  city        winner
0   1           2017    Hyderabad   Sunrisers Hyderabad
1   2           2017    Pune        Rising Pune Supergiant
2   3           2017    Rajkot      Kolkata Knight Riders   
3   4           2017    Indore      Kings XI Punjab
4   5           2017    Bangalore   Royal Challengers Bangalore 

I tried this,

df.set_index('season')

and then,

df.reindex([2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015,2016, 2017, 2018, 2019])

but the output was horrible,

        match_id    season  city    winner
2008    NaN         NaN     NaN     NaN 
2009    NaN         NaN     NaN     NaN
2010    NaN         NaN     NaN     NaN
2011    NaN         NaN     NaN     NaN
2012    NaN         NaN     NaN     NaN
2013    NaN         NaN     NaN     NaN
2014    NaN         NaN     NaN     NaN
2015    NaN         NaN     NaN     NaN
2016    NaN         NaN     NaN     NaN
2017    NaN         NaN     NaN     NaN
2018    NaN         NaN     NaN     NaN
2019    NaN         NaN     NaN     NaN

Solution

First idea is sorting by ordered categoricals with order by list:

L =[2008,2009, 2010, 2011, 2012, 2013, 2014, 2015,2016,2017, 2018, 2019]
df['season'] = pd.Categorical(df['season'], ordered=True, categories=L)

df = df.sort_values(['season','match_id'], ignore_index=True)

Or you can create dictionary with enumerate for mapping in key parameter:

L =[2008,2009, 2010, 2011, 2012, 2013, 2014, 2015,2016,2017, 2018, 2019]

d = {v: k for k, v in enumerate(L)}
df = df.sort_values('season', key = lambda x: x.map(d), ignore_index=True)

Answered By - jezrael