Counting Events in a moving date range

Question

I want to count the number of times events take place in a date range. The counting is done over a window of e.g. 28 days where the goal is to have between 11 and 13 events in the time window. The date range for the counting is e.g. from last October until now.

My current solution seems to be inefficient, both timing wise (it takes almost 63 seconds for this small, sample test data) and from an intuitive way of thinking about the problem (with no repeated recounting of values).

My question is: How can I solve this problem in a more efficient and elegant way?

Here is my version

sumEventsInRange[targetDate_, eventlist_, minDiff_, maxDiff_] := 
  Length[Select[
    eventlist, (minDiff < DateDifference[Part[#, 2], targetDate] < 
       maxDiff) & ] ];

dvHITS = Drop[
   Last[Import[
     "https://docs.google.com/spreadsheet/ccc?key=\
0AstJzCCxOXH1dDdBaDRHMW5FMFFFVzhPRnpTUXlrU1E&usp=sharing", "Data"]], 
   1];

DateListPlot[
 Transpose[{DateRange[{2013, 10, 1}, 
    now], (sumEventsInRange[#, dvHITS, 0, 28] &) /@ 
    DateRange[{2013, 10, 1}, now]}], 
 PlotRange -> {{{2013, 10, 1}, now}, {0, 20}}, 
 GridLines -> {Automatic, {11, 13}}, Joined -> True]

Which generates the following plot

Answer 1

I'll go ahead and post this per our comments interchange, happy to tinker with it when time allows, but you'll get the idea and can do so yourself if you want. This does not use sparse arrays I alluded to, probably easier to understand and almost as fast. Note results slightly differ from yours in this example (hence tinkering comment): it produces correct counts for the window size (closed intervals), your OP seems to use open intervals. On with it...

windowedData = 
  Partition[ MapAt[# - 1 &, Tally[Join[ DateRange[dvHITS[[1, 2]], 
         dvHITS[[-1, 2]]], (DateList[{#, {"Year", "Month", "Day"}}] & /@ 
         dvHITS[[All, 2]])[[All, ;; 3]]]], {All, 2}], 28, 1];

windowedEvents = 
  Transpose[{windowedData[[All, 1, 1]], windowedData[[All, -1, 1]], 
    Total[windowedData[[All, All, 2]], {2}]}];

The above produces a list in windowedEvents:

windowedEvents // Short

(* {{{2013,8,24},{2013,9,20},12},<<175>>,{{2014,2,16},{2014,3,15},13}} *)

which consists of lists of date pairs (the start and end of that position of the "window") and the count of "events" in that window position (based on OP, I assumed and "event" is the presence of an entry, nothing more.)

You can pluck specifics out of the result with Select, Cases, etc., or just pull the last element of all sublists for the overall list of event counts only:

(* grab one window by end of window date *)
Cases[windowedEvents, {_, {2013, 10, 3}, c_}]

(* grab one window by start of window date *)
Cases[windowedEvents, {{2013, 9, 6}, _, c_}]

(* grab one window by end of window date, just get count *)
Cases[windowedEvents, {_, {2013, 10, 3}, c_} :> c]

(* grab one window by start of window date, just get count *)
Cases[windowedEvents, {{2013, 9, 6}, _, c_} :> c]

(* just get list of event counts over all dates *)
windowedEvents[[All, 3]] // Short

(*

{{{2013, 9, 6}, {2013, 10, 3}, 11}}
{{{2013, 9, 6}, {2013, 10, 3}, 11}}
{11}
{11}
{12,11,12,12,12,12,12,12,12,10,11,<<155>>,12,12,12,13,12,13,13,13,13,12,13}

*)

Some notes: This is quite fast, generating counts for a given window size in a few hundredths of a second. Assumes base data (dvHITS) is in sorted date order. The second argument to Partition (28 in this example) determines window size. Overall date range considered is first and last date of dvHITS.

Hope this is of some use, and again, happy to polish when time permits if you think worthwhile.

Answer 2

You seem to count events in a gliding time window of 28 days wide.
I would do it like this:

now = Take[DateList[], 3]; (* you forgot to initialise 'now' *)  
it = DateDifference[{2013, 10, 1}, Part[#, 2]] & /@ dvHITS ;  
Table[Count[it, d_ /; (d > 0 + del && d < 28 + del)], {del, -28, 165 - 27}]  

It takes less than 1 second.