How to concatenate row values to a new column based on other columns?

user_13874036 0
Viewed 37

I'm trying to create a new column whose value is a concatenated value of two or more row values.

QID     Text                               Type
1       Please answer yes or no        Question         
1       Yes                             Choices         
1       No                              Choices
2       Please answer true or false    Question         
2       True                            Choices         
2       False                           Choices

I'd like to convert this to

QID     Text                             Choices
1       Please answer yes or no          Yes; No
2       Please answer true or false      True; False

I've tried unite with group_by and pivot_wider but I haven't able to figure out the right approach.

purrr tidyr dplyr r
1 Answers

How about this:

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(stringr)
dat <- tibble::tribble(
  ~QID,     ~Text,                               ~Type,
1,       "Please answer yes or no",        "Question",         
1,       "Yes",                             "Choices",         
1,       "No",                              "Choices",
2,       "Please answer true or false",    "Question",         
2,       "True",                            "Choices",         
2,       "False",                           "Choices")

dat %>% 
  group_by(QID) %>% 
  mutate(Choices = str_c(Text[which(Type== "Choices")], collapse="; ")) %>% 
  slice_head(n=1) %>% 
  select(-Type)
#> # A tibble: 2 × 3
#> # Groups:   QID [2]
#>     QID Text                        Choices    
#>   <dbl> <chr>                       <chr>      
#> 1     1 Please answer yes or no     Yes; No    
#> 2     2 Please answer true or false True; False

Created on 2022-06-27 by the reprex package (v2.0.1)

user_82064340
Related