7
$\begingroup$

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

enter image description here

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"]]

enter image description here

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

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

enter image description here

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 *)
$\endgroup$
0

3 Answers 3

7
$\begingroup$

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] &] 
$\endgroup$
10
$\begingroup$

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|>}
$\endgroup$
5
  • $\begingroup$ +1 Nice. And the current undocumented Pivot will exist as some other function, Transpose will be extended, or it will just disappear? $\endgroup$
    – Rojo
    Jul 15, 2014 at 18:04
  • 2
    $\begingroup$ @Rojo I don't know yet. $\endgroup$ Jul 15, 2014 at 19:30
  • $\begingroup$ Pivot no longer works on V10.0.1. $\endgroup$
    – Murta
    Sep 22, 2014 at 13:19
  • $\begingroup$ @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'. $\endgroup$ Sep 24, 2014 at 5:22
  • 1
    $\begingroup$ @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. $\endgroup$
    – Murta
    Sep 24, 2014 at 10:44
8
$\begingroup$

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.