add rows to data frame for each value from a character column R

Question

I better explain my problem with an example:

    Role Skill        ID
1:    A     a       1, 1/a, 1/b
2:    A     b       1/a, 2/a
3:    B     c       1/a, 2/c
4:    B     d          3
5:    C     e          4

Having the data, dt, above, I would like to create additional rows, for each value of the ID variable. The end result should be:

  Role    Skill    ID
1:    A     a       1
2:    A     a       1/a
3:    A     a       1/b
4:    A     b       1/a
5:    A     b       2/a
6:    B     c       1/a
7:    B     c       2/c
8:    B     d       3
9:    C     e       4

Below, the code to replicate the data:

dt <- data.table(Role = c("A","A","B","B","C"),
                 Skill = c("a","b",'c',"d","e"),
                 ID = c(c("1, 1/a, 1/b"),c("1/a, 2/a"),c("1/a, 2/c"),
                    c("3"),c("4")))

Did you read: [Split comma-separated strings in a column into separate rows](https://stackoverflow.com/questions/13773770/split-comma-separated-strings-in-a-column-into-separate-rows) ? — markus, Apr 15 '20 at 19:12
true, same question. sorry, I will delete my question. I did not know what keywords to look for. — ralucaGui, Apr 15 '20 at 19:17
@ralucaGui there is no need to delete a question. If that is the case, 90% of the questions would get deleted — akrun, Apr 15 '20 at 19:40

score 0 · Accepted Answer · answered Apr 15 '20 at 19:10

We can use separate_rows

library(dplyr)
library(tidyr)
dt %>%
   separate_rows(ID, sep=",\\s*")
#    Role Skill  ID
#1:    A     a   1
#2:    A     a 1/a
#3:    A     a 1/b
#4:    A     b 1/a
#5:    A     b 2/a
#6:    B     c 1/a
#7:    B     c 2/c
#8:    B     d   3
#9:    C     e   4

Or with strsplit

 dt[, .(ID = unlist(strsplit(ID, ",\\s*"))), .(Role, Skill)]

add rows to data frame for each value from a character column R

1 Answers1