Splitting data.table column into many unknow number of columns based on pattern

Question

I want to split column B of data.table dt1 into many column based on space between its values.

df1 <- 
  structure(list(B = c("3,845,168 15,467,645 15,054,813 913 30,523,371", 
        "3,104,154 12,495,278 12,298,236 223 24,793,737", "741,014 2,972,367 2,756,577 690 5,729,634", 
        "218,044 1,035,308 1,008,748 18 2,044,074", "200,744 961,775 942,901 13 1,904,689", 
        "17,300 73,533 65,847 5 139,385"), C = c("17,743,645", "14,456,435", 
        "3,287,210", "1,165,692", "1,071,138", "94,554"), D = c("102.74", 
        "101.60", "107.83", "102.63", "102.00", "111.67"), E = c("2.89", 
        "2.87", "2.96", "2.99", "3.07", "2.06")), .Names = c("B", "C", 
        "D", "E"), row.names = c(NA, -6L), class = "data.frame"
        )
library(data.table)
dt1 <- data.table(df1)
dt1
                                                B          C      D    E
1: 3,845,168 15,467,645 15,054,813 913 30,523,371 17,743,645 102.74 2.89
2: 3,104,154 12,495,278 12,298,236 223 24,793,737 14,456,435 101.60 2.87
3:      741,014 2,972,367 2,756,577 690 5,729,634  3,287,210 107.83 2.96
4:       218,044 1,035,308 1,008,748 18 2,044,074  1,165,692 102.63 2.99
5:           200,744 961,775 942,901 13 1,904,689  1,071,138 102.00 3.07
6:                 17,300 73,533 65,847 5 139,385     94,554 111.67 2.06

Answer 1

We could use the tstrsplit

tmp <- dt1[, tstrsplit(B, "\\s+")]
dt1[, paste0("B", seq_along(tmp)) := tmp]
rm(tmp)

---

Or as @DavidArenburg mentioned, we can avoid the creation of temporary object by first finding out the number of spaces with stri_count_fixed from stringi and then use tstrsplit with fixed = TRUE argument

M <- max(stringi::stri_count_fixed(dt1$B, " ")) + 1
dt1[, paste0("B", seq_len(M)) := tstrsplit(B, " ", fixed = TRUE)]

Update

As the , is not considered in a numeric column, we remove that and split with type.convert = TRUE in tstrsplit

dt1[, paste0("B", seq_len(M)) := tstrsplit(gsub(",", "", B), " ",
         fixed = TRUE, type.convert = TRUE)]