Rbind in variable row size not giving NA's

Question

The initial data frame mergedDf is

  PROD_CODE
  1 PRD0900033,PRD0900135,PRD0900220,PRD0900709
  2                       PRD0900097,PRD0900550
  3                                  PRD0900121
  4                                  PRD0900353
  5                       PRD0900547,PRD0900614

After calling

mergedDf<-data.frame(do.call('rbind', strsplit(as.character(mergedDf$PROD_CODE),',',fixed=TRUE)))

Output becomes

 X1         X2         X3         X4
1 PRD0900033 PRD0900135 PRD0900220 PRD0900709
2 PRD0900097 PRD0900550 PRD0900097 PRD0900550
3 PRD0900121 PRD0900121 PRD0900121 PRD0900121
4 PRD0900353 PRD0900353 PRD0900353 PRD0900353
5 PRD0900547 PRD0900614 PRD0900547 PRD0900614

It seems the excess rows are being refilled.

I have tries using bind_rows(), rbind_all() but these require changing the splitted ones to data.frame which I couldn't do. I also tried using rbindlist() which also needed a data.frame as an argument.

I need the ouput to be. The positions do not matter.

  X1         X2         X3         X4
1 PRD0900033 PRD0900135 PRD0900220 PRD0900709
2 PRD0900097 PRD0900550 NA         NA
3 PRD0900121 NA         NA         NA
4 PRD0900353 NA         NA         NA
5 PRD0900547 PRD0900614 NA         NA

Or if anyone could recommend a nicer way to format for apriori algorithm implementation it would be nice. Please Help.

Answer 1

You can try cSplit

library(splitstackshape)
setnames(cSplit(mergedDf, 'PROD_CODE', ','), paste0('X',1:4))[]
#           X1         X2         X3         X4
#1: PRD0900033 PRD0900135 PRD0900220 PRD0900709
#2: PRD0900097 PRD0900550         NA         NA
#3: PRD0900121         NA         NA         NA
#4: PRD0900353         NA         NA         NA
#5: PRD0900547 PRD0900614         NA         NA

Or using the devel version of data.table i.e. v1.9.5

library(data.table)
setDT(mergedDf)[, tstrsplit(PROD_CODE, ',', fixed=TRUE)]
#           V1         V2         V3         V4
#1: PRD0900033 PRD0900135 PRD0900220 PRD0900709
#2: PRD0900097 PRD0900550         NA         NA
#3: PRD0900121         NA         NA         NA
#4: PRD0900353         NA         NA         NA
#5: PRD0900547 PRD0900614         NA         NA

Or using stringi (contributed by @David Arenburg)

library(stringi)
d1 <- as.data.frame(stri_split_fixed(mergedDf$PROD_CODE, ",", simplify = TRUE))
is.na(d1) <- d1==''
d1
#          V1         V2         V3         V4
#1 PRD0900033 PRD0900135 PRD0900220 PRD0900709
#2 PRD0900097 PRD0900550       <NA>       <NA>
#3 PRD0900121       <NA>       <NA>       <NA>
#4 PRD0900353       <NA>       <NA>       <NA>
#5 PRD0900547 PRD0900614       <NA>       <NA>

Or separate from tidyr (contributed by @David Arenburg)

 library(tidyr)
 separate(mergedDf, PROD_CODE, 1:4, extra = "merge") #note the extra='merge'
 #         1          2          3          4
 #1 PRD0900033 PRD0900135 PRD0900220 PRD0900709
 #2 PRD0900097 PRD0900550       <NA>       <NA>
 #3 PRD0900121       <NA>       <NA>       <NA>
 #4 PRD0900353       <NA>       <NA>       <NA>
 #5 PRD0900547 PRD0900614       <NA>       <NA>

Or using base R

 read.table(text=mergedDf$PROD_CODE, sep=",", col.names=paste0("X",1:4),
                        fill=TRUE, na.strings='', stringsAsFactors=FALSE)
 #         X1         X2         X3         X4
 #1 PRD0900033 PRD0900135 PRD0900220 PRD0900709
 #2 PRD0900097 PRD0900550       <NA>       <NA>
 #3 PRD0900121       <NA>       <NA>       <NA>
 #4 PRD0900353       <NA>       <NA>       <NA>
 #5 PRD0900547 PRD0900614       <NA>       <NA>

Or with strsplit (lengths function was introduced in R 3.2.0. The equivalent code for earlier versions is sapply(lst, length))

 lst <- strsplit(mergedDf$PROD_CODE, ',')
 res <- do.call(rbind.data.frame,lapply(lst, `length<-`, max(lengths(lst))))
 names(res) <- paste0("X", 1:4)
 res
 #        X1         X2         X3         X4
 #1 PRD0900033 PRD0900135 PRD0900220 PRD0900709
 #2 PRD0900097 PRD0900550       <NA>       <NA>
 #3 PRD0900121       <NA>       <NA>       <NA>
 #4 PRD0900353       <NA>       <NA>       <NA>
 #5 PRD0900547 PRD0900614       <NA>       <NA>