Dropping columns or inputing numbers

Question

After looking at the various different ways of inputting data to replace NaN in a dataset vs. dropping observations or columns based on a threshold, the right technique is still is very confusing. I know that this must be treated on a case by case basis so I will give a context:

I have a dataset of ~15k observations and over 40 columns. Col1 to Col6 have high missing values because the data simply does not exist. For example Col1 could be the average number of days between 2 consecutive transaction. If a customer only purchased once then the average is NULL.

What would be the appropriate technique to approach this?

column_name	Count	Percent
Col1	12000	80%
Col2	11500	78%
Col3	10200	65%
Col4	10000	62%
Col5	8000	40%
Col6	7500	36%
Col7	2000	7%

Answer 1

Columns 1 to 6: if the data is missing because it does not exist, does that tell you something about the variable/target/customer? If so, you want to preserve that information in your imputation.

For instance, if Column X is the average number of days since the last transaction, does a missing value mean that this is a new customer?

If that's the case, then one approach is to replace the missing values with a distinct value that allows you and your model pipeline to recognize that no such event has happened. So, missing values in Column 1 could be replaced with -1 say.

Column 7: there are various approaches, including replacing missing values with a statistic based on non-missing values (e.g. mean, median, mode) and more advanced approaches like modeling Column 7 using other columns and using model predictions to replace missing values (MICE).

Answer 2

If 80% of the values for a feature are missing, you probably should drop that feature. There is just not that much signal in the remaining 20% of values. Also, the reason that the data is missing will most likely impact the modeling.

Many of the other features also have more data missing than the present.

Even at a 40-36% missing rate, the feature is suspect.

That leaves a single feature with a 7% missing rate. The most powerful imputation techniques use existing values from other columns to predict a missing value in a single column. Those techniques should not be applied since the other columns are suspect.

Single column imputation techniques (e.g., replace missing with median or mode) could be possibly be applied to Col7.

Depending on the goals of the project and how much you want to trust the result, it appears that only a single feature (Col7) should be used.

Answer 3

An interesting solution is to measure uncertainty of your model in order to quantify the quality of each result, because some cases would have a low uncertainty, some other cases wouldn't.

Here is a paper that explains how to do it:

https://arxiv.org/abs/1506.02142

Answer 4

Answer

Take two step approach.

• What should be imputed at missing data points?
• Can we add a new column to indicate missing second transaction?

E.g. First, impute the missing values with a negative value. Second, create additional feature say Col1_flag which will have binary value yes and No. Yes indicating missing second transaction- the reason for NaN, and No indicating normal scenario where the Col1 has legit value. Now, this you will need to do for all the 6 columns i.e. impute with a negative value for missing values and create a new feature for each such column to indicate condition of transaction. Therefore, you will have 6 new features also to be included in modeling. This work successfully, please try and let me know.

@Roger a detailed discussion below: Here is a simple approach that works for filling NaN or Null for the transactional data points which have large number of missing values due to nature of feature and one needs to keep such columns to ensure business logic flow into the machine learning model when training.

Imputation

Suppose, Col1 could be the average number of days between 2 consecutive transaction. If a customer only purchased once then the average is NULL. First, the imputation for the missing values need to be numeric. And because there exists only one transaction, we cannot calculate difference, mean, median, or std. Therefore, in such scenario, impute the missing values in the Col with a large negative value e.g., -9999. This will help send signal to machine learning model about special importance of missing value at the same time keeps the transaction entries intact. Thus, we don't compromise on number of data points.

column_name	Count	Percent	Missing value imputation
Col1	12000	80%	-9999
Col2	11500	78%	-9999
Col3	10200	65%	-9999
Col4	10000	62%	-9999
Col5	8000	40%	-9999
Col6	7500	36%	-9999
Col7	2000	7%	-9999