Pandas - Filtering None Values

Question

I'm using Pandas to explore some datasets. I have this dataframe:

I want to exclude any row that has a value in column City. So I've tried:

new_df = all_df[(all_df["City"] == "None") ]
new_df

But then I got an empty dataframe:

It works whenever I use any value other than None. Any idea how to filter this dataframe?

Why is it `None` at all? Is it an actual string `"None"`? For me it's `NaN` if I fill DataFrame with `None` values. — mr.tarsa, Jul 15 '17 at 10:52
@tarashypka it was a one column (Region\City\Village) that i split it up into 3 using "all_df[['Region', 'City', 'Village']] = all_df['Cities_Full_Name'].str.split('\\', expand=True)" — Shadin, Jul 15 '17 at 11:03
@Shadin Yes, my bad, actually it fills it with `NaN` values if data type is numeric, if data type is mixed (object) then there will be `None` values. — mr.tarsa, Jul 15 '17 at 11:13

score 190 · Accepted Answer · answered Jul 15 '17 at 11:08

190

Consider using isnull() to locate missing values

all_df[all_df['City'].isnull()]

answered Jul 15 '17 at 11:08

mr.tarsa

6,386
3
25
42

25

To exclude those rows: all_df[~all_df['City'].isnull()] – fstang Apr 25 '19 at 06:22
35

@fstang You can also use notnull: `all_df[all_df['City'].notnull()]` – mr.tarsa Apr 25 '19 at 14:05
2

`isnull()` returns True for both `None` and `NaN` values. – Olsgaard Jan 20 '22 at 09:49

imanzabet · Answer 2 · 2017-07-15T11:31:30.717

7

Try this to select only the None values for city column:

new_df = all_df['City'][all_df['City'] == "None"]

Try this to see all other columns which has the same rows of 'City'==None

new_df = all_df[all_df['City'] == "None"]
print(new_df.head()) # with function head() you can see the first 5 rows

edited Jul 15 '17 at 11:31

answered Jul 15 '17 at 10:55

imanzabet

2,752
2
26
19

Thanks! but when i print the new df i get: Series([], Name: City, dtype: object) – Shadin Jul 15 '17 at 11:07
How can i print it properly? – Shadin Jul 15 '17 at 11:07
Ok, I think I got your question. You need to print all other columns as well for city==None. I have edited the codes. Hope it will be helpful – imanzabet Jul 15 '17 at 11:28

score 2 · Answer 3 · answered Sep 27 '22 at 01:36

None is a keyword, not a string, so don't use quotes.
None == None gives True, but in custom classes the comparison operator can be overridden, so it's safer to use is None.
Pandas provides the isna() function. So I suggest:

new_df = all_df[all_df['City'].isna()]

score 1 · Answer 4 · answered May 14 '22 at 07:02

1

Another alternative is to use the query method:

In [3]: all_df.query('City != City')
Out[3]: 
   FACTS_Value    Region  City Village
0      34135.0  Al Bahah  None    None

answered May 14 '22 at 07:02

rachwa

1,805
1
14
17

score 1 · Answer 5 · edited Jul 22 '22 at 05:42

1

Method to extract all the values other than None given its column name.

df = df[df.columnname.notna()]

edited Jul 22 '22 at 05:42

cottontail

10,268
18
50
51

answered Jul 20 '22 at 14:22

skin

21
5

this solution is incorrect. None and Na are 2 different values. – Frederic Bazin Jul 21 '22 at 08:38

score 0 · Answer 6 · edited Jul 15 '17 at 10:54

0

I hope "where" can do what you expect

new_df = new_df.where(new_df["city"], None)

And it is better use np.nan rather than None.

For more details pandas.DataFrame.where

edited Jul 15 '17 at 10:54

Serenity

35,289
20
120
115

answered Jul 15 '17 at 10:54

Vibhutha Kumarage

1,372
13
26

1

How about getting all that is not None? – Nabin Feb 18 '20 at 18:25
@Nabin `all_df[~all_df['City'].isnull()]` – BND Jun 02 '21 at 08:25

score 0 · Answer 7 · answered Feb 25 '22 at 07:19

If it's desired to filter multiple rows with None values, we could use any, all or sum. For example, for df given below:

   FACTS_Value    Region      City  Village
0        16482  Al Bahah      None     None
1        22522  Al Bahah   Al Aqiq     None
2        12444  Al Bahah   Al Aqiq  Al Aqiq
3        12823  Al Bahah  Al Bahah  Al Aqiq
4        11874      None      None     None

If we want to select all rows with None value in at least one column, we could use isna + any on axis to build a boolean mask:

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

Output:

   FACTS_Value    Region     City Village
0        16482  Al Bahah     None    None
1        22522  Al Bahah  Al Aqiq    None
4        11874      None     None    None

If we want the rows where all non-numeric column values are None, then we could use isna + all on axis:

msk = df.select_dtypes(exclude='number').isna().all(axis=1)

or

msk = df[['Region', 'City', 'Village']].isna().all(axis=1)
out = df[msk]

Output:

   FACTS_Value Region  City Village
4        11874   None  None    None

If we want to filter rows where there are exactly n None values, then we could use sum on axis + eq:

msk = df.isna().sum(axis=1) == 2
out = df[msk]

Output:

   FACTS_Value    Region  City Village
0        16482  Al Bahah  None    None

score 0 · Answer 8 · answered Jul 22 '22 at 06:29

Given, where None is python None, not 'None':

   FACTS_Value    Region      City  Village
0        16482  Al Bahah      None     None
1        22522  Al Bahah   Al Aqiq     None
2        12444  Al Bahah   Al Aqiq  Al Aqiq
3        12823  Al Bahah  Al Bahah  Al Aqiq
4        11874      None      None     None

Fix your None values:

df = df.fillna(np.nan)

Now the following works as expected:

df[df.City.isna()]

Pandas - Filtering None Values

8 Answers8

Linked