How to remove rows from a DataFrame where some columns only have zero values

Question

I have the following Pandas DataFrame in Python:

import numpy as np
import pandas as pd
df  = pd.DataFrame(np.array([[1, 2, 3, 4, 5, 6], [11, 22, 33, 44, 55, 66], 
                             [111, 222, 0, 0, 0, 0], [1111, 0, 0, 0, 0, 0]]),
                   columns=['a', 'b', 'c', 'd', 'e', 'f'])

DataFrame looks as the following in a table:

    a      b     c    d     e    f
0   1      2     3    4     5    6
1   11     22    33   44    55   66
2   111    222   0    0     0    0
3   1111   2222  0    0     0    0

The original DataFrame is much bigger than this. As seen, some rows have zero values in some columns (c, d, e, f).

I need to remove these columns from the DataFrame so that my new DataFrame will look as the following (after removing rows where given columns are zeros only):

    a      b     c    d     e    f
0   1      2     3    4     5    6
1   11     22    33   44    55   66

And I only need to remove the rows where all these column (c, d, e, and f) are zeros. If, for example, 2 of them are 0, then I will not remove such rows.

Is there a good way of doing this operation without looping through the DataFrame?

thorntonc · Accepted Answer · 2020-08-11 15:35:26Z

3

Row filtering on selected columns, any have zeroes with any:

import numpy as np
import pandas as pd

df  = pd.DataFrame(np.array([[1, 2, 3, 4, 5, 6], [11, 22, 33, 44, 55, 66],
                             [111, 222, 0, 0, 0, 0], [1111, 0, 0, 0, 0, 0]]),
                   columns=['a', 'b', 'c', 'd', 'e', 'f'])

df = df[(df[['c', 'd', 'e', 'f']] != 0).any(axis=1)]

print(df)

Output:

    a   b   c   d   e   f
0   1   2   3   4   5   6
1  11  22  33  44  55  66

edited Aug 11, 2020 at 15:35

answered Aug 11, 2020 at 15:24

thorntonc

2,1261 gold badge10 silver badges20 bronze badges

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

2 Comments

edn Over a year ago

thank you for your answer. The solution only needs to remove rows where all given columns are zeros. If, for example, 3 of them are zeros, then the code should not touch such rows. I tried your solution by changing the 3rd row to [111, 222, 333, 0, 0, 0] but it removed this row as well but it needed to have it left in the table.

thorntonc Over a year ago

@edn Fixed just now.

geekzeus · Accepted Answer · 2020-08-11 15:53:31Z

2

with operators

df.loc[~((((df['c'] == 0) & (df['d'] == 0)) & (df['e'] == 0)) & (df['f'] == 0))]

answered Aug 11, 2020 at 15:53

geekzeus

8958 silver badges15 bronze badges

Comments

sushanth · Accepted Answer · 2020-08-11 15:26:34Z

1

try this,

df[~df[list('cdef')].eq(0).all(axis = 1)]

    a   b   c   d   e   f
0   1   2   3   4   5   6
1  11  22  33  44  55  66

edited Aug 11, 2020 at 15:26

answered Aug 11, 2020 at 15:22

sushanth

8,2923 gold badges20 silver badges31 bronze badges

3 Comments

edn Over a year ago

thank you for your answer. This code removes any row where any column is zero. It is ok if one or two columns are zero. But in my case, all c, d, e, f columns needs to be zero. Updated the question now.

sushanth Over a year ago

@edn, boolean mask on c,d,e,f followed by all with axis=1 to ensure all values are true.

edn Over a year ago

Thank you @Sushanth for your answer.

mosc9575 · Accepted Answer · 2020-08-11 15:44:32Z

1

Here is one more option: Use df.query() with an self defined query.

my_query = '~('+'and '.join([f'{name}==0' for name in 'cdef'])+')'
df.query(my_query)

If you print my_query it is easy to read: ~(c==0 and d==0 and e==0 and f==0) with ~ means 'not'.

edited Aug 11, 2020 at 15:44

answered Aug 11, 2020 at 15:30

mosc9575

6,3922 gold badges12 silver badges36 bronze badges

Collectives™ on Stack Overflow

How to remove rows from a DataFrame where some columns only have zero values

4 Answers 4

2 Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related