With “named row” nested Datasets: How to Select or Filter rows by row key with criteria from another Dataset?

Question

After some calculations and grouping I have a Dataset similar to the following form:

data = Dataset@<|
   "A" -> Dataset@<|
      "x" -> <|"c1" -> 22, "c2" -> 34|>,
      "y" -> <|"c1" -> 1, "c2" -> 18|>,
      "z" -> <|"c1" -> 11, "c2" -> 2|>
      |>,
   "B" -> Dataset@<|
      "x" -> <|"c1" -> 13, "c2" -> 4|>,
      "y" -> <|"c1" -> -2, "c2" -> 5|>,
      "z" -> <|"c1" -> 33, "c2" -> 12|>
      |>
   |>

The row names are items of a groups located in another dataset with form similar to the following:

groups = Dataset@{
    <|"Name" -> "Group1", "Id" -> "x"|>,
    <|"Name" -> "Group1", "Id" -> "y"|>,
    <|"Name" -> "Group2", "Id" -> "z"|>
   }

I would like to Select (or the equivalent) the named rows in the sub-datasets of data that have row keys in "Group1" of groups. This is to be in a Manipulate or DynamicModule where the user will select the group name from groups and have final calculations made and displayed from that group in data across the rows.

I can get a list of the ids for the a group with:

Normal@groups[Select[Function[{row}, row["Name"] == "Group1"]], "Id"]
(* {"x", "y"} *)

As I understand data[All, op] should run operator op on the sub-dataset in each row of data. I planned on using KeyValueMap in a Select for op like so.

data[All,
 Select[
  KeyValueMap[
    Function[{key, value},
     ContainsAny[{key}, 
      Normal@groups[Select[Function[{row}, row["Name"] == "Group1"]], "Id"]]
     ], #] &]
 ]

However, this is returning empty sub-datasets.

How do I get the following as my result? I've had a look at JoinAcross but it doesn't seem to be for this purpose.

Answer 1

First, we want to Select the groups where the "Name" is "Group1", which we do like so:

In[1]:= groups[Select[#Name == "Group1" &] /* Lookup["Id"]] // Normal
Out[1]= {"x", "y"}

From there, we want to call data on our Dataset of keys; note that this won't work if we just pass it a list of keys instead. I'm actually not sure precisely where this behavior is documented; it comes from having nested Datasets, though.

In[2]:= data[groups[Select[#Name == "Group1" &] /* Lookup["Id"]]]

There are many ways of accomplishing this; another way depends on GroupBy:

data[GroupBy[groups, First -> Last]["Group1"]]

Answer 2

Probably using only Dataset objects is a requirement. The solution shown here is in the same vein as the one given for How to merge Datasets representing matrices with named rows/columns?. I.e. this solution does not use Dataset objects but RSparseMatrix objects.

Load the package:

Import["https://raw.githubusercontent.com/antononcube/MathematicaForPrediction/master/Misc/RSparseMatrix.m"]

The dataset elements converted to RSparseMatrix objects:

rmats = ToRSparseMatrix /@ (Normal[Values[data]]);
MatrixForm /@ %

Here is a conversion of groups into an association (I assume it can be done in a better way):

grouping = 
 Association[
  Map[{#[[1, 1]] -> #[[All, 2]]} &, 
   GatherBy[Values /@ Normal[groups], #[[1]] &]]]

(* <|"Group1" -> {"x", "y"}, "Group2" -> {"z"}|> *)

Now the subsetting with row names corresponding to a group is easy:

Map[#[[grouping["Group1"]]] &, rmats];
MatrixForm /@ %

This solution is fairly close to a solution of the same problem in R. I would have given an R/RLink solution, but the conversion of datasets into R's data frames did not seem to be easy.