How to Split comma separated column values in multiple columns to rows in R

Question

I have a data frame having the following structure

Value1      Value2      Value3  
A,B,C       L,K         T
L           B,P         A
D,F,J       A,B,C       P

I want to reshape this data to the following format

Value1      Value2      Value3  
A           L           T
B           L           T
C           L           T
A           K           T
B           K           T
C           K           T
L           B           A
L           P           A
D           A           P
F           A           P
J           A           P
D           B           P
F           B           P
J           B           P
D           C           P
F           C           P
J           C           P

Any help will be much appreciated.

score 2 · Answer 1 · answered Oct 23 '17 at 13:30

read.table(stringsAsFactors = FALSE, header=TRUE, text="Value1      Value2      Value3  
A,B,C       L,K         T
L           B,P         A
D,F,J       A,B,C       P") -> xdf

library(tidyr)

separate_rows(xdf, Value1) %>%
  separate_rows(Value2)
##    Value3 Value1 Value2
## 1       T      A      L
## 2       T      A      K
## 3       T      B      L
## 4       T      B      K
## 5       T      C      L
## 6       T      C      K
## 7       A      L      B
## 8       A      L      P
## 9       P      D      A
## 10      P      D      B
## 11      P      D      C
## 12      P      F      A
## 13      P      F      B
## 14      P      F      C
## 15      P      J      A
## 16      P      J      B
## 17      P      J      C

jogo · Answer 2 · 2017-10-23T13:42:26.460

This gives the desired result:

d <- read.table(header=TRUE, text="Value1      Value2      Value3  
A,B,C       L,K         T
L           B,P         A
D,F,J       A,B,C       P")

myfun <- function(x) {
  expand.grid(strsplit(x[1], split=",")[[1]], strsplit(x[2], split=",")[[1]], strsplit(x[3], split=",")[[1]])
}

do.call(rbind, apply(d, 1, myfun))

For your data the last strplit() is not needed. You can substitute strsplit(x[3], split=",")[[1]] by x[3]. Eventually you want to set the columnnames of the result: ... <- names(d)

How to Split comma separated column values in multiple columns to rows in R

2 Answers2