how to create a list column of column names where column values are TRUE

user_1009730 0
Viewed 265

I've got some survey data for multiple-selection questions that has been output with a column for each possible selection, populated with TRUE/FALSE values, e.g.

library(dplyr)

dat <- tribble(
  ~name, ~state, ~onions, ~`sweet potatoes`, ~garlic,
  "Tom", "WV", TRUE, FALSE, TRUE,
  "Larry", "NC", FALSE, TRUE, FALSE,
  "Beth", "NY", TRUE, TRUE, TRUE
)

dat

#>   name  state onions `sweet potatoes` garlic
#>   <chr> <chr> <lgl>  <lgl>            <lgl> 
#> 1 Tom   WV    TRUE   FALSE            TRUE  
#> 2 Larry NC    FALSE  TRUE             FALSE 
#> 3 Beth  NY    TRUE   TRUE             TRUE

How can I create a list-column containing the column names for which the respondents answered "TRUE"?

simliar to https://stackoverflow.com/a/9508203/1009730

I've tried several variations on the theme of

dat %>%
  rowwise() %>%
  mutate(fav_foods = list(names(which(~ is.logical(.x) && .x))))

which gives the error:

Error: Problem with mutate() input fav_foods. x argument to 'which' is not logical i Input fav_foods is list(names(which(~is.logical(.x) && .x))). i The error occurred in row 1.

dplyr r
3 Answers

With the addition of purrr, you could do:

dat %>%
 mutate(fav_foods = pmap_chr(across(where(is.logical)), ~ toString(names(c(...))[which(c(...))])))

  name  state onions `sweet potatoes` garlic fav_foods                     
  <chr> <chr> <lgl>  <lgl>            <lgl>  <chr>                         
1 Tom   WV    TRUE   FALSE            TRUE   onions, garlic                
2 Larry NC    FALSE  TRUE             FALSE  sweet potatoes                
3 Beth  NY    TRUE   TRUE             TRUE   onions, sweet potatoes, garlic

Or if you need it as a list:

dat %>%
 mutate(fav_foods = pmap(across(where(is.logical)), ~ names(c(...))[which(c(...))]))

  name  state onions `sweet potatoes` garlic fav_foods
  <chr> <chr> <lgl>  <lgl>            <lgl>  <list>   
1 Tom   WV    TRUE   FALSE            TRUE   <chr [2]>
2 Larry NC    FALSE  TRUE             FALSE  <chr [1]>
3 Beth  NY    TRUE   TRUE             TRUE   <chr [3]>
user_59645570

The OP's code can be corrected with c_across without using any other external packages other than dplyr

library(dplyr)
out <- dat %>% 
       rowwise %>% 
       mutate(fav_foods = list(names(select(.,
            where(is.logical)))[c_across(where(is.logical))])) %>%
       ungroup 
 out
# A tibble: 3 x 6
  name  state onions `sweet potatoes` garlic fav_foods
  <chr> <chr> <lgl>  <lgl>            <lgl>  <list>   
1 Tom   WV    TRUE   FALSE            TRUE   <chr [2]>
2 Larry NC    FALSE  TRUE             FALSE  <chr [1]>
3 Beth  NY    TRUE   TRUE             TRUE   <chr [3]>
> out$fav_foods
[[1]]
[1] "onions" "garlic"

[[2]]
[1] "sweet potatoes"

[[3]]
[1] "onions"         "sweet potatoes" "garlic"

Or we can pivot to 'long' format, filter the name column and bind it to the original

library(dplyr)
library(tidyr)
dat %>%
  select(where(is.logical)) %>% 
  mutate(rn = row_number()) %>% 
  pivot_longer(cols = -rn) %>%        
  filter(value) %>% group_by(rn) %>%
  summarise(name1 = list(name), .groups = 'drop') %>% 
  select(name1) %>% 
  bind_cols(dat, .)

-output

# A tibble: 3 x 6
#  name  state onions `sweet potatoes` garlic name1    
#  <chr> <chr> <lgl>  <lgl>            <lgl>  <list>   
#1 Tom   WV    TRUE   FALSE            TRUE   <chr [2]>
#2 Larry NC    FALSE  TRUE             FALSE  <chr [1]>
#3 Beth  NY    TRUE   TRUE             TRUE   <chr [3]>
user_37322710

Using pivot_long then summarize

output <- dat %>%
  # get all the columns not name/state into variable & TRUE/FALSE values into values
  pivot_longer(cols = !one_of("name", "state"),
    names_to = "variable", values_to = "value") %>%
  # combined all variables that have values TRUE into variable_true
  # variables that have values FALSE into variable_false
  group_by(name, state) %>%
  arrange(variable) %>%
  summarize(variable_true = paste0(variable[value], collapse = ","),
    variable_false = paste0(variable[!value],collapse = ","),
    .groups = "drop")

Here is the output

# A tibble: 3 x 4
  name  state variable_true                variable_false  
  <chr> <chr> <chr>                        <chr>           
1 Beth  NY    garlic,onions,sweet potatoes ""              
2 Larry NC    sweet potatoes               "garlic,onions" 
3 Tom   WV    garlic,onions                "sweet potatoes"

Adnd if you want to have previous columns as well do a join with the original data

dat %>% left_join(output, by = c("name", "state"))

You got this

# A tibble: 3 x 7
  name  state onions `sweet potatoes` garlic variable_true                variable_false  
  <chr> <chr> <lgl>  <lgl>            <lgl>  <chr>                        <chr>           
1 Tom   WV    TRUE   FALSE            TRUE   garlic,onions                "sweet potatoes"
2 Larry NC    FALSE  TRUE             FALSE  sweet potatoes               "garlic,onions" 
3 Beth  NY    TRUE   TRUE             TRUE   garlic,onions,sweet potatoes ""
user_42096570
Related