On Thu, Sep 9, 2010 at 12:39 PM, Dimitri Shvorob <dimitri.shvo...@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 ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.