0

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

Lalit
  • 79
  • 6
  • `df.sort_values(['X','Z'], ascending=[True,False])`? – Quang Hoang Jul 16 '20 at 15:10
  • 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](https://stackoverflow.com/a/13839029/14148248). – Patrick FitzGerald Feb 28 '21 at 16:20

2 Answers2

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
BENY
  • 317,841
  • 20
  • 164
  • 234
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'])
uitb
  • 11
  • 2