Pandas action on column between two numbers

user_6724526 0
Viewed 376

Currently using Pandas and Numpy. I have a dataframe named 'df'. Lets say I have the below data, how can I give the third column a value based on a between clause? I'd like to treat this as a vectorised approach if possible to maintain the speed of what I already have.

I've tried lambda functions, but frankly I don't understand what I'm doing and I'm getting errors such as the object has no attribute 'between'.

General approach - using a non vectorised approach: 

NOTE: I am looking for a way to make this vectorised.

If df.['Col2'] is between 0 and 10
   df.['Col 3'] = 1
Elseif df.['Col2'] is between 10.01 and 20
   df.['Col3']  = 2
Else if df.['Col2'] is between 20.1 and 30
   df.['Col3']  = 3

Sample set

+------+------+------+
| Col1 | Col2 | Col3 |
+------+------+------+
| a    |    5 |    1 |
| b    |   10 |    1 |
| c    |   15 |    2 |
| d    |   20 |    2 |
| e    |   25 |    3 |
| f    |   30 |    3 |
| g    |    1 |    1 |
| h    |   11 |    2 |
| i    |   21 |    3 |
| j    |    7 |    1 |
+------+------+------+

Many thanks

pandas dataframe python-3.x python
4 Answers

Solution reusing your current code:

def cust_func(row):
    r = row['Col2']
    if  r >=0 AND r<=10:
        val = 1
    elif r >=10.01 AND r<=20:
        val = 2
    elseif r>=20.01 AND r<=30:
        val = 3
    return val

df['Col3'] = df.apply(cust_func, axis=1)

Optimal solution:

cut_labels = [1, 2, 3]
cut_bins = [0, 10, 20,30]
df['Col3'] = pd.cut(df['Col2'], bins=cut_bins, labels=cut_labels)
user_27721180

There are a couple of ways: numpy select and numpy.searchsorted; I prefer the latter as I don't have to list out the conditions - it works on the bisect algorithm, as long as your data is sorted; and yes, I'd like to think it is the fastest of the bunch.

It would be cool if you ran some timings and shared the results:

  Col1  Col2
0   a   5
1   b   10
2   c   15
3   d   20
4   e   25
5   f   30
6   g   1
7   h   11
8   i   21
9   j   7

   #step 1: create your 'conditions'

#sort dataframe on Col2

df = df.sort_values('Col2')
#benchmarks are ur ranges within which you set your scores/grade
benchmarks = np.array([10,20,30])

#the grades to be assigned for Col2
score = np.array([1,2,3])

#and use search sorted
#it will generate the indices for where the values should be
#e.g if you have [1,4,5] then the position of 3 will be 1, since it is between 1 and 4
#and python has a zero based index notation
indices = np.searchsorted(benchmarks,df.Col2)

#create ur new column by indexing the score array with the indices
df['Col3'] = score[indices]

df = df.sort_index()

df

    Col1    Col2  Col3
0    a       5      1
1    b       10     1
2    c       15     2
3    d       20     2
4    e       25     3
5    f       30     3
6    g       1      1
7    h       11     2
8    i       21     3
9    j       7      1
user_71757130

Please Try,Boolean select

a=df['Col2'].between(0,10)
b=df['Col2'].between(10.01,20)
c=df['Col2'].between(20.1,30)

Apply np.where

import numpy as np
df['Col3']  =np.where(a,1,(np.where(b,2,(np.where(c,3,df['Col3'] )))))

Output

enter image description here

user_89869750

You can do this nice and cleanly with np.select(). I added some <= because I guessed you wanted all the values updated. But it's an easy edit if needed. 

conditions = [(df['Col2'] > 0) & (df['Col2'] <= 10),
               (df['Col2'] > 10) & (df['Col2'] <= 20),
               (df['Col2'] > 20) & (df['Col2'] <= 30) ]

updates = [1, 2, 3]

df["Col3"] = np.select(conditions, updates, default=999)

Using your original range would result in this, where the values == 10, 20, 30 get the value 999 from np.select().

conditions = [(df['Col2'] > 0) & (df['Col2'] < 10),
               (df['Col2'] > 10.01) & (df['Col2'] < 20),
               (df['Col2'] > 20.1) & (df['Col2'] < 30) ]

updates = [1, 2, 3]

df["Col3"] = np.select(conditions, updates, default=999)

print(df)

    Col1    Col2    Col3
0   a   5   1
1   b   10  999
2   c   15  2
3   d   20  999
4   e   25  3
5   f   30  999
6   g   1   1
7   h   11  2
8   i   21  3
9   j   7   1
user_73274830
Related