3
$\begingroup$

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?

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

2 Answers 2

1
$\begingroup$

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
$\endgroup$
3
  • $\begingroup$ 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}). $\endgroup$
    – khaldi
    Mar 29, 2018 at 16:43
  • $\begingroup$ @khaldi Please ask that with a new question on Stack Overflow. $\endgroup$
    – tuomastik
    Mar 29, 2018 at 18:23
  • $\begingroup$ Hi @tuomastik, this has already done. $\endgroup$
    – khaldi
    Mar 29, 2018 at 18:58
1
$\begingroup$

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
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.