在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.
即使这很好,我想知道是否有更好的(更高效的) code> 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
方法大约需要20X次
So the str_split_fixed
method takes about 20X times longer.
推荐答案
更新:从版本1.9.6开始,我们可以使用函数 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屋!