Sign up ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

This question is related to this one re pivoting on keys.

Because of the nesting, using Leonid's method as starting point - single key:

pivotUp[key_] := Query[GroupBy[Key@key], KeyDrop[key]]

How to extend the operation iteratively - but partially - to create hierarhical Datasets from tabular data.

Interesting cases include duplicate values, which are pivoted to duplicate keys.


pivotUp[keys_List] := 
 Sequence @@ (Flatten@{GroupBy[Key@#] & /@ keys, KeyDrop@keys})


data = AssociationThread[{"key1", "key2", "key3"} -> #] & /@  {{1, 
     "a", 10}, {1, "b", 20}, {2, "c", 30}} // Dataset

enter image description here

One way to use pivotUp is this:

  data[pivotUp[Key@"key1"]][All, pivotUp[Key@"key2"]] 

This is more compact:

data[pivotUp[{"key1", "key2"}]]

  <|1 -> <|"a" -> {<|"key3" -> 10|>}, "b" -> {<|"key3" -> 20|>}|>, 
     2 -> <|"c" -> {<|"key3" -> 30|>}|>|> (* normal form *)

But how can "key3" be accessed, either with given pivotUp or alternatives? These don't work:

data[pivotUp[{"key1", "key2"}], "key3"]

enter image description here

The desired output is:

data[pivotUp[{"key1", "key2"}]][All, All, All, "key3"] // Normal

<|1 -> <|"a" -> {10}, "b" -> {20}|>, 2 -> <|"c" -> {30}|>|>

Ideally, these partial pivots should handle {__Association} structures.

share|improve this question
But what't wrong with your last method (last line)? Why are you not satisfied by it? – Leonid Shifrin Jul 15 '14 at 23:04
We routinely deal with hierarhical data structures that can exceed this number of levels. It's too cumbersome to use the last method. – alancalvitti Jul 15 '14 at 23:57

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.