On Mon, Jan 25, 2010 at 2:17 PM, GL <pfl...@shands.ufl.edu> wrote:
>
> trying to structure sql to merge two datasets. structure follows:
>
> dbs.possible.combos (all possible combinations of dates and places)
> Date Place
> 1/1/10 N-01
> 1/1/10 S-02
> 1/2/10 N-01
> 1/2/10 S-02
> etc...
>
> dbs.aggregate (the raw data aggregated by date and location)
> Date Place Days
> 1/1/10 N-01 6
> 1/1/10 S-02 10
> 1/2/10 S-02 5
>
>
> Trying to merge so I look-up the values for each possible combo
> dbs.final <- sqldf("select dbs.possible.combos$Date,
> dbs.possible.combos$Place, dbs.possible.combos$Days FROM dbs.possible.combos
> LEFT JOIN dbs.aggregate ON (dbs.possible.combos$Place = dbs.aggregate$Place)
> AND (dbs.possible.combos$Date = dbs.aggregate$Date)")
>
> Resulting in:
> Error in sqliteExecStatement(con, statement, bind.data) :
> RS-DBI driver: (error in statement: near ".": syntax error)
>
> What am I getting wrong in the syntax?
You have to pass it a valid SQL statement but $ is not an SQL
operator. Also dot (.) is an SQL operator so you have quote
identifiers that contain a dot so that it will not regard those dots
as operators.
Try this:
library(sqldf)
lines1 <- "Date Place
1/1/10 N-01
1/1/10 S-02
1/2/10 N-01
1/2/10 S-02"
dbs.possible.combos <-
read.table(textConnection(lines1), header = TRUE, as.is = TRUE)
lines2 <- "Date Place Days
1/1/10 N-01 6
1/1/10 S-02 10
1/2/10 S-02 5"
dbs.aggregate <- read.table(textConnection(lines2), header = TRUE, as.is = TRUE)
dbs.final <- sqldf('select Date, Place, Days
FROM "dbs.possible.combos"
LEFT JOIN "dbs.aggregate" using (Place, Date)')
Giving:
> dbs.final
Date Place Days
1 1/1/10 N-01 6
2 1/1/10 S-02 10
3 1/2/10 N-01 NA
4 1/2/10 S-02 5
______________________________________________
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.
