delete CSV rows with conditional in python

Question

I have a csv file with the following:

storeNumber, sale1, sale2
1, 1, 1
2, 0, 0
3, 1, 0
4, 0, 1
...
25, 0, 0
26, 1, 0
27, 0, 1
28, 0,0

I need to delete rows with sale1 and sale2 that are equal to 0.

I have the following code setup:

import pandas as pd
df = pd.read_csv('sales.csv', index_col=0)

df_new = df[df.sale1 != 0] and df[df.sale2 != 0]

print(df_new)

the code works if I will only delete one of each column that has 0 value.

df_new = df[df.sale1 != 0]

or

df_new = df[df.sale2 != 0]

However, when put the code above with the "and", I get an error that says:

ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

what is the right code for deleting rows that have 0 value for both sale1 and sale2?

Make sure the code doesn't have a typo: in your example with the and you use df.KC_2 on both sides of the statement. — sal
– sal, Commented Mar 1, 2021 at 2:54

bb1 · Accepted Answer · 2021-03-01 16:51:07Z

1

To operator you need to use to combine the two logical conditions is & instead of and. This is explained in detail here. So, what you need is:

df_new = df[(df.sale1 != 0) & (df[df.sale2 != 0)]

Notice that both conditions must be in parentheses since & binds stronger than !=.

answered Mar 1, 2021 at 3:00

bb1

7,9232 gold badges11 silver badges26 bronze badges

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

Comments

Chris · Accepted Answer · 2021-03-01 03:13:14Z

0

Another way of writing this would be to keep only rows where any of the two columns is not equal to zero.

df.loc[df[['KC_1','KC_2']].ne(0).any(axis=1)]

answered Mar 1, 2021 at 3:13

Chris

16.3k3 gold badges26 silver badges41 bronze badges