R help - Jul 2010 - Left Outer Join 2 DF's on Multiple Conditions

Hi,

I am trying to execute the following SQL statement using two data frames:

tab1, tab2 : Two Tables

Select tab1.*, tab2.*, tab1.tobiiTime - tab2.ruiTime as timeDiff,
IFNULL(n-m, -9999999) as alwaysIncrement
                  FROM tab1
LEFT OUTER JOIN tab2 On tab1.data1 - tab2.mouseX = 0 And tab1.data2 -
tab2.mouseY = 0


I am trying to do the following in R:-

*#Getting error here:*
data <- merge(tab1,tab2, all.x=TRUE, by=(data$data1 - data$mouseX == 0),
by=(data$data2 - data$mouseY == 0))
 data <- cbind(data, data[, "tobiiTime"] - data[,
"ruiTime"], data[, "n"] -
data[, "m"])

#Change name of column tobiiTime-ruiTime to timeDiff, for convenience
data <- rename(data, c(tobiiTime-ruiTime="timeDiff"))

#Change name of column n-m to alwaysIncrement, for convenience
data <- rename(data, c(n-m="alwaysIncrement"))

*In the merge step, I want to include the following condition of merging:-*
tab1.data1 - tab2.mouseX = 0 And tab1.data2 - tab2.mouseY = 0

Any ideas how this could be done.

Thanks in advance.

Regards,
Harsh Yadav

	[[alternative HTML version deleted]]

Look at sqldf package, it is easier to do sql like statements with it.

Nikhil Kaza
Asst. Professor,
City and Regional Planning
University of North Carolina

nikhil.list at gmail.com

On Jul 25, 2010, at 6:10 PM, harsh yadav wrote:
> Hi,
>
> I am trying to execute the following SQL statement using two data  
> frames:
>
> tab1, tab2 : Two Tables
>
> Select tab1.*, tab2.*, tab1.tobiiTime - tab2.ruiTime as timeDiff,
> IFNULL(n-m, -9999999) as alwaysIncrement
>                  FROM tab1
> LEFT OUTER JOIN tab2 On tab1.data1 - tab2.mouseX = 0 And tab1.data2 -
> tab2.mouseY = 0
>
>
> I am trying to do the following in R:-
>
> *#Getting error here:*
> data <- merge(tab1,tab2, all.x=TRUE, by=(data$data1 - data$mouseX ==  
> 0),
> by=(data$data2 - data$mouseY == 0))
> data <- cbind(data, data[, "tobiiTime"] - data[,
"ruiTime"], data[,
> "n"] -
> data[, "m"])
>
> #Change name of column tobiiTime-ruiTime to timeDiff, for convenience
> data <- rename(data, c(tobiiTime-ruiTime="timeDiff"))
>
> #Change name of column n-m to alwaysIncrement, for convenience
> data <- rename(data, c(n-m="alwaysIncrement"))
>
> *In the merge step, I want to include the following condition of  
> merging:-*
> tab1.data1 - tab2.mouseX = 0 And tab1.data2 - tab2.mouseY = 0
>
> Any ideas how this could be done.
>
> Thanks in advance.
>
> Regards,
> Harsh Yadav
>
> 	[[alternative HTML version deleted]]
>
> ______________________________________________
> 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.