# How to create a GroupBy function that is better aligned with relational algebra?

I want a GroupBy function that behaves more in alignment with relational algebra principles. I can get what I want if I do it in 2 steps:

Preparation:

data = {<|"A" -> 1, "B" -> 2, "C" -> 10|>, <|"A" -> 1, "B" -> 2,
"C" -> 20|>, <|"A" -> 1, "B" -> 3, "C" -> 30|>, <|"A" -> 2,
"B" -> 4, "C" -> 40|>, <|"A" -> 2, "B" -> 4, "C" -> 50|>};

ds = Dataset[data];


Step 1:

groupedData = ds[GroupBy[{#A, #B} &]];


Step 2:

nestedRelationDataset =
Dataset@
KeyValueMap[
Function[{key, value}, <|"A" -> key[[1]], "B" -> key[[2]],
"Nested" -> value|>], groupedData]


Output:

Dataset[{<|A->1,B->2,Nested->{<|A->1,B->2,C->10|>,<|A->1,B->2,C->20|>}|>,<|A->1,B->3,Nested->{<|A->1,B->3,C->30|>}|>,<|A->2,B->4,Nested->{<|A->2,B->4,C->40|>,<|A->2,B->4,C->50|>}|>}]


But when I try to combine them, result is nonsensical:

relationalGroupBy[ds_Dataset, groupCols_List] :=
Module[{groupedData, nestedRelationDataset},
groupedData = ds[GroupBy[groupCols]];
nestedRelationDataset =
Dataset@
KeyValueMap[
Function[{key, value},
Association[groupCols -> key, "Nested" -> value]],
groupedData];
nestedRelationDataset]


Single Call:

relationalGroupBy[ds, {"A", "B"}]


Output:

Dataset[{<|{A,B}->A(<|A->1,B->2,C->10|>),Nested-><|B(<|A->1,B->2,C->10|>)->{<|A->1,B->2,C->10|>}|>|>,<|{A,B}->A(<|A->1,B->2,C->20|>),Nested-><|B(<|A->1,B->2,C->20|>)->{<|A->1,B->2,C->20|>}|>|>,<|{A,B}->A(<|A->1,B->3,C->30|>),Nested-><|B(<|A->1,B->3,C->30|>)->{<|A->1,B->3,C->30|>}|>|>,<|{A,B}->A(<|A->2,B->4,C->40|>),Nested-><|B(<|A->2,B->4,C->40|>)->{<|A->2,B->4,C->40|>}|>|>,<|{A,B}->A(<|A->2,B->4,C->50|>),Nested-><|B(<|A->2,B->4,C->50|>)->{<|A->2,B->4,C->50|>}|>|>}]


What am I doing wrong?

Update. This:

relationalGroupBy[ds_Dataset, grouper_Function, groupCols_List] :=
Module[{groupedData, nestedRelationDataset},
groupedData = ds[GroupBy[grouper]];
nestedRelationDataset =
Dataset@
KeyValueMap[
Function[{key, value},
Association[groupCols -> key, "Nested" -> value]],
groupedData];
nestedRelationDataset]


with this:

relationalGroupBy[ds, {#A, #B} &, {"A", "B"}]


fails too:

Dataset[{<|{A,B}->{1,2},Nested->{<|A->1,B->2,C->10|>,<|A->1,B->2,C->20|>}|>,<|{A,B}->{1,3},Nested->{<|A->1,B->3,C->30|>}|>,<|{A,B}->{2,4},Nested->{<|A->2,B->4,C->40|>,<|A->2,B->4,C->50|>}|>}]

• look at ds[GroupBy[{"A", "B"}]] Commented Mar 21, 2023 at 17:51
• Thanks for commenting @lericr, but I have no idea what you mean. Commented Mar 21, 2023 at 17:55
• What I mean is that ds[GroupBy[{"A", "B"}]] gives a very different result than ds[GroupBy[{#A, #B} &]]. In your step-by-step approach you had groupedData = ds[GroupBy[{#A, #B} &]], but in your single call you had relationalGroupBy[ds, {"A", "B"}]. In relationalGroupBy you have groupedData = ds[GroupBy[groupCols]], so that groupCols will be the string version, not the function version. Commented Mar 21, 2023 at 18:10
• I see, indeed, not the same, but I cannot find a way to make it work... Commented Mar 21, 2023 at 21:09
• I guess I'm struggling with understanding what you want to do with this structure. What you've said is your desired result looks awkward to me. Nevertheless, one thing you might try is creating an aggregate key. Something like this: ds[All, <|"Key" -> <|"A" -> #A, "B" -> #B|>, "Val" -> #|> &]. Then you can do your grouping with GroupBy["Key"] Commented Mar 21, 2023 at 22:47

You can use RightComposition. Well, you can also use Composition but I prefer the former for Query which is what you are using in Dataset.

Your set of functions can work with a few tweaks.

ClearAll[relationalGroupBy]
relationalGroupBy[ds_Dataset, groupCols_List] :=
ds[
GroupBy[Evaluate[Slot /@ groupCols] &]/*
KeyValueMap[{Sequence @@ Thread[groupCols -> #], "Nested" -> #2} &]/*
Map[Association]
]


Then with ds as in OP.

relationalGroupBy[ds, {"A", "B"}] // Normal

{ <|"A" -> 1, "B" -> 2,
"Nested" -> {<|"A" -> 1, "B" -> 2, "C" -> 10|>, <|"A" -> 1, "B" -> 2, "C" -> 20|>}|>
,<|"A" -> 1, "B" -> 3,
"Nested" -> {<|"A" -> 1, "B" -> 3, "C" -> 30|>}|>
,<|"A" -> 2, "B" -> 4,
"Nested" -> {<|"A" -> 2, "B" -> 4, "C" -> 40|>, <|"A" -> 2, "B" -> 4, "C" -> 50|>}|> }


I used Postfix and Normal to unpack the Dataset for the post.

Hope this helps.

• Great! Thanks! I hope one day i will understand what you did Commented Mar 22, 2023 at 2:02

This is probably a bit self-serving, but based on the comments I think there are some relevant alternatives to explore. The OP, in a comment, linked to a grouping operator described by C.J. Date. However, Date's grouping operator doesn't repeat all of the original columns within the relations that become the values in the new group column. I think this is a reasonable semantic, because it allows futher "natural" operations on these relational values that the inclusion of the original columns would impede. In effect, we're picking a key for the new relation, and having that key be duplicated in the grouped value (a relation itself) would usually constitute a functional dependency (I mean, maybe the semantic context would make this okay, but I think in normal circumstances this would end up being denormalized).

With that preamble, here is an implementation that I think is closer to the spirit of Date's grouping operator:

groupRelationally[ds_Dataset, cols_List] :=
With[
{keyGroup = Thread[cols -> (Slot /@ cols)]},
ds[All, <|keyGroup -> {"Group" -> KeyDrop[#, cols]}|> &][Merge[Identity]][KeyValueMap[<|#1, Merge[Identity][#2]|> &]]]


Someone with more proficienty with Dataset might be able to simplify this. Also, "Group" is a bit lazy, but I didn't want to obfuscate the code just to create a better column name. Anyawy, let's compare this with the OP's original expectation:

original expectation

relationalGroupBy[ds, {"A", "B"}]


variant aligned with Date's grouping operator

groupRelationally[ds, {"A", "B"}]


• Nice! Thank! One thing I realized, however, after re-reading those C.J. Date chapters is that there are 2 operators Group and GroupBy (the latter being the one available in SQL). In GroupBy you specify the columns that will not be nested. In Group, you specificy the columns to nest. (So, to be fully Date operator compliant, you either have to change the name of your function to "groupByRelationally", on reverse its behavior) Commented Mar 23, 2023 at 12:58
• lol. That's how I did it originally (the reverse behavior), and then I decided to change it when I posted so that it matched the signature of your method. But yes, you are right. Commented Mar 23, 2023 at 14:07