R - Extract the matched string, split into multiple columns which are matched by a dictionary vector

Question

I want to extract specific string of favor column in target data which is matched by a dictionary. Here is my data:

dictionary <- c("apple", "banana", "orange", "grape")

target <- data.frame("user" = c("A", "B", "C"),
                     "favor" = c("I like apple and banana", "grape and kiwi", "orange, banana and grape are the best"))
target
  user                                 favor
1    A               I like apple and banana
2    B                        grape and kiwi
3    C orange, banana and grape are the best

And below is my expected outcome result. I want to automatically create the column based on the most favors I matched in dictionary(in my case, 3), and extract the string I match in dictionary.

result <- data.frame("user" = c("A", "B", "C"), 
                     "favor_1" = c("apple", "grape", "orange"), 
                     "favor_2" = c("banana", "", "banana"), 
                     "favor_3" = c("", "", "grape"))
result

  user favor_1 favor_2 favor_3
1    A   apple  banana        
2    B   grape                
3    C  orange  banana   grape

Any help will be thankful.

Possible duplicate of [Split a column of a data frame to multiple columns](https://stackoverflow.com/questions/4350440/split-a-column-of-a-data-frame-to-multiple-columns) — Paul Endymion, Apr 30 '18 at 14:03

score 3 · Answer 1 · answered Apr 30 '18 at 14:16

3

# Remove all words from `target$favor` that are not in the dictionary
result <- lapply(strsplit(target$favor, ',| '), function(x) { x[x %in% dictionary] })
result
# [[1]]
# [1] "apple"  "banana"
# 
# [[2]]
# [1] "grape" 
# 
# [[3]]
# [1] "orange" "banana" "grape" 

# Fill in NAs when the rows have different numbers of items
result <- lapply(result, `length<-`, max(lengths(result)))

# Rebuild the data.frame using the list of words in each row
cbind(target[ , 'user', drop = F], do.call(rbind, result))
#   user      1      2     3
# 1    A  apple banana  <NA>
# 2    B  grape   <NA>  <NA>
# 3    C orange banana grape

Note that I read in target with stringsAsFactors = FALSE so that strsplit can work.

answered Apr 30 '18 at 14:16

C. Braun

5,061
19
47

1

Optionally, the first `lapply` could be something like: `lapply(target$favor, function(x) regmatches(x, gregexpr(paste(dictionary, collapse = "|"), x)))` so you don't have to remove strings. – Mike H. Apr 30 '18 at 14:24
Also if you wanted you could use `data.frame` in the assignment like: `target[, c("favor_1", "favor_2", 3)] <- data.frame(do.call(rbind, result))`. Don't know if its more efficient, but just an option. Overall nice answer! – Mike H. Apr 30 '18 at 14:25

score 1 · Accepted Answer · answered Apr 30 '18 at 14:17

Your best bet is probably to apply str_extract_all to each row.

library(stringr)
result <- t(apply(target, 1,
                  function(x) str_extract_all(x[['favor']], dictionary, simplify = TRUE)))

     [,1]    [,2]     [,3]     [,4]   
[1,] "apple" "banana" ""       ""     
[2,] ""      ""       ""       "grape"
[3,] ""      "banana" "orange" "grape"

R - Extract the matched string, split into multiple columns which are matched by a dictionary vector

2 Answers2

Linked