How to select rows with NaN in particular column?

Question

Given this dataframe, how to select only those rows that have "Col2" equal to NaN?

df = pd.DataFrame([range(3), [0, np.NaN, 0], [0, 0, np.NaN], range(3), range(3)], columns=["Col1", "Col2", "Col3"])

which looks like:

   0   1   2
0  0   1   2
1  0 NaN   0
2  0   0 NaN
3  0   1   2
4  0   1   2

The result should be this one:

   0   1   2
1  0 NaN   0

score 272 · Accepted Answer · answered May 07 '17 at 12:42

272

Try the following:

df[df['Col2'].isnull()]

answered May 07 '17 at 12:42

qbzenker

4,412
3
16
23

17

Alternatively `df.loc[df['Col2'].isnull()]` if .loc is your kind of thing – Alexander Jul 20 '18 at 16:14
8

Q: How to negate thi, i.e. where data in column "is not null"? A: by using the `.notnull()` operator. – swiss_knight Feb 17 '20 at 10:09
How to select rows of a df, when multiple columns are null? not just any one, but only when a set of columns are null. – Naveen Reddy Marthala May 28 '21 at 06:10
5

@NaveenReddyMarthala try this: `df[df['Col1'].isnull() & df['Col2'].isnull()]` – Dhruv Kapoor Sep 10 '21 at 20:38

score 16 · Answer 2 · edited May 23 '17 at 12:10

16

@qbzenker provided the most idiomatic method IMO

Here are a few alternatives:

In [28]: df.query('Col2 != Col2') # Using the fact that: np.nan != np.nan
Out[28]:
   Col1  Col2  Col3
1     0   NaN   0.0

In [29]: df[np.isnan(df.Col2)]
Out[29]:
   Col1  Col2  Col3
1     0   NaN   0.0

edited May 23 '17 at 12:10

Community

1
1

answered May 07 '17 at 12:52

MaxU - stand with Ukraine

205,989
36
386
419

score 9 · Answer 3 · answered Mar 28 '22 at 15:30

If you want to select rows with at least one NaN value, then you could use isna + any on axis=1:

df[df.isna().any(axis=1)]

If you want to select rows with a certain number of NaN values, then you could use isna + sum on axis=1 + gt. For example, the following will fetch rows with at least 2 NaN values:

df[df.isna().sum(axis=1)>1]

If you want to limit the check to specific columns, you could select them first, then check:

df[df[['Col1', 'Col2']].isna().any(axis=1)]

If you want to select rows with all NaN values, you could use isna + all on axis=1:

df[df.isna().all(axis=1)]

If you want to select rows with no NaN values, you could notna + all on axis=1:

df[df.notna().all(axis=1)]

This is equivalent to:

df[df['Col1'].notna() & df['Col2'].notna() & df['Col3'].notna()]

which could become tedious if there are many columns. Instead, you could use functools.reduce to chain & operators:

import functools, operator
df[functools.reduce(operator.and_, (df[i].notna() for i in df.columns))]

or numpy.logical_and.reduce:

import numpy as np
df[np.logical_and.reduce([df[i].notna() for i in df.columns])]

If you're looking for filter the rows where there is no NaN in some column using query, you could do so by using engine='python' parameter:

df.query('Col2.notna()', engine='python')

or use the fact that NaN!=NaN like @MaxU - stop WAR against UA

df.query('Col2==Col2')

How to select rows with NaN in particular column?

3 Answers3

Linked

Related