Lookup table in R by matching ranges

I have a table:

CHR POS 
10  4342 
20  100
22  5422

I have another lookup:

CHR start end Gene
10  4000  5999 ABC1
20  50    200  JHT
22  5000  6000 KLO

Desired output:

CHR POS 
10  4342  ABC1
20  100   JHT
22  5422  KLO

In reality there are 700,000 entries in table 1 and roughly 60000 genes. I need to match on chromsome and then get the POS to be between start-end of table 2 to add a new column with the gene name.

I tried :

library(dplyr)

# create sample data
df1 <- data.frame(chromosome = c("chr1", "chr1", "chr2", "chr3"), position = c(100, 200, 300, 400))
df2 <- data.frame(chromosome = c("chr1", "chr2", "chr3"), start = c(50, 250, 350), end = c(150, 350, 450), gene = c("geneA", "geneB", "geneC"))

# perform left join
joined_df <- left_join(df1, df2, by = "chromosome")

# create new column indicating if each row lies within a gene
result_df <- joined_df %>%
              mutate(in_gene = if_else(position >= start & position <= end, gene, NA_character_))

# view result
result_df

But the vector was too large to store.

You can use GenomicRanges for something like this. See commented out code at the beginning below for installing.

The GRanges class is a container for genomic locations and associated annotations.

The function makeGRangesFromDataFrame will take a data.frame as input and automatically find the columns that describe genomic ranges (default is start and end or stop).

Below uses the additional sample data provided.

# if (!require("BiocManager", quietly = TRUE))
#   install.packages("BiocManager")

# BiocManager::install("GenomicRanges")

library(GenomicRanges)

gr1 <- GRanges(seqnames = df1$chromosome,
               IRanges(start = df1$position, width = 1))
gr2 <- makeGRangesFromDataFrame(df2, keep.extra.columns = TRUE)

df1$gene <- NA
ovlp <- findOverlaps(gr1, gr2)
df1$gene[queryHits(ovlp)] <- gr2$gene[subjectHits(ovlp)]

df1

Output

  chromosome position  gene
1       chr1      100 geneA
2       chr1      200  <NA>
3       chr2      300 geneB
4       chr3      400 geneC

With dplyr 1.1.0, we can use join_by for non-equi joins

library(dplyr)
left_join(df1, df2, by = join_by(CHR, 
    closest(POS >= start), closest(POS <= end))) %>%
   select(-start, -end)

-output

 CHR  POS Gene
1  10 4342 ABC1
2  20  100  JHT
3  22 5422  KLO

Or with data.table

library(data.table)
 setDT(df1)[df2, Gene := i.Gene, on = .(CHR, POS >= start, POS <= end)]

-output

> df1
   CHR  POS Gene
1:  10 4342 ABC1
2:  20  100  JHT
3:  22 5422  KLO

data

df1 <- structure(list(CHR = c(10L, 20L, 22L), POS = c(4342L, 100L, 5422L
)), class = "data.frame", row.names = c(NA, -3L))

df2 <- structure(list(CHR = c(10L, 20L, 22L), start = c(4000L, 50L, 
5000L), end = c(5999L, 200L, 6000L), Gene = c("ABC1", "JHT", 
"KLO")), class = "data.frame", row.names = c(NA, -3L))

Here is a variation of left_join:

library(dplyr)

df1 %>% 
  left_join(df2, by="CHR") %>% 
  filter(between(POS, start, end)) %>% 
  select(-c(start, end))

  CHR  POS Gene
1  10 4342 ABC1
2  20  100  JHT
3  22 5422  KLO

Considering these tho guys,

m1
#   CHR  POS
# 1  10 4342
# 2  20  100
# 3  22 5422
# 4  11   10

m2
#   CHR start  end Gene
# 1  10  4000 5999 ABC1
# 2  20    50  200  JHT
# 3  22  5000 6000  KLO
# 4  11  5000 6000  KLO

you can do:

merge(m1, m2) |> {\(.) subset(., data.table::between(.$POS, .$start, .$end))}()
#   CHR  POS start  end Gene
# 1  10 4342  4000 5999 ABC1
# 2  20  100    50  200  JHT
# 3  22 5422  5000 6000  KLO

Data:

m1 <- structure(list(CHR = c(10L, 20L, 22L, 11L), POS = c(4342L, 100L, 
5422L, 10L)), class = "data.frame", row.names = c(NA, -4L))


m2 <- structure(list(CHR = c(10L, 20L, 22L, 11L), start = c(4000L, 
50L, 5000L, 5000L), end = c(5999L, 200L, 6000L, 6000L), Gene = c("ABC1", 
"JHT", "KLO", "KLO")), class = "data.frame", row.names = c(NA, 
-4L))