Python pandas Filtering out nan from a data selection of a column of strings

Question

Without using groupby how would I filter out data without NaN?

Let say I have a matrix where customers will fill in 'N/A','n/a' or any of its variations and others leave it blank:

import pandas as pd
import numpy as np


df = pd.DataFrame({'movie': ['thg', 'thg', 'mol', 'mol', 'lob', 'lob'],
                  'rating': [3., 4., 5., np.nan, np.nan, np.nan],
                  'name': ['John', np.nan, 'N/A', 'Graham', np.nan, np.nan]})

nbs = df['name'].str.extract('^(N/A|NA|na|n/a)')
nms=df[(df['name'] != nbs) ]

output:

>>> nms
  movie    name  rating
0   thg    John       3
1   thg     NaN       4
3   mol  Graham     NaN
4   lob     NaN     NaN
5   lob     NaN     NaN

How would I filter out NaN values so I can get results to work with like this:

  movie    name  rating
0   thg    John       3
3   mol  Graham     NaN

I am guessing I need something like ~np.isnan but the tilda does not work with strings.

df[df.name.notnull()]

min2bro
– min2bro

2022-01-31 06:36:05 +00:00
Commented Jan 31, 2022 at 6:36 — min2bro
– min2bro, Commented Jan 31, 2022 at 6:36

Gil Baggio · Accepted Answer · 2019-12-09 14:32:46Z

431

Simplest of all solutions:

filtered_df = df[df['name'].notnull()]

Thus, it filters out only rows that doesn't have NaN values in 'name' column.

For multiple columns:

filtered_df = df[df[['name', 'country', 'region']].notnull().all(1)]

edited Dec 9, 2019 at 14:32

answered Dec 4, 2017 at 8:38

Gil Baggio

14.1k3 gold badges51 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cerberton · Accepted Answer · 2019-07-12 08:18:23Z

Just drop them:

nms.dropna(thresh=2)

this will drop all rows where there are at least two non-NaN.

Then you could then drop where name is NaN:

In [87]:

nms
Out[87]:
  movie    name  rating
0   thg    John       3
1   thg     NaN       4
3   mol  Graham     NaN
4   lob     NaN     NaN
5   lob     NaN     NaN

[5 rows x 3 columns]
In [89]:

nms = nms.dropna(thresh=2)
In [90]:

nms[nms.name.notnull()]
Out[90]:
  movie    name  rating
0   thg    John       3
3   mol  Graham     NaN

[2 rows x 3 columns]

EDIT

Actually looking at what you originally want you can do just this without the dropna call:

nms[nms.name.notnull()]

UPDATE

Looking at this question 3 years later, there is a mistake, firstly thresh arg looks for at least n non-NaN values so in fact the output should be:

In [4]:
nms.dropna(thresh=2)

Out[4]:
  movie    name  rating
0   thg    John     3.0
1   thg     NaN     4.0
3   mol  Graham     NaN

It's possible that I was either mistaken 3 years ago or that the version of pandas I was running had a bug, both scenarios are entirely possible.

JacoSolari · Accepted Answer · 2019-06-06 11:20:09Z

18

df.dropna(subset=['columnName1', 'columnName2'])

answered Jun 6, 2019 at 11:20

JacoSolari

1,46416 silver badges31 bronze badges

Comments

Bashar Mohammad · Accepted Answer · 2019-01-09 12:06:10Z

10

df = pd.DataFrame({'movie': ['thg', 'thg', 'mol', 'mol', 'lob', 'lob'],'rating': [3., 4., 5., np.nan, np.nan, np.nan],'name': ['John','James', np.nan, np.nan, np.nan,np.nan]})

for col in df.columns:
    df = df[~pd.isnull(df[col])]

edited Jan 9, 2019 at 12:06

answered Jan 9, 2019 at 11:58

Bashar Mohammad

1111 silver badge4 bronze badges

Comments

user7864386 · Accepted Answer · 2022-02-17 09:03:33Z

3

You can also use query:

out = df.query("name.notna() & name !='N/A'", engine='python')

Output:

  movie  rating    name
0   thg     3.0    John
3   mol     NaN  Graham

answered Feb 17, 2022 at 9:03

user7864386

1 Comment

LaTouwne Over a year ago

Your answer could be improved with additional supporting information like citations or documentation about the use of query.

rachwa · Accepted Answer · 2022-02-23 21:53:04Z

1

Inside query() pass column_name == column_name to keep the rows where column_name is not NA.

For your case:

nms.query('name == name')

answered Feb 23, 2022 at 21:53

rachwa

2,3901 gold badge21 silver badges20 bronze badges

Comments

Howdyouride · Accepted Answer · 2023-01-23 07:53:27Z

1

You can filter negative to columns with na values: dt = dt[~dt[columns_to_filter].isna().all(1)]

answered Jan 23, 2023 at 7:53

Howdyouride

1752 silver badges9 bronze badges

1 Comment

shamnad sherief Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Python pandas Filtering out nan from a data selection of a column of strings

7 Answers 7

Comments

Comments

Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related