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!
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 
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 