18

The request is simple: I want to select all rows which contain a value greater than a threshold.

If I do it like this:

df[(df > threshold)]

I get these rows, but values below that threshold are simply NaN. How do I avoid selecting these rows?

miradulo
  • 28,857
  • 6
  • 80
  • 93
Stefan Falk
  • 23,898
  • 50
  • 191
  • 378

2 Answers2

32

There is absolutely no need for the double transposition - you can simply call any along the column index (supplying 1 or 'columns') on your Boolean matrix. 

df[(df > threshold).any(1)]

Example

>>> df = pd.DataFrame(np.random.randint(0, 100, 50).reshape(5, 10))

>>> df

    0   1   2   3   4   5   6   7   8   9
0  45  53  89  63  62  96  29  56  42   6
1   0  74  41  97  45  46  38  39   0  49
2  37   2  55  68  16  14  93  14  71  84
3  67  45  79  75  27  94  46  43   7  40
4  61  65  73  60  67  83  32  77  33  96

>>> df[(df > 95).any(1)]

    0   1   2   3   4   5   6   7   8   9
0  45  53  89  63  62  96  29  56  42   6
1   0  74  41  97  45  46  38  39   0  49
4  61  65  73  60  67  83  32  77  33  96

Transposing as your self-answer does is just an unnecessary performance hit. 

df = pd.DataFrame(np.random.randint(0, 100, 10**8).reshape(10**4, 10**4))

# standard way
%timeit df[(df > 95).any(1)]
1 loop, best of 3: 8.48 s per loop

# transposing
%timeit df[df.T[(df.T > 95)].any()]
1 loop, best of 3: 13 s per loop
miradulo
  • 28,857
  • 6
  • 80
  • 93
  • 1
    Super, nice answer. – jezrael Mar 05 '17 at 20:42
  • I'd like to add some clarification for others learning Pandas. Initially I thought it should read: `any(0)` but I guess in this context you should interpret it like this: axis=0 means ALONG or ACROSS the row axis (i.e. every value along a given column as you read along the row axis) and axis=1 means ALONG or ACROSS the column axis (aka every value along a given row as you read across the column axis). This other [SO](https://stackoverflow.com/a/22149930/295019) question sort of put it in perspective for me. – racl101 Apr 16 '18 at 21:04
  • What if you want to select those columns that are above the threshold, keeping all rows? For some reason, changing `.any(1)` into `.any(0)` doesn't do that for me. In fact, on my df it gives me an indexing error (`IndexingError: Unalignable boolean Series provided as indexer`) – skleijn Nov 30 '21 at 19:57
1

This is actually very simple:

df[df.T[(df.T > 0.33)].any()]
Stefan Falk
  • 23,898
  • 50
  • 191
  • 378