Given a list of date and value pairs, efficiently sum the values by date [duplicate]

I have a list of date & value pairs. There can be multiple entries for each day. Sample data can be generated with the following:

maxDays = 10;
date = AbsoluteTime[{2013, 5, 13}];
dateValuePairs = Flatten[Table[{date + day * 86400, value}, {day, maxDays}, {value, 10}], 1];

The sample data looks like:

{{3577478400, 1}, {3577478400, 2}, ... {3577478400, 10},
{3577564800, 1}, {3577564800, 2}, ... {3577564800, 10},
...
{3578256000, 1}, {3578256000, 2}, ... {3578256000, 10}}

I am trying to sum the values for the same day. With the sample data above, I expected to see ten days, each with a value of 55 .

I tried the following:

Clear[sumByDay];
Map[
(sumByDay[#[[1]]] =
If[ValueQ[sumByDay[#[[1]]]],
sumByDay[#[[1]]] + #[[2]],
#[[2]]
]) &, dateValuePairs];
DownValues[sumByDay]

... and I expected it to return:

Out[]= {HoldPattern[sumByDay[3577478400]] :> 55, HoldPattern[sumByDay[3577564800]] :> 55, ... HoldPattern[sumByDay[3578256000]] :> 55}

\$RecursionLimit::reclim: Recursion depth of 256 exceeded. >>

Out[]= {HoldPattern[sumByDay[3577478400]] :> 1 + sumByDay[3577478400]}

sumByDay[#[[1]]] + #[[2]] must not be being evaluated as I expected, see below:

In[]:= a[3577478400] = 1
In[]:= a[3577478400] = a[3577478400] + 2
...
In[]:= a[3577478400] = a[3577478400] + 10

Out[]= 1
Out[]= 3
...
Out[]= 55

And the Question of Efficiency ...

I am sure I can accomplish this less efficiently. As I am equally sure that it can be done more efficiently. i.e. :

Map[(day = #;
{day, Total[
Transpose[Select[dateValuePairs, #[[1]] == day &]][[2]]]}
) &, Sort[DeleteDuplicates[Transpose[dateValuePairs][[1]]]]]

The number of days defined by maxDays in the sample data example will grow at a const rate.

marked as duplicate by Mr.Wizard♦Jul 29 '17 at 14:46

• You can use GatherBy as in {#[[1, 1]], Total[#[[All, 2]]]} & /@ GatherBy[dateValuePairs, #[[1]] &]. – b.gates.you.know.what May 13 '13 at 20:08
• A variant of this has been asked several times on this site... please try searching the list-manipuation tag for Gather and GatherBy (and look through the results for one that matches) – rm -rf May 13 '13 at 20:09
• Gather & GatherBy ... that is what I was missing. – mmorris May 13 '13 at 20:29
• And for using a wrench to hammer the nail in ... Any idea where I went wrong with DownValues? – mmorris May 13 '13 at 20:33
• The LHS of your internal function is not correct yet it manages to send you into infinite recursion... – SEngstrom May 13 '13 at 21:59

Here is a quick and dirty way to grab the data and tally it up:

data = Gather[dateValuePairs, #1[[1]] == #2[[1]] &];
sumSet[list_] := {list[[1, 1]], Plus @@ (#[[2]] & /@ list)}
sumSet /@ data

(*
{{3577478400, 55}, {3577564800, 55}, {3577651200, 55}, {3577737600,
55}, {3577824000, 55}, {3577910400, 55}, {3577996800,
55}, {3578083200, 55}, {3578169600, 55}, {3578256000, 55}}
*)

The second argument to Gather[] is a request to gather those pairs that have the same date.

• It will usually be far more efficient to use GatherBy, where applicable, than the second argument of Gather. – Mr.Wizard Aug 12 '13 at 10:15

In versions 10+, you use GroupBy

List @@@ Normal @ GroupBy[dateValuePairs, First->Last, Total]

{{3577478400, 55}, {3577564800, 55}, {3577651200, 55}, {3577737600, 55},
{3577824000, 55}, {3577910400, 55}, {3577996800, 55},
{3578083200, 55}, {3578169600, 55}, {3578256000, 55}}

or Merge:

List @@@ Normal @ Merge[Total][Rule @@@ dateValuePairs]

{{3577478400, 55}, {3577564800, 55}, {3577651200, 55}, {3577737600, 55},
{3577824000, 55}, {3577910400, 55}, {3577996800, 55},
{3578083200, 55}, {3578169600, 55}, {3578256000, 55}}