21
$\begingroup$

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

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

7 Answers 7

9
$\begingroup$

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.

$\endgroup$
0
15
$\begingroup$

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.

$\endgroup$
1
  • $\begingroup$ This is efficient, readable, and terse. +1 $\endgroup$
    – Mr.Wizard
    Apr 17, 2012 at 1:37
8
$\begingroup$

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}}}
*)
$\endgroup$
5
  • $\begingroup$ You've got my vote. :-) $\endgroup$
    – Mr.Wizard
    Apr 17, 2012 at 1:19
  • $\begingroup$ @Mr.Wizard why? Reap and Sow? Or, the "excessive" use of shorthand? Or, better yet, it's a one liner? $\endgroup$
    – rcollyer
    Apr 17, 2012 at 1:21
  • $\begingroup$ Reap and Sow only. You can do better if you're going for terse (#1 -> #, Total -> Tr at least). $\endgroup$
    – Mr.Wizard
    Apr 17, 2012 at 1:26
  • $\begingroup$ @Mr.Wizard that's true, but sometimes it pays to include the 1 for clarity. :P $\endgroup$
    – rcollyer
    Apr 17, 2012 at 1:35
  • $\begingroup$ 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. ;-) $\endgroup$
    – Mr.Wizard
    Apr 17, 2012 at 1:39
7
$\begingroup$

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]
$\endgroup$
0
6
$\begingroup$

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}}
$\endgroup$
2
  • 2
    $\begingroup$ 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]]. $\endgroup$
    – user31159
    Jan 3, 2016 at 13:41
  • $\begingroup$ @Xavier Thanks for the note. I overlooked this comment before. $\endgroup$
    – Mr.Wizard
    Jan 15, 2016 at 0:19
5
$\begingroup$

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}]
]
$\endgroup$
1
  • $\begingroup$ 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. $\endgroup$
    – gpap
    Aug 6, 2014 at 14:12
4
$\begingroup$

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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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