R help - Mar 2012 - R merge two dataframes with different row?

Hi everyone,
I have a question for R code to merge.
Say I have two dataframes:
File1 is:
V1    V2    V3    V4
1    100    101    name1
2    200    201    name2
2    300    301    name3
3    400    401    name4
3    500    501    name5
4    600    601    name6
4    700    701    name7

File2 is:
V1    V2    V3    V4
1    50    55    p1
3    402    449    p2
4    550    650    p3
4    651    660    p4
2    150    250    p5
2    250    350    p6
3    450    499    p7
2    100    250    p8

I hope to have the merged file3 meet the following three criteria:
(1) File1$V1==File2$V1, and
(2) File1V2>=File2$V2, and
(3) File1V3<=File2$V3.

In this case, we can see that there should be four records meet these three
criteria. So the final merge file should looks like:
File1$V1 File1$V2 File1$V3 File1$V4 File2$V1 File2$V2 File2$V3 File2$V4
2            200         201         name2   2            150
250        p5
2            200         201         name2   2             100
250        p8
2            300         301         name3   2             250
350        p6
4            600         601         name6   4             550
650       p3

Is there any one know how to make R code to achieve this? I thought out a
way to use for loop to read for each row in the file1 to compare the whole
data frame in file2 to find the mathcing rows but for loop takes so much
time. I would like to have a vectorized code to make it faster but I don't
know how to manage this.

Thanks,

	[[alternative HTML version deleted]]

On Mar 16, 2012, at 5:51 AM, zhu free wrote:
> Hi everyone,
> I have a question for R code to merge.
> Say I have two dataframes:
> File1 is:
> V1    V2    V3    V4
> 1    100    101    name1
> 2    200    201    name2
> 2    300    301    name3
> 3    400    401    name4
> 3    500    501    name5
> 4    600    601    name6
> 4    700    701    name7
>
> File2 is:
> V1    V2    V3    V4
> 1    50    55    p1
> 3    402    449    p2
> 4    550    650    p3
> 4    651    660    p4
> 2    150    250    p5
> 2    250    350    p6
> 3    450    499    p7
> 2    100    250    p8
>
> I hope to have the merged file3 meet the following three criteria:
> (1) File1$V1==File2$V1, and
> (2) File1V2>=File2$V2, and
> (3) File1V3<=File2$V3.
>
> In this case, we can see that there should be four records meet  
> these three
> criteria. So the final merge file should looks like:
> File1$V1 File1$V2 File1$V3 File1$V4 File2$V1 File2$V2 File2$V3  
> File2$V4
> 2            200         201         name2   2            150
> 250        p5
> 2            200         201         name2   2             100
> 250        p8
> 2            300         301         name3   2             250
> 350        p6
> 4            600         601         name6   4             550
> 650       p3
>
> Is there any one know how to make R code to achieve this? I thought  
> out a
> way to use for loop to read for each row in the file1 to compare the  
> whole
> data frame in file2 to find the mathcing rows but for loop takes so  
> much
> time. I would like to have a vectorized code to make it faster but I  
> don't
> know how to manage this.
Why not merge on the exact criterion and then subset out the rows that  
qualify on the logical tests?

dfm <- merge(File1, File2, 1)
subset(dfm, V2.x >= V2.y & V3.x <= V3.y)
    V1 V2.x V3.x  V4.x V2.y V3.y V4.y
2   2  200  201 name2  150  250   p5
4   2  200  201 name2  100  250   p8
6   2  300  301 name3  250  350   p6
12  4  600  601 name6  550  650   p3
--

David Winsemius, MD
West Hartford, CT