["python","pandas","dataframe","indexing","filter"]
How to filter Pandas dataframe using 'in' and 'not in' like in SQL

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?
You can use pd.Series.isin. For "IN" use: something.isin(somewhere) Or for "NOT IN": ~something.isin(somewhere) As a worked example: >>> 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