Pandas - Based on top x% value of each column, Mark as new number

Question

I have a pandas dataframe below:

    df

    name    value    
0   Jack       3      
1   Luke       3      
2   Mark       2      
3   Chris      1      
4   Ace        10
5   Isaac      8

Based on the "value" column, I want to have the top 50% value to be marked as 1, bottom 50% value marked as 0.

Expecting to get result below:

    Results

    name    value      percent mark  
0   Jack       3            0
1   Luke       4            1
2   Mark       2            0
3   Chris      1            0
4   Ace        10           1
5   Isaac      8            1

Thx in advance.

Yes. But I just use 50% as example, I would like to do 25% or x% as well. — SwagZ, Oct 18 '18 at 14:35

jpp · Accepted Answer · 2018-10-18T14:39:44.767

4

You can compare a series with its median and then convert bool to int:

df['percent_mark'] = (df['value'] > df['value'].median()).astype(int)

For a specific percentage, use pd.Series.quantile. For example:

df['percent_mark'] = (df['value'] > df['value'].quantile(0.25)).astype(int)

edited Oct 18 '18 at 14:39

answered Oct 18 '18 at 14:35

jpp

159,742
34
281
339

J. Doe · Answer 2 · 2018-10-18T15:01:47.527

1

You can also use numpy which is maybe faster

import numpy as np
df['percent_mark_50'] = np.where(df.value > df.value.median(), 1, 0)

Or

df['percent_mark_25'] = np.where(df.value > np.percentile(df.value, 25), 1, 0)

edited Oct 18 '18 at 15:01

answered Oct 18 '18 at 14:43

J. Doe

3,458
2
24
42

Pandas - Based on top x% value of each column, Mark as new number

2 Answers2