Row filtering so that we only keep finite entries

Question

I found this recipe to keep finite entries in my dataframe.

The formula is:

df[df == np.Inf] = np.NaN
df.dropna()

However, when I try it:

In: df[df == np.Inf] = np.NaN

## -- End pasted text --
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-4-88eed8630e79> in <module>()
----> 1 df[df == np.Inf] = np.NaN

/Users/josh/anaconda/envs/py27/lib/python2.7/site-packages/pandas/core/frame.pyc in __setitem__(self, key, value)

/Users/josh/anaconda/envs/py27/lib/python2.7/site-packages/pandas/core/frame.pyc in _setitem_frame(self, key, value)

TypeError: Cannot do boolean setting on mixed-type frame

Are there any better recipes to filter rows so that we only keep finite entries in a specific column?

Paul H · Answer 1 · 2014-05-28T23:25:28.923

2

Use np.isinf()

x = pandas.DataFrame([
    [1, 2, np.inf],
    [4, np.inf, 5],
    [6, 7, 8]
])
x[np.isinf(x)] = np.nan
print(x)

   0   1   2
0  1   2 NaN
1  4 NaN   5
2  6   7   8

so then x.dropna() gives me:

   0  1  2
2  6  7  8

To look at only subset of columns, use the subset kwarg (always takes a list):

x.dropna(subset=[1])
   0   1   2
0  1   2 NaN
2  6   7   8

You can also take DSM's advice and just index the dataframe directly: x[~np.isinf(x).any(axis=1)]

edited May 28 '14 at 23:25

answered May 28 '14 at 22:04

Paul H

65,268
20
159
136

2

You could also use `isinf` without using it to replace values, e.g. `x[~np.isinf(x).any(axis=1)]`. – DSM May 28 '14 at 22:09
Hmm @DSM I get: `TypeError: ufunc 'isinf' not supported for the input types` – Amelio Vazquez-Reina Jun 02 '14 at 15:47
show me an example dataframe and code to reproduce that and we should be able to get use squared away. (@user815423426) – Paul H Jun 02 '14 at 16:23

score 2 · Accepted Answer · answered Jun 02 '14 at 17:02

As suggested here you can use mode.use_inf_as_null:

In [14]: df = DataFrame({'a': randint(3,size=10)})

In [15]: df['b'] = tm.choice([2,3,nan,inf,-inf], size=len(df))

In [16]: df
Out[16]:
   a       b
0  1     inf
1  2    -inf
2  0  3.0000
3  1    -inf
4  2     NaN
5  1  3.0000
6  1     inf
7  0  2.0000
8  2    -inf
9  2     inf

In [17]: with pd.option_context('mode.use_inf_as_null', True):
   ....:     res = df.dropna()
   ....:

In [18]: res
Out[18]:
   a  b
2  0  3
5  1  3
7  0  2

Row filtering so that we only keep finite entries

2 Answers2