Frequency of strings and their IDs in a dataframe using R

user_13005699 0
Viewed 144

The goal is to generate the frequency of a text variable and associate the corresponding IDs with it.

Suppose Sample is a dataframe as shown below:

Sample <- data.frame(ID = c('1', '2', '3', '4', '5', '6'), 
                        Var = c('How are you', 
                                 'Do not go', 
                                 'How are you', 
                                 'Please go',  
                                 'How are you',
                                 'Do not go'))

The following command generates the frequency of the strings in the column Var as follows:

as.data.frame(table(unlist(strsplit(tolower(Sample$Var), ', '))))

enter image description here

Is there a way to generate the associated IDs together in the table, say as?:

enter image description here

text-mining dataframe frequency aggregate r
4 Answers

Try this:

library(dplyr)
#Code
New <- Sample %>% group_by(Var) %>%
  summarise(Freq=n(),IDS=toString(ID))

Output:

# A tibble: 3 x 3
  Var          Freq IDS    
  <chr>       <int> <chr>  
1 Do not go       2 2, 6   
2 How are you     3 1, 3, 5
3 Please go       1 4
user_20808480

Here is another option if you apply data.table

> setDT(Sample)[, .(Freq = .N, ID.asso = list(ID)), keyby = Var]
           Var Freq ID.asso
1:   Do not go    2     2,6
2: How are you    3   1,3,5
3:   Please go    1       4
user_121587570

We can use dplyr with stringr

library(dplyr)
library(stringr)
Sample %>%
   group_by(Var) %>%
    summarise(Freq = n(), IDS = str_c(ID, collapse=", "))
user_37322710

Base R solution:

data.frame(do.call(rbind, lapply(with(Sample, split(Sample, Var)), function(x){
      with(x, data.frame(Var = unique(Var), Freq = nrow(x), ID = toString(ID)))
   }
  )
), row.names = NULL, stringsAsFactors = FALSE)
user_92031580
Related