Reshaping associations, generalization of GroupBy

Question

Let's say we have a set a\of associations:

dataset = { <|"type" -> "a", "subtype" -> "I", "value" -> 1|>, <|"type" -> "a", "subtype" -> "II", "value" -> 2|>, <|"type" -> "b", "subtype" -> "I", "value" -> 1|>, <|"type" -> "b", "subtype" -> "II", "value" -> 2|> }

where every entry is unique in terms of {#type, #subtype},

I'd like to build a nested association for more handy querying, e.g. I would like to have:

nested["a", "II", "value"]

I can start with

GroupBy[dataset, {#type &, #subtype &}]

<| "a" -> <| "I" -> {<|"type" -> "a", "subtype" -> "I", "value" -> 1|>}, "II" -> {<|"type" -> "a", "subtype" -> "II", "value" -> 2|>}|>, "b" -> <| "I" -> {<|"type" -> "b", "subtype" -> "I", "value" -> 3|>}, "II" -> {<|"type" -> "b", "subtype" -> "II", "value" -> 4|>} |>|>

But nested["a", "I"] points to a list with one association, what is expected but I would like to drop that list.

It seems that the third argument of GroupBy isn't generalized to handle nested grouping...

So basically I would like to have ... "I" -> <|"type" -> "a", ....

What is a generic way to go?

I can do:

nested GroupBy:

GroupBy[dataset, #type &, GroupBy[#, #subtype &, First] &]

Map later:

GroupBy[dataset, {#type &, #subtype &}] // Map[First, #, {-3}] &

But the first is not handy in general while the second is ugly (and not general either).

Acceptable outputs are:

<| "a" -> <| "I" -> <|"type" -> "a", "subtype" -> "I", "value" -> 1|>, ... |>

or

<| "a" -> <| "I" -> <|"value" -> 1|>, ... |>

or

<| "a" -> <| "I" -> 1 , ...|>

but the first is the most desired one because we may have more that one ("value") key left.

What about something like GroupBy[ dataset, {#type &, #subtype &}, Apply[ Association, #, 1 ] & ]? — gwr
– gwr, Commented Feb 15, 2016 at 13:09
@gwr unfortunately, it is not general enough. We may have more levels, you can mimic it with: GroupBy[dataset, {#type &, #subtype &, #type &, #subtype &}], now you would have to adjust levelspec of Apply, and using negative levels may be problematic because the input may be more complex than a list of 1-lvl associations. — Kuba
– Kuba, Commented Feb 15, 2016 at 13:12
Did you note GroupBy[ dataset, {#type, #subtype}& ] which leads to a more controlled levelspec (this is also robust for your mimic extended case)? Maybe nested[ {"a", "I" }, "value"] is an option? — gwr
– gwr, Commented Feb 15, 2016 at 13:44
@gwr I didn't, this is quite nice. The "full nested" approach is "better" in that sense that you can still take nested["a"] or nested[[All, "I"]]. — Kuba
– Kuba, Commented Feb 15, 2016 at 13:52
Why is GroupBy[dataset, {#type &, #subtype &}, Map[First]] not suitable? — J. M.'s missing motivation
– J. M.'s missing motivation, Commented Feb 15, 2016 at 14:51

WReach · Accepted Answer · 2016-02-15 18:01:51Z

One approach is to employ a helper function that unwraps singleton lists:

{delist[v_]} ^:= v

With this, the GroupBy expression is fairly succinct:

dataset // GroupBy[{#type&, #subtype& -> delist}] (* <| "a" -> <| "I" -> <|"type" -> "a", "subtype" -> "I", "value" -> 1|> , "II" -> <|"type" -> "a", "subtype" -> "II", "value" -> 2|> |> , "b" -> <| "I" -> <|"type" -> "b", "subtype" -> "I", "value" -> 1|> , "II" -> <|"type" -> "b", "subtype" -> "II", "value" -> 2|> |> |> *)

This generalizes to deeper nesting:

dataset // GroupBy[{#type&, #subtype&, #type&, #subtype& -> delist}] (* <| "a" -> <| "I" -> <|"a" -> <|"I" -> <|"type" -> "a", "subtype" -> "I", "value" -> 1|>|>|> , "II" -> <|"a" -> <|"II" -> <|"type" -> "a", "subtype" -> "II", "value" -> 2|>|>|> |> , "b" -> <| "I" -> <|"b" -> <|"I" -> <|"type" -> "b", "subtype" -> "I", "value" -> 1|>|>|> , "II" -> <|"b" -> <|"II" -> <|"type" -> "b", "subtype" -> "II", "value" -> 2|>|>|> |> |> *)

+1. A very nice appraoch that does beat applying Association at the right level. — gwr
– gwr, Commented Feb 15, 2016 at 18:58
@WReach, Another way to delist singletons (R.M. 2012) list /. [x] :> x mathematica.stackexchange.com/questions/8790/… — alancalvitti
– alancalvitti, Commented Mar 26, 2017 at 18:37

Edmund · Accepted Answer · 2016-02-15 17:32:52Z

Instead of a nested association solution, would Query and Select be acceptable.

Query[Select[#type == "a" && #subtype == "I" &], "value"]@dataset (* {1} *)

This form is more descriptive on what is happening and does not require reshaping of the list of associations.

If your data is such that there is only ever one item intersecting a particular "type" and "subtype" then tack on First.

First@Query[Select[#type == "a" && #subtype == "I" &], "value"]@ dataset (* 1 *)

Hope this helps.

Extension

You can extend this to a more general case in which you parametrise the filter by both key and value.

filterBy[filter_] := Function[Evaluate[And @@ ReleaseHold[Hold[Slot][First@#] == Last@# & /@ filter]]]

then with

target = {{"type", "a"}, {"subtype", "I"}}; Query[SelectFirst[filterBy[target]], "value"]@dataset (* 1 *)

This is a solution, but I really don't like #type == "a" such statements. The main point is the have compact and flexible solution. So once I have got my nested, it's easy to do many things with this. — Kuba
– Kuba, Commented Feb 15, 2016 at 13:55
@Kuba You can also go with #["type"] == "a" && #["subtype"] == "I" &. This gives additional flexibility as you can parametrise the selection by both key and value. For example f[filterBy_] := ReleaseHold[Hold[Slot][First@#] == Last@# & /@ filterBy] then And @@ f[{{"type", "a"}, {"subtype", "I"}}] in the Select. — Edmund
– Edmund, Commented Feb 15, 2016 at 14:40

Community · Accepted Answer · 2017-04-13 12:55:52Z

I have posted code doing a very similar thing here - the functions pushUp and pushUpNested. That code was more general, since there I provided a declarative interface to group by values or their parts. To do what you need, I'll redefine slightly (assuming you run that code):

ClearAll[pushUpNested]; pushUpNested[{}, elemF_: Identity] := elemF; pushUpNested[specs : {_List ..}, elemF_: Identity ] := Composition[ Map[pushUpNested[Rest[specs], elemF]], pushUp@First[specs] ];

Now we create a transform:

transform = pushUpNested[{{"type"}, {"subtype"}}, First] (* Map[Map[First]@*GroupBy[#1[[Sequence[Key["subtype"]]]] &]]@* GroupBy[#1[[Sequence[Key["type"]]]] &] *)

which we can now apply to get the nested structure:

nested = transform@dataset (* <| "a" -> <| "I" -> <|"type" -> "a", "subtype" -> "I", "value" -> 1|>, "II" -> <|"type" -> "a", "subtype" -> "II", "value" -> 2|> |>, "b" -> <| "I" -> <|"type" -> "b", "subtype" -> "I", "value" -> 1|>, "II" -> <|"type" -> "b", "subtype" -> "II", "value" -> 2|> |> |> *)

The advantage of using pushUpNested is that it makes it very easy and declarative to construct such transforms, and the transform is available for inspection as a stand-alone fully-prepared function.

pushUp is needed too. Thanks, I've read your answer before, I just somehow forgot pushUpNested because I was able to do what I needed without additional functions. Now they seem inevitable. — Kuba
– Kuba, Commented Feb 15, 2016 at 14:14
@Kuba Yes, pushUp is needed, which is why I mentioned that I assumed you first run that code. I did use a (slightly more complex) version of pushUpNested for a real db with hundreds of thousands of entries, and it was a snap. — Leonid Shifrin
– Leonid Shifrin, Commented Feb 15, 2016 at 14:16
Is that intentional or is GroupBy not just finished? Because I have the latter impression. It allows nested grouping, yet the third argument can't handle it. It is so close to give me what I need :) — Kuba
– Kuba, Commented Feb 15, 2016 at 14:22
@Kuba I can't say for sure. You might be right. I will ask around when I get a chance. — Leonid Shifrin
– Leonid Shifrin, Commented Feb 15, 2016 at 14:26

Community · Accepted Answer · 2017-04-13 12:56:03Z

I believed this question to be a duplicate of Create Nested List from tabular data and proposed, with minor variation, the same answer:

fn[x_List] := GroupBy[x, First -> Rest, fn] fn[{a_}] := a nested = fn[dataset]

<|"a" -> <|"I" -> <|"value" -> 1|>, "II" -> <|"value" -> 2|>|>, "b" -> <|"I" -> <|"value" -> 1|>, "II" -> <|"value" -> 2|>|>|>

nested["a", "II", "value"]

WReach suggested a different reading of this question however. To that end I propose:

ClearAll[fn] fn[p_, r___][x_List] := GroupBy[x, Lookup[p] -> KeyDrop[p], fn[r]] fn[][{x_}] := x nested = dataset // fn["type", "subtype"]

<|"a" -> <|"I" -> <|"value" -> 1|>, "II" -> <|"value" -> 2|>|>, "b" -> <|"I" -> <|"value" -> 1|>, "II" -> <|"value" -> 2|>|>|>

nested["a", "II", "value"]

I think that the associations in the source data introduce complexities that differentiate this question from the former one. For example, care must be taken to deal with groups with single entries (e.g. try changing the type of the first entry from "a" to "z"). Also, I got the impression that it was important to be able to specify the grouping keys explicitly rather than implicitly assuming that the data is to be grouped by the two left-most keys appearing in a fixed order in every entry. Neither of these issues arise in the other question. — WReach
– WReach, Commented Feb 17, 2016 at 1:01
@WReach Does my update address your reading of this question? — Mr.Wizard
– Mr.Wizard, Commented Feb 17, 2016 at 3:37

gwr · Accepted Answer · 2016-02-16 15:00:18Z

Here is the approach I would take to transform your dataset to a nested Association:

Clear[ makeNested ]; makeNested[ assoc_, keylist_] := GroupBy[ assoc, keylist ] // Apply[ Association, #, { Length @ keylist } ] &

Now makeNested[ dataset, { #type &, #subtype &} ] and

makeNested[ dataset, { #type &, #subtype &, #type &, #subtype & } ] will work as wanted.

For example:

(nested = makeNested[ dataset, { #type &, #subtype & } ]) // Dataset

nested["a","I","value"]

1

Update: A more streamlined solution

Inspired by WReach's solution I tried to streamline this into a compact function that also get's rid of the keys that have already been used in the nesting. So here it is:

Clear[ formatLeaves ]; Options[ formatLeaves ] = { "DropKeys" -> None }; formatLeaves/: List[ formatLeaves[ data_ ] ] := Module[ { keys = OptionValue[ formatLeaves, "DropKeys" ] }, KeyDrop[ data, keys ] ] Clear[ makeNested ]; makeNested[ data_ , keylist_?(VectorQ[ #, StringQ ]&) ] := Module[ { listKeys = Map[Key] @ keylist, lastKey }, lastKey = Last @ listKeys; SetOptions[ formatLeaves, "DropKeys" -> listKeys ]; GroupBy[ data, ReplaceAll[ listKeys, Rule[ lastKey, lastKey -> formatLeaves ] ] ] ]

Now:

(nested = makeNested[ dataset, { "type", "subtype" } ]) // Dataset

nested[ "a","I","value" ]

1

Naturally this saves space in memory and on disk:

Map[ByteCount] @ { nested, dataset }

{2064, 2280}

Nice, or even nicer: GroupBy[assoc, keylist, Apply[# &, #, {Length@keylist - 1}] &]. — Kuba
– Kuba, Commented Feb 15, 2016 at 15:06
Interestingly in the updated version makeNested[ dataset, {"type", "subtype", "type", "subtype" } ] will crash Mathematica. So no mimicing with this one. :-/ — gwr
– gwr, Commented Feb 16, 2016 at 15:04
One might of course streamline formatLeaves as it may make sense to simply make the leaves Datasets. Also I find it quite annoying having to remember which functions accept #key& or Key["key"] or simply "key"- but hope for improvement never dies. — gwr
– gwr, Commented Feb 16, 2016 at 15:08

Community · Accepted Answer · 2017-04-13 12:56:03Z

If you need to retain the original keys, I'd be inclined to follow Edmund's answer; alternatively, if you are happy enough to throw away the keys (also given that "every entry is unique") one flexible approach follows a nice solution of your own.

 RecurAssocMerge[a : {__Association}] := Merge[a, RecurAssocMerge]; RecurAssocMerge[a_] := Last[a]; RecurAssocMerge[ini_Association, path_List, value_] := RecurAssocMerge[{ini, Fold[<|#2 -> #|> &, value, Reverse@path]}]; RecurAssocMerge[ini_Association, fullPath_List] := RecurAssocMerge[ini, Most@fullPath, Last@fullPath]; (* added from previous post to handle single list specification *)

Now build up incrementally after extracting values

Query[All, Values]@dataset // Fold[RecurAssocMerge, <||>, #] & (* <|"a" -> <|"I" -> 1, "II" -> 2|>, "b" -> <|"I" -> 1, "II" -> 2|>|> *)

Stack Exchange Network

Reshaping associations, generalization of GroupBy

I'd like to build a nested association for more handy querying, e.g. I would like to have:

I can start with

What is a generic way to go?

6 Answers 6

Update: A more streamlined solution

Linked

Hot Network Questions

Reshaping associations, generalization of GroupBy

I'd like to build a nested association for more handy querying, e.g. I would like to have:

I can start with

What is a generic way to go?

6 Answers 6

Update: A more streamlined solution

Linked

Related

Hot Network Questions