[R] Failure to aggregate

[jim holtman]

I think your main problem is that you have your time as POSIXlt which
is a multiple valued vector.  I converted the 't' to POSIXct, removed
the other POSIXlt value and created a 'h' as the character for the
hour and it works fine:

> str(g)
'data.frame':   6 obs. of  5 variables:
 $ price: int  500 500 501 501 500 501
 $ size : int  221000 2000 1000 13000 3000 3000
 $ src  : chr  "R" "R" "R" "R" ...
 $ time : POSIXct, format: "2005-01-04 09:00:24" "2005-01-04 09:00:47"
"2005-01-04 09:01:12" "2005-01-04 09:01:18" ...
 $ h    : chr  "09" "09" "09" "09" ...
> sqldf("select distinct h, src, count(*) from g group by h,src")
   h src count(*)
1 09   R        6


On Thu, Sep 9, 2010 at 11:16 AM, Dimitri Shvorob
<dimitri.shvorob at gmail.com> wrote:
>
>> g = head(x)
>> dput(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")
>
>> n = sqldf("select distinct h, src, count(*) from g group by h, src")
> Loading required package: tcltk
> Loading Tcl/Tk interface ... done
> Error in sqliteExecStatement(con, statement, bind.data) :
>  RS-DBI driver: (error in statement: no such table: g)
> In addition: Warning message:
> In value[[3L]](cond) : RAW() can only be applied to a 'raw', not a 'double'
>
> --
> View this message in context: http://r.789695.n4.nabble.com/Failure-to-aggregate-tp2528613p2533051.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> R-help at 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.
>



-- 
Jim Holtman
Cincinnati, OH
+1 513 646 9390

What is the problem that you are trying to solve?