在 data.table 列中拆分文本字符串

我有一个脚本，它将 CSV 文件中的数据读入到 data.table中，然后将一列中的文本分成几列。我目前正在使用 lapply和 strsplit函数来完成这项工作。这里有一个例子:

library("data.table")
df = data.table(PREFIX = c("A_B","A_C","A_D","B_A","B_C","B_D"),
VALUE  = 1:6)
dt = as.data.table(df)


# split PREFIX into new columns
dt$PX = as.character(lapply(strsplit(as.character(dt$PREFIX), split="_"), "[", 1))
dt$PY = as.character(lapply(strsplit(as.character(dt$PREFIX), split="_"), "[", 2))


dt
#    PREFIX VALUE PX PY
# 1:    A_B     1  A  B
# 2:    A_C     2  A  C
# 3:    A_D     3  A  D
# 4:    B_A     4  B  A
# 5:    B_C     5  B  C
# 6:    B_D     6  B  D

在上面的示例中，在“ _”字符上将列 PREFIX拆分为两个新列 PX和 PY。

尽管这样做效果很好，但我想知道是否有更好(更有效)的方法来使用 data.table完成这项工作。我的真实数据集有 > = 10M + 行，因此时间/内存效率变得非常重要。

更新:

按照@Frank 的建议，我创建了一个更大的测试用例并使用了建议的命令，但是 stringr::str_split_fixed比原来的方法花费了更长的时间。

library("data.table")
library("stringr")
system.time ({
df = data.table(PREFIX = rep(c("A_B","A_C","A_D","B_A","B_C","B_D"), 1000000),
VALUE  = rep(1:6, 1000000))
dt = data.table(df)
})
#   user  system elapsed
#  0.682   0.075   0.758


system.time({ dt[, c("PX","PY") := data.table(str_split_fixed(PREFIX,"_",2))] })
#    user  system elapsed
# 738.283   3.103 741.674


rm(dt)
system.time ( {
df = data.table(PREFIX = rep(c("A_B","A_C","A_D","B_A","B_C","B_D"), 1000000),
VALUE = rep(1:6, 1000000) )
dt = as.data.table(df)
})
#    user  system elapsed
#   0.123   0.000   0.123


# split PREFIX into new columns
system.time ({
dt$PX = as.character(lapply(strsplit(as.character(dt$PREFIX), split="_"), "[", 1))
dt$PY = as.character(lapply(strsplit(as.character(dt$PREFIX), split="_"), "[", 2))
})
#    user  system elapsed
#  33.185   0.000  33.191

因此，str_split_fixed方法需要大约20倍的时间。

68444

小开

最佳答案

Update: From version 1.9.6 (on CRAN as of Sep'15), we can use the function tstrsplit() to get the results directly (and in a much more efficient manner):

require(data.table) ## v1.9.6+
dt[, c("PX", "PY") := tstrsplit(PREFIX, "_", fixed=TRUE)]
#    PREFIX VALUE PX PY
# 1:    A_B     1  A  B
# 2:    A_C     2  A  C
# 3:    A_D     3  A  D
# 4:    B_A     4  B  A
# 5:    B_C     5  B  C
# 6:    B_D     6  B  D

tstrsplit() basically is a wrapper for transpose(strsplit()), where transpose() function, also recently implemented, transposes a list. Please see ?tstrsplit() and ?transpose() for examples.

See history for old answers.

小开

I add answer for someone who do not use data.table v1.9.5 and also want an one line solution.

dt[, c('PX','PY') := do.call(Map, c(f = c, strsplit(PREFIX, '-'))) ]

小开

Using splitstackshape package:

library(splitstackshape)
cSplit(df, splitCols = "PREFIX", sep = "_", direction = "wide", drop = FALSE)
#    PREFIX VALUE PREFIX_1 PREFIX_2
# 1:    A_B     1        A        B
# 2:    A_C     2        A        C
# 3:    A_D     3        A        D
# 4:    B_A     4        B        A
# 5:    B_C     5        B        C
# 6:    B_D     6        B        D

小开

With tidyr the solution is:

separate(df,col = "PREFIX",into = c("PX", "PY"), sep = "_")

小开

We could try:

library(data.table)
cbind(dt, fread(text = dt$PREFIX, sep = "_", header = FALSE))
#    PREFIX VALUE V1 V2
# 1:    A_B     1  A  B
# 2:    A_C     2  A  C
# 3:    A_D     3  A  D
# 4:    B_A     4  B  A
# 5:    B_C     5  B  C
# 6:    B_D     6  B  D