Ordering value in DataFrame based on pre-defined percentage groups

user_4932316 0
Viewed 70

I have the following sample dataframe

df = pd.DataFrame({'ID': [11, 12, 16, 19, 14, 9, 4, 13, 6, 18], 
                   'Value': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]})

to which I add the percentage of the Value using 

df['Percent Value'] = df.reset_index()['Value'].rank(method='dense', pct=True)

which gives

    ID  Value   Percent Value
0   11   1         0.1
1   12   2         0.2
2   16   3         0.3
3   19   4         0.4
4   14   5         0.5
5   9    6         0.6
6   4    7         0.7
7   13   8         0.8
8   6    9         0.9
9   18   10        1.0

I then define the following percentage array

percentage = np.array([30, 40, 60, 90, 100])

My desired output is the following with an Order column where, based on the percentage array, the Value which are up to 30% gets order 1, 30-40% gets order 2, 40-60% gets order 3, 60-90% gets order 4, 90-100% gets order 5.

So the final output is

    ID  Value   Percent Value   Order
0   11   1          0.1           1
1   12   2          0.2           1
2   16   3          0.3           1
3   19   4          0.4           2
4   14   5          0.5           3
5   9    6          0.6           3
6   4    7          0.7           4
7   13   8          0.8           4
8   6    9          0.9           4
9   18   10         1.0           5

I can do this by looping over the Percent Value column and taking the index of the first value in percentage array which returns True for < condition. I am wondering if there is some simpler way in pandas where I can pass percentage array as the argument to compare the Percent Value column.

My attempt using list comprehension

df['Order'] = [1 + np.where(v <= percentage)[0][0] for v in df['Percent Value']]
pandas dataframe python
4 Answers

Pandas cut could help here; I included a 0 at the start to get ranges less than 0.3

df['Percent Value'] = df.Value.rank(method='dense',pct=True)

percentage = np.array([30, 40, 60, 90, 100])


#get the values in fraction, since percent value is in that format
percentage = percentage/100


#insert a 0 at the start to get the boundary,
#so u'll have a 0 to 0.3 bin, 0.3 to 0.4, 0.4 to 0.6, and so on
#the final value will have the labels based on the bins
df['Order'] = pd.cut(df['Percent Value'],bins=np.insert(percentage,0,0), labels = [1,2,3,4,5])


    ID  Value   Percent Value   Order
0   11    1      0.1            1
1   12    2      0.2            1
2   16    3      0.3            1
3   19    4      0.4            2
4   14    5      0.5            3
5   9     6      0.6            3
6   4     7      0.7            4
7   13    8      0.8            4
8   6     9      0.9            4
9   18    10     1.0            5
user_71757130

Yet another approach.

>>> df
   ID  Value  Percent Value
0  11      1            0.1
1  12      2            0.2
2  16      3            0.3
3  19      4            0.4
4  14      5            0.5
5   9      6            0.6
6   4      7            0.7
7  13      8            0.8
8   6      9            0.9
9  18     10            1.0
>>>
>>> percentage = np.array([30, 40, 60, 90, 100])
>>> pd.cut(df['Percent Value'], bins=[-np.inf, *percentage/100], labels=range(1, len(percentage) + 1))
0    1
1    1
2    1
3    2
4    3
5    3
6    4
7    4
8    4
9    5
Name: Percent Value, dtype: category
Categories (5, int64): [1 < 2 < 3 < 4 < 5]

pandas 1.0.3

user_36200030

You can use np.select:

In [1695]: import numpy as np

In [1702]: conditions = [df['Percent Value'].le(0.30),\ 
  ...: (df['Percent Value'].gt(0.30) & df['Percent Value'].le(0.40)),\ 
  ...: (df['Percent Value'].gt(0.40) & df['Percent Value'].le(0.60)),\ 
  ...: (df['Percent Value'].gt(0.60)& df['Percent Value'].le(0.90)),\ 
  ...: (df['Percent Value'].gt(0.90) & df['Percent Value'].le(1))]

In [1694]: choices = [1,2,3,4,5]

In [1697]: df['Order'] = np.select(conditions, choices)

In [1698]: df 
Out[1698]:
   ID  Value  Percent Value  Order
0  11      1           0.10      1
1  12      2           0.20      1
2  16      3           0.30      1
3  19      4           0.40      2
4  14      5           0.50      3
5   9      6           0.60      3
6   4      7           0.70      4
7  13      8           0.80      4
8   6      9           0.90      4
9  18     10           1.00      5

Just a performance comparison for all answers:

@sammywemmy's answer:

In [1723]: %timeit pd.cut(df['Percent Value'],bins=np.insert(percentage,0,0), labels = [1,2,3,4,5])
1.21 ms ± 57.2 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

@timgeb's answer:

In [1725]: %timeit pd.cut(df['Percent Value'], bins=[-np.inf, *percentage/100], labels=range(1, len(percentage) + 1)) 
1.02 ms ± 64.2 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

My answer:

In [1726]: %timeit np.select(conditions, choices) 
86 µs ± 3.4 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)

I know writing out conditions explicitly is cumbersome, but performance of numpy is very high. Please check above metrics shared per answer.

user_58208140

Using np.where as a CASE statement: 

# Initialise packages in session: 
import pandas as pd
import numpy as np

# Create data: df => data.frame
df = pd.DataFrame({'ID': [11, 12, 16, 19, 14, 9, 4, 13, 6, 18], 
                   'Value': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]})

# Percentage Dense ranke the value vector: Percent Value => float vector
df['Percent Value'] = df.reset_index()['Value'].rank(method = 'dense', pct = True)

# Conditionally define order vector: order => integer vector
df['order'] = np.where(
     df['Percent Value'].between(.0, .30, inclusive = True), 
    1, 
     np.where(
        df['Percent Value'].between(.31, .40, inclusive = True), 2,
         np.where(
             df['Percent Value'].between(.41, .59, inclusive = True), 3, 
             np.where(
                 df['Percent Value'].between(.60, .90, inclusive = True), 4, 5
                 )
             )
         )
     )

# Display data.frame: df => stdout (console)
print(df)
user_92031580
Related