Pandas sort_value() issue. Wrong sorting integer when applied key parameter

Question

Hi everyone I have tried to look everywhere for this issue but I cannot figure a solution out. I'd be glad if you'd help me.

So, basically I have this dataset:

df = pd.DataFrame({"col1": ['xxx', 'xxx', 'xxx', 'kkk', 'www', 'www'],
                 "col2": [ 2020, 1994, 2013, 1000, 1996, 2021]})

df.dtypes
col1    object
col2     int64
dtype: object

and I want to order the first column with a costum order and the second column with ascending order. The final result should be the following:

    col1    col2
4    www    1996
5    www    2021
3    kkk    1000
0    xxx    1994
1    xxx    2013
2    xxx    2020

So, in order to accomplish that I do this:

d = {'xxx': 4, 'zzz':1, 'yyy':5, 'kkk':2, 'jjj':3, 'www':0} # to customize order 

df.sort_values(by = ['col1' , 'col2'], key = lambda x: x.map(d))

but I end up with this:

    col1    col2
4    www    1996
5    www    2021
3    kkk    1000
0    xxx    2020
1    xxx    1994
2    xxx    2013

If I only do:

df.sort_values(by = ['col1' , 'col2'])

    col1    col2
3    kkk    1000
4    www    1996
5    www    2021
1    xxx    1994
2    xxx    2013
0    xxx    2020

The col2 is ordered fine. I really don't know why I am having this issue. Has anyone experienced something similar? Thanks

jezrael · Accepted Answer · 2021-04-23 11:04:02Z

1

Possible trick is expand dictionary by values from col2:

d = {'xxx': 4, 'zzz':1, 'yyy':5, 'kkk':2, 'jjj':3, 'www':0} # to customize order 
d = {**d, **dict(zip(df.col2, df.col2))}

df = df.sort_values(by = ['col1' , 'col2'], key = lambda x: x.map(d))
print (df)
  col1  col2
4  www  1996
5  www  2021
3  kkk  1000
1  xxx  1994
2  xxx  2013
0  xxx  2020

Or solution with get - if no match is returned same value instead NaN:

df = df.sort_values(by = ['col1' , 'col2'], key = lambda x: x.map(lambda y: d.get(y, y)))
print (df)
  col1  col2
4  www  1996
5  www  2021
3  kkk  1000
1  xxx  1994
2  xxx  2013
0  xxx  2020

Solution with helper column:

d = {'xxx': 4, 'zzz':1, 'yyy':5, 'kkk':2, 'jjj':3, 'www':0} 

df = df.assign(new=df['col1'].map(d)).sort_values(by=['new','col2']).drop('new', axis=1)
print (df)
  col1  col2
4  www  1996
5  www  2021
3  kkk  1000
1  xxx  1994
2  xxx  2013
0  xxx  2020

edited Apr 23, 2021 at 11:04

answered Apr 23, 2021 at 10:41

jezrael

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

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

1 Comment

fecke9296 Over a year ago

So, In the end I used the get solution since I actually had a bigger dataset with different values so I couldn't apply the ```categorical`` solution in a easy way. Thanks a lot for the answer.

Nk03 · Accepted Answer · 2021-04-23 10:41:57Z

1

Try this. -

df  =df.groupby('col1')['col2'].apply(sorted).explode().reset_index()
d = {'xxx': 4, 'zzz':1, 'yyy':5, 'kkk':2, 'jjj':3, 'www':0} 
df['sorted'] = df['col1'].map(d)
df = df.sort_values('sorted').drop('sorted', axis=1)

answered Apr 23, 2021 at 10:41

Nk03

15k2 gold badges11 silver badges24 bronze badges

Comments

Rob Raymond · Accepted Answer · 2021-04-23 10:46:46Z

1

Change col1 to be a categorical where it is ordered.

df = pd.DataFrame({"col1": ['xxx', 'xxx', 'xxx', 'kkk', 'www', 'www'],
                 "col2": [ 2020, 1994, 2013, 1000, 1996, 2021]})
d = {'xxx': 4, 'zzz':1, 'yyy':5, 'kkk':2, 'jjj':3, 'www':0} # to customize order 

df["col1"] = pd.Categorical(df["col1"], sorted(list(d.keys()), key=lambda x: d[x]), ordered=True)
df.sort_values(["col1","col2"])

	col1	col2
4	www	1996
5	www	2021
3	kkk	1000
1	xxx	1994
2	xxx	2013
0	xxx	2020

answered Apr 23, 2021 at 10:46

Rob Raymond

31.5k3 gold badges19 silver badges34 bronze badges

Comments

Andreas · Accepted Answer · 2021-04-23 10:50:00Z

1

Pandas has a datatype "categorical" which can do that:

import pandas as pd
df = pd.DataFrame({"col1": ['xxx', 'xxx', 'xxx', 'kkk', 'www', 'www'],
                 "col2": [ 2020, 1994, 2013, 1000, 1996, 2021]})

df["col1"] = pd.Categorical(df["col1"], ordered=True,
             categories=['www', 'zzz', 'kkk', 'jjj', 'xxx', 'yyy'])


df.sort_values(["col1", "col2"], ascending = (True, True))

  col1  col2
4  www  1996
5  www  2021
3  kkk  1000
1  xxx  1994
2  xxx  2013
0  xxx  2020

answered Apr 23, 2021 at 10:50

Andreas

9,2853 gold badges20 silver badges47 bronze badges

Comments

STerliakov · Accepted Answer · 2021-04-23 10:55:42Z

1

Well, it's not a bug. What do you expect from key when passing it to multiple columns? It will be applied to each of columns and used as sorting key. You don't have any of col2 elements in d, so it returns None (I think so because no error is raised, thus it may use d.get(x, None)). For your use case the most simple solution will be sorting twice:

df.sort_values(by='col2').sort_values(by='col1', key=lambda x: x.map(d))

I have also checked whether my assumptions were correct. If you set key=x.map(lambda t: d[t]) instead of x.map(d) in your code example, error is raised saying that key is unmatched. So you really try to apply it to both columns and sort by results.

answered Apr 23, 2021 at 10:55

STerliakov

8,8883 gold badges27 silver badges59 bronze badges

Comments

Victor Ermakov · Accepted Answer · 2021-04-23 14:27:19Z

1

create new column, map it to your dictionary, sort by it, then delete :)

df = pd.DataFrame({"col1": ['xxx', 'xxx', 'xxx', 'kkk', 'www', 'www'],
                 "col2": [ 2020, 1994, 2013, 1000, 1996, 2021]})

d = {'xxx': 4, 'zzz':1, 'yyy':5, 'kkk':2, 'jjj':3, 'www':0} # to customize order

df['sorting'] = df['col1'].map(d)
df = df.sort_values('sorting')
del df['sorting']

edited Apr 23, 2021 at 14:27

answered Apr 23, 2021 at 10:49

Victor Ermakov

4913 silver badges6 bronze badges

Collectives™ on Stack Overflow

Pandas sort_value() issue. Wrong sorting integer when applied key parameter

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related