custom sort in python pandas dataframe needs better approach

Question

i have a dataframe like this

user = pd.DataFrame({'User':['101','101','101','102','102','101','101','102','102','102'],'Country':['India','Japan','India','Brazil','Japan','UK','Austria','Japan','Singapore','UK']})

i want to apply custom sort in country and Japan needs to be in top for both the users

i have done this but this is not my expected output

user.sort_values(['User','Country'], ascending=[True, False], inplace=True)

my expected output

expected_output = pd.DataFrame({'User':['101','101','101','101','101','102','102','102','102','102'],'Country':['Japan','India','India','UK','Austria','Japan','Japan','Brazil','Singapore','UK']})

i tried to Cast the column as category and when passing the categories and put Japan at the top. is there any other approach i don't want to pass the all the countries list every time. i just want to give user 101 -japan or user 102- UK then the remaining rows order needs to come.

Thanks

Cast the column as category and when passing the categories put Japan at the top. — ayhan, Jun 10 '18 at 18:04
i tried that is there any other apporach i dont want to pass the all the countries list everytime. i just want to give user 101 -japan or user 102- UK then the remaining rows order needs to come. — Kumar AK, Jun 10 '18 at 18:06

BENY · Accepted Answer · 2018-06-10T18:47:38.133

2

Create a new key help sort by using map

user.assign(New=user.Country.map({'Japan':1}).fillna(0)).sort_values(['User','New'], ascending=[True, False]).drop('New',1)
Out[80]: 
     Country User
1      Japan  101
0      India  101
2      India  101
5         UK  101
6    Austria  101
4      Japan  102
7      Japan  102
3     Brazil  102
8  Singapore  102
9         UK  102

Update base on comment

mapdf=pd.DataFrame({'Country':['Japan','UK'],'User':['101','102'],'New':[1,1]})
user.merge(mapdf,how='left').fillna(0).sort_values(['User','New'], ascending=[True, False]).drop('New',1)
Out[106]: 
     Country User
1      Japan  101
0      India  101
2      India  101
5         UK  101
6    Austria  101
9         UK  102
3     Brazil  102
4      Japan  102
7      Japan  102
8  Singapore  102

edited Jun 10 '18 at 18:47

answered Jun 10 '18 at 18:18

BENY

317,841
20
164
234

Thanks for the quick response. Appreciate it :) – Kumar AK Jun 10 '18 at 18:25
suppose if the user preferences are different means user 101 have Japan but user 102 have UK. in that case how to do that? – Kumar AK Jun 10 '18 at 18:39
1

@KumarAK create map dataframe , then using merge – BENY Jun 10 '18 at 18:47

jezrael · Answer 2 · 2018-06-10T18:22:45.950

Use boolean indexing with append, last sort by column User:

user = (user[user['Country'] == 'Japan']
        .append(user[user['Country'] != 'Japan'])
        .sort_values('User'))

Alternative solution:

user = (user.query('Country == "Japan"')
            .append(user.query('Country != "Japan"'))
            .sort_values('User'))

print (user)
  User    Country count
1  101      Japan     1
0  101      India     2
2  101      India     3
5  101         UK     1
6  101    Austria     1
4  102      Japan     1
7  102      Japan     1
3  102     Brazil     2
8  102  Singapore     1
9  102         UK     1

custom sort in python pandas dataframe needs better approach

2 Answers2