4
$\begingroup$

I would like to be able to create a new dataset by extracting 2 columns to make a composite key, and also extracting other columns. For example, suppose my dataset looked like:

enter image description here

I would like to create:

{<| New Key -> {2013, TYML143}, Earned Prem Est -> -19322.6|>,
 <| New Key -> {2013, TYML143}, Earned Prem Est -> -7323.55|>,
 <| New Key -> {2013, TYML529}, Earned Prem Est -> -2259.94|>,
 ...}
$\endgroup$
  • $\begingroup$ +1 but just a comment, {2013, TYML143} -> data is a composite Key, whereas the list is the Value here. $\endgroup$ – alancalvitti Nov 13 '14 at 22:17
6
$\begingroup$

If you have your data as a Dataset, for example, using m_goldberg's data:

ds= Dataset[data]

You can get a new Dataset as follows:

ds[All, <|"newKey" -> {#year, #id}, "qty" -> #qty|> &] 

New dataset

Or if you want a list of Associations:

ds[All, <|"newKey" -> {#year, #id}, "qty" -> #qty|> &] //Normal
{<|"newKey" -> {2012, "abc"}, "qty" -> 123|>,
 <|"newKey" -> {2012, "def"}, "qty" -> 456|>,
 <|"newKey" -> {2013, "abc"}, "qty" -> 321|>, 
 <|"newKey" -> {2013, "ghi"}, "qty" -> 42|>, 
 <|"newKey" -> {2014, "abc"}, "qty" -> 312|>}
$\endgroup$
2
$\begingroup$

First I have to make some data, since no data in copyable form was given in the question.

keys = {"year", "id", "qty"};
vals = 
  {{2012, "abc", 123}, {2012, "def", 456}, {2013, "abc", 321}, 
   {2013, "ghi", 42}, {2014, "abc", 312}};
data = AssociationThread[keys, #] & /@ vals
{<|"year" -> 2012, "id" -> "abc", "qty" -> 123|>, 
 <|"year" -> 2012, "id" -> "def", "qty" -> 456|>, 
 <|"year" -> 2013, "id" -> "abc", "qty" -> 321|>, 
 <|"year" -> 2013, "id" -> "ghi", "qty" -> 42|>, 
 <|"year" -> 2014, "id" -> "abc", "qty" -> 312|>}

Given the data, a composition of MapThread, Join, Map, KeyDrop, KeyTake and Values will do the job. Perhaps it can be expressed more succinctly, but I've been much good at code golf.

MapThread[
  Join, 
  {<|"newKey" -> #|> & /@ (KeyDrop[data, "qty"] // Values), KeyTake[data, "qty"]}]
{<|"newKey" -> {2012, "abc"}, "qty" -> 123|>, 
 <|"newKey" -> {2012, "def"}, "qty" -> 456|>, 
 <|"newKey" -> {2013, "abc"}, "qty" -> 321|>,
 <|"newKey" -> {2013, "ghi"}, "qty" -> 42|>, 
 <|"newKey" -> {2014, "abc"}, "qty" -> 312|>}
$\endgroup$

Your Answer

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

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