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

enter image description here

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

enter image description here

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.

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

However, this is returning empty sub-datasets.

enter image description here

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.

enter image description here

  • $\begingroup$ Fyi, the inner Dataset are typically unnecessary $\endgroup$ Nov 23, 2015 at 19:24

2 Answers 2


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

resulting Dataset

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

data[GroupBy[groups, First -> Last]["Group1"]]
  • $\begingroup$ I'm a bit confused. data[{"x", "y"}] errors as expected so how is data[groups[Select[#Name == "Group1" &] /* Lookup["Id"]]] working as it essentially is passing the same thing. May you explain how this is working in the answer? $\endgroup$
    – Edmund
    Nov 17, 2015 at 12:43
  • $\begingroup$ +1 Wow, a tip o' the hat for jamming so much cleverness into a single query. Kudos for figuring out that Dataset["x"]@Dataset[<|"x" -> <|"a" -> 1|>, "y" -> <|"a" -> 2|>|>], will cause the inner expressions like <|"a"->1|> to be treated as plain data instead of as operators. But also using this to flip the query/data relationship: mind-blowing. I wonder how stable such subtle behaviour will be between releases? $\endgroup$
    – WReach
    Nov 17, 2015 at 19:08
  • $\begingroup$ @WReach Is your comment to be taken as a warning that this method should not be used? $\endgroup$
    – Edmund
    Nov 17, 2015 at 20:15
  • 1
    $\begingroup$ @Edmund No, I would use the method if it fills your needs. My question may be rhetorical but it is genuine (as is my praise). I am only musing upon the subtle changes in Query behaviour that we have seen with each 10.x release to date. I'm hopeful that such changes are becoming less frequent. $\endgroup$
    – WReach
    Nov 17, 2015 at 20:25

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:


The dataset elements converted to RSparseMatrix objects:

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

enter image description here

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

grouping = 
  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 /@ %

enter image description here

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.

  • $\begingroup$ An interesting solution (+1) but, as you noted, I'd much prefer to stick with Dataset throughout rather than introduce a dataset-esque structure. $\endgroup$
    – Edmund
    Nov 17, 2015 at 12:38
  • $\begingroup$ @Edmund Right, I understand. It is just this problem and my solution would fit very well in the discussions I had at WTC 2015 about Datasets and Mathematica vs R. $\endgroup$ Nov 17, 2015 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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