5

I have a list of associations. I will need to add 2 columns (or perform some other arithmetic operation) and put the answer in a new column. For example:

{<|Year->2014, ID->1, amt1->10, amt2->20|>
 <|Year->2015, ID->1, amt1->20, amt2->25|> 
 <|Year->2014, ID->2, amt1->11, amt2->21|>}

I want to get:

{<|Year->2014, ID->1, amt1->10, amt2->20, tot->30|>
 <|Year->2015, ID->1, amt1->20, amt2->25, tot->45|> 
 <|Year->2014, ID->2, amt1->11, amt2->21, tot->32|>}

Sometimes I will want to delete the amt1 and amt2, depending on what I'm doing at the time.

I've done this by converting this back into a list of values, operating on the list and putting it back into an association. But it seems like there must be a better way of doing this.

Mitchell Kaplan
  • 3,696
  • 22
  • 34

2 Answers2

7

Given the data from the question:

data = {<|Year->2014, ID->1, amt1->10, amt2->20|>,
        <|Year->2015, ID->1, amt1->20, amt2->25|>,
        <|Year->2014, ID->2, amt1->11, amt2->21|>};

We can introduce the key tot which is the sum of amt1 and amt2 like this:

<| #, tot -> #[amt1] + #[amt2] |> & /@ data

(*
   {<| Year->2014, ID->1, amt1->10, amt2->20, tot->30 |>,
    <| Year->2015, ID->1, amt1->20, amt2->25, tot->45 |>,
    <| Year->2014, ID->2, amt1->11, amt2->21, tot->32 |>}
*)

Should we wish to drop the input keys amt1 and amt2:

<| KeyDrop[#, {amt1, amt2}], tot -> #[amt1] + #[amt2] |> & /@ data

(*
   {<| Year->2014, ID->1, tot->200 |>,
    <| Year->2015, ID->1, tot->500 |>,
    <| Year->2014, ID->2, tot->231 |>}
*)

In some situations, it might just be easiest to build the result associations from scratch:

<| Year -> #[Year], ID -> #[ID], tot -> #[amt1] + #[amt2] |> & /@ data

(*
   {<| Year->2014, ID->1, tot->30 |>,
    <| Year->2015, ID->1, tot->45 |>,
    <| Year->2014, ID->2, tot->32 |>}
*)
WReach
  • 68,832
  • 4
  • 164
  • 269
4
data = {<|Year -> 2014, ID -> 1, amt1 -> 10, amt2 -> 20|>,
        <|Year -> 2015, ID -> 1, amt1 -> 20, amt2 -> 25|> , 
        <|Year -> 2014, ID -> 2, amt1 -> 11, amt2 -> 21|>};

Don't trust me, it's my 3rd answer about associations, but it works, so here you go:

data[[All, Key[tot]]] = Total /@ data[[All, {Key[amt1], Key[amt2]}]];
data

{<|Year -> 2014, ID -> 1, amt1 -> 10, amt2 -> 20, tot -> 30|>,
 <|Year -> 2015, ID -> 1, amt1 -> 20, amt2 -> 25, tot -> 45|>, 
 <|Year -> 2014, ID -> 2, amt1 -> 11, amt2 -> 21, tot -> 32|>}

Instead of Part you can use Lookup:

Total /@ Lookup[data, {amt1, amt2}, 0]

which is shorter and can give default value 0.

more general:

SetAttributes[f, HoldFirst];
f[data_, newKey_, keys_, function_] := data[[All, newKey]] = (function @@@ Lookup[data, keys]);

f[data, "total", Key /@ {Year, ID}, List];
data

{<|Year -> 2014, ID -> 1, amt1 -> 10, amt2 -> 20, "total" -> {2014, 1}|>,
 <|Year -> 2015, ID -> 1, amt1 -> 20, amt2 -> 25, "total" -> {2015, 1}|>,
 <|Year -> 2014, ID -> 2, amt1 -> 11, amt2 -> 21, "total" -> {2014, 2}|>}
Kuba
  • 136,707
  • 13
  • 279
  • 740
  • That works, thank you. Suppose I want to multiply the 2 elements, or divide one by the other? Can I somehow set up a formula to do that? Or to operate on more than 2 elements. For example, for 3 elements, to add the first 2 and subtract the third one. – Mitchell Kaplan Feb 02 '15 at 21:13
  • I guess either. I just did an example by using the list of associations as just a list. i.e., data[[All,3]]*data[[All,4]]. This gives me back a list which I'd have to turn back into an association. Ideally my code would probably be clearer if I used the keys rather than indices. Also, if it seems that I should just work with lists, and not try to do this inside the association, I can do that. – Mitchell Kaplan Feb 02 '15 at 21:19
  • Sorry, I see that my previous edit was not at all clear. What about a case where I want to divide amt1 by amt2? If I can do that, I should be able to handle most of the situations I'm encountering. – Mitchell Kaplan Feb 02 '15 at 21:41
  • @MitchellKaplan Then you could do f[data, Key[div], Key /@ {amt1, amt2}, #/#2 &]; – Kuba Feb 02 '15 at 21:42