I am currently working with a universe of 100 companies and interested in how they compare on certain metrics. Some companies do not report all metrics that I am interested in so I am reporting them as null vs inserting a 0 value. In the extreme case that I only have one company reporting a metric, is it statically accurate to report that lone value as the average of the entire universe? Or is it more appropriate to divide that lone value by 100 even if the other 99 are null values?
1
-
$\begingroup$ If the whole world only contains these 100 companies and some are missing values, then you simply have a sample instead of the whole population. You could consider trying to construct confidence intervals on the true means, but this may not be entirely straight-forward since you're sampling without replacement from a small population, meaning certain standard statistical ideas won't apply 100%. What exactly do you mean by "compare metrics?" $\endgroup$– dsaxtonNov 24, 2015 at 2:13
Add a comment
|
2 Answers
$\begingroup$
$\endgroup$
There is no single correct way to deal with missing values when calculating an average, however, it would certainly be wrong to divide one single value by 100 (if there are 99 missing values).
Taking the average of the non-missing values
The simplest option would be to just average the numbers based on how many non-missing values you have (i.e. the missing values are null). However, the problem with this is that it might not be very representative and thus would be misleading. You may wish to set a threshold of how many values are present before you average them, and the rest are not reported.
Replacing the missing data
There are various techniques to replace the missing values, for example single or multiple imputation. You could replace missing values with the previous value, or a randomly selected value from the same metric, or every missing value could be replaced by the mean value for that metric. If the data you have fits a model, you could replace the missing values based on what the model predicts.
Another consideration is that perhaps the missing values have something in common. For example, if one of your metrics measured how well the company was doing financially, it would make sense if companies who are doing badly would be more likely to not give you that value!
$\begingroup$
$\endgroup$
4
It's quite simple. Think of your dataset as a matrix with rows (examples) and columns (features).
Two options:
- Don't include that company (delete row)
- Impute some other value into that missing column value
Imputation has a number of methods:
- Mean of column
- Median of column
- Mode of column
- Model based (generate a value based on other rows with similar values in other columns) like Bayesian inference imputation, etc
- etc, etc
For (4), imagine imputing the value of the missing column for the k closest companies in the same sector by market cap. You might imagine even better schemes that build a regression model to actually predict the value based on other examples (rows) on certain columns. This is just an example.
You'll have to find what works best for you. Imputation, however, is always bad - the question is whether it is worse than a smaller dataset and by what degree.
The only arbiter is your test or CV target - does imputation or deletion deliver better, more generalizable models as a result?
answered Nov 24, 2015 at 1:40
-
$\begingroup$ I don't see how point (4) and your description below it are anyhow Bayesian? Could you elaborate a little bit more on why such approach is Bayesian? $\endgroup$– Tim ♦Nov 24, 2015 at 9:43
-
$\begingroup$ See section 25.7, and also these slides for much more in-depth description of bayesian inference imputation. To clarify, the k-closest method was just something else I threw out - not Bayesian. Both however are "model based" imputation methods. $\endgroup$ Nov 25, 2015 at 19:39
-
$\begingroup$ OK, now it is more clear. Just from initial wording of your answer it sounded as you considered knn as a Bayesian method. $\endgroup$– Tim ♦Nov 25, 2015 at 19:50
-
$\begingroup$ Right. The "predict values based on other examples (rows) on certain columns" is the Bayesian part I was referring to. $\endgroup$ Nov 26, 2015 at 2:29