blocks|key|2935918|text|通常，您可以使用@local_variable_name，因此类似于|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2935919|>>>+pi+=+np.pi;+nan+=+np.nan
>>>+df+=+pd.DataFrame({"value":+[3,4,9,10,11,np.nan,12]})
>>>+df.query("(value+<+10)+and+(value+>+@pi)")
+++value
1++++++4
2++++++9|code-block|syntax|javascript|2935920|可以工作，但是nan不等于它自己，所以value+==+NaN将始终为false。解决这个问题的一种方法是使用这一事实，并使用value+!=+value作为isnan检查。我们有|2935921|>>>+df.query("(value+<+10)+or+(value+==+@nan)")
+++value
0++++++3
1++++++4
2++++++9|2935922|但|2935923|>>>+df.query("(value+<+10)+or+(value+!=+value)")
+++value
0++++++3
1++++++4
2++++++9
5++++NaN|2935924|entityMap^0|8|K|0|0|7|3|J|C|1R|E|27|5|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@$9|Z|A|10|B|C]|$9|11|A|12|B|C]|$9|13|A|14|B|C]|$9|15|A|16|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|17|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|18|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|19|8|@]|D|@]|E|$I|J]]|$1|S|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]]|T|$]]

In general, you could use <code>@local_variable_name</code>, so something like

<pre><code>&gt;&gt;&gt; pi = np.pi; nan = np.nan
&gt;&gt;&gt; df = pd.DataFrame({"value": [3,4,9,10,11,np.nan,12]})
&gt;&gt;&gt; df.query("(value &lt; 10) and (value &gt; @pi)")
 value
1 4
2 9
</code></pre>

would work, but <code>nan</code> isn't equal to itself, so <code>value == NaN</code> will always be false. One way to hack around this is to use that fact, and use <code>value != value</code> as an <code>isnan</code> check. We have

<pre><code>&gt;&gt;&gt; df.query("(value &lt; 10) or (value == @nan)")
 value
0 3
1 4
2 9
</code></pre>

but

<pre><code>&gt;&gt;&gt; df.query("(value &lt; 10) or (value != value)")
 value
0 3
1 4
2 9
5 NaN
</code></pre>

blocks|key|146582|text|根据this+answer的说法，您可以使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|146583|df.query('value+<+10+%7C+value.isnull()',+engine='python')|code-block|syntax|javascript|146584|我验证了它是有效的。|146585|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/46021212/18891^0|2|B|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

According to <a href="https://stackoverflow.com/a/46021212/18891">this answer</a> you can use:

<pre><code>df.query('value &lt; 10 | value.isnull()', engine='python')
</code></pre>

I verified that it works.

blocks|key|2723692|text|对于value不为null的行|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2723693|df.query("value+==+value")|code-block|syntax|javascript|2723694|对于value为null的行|2723695|df.query("value+!=+value")|2723696|entityMap^0|2|5|0|0|2|5|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|P|$]]

For rows where <code>value</code> is not null

<pre><code>df.query("value == value")
</code></pre>

For rows where <code>value</code> is null

<pre><code>df.query("value != value")
</code></pre>

blocks|key|146642|text|您可以使用isna和notna+Series方法，它们非常简洁和可读。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|146643|import+pandas+as+pd
import+numpy+as+np

df+=+pd.DataFrame({'value':+[3,+4,+9,+10,+11,+np.nan,+12]})
available+=+df.query("value.notna()")
print(available)

#++++value
#+0++++3.0
#+1++++4.0
#+2++++9.0
#+3+++10.0
#+4+++11.0
#+6+++12.0

not_available+=+df.query("value.isna()")
print(not_available)

#++++value
#+5++++NaN|code-block|syntax|javascript|146644|如果您安装了numexpr，则需要传递engine="python"才能使其与.query一起工作。numexpr是pandas的recommended，用于在更大的数据集上加速.query的性能。|146645|available+=+df.query("value.notna()",+engine="python")
print(available)|146646|或者，您也可以使用toplevel+pd.isna函数，将其作为局部变量引用。同样，当存在numexpr时，需要传递engine="python"。|146647|import+pandas+as+pd
import+numpy+as+np


df+=+pd.DataFrame({'value':+[3,+4,+9,+10,+11,+np.nan,+12]})
df.query("@pd.isna(value)")

#++++value
#+5++++NaN|146648|entityMap|0|LINK|mutability|MUTABLE|url|https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.isna.html#pandas.Series.isna|1|https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.notna.html#pandas.Series.notna|2|https://pypi.org/project/numexpr/|3|https://pandas.pydata.org/pandas-docs/stable/user_guide/enhancingperf.html#expression-evaluation-via-eval|4|https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.isna.html^0|5|4|A|5|G|6|5|4|0|A|5|1|0|0|6|7|J|F|13|6|1E|7|2H|6|6|7|2|1T|B|3|0|0|I|7|19|7|1M|F|I|7|4|0|0^^$0|@$1|2|3|4|5|6|7|18|8|@$9|19|A|1A|B|C]|$9|1B|A|1C|B|C]|$9|1D|A|1E|B|C]]|D|@$9|1F|A|1G|1|1H]|$9|1I|A|1J|1|1K]]|E|$]]|$1|F|3|G|5|H|7|1L|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1M|8|@$9|1N|A|1O|B|C]|$9|1P|A|1Q|B|C]|$9|1R|A|1S|B|C]|$9|1T|A|1U|B|C]|$9|1V|A|1W|B|C]]|D|@$9|1X|A|1Y|1|1Z]|$9|20|A|21|1|22]]|E|$]]|$1|M|3|N|5|H|7|23|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|24|8|@$9|25|A|26|B|C]|$9|27|A|28|B|C]|$9|29|A|2A|B|C]]|D|@$9|2B|A|2C|1|2D]]|E|$]]|$1|Q|3|R|5|H|7|2E|8|@]|D|@]|E|$I|J]]|$1|S|3|-4|5|6|7|2F|8|@]|D|@]|E|$]]]|T|$U|$5|V|W|X|E|$Y|Z]]|10|$5|V|W|X|E|$Y|11]]|12|$5|V|W|X|E|$Y|13]]|14|$5|V|W|X|E|$Y|15]]|16|$5|V|W|X|E|$Y|17]]]]

You can use the <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.isna.html#pandas.Series.isna" rel="noreferrer"><code>isna</code></a> and <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.notna.html#pandas.Series.notna" rel="noreferrer"><code>notna</code></a> <code>Series</code> methods, which is concise and readable.
<pre class="lang-py prettyprint-override"><code>import pandas as pd
import numpy as np

df = pd.DataFrame({'value': [3, 4, 9, 10, 11, np.nan, 12]})
available = df.query(&quot;value.notna()&quot;)
print(available)

# value
# 0 3.0
# 1 4.0
# 2 9.0
# 3 10.0
# 4 11.0
# 6 12.0

not_available = df.query(&quot;value.isna()&quot;)
print(not_available)

# value
# 5 NaN
</code></pre>
In case you have <a href="https://pypi.org/project/numexpr/" rel="noreferrer"><code>numexpr</code></a> installed, you need to pass <code>engine=&quot;python&quot;</code> to make it work with <code>.query</code>.
<code>numexpr</code> is <a href="https://pandas.pydata.org/pandas-docs/stable/user_guide/enhancingperf.html#expression-evaluation-via-eval" rel="noreferrer">recommended</a> by pandas to speed up the performance of <code>.query</code> on larger datasets.
<pre class="lang-py prettyprint-override"><code>available = df.query(&quot;value.notna()&quot;, engine=&quot;python&quot;)
print(available)
</code></pre>
Alternatively, you can use the toplevel <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.isna.html" rel="noreferrer"><code>pd.isna</code></a> function, by referencing it as a local variable. Again, passing <code>engine=&quot;python&quot;</code> is required when <code>numexpr</code> is present.
<pre class="lang-py prettyprint-override"><code>import pandas as pd
import numpy as np


df = pd.DataFrame({'value': [3, 4, 9, 10, 11, np.nan, 12]})
df.query(&quot;@pd.isna(value)&quot;)

# value
# 5 NaN
</code></pre>

blocks|key|144343|text|Pandas用NumPy的NaN值填充DataFrame中的空单元格。事实证明，这有一些有趣的属性。首先，没有任何东西等同于这种null，甚至它本身也是如此。因此，您不能通过检查任何特定的相等性来搜索它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|144344|In+:+'nan'+==+np.nan
Out:+False

In+:+None+==+np.nan
Out:+False

In+:+np.nan+==+np.nan
Out:+False|code-block|syntax|javascript|144345|但是，由于包含np.nan值的单元格将不等于任何内容，包括另一个np.nan值，因此我们可以检查它是否与自身不相等。|144346|In+:+np.nan+!=+np.nan
Out:+True|144347|您可以使用Pandas+query方法来利用这一点，只需搜索特定列中的值不等于其自身的单元格。|144348|df.query('a+!=+a')|144349|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Pandas fills empty cells in a DataFrame with NumPy's nan values. As it turns out, this has some funny properties. For one, nothing is equal to this kind of null, even itself. As a result, you can't search for it by checking for any particular equality.

<pre><code>In : 'nan' == np.nan
Out: False

In : None == np.nan
Out: False

In : np.nan == np.nan
Out: False
</code></pre>

However, because a cell containing a np.nan value will not be equal to anything, including another np.nan value, we can check to see if it is unequal to itself.

<pre><code>In : np.nan != np.nan
Out: True
</code></pre>

You can take advantage of this using Pandas query method by simply searching for cells where the value in a particular column is unequal to itself.

<pre><code>df.query('a != a')
</code></pre>

blocks|key|2936098|text|这也应该是可行的：df.query("value+==+'NaN'")|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2936099|entityMap^0|9|Q|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

This should also work: <code>df.query(&quot;value == 'NaN'&quot;)</code>

blocks|key|2936028|text|我认为其他答案通常会更好。在一种情况下，我的查询必须经过eval+(非常小心地使用eval+)，下面的语法很有用。要求数字既小于又大于或等于将排除所有数字，只保留类似于null的值。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2936029|df+=+pd.DataFrame({'value':[3,4,9,10,11,np.nan,+12]})

df.query("value+<+10+or+(~(value+<+10)+and+~(value+>=+10))")|code-block|syntax|javascript|2936030|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 think other answers will normally be better. In one case, my query had to go through eval (use eval very carefully) and the syntax below was useful. Requiring a number to be both less than and greater than or equal to excludes all numbers, leaving only null-like values.
<pre><code>df = pd.DataFrame({'value':[3,4,9,10,11,np.nan, 12]})

df.query(&quot;value &lt; 10 or (~(value &lt; 10) and ~(value &gt;= 10))&quot;)
</code></pre>

Say I have a dataframe <code>df</code> with a column <code>value</code> holding some float values and some <code>NaN</code>. How can I get the part of the dataframe where we have <code>NaN</code> using the query syntax?

The following, for example, does not work:

<pre><code>df.query( '(value &lt; 10) or (value == NaN)' )
</code></pre>

I get <code>name NaN is not defined</code> (same for <code>df.query('value ==NaN')</code>)

Generally speaking, is there any way to use numpy names in query, such as <code>inf</code>, <code>nan</code>, <code>pi</code>, <code>e</code>, etc.?

Querying for NaN and other names in Pandas

假设我有一个包含value列的dataframe df，其中包含一些浮点值和一些NaN。如何使用查询语法获取其中包含NaN 的那部分数据帧例如，以下代码不起作用：df.query( '(value < 10) or (value == NaN)' )我得到了name NaN is not defined (和df.query('value ==NaN')一样)一般来说，有没有办法在查询中使用num

问在Pandas中查询NaN和其他名称
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Pandas中查询NaN和其他名称EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Pandas中查询NaN和其他名称
EN