Find rows with non zero values in a subset of columns in pandas dataframe

Question

I have a datframe with 4 columns of strings and others as integers. Now I need to find out those rows of data where at least one of the column is a non-zero value (or > 0).

manwra,sahAyaH,T7,0,0,0,0,T
manwra, akriti,T5,0,0,1,0,K 
awma, prabrtih,B6, 0,1,1,0,S

My output should be

manwra, akriti,T5,0,0,1,0,K 
awma, prabrtih,B6, 0,1,1,0,S

I have tried the following to obtain the answer. The string values are in colums 0,1,2 and -1 (last column).

KT[KT.ix[:,3:-2] != 0]

What I am receiving as output is

NaN,NaNNaN,NaN,NaN,NaN,NaN,NaN
NaN,NaN,NaN,NaN,NaN,1,NaN,NaN
NaN,NaN,NaN,NaN,1,1,NaN,NaN

How to obtain the desired output

MaxU - stand with Ukraine · Accepted Answer · 2016-08-28 10:50:09Z

12

Here is an alternative solution which uses select_dtypes() method:

In [41]: df[(df.select_dtypes(include=['number']) != 0).any(1)]
Out[41]:
        0          1   2  3  4  5  6  7
1  manwra     akriti  T5  0  0  1  0  K
2    awma   prabrtih  B6  0  1  1  0  S

Explanation:

In [42]: df.select_dtypes(include=['number']) != 0
Out[42]:
       3      4      5      6
0  False  False  False  False
1  False  False   True  False
2  False   True   True  False

In [43]: (df.select_dtypes(include=['number']) != 0).any(1)
Out[43]:
0    False
1     True
2     True
dtype: bool

answered Aug 28, 2016 at 10:50

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

piRSquared Over a year ago

This is a better answer

piRSquared · Accepted Answer · 2016-08-28 03:41:50Z

7

assume your dataframe is df

df.loc[(df.loc[:, df.dtypes != object] != 0).any(1)]

answered Aug 28, 2016 at 3:41

piRSquared

296k68 gold badges509 silver badges654 bronze badges

4 Comments

Merlin Over a year ago

@piRSqaured can a derivative of this be used for stackoverflow.com/questions/39184442/… ... I got df.ix[:, df.dtypes == object ] ... couldnt apply back to DF....

piRSquared Over a year ago

@Merlin TL;DR I don't think so at least not directly. The core issue of the linked question is making an attempt to convert strings to numeric. I was explicitly trusting that dtypes are accurate and have no interest in converting them. If you applied this to the linked question, you'd miss the point of converting the string "2.0" to the float 2.0.

Merlin Over a year ago

Thanks, ok, was looking for replacement for convert_objects ( convert_numeric = True )

piRSquared Over a year ago

@Merlin what about pd.to_numeric(df.loc[:, df.dtypes == object].stack(), 'coerce').unstack()

Merlin · Accepted Answer · 2016-08-28 04:44:10Z

2

You were close:

 #your's    
 KT[KT.ix[:,3:-2] != 0]

 #works 
 KT[(KT.ix[:,3:6] > 0).any(1)]

        0         1   2  3  4  5  6   7
1  manwra    akriti  T5  0  0  1  0  K 
2    awma  prabrtih  B6  0  1  1  0  S

 #key diff
 (KT.ix[:,3:6] > 0)
       3      4      5      6
0  False  False  False  False
1  False  False   True  False
2  False   True   True  False

answered Aug 28, 2016 at 4:44

Merlin

25.9k44 gold badges141 silver badges213 bronze badges

Comments

Magnus Persson · Accepted Answer · 2021-09-29 09:54:43Z

Alternative solution

An alternative solution, which could be useful in certain situations. For example when plotting something and you do not want to create new copies of DataFrames just to filter out some rows and plot it. An added bonus is that the column dtypes in this solution are not limited to numbers, wherever you can do a comparison you can just add it to the eval call.

Guided answer from the start, first to read the data I use the pandas.read_clipboard() function.

c0 c1 c2 c3 c4 c5 c6 c7
manwra sahAyaH T7 0 0 0 0 T
manwra akriti T5 0 0 1 0 K 
awma prabrtih B6 0 1 1 0 S

I added column names c0-c7 here. Just select this table and copy Ctrl+C it to the clipboard.

import pandas as pd 
df = pd.read_clipboard()

and then by chaining an .eval() with a .query() on the DataFrame

df.eval("all_zero = (c3==0)&(c4==0)&(c5==0)&(c6==0)").query("all_zero==False")

we get the desired results.

	c0	c1	c2	c3	c4	c5	c6	c7	all_zero
1	manwra	akriti	T5	0	0	1	0	K	False
2	awma	prabrtih	B6	0	1	1	0	S	False

In the eval call we are just doing comparisons on the columns we want to be zero, if they are all zero the & sign will combine them to True (1) in column all_zero, then we can query the results and say we only want rows where this new column is False (we could as well say all_zero!=True, or changed the comparison in the eval call).

If you do not want the all_zero column just drop that as well

df.eval("all_zero = (c3==0)&(c4==0)&(c5==0)&(c6==0)").query("all_zero==False").drop('all_zero',axis=1)

	c0	c1	c2	c3	c4	c5	c6	c7
1	manwra	akriti	T5	0	0	1	0	K
2	awma	prabrtih	B6	0	1	1	0	S

Timings / Speed comparison

Comparing the speed of the solutions on a bigger DataFrame of same layout we see that my solution is slower (as expected), but it has some added benefits in terms of filtering more broadly. To compare timings I replicated the DataFrame so that it contains 30 000 rows instead

df = df.append([df]*(10000-1),ignore_index=True)

and then ran the different solutions on it and timed it.

I will leave my solution here for reference!

Collectives™ on Stack Overflow

Find rows with non zero values in a subset of columns in pandas dataframe

4 Answers 4

1 Comment

4 Comments

Comments

Alternative solution

Timings / Speed comparison

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

4 Comments

Comments

Alternative solution

Timings / Speed comparison

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related