Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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 *)
share|improve this question
    
Rojo, outstanding. I will accept if you officially answer below. –  alancalvitti Jul 14 at 22:05

3 Answers 3

up vote 5 down vote accepted

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] &] 
share|improve this answer

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|>}
share|improve this answer
    
+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 at 18:04
2  
@Rojo I don't know yet. –  Taliesin Beynon Jul 15 at 19:30
    
Pivot no longer works on V10.0.1. –  Murta Sep 22 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 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 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.

share|improve this answer
    
Nice use of that peculiar descending operator GroupBy –  Rojo Jul 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 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 at 17:39
    
@LeonidShifrin, how does ascending composition by First work here? Substituting Last for First gives the same result. –  alancalvitti Jul 15 at 17:43
    
@alancalvitti Basically, without First, you get a list of all results with this key, as a value for the key in a new assoc. First and Last give the same result only when the keys are unique, because then all groups will be lists with a single element. –  Leonid Shifrin Jul 15 at 18:12

Your Answer

 
discard

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

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