Roland Roland - 1 month ago 7
R Question

Strsplit on a column of a data frame in R

I have a

data.frame
where one of the variables is a vector (or a list), like this:

MyColumn <- c("A, B,C", "D,E", "F","G")
MyDF <- data.frame(group_id=1:4, val=11:14, cat=MyColumn)

# group_id val cat
# 1 1 11 A, B,C
# 2 2 12 D,E
# 3 3 13 F
# 4 4 14 G


I'd like to have a new data frame with as many rows as the vector

FlatColumn <- unlist(strsplit(MyColumn,split=","))


which looks like this:

MyNewDF <- data.frame(group_id=c(rep(1,3),rep(2,2),3,4), val=c(rep(11,3),rep(12,2),13,14), cat=FlatColumn)

# group_id val cat
# 1 1 11 A
# 2 1 11 B
# 3 1 11 C
# 4 2 12 D
# 5 2 12 E
# 6 3 13 F
# 7 4 14 G


In essence, for every factor which is an element of the list of
MyColumn
(the letters A to G), I want to assign the corresponding values of the list. Every factor appears only once in
MyColumn
.

Is there a neat way for this kind of reshaping/unlisting/merging? I've come up with a very cumbersome
for
-loop over the rows of
MyDF
and the length of the corresponding element of
strsplit(MyColumn,split=",")
. I'm very sure that there has to be a more elegant way.

Answer

How about

lst <- strsplit(MyColumn, split = ",")
k <- lengths(lst)    ## expansion size
FlatColumn <- unlist(lst, use.names = FALSE)
MyNewDF <- data.frame(group_id = rep.int(MyDF$group_id, k),
                      val = rep.int(MyDF$val, k),
                      cat = FlatColumn)

#  group_id val cat
#1        1  11   A
#2        1  11   B
#3        1  11   C
#4        2  12   D
#5        2  12   E
#6        3  13   F
#7        4  14   G