Pandas: Using the tilde operator to return inverse data with two filters

Question

I'm filtering on two DataFrame columns using isin. Aim is to return two distinct DataFrames: One where the filter conditions are met and one where they're not. The DataFrames should be exact opposites, in effect. However I can't seem to use the tilde operator in the way I assumed I could.

A reproducible example:

raw_data = {
    'id': ['s1', 's2', 's1', 's4', 's2', 's5', 's4', 's2'], 
    'car': ['ford', 'bmw', 'ford', 'mazda', 'ford', 'bmw', 'audi', 'bmw']}

df_a = pd.DataFrame(raw_data, columns= ['id', 'car'])

values1 = ['s1', 's2']
values2 = ['bmw', 'ford']
df_a[(df_a['id'].isin(values1)) & (df_a['car'].isin(values2))]

Returns this:

    id  car
0   s1  ford
1   s2  bmw
2   s1  ford
4   s2  ford
7   s2  bmw

Which is correct. But if try to reverse that using:

df_a[~(df_a['id'].isin(values1)) & (df_a['car'].isin(values2))]

I get:

    id  car
5   s5  bmw

Which is not the inverse. I've tried adding a second tilde to the second filter but can't make it work. Where am I going wrong, or is there a better way to do this?

Answer 1

You need additional parentheses:

In [411]:
df_a[~((df_a['id'].isin(values1)) & (df_a['car'].isin(values2)))]
#     ^                                                        ^
Out[411]:
   id    car
3  s4  mazda
5  s5    bmw
6  s4   audi

What you did was invert only the first condition.