# How do you skip missing data values in a data file?

In scanning through a simple imported file of weather station data, some dates have missing data.

What is a test to use to check whether a datum is valid? I tried If[tempData[index][[4]]≠NaN, ] and If[tempData[index][[4]]≠Indeterminate, ], but neither seemed to work.

Most of my data is valid, and so it is just a matter of skipping/ignoring the occasional missing entry, and not incrementing my accumulators or counters.

The data are already arranged in a Mathematica list, and a missing entry is represented by two adjacent commas with nothing in between.

So, what I need is an If[ ] statement that will trigger (or not trigger) when it encounters such a missing data element.

Here is the code, along with a first snippet of output:

f[x_ /; MemberQ[Range@12, x]] :=
Switch[x, 2, 28, 4 | 6 | 9 | 11, 30, _, 31]

Do[

Do[ sum = 0.0; count = 0; index = 1;

While[index < 20454,

If[Part[tempData, index][[{2, 3}]] == {month, day},

sum = sum + Part[tempData, index][[4]]; count++ ]; index++];

Print[{month, day}, " ", sum, " ", count],

{day, 1, f[month]}

],

{month, 1, 12}

]

{1,1} 87.5 +5  56

{1,2} 60.4 +5  56

{1,3} 59.5 +5  56

{1,4} 54.5 +5  56

{1,5} 37.4 +5  56

{1,6} 39.6 +5  56


I interpret these results to mean that Mathematica found, for these six days in January, the sums of the temperatures for the corresponding days in the 20454 days of data (about 56 years) contained in tempData which had valid data. I think the +5 means that the program encountered 5 Null values, so that the data count should really be 51. If I wanted an average temperature for 01 January based on these data, I would divide 87.5 by 51.

My main concern is not computing the average, or any of the other manipulations I plan to do, but merely how to skip the Null data values. My most recent attempt was to add

If[!Null === Part[tempData, index][[4]], sum = sum + Part[tempData, index][[4]], count++]


but that did worse than not work — the sums and counts are all zero.

Here is a sample of the input data, tempData:

{{1955, 1, 1, 0.4, 1.9, -0.3, 4.2},

{1955, 1, 2, -0.5, 0.5, -1.1, 2.5},

{1955, 1, 3, 0.5, 2.7, -1.9, 0.8},

{1955, 1, 4, 1.4, 2.6, 0.6,"0T"},

{1955, 1, 5, 0.8, 2.3, -0.3, 0},

{1955, 1, 6, 0.7, 3.7, -1.7, 0}, etc. }

-
In the off chance you are new to Mathematica, ... . The first time I tried to test for Null, I almost pulled all of my hair out. value = Null; If[Null == value, "T", "F", "O"] yields T as expected, but value = 1; If[Null == value, "T", "F", "O"] yields O not F as one might think. The trick is when testing for Null to use three ='s. value = Null; If[Null === value, "T", "F", "O"] yields T and value = 1; If[Null === value, "T", "F", "O"] yields F as expected. – mmorris Mar 7 '12 at 0:42
Your comment is very enlightening. Yes, I am entirely self-taught and isolated with MMA, so I am reasonably proficient in some areas, and a dunce in others. The three equals signs (and the word "Null") are both new thoughts. What I think I need is the proper form of "not equals". – R. Peter DeLong Mar 7 '12 at 17:04
If[!Null === value, – mmorris Mar 7 '12 at 17:05
What I think I am getting from the various answers posted is that Mathematica is only able to recognize a Null or Missing value when it is traversing a row and can directly detect the adjacent commas. When it is traversing a column, as in my code, it is unable to recognize a missing value. Hence, one must transpose a data structure, no matter how enormous, and traverse it as a row in order to detect a Null value. Am I wrong? – R. Peter DeLong Mar 8 '12 at 2:53

Missing data is encoded in Mathematica using the inert function Missing. It can take several forms (Missing["reason"]):

Typical examples of "reason" include "NotApplicable", "Unknown", "NotAvailable", "Nonexistent", "Indeterminate", "Variable", "Disputed", or "TooLarge"

You can filter your data using DeleteCases:

DeleteCases[data, _Missing]


Lists with empty fields can be cleaned as follows:

DeleteCases[{1, 2, , 3, 4}, Null]

(*
==> {1, 2, 3, 4}
*)

-

You can use an approach like the following to skip missing data (typically, data imported from Mathematica's *Data functions have this format for missing values)

list = {a, b, Missing["Unknown"], d, Missing["NotAvailable"], f}; (* test list *)
Scan[If[! Head@# === Missing, Print@#] &, list]

Out[1]= a
b
d
f


a missing entry is represented by two adjacent commas with nothing in between.

This is interpreted as Null by Mathematica and you can replace the first argument in If above with ! # === Null and proceed.

-

The data are already arranged in a Mathematica list, and a missing entry is represented by two adjacent commas with nothing in between.

This has the FullForm of Null. Witness:

a = {"dog", "cat", , "bird", "fish"};

a // FullForm

List["dog","cat",Null,"bird","fish"]


Therefore, you need to check if a value is Null and act accordingly.

f[x : Except[Null]] := Print["My pet is a ", x, "."]

Scan[f, a]


My pet is a dog.

My pet is a cat.

My pet is a bird.

My pet is a fish.

-
data = {{1,2,3,4},
{ ,2,3,4},{1, ,3,4},{1,2, ,4},{1,2,3, },
{1,2, , },{1, ,3, },{1, , ,4},{ ,2,3, },{ ,2, ,4},{ , ,3,4},
{1, , , },{ ,2, , },{ , ,3, },{ , , ,4},
{ , , , },
Null,
{5,6,7,8}};

numberOfDataElements = 4;
validData = Select[data, numberOfDataElements == Length[DeleteCases[#, Null]] &];

validData


Results:

{{1, 2, 3, 4}, {5, 6, 7, 8}}

-
This data set can be used to illustrate the sort of problem I am trying to solve. I would like to determine the average value of the valid first entries at level 2, the valid second entries, etc. So, I see eight 1s and one 5 as valid first entries. I would like to add them to get 13, and count them to get 9, for an average = 13/9. – R. Peter DeLong Mar 7 '12 at 16:54
I should add my data set has 22000 rows and 7 columns, and my code must traverse it a few hundred times, looking for featues in various rows. – R. Peter DeLong Mar 7 '12 at 17:08
@R.PeterDeLong numberOfDataElements = 7; – mmorris Mar 7 '12 at 17:11
@R.PeterDeLong Please see the answer starting with your quote above. – mmorris Mar 7 '12 at 20:50
Is it really necessary to create a new data set with the invalid rows removed? I would have to create a new data set for each column I process… – R. Peter DeLong Mar 8 '12 at 1:45

Very late to the party, but... if you have data in precisely the format you describe, you can use the Block trick as follows:

ClearAll[withSkippedMissingElements];
SetAttributes[withSkippedMissingElements, HoldAll];
withSkippedMissingElements[code_] :=
Block[{Null = Sequence[]}, code];


This function allows you to run arbitrary data-manipulation code inside it, as if the missing data just did not exist. Borrowing the test data from the answer of @mmorris, we get:

withSkippedMissingElements[Select[data, Length[#] == 4 &]]

(*
==> {{1, 2, 3, 4}, {5, 6, 7, 8}}
*)


A similar technique was recently used in this answer, where it was demonstrated that it has speed advantages w.r.t. explicit removal of unwanted elements.

However, this will only be safe, in this case, if Null is not used constructively anywhere in the code you run inside this local environment. A safer use of this construct is to only wrap it around the data you have:

Select[withSkippedMissingElements@data, Length[#] == 4 &]

-

This data set can be used to illustrate the sort of problem I am trying to solve. I would like to determine the average value of the valid first entries at level 2, the valid second entries, etc. So, I see eight 1s and one 5 as valid first entries. I would like to add them to get 13, and count them to get 9, for an average = 13/9. – R. Peter DeLong

data = {{1,2,3,4},
{ ,2,3,4},{1, ,3,4},{1,2, ,4},{1,2,3, },
{1,2, , },{1, ,3, },{1, , ,4},{ ,2,3, },{ ,2, ,4},{ , ,3,4},
{1, , , },{ ,2, , },{ , ,3, },{ , , ,4},
{ , , , },
Null,
{5,6,7,8}};

validData = Map[DeleteCases[#, Null] &, Transpose[DeleteCases[data, Null]]];
averagesAsFractions = Map[Sum[i, {i, #}] / Length[#]  &, validData]
averagesAsDecimals = Map[N[Sum[i, {i, #}]] / Length[#]  &, validData]


Results:

{13/9, 22/9, 31/9, 40/9}
{1.44444, 2.44444, 3.44444, 4.44444}


I think everyone looked at your initial question and interpreted it in a slightly different way. For future questions, to help everyone better understand your question, it would be great if you could provide sample data and the results you are expecting.

-
 I'm curious why you posted a separate answer instead of editing the info into your current answer (which already has 5 votes) – rm -rf♦ Mar 7 '12 at 21:12 @R.M This question appears to be a bit of a moving target. As the author continues to clarify what he is looking for. I tried to get the answer in a comment, but it would not fit, nor did it look right. I believe both answers have merit as someone might be looking for either solution. Why do you ask? – mmorris Mar 7 '12 at 21:35