I have two data frames and wish to use the value in one (DF1$pos) to search through two columns in DF2 (DF2start, DF2end) and if it falls within those numbers, return DF2$name
DF1
ID pos name
chr 12
chr 542
chr 674
DF2
ID start end annot
chr 1 200 a1
chr 201 432 a2
chr 540 1002 a3
chr 2000 2004 a4
so in this example I would like DF1 to become
ID pos name
chr 12 a1
chr 542 a3
chr 674 a3
I have tried using merge and intersect but do not know how to use an if statement with a logical expression in them.
The data frames should be coded as follows,
DF1 <- data.frame(ID=c("chr","chr","chr"),
pos=c(12,542,672),
name=c(NA,NA,NA))
DF2 <- data.frame(ID=c("chr","chr","chr","chr"),
start=c(1,201,540,200),
end=c(200,432,1002,2004),
annot=c("a1","a2","a3","a4"))
Perhaps you can use foverlaps from the "data.table" package.
library(data.table)
DT1 <- data.table(DF1)
DT2 <- data.table(DF2)
setkey(DT2, ID, start, end)
DT1[, c("start", "end") := pos] ## I don't know if there's a way around this step...
foverlaps(DT1, DT2)
# ID start end annot pos i.start i.end
# 1: chr 1 200 a1 12 12 12
# 2: chr 540 1002 a3 542 542 542
# 3: chr 540 1002 a3 674 674 674
foverlaps(DT1, DT2)[, c("ID", "pos", "annot"), with = FALSE]
# ID pos annot
# 1: chr 12 a1
# 2: chr 542 a3
# 3: chr 674 a3
As mentioned by @Arun in the comments, you can also use which = TRUE in foverlaps to extract the relevant values:
foverlaps(DT1, DT2, which = TRUE)
# xid yid
# 1: 1 1
# 2: 2 3
# 3: 3 3
DT2$annot[foverlaps(DT1, DT2, which = TRUE)$yid]
# [1] "a1" "a3" "a3"