Preserve NaN values in pandas boolean comparisons

Question

I have two boolean columns A and B in a pandas dataframe, each with missing data (represented by NaN). What I want is to do an AND operation on the two columns, but I want the resulting boolean column to be NaN if either of the original columns is NaN. I have the following table:

    A      B
0   True   True    
1   True   False   
2   False  True   
3   True   NaN    
4   NaN    NaN
5   NaN    False

Now when I do df.A & df.B I want:

0    True
1    False
2    False
3    NaN
4    NaN
5    False
dtype: bool

but instead I get:

0    True
1    False
2    False
3    True
4    True
5    False
dtype: bool

This behaviour is consistent with np.bool(np.nan) & np.bool(False) and its permutations, but what I really want is a column that tells me for certain if each row is True for both, or for certain could not be True for both. If I know it is True for both, then the result should be True, if I know that it is False for at least one then it should be False, and otherwise I need NaN to show that the datum is missing.

Is there a way to achieve this?

No, as long as I know at least one of the entries is False, I know that they are not both true. — Will Bryant, Jun 27 '17 at 15:04

score 8 · Accepted Answer · edited Jun 27 '17 at 13:01

8

Let's use np.logical_and:

import numpy as np
import pandas as pd
df = pd.DataFrame({'A':[True, True, False, True, np.nan, np.nan], 
                   'B':[True, False, True, np.nan, np.nan, False]})

s = np.logical_and(df['A'],df['B'])
print(s)

Output:

0     True
1    False
2    False
3      NaN
4      NaN
5    False
Name: A, dtype: object

edited Jun 27 '17 at 13:01

jezrael

822,522
95
1,334
1,252

answered Jun 27 '17 at 12:53

Scott Boston

147,308
15
139
187

Can you extend your answer for eq? `df.x = df.x.where(pd.isna, df.x == '1')` – Evan Benn May 23 '19 at 02:26
1

@EvanBenn Looks to me that you are trying to do a `fillna`. `df.x.fillna(1)` – Scott Boston May 23 '19 at 17:04
I am trying for df.x = NaN if df.x is NaN else df.x == 1 – Evan Benn May 28 '19 at 02:09
1

Try this: `df.x.mask(df.x.notnull(),1)` – Scott Boston May 28 '19 at 02:13

cs95 · Answer 2 · 2020-12-19T11:24:23.230

pandas >= 1.0

This operation is directly supported by pandas provided you are using the new Nullable Boolean Type boolean (not to be confused with the traditional numpy bool type).

# Setup
df = pd.DataFrame({'A':[True, True, False, True, np.nan, np.nan], 
                   'B':[True, False, True, np.nan, np.nan, False]})

df.dtypes                                                                  

A    object
B    object
dtype: object

# A little shortcut to convert the data type to `boolean`
df2 = df.convert_dtypes()                                                  
df2.dtypes                                                                 

A    boolean
B    boolean
dtype: object

df2['A'] & df2['B']                                                        

0     True
1    False
2    False
3     <NA>
4     <NA>
5    False
dtype: boolean

In conclusion, please consider upgrading to pandas 1.0 :-)

Preserve NaN values in pandas boolean comparisons

2 Answers2

pandas >= 1.0