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

Question

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. }

Answer 1

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}
*)

Answer 2

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.

Answer 3

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.

Answer 4

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}}

Answer 5

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 &]

Answer 6

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.