如何像在 SQL 中一样使用“in”和“not in”过滤 Pandas 数据框

问题描述：

我怎样才能实现 SQL 的IN和的等价功能NOT IN？

我有一个包含所需值的列表。以下是场景：

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'])]

但这看起来太糟糕了。有人能改进它吗？

解决方案 1：

您可以使用pd.Series.isin。

对于“IN”使用：something.isin(somewhere)

或者对于“NOT IN”：~something.isin(somewhere)

作为一个示例：

>>> 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

解决方案 2：

使用.query()方法的替代解决方案：

In [5]: df.query("country in @countries_to_keep")
Out[5]:
  countries
1        UK
3     China

In [6]: df.query("country not in @countries_to_keep")
Out[6]:
  countries
0        US
2   Germany

解决方案 3：

如何为 pandas DataFrame 实现“in”和“not in”？

Pandas 提供两种方法：Series.isin和DataFrame.isin，分别用于 Series 和 DataFrames。

根据一列过滤 DataFrame (也适用于 Series)

最常见的情况是应用isin特定列的条件来过滤 DataFrame 中的行。

df = pd.DataFrame({'countries': ['US', 'UK', 'Germany', np.nan, 'China']})
df
  countries
0        US
1        UK
2   Germany
3     China

c1 = ['UK', 'China']             # list
c2 = {'Germany'}                 # set
c3 = pd.Series(['China', 'US'])  # Series
c4 = np.array(['US', 'UK'])      # array

Series.isin接受各种类型作为输入。以下都是获取所需内容的有效方法：

df['countries'].isin(c1)

0    False
1     True
2    False
3    False
4     True
Name: countries, dtype: bool

# `in` operation
df[df['countries'].isin(c1)]

  countries
1        UK
4     China

# `not in` operation
df[~df['countries'].isin(c1)]

  countries
0        US
2   Germany
3       NaN

# Filter with `set` (tuples work too)
df[df['countries'].isin(c2)]

  countries
2   Germany

# Filter with another Series
df[df['countries'].isin(c3)]

  countries
0        US
4     China

# Filter with array
df[df['countries'].isin(c4)]

  countries
0        US
1        UK

根据多列进行过滤

有时，你会希望对多个列中的一些搜索词应用“in”成员资格检查，

df2 = pd.DataFrame({
    'A': ['x', 'y', 'z', 'q'], 'B': ['w', 'a', np.nan, 'x'], 'C': np.arange(4)})
df2

   A    B  C
0  x    w  0
1  y    a  1
2  z  NaN  2
3  q    x  3

c1 = ['x', 'w', 'p']

要将isin条件应用于“A”列和“B”列，请使用DataFrame.isin：

df2[['A', 'B']].isin(c1)

      A      B
0   True   True
1  False  False
2  False  False
3  False   True

由此，为了保留至少有一列的行Trueany，我们可以沿第一个轴使用：

df2[['A', 'B']].isin(c1).any(axis=1)

0     True
1    False
2    False
3     True
dtype: bool

df2[df2[['A', 'B']].isin(c1).any(axis=1)]

   A  B  C
0  x  w  0
3  q  x  3

请注意，如果您想搜索每一列，只需省略列选择步骤并执行

df2.isin(c1).any(axis=1)

类似地，要保留所有列均为的行True，请使用all与之前相同的方式。

df2[df2[['A', 'B']].isin(c1).all(axis=1)]

   A  B  C
0  x  w  0

值得一提的是：numpy.isin，，query列表推导（字符串数据）

除了上面介绍的方法之外，您还可以使用 numpy 等效方法：numpy.isin。

# `in` operation
df[np.isin(df['countries'], c1)]

  countries
1        UK
4     China

# `not in` operation
df[np.isin(df['countries'], c1, invert=True)]

  countries
0        US
2   Germany
3       NaN

为什么值得考虑？由于开销较低，NumPy 函数通常比 pandas 函数快一点。由于这是一个不依赖于索引对齐的元素级操作，因此在极少数情况下，这种方法不适合替代 pandas 的isin。

Pandas 例程在处理字符串时通常是迭代的，因为字符串操作很难矢量化。有很多证据表明列表推导在这里会更快。。我们现在求助于in检查。

c1_set = set(c1) # Using `in` with `sets` is a constant time operation... 
                 # This doesn't matter for pandas because the implementation differs.
# `in` operation
df[[x in c1_set for x in df['countries']]]

  countries
1        UK
4     China

# `not in` operation
df[[x not in c1_set for x in df['countries']]]

  countries
0        US
2   Germany
3       NaN

然而，指定起来会更加麻烦，所以除非您知道自己在做什么，否则不要使用它。

最后，这个答案DataFrame.query中也涵盖了这一点。numexpr FTW！

解决方案 4：

我通常对行进行常规过滤，如下所示：

criterion = lambda row: row['countries'] not in countries
not_in = df[df.apply(criterion, axis=1)]

解决方案 5：

从答案中整理可能的解决方案：

对于印第安纳州：df[df['A'].isin([3, 6])]

对于 NOT IN：

1. df[-df["A"].isin([3, 6])]

2. df[~df["A"].isin([3, 6])]

3. df[df["A"].isin([3, 6]) == False]

4. df[np.logical_not(df["A"].isin([3, 6]))]

解决方案 6：

为什么没有人谈论各种过滤方法的性能？事实上，这个话题经常出现在这里（见示例）。我对一个大数据集进行了自己的性能测试。这非常有趣且具有启发性。

df = pd.DataFrame({'animals': np.random.choice(['cat', 'dog', 'mouse', 'birds'], size=10**7), 
                   'number': np.random.randint(0,100, size=(10**7,))})

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 10000000 entries, 0 to 9999999
Data columns (total 2 columns):
 #   Column   Dtype 
---  ------   ----- 
 0   animals  object
 1   number   int64 
dtypes: int64(1), object(1)
memory usage: 152.6+ MB

%%timeit
# .isin() by one column
conditions = ['cat', 'dog']
df[df.animals.isin(conditions)]

367 ms ± 2.34 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
# .query() by one column
conditions = ['cat', 'dog']
df.query('animals in @conditions')

395 ms ± 3.9 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
# .loc[]
df.loc[(df.animals=='cat')|(df.animals=='dog')]

987 ms ± 5.17 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
df[df.apply(lambda x: x['animals'] in ['cat', 'dog'], axis=1)]

41.9 s ± 490 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
new_df = df.set_index('animals')
new_df.loc[['cat', 'dog'], :]

3.64 s ± 62.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
new_df = df.set_index('animals')
new_df[new_df.index.isin(['cat', 'dog'])]

469 ms ± 8.98 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
s = pd.Series(['cat', 'dog'], name='animals')
df.merge(s, on='animals', how='inner')

796 ms ± 30.9 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

因此，isin方法是最快的，而方法apply()是最慢的，这并不奇怪。

解决方案 7：

我想过滤掉 dfbc 行中那些 BUSINESS_ID 也位于 dfProfilesBusIds 的 BUSINESS_ID 中的行

dfbc = dfbc[~dfbc['BUSINESS_ID'].isin(dfProfilesBusIds['BUSINESS_ID'])]

解决方案 8：

您也可以使用.isin()内部.query()：

df.query('country.isin(@countries_to_keep).values')

# Or alternatively:
df.query('country.isin(["UK", "China"]).values')

要否定您的查询，请使用~：

df.query('~country.isin(@countries_to_keep).values')

更新：

另一种方法是使用比较运算符：

df.query('country == @countries_to_keep')

# Or alternatively:
df.query('country == ["UK", "China"]')

要否定查询，请使用!=：

df.query('country != @countries_to_keep')

解决方案 9：

df = pd.DataFrame({'countries':['US','UK','Germany','China']})
countries = ['UK','China']

实施于：

df[df.countries.isin(countries)]

在其他国家中，以下国家不实施此项政策：

df[df.countries.isin([x for x in np.unique(df.countries) if x not in countries])]

解决方案 10：

如果你想保持列表的顺序，可以使用以下技巧：

df = pd.DataFrame({'country': ['US', 'UK', 'Germany', 'China']})
countries_to_keep = ['Germany', 'US']


ind=[df.index[df['country']==i].tolist() for i in countries_to_keep]
flat_ind=[item for sublist in ind for item in sublist]

df.reindex(flat_ind)

   country
2  Germany
0       US

解决方案 11：

groupby可以用来将数据框“拆分”成两个

如果目的是将一个数据框分成两个数据框，其中一个数据框包含要保留的国家而另一个数据框不包含，isin则可以使用调用创建的布尔掩码groupby将数据框分成两个：包含和不包含。

df = pd.DataFrame({'country': ['US', 'UK', 'Germany', 'China'], 'value': range(4)})
countries_to_keep = ['UK', 'China']
df1, df2 = [g for _, g in df.groupby(df['country'].isin(countries_to_keep))]

eval()也可以使用

query()其他地方建议评估数值表达式。相关方法是eval()。它可用于创建布尔掩码并过滤框架。它可以与其他地方创建的其他掩码一起使用，以实现更灵活的过滤。

msk = df.eval('country in @countries_to_keep')
to_keep = df[msk]     # in
not_keep = df[~msk]   # not in

一个特殊的情况是，如果你想使用一个条件过滤单个列，query内存效率非常低，因为它会创建过滤框架的副本，该副本需要再次过滤单个列，而loc使用布尔掩码列标签组合一次性选择该列。eval()可以做同样的事情。1

df = pd.DataFrame({'country': ['US', 'UK', 'Germany', 'China']*25000})
df[[f"col{i}" for i in range(50)]] = np.random.rand(100000, 50)
countries_to_keep = ['UK', 'China']

filtered = df.loc[df.eval('country==@countries_to_keep'), 'col1']

1内存分析器测试：

import numpy as np
import pandas as pd
%load_ext memory_profiler

df = pd.DataFrame({'country': ['US', 'UK', 'Germany', 'China']*25000})
df[[f"col{i}" for i in range(50)]] = np.random.rand(100000, 50)
countries_to_keep = ['UK', 'China']


%memit x = df.loc[df.eval('country==@countries_to_keep'), 'col1']
# peak memory: 157.28 MiB, increment: 5.44 MiB

%memit y = df.query('country==@countries_to_keep')['col1']
# peak memory: 195.39 MiB, increment: 38.11 MiB

%memit z = df.loc[df['country'].isin(countries_to_keep), 'col1']
# peak memory: 176.93 MiB, increment: 0.76 MiB

解决方案 12：

我的 2c 价值：我需要数据框的 in 和 ifelse 语句的组合，这对我有用。

sale_method = pd.DataFrame(model_data["Sale Method"].str.upper())
sale_method["sale_classification"] = np.where(
    sale_method["Sale Method"].isin(["PRIVATE"]),
    "private",
    np.where(
        sale_method["Sale Method"].str.contains("AUCTION"), "auction", "other"
    ),
)