5

I have a dataframe as such:

     col0   col1  col2  col3
ID1    0      2     0     2
ID2    1      1     2     10
ID3    0      1     3     4

I want to remove rows that contain zeros more than once.

I've tried to do:

cols = ['col1', etc]
df.loc[:, cols].value_counts()

But this only works for series and not dataframes.

df.loc[:, cols].count(0) <= 1

Only returns bools.

I feel like I'm close with the 2nd attempt here.

PeptideWitch
  • 2,239
  • 14
  • 30

3 Answers3

8

Apply the condition and count the True values.

(df == 0).sum(1)

ID1    2
ID2    0
ID3    1
dtype: int64

df[(df == 0).sum(1) < 2]

     col0  col1  col2  col3
ID2     1     1     2    10
ID3     0     1     3     4

Alternatively, convert the integers to bool and sum that. A little more direct.

# df[(~df.astype(bool)).sum(1) < 2]
df[df.astype(bool).sum(1) > len(df.columns)-2]  # no inversion needed

     col0  col1  col2  col3
ID2     1     1     2    10
ID3     0     1     3     4

For performance, you can use np.count_nonzero:

# df[np.count_nonzero(df, axis=1) > len(df.columns)-2]
df[np.count_nonzero(df.values, axis=1) > len(df.columns)-2]

     col0  col1  col2  col3
ID2     1     1     2    10
ID3     0     1     3     4

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

%timeit df[(df == 0).sum(1) < 2]
%timeit df[df.astype(bool).sum(1) > len(df.columns)-2]
%timeit df[np.count_nonzero(df.values, axis=1) > len(df.columns)-2]

7.13 ms ± 161 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
4.28 ms ± 120 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
997 µs ± 38.2 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
cs95
  • 379,657
  • 97
  • 704
  • 746
4

Using 

df.loc[df.eq(0).sum(1).le(1),]
     col0  col1  col2  col3
ID2     1     1     2    10
ID3     0     1     3     4

A fun way 

df.mask(df.eq(0)).dropna(0, thresh=df.shape[1] - 1).fillna(0)
     col0  col1  col2  col3
ID2   1.0     1   2.0    10
ID3   0.0     1   3.0     4
BENY
  • 317,841
  • 20
  • 164
  • 234
  • Are you using `loc` to [avoid the SettingWithCopyWarning](https://stackoverflow.com/questions/20625582/how-to-deal-with-settingwithcopywarning-in-pandas/53954986#53954986)? – cs95 Apr 13 '19 at 00:50
  • Method #1 works but only returns the IDs of the dataset, so you'd have to wrap this inside a condition to filter out the dataframe to begin with. Still, neat answer. Ty :) – PeptideWitch Apr 13 '19 at 01:05
0
df.replace(0, np.nan, inplace=True)
df.dropna(subset=df.columns, thresh=2, inplace=True)
df.fillna(0., inplace=True)
vinu
  • 457
  • 4
  • 11