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.
A data.table
solution:
library(data.table)
mdt <- melt(setDT(df), id.vars="Column1")[,strsplit(as.character(value),",",fixed=TRUE),
by=list(Column1,variable)]
the result:
> mdt
Column1 variable V1
1: id1 Column2 text1
2: id1 Column2 text2
3: id1 Column2 text3
....
You can also use the tstrsplit
function from the latest version of data.table
(v1.9.5+) which keeps the name for the value
column instead of renaming it to V1
:
mdt <- melt(setDT(df), id.vars="Column1")[,lapply(.SD, function(x) tstrsplit(x, ",", fixed=TRUE)),
by=list(Column1,variable)]
the result:
> mdt
Column1 variable value
1: id1 Column2 text1
2: id1 Column2 text2
3: id1 Column2 text3
....
An alternative solution with dplyr
& tidyr
:
library(dplyr)
library(tidyr)
mdf <- df %>% gather(variable, value, -Column1) %>%
transform(value = strsplit(as.character(value),",")) %>%
unnest(value)
the result:
> mdf
Column1 variable value
1 id1 Column2 text1
2 id1 Column2 text2
3 id1 Column2 text3
....
With the latest version of tidyr
, you can also use the separate_rows
-function:
mdf <- df %>%
gather(variable, value, -Column1) %>%
separate_rows(value)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With