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

Here. Okay. Hello everyone. Welcome to yet another design review about tabular. And today we are pivoting to pivots or something. All right, let's take a look at this. By the way, when are we getting these icons fixed? Kind of annoying that these are still wrong. This has been like a month. I think we need to have about.

What the correct, what they should be.

Yeah, I, I, I, I think we need Jeremy in here with you. We, we had a meeting and I thought that been corrected and so, well.

So let's see if we can reach him for part of the meeting for today. Somebody do that please. Yep, I'll do that. Okay, so what's the point here?

So this is. Go ahead, Rob.

Go ahead, Jose.

So this is an operation that is frequently used by people manipulating large amounts of data. And so it's separated into three functions and we are presenting them here. So this is an example. And you see that it's a matter of reorganizing the data so that it can be operated later in a different way.

So it's kind of a more limited flatten.

It combines reorganization, flattening, reordering. It's a powerful operation.

Okay, and what would it do on ordinary lists?

The pivot table wouldn't do anything on ordering lists because we documented reduced tabular to only work on tabular things because it's a, so, so the pivot table is a way, is in a sense a way of showing the results of reduced tabular in a more table like form. So it's easier to read.

I, I don't know. I, I'm not a strong believer in this operation as it's shown now because you, once you have data in the column names, you're kind of in a dead end with respect to all the other tabular operations.

Yeah, which is why it's sort of a, it's a, a readability thing.

Okay, hold on, hold on. How does it relate to reduced tabular?

Okay, it, it is a reduced tabular followed by a thing. So look at the reduced tabular thing below. It's right at the bottom of your screen. That's what you get out of reduced tabular. And the pivot table is showing exactly the same data.

Okay, hold on, hold on. Let me understand this. First of all. Okay, so. Hash survived. What is this third argument doing here?

That's, that's the grouping. So it's grouping by class and sex.

Okay.

So that's why those columns come out gray, because they're the key columns.

Yes. And there's nothing else in the reduced tabular. The reduced tabular is like constructabular, right?

It's really an aggregate tabular.

No, I understand that, but it's.

Yeah, it is. It is like.

Oh, yeah. In that sense, yes, yes.

It keeps the key columns and the things you're constructing. That's absolutely correct, Stephen.

Okay. I hope it has the right name.

I have a hard time with the name. I get. I get them mixed up. I always type in tabular reduce.

And we keep talking about it as aggregate internally.

Really? I mean, construct tabular, transform tabular. Right. Those are our other two, right?

No, construct columns and transform columns.

Oh, yeah, that's right, that's right, that's right, that's right, that's right.

There's one thing I'm confused about here. Why are you. Why are you forced to add the key columns manually? Isn't that, like, from construction? You can tell that class.

Oh, that's big. That. That's just a bug in the pivot table code. I fixed it already, but it's not in Steven's version. It's not in the version we were working with this morning.

I'm sorry, which.

Very easy.

I'm sorry, which part?

It.

If you look under the pivot table and it should automatically do this.

Do what?

Tabular of percent key columns goes to class. That's. So in the current code, it's automatically done properly. That's just not made into the prototype yet.

Okay, so that. So the pivot table will return a thing like reduced tabular that has the class as a key.

Yes, exactly.

Yeah.

But the thing is that really, the data out of reduced tabular and out of pivot is the same data, but in one case it's represented as sparse and the other case is more dense.

I don't understand this. I don't understand why in pivot table, why are there not two keys, class and sex?

Because one has been transposed and moved to the.

How do I know that?

I have no clue first.

Right, so first we say rows. Things to show as rows and things to show as columns. Or the two. Two things. Yeah. If you open the ref page, you'll see that that's. That's how it's written. And maybe there's a different way of. Of setting up the design. This was just a prototype so that.

That pair contains very different type of elements in its two members.

I don't like it. By the way, I had asked last time what there was that still needed to be looked at, and I don't think you may even mention this, so I'm a little bit concerned.

No, no, no, we talked about that. We need a bunch of things around pivoting.

Okay.

Whether we will finalize the design now or not.

Okay. All right. We're still gonna dig in. Let's dig in. Let's dig into this. Okay. So. All right, let me just get a scope for what we're doing here. So this is.

So if you met that point, you know, Carlo is right that this is not in the form of tabular that you use for further processing. The output from pivot table is something that's for humans. It's something that you would put in a report.

Yeah, maybe.

I would argue that, you know, if it is something that is about printing differently, it should not touch the data. It's like another form. It's, it's not, it's not a data transformation.

Okay, let, let me understand this a bit more. I'm, I'm not buying that yet. I, I don't understand why you wouldn't do something with this data still. I, I, I just don't get it. I don't see why what this is doing. Okay, hold on. So. You are putting things, you are treating these as the keys and these as the columns. Right. These are the row keys. Right. So basically it's pivot table of tabular function.

What do the inner lists in that input mean?

Because you can have multiple things in the, in this.

Look at the, look at the documentation. It's the rows versus the columns.

Yeah.

Ah, okay.

Right. Okay, so this, So the contents of. So notice that the sex designator never appears, but the class one does appear.

Yes. Yep.

Okay.

Now some systems have an idea of named, named column level names.

But yeah, but if you do that, then you get into the labeled array all up kind of.

Right.

Which is a different data model.

Really.

Well, we could have extended key of sex comma female sex male.

Yes, we absolutely could. And what would it display? I mean that to me.

We have examples of that further now.

Yeah. What is this? Should I run the initialization? No. Okay. All right, let me see, Let me keep going. Make sure that I understand this. Margins. What's that? What does that mean?

Sorry, go ahead.

Those are summaries for, for so and so the all under first in that row. That's the survival rate for everybody, male and female in first classical margins. That's just what other systems have called it. Notice I kept that as a quote because I knew we wouldn't wind up with that name.

They have different names. I mean, they quote called Subtotals and grand totals. Etc.

Okay.

I presume the order is significant. So if you had sex class, you would get first female and then male and then 1, 2, 3. 1, 2, 3.

Yes, you get 1, 2, 3 across the top.

Well, that's if you did. No, that's. No, I'm talking about the flat case.

What? Flat case.

Go up a little bit.

You mean the reduced tabular case?

Oh, is that reduced tabular?

That's reduced tabular. It would. It wouldn't make any. It would just change the order of those two columns. It wouldn't make any difference in the display.

Besides that, the last two examples of.

The previous section show precisely that.

Why didn't the female get grouped together and the male together? In this example, with the extended key, shouldn't they span?

You just mean the formatting.

Yeah, yeah, yeah. It should. Should. Should do, but doesn't.

So, yeah, it's a bug.

That's another fish to fry.

It's just a bug. It's not even like a. You know, I'll do an eating them, fried or otherwise. Okay, Okay, Okay, okay.

So. So let me ask this question that I asked before and I guess I don't know what the answer is anymore. So what happens if that third element is a flat list?

It doesn't work.

It'll default to something. But it won't otherwise work, right?

No, it complains. It says you need two lists.

Yeah, I mean, I'm not sure the syntax is right. But let's just understand here. This reductions thing is a list of rules here. But I don't understand. Your crate here is not single.

That's a single rule.

I'm very confused by that.

So that the freight is percentage rate of time.

I can see that. Right. Yeah, but. But what that's doing is it's. It's mapping. What. How does it know? Let's see.

Well, it. If.

Maybe.

Maybe you're right. Maybe we should be using extended key of sex female, extended key of sex male. And then it would be more consist with the case below.

Yeah.

I made a special case that if it was only one and we didn't need the extra thing to keep the column name simple, we just use that. But actually with extended key, there's no reason to not.

There's no reason to not.

Yeah, I. I feel that we are using extended keys to mean different things there. Right. Because in some cases the key coming from the. From the. From the second argument, in some cases it's a value coming from the third argument.

I don't Think so, but I'm not sure. Wrapped our brains around like that thing.

That thing lived and died. Those are the names of the reductions. And sex is yet another thing. So yeah, we have levels there and they all mean different things.

Which shows how flexible extended keys.

Okay, all right, all right, let's just. Let me just understand these other things and then we're going to come back to this.

Right? And I, I'm. I'm a bit surprised you don't have any examples with more than two things in the third argument.

What is this? What is this? Pivot to a wider table with column names coming from.

So this is a similar idea. You have to specify the two on the left, the one providing the VARs and the one providing the values.

I don't understand.

Can I, can I make an attempt at explaining what this kind of operation is doing in general?

No, let me just look at the example for a second. Okay, so this is saying. Extreme and temp. No, I don't understand this. Go ahead.

Okay, so what. The way I think about this stuff is that in general, if you look at, look at the examples. If you scroll up to the example of reduced tabular in general, in tabular, you want to have this kind of representation, which is a lot like the sparse array representation, only Instead of having 1, 2, 3 as the coordinates in your matrix, you have strings here. First, second, female, male.

Right.

So all these operations are operations that are going back and forth between a sparse and a dense representation.

I'm not understanding that claim.

Well, this thing first, female. It's telling you, it's like coordinate one, one in table, right below, where 97 goes. Then first male is one, two, second male is two, two, and so on.

It's not. Okay, so you're thinking of this, which I don't understand because there's also aggregation going on. Yeah, totally confusing.

In fact, the aggregation is. For me, the fact that aggregation is baked into this is extremely confusing because it's a separate operation. But for me, the question of people table is like, how do you go from the form where you have two key columns to one where.

One thing one can do? Let's just understand the thing as a list of associations. Okay. Actually, it's an association of associations. Right? Right.

Yes, yes.

Is the underlying structure. Okay, so let's just look at the association of associations. I also don't understand what this thing is. What's that structure? Is that an association? Is that an association as a. No, no, no.

That thing is always a list of associations. Only that some, some of the values in the ass, some of the key value pairs in the association are marked as being unique together, but it's always in tabular. It's always a list of associations. Yeah, they just.

You can do normal on that reduced tabular and you'll see what you get. I mean, you might have to enter the Titanic data set first.

Okay, all right, so now let's understand. Hold on. Let's understand your statement, which is interesting, which is your statement is that independent of any reduction, there is a kind of transposition operation that's being done. Is that right?

Yeah, I wouldn't call it a transposition. I think the generalization of this is not a transposition, but it's going from a depth to object to a depth N, which in this case happens.

Why I'm saying it's like flattened.

But wait a minute. What's the depth N here? The depth N is defined by extended keys.

No. Okay, so the point is that a pivot table can only ever be depth 2.

So I understand that. I understand. I'm not asking about what people who've only had systems that can deal with depth two have done. I'm trying to understand from our point of view, right? It sounds to me like if I look at this, okay, it is a thing which has aggregated. That aggregation has been lost here. Is that correct?

No, it's not.

No, that has the aggregation.

It's like. No, no, no, no. Look, look. How do I know that this is a key here? There's nothing that says, you know, from metadata. No, no, no, no, no, no, no. You lost the game.

The normal loses the metadata. You're right, Stephen. Yep.

Right. Okay, so let's understand if that's the case, maybe we should be exposing the metadata more clearly.

Absolutely. And also, I think there should be a. From tabular that doesn't have this. Instead of like, normal can only do one thing, but from tabular could return an association of associations.

Fine, that's fine. Let's pin that particular idea because that's a perfectly reasonable idea. But what I'm trying to understand is what. What we're doing, if I'm understanding correctly, in a bunch of this pivot table stuff and the reduction, but the reduction is doing something else as well, is we are rewriting the metadata. True or not true.

You're rewriting the metadata in a way that is data dependent.

But.

Yeah.

No, no, no, no. Just a second, just a second, just a second. What does. Okay, it's a little bit surprising to me that the normal of this Shows no sign that these are keys. And it seems wrong to me. Am I making sense?

But the problem is that those are not keys. If you use select on that thing, you can say, select sex equals male. So you don't access it as keys. You access it with select. You can actually.

Why is it not. Wait a minute. Why is it not a key in that sense? Then what the heck is it doing? Why is it showing up as gray here other than just for some crazy random tabular metadata region?

So we. We spent a lot of time talking about this. It can act as a key, but it can also act at date as data and. And we float.

Sounds like a hack to me.

It's not. It's not.

It's.

It's. It's actually way more powerful to be able to.

I don't care. I mean, I'm sorry. I'm just. I'm. Look, have we talked about this before? With me?

Yes, yes, absolutely.

We spent weeks on this in. In the spring.

Okay. Right. So just so I understand, What is it about this tabular that makes those things be key columns? Is that what we talked about as a key column? What about a column over on the right? Can it also be gray and be a key column? Or is it only ones on the left?

They don't need to be on the left. They can be in the middle, the position.

Okay, so if I make this into a tabular. How do I make this into a tabular? With a key column with survival percentage as a key column.

You take the data and two, tabular.

Survival percentage can't be a key column because it's not. It's not unique. It doesn't select a unique row.

But. But you can say tabular of the data key columns goes to six, then only the second one should.

That would be incorrect as well. And we need to check for that. We don't have to.

Let's see what happens.

Yeah, yeah.

See, it's wrong.

But what does it mean that the.

Right key columns are columns that if between them you make it actually you create new. You can identify unique rows. Right.

By the way, this should have errored out, right?

Yes, it should. That's what I said.

Yeah.

It needs to check that. It doesn't do that now.

But the point of key columns is you can. You can index now by like male, female, Right? I mean, in this case.

We never came up with an indexing method that actually uses key columns. We discussed it for ages, but we never came up with an actual design for indexing via key columns.

So what's a situation where it matters what the key columns are?

We haven't. We haven't. I mean, in spring, when we talked about this, we floated the idea of having some kind of select first equivalent that uses key columns, but never, never figured out what the right name was and still didn't want to use it as. As part. Unless it was the normal. Was also like an association of associations.

Yeah.

Okay, so we made the decision here. And by the way, I don't think it should be this gray color. I think it should be another color. If this isn't a genuine key, I don't think it should be gray like this.

Well, but is it going to be a genuine key at some point in the future or something? Or is it.

No, I don't think so. I think it's not what was imagined here.

I think it. I mean, I would really like it to work like key. But you don't like.

But it doesn't. It doesn't work like key. Look at it. It doesn't work like key. If you want it to work like key, let's have a sub association here.

This should be the role of. From tabular. We have a second argument that returns what you want and it might be the normal. That would suggest.

Yeah, I understand, but how do we use. How do I find the number 15? What do I do to get to the number 15?

Select.

Okay, but then I'm doing select. Okay, hold on, hold on. Then let me just do it. Okay, so I'm doing select there. And then I say hash class equals. Equals second and hash sex equals. Equals male. Oops. You know this thing with. Could. Could you mark that? Because I've been telling John when I'm running into situations where the wrong thing happens with. With the. With the auto closing. That was an example. Timestamp. Well, 2.46 Eastern Time.

Yep.

My dad, could you. What the hell? What's that about? Why didn't that work?

I'm not sure.

Function false goes to error. Box of. Tag box of.

Okay, but this should have worked. Correct. And it should have returned the tabular with just one row. Correct.

Yeah.

Yes.

Yep. I can't tell what's wrong with. It's a bug or something.

Wrong.

I can't tell from just.

I think that the boxes are corrupted for some reason. Right?

What's that?

I think the boxes are corrupted for some reason.

Try putting the two tabular in instead of the pasted boxes in input three. See if that works. Or just percent three maybe.

Oh, let's put in the percent three. Yeah.

May not. I don't think that's going to change anything.

Notice the bugs at the bottom bar. Is. Is disappeared. Also on this one, do you have that bug Pelipe? You see a bug? I just hit a bug. Yeah, okay. Boy, it's hard to extract that number, isn't it? How would I do it?

Oh, you can just do open. I mean after. Instead of doing normal, you can just do one comma survival percentage.

Percent five, I think.

Yeah.

Okay. Okay, so.

But it's kind of.

No, no, wait a second. Right now, key columns are purely decorative, correct?

Correct.

Yeah.

Okay. Even if we didn't, there is no way to select by key columns. Go ahead, what were you saying?

Well, I was going to say even if we didn't, I mean, it seems like having indexing them at some point would be nice, but. But even if we didn't, would there still be some like, performance thing like you designate something as a key column and now joins are faster over key columns or something?

I mean, I don't think performance wise that really matters. What is important is that if you designate something as a key column, then basically the effect of the implicit select you do on them is guaranteed to return either one.

I got it. I got it. So look, if we're going to have these key columns, okay, and we're going to display them like this, then I think we need some kind of way to index into this using the key column information. Do you see what I'm saying? So we need a function that. Let's assume we had these two key columns up here, right? This. This thing here, right. We need a function that, if I say, you know, That somehow is capable of taking that thing, which is a key column designation, key, you know, a combined key column designation. And that defines a row, right.

Can we use tabular row for that? Like tabular row of tabular comma, a key column designation.

What does tabular row do otherwise?

It doesn't evaluate. Right.

What's an instructor? For a tabular row, you do tabular row of like list of 1, 2, 3, and it creates a tabular row object or a single association.

Why doesn't it typeset more nicely than that?

Well, Jason and John. Jason will be on it eventually.

Okay, make sure. Felipe, make sure that you want to.

Talk about the icons or other appearance issues.

Sorry, Jeremy is here. We have Jeremy and Tim. Okay. All right, we'll get to. Just a second. Let's just think this through for a second. Okay? I don't think this is right. The Issue here is you're selecting normally the only way you pick out a row. Okay. If I take a tabular like percent3 and I select like that. Wait a minute. What the hell?

No, the first one is row.

All comma sex.

All comma sex you can do.

Okay, so why is it the case then that we're not supporting four key columns? That we're not supporting this kind of syntax where you just give the key columns right here?

Because that doesn't fit with normal. But I would.

Wait, wait, wait, wait. Given that we define these as key columns, that defines a unique row. Correct. Key columns. The definition of a key column is that the set of key columns with their values defines a unique row. Correct?

Yes. Or. Or zero or zero. I mean, you. You might type. You know, a fourth class doesn't exist.

Okay, I understand, I understand. Either return a tabular row or it should return missing, I claim. See what I'm saying?

Yeah. It's a bit verbose to have to repeat the key names, but why not.

Use an extended key with the same key columns in proper order?

I'm sorry, what are you saying? What are you saying? Wait, stop.

I would use extended key with the key columns in the proper order.

Okay, so this would be the alternative. Would be two extended keys here. But that's not going to work.

No.

Why you have extended key of. Of second comma, female.

Exactly.

But that's a little weird because, I mean, normally extended keys, it's like that. The earlier arguments are things like what table you got it from?

Yeah, that's right. I mean, the extended key here is this, isn't it?

No, not through the.

Either. No, no, no. It would be. There's no extended second comma is what.

But that's not right because extended key is like a directory, you know, it's like a path name, extended key. As we are using it in my. As my understanding is it's like a path going from more general to more specific, not just collection. What's that?

It's hierarchical.

Yes. If we wanted to, we could have some kind of extended key of list that will be a, you know, just a, you know, a blob. But it's. I think generally it should be hierarchical.

Well, extended key of class goes to seconds and female.

I mean, we can do it. We could do that. That's fine. But it's equivalent to this.

Yeah.

Yes.

Guys, can I. Like, what's the cost of having another symbol do that instead of extended key?

I don't think we need.

I think another symbol would be Better. Yeah.

No, but I'm saying if you don't want to repeat class and sex all the time and you do this often, like if.

Well, let's see whether that's. I mean, in that case this would be. I mean this would be rather obscurely. It would be columns. Key.

Could it be just normal key?

Yeah, we have nothing with the. That's true.

We're not using key directly, so I.

Don'T think that's great because I think key. Why did we introduce extended key? Could.

To have this hierarchical thing that can be elided sometimes could be roki. It's the.

It's a decent idea.

Yeah, that's actually very clear.

That makes sense to me.

I don't know if it should be bar or have a list, but I'm not sure either.

I kind of think a list might be a good idea. It feels like a list kind of thing. And also the list is what you're going to get out if you say values of this.

Yeah.

So I agree with that. I think these two could be an alternative syntax. Right. One of these two. Either of those two. Yeah.

The question is what happens if you specify an association that is not a match? Like you put in the association, you put just class and then you get something out that is not a unique set. What do you get?

Well, the question is then do you get. Is that like a part specification with a list where you get. I mean, this is going to return. Okay. This would return a tabular row. So it has no choice but to return a tabular row. It can't return a list of tabular rows. So I think it's an error.

That's fine. Baming Fine.

So they're not. If they're not key columns, we. And it doesn't include all the key columns. It's an error.

It's. Is it a missing or a failure?

I think it's a failure.

I think it's a failure. Yeah.

Okay, so now then key columns mean something. It means it has two attributes. This is nice. I think key columns actually then both displays in a certain way and it is useful for row indexing. Right, Right. Yeah.

And yeah. And it. It makes you win a new from tabular like form that wasn't possible before.

Yes. Okay. All right. So we have to define what the from tabular has in it then. Just so I understand here, this. The pivot table is making a bunch of row keys.

No, that's what the reduced tabular.

That's reduced tabular makes a bunch of row keys and then the pivot table just rearranges those to be more readable.

How does reduced tabular.

Well, reduced tabular finds all the possible groups of the list, class and sex.

But reduced tabular can work without that list of groups, right? It can just.

But then it only gives a single. It only gives a single row.

I see. So as you might as well just have extracted that tabular column and just done the operation on it.

Yeah.

Sort of obscure to do it this way.

Yep.

So what this is doing, which is kind of addresses the name reduced Tabular, This is reducing. It's aggregating into. I mean, maybe we just. You know, I think we said reduce tabular because we were considering the two argument case. But maybe the two argument case we don't really care about. Maybe it's the three argument case that we care about. And the point about three argument case is it's creating a bunch of things with key columns.

Yeah. And I think it is the three. I mean, Carla can correct me if I'm wrong, but I think it's a three argument case that. That really matters.

I don't know. I think they are both important.

Okay, listen, we gotta make a decision. Okay. I think the two argument case we should blow off because I think the two argument case, It's a really obscure way to basically total a column. Right.

Can I. No, I disagree. I think if you think about it like the. It's not like the true argument case doesn't have key columns. It's that it has a set of, say, zero of key columns. So it can only ever have one row. So it's, It's. It's the natural. It's the natural extension of this.

I, I understand, I understand. But I think we should name it based on the three argument version. And the two argument version is a degenerate case.

Okay.

Because I don't think the. The two argument version. I think for many people, if I want to get the total here of those numbers, I'm not going to do that. I mean, let me just see what's involved. Can I. Can I run this. This thing? Or is it dead meat? See? Okay. All right. So now I want to get that. Okay. So here's what I do. So because we have to say all comma, survival, percentage, right? That's how we extract that column. Now, if we said total of that, we get the result. Good. Now, is there a less obscure way to do it than that? I don't think that this. You know, if I say reduce tabular, I really don't think this is going to be less obscure of this comma.

It's not less.

So hold on, let's just see what it is.

No, no, you need, you need to specify. So you want to go. You want to go new total is.

Is total goes to.

Total goes to total of pound survival percent.

You know, we really need to get a user assistance thing that lets you. That gives you an autocomplete there. It needs to give an autocomplete. Did you understand what I'm saying? In the case where you put that ampersand, it needs an autocomplete there. Could we make sure that that's in the user assistance meeting again?

Can I. Can I also make. I think another thing that would be cool is like you double click on total, you select the whole expression, you press some percent. You get that like, you know, auto parenthesized.

I see what you're saying. That's not a bad idea as well. Felipe, could you communicate that to User Assistance also? Yep, I can.

There's also of course the long mentioned thing about we could have a match fix notation for function.

Yes, all right. But by the way, it's not going to work to press an ampersand there unless it's a control ampersand or something. Would have to be command ampersand.

Why? Why not?

I mean, because it's a selection and you type Amazon. It replaces the selection.

Yeah, but I think a lot of ides lately cross bridge that like nonsense.

We're not going to do that. We're not going to do that. If you have a selection and you type a text key, you're going to get the text key.

Okay, but let's, let's have a.

Generally it's always. I've seen some things like this in like editors and I always turn it off. It's super annoying.

It does in Visual Studio and it drives me nuts every time.

Let's not discuss what's wrong with other people's ux. It' very interesting. Okay, what we are getting here. This is a total mess, right? This is a total, total mess. To get out that number. My gosh, how many more levels of crud do I have to put in there?

You don't do normal, you just do part one, comma total would be what you would do.

One, one. Really? But.

Yes, but I understand that. But, but either way.

But the point of this is that you can do it on, on multiple columns. You can get multiple metrics at the same time. It stays a tabular. You can you use it in another tabular Computation after.

Honestly, if there's no.

You know, the.

Yeah.

Naive thing. I mean, if there's. If there's no grouping in the reduced tabular is the reason it returns a tabular at all and not just the row.

Yeah.

I don't think this case is useful. I don't think the two argument case is very useful. So let's not concentrate on it. Let's look at the three argument case. Okay.

Yep.

Okay, so what is the three argument case doing? It is creating a bunch of key columns. Which could be anywhere. They don't have to be at the. At the beginning here.

Oh, they don't even have to exist. You can even say, like, put a function there. Well, a rule, a named function. But you can create a new function there as a grouping.

Okay.

Like you can.

Oh, yeah. But. But they have to be key columns. This, this set of things. Is that true or not?

They don't have to be key columns.

They become. Well, are they key columns or are they not key columns?

They become key columns. Right, but that's the whole point.

That's a criterion for a key column, which is that it has to be uniquely select a column. Right?

Yeah.

But it has to be unique because the output is a group by. Right. So of course.

Right.

Yeah.

Okay.

Yeah.

Okay.

And I think this is. This would be the canonical. Like, this is the most rigorous way to add key columns to a tabular.

Arguably. Yes, I understand. But there might be an existing key column or can there not be an existing key column? Does it have.

Like you're importing the data from a relational database. There is defined as primary keys in the original data. It's a key column.

Okay.

It gets imported that way.

Okay. So I don't believe in this term reduced tablet.

Me neither.

Like it either.

So what's that?

I don't like it either.

So.

Right. Okay. So. But the function that you use is a function you are applying to a list of data, correct? Or. No, your. Your. Your. It's F of that list, right?

Well, no, it's. It's. It's F of the columns. So you're. You're doing F on the. The association of columns.

Not an association, it's a list. It's. Yeah. Wait, association.

Which F? Which F? Because there's. The F in the second argument is applied transpose.

No, no. And the third argument.

Yeah, that's applied.

Is applying total. For example.

Right. Okay. There's two.

The points.

There's a function. This is really almost identical to group by. And in group by, you have Actually three functions that you can give. Right. So, I mean, here we're just doing the class comma sex is equivalent to saying something like, look up of class comma sex or something like that.

How equivalent to group by. Is it.

Almost the same? Except in group by, you don't have to name the thing like P like you did. I'm not sure that's as meaningful.

And also it's like array rules of group by.

Really, let's be specific because we may be able to use group by as a naming anchor.

Right, so. So if you write. If you. If you maybe just go to the docs for group by to get some example input. But. Right, look at. Yeah, look at that. Like third example, the one that has first arrow, last comma mean.

Right.

So this, I think is like a reduced tabular of that, you know, some data kind. I mean, this is not really tabular, quite tabular data, but like. And then, and then you'd write something like.

So what is the first arrow last?

So that means you take the first. You group by the first thing. So like a, you know, B, A. Oh, I see. On the last. Right.

Well, we can try to use the Titanic data set like that. The normal.

Okay, okay, okay, fine. What's the analogous thing for tabular?

So it's reduced tabular. I think this would be reduced tabular of that same list. And then it would be. You're grouping by first. So instead of class comma sex, you'd write first.

That's.

That's the third argument of reduced by. So the argument order is all flipped. I'm not even sure it's in a list like that. I think it might just be first, but I. I'm not sure it can.

Be in a list.

Okay, fine.

Probably, yeah, probably it's something.

But I'm sorry, listen, listen, may I make a metamathematic comment? We have got to speed up these reviews of tabular. We cannot spend another tens of hours on this thing. So Carlo, please try and restrain yourself. Okay? Just, just let's keep, you know, focused. Okay, Sorry.

Okay, fine.

And.

And then the second argument is basically going to be mean, but it's going to be annotated in some way because it needs to be a name, needs to have a name.

So let's see if we can imitate this syntax, right? This is the aggregate. And this is. I mean, if we said, if we even allowed group by to work on tabular, what would that mean?

Problem is group by creates nested sets of associations. So that's. That's where we run into trouble. We want to have this. This form where we keep a table versus a nested set.

What does it do? Nested? Can I see the nested version?

You should try a list of. A list of associations version.

This seems to be one. That one.

Yes.

That looks like a set of.

Well, I think that's something because they. They did two functions. That's actually a different. That's making nested stuff in a different way.

I see. But it says there. Says there's a note that talks about the nested.

I mean, it's really just if you group by. If you have, say, the Titanic data.

But can they get the Titanic data? Where is the Titanic data? Let's see. Example data, data set. So I have to do it.

You need a list. Run that list, data set, comma, Titanic.

Okay, so say normal of that and. And group by comma, sex or class or whatever you want. Well, you can see this actually kind of represents the key column stuff we were just talking about. So you actually have to write lookup of class because it has to be a function incidentally, inside of. Yeah.

What do I mean?

Key of class.

You can just string class.

Can you? I thought you could only do that in data set. Yeah, that's what I was going to say. In dataset.

Send data set. Yes, you're right.

Sorry.

You can use key of class, I think.

Can you? I didn't know that.

Oh, wow.

I always use lookup, but because key works like Bart.

Okay.

Yeah.

So note though, that basically if you run a data set of this or if you just look at it, this is really a association with a bunch of separate tabulars. It's like three tabulars.

Right. Which is pretty easy to understand given.

That although it has a completely useless first column for.

Well, yeah. Right. So the first column. Right. In this case, though, it's because we.

Grouped by a column.

We might have grouped by something more complicated. Right. Like string reverse of, you know, class.

Right. But that's a part of what we did when we designed the tableaureduce is.

We tried to get rid of this.

Nested nature of the group by.

Right. What happens if I do this? If I say group by and then I say hash class, and then I say round of hash class, comma, 10. No, not that class is not a.

Yeah, you can do that.

But you can do that in reduced tabular.

You can do that.

Right.

So I think that the key difference in this and reduced tabular is instead of.

Right.

Instead of making this nested association structure. You just add a column that says what group you're in, and then you.

Add a key column.

And that's the thing is we're keeping it. That's. That's exactly what we did in the design, Christopher, is we tried to keep it tabular, as opposed to deeply nested.

Yeah, that's the thing to do. Yeah.

So, but maybe what we need is to call it tabular grouped by. If it's going to have exactly the same behavior as group by, but it maintains tabularness.

But it seems like the syntax will also be probably a little bit different.

Yeah.

Because again, we need to name. Yeah. With nothing else. You need to name the new column. Right. The one that says what group you're in.

And the other thing that, you know, group buy has this idea that it's fine to return list of things, whereas in tabular, you can't really return list of things. I mean, you can, but it looks terrible because they go into the cells.

That's not pure words. Come on, we've got to really hurry up. Your point is what you need also.

You always need to aggregate as well. You can't just group. You then need to.

Thank you. Okay, that's what we needed to know. Okay, so. You're saying, I mean, if we called this. What. What is the relationship between this and aggregated entity class?

Same argument structure.

What's the same argument structure?

Aggregate, reduced tabular, aggregated entity class have the same argument structure.

Oh, it's exactly the same.

Yeah.

In fact, you got cold.

Aggregate. So what would happen if we had like an aggregated tabular? I mean, every identity class is probably not going to survive because that whole.

I would argue that.

Go ahead.

I was just gonna say, I would argue it shouldn't be passive voice like that because all the other tabular ones are active.

Right.

Yeah, yeah. And, and.

All right, okay. Aggregate tabular. Okay, hold on, hold on. So aggregate tabular. And then we should not worry about maintaining compatibility with aggregated entity class, because I really think that's going to go away.

But an advantage of that, that syntax with thing goes to one is it's very similar to transform and construct columns.

Which thing here? The reduced tabular.

Yeah, the.

The.

The name goes to function.

I understand. But in the end. We are, you know, the two argument version is not useful, so we should not put a lot of effort into it. I mean, the main point here is. Well, did.

I mean, if you call it something aggregate, then like, if, if it has a name like group by then. Right. The Primary thing it's doing is grouping, and then the true argument form is pretty random. But if it's called something like aggregate, then I would assume aggregating the whole table is kind of a primary thing.

It does.

I mean, what other way do we have fragrating the whole table? I mean, is it just you index the columns?

As you could see, it was a big mess. It was a big mess. Doing this. Look at this messy thing. Look at that.

What else do you write? You.

You have to.

You do tab part.

This total of all commas. That thing.

Yeah, okay, fine.

That's it. Now, it is the case that you might want to total all the numerical columns, for example, I'm saying how would you do that? How would you add a row that totals the numerical columns?

That's something we're still trying to figure out is how do we. How do we specify types with the column metadata?

But even, even just assume they're all numerical columns. How do I add a row that represents the total of all of them? We don't have a way of adding a row. Right. We have ways of adding columns, but not ways of adding rows.

It's a very weird thing to do.

Yeah, wouldn't be.

I think you just got to use a pend and stuff. Like that's just a. Yeah, okay.

All right. Okay. So. So we don't think that's a. I mean, it would be in a spreadsheet, but we don't think it is here.

Yeah, no, you should not have one magic row that represents something different from all the other rows. All right.

Okay. Okay, fine. Okay.

That's sort of what the margins thing was in the, the, the pivot table idea.

Okay. Aggregate tabular, where. It's not fraking aggregate. Why isn't it just construct column? I mean, this is constructing a column.

It's a different, A different functional. Functional semantics.

And what is being fed if I put a hash class. Look, it's like construct column, but instead of giving you. When you say hash foo in this construction thing here. Okay, so if I say construct column items tab, let's say I put in total here, but then I have a hash foo. Yeah, that's going to just make me the individual hash foo in that.

Well, see, the function is being fed.

I know it's going. Fed the individual row. This is being fed.

No, no, no. Columns. Yes, that's correct.

Yeah.

Yep.

Yeah. Individual row. Right, right. Whereas the corresponding aggregate tabular case call.

To make aggregate rows. Given that we have construct columns, I.

Think that's an interesting Idea or group rows. Okay, so here this is being fed for hash foo a list of the grouped rows. Correct. Grouped by these things here. Correct.

Yep. What it does is it basically groups groups by the rows, separates those and makes columns out of those.

And we remove the aggregation argument from construct columns. Because we certainly had one at one point.

No, I mean, no, we came up.

With this idea of column wise thread and column wise value.

So you're saying that if we did a column wise value, a column wise.

Value has the same semantics and the construct columns with column value has the same semantics as aggregate rows.

Right. So this here.

Right.

That there. Is going to give you a column for every row that has the same number in it every time.

Right.

I mean, correct.

Yeah. Well, you'd have to put the total around it to get that, to get it to a single value. But. Yes.

Otherwise it would be a list. Okay.

You want the call, you want the total inside the column wise value.

But. Oh, yeah, yeah, right.

Yeah, precisely.

Okay, so that becomes same row, same value. Right, Right.

Yep.

Okay, so here, this is implicitly. This is equivalent to. I mean, if we made column wise value capable of taking a group argument, can it do that? Let's say we said column wise value #foo comma and then some kind of grouping designator. Right. Then that would be giving for that row. Right. That is, that knows what row it's on. I mean, in general, hash food, this whole thing knows what row it's on. Yeah, but that.

You can do that. But then, then what you have is window functions and not aggregation. In that I'm not.

No, no, we're not talking about that. Right. We're just trying to understand whether this is useful.

Oh, absolutely. Yes.

It's definitely potentially very useful.

Right. And this seems like a reasonable syntax for it. Do you agree with.

If we can add the third argument for sorting, then it's just perfect.

I think it's a sensible idea. I like this idea of aggregate rows. That seems somewhat understandable. And I think we should primarily document its three argument form and secondarily document the fact that it will do it for.

Well, we could just require three arguments and make somebody put in the null list the third argument.

Yeah, don't worry about that.

Okay.

That's hostile. I guess we just document it as a second case. Right. All right. Okay. I think I understand this now. So when we say aggregate rows, class and sex become key columns in this case. Yeah, which we know is okay. Yes. Right. Yeah.

The, the grouping. The grouping guarantees that that's okay.

Yeah. Okay, yeah. And with information or is it with options? We can extract the key columns of a tabular. How do we extract the key columns?

There's a, there's a, there's a, there's a string property quote key columns that would extract them.

But we, we're going in the direction of the.

Wait, wait, wait, wait, wait, wait, wait, wait. That's not with a sub value, right?

Yeah, that's with a sub value that.

We can't do that because the sub value of a tabular is a thing which is an index.

Never a bear string.

Okay, fair enough. Okay.

But also Steven, we're going in the direction of not making these things that are not optional options like column names and key columns and so on.

Yeah.

I'm sorry, key columns is optional, right? I mean, yeah, I'm not sure I.

Understand, but it's, I mean Carlo, Carl and I both think that it's, it's, it's much more fundamental than things that should be options. It's really an important part of what the, the table is.

Oh, I see.

And so because you're not a database back end, it's pretty fundamental. So we want to have a different way of inputting that because Roger wants options of tabular to return all the options which are going to be things like styling and why don't we say information.

Why don't we have an information of tabular that returns all of this stuff? Does that make sense? What does information of tabular do?

Right now it just returns what some of the sub value methods can be. Kind of like data set, data structure. I mean.

Well, I mean information should return something useful. An information of tabular comma, blah blah, blah, should return the value of the appropriate value of that property.

I think if we decide that sub values are taken, then that's the way to do it. And we do that in several other places where basically you can't use sub values. So instead use two argument.

I'm skeptical about using sub values and I think it'd be better to use information. I think using sub values is a hack.

Okay. And I think the bigger thing with sub values is if we, if we change the way indexing works, then it.

Would be a problem. Yeah. So I think we should set up information to work with tabular. Okay, let's come back to the main topic. The main topic is this is very useful progress in my opinion. This aggregate rows thing is nice. Please, let's get it implemented quickly so we're not and you know, etc. Right.

Yeah. That can be done that can be done very quickly.

It's basically colonized values, second arguments and third arguments. Also interesting.

Okay, that's not so easy.

Okay but, but let's make sure it's in the list so we know we're doing it right. So we don't.

I mean, and an alternative to that might be to have, you know, the third argument in construct columns to tell what groups to use inside of any column wise value or column wise thread.

What's the third argument of construct columns?

Normally it would be. It. There is none.

Okay. I think that's a reasonable idea.

And then the grouping could be computed once and not have to be done every time you run into a construct columns. But maybe Carlo has a different case.

For that and I have to think about it.

Okay. I think both of those are reasonable ideas. I think both of these should be doable. You know, this might. I can imagine sometimes wanting to look at something which is, has, you know, the local group, so to speak, and sometimes not bothering. Yeah.

One quick question about aggregate rows. Is, is it more useful to have the two argument forms as an operator form?

I think we.

Are we doing operator forms for other tabular operations?

We are, absolutely, yes. Because. Yeah, this is very nice in the set.

Like Carl actually just fixed that this morning very nicely so that, so in.

That case, construct columns can't have a. Can it have a third argument?

Yeah, yeah, yeah. It's not a, it's not a problem because the, the, you know, tabular is never a list of rules. So there's, there's no clash there.

Yeah, but construct column has to take lists of functions, so that's more difficult. Yeah. So I think, I think we have to stick within the column wise value. But, but so that, that's harder to do. But it's, it's possible and super, super useful to have that grouping there.

Okay, let's. I kind of agree that the operator form aggregate rows is nice. So we shouldn't insist on a third argument.

It. It works. I mean it works with three arguments. Two arguments works in the prototype just merged it.

What Aggregate rows.

Aggregate rows. I mean, with your name.

We'll change the name.

Yeah, yeah. Okay.

Yeah.

It'll be in tomorrow's furtive.

So the two argument case is an operator form. Is that what you're telling me?

You can also have a one argument operator form if you want.

No, no, no, no, no, no, no. I'm talking about the two argument form because previously we've said the two argument form can be this where it's aggregating all rows. Okay, that's the. Okay, so that two argument form cannot be supported if we have a two argument form that looks like this. That is an operator form. Right.

There are always different objects.

But I don't care.

I just. So that it works.

But. Okay, fine, that's lovely. But this will be the documented thing that. That's an operator form.

Yeah.

Okay.

Okay. Okay, let's go back to the pivot table thing. And I know we got to deal with this graphic design thing, but I'm sorry, we're kind of on a roll with this other stuff, and I don't want to let it go. The.

So what the pivot table is, is effectively an aggregate rows followed by a pivoting to change some of those key columns into. Into actual column names. That's all it is.

What do you mean by key columns to column names?

Well, okay, so when you do. When you do aggregate rows of titanic comma, p rate, sex, class, sex, you get that thing. And so you have. So the sex is a key column, right?

Yeah.

In the pivot table, we're saying, instead of taking that, showing that as a row as a bunch of rows with female male, we're saying make those female male into column names and show it as a. As a more compact, you know, readable table.

Okay, so.

So the data is identical to the aggregate rows data, but we're just showing it in a. In a different way.

So the function pivot tabular, or whatever it's going to be called, pivot table. I know that's what it's called. Yeah, okay, but, but I.

In, in addition, those totals also, you know, adds value.

Okay, but you know what the thing I don't. You know, these functions that like sing, dance, jump through hoops, and so on, all at the same time aren't necessarily a good idea because they're very hard to understand. And people. And people end up in this mode, which they do with spreadsheets all the time. Oh, I just push this button and it produces the table that I deliver on Wednesdays type thing without ever understanding anything about what's going on there. Somebody set up that button for them 10 years ago, and that's the button they push. And, you know, that has value, but it's not typically what we are, you know, going for. Okay, so let's just understand what this does. The minimal operation that this does. Right. So is this the pivoted result or the unpivoted result?

That's the unpivoted result.

Wait a minute. What Are you saying that that's the result from aggregate rows?

Right, exactly. And so what we do is we take. We go one.

Where's the pivoted result of that?

Let's see. That's, that's, that's there, that, that pivot table.

Okay, so this here.

Yeah.

Is the pivoted result of a thing with these key columns.

Yep.

I need to put these both on the same screen. I forget about the margins thing.

Yeah, forget it. I mean, those two examples up near the top of the page show two different directions of pivoting. You know, one shows classes, columns, and the other shows gender as columns.

What's the minimal operation that's going on here? Is there an operation. Forget aggregation. Forget anything about aggregation here. What's the minimum of, you know, thing. If we think about it in terms of lists of associations, what is this doing?

Well, that's the minimal operation from that table you just pasted is what we've called pivot wider. So it takes the female male, it takes the delete duplicates of that and makes those into column names. And then you have to give it. What column do you want to choose for the data? And that would be survival percent. And it associates the data with every female, it puts that in the female column. And the data from every male, it puts in the male column.

And how does this relate to what we're doing in data cleaning when we. I mean, if this was done with sub associations, each one of these things will be a sub association, and then we are basically flattening that sub association into the association for every row. Maybe that wasn't very useful.

I'm not sure.

Well, anyway, all right, so again, can we do the minimal operation here? No aggregation, no whatever. Just turning things around.

Yeah, that's what we've called. We've called that. So that's below. That's what we called pivot wider now.

Okay, is this an example of that here?

This is an example of that. So. So we have a column. We have a column of high and low, right. And we're changing those high and low by delete duplicates. So we get the delete duplicates.

So what we're doing here is we're taking. This is the content we're filling in.

Yep, precisely.

And.

I don't understand the syntax at all. I don't understand it at all. Why isn't that saying extreme?

You have to choose two columns, right? You have to choose two columns.

And.

And the role is very different.

If you look. If you look a little bit below Jose Put in an example where we use extended key, and that might be much. Oh, no, we didn't put it here, did we?

Okay, okay. But look, let's just take a look at this. Okay, so here we're taking these values, categorical values.

Yep.

We're using. We're columnifying those things. We're turning.

Each category becomes a column.

Yeah. Right. Categories to columns, basically. Yep.

Mm.

That's the key operation, correct?

Yep.

Yep. Okay.

That.

That is what pivoting is.

Okay. All right. So. So we didn't lose. So in this data here.

Yeah. It's the exact same data just rearranged.

It's rearranged to pair this as a column name. That row value became a column name. Okay, so it's basically row value to column name.

Yes.

Okay, let me understand what. How we would. Earth. We would say that. So, I mean, we could certainly, you know, that's what. Now here's a question. Is there an operation. Okay, let's just think about ordinary lists. No. What would. What. What's the minimal structure to which this meaningfully applies? Is it a list of associations? Yes, it is. Right?

Yes, I think so.

Row value A. Whoops. A goes to X, B goes to Y, A goes to one, B goes to Whatever goes to.

Yeah.

And then we say A goes to two, B goes to Y. And then we say A goes to one, P goes to Z. Okay. Now if we do a row value to column name on this, so it's the value of the. A's become a column name. So the pivoted version of this becomes column name one. If I'm not wrong, one goes to X, Y. I'm confused here. What. What else do I get? I. I've also got a one goes to Z, which doesn't that. Right. One goes to. Yeah. One goes to Z. Yep. And then I've got. I've got two goes to Y. Okay, so what Operate. What do I. What's that? Is that correct?

Yeah, so. Yeah, but the. But see, to make those columns, See, each. Each association should have a 1 and a 2, And you're making them into columns.

So if.

If two is a column, it has to be in each. Each association.

Okay, so then explain to me what. What this should look like.

So that should be. I believe it should be two goes to Y.

Here.

Yes.

Why?

Well, how else is it going to be a column?

Okay, but then what. What about the one goes to Z?

That's. That's an additional row.

The.

The problem is that you didn't have enough data here.

And then. Then you get a two column. No, no.

Anybody say carlo, what additional data do I need here?

You need at least three columns because one column has to go into the rows and one has to go into the columns. The way I understand this is. I think about it in terms of a sparse array. That becomes a dense array. So if you want to make a 2D thing, you have to start with at least two index columns and one data column.

I see. Okay.

So if you call the column XYN value, then it's easier to see it mathematically.

All right. Okay, so let's try it. Okay, so what are you saying? You're saying table. I'm just going to make this at random of X, Y.

Values.

Random real, something like that.

You know what I'm going to do?

Should they be strings or Is that.

Okay, random word shouldn't matter.

Well, random word, they won't ever collide.

No, like you can. If you. If they never collide, then. Then you get a problem because this thing has a lot of holes.

But yeah, the pivot wider is not guaranteed to make a nice. Nice thing.

Table of this comma 10, let's say. Yeah, okay, there we have it. Okay. Now we're saying we want to pivot this into something which has an X column. Okay. So the goal here is to make an X column. So we're going to have. Basically for every entry, we're going to have an X arrow. I don't understand. No, no, no.

The values are 0 to 10, right? No, to 5. Okay, yeah, yeah, 0 to 5.

First you decide what you're going to make columns out of. Are you going to make columns out of?

Sorry, you sent me something?

Yeah, I sent you an email titled Label Array, which is the thing that made this click for me and understand what it was doing. Trying to generalize to a higher dimension and see what it did.

Yeah, we actually. Yeah, we did that in one of these meetings.

What are we doing here now? What am I looking at? What am I looking at here?

Yeah, maybe we should.

What am I supposed to look at?

Scroll after code and I'll explain. So this is in general, class and sex are taken as dimensions and I put labels on the axis and that thing in the cells is the mean age. And you can do it for three.

Sorry, I don't want to stop. Could somebody explain this to me? What does this become?

Right, well, are you making the values of X the column or values of y?

You can put the data in a tabular and play with the functions.

Yeah, that's the other thing you could do, we could just do tabular.

Tabular of percent.

Okay, now what?

Okay, and now you can do pivot wider.

Percent.

Okay, so which, which column do you want to. Which, which. Which set of integers do you want to create into column names? X or Y?

The X's. Okay. Both X's and Y's.

Well, let's just start with the X's.

Okay, let's do X.

Let's just start with the X. Okay. I mean, it should work.

Now, what can we do?

So brace for list, quote X. Oh.

It'S not quotes, actually.

Oh, okay. It's just X and then comma value. Because you want to take. So X is which columns you want to convert, which column you want to curt in the column and then put goes to automatic or maybe goes to extended key would be the best thing to put in.

What about a list or something?

Well, we'd set that up to be a function, but we need to get a basic understanding of what. Yeah, that should work.

I see the.

Oh.

Right.

So there are lots of missings.

Yep. And that's typical. If you just generate a table like that, there's gonna be lots of missings. If you.

Wait, so what happened to like Y here?

Okay, why. Why I. I think of Y is going along for the ride. So wherever there was a four for Y. Okay, you get.

You get a set four for Y and the four for X. You've got a value here.

Then why are there two ones on Y?

Yeah, because there were two, two, two values that had different values of X.

But how does it decide that the 3x goes with the. Not sure. I totally understand. There was a collision where there was. There was. There was a Y1X1 and another Y1X1. Right, right.

Yeah. So what. What. How did it know. Yeah. That what you're asking is how does the y1x1 go with that one as opposed to this y1x1 going with that?

Simple. Simple ordering. Original ordering in the table. Just like, I presume the first one group by.

Yeah, you can see that the 0.65 goes with the first one 1, whereas the 0.04 is the second one 1 in the table.

To.

To be clear, though, so the thing like the 0.8 and the 0.28.07. Right. You see those. Those could have just as easily gone in the row.

Right. But we always put it in the first row.

It's a convenience to put it in the first row.

Additional rows.

If, by the way. By the way, I do Want to say that in the documentation we absolutely should. Should have an example like this.

Yep. Oh, I agree.

Perhaps with random words for the values or something, so it's easier to tell what's going on.

The thing is, if you've, if you've, if you've got something and you do this operation with a key column, then you're not going to get those missings.

I understand. So what happens if I have both?

You're still gonna get missings. You might not.

You might, you might, you might. Okay, what happens if I also add the Y here as another thing that I want to use?

Well, I think you'd have to do. I don't know if this is actually support. You have to do list of X comma, y comma value.

Then you get nothing left.

Okay. So it's all, it's all converted into that.

But that's a useful thing to see.

So that's really like the sparse array input, basically, right?

That's one way to look at it, actually. Yes.

Wait a second. This here, what is that doing exactly? That's collecting the things for the column names. Is that correct?

Yeah, it includes the name of the. So the last thing in extended key there is the name of the column the values came from. In this case, it happens to be called value. Okay.

The.

The other ones are the ones that came from the columns you, you're creating.

Why isn't there an obvious default for that second argument? I mean, in other words.

Well, yeah, we have an automatic. And what it is is it uses extended key if there are.

Right, but. Okay, but you're naming. What you're doing here is you're naming the columns. That's the only thing this is doing, is naming columns. Correct.

Yep.

It's a really weird place. Put the column namer on the right hand side of that rule.

Wouldn't it make more sense to have extended key be fed the arguments in reverse order than that? Like you can't use extended key if all the right hand side.

It doesn't matter. Doesn't matter right now. Doesn't matter. Okay.

I think. Right. This challenges, I think Carlo's assertion that you're going from a sparse representation to a dense representation. Because the out of, out of 30 looks a lot more dense to me than out of 32.

Yeah. Okay. Okay. Okay.

Yeah, but you can, you can try it with value goes to automatic and you'll see what you get. It'll. It'll just leave out. Instead of extended key, you can use automatic.

Okay, and then, and then also Try with F. Just because I, I still don't totally understand.

I think F, it's going to make a whole Columbus to see it. Yeah, there you go.

Yeah, yeah.

Oh, but it says F of extended key.

See, F operates on. If you look at the, if you look at the ref page for it, you'll see what F actually operates. And, and Jose put some nice examples in here that show you how the, the F works.

You just use echo at star.

Using echo, exactly.

Yeah.

So the first argument of F goes F operates on the extended key of the first, the. The columns, and then the second argument is the name of the column for values. And it may not. I mean, there are other choices. This is the choice that just seemed the most sensible and easy.

Okay, okay, okay, wait, wait, wait, wait. Okay, so let's just try to understand. So the difference between this and the actual pivot is that an operation is done in addition to this rearrangement. Is that correct? The pivot table function, what does it do beyond what pivot wider is doing?

Oh, it does an aggregation. It does row, aggregate rows and then calls pivot wider on that result.

Is that exactly what it does?

Like that's exactly what it does.

Okay.

And in fact, the, the summary margins, you do. You basically do aggregation with the grouping being subsets of the groups you specify, and then you combine them together in a vertical thing before you do the pivot wider. So it truly, I mean, the, the basic operation of pivot table is just aggregate rows followed by a pivot wider.

Okay, so. So let's concentrate on those things and maybe we have a. Okay, okay. What a weird name. Pivot wider. Weird, weird, weird. I mean, I understand what it's doing from these, but it's just a weird name. Weird, incomprehensible name.

Well, Panda's name.

Taller. Right.

Well, Panda, the panda's name. The Python name is melt. So go figure.

There's something poetic, maybe.

I don't know.

I guess.

So what's the, what's the Python name for pivot taller?

I don't remember. It may just be pivot.

It's called freeze or something.

What does this do, roughly?

Okay, it does. It's sort of like the opposite operation. And this one, this one, you won't generate the missings in general. So what this is saying is I've got columns. Name. Monday, Tuesday, Wednesday, Thursday, Friday. Right. And when I take those columns and make it into data.

Yep, I get it.

There is an identity at the end.

I kind of feel like there might be a name here that's something like rows to columns.

But it's. It's kind of values.

I know. So row values.

Well, no, it's. It's. You see, instead of column values, it's column keys to run.

Yes, exactly.

Is it? Yeah. I thought it was the values within those columns.

No.

Yes, it is. Oh, it isn't. Oh, it's column keys to rows.

The keys that get put into the. Into rows. Yeah.

What do we think of these names? If somebody saw that in a piece of code, they might actually understand.

Yeah, I think it, I think. I think it's. It's, you know, they're semi. Understandable.

Definitely semi.

Well, you get some sense of what might be going on.

I mean, I get that there's things with rows and columns and keys, but I don't really know exactly what.

Right, well, let's see what the argument structure would be. Okay, so row values to column. So we're now saying. What row values. We've got certain row values. Certain rows. No, we've got the values within the values. A column that has those particular. So X and Y here.

Yeah.

That specifies which values. I mean, we could as well. And I don't know whether this is a good idea. It could be a function here. X comma, hash. Yes. Because each one of those. Ultimately. I mean, this could be X +3 or something. We wanted to. Am I making sense?

Sure.

All it's got to do is to take columns, take elements of a row. And. But, but doesn't each one of these have to become.

Well, see, this is. This is where it gets confusing because one thing, one thing is taken. Taken to be the column names or like, they're like the variables. Those become variables. You're converting them to variables. And the other are values that. That get associated with those column names.

Okay, Wait a minute. This thing has values. There's values of X, basically.

Right. They're values of X and. And the unique values of X become column names or column keys.

Right. Values of X become column keys. But in this case. Wait a second. In this case, the column keys contain both X and Y and default is extended. Some kind of extended key or something of xy. Correct. Is that correct? Is that correct?

It's the value of X and the name of Y.

The name of Y. Yeah.

No, no, no, I'm talking about if, I'm saying, if I'm using both X and Y, do I have to put another list in here?

Yeah, you have to put another list.

Okay. And the other case, if I just have a single one here. What, what is the second thing here?

This is the second thing where the values come from. So, so like, you know, in my example, I took the temperatures. The temperatures were associated with the days.

But isn't it like that? Shouldn't it be like that?

No, because you have to give. You have to give names for the. You have to give. Yeah. For row values to columns. That might be a possible. A workable solution.

Right. There's another function you have to give in the pivot thing, where you're also giving the aggregation function. Correct.

That's the pivot table.

Yeah, I know, I know. So. So this syntax would work for row values to columns. And I. I think that's right.

Yes, that would work for row values to columns.

Now, is this thing here, Is that always a single thing there, right hand side? Let me look at this.

No, it can be. It can be multiple things.

And then what happens?

You may want to. You might want to do it for more than just a single column to associate with the.

Well, then what happens? What does it do?

Well, I think we have an example in the notebook.

Yeah, I don't want. I want to see what it does.

Okay, well, what you do is you get 4, comma, value 1, 4, comma value 2 for the keys.

Okay, so hold on, let's do value one is random word, Value 2. I see Carlo's suggestion of values to colon keys, column keys to values.

That's interesting because the rows are like. The values are always in the rows in general.

Okay.

Yeah.

Right, so here we go. Right, so this. Now what I want to do with this, I want to make that into tabular, Right?

Yeah.

And now what do I do with that?

Well, you do pivot. Pivot wider percent. Yeah. And then you want to do X and you. So then do list and then another list X and Y. And I don't think they're quotes, since you didn't use quotes.

Yeah, X. Go ahead. What were you saying now?

Yeah, so you need another list.

What do you mean another list?

So.

Oh, you mean you want me to put X, Y like this?

And then another list of value 1, comma, value 2.

That's.

That's the way we have the input set up now.

Okay, so let's look at this one. First value one, like that.

Goes to automatic.

Oh, it has to. Goes to automatic or goes to extended key. And that's going to be. That's going to be almost all missings because the things you need are the value columns.

If I do this here, I get a lot more collisions yeah. Some more collisions. Yeah. Okay.

Okay, now. Now try the value one, value two in a list.

Oh, my gosh.

I. I think the order in extended key is reversed.

Really? Okay, hold on, hold on. What the heck is this? Because value one never appears.

Well, no, you're not doing it on. You're. You're doing it on the table above, where value one does appear.

I know, I use percent 40. Right, right. But my point is in this output, right? I never see value one, but now magically, here, I see value one.

Why change automatic.

Change automatic to extended key. And then Jose and I discussed this this morning. We realized the automatic probably wasn't right.

Right. Okay, that looks better there.

Yeah, I think that's a better default to have, actually.

Okay. All right. And one of the reasons you want to package this together with rules is, is so that it has a. It has an operator form. Is that correct? Is that correct?

I honestly don't remember why we decided to do rules at the beginning.

All right, okay, so then let's imagine that this was called. Let's say it was called values to columns, Which, by the way, would make some sense. And then the other way around will be columns to values. Yeah, yeah, I think this is somewhat comprehensible. I mean, so then values to columns. I mean, if it didn't need an operator form.

I don't think we need an operator form for this because typically this is something you're going to do at the end of computation to look at the data.

Well, not necessarily. I think that column to values is something that you might do if your data is not, well, normalized. Like it's values to columns. That is terminal, not the ioa.

Fair enough, Carla. Yeah, but what do you. Yeah, so. So that works. That works as a.

I don't understand. I mean, we can do operator forms with two or three arguments that none of these are, are.

No, I understand, I understand, but we. We need to. Okay, hold on. So what we're saying here, That arrow isn't right. It's more like. Do we have to specify these?

Yes.

Why?

Well, okay, so look at. Look at your case where you just gave value one. We keep value two as a column. So all the ones that aren't specified as values to go in the new columns are kept as columns from the original table.

So this really is the. Is the dense array part of the storage.

Yes.

Exactly.

I. I mean, I take. Carlos. Carlos.

Idea of Maybe the right way to call this is something that says this is. Maybe it's not quite values to columns. It's something slightly different. So this, this here. And. And the. And the other argument is about column naming. Column namer. That's what it's. That's what that function is doing. Right? It's a column name, a function.

Yep, yep. And that could be. That could actually be an option if we.

Yeah, I would think so.

We. If we design it. Right. If we. I think.

Okay. Okay, so let's just understand what this. Okay, so what is this really doing? It's taking certain. Values. And making them keys for the part of the array. Dang. What is this? You know, because what you're eventually going to do is you're going to have an array whose appearance is.

I think you're going in the good direction. So that specifies the new columns. And now those columns have to have some values. And where do we get those values from? That's given by the values one.

Yeah.

Right. So how does this relate to construct columns? Because that's. Again, you know, construct columns has a table and then it has a column name. But these are existing columns. Right. We're recycling their existing row values, which were recycling.

Yep, yep. Those could very well be functions for construct. You know, for construct columns, too.

Yes, I understand. I mean, this is really. It's construct columns from row values. It's kind of weird. Let me think here. Okay, so.

But I think that the key difference between construct columns and this is that the column keys come from the data.

I know, I know.

Yeah. And construct columns.

Wait, I know, I got it. As various people are pointing out, these could be functions of the data.

Yeah, but construct columns always uses row values, but they go into other row values.

I know, I know, I know, I know.

Yeah.

I know. We have to. The graphic design stuff. One more. Couple more minutes on this. We're almost there. I mean, I assume. Construct columns from values. Do you think that's correct?

I think it's. I think it's confusing things with our actual construct columns. It implies that they're similar in some way and they're.

Well, they are. Because they're going to construct new columns.

Not really. Because what goes in the columns already exist. And construct columns actually constructs new values. This one doesn't.

Okay, fair enough.

It's just rearranging.

It's rearranging versus constructing.

That's why I propose column keys from values. Because the only thing that you're constructing is the keys. The values exist already.

Well, it wasn't what you proposed, but that might be better. Column keys from values.

What do you mean?

Sorry, What I thought. No, you said values to column keys.

Yeah, well, to or from. But.

Okay, but it makes a difference, right? Let's see. Arrange by column, by. It's not really column keys, it's really columns. You are constructing columns from values, right? Yeah. Okay, let's see what we. How we would describe this would be constructs. Wait a minute. What's the thing that. I see? Transforms a tabular. Constructing. Transform a tabular using the values of. Elements x and y as. Make. Making columns from all distinct values of x and y. Making new columns. Or making columns. Making new columns because it leaves the existing. That one there. Right. Making new columns. From all distinct values of X and Y.

Right, but. But where. Where do those. Where do the elements in those new columns come from?

Right. Making new columns from all distinct values. And getting the values. And we could have two things here. We could say values one, values two. Right. That's what we did. What's the description of what we get here? This is a thing with.

What's basically keys. XY duplicates.

No, no, I know that. I know that. But it's basically key. Looks like we have lots of people leaving. That's a shame. Here, I also. I also have to go. But, but. All right, let's take just a. Okay, we're close on this. I think columns from values is the least ridiculous that we've had. And the default is extended key. And the column name is sort of a detail.

Yeah, I think it's an option.

Fine. Yeah.

But please, please reverse the order. Otherwise it would be impossible to use.

I'm sorry, reverse the order of what?

Like, value zero one should be the other way around. So that value gets nice and grouped on top and it's like.

What are you talking about? Wait a minute.

I think he's talking about the orientation.

Extended key. The arguments go in an extended key in a certain way. Right.

Okay. Okay. Okay.

Value one is the least interesting thing there because it's always the same. So it should go first, not last. It's the directory that contains everything.

Yeah, I can do. I can do that for you.

Can we just take a moment here and. I'm sorry, we're looking. Can. Can I get a. A table of what the. What the graphical appearances are?

Okay, I'll. I'll draw for that.

Bye. Tim, do you have that? What's that? Yeah, let me go ahead and share.

I can share.

Oh, really? Send it to me, please. Please.

Okay.

Email is fine.

While we're waiting for that, I just wanted to point out that, you know, the Reason map is comprehensible is that, you know, Lisp invented that name 70 years ago or however long and, you know, we've all gotten used to it. I don't know if something like this has existed in the database world, but.

But, well, pivot is the, is a closer to what it is. Honestly, I've, you know, I've certainly heard of, of pivot tables. Okay, you should have it.

There are also linear algebra pivot tables, which are quite different.

What are those? Oh yeah, yeah, right. Pivoting. Oh yeah, that's confused the hell out of me.

Yeah, has nothing to do with that. This was invented by Lotus Corp in the late 80s.

Right. Okay. The, the top is actual shapes. The bottom, bottom row is this the, the correct size and color. So this is what we're talking about. In every row, in every column, we're indicating what kind of a thing it is, right? Correct. Now, and this is where we agreed to put it in the top, right? Is that correct?

Yes.

And is this an iteration of what we saw before? Is this the. I mean, these are too big. And my opinion, right, we don't want a cartoon sized thing. Am I making sense?

Well, I mean, at some point it becomes too small to be distinguishable.

I think that's just 10 points high. But you're viewing 150% too. I think those could get smaller. The others have some detail that you don't want to get rid of. Those are way too big. Yeah, Well, I think the iconography is wrong if, if, you know, I think, You know, get rid of the one here, for example.

Well, how do you distinguish the integer from the real.

By putting a period.

So just plus, minus, period.

I think so. Look, the point is this, the point is this. People will get used to these fairly quickly. I mean, you're already seeing the data, you already have a sense of what it is. Right. The only thing you're doing is you're checking up there, you're taking a small gander to find out if it's a string. This. It looks like a number. It talks, you know, walks like a number. But is it actually a number? So I'm saying that's the primary use, is it not?

That is a key use. Yes. Same thing with other things.

Like, and I worry that the plus minus actually looks a little bit like sorting, doesn't it? Isn't that the sorting icon for up, up, sort up, sort down or. No, those are arrows.

No, usually those are arrows.

Okay, so I mean, let's again, let's review what it is one's using this for it is to confirm one's suspicion about what this column actually is. How common is that really going to be? I mean, are we sure we have to display this and it can't just be a mouse over? Why? Why? I mean, most of the time it's just plainly obvious.

No, I don't think that's at all true. When you've imported something, I don't think it's at all obvious. Is that.

Okay, okay, let's talk about that. That. Let's talk about that case. Let's talk about that case.

Strings versus numbers is very important.

Yeah.

And let me make a suggestion.

Strings versus categories are very important. You can almost do nothing with strings.

Okay. Okay. So the point really, this is an import story. You want to know when you imported. You actually want a pretty big indication of what the thing is. You see what I'm saying? In fact, the more subtle, the worse it is. But later on, the barest hint is sufficient. So let me, let me throw out a possibility. Okay? I. Not. This is not properly thought through. Say that on import, I'm thinking about some kind of raft or one of those blue, you know, progress indicator type things. Here's a possibility. Maybe it's crazy, we don't have anybody from the front end team here who can address this, but let's imagine that what came in from the import is something with a blue, you know, cap on it header at the top with an X that lets you get rid of it. And with the feature that on evaluation it just goes away. It's like, it's like that when I, when I color a, you know, a variable green, it goes away on evaluation, similar to various kinds of summary box type type stuff. And that it is a specific thing you get from an importer because I really think that case is important. Right? And once you're in action, it's just annoying. So is there any other way we can distinguish the, the, the delivery of an import relative to just a computation?

Right. I mean, right. The importer would have to know to somehow specially mark the. Yeah, the end. Even then it's not clear to me how this would auto clear itself. Unless, unless you're talking about, you have to manually click and that changes.

Right?

But that could only change what's.

No, no, here's what I'm suggesting. Here's what I'm suggesting. Okay. The importer somehow, through some magic as yet unknown to me, okay, It's a, It's a same kind of magic. Let me give you an Example of a piece of magic. Let's say defer. Okay, I've got that and now I evaluate that and I get four. Yep. Okay, right.

But the point is that you had to copy and paste that.

Not that I didn't copy and paste. I didn't. I just did a shift to.

Well, you did. It doesn't.

That's the same. Yeah, okay, fine, fine, fine, fine, fine.

1045 would not give you four.

Right, I agree, I agree, I agree.

So some sort of thinking of some kind, some sort of front endy thing could change, you know, the output that you could then, you know, copy and paste, but it wouldn't change if you did. X equals blah. That wouldn't change. X.

No, X equals. Would not see the blue raftiness.

But what if you do X equals import of blah, blah, blah.

Yeah, I understand that there's a problem there. I mean it has to not see it. I.

This sounds completely insane to me.

Let's just think it through for a second, okay, Because I want to address what we're actually.

Why don't we postpone it to a time when we actually have all of the relevant folk.

Okay. All right. We should have the front end team people here as well. Because I think it's going to be a front end play. Because I just want to. I just want to review. To my mind, the goal here is make it in your face when it's been imported and when it's in the middle of computation, it's at best to hint. Do you agree with that objective? I don't know how to achieve the objective. But do you agree with the objective? Is there any other case?

Basically, Right, Stephen. I mean, I think import is the most important place to understand about.

But that assumes that like a single working session or a single individual by the time you're sharing or you know, deploying, saving and giving to somebody else. Good point.

I was going to say I'm also.

If I've changed, if I have another.

Tabular, if I've changed the types and.

They look completely identical, how do I know which one has a string and.

Which one has a categorical type?

Right. It's actually important to know it at transform columns time or aggregate rows. What?

No, because. No, wait a minute, Rob, Rob, the main point here is most of the time it's dead obvious.

That's what we disagree with.

Well, the only number and string, but. But not category. Let's say which looks like a string.

Yeah.

What about since strings the problem, why not just mark strings and then everything else you should be able to see. That's a good point. There's a good.

Although that you wouldn't necessarily be able to distinguish a string from an inert expression.

Oh, yeah, but that's a. I agree. That's. That's a. Strings and inert expressions. Maybe, but that's a good observation. I mean, a date. You just don't need to see that it's a. You know, by the time it's a. Well, okay, it could. If it is, it's either a string or it's an actual date. See what I'm saying?

Yes.

Yep. It's important to know when you're computing on those.

Stephen's point is that if you mark strings explicitly and inert expressions explicitly, all the other types are just easily distinguishable by looking at the data.

Right. Because you. Because what else could it be? Tim is absolutely right. We just need to mark. That's a. I think that's the right answer.

Well, that's the right answer. And especially if we put the units for quantities as a part of the header, then quantities are totally clear as well. Yes, that's actually very sensible.

Okay, so all we need is an icon for strings and for inert expressions and byte array.

Well, I guess. I don't know. What, what, what, what. What you see in the data column when it's a byte array.

Yeah. What do you see? You're going to see something that says byte array all over it, I think. Because you're not going to see hex. If you do see hex, then you know it's a byte array.

Yeah.

We could, we could just display byte array values as, as an abbreviated hex or something.

That's what I think we should do. And it's totally obvious it's a byte array. Okay. So let's just make up an icon for the string. This is great. I would have thought that strings. Okay. I'm going to speculate. Maybe double quotes inside a circle or a single double quote inside a circle. Is that for some reason, I think it needs to be sort of a badge like thing and not something where it feels like. Needs to be something where it is a. It's a clear badge rather than something that thinks it's going to take action or something.

Right.

And I'm. I wonder, I mean, why do we have the exp and whatever those other corners are and not just like a lowercase e? Like we use everywhere for expression.

That's fine.

Yep. Yeah. But, but okay, but I mean, maybe what we do is if we could do it either in a circle or put up in a. In a kind of a square.

Right, right. No, no, that's what I'm saying. That the content of the badge could be just an E or a double.

Yeah, yeah, yeah.

So, I mean, this becomes. Then. Probably crazy. Yeah.

You probably want to set the font size or something, right? Like 72 or something.

No, I was just doing that.

Yeah.

It's gonna be bigger if you put a circle around it. I understand, but I think I prefer to have.

Then you want the third argument of text to actually center it. Right. It's putting the baseline at zero.

Yeah, yeah, yeah, yeah. So what about a dark circle? And then the quotes are white. They drop out of it. Possibly. I. It doesn't want to be that strong. I mean, I think it. It just needs to be. And then it has a mouse over that says it's a string. Okay. Do we mouse over the other types as well? Just over the whole item box? Yeah. No. Why not? I mean, and then, I mean, the question is. It needs to not look like it's an error, but it needs to. It needs to look like it's a badge, but not an error and also not a clickable thing. Well, I guess we could mouse over it so it doesn't harm that. All right, we're going to get something here. That's a great answer. Well done. Thank you particularly, Tim, for that comment. That was very useful. Sorry, your icons bite the dust, but we can use it somewhere else. Yes, yes, I'm sure we can. Okay. Anything else for this meeting, guys? Oh, I have some comments here on our live stream, Ethan, commenting that the summary box for information should include. Oh, I see. That the summary box for tabular should include missing counts, sizes, mins, maxes. Well, certainly the information could include that. Okay, anything else, guys? All right, thanks very much. See you all soon. Bye.