# Counting Events in a moving date range

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[
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

• You might want to define what an "event" is, it's a little bit off-putting to have to read the code to understand what you are trying to do. – C. E. Mar 16 '14 at 18:02
• Good point! The list dvHITS is the date and the time for "High Intensity Training" events. My goal is to have 12 of these for the last 28 days, for every day. – FredrikD Mar 16 '14 at 18:48
• Hint: Using SparseArray properties and AbsoluteTime, you can get results for all dates with an arbitrary window size in a few hundredths of a second. – ciao Mar 17 '14 at 2:23
• @rasher I would need a more precise hint :-) If I understand your hint it would be to change the list to a SparseArray (w Absolute times) and then e.g. use the approach in Woulers answer. – FredrikD Mar 17 '14 at 7:59
• @FredrikD:No, completely different, taking advantage of SA properties. Don't have time to work on it right now, but there's a similar way w/o SA that performs similarly, i.e., a some hundredths of a second to generate all results for a given window size. If an answer that's not fully tailored to OP is OK, happy to post example, otherwise I can get to a precise one later. – ciao Mar 17 '14 at 9:11

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.

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.

• Great! The constants in the index part of the Table has to do with the time window and the number of days from October 1 to "now", right? – FredrikD Mar 16 '14 at 18:57
• The -28 puts the right side of the window on 0, your 'targetdate', and the 165 is date difference between target date and the last date in dvHITS. – Wouter Mar 16 '14 at 19:04