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. — user2285236
– user2285236, Commented Jun 10, 2018 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
– Kumar AK, Commented Jun 10, 2018 at 18:06

BENY · Accepted Answer · 2018-06-10 18:47:38Z

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, 2018 at 18:47

answered Jun 10, 2018 at 18:18

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kumar AK Over a year ago

Thanks for the quick response. Appreciate it :)

Kumar AK Over a year ago

suppose if the user preferences are different means user 101 have Japan but user 102 have UK. in that case how to do that?

BENY Over a year ago

@KumarAK create map dataframe , then using merge

jezrael · Accepted Answer · 2018-06-10 18:22:45Z

1

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

edited Jun 10, 2018 at 18:22

answered Jun 10, 2018 at 18:17

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

custom sort in python pandas dataframe needs better approach

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related