# Given a list of date and value pairs, efficiently sum the values by date

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}


... however I receive:

\$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.

-
You can use GatherBy as in {#[[1, 1]], Total[#[[All, 2]]]} & /@ GatherBy[dateValuePairs, #[[1]] &]. –  b.gatessucks 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