search number falls in between range of numbers

Question

I have these tables below that consists of a million rows and here i want to see if there is an overlap between Table 1 Target start- table 2 Target start, and so on with target end. How can i achieve this in R?

Table 1 Target start and Target ends (numerics)

TargetStart TargetEnd
7756        8357
35598       35009
9954126     9954371
9954126     9954346
9954126     10115435

Table2 Target start and Target ends (numerics)

Target_Start  Target_End        
7000           10000        
23184775       23190099 
900000         1000000      
23157928       23165621 
23157410       23158724

Desired table would consist of the status overlaps

Target_Start  Target_End    Overlaps
7000          10000         yes
23184775      23190099      no
90000         1000000       yes
23157928       23165621     no
23157410      23158724      no

Please anyone can you guys help me in this? thanks

p/s: i made some correction on the typo i made previously

https://stackoverflow.com/questions/16905425/find-duplicate-values-in-r — Gaterde, Apr 23 '18 at 13:59
While trying to solve such a problem, you must know if your tables are sorted and if there is a possibility that Target_End[tuple]>Target_Start[next_tuple]. — Dominique, Apr 23 '18 at 14:23
what do you mean by overlap here? how are you calculating that? — Dhawal Kapil, Apr 23 '18 at 14:52
im sorry but what i mean is that whether the value in table 1 is within the range in table 2. overlaps means the value is the same or within that range.. — Budakbarubelajar, Apr 23 '18 at 15:04

score 0 · Answer 1 · answered Apr 23 '18 at 15:39

0

You can use data.table::inrange like this - note that you most likely have a typo in Table 1 and the result you expected in Desired Table based on the actual inputs is incorrect

cbind(tbl1, tbl2) %>%
  mutate(Overlaps = as.logical(pmax(data.table::inrange(TargetStart, Target_Start, Target_End), data.table::inrange(TargetEnd, Target_Start, Target_End))))

  # TargetStart TargetEnd Target_Start Target_End Overlaps
# 1        7756      8357         7000      10000     TRUE
# 2       35598     35009     23184775   23190099    FALSE
# 3     9954126   9954371        90000     100000    FALSE
# 4     9954126   9954346     23157928   23165621    FALSE
# 5     9954126  10115435     23157410   23158724    FALSE

Data

tbl1 <- read.table(text="TargetStart TargetEnd
7756        8357
35598       35009
9954126     9954371
9954126     9954346
9954126     10115435", header=TRUE)

tbl2 <- read.table(text="Target_Start  Target_End        
7000           10000        
23184775       23190099 
90000          100000       
23157928       23165621 
23157410       23158724", header=TRUE)

answered Apr 23 '18 at 15:39

CPak

13,260
3
30
48

Thank you for your reply but the data is as it is and is so random , i know. lol. – Budakbarubelajar Apr 23 '18 at 16:05
Can you look at row 3 - the answer should be `no` but you indicated it should be `yes` – CPak Apr 23 '18 at 16:07
yup, my bad,it was a typo.That was one that i typed to show how it should be -without that typo of course. so about your answer, cbind(tbl1, tbl2) %>% mutate(Overlaps = as.logical(pmax(data.table::inrange(TargetStart, Target_Start, Target_End), data.table::inrange(TargetEnd, Target_Start, Target_End)))) -those should be in one line? – Budakbarubelajar Apr 23 '18 at 16:12
I am sorry im a newbie in programming and R – Budakbarubelajar Apr 23 '18 at 16:13
It can be one line or separated by a newline like `cbind(...,...) %>% \newline mutate(...)` – CPak Apr 23 '18 at 18:28
I found the error that says **Error: could not find function "%>%"** why is that happened? – Budakbarubelajar Apr 24 '18 at 09:04
Load the tidyverse: `library(tidyverse)` – CPak Apr 24 '18 at 12:20

search number falls in between range of numbers

1 Answers1