# Dropping columns or inputing numbers

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%

The best technique to handle missing data comes from understanding your data better

Step 1: Do a exploratory data analysis along with your missing data. Python package missingno helps to Visualize data with all missing values. Here's the python package link

Step 2: Do analyze the nature of missingness to help you better understand how to handle missing data. Based on the reasons of missing data, there are three types of missing data

• MCAR(missing completely at random): Missing data values do not relate to any other data in the dataset and there is no pattern to the actual values of the missing data themselves.
• MAR(missing at random): Missing data do have a relationship with other variables in the dataset. However, the actual values that are missing are random.
• MNAR(missing not at random): The pattern of missingness is related to other variables in the dataset, but in addition, the values of the missing data are not random.

Step 3: Utilize right techniques to handle missing data

1. Deletion methods
• Listwise deletion: ideal for MCAR
• Pairwise deletion: ideal for MAR or MCAR
2. Single imputation:
• Mean/Median/Mode substitution
• Regression imputation
• LOCF(Last observation carried forward)
3. Model-Based methods:
• Maximum likelihood: best maximum likelihood technique is EM (Expectation-Maximization)
• Multiple Imputation: MICE algorithm, Amelia(ideal for time series) are few packages that handle multiple imputation.

Look into these for better understanding of your missingness

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:

https://arxiv.org/abs/1506.02142

• Can you elaborate on how this approach may be used to handle missing values? Jul 12 at 14:36
• 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. Jul 13 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.

### 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