在 data.table 列中拆分文本字符串 [英] Split text string in a data.table columns

问题描述

我有一个脚本，它将 CSV 文件中的数据读入 data.table，然后将一列中的文本拆分为几个新列.我目前正在使用 lapply 和 strsplit 函数来执行此操作.这是一个例子:

I have a script that reads in data from a CSV file into a data.table and then splits the text in one column into several new columns. I am currently using the lapply and strsplit functions to do this. Here's an example:

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.

In the example above the column PREFIX is split into two new columns PX and PY on the "_" character.

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

Even though this works just fine, I was wondering if there is a better (more efficient) way to do this using data.table. My real datasets have >=10M+ rows, so time/memory efficiency becomes really important.

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

Following @Frank's suggestion I created a larger test case and used the suggested commands, but the stringr::str_split_fixed takes a lot longer than the original method.

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 倍的时间.

So the str_split_fixed method takes about 20X times longer.

推荐答案

更新: 从 1.9.6 版本开始(CRAN 截至 15 年 9 月)，我们可以使用函数 tstrsplit() 直接获取结果(并且以更有效的方式):

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() 基本上是 transpose(strsplit()) 的包装，其中 transpose() 函数，也是最近实现的，转置一个列表.示例请参见 ?tstrsplit() 和 ?transpose().

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.

这篇关于在 data.table 列中拆分文本字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！