# How can I extend BinCounts to work on Times and Dates?

I want to extend BinCounts to work on a list of times or dates, binning them by Day, Week, Hour of the Month, Day of the Year, etc... So given a list of times and a time bin spec, I need to calculate the bin counts.

One example might be if I have list of times I've been on Facebook and I want to to plot these times on a 2D Histogram where the Y axis is "MinuteOfTheDay" and the x axis is "DayOfTheWeek". Alpha can already do this (but not with arbitrary time bins)

To clarify my question I made a prototype:

d = RandomDates[1000];
Timing[TimeBinCounts[d, {"Day", "Week"}]]


gives the proper counts {157, 150, 136, 147, 149, 129, 132}, (e.g. Count[DayOfWeek /@ d, Monday] == 157). And queries like

Timing[TimeBinCounts[d, {"Month", "Year"}]]


work too, but I'm not sure how to handle an arbitrary time bin spec like "HoursOfTheWeek"

Timing[TimeBinCounts[d, {"Hour", "Week"}]]


Will not work...

<<Calendar
TimeBinCounts::badspec = "The time bin spec 1 is not acceptable.";
DaysOfTheWeek = {Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday};
RandomDateList[] := {RandomInteger[{1800, 2100}], RandomInteger[{1, 12}], RandomInteger[{1, 28}], RandomInteger[{0, 23}], RandomInteger[{0, 59}], RandomInteger[{0, 59}]};
RandomDates[n_] := Table[RandomDateList[],{n}]

TimeSpecHelper[spec_] := Module[
{types, convert, result},
types = {"Second", "Minute", "Hour", "Day", "Week", "Month", "Year"};
convert = {60, 60, 24, 7, 4.345, 12};
result = Range[Times @@ Part[convert, Most[Range @@ Flatten[
First[Position[types, #]]& /@ spec]]]];
If[MatchQ[spec[[1]], "Second"|"Minute"|"Hour"],
result - 1,
result
]
]

TimeBinCounts[times_List, spec_] := Module[
{dayorder, bins, minDate, maxDate, order},

minDate = Min[times[[All, 1]]];
maxDate = Max[times[[All, 1]]];

order = Thread[Rule[{"Second", "Minute", "Hour", "Day", "Week", "Month", "Year"}, Range[7]]];
If[Greater @@ (spec /. order),
Return[\$Failed];
];

Switch[spec,
{"Day","Week"} | "Day",
With[{days = DayOfWeek /@ times /. daysNum},
BinCounts[days, {1, 8, 1}]],
{"Hour", "Day"}|"Hour",
BinCounts[times[[All, 4]], {0, 24, 1}],
{"Day", "Month"},
BinCounts[times[[All, 3]], {1, 32, 1}],
{"Month", "Year"}|"Month",
BinCounts[times[[All, 2]], {1, 13, 1}],
"Year",
With[{interval = Range[minDate[[1]], maxDate[[1]]]},
Count[times[[All, 1]], #]& /@ interval
],
_,
order = Thread[Rule[{"Second", "Minute", "Hour", "Day", "Month", "Year"},
Range[6]]];
With[{interval = TimeSpecHelper[spec]},
Count[Mod[times[[All, 6 - (spec[[1]] /. order) + 1]], Length[interval]], #]& /@ TimeSpecHelper[spec]
]
]
]

-
GatherBy might be what you need. – Verbeia Jun 19 '12 at 20:29
It will be a lot more involved than just using that GatherBy, what is needed is the temporal analogue of BinCounts, TimeBinCounts. – M.R. Jun 19 '12 at 20:53
I haven't got time to test this out now but indeed GatherBy is not enough on its own. But something like {First[#],Last[#]}& @@@ GatherBy[data,datespec] might work. – Verbeia Jun 19 '12 at 21:51
The time compelxity of GatherBy is too high, this needs to work on 100,000s of dates. – M.R. Jun 19 '12 at 22:18
I deleted my answer, so commenting here: the main bottleneck of your current implementation is in the DayOfWeek function, and here I show one possible way to significantly speed it up with the help of Java. – Leonid Shifrin Jun 20 '12 at 19:50

This isn't a complete solution, but perhaps it has some ideas that you can work into something usable.

Use GatherBy to organise the dates into lists of dates with common parts in Years, Months, Days, Hours, Minutes:

dates = ToDate /@ Range[10^8, 10^8 + 3600 24 1000, (3600 24)/400];

arranged=GatherBy[dates, {#[[1]] &, #[[2]] &, #[[3]] &, #[[4]] &, #[[5]] &}];


arranged took about 3/4 of a second to construct with about 400,000 dates.

To count the dates in specific years, days, hours, mins etc something like this ...

Length@Position[arranged, {1903, 3, 4, _, _, _}]


238

gives the count for a day.

This gives bin counts for hours for a day:

Length@Position[arranged, {1903, 3, 4, #, _, _}] & /@ Range@24


{0, 0, 0, 0, 0, 0, 0, 0, 4, 17, 17, 16, 17, 17, 16, 17, 17, 16, 17, 17, 16, 17, 17, 0}

It just occurred to me that you don't really need to use gather for this approach:

Length@Position[dates, {1903, 3, 4, #, _, _}] & /@ Range@24
`

{0, 0, 0, 0, 0, 0, 0, 0, 4, 17, 17, 16, 17, 17, 16, 17, 17, 16, 17, 17, 16, 17, 17, 0}

gives exactly the same output.

Maybe the task can be re-cast as creating suitable patterns to select the bins you need, or bins which form the elements of your range and then summing them.

-