Delete columns and rows containing specific values in pandas dataframe

Question

I have a pandas dataframe which looks like this (but is actaully much bigger):

           a    b    c    d    e      f      g     h    i    j

       0|  0    1    2    3    4    -500   -500    5    6    7 
       1|  2    3    4    5    6    -500   -500    6    5    4
       2|-500 -500 -500 -500 -500   -500   -500  -500 -500 -500
       3|  3    4    5    2    1    -500   -500    5    3    6

I want to delete only entire rows which contain -500 (2) and entire columns(f and g). My dataframe is generated automatically, and I do not already know which columns and rows contain -500.

Would anyone know a way to do this?

Thanks!

Possible duplicate of [Deleting DataFrame row in Pandas based on column value](https://stackoverflow.com/q/18172851/1278112) — Shihe Zhang, Nov 06 '17 at 05:33

score 4 · Accepted Answer · answered Apr 29 '17 at 12:21

In [76]: mask = df.eq(-500)

In [77]: df.loc[~mask.all(1), ~mask.all()]
Out[77]:
   a  b  c  d  e  h  i  j
0  0  1  2  3  4  5  6  7
1  2  3  4  5  6  6  5  4
3  3  4  5  2  1  5  3  6

or

In [83]: mask = df.ne(-500)

In [85]: df = df.loc[mask.any(1), mask.any()]

In [86]: df
Out[86]:
   a  b  c  d  e  h  i  j
0  0  1  2  3  4  5  6  7
1  2  3  4  5  6  6  5  4
3  3  4  5  2  1  5  3  6

this is how does mask look like:

In [87]: mask
Out[87]:
       a      b      c      d      e      f      g      h      i      j
0   True   True   True   True   True  False  False   True   True   True
1   True   True   True   True   True  False  False   True   True   True
2  False  False  False  False  False  False  False  False  False  False
3   True   True   True   True   True  False  False   True   True   True

Divakar · Answer 2 · 2017-04-29T12:59:08.423

Here's a NumPy approach meant for performance specifically for such cross-dimensional selection being efficiently performed with open 1D arrays using numpy.ix_ -

def delete_rows_cols(df):
    a = df.values
    mask = a!=-500
    m0 = mask.any(0)
    m1 = mask.any(1)
    return pd.DataFrame(a[np.ix_(m1,m0)], df.index[m1], df.columns[m0])

Sample run -

In [255]: df
Out[255]: 
     a    b    c    d    e    f    g    h    i    j
0    0    1    2    3    4 -500 -500    5    6    7
1    2    3    4    5    6 -500 -500    6    5    4
2 -500 -500 -500 -500 -500 -500 -500 -500 -500 -500
3    3    4    5    2    1 -500 -500    5    3    6

In [256]: delete_rows_cols(df)
Out[256]: 
   a  b  c  d  e  h  i  j
0  0  1  2  3  4  5  6  7
1  2  3  4  5  6  6  5  4
3  3  4  5  2  1  5  3  6

Runtime test -

# Setup input dataframe
In [257]: arr = np.random.randint(0,100,(1000,1000))

In [258]: arr[:,np.random.choice(1000,100,replace=0)] = -500

In [259]: arr[np.random.choice(1000,100,replace=0)] = -500

In [260]: df = pd.DataFrame(arr)

# @MaxU's pandas soln step-1
In [262]: mask = df.ne(-500)

In [263]: %timeit df.ne(-500)
1000 loops, best of 3: 606 µs per loop

# @MaxU's pandas soln step-2
In [264]: %timeit df.loc[mask.any(1), mask.any()]
10 loops, best of 3: 21.1 ms per loop

In [261]: %timeit delete_rows_cols(df)
100 loops, best of 3: 3.75 ms per loop

Delete columns and rows containing specific values in pandas dataframe

2 Answers2