Missing Values in Data [duplicate]

This question already has an answer here:

I have experienced that most of the datasets contain missing values, which make our task bit challenging.

Please let me know how to fill up those missing values in an efficient way? and is there any specific techniques to handle missing values?

marked as duplicate by Ricardo Cruz, Sean OwenSep 1 '17 at 9:24

Various methods are available for fill missing values in data.

1. Ignore the tuple is the simplest and not effective method.

2. Fill the missing value manually.

3. Use a global constant to fill the missing value.

4. Use attribute mean value to fill missing value.

5. Use attribute mean for all samples belonging to the same class as the given tuple.

6. Use most probable value to fill in the missing value (this may be determined with regression, inference tool, or decision tree induction)

Reference:

Data Mining – Concepts and Techniques - JIAWEI HAN & MICHELINE KAMBER, ELSEVIER, 2nd Edition.

Yes there are so many approaches to handle missing data or missing values depending on the task and the property of the data itself. For example in time series you can think about forward filling or even backward filling, max, mean or median over a time lapse. There are also 'smarter' ways like training a model over the available data and try to predict the missing ones. The latest requires much data so can give efficient results. You can refer to these two links for more information.

First of all, if most of your data is missing, you are in trouble anyway. You need to ask why is most of the data missing, and also, why are the data you observed not missing. Being missing is very likely telling you something in your data.

All methods of correcting missing data, including the naive interpolation, mean replacement, and median replacement methods, assume that you can largely ignore the reason for the data being missing - These are the Missing at Random [MAR], and the (much stronger) Missing Completely at Random [MCAR], assumptions. If one, or both, of these are not true, which is very likely if most of your data are missing, then no asymptotically reliable method of imputation is known to exist. This doesn't mean you can do nothing - see here for some suggestions.

In most circumstances, with MAR data, people use model based methods. Essentially these impute a dataset many times, filling in the missing values using plausible models of the missing data, and then run analyses on the ensemble of imputed datasets.

I've usually used the mice package in r. A paper is here. A useful webpage is at web.maths.unsw.edu.au/~dwarton/missingDataLab.html.

If the process which leads to your missing data is not ignorable, in other words, if being missing tells you something, none of this will work. It will produce nice looking numbers, but no-one, you included, will have any idea what they mean.

One of the most common way to fill up the missing values is using linear interpolation : The previous valid values before the missing value and the following valid values after the missing value are used for the interpolation.

For example, in Python-Numpy package, you can use numpy.interpfor filling in missing values using linear interpolation. Here is an example for a simple array:

>> import numpy as np
>> data=np.array([1,2,np.nan,6,9,np.nan,15])
>> print(data)
[  1.   2.  nan   6.   9.  nan  15.]
>> nans, xf= np.isnan(data), lambda z: z.nonzero()
>> data[nans]= np.interp(xf(nans), xf(~nans), data[~nans])
>> print(data)
[  1.   2.   4.   6.   9.  12.  15.]
• I am sorry but this makes sense only in the corner case where you do expect values to vary in a quasi-continuous manner from one row to another; for example time series, stock returns. In a other instances this would be a wrong approach: think about each row as a customer and the missing value being age. – famargar Sep 4 '17 at 16:25
• @famargar, although this is not one solution to all, it is the simplest and most common way. Anyhow, you have to use existing valid data for filling missing ones. In your customers example, you should consider another numerical attribute of your customers' dataset for running interpolation. I mean, 'X= your selected attribute' and 'Y= age'. (and round answers up to the nearest integer ) – moh Sep 5 '17 at 7:36
• Linear interpolation only makes sense under the assumptions I stated above. I elaborate further my example: customer A is 70 year old, customer C is 90, thus customer B is 80? If we take the known age distribution, and assume our services do not depend on age, then the chances for customer B to be 80y old are pretty close to zero. The simplest and most common way to impute values (unless the assumption of continuity of the value to be imputed along rows holds, which is rare) is mean imputation; that is to say, to replace the nulls with the mean of that quantity over the training set. – famargar Sep 5 '17 at 8:29
• @famargar, the mean of the quantity give you just one number, so all the missing values will be filled up with the same number. Interpolation is a simple case of a predictor, maybe for your problem, you should utilize a more task-specific predictor. – moh Sep 5 '17 at 8:41
• There are plenty of ways to impute missing data indeed. What I am commenting on is on what is simplest and more general. I can't answer the OP because what is most efficient really depends on the problem at hand. Linear interpolation is not the most general (you need to have the continuity assumption) and it's not the simplest - the simplest is dropping the rows, the next-to-simplest is mean (or median) imputation. The most sophisticated would be to train a classifier (for categorical variables) or a regression using the other values as dependent variables. Hope that clarifies. – famargar Sep 5 '17 at 11:59