similar to: Failure to aggregate

Hi All, I am having trouble executing SQL statements on a few dataframes, but the funny thing is that I am able to execute the statement on some other dataframes. To test, I have 2 very small dataframes (6 rows and some columns). One is 'lessliq', the dput is given below. > dput(head(lessliq)) structure(list(V1 = c(50464677L, 50464846L, 50432581L, 50426614L, 50504329L, 50504735L), V2

Hi, I am new to R and trying to migrate from SAS. I am trying to use sqldf to create a new table from existed table and change some of the columns. I have table called DataOld with columns commodity, rate and total and I am trying to create new table called DataNew with columns commodity, ratenew and totalNew. > sqldf("create table datanew as select commodity, ratenew as rate * 10, >

Please correct the following > sqldf("update esc left join forwagg on esc.ym=forwagg.Date set esc.ri2=forwagg.N1 where esc.age=12","select * from main.esc") Error in sqliteExecStatement(con, statement, bind.data) : RS-DBI driver: (error in statement: near "left": syntax error) Thanks. Stephen [[alternative HTML version deleted]]

I'm trying to use sqldf to query for the earliest date of a blood test when patients have had multiple tests in a given year. My query looks like this: test11 <- sqldf("select CHILD_ID, min(SAMP_DATE) from lab group by CHILD_ID having extract (year from SAMP_DATE) = 2011") SAMP_DATE has class "date." I get the error

Dear list,   Dear list, I am using read.csv.sql() from the sqldf package to read individual-based data from a csv file that is too large for R. My original file contains a column called “ID” that identifies the individual. I would like to read in data for only one individual at a time, for example "Bobby". read.csv.sql("filename",sql = 'select * from file where ID =

I am trying to exclude integer values from a small data frame 1, d1 that have matching hits in data frame 2, d2 (Very big) which involves matching those hits first. I am trying to use sqldf on the df's in the following fashion: df1: V1 12675 14753 16222 18765 df2: head(df2) V1 V2 13647 rd1500 14753 rd1580 15987 rd1590 16222 rd2020.....

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

> grep("f[0-9]+=", "f1=5,f22=3,", value = T) [1] "f1=5,f22=3," How do I make the line output c("f1", "f22") instead? (Actually, c(1,22) would be even better). Thank you. -- View this message in context: http://r.789695.n4.nabble.com/Quick-GREP-challenge-tp2339486p2339486.html Sent from the R help mailing list archive at Nabble.com.

... Being an R newbie, I can only think of extracting distinct x values with unique, looping over them, extracting matching rows from the original data frame, applying table, and recording the size of table's output alongside the x value being checked. Is there a more elegant way? Thank you. -- View this message in context:

Dear R-Listers, I am a Windows user (R 2.6.2) using the development version of sqldf to try to read a 3GB file originally stored in .sas7bdat-format. I convert it to comma-delimited ASCII format with StatTransfer before trying to import just the rows I need into R. The problem is that I get this error: > f <- file("hugedata.csv") > DF <- sqldf("select * from f where

Hi, I am running a query using sqldf() [package : sqldf]. The query is:- userid <- 5 taskid <- 5 tab1 <- fn$sqldf("SELECT tobiiEvents.data1, tobiiEvents.data2, events.`timestamp` as tobiiTime FROM tobiiEvents INNER JOIN events ON events.eventid = tobiiEvents.eventid WHERE tobiiEvents.subtype = 'MOUSE' AND tobiiEvents.userid = 5 AND tobiiEvents.taskid = 5 ORDER BY

Hello Group, I am having trouble with the sqldf package on unix. The same code works fine on windows. Silly Example script: # Load the package library(sqldf) # Use the titanic data set data(women) colnames(women) head(women) sqldf('select height, count(*) from women where height is not null group by weight') Unix Output and error: bash-3.00$ R --vanilla <testR.R

SQldf with sqlite and H2 I have a large csv file (about 2GB) and wanted to import the file into R and do some filtering and analysis. Came across sqldf ( a great idea and product) and was trying to play around to see what would be the best method of doing this. csv file is comma delimited with some columns having comma inside the quoation like this "John, Doe". I tried this first

x = structure(list(date = structure(list(sec = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), min = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), hour = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), mday = c(1L, 2L, 3L, 4L, 5L, 8L, 9L, 10L, 11L, 12L, 15L, 16L, 17L, 18L, 19L, 22L, 23L, 24L,

Hi, I am trying to aggregate max a Date type column but have weird result, how do I fix this? > a <- rbind( + data.frame(name='Tom', payday=as.Date('1999-01-01')), + data.frame(name='Tom', payday=as.Date('2000-01-01')), + data.frame(name='Pete', payday=as.Date('1998-01-01')), + data.frame(name='Pete',

2018-03-3 in your code should be 2018-03-31. The line then'201415' needs to be fixed. When posting please provide minimal self-contained examples. There was no input provided and library statements not relevant to the posted code were included. Fixing the invalid date and bad line, getting rid of those library statements that are unnecessary and providing some test input, it works

Hi all, I am trying to create a new variable called Fiscal Year (FY) using case expression in sqldf and I am getting a null FY , see the code below . Please advise me as to how I can do this mutation. library(zoo) library(lubridate) library(stringr) library(RH2) library(sqldf) cr$ReportDate = as.Date(cr$ReportDate, format ='%Y-%m-%d') > cr2 = sqldf(" select

Hi, I have a query about sqldf, and dates in general. I couldnt find much on the net or on the forums, hence I am here. Here is the issue: I want to write a function that accepts 3 arguments: date1, date2 and a dataframe, say 'df'. Within the function, I want to populate a temp dataframe which essentially contains the output of the query "select * from df where DATE between date1

Hi, consider the following two dataframes: x1=c("232","3454","3455","342","13") x2=c("1","1","1","0","0") data1=data.frame(x1,x2) y1=c("232","232","3454","3454","3455","342","13","13","13","13")

Have a long script that runs fine on windows (32 bit). When I try to run in on two different macs (64 bit), however, it hangs with identical behavior. I start with: library(sqldf) This results in messages: Loading required package: DBI Loading required package: RSQLite Loading required package: RSQLite.extfuns Loading required package: gsubfn Loading required package: proto Loading required