Qfyilyi

I have two dataframes.
df1 looks like (or the column of df1 i am interested in):

position

2

6

12

18

25

31

and df2 looks like:

start   end

2       17

24      29

I want to keep the positions in df1 that only fall between (<= or >=) the start and end coordinates of df2, so that df1 looks like this after filtering:

position

2

6

12

25

Then I want to keep the filtered out "leftover" values of df1 in another dataframe, let's call it df4.

df4 would look like:

position

18

31

I can do this the perl way using a for loop (coming from perl and currently learning R) but I am pretty sure I can somehow use filter or some other dplyr or base R combination of functions to achieve this.

Any help would be appreciated!

EDIT: Added df4 calculation as my question was marked as duplicate and this is sth not found in the other similar threads. This is something I am interested in doing to make my code faster!

We can full_join these two data frames and then filter for the rows within the start and end column. The Flag column in the example is just for the join. Finally, we can use distinct to remove duplicated rows.

library(dplyr)



df3 <- df1 %>%

  mutate(Flag = 1) %>%

  full_join(df2 %>% mutate(Flag = 1), by = "Flag") %>%

  filter(position >= start, position <= end) %>%

  distinct(position)

df3

#   position

# 1        3

# 2        6

# 3       12

# 4       25

DATA

df1 <- read.table(text = "position

3

                  6

                  12

                  18

                  25

                  31", header = TRUE)



df2 <- read.table(text = "start   end

2       17

24      29",

                  header = TRUE)

Single line, simple base solution:

df1[df1$position %in% unlist(apply(df2,1,function(x) x["start"]:x["end"])),]

The apply simply generates a vector of all the cases that fall between starts and ends.

Here is a base R option

do.call(rbind, Map(function(i, j) 

  df1[df1$position > i & df1$position < j, , drop = FALSE], 

      df2$start, df2$end))

#    position

#1        3

#2        6

#3       12

#5       25

Or using fuzzy_join

library(fuzzyjoin)

library(dplyr)

fuzzy_inner_join(df1, df2, by = c('position' = 'start', 'position' = 'end'), 

        match_fun = list(`>`, `<`)) %>%

    select(position)

#  position

#1        3

#2        6

#3       12

#4       25

Or use a non-equi join from data.table

setDT(df2)[df1, on = .(start < position, end > position), .(position), nomatch = 0]

#   position

#1:        3

#2:        6

#3:       12

#4:       25

data

df1 <- structure(list(position = c(3L, 6L, 12L, 18L, 25L, 31L)), row.names = c(NA, 

 -6L), class = "data.frame")



df2 <- structure(list(start = c(2L, 24L), end = c(17L, 29L)), 

 class = "data.frame", row.names = c(NA, -2L))

base R solution (no packages needed)

keepRows<-

sapply(df1$position, function(x_o) {

    any(apply(df2, 1, function(x) {x_o => x[1] & x_o <= x[2]}))

})



df1[keepRows,, drop = FALSE]

Result:

#  position

#1        3

#2        6

#3       12

#5       25

Please note:

• This is basically a double loop, I don't know how else to solve this with base::.




• Should the border values be included? You are very vague with "between". Currently, I don't include them. You can change that using <=, >=.

To get the "leftover" use negation:

df1[!keepRows,, drop = FALSE]

Here is another take that starts with df2 (I don't say this is wiser than Andre's approach):

subset(df1, apply(apply(df2, 1, function (x) {dplyr::between(df1$position, x["start"], x["end"])}), 1, any))

You should probably run some benchmarks on the proposed approaches before making a decision.