Column With Many Missing Values (36%)

Question

Hello this is my first machine learning project, I got a dataset with 18.000 rows and I have a column with 4244 values missing.

I don't know why the values are missing since when it's appropriate there's a 0 value in it. The dtype of the column is int64 I consider this column usable and would like to implement it to the model.

Could you please help me with how to deal with this problem, or lead my to a resource to teach me how to deal with this ?

Answer 1

I don't know why the values are missing since when it's appropriate there's a 0 value in it.

The first step is to check with some SME (Subject Matter Expert) or the Data Custodian. I can't tell you how many times I've built a model/started analysis just to figure out that the data was wrong. Try to figure out the reason behind the Nulls/0.

Besides that there are many ways to handle missing data a few are below:

• Remove records with this missing value in your column. If this is an important column to your model it may be best to get rid of that record depending on the shape (rows x cols/features) of your dataset. Don't throw off the results of your model because there's some data that may throw it off (even if you use some of the methods below)
• Mean/Median/Mode Impute - A common method of handling missing data is to fill the missing values with the column's mean or median (rarely do you use the Mode).
• Fill the values that creates a normal distribution - it depends on your data, but filling the values so you get normally distributed column data can be beneficial
• Try all these methods and more - When you start modeling you'll learn to "throw stuff at the wall" and see what sticks. Look at your model results, talk with SMEs, and think about what makes sense. Some ways of handling missing data will work better with different models/datasets. Experiment and have fun!

Answer 2

What you want to do is called imputation of missing values. There are some different strategies. Commonly you use the column mean, median or a value that serves as a good default.

If you are using a Pandas DataFrame then you can do:

Replace with 0

df = df.fillna(0)

Replace with column mean

df = df.fillna(np.mean())

Replace with column median

df = df.fillna(np.median())

If you are using numpy you could do:

Replace with 0

X = np.nan_to_num(X)

Replace with mean

col_mean = np.nanmean(X, axis=0)
inds = np.where(np.isnan(X))
X[inds] = np.take(col_mean, inds[1])

Replace with median

col_median = np.nanmedian(X, axis=0)
inds = np.where(np.isnan(X))
X[inds] = np.take(col_median, inds[1])

If you want some reading: imputation strategies