Matching a column from a data frame on the columns of another data frame

[zisis86]

I got two big data frames, one (df1) has this structure

V1 V2 V3
1 Chr1 7507 10944
2 Chr1 10944 13170
3 Chr1 13170 20065
4 Chr1 20065 28273
5 Chr1 28273 29960
6 Chr1 29960 36599
7 Chr1 36599 37513
8 Chr1 37513 40360
9 Chr1 40360 48796
10 Chr1 48796 50661

The other (df2) has this

V1 V2 V3 V4 V5
1 Chr1 7507 7507 1 1
2 Chr1 10944 10944 1 2
3 Chr1 13170 13170 1 22
4 Chr1 20065 20065 1 3
5 Chr1 28273 28273 1 161
6 Chr1 29960 29960 1 10
7 Chr1 36599 36599 1 604
8 Chr1 37513 37513 1 117
9 Chr1 40360 40360 1 8
10 Chr1 48796 48796 1 3
what I'm trying to do is to check if the column V2 or V3 (is the same) of df2 is = or between the range of V2 and V3 of df1 then I want to write the value of V5 of df2 in a new column in df1 if not write 0. the result that i want would be like :

Chr1 7507 10944 1
Chr1 10944 13170 2
Chr1 13170 20065 22
Chr1 20065 28273 3
Chr1 28273 29960 161
Chr1 29960 36599 10
Chr1 36599 37513 604
Chr1 37513 40360 117
Chr1 40360 48796 8
.
.
.
Do you know any good way to do this?
Thank you very much.

[dpryan]

The simplest solution is to make these GRanges objects and then use findOverlaps. You can then add meta information columns to the first dataset (just 2 columns of 0s) and then increment those values according to the overlap values. This has the benefit of taking care of cases where there are multiple overlaps.

[bioBob]

Do you want to compare these line by line? Also, are V2 and V3 always the same?

If so, why not use an ifelse statement in R?

df3<-cbind(df1,nrow(df1) ##just adds another column to the df equal to 0
df3[,4]<-ifelse((df2[,2]>(df1[,2]-1) || df2[,2]<(df1[,3]+1)),df2[,5],df3[,4])

not tested, but something like this should work if you are going line by line