Pandas ordering based on column value

Question

I have a pandas dataframe like:

I have the data frame as like below one,

Input DataFrame
     id          ratio
 0   1           5.00%
 1   2           9.00%
 2   3           6.00%
 3   2           13.00%
 4   1           19.00%
 5   4           30.00%
 6   3           5.5%
 7   2           22.00%

How can I then group this like

         id          ratio
     0   1           5.00%
     4   1           19.00%
     6   3           5.5%
     2   3           6.00%
     1   2           9.00%
     3   2           13.00%
     7   2           22.00%
     5   4           30.00%

So essentially first looks at the ratio, takes the lowest for that value and groups the rest of the rows for which it has the same id. Then looks for the second lowest ratio and groups the rest of the ids again etc.

Does this answer your question? apply sort to a pandas groupby operation — cadolphs
– cadolphs, Commented Jan 4, 2020 at 0:08

Erfan · Accepted Answer · 2020-01-04 00:40:18Z

2

First convert your ratio column to numeric.

Then we get the lowest rank per group by using Groupby

Finally we sort based on rank and numeric ratio.

df['ratio_num'] = df['ratio'].str[:-1].astype(float).rank()
df['rank'] = df.groupby('id')['ratio_num'].transform('min')

df = df.sort_values(['rank', 'ratio_num']).drop(columns=['rank', 'ratio_num'])

   id   ratio
0   1   5.00%
1   1  19.00%
2   3    5.5%
3   3   6.00%
4   2   9.00%
5   2  13.00%
6   2  22.00%
7   4  30.00%

edited Jan 4, 2020 at 0:40

answered Jan 4, 2020 at 0:27

Erfan

43.3k10 gold badges75 silver badges86 bronze badges

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

Comments

Andrej Kesely · Accepted Answer · 2020-01-04 00:39:57Z

0

With help of pd.Categorical:

d = {'id':[1, 2, 3, 2, 1, 4, 3, 2],
     'ratio': ['5.00%', '9.00%', '6.00%', '13.00%', '19.00%', '30.00%', '5.5%', '22.00%']}

df = pd.DataFrame(d)

df['ratio_'] = df['ratio'].map(lambda x: float(x[:-1]))
df['id'] = pd.Categorical(df['id'], categories=df.sort_values(['id', 'ratio_']).groupby('id').head(1).sort_values(['ratio_', 'id'])['id'], ordered=True)
print(df.sort_values(['id', 'ratio_']).drop('ratio_', axis=1))

Prints:

  id   ratio
0  1   5.00%
4  1  19.00%
6  3    5.5%
2  3   6.00%
1  2   9.00%
3  2  13.00%
7  2  22.00%
5  4  30.00%

answered Jan 4, 2020 at 0:39

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

Collectives™ on Stack Overflow

Pandas ordering based on column value

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