r - Split concatenated column to corresponding column positions

Question

I have a data frame where a column may contain concatenated characters separated by |:

df <- data.frame(FOO = c('A|B|C', 'A|B', 'B|C', 'A', 'C'))

# df
#     FOO
# 1 A|B|C
# 2   A|B
# 3   B|C
# 4     A
# 5     C

I want to split the string and put the individual values into different columns:

df
#  X1 X2 X3
# 1 A  B  C
# 2 A  B
# 3    B  C
# 4 A
# 5       C

So far I tried with this example: [https://stackoverflow.com/questions/7069076/split-column-at-delimiter-in-data-frame][1] but it is not splitting the columns without repeating values, what I get there is:

df <- data.frame(do.call('rbind', strsplit(as.character(df$FOO),'|',fixed=TRUE)))

> df
  X1 X2 X3
1  A  B  C
2  A  B  A
3  B  C  B
4  A  A  A
5  C  C  C

And I also get this warning:

Warning message: In rbind(c("A", "B", "C"), c("A", "B"), c("B", "C"), "A", "C") : number of columns of result is not a multiple of vector length (arg 2)

What can I do in those cases? Preferably with base R. [1]: Split column at delimiter in data frame

Answer 1

Simply do:

splt <- strsplit(as.character(df$FOO),"\|")
all_val <- sort(unique(unlist(splt)))
t(sapply(splt,function(x){all_val[!(all_val %in% x)]<-NA;all_val}))


#     [,1] [,2] [,3]
#[1,] "A"  "B"  "C" 
#[2,] "A"  "B"  NA  
#[3,] NA   "B"  "C" 
#[4,] "A"  NA   NA  
#[5,] NA   NA   "C" 

data:

df <- data.frame(FOO = c('A|B|C', 'A|B', 'B|C', 'A', 'C'))

Please note:

My version is base:: (no libraries needed) and general:

It would also work with:

df <- data.frame(FOO = c('A|B|C', 'A|B', 'B|C', 'A', 'C', 'B|D|F'))