Deleting DataFrame rows in Pandas based on column value - multiple values to remove

Question

I have a list of values (not known in advance, in a Python list) that a column in my Panda DataFrame must not have for all rows.

All recipes on the Web (like this one) show how to do it with only one value to exclude, but I have multiple values to exclude. How do I do it?

Please note that I cannot hardcode the values to exclude in my code.

Thanks!

Answer 1

You can do negative isin() indexing:

In [57]: df
Out[57]:
   a  b  c
0  1  2  2
1  1  7  0
2  3  7  1
3  3  2  7
4  1  3  1
5  3  4  2
6  0  7  1
7  5  4  3
8  6  1  0
9  3  2  0

In [58]: my_list = [1, 7, 8]

In [59]: df.loc[~df.b.isin(my_list)]
Out[59]:
   a  b  c
0  1  2  2
3  3  2  7
4  1  3  1
5  3  4  2
7  5  4  3
9  3  2  0

or using query() function:

In [60]: df.query('@my_list not in b')
Out[60]:
   a  b  c
0  1  2  2
3  3  2  7
4  1  3  1
5  3  4  2
7  5  4  3
9  3  2  0

Answer 2

you could also use np.in1d. From https://stackoverflow.com/a/38083418/2336654

For your use case:

df[~np.in1d(df.b, my_list)]

Demonstration

from string import ascii_letters, ascii_lowercase, ascii_uppercase
df = pd.DataFrame({'lower': list(ascii_lowercase), 'upper': list(ascii_uppercase)}).head(6)
exclude = list(ascii_uppercase[:6:2])

print df

  lower upper
0     a     A
1     b     B
2     c     C
3     d     D
4     e     E
5     f     F

print exclude

['A', 'C', 'E']

print df[~np.in1d(df.upper, exclude)]

  lower upper
1     b     B
3     d     D
5     f     F

---

Timing

Various methods

With 1.3 Million rows excluding 3 items

1.3 Million rows excluding 12 items

Conclusion

Clearly, isin and query scale better in this case.

Answer 3

I decided to add another answer with timings for different methods and different dtypes - it would be too long for one answer...

Timings against 1M rows DF for the following dtypes: int32, int64, float64, object (string):

In [207]: result
Out[207]:
                              int32  int64  float  string
method
df[~np.in1d(df.col, excl)]      249    271    307    2420
df[~df.col.isin(excl)]          414    210    514     189
df.ix[~df.col.isin(excl)]       399    212    553     189
df.query('@excl not in col')    415    228    563     206

In [208]: result.T
Out[208]:
method  df[~np.in1d(df.col, excl)]  df[~df.col.isin(excl)]  df.ix[~df.col.isin(excl)]  df.query('@excl not in col')
int32                          249                     414                        399                           415
int64                          271                     210                        212                           228
float                          307                     514                        553                           563
string                        2420                     189                        189                           206

Raw results:

int32:

In [159]: %timeit df[~np.in1d(df.int32, exclude_int32)]
1 loop, best of 3: 249 ms per loop

In [160]: %timeit df[~df.int32.isin(exclude_int32)]
1 loop, best of 3: 414 ms per loop

In [161]: %timeit df.ix[~df.int32.isin(exclude_int32)]
1 loop, best of 3: 399 ms per loop

In [162]: %timeit df.query('@exclude_int32 not in int32')
1 loop, best of 3: 415 ms per loop

int64:

In [163]: %timeit df[~np.in1d(df.int64, exclude_int64)]
1 loop, best of 3: 271 ms per loop

In [164]: %timeit df[~df.int64.isin(exclude_int64)]
1 loop, best of 3: 210 ms per loop

In [165]: %timeit df.ix[~df.int64.isin(exclude_int64)]
1 loop, best of 3: 212 ms per loop

In [166]: %timeit df.query('@exclude_int64 not in int64')
1 loop, best of 3: 228 ms per loop

float64:

In [167]: %timeit df[~np.in1d(df.float, exclude_float)]
1 loop, best of 3: 307 ms per loop

In [168]: %timeit df[~df.float.isin(exclude_float)]
1 loop, best of 3: 514 ms per loop

In [169]: %timeit df.ix[~df.float.isin(exclude_float)]
1 loop, best of 3: 553 ms per loop

In [170]: %timeit df.query('@exclude_float not in float')
1 loop, best of 3: 563 ms per loop

object / string:

In [171]: %timeit df[~np.in1d(df.string, exclude_str)]
1 loop, best of 3: 2.42 s per loop

In [172]: %timeit df[~df.string.isin(exclude_str)]
10 loops, best of 3: 189 ms per loop

In [173]: %timeit df.ix[~df.string.isin(exclude_str)]
10 loops, best of 3: 189 ms per loop

In [174]: %timeit df.query('@exclude_str not in string')
1 loop, best of 3: 206 ms per loop

Conclusion:

np.in1d() - wins for (int32 and float64) searches, but it's approx. 10 times slower (compared to others) when searching strings, so don't use it for object (strings) and for int64 dtypes!

Setup:

df = pd.DataFrame({
    'int32':    np.random.randint(0, 10**6, 10),
    'int64':    np.random.randint(10**7, 10**9, 10).astype(np.int64)*10,
    'float':    np.random.rand(10),
    'string':   np.random.choice([c*10 for c in string.ascii_uppercase], 10),
    })


df = pd.concat([df] * 10**5, ignore_index=True)

exclude_str = np.random.choice([c*10 for c in string.ascii_uppercase], 100).tolist()
exclude_int32 = np.random.randint(0, 10**6, 100).tolist()
exclude_int64 = (np.random.randint(10**7, 10**9, 100).astype(np.int64)*10).tolist()
exclude_float = np.random.rand(100)


In [146]: df.shape
Out[146]: (1000000, 4)

In [147]: df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1000000 entries, 0 to 999999
Data columns (total 4 columns):
float     1000000 non-null float64
int32     1000000 non-null int32
int64     1000000 non-null int64
string    1000000 non-null object
dtypes: float64(1), int32(1), int64(1), object(1)
memory usage: 26.7+ MB

In [148]: df.head()
Out[148]:
      float   int32       int64      string
0  0.221662  283447  6849265910  NNNNNNNNNN
1  0.276834  455464  8785039710  AAAAAAAAAA
2  0.517846  618887  8653293710  YYYYYYYYYY
3  0.318897  363191  2223601320  PPPPPPPPPP
4  0.323926  777875  5357201380  QQQQQQQQQQ