Filtering rows based on specific values: Pandas

user_14882883 0
Viewed 115

I have the following dataframe.

ID Value1  Value2  Value3  Value4  Value5
1   0        0       50      100     0
2   0        0       0        0      0 
3   0        0       0        50     0
4   0        100     0        0      0 
5   50        0      100      50     50

I would like to filter IDs which has only one non-zero values. For example ID == 1 has two non-zero values but ID == 3 and 4 has only one non-zero values and I would like to filter them.

The expected output is ID 3 and 4.

ID Value1  Value2  Value3  Value4  Value5
3   0        0       0        50     0
4   0        100     0        0      0

Can anyone help on this?

pandas python
3 Answers

Solutions if ID is column with boolean indexing and count not 0 values by compare and sum:

df[df.drop('ID', 1).ne(0).sum(axis=1).eq(1)]

Or if need omit first column:

df[df.iloc[:, 1:].ne(0).sum(axis=1).eq(1)]

If ID is index:

df[df.ne(0).sum(axis=1).eq(1)]
user_29010020

use a boolean filter with .set_index

you can filter the index by using .loc

df.set_index('ID').loc[df.set_index('ID').gt(0).sum(axis=1).eq(1)]


    Value1  Value2  Value3  Value4  Value5
ID
3        0       0       0      50       0
4        0     100       0       0       0

print(df.set_index('ID').gt(0).sum(axis=1).eq(1))

ID
1    False
2    False
3     True # <-- filter this row
4     True # <-- and this.
5    False
dtype: bool

another way using .filter

df.loc[df.filter(like='Val').gt(0).sum(axis=1).eq(1)]
user_93751020 
df1.set_index('ID')[(df1.set_index('ID')>0).sum(1)==1]
user_89869750
Related