数据表元程序设计 [英] Data.table meta-programming
问题描述
我认为元编程是正确的术语。
我想要能够使用data.table,就像在webapp中使用MySQL一样。也就是说,Web用户使用一些Web前端(例如,像Shiny服务器)来选择数据库,选择要过滤的列,选择要分组的列,选择要聚合的列和聚合函数。我想使用R和data.table作为后端进行查询,聚合等。假设前端存在,R有这些变量作为字符串,并且它们被验证等。
我写了以下函数来构建data.table表达式,并使用R的parse / eval元编程功能来运行它。这是一个合理的方法吗?
我包括所有相关代码来测试这个。源代码这个代码(阅读后它的安全!)和
运行test_agg_meta()来测试它。这只是一个开始。我可以添加更多的功能。
但我的主要问题是我是否过度地想这个。有没有更直接的方法来使用data.table当所有的输入在手之前未确定,而不求助于parse / eval元编程?
我也知道with语句和一些其他无糖功能的方法,但不知道他们是否可以照顾所有情况。
require(data.table)
fake_data< -function(num = 12){
#make一些假数据
x = 1:num
let = letters [1:num]
data = data.table(
u = rep(c B,C),floor(num / 3)),
v = x %% 2,w = lets,x = x,y = x ^ 2,z = 1-x)
return(data)
}
data_table_meta <-function(
#以元程序化的方式汇总数据
data_in = fake_data(),
filter_cols = NULL,
filter_min = NULL,
filter_max = NULL,
groupby_cols = NULL,
agg_cols = setdiff(names(data_in),groupby_cols),
agg_funcs = NULL,
verbose = F,
validate = T,
jsep =_
){
all_cols = names(data_in)
if(validate){
stopifnot(length(filter_cols)== length(filter_min))
stopifnot(length(filter_cols)== length(filter_max))
stopifnot filter_cols%in%all_cols)
stopifnot(groupby_cols%in%all_cols)
stopifnot(length(intersect(agg_cols,groupby_cols))== 0)
stopifnot length(agg_funcs))| (length(agg_funcs)== 1)| (length(agg_funcs)== 0))
}
#build命令
#defaults
i_filter =
j_select =
n_agg_funcs = length(agg_funcs)
n_agg_cols = length(agg_cols)
n_groupby_cols = length(groupby_cols)
if(n_agg_funcs == 0){
# NULL
print(NULL)
j_select = paste(agg_cols,collapse =,)
j_select = paste(list(,j_select,))
} else {
agg_names = paste(agg_funcs,agg_cols,sep = jsep)
jsels = paste(agg_names,=,agg_funcs,(,agg_cols,),sep =
if(n_groupby_cols> 0)jsels = c(jsels,N_Rows_Aggregated = .N)
j_select = paste(jsels,collapse =,)
j_select = ,j_select,))
}
groupby =
if(n_groupby_cols> 0){
groupby = paste(groupby_cols, collapse =,)
groupby = paste(by = list(,groupby,),sep =)
}
n_filter_cols = length )
if(n_filter_cols> 0){
i_filters = rep(,n_filter_cols)
for(i in 1:n_filter_cols){
i_filters [i] (,filter_cols [i],> =,filter_min [i],& ,filter_cols [i],<=,filter_max [i],),sep =)
}
i_filter =粘贴(i_filters,collapse =& b $ b}
command = paste(data_in [,i_filter,,,j_select,,,groupby,],sep =)
if(verbose == 2){
print(all_cols:)
print(all_cols)
print(filter_cols:)
print $ b print(agg_cols:)
print(agg_cols)
print(filter_min:)
print(filter_min)
print(filter_max:)
print(col_cols)
print(groupby_cols:)
print(groupby_cols)
print agg_funcs:)
print(agg_funcs)
print(i_filter)
print(i_filter)
print(j_select)
print $ b print(groupby)
打印(groupby)
打印(命令)
打印(命令)
}
打印:,command))
eval(parse(text = command))
}
my_agg< ;-function(data = fake_data()){
data_out = data [
i = x <= 5,
j = list(
mean_x = mean(x),
mean_y = mean(y),
sum_z = sum ),
N_Rows_Aggregated = .N
),
by = list(u,v)]
return(data_out)
}
my_agg_meta< -function(data = fake_data()){
#s应该给出与my_agg
相同的结果data_out = data_table_meta(data,
filter_cols = c(x),
filter_min = c(-10000),
filter_max = c(5),
groupby_cols = c(u,v),
agg_cols = c(x,y ,z),
agg_funcs = c(mean,mean,sum),
verbose = T,
validate = T,
jsep = _)
return(data_out)
}
test_agg_meta <-function(){
stopifnot(all(my_agg()== my_agg_meta
print(Congrats,you passed the test)
}
虽然你的功能肯定看起来很有趣,但我相信你问是否还有其他方法可以解决。
我个人喜欢使用这样的东西: p>
##样本数据
DT1 DT2 DT3
访问表格通过引用表名: h2>
这很简单,很像 R
#使用字符串选择表
tablesSelected< - DT3
#使用get来访问它们
get(tablesSelected)
#,我们可以执行操作:
get(tablesSelected)[,list(C1mean = mean(Col1),C2mean = mean(Col2))]
按引用选择列
要通过引用其名称来选择列,请使用 .SDcols
参数。
给定一个列名的向量:
columnsSelected< - c(Col1,Col2)
将该向量分配给.SDcols参数:
##这里我们只是访问这些列
DT3 [,.SD,.SDcols = columnsSelected]
我们还可以对字符串向量中指定的每个列应用一个函数:
##为每个列应用一个函数
DT3 [,lapply(.SD,mean),.SDcols = columnsSelected]
$请注意,如果我们的目标只是输出列,我们可以用关闭:
#这适用于显示
DT3 [,columnsSelected,with = FALSE]
但是,如果使用
with = FALSE
,我们不能以通常的方式直接对列进行操作##这不工作:
DT3 [,someFunc(columnsSelected),with = FALSE]
# #这个DOES工作:
DT3 [,someFunc(.SD),.SDcols = columnsSelected]
##这也工作,但是不太理想,即分配给新列更麻烦
DT3 [,columnsSelected,with = FALSE] [,someFunc(.SD)]
我们也可以使用
get
,但它有点棘手。
我将它留在这里供参考,但.SDcols
是要走的路线##我们需要使用`get`,但在`j`
##里面,并且在wrapper函数<`~~~~~这是VITAL
DT3 [,lapply(columnsSelected,function(.col)get(.col))]
##我们可以对列执行函数:
DT3 [,lapply(columnsSelected,function(.col)mean(get(.col)))]
##当然,我们可以使用更多的涉及函数, * ply call:
#using .SDcols
DT3 [,lapply(.SD,function(.col)c(mean(.col)+ 2 * sd(.col),mean(.col) - 2 * sd(.col))),.SDcols = columnsSelected]
#使用`get`并赋值给var。
#注意,这种方法有内存缺点,所以使用.SDcols是首选
DT3 [,lapply(columnsSelected,function(.col){TheCol < - get(.col); c Thecol)+ 2 * sd(TheCol),mean(TheCol)-2 * sd(TheCol))})]
$ b b作为参考,如果你尝试以下,你会注意到他们不产生我们以后的结果。
##这不工作
DT3 [,columnsSelected]
## netiher这个
DT3 [,eval(columnsSelected)]
##仍然不工作:
DT3 [,lapply(columnsSelected,get)]
如果要更改列的名称:
#使用`.SDcols`方法:使用`setnames`(小写n)更改名称
DT3 [,setnames(.SD,c(new.Name1,new .Name2)),.SDcols = columnsSelected]
#使用`get`方法:
##新列的名称将是`columnsSelected`的名称vector
##因此,如果我们要保留名称,请使用以下命令:
names(columnsSelected)DT3 [,lapply(columnsSelected,function(.col)get col))
##我们也可以使用这个技巧给列新的名称
names(columnsSelected)< - c(new.Name1,new.Name2 )
DT3 [,lapply(columnsSelected,function(.col)get(.col))]
显然,使用.SDcols更容易,更优雅。
c>?
#`by'是简单的,你可以在`by`参数中使用一个字符串向量。
#允许添加另一列来显示如何在`by'中使用两列
DT3 [,secondID:= sample(letters [1:2],20,TRUE)]
#这里是我们的字符串向量:
byCols< - c(id,secondID)
#这里是我们的调用
DT3 [,lapply(columnsSelected,function(.col)mean(get(.col))),by = byCols]
$ b b
将其全部合并
我们可以通过引用其名称访问data.table,然后选择它列也按名称:
get(tablesSelected)[,.SD,.SDcols = columnsSelected]
$ b b ## OR WITH MULTIPLE
tablesSelected < - c(DT1,DT3)
lapply(tablesSelected,function(.T)get(.T)[,.SD,.SDcols = columnsSelected])
#我们可能要为向量命名整齐,因为
#结果列表继承了名称。
names(tablesSelected)< - tablesSelected
这是最好的部分:
由于在
data.table
中这么多是通过引用传递的,很容易有一个表的列表,要添加的列的单独列表,以及要操作的列的另一列表,并且将所有列放在一起以对所有表执行类似的操作(但使用不同的输入)。
与使用data.frame
类似的操作,不需要重新分配最终结果。newColumnsToAdd< - c(UpperBound,LowerBound)
FunctionToExecute& c(平均值(vec)-2 * sd(vec),平均值(vec)+ 2 * sd(vec))
注意每个表的列名列表!
columnsUsingPerTable< - list(DT1=Col1,DT2 =Col2,DT3 =Col1)
tablesSelected< - names(columnsUsingPerTable)
byCols& c(id)
#TADA:
dummyVar< - #我使用`dummyVar`,因为我不想显示输出
lapply(tablesSelected,function .T)
get(.T)[,c(newColumnsToAdd):= lapply(.SD,FunctionToExecute),.SDcols = columnsUsingPerTable [[。T]],by = byCols])
#现在看看表:
DT1
DT2
DT3
I think meta-programming is the right term here.
I want to be able to use data.table much like one would use MySQL in say a webapp. That is, web users use some web front-end (like Shiny server for example) to select a data-base, select columns to filter on, select columns to group-by, select columns to aggregate and aggregation functions. I want to use R and data.table as a backend for querying, aggregation etc. Assume that front end exists and R has these variables as character strings and they are validated etc.
I wrote the following function to build the data.table expression and use the parse/eval meta-programming functionality of R to run it. Is this a reasonable way to do this?
I includes all relevant code to test this. Source this code (after reading it for security!) and run test_agg_meta() to test it. It is just a start. I could add more functionality.
But my main question is whether I am grossly over-thinking this. Is there is a more direct way to use data.table when all of the inputs are undetermined before hand without resorting to parse/eval meta-programming?
I am also aware of the "with" statement and some of the other sugarless-functional methods but don't know if they can take care of all cases.
require(data.table) fake_data<-function(num=12){ #make some fake data x=1:num lets=letters[1:num] data=data.table( u=rep(c("A","B","C"),floor(num/3)), v=x %%2, w=lets, x=x, y=x^2, z=1-x) return(data) } data_table_meta<-function( #aggregate a data.table meta-programmatically data_in=fake_data(), filter_cols=NULL, filter_min=NULL, filter_max=NULL, groupby_cols=NULL, agg_cols=setdiff(names(data_in),groupby_cols), agg_funcs=NULL, verbose=F, validate=T, jsep="_" ){ all_cols=names(data_in) if (validate) { stopifnot(length(filter_cols) == length(filter_min)) stopifnot(length(filter_cols) == length(filter_max)) stopifnot(filter_cols %in% all_cols) stopifnot(groupby_cols %in% all_cols) stopifnot(length(intersect(agg_cols,groupby_cols)) == 0) stopifnot((length(agg_cols) == length(agg_funcs)) | (length(agg_funcs)==1) | (length(agg_funcs)==0)) } #build the command #defaults i_filter="" j_select="" n_agg_funcs=length(agg_funcs) n_agg_cols=length(agg_cols) n_groupby_cols=length(groupby_cols) if (n_agg_funcs == 0) { #NULL print("NULL") j_select=paste(agg_cols,collapse=",") j_select=paste("list(",j_select,")") } else { agg_names=paste(agg_funcs,agg_cols,sep=jsep) jsels=paste(agg_names,"=",agg_funcs,"(",agg_cols,")",sep="") if (n_groupby_cols>0) jsels=c(jsels,"N_Rows_Aggregated=.N") j_select=paste(jsels,collapse=",") j_select=paste("list(",j_select,")") } groupby="" if (n_groupby_cols>0) { groupby=paste(groupby_cols,collapse=",") groupby=paste("by=list(",groupby,")",sep="") } n_filter_cols=length(filter_cols) if (n_filter_cols > 0) { i_filters=rep("",n_filter_cols) for (i in 1:n_filter_cols) { i_filters[i]=paste(" (",filter_cols[i]," >= ",filter_min[i]," & ",filter_cols[i]," <= ",filter_max[i],") ",sep="") } i_filter=paste(i_filters,collapse="&") } command=paste("data_in[",i_filter,",",j_select,",",groupby,"]",sep="") if (verbose == 2) { print("all_cols:") print(all_cols) print("filter_cols:") print(filter_cols) print("agg_cols:") print(agg_cols) print("filter_min:") print(filter_min) print("filter_max:") print(filter_max) print("groupby_cols:") print(groupby_cols) print("agg_cols:") print(agg_cols) print("agg_funcs:") print(agg_funcs) print("i_filter") print(i_filter) print("j_select") print(j_select) print("groupby") print(groupby) print("command") print(command) } print(paste("evaluating command:",command)) eval(parse(text=command)) } my_agg<-function(data=fake_data()){ data_out=data[ i=x<=5, j=list( mean_x=mean(x), mean_y=mean(y), sum_z=sum(z), N_Rows_Aggregated=.N ), by=list(u,v)] return(data_out) } my_agg_meta<-function(data=fake_data()){ #should give same results as my_agg data_out=data_table_meta(data, filter_cols=c("x"), filter_min=c(-10000), filter_max=c(5), groupby_cols=c("u","v"), agg_cols=c("x","y","z"), agg_funcs=c("mean","mean","sum"), verbose=T, validate=T, jsep="_") return(data_out) } test_agg_meta<-function(){ stopifnot(all(my_agg()==my_agg_meta())) print("Congrats, you passed the test") }
解决方案While your functions certainly look interesting, I believe you are asking if there are other ways to go about it.
Personally, I like to use something like this:## SAMPLE DATA DT1 <- data.table(id=sample(LETTERS[1:4], 20, TRUE), Col1=1:20, Col2=rnorm(20)) DT2 <- data.table(id=sample(LETTERS[3:8], 20, TRUE), Col1=sample(100:500, 20), Col2=rnorm(20)) DT3 <- data.table(id=sample(LETTERS[19:20], 20, TRUE), Col1=sample(100:500, 20), Col2=rnorm(20))
ACCESSING A TABLE BY REFERENCE TO THE TABLE NAME:
This is straightforward, much like any object in
R
# use strings to select the table tablesSelected <- "DT3" # use get to access them get(tablesSelected) # and we can perform operations: get(tablesSelected)[, list(C1mean=mean(Col1), C2mean=mean(Col2))]
SELECTING COLUMNS BY REFERENCE
To select columns by reference to their names, use the
.SDcols
argument. Given a vector of column names:columnsSelected <- c("Col1", "Col2")
Assign that vector to the .SDcols argument:
## Here we are simply accessing those columns DT3[, .SD, .SDcols = columnsSelected]
We can also apply a function to each column named in the string vector:
## apply a function to each column DT3[, lapply(.SD, mean), .SDcols = columnsSelected]
Note that if our goal is simply to output the columns we can turn off
with
:# This works for displaying DT3[, columnsSelected, with=FALSE]
However, if using
with=FALSE
, we cannot then operate directly on the columns in the usual fashion## This does NOT work: DT3[, someFunc(columnsSelected), with=FALSE] ## This DOES work: DT3[, someFunc(.SD), .SDcols=columnsSelected] ## This also works, but is less ideal, ie assigning to new columns is more cumbersome DT3[, columnsSelected, with=FALSE][, someFunc(.SD)]
We can also use
get
, but it is a bit trickier. I am leaving it here for reference, but.SDcols
is the way to go## we need to use `get`, but inside `j` ## AND IN A WRAPPER FUNCTION <~~~~~ THIS IS VITAL DT3[, lapply(columnsSelected, function(.col) get(.col))] ## We can execute functions on the columns: DT3[, lapply(columnsSelected, function(.col) mean( get(.col) ))] ## And of course, we can use more involved-functions, much like any *ply call: # using .SDcols DT3[, lapply(.SD, function(.col) c(mean(.col) + 2*sd(.col), mean(.col) - 2*sd(.col))), .SDcols = columnsSelected] # using `get` and assigning the value to a var. # Note that this method has memory drawbacks, so using .SDcols is preferred DT3[, lapply(columnsSelected, function(.col) {TheCol <- get(.col); c(mean(TheCol) + 2*sd(TheCol), mean(TheCol) - 2*sd(TheCol))})]
For reference, if you try the following, you will notice that they do not produce the results we are after.
## this DOES NOT work DT3[, columnsSelected] ## netiher does this DT3[, eval(columnsSelected)] ## still does not work: DT3[, lapply(columnsSelected, get)]
If you want to change the name of the columns:
# Using the `.SDcols` method: change names using `setnames` (lowercase "n") DT3[, setnames(.SD, c("new.Name1", "new.Name2")), .SDcols =columnsSelected] # Using the `get` method: ## The names of the new columns will be the names of the `columnsSelected` vector ## Thus, if we want to preserve the names, use the following: names(columnsSelected) <- columnsSelected DT3[, lapply(columnsSelected, function(.col) get(.col))] ## we can also use this trick to give the columns new names names(columnsSelected) <- c("new.Name1", "new.Name2") DT3[, lapply(columnsSelected, function(.col) get(.col))]
Clearly, using .SDcols is easier and more elegant.
What about
by
?# `by` is straight forward, you can use a vector of strings in the `by` argument. # lets add another column to show how to use two columns in `by` DT3[, secondID := sample(letters[1:2], 20, TRUE)] # here is our string vector: byCols <- c("id", "secondID") # and here is our call DT3[, lapply(columnsSelected, function(.col) mean(get(.col))), by=byCols]
PUTTING IT ALL TOGETHER
We can access the data.table by reference to its name and then select its columns also by name:
get(tablesSelected)[, .SD, .SDcols=columnsSelected] ## OR WITH MULTIPLE TABLES tablesSelected <- c("DT1", "DT3") lapply(tablesSelected, function(.T) get(.T)[, .SD, .SDcols=columnsSelected]) # we may want to name the vector for neatness, since # the resulting list inherits the names. names(tablesSelected) <- tablesSelected
THIS IS THE BEST PART:
Since so much in
data.table
is pass-by-reference, it is easy to have a list of tables, a separate list of columns to add and yet another list of columns to operate on, and put all together to add perform similar operations -- but with different inputs -- on all your tables. As opposed to doing something similar withdata.frame
, there is no need to reassign the end result.newColumnsToAdd <- c("UpperBound", "LowerBound") FunctionToExecute <- function(vec) c(mean(vec) - 2*sd(vec), mean(vec) + 2*sd(vec)) # note the list of column names per table! columnsUsingPerTable <- list("DT1" = "Col1", DT2 = "Col2", DT3 = "Col1") tablesSelected <- names(columnsUsingPerTable) byCols <- c("id") # TADA: dummyVar <- # I use `dummyVar` because I do not want to display the output lapply(tablesSelected, function(.T) get(.T)[, c(newColumnsToAdd) := lapply(.SD, FunctionToExecute), .SDcols=columnsUsingPerTable[[.T]], by=byCols ] ) # Take a look at the tables now: DT1 DT2 DT3
这篇关于数据表元程序设计的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!