i think this is what you want.. :)
http://stackoverflow.com/questions/4765936/using-joined-tables-to-exclude-certain-records
library(sqldf)
# use the mtcars example table
mtcars
# keep the first eight records in a second, separate data set
x <- mtcars[ 1:8 , ]
# keep all the records from mtcars where the mpg value doesn't match a
record in the `x` data table
y <- sqldf( "select * from mtcars where not exists ( select * from x where
x.mpg == mtcars.mpg )" )
# the above example assumes your tables won't fit in RAM.
# if they do, this method is obviously much easier
y <- mtcars[ !( mtcars$mpg %in% x$mpg ) , ]
On Tue, Jan 15, 2013 at 4:02 PM, Matthew Liebers <mr...@cornell.edu> wrote:
> 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.....
>
> df1_new<-sqldf("select df1.V1, df2.V2 where rs10.V1 = d10.pos”) - Ideally I
> would like to try to use "delete" or not equal to "!=" though I can only
> find that delete works with sqldf.
> but it returns this error:
> Error in sqliteExecStatement(con, statement, bind.data) :
> RS-DBI driver: (error in statement: no such column: df1.V1)
> I am also trying this:
> df1_new<-sqldf("select V1 from df1, V2 from df2 where df1.V1 = df2.V1")
> which returns this error:
> Error in sqliteExecStatement(con, statement, bind.data) :
> RS-DBI driver: (error in statement: near "from": syntax error)
>
> If anyone with sqldf knowledge could lend me a hand that would be great.
> Thanks!
>
> Matt
>
> [[alternative HTML version deleted]]
>
>
> ______________________________________________
> 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.
>
>
[[alternative HTML version deleted]]
______________________________________________
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.
