按一列分组,为每对列选择最少一列的行 [英] Group by one column, select row with minimum in one column for every pair of columns
问题描述
难以回答的问题。这是我想做的一个例子。我从以下内容开始的示例:
Difficult question to phrase. Here is an example of what I would like to do. An example of what I am starting with:
set.seed(0)
dt <- data.table(dr1.d=rnorm(5), dr1.p=abs(rnorm(5, sd=0.08)),
dr2.d=rnorm(5), dr2.p=abs(rnorm(5, sd=0.08)),
dr3.d=rnorm(5), dr3.p=abs(rnorm(5, sd=0.08)),
dr4.d=rnorm(5), dr4.p=abs(rnorm(5, sd=0.08)),
sym = paste("sym", c(1,1,1,2,2)))
dt
dr1.d dr1.p dr2.d dr2.p dr3.d dr3.p dr4.d dr4.p sym
1: 1.2629543 0.1231960034 0.7635935 0.03292087 -0.22426789 0.040288638 -0.2357066 0.09215294 sym 1
2: -0.3262334 0.0742853628 -0.7990092 0.02017788 0.37739565 0.086861549 -0.5428883 0.07937283 sym 1
3: 1.3297993 0.0235776357 -1.1476570 0.07135369 0.13333636 0.055276307 -0.4333103 0.03436105 sym 1
4: 1.2724293 0.0004613738 -0.2894616 0.03485466 0.80418951 0.102767948 -0.6494716 0.09906433 sym 2
5: 0.4146414 0.1923722711 -0.2992151 0.09900307 -0.05710677 0.003738094 0.7267507 0.02234770 sym 2
对于共享药物的所有成对色谱柱(例如 dr1)我想按 sym对行进行分组,然后选择每个组中p值最小的行(以 .p结尾)。上述data.table的最终结果将是:
For all pairs of columns that share a drug (e.g. 'dr1') I want to group rows by 'sym', then select the row with the smallest p-value (ends in '.p') within each group. The final result of the above data.table would be this:
dr1.d dr1.p dr2.d dr2.p dr3.d dr3.p dr4.d dr4.p sym
1: 1.3297993 0.0235776357 -0.7990092 0.02017788 -0.22426789 0.040288638 -0.4333103 0.03436105 sym 1
2: 1.2724293 0.0004613738 -0.2894616 0.03485466 -0.05710677 0.003738094 0.7267507 0.02234770 sym 2
我尝试使用.SD和lapply来完成此操作,但是我无法解决这个问题。谢谢!
I have tried using .SD and lapply to accomplish this, but I can't wrap my head around it. Thank you!
推荐答案
了解 data.table 的最重要(也是最强大的)事情 code>就是,只要
j
返回一个 list ,列表中的每个元素都将成为结果中的一列。
The most important (and powerful) thing to understand about data.table
is that, as long as j
returns a list, each element of the list will become a column in the result.
有了这些知识和一些R的基本乐趣,我们可以直接通过以下操作获得此结果:
With that knowledge and some base R fun, we can get this result directly by doing:
# I'm on v1.9.7, see https://github.com/Rdatatable/data.table/wiki/Installation
cols1 = grep("d$", names(dt), value=TRUE)
cols2 = grep("p$", names(dt), value=TRUE)
dt[, Map(`[`, mget(c(cols1,cols2)), lapply(mget(cols2), which.min)), by=sym]
# sym dr1.d dr2.d dr3.d dr4.d dr1.p dr2.p
# 1: sym 1 1.329799 -0.7990092 -0.22426789 -0.4333103 0.0235776357 0.02017788
# 2: sym 2 1.272429 -0.2894616 -0.05710677 0.7267507 0.0004613738 0.03485466
# dr3.p dr4.p
# 1: 0.040288638 0.03436105
# 2: 0.003738094 0.02234770
请参见渐晕以获取更多信息。
See the vignettes for more.
这篇关于按一列分组,为每对列选择最少一列的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!