Split delimited strings in a column and insert as new rows
data-manipulation, dataframe, r, reshape, strsplit
Solution
Here is another way of doing it..
df <- read.table(textConnection("1|a,b,c\n2|a,c\n3|b,d\n4|e,f"), header = F, sep = "|", stringsAsFactors = F)
df
## V1 V2
## 1 1 a,b,c
## 2 2 a,c
## 3 3 b,d
## 4 4 e,f
s <- strsplit(df$V2, split = ",")
data.frame(V1 = rep(df$V1, sapply(s, length)), V2 = unlist(s))
## V1 V2
## 1 1 a
## 2 1 b
## 3 1 c
## 4 2 a
## 5 2 c
## 6 3 b
## 7 3 d
## 8 4 e
## 9 4 f
Problem
I have a data frame as follow: ``` +-----+-------+ | V1 | V2 | +-----+-------+ | 1 | a,b,c | | 2 | a,c | | 3 | b,d | | 4 | e,f | | . | . | +-----+-------+ ``` Each of the alphabet is a character separated by comma. I would like to split V2 on each comma and insert the split strings as new rows. For instance, the desired output will be: ``` +----+----+ | V1 | V2 | +----+----+ | 1 | a | | 1 | b | | 1 | c | | 2 | a | | 2 | c | | 3 | b | | 3 | d | | 4 | e | | 4 | f | +----+----+ ``` I am trying to use `strsplit()` to spit V2 first, then cast the list into a data frame. It didn't work. Any help will be appreciated.