R - comparing data between 2 data frames in R

Question

Let us assume we have 2 data-frames in R.

a = data.frame(col1 = round(runif(6,1,20)),col2 = c("a b c","b e z","a c q","a b","w","u o p l"), stringsAsFactors = F)
b = data.frame(col1 = 1:10, col2 = round(runif(10,1,10)), col3 = round(runif(10,10,20)), col4 = c(paste(letters[1:15], collapse=" "),paste(letters[10:25], collapse=" "),paste(letters[1:15], collapse=" "),paste(letters[1:19], collapse=" "),paste(letters[10:15], collapse=" "),paste(letters[1:15], collapse=" "),paste(letters[20:25], collapse=" "),paste(letters[1:15], collapse=" "),paste(letters[3:26], collapse=" "),paste(letters[1:2], collapse=" ")),stringsAsFactors = F)

The data sets are :

a
 col1    col2
   15   a b c
    8   b e z
   11   a c q
   15     a b
    5       w
   12 u o p l

b
col1 col2  col3                                           col4
1     1     10                   a b c d e f g h i j k l m n o
2     2     12                 j k l m n o p q r s t u v w x y
3     4     12                   a b c d e f g h i j k l m n o
4     4     16           a b c d e f g h i j k l m n o p q r s
5     2     13                                     j k l m n o
6     3     15                   a b c d e f g h i j k l m n o
7     1     12                                     t u v w x y
8     2     18                   a b c d e f g h i j k l m n o
9     4     16 c d e f g h i j k l m n o p q r s t u v w x y z
10    3     12                                             a b

I want to create a 3rd column col3 in data-frame "a" based on the following points:

1. a$col3 would consist of lists of serial numbers of values from col1 of data-frame "b".
2. Those values of b$col1 would be picked for which a$col1 falls in between the values of b$col2 & b$col3 for each row.
3. Also, the letters in a$col2 should be present in the b$col4. (Ordering is not required. eg. "a s" is equivalent to "s a".)

Final required dataset.

a
 col1    col2     col3
   15   a b c    4 6 8
    8   b e z   
   11   a c q      4 9
   15     a b    4 6 8
    5       w    2 7 9
   12 u o p l      2 9

A word of caution - For-loops solutions won't work as the data frames I work with are huge. (Have millions of rows). Any other method will be very much appreciated.

Thanks in advance.

Answer 1

Using the tidyverse (dplyr, stringr and purrr) you could do something like this...

 a2 <- b %>% mutate(col5=map2(col2,col3,~seq(.x,.y,1))) %>% #expand b to include all values between col2 and col3
  unnest() %>% 
  inner_join(a,by=c("col5"="col1")) %>% #match these against a col1
  filter(map2_lgl(col2.y,col4,~all(str_detect(.y,unlist(strsplit(.x," ")))))) %>% #filter by string matches
  group_by(col5,col2.y) %>% #group by original a columns
  summarize(col3=paste(sort(col1),collapse=" ")) %>% #collapse matching b col1 values
  right_join(a,by=c("col5"="col1","col2.y"="col2")) %>% #merge back into a
  rename(col1=col5,col2=col2.y) #restore column names

I get different dataframes due to your randomising process (as an aside, sample() is probably a better way of doing it than round(runif())), but here is what I ended up with...

> a
  col1    col2
1    7   a b c
2    5   b e z
3   10   a c q
4   14     a b
5    4       w
6    2 u o p l

> b
   col1 col2 col3                                            col4
1     1    4   11                   a b c d e f g h i j k l m n o
2     2   10   15                 j k l m n o p q r s t u v w x y
3     3    4   19                   a b c d e f g h i j k l m n o
4     4    8   13           a b c d e f g h i j k l m n o p q r s
5     5    7   13                                     j k l m n o
6     6    2   14                   a b c d e f g h i j k l m n o
7     7    8   11                                     t u v w x y
8     8    8   19                   a b c d e f g h i j k l m n o
9     9   10   19 c d e f g h i j k l m n o p q r s t u v w x y z
10   10    8   16                                             a b

> a2
# A tibble: 6 x 3
# Groups:   col1 [6]
   col1 col2    col3    
  <dbl> <chr>   <chr>   
1    7. a b c   1 3 6   
2    5. b e z   NA      
3   10. a c q   4       
4   14. a b     3 6 8 10
5    4. w       NA      
6    2. u o p l NA

Answer 2

Here is a possible solution. For me, after running the code to generate a and b, the datasets are as follows.

a

col1 col2
5    a b c
4    b e z
2    a c q
17   a b
8    w
17   u o p l

b

col1 col2 col3 col4
1    5    13   a b c d e f g h i j k l m n o
2    6    20   j k l m n o p q r s t u v w x y
3    8    17   a b c d e f g h i j k l m n o
4    3    17   a b c d e f g h i j k l m n o p q r s
5    7    12   j k l m n o
6    4    13   a b c d e f g h i j k l m n o
7    2    18   t u v w x y
8    7    14   a b c d e f g h i j k l m n o
9    4    18   c d e f g h i j k l m n o p q r s t u v w x y z
10   8    18   a b

First we use the fuzzjoin package to ensure the values of a$col1 fall between b$col2 and b$col3 (inclusive).

library(fuzzyjoin)
c <- fuzzy_inner_join(a, b,
                      by = c("col1" = "col2", "col1" = "col3"),
                      match_fun = list(`>=`, `<=`))

Next, thanks to this answer, we use

compare <- function(s1, s2) {
  c1 <- unique(strsplit(s1, "")[[1]])
  c2 <- unique(strsplit(s2, "")[[1]])
  length(intersect(c1,c2))/length(c1)
}

vcomp <- Vectorize(compare)
c <- transform(c, comp = vcomp(col2.x, col4))

we obtain an estimate of the percentage of characters in a$col2 which appear in b$col4.

Finally, we restrict to records where 100% of the characters match and also collapse b$col1 into a string separated by spaces. This is accomplished using the dplyr package.

library(dplyr)
d <- c %>%
  filter(comp >= 1) %>%
  select(col1.x, col2.x, col1.y) %>%
  group_by(col1.x, col2.x) %>%
  summarise(col3 = paste(col1.y, collapse = " "))
colnames(d) <- c("col1", "col2", "col3")

The final result is obtained in table d.

col1 col2    col3
5    a b c   1 4 6
8    w       2 7 9
17   a b     3 4 10
17   u o p l 2 9

Answer 3

The example data had randomness - always a good idea to use set.seed for reproducibility. So here's another dataset:

set.seed(1)
a = data.frame(col1 = round(runif(6,1,20)),col2 = c("a b c","b e z","a c q","a b","w","u o p l"), stringsAsFactors = F)
b = data.frame(col1 = 1:10, col2 = round(runif(10,1,10)), col3 = round(runif(10,10,20)), col4 = c(paste(letters[1:15], collapse=" "),paste(letters[10:25], collapse=" "),paste(letters[1:15], collapse=" "),paste(letters[1:19], collapse=" "),paste(letters[10:15], collapse=" "),paste(letters[1:15], collapse=" "),paste(letters[20:25], collapse=" "),paste(letters[1:15], collapse=" "),paste(letters[3:26], collapse=" "),paste(letters[1:2], collapse=" ")),stringsAsFactors = F)

> a
  col1    col2
1    6   a b c
2    8   b e z
3   12   a c q
4   18     a b
5    5       w
6   18 u o p l

> b
   col1 col2 col3                                            col4
1     1   10   17                   a b c d e f g h i j k l m n o
2     2    7   20                 j k l m n o p q r s t u v w x y
3     3    7   14                   a b c d e f g h i j k l m n o
4     4    2   18           a b c d e f g h i j k l m n o p q r s
5     5    3   19                                     j k l m n o
6     6    3   12                   a b c d e f g h i j k l m n o
7     7    7   17                                     t u v w x y
8     8    4   11                   a b c d e f g h i j k l m n o
9     9    8   13 c d e f g h i j k l m n o p q r s t u v w x y z
10   10    5   14                                             a b

First convert the strings into vectors:

a$col2_vec <- strsplit(a$col2, " ")
b$col4_vec <- strsplit(b$col4, " ")

Find all the rows that satisfy the "a$col1 is between b$col2 and b$col3".

btwn <- lapply(a$col1, function(x) which(b$col2 <= x & x <= b$col3))

Find all the rows that satisfy the "letters of a$col2 are in b$col4"

ltr_in <- lapply(a$col2_vec, 
                 function(y) which(sapply(b$col4_vec, 
                                          function(x) all(y %in% x))
                                  )
                )  

Find the intersections of rows and paste them up into a string.

a$col3 <- sapply(lapply(seq_along(btwn), 
                        function(i) intersect(btwn[[i]], ltr_in[[i]])), 
                 paste0, collapse=" ")

Result:

a$col2_vec <- NULL
> a
  col1    col2  col3
1    6   a b c 4 6 8
2    8   b e z      
3   12   a c q     4
4   18     a b     4
5    5       w      
6   18 u o p l     2

If one is concerned about a very large dataset, this seems to be quicker than the other answer, which is still very nice for learning purrr stuff. (Edit: added the third answer.)

Unit: milliseconds
           expr       min        lq      mean   median       uq       max neval
           @ngm  1.300393  1.412308  1.625972  1.45799  1.49936  14.94079   100
 @Andrew Gustar 18.630475 19.208137 19.825766 19.47883 20.09018  23.84303   100
      @radmuzon 57.647023 58.555243 64.455069 60.30342 62.77680 286.40073   100