Looking for ranges in dataframe values

Question

I have 2 dataframes:

> access
     V1     V2     V3
1 chr10 136122 136533
2 chr10 179432 179769
3 chr10 182988 183371
4 chr10 224234 224489
5 chr10 237693 237958

and

> peaks
     V1     V2     V3
1 chr10 126122 126533
2 chr10 179450 179730
3 chr10 182788 183350
4 chr10 224244 224500
5 chr10 237695 237950

The coloumn V2 and V3 are start and end of regions (range) in both dataframes. I want to keep those rows in peaks dataframe for which access$V1 == peaks$V1 AND which fall in the range (or regions) of access dataframe. For example the new dataframe will be like: peaks dataframe's

• 1st row region doesn't exist in access dataframe so it will be assigned category U.

• 2nd row of peaks falls in the given range in access dataframe (2nd row) and will be assigned category B.

• 3rd row of peaks doesn't completely fall in that region but it somehow overlaps with region in 3rd row of access, for this I will assign category A.

• 4th row of peaks also doesn't overlap completely at it ends 11 number after the end of region in row 4 of access, this will also be in category A.

• 5th row falls in the region hence will be in category B.

Expected output:

> newdf   
     V1     V2     V3 V4
1 chr10 126122 126533  U
2 chr10 179450 179730  B
3 chr10 182788 183350  A
4 chr10 224244 224500  A
5 chr10 237695 237950  B

Here are the dput of input dataframes:

> dput(peaks)
structure(list(V1 = structure(c(1L, 1L, 1L, 1L, 1L), .Label = "chr10", class = "factor"), 
    V2 = c(126122L, 179450L, 182788L, 224244L, 237695L), V3 = c(126533L, 
    179730L, 183350L, 224500L, 237950L)), .Names = c("V1", "V2", 
"V3"), class = "data.frame", row.names = c(NA, -5L))

> dput(access)
    structure(list(V1 = structure(c(1L, 1L, 1L, 1L, 1L), .Label = "chr10", class = "factor"), 
        V2 = c(136122L, 179432L, 182988L, 224234L, 237693L), V3 = c(136533L, 
        179769L, 183371L, 224489L, 237958L)), .Names = c("V1", "V2", 
    "V3"), class = "data.frame", row.names = c(NA, -5L))

Edit:

My new access df looks like this and now I also want to append the last column in my final output df:

> access
     V1     V2     V3  V4
1 chr10 136122 136533  found
2 chr10 179432 179769  notFound
3 chr10 182988 183371  found
4 chr10 224234 224489  found
5 chr10 237693 237958  notFound

So now there is one extra condition which is if row in access falls in peaks range then also append the value in V4 in a new column in final df, if some region is not found then by default will be notFound. Therefore, final output will be:

> newdf   
     V1     V2     V3 V4 V5
1 chr10 126122 126533  U notFound
2 chr10 179450 179730  B notFound
3 chr10 182788 183350  A found
4 chr10 224244 224500  A found
5 chr10 237695 237950  B notFound

Here in row1$V5 the value is notFound because this region was not found and in remaining cases we got the values in V5 from modified access df.

Answer 1

If speed is a concern, the linked data.table solution will probably be superior, but it can also be achieved in dplyr, but likely much slower:

library(dplyr)
names(access)[2:3] <- c('start', 'end')

bind_cols(peaks[-1], access) %>%
  rowwise() %>% 
  mutate(V4 = if_else(all(V2:V3 %in% start:end), 'B',
                      if_else(any(V2:V3 %in% start:end), 'A',
                              'U')))

Result:

Source: local data frame [5 x 6]
Groups: <by row>

# A tibble: 5 x 6
      V2     V3     V1  start    end    V4
   <int>  <int> <fctr>  <int>  <int> <chr>
1 126122 126533  chr10 136122 136533     U
2 179450 179730  chr10 179432 179769     B
3 182788 183350  chr10 182988 183371     A
4 224244 224500  chr10 224234 224489     A
5 237695 237950  chr10 237693 237958     B

Answer 2

Though its a long way of doing it. But it gives the desired results.

library(dplyr)
df<-cbind(peaks,access) #merging both df
colnames(df)<-c("pV1","pV2","pV3","aV1","aV2","aV3")
df<-df[c(which(df$pV1==df$aV1)),] # selecting rows with pV1=aV1
# creating U, A, B
U1<-df%>%
   filter(pV2<aV2 & pV3<aV2)%>%
   mutate(V4="U")
U2<-df%>%
  filter(pV2>aV3 & pV3>aV3)%>%
  mutate(V4="U")
B<-df%>%
 filter(pV2>aV2 & pV3<aV3)%>%
   mutate(V4="B")
A1<-df%>%
   filter(pV2>aV2 & pV3>aV3)%>%
   mutate(V4="A")
A2<-df%>%
   filter(pV2<aV2 & pV3<aV3 & pV3>aV2)%>%
   mutate(V4="A")
#merging U, A and B into newdf
newdf<-arrange(rbind(U1,U2,B,A1,A2),pV2)
newdf<-newdf[,-c(4:6)]
newdf
    pV1    pV2    pV3 V4
1 chr10 126122 126533  U
2 chr10 179450 179730  B
3 chr10 182788 183350  A
4 chr10 224244 224500  A
5 chr10 237695 237950  B

Answer 3

Using the foverlaps function it can be done with the following statement:

setkey(setDT(access),V1,V2,V3)
setkey(setDT(peaks),V1,V2,V3)

access[,V4:= ifelse(!is.na(foverlaps(peaks, access, type="within", which=TRUE)$yid),"B",ifelse(!is.na( foverlaps(peaks, access, type="any", which=TRUE)$yid),"A","U"))]

The way it functions is the following:

1. I first use the "within" type to determine if there is an exact overlap (so if a range is included in any of the matching access ranges.If it is the case then "B"
2. If this is not the case, I use the "any" to determine if we have any overlap which practically identifies the values that have a partial overlap since the values with exact overlap were excluded in the previous step. These values get an "A"
3. The rest gets a "U"

Answer 4

Here's another (straightforward) solution using the non-equi joins implemented recently and available in the current development version of data.table, v1.9.7. See installation instructions here:

require(data.table) # v1.9.7+
setDT(access)
setDT(peaks)[, V4 := "U"]                              # no overlap
peaks[access, V4 := "A", on=.(V1, V2 <= V3, V3 >= V2)] # any overlap
peaks[access, V4 := "B", on=.(V1, V2 >= V2, V3 <= V3)] # completly within
#       V1     V2     V3 V4
# 1: chr10 126122 126533  U
# 2: chr10 179450 179730  B
# 3: chr10 182788 183350  A
# 4: chr10 224244 224500  A
# 5: chr10 237695 237950  B

Add a new column to peaks which is all "U". Then replace those rows where there's any kind of overlap with "A". That would contain all rows which are also completely "within". Then once again, perform a conditional join, but this time only for completely within, and replace with "B".

---

Note that the foverlaps() solution would work just fine as well (it also comes from data.table package). But the new non-equi joins fits well with the [.data.table syntax which allows to aggregate/add/update cols while joining.