blocks|key|4703557|text|您可以使用pd.Series.isin。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4703558|用于"IN“的用法：something.isin(somewhere)|4703559|或者是"NOT+IN"：~something.isin(somewhere)|4703560|作为一个有效的示例：|4703561|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|code-block|syntax|javascript|4703562|entityMap|0|LINK|mutability|MUTABLE|url|https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.isin.html^0|5|E|5|E|0|0|A|P|0|C|Q|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@$9|11|A|12|1|13]]|E|$]]|$1|F|3|G|5|6|7|14|8|@$9|15|A|16|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|17|8|@$9|18|A|19|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|1A|8|@]|D|@]|E|$]]|$1|L|3|M|5|N|7|1B|8|@]|D|@]|E|$O|P]]|$1|Q|3|-4|5|6|7|1C|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|X]]]]

You can use <a href="https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.isin.html" rel="noreferrer"><code>pd.Series.isin</code></a>.
For &quot;IN&quot; use: <code>something.isin(somewhere)</code>
Or for &quot;NOT IN&quot;: <code>~something.isin(somewhere)</code>
As a worked example:
<pre><code>import pandas as pd

&gt;&gt;&gt; df
 country
0 US
1 UK
2 Germany
3 China
&gt;&gt;&gt; countries_to_keep
['UK', 'China']
&gt;&gt;&gt; df.country.isin(countries_to_keep)
0 False
1 True
2 False
3 True
Name: country, dtype: bool
&gt;&gt;&gt; df[df.country.isin(countries_to_keep)]
 country
1 UK
3 China
&gt;&gt;&gt; df[~df.country.isin(countries_to_keep)]
 country
0 US
2 Germany
</code></pre>

blocks|key|197693|text|使用.query()方法的替代解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|197694|In+[5]:+df.query("countries+in+@countries_to_keep")
Out[5]:
++countries
1++++++++UK
3+++++China

In+[6]:+df.query("countries+not+in+@countries_to_keep")
Out[6]:
++countries
0++++++++US
2+++Germany|code-block|syntax|javascript|197695|entityMap|0|LINK|mutability|MUTABLE|url|http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#the-query-method^0|2|8|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Alternative solution that uses <a href="http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#the-query-method" rel="noreferrer">.query()</a> method:
<pre><code>In [5]: df.query(&quot;countries in @countries_to_keep&quot;)
Out[5]:
 countries
1 UK
3 China

In [6]: df.query(&quot;countries not in @countries_to_keep&quot;)
Out[6]:
 countries
0 US
2 Germany
</code></pre>

blocks|key|4703596|text|我通常会像这样对行进行通用过滤：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4703597|criterion+=+lambda+row:+row['countries']+not+in+countries
not_in+=+df[df.apply(criterion,+axis=1)]|code-block|syntax|javascript|4703598|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I've been usually doing generic filtering over rows like this:

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

blocks|key|197666|text|我想过滤掉具有也在dfProfilesBusIds的BUSINESS_ID中的BUSINESS_ID的dfbc行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|197667|dfbc+=+dfbc[~dfbc['BUSINESS_ID'].isin(dfProfilesBusIds['BUSINESS_ID'])]|code-block|syntax|javascript|197668|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I wanted to filter out dfbc rows that had a BUSINESS_ID that was also in the BUSINESS_ID of dfProfilesBusIds

<pre><code>dfbc = dfbc[~dfbc['BUSINESS_ID'].isin(dfProfilesBusIds['BUSINESS_ID'])]
</code></pre>

blocks|key|4703806|text|为什么没有人谈论各种过滤方法的性能？事实上，这个主题经常出现在这里(参见示例)。我对一个大型数据集进行了自己的性能测试。它非常有趣，也很有启发性。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4703807|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%2B+MB|code-block|syntax|javascript|4703808|%25%25timeit
#+.isin()+by+one+column
conditions+=+['cat',+'dog']
df[df.animals.isin(conditions)]|4703809|367+ms+±+2.34+ms+per+loop+(mean+±+std.+dev.+of+7+runs,+1+loop+each)|4703810|%25%25timeit
#+.query()+by+one+column
conditions+=+['cat',+'dog']
df.query('animals+in+@conditions')|4703811|395+ms+±+3.9+ms+per+loop+(mean+±+std.+dev.+of+7+runs,+1+loop+each)|4703812|%25%25timeit
#+.loc[]
df.loc[(df.animals=='cat')%7C(df.animals=='dog')]|4703813|987+ms+±+5.17+ms+per+loop+(mean+±+std.+dev.+of+7+runs,+1+loop+each)|4703814|%25%25timeit
df[df.apply(lambda+x:+x['animals']+in+['cat',+'dog'],+axis=1)]|4703815|41.9+s+±+490+ms+per+loop+(mean+±+std.+dev.+of+7+runs,+1+loop+each)|4703816|%25%25timeit
new_df+=+df.set_index('animals')
new_df.loc[['cat',+'dog'],+:]|4703817|3.64+s+±+62.5+ms+per+loop+(mean+±+std.+dev.+of+7+runs,+1+loop+each)|4703818|%25%25timeit
new_df+=+df.set_index('animals')
new_df[new_df.index.isin(['cat',+'dog'])]|4703819|469+ms+±+8.98+ms+per+loop+(mean+±+std.+dev.+of+7+runs,+1+loop+each)|4703820|%25%25timeit
s+=+pd.Series(['cat',+'dog'],+name='animals')
df.merge(s,+on='animals',+how='inner')|4703821|796+ms+±+30.9+ms+per+loop+(mean+±+std.+dev.+of+7+runs,+1+loop+each)|4703822|因此，isin方法被证明是最快的，而使用apply()的方法是最慢的，这并不奇怪。|offset|length|style|CODE|4703823|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|3|4|K|7|0^^$0|@$1|2|3|4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1H|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|D|7|1I|8|@]|9|@]|A|$E|F]]|$1|I|3|J|5|D|7|1J|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|D|7|1K|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|D|7|1L|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|D|7|1M|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|D|7|1N|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|D|7|1O|8|@]|9|@]|A|$E|F]]|$1|U|3|V|5|D|7|1P|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|D|7|1Q|8|@]|9|@]|A|$E|F]]|$1|Y|3|Z|5|D|7|1R|8|@]|9|@]|A|$E|F]]|$1|10|3|11|5|D|7|1S|8|@]|9|@]|A|$E|F]]|$1|12|3|13|5|D|7|1T|8|@]|9|@]|A|$E|F]]|$1|14|3|15|5|D|7|1U|8|@]|9|@]|A|$E|F]]|$1|16|3|17|5|D|7|1V|8|@]|9|@]|A|$E|F]]|$1|18|3|19|5|6|7|1W|8|@$1A|1X|1B|1Y|1C|1D]|$1A|1Z|1B|20|1C|1D]]|9|@]|A|$]]|$1|1E|3|-4|5|6|7|21|8|@]|9|@]|A|$]]]|1F|$]]

Why is no one talking about the performance of various filtering methods? In fact, this topic often pops up here (see the example). I did my own performance test for a large data set. It is very interesting and instructive.
<pre class="lang-py prettyprint-override"><code>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()

&lt;class 'pandas.core.frame.DataFrame'&gt;
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
</code></pre>
<pre class="lang-py prettyprint-override"><code>%%timeit
# .isin() by one column
conditions = ['cat', 'dog']
df[df.animals.isin(conditions)]
</code></pre>
<pre><code>367 ms ± 2.34 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
</code></pre>
<pre class="lang-py prettyprint-override"><code>%%timeit
# .query() by one column
conditions = ['cat', 'dog']
df.query('animals in @conditions')
</code></pre>
<pre><code>395 ms ± 3.9 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
</code></pre>
<pre class="lang-py prettyprint-override"><code>%%timeit
# .loc[]
df.loc[(df.animals=='cat')|(df.animals=='dog')]
</code></pre>
<pre><code>987 ms ± 5.17 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
</code></pre>
<pre class="lang-py prettyprint-override"><code>%%timeit
df[df.apply(lambda x: x['animals'] in ['cat', 'dog'], axis=1)]
</code></pre>
<pre><code>41.9 s ± 490 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
</code></pre>
<pre class="lang-py prettyprint-override"><code>%%timeit
new_df = df.set_index('animals')
new_df.loc[['cat', 'dog'], :]
</code></pre>
<pre><code>3.64 s ± 62.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
</code></pre>
<pre class="lang-py prettyprint-override"><code>%%timeit
new_df = df.set_index('animals')
new_df[new_df.index.isin(['cat', 'dog'])]
</code></pre>
<pre><code>469 ms ± 8.98 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
</code></pre>
<pre class="lang-py prettyprint-override"><code>%%timeit
s = pd.Series(['cat', 'dog'], name='animals')
df.merge(s, on='animals', how='inner')
</code></pre>
<pre><code>796 ms ± 30.9 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
</code></pre>
Thus, the <code>isin</code> method turned out to be the fastest and the method with <code>apply()</code> was the slowest, which is not surprising.

blocks|key|4703879|text|您还可以在.query()中使用.isin()|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4703880|df.query('country.isin(@countries_to_keep).values')

#+Or+alternatively:
df.query('country.isin(["UK",+"China"]).values')|code-block|syntax|javascript|4703881|要取消查询，请使用~|4703882|df.query('~country.isin(@countries_to_keep).values')|4703883|entityMap|0|LINK|mutability|MUTABLE|url|https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.query.html|1|https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.isin.html^0|5|8|G|7|5|8|0|G|7|1|0|0|9|1|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]|$9|11|A|12|B|C]]|D|@$9|13|A|14|1|15]|$9|16|A|17|1|18]]|E|$]]|$1|F|3|G|5|H|7|19|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1A|8|@$9|1B|A|1C|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|1D|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|1E|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]|W|$5|R|S|T|E|$U|X]]]]

You can also use <a href="https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.isin.html" rel="nofollow noreferrer"><code>.isin()</code></a> inside <a href="https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.query.html" rel="nofollow noreferrer"><code>.query()</code></a>:
<pre><code>df.query('country.isin(@countries_to_keep).values')

# Or alternatively:
df.query('country.isin([&quot;UK&quot;, &quot;China&quot;]).values')
</code></pre>
To negate your query, use <code>~</code>:
<pre><code>df.query('~country.isin(@countries_to_keep).values')
</code></pre>

blocks|key|4703687|text|如果你想保持列表的顺序，有一个诀窍：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4703688|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|code-block|syntax|javascript|4703689|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

A trick if you want to keep the order of the list:
<pre><code>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
</code></pre>

blocks|key|197758|text|我的2c价值:我需要数据帧的in和ifelse语句的组合，这对我很有效。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|197759|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"
++++),
)|code-block|syntax|javascript|197760|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

My 2c worth:
I needed a combination of in and ifelse statements for a dataframe, and this worked for me.
<pre class="lang-py prettyprint-override"><code>sale_method = pd.DataFrame(model_data[&quot;Sale Method&quot;].str.upper())
sale_method[&quot;sale_classification&quot;] = np.where(
 sale_method[&quot;Sale Method&quot;].isin([&quot;PRIVATE&quot;]),
 &quot;private&quot;,
 np.where(
 sale_method[&quot;Sale Method&quot;].str.contains(&quot;AUCTION&quot;), &quot;auction&quot;, &quot;other&quot;
 ),
)
</code></pre>

How can I achieve the equivalents of SQL's <code>IN</code> and <code>NOT IN</code>?
I have a list with the required values.
Here's the scenario:
<pre><code>df = pd.DataFrame({'country': ['US', 'UK', 'Germany', 'China']})
countries_to_keep = ['UK', 'China']

# pseudo-code:
df[df['country'] not in countries_to_keep]
</code></pre>
My current way of doing this is as follows:
<pre><code>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'])]
</code></pre>
But this seems like a horrible kludge. Can anyone improve on it?

How to filter Pandas dataframe using 'in' and 'not in' like in SQL

性能测试

如何实现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]我目前

问如何在SQL中使用' in‘和'not in’过滤Pandas数据帧
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL中使用' in‘和'not in’过滤Pandas数据帧EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL中使用' in‘和'not in’过滤Pandas数据帧
EN