3
$\begingroup$

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.

Consider:

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

Given:

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.

$\endgroup$
  • $\begingroup$ But what't wrong with your last method (last line)? Why are you not satisfied by it? $\endgroup$ – Leonid Shifrin Jul 15 '14 at 23:04
  • $\begingroup$ We routinely deal with hierarhical data structures that can exceed this number of levels. It's too cumbersome to use the last method. $\endgroup$ – alancalvitti Jul 15 '14 at 23:57
  • $\begingroup$ Your last eample gives me Failure[Dataset, {"MessageTemplate" :> Dataset::partatomic, "MessageParameters" -> <|"Type" -> TypeSystem`Atom[Integer], "Part" -> "key3", "Symbol" -> Part|>}] $\endgroup$ – Kuba Jul 2 '16 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.