Sort 2 columns in pandas keeping 1 additional column fixed

Question

I have a pandas dataframe which looks like this:

X    Y        Z
AB  bat     0.51184607
AB  ball    0.45687276
AB  cat     0.5428388
CD  apple   0.89665717
CD  tiger   0.6785901
CD  bench   0.787102
CD  dragon  0.8066087

I want to sort the dataframe based on column Z values in descending and accordingly get the respective Y column values but keeping column X as constant. So the output should look like:

X    Y         Z
AB  cat     0.5428388
AB  bat     0.51184607
AB  ball    0.45687276
CD  apple   0.89665717
CD  dragon  0.8066087
CD  bench   0.787102
CD  tiger   0.6785901

How can I effectively do this in pandas

For cases where X must remain fixed but does not have an alphanumeric order, it would have to first be converted to dtype category as shown in this answer. — Patrick FitzGerald
– Patrick FitzGerald, Commented Feb 28, 2021 at 16:20

BENY · Accepted Answer · 2020-07-16 15:10:24Z

2

You can do sort_values

df = df.sort_values(['X','Z'],ascending=[True,False])
Out[74]: 
    X       Y         Z
2  AB     cat  0.542839
0  AB     bat  0.511846
1  AB    ball  0.456873
3  CD   apple  0.896657
6  CD  dragon  0.806609
5  CD   bench  0.787102
4  CD   tiger  0.678590

answered Jul 16, 2020 at 15:10

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Comments

uitb · Accepted Answer · 2023-06-05 07:57:09Z

0

this work, but inefficient：

def sort_cols(df, cols):
    df = df.sort_values(cols)
    return df

new_df = df.groupby('X').apply(sort_cols, cols='Z').reset_index(drop=True)
df = pd.merge(df, new_df, one=df.columns.tolist())

another way from sorting by a custom list in pandas

sorter = df.X.unique()
df.X = df.X.astype('category')
df.X = df.X.cat.set_categories(sorter)
df = df.sort_values(['X', 'Z'])

edited Jun 5, 2023 at 7:57

answered Jun 5, 2023 at 7:16

uitb

212 bronze badges

Collectives™ on Stack Overflow

Sort 2 columns in pandas keeping 1 additional column fixed

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related