Detect string pattern in dataframe and conditionally fill another in R

user_10734443 0
Viewed 197

I have a dataframe containing text and numeric references, and a vector of words that may appear in the text. What I want is to check for every instance in which a word from words_df appears in text_df$text, and record the word from word_df and the numeric reference from text_df$ref in a new dataframe (edge_df).

text_df <- data.frame(text = c("John went to the shops", "Sarita hates apples", "Wendy doesn't care about this"),
                      ref = c("13.5", "1.9.9", "20.1"))

words_df <- data.frame(word = c("shops", "John", "apples", "Wendy", "this"))

edge_df <- data.frame(ref = NA, word = NA)

The output should look like this:

> edge_df
    ref   word
1  13.5  shops
2  13.5   John
3 1.9.9 apples
4  20.1  Wendy
5  20.1   this

It isn't very elegant but I thought a for-loop would work, where each word is checked against the text using stringr::str_detect, and if the result is TRUE it would record the word and ref:

for (i in 1:nrow(text_df)) {
  for (j in 1:nrow(words_df)) {
    if (str_detect(text_df$text[i], words_df$word[j]) == TRUE) {
      edge_df$ref <- text_df$ref[i]
      edge_df$word <- words_df$word[j]
    }
  }
}

This did not work, and nor have several variations on this loop. If possible I would rather not use a loop at all as the dataframes I'm working with have around 1000 rows each and it takes far too long to loop through them. Any fixes to the loop much appreciated, and bonus points/props if you can do it without a loop at all.

Thank you!

stringr r for-loop loops
4 Answers

Try this tidyverse approach. The key for your issue: you can format your data to long by separating each word in the sentences and then use left_join(). Here the code (I have used the data you provided):

library(tidyverse)
#Data
text_df <- data.frame(text = c("John went to the shops", "Sarita hates apples", "Wendy doesn't care about this"),
                      ref = c("13.5", "1.9.9", "20.1"),stringsAsFactors = F)

words_df <- data.frame(word = c("shops", "John", "apples", "Wendy", "this"),stringsAsFactors = F)
#Join
words_df %>% left_join(text_df %>% separate_rows(text,sep = ' ') %>%
  rename(word=text))

Output:

    word   ref
1  shops  13.5
2   John  13.5
3 apples 1.9.9
4  Wendy  20.1
5   this  20.1
user_20808480

Here is an option with str_extract and unnest. We extract the words from the 'text' column into a list and use unnest the expand the rows

library(dplyr)
library(stringr)
library(tidyr)
text_df %>%
   transmute(ref, word = str_extract_all(text, 
                 str_c(words_df$word, collapse="|"))) %>%
   unnest(c(word))
# A tibble: 5 x 2
#  ref   word  
#  <chr> <chr> 
#1 13.5  John  
#2 13.5  shops 
#3 1.9.9 apples
#4 20.1  Wendy 
#5 20.1  this
user_37322710

Here is a base R option

u <- lapply(text_df$text,function(x) words_df$word[sapply(words_df$word,function(y) grepl(y,x))])
edge_df <- data.frame(ref = rep(text_df$ref,lengths(u)),word = unlist(u))

which gives

    ref   word
1  13.5  shops
2  13.5   John
3 1.9.9 apples
4  20.1  Wendy
5  20.1   this
user_121587570 
library(data.table)

words_df <- data.frame(word = c("shops", "John", "apples", "Wendy", "this"))

text_df <- data.frame(text = c("John went to the shops", 
                      "Sarita hates apples", "Wendy doesn't care about this"),
                  ref = c("13.5", "1.9.9", "20.1"))

setDT(words_df)
setDT(text_df)

First we get our words vector ready.

 wordvec <- paste0(words_df[,word],collapse="|")

Now all there is to do is to check, for each row all the words in wordvec

   ## > text_df[,.(word=unlist(regmatches(text,gregexpr(wordvec,text)))),ref] 
##      ref   word
## 1:  13.5   John
## 2:  13.5  shops
## 3: 1.9.9 apples
## 4:  20.1  Wendy
## 5:  20.1   this

The functions regmatches,grepexpr will return a list containing all the words that match the pattern wordvec.

> regmatches("John went to the shops",gregexpr(wordvec,"John went to the shops"))
##[[1]]
##[1] "John"  "shops"

Warning, to format the output quickly I'm over-relying the ref variable and consider them to be ids. If it is not the case then it is best to create an id column and use it with in addition to ref. For instance

text_df[,id:=1:.N][,.(word=unlist(regmatches(text,
                   gregexpr(wordvec,text)))),.(id,ref)]
user_20814270
Related