similar to: question on sqldf syntax

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.....

Hi, Try this: lines1<- readLines(textConnection("gene1 or1|1234 or3|56 or4|793 gene4 or2|347 gene5 or3|23 or7|123456789")) lines2<-readLines(textConnection(">or1|1234 ATCGGATTCAGG >or2|347 GAACCTATCGGGGGGGGAATTTATATATTTTA >or3|56 ATCGGAGATATAACCAATC >or3|23 AAAATTAACAAGAGAATAGACAAAAAAA >or4|793 ATCTCTCTCCTCTCTCTCTAAAAA >or7|123456789

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

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 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, >

Dear Eliza, Try this: Lines1<-readLines(textConnection("1911.01.01?????? 7.87 1911.01.02?????? 9.26 1911.01.03?????? 8.06 1911.01.04?????? 8.13 1911.01.05????? 12.90 1911.02.06?????? 5.45 1911.02.07?????? 3.26 1911.03.08?????? 5.70 1911.03.09?????? 9.24 1911.04.10?????? 7.60 1911.05.11????? 14.82 1911.05.12????? 14.10 1911.06.13?????? 7.87 1911.06.14?????? 9.26

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

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

Hi all; I have a data set with the format below: Year, Day, Hour, Value 2010, 001, 0, 15.9 2010, 001, 1, 7.3 2010, 001, 2, 5.2 2010, 001, 3, 8.0 2010, 001, 4, 0.0 2010, 001, 5, 12.1 2010, 001, 6, 11.6 2010, 001, 7, 13.9 2010, 001, 8, 11.9 2010, 001, 9, 13.6 2010, 001, 10, 16.1 2010, 001, 11, 18.5 That should

Hi R users, Wonder if somebody could give me help on how to reshape this type of data: ----------------------------------------------------------------------------------------------------------------------- Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0000-0010 | 28| 28 0010-0020| 302| 302 0020-0030| 42| 42 0030-0040| 2| 2 0040-0050| 1| 1 0060-0070| 1| 1

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

Readers, I have data with different time stamps that I wish to plot (for example): data set 1 time(hh:mm:ss),datum 01:00:00,500 01:00:15,600 01:00:30,750 01:00:45,720 01:01:00,700 01:01:15,725 01:01:30,640 01:01:45,710 data set 2 time,datum 01:00:12,20 01:01:01,55 01:01:55,22 The time interval in data set 1 does not change, but the time interval in data set 2 does change, such that for a

I have two files with dates and prices in each. The number of rows in each of them will differ. How do I create a new file which contains data from both these files? Cbind and merge are not helpful. For cbind because the rows are not the same replication occurs. Also if I have similar data how do I write a vlookup kind of function? I am giving an example below: Say Price1 file contains the

Dear all, I have following 2 zoo objects. However when I try to merge those 2 objects into one, nothing is coming as intended. Please see below the objects as well as the merged object: > dat11 V2 V3 V4 V5 2010-10-15 13:43:54 73.8 73.8 73.8 73.8 2010-10-15 13:44:15 73.8 73.8 73.8 73.8 2010-10-15 13:45:51 73.8 73.8 73.8 73.8 2010-10-15 13:46:21 73.8 73.8 73.8 73.8

Hi everyone, I'm writting a script that will open multiple files in a single folder and then will do some calculations to finally save everything in a big table. So here is the pseudo code #read all files in the given directory myfiles <-list.files("c:\\myDir") #initialize table ??? #loop through files for(f in myfiles[-1]) { netMat <-

Hi Using the ggplot2 package, I would like to obtain a plot that contains two time series that have data points on different dates. For instance, one data frame looks like: date1, value1 2010-01-05, 2921.74 2010-01-08, 2703.89 2010-01-14, 3594.21 2010-01-20, 3659.22 The other data frame looks like date2, value2 2010-01-01, 285.85 2010-01-02, 229.20 2010-01-05, 333.91 2010-01-06, 338.27

When I run sqldf to merge two datasets, it's changing the Date (class date) to a numeric value (class factor). Not sure why. Appreciate any insight. Console output for two datasets and the merged dataset (via sqldf) listed below. > summary(df.aggregate) Date Hour x Min. :2010-07-01 0 : 64 Min. : 0.00 1st Qu.:2010-07-25 1 :

I have a (very big - 1.5 rows) dataframe with a (POSIXt" "POSIXlt") column h (hour). Surprisingly, I cannot calculate a simple aggregate over the dataframe. > n.h1 = sqldf("select distinct h, count(*) from x group by h") Error in sqliteExecStatement(con, statement, bind.data) : RS-DBI driver: (error in statement: no such table: x) In addition: Warning message: In

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 =