Can I sum or mulitply 2 columns in a list of associations

Question

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.

Answer 1

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 |>}
*)

Answer 2

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}|>}