I have a data frame like below.
A B C
V1 a 1 e
V2 b 2 f
V3 c 3 g,h,i
V4 d 4 j,k
Class of data in C column is list.
And I want to unlist the C column data and make multiple rows in data frame like this.
A B C
V1 a 1 e
V2 b 2 f
V3 c 3 g
V4 c 3 h
V5 c 3 i
V6 d 4 j
V7 d 4 k
How to do this?
Thanks a lot.
We can use cSplit
library(splitstackshape)
cSplit(df1, "C", sep=",", "long")
# A B C
#1: a 1 e
#2: b 2 f
#3: c 3 g
#4: c 3 h
#5: c 3 i
#6: d 4 j
#7: d 4 k
Or use unnest from tidyr
library(tidyr)
unnest(df1, C=strsplit(C, ","))
# A B C
#1 a 1 e
#2 b 2 f
#3 c 3 g
#4 c 3 h
#5 c 3 i
#6 d 4 j
#7 d 4 k
Or with base R
lst <- strsplit(df1$C, ",")
transform(df1[rep(1:nrow(df1), lengths(lst)),-3], C= unlist(lst))
# A B C
#V1 a 1 e
#V2 b 2 f
#V3 c 3 g
#V3.1 c 3 h
#V3.2 c 3 i
#V4 d 4 j
#V4.1 d 4 k
NOTE: If the "C" column is factor, convert to character and use it in strsplit i.e. strsplit(as.character(df1$C), ",")
Suppose if the column "C" is a list, we can still use unnest
unnest(df2, C)
# A B C
#1 a 1 e
#2 b 2 f
#3 c 3 g
#4 c 3 h
#5 c 3 i
#6 d 4 j
#7 d 4 k
Or with listCol_l from splitstackshape
listCol_l(df2, "C")[]
df1 <- structure(list(A = c("a", "b", "c", "d"), B = 1:4, C = c("e",
"f", "g,h,i", "j,k")), .Names = c("A", "B", "C"),
class = "data.frame", row.names = c("V1", "V2", "V3", "V4"))
df2 <- structure(list(A = c("a", "b", "c", "d"), B = 1:4, C = list("e",
"f", c("g", "h", "i"), c("j", "k"))), .Names = c("A", "B",
"C"), row.names = c("V1", "V2", "V3", "V4"), class = "data.frame")
Use:
s <- strsplit(df$C, split = ",")
data.frame(A = rep(df$A, sapply(s, length)), C = unlist(s))
