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

Question

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};
daysNum = Thread[Rule[DaysOfTheWeek, Range[7]]];
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),
        Message[TimeBinCounts::badspec, spec];
        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]
    ]
]
]

Answer 1

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.