Separating a column in R

Question

I have simple data like below its MovieLense 1M data files

  item_id                              title                       genres
1       1                   Toy Story (1995)  Animation|Children's|Comedy
2       2                     Jumanji (1995) Adventure|Children's|Fantasy
3       3            Grumpier Old Men (1995)               Comedy|Romance
4       4           Waiting to Exhale (1995)                 Comedy|Drama
5       5 Father of the Bride Part II (1995)                       Comedy
6       6                        Heat (1995)        Action|Crime|Thriller

my genres column data contain 19 values. How should I change my data to shown like above sample?

Genre Table

genreTbl['title']
         title
1      unknown
2       Action
3    Adventure
4    Animation
5   Children's
6       Comedy
7        Crime
8  Documentary
9        Drama
10     Fantasy
11   Film-Noir
12      Horror
13     Musical
14     Mystery
15     Romance
16      Sci-Fi
17    Thriller
18         War
19     Western

I want to change my data to this structure:

  item_id                                          movie_title release_date
1       1                                     Toy Story (1995)         <NA>
2       2                                     GoldenEye (1995)         <NA>
3       3                                    Four Rooms (1995)         <NA>
4       4                                    Get Shorty (1995)         <NA>
5       5                                       Copycat (1995)         <NA>
6       6 Shanghai Triad (Yao a yao yao dao waipo qiao) (1995)         <NA>
  unknown Action Adventure Animation Children's Comedy Crime Documentary Drama
1       0      0         0         1          1      1     0           0     0
2       0      1         1         0          0      0     0           0     0
3       0      0         0         0          0      0     0           0     0
4       0      1         0         0          0      1     0           0     1
5       0      0         0         0          0      0     1           0     1
6       0      0         0         0          0      0     0           0     1
  Fantasy Film-Noir Horror Musical Mystery Romance Sci-Fi Thriller War Western
1       0         0      0       0       0       0      0        0   0       0
2       0         0      0       0       0       0      0        1   0       0
3       0         0      0       0       0       0      0        1   0       0
4       0         0      0       0       0       0      0        0   0       0
5       0         0      0       0       0       0      0        1   0       0
6       0         0      0       0       0       0      0        0   0       0

I need all my genres be in column just like above and if my item genre value contain selected genre value should be 1 else 0.

Where is the code showing your attempts to solve this yourself? — Rich Scriven, Feb 01 '16 at 19:04
Tip - write a function which returns 0/1 vector for "genrestring" and apply it to your table. I can help you with this, but not now. — Paweł Sopel, Feb 01 '16 at 19:11
@PawełSopel I'm not good in R so you make me happy if you could help me with this :) — Amir Fahmideh, Feb 01 '16 at 19:14
What happened when you googled your title "Separating a column in R"? Please take the tour and read [**about SO**](http://stackoverflow.com/tour): "Include details about what you have tried"; "Don't ask ... [q]uestions you haven't tried to find an answer for (**show your work!**)". — Henrik, Feb 01 '16 at 19:27

score 5 · Answer 1 · edited Feb 02 '16 at 03:33

Alternatively you could use the concat.split function, also from the splitstackshape package:

library(splitstackshape)
concat.split.expanded(df, split.col = "genres", sep = "|", type = "character",
                  mode = "binary", fixed = TRUE, fill = 0)

## Alternative alias
## Note also `drop = TRUE` to drop the original column
cSplit_e(mydf, split.col = "genres", sep = "|", type = "character", 
         mode = "binary", fixed = TRUE, fill = 0, drop = TRUE)

Jaap · Accepted Answer · 2016-02-01T21:00:50.470

Using a combination of cSplit from the splitstackshape and dcast from reshape2 / data.table. By using length as aggregate function, you create logical integer values:

library(splitstackshape)
library(reshape2)   # or library(data.table)
dcast(cSplit(mydf, "genres", sep="|", "long"),
      item_id + title ~ genres, 
      fun.aggregate = length)

which gives:

   item_id                        title Action Adventure Animation Children's Comedy Crime Drama Fantasy Romance Thriller
1:       1               ToyStory(1995)      0         0         1          1      1     0     0       0       0        0
2:       2                Jumanji(1995)      0         1         0          1      0     0     0       1       0        0
3:       3         GrumpierOldMen(1995)      0         0         0          0      1     0     0       0       1        0
4:       4        WaitingtoExhale(1995)      0         0         0          0      1     0     1       0       0        0
5:       5 FatheroftheBridePartII(1995)      0         0         0          0      1     0     0       0       0        0
6:       6                   Heat(1995)      1         0         0          0      0     1     0       0       0        1

Used data:

mydf <- structure(list(item_id = 1:6, title = structure(c(5L, 4L, 2L, 
6L, 1L, 3L), .Label = c("FatheroftheBridePartII(1995)", "GrumpierOldMen(1995)", 
"Heat(1995)", "Jumanji(1995)", "ToyStory(1995)", "WaitingtoExhale(1995)"
), class = "factor"), genres = structure(c(3L, 2L, 6L, 5L, 4L, 
1L), .Label = c("Action|Crime|Thriller", "Adventure|Children's|Fantasy", 
"Animation|Children's|Comedy", "Comedy", "Comedy|Drama", "Comedy|Romance"
), class = "factor")), .Names = c("item_id", "title", "genres"
), class = "data.frame", row.names = c("1", "2", "3", "4", "5", 
"6"))

Thanks for your help your code and sample data is so perfect I am going to test it on my project :) thank you billions time.Can you give a good reference to learning R as master as your self ? — Amir Fahmideh, Feb 01 '16 at 20:33
after testing your sample code I loose my item_id ? how can I fix this too, in your sample code too there is no item_id too. — Amir Fahmideh, Feb 01 '16 at 20:56
@WhizDeveloper thanx, see the [info page of the R-tag](http://stackoverflow.com/tags/r/info) for a nice list of resources about R — Jaap, Feb 01 '16 at 21:20
@WhizDeveloper btw: it's more about persistence than talent imo, I've put a lot hours into learning R and I feel I've only scratched the surface; there is still so much more to learn — Jaap, Feb 01 '16 at 21:34

Separating a column in R

Genre Table

2 Answers2

Linked

Related