Query: A Functional Programming Construct

Seth J. Chandler
University of Houston Law Center

(RowDefault[, (Spacer1), , (Spacer1), ])  #WolframTechConf

ABSTRACT

This presentation demystifies the Query function for the intermediate user and shows how users at all levels can effectively use it as a functional programming construct. It draws from parts of the author’s new Wolfram Media book, Query: Getting Information from Data with the Wolfram Language.

A World Without Query

This is adapted from Chapter 3 of the book.

The Titanic Problem

Suppose we want to obtain the mean age of persons on the Titanic but grouped by the class in which the person had a cabin.

Data

ro=ResourceObject["Sample Data for Query Book"]

(titanic=ResourceData[ro,"Titanic List of Associations Dataset"])//[MaxItems->5]

I' ll also retrieve data on an imaginary dinghy from the Titanic that contains just a few rows of data. We’ll use it later.

dinghy=ResourceData[ro,"Dinghy List of Associations"]

Non - operator form programming

Not good

GroupBy[titanic,(#class&)->(#age&),Mean]

Better

GroupBy[Select[titanic,NumericQ[#age]&],(#class&)->(#age&),Mean]

How readable is this code?

Query

This is adapted from Chapter 4 of the book.

A basic example

Three arguments and assigned to a symbol :

q =Query[GroupBy[#class&],Mean,#age&]

Notice that the Query can exist without regard to any data structure. It is essentially a recipe for building a function. Although this concept is sometimes difficult for newcomers to functional programming or the Wolfram Language, it’s really no different than writing a function x|->(x^2) or the function Map[Sqrt]. Those functions can exist perfectly well without ever seeing any data.

Applied to data

q[titanic]

Parameterized

We can parameterize a query

classAgeQuery[op_]:=Query[GroupBy[#class&],op,#age&]

classAgeQuery[Mean][titanic]

classAgeQuery[Median][titanic]

Notice that we did not have to deal with missing values. Query is smart enough to figure out (most of the time) how to deal with missing values.

Implicit Query

This syntax avoids having to type query and the data is put first. Notice that it automatically deals with Missing[]. Some people like this syntax; perhaps it is a little more “object-like.” I prefer writing out Query.

titanic[GroupBy[#class&],Mean,#age&]

Options

Query also takes options.

Options[Query]

One option is what to do about Missing data.

q2=Query[GroupBy[#class&],Mean,#age&,MissingBehavior->None];

q2[titanic]

Here, setting MissingBehavior to None would probably be a poor idea. Insted you could set the option to its default value of Automatic. Other times, however you do not want to just ignore missing data.

Using options with implicit Query.

titanic[GroupBy[#class&],Mean,#age&,MissingBehavior->None]

Other options are discussed in the book.

Query Structure

The hardest thing about Query is figuring out the order in which its arguments are applied to an expression. But there’s a great discovery mechanism and with practice you can develop your intuition. Or you can read the documentation for hours.

A Heuristic

Query produces a pipeline of operator forms. The precise pipeline depends on the position and content of each function or composition in the sequence of arguments presented to it. The first argument generally operates on the entirety of the expression. The second argument generally operates at the first level of the expression. A third argument generally creates code that will operate at the second level of the expression and so on. Each subsequent position of an argument in Query generally operates at the next lower level of the expression to which it will ultimately be applied. 

The “generally” qualification is present in each of the preceding sentences for good reason, however. The exact order in which Query operates depends on the content of the functions being used. Fortunately, the exceptions are generally logical. And, as I’ll show, there’s a simple way to determine the order of operations created by Query without having to actually run the Query function on data.

Here’s a simple example. Suppose I wanted to Map the function v over each item at level 1 of an expression and then apply the function u to the entire result. Here’s how I could do it. I’ll use that little expression g[8,h[x,y,9]] to illustrate the result.

Apply functions at different levels of an expression without Query:

expr=g[8,h[x,y,9]];

u[Map[v,expr]]

Here’s how I could do it using Query. I make u the first argument to Query because I basically want it to work at the top level (level 0) and make v the second argument because I want it to work on everything at the first level.

Apply functions at different levels with Query:

Query[u,v][expr]

Discovering the Order of Operations Produced by a Query

For beginners (and others), the best way to determine the pipeline of operations produced by a Query expression is to run Normal on it. Let’s look at my previous example.

Normal shows the order of operations resulting from a Query expression:

Normal[Query[u,v]]

FullForm[%]

Second, notice that the Wolfram Language kernel returns a RightComposition of functions. The /* is a short form for RightComposition. First, the v function is mapped over the data. Then the u function is applied to the result. I like RightComposition because it makes the code more readable. I just read it “and then.” It lets you use procedural syntax on functional constructs.

Here’s another example. I pick this one because, without reading either the rest of this chapter or the Wolfram Language documentation, you might not intuit the order in which operations are done. This is why Normal is so helpful. The operation is extremely fast even when working with big data: it doesn’t need the data on which the Query will ultimately be performed. It thus lets one learn by experimentation and practice.

GroupBy in a Query produces a different order of operations in the pipeline than might naively be expected:

Normal[Query[GroupBy[f],g,h]]

This response is a little hard to read because of the nested Map statements. The idea, however, is that first the grouping is performed. Then the function h is mapped over the prior result at level {2}, and then the function g is mapped over the prior result. Although Normal when applied to Query does not make use of ResourceFunctions to express its output, if it did, the output from the previous Query might look like the following output.

A Map that contains a pipeline that itself contains a Map can be “flattened” with use of the MapLevel ResourceFunction:

GroupBy[f]/*[h,{2}]/*Map[g];

Self - serving Editorial : MapLevel should be promoted to be part of the language .

I cannot say this too often—when in doubt, apply Normal to the Query and study the resulting pipeline. Although the Normal of a Query is not quite a perfect replica of the actual Query, the order of operations for the two is always the same.

By the way, here' s the normal form of our Query q above .

Normal[q]

Order of Operations in Query

Learning Strategies

The hardest part of Query is mastering order of operations. Why, for example, was the previous pipeline Map[v]/*u instead of u/*Map[v]? There are three ways of learning this order. The first is the one I already suggested. Just experiment using Normal. One can learn a lot by playing around. Sometimes, however, repeated experimentation with Normal on different Query expressions devolves into hopeless floundering. It’s often useful and more elegant to get the Query expression right the first time. I now thus present two design principles that, along with the details contained in the official documentation, should help you quickly generate efficient queries.

LIFO: The General Rule

Here’s the first principle. It’s LIFO. Last in, first out. The order of operations is generally in the opposite order of the arguments to Query. By way of example, in the previous Query, the v function is the second argument to Query, so it is put in the pipeline before the u function, which is the first argument.
LIFO isn’t an arbitrary convention. It makes a lot of sense. The operations generally have to start at the lowest level instead of the highest level because a function applied higher in an expression could eliminate subexpressions at lower levels. And if that happened, subsequently applied functions would then have no input on which to operate.
Let’s look at more complicated examples of this general principle. Here is a visualization of Query[u,v,w].

Visualize the pipeline produced by a three-level Query as a tree:

Visualize the pipeline produced by Query as a tree:

labeledQueryTreeForm[q_Query,opts:OptionsPattern[]]:=[q,opts]//(Labeled[#,{q,Normal[q]},{Top,Bottom}]&)

labeledQueryTreeForm[Query[u,v,w]]//Framed

Visualize a Query pipeline produced with composed operators:

labeledQueryTreeForm[Query[u1/*u2,v,w]]//Framed

Here’s a yet more baroque Query. I wish I could tell you that we never do things this complicated, but as you will see, we do. So it’s crucial to understand the order of operations.

Visualize a Query making extensive use of composed operators:

labeledQueryTreeForm[Query[u1/*u2,v1/*v2/*v3,w1/*w2],ImageSize->420]//Framed

Beyond LIFO : Descending and Ascending Operators

The second principle is that Query doesn’t insist on LIFO where there is good reason to believe that doing things out of LIFO order will be efficient and will not wreck anything. This principle is consistent with the reasoning behind the LIFO presumption. One generally operates first at the lowest level because one worries that doing otherwise could destroy data in a way that would make operations at higher levels meaningless. But there are operations whose form gives one confidence that they will not destroy data. Query constructs a pipeline that puts those operations first because doing so is faster and can often save memory. For example, suppose that one wasn’t talking about a little dinghy but the full ocean liner Titanic or all ships transiting the Atlantic. It would be wasteful to go through the entire data structure to get the age of every single passenger and then select the age of just the first three. It would be smarter to get the first three passengers and then determine their age.

“Do things that are efficient and won’t wreck things” is not really a rule that the kernel can implement. Determination of which operators are put first in the pipeline is based on their form, not necessarily their function. So these principles are implemented—pretty well but not perfectly—by imagining the kernel as moving through the expression tree, first going down in a breadth-first manner and then coming back up. (Expression trees generally grow upside down in the computer world.) Only the few operators labeled as descending are executed on the way down. Otherwise, the kernel works in the normal way set forth in its documentation: starting with the leaves of the expression and working its way up the tree. Operators that aren’t descending are called ascending. When in doubt, assume an operator is ascending.

Here’s a picture that may help. Here’s a TreeForm of Query[f,g] applied to the List of Lists {{3,4},{5,6}}. Ordinarily, if the resulting structure wasn’t the result of a Query, the deepest levels would be evaluated first. That is, think of a little creature wandering the (upside down) tree. It keeps heading down, and when it can’t get any further, it goes back up until it can go down over previously untraversed paths. On the way up—but not on the way down—when it finds an expression it can transform, it does so, so here the kernel would keep going down to, say 3, come up to List, go down to 4, come up to List, come up to g and say, “Wait, I can evaluate g[{3,4}]; I’ll do that now.” The creature would wander further and ultimately hit g[{5,7}] and evaluate that. Then it would pass the resulting expression to List (which wouldn’t do anything) and then pass the resulting expression to f.

Using TreeForm to demonstrate the order of kernel evaluations:

TreeForm[Query[f,g][{{3,4},{5,7}}]]//Framed

Descending Operators

I will now discuss some of the most common “descending operators” that cut in line in the operator pipeline formed by Query. Select is a common example. It makes sense to select data first because then the pipeline doesn’t have to process as much at the lower levels. Moreover, Select won’t change the content of the expressions it encounters. It just keeps some while discarding others.

Here’s the result of a Query whose first argument selects values whose total is even and whose second argument maps the addition of 1 to the second part of each of the expressions it confronts. If the conventional LIFO order prevailed, the kernel would first perform #+1& on each of the second parts of {{3,4},{5,7}} and produce {{3,5},{5,8}}. Then Select would realize that only {3,5} has an even total. The result of the Query would be {3,5}.

However, that’s not what happens. Instead, Select gets precedence. It identifies {5,7} but not {3,4} as having a total that is even, so we are left with just {{5,7}}. We then add 1 to the second position of each element of that intermediate result and are left with {{5,8}}.

The Select in this Query gets executed first, even though it is the first argument to Query:

q2=Query[Select[EvenQ[Total[#]]&],MapAt[#+1&,{2}]]

Normal[q2]

q2[{{3,4},{5,7}}]

Here are some other common descending operators that Query inserts early in the operator pipeline it constructs.

Slice Operators

There are a number of built-in Wolfram Language functions that get converted into a special operator: GeneralUtilities`Slice . ` functions that get converted include Span, Take, and Key. The ResourceFunction Slice also gets converted. Anything that gets converted into this form is a descending operator; Query inserts it early in the pipeline. This early insertion makes sense because these operators just filter out unwanted items in a data structure. Here’s a demonstration.

Operators that are transformed into Slice are descending and get precedence:

{Normal[Query[1;;3]],Normal[Query[Take[5]]],Normal[Query[Key[imakey]]]}

When one uses a string as an argument to Query, the kernel assumes one means to use it as a Key. It is thus converted into a GeneralUtilities`Slice  and placed early in the pipeline.

Query treats a string argument as if wrapped by Key and converts into a GeneralUtilities`Slice:

Normal[Query["a",f]]

A Query that extracts the value associated with the "a" key and applies the function f to the result:

Query["a",f][Association["a"->3,"b"->4]]

Variants of Select (SelectFirst, KeySelect, MaximalBy, MinimalBy) likewise just prune out data, so they are “descending,” getting applied on the way down.

Select, KeySelect, and similar operators get precedence:

{Normal[Query[Select[EvenQ],g]],Normal[Query[SelectFirst[EvenQ],g]],Normal[Query[KeySelect[EvenQ],g]],Normal[Query[MaximalBy[Last],g]],Normal[Query[MinimalBy[Last],g]]}//Column

What Else?

Deletes
Cases (sometimes)
(some) Sorting operators

But Form Matters

Here’s an example showing more concretely the importance of form in Query arguments. I show two variants of Sort that might appear identical. The first time, I use Sort. The second time I use Sort[#]&. The results differ because the order of operations is different.

Take a matrix. The order in which Total is applied to the rows depends on whether Sort or Sort[#]& is used:

(matrix={{2,11},{7,0},{-1,9},{4,5},{1,16}})//MatrixForm

{Query[Sort,Total][matrix],Query[Sort[#]&,Total][matrix]}

The book has a more thorough discussion (t.ly/5yD38)

DescendingQ

In cases of doubt, there is a function tucked away in the Dataset` context that can help you determine whether a single operator will be descending or not: Dataset`DescendingQ. (Don’t use this on composed operators.) Here’s an example of how that function works.

Use Dataset`DescendingQ to know for sure if an operator is descending:

Needs["Dataset`"]

AssociationMap[Dataset`DescendingQ,{Select[f],#age&,Sqrt,SortBy[#age&],Take[5],DeleteMissing,DeleteMissing[#,1,2]&}]

Four Complications

I will now deal with four complications: (1) composed operators; (2) GroupBy; (3) subqueries; and (4) “Part Specification-Rule” notation for MapAt.

Complication 1: Compositions of Ascending and Descending Operators

One can have a composed function as an argument to Query, such as f/*g or Select[f]/*g or g/*Select[f] or f/*g/*h or an infinite number of other compositions. If the operators within the composition are all ascending or all descending, then it’s easy. If they are all ascending, then they are applied in the usual way as the kernel heads back up the tree. If they are all descending, then they all are applied early as the kernel heads down the tree.

But what if the composed operators are a mix between ascending and descending operators?

If all the descending operators in a mixed composition come before any ascending operators, the kernel applies all the descending operators first. Then it proceeds in the normal way, which would mean it applies operators in subsequent arguments to Query and only then comes back to apply the remaining operators in the prior arguments. Here’s an example.

Descending operators sometimes stay descending even if RightComposed with ascending operators:

Normal[Query[(Select[f]/*SortBy[g]/*h/*i),j]]

Notice that all of the descending operators in the RightComposition in the first argument to Query come before any of the ascending operators (f and g). So it first applies the two descending operators Select[f] and SortBy[g]. Then it skips the remaining two ascending operators h and i in the RightComposition and goes to the end to apply j. Then it comes back and applies h and i.

However, that’s the only circumstance in which any of the operators in a mixed RightComposition are privileged to come first. Here, I’ve stuck SortBy[h], a descending operator, to the right of g, an ascending operator, in the RightComposition. The RightComposition is now “contaminated,” and evaluation proceeds as if none of the operators were descending.

Usually, descending operators lose their privilege if they mix with ascending operators in a Composition or RightComposition:

Normal[Query[(Select[f]/*g/*SortBy[h]),j]]

Again, when in doubt or when the results from your Query produce errors or just don’t make sense, just Normal the Query before running it. Doing so seldom takes long at all.

Complication 2: GroupBy

GroupBy [ f ] is a special case. This function definitely changes the structure of its argument. It doesn’t tend to reduce the amount of data to be processed. Nonetheless, it’s a descending operator.

You can see this with an example. If the following Query were evaluated in the LIFO way, first it would Map Length over the List of Associations and get a bunch of numbers. It would then try to group those numbers by #sex&, but I’d then get an error message because numbers don’t have a sex. The situation is saved by the Query treating GroupBy as descending. It gets applied first. Since there is one male and four females on the dinghy and I am now mapping Length to an Association, I get an Association with the same keys and values of 4 for female and 1 for male.

GroupBy[f] is a descending operator that inserts a level into the data on which the Query is being applied:

dinghy

Query[GroupBy[#sex&],Length][dinghy]

One can think of Query with GroupBy[f] as increasing the depth of the expression on which it is going to act. (The f in the preceding sentence is just a shorthand for some arbitrary function.) It can be thought of as inserting a layer into the target expression. Thus, a Query with three arguments, the first of which is a GroupBy[f], creates a pipeline that performs the GroupBy[f] first. If the GroupBy[f] is composed with further operators at the same level, they are then performed on the entirety of the data structure according to the usual order of operations.

Here’s an example. I’m trying to get the mean age for each sex (gender) of the passengers. If I were to run this Query, I would get an error because when GroupBy is written as a function, it is not recognized as an operator. Remember, with Query form matters!

A failed attempt at grouping using a function involving GroupBy:

queryWithAFunctionOperator=Query[Function[passenger,GroupBy[passenger,#sex&]],Mean,#age&];

queryWithAFunctionOperator[dinghy]

Running Normal on the Query confirms that the order of operations is not correct:

Normal[queryWithAFunctionOperator]

But if I write GroupBy[f] as an operator, I get the desired behavior:

queryWithAGroupByOperator=Query[GroupBy[#sex&],Mean,#age&]

queryWithAGroupByOperator[dinghy]

A digression. Do you remember how I said earlier in the chapter that the Normal of a Query is not exactly the same thing as the Query? The code in the previous input provides an example of this subtlety.

Here, I Normal the Query before executing it:

normalQueryWithAGroupByOperator=Normal[queryWithAGroupByOperator]

normalQueryWithAGroupByOperator[dinghy]

The result is probably not what one would want. Because the age of one of the female passengers is Missing, the Mean function won’t compute a purely numeric value. By contrast, the un-normalized version of Query produces the result one would want most of the time. It works because Query has built-in capabilities for dealing with Missing values. As discussed later, setting the MissingBehavior option to None lets one turn that behavior off.

Now the behavior of Query is the same as when I applied Normal:

Query[GroupBy[#sex&],Mean,#age&,MissingBehavior-> None][dinghy]

Other operator forms of GroupBy likewise are also treated as descending and promoted in the operator pipeline. Here’s the version of GroupBy that takes a rule between functions as its argument.

GroupBy retains descending status even when its argument is a rule between functions:

Normal[Query[GroupBy[(#sex&)->(#age&)],Mean/*N]]

Query[GroupBy[(#sex&)->(#age&)],Mean/*N][dinghy]

Complication 3: Subqueries

Suppose one wants to convert a descending operator into an ascending one. One wants, for example, a Select[f] operator to be applied in the usual LIFO way. One can change the order of operations produced by Query by wrapping what would otherwise be a descending operator in (another) Query. One could term these wrapped queries as “subqueries.”

The following list shows how wrapping a descending Select operator in Query turns it into an ascending operator that is applied last.

Use nested Query functions to try and change the order of operations:

Map[Normal,{Query[Select[f],g],Query[Query[Select[f]],g]}]

I suspect that if you are using a lot of subqueries you may not want to be using Query at all. They detract from code readability.

Complication 4: Syntactic Sugar for MapAt

Suppose one wants to apply a function only to one position with a Wolfram Language expression. The functional programming construct to do this is MapAt.

Apply the f function to the third element of dinghy:

MapAt[f,3][dinghy]

Or, if one wants to apply f to every age value within the List of Associations, one writes the following code.

Use All in combination with key names in the Part specification to MapAt in order to operate on columns:

MapAt[f,{All,"age"}][dinghy]

If one knew age was the second key-value pair, one could write the code using an integer part number. (Yes, I know this is against the whole philosophy of Association, but it can be done.)

Use All in combination with integers in the Part specification to MapAt in order to operate on columns:

MapAt[f,{All,2}][dinghy]

Although this notation is fine, it can be somewhat cumbersome when one wants to apply different functions to different parts of the data. Consider, for example, how one would need to write an operation that mapped the function f onto the age column, the function g onto the sex column, and the function h onto the survived column. One can imagine doing this if one had different rules for cleaning up each column of data, but doing so is a bit cumbersome.

The composition of MapAts permits different functions to be applied to different columns:

(MapAt[f,{All,"age"}]/*MapAt[g,{All,"sex"}]/*MapAt[h,{All,"survived"}])[dinghy]

Within Query, however, there is an alternative syntax for MapAt: Part Specification-Rule notation. One puts the part (or key) number as the left-hand side of a Rule and the function to be applied as the right-hand side, and one wraps it all up in a List.

There’s syntactic sugar for MapAt. One can “speak” the code below as “apply f to the third part”:

Normal[Query[{3->f}]]

Query[{3->f}][dinghy]

To apply the function only to the "age" key-value pair within every Association, I write the following code:

Query[All,{"age"->f}][dinghy]

Notice how when Normal is applied to this Query, one gets a MapAt operator with a two-argument Part specification, the first argument of which is All (meaning all of the rows).

For example:

Normal[Query[All,{"age"->f}]]

One can use this same syntactic sugar to perform RightComposition of multiple MapAt statements, each operating on different parts.

Here I apply the function f to the age values and the function g to the sex values:

Query[All,{"age"->f,"sex"->g}][dinghy]

A Normal of the Query shows that it literally constructs a RightComposition of MapAts:

Normal[Query[All,{"age"->f,"sex"->g}]]

Solve Selected Sample Problems with Query

Let’s now further explore Query by redoing a few of the examples from Chapter 3 of the book. The material in Example 1 is particularly important for understanding the remainder of this book.

Example 1 Redone Using Query

In Example 1 from the preceding chapter, we wanted to get the genders of persons over the age of 70 on the Titanic. Here’s a traditional approach to retrieving that information.

Get the genders of everyone over the age of 70:

Map[#sex&,Select[titanic,#age>70&]]

Here’s the pipeline version I construct without use of Query. First I create a Select operator using the function #age>70&. I take the output from that operation and map #sex& over it.

As in the previous chapter, I can name the pipeline oldGenders and apply it to the data stored in titanic:

oldGenders=Select[#age>70&]/* Map[#sex&]

oldGenders[titanic]

Here’s how I could do the same thing using Query. The Select[#age>70&] operator will be applied first. The #sex& function will be applied second but automatically mapped over the result due to its position in the Query arguments. It’s cleaner code, in my opinion.

Construct a Query in which a Select function is the first argument and an anonymous function is the second argument:

Query[Select[#age>70&],#sex&][titanic]

It is interesting, by the way, that there were so few passengers over the age of 70. Perhaps it was because of a combination of people not living as long at the start of the twentieth century and the elderly generally not making sea voyages in the Atlantic chill of mid-April.

Example 4 Redone Using Query

Let’s  find the median age of Titanic passengers (restricted to those under the age of 50) based on whether the passenger survived. Here’s what I did.

Use conventional programming techniques to compute the median age of Titanic passengers (restricted to those under the age of 50) based on whether the passenger survived:

Map[Median,Map[#age&,GroupBy[Select[titanic,#age<50&],#survived&],{2}]]

Or if I preferred a pipeline style of program, I could write out the following RightComposition of operators.

Create an operator pipeline by hand and then apply it to titanic to do the same computation:

medianSurvival=Select[#age<50&]/*GroupBy[#survived&]/*Map[Map[#age&]/*Median];medianSurvival[titanic]

Query again permits the code to be more lucid. I use three arguments. The first accomplishes filtering and grouping on the entire set of data. The third argument applies a function (#age&) to each member of each resulting group. And the second argument specifies what to do (Median) with each group after the function in the third argument is applied.

A Query operator is constructed from three arguments:

medianSurvivalQuery=Query[Select[#age<50&]/*GroupBy[#survived&],Median,#age&];
medianSurvivalQuery[titanic]

Here’s the Normal version of what’s going on:

Normal[medianSurvivalQuery]

Notice that we did not have to deal with passengers whose ages were missing. The default behavior of Query (set by the default option MissingBehavior -> Automatic) is to drop or ignore missing values presented to certain aggregation functions such as Total, Mean, Median, Min, Max, and others. I’ll discuss how Query deals with missing values in more detail later.

Example 5 Redone Using Query

Often in data analysis, one needs to join two sets of data together using some common column to perform the assembly. In the previous chapter, I showed how to use JoinAcross to accomplish this goal.

Use JoinAcross to join two sets of data:

titanicCabins=ResourceData[ro,"Titanic Cabins List of Associations"]

You need to Normal the Dataset in order to JoinAcross it ☹

joined=JoinAcross[Normal@titanic,titanicCabins,"class"]

While this joining could be done using Query as shown next, there’s really no point. Although Query is a wonderful functional programming construct, one doesn’t have to use it every time one wants to do data analysis.

Combine Query with an anonymous function based on JoinAcross to join information on the dinghy passengers with information on Titanic cabins:

Query[JoinAcross[Normal@titanic,#,"class"]&][titanicCabins]

Now Query will come in handy for doing data analysis on the resulting data. Recall that before I wanted the mean square footage of cabins on the Titanic, but I wanted it broken down by the sex of its occupying passenger.

Here’s essentially how I did it with a pipeline of operators:

(GroupBy[#sex&]/*Map[Map[#squarefeet&]/*Mean/*N])[joined]

And with the useful MapLevel ResourceFunction, I could have written it this way to avoid the ugly nested Map statements:

(GroupBy[#sex&]/*[#squarefeet&,{2}]/*Map[Mean/*N])[joined]

With Query, the code is simpler:

Query[GroupBy[#sex&],Mean/*N,#squarefeet&][joined]

How to Do Nothing: All or Identity

Sometimes, one does not wish to do anything at a particular level of an expression. One wants to leave it as is. Suppose, for example, you just want the age and sex of everyone in the dinghy. You can’t do this by putting a Null as an argument to Query (unlike some other languages where this is permissible: Query[,{"age","sex"}][dinghy]).

To fix this, use All or Identity as a kind of placeholder that tells the kernel to do nothing. The Identity function just returns its argument unchanged. Query appears to convert  All into Identity, so it should not make any difference whether one uses All or Identity in a Query.

Use All or Identity to tell the Query effectively to do nothing at a given level:

Query[All,{"age","sex"}][dinghy]

Query[Identity,{"age","sex"}][dinghy]

Queries with Deeply Nested Datasets

One useful feature of the Wolfram Language is its support for hierarchical data, deeply nested Lists, Associations, and Datasets. One is not limited to simple two-dimensional tables as might be the case in systems based, for example, on SQL. Here’s an example: the planets Dataset (put in Normal form since I haven’t shown you the Dataset wrapper yet). Basically, it’s an Association whose outer keys are the names of the planets. Each value is data on the mass, radius, and moons of that planet, but the moons data is itself an Association in which the keys are the names of the moons and the values are Associations in which the keys are the mass of the moon and its radius.

Get the planets data in Association form from the ResourceObject for this book:

(planets=ResourceData[ro,"Planets Deeply Nested Structure"])//[5]

Suppose I want the radius of Phobos, a moon of Mars. I could just use a Query that picked out keys at different levels.

Use Query with lots of arguments to access deeply nested data:

Query["Mars","Moons","Phobos","Radius"][planets]

Or suppose I want the mean mass of the moons of Mars, a tongue twister as well as a Query. (There is a great Live CEOing video of Stephen Wolfram struggling to come up with the right query ☺)

The mean mass of the moons of Mars:

Query["Mars","Moons",Mean,"Mass"][planets]

Or the total mass of the moons for each planet:

Query[All,"Moons",Total,"Mass"][planets]

Essentially, working with deeply nested Associations is no different than working with less nested ones.

Debugging Queries That Go Wrong

Unless you are an extraordinary programmer, your queries will create errors. An important skill in working with data is relaxing when these errors occur, being able to diagnose the root cause, and developing a fix. Of course, all the skills you’ve learned in other parts of Wolfram Language programming (and programming generally) apply here. Thus, techniques like reading the error message carefully, checking the spelling of your code (including capitalization), reading the documentation, looking up the error message in a search engine, and breaking the program up into pieces are all extremely useful. There are, however, some common and special errors associated with Query that are useful to identify and study. There are further errors that can occur when the data is part of a Dataset, but I will defer examination of those errors until the Dataset chapter.

Results That Make No Sense

Suppose I want to group dinghy data by passenger class, get the ages of the passengers, and then sort the list of ages within each passenger group. Here’s my first (flawed) attempt.

dinghy=ResourceData[ro,"Dinghy List of Associations"]

Errors can occur if one forgets that Sort is a descending operator:

Query[GroupBy[#class&]/*Sort,All,#age&][dinghy]

This Query works, but it gets the wrong answer. The ages are not sorted properly. Very frequently, in my experience, Query errors result from misunderstandings about order of operations.

I’ll do a Normal transformation of the Query and see what is going on:

Normal[Query[GroupBy[#class&]/*Sort,All,#age&]]

We can see that at the time the Sort operation occurs, the kernel has not isolated the age column, so Associations are being sorted as a whole rather than on just their age component. I now realize I need to change the order of operations. We’ve looked at several techniques in this chapter about how to do so. Here’s a simple fix: subordinate the precedence of Sort by wrapping Query around it.

Change the order of operations by moving the position of the operands to Query:

Normal[Query[GroupBy[#class&],Query[Sort],#age&]]

Query[GroupBy[#class&],Query[Sort],#age&][dinghy]

The Failure to Obtain Any Result at All

Sometimes a Query will not produce a result at all. Instead, one obtains a Failure object. Here’s one where I use IntegerDigits to obtain the second digit of the passengers’ ages. This would seem to work—at least so long as the passengers’ ages have two digits.

By default, if Query finds any errors, it returns a Failure object:

Query[All,IntegerDigits[#age][[2]]&][titanic]

This Query fails for two reasons. One is that some of the passengers are less than 10 years old; there is no second base-10 digit to their age. As it happens, I could eliminate this particular problem by writing the Query as shown.

Using Last lets the Query succeed when there is only one item in the list of digits:

Query[All,(IntegerDigits[#age]&)/*Last][titanic]//[5]

But suppose I did not know the data well enough to realize this problem? Or suppose the error was more subtle?

First, I might want to just ram through the Query and get it to work on as much of the data as possible. That is, maybe 99% of the rows work just fine and that’s all I care about. A Failure object can be annoying in these circumstances. To ignore the failed items, I add an option at the end of the Query, FailureAction->None. One of the things that makes Queries different from their Normal form is that Query has error handling built in. The default option means that the computation generates a Failure object when an error occurs. There’s no “partial credit.”

In this instance, shutting off the error checking is fairly helpful. I can see more clearly that the code is trying to take the second part of lists that have just one part.

Avoid Failure returns by setting FailureAction -> None as an option to Query:

(Query[All,IntegerDigits[#age][[2]]&,FailureAction->None][titanic])//[5]/*Normal

I might decide at that point that I don’t want the results that have problems; I just want the error-free components of the result. To do this, I set the FailureAction option to "Drop". (Make sure to put "Drop" in quotes; it’s a string.) Now I get a clean response that does not generate any error messages and that appears free of errors.

Setting FailureAction -> "Drop" avoids error messages and just keeps “the good stuff”:

Query[All,IntegerDigits[#age][[2]]&,FailureAction->"Drop"][titanic]//[5]/*Normal

This is a mixed virtue. On the one hand, I get a clean-looking result; on the other hand, I may have masked serious problems with my understanding of the data that come back to produce more problems with future queries. Worse, we might have undetected, substantive errors in the results. Still, sometimes, just dropping the bad items is a reasonable fix to a Query problem.

There are a couple of ways to clean up the Query. One is to use a Switch statement as shown here. If the age is missing, it outputs Missing[]. Otherwise it outputs the last digit of the age.

Use Switch to clean up the Query:

Query[All,Switch[#age,_Missing,Missing[],_,Last[IntegerDigits[#age]]]&][titanic]//Short/*Normal

A more informative method, but one that would be less amenable to subsequent processing, would be to generate some text. Here I do so by using StringTemplate and Style to generate an error message that may be more interpretable by humans.

Combine custom programs with FailureAction -> "Encapsulate" to generate custom responses when the operators inside Query encounter problems:

Query[All,If[#age≥10,IntegerDigits[#age][[2]],Style[StringTemplate["`1` is not a 2 digit number"][#age],Pink]]&,FailureAction->"Encapsulate"][titanic]//[5]

The book has much more information on debugging queries .

Do the new Chat capabilities make all of this moot?

Not yet!

I also asked ChatGPT with “Advanced Data Analysis”

meanAgeByClass=data[GroupBy["class"],All,"age"]//Query[All,Mean]

Not good .

What Else is in The Book?

Chapter 1: Introduction

This book is intended for intermediate users of the Wolfram Language. It’s not right for those completely unfamiliar with the Wolfram Language. You should understand, for example, that working with the Wolfram Language is kind of like working with the internet: it’s a client-server architecture. There’s a server somewhere—it could be on your machine, a machine in your local network, a cluster, or the Wolfram Cloud. The server runs a Wolfram kernel (or kernels) or Wolfram Engine that does all the nitty-gritty computations, such as computing the parameters of a logistic regression, training a neural network, creating geographic primitives, undertaking “map reduce” operations on a dataset, or, indeed, doing virtually any computation you can imagine, including those it might offload via external functions and other techniques to computers running Python, R, JavaScript, or many other languages.

Chapter 2: Data Structures and Functions

I start with some intermediate-level Wolfram Language programming. Here I rapidly survey some true fundamentals of the Wolfram Language: everything is an expression, expressions have parts and levels, and functions exist apart from the data on which they operate. Because the Wolfram Language flourished well before the introduction of associations and datasets that should today prevail, and because there’s often still good reason to use earlier data structures either on their own or in combination with Association-based structures, I also address data structures intermediate-level Wolfram Language users are likely to have encountered before, most notably lists and arrays, or lists of lists.

This chapter pays particular attention to functional programming and to the use and composition of operator forms, functions that tend to take one argument and serve as little units of computation. I do so because functional programming often lies at the heart of efficient, idiomatic Wolfram Language programming and because composition of operators (a kind of function) is critical to effective use of the Query function, which lies at the heart of this book. I also stress functions that restructure lists or arrays, such as GatherBy and Transpose.

The chapter ends, as do several subsequent chapters, with a walkthrough of various “real-world” problems. In this chapter, the problems are taken from data involving salaries in Major League Soccer (MLS), the primary association of men’s club soccer (football) in the United States. The idea is to show how these computations can be performed (often well) using early Wolfram Language constructs and then in later chapters to show how use of progressively more powerful tools, like Association and Query, enable these computations to be done either more swiftly or in a way whose intent is more transparent.

Chapter 3 : Association

The next chapter introduces Association, which joined the Wolfram Language in Version 10 in 2014. No longer is the user forced to remember the column number or row number in which some data is kept and use memorized integers to reference components of the data. Instead, the user can now give rows and columns memorable names. Moreover, along with Association comes various powerful functions useful in creating them, such as GroupBy and Counts, or in manipulating them, such as Merge and JoinAcross. This chapter again uses functional programming and compositions to extend the techniques used in the preceding chapter to cover associations and certain data structures containing them, such as lists. I use data from MLS in order to illustrate the concepts as well as data from the doomed ship Titanic.

Also, there’s one feature of associations I don’t try to squeeze into this chapter: the use of Association as something similar to a function. That feature is so important and powerful, I defer its consideration to the next chapter, which focuses on just one Wolfram Language function, Query.

Chapter 4: Query

Query is central to modern approaches to data analysis in the Wolfram Language. Although it initially seems mysterious to many, once mastered, it’s an incredibly powerful functional programming tool. I will admit to a bit of schadenfreude watching Stephen Wolfram himself attempt in a live programming exercise to extract from a hierarchical data structure the mean mass of the moons of Mars. Moreover, it’s a tool that can be used not only on newer data structures, such as Association (and the cousin found in the next chapter, Dataset), but can and should be used on traditional data structures such as lists of lists. The chapter shows how Query creates a pipeline of operators (themselves often composed of operators) intended to work on different levels of a Wolfram Language expression. Drawing on the discussion of operator pipelines in the preceding chapters, it spends considerable time providing a conceptual and practical understanding of the sometimes challenging issue of the order of operations in Query-generated pipelines. I show how use of Normal when applied to a Query can both show the order of operations and provide numerous examples that facilitate understanding of basic Query principles.

I know all of this can be challenging. That’s why I make some effort using the wonderful TreeForm function to provide visualizations that I believe help explain it. I also show—and this was the matter deliberately not addressed in the preceding chapter—how one can make it appear that an Association is a function through the implicit construction of a Query.

The latter part of the Query chapter tries to make it all concrete. I essentially redo the analysis of MLS and the Titanic, this time using the Query construct. I hope you’ll be excited to see that with the mastery of Query, data analysis that proved challenging in the preceding chapter is now somewhat easier. I also hope you are gratified, as I am, to see that the code is more concise. Programming errors are roughly proportionate to program size.

Chapter 5: Dataset

One of the ideas of this book is that an awful lot can be done without Dataset. You can just use conventional Wolfram Language data structures, such as nested mixtures of lists and associations. And I also like to show that with the Query function and operator pipelines, one can transform data in this form in powerful ways. In this chapter, however, I want to show the advantages gained by wrapping these data structures with Dataset.

Dataset is a command whose utility is greatest when using the Wolfram Language front end or the Wolfram Cloud as the client. In these settings, wrapping Dataset around Wolfram Language expressions powerfully extends the ability to visualize and manipulate data. The presentation is dynamic: various mouse clicks let the user burrow deep into data, with the option to paste results into cells below. It’s sort of a hierarchical spreadsheet of a type not available in many other programming environments. I’ve found it immensely valuable and suspect you will too.

Even when the client doesn’t have these dynamic capabilities, however, Dataset is important because it can provide a picture of the data, a static tabular form. And, as I will show, in more recent versions of the Wolfram Language, datasets can be formatted in custom ways to make them particularly attractive. I spend considerable time in the chapter explaining how that formatting can be done precisely, also briefly examining a very complex topic: the internal structure of Dataset objects. This information is useful to ensure that the presentation of derived data, such as that produced through grouping and filtering operations, is exactly the way you want it. Even if you don’t want visualization, Dataset can be important because it provides a structure that can optimize various queries.

Chapter 6: Basic Recipes

Some people are understandably impatient. They don’t want to know—at least quite yet—the intricacies of how and why. They don’t want to know the theory of the Maillard reaction; they just want to know how to cook a nicely browned hamburger. Right now. So, if you want to know, for example, how to add columns to a Dataset based on pre-existing data, add row headers to a Dataset, convert row headers into columns, or learn the “Double Transpose Trick,” the many recipes in this chapter are for you. Even if you prefer learning things in a more theoretical way, the examples provided here should provide insight into the ideas behind Association, Query, and the other aspects of the Wolfram Language emphasized in this part of the book.

Chapter 7: Advanced Recipes

Getting data in the right form isn’t always easy, and while it may be easier in the Wolfram Language than in competing programming languages for some tasks, the distance between the form of the original data and the desired form means that there will inevitably be complications. Moreover, having poked over the years through more than my share of data scattered over the internet, I can assure you that a lot of data is a structural mess or just contains errors. In this chapter, I survey the types of complex problems I have come across most frequently when using the sort of programming techniques fostered in this book and in confronting the sorts of problems shown in this book.

I try to stick with the Titanic example insofar as possible to leverage understanding of that dataset and its transformation, but in this chapter, I also consider data found in the Wolfram Data Repository on Indian reservations or more hierarchical data, such as that on planetary moons. Problems addressed include creating attractive datasets with data grouped in multiple ways, placing data in forms optimal for Wolfram Language machine learning functionality, placing data in forms optimal for Wolfram Language statistical analysis, and placing data in a form for charting or geographic mapping.

Chapter 8: Import and Export

Thus far the book has used data that’s already within the Wolfram ecosystem: data generated from within the Wolfram Language, example data built into the Wolfram Language, or data from the Wolfram Data Repository, but you are likely to be working with “wild” data from somewhere on the internet or perhaps generated by you using some other computer language, such as Python, R, C, or Google Sheets, or even data generated by a device. Moreover, you may need to share your data with persons who need or prefer to transform it further using a system other than the Wolfram Language, such as Excel, Tableau, or Julia. This chapter teaches you how to “domesticate” wild data and how to export data in typical forms, such as CSV, XLSX, JSON, and ZIP.

This chapter on import and export ends the hard copy version of the book, the material that every user needs to understand in order to transform data into information using the Wolfram Language.

Index

A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X–Z

Chapter 1