I have a dataset. There are lots of missing values. For some columns, the missing value was replaced with -999, but other columns, the missing value was marked as 'NA'.
Why would we use -999 to replace the missing value?
$\begingroup$
$\endgroup$
5
-
1$\begingroup$ I have never seen -999 myself. So far I have seen empty strings and NAs. $\endgroup$– Ye TianCommented Jul 22, 2016 at 19:51
-
14$\begingroup$ Because they want to horribly mess up results for people who do superficial analysis and don't read documentation carefully! (Being serious though, @Stephan Kolassa has the real answer.) $\endgroup$– Matthew GunnCommented Jul 22, 2016 at 20:45
-
1$\begingroup$ Ask the person who provided the dataset...? $\endgroup$– BlueRaja - Danny PflughoeftCommented Jul 22, 2016 at 21:41
-
$\begingroup$ Better yet are 999 values in measurement columns to indicate the measurement doesn't apply... at least a -999 or a 0 would have the semantic meaning that it's not a valid/important value. 999 is just the worst. $\endgroup$– Adam MartinCommented Jul 22, 2016 at 22:50
-
1$\begingroup$ I feel like I once read that this was a convention from the days of recording data on punch cards. Knocking out a full row of 9s made the missing data clearly visible when looking at the card since it was literally a "missing row". I can't find a reference to back this up, so I'll just leave it as a comment. $\endgroup$– LucasCommented Jul 23, 2016 at 1:13
Add a comment
|
6 Answers
$\begingroup$
$\endgroup$
12
This is a holdout from earlier times, when computer software stored numerical vectors as numerical vectors. No real number has the semantics "I'm missing". So when early statistical software had to differentiate between "true" numbers and missing values, they put in something that was "obviously" not a valid number, like -999 or -9999.
Of course, that -999 or -9999 stood for a missing value is not "obvious" at all. Quite often, it can certainly be a valid value. Unless you explicitly check for such values, you can have all kinds of "interesting" errors in your analyses.
Nowadays, numerical vectors that can contain missing values are internally represented as "enriched" numerical vectors, i.e., numerical vectors with additional information as to which values are missing. This of course is much better, because then missing values will be treated as such and not mistakenly treated as valid.
Unfortunately, some software still uses such a convention, perhaps for compatibility. And some users have soaked up this convention through informal osmosis and enter -999 instead of NA even if their software supports cleanly entering missing values.
Moral: don't encode missing values as -999 or -9999.
answered Jul 22, 2016 at 19:55
-
1$\begingroup$ In addition to it being a holdout from earlier times, there are likely people who have never learned that explicit representation of missing values exists, and so, when they need to insert a missing value, figure that the thing to use is some kind of bogus number. $\endgroup$ Commented Jul 23, 2016 at 4:07
-
10$\begingroup$ This is not just a holdout thing. If I'm writing a tool to do some manipulation of data, reading in columns of numbers is easy in just about any programming language out there. Reading values in, detecting non-numerical values, and parsing them accordingly takes quite a lot more time. And if you think -9999 mucks with the statistics, my group I work with has developed a love of using 1e32 for invalid values. Let me tell you what happens to your variance when you do that! $\endgroup$ Commented Jul 23, 2016 at 5:40
-
1$\begingroup$ Great, succint answer. But adding some historical detail might make it an even better answer. Admittedly, that would take some research. :) $\endgroup$ Commented Jul 23, 2016 at 6:25
-
1$\begingroup$ Such encoding might also be used to denote some other kind of measurement error; i.e. "machine wasn't working" vs. "no one was there to make the measurement". $\endgroup$– Hao YeCommented Jul 23, 2016 at 22:46
-
1$\begingroup$ @qqqwww: it would be best to detect the value used to indicate missingness and convert such entries to true missingness or NA. Discretization of numerical data is usually not a good idea. $\endgroup$ Commented Jul 24, 2016 at 9:13
$\begingroup$
$\endgroup$
5
Such values are for databases. Most databases long ago, and many today, allocated a fixed number of digits for integer-valued data. A number like -999 is the smallest that can be stored in four characters, -9999 in five characters, and so on.
(It should go without saying that--by definition--a numeric field cannot store alphanumeric characters such as "NA". Some numeric code has to be used to represent missing or invalid data.)
Why use the most negative number that can be stored to signify a missing value? Because if you mistakenly treat it as a valid number, you want the results to be dramatically incorrect. The further your codes for missing values get from being realistic, the safer you are, because hugely wrong input usually screws up the output. (Robust statistical methods are notable exceptions!)
How could such a mistake happen? This occurs all the time when data are exchanged between systems. A system that assumes -9999 represents a missing value will blithely output that value when you write the data out in most formats, such as CSV. The system that reads that CSV file might not "know" (or not be "told") to treat such values as missing.
Another reason is that good statistical data and computing platforms recognize many different kinds of missing values: NaNs, truly missing values, overflows, underflows, non-responses, etc, etc. By devoting the most negative possible values (such as -9999, -9998, -9997, etc) to these, you make it easy to query out all missing values from any table or array.
Yet another is that such values usually show up in graphical displays as extreme outliers. Of all the values you could choose to stand out in a graphic, the most negative possible one stands the greatest chance of being far from your data.
There are useful implications and generalizations:
A good value to use for missing data in floating-point fields is the most negative valid number, equal approximately to $-10^{308}$ for double-precision floats. (Imagine the effect that would have on any average!) On the same principle, many old programs, which used single-precision floats, used somewhat arbitrary large numbers such as
1E+30for missing values.Adopt a standard rule of this type to make it easy to invent NoData codes in new circumstances (when you are designing your own database software).
Design your software and systems to fail dramatically if they fail at all. The worst bugs are those that are intermittent, random, or tiny, because they can go undetected and be difficult to hunt down.
-
2$\begingroup$ Of course, if you're using floats, you can just use
NaNfor missing values. That has no ambiguity about whether it's a valid numeric value or not, and (given that most standard floating-point operations are NaN-propagating) will screw up any calculation that mistakenly treats it as a valid data point even more surely and obviously than, say, $-10^{303}$ would. $\endgroup$ Commented Jul 23, 2016 at 17:26 -
$\begingroup$ NaN is Not a Number, NA is Not Available at least in R $\endgroup$– llrsCommented Jul 23, 2016 at 18:43
-
1$\begingroup$ In theory IEEE floats support different kinds of NaNs which would work well as tags for different kinds of missing/invalid data. In practice most programming languages lack proper support for distinguishing such NaNs and are prone to losing that extra information. $\endgroup$ Commented Jul 24, 2016 at 14:18
-
$\begingroup$ Very good except for the sign bit didn't take up any space in the scheme actually used by databases. They would break up into digit pairs of 00-99 and treat those as numbers; but if the number was negative, they would store 255 - # instead of number. Also 127 and 128 were used for +/- inf. $\endgroup$– JoshuaCommented Jul 24, 2016 at 15:16
-
$\begingroup$ @Joshua That might be correct for some databases, but definitely was not the case for many others, especially early in the development. Any database using decimal encoded values could not have used the mechanism you propose, anyway. An example of an early and widely-used PC database that works the way I described is dBase, eventually offered as the dBase III, Clipper, and FoxPro products (acquired by Microsoft and later abandoned in favor of Access). For fixed field width data interchange formats, the minus sign always occupies a character. $\endgroup$– whuber ♦Commented Jul 24, 2016 at 16:20
$\begingroup$
$\endgroup$
You can use anything to encode missing values. Some software, like R, use special values to encode missing data, but there are also software packages, e.g. SPSS, that do not have any special codes for missing data. In the second case you need to make arbitrary choice for such values. You can choose anything, but generally it is a good idea to choose some value that visibly differs from your data (e.g. your data are percentages in 0-100 range, so you choose 999 for encoding missing data, or your data is human age and you use negative values for missing observations). The idea behind it is that by doing so you should be able to notice if something went wrong and the numbers do not add up.
The problem with such encoding is however that you actually can not notice the special encoding and end up with rubbish results.
$\begingroup$
$\endgroup$
Of course, in SPSS, the missing value(s) 999 or whatever IS tagged as a special missing code and handled separately from other values. It may be tabulated separately or excluded entirely. A distinction is made from the result of things like zero division or log(0).
$\begingroup$
$\endgroup$
Are there computed variables in the dataset? Or is this an analytic dataset that comes form merged / sorted data? Some software uses very large negative values to denote missing data. But other software creates missing values with NA or .. When they are discrepant, usually some post processing has led to disagreement.
$\begingroup$
$\endgroup$
In some cases it is useful to encode -9999 values instead of NA. I have come across datasets with such encoding where the intent was to clearly define the limits of data for instance on a map. A quick histogram will reveal these sort of values.
