# what to do if the missing data in one column is based on some value/condition in another column in r?

I have a dataset with 20,000 observations and 19 variables. To start off with I have a gender column which has three levels namely 'M', 'F' and 'U' where U can be taken as not disclosed. Whenever there is a 'U' in the gender column, there is an NA in two of the other columns namely Age and Tenure. This could basically be interpreted as a person who is not ready to disclose their Gender is not ready to disclose their age and tenure. How do I deal with such a situation? Apart from these three columns there are other 16 columns in the dataset that have got meaningful data in them. Would the normal imputation techniques out there like a KNN Imputation help me out in such a case?

Here is my reproducible example that I have tried my best with:

x<-data.frame(gender=c('M','M','F','F','U','F','M','U'),age=c(21,24,20,34,NA,40,56,NA),tenure=c(7,4,5,3,NA,2,4,NA),job=c('Doctor','IT','Banking','Truck Driver','Finance','Agriculture','Electrician','Teacher'),country=c('Australia','America','New Zealand','Sweden','England','France','Denmark','Norway'))


The Dataframe:

 gender age  tenure      job      country
1      M   21     7       Doctor    Australia
2      M   24     4        IT       America
3      F   20     5      Banking    New Zealand
4      F   34     3    Truck Driver Sweden
5      U   NA     NA      Finance    England
6      F   40      2    Agriculture  France
7      M   56      4    Electrician  Denmark
8      U   NA     NA      Teacher    Norway


As you can see from the example above whenever the gender is undefined, there are missing values in both age and tenure and this is the case overall in the entire dataset. What would be the best way to deal with such a situation? And this what is called a Missing at Random data, is that right? Any suggestions would be extremely helpful. Thanks a lot.

• This is Missing Not At Random. – user2974951 Dec 11 '18 at 8:31

If you plan to use LightGBM or XGBoost, the advise is "Do not do anything". These methods treat NA in a specific way, different in each decision tree and the results obtained are usually much better than using imputing.

You kinda answered your question, if you pay close attention you already now the value the gender U has in your dataset. Why don't you train a different classifier (thus, building 2) for the gender U and then choose the classifier based if the user meets the condition of it's gender being "U" or not?.

Also, can you elaborate more on the goal of the model you're building? That might change the approach to the problem.

• It is a dataset that contains details about a list of transactions and the associated user profile and I am trying to build a model to predict whether the transaction was successful or was it cancelled. Different classifier as in? Would you please be able to elobarate a bit on that please? I would like to explore your idea. Thanks. – The Asipiring one Sep 12 '18 at 4:21

More generally, if you know the relationship between your data and missing data, then you can use that relationship to populate the missing values.

If you don't know the relationship between your data and your missing data, you have a few options:

1. Drop the observations with missing data (not always recommended as you are essentially losing information).
2. Replace the missing values with a statistic such as mean / mode / most popular value
3. Train a model(s) to predict the value and replace any missing values with the model predictions (also called imputation).

If you're interested in #3 above, then there is an R package called mice which focusses on imputation.