# Conditional selection of rows from a dataset

How can one select rows "a" and "b" from the following dataset where "b" values are larger than 8?

keys = {"a", "b", "c", "d"};
vals = Partition[Range[20], 5]


Notice this is a row-oriented dataset. If we were to transpose it and convert it to a column-oriented dataset (e.g. Excel and pandas format), the circled items are what I'm after

Dataset[Transpose[dataset]]


The output should be the following row-oriented dataset:

• all the values of a row? or just select the row when some value will be greater than 8? Anyway, your requirements are vague. Please, elaborate... – José Antonio Díaz Navas Feb 6 '18 at 12:41
• So it would be: <|"a"->{4, 5}, "b"->{9, 10}|> – Miladiouss Feb 6 '18 at 12:45
• why |"a"->{4,5}|? Sorry, I cannot see where it comes from... – José Antonio Díaz Navas Feb 6 '18 at 13:11
• @JoséAntonioDíazNavas, I have added pictures and more details. Hope that clarifies it. Thanks for your feedback. – Miladiouss Feb 6 '18 at 22:40

You may have a good reason for your dataset structure, but the question as written seems to suggest a different structure (named columns instead of named rows) and thereby a simpler answer:

keys = {"a", "b", "c", "d"};
vals = Partition[Range[20], 5]
ds = Dataset[AssociationThread[keys, #] & /@ Transpose@vals]
ds[Select[#b > 8 &], {"a", "b"}]


I have assumed the doubled 18 was a typo.

• Yes. The 18 was a typo. I've fixed it. Thank! – Miladiouss Feb 6 '18 at 21:24
• So, your ds is a "table with named columns". Can we do exactly what you did for a "table with named rows" without transposing back and forth? – Miladiouss Feb 6 '18 at 22:45
• Why are named columns favored, everywhere? Shouldn't be just as easy to deal with named rows? Aren't named columns less efficient since there is so much redundancy? Or the redundancy is just in output representation? – Miladiouss Feb 6 '18 at 22:57
• @Miladiouss I have no idea what the design considerations were, but I find it natural to focus on the simplest case, where a dataset is effectively a (wrapped) list of associations. This has a good match to database theory, which in turns helps explain the query operations. I presume (but do not know) that the representation in memory exploits this structure as popular databases do. – Alan Feb 7 '18 at 13:37

How about one of these two options?

Two simple queries:

dataset[{"a", "b"}][{"b" -> Select[Greater[#, 8] &]}]


One slightly more complicated query:

dataset[<|"a" -> #a, "b" -> Select[#b, Greater[#, 8] &]|> &]


This will give the desired result:

result =
dataset[
{All, Position[#b, b_ /; b > 8]&} /*
(Query[{"a", "b"}, #[[2, All, 1]]][#[[1]]]&)
]


... although the Dataset visualizer is not very good at rendering lists within associations. It will do slightly better if we rewrap the result:

result // Dataset


As Alan notes in his response, Dataset works more naturally with lists of associations than associations of lists.

Edit For Updated Question

The result shown here is structurally the same as the input. However because there are only two values per key, the Dataset visualizer does not show it in the same format. Consider:

<|"a" -> Range[2]|> // Dataset


<|"a" -> Range[4]|> // Dataset


<|"a" -> Range[5]|> // Dataset


The visualizer is using a heuristic with a cutoff of four values.

If the visualization is more important that the result structure, we can force the desired appearance like this:

result // Query[All, List]


Caveat: Dataset visualization heuristics change from release to release. The screenshots in this response are current as of Mathematica 11.2.