如何使用'in'和'not in'像SQL一样过滤Pandas数据框

问题描述：

如何实现 SQL 的 IN 和 NOT IN 的等效项?

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]

我目前的做法如下:

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?

答

您可以使用 pd.Series.isin.

You can use pd.Series.isin.

对于IN"使用:something.isin(somewhere)

或者对于NOT IN":~something.isin(somewhere)

Or for "NOT IN": ~something.isin(somewhere)

作为一个有效的例子:

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

如何使用'in'和'not in'像SQL一样过滤Pandas数据框

相关推荐