[ENH] Add ability to flag null values in columns

[anzelpwj]

Brief Description

Hello everyone, I'd like to suggest a new method (I'll tentatively call it flag_nulls) that adds a new column to the dataframe to indicate if there are null values in the row.

If you are preparing a dataframe for machine learning or something, it's important to fill in the null values with something. However, the fact that the data is null could, in fact, be its own feature--for example, if someone is submitting an insurance claim and they haven't provided important information about themselves, that might be a flag that the claim is fraudulent.

Example API

# flag null values in columns
df.flag_nulls(column_name='null_flag', columns=None)
# column_name gives the name of the new column we generate
# columns is a list of the columns that we check for null values. If columns is None, we use all the columns

df1 = pd.DataFrame({'a': [1, 2, None, 4], 'b': [5.0, None, 7.0, 8.0]}

print df1.flag_nulls()

a	b	null_flag
1	5.0	0
2	None	1
None	7.0	1
4	8.0	0

print df1.flag_nulls(columns=['a'])

a	b	null_flag
1	5.0	0
2	None	0
None	7.0	1
4	8.0	0

print df1.flag_nulls(columns=['a'], column_name='flag')

a	b	flag
1	5.0	0
2	None	0
None	7.0	1
4	8.0	0

Notes

• Computerphile agrees with me on this strategy as well ;)
• I would like to work on this issue if that's acceptable.

[jk3587]

Seems like a useful feature to implement, especially if it could be vectorized.

When I do something like this, I do a slow apply method.

df1['null_flag'] = df1.apply(lambda row: any(row.isna()), axis=1).astype(int)

[ericmjl]

Thanks @anzelpwj! I have marked this as being worked on, looking forward to seeing your PR!

[anzelpwj]

Okay, I tested several different approaches. I created some dataframes of random values, and set 5% of entries to be null. First testing was on a dataframe of 10 columns and 10k rows.

Using the apply function with

df.apply(lambda row: any(row[list_of_cols].isna()), axis=1)

and

df.apply(lambda row: pd.isnull(row[list_of_cols]).any(), axis=1)

Were about equivalent, and very slow.

Two much faster approaches are as follows:

# Option 1
null_array = np.zeros(len(df))
for col in list_of_cols:
    null_array = np.logical_or(
        null_array,
        pd.isnull(df[col]))
# Option 2
pd.isnull(df[list_of_cols]).any(axis=1)

Using %timeit on my laptop reveals the following:

num_rows	num_cols	time numpy logical_or	time pd isnull any
10000	10	2.2 ms	2.36 ms
30000	10	2.6 ms	5.13 ms
100000	10	3.74 ms	15.3 ms
300000	10	6 ms	43.6 ms
10000	100	21.6 ms	12.6 ms
10000	1000	220 ms	126 ms
30000	100	24.4 ms	37.2 ms

Given that dataframes are probably going to be much longer than they are wide (and people may be using this on just a few columns), I'm inclined to say that using logical_or is probably the way to go. If we want to get really tricky, I could probably do something to compare the width to the length, but I'm guessing that might be overthinking things for now. I'll go ahead with just the logical_or.

[anzelpwj]

Hmmm, I just thought to try np.any(pd.isnull(df[list_of_cols]), axis=1) instead of the pandas any, and this seems to be somewhat faster than the pandas approach, but still fails compared to the numpy logical_or for long, thin tables. Worth keeping in mind though...