Splitting string column into many using tidyverse

Question

I have a string column with commas. I want to convert this single column into multiple labeled columns, with values filled in appropriately. The output data frame would have 3 columns (A, B, and C). Row 1 would have columns A and B filled in with "Yes", and C with "No". Row 2 would have all 3 columns filled with "Yes", etc.

df1 <- data.frame(X= c("A, B", "A, B, C", "A", "A, C"))

df1
        X
1    A, B
2 A, B, C
3       A
4    A, C

Required Output

A    B    C
Yes  Yes  No
Yes  Yes  Yes
Yes  No   No
Yes  No   Yes

Any hint, please.

[Dummy variables from a string variable](https://stackoverflow.com/questions/16267552/dummy-variables-from-a-string-variable) — Henrik, Jan 12 '19 at 18:49

score 4 · Accepted Answer · answered Jan 12 '19 at 18:04

Something like this:

library(tidyverse)

df1 %>%
  mutate(id = row_number()) %>% 
  separate_rows(X) %>% 
  group_by(id) %>% 
  mutate(Y = "yes") %>% 
  spread(X, Y, fill = "no")

# A tibble: 4 x 4
# Groups:   id [4]
     id A     B     C    
  <int> <chr> <chr> <chr>
1     1 yes   yes   no   
2     2 yes   yes   yes  
3     3 yes   no    no   
4     4 yes   no    yes

score 3 · Answer 2 · answered Jan 12 '19 at 18:25

Here is an option using base R with table. We split the 'X' column by , into a list of vectors, convert it to a two column data.frame with stack, get the frequency with table and convert it to logical

table(stack(setNames(strsplit(as.character(df1$X), ", +"), 
                    seq_len(nrow(df1))))[2:1]) > 0
 #   values
#ind    A     B     C
#  1 TRUE  TRUE FALSE
#  2 TRUE  TRUE  TRUE
#  3 TRUE FALSE FALSE
#  4 TRUE FALSE  TRUE

Rich Scriven · Answer 3 · 2019-01-12T19:05:25.950

3

Via stringi

stringi::stri_split_fixed(df1$X, ", ", simplify = TRUE) != ""
#      [,1]  [,2]  [,3]
# [1,] TRUE  TRUE FALSE
# [2,] TRUE  TRUE  TRUE
# [3,] TRUE FALSE FALSE
# [4,] TRUE  TRUE FALSE

TRUE/FALSE is essentially yes/no but if you need the character matrix you can always do ifelse(., "yes", "no") and retain the matrix structure.

edited Jan 12 '19 at 19:05

answered Jan 12 '19 at 18:27

Rich Scriven

97,041
11
181
245

score 2 · Answer 4 · answered Jan 12 '19 at 18:08

A slightly different approach that doesn't rely on grouping. The final conversion to "Yes/"No" is also performed column-wise, rather than relying on a conversion from long to wide data. For a very large data set this may be somewhat more efficient.

df2 <- df1 %>% 
  mutate(row_num = 1:n()) %>% 
  separate_rows(X) %>% 
  spread(X, 1) %>% 
  select(-row_num) %>% 
  mutate_all(~ifelse(!is.na(.), 'Yes', 'No'))

    A   B   C
1 Yes Yes  No
2 Yes Yes Yes
3 Yes  No  No
4 Yes  No Yes

score 2 · Answer 5 · answered Jan 12 '19 at 18:12

Using splitstackshape

library(splitstackshape)
newdf=cSplit_e(df1, "X", sep = ", ",type = "character")
newdf[newdf==1]='Yes'
newdf[is.na(newdf)]='No'

newdf
        X X_A X_B X_C
1    A, B Yes Yes  No
2 A, B, C Yes Yes Yes
3       A Yes  No  No
4    A, C Yes  No Yes

niko · Answer 6 · 2019-01-12T20:03:36.867

1

Here is another solution in base

lets <- strsplit(as.character(.subset2(df1,1L)), ', ')
lets_unique <- unique(unlist(lets))
vapply(seq_along(lets_unique),function(k)grepl(lets_unique[k],lets),logical(length(lets)))
#      [,1]  [,2]  [,3]
# [1,] TRUE  TRUE FALSE
# [2,] TRUE  TRUE  TRUE
# [3,] TRUE FALSE FALSE
# [4,] TRUE FALSE  TRUE

edited Jan 12 '19 at 20:03

answered Jan 12 '19 at 18:23

niko

5,253
1
12
32

Splitting string column into many using tidyverse

6 Answers6