[R] Failure to aggregate

[Gabor Grothendieck]

On Thu, Sep 9, 2010 at 12:39 PM, Dimitri Shvorob
<dimitri.shvorob at gmail.com> wrote:
>
> Update: What did make a difference for me - and something that was present in
> Jim's example, but not reproduced by myself initially - was dropping columns
> other than the two involved. When I dropped all columns except for h and
> src, the sqldf call worked.
>
> ... Is it an R bug or what? (I am saying R, not sqldf, because built-in
> tapply did not work either). Arrgh...
>

You need to show what you have done in a reproducible fashion to get
answers.  Converting to POSIXct does work.  g below is the object you
posted earlier in this thread.  I have also removed the erroneous
"distinct" although it will work even if you leave it in.

> g <-
+ structure(list(price = c(500L, 500L, 501L, 501L, 500L, 501L),
+    size = c(221000L, 2000L, 1000L, 13000L, 3000L, 3000L), src = c("R",
+    "R", "R", "R", "R", "R"), t = structure(list(sec = c(24.133,
+    47.096, 12.139, 18.142, 10.721, 28.713), min = c(0L, 0L,
+    1L, 1L, 2L, 2L), hour = c(9L, 9L, 9L, 9L, 9L, 9L), mday = c(4L,
+    4L, 4L, 4L, 4L, 4L), mon = c(0L, 0L, 0L, 0L, 0L, 0L), year = c(105L,
+    105L, 105L, 105L, 105L, 105L), wday = c(2L, 2L, 2L, 2L, 2L,
+    2L), yday = c(3L, 3L, 3L, 3L, 3L, 3L), isdst = c(0L, 0L,
+    0L, 0L, 0L, 0L)), .Names = c("sec", "min", "hour", "mday",
+    "mon", "year", "wday", "yday", "isdst"), class = c("POSIXt",
+    "POSIXlt")), d = structure(list(sec = c(0, 0, 0, 0, 0, 0),
+        min = c(0L, 0L, 0L, 0L, 0L, 0L), hour = c(0L, 0L, 0L,
+        0L, 0L, 0L), mday = c(4L, 4L, 4L, 4L, 4L, 4L), mon = c(0L,
+        0L, 0L, 0L, 0L, 0L), year = c(105L, 105L, 105L, 105L,
+        105L, 105L), wday = c(2L, 2L, 2L, 2L, 2L, 2L), yday = c(3L,
+        3L, 3L, 3L, 3L, 3L), isdst = c(0L, 0L, 0L, 0L, 0L, 0L
+        )), .Names = c("sec", "min", "hour", "mday", "mon", "year",
+    "wday", "yday", "isdst"), class = c("POSIXt", "POSIXlt")),
+    h = structure(list(sec = c(0, 0, 0, 0, 0, 0), min = c(0L,
+    0L, 0L, 0L, 0L, 0L), hour = c(9L, 9L, 9L, 9L, 9L, 9L), mday = c(4L,
+    4L, 4L, 4L, 4L, 4L), mon = c(0L, 0L, 0L, 0L, 0L, 0L), year = c(105L,
+    105L, 105L, 105L, 105L, 105L), wday = c(2L, 2L, 2L, 2L, 2L,
+    2L), yday = c(3L, 3L, 3L, 3L, 3L, 3L), isdst = c(0L, 0L,
+    0L, 0L, 0L, 0L)), .Names = c("sec", "min", "hour", "mday",
+    "mon", "year", "wday", "yday", "isdst"), class = c("POSIXt",
+    "POSIXlt")), m = structure(list(sec = c(0, 0, 0, 0, 0, 0),
+        min = c(0L, 0L, 1L, 1L, 2L, 2L), hour = c(9L, 9L, 9L,
+        9L, 9L, 9L), mday = c(4L, 4L, 4L, 4L, 4L, 4L), mon = c(0L,
+        0L, 0L, 0L, 0L, 0L), year = c(105L, 105L, 105L, 105L,
+        105L, 105L), wday = c(2L, 2L, 2L, 2L, 2L, 2L), yday = c(3L,
+        3L, 3L, 3L, 3L, 3L), isdst = c(0L, 0L, 0L, 0L, 0L, 0L
+        )), .Names = c("sec", "min", "hour", "mday", "mon", "year",
+    "wday", "yday", "isdst"), class = c("POSIXt", "POSIXlt")),
+    s = structure(list(sec = c(24, 47, 12, 18, 10, 28), min = c(0L,
+    0L, 1L, 1L, 2L, 2L), hour = c(9L, 9L, 9L, 9L, 9L, 9L), mday = c(4L,
+    4L, 4L, 4L, 4L, 4L), mon = c(0L, 0L, 0L, 0L, 0L, 0L), year = c(105L,
+    105L, 105L, 105L, 105L, 105L), wday = c(2L, 2L, 2L, 2L, 2L,
+    2L), yday = c(3L, 3L, 3L, 3L, 3L, 3L), isdst = c(0L, 0L,
+    0L, 0L, 0L, 0L)), .Names = c("sec", "min", "hour", "mday",
+    "mon", "year", "wday", "yday", "isdst"), class = c("POSIXt",
+    "POSIXlt"))), .Names = c("price", "size", "src", "t", "d",
+ "h", "m", "s"), row.names = c(NA, 6L), class = "data.frame")
>
> library(sqldf)
>
> # convert POSIXlt columns to POSIXct
> ix <- sapply(g, inherits, "POSIXlt")
> g[ix] <- lapply(g[ix], as.POSIXct)
>
> n <- sqldf("select h, src, count(*) from g group by h, src")
> n
                    h src count(*)
1 2005-01-04 09:00:00   R        6




-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com