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.

akrun · Accepted Answer · 2015-06-22T19:15:34.853

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>

That `length<-` bit is very strange. Too bad there's no `lengths(lst) <- max(lengths(lst))` yet. — Frank, Jun 22 '15 at 18:44
@Frank Yes, that is a bit cryptic assignment. Hopefully, it gets better with the next R version. — akrun, Jun 22 '15 at 18:46
`lengths` get the length of each list element. In this case it is `lst`. It is equivalent of `sapply(lst, length)` but faster — akrun, Jun 22 '15 at 18:57
@Legalizelt Sorry, I am not following you. Can you show an example — akrun, Jun 22 '15 at 19:00
@LegalizeIt It was introduced in `R 3.2.0`. Which version you are using? — akrun, Jun 22 '15 at 19:03
It's okay. I have to update R to R 3.2.1. Hopefully, there would be many changes. — akrun, Jun 22 '15 at 19:05
It's a useful addition to know about. thanks for the great answer! — Rorschach, Jun 22 '15 at 19:08
Also maybe `stringi::stri_split_fixed(mergedDf$PROD_CODE, ",", simplify = TRUE)` or `tidyr::separate(mergedDf, PROD_CODE, 1:4, extra = "merge")` though a bit less robust — David Arenburg, Jun 22 '15 at 19:12
@DavidArenburg Thanks, any option to replace `''` to `NA` on the fly — akrun, Jun 22 '15 at 19:16

Rbind in variable row size not giving NA's

1 Answers1