CODEWITHSUNDEEP
pythonpandas
Hiwot
Hiwot

Reputation: 588

Filtering rows based on specific values: Pandas

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?

Upvotes: 1

Views: 245

Answers (3)

jezrael
jezrael

Reputation: 862511

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)]

Upvotes: 3

wwnde
wwnde

Reputation: 26676

df1.set_index('ID')[(df1.set_index('ID')>0).sum(1)==1]

Upvotes: 1

Umar.H
Umar.H

Reputation: 23099

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)]

Upvotes: 1

Related Questions