# Normal // Association // Dataset workaround for some user query operators

Given example tabular data (ie, with rows and columns, or "data frames")

data = Table[<| "key1" -> i, "key2" -> i + 10 ,
"key3" -> i + 20|>, {i, 4}] //Dataset


The following query operator almost works to associate (the values of) a selected "primary" key at first level:

primaryKey[key_] := Sequence[All, Slot[key] -> KeyDrop[#, key] &]


This allows the lookup syntax:

data[primaryKey["key1"]][3]

(* 3 -> <|"key2" -> 13, "key3" -> 23|> *)


How to modify this query to handle:

1. Return only the value <|"key2" -> 13, "key3" -> 23|>, similar to how data[All, #key1 &] // Normal returns only the values {1, 2, 3, 4}.

2. Dataset doesn't recognize the implicit global association structure, flattening the data by 1 level:

data[primaryKey["key1"]]

The following workaround works but how to incorporate in the operator?

data[primaryKey["key1"]] // Normal // Association // Dataset


The same issue arises from other restructuring operators, like generating an association from (the values of) two specified keys eg

data[All, #key1 -> #key3 &][2]

(* 2 -> 22 *)


Whereas

data[All, #key1 -> #key3 &] // Normal // Association //
Dataset // #[2] &

(* 22 *)

-
Rojo, outstanding. I will accept if you officially answer below. – alancalvitti Jul 14 '14 at 22:05

Changing the descending operator All to the ascending Association seems to work at turning the resulting list of rules into an association.

primaryKey[key_] := Sequence[Association, Slot[key] -> KeyDrop[#, key] &]

-

Thanks to @Leonid and @Rojo for answering the question.

I want to add that I'll be adding a specialized function to do precisely this operation (and its inverse).

I think it will be called Pivot (the existing, undocumented Pivot will go away), and it will work like this:

In[1]:= Pivot[{<|a -> 1, b -> 2|>, <|a -> 3, b -> 4|>}, Key[a]]
Out[1]= <|1 -> <|b -> 2|>, 3 -> <|b -> 4|>|>

In[2]:= Pivot[<|1 -> <|b -> 2|>, 3 -> <|b -> 4|>|>, Key[a]]
Out[2]= {<|a -> 1, b -> 2|>, <|a -> 3, b -> 4|>}

-
+1 Nice. And the current undocumented Pivot will exist as some other function, Transpose will be extended, or it will just disappear? – Rojo Jul 15 '14 at 18:04
@Rojo I don't know yet. – Taliesin Beynon Jul 15 '14 at 19:30
Pivot no longer works on V10.0.1. – Murta Sep 22 '14 at 13:19
@Murta Yes, Pivot is gone. Undocumented functions can be removed at the slightest whim, unfortunately :-). The truth is there is a much simpler and more useful operation that wants the name 'Pivot'. – Taliesin Beynon Sep 24 '14 at 5:22
@TaliesinBeynon I can imagine.. to do something like Excel Pivot Table in Mathematica you have to create your own functions today. Pivot is one of the best Excel utilities, and MMA should have something similar if it really wants to make data science analysis easier. – Murta Sep 24 '14 at 10:44

Here is how I'd do this:

transformed = data[GroupBy[Key["key1"]], Delete[Key["key1"]] @* First]


This avoids the round-trip to a list of rules and back to Association.

-
Nice use of that peculiar descending operator GroupBy – Rojo Jul 14 '14 at 22:33
@Rojo Thanks. Your method is also nice. Shows once again that Dataset is hackable, which is good. – Leonid Shifrin Jul 14 '14 at 22:41
@LeonidShifrin, thanks for the paradigm. This works also when the key is duplicate, whereas Slot[key] returns only the last appearance. – alancalvitti Jul 15 '14 at 17:39
@LeonidShifrin, how does ascending composition by First work here? Substituting Last for First gives the same result. – alancalvitti Jul 15 '14 at 17:43
A different way to write the same thing could be data[GroupBy[Key["key1"] -> KeyDrop["key1"]], First] – Rojo Jul 19 '14 at 16:05