5
$\begingroup$

Consider a dataset with missing values:

ds={<|"timestamp" -> 
DateObject[{2000, 1, 1, 1, 0, 0}, "Instant", "Gregorian", 2.], 
"BASCH" -> 108., "BONAP" -> Missing["Unrecognized", "n/d"], 
"PA18" -> 65., 
"VERS" -> 47.|>, <|"timestamp" -> 
DateObject[{2000, 1, 1, 2, 0, 0}, "Instant", "Gregorian", 2.], 
"BASCH" -> 104., "BONAP" -> 60., "PA18" -> 77., 
"VERS" -> 42.|>, <|"timestamp" -> 
 DateObject[{2000, 1, 1, 3, 0, 0}, "Instant", "Gregorian", 2.], 
"BASCH" -> 97., "BONAP" -> 58., "PA18" -> 73., 
"VERS" -> 34.|>, <|"timestamp" -> 
DateObject[{2000, 1, 1, 4, 0, 0}, "Instant", "Gregorian", 2.], 
"BASCH" -> 77., "BONAP" -> 52., "PA18" -> 57., 
"VERS" -> 29.|>, <|"timestamp" -> 
DateObject[{2000, 1, 1, 5, 0, 0}, "Instant", "Gregorian", 2.], 
"BASCH" -> 79., "BONAP" -> 52., "PA18" -> 64., "VERS" -> 28.|>}

I can get the mean of a given key easily, even with missing values:

no2[Mean, "BONAP"]
(*64.0017*)

But if I try to apply Mean to 2 columns, the Missing values become a problem:

no2[Mean, {"BONAP", "PA18"}]

This returns a dataset with missing values. I suspect that this is not the right syntax, since in the first case the result is numeric, while the second operation returns a dataset. How does one apply a function to several columns?

Edit:

This works:

no2[Mean, #] & /@ {"BASCH", "BONAP", "PA18", "VERS"}

But is not what I'm looking for. I'm looking for a way to do it within the framework of the dataset.

$\endgroup$
  • $\begingroup$ This works: ds[Transpose, 2 ;;][All, Mean]. But this does not work: ds[Transpose /* Map[Mean], 2 ;;]. I am not confident enough with Dataset to answer. $\endgroup$ – Szabolcs Oct 15 at 13:39
  • 2
    $\begingroup$ @Szabolcs What does work on the other hand is ds[Transpose /* Query[All, Mean], 2;;], see my answer (at the bottom). The problem is that the MissingBehavior is only applied to a small hard-coded list of operators, which does not include Map[...] $\endgroup$ – Lukas Lang Oct 15 at 13:42
6
$\begingroup$

The issue

The problem is the structural difference between the two cases:

ds[All, "BONAP"] // Normal
(* {Missing["Unrecognized", "n/d"], 60., 58., 52., 52.} *)

ds[All, {"BONAP", "PA18"}] // Normal
(* {
    <|"BONAP" -> Missing["Unrecognized", "n/d"],  "PA18" -> 65.|>, 
    <|"BONAP" -> 60., "PA18" -> 77.|>, 
    <|"BONAP" -> 58., "PA18" -> 73.|>,
    <|"BONAP" -> 52., "PA18" -> 57.|>, 
    <|"BONAP" -> 52., "PA18" -> 64.|>
   } *)

Note how the second case contains an additional layer is the final structure. The problem now is connected to MissingBehavior: For a small list of hard-coded functions, Query and Dataset invoke special behavior for Missing[...] entries. Looking at the example above, we see that Mean would be applied to a list of associations (each with two entries). Since none of them are Missing[...] (only some of their entries are), no special behavior is invoked. And the default behavior of Mean is something like:

Mean[list_] := Total[list] / Length[list]

This explains why it works without missing entries, and why you get +Missing[...] as part of the result otherwise.

Workaround

To get the mean of each column separately, the easiest thing is to transpose the data:

ds[All, {"BONAP", "PA18"}][Transpose] // Normal
(* <|"BONAP" -> {Missing["Unrecognized", "n/d"], 60., 58., 52., 52.}, 
     "PA18" -> {65., 77., 73., 57., 64.}|> *)

Mean can then be applied to each column separately, with the MissingBehavior being applied correctly:

ds[All, {"BONAP", "PA18"}][Transpose][All, Mean] // Normal
(* <|"BONAP" -> 55.5, "PA18" -> 67.2|> *)

Slightly more compact:

ds[Transpose, {"BONAP", "PA18"}][All, Mean]
(* same result *)

As one query:

ds[Transpose /* Query[All, Mean], {"BONAP", "PA18"}]
(* same result *)

Note: As mentioned by @Szabolcs in the comments, the following (seemingly equivalent) approach does not work:

ds[Transpose /* Map[Mean], {"BONAP", "PA18"}]
(* wrong result *)

The issue is again that Map[Mean] is not on the list of operators handled by MissingBehavior, so the (wrong) default behavior is invoked.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.