pandas check if column is null with query function

Question

I have pandas dataframe that I want to execute on it query function with isnull() or not isnull() condition like that:

In [67]: df_data = pd.DataFrame({'a':[1,20,None,40,50]})
In [68]: df_data
Out[68]:       a
         0   1.0
         1  20.0
         2   NaN
         3  40.0
         4  50.0

if I use this command:

df_data.query('a isnull', engine='python')

or this command:

df_data.query('a isnull()', engine='python')

I get an error:

In [75]: df_data.query('a isnull', engine='python')  
File "<unknown>", line 1    a isnull           
SyntaxError: invalid syntax

In [76]: df_data.query('a isnull()', engine='python')  
File "<unknown>", line 1    a isnull ()           
SyntaxError: invalid syntax

What is the right way to do that?

Thank you.

jezrael · Accepted Answer · 2021-10-04T12:35:08.437

54

Use .:

a = df_data.query('a.isnull()', engine='python')
print (a)
    a
2 NaN

b = df_data.query('a.notnull()', engine='python')
print (b)
      a
0   1.0
1  20.0
3  40.0
4  50.0

You can use also logic NaN != NaN:

a = df_data.query('a != a')
print (a)
    a
 2 NaN

b = df_data.query('a == a')
print (b)
      a
0   1.0
1  20.0
3  40.0
4  50.0

edited Oct 04 '21 at 12:35

answered Sep 03 '17 at 07:29

jezrael

822,522
95
1,334
1,252

3

`df_data.query('a.isnull()')` works without engine='python' – Wassadamo Oct 12 '20 at 10:55
3

I can't get this to work without the engine='python' – Priscilla Oct 04 '21 at 12:34
1

I don't think `df_data.query('a.isnull()')` works in Pandas 1.x, not sure what broke it. – NikoNyrh Nov 17 '22 at 11:50
1

I really like the `a == a` trick – Daniel Ryan May 02 '23 at 20:30

pandas check if column is null with query function

1 Answers1

Linked