Live CEOing Ep 841: Language Design in Wolfram Language [Tabular]

Okay. Hello, everyone. We are continuing our discussion of tabular and its design for the upcoming version of orphan language. All right, so changes since the last meeting. Aggregate rows, key columns marked in gray. Okay. They're only type icons for strings and expressions. Okay, so where do we start?

Right, so the other day we were looking at these three functions and we were suggesting.

Yeah, maybe, maybe we should start with comparing pivot wider and pivottaller, since we don't those ones.

Okay. Aggregate rows is the new reduced tabular, right?

Yeah.

Yes.

Okay. Key columns are marked in gray. Do I see that here?

Not in this example. But if you go down a little.

Bit, you see that people take clearly not the right graphic design. Right. We know that, right?

Yes.

Okay.

It's what was whipped together in, in, you know, 72 hours. In less than 72 hours.

Okay, that's fine.

I, I was re. Listening to the end of the meeting that I missed about this thing, but I was thinking like, if instead of having these icons, we'd had something that distinguished strings also in terms of like, of the data itself. Like if.

Well, we do that right In, I mean, in the standard, in the product, right? If I type in a string that's slightly, you know, that's, that's, you know, that's slightly gray. I don't know if that, that may be sufficient. And on the other hand, I think it's also ugly to have. I would prefer to have a hint up here than to make this all gray. I don't think we have anybody from graphic design here, so I'm not sure that we should have that discussion.

And I was thinking that also things like dates could be distinct. Like in date object, it's blue, it's not black.

So we, we kind of, let's do some experiments. But that's a graphic design issue. Let's get graphic design to do those experiments. Okay. I, I'm a little bit concerned about having something where I, I don't know whether this hint is going to be enough. I don't know. And I don't want it to be the case that, you know, we want tabula to be an output format, not something that has, you know, is themed for typing particularly. Do you see what I'm saying? It doesn't need, it doesn't. We don't want to spray sort of. Oh, let's tell you about the data type all over the thing. Get what I'm saying?

Yeah.

Okay, so we got two choices. Let's get mock ups of them. Okay. The two choices are we make Some small change to the appearance of the whole thing. The other possibility is we get, you know, an indication up there and let's see who is, who's, who's Carla's project managing here. So Carla, can you make sure that those things happen and then we get those markups in the next meeting. We have appropriate people from Design here to talk about them. Let's go on, please.

But Stephen, sorry, you were talking simultaneously about key columns and strings. So which one are you talking about?

I'm just talking about the strings issue, the data strings. Okay. Okay. Let's see the key columns example. Where is that?

Well, no, right, exactly. What I'm saying is that in the previous meeting, we had to manually change the, some of those colors to gray. So now you see that pivot table is already returning something marked with a key column class. In your first example there.

Where this one here. Oh, I see.

Yes. Yeah, exactly. So pivot table returns the.

Oh, I see. That's perfectly nice.

Yep. I think that that helps understanding and it also shows the role of.

Wait A second, the 1, 2, 3, 4s are gone here. Exactly. Is that what we intend? Do you understand my question?

Yes.

Yeah. The 1 234, I presume, is because that's nameless, right?

I think they should be gone. Yeah.

Because they should be gone in this case, in the key column case, but not in this case.

Right, yeah.

Okay, so if there is no key column, you get the 1, 2, 3 fours. If there is a key column which are in gray and on a gray background. If you, if you do have a key column, the key columns are indicated in gray and there is no 1, 2, 3, 4. Agreed.

I think there should be an option to toggle that just graphical option because sometimes can we.

Who, who's, who's setting up those graphical options? Who's setting those up? Who has an inventory of those? Can you make sure we have an inventory of those, please?

Yeah, I'm thinking that, I don't know.

Why I don't know what is meant by a graphical option.

Well, I mean there's some appearance option. Yes or no, is there an appearance option for tabular?

Yes, there are a number of them. Like for columns.

No, no, no, no. We don't need to know the details.

Specific thing and I don't know. Well, we can do options of tabular and look, I, I, I presume there.

Is, there is an appearance option.

I don't. Do I want to run these or not?

No.

There you go.

It's the first option.

Okay, so what does it have, as the right hand side, is it an association or what is it?

It's probably a string or less. It can probably be a number of things. Strings, list of things.

But data set, what did we do with. Okay, so with Data Set, we have more elaborate options. Yes, yes.

In Data Set, the options are separate. I think the idea is to have them here together, at least for a moment.

Together in appearance or what?

In the appearance option? Yes. So I think Brett's team is working on this.

Yes.

And Data Set, there's no representative from.

That team here, is that correct?

Yes, that's correct.

Okay, so I'd like to say that I, you know, if we put these into Data Set, I don't understand why we would not be trying to move them into tabular as well.

We have an elaborate design that we want to present soon, which may or may not make it to iteration one of this tabular. Okay, but it's looking very promising.

Okay, but is it going to be equivalent to the Data set one? What are we going to do with Data set one? What's the idea?

It's a little bit more sophisticated, sophisticated than the Data set one. Because the data set one, none of it looks, you know, if you're going to look at data, you kind of have to.

Okay, Roger, this thing, what we have in Data set is not useful. Whatever we do, we've got to make sure that I cannot.

I'm going to say what you say. Let's define where we're going, and then we figure out the break.

That's fine. That's fine. I agree with you. Okay, that's fine.

And obviously Data set theme doesn't make sense in tabular.

Yeah, right.

I think we have something that you're gonna appreciate.

Okay.

Okay, fine.

We can show what it can achieve.

Okay. Is, are we. So what are we doing? So, so that means we're tabling whatever Carlo is talking about for the appearance option for the time when we have these more serious options for display.

I think so.

Okay, fine. So Carlo makes sure that we have that information from Carlo so that we know when we are discussing these. These other display options that we have that available. Okay. Okay. All right, what's next? What are we looking at next?

I think we. We should really delve into pivot wider and pivot longer.

That. Because. Okay, all right. Okay, fine. Okay, so let me see if I still remember what the heck was going on there from a few days ago. So.

So we spent most of the time looking at the previous function, a pivot table and you have your notes in those from the previous meeting sections.

Okay.

And then we started.

So this is pivot. This is the pivot table.

Exactly.

Yeah.

Right.

So I don't think we need to go into those. I think we want to just start really diving into pivot wider.

Okay, but so did we solve pivot table? Did we feel like we solved it?

Model of the name. What we decided is to separate the two. There is a list of lists there, and we spent some time discussing whether it was possible to separate them into two arguments and moving. So the general notation, and Rob, correct me if I'm wrong, is those two lists we see there, arrow, a column renamer function, which would be moved into an option.

I'm sorry, what? Which thing? Well, what are we looking at?

Pivot table. Yeah, right now the syntax is list row keys, goes to func as one argument.

I see. No, so just.

It's not.

It's list of rokies comma call keys.

Oh, sorry. Sorry, I. That's what I meant. That's not what I actually said, but yes, that is what I meant.

Okay, so can you just remind me. It's a bad sign that I've already forgotten how this works. Remind me. This is our proposed final syntax here. Is that correct?

Yes.

Yeah. Okay, so just. Let's walk through. Just say, as they say, in your own words, so to speak. What does this do? What's the one sentence for what it does?

Okay, what it does is it doesn't aggregate rows of tab func, join of rokies call keys.

Okay. Aggregate rows of tab comma, funk. Okay, and then on which rows? Because aggregate rows is the third argument. Right, right.

And then it's join of row keys.

Comma, call keys, by the way, Bug there. Somebody make a note of that, please. Function. Whatever it is.

It's join in a flat list.

No, that can't be right.

It is right. It's join of row keys, comma call keys.

How can that be right? I mean, aggregate rows is. It aggregates those rows.

We. We have categorical columns. Right. The rows and the columns. Right. But when we aggregate rows, there's all flat, all the categories that we want to aggregate on this in one list. So that's why Rob says join them.

Yeah, I'm sorry, row keys means the label of the row.

Yes.

Which could be.

But those.

Those are the. Those are the. Maybe keys is not the right word to use. It's. It's the. It's the columns you want to see listed as rows versus the columns. You want to see listed as columns, right?

Yeah.

So the name keys is maybe misleading.

Okay, so that isn't right. So just tell me what it is. So.

Call it row calls, comma, all.

Calls row columns and column columns.

What the hell does that mean?

And what does that mean, Rob? How. How can it. How can it. How does it know which is. How does aggregate know which is which? If you're joining them in the flat.

The aggregate doesn't need to know.

I see.

Okay.

What are those things? Are those the names of columns? They're all the names of columns.

They're all the names of columns. Right, exactly. Just what's the difference between a row.

Column col. Col in this case?

Because. Well, it's going to lay out the table with some of them being rows.

And some of them those two examples above. Okay, look at the pivot table examples and imagine they're separate arguments. Okay, so class shows up as listed by rows. Right? And sex shows up listed as columns. Whereas if you reverse them, it's exactly the opposite.

Okay, so let me see if I can write a piece of text here. Let's say pivots. Pivots tab. So that. Yeah, we're going to change this to. So that the columns in the list row calls. Oh, gosh. Remind me what this is again. Okay. Become. These become. Wait a minute. Okay, just a minute. Okay, just a minute. And the columns in the list column cols. Become columns in the new tabular. It isn't the columns, it's the. And the. And the entries and the categorical entries.

Yes, right. It's the entries in the list call. Yeah, right, precisely.

Categories, categorical. Entries in the columns in the list become columns in the new tabular. Okay. So that the columns on the list row calls. So what is it? The columns and row calls.

It's again the entries become.

Well, they become keys.

Tell me what it is again.

Entries into columns in the list row calls become keys for rows. Or I guess if you wanted to make it more. More parallel, you could just say become rows in.

In the new tabular, become rows in the new table. Okay.

Yeah, of course, we don't say funk anywhere in this description.

What you're missing in that sense is it. It does an aggregation with the function funk.

Okay. All right. Okay. Pivots up by aggregating using the function funk. Yeah. And makes. Why does it need to do that? What does funk return? Okay. Funk takes a list of things and returns a single thing. Is that true?

Yeah, typically total or mean.

Okay, so. So let's just Remind ourselves. So func takes a list of entries of values. Use.

Yes.

And returns a single value. Right, right, right, right.

So you, you might phrase it as pivots by aggregating values using fun. Using the function funk.

And puts and. Okay. Makes. But. Okay. And make a new tabular in which entries and the columns become rows. I mean, I'm not writing quite the right words here.

Yeah.

Okay. Entries in the columns in the listrokehorse. So this is categorical entries again.

Yeah, I think it's which unique entries or categorical entries.

Okay. In which unique entries. Yeah. Okay.

But for my understanding, is pivot table. Just aggregate rows plus pivot wider.

Exactly. That's all it is.

All it is.

So, so, so.

Okay. Right. So I was going to ask. How does it know. How. How does it know which are rows are.

Are.

Which are rows and which are columns? And so you're saying it's a. There has to be an additional operation there to distinguish.

Right? Yeah, so that. Yeah. So. So Steven's written step one there. And what we need step two is to do you pivot wider, which, which I think we changed the name to something like possible name change was columns to values or. Or values to columns.

Right, exactly. So that was values to columns and that was. And the other one is columns to values. Is that right?

Right. Those are. Those are possibilities.

And I mean values to columns of aggregate rows of blah, blah, blah.

Yes. Right.

But do we ever like in the documentation, do we ever say in the usage message, this is effectively like calling this and then that? Or does that only go in the.

We're not writing the usage message yet. Okay. We're not writing the usage message. We're just trying to understand for our own.

Okay, okay. Sorry, sorry, sorry, sorry, sorry. It felt like a user message, a usage message.

No, we're not writing the usage message. No, we're trying to make sure that we could write a usage message.

Yes.

Okay, so let's just understand what values to columns does. What values to columns does is it takes of tab comma, a column, right?

Yeah.

One column. Is that correct?

Well, it could be. It could be, you know, group sets. A set of columns too.

Okay. Calls. Okay.

You know, Steven, if you have discrete variables, right? So having a vector of variables can be flattened into one.

Yeah. Okay.

Right.

Okay. So takes the unique entries. No, this isn't the right way around. Takes what is happening. It takes the unique entries and the columns calls. Is that what it's doing? Values.

So, so it needs to. It values to columns needs two Two sets of columns. One, one. Which are the, the columns that, that, that are the categorical thing. And you also need to tell it where are the values coming from that are going to go in those new columns?

Which order are we putting these in? Are they new columns? There's a list of new columns here. Right, but is that, Are these two corresponding lists? Or how does it, how does it.

No, the, the, the, the. The name and the new columns we don't need. It's, it's the, the, the. The columns. So, so look, look at the aggregate rows above and I'll try and explain what's happening. Okay. No, no, no, Don, keep. Kind of keep the screen where you were. Yeah, that one. Okay, so, so what we're doing to make that pivot table is we're taking sex. It has two categories, female and male. Right. But we also. What. We, we need to tell it what. Because this, this table may have a hundred different columns. Right? So we need to tell it which, which values you want to associate to those new columns. And that comes from. So you need to specify the column that's going to become the names, the categorical column, but you also need to specify the value column.

Survival percent in this case.

Right, okay. That's the, that's the. So previously the rows here were individual people, right? Yes, but in this case, the rows. So we want to say here. And by the way, Carla, please make sure you know where we're writing all of these different. What am I saying? All these different notes of proto. Whatever they are. Okay. Proto usage message. Okay, so aggregates rows in tab. Well, let's see what we're going to write this here. Carly, you are keeping track of where we're writing these things, right? So we're going to collect them together. Yep. Okay. Yeah, yeah, yeah. Okay. Okay, so now what's the name of that second entry? But that, That's a new column, right? New call arrow funk.

Right.

And these are old calls, right? Or calls or something. Right. Aggregates rows in tab by taking unique. Well, let's say it creates rows and tab from the unique values and calls from the combinations of unique values and calls. Aggregating. Aggregating. Using as the values of these rows. Okay, wait a minute. Creating. Rows and tab from combinations. Okay, it's going to. Wait a minute. Okay, hold on. Creates. A new tabular with column columns or something New call. Can there be multiple ones there? There can. Right? There can be new call. 1, 2, 3, 4. Okay. Creates a new tabular with column, new call. Columns, new calls. Or whatever. And rows obtained from.

This. You have new calls and plus calls. I think not just new calls.

No, no, no, no, no, no. That's not right. Hold on one second. Okay, hold on, hold on, hold on, hold on, hold on. With. Okay, you're right. Okay, good. Creates a new tabular with columns, calls and new calls. Where. No, look it. Okay. Calls contains the unique values. From where each col I. Where each new call I contains only the unique values from the old call I. And where the. Where new and new columns have been created. New columns, new call I have been created using the aggregation function func. What do you think of this?

Funky?

It's. I think it's. It's really close. But what. What is the aggregation funk I applied to.

Hash everything. Hash anything?

No, see, it's. It's only. So. So for each value and call I that's unique, the function is applied to all rows with that same value of that same unique value.

I see, I see, I see. Using the function aggregation functions fun I applied to lists of values. Associated with. The unique. Yep. That is the longest and most complicated usage methods in the system.

I think. I think it would be bet one thing you can improve. Instead of list of values, I think you could put the rows of tab.

It's not the rows of tab, it's the columns it's applied col. Well, that's.

Yeah.

Okay, hold on. Let's see if we can make this. Okay. Creates a new tabular in which rows have been aggregated. Thinking of something like this. Wait a minute. Creates a new tabular in which rows have been aggregated. By grouping together. Entries with the same values for. In the call I. Combining entries that have the same values. Okay. The new tabular has rows whose keys are the unique entries from the call I. It has in addition to the columns col it has new columns. You call I formed by. The lists of values Are aggregating lists of values. I think that's roughly right.

The problem is the fun I gets an association, not a list.

Okay. All right. What is an association of?

It's association of the sub columns.

Okay. All right. By aggregating values using the funk I the values. Association. Each column in the original tab tabula. The func I. Well, here I think there'll be more of a note below. The funk I are applied to an association. Whose values are funky are applied. For each of the new rows. The funki are applied to an association to generate the values for each of the new rows. The funki are applied to. An association. Whose keys are the columns are the keys for the columns in tab. And whose values, entries, whatever, are lists of the values. In each column associated with. The unique entries. In call I. Of lists of values. That are being aggregated for this row. That makes sense. You guys are really.

Yeah, I think it works.

Driving my technical writing skills here. Okay. All right, so now you want me to. Let's do pivot table. Actually, we got all these to do. Okay. All right.

So notice on the.

Sorry, no, I'm thinking about the name pivot table. Go ahead. What were you going to say?

I was going to mention that in the examples before, we have the three outputs which represent the two steps that we are discussing here. So there is a Titanic notebook at the beginning, the aggregates rows which you just described, and then the other steps to the pivot table.

Okay. What does pivot table mean? Is that word? Is the table the output supposed to designate the output or is pivot table supposed to designate the action of pivoting?

I think it's kind of both, but it's literally presented as a table.

Yeah. Maybe we should be calling it pivot. Or we should call it pivot tabular.

The only challenge is it's not sort of a clean tabular, I'm saying, where each column is a variable and each row is a measurement.

What do you mean?

I mean, so. So when we want to do processing, you know, generally processing, we. We want sort of a clean tabular where each column represents effectively a variable and each row represents a measurement across many variables.

Yes.

This one is not quite. It's not that the output from pivot table is not that. So it's unclean in that sense, but it's perfect for humans. It's not perfect for processing.

Is that distinguishing it from pivot wider and pivot whatever. The opposite of pivot wider is longer.

Yeah. So those two operations, you could.

Okay.

I think. I think pivot wider has the same problem. Pivot longer doesn't.

Okay. So let's just write what this might say. Generates a pivot table version of the tabular tab. Okay. In which. Unique entries, by the way, call calls and row calls. The right way around. Write argument order.

I think. So you usually think row columns.

Right. Okay. Generate a pivot table version of the tabular tab. Whose rows are defined by. The unique values, the unique, unique entries in the columns, row calls and who's.

Oh, and I think what the point of pivot table is that row calls and call calls are all unique values.

You.

You take the difference is with the. With aggregate rows. Is that once you put on top and once you put on the bottom. But the content, it's like aggregating together on roll calls and call calls together and then rearranging.

This is not the point. Point is, if you're going to explain this to anybody and have them have any chance of understanding what's going on, you have to say what the point is. But so describing the mechanics of how it works is not what you do in the first sentence.

No, but what I'm saying is that the mechanic of how it works is that it's. It's a double entry table. So it's. And you put at the intersection of rows and columns, the aggregations of that row and that column together.

So.

I was going to say, I think your sentence below, the one you're working on now actually is a reasonably good description of what it does.

I don't like it. Okay, I'm lost halfway through the sentence. I want to say what the point is first. You see what I'm saying? The first thing is to say what the point is, then to say how it works. It's a pivot table version. It's like, what am I trying to do here? Pivot table version. Whose rows are defined by the unique entries in the columns? Row calls. And whose columns. Are they? Literally the columns. Col. Cols.

No, they're the values of.

They're the values.

No, they're not even the values. Like, they're. Yeah, they're the values become the labels.

Right?

The unique values become the labels and the un.

Wait, the unique values of what?

Unique values of call calls become the labels of the columns in the output.

There is one col and there is one row call. The unique values of row call become the keys to the rows, and the unique values of the cold calls become the keys of the columns. The space between those is filled in with the output of func. So it's important that the roll calls and the cold calls go only in the keys in the headers.

So if there's only one of each, why is it row calls and call calls and not row call and call?

Because I guess we'd like some tricks of standard keys and so on. You can do some terrible stuff with it and then have.

Can we zoom out for a minute, please? Just explain to me, okay, the. The unique entries. I see. Whose rows are defined by the unique entries and the columns. Row calls. And whose columns are defined by the unique entries in the columns? Col. Cols.

But if you write it like you're using column on. In One part of the sentence column means the content of the column, and in the other it means the keys of the column. So when you say whose columns and whose rows, you mean whose column keys and whose row key are defined by the entries.

Whose row labels.

Labels may be yet something different. I hate it because it's really. Who's with rows generates a pivot table version of the tabular tab with rows defined by the unique.

I mean, I think that works. I. I'm not sure what the objection to that is.

With rows defined by the unique entries in the column row calls and with columns defined by the unique. Okay, okay. This is vaguely understandable. Okay. The values in the pivot table version. Are generated. By aggregating. I see we have a sentence from Seth Chandler here in our livestream. Let me pull in Seth's drafting to use his domains language. I think by the time we've got row columns like that, it's a bit. We're a little bit.

Steven, I sent you a notebook called Explaining Pivot X.

But. But cess Description is actually quite. I mean, it's really goes to the purpose of it, which is nice.

Yes. It's important that we explain the purpose, Although usually in usage messages we don't so much explain the purpose. Although maybe that's a mistake. Seth is doing a better job at the marketing of the point of this function, which is an important thing, rather than what we tend to do in usage messages, which is just say, say what it does, so to speak. This is highly useful. Thank you. Okay, let's just look at Roger's pivot. Yeah. What I want to know is how do pivot tables show up in quantum mechanics? What are they? I bet there's something that there is. Yeah, I bet there's something. And some projections.

Yeah. It is always some form of aggregation that's going on here. And measurement is probably aggregation in quantum mechanics.

So. Well, it's more. This is some kind of projection operator applied to something. Just so for my understanding, what is the relation, if any, between relational algebra and all of this?

Actually, the.

The.

The beginning of this notebook that I cut this out of was all about relational algebra for that very reason.

But there is a very clean mapping. There is this notebook that I've shown you a couple of times called labeled Array that basically translates from operations you would do on a pivoted data set to operations you do on a relational data set. But I was thinking something else. Could we say it in terms like pivot table constructs? A table where the value corresponding to Row I call I is constructed from FUNC on the subtabular that has value blah for and blah for. So like, we define it as the sparse array. The value at position blah is given by.

Okay, look, we got to start here. I think it's important, honestly, I really like the idea of saying. Generates a pivot table version of, You know, saying this, Because something like this, like I've got some mental image what the heck I'm doing here. And how you rearrange these things, how you actually aggregate them. Like I can imagine the thing I'm imagining. Okay? So I'm trying to do something and I'm trying to say I want to see all the data about this and all the data about that. Secondary question, how do I combine this data? So I'm saying I, I think in the. Conceptually, you think first, what are you trying to achieve? What are you trying to. What are you trying to see from the data? And the second question is, you know, do I do a. Mean, Do I mean do a median, whatever else? That's why I think we should lead with this. This is what we're trying to achieve. Maybe that's wrong, but maybe, I mean, maybe another way of looking at it. The first thing you're doing is saying, I'm doing an aggregation. But. Okay, let's, let's. Okay, let's. We sort of have a vague understanding here, we haven't completely nailed it, but that we've got some pieces here. Now let's talk about values to columns. Columns to values. Okay, what that's really saying is. Take the unique entries. Okay? This is Carlo's version. That's not bad. Carlo. That's not bad. That's a nice structural explanation. The subtabular defined. That contains. That's not bad. Okay, we're getting there. All right, let's, let's go to. I like that. I pretty much like that. Okay, let's go to this. So first of all, tell me again what it does. Can somebody tell me what it does, please?

So values to columns is basically the same pivot table without the aggregation first. So you have no guarantee that the entries are unique. It's what's left to take you from the aggregate row thing to the pivot table version.

Tell me, what do you think it should say here?

Well, well, let's put in, let's put in a full specification before we try and describe it. Okay, so you need, you need a column for the column names and you need a column for the actual values. That, that.

So how many arguments does Values to columns have.

I think it could have. It could have.

I think.

I think it should have. It should be calls, you know, it should be a, A pair of thing. It should be a list of. Ideally, it would be a list of. Of rules or something like that.

Okay, but. Okay, so values to columns. Okay, so then, then just say. So this is VAL call to call. You know, VAL call to call one. That. Right. VAL call to call two. Is that right?

Yes, I think that's. That should be adequate.

Okay.

Yeah.

All right, so now we say. Rearranges tab. So that. I don't understand this. Now I don't understand. So that the unique values in VAL call one become what in call one?

Well, I think with, with that notation, I would have expected call one to be the, the column that you get the names of the new columns from. But maybe you're thinking about the other.

Okay, well, I don't know. I don't know. I don't know. Rearranges TAB so that the unique. Well, the names of the new columns.

You don't have to give names because those come from the unique values.

So what the heck is call one doing?

Call one is. Okay, so if you're thinking about the. The val. The values to call them, the VAL call has to be the ones with unique names. And then the, the values that go in those new columns with those unique names has to come from call one.

Sorry, say that again.

Shouldn't you then say VAL call the name call?

Yeah, but if we're calling it values to columns, that's.

Well, let's not worry what we're calling yet.

Okay, yeah, we could. We could do that.

Yeah. Okay, so now tell me what it should say here.

Okay, so it rearranges tab with. With. So that there are. There are new columns with unique values in name. Call one.

Rearranges TAB to. To. So it doesn't affect the rows. Is this true?

Yes, it. It affects the rows. That's why it's called wider. It's. It's typically putting fewer rows and more columns.

Maybe it's like. Okay, rearranges tab. So that. It's entries in rows. So that. I mean, so that certain. Oh, gosh. What is it doing again? Let me see an example. Where is it?

Yeah, so look at the, look at the, the aggregate and then the pivot table.

Exactly. No, no, I don't want to see that. I want to see pivot wider.

Yes.

Oh, that, that we have below some. Oh, that.

Yeah. This one?

Yeah.

Okay, where's the original above? Yeah, that's above.

So your name column is extreme. It has two unique values. High, low.

Yeah.

Become column names. They pivot to become column names.

Right. Okay, let me. Let me remind myself what this is saying. Yeah. So I'm just going to try pivot to columns and pivot to rows. Rearranges tab. So that. Unique values, unique entries in call whatever it is. To. A column for. Each. Unique entry in each unique value. In Just to create a new column for each unique value in. The original column call. Let's say is that reasonable? Where did that.

Where the entries in those new columns come from.

I see. Create a new column for each unique value in the original. Taking that, taking the entries. In the new columns from the. Whatever the names are, we might.

Maybe it made sense to call it E call for entry columns.

What do we have to specify here?

So we have to specify the. The column the names are coming from and the columns the entries are coming from.

I would guess that it's like name first and VAL off.

Yeah, that's.

Yeah, VAL co. Okay, but.

But I think if we do it by rules, then we can have several sets of them.

I know from the. Okay, well, we. In the documentation we can give the. The one with the single one first to create a new new column. For each unique value in column. In the column new call column name call. The entries with the entries and the new column. Coming from. Coming from the column VAL call.

The thing that this doesn't explain is what happens to. Like if you read this, you wouldn't think that this changes the number of rows, but it does.

Well, the entries in the new column come from the.

Come from. Come from the aggregating.

No, there's no aggregation. It's the entries entries. The entries in the column valco.

From.

The rows in the column valco call for that unique value.

The entry is a new column coming.

No, wait. The number of rows is determined by the columns that you haven't mentioned anywhere. The day call there, for example, is not mentioned in the spec. And so basically what you're saying is that I don't even know how it works when they're not key columns. But basically the number of rows is defined by the set of columns that you haven't mentioned anywhere.

Yes, that's correct.

I am honestly, I kind of understand pivot table. But just because you can only do it with things that are already unique. What this does pivot wider when the. When the. When there's no guarantee of uniqueness is baffling too.

Okay. Yeah. You know what? Pivot wider Reminds me of is in typesetting, you know, mathematical typesetting before modern times, when authors typeset themselves, there were all these names that the people in the typesetting business used for mathematical notation. So, for example, I believe fences were the name for the things which were like modulus, vertical bars. What's my point here? My point is there is a kind of semantic, non semantic word, you know, and that's a. If you are a person who's got a bunch of lead type and you're trying to find the thing that you know corresponds to this mathematical notation, you don't really care what the notation stands for. You just want to know. I want the. That looks like that. And I feel like Pivot wider is a name that is sort of reminiscent of. I just want the thing to be that shape. And I don't know whether people, you know, I feel like, what is the conceptual seed that people have that leads them to this operation?

Well, you're right. Like, I think you got. You nailed something on the head there. That Pivot wider is something that is like a total dead end for processing. Pivot wider is something that you only care about for presentation, for typesetting.

So I don't know if it's true. I don't know if that's true.

That's not always true, but it's often true.

Okay, but I want to know what is the seed? What is the conceptual seed? If the conceptual seed is. I just want it to be, you know, this shape. Right. If that's what people. It's like an aspect ratio type thing. If that's what people are thinking, that's one thing. I. I'm skeptical.

It's not the case. The. The example that's in the reference page shows kind of why you might want it. You know, it's like how people collect data is not in a way that you want to process data necessarily. So it's type of structural cleaning operation.

Okay.

The example there in this notebook produces a tabular, which is perfectly fine. I mean, we could use it as a starting tabular in any other place. It's just data in a different way, right?

Yeah, but it's clunky again, doesn't matter.

Look, hold on. What we're trying to figure out is, okay, is the notion, you know, pivot table, pivot to columns. I don't know whether pivot to columns is the right thing. It's like, you know, we've got aggregate rows. Aggregate rows. I keep on saying it wrong, which is not a good sign, I think.

Columns is nice, actually. It does, sort of, because you're. You're creating new columns by pivoting.

It could be pivot to columns and pivot from columns.

Yes.

Yeah.

Yes. That's.

That's not bad.

Okay, but does that fit in with pivot table? What is pivot table in this way of talking about it?

Well, you pivot. I mean, you pivot to both columns and rows.

In that case, it also has aggregation in it.

No, I think you always pivot to column in pivot table.

It's a combination of aggregation and pivot to columns.

You don't pivot to rows.

Yeah.

As a matter of fact, if you pivot from columns on a pivot table, you get aggregate rows.

So, I mean, obviously we could call it aggregate. Aggregate did pivot to columns. I mean, that's kind of a stupid name, but it is descriptive.

Yeah.

Well, another way to do it. This is a bit. This is a bit. So. So if you look at the pivottable, we have pivot table, tab func, row calls, call calls. If. If call calls is the null list. I either. None. That's. That is aggregate rows.

Sorry, if. Which one has. Sorry, which one?

If call calls is the empty list.

In which case in pivot table.

In pivot table, the result is the same as aggregate rows of tab func. We have to give the funk a name. But row calls.

What I don't like is the. Is the motion of P. Pivot to columns. Pivot from columns, which I. Which is fine. And then pivot table, which is not.

A motion word, but Stephen, Pivot. I think that while pivot longer, pivot wider, et cetera. Like, you go to different things and they have different names in different.

Yeah. What do you say?

Pivot table has been the same name since Lotus. One, two, three.

I know, I know. I'm well aware of this. I know.

Maybe middle school.

Okay, so what's your point?

My point is that you should call it that because people know it by that name.

I'm aware of this point. I think it will be enough to have the word pivot in there. It's not obvious. It has to be pivot. And that single thing. I understand the argument.

Pivot table is a noun. It's a visual tabular.

Yeah. And I don't think there's such a distinction. I mean, there's things like column wise, pivot, but I don't think that's very nice. Pivot to columns. Pivot to columns is fairly descriptive. It makes columns from what would otherwise be just entries yes, Well, provisionally, I think this makes sense. Okay, what do we have to figure out? I'm somewhat pivoted out here. I have we thought through what the minimal example is. And let me make a suggestion for documentation. Okay. That we do something where we have, for goodness sake, don't have real data, have ABC 1, 2, 3, those kinds of things in the kind of pictorial examples.

Sure.

Do you see what I'm saying? And, and that way. And keep it really, really minimal. Try to, try to see what is the minimal case that isn't ambiguous. Does that make sense?

Y.

And I think having that illustration there will be helpful.

I by the way, remember when we had a bunch of flesh. I mean it was before I joined the company and Flash animations.

That explains.

This is the kind of thing that makes way more sense if you make it like an animation rather than a static picture.

Good luck. No, I think it will be incomprehensible. Yeah, I think it's one of those things. The thing about animations and exposition, you know, a zillion times I thought, gosh, golly, I should use an animation for this. The problem with an animation is you are not in control of how quickly you digest it. You see what I'm saying? If it's a visual thing with a series of steps, you can look at one step, look at the next step. Oh, I didn't understand what was happening. Look back at the previous step, et cetera, et cetera, et cetera. Whereas in animation you are being force fed the thing at a certain rate. And very often. What's that?

If it's something that loops and it's two seconds, doesn't help.

Doesn't help, does not help. People don't manage to. The only way that animations are helpful is where there is some kind of overall effect that you're seeing. I mean, I, I find it in, you know, in those, those animations that we had looks really cool, but most people are like, that's cool. And they're like, what did it do again? I mean, it may be useful giving some intuitive sense. I think it is useful for that. For something like partitioning a list or something. It is useful for giving a visual sense, but it is not useful for saying in detail what it does. And maybe in this case, I mean, I kind of think that particularly if we do this and we have like color backgrounds for these different entries and things that it we fairly obvious we have before and after. See what I'm saying? I'm optimistic about that. All right. Anything else that we have to cover in this meeting. Give me a bit of a roadmap. Go ahead.

I just wanted to say one thing that is funny about this function is that pivot from columns is unimplementable. Lazily. For lazy backends, pivot two columns is. There's this weird asymmetry because one is denormalizing and the other is normalizing. One is data dependent, the other isn't. They're really not the same.

Okay, so what?

Just saying.

How can either of them not be data dependent? Because.

So that from columns, you're taking values and putting them in columns. So you have to actually look at the data to know what the outgoing columns are. And so if you do it, when.

You do two columns as well, needs to know what the unique entries are, right?

No, no, two columns doesn't. It needs to know what the name of the columns are.

No, no. What the name of the destination columns is, which come from the unique entries in the data.

From column. From columns. Doesn't need to look at the data.

That's right.

Because the data.

So the point is that two columns.

Wait, wait, wait, wait, wait, wait, wait, wait, wait. You're saying from columns. That's correct. It is a merely structural rearrangement.

Yes.

Okay. Likewise, if you said from the outside two columns, you know, you already know what the unique entries are and so on. I don't know whether. Well, it needs to know where those are. It needs to do a position basically to find out where those are.

Yeah.

Whereas the other one is just indexing with part.

Yes, pretty much.

Right. So. Yes, I agree.

But I mean, there is a version that would be implementable of pivot.

Why does this matter? Does this matter? Go ahead.

Yes, it matters for the design. Because if pivot. Sorry, two columns. If we add something that for this example is set to automatic, but where you could specify that, for example, the things are Monday, Tuesday, Wednesday, Thursday. No, then I could implement it.

I understand. That's what the column. You need to know what's in the columns. Right?

Yeah.

I think we should hide that detail. I think.

Okay.

I think that, that. I mean, I think that you can. There may be. There may very well be in the end, some kind of, you know, database machinery options that you have to give. I can quite imagine that. Optimizations and so on.

Yeah.

But I. I don't think it's very interesting in general because it's something that you do to visualize the data in a nicer way.

I understand. In terms of design. In terms of design, Right.

Yeah.

I think we might want to give up. Give ourselves an escape valve for communicating with the database server. In a bunch of these cases, this would be one of the things you might choose to communicate with the database server.

I thought, so far, this is the first time I've come up, and I've already implemented a lot of these things that we designed for the databases, so hopefully not too much.

Okay, fine.

Actually, our original. Our original design for the pivot. Pivot wider, with that extreme temp, that automatic. I mean, it could be the new column names. That's. That's actually how I originally had done it.

All right, you've got some more detail to fill in here. I am. I am really, my gosh, seriously pivoted out. I. I'm. I hope I'm going to. You know, maybe this will become. I always find when people talk about, oh, I use Excel and have you learned how to use pivot tables yet? And most people say no. And then some people who feel very sophisticated say, I know pivot tables. So maybe I'm now in the class that can say, I know pivot tables.

A little historical note I just looked up. So pivot tables were first implemented in Lotus Improv on the next at Steve Jobs behest. Although they were called flexible views, it wasn't until that idea was copied into Excel that they were named pivot tables.

That makes sense. Huh? Huh. Well, good for Steve. I wonder if. If Teo or I was around when that whole discussion was happening. That's interesting. I wonder what led him to. I mean, I'm sure he said I need to summarize this data in a better way. Right.

It seems like Lotus was experimenting with this, and. And when. When he saw sort of an early prototype was like, this is the thing you need to do, or write this for the next. And we'll, you know, we'll take over the world in his typical fashion.

Yeah.

But I. I also think it's interesting that he named it a view. So you consider it like a visual thing.

Yes. Makes me wonder whether we should call it pivot View.

The table. I think. I mean, things.

That's a little dangerous just because View sounds an awful lot like a user interface element.

Yeah, I kind of agree with you.

Yeah.

But on the other hand, I've thought about, like, what if pivot from columns was like a pivot form? Like, maybe not, but it's like just a way of re. Representing the same data without changing the data. Sorry, people, two columns not from.

Let me think about this for a second.

I mean, I mean, we listed partly as a data cleaning operation, so structural cleaning operation.

That's part of that and it's partly viewing.

Yes.

But that's. That's kind of the idea behind labeled array that, you know, can be. You can have a tablet underneath, but it behaves like a deeply nested, you know, matrix tensor kind of thing.

I still disagree strongly with this point of view. Once you have a tabular output, it doesn't matter where it came from, if it's still usable. It's a tower learning itself.

But if you look at that pivot, the second example on the notebook on the right, if you decide that you want to have the difference between high and low, I mean, there are difference between high and low for each day is something that is easy to compute, but then the things that you want to compute vertically becomes almost impossible. Like you are introducing a strong anisotropy that makes it harder to work with.

The data to do things that you wanted to do with the previous tabular, but now you do what you want.

To do and you might want to do something different here. I need to go. But. This is an interesting example. This flexible views thing is an interesting example of what I found all over the place, which is. It's always useful to know where ideas come from. It illuminates the ideas. I think it'll be interesting to know a bit more of that story because I think we might learn something. I wonder if there's. There's probably. Yeah. Lotus Improv would be interesting to see if we can find the documentation that they had for these things because they had to explain it for the first time. So anyway, should go. All right, we're making progress. See you all soon. Bye Bye.