Split or separate uneven/unequal strings with no d

Question

Given the dataframe df:

x <- c("X1", "X2", "X3", "X4", "X5")
y <- c("00L0", "0", "00012L", "0123L0", "0D0")
df <- data.frame(x, y)

How can I leverage tidyr::separate to put each character of the y strings into a separate column (one column per string position)?

Desired output:

x <- c("X1", "X2", "X3", "X4", "X5")
m1 <- c(0, 0, 0, 0, 0)
m2 <- c(0, NA, 0, 1, "D")
m3 <- c("L", NA, 0, 2, 0)
mN <- c(NA, NA, NA, NA, NA)
df <- data.frame(x, m1, m2, m3, mN)

Where mN could theoretically go up to m100 (100 columns), or higher.

Answer 1

You can split the string in column y into individual characters using strsplit:

> strsplit("00L0",c()) 
[[1]]
[1] "0" "0" "L" "0"

Starting with your data frame:

> df
   x      y
1 X1   00L0
2 X2      0
3 X3 00012L
4 X4 0123L0
5 X5    0D0

I solved the problem of putting these characters into columns by:

First: Use ddply to split all the strings in column y and put these in separate rows

> ddply(df, .(x), summarise, v = 1:nchar(as.character(y)), 
        y = unlist(strsplit(as.character(y),c())))
    x v y
1  X1 1 0
2  X1 2 0
3  X1 3 L
4  X1 4 0
5  X2 1 0
6  X3 1 0
7  X3 2 0
8  X3 3 0
9  X3 4 1
10 X3 5 2
11 X3 6 L
12 X4 1 0
13 X4 2 1
14 X4 3 2
15 X4 4 3
16 X4 5 L
17 X4 6 0
18 X5 1 0
19 X5 2 D
20 X5 3 0

Second: Use reshape to convert the rows with same x-value into columns

> reshape(ans, idvar=c("x"), timevar="v", direction="wide")
    x y.1  y.2  y.3  y.4  y.5  y.6
1  X1   0    0    L    0 <NA> <NA>
5  X2   0 <NA> <NA> <NA> <NA> <NA>
6  X3   0    0    0    1    2    L
12 X4   0    1    2    3    L    0
18 X5   0    D    0 <NA> <NA> <NA>

This may be over-complicating the problem, but it is the only way I could get it to work!

Answer 2

Here is a base R method.

# split the strings
temp <- strsplit(df$y, split="")
# maximum length of the list items
maxL <- max(sapply(temp, length))
# contstruct data.frame with NAs as fills
temp <- data.frame(do.call(rbind, lapply(temp, function(i) c(i, rep(NA, maxL-length(i))))))

# add to df
df <- cbind(x=df[, -2], temp)

which results in

        x X1   X2   X3   X4   X5   X6
1       X1  0    0    L    0 <NA> <NA>
2       X2  0 <NA> <NA> <NA> <NA> <NA>
3       X3  0    0    0    1    2    L
4       X4  0    1    2    3    L    0
5       X5  0    D    0 <NA> <NA> <NA>

I used stringsAFactors=FALSE in the creation of the df:

df <- data.frame(x, y, stringsAsFactors = F)

But, if I didn't, this code would result in an error as @m0h3n points out. The without this alternative data.frame construction, it is necessary to wrap df$y in as.character to coerce the variable from a factor to a character:

# split the strings
temp <- strsplit(as.character(df$y), split="")

Thanks @m0h3n for pointing this out.

Answer 3

This works. It fills with blanks rather than NAs, but you can change that post-hoc if you prefer. (fill = 'right' only works when splitting on a character vector, not explicit positions.)

maxchar = max(nchar(as.character(df$y)))
tidyr::separate(df, y, into = paste0("y", 1:maxchar), sep = 1:(maxchar - 1))

#    x y1 y2 y3 y4 y5 y6
# 1 X1  0  0  L  0         
# 2 X2  0                  
# 3 X3  0  0  0  1  2  L   
# 4 X4  0  1  2  3  L  0   
# 5 X5  0  D  0      

Answer 4

Here is another base R option where we create a delimiter , between each character of the 'y' column using gsub and then read it with read.csv

cbind(df[1],read.csv(text=gsub("(?<=.)(?=.)", ",", df$y, perl=TRUE), 
                header=FALSE,fill=TRUE, na.strings = ""))
#   x V1   V2   V3 V4   V5   V6
#1 X1  0    0    L  0 <NA> <NA>
#2 X2  0 <NA> <NA> NA <NA> <NA>
#3 X3  0    0    0  1    2    L
#4 X4  0    1    2  3    L    0
#5 X5  0    D    0 NA <NA> <NA>

---

Or use tstrsplit from data.table

mxr = max(nchar(as.character(df$y)))
setDT(df)[, paste0("y", seq(mxr)) := tstrsplit(y, "")]