How to filter Pandas dataframe using 'in' and 'not in' like in SQL
dataframe, filtering, pandas, python, sql-function
Solution
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
Problem
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?