How to filter Pandas dataframe using 'in' and 'not in' like in SQL

Question

How can I achieve the equivalents of SQL's IN and NOT IN?

I have a list with the required values. Here's the scenario:

df = pd.DataFrame({'country': ['US', 'UK', 'Germany', 'China']}) countries_to_keep = ['UK', 'China']  # pseudo-code: df[df['country'] not in countries_to_keep] 

My current way of doing this is as follows:

df = pd.DataFrame({'country': ['US', 'UK', 'Germany', 'China']}) df2 = pd.DataFrame({'country': ['UK', 'China'], 'matched': True})  # IN df.merge(df2, how='inner', on='country')  # NOT IN not_in = df.merge(df2, how='left', on='country') not_in = not_in[pd.isnull(not_in['matched'])] 

But this seems like a horrible kludge. Can anyone improve on it?

Answer 1

You can use pd.Series.isin.

For "IN" use: something.isin(somewhere)

Or for "NOT IN": ~something.isin(somewhere)

As a worked example:

import pandas as pd  >>> df   country 0        US 1        UK 2   Germany 3     China >>> countries_to_keep ['UK', 'China'] >>> df.country.isin(countries_to_keep) 0    False 1     True 2    False 3     True Name: country, dtype: bool >>> df[df.country.isin(countries_to_keep)]   country 1        UK 3     China >>> df[~df.country.isin(countries_to_keep)]   country 0        US 2   Germany