How to find search words from a table, in another table, and then create new columns of the results?

Question

I'm trying to find specifice words listed in a tibble arbeit in the another tibble rawEng$Text. If a word, or words, were found, I want to create, or mutate, a new data frame iDataArbeit with two new columns, one for the found word/s wArbeit, and one for the sum of there tf-idf iArbeitscores from arbeit$tfidf

My Data:

arbeit:

     X1 feature                   tfidf
  <dbl> <chr>                     <dbl>
1     0 sick                      0.338
2     2 contract                  0.188
3     3 pay                       0.175
4     4 job                       0.170
5     5 boss                      0.169
6     6 sozialversicherungsnummer 0.169

rawEng:

Gender Gruppe        Datum               Text                                            
  <chr>  <chr>         <dttm>              <chr>                                           
1 F      Berlin Expats 2017-07-07 00:00:00 Anyone out there who's had to apply for Führung~
2 F      FAB           2018-01-18 00:00:00 Dear FAB, I am in need of a Führungszeugnis no ~
3 M      Free Advice ~ 2017-01-30 00:00:00 Dear Friends, i would like to ask you how can I~
4 M      FAB           2018-04-12 00:00:00 "Does anyone know why the \"Standesamt Pankow (~
5 F      Berlin Expats 2018-11-12 00:00:00 having trouble finding consistent information a~
6 F      Toytown Berl~ 2017-06-08 00:00:00 "Hello\r\n\r\nI have a question regarding Airbn~

I've tried with dplyr::mutate, using this code:

idataEnArbeit <- mutate(rawEng, wArbeit = ifelse((str_count(rawEng$Text, arbeit$feature))>=1,
                                                       arbeit$feature, NA),
                        iArbeit = ifelse((str_count(rawEng$Text, arbeit$feature))>=1,
                                         arbeit$tfidf, NA))

but all I get is one Word, and it's tf-idf score, in the new columens iDatatArbeit$wArbeitand iDataArbeit$iArbeit

Gender Gruppe          Datum               Text                           wArbeit iArbeit
  <chr>  <chr>           <dttm>              <chr>                          <chr>     <dbl>
1 F      Berlin | Girl ~ 2018-09-11 13:22:05 "11 septembre, 13:21     GGI ~ sick      0.338
2 F      ExpatBabies Be~ 2017-10-19 16:24:23 "16:24   Babysitter needed! B~ sick      0.338
3 F      Berlin | Girl ~ 2018-06-22 18:24:19 "gepostet.       Leonor Valen~ sick      0.338
4 F      'Neu in Berlin' 2018-09-18 23:19:51 "Hello guys, I am working wit~ sick      0.338
5 M      Free Advice Be~ 2018-04-27 08:49:24 "In need of legal advice: Wha~ sick      0.338
6 F      Free Advice Be~ 2018-07-04 18:33:03 "Is there somebody I can pay ~ sick      0.338

In summary: I want all words from arbeit$feature which are found in rawEng$Text to be added in iDataArbeit$wArbeit, and the sum of there tf-idf score to be added in iDataArbeit$iArbeit

Don't use `$` inside `dplyr` functions to refer to columns in that data frame. It's also going to be difficult for others to work with your data in this format; try to make it [reproducible](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) like using `dput` — camille, Aug 26 '19 at 15:26
@camille Thanks for pointing this out. I'll definitely work more on my question formulation in the future. — dataminor, Aug 26 '19 at 20:13

Amit Kohli · Accepted Answer · 2019-08-26T16:36:21.383

Since I don't have your data, I'll import the gutenbergr library and play w/ Treasure Island.

library(tidytext)
library(gutenbergr)

## Now get the dataset
Treasure_Island <- gutenberg_works(title == "Treasure Island") %>% pull(gutenberg_id) %>% 
  gutenberg_download(.)

## and construct a toy arbeit:
arbeit <- data.frame(feature = c("island", "treasure", "to"),
                     tfidf = c(0.3,0.5,0.6))

## Break up a word into it's components (the head is just to keep the example short... you omit)
tidy_treasure <- unnest_tokens(Treasure_Island, feature, text, drop = FALSE) %>% 
  head(500)

## now bring the tfidf into tidy_treasure
df <- left_join(tidy_treasure, arbeit, by = "feature")

## and now you can average by sentence normally.
## To get the words we have to throw out the words that don't contribute to our tfidf.

## Two options:
df %>% filter(!is.na(tfidf)) %>% group_by(text) %>% summarize(AveTFIDF = sum(tfidf, na.rm = TRUE),
                                    Words = paste(feature, collapse = ";"))  

## Or if you want to keep a row for each found word, we can't use summarize, but we can still add them all up.
df %>% filter(!is.na(tfidf)) %>% group_by(text) %>% mutate(AveTFIDF = sum(tfidf, na.rm = TRUE))

Thanks for the reply. This almost solved my problem. I tested your code with your, and then my data. I spoted 2 issues: 1- `df` from your code gives the mean of `tfidf`, and I need the sum. 2- The last line of code creates a data frame that doesn't include the found words, and I need these words, also. How can I solve these issues? — dataminor, Aug 26 '19 at 16:27
The first option is exactly what I'm looking for! I tested it with my data and it worked great! Thank you very much @AmitKohli — dataminor, Aug 26 '19 at 19:56

How to find search words from a table, in another table, and then create new columns of the results?

1 Answers1