Remove columns and rows containing specific values in pandas dataframe
I have a pandas framework that looks like this (but it's much more):
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 only want to delete whole rows containing -500 (2) and whole columns (f and g). My dataframe is auto-generated and I don't know yet which columns and rows contain -500.
Does anyone know a way to do this?
Thank!
+3
source to share
2 answers
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 what it looks like mask
:
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
+4
source to share
Here's a NumPy approach designed for performance, specifically for this kind of cross-dimensional selection, efficiently done with open arrays 1D
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])
Example 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 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 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
+1
source to share