[R] R merge two dataframes with different row?

[David Winsemius]

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