The fastest way to parse rules string into data.frame

Question

I have a dataset with string rule set.

R> input
   id                                   rules
1   1 1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0
2   2 1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0
3   3 1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0
4   4 1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0
5   5 1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0
6   6 1.11=>0;1.12=>0;1.13=>0;1.14=>1;1.15=>0
7   7 1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0
8   8 1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0
9   9 1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0
10 10 1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0
11 11 1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0
12 12 1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0
13 13 1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0
14 14 1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0
15 15 1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0
16 16 1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0

What is the fastest way to split and join this rules as separate columns? Desired result:

R> res
   R1.11 R1.12 R1.13 R1.14 R1.15 id
1      0     0     0     0     0  1
2      0     0     0     0     0  2
3      0     0     0     0     0  3
4      0     0     0     0     0  4
5      0     0     0     0     0  5
6      0     0     0     1     0  6
7      0     0     0     0     0  7
8      0     0     0     0     0  8
9      0     0     0     0     0  9
10     0     0     0     0     0 10
11     0     0     0     0     0 11
12     0     0     0     0     0 12
13     0     0     0     0     0 13
14     0     0     0     0     0 14
15     0     0     0     0     0 15
16     0     0     0     0     0 16

To reproduce the data sets see structures below.

Input data structure:

input <- structure(
    list(id = 1:16,
         rules = c("1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0", 
                   "1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0", 
                   "1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0", 
                   "1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0", 
                   "1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0", 
                   "1.11=>0;1.12=>0;1.13=>0;1.14=>1;1.15=>0", 
                   "1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0", 
                   "1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0", 
                   "1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0", 
                   "1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0", 
                   "1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0", 
                   "1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0", 
                   "1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0", 
                   "1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0", 
                   "1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0", 
                   "1.11=>0;1.12=>0;1.13=>0;1.14=>0;1.15=>0")), 
    .Names = c("id", "rules"),
    row.names = c(NA, -16L),
    class = "data.frame")

Output data structure:

output <- structure(
    list(R1.11 = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L),
         R1.12 = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), 
         R1.13 = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), 
         R1.14 = c(0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), 
         R1.15 = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L),
         id = 1:16), 
    .Names = c("R1.11", "R1.12", "R1.13", "R1.14", "R1.15", "id"),
    class = "data.frame",
    row.names = c(NA, -16L))

Perhaps [here](http://stackoverflow.com/questions/37827946/fast-handling-of-rules-in-a-simulation) is some useful input. — Christoph, Jan 30 '17 at 15:05

score 4 · Answer 1 · answered Jan 30 '17 at 15:06

I might do...

library(splitstackshape)
res = cSplit(input, "rules", ";", "long")
res[, c("variable", "value") := tstrsplit(rules, "=>", type.convert=TRUE)]

# head(res)
#    id   rules variable value
# 1:  1 1.11=>0     1.11     0
# 2:  1 1.12=>0     1.12     0
# 3:  1 1.13=>0     1.13     0
# 4:  1 1.14=>0     1.14     0
# 5:  1 1.15=>0     1.15     0
# 6:  2 1.11=>0     1.11     0

I'd stop here, with the data in long format, but you can go to your desired wide output with...

wideres = dcast(res, id ~ paste0("R", variable), value.var="value")

# test that it's essentially correct:
fsetequal(wideres, setcolorder(data.table(output), names(wideres)))

score 3 · Answer 2 · answered Jan 30 '17 at 15:12

3

Here's a base R approach:

input[3:7] <- 
 matrix(as.integer(sub(".*=>", "", unlist(strsplit(input$rules, ";", fixed=TRUE)))), 
         ncol=5, byrow = TRUE)

You'd still need to set the column names as desired.

answered Jan 30 '17 at 15:12

talat

68,970
21
126
157

score 1 · Answer 3 · answered Jan 30 '17 at 15:09

Wrote a quick function to do this for you.

my.Parser <- function(row){
  r <- unlist(strsplit(gsub(";","x",row[-1]),"x"))
  Values <- data.frame(t(gsub("*.*=>","",r)))
  colnames(Values) <- paste("R",gsub("=>*.*","",r,),sep="")
  Values <- cbind(id=row[1], Values)
  return(Values)
}

Test on first row

my.Parser(input[1,])

To apply over your input

Results <- apply(input,1,function(x) my.Parser(x))
Results <- do.call("rbind", Results)
Results

Output:

    id R1.11 R1.12 R1.13 R1.14 R1.15
id    1     0     0     0     0     0
id1   2     0     0     0     0     0
id2   3     0     0     0     0     0
id3   4     0     0     0     0     0
id4   5     0     0     0     0     0
id5   6     0     0     0     1     0
id6   7     0     0     0     0     0
id7   8     0     0     0     0     0
id8   9     0     0     0     0     0
id9  10     0     0     0     0     0
id10 11     0     0     0     0     0
id11 12     0     0     0     0     0
id12 13     0     0     0     0     0
id13 14     0     0     0     0     0
id14 15     0     0     0     0     0
id15 16     0     0     0     0     0

Artem Klevtsov · Accepted Answer · 2017-04-27T16:34:57.173

Thanks for the replies. My solution based on the data.table package only:

library(data.table)
parse_rules <- function(.data, id_col, rules_col, prefix = "R", sep1 = ";", sep2 = "=>", convert = TRUE) {
    if (is.numeric(id_col)) id_col <- names(.data)[id_col]
    if (is.numeric(rules_col)) rules_col <- names(.data)[rules_col]
    res <- .data[, tstrsplit(get(rules_col), split = sep1, fixed = TRUE)]
    res[, (id_col) := .data[[id_col]]]
    res <- melt(res, id.vars = id_col, measure.vars = setdiff(names(res), id_col))
    res[, c("variable", "value") := tstrsplit(value, split = sep2, fixed = TRUE, type.convert = convert)]
    res <- dcast(res, get(id_col) ~ paste0(prefix, variable), value.var = "value")
    setnames(res, "id_col", id_col)
    return(res)
}
setDT(input)
parse_rules(input, "id", "rules")

In result:

    id R1.11 R1.12 R1.13 R1.14 R1.15
 1:  1     0     0     0     0     0
 2:  2     0     0     0     0     0
 3:  3     0     0     0     0     0
 4:  4     0     0     0     0     0
 5:  5     0     0     0     0     0
 6:  6     0     0     0     1     0
 7:  7     0     0     0     0     0
 8:  8     0     0     0     0     0
 9:  9     0     0     0     0     0
10: 10     0     0     0     0     0
11: 11     0     0     0     0     0
12: 12     0     0     0     0     0
13: 13     0     0     0     0     0
14: 14     0     0     0     0     0
15: 15     0     0     0     0     0
16: 16     0     0     0     0     0

Another solution based on the base functions:

parse_rules <- function(.data, col, prefix = "R", sep1 = ";", sep2 = "=>", convert = TRUE) {
    n <- length(gregexpr(sep1, .data[1L, col])[[1]]) + 1L
    str <- unlist(strsplit(.data[[col]], sep1, fixed = TRUE))
    res <- matrix(sub(paste0(".*", sep2), "", str), ncol = n, byrow = TRUE)
    res <- as.data.frame(res, stringsAsFactors = FALSE)
    nm <- paste0(prefix, sub(paste0(sep2, ".*"), "", str[seq_len(n)]))
    names(res) <- nm
    if (convert && any(chr <- sapply(res, is.character))) {
        for (c in names(res)[chr])
            res[[c]] <- type.convert(res[[c]], as.is = TRUE)
    }
    .data[[col]] <- NULL
    res <- cbind(.data, res, stringsAsFactors = FALSE)
    res
}

The fastest way to parse rules string into data.frame

4 Answers4