How do we separate a column into multiple ones based on "|"?

Question

I have a tibble.

library(tidyverse)
df <- tibble(
  id = 1:4,
  genres = c("Action|Adventure|Science Fiction|Thriller", 
        "Adventure|Science Fiction|Thriller",
        "Action|Crime|Thriller",
        "Family|Animation|Adventure|Comedy|Action")
)
df

I want to separate the genres by "|" and empty columns filled with NA.

This is what I did:

df %>% 
  separate(genres, into = c("genre1", "genre2", "genre3", "genre4", "genre5"), sep = "|")

However, it's being separated after each letter.

Use `cSplit` from `splitstackshape` package, `cSplit(df, "genres", "|")`. — Ronak Shah, Jun 16 '17 at 01:24

RobertMyles · Answer 1 · 2017-06-16T16:27:29.883

2

I think you haven't included into:

df <- tibble::tibble(
  id = 1:4,
  genres = c("Action|Adventure|Science Fiction|Thriller", 
             "Adventure|Science Fiction|Thriller",
             "Action|Crime|Thriller",
             "Family|Animation|Adventure|Comedy|Action")
)
df %>% tidyr::separate(genres, into = c("genre1", "genre2", "genre3", 
                 "genre4", "genre5"))

Result:

# A tibble: 4 x 6
     id    genre1    genre2    genre3   genre4   genre5
* <int>     <chr>     <chr>     <chr>    <chr>    <chr>
1     1    Action Adventure   Science  Fiction Thriller
2     2 Adventure   Science   Fiction Thriller     <NA>
3     3    Action     Crime  Thriller     <NA>     <NA>
4     4    Family Animation Adventure   Comedy   Action

Edit: Or as RichScriven wrote in the comments, df %>% tidyr::separate(genres, into = paste0("genre", 1:5)). For separating on | exactly, use sep = "\\|".

edited Jun 16 '17 at 16:27

answered Jun 16 '17 at 01:41

RobertMyles

2,673
3
30
45

2

Or `into = paste0("genre", 1:5)` – Rich Scriven Jun 16 '17 at 02:06
@RichScriven Even better! – RobertMyles Jun 16 '17 at 11:48
@RobertMc- sorry for my incomplete code, I have made edits. The code you have provided splits Science and Fiction into separate columns, which is not what I want. I want Science Fiction to be in the same column and separation based only on "|". – arjan-hada Jun 16 '17 at 13:52
@arjan-hada Ah okay. I added that in to make it clearer. – RobertMyles Jun 16 '17 at 16:27

score 0 · Answer 2 · answered Jun 16 '17 at 14:31

0

Well, this is what helped, writing regex properly.

df %>% 
  separate(genres, into = paste0("genre", 1:5), sep = "\\|")

answered Jun 16 '17 at 14:31

arjan-hada

251
3
17

How do we separate a column into multiple ones based on "|"?

2 Answers2