Delete rows based on two conditions

Question

I have a dataframe df that looks like:

 0 1 2 3 0 x a 1 x b 2 x c 3 x a 4 x b 5 x c 6 y a 7 y b 8 y c 9 z a 10 z b 11 z c 12 z a 13 z b 14 z c

I want to delete rows where df[1]=="c" AND df[0]==df[0].shift(-1)

However I am not able to combine these 2 conditions. Here is my code:

m1 = (df[df[0].eq(df[0].shift(-1))]) m2 = (df[df[1].eq("x")]) df[~(m1 & m2)]

I get error:

TypeError: unsupported operand type(s) for &: 'str' and 'str'

If I try

df[~(m1 and m2)]

I get error:

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

Should've just been "df" I changed the dataframe name to make it more common — Mitch
– Mitch, Commented Jul 8, 2020 at 20:03

Quang Hoang · Accepted Answer · 2020-07-08 19:53:32Z

1

Try:

m1 = df[0].eq(df[0].shift(-1)) m2 = df[1].eq("x") df[~(m1 & m2)]

answered Jul 8, 2020 at 19:53

151k11 gold badges64 silver badges86 bronze badges

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

2 Comments

I edited my question. That's what my code actually looks like already and I get the errors I posted.

@Mitch take another look, my code is still different than yours :-).

Valdi_Bo · Accepted Answer · 2020-07-08 20:10:13Z

You should replace AND in your condition with &.

To drop your "not-wanted" rows, retrieve rows not meeting your condition and save under df:

df = df[~((df[1] == 'c') & (df[0] == df[0].shift(-1)))]

The result is:

 0 1 0 x a 1 x b 3 x a 4 x b 5 x c 6 y a 7 y b 8 y c 9 z a 10 z b 12 z a 13 z b 14 z c

(rows with index == 2 and 11 dropped).