Assign another column when text matching

Question

I want to assign another column when a keyword matches a word in the text, assign the value one. If multiple of the same word is in the text, only assign the maximum value of 1 or 0 otherwise.

Let's say I have this dataset:

df = structure(list(text = c("I hate good cheese", "cheese that smells is the best",
                             "isn't it obvious that green cheese serves you well", 
                             "don't fight it just eat the cheese", "the last good cheese is down"), 
                    stuff = c(3, 2, 40, 4, 5) ), row.names = c(NA, 5L), 
               class = c("tbl_df", "tbl", "data.frame"))

with the following keywords to search for:

keywords = structure(list(keyword_one = c("cheese", "blue", "best"),
                          keyword_two = c("smells", "final", 'south')
                          ),
                     row.names = c(NA, -3L),   
                     class = c("tbl_df", "tbl", "data.frame"))

I can do the following:

df[str_detect(df$text, keywords$keyword_one),]

to return the rows where the keyword matches but how do I just get all the rows but assign the value one when it matches? so something like:

# A tibble: 5 × 2
  text                                               stuff    keyword1    keyword 2
*                                               
1 I hate good cheese                                     3        1         0
2 cheese that smells is the best                         2        0         1
3 isn't it obvious that green cheese serves you well    40        0         0
4 don't fight it just eat the cheese                     4        1         0
5 the last good cheese is down                           5        0         0

Alternatively, I found that I could do:

ifelse(str_detect(df$text, keywords$keyword_one), 1, 0)
ifelse(str_detect(df$text, keywords$keyword_two), 1, 0)

However it's inefficient if I have many columns in keyword and wanted to iterate over all of these.

Furthermore, I have noticed that str_detect seems to not detect the word cheese in all of the texts` why is this happening?

user_6843745 · Answer

why are you losing "cheese"?

If I'm interpreting your question correctly, you want to match any of the words in a keyword column.

But a lot of R functions are vectorized so when you str_detect(df$text, keywords$keyword_one) it compares vectors element by element and recycles the shorter vector. R gives you the warning

Warning message: In stri_detect_regex(string, pattern, negate = negate, opts_regex = opts(pattern)) : longer object length is not a multiple of shorter object length

What you want is str_detect(df$text, "cheese|blue|best") which will return TRUE if you see the strings "cheese", "blue" or "best".

solution

The following code should work for an arbitrary number of keywords. It requires dplyr in addition to stringr. The trick is to use pivots so that the data is "tidy" for each given coding move.

# First reorganize the keywords as suggested above

  keys <- keywords %>%
    summarize_all(function(x) str_c(x, collapse ="|")) %>%
    pivot_longer(everything())

> key 
# A tibble: 2 × 2
  name        value             
                      
1 keyword_one cheese|blue|best  
2 keyword_two smells|final|south

# now for each set of keywords test to see if they're in the `text` column.

  df %>% 
    crossing(keys)  %>%
    mutate(observed = 1*str_detect(text, value)) %>%
    select(-value) %>%
    pivot_wider(values_from = observed)

And you get:

# A tibble: 5 × 4
  text                                               stuff keyword_one keyword_two
                                                              
1 cheese that smells is the best                         2           1           1
2 don't fight it just eat the cheese                     4           1           0
3 I hate good cheese                                     3           1           0
4 isn't it obvious that green cheese serves you well    40           1           0
5 the last good cheese is down                           5           1           0

Assign another column when text matching

1 Answers

why are you losing "cheese"?

solution