count the number of text in r

user_9209203 0
Viewed 145

I have a dataset like df. I want to count the text occurrence for each bars_by_foo and create my.column. many thanks in advance.

 data <- data.frame(foo=c(1, 1, 1, 2, 2, 3, 3, 3), bar=c('a', 'a', 'b', 'a', 'b', 'b', 'c', 'd'))
data
agg <- aggregate(bar~foo, data = data, paste0, collapse="")
df <- merge(data, agg, by = "foo", all = T)
colnames(df) <- c(colnames(data), "bars_by_foo") # optional
df

Expected Answer

 foo bar bars_by_foo  my.column
   1   a         aab     a(2), b(1)
   1   a         aab     a(2), b(1)
   1   b         aab     a(2), b(1)
   2   a          ab     a(1), b(1)
   2   b          ab     a(1), b(1)
   3   b         bcd     b(1), c(1), d(1)
   3   c         bcd     b(1), c(1), d(1)
   3   d         bcd     b(1), c(1), d(1)
tidyr tidyverse data-manipulation r
1 Answers

We can do a group by paste

  1. add_count - create a frequency column based on 'foo', 'bar' columns
  2. Paste the 'bar' elements together after grouping by 'foo' - 'bars_by_foo'
  3. Use sprintf to format the 'bar', 'n' columns into a single string, get the unique elements and paste with toString
library(dplyr)
library(stringr)
data %>%
    add_count(foo, bar) %>%
    group_by(foo) %>%
    mutate(bars_by_foo = str_c(bar, collapse=""),
      my.column = toString(unique(sprintf('%s(%d)', bar, n)))) %>%
    ungroup

-output

# A tibble: 8 x 5
    foo bar       n bars_by_foo my.column       
  <dbl> <chr> <int> <chr>       <chr>           
1     1 a         2 aab         a(2), b(1)      
2     1 a         2 aab         a(2), b(1)      
3     1 b         1 aab         a(2), b(1)      
4     2 a         1 ab          a(1), b(1)      
5     2 b         1 ab          a(1), b(1)      
6     3 b         1 bcd         b(1), c(1), d(1)
7     3 c         1 bcd         b(1), c(1), d(1)
8     3 d         1 bcd         b(1), c(1), d(1)
user_37322710
Related