[R] Aggregating data -- table almost does it

[Mike Nielsen]

Dear r-helpers,

I have a data frame that looks like this:

> str(fred)
`data.frame':	3243 obs. of  14 variables:
 $ date       : chr  "02-09-19" "02-09-19" "02-09-19" "02-09-19" ...
 $ time       : chr  "10:31:34" "10:31:34" "10:31:39" "11:36:12" ...
 $ cpusys     : num  0 0.11 0.37 0 0.13 0.46 0 0.01 0.01 0 ...
 $ cpuelapsed : num  0.00328 0.44335 5.08891 0.01576 0.53153 ...
 $ cpuuser    : num  0 0.3 4.19 0 0.28 4.07 0 0 0 0 ...
 $ dread      : int  0 0 0 0 0 0 0 0 0 0 ...
 $ dwrit      : int  0 0 0 0 0 0 0 0 0 0 ...
 $ sread      : int  10 1273 1271 10 1271 1270 0 10 17 27 ...
 $ swrit      : int  0 13 30 0 9 29 0 0 3 0 ...
 $ lread      : int  0 27 0 0 27 0 0 0 0 0 ...
 $ lwrit      : int  0 27 0 0 27 0 0 0 0 0 ...
 $ sbhr       : num  89.25 29.24  5.01 89.25 29.31 ...
 $ lbhr       : num   0.0 99.1  0.0  0.0 99.1 ...
 $ dates.chron:Classes 'chron', 'dates', 'times'  atomic [1:3243] 11949
11949 11949 11949 11949 ...
  .. ..- attr(*, "format")= chr [1:2] "y-m-d" "h:m:s"
  .. ..- attr(*, "origin")= Named num [1:3] 1 1 1970
  .. .. ..- attr(*, "names")= chr [1:3] "month" "day" "year"

Each observation represents an "event".  

Now, I would like to (for example) count the number of events (or maybe
find the mean of a certain variable) in each hour of each day.  To
count, I could use:

> table(as.character(dates(fred$dates.chron)),hours(fred$dates.chron))
          
           10 11  12   13 14
  09/19/02  3 63 112 3042 23

(there is, so far, only one day's worth of events in the data frame).

To do fancier stuff, I know I could use aggregate.

However, I only get a column for each hour in which there was an event. 
What I would dearly love to have is 24 columns, with 0 (zero) indicating
that there were no events in that hour of that day.

Of course, I could use a loop, or even re-write the SQL that I use to
get the data frame, but, after hours of fiddling with it and flipping
through R manuals, I'm starting to boggle and also to fall behind on
what was to be a quick project.  Still, I'd really like to know the
elegant R solution that has got to exist, and would be extremely
grateful to any kind soul who could point me in the right direction.


Thanks so much.

Regards,

Mike

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._