Set all values in one column to NaN if the corresponding values in another column are also NaN

Question

The goal is to maintain the relationship between two columns by setting to NaN all the values from one column in another column.

Having the following data frame:

df = pd.DataFrame({'a': [np.nan, 2, np.nan, 4],'b': [11, 12 , 13, 14]})

     a   b
0  NaN  11
1    2  12
2  NaN  13
3    4  14

Maintaining the relationship from column a to column b, where all NaN values are updated results in:

     a    b
0  NaN  NaN
1    2   12
2  NaN  NaN
3    4   14

One way that it is possible to achieve the desired behaviour is:

df.b.where(~df.a.isnull(), np.nan)

Is there any other way to maintain such a relationship?

`Is there any other way...`. What's *wrong* with your current method? Are you looking for cleaner syntax, a more efficient solution, or something else? — jpp, Aug 06 '18 at 15:38

score 9 · Accepted Answer · answered Aug 06 '18 at 15:24

9

You could use mask on NaN rows.

In [366]: df.mask(df.a.isnull())
Out[366]:
     a     b
0  NaN   NaN
1  2.0  12.0
2  NaN   NaN
3  4.0  14.0

For, presence of any NaN across columns use df.mask(df.isnull().any(1))

answered Aug 06 '18 at 15:24

Zero

1

You can also use `inplace=True` for the changes to stick. – jpp Aug 06 '18 at 15:37

score 3 · Answer 2 · answered Aug 06 '18 at 15:47

3

Using pd.Series.notnull to avoid having to take the negative of your Boolean series:

df.b.where(df.a.notnull(), np.nan)

But, really, there's nothing wrong with your existing solution.

answered Aug 06 '18 at 15:47

jpp

score 2 · Answer 3 · answered Aug 06 '18 at 15:31

2

Another one would be:

df.loc[df.a.isnull(), 'b'] = df.a

Isn't shorter but does the job.

answered Aug 06 '18 at 15:31

zipa

score 1 · Answer 4 · answered Aug 06 '18 at 15:24

1

Using dropna with reindex

df.dropna().reindex(df.index)
Out[151]: 
     a     b
0  NaN   NaN
1  2.0  12.0
2  NaN   NaN
3  4.0  14.0

answered Aug 06 '18 at 15:24

BENY

This solution would only work across the columns, right? I would like to be able to apply it to a single column or a selected set of columns. – Krzysztof Słowiński Aug 07 '18 at 00:48

score 1 · Answer 5 · answered Aug 06 '18 at 15:47

Using np.where(),

df['b'] = np.where(df.a.isnull(), df.a, df.b)

Working - np.where(condition, [a, b])

Return elements, either from a or b, depending on condition.

Output:

>>> df
    a       b
0   NaN     NaN
1   2.0     12.0
2   NaN     NaN
3   4.0     14.0

5 Answers5