21

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

J. M.'s missing motivation
  • 124,525
  • 11
  • 401
  • 574
John
  • 4,361
  • 1
  • 26
  • 41

7 Answers7

15

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.

DavidC
  • 16,724
  • 1
  • 42
  • 94
9

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.

Andy Ross
  • 19,320
  • 2
  • 61
  • 93
8

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}}}
*)
rcollyer
  • 33,976
  • 7
  • 92
  • 191
7

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]
Artes
  • 57,212
  • 12
  • 157
  • 245
6

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:

res = 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 Normal or KeyValueMap:

List @@@ Normal[res]

KeyValueMap[List, res]

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

{{baz, 147}, {foo, 123}, {bar, 203}}
Mr.Wizard
  • 271,378
  • 34
  • 587
  • 1,371
  • 2
    From Mathematica version 10.1, KeyValueMap can be used on the association: KeyValueMap[List, GroupBy[dat2, First -> Last, Total]] or KeyValueMap[List, Merge[Rule @@@ dat2, Total]]. –  Jan 03 '16 at 13:41
  • @Xavier Thanks for the note. I overlooked this comment before. – Mr.Wizard Jan 15 '16 at 00:19
5

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}]
]
rm -rf
  • 88,781
  • 21
  • 293
  • 472
  • 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 06 '14 at 14:12
4

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.

rm -rf
  • 88,781
  • 21
  • 293
  • 472