enricoferrero enricoferrero - 9 days ago 6
R Question

How to strsplit data frame column and replicate rows accordingly?

I have a data frame like this:

> df <- data.frame(Column1=c("id1", "id2", "id3"), Column2=c("text1,text2,text3", "text4", "text5,text6"), Column3=c("text7", "text8,text9,text10,text11", "text12,text13"))

> df
Column1 Column2 Column3
1 id1 text1,text2,text3 text7
2 id2 text4 text8,text9,text10,text11
3 id3 text5,text6 text12,text13


How do I transform it in this format?

Column1 variable value
1 id1 Column2 text1
2 id1 Column2 text2
3 id1 Column2 text3
4 id2 Column2 text4
5 id3 Column2 text5
6 id3 Column2 text6
7 id1 Column3 text7
8 id2 Column3 text8
9 id2 Column3 text9
10 id2 Column3 text10
11 id2 Column3 text11
12 id3 Column3 text12
13 id3 Column3 text13





I guess the first step is to
melt()
the data frame (btw, should I worry about that warning?):

> library(reshape2)
> mdf <- melt(df, id.vars="Column1", measure.vars=c("Column2", "Column3"))
> mdf
Column1 variable value
1 id1 Column2 text1,text2,text3
2 id2 Column2 text4
3 id3 Column2 text5,text6
4 id1 Column3 text7
5 id2 Column3 text8,text9,text10,text11
6 id3 Column3 text12,text13
Warning message:
attributes are not identical across measure variables; they will be dropped


Then I would basically need to ``strsplit()` the 'value' column and replicate the rows accordingly, but I can't think of a way to do it.

> strsplit(mdf$value, ",")
[[1]]
[1] "text1" "text2" "text3"

[[2]]
[1] "text4"

[[3]]
[1] "text5" "text6"

[[4]]
[1] "text7"

[[5]]
[1] "text8" "text9" "text10" "text11"

[[6]]
[1] "text12" "text13"


Any help is appreciated! Thanks.

Answer

You could try:

 library(reshape2)

cSplit from https://gist.github.com/mrdwab/11380733

 cSplit(melt(df, id.vars="Column1"), "value", ",", "long")
 #      Column1 variable  value
 # 1:     id1  Column2  text1
 # 2:     id1  Column2  text2
 # 3:     id1  Column2  text3
 # 4:     id2  Column2  text4
 # 5:     id3  Column2  text5
 # 6:     id3  Column2  text6
 # 7:     id1  Column3  text7
 # 8:     id2  Column3  text8
 # 9:     id2  Column3  text9
 #10:     id2  Column3 text10
 #11:     id2  Column3 text11
 #12:     id3  Column3 text12
 #13:     id3  Column3 text13

Alternatively, if one wants to stick to functions available in CRAN packages:

library(reshape2)
library(splitstackshape)
library(dplyr)
select(na.omit(concat.split.multiple(melt(df, id.vars="Column1"), split.col="value", sep=",", direction="long")), -time)
Comments