Split string column to create new binary columns

Question

My data has one column and I am trying to create additional columns with what’s after each “/” in the rows. Here are the first few rows of the data:

> dput(mydata)
structure(list(ALL = structure(c(1L, 4L, 4L, 3L, 2L), .Label = c("/
ca/put/sent_1/fe.gr/eq2_on/eq2_off",
"/ca/put/sent_1/fe.gr/eq2_on/eq2_off/cbr_LBL", "/ca/put/sent_1/fe.g
r/eq2_on/eq2_off/cni_at.p3x.4",
"/ca/put/sent_1/fe.gr/eq2_on/eq2_off/hi.on/hi.ov"), class = "factor
")), .Names = "ALL", class = "data.frame", row.names = c(NA,
-5L))

The result should look like this (data frame) with a “1” in the new column if the variable appears in the row and “0” if not:

> dput(Result)
structure(list(ALL = structure(c(1L, 4L, 5L, 3L, 2L), .Label = c("/ca
/put/sent_1/fe.gr/eq2_on/eq2_off",
"/ca/put/sent_1/fe.gr/eq2_on/eq2_off/cbr_LBL", "/ca/put/sent_1/fe.gr/
eq2_on/eq2_off/cni_at.p3x.4",
"/ca/put/sent_1/fe.gr/eq2_on/eq2_off/hi.on/hi.ov", "/ca/put/sent_1fe.
gr/eq2_on/eq2_off/hi.on/hi.ov"
), class = "factor"), ca = c(1L, 1L, 1L, 1L, 1L), put = c(1L,
1L, 1L, 1L, 1L), sent_1 = c(1L, 1L, 1L, 1L, 1L), fe.gr = c(1L,
1L, 1L, 1L, 1L), eq2_on = c(1L, 1L, 1L, 1L, 1L), eq2_off = c(1L,
1L, 1L, 1L, 1L), hi.on = c(0L, 1L, 1L, 0L, 0L), hi.ov = c(0L,
1L, 1L, 0L, 0L), cni_at.p3x.4 = c(0L, 0L, 0L, 1L, 0L), cbr_LBL = c(0L
,
0L, 0L, 0L, 1L)), .Names = c("ALL", "ca", "put", "sent_1", "fe.gr",
"eq2_on", "eq2_off", "hi.on", "hi.ov", "cni_at.p3x.4", "cbr_LBL"
), class = "data.frame", row.names = c(NA, -5L))

I have tried many functions including strsplit and sapply:

sapply(strsplit(as.character(mydata$ALL), “\\/”), “[[“, 2) #returns "ca"s only

sapply(strsplit(as.character(mydata$ALL), "\\/"), "[[", 3) #returns "put"s only

There are millions of rows and I’d greatly appreciate anything that is quick and efficient.

Answer 1

Using mtabuate from the qdapTools package that I maintain:

library(qdapTools)
mtabulate(strsplit(as.character(dat[[1]]), "/"))

##   V1 ca cbr_LBL cni_at.p3x.4 eq2_off eq2_on fe.gr hi.on hi.ov put sent_1 sent_1fe.gr
## 1  1  1       0            0       1      1     1     0     0   1      1           0
## 2  1  1       0            0       1      1     1     1     1   1      1           0
## 3  1  1       0            0       1      1     0     1     1   1      0           1
## 4  1  1       0            1       1      1     1     0     0   1      1           0
## 5  1  1       1            0       1      1     1     0     0   1      1           0

Answer 2

a tidyverse solution

library(tidyverse)

mydata %>%
  rownames_to_column() %>%
  mutate(key = strsplit(levels(ALL)[ALL],"/"),value=1) %>%
  unnest %>%
  spread(key,value,0) %>%
  select(-rowname)

#   ALL   ca cbr_LBL cni_at.p3x.4 eq2_off eq2_on fe.gr hi.on hi.ov put sent_1
# 1   1 1  1       0            0       1      1     1     0     0   1      1
# 2   4 1  1       0            0       1      1     1     1     1   1      1
# 3   4 1  1       0            0       1      1     1     1     1   1      1
# 4   3 1  1       0            1       1      1     1     0     0   1      1
# 5   2 1  1       1            0       1      1     1     0     0   1      1

data

mydata <- structure(list(ALL = structure(c(1L, 4L, 4L, 3L, 2L), .Label = c(
  "/ca/put/sent_1/fe.gr/eq2_on/eq2_off",
  "/ca/put/sent_1/fe.gr/eq2_on/eq2_off/cbr_LBL",
  "/ca/put/sent_1/fe.gr/eq2_on/eq2_off/cni_at.p3x.4",
  "/ca/put/sent_1/fe.gr/eq2_on/eq2_off/hi.on/hi.ov"), class = "factor
  ")), .Names = "ALL", class = "data.frame", row.names = c(NA,-5L))

Answer 3

You can use cSplit_e from my "splitstackshape" package:

library(splitstackshape)
cSplit_e(mydata, "ALL", "/", type = "character", fill = 0)
#                                                ALL ALL_ca ALL_cbr_LBL
# 1              /ca/put/sent_1/fe.gr/eq2_on/eq2_off      1           0
# 2  /ca/put/sent_1/fe.gr/eq2_on/eq2_off/hi.on/hi.ov      1           0
# 3   /ca/put/sent_1fe.gr/eq2_on/eq2_off/hi.on/hi.ov      1           0
# 4 /ca/put/sent_1/fe.gr/eq2_on/eq2_off/cni_at.p3x.4      1           0
# 5      /ca/put/sent_1/fe.gr/eq2_on/eq2_off/cbr_LBL      1           1
#   ALL_cni_at.p3x.4 ALL_eq2_off ALL_eq2_on ALL_fe.gr ALL_hi.on ALL_hi.ov ALL_put
# 1                0           1          1         1         0         0       1
# 2                0           1          1         1         1         1       1
# 3                0           1          1         0         1         1       1
# 4                1           1          1         1         0         0       1
# 5                0           1          1         1         0         0       1
#   ALL_sent_1 ALL_sent_1fe.gr
# 1          1               0
# 2          1               0
# 3          0               1
# 4          1               0
# 5          1               0

(Note: I think there's a problem in row 3 of your dput which is why it doesn't match your desired output. Notice that the third item in row 3 is "sent_1fe.gr" with no "/" between them.)

Answer 4

How about something like this

spt <- strsplit(as.character(mydata$ALL),"/", fixed=T)
do.call(rbind, lapply(lapply(spt, factor, levels=unique(unlist(spt))), table))

which returns

       ca put sent_1 fe.gr eq2_on eq2_off hi.on hi.ov sent_1fe.gr cni_at.p3x.4 cbr_LBL
[1,] 1  1   1      1     1      1       1     0     0           0            0       0
[2,] 1  1   1      1     1      1       1     1     1           0            0       0
[3,] 1  1   1      1     0      1       1     1     1           1            0       0
[4,] 1  1   1      1     1      1       1     0     0           0            1       0
[5,] 1  1   1      1     1      1       1     0     0           0            0       1

Answer 5

Other option is to melt the split string in list to long form and then use table

library(reshape2)
as.data.frame.matrix(table(melt(strsplit(as.character(
                                   mydata[[1]]), "/"))[2:1]))[,-1]
#    ca eq2_off eq2_on fe.gr put sent_1 hi.on hi.ov sent_1fe.gr cni_at.p3x.4
#1  1       1      1     1   1      1     0     0           0            0
#2  1       1      1     1   1      1     1     1           0            0
#3  1       1      1     0   1      0     1     1           1            0
#4  1       1      1     1   1      1     0     0           0            1
#5  1       1      1     1   1      1     0     0           0            0
#  cbr_LBL
#1       0
#2       0
#3       0
#4       0
#5       1