How to strsplit data frame column and replicate rows accordingly?
dataframe, melt, r, reshape2, split
Solution
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)
Problem
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.