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?
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!
It's quite simple. Think of your dataset as a matrix with rows (examples) and columns (features).
- 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?