How to check if any value of a column is in a range (in between two values) in Pandas?

Question

I have a DataFrame, and I would like to check if any of the values (v) of a column satisfies x<=v<=y.

equal = any(df['columnX'] == value) # No problems here
in_between = any(x <= df['columnX'] <= y) # ValueError :/

The error I get is ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all(). But I am using any() already!

So what's the problem here? Why does it work with == but not with x<=v<=y?

score 49 · Accepted Answer · answered Oct 20 '16 at 13:56

49

Use between to do this, it also supports whether the range values are included or not via inclusive arg:

In [130]:
s = pd.Series(np.random.randn(5))
s

Out[130]:
0   -0.160365
1    1.496937
2   -1.781216
3    0.088023
4    1.325742
dtype: float64

In [131]:
s.between(0,1)

Out[131]:
0    False
1    False
2    False
3     True
4    False
dtype: bool

You then call any on the above:

In [132]:
s.between(0,1).any()

Out[132]:
True

answered Oct 20 '16 at 13:56

EdChum

376,765
198
813
562

Would this be correct then? `in_between = any(df['columnX'].between(x, y,inclusive=True))` – alec_djinn Oct 20 '16 at 14:00
2

I'd do `in_between = df['columnX'].between(x, y,inclusive=True).any()` personally but yes that would work – EdChum Oct 20 '16 at 14:02

score 13 · Answer 2 · answered Oct 20 '16 at 13:58

13

You can just have two conditions:

df[(x <= df['columnX']) & (df['columnX'] <= y)]

This line will select all rows in df where the condition is satisfied.

answered Oct 20 '16 at 13:58

Ulf Aslak

7,876
4
34
56

score 6 · Answer 3 · answered Apr 06 '21 at 13:33

6

You can use custom function for running on the dataframe:

df1 = pd.DataFrame({'a':[1,1,2,2], 'b':[1,1,2,2], 'c':[2,-1,4,5]})
myfun = lambda row: row['b']<=row['a']<=row['c']
df1['Result'] = df1.apply(myfun, axis=1)
display(df1)

answered Apr 06 '21 at 13:33

shahar_m

3,461
5
41
61

score 3 · Answer 4 · answered Oct 28 '19 at 23:43

3

If you like to see other column values, you could try

df.loc[ df.loc[:, 'columnX'].between(a, b), : ]

answered Oct 28 '19 at 23:43

Cloud Cho

1,594
19
22

score 1 · Answer 5 · answered Jul 11 '22 at 20:47

Another way is to use query:

In [2]: df = pd.DataFrame({'num': range(0, 5), 'alpha': list('ABCDE')})

In [3]: df
Out[3]: 
   num alpha
0    0     A
1    1     B
2    2     C
3    3     D
4    4     E

In [4]: df.query('1 <= num <= 3')
Out[4]: 
   num alpha
1    1     B
2    2     C
3    3     D

How to check if any value of a column is in a range (in between two values) in Pandas?

5 Answers5

Linked