Applying a function on several columns of a dataset fails on Missing

Question

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.

Answer 1

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.