Florian Gauthier Florian Gauthier - 4 months ago 11
Python Question

Check if value is in categorical series of float range

I got the following pandas DataFrame :

bucket value
0 (15016, 18003.2] 368
1 (12028.8, 15016] 132
2 (18003.2, 20990.4] 131
3 (9041.6, 12028.8] 116
4 (50.128, 3067.2] 82
5 (3067.2, 6054.4] 79
6 (6054.4, 9041.6] 54
7 (20990.4, 23977.6] 28
8 (23977.6, 26964.8] 8
9 (26964.8, 29952] 2


buckets
have been computed with
pd.cut()
command (dtype is
cateogry
)

I would like to check if a value, let's say
my_value = 20000
, is in one of
bucket
's range.

It could return a dataframe with one more column :

bucket value value_in_bucket
0 (15016, 18003.2] 368 FALSE
1 (12028.8, 15016] 132 FALSE
2 (18003.2, 20990.4] 131 TRUE
3 (9041.6, 12028.8] 116 FALSE
4 (50.128, 3067.2] 82 FALSE
5 (3067.2, 6054.4] 79 FALSE
6 (6054.4, 9041.6] 54 FALSE
7 (20990.4, 23977.6] 28 FALSE
8 (23977.6, 26964.8] 8 FALSE
9 (26964.8, 29952] 2 FALSE


The main problem is that each item of
bucket
is a string, so I could split the string into 2 columns and use a basic test and an
apply
but it does not seem so classy to me.

Answer

you can apply pd.cut() using the same bins (or, better, as @ayhan suggested save bins when you create bucket column, using retbins=True parameter) on value column and compare it to the bucket column.

Demo:

In [265]: df = pd.DataFrame(np.random.randint(1,20, 5), columns=list('a'))

In [266]: df
Out[266]:
    a
0   9
1   6
2  13
3  11
4  17

create bucket column and save bins in one step:

In [267]: df['bucket'], bins = pd.cut(df.a, bins=5, retbins=True)

In [268]: df
Out[268]:
    a        bucket
0   9   (8.2, 10.4]
1   6  (5.989, 8.2]
2  13  (12.6, 14.8]
3  11  (10.4, 12.6]
4  17    (14.8, 17]

In [269]: bins
Out[269]: array([  5.989,   8.2  ,  10.4  ,  12.6  ,  14.8  ,  17.   ])

generate a new column which we want to compare:

In [270]: df['b'] = np.random.randint(10,12, 5)

In [271]: df
Out[271]:
    a        bucket   b
0   9   (8.2, 10.4]  10
1   6  (5.989, 8.2]  11
2  13  (12.6, 14.8]  11
3  11  (10.4, 12.6]  11
4  17    (14.8, 17]  11

compare whether we have matches (using saved bins):

In [272]: pd.cut(df.b, bins=bins) == df.bucket
Out[272]:
0     True
1    False
2    False
3     True
4    False
dtype: bool