0
$\begingroup$

What is the most efficient way to create a new column based off of nan values in a separate column (considering the dataframe is very large)

1 2 3
4 5 NaN
7 8 9
3 2 NaN
5 6 NaN

Should give

1 2 3    0
4 5 NaN  1
7 8 9    0
3 2 NaN  1
5 6 NaN  1

EDIT:

What if it were based on 2 columns? Like:

1 2   3    0
4 NaN 1    1
7 8   9    0
3 2   NaN  1
5 NaN 2    1
$\endgroup$
0
$\begingroup$
In [8]: %timeit df['B'] = df['A'].isnull()*1                                    
517 µs ± 145 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
In [7]: %timeit df['B'] = df['A'].isnull().astype(int)                          
283 µs ± 14.6 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
In [12]: %timeit df['C'] = np.where(np.isnan(df['A'].values), 1, 0)                                          
105 µs ± 5.97 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)
In [13]: %timeit df['C'] = np.where(df['A'].isnull(), 1, 0)                                                  
227 µs ± 12.3 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

Seems like the full numpy solution (In [12]) is fastest.

EDIT:

For across multiple columns

df['C'] = np.where(np.any(np.isnan(df[['A', 'B']])), 1, 0)
$\endgroup$
3
  • $\begingroup$ Thanks! I have a small query, what if we were looking for nan in 2 columns (Edited above in the question) ? $\endgroup$
    – jillyjill
    Nov 24 '19 at 4:22
  • $\begingroup$ @jillyjill See my edit $\endgroup$
    – m13op22
    Nov 24 '19 at 6:22
  • $\begingroup$ Thanks. but I get the error TypeError: ufunc 'isnan' not supported for the input types, and the inputs could not be safely coerced to any supported types according to the casting rule ''safe'' $\endgroup$
    – jillyjill
    Nov 26 '19 at 3:47
1
$\begingroup$

Try this -

Column is your column with NaN values and column B is the new column

df['B'] = df['A'].isnull()*1
$\endgroup$

Not the answer you're looking for? Browse other questions tagged or ask your own question.