CODEWITHSUNDEEP
pythonpandas
hedebyhedge
hedebyhedge

Reputation: 455

Sort by specific column but keep items with same primary index together

If I had a dataframe like below, where user and item are the indices.

user        item        value
2           a           5555
            b           7777
3           c           9999
            d           2222
1           e           6666

How would I approach such that values are sorted accordingly, but if two items have the same user they are kept together regardless?

user        item        value
3           c           9999
            d           2222
2           b           7777
            a           5555
1           e           6666.

To get something like above. It is sorted by largest value for user, but items with same user are kept together.

Upvotes: 1

Views: 79

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150785

IIUC, you want to sort by max value in each group, then by the value of each row:

# max value for each user
df['max_val'] = df.groupby('user')['value'].transform('max')
(df.sort_values(['max_val', 'user', 'value'], ascending=False)
   .drop('max_val', axis=1) # drop the max value 
)

output:

           value
user item       
3    c      9999
     d      2222
2    b      7777
     a      5555
1    e      6666

Upvotes: 2

Related Questions