# Combining values in one column (or part) when values in another column (or part) match

Consider the following:

data={{{3542313600, 175}, {3542918400, 175}}, {{3544128000, 450},
{3544128000, 450}, {3545337600, 450}, {3545942400, 450}, {3546547200, 450}}};


whereas the first element of each {_,_} represent a date in absolute time, i.e. {_,_} corresponds to the pattern {AbsoluteDate_,Value_}.

Now I would like to calculate the sum of all Value_ when the corresponding AbsoluteDate_ is equal, e.g.

{3545337600, 450}, {3545337600, 450}-> {3545337600, 900}.

-
Do you mind if your list gets sorted? –  rm -rf Apr 16 '12 at 23:28
Looks like there are many ways to skin the cat. –  David Carraher Apr 17 '12 at 0:50
@David: in Mathematica? Always. –  Ｊ. Ｍ. Apr 17 '12 at 11:58
@John moving-target questions are generally discouraged. You should consider posting your update as a new question. –  Mr.Wizard Apr 17 '12 at 21:22
I changed everything back and posted a new question: mathematica.stackexchange.com/questions/4379/… –  John Apr 17 '12 at 21:32

The following also works (though its a bit messy to look at) and will probably be faster than pattern matching if you have large input data.

Transpose[{#[[All, 1, 1]],
Total[#[[All, All, 2]], {2}]}] &[#] & /@ (GatherBy[#, First] & /@
data)


EDIT:

I should point out that there are at least a couple of different problems being solved in the answers posted here. The results posted by myself and @RM attempt to preserve the structure of the original data and so do not gather like dates if they are present in multiple sublists.

Answers posted by @David Carraher and @rcollyer ignore the structure of the original data and gather all like dates.

I'm not sure which is the correct approach in this case but it felt worth pointing out the difference.

-

Here's another one to consider:

{#[[1, 1]], Total[#[[All, 2]]]} & /@ GatherBy[Flatten[data, 1], First]


GatherBy... gathers pairs with the same AbsoluteDate.

{#[[1, 1]], Total[#[[All, 2]]]} & /@ returns {date, totVal} for each date.

-
This is efficient, readable, and terse. +1 –  Mr.Wizard Apr 17 '12 at 1:37

Here is another way (partially similar to Andy's solution) :

Transpose[{#[[All, 1, 1]], Total[#[[All, All, 2]], {2}]}] &[#]& @ SplitBy[ Flatten[ data, 1], First]


or similar to David's solution :

{#[[1, 1]], Total[#[[All, 2]]]} & /@ SplitBy[ Flatten[ data, 1], First]

-
+1 for SplitBy, which I've never used. –  David Carraher Apr 17 '12 at 0:51

Assuming that you did not intend to add an extra layer of lists in your data, for this type of manipulation, I'm a fan of Reap and Sow:

Reap[ Sow[#2, #1]& @@@ Flatten[data,1], _, {#1,Total[#2]}&][[2]]
(* {{3542313600, 175}, {3542918400, 175}, {3544128000, 450},
{3544732800, 450}, {3545337600, 900}, {3545942400, 450},
{3546547200, 450}, {3547152000, 0}}
*)


If it wasn't mistaken, we need to Map the above onto each data set:

Reap[ Sow[#2, #1]& @@@ #, _, {#1,Total[#2]}&][[2]]& /@ data
(* {{{3542313600, 175}, {3542918400, 175}},
{{3544128000, 450}, {3544732800, 450}, {3545337600, 900},
{3545942400, 450}, {3546547200, 450}, {3547152000, 0}}}
*)

-
You've got my vote. :-) –  Mr.Wizard Apr 17 '12 at 1:19
@Mr.Wizard why? Reap and Sow? Or, the "excessive" use of shorthand? Or, better yet, it's a one liner? –  rcollyer Apr 17 '12 at 1:21
Reap and Sow only. You can do better if you're going for terse (#1 -> #, Total -> Tr at least). –  Mr.Wizard Apr 17 '12 at 1:26
@Mr.Wizard that's true, but sometimes it pays to include the 1 for clarity. :P –  rcollyer Apr 17 '12 at 1:35
Agreed. I was simply explaining that if you want to gain my vote on the basis of shorthand it's going to have to be more thorough. ;-) –  Mr.Wizard Apr 17 '12 at 1:39

This solution doesn't involve pattern matching and should be fast for large lists (pattern matching, especially with ___ slows down tremendously):

Block[{g},
g[{x_?NumericQ, y_?NumericQ}] := {x, y};
g[x_List, y__List] := {First@x, Total[{x, y}[[All, 2]]]};
Apply[g, (GatherBy[#, First] & /@ data), {2}]
]

-
I wrote (completely independently) almost the exact same function (even used g as the dummy function!) in a more recent question that I didn't realise was a duplicate of this one. I even got downvoted and annoyed cause I thought it was a decent answer! Naturally I will give you +1. I had defined g[x_?VectorQ] := x; in place of your g[{x_?NumericQ, y_?NumericQ}] := {x, y}; but I doubt this makes any difference here. –  gpap Aug 6 '14 at 14:12

Here's a straightforward one using replacements:

data /. {h___, {x_, y_}, b___, {x_, z_}, t___} :> {h, {x, y + z}, b, t}
(* Out[1]= {{{3542313600, 175}, {3542918400, 175}},
{{3544128000, 450}, {3544732800, 450}, {3545337600, 900}, {3545942400, 450},
{3546547200, 450}, {3547152000, 0}}} *)


Change the /. or ReplaceAll to //. or ReplaceRepeated for lists with multiple repeated first elements. This method doesn't sort the list.

-

For data that is not already grouped by the first element:

dat2 = {{baz, 16}, {foo, 65}, {baz, 41}, {bar, 88}, {foo, 2},
{bar, 96}, {foo, 20}, {bar, 19}, {foo, 36}, {baz, 90}};


GroupBy (introduced in version 10) offers a concise syntax:

GroupBy[dat2, First -> Last, Total]

<|baz -> 147, foo -> 123, bar -> 203|>


Note that this provides the flexibility of specifying First and Last as the functions that yield the parts to compare and combine. For the specific format shown a shorter v10 method is to convert the expressions into a Rule list and Merge them:

Merge[Rule @@@ dat2, Total]

<|baz -> 147, foo -> 123, bar -> 203|>


The result is an Association. Keeping the data in this format has a number of benefits but it can also be converted back to a common List with:

List @@@ Normal[%]

{{baz, 147}, {foo, 123}, {bar, 203}}

-