Query: A Functional Programming Construct

Hi, I'm Seth Chandler, I'm a law professor at the University of Houston Law center. And today I want to talk to you about the query command in Wolfram language, which I think you can think of as basically another functional programming construct. Who's this talk intended for? It's intended for intermediate users of Wolfram language. If you're a rank beginner, if this is the first time you've used it, this might not be the talk for you. If you're an expert, you're going to say, yeah, but I know that and I hope though that you learn something. In any event, if you want to learn more, there's a whole book that I've written that is now out makes a great holiday present called Query Getting Information from Data with the Wolfram Language. And I'm going to be talking about some of the materials in chapter three and four of that 350 page book. All right, so let's start out with the world before there was a query construct and there is a resource object in the Wolfram data repository for the book called sample data for querybook. And we can just download that. I guess you can see that I ran this before, but you're going to trust me that it all works. We're going to download the Titanic in the form of a list of associations data set. And then you'll see here I've got a little resource function that's going to change the number of rows in the data. This is only here to make the presentation a little more bit simpler for us, easy for you. Otherwise its default is 20 rows and that takes up an awful lot of screen real estate. So let's get in the Titanic. There we go. And also I'm going to be using an imaginary dinghy that was cast off the Titanic when we just need a small little data set. All right, so here, supposing you wanted to figure out what was the mean age of the passengers on the Titanic grouped by the cabin class in which they stayed. And so you might think, well, I'll just use the group by command and I will group by class and then I'll pick out the age and then I'll use a reduction operation mean on each of the classes. And if we run that, what you're going to see is that you get a very unattractive answer. And why is that? It's because some of the passengers, we don't know their ages. And therefore when you're trying to take the mean, you're taking a mixture of numeric values and missing values, and you get something that isn't particularly useful. So you could, of course, eliminate the problem by first requiring that the new that age have a numeric value and then perform the operation and then take the mean. And that would definitely work. And the question you might ask though, is, is this the most readable code? And, you know, if you're a Wolfram language veteran, you've adapted your brain so that it reads inside out. But if you're not such a veteran, it might be more convenient to see it in another form. So let's look at what I present in chapter four of the book, which is how to use the query operator to do this in, I think, a more aesthetically pleasing and often a more efficient way. So let's define a query, and we just use the query head. And then we're gonna have three arguments. We're gonna have a grouper. We're going to have something that's gonna take the mean of each of the groupings, and what are we gonna take it of the age? And. And so you'll see I'm not applying this to any data. There's no data attached to this query. It's just sort of a lazy functional construct that's sitting out there just like, you know, you can have a function f or a function group by class ampersand, and it just kind of sits there waiting for you to use it. But then what we can do is we can now apply that query to data here. I'm going to apply it to the Titanic. And you can see that we get the mean age of the passengers by passenger class. And you can see that the wealthier people who were in first class tended to be of an older demographic than the. What was her name? Kate Winslet and Leonardo DiCaprio's. Or I guess Kate was up in first class and Leonardo was down in third. But in any event, that's what we see. One nice thing is it's just a function. It's just a wolf. It's just Wolfram language code. And so you can write functions that construct queries. So supposing I didn't want to necessarily get the mean of the age of the people group by class. Maybe I want the median or the maximum or the min max or who knows what. And so we can just write a little function that does that. I can now create a query basically by running this command and then apply that constructed queen query to the Titanic. But if I want the median, I just replace the mean operator here with the median and I get the Median of the Titanic. You'll notice, by the way, that how come there aren't I. I didn't say select. Only those who are have a numeric age. How is it that query was smart enough to discard that information? And the answer is that query. Query is sort of customized. One of the virtues of using query is that it has an intelligent handling of missing values so that you don't have to deal with them if you don't want to. Let me also show you another syntax for using query. There's no name that I know of for this. I call it an implicit query. Cause the word query doesn't exist. But you kind of take the object titanic and then apply the arguments of query to that object. That is, this is the same three arguments that I used in the query above here. And if you do that, it works just fine. It's a matter of taste, I think, whether you want to write it with an implicit form where the word query doesn't appear and you just present the query arguments after the data that you want to examine. My own preference is for whatever reason, I like writing out query. To me it makes it clearer what's going on. But reasonable minds could differ. Okay, so query takes options. And here are the three options that it takes and including the default behavior. You'll notice that one of them is missing behavior. What do we in fact do about missing data? The default value is to try and be smart about it, but you can also turn that off. Maybe you are mistrustful or maybe you want to know about missing data. And so we simply add another optional argument to the end of the query set of arguments, just like you do for any other Wolfram language function. And so if I now have Q2, which has missing behavior none, and I run that on the Titanic, we get this information that there's actually a lot of missing data, particularly on those third class people who maybe didn't take the time to write down their age. And you can do the same thing using implicit query form. It's exactly the same thing. Okay, there are the other options to query, including part behavior and failure action. If we have time, I may talk about failure action, but you can read all about those options and how they work in the outstanding book. That's a great holiday present. Okay, so what's hard about query? And what's hard about query is order of operations. How does it construct? Basically what query does is it constructs write composed operators which then work one by one on the data. But which operator comes first? Okay, so What I would tell you is, first of all, the each argument tends to work. You'll notice I keep hedging it with generally at a different level of the expression. So the first argument to query generally operates on the entire expression that you're giving it. The second argument operates at the first level, the third argument operates at the second level, et cetera. Okay? That is only, however, a generalization. And the exact order of the pipeline of operators that query produces depends quite precisely on the nature of those operators. There is, however, and a logic to it, and there's also a very simple method for experimentation. And that's what I want to emphasize. So you don't have to read all the documentation, which is very long and dare I say, a little complicated. And instead you can just listen to me. All right, so supposing we have an expression. Here's an abstract Wolfram language expression. And what we want to do is we want to map an abstract function V over that expression, and, and then take the apply U to the result of that mapping. So if you're in this session, you should know how to do this. And so here we are, we're applying V to 8 and V to H of X, Y9, and then we're taking this sequence of parts and applying U to it. All right, so of course you can do that. You can still do that. Nothing. Query doesn't stop you from doing anything you couldn't do in Wolfram language before. But here's how you would do it with query, you'd simply say query U operate the at the highest level, V operated at the first level, and apply that to expression. And it does the same thing. Okay, well, I've told you, that's what it does. But how, how could you figure that out for yourself? And the key thing, if you take away one thing from this talk, it is that normal is your friend. If you want to find out the order of operations of a query, just stick normal in front of it. And what this is going to tell you is first I'm going to map V over whatever the data is, and then which is how you translate slash star. Yes, Wolfram would like you to say right composition, but I feel that saying and then works just well. And then you it. Okay, and if you want to see the full form of that, you can just write full form and it'll tell you, hey, it's the right composition of a mapping of V and you. Okay, so if you normal any query, it will represent that as a right composition of operators and indeed one of the things I've learned as a result of writing this book and working with query is that this is actually even when you don't use query, it's a very good way of programming because it takes. You're using functional programming, you're getting all the benefits of functional programming, but you're not having to do things in, in an inside out way. Rather it's just first do this and then do that and then do something else. So here's another example of using normal to figure out what query does. And here the query is basically group the whole thing by F, apply G to each part, but first take H at sort of the lowest level. So to represent that, what this is going to tell you is first group by a F, and then for each resulting part, map H over it. Notice the H has come first. Even though it's third in the list of arguments, it's actually getting ahead. And then apply G to each of the parts created by F. Okay, now could you memorize as I have and sort of intuit yes, of course, this is the order of operations, then it's gonna produce. And the answer is yes. If you work on this for a while or you're really smart, you can figure this out, or you read the documentation. But the other way, frankly is to play with it a little bit, make mistakes and use normal to figure out what is going on. I will say that I'm not a big fan of the map of the map. That's what a query does when you normal it. There is a fantastic resource function authored by one of Wolfram's best community members, which it says basically map level, which says that apply the function H at level 2. And so here you can see an alternative way of representing what query is doing. Group it by F, map H at level 2 and then map G. Okay, so again, if you were in my class, I'd ask you to repeat by me and repeat after me and your friend is normal. Can you also generate a pivot layout data set? I think I have some idea what that means. And so let's be bold here and let's do this. Is it going to let me write in this or is it locked me out? All right, so let's just say query group by cabin class. And then let's take each of those groupings. Oh, and let's spell group by correctly. Then let's group by sex. And then let's take the length of each of the components. And again, to show you I put my money where my mouth is or whatever the Correct cliche is we can normal that. And let's see what it's going to do. What it's going to do is it's first going to group it by class and then for each such grouping, it's gonna group it by sex, and then for each sex it's going to determine the length. So this is quite promising. And let's see. Okay, so that's not bad. If you read later in the book, you're gonna see this is kind of annoying. This is not how I would want the output to be represented. And the reason that it's not in a nice table form is that for first class people, the first person that it encountered was a female and then the next person was a male, whereas in the second class grouping, the first person that encountered was a male and the second person that encountered was a female. So what I'm going to do is I'm going to compose this with a key sort operation so that it's consistent and now we get a much more attractive layout. Okay, so that's a digression, but I hope that was the answer to the question. All right, so supposing you're sick of using normal, you think it's lazy. Who could, who could know that you want to know the rules? All right, so the general rule is lifo, last in, first out. The order of operations is generally in the opposite order of the arguments to query. Okay, so lifo, for reasons I go through in the book, actually makes a lot of sense. You tend to want to operate on things lower down in the expression before you operate on things that are higher level. Because otherwise if you operate at things that are at a higher level, you're going to mess up what's at a lower level. And Joel, thank you for your comment. I agree. You start with a data source and then successfully successively transform it. And I just add, you know, you can actually program it this way. Start with a data source and query group by X and get the length. So here, I mean, this is exactly what Joel is talking about in his comment. You can write the code where you just start with the data and then you have a pipeline of operators. And then I. Anyway, we could add additional things to the pipeline, but I don't want to do that right now. Okay, so I'm going to skip query tree. If you want to learn more about how to visualize a query, there's this function that you can see in this notebook which is available through the handouts and it'll tell you one way of visualizing A query. All right, but what about the rules? LIFO is only the general rule, and since I'm a law professor, we have to deal with exceptions. And so there are lots of exceptions to the LIFO rule. The documentation is going to talk a lot about descending and ascending operators. The general principle that sort of explains what it's doing is do things that are efficient and won't wreck things. So for example, supposing, let's see if I have. Where's my example? Right. Supposing you wanted to get the mean age of women on the Titanic. You could first get all the pieces of age data and then keep that in the data set and then just select those who were women and then computer mean. But that requires you to get all the age data, even for people that you're ultimately going to ignore. And so in, in the query system, as in other data systems I think, such as SQL, that you want to basically look at as little data as possible. And so it often makes sense to do the things like select first. Okay, and so let's look at this here. Supposing I have. Where did I. I have my matrix written down. Oh, okay, we're fine. So here's a query and what it's going to do. Let's actually, since I will just ask for normal what to tell it what it's going to do. It's going to select the data for which the total value is even and then add one to all of those in the second position. Okay, so for example, let's look at this here. Well, that's not even. So it should basically ignore that should be kicked out the sum here. Five plus seven is even. So we're going to keep that and then we're going to add one to the value in the second position. And that explains why you get Q8 and so you'll notice, I guess the point is that the. Remember I told you the general rule is lifo, but it's not always the rule. You want to do the select first. We want to get rid of the data for which the total is not even. What else is gets to jump the queue, gets to come first in the operator pipeline. And the answer is various things that resolve to a slice operator. So if I say what is the Query of parts 1 through 3? Or what is the query of? Take the first five or what is the query of the key? I'm a key of some piece of data. What you'll see is those all resolve down to a general utilities slice operator and things that resolve down to A general utility slice operator come first in the get inserted early into the pipeline of operators so that what else resolves down to a slice? If you give it a string, you want the eighth part of, for example, some association that will resolve down to a string. And you'll notice that because this resolves down to a slice operator, it is going to come in. It is coming ahead of F. It's not going to use lifo, it's going to use this notion that slices come first. Okay, what else tends to come first? Most of the variants of select, select first, key select and then some of its friends, maximal I minimal by all come first. Other things that tend to come first are a lot of deletion operations because again, you want to get rid of data before you do further operations. Cases similar work similarly to select and some but not all sorting operators. And herein lies a source of confusion, which is that query is not quite smart enough to realize that certain things are intended to function as a sort of or intended to function as a select. And it tends to be fairly literal. So for example, here's a matrix and if I say sort that just using the function sort, what you'll see is, and you might think sort it according to the total. So the question is, is it going to do a row wise sort or is it going to do a column Y sort? And you can see when I do it this way, it's going to do a row Y sort. This total here is eight. That happens to be the lowest total. Is that right? Okay, right. This is why it's important. Um, so you'll notice that you get two different answers. That's one thing we can notice that's clear, that using sort gives us a different answer than using sort something. And you would might think, the untrained eye might think these are the same commands, but in fact, if we look at the normal form of this, what we'll see is the sort comes first. It gets the, it comes first before the totaling is done, which is why we get this screwy answer. Whereas if we do this and take the normal of it, you will see that the sort comes last. Okay, so if you read the book which has a beautiful cover, you will see that there is a much more thorough discussion of sorting. So let's go to one more thing, which are the complications. I think this complication is sufficiently complicated that I'm not going to deal with it in this talk in which I just have a few moments remaining. The issue is how do you deal with the situation in which an argument to query is itself a composition of functions, and there are rules about that that you can learn. All right? The second complication is what to do with group by. Group by is something that you very frequently want to use when you're dealing with data. And if you think about it, you wouldn't want to do the. Generally, you would not want to do the grouping last, you would want to do the grouping first. And so Groupby basically gets stuck in first or early in the queue. So here's our dinghy, and if I say group by sex and then take the length, it will in fact first group by sex and then it will determine the length of each of the resulting groups. So that again, if I were to normal this, you would see that that's the order of operations. And you can see by the way that this again, you have to remember that query is quite literal in its understanding. So here's a function in which this is just a grouping, right? This is saying group by group, the passenger by sex. But if I run. If I run the query this way, where I'm not just using group by, but I'm embedding group by inside another function. Wolfram isn't smart enough yet to understand that this is really just a group by, and it's going to give me an error message. Because what it's doing is it's first taking the age, stripping out things like the sex, and then when it comes time to group by sex, it's saying, I don't see any sex. So it fails. Whereas. Whereas if I just write group by, it's smart enough to say, oh, that's a group by operation. It should come first and if I then run it, it's going to be fine. Okay, so when this is a very common error and you scratch your head and you're not sure what's going on, normal is your friend. And try to figure out why the order of operations is wrong. Complication number three are subquery. Supposing you actually want to check change the order of operations, you don't want the selection to be done first. The way you deal this, which is a little bit awkward, is you nest the operator in query itself and that will basically subordinate its priority so that if you look at query select F of G first it does the select and then it maps G over the result. Whereas if you wrap the select in a query query, you'll see that it first maps G and then it selects. The fourth complication is map at. And let me skip down to, I think what you need to see which is, you know, here's what you could do. You could say, all right, first I'll apply age. I'll apply F to all the age data. Then I'll apply G to all the sex data, then I'll apply H to all the survival data. And yes, you can write that code, but it's kind of ugly, I think. And so query has a convenience function. And let me just skip down to where that is. Here we go. This is saying, look, take all the rows and for age, apply F to it, and for sex, apply G to it. And if I then look at the normal form of that, what you're going to see is it does, it does the work of composing the sequence, the right composition of the map ads. All right, so there is lots more that one could talk about. You can see how to solve sample problems with query, what to do when you don't want to do anything at a particular row or column, how to work with much more hierarchical data sets. And in fact, if you want to have a little bit of sangfwa, you can learn how to find the mean mass of the moons of Mars. And there's a wonderful live CEOing video where Stephen Wolfram struggles to try and figure out how to do this. And so I wrote this little piece of code for him. This is how you figure out the mean mass of the moons of Mars. So let me stop there and see if there are questions that I haven't answered. And Laurent, if I didn't answer your question because I didn't understand it, maybe you could rephrase. But hopefully I. I got it. Any other questions? All right, debugging. I guess I'll say something about debugging, which is that here you want to learn about the failure action option to. To query, and you will. Right now, if you run query and there's basically any bug in your code, you're going to get basically nothing. And nothing is sometimes not particularly instructive. And so there are options to failure action which will. It'll run your code and it'll try and tell you, hey, there's. This is what the problem is. And I find that setting failure action to none is, is very instructive. There are more elaborate things you can do, like basically say, if it doesn't work, just skip it. Failure action goes to drop. So failure action is your key to debugging queries. And some of you might be thinking, this seems awfully complicated. Isn't the whole point of, you know, like this chat notebook thing that you don't have to do all this and I don't have to learn anything anymore. And the answer is not quite yet. Yet it may be that in two or three years you and I are completely obsoleted. But at the moment the chat notebook was not able to do what I wanted, which was to group it by what did I ask it to do, right? How would I obtain the mean age of the passengers based on class? And at the moment it wasn't able to do that. I would be delighted in some sense if a few years down the road, after my book has sold a lot of copies, that Wolfram language was able to handle this directly through a chat interface. Is there an intentional relationship between query and SQL? I don't know exactly that because, I mean, I know who sort of wrote it. A test, as I understand it, Tali Banon wrote it and I, I'm sure he was well versed in SQL, but I can't, I, I don't know enough about the process of, of writing this code to answer that. All right, folks, I think I'm going to wish you well. And as I said, I'm, I'm not just hawking the book, really. The book covers this a lot more material. There's information on how to import data from the wild, how to get it into Wolfram language, how to export data so that you can then use it in other languages or for other purposes. It goes through a lot on formatting of data sets because you can make those data sets very attractive or you can make them ghastly. And so the point of the book is not to tell you this is how you do machine learning, this is how you do statistics. It's how do you get your data in a form so that you can then intelligently use all the power of Wolfram language on it. It's sort of more of a data wrangling book than a particular methodology book. But frankly, in my experience, like 90% of the problem is usually data wrangling. And so I hope the book provides an answer to that. And as for the person who bought it and is waiting for it, I hope it gets there soon. Thanks.