<blockquote>
<h2>How to implement 'in' and 'not in' for a pandas DataFrame?</h2>
</blockquote>
<p>Pandas提供了两种方法:分别用于序列和数据帧的<a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.isin.html" rel="noreferrer"><strong>^{<cd1>}</strong></a>和<a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.isin.html#pandas.DataFrame.isin" rel="noreferrer"><strong>^{<cd2>}</strong></a>。</p>
<hr/>
<h2>基于一列筛选数据帧(也适用于序列)</h2>
<p>最常见的情况是对特定列应用<code>isin</code>条件来筛选数据帧中的行。</p>
<pre><code>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
</code></pre>
<p/>
<p><code>Series.isin</code>接受各种类型作为输入。以下是获得你想要的一切的有效方法:</p>
<pre><code>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
</code></pre>
<p/>
<pre><code># Filter with `set` (tuples work too)
df[df['countries'].isin(c2)]
countries
2 Germany
</code></pre>
<p/>
<pre><code># Filter with another Series
df[df['countries'].isin(c3)]
countries
0 US
4 China
</code></pre>
<p/>
<pre><code># Filter with array
df[df['countries'].isin(c4)]
countries
0 US
1 UK
</code></pre>
<hr/>
<h2>在许多列上筛选</h2>
<p>有时,您会希望对多个列应用带有某些搜索词的“in”成员资格检查</p>
<pre><code>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']
</code></pre>
<p>要将<code>isin</code>条件应用于“A”和“B”列,请使用<code>DataFrame.isin</code>:</p>
<pre><code>df2[['A', 'B']].isin(c1)
A B
0 True True
1 False False
2 False False
3 False True
</code></pre>
<p>由此,<strong>要保留至少有一列是<code>True</code></strong>的行,我们可以沿第一个轴使用<code>any</code>:</p>
<pre><code>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
</code></pre>
<p>注意,如果要搜索每一列,只需省略列选择步骤并执行</p>
<pre><code>df2.isin(c1).any(axis=1)
</code></pre>
<p>类似地,<strong>若要保留所有列都是<code>True</code></strong>的行,请使用与以前相同的方式<code>all</code>。</p>
<pre><code>df2[df2[['A', 'B']].isin(c1).all(axis=1)]
A B C
0 x w 0
</code></pre>
<hr/>
<h2>值得注意的是:<code>numpy.isin</code>,<code>query</code>,列表理解(字符串数据)</h2>
<p>除了上面描述的方法之外,您还可以使用numpy等价物:<a href="https://docs.scipy.org/doc/numpy/reference/generated/numpy.isin.html" rel="noreferrer">^{<cd11>}</a>。</p>
<pre><code># `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
</code></pre>
<p>为什么值得考虑?由于开销较低,NumPy函数通常比pandas等价函数快一点。由于这是一个不依赖于索引对齐的元素操作,因此很少有情况下此方法不适合替换pandas'<code>isin</code>。</p>
<p>Pandas例程在处理字符串时通常是迭代的,因为字符串操作很难矢量化。<a href="https://stackoverflow.com/questions/54028199/for-loops-with-pandas-when-should-i-care">There is a lot of evidence to suggest that list comprehensions will be faster here.</a>。
我们现在要进行一次<code>in</code>检查。</p>
<pre><code>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
</code></pre>
<p>然而,指定它要困难得多,所以除非你知道自己在做什么,否则不要使用它。</p>
<p>最后,还有<code>DataFrame.query</code>,它已经包含在<a href="https://stackoverflow.com/a/45190397/4909087">this answer</a>中。新墨西哥自由贸易区!</p>