Filtering Pandas DataFrame: SQL-like 'IN' and 'NOT IN' operations

Data and Databases
1

I’m trying to figure out how to filter a Pandas DataFrame similar to SQL’s IN and NOT IN operations. Here’s what I want to do:

data = pd.DataFrame({'region': ['Europe', 'Asia', 'Africa', 'Americas']})
regions_of_interest = ['Asia', 'Americas']

# How can I do something like this?
data[data['region'] in regions_of_interest]
data[data['region'] not in regions_of_interest]

I’ve tried using merge, but it feels clunky:

data = pd.DataFrame({'region': ['Europe', 'Asia', 'Africa', 'Americas']})
filter_df = pd.DataFrame({'region': ['Asia', 'Americas'], 'keep': True})

# IN
result_in = data.merge(filter_df, how='inner', on='region')

# NOT IN
result_not_in = data.merge(filter_df, how='left', on='region')
result_not_in = result_not_in[result_not_in['keep'].isna()]

Is there a more straightforward way to do this in Pandas? I feel like I’m missing something obvious. Any help would be great!

2

hey luke, u can use isin() method for this. it’s pretty simple:

data[data[‘region’].isin(regions_of_interest)] # for IN
data[~data[‘region’].isin(regions_of_interest)] # for NOT IN

the ~ symbol is for negation. hope this helps!

3

hey there! have u considered using the loc accessor? it’s pretty neat:

data.loc[data[‘region’].isin(regions_of_interest)] # IN
data.loc[~data[‘region’].isin(regions_of_interest)] # NOT IN

it’s clear and concise. what do you think? any specific reasons you’re looking for alternatives?

4

I’ve encountered similar situations in my data analysis work. The isin() method is indeed efficient, but there’s another approach worth considering. You can leverage boolean indexing with a list comprehension:

data[data[‘region’].apply(lambda x: x in regions_of_interest)] # for IN
data[data[‘region’].apply(lambda x: x not in regions_of_interest)] # for NOT IN

This method offers flexibility, especially when dealing with more complex filtering conditions. It’s slightly more verbose but can be more intuitive for those coming from a programming background. Additionally, it allows for easy customization of the filtering logic if needed in the future.