Capturing presence of NAs in a new column for numeric columns

user_2137794 0
Viewed 67

I have a dataset that I want to run decision tree modelling on. The dataset however has NAs in both the numeric and categorical columns.

For the categorical columns, my solution was simple, I used dummy encoding with dummy_na=True on my entire list of categorical columns. All of my columns had _CAT in their name so they were easy to capture.

#get list of cat columns
cat_cols = [col for col in df5.columns if '_CAT' in col]
#dummy encode and capture NA presence
df_new = pd.get_dummies(df_old,dummy_na=True, columns = cat_cols )

The problem is with the numerical columns: I can't impute a mean/median into the NAs as the absence of data has meaning behind it. I can't impute a 0 since it's a valid value for the columns. I could put in something funky like -9999999.9 as it would be such a large outlier, it might set the NAs apart from the rest of the numeric data.

But I was wondering if there was some way I could easily create a column for each numerical column that would have a binary 1 or 0 indicator to show whether that numeric column had a NA in it's row or not.

So If I have this:

  ID Value1_X Class Value2_X
0  1       33     Y     0.01
1  2      101     N     0.05
2  3       25     N      NaN
3  4      245     N      NaN
4  5      NaN     N     0.61
5  6    30000     Y      2.3

It becomes this:

  ID Value1_X  Value1_NA Class Value2_X  Value2_NA
0  1       33          0     Y     0.01          0
1  2      101          0     N     0.05          0
2  3       25          0     N      NaN          1
3  4      245          0     N      NaN          1
4  5      NaN          1     N     0.61          0
5  6    30000          0     Y      2.3          0

Also, all of my numerical columns have _NUM in their names. Is there a way to autocreatee the NA indicator columns for all columns with _NUM in their name, the way I could do for the categorical columns? And if the NA indicator column names could somewhat match the numeric column names the way they do in the above example?

Data to recreate above samples:

data2 = [['1', 33,'Y',0.01], ['2', 101,'N',0.05],
        ['3', 25,'N',np.nan],['4', 245,'N',np.nan],
        ['5',np.nan ,'N',0.61], ['6', 30000,'Y',2.3]] 

df2 = pd.DataFrame(data2, columns = ['ID', 'Value1_X','Class','Value2_X']) 

data3 =  [['1', 33,0,'Y',0.01,0], 
          ['2', 101,0,'N',0.05,0],
        ['3', 25,0,'N','NaN',1],
        ['4', 245,0,'N','NaN',1],
        ['5','NaN',1 ,'N',0.61,0], 
        ['6', 30000,0,'Y',2.3,0]] 

df3 = pd.DataFrame(data3, columns = ['ID', 'Value1_X','Value1_NA','Class','Value2_X','Value2_NA'])
pandas python
2 Answers

Imports

import numpy as np
import pandas as pd
import math

data2 = [['1', 33,'Y',0.01], ['2', 101,'N',0.05],
        ['3', 25,'N',np.nan],['4', 245,'N',np.nan],
        ['5',np.nan ,'N',0.61], ['6', 30000,'Y',2.3]] 

df2 = pd.DataFrame(data2, columns = ['ID', 'Value1_X','Class','Value2_X'])

Checker Function

def func(x):
    if(math.isnan(x)):
        return 0;
    else:
        return 1;

Function Call

df2["value_1X_B"]=df2["Value1_X"].apply(func)

Output

    ID  Value1_X    Class   Value2_X    value_1X_B
0   1   33.0        Y       0.01        1
1   2   101.0       N       0.05        1
2   3   25.0        N       NaN         1
3   4   245.0       N       NaN         1
4   5   NaN         N       0.61        0
user_107813400

You can try something like this:

data2 = [['1', 33,'Y',0.01], ['2', 101,'N',0.05],
        ['3', 25,'N',np.nan],['4', 245,'N',np.nan],
        ['5',np.nan ,'N',0.61], ['6', 30000,'Y',2.3]] 

df2 = pd.DataFrame(data2, columns = ['ID', 'Value1_X','Class','Value2_X'])

df2.assign(**df2.select_dtypes(include='number')
                .isna()
                .astype(int)
                .rename(columns=lambda x: x.split('_')[0]+'_NA'))

Output:

  ID  Value1_X Class  Value2_X  Value1_NA  Value2_NA
0  1      33.0     Y      0.01          0          0
1  2     101.0     N      0.05          0          0
2  3      25.0     N       NaN          0          1
3  4     245.0     N       NaN          0          1
4  5       NaN     N      0.61          1          0
5  6   30000.0     Y      2.30          0          0

Note: I modified your input dataframe such that 'NaN' was really np.nan to get the datatype of the columns to be floats instead of string/object dtypes.

user_63615310
Related