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