在data.table中按组进行回归和汇总统计 [英] Regression and summary statistics by group within a data.table
问题描述
我想计算一些汇总统计数据，并在数据表中按组进行不同的回归，并以宽"格式显示结果(即，每组一行，多列).我可以分多个步骤进行操作，但似乎应该可以一次完成所有操作.
I would like to calculate some summary statistics and perform different regressions by group within a data table, and have the results in "wide" format (i.e. one row per group with several columns). I can do it in multiple steps, but it seems like it should be possible to do all at once.
请考虑以下示例数据:
set.seed=46984
dt <- data.table(ID=c(rep('Frank',5),rep('Tony',5),rep('Ed',5)), y=rnorm(15), x=rnorm(15), z=rnorm(15),key="ID")
dt
# ID y x z
# 1: Ed 0.2129400 -0.3024061 0.845335632
# 2: Ed 0.4850342 -0.5159197 -0.087965415
# 3: Ed 1.8917489 1.7803220 0.760465271
# 4: Ed -0.4330460 -2.1720944 0.973812545
# 5: Ed 0.7685060 0.7947470 1.279761200
# 6: Frank 0.4978475 -0.2906851 0.568101004
# 7: Frank 0.6323386 -0.5596599 1.537133025
# 8: Frank -0.8243218 -0.4354885 0.057818033
# 9: Frank 1.2402488 0.3229422 0.005995249
#10: Frank 0.2436210 -0.2651422 0.349532173
#11: Tony 0.4179568 0.1418463 0.142380549
#12: Tony 0.7036613 0.4402572 0.141237901
#13: Tony -0.1978720 -0.9553784 0.480425820
#14: Tony -1.7269375 -0.1881292 0.370583351
#15: Tony 1.1064903 0.4375014 -0.798221750
假设我要按ID获取中位数，按ID对y〜x进行线性回归，并按ID对y〜x + z进行线性回归.在这里，我得到了中位数:
Let's say I want to get the median by ID, perform linear regression on y ~ x by ID, and perform linear regression on y ~ x + z by ID. Here I get the median:
dt.med <- dt[,list(y.med=median(y)),by=ID]
dt.med
# ID y.med
#1: Ed 0.4850342
#2: Frank 0.4978475
#3: Tony 0.4179568
感谢@DWin的此答案，在这里，我得到了两个独立的回归系数集，作为ID的列:
And thanks to this answer by @DWin, here I get the two individual sets of regression coefficients as columns by ID:
dt.reg.1 <- dt[,as.list(coef(lm(y ~ x))), by=ID]
dt.reg.1
# ID (Intercept) x
#1: Ed 0.63057884 0.5482373
#2: Frank 0.69720351 1.3813007
#3: Tony 0.08588421 1.0179131
dt.reg.2 <- dt[,as.list(coef(lm(y ~ x + z))), by=ID]
dt.reg.2
# ID (Intercept) x z
#1: Ed 0.8262577 0.5587170 -0.2582699
#2: Frank 0.4317538 2.7221024 1.1807442
#3: Tony 0.1494439 0.3166547 -1.2029693
现在，我必须加入三个结果集，并重命名各列:
Now I have to join the three result sets, and rename the columns:
dt.ans <- dt.med[dt.reg.1][dt.reg.2]
setnames(dt.ans,c("ID","y.med","reg.1.c0","reg.1.c1","reg.2.c0","reg.2.c1","reg.2.c2"))
最后，这是示例的所需输出:
dt.ans
# ID y.med reg.1.c0 reg.1.c1 reg.2.c0 reg.2.c1 reg.2.c2
#1: Ed 0.4850342 0.63057884 0.5482373 0.8262577 0.5587170 -0.2582699
#2: Frank 0.4978475 0.69720351 1.3813007 0.4317538 2.7221024 1.1807442
#3: Tony 0.4179568 0.08588421 1.0179131 0.1494439 0.3166547 -1.2029693
计算三个结果，将它们合并，然后重命名列似乎效率低下.另外，我的实际表比较大，所以我想确保我不使用过多的系统内存. 是否可以在一个" data.table语句中完成所有操作?或者更笼统地说，可以更有效地做到这一点吗?
It seems inefficient to calculate the three results, join them, and then rename the columns. Also, my actual tables are largish so I'd like to make sure I don't use too much system memory. Is it possible to do this all within "one" data.table statement? Or more generally, can this be done more efficiently?
我尝试了不同的事情.这是一个失败的示例，该示例给出了中位数，但忽略了回归系数:
I've tried different things. Here is one failed example that gives the median but ignores the regression coefficients:
dt[,as.list(median(y),coef(lm(y ~ x))), by=ID]
# ID V1
#1: Ed 0.4850342
#2: Frank 0.4978475
#3: Tony 0.4179568
推荐答案
dt[,c(y.med = median(y),
reg.1 = as.list(coef(lm(y ~ x))),
reg.2 = as.list(coef(lm(y ~ x + z)))), by=ID]
# ID y.med reg.1.(Intercept) reg.1.x reg.2.(Intercept) reg.2.x reg.2.z
#1: Ed 0.7280448 0.75977555 0.1132509 0.83322290 -0.484348116 0.7655563
#2: Frank 0.6100339 -0.07830664 0.2700846 0.04720686 0.004027939 0.7168521
#3: Tony 0.2710623 -0.78319379 0.9166601 -0.35836990 0.622822617 0.4161102
这篇关于在data.table中按组进行回归和汇总统计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
