# How to count occurrences of values within specific range by row

I have a data frame of 3000 rows x 101 columns like as follow:

Time   id0  id1  id2     ………… id99

1      1.71 6.99 4.01    ………… 4.98

2      1.72 6.78 3.15    ………… 4.97

.

.

3000   0.36 0.23 0.14    ………… 0.28


Using Python, how could we add a column that counts for each row the number of values (in column id0, to id99) that are within a specific range?

• This isn't the correct site for asking such things? – Aditya Mar 29 '18 at 7:23
• @Aditya: why question mark? are you asking or are you telling? – Toros91 Mar 29 '18 at 7:54
• Typo but can't be edited now @Toros91 Still too young to recognise that mistake as I myself do it. – Aditya Mar 29 '18 at 8:51

You can apply a function to each row of the DataFrame with apply method. In the applied function, you can first transform the row into a boolean array using between method or with standard relational operators, and then count the True values of the boolean array with sum method.

import pandas as pd

df = pd.DataFrame({
'id0': [1.71, 1.72, 1.72, 1.23, 1.71],
'id1': [6.99, 6.78, 6.01, 8.78, 6.43],
'id2': [3.11, 3.11, 4.99, 0.11, 2.88]})

def count_values_in_range(series, range_min, range_max):

# "between" returns a boolean Series equivalent to left <= series <= right.
# NA values will be treated as False.
return series.between(left=range_min, right=range_max).sum()

# Alternative approach:
# return ((range_min <= series) & (series <= range_max)).sum()

range_min, range_max = 1.72, 6.43

df["n_values_in_range"] = df.apply(
func=lambda row: count_values_in_range(row, range_min, range_max), axis=1)

print(df)


Resulting DataFrame:

    id0   id1   id2  n_values_in_range
0  1.71  6.99  3.11                  1
1  1.72  6.78  3.11                  2
2  1.72  6.01  4.99                  3
3  1.23  8.78  0.11                  0
4  1.71  6.43  2.88                  2

• In fact the measures in the 100 culumns (from id0 to id99) represent distances of agents in a global coordinate frame. Is it possible to generate a heatmap for the last row in a way that the related (100x100) matrix contains the differences in distance between each paire of agent (id_i - id_j , with i and j /in {0..99}). – khaldi Mar 29 '18 at 16:43
• @khaldi Please ask that with a new question on Stack Overflow. – tuomastik Mar 29 '18 at 18:23
• Hi @tuomastik, this has already done. – khaldi Mar 29 '18 at 18:58

IIUC you can use DataFrame.isin() method:

Data:

In [41]: given_set = {3,8,11,18,22,24,35,36,42,47}

In [42]: df
Out[42]:
a   b   c   d   e
0  36  38  27  12  35
1  45  33   8  41  18
4  32  14   4  14   9
5  43   1  31  11   3
6  16   8   3  17  39


Solution:

In [44]: df['new'] = df.isin(given_set).sum(1)

In [45]: df
Out[45]:
a   b   c   d   e  new
0  36  38  27  12  35    2
1  45  33   8  41  18    2
4  32  14   4  14   9    0
5  43   1  31  11   3    2
6  16   8   3  17  39    2


Explanation:

In [49]: df.isin(given_set)
Out[49]:
a      b      c      d      e
0   True  False  False  False   True
1  False  False   True  False   True
4  False  False  False  False  False
5  False  False  False   True   True
6  False   True   True  False  False

In [50]: df.isin(given_set).sum(1)
Out[50]:
0    2
1    2
4    0
5    2
6    2
dtype: int64


if you want check for existence instead of counting, you can do it this way:

In [6]: df.isin(given_set).any(1)
Out[6]:
0     True
1     True
4    False
5     True
6     True
dtype: bool

In [7]: df.isin(given_set).any(1).astype(np.uint8)
Out[7]:
0    1
1    1
4    0
5    1
6    1
dtype: uint8