Counting unique character values in grouped dataframe: dplyr::count(), stringr::str_count() and/or purrr::map()

Question

Building upon Error using dplyr::count() within purrr::map()

I want dataframes of counts unique character values by subsets of rows Full dataset is 1000+ rows, many tumour types

Toy example:

library(tidyverse)

df <- tibble::tribble(
  ~tumour, ~impact.on.surgery, ~impact.on.radiotherapy, ~impact.on.chemotherapy, ~impact.on.biologics, ~impact.on.immunotherapy,
  'Breast', NA,               NA,               NA,               'Interrupted',      NA,               
  'Breast', NA,               NA,               NA,               'As.planned',       NA,               
  'Breast', NA,               NA,               NA,               'Interrupted',      NA,               
  'Breast', NA,               NA,               'As.planned',     NA,                NA,               
  'Breast', NA,               NA,               NA,               NA,               NA,               
  'Breast', NA,               NA,               NA,               'Interrupted',      NA             

> df
# A tibble: 6 x 6
  tumour impact.on.surgery impact.on.radiotherapy impact.on.chemotherapy impact.on.biologics impact.on.immunotherapy
  <chr>  <lgl>             <lgl>                  <chr>                  <chr>               <lgl>                  
1 Breast NA                NA                     NA                     Interrupted         NA                     
2 Breast NA                NA                     NA                     As.planned          NA                     
3 Breast NA                NA                     NA                     Interrupted         NA                     
4 Breast NA                NA                     As.planned             NA                  NA                     
5 Breast NA                NA                     NA                     NA                  NA                     
6 Breast NA                NA                     NA                     Interrupted         NA                     
)

Desired output: Ideally as a named list of dataframes by tumour type, so I can then later reduce(bind_rows, .id = 'tumour') appending a .id column label

$ Breast
# A tibble: 2 x 6
  impact      impact.on.surgery impact.on.radiotherapy impact.on.chemotherapy impact.on.biologics impact.on.immunotherapy
  <chr>                   <dbl>                  <dbl>                  <dbl>               <dbl>                   <dbl>
1 Interrupted                 0                      0                      0                   3                       0
2 As.planned                  0                      0                      1                   1                       0

Tried so far:

# Gets single row tibble, but not sure how to `.id` label each row, map across all values & bind
df %>%   
  summarise(across(starts_with('impact'), ~sum(str_count(.x, 'As.planned'), na.rm = T)))

# A tibble: 1 x 5
  impact.on.surgery impact.on.radiotherapy impact.on.chemotherapy impact.on.biologics impact.on.immunotherapy
              <int>                  <int>                  <int>               <int>                   <int>
1                 0                      0                      1                   1                       0

# ?Counts all variable values (no need to specify), simpler code, but also counts `NAs` and I can't pivot that to a wide form as it has 'counted' the tumour
df %>% 
  map_dfr(~count(data.frame(x=.), x), .id = 'var')

                      var           x n
1                  tumour      Breast 6
2       impact.on.surgery        <NA> 6
3  impact.on.radiotherapy        <NA> 6
4  impact.on.chemotherapy  As.planned 1
5  impact.on.chemotherapy        <NA> 5
6     impact.on.biologics  As.planned 1
7     impact.on.biologics Interrupted 3
8     impact.on.biologics        <NA> 2
9 impact.on.immunotherapy        <NA> 6

For the resultant dataframe I want each row to the sum/count of occurences of that value within each column. Eg in toy datastet, unique character values are 'As.planned' and 'Interrupted' - I want each row to be a count of occurrences of each value per column — Brent, Sep 13 '20 at 22:40

akrun · Accepted Answer · 2020-09-13T23:00:45.000

An option with map would be to loop over the elements to be counted i.e. "Interrupted", "As.planned", then use summarise across the columns that starts_with prefix names 'impact' after grouping by 'tumour', get the frequency count by taking the sum of logical vector in each column

library(dplyr)
library(purrr)
library(stringr)
map_dfr(dplyr::lst('Interrupted', 'As.planned'), ~
     df %>%
        group_by(tumour) %>% 
        summarise(across(starts_with('impact'), function(x)
             sum( x == .x, na.rm = TRUE)), .groups = 'drop'), .id = 'impact') %>%
     mutate(impact = str_remove_all(impact, '"'))
# A tibble: 2 x 7
#  impact      tumour impact.on.surgery impact.on.radiotherapy impact.on.chemotherapy impact.on.biologics impact.on.immunotherapy
#  <chr>       <chr>              <int>                  <int>                  <int>               <int>                   <int>
#1 Interrupted Breast                 0                      0                      0                   3                       0
#2 As.planned  Breast                 0                      0                      1                   1                       0

Or to avoid getting the quotes around the values, use setNames instead of lst

map_dfr(setNames(c('Interrupted', 'As.planned'),
       c('Interrupted', 'As.planned')),  ~
      df %>%
        group_by(tumour) %>% 
         summarise(across(starts_with('impact'), function(x)
              sum( x == .x, na.rm = TRUE)), .groups = 'drop'), .id = 'impact')

Or using base R

lst1 <- lapply(c("Interrupted", "As.planned"), 
    function(x) aggregate(.~ tumour, df, FUN = function(y)
    sum(y == x, na.rm = TRUE), na.action = NULL))

data.frame(impact = c("Interrupted", "As.planned"), do.call(rbind, lst1))
#     impact tumour impact.on.surgery impact.on.radiotherapy impact.on.chemotherapy impact.on.biologics impact.on.immunotherapy
#1 Interrupted Breast                 0                      0                      0                   3                       0
#2  As.planned Breast                 0                      0                      1                   1                       0

Fab, that gets the counts in a usable format - how to id that whole (test) dataframe as a list item of tumour type `'Breast'` so on full data I can count within a tumour type only, then `reduce(bind_rows)` back into full df? — Brent, Sep 13 '20 at 22:50
Great! I can remove the characters `"/"` surrounding each `impact` with `gsub` or `str_remove_all` etc, but is there a way to stop them popping up from the `lst` call in the first place? — Brent, Sep 13 '20 at 22:58
@Brent I got your query. It is just that `lst` creates that quote. You can do `setNames` — akrun, Sep 13 '20 at 23:03

Counting unique character values in grouped dataframe: dplyr::count(), stringr::str_count() and/or purrr::map()

1 Answers1

Linked