How to separate a string column into multiple columns?

Question

# A tibble: 268 x 1
   `Which of these social media platforms do you have an account in right now?`
   <chr>                                                                       
 1 Facebook, Instagram, Twitter, Snapchat, Reddit, Signal                      
 2 Reddit                                                                      
 3 Facebook, Instagram, Twitter, Linkedin, Snapchat, Reddit, Quora             
 4 Facebook, Instagram, Twitter, Snapchat                                      
 5 Facebook, Instagram, TikTok, Snapchat                                       
 6 Facebook, Instagram, Twitter, Linkedin, Snapchat                            
 7 Facebook, Instagram, TikTok, Linkedin, Snapchat, Reddit                     
 8 Facebook, Instagram, Snapchat                                               
 9 Linkedin, Reddit                                                            
10 Facebook, Instagram, Twitter, TikTok                                        
# ... with 258 more rows

I'd like to separate this one string column into multiple columns to obtain each social media in a column of its own.

score 4 · Accepted Answer · 2021-06-02 15:18:33Z

4

You can use unnest_tokens from the tidytext package combined with spread from tidyr to get the effect you're after...

library(tidyverse)
library(tidytext)

df %>%
  mutate(Id = row_number(), HasAccount = "Yes") %>%
  unnest_tokens(Network, `Which of these social media platforms do you have an account in right now?`, to_lower = F) %>%
  spread(Network, HasAccount, fill = "No")

(I generated my own version of your data, so this will look different from yours)

# A tibble: 268 x 8
      Id Facebook Instagram Reddit Signal Snapchat TikTok Twitter
   <int> <chr>    <chr>     <chr>  <chr>  <chr>    <chr>  <chr>  
 1     1 No       No        No     No     No       No     Yes    
 2     2 Yes      Yes       No     No     Yes      No     Yes    
 3     3 No       Yes       No     Yes    No       Yes    No     
 4     4 No       Yes       No     No     Yes      No     No     
 5     5 No       Yes       No     Yes    Yes      Yes    Yes    
 6     6 No       Yes       No     No     No       No     No     
 7     7 No       No        Yes    Yes    No       Yes    Yes    
 8     8 No       No        Yes    No     No       No     Yes    
 9     9 No       No        Yes    No     Yes      Yes    No     
10    10 No       Yes       Yes    Yes    Yes      No     Yes

edited Jun 2, 2021 at 15:18

answered Jun 2, 2021 at 14:34

user6212

Sign up to request clarification or add additional context in comments.

3 Comments

wageeh Over a year ago

I received this error when executing your code ```` Erreur : Each row of output must be identified by a unique combination of keys. Keys are shared for 2 rows: * 451, 452````

user6212 Over a year ago

@wageeh I added a fix (the mutate(Id = row_number()) line)

wageeh Over a year ago

I still get this error Error in UseMethod("mutate") : no applicable method for 'mutate' applied to an object of class "function

Ben Bolker · Accepted Answer · 2021-06-02 14:36:52Z

2

tidyr::separate should do this for you (although it may warn about uneven numbers of elements in different rows)

library(tidyverse)
dd <- tibble(x = c("a", "a, b", "a, b, c"))
maxcols <- 3
dd %>% separate(x, into=paste0("y", 1:maxcols))

 y1    y2    y3   
  <chr> <chr> <chr>
1 a     NA    NA   
2 a     b     NA   
3 a     b     c

Warning message: Expected 3 pieces. Missing pieces filled with NA in 2 rows [1, 2].

I think @JasonPunyon's answer is more useful than mine, although mine does address your question as written ("separate this one string column into multiple columns")

edited Jun 2, 2021 at 14:36

answered Jun 2, 2021 at 14:25

Ben Bolker

230k26 gold badges405 silver badges497 bronze badges

Collectives™ on Stack Overflow

How to separate a string column into multiple columns?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related