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'...
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?
11 Answers
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
Alternative solution that uses .query() method:
In [5]: df.query("countries in @countries_to_keep")
Out[5]:
countries
1 UK
3 China
In [6]: df.query("countries not in @countries_to_keep")
Out[6]:
countries
0 US
2 Germany
How to implement 'in' and 'not in' for a pandas DataFrame?
Pandas offers two methods: Series.isin and DataFrame.isin for Series and DataFrames, respectively.
Sophia analyzes international trade, startup ecosystems, retail transformation, and supply chain logistics for modern digital publications.
