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%

4 Answers 4


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).


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.


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:


  • $\begingroup$ Can you elaborate on how this approach may be used to handle missing values? $\endgroup$
    – Jonathan
    Jul 12, 2021 at 14:36
  • $\begingroup$ My proposal comes after the other ones. I don't mean to apply exactly the solution described in the publication, but to apply the same logic by having a uncertainty score for each result. This case suggest a high level of entropy, and should focus on dealing primarily with uncertainty, rather than just applying well known methods of ML. $\endgroup$ Jul 13, 2021 at 8:50


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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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