# Unexpected behavior when using SortBy as a dataset filter

I am still experimenting with datasets. Recently I tried to query the Titanic example to get info on the oldest passengers. Here is what I tried

titanic = ExampleData[{"Dataset", "Titanic"}];
titanic[Select[#age > 65 &]]


That's fine, but I really wanted the data sorted by age. The documentation for datasets mentions that SortBy can be used as filtering operator, but gives no further details on it use that I could find. So I was left to experiment on my own. Here is my second attempt.

titanic[Select[#age > 65 &] /* SortBy["age"]]


The result shows sorting, but the "age" category is only locally sorted within each "class" category. That is not what I wanted. I wanted the sort to treat the "age" category as if it were the major key of the dataset. Does anyone know how to dot that?

I did find a work-around but it's weird.

titanic[Select[#age > 65 &], RotateLeft[#, 1] &][SortBy[42]]


Note that the argument to SortBy is complete nonsense, but this last code works as I want. Can anyone explain this result?

One last question. Since there is essentially no useful documentation on using SortBy in the way I have above, it's hard to dispute any behavior it shows. Nevertheless, I ask if you think I should report the behavior I have observed to Wolfram tech support as a possible bug?

-
@Karsten7. The reason I picked 42 as the argument for SortBy was to emphasize that any inappropriate argument would work in the last example. Your answer tells me what the appropriate argument is. For which I thank you. It's hard for me to believe, after some much needed sleep, that I didn't think of using #age &, but there is no getting around that I didn't. –  m_goldberg Aug 2 at 17:45

I do not believe that this behaviour is a bug. The correct usage would be SortBy[#age&] or SortBy[Key@"age"]. The rest of this response will explain these assertions.

The crucial point is that the "age" argument in SortBy["age"] is not conferred with any special meaning on account of SortBy being used as a Dataset operator.

In the absence of such special treatment, SortBy["age"] gives us the same result as if we used it as a normal function outside of a query. That is, the result is the same as if we say SortBy[Identity] or simply Sort. This point will be elaborated upon later in this response. But for now let us take up why SortBy arguments receive no special treatment in a dataset query.

Dataset Query Operators

One might reasonably expect that since titanic[All, "age"] returns the age values from all of the associations, then by extension titanic[SortBy["age"]] should sort by them. Unfortunately, this is not so. To understand why, we must delve into what exactly constitutes a Dataset operator.

The Dataset documentation tells us:

Dataset[...][op1, op2, ...] is equivalent to Query[op1, op2, ...][Dataset[...]].

In turn, the Query documentation describes a domain specific language or DSL that defines query operators for hierarchical data. This is somewhat like the Graphics DSL for graphical operations. {Red, Circle[]} is just an inert list as a free-standing expression, but it has special meaning inside a Graphics form.

So it is with Query operators: "age" is just a string by itself, but the documentation tells it takes the role of a key selector descending operator within a Query. Indeed, all of the descending operators have special treatment in the query context.

Very few "ascending" operators are given special treatment. The documentation calls out the following special cases, called "ascending subquery operators":

Query[...]                    perform a subquery on the result
{op1, op2, ...}               apply multiple operators, yielding a list
op1 /* op2 /* ...             apply op1, then apply op2, at the same level, etc.
<|key1->op1,key2->op2,...|>   apply multiple operators, yielding an association
{key1->op1,key2->op2,...}     apply different operators to specific parts


Note carefully the use of op1 and op2 in the descriptions, and well as the explicit statement that they refer to operators.

In contrast, the description of SortBy makes no mention of operators:

SortBy[crit]     sort parts in order of crit

Thus, a close reading tells us that SortBy parameters are not operators. This is true for all ascending operators that do not appear in the "subquery" list above.

One might accept that this is documented behaviour, but then wonder why SortBy, Merge, Counts and all the other ascending operators do not accept operator arguments?

Allowing operators in arbitrary ascending operators would cause confusion. Observe that any expression that is not explicitly designated as a descending operator is by definition an ascending operator. This includes all user-defined functions. How can the query evaluator decide whether to treat any given argument as an operator? Which arguments to Histogram should be operators? What about the arguments of a user-supplied function? It is difficult to tell without contextual knowledge. This would mean either long lists of exceptions, or some elaborate syntax to designate exceptions. In my opinion, WRI chose wisely to go with the exhibited simple rules.

What does SortBy["age"] mean?

Earlier, it was stated that SortBy["age"] is the same as SortBy[Identity]:

list = { <| "class" -> "1st", "age" -> 99 |>, <| "class" -> "2nd", "age" -> 1 |> };

SortBy["age"] @ list === SortBy[Identity] @ list === Sort @ list

(* True *)


Why? SortBy["age"] specifies that the list is to be sorted by the result of applying "age" to each associations, as if we were sorting like this:

Sort["age" /@ list]

(* {age[<|class->1st,age->99|>],age[<|class->2nd,age->1|>]} *)


Since the head "age" is inert, it is simply wrapped around each association. This wrapper does not change the lexicographic order of the list elements. So the result ends up being sorted first by class and then by age since that is the order that the keys appear in the association.

If the keys were reversed, then the age sort would appear to work... but only by blind luck!

list2 = { <| "age" -> 99,"class" -> "1st" |>, <| "age" -> 1, "class" -> "2nd" |> };

SortBy[list2, "age"]

(* {<|age->1,class->2nd|>,<|age->99,class->1st|>} *)


If we wish to reliably sort by age, then we need to use one of the following forms:

SortBy[list, #age&]

(* {<|class->2nd,age->1|>,<|class->1st,age->99|>} *)

SortBy[list, Key["age"]]

(* {<|class->2nd,age->1|>,<|class->1st,age->99|>} *)


These both correctly extract the age key from each assocation.

But It Could Have Worked...

Having said all that, I will note in closing that if WRI had added an up-value to Association along the lines of...

s_String @ a_Association ^:= a @ s


... then we would not be having this discussion. The original example would have worked, and all without having to introduce any exceptional cases into the Query DSL.

-
Nice answer. It could also have been a subvalue on String :P. Query isn't necessarily given special treatment as an ascending operator I suppose, right? –  Rojo Aug 3 at 2:21
@Rojo "Yes" on both points. Query always treats its parameters as operators, so it is not receiving special treatment when inside another query. I'll leave it in the quoted extract since that it how it appears in the documentation. –  WReach Aug 3 at 14:46
Really nice summary -- I couldn't have said it better myself. I should mention I added Key[...] @ Association[...] as sugar for key lookup at pretty much the last minute, it would have been much harder to push through special treatment of strings as well (though perhaps that is consistent, given the other special treatment of strings in e.g. Part). –  Taliesin Beynon Aug 7 at 18:27

You have to use a pure function also for SortBy

titanic[Select[#age > 65 &] /* SortBy[#age &]]


Since #age will pick out the values of the key "age" in the association.

-
Is there any way to write a single Query[ ][Dataset] like this without the perversion of /* –  Martin John Hadley Aug 2 at 20:45
@MartinJohnHadley /* is just RightComposition. There are many ways, e.g.: titanic[RightComposition[Select[#age > 65 &], SortBy[#age &]]], Query[SortBy[#age &]@*Select[#age > 65 &]][titanic], titanic[Select[#age > 65 &]][SortBy[#age &]], SortBy[titanic[Select[#age > 65 &]], #age &], Query[SortBy[#age &]@*Select[#age > 65 &]]@titanic. –  Karsten 7. Aug 2 at 21:59
all of which are pretty horrendous and really do show the benefit of /* existing, thank you +1 –  Martin John Hadley Aug 2 at 23:38
I very much wish I could accept your answer as well as WReach's. –  m_goldberg Aug 3 at 14:23