Conditional selection of rows from a dataset

Question

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] 
dataset = Dataset@AssociationThread[keys, vals]

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:

Answer 1

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.

Answer 2

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

Answer 3

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.