Mathematica Stack Exchange is a question and answer site for users of Mathematica. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 last eample gives me Failure[Dataset, {"MessageTemplate" :> Dataset::partatomic, "MessageParameters" -> <|"Type" -> TypeSystem`Atom[Integer], "Part" -> "key3", "Symbol" -> Part|>}] – Kuba Jul 2 at 10:54

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.