从data.table查找中按组平均第一行 [英] Average the first row by group from data.table lookup

问题描述

我希望对在指定日期之前发生的每个个人的公司最新行进行平均.

I wish to average the most recent company rows, for each individual which occur before a specified date.

换句话说，我想平均每个人和每个日期的最新(每个公司)以前的alpha值.

In other words I would like to average the most recent (for each company) previous alpha values for each individual and for each date.

table1 <- fread(
  "individual_id | date       
       1             |  2018-01-02
       1             |  2018-01-04
       1             |  2018-01-05
       2             |  2018-01-02
       2             |  2018-01-05", 
  sep ="|"
)
table1$date = as.IDate(table1$date)
table2 <- fread(
  "individual_id | date2       | company_id | alpha
       1             |  2018-01-02 |     62       |  1     
       1             |  2018-01-04 |     62       |  1.5 
       1             |  2018-01-05 |     63       |  1   
       2             |  2018-01-01 |     71       |  2     
       2             |  2018-01-02 |     74       |  1   
       2             |  2018-01-05 |     74       |  4",
  sep = "|"
)

例如:

• 表1中的观测值1是2018年1月2日的个人"1".
• 要实现这一目标，我在表2中看到，个人1拥有一个实例prio或公司62在2018年1月2日.因此，平均值只有1个值，平均alpha为1.

示例2:

• 2018年2月5日对第2个人的观察.
• 这里对于个人2有3个观察值，对于公司71有1个观察值，对于公司74有2个观察值，所以我们选择了每个公司的最新观察值，这给我们留下了2018年1月1日的2个观察值71和2018年1月的74个观察值.-05，alpha值为2和4，则平均alpha为3.

结果应如下所示:

table1 <- fread(
      "individual_id | date         | mean alpha
       1             |  2018-01-02  | 1
       1             |  2018-01-04  | 1.5
       1             |  2018-01-05  | (1.5+1)/2 = 1.25
       2             |  2018-01-02  | (2+1)/2 = 1.5
       2             |  2018-01-05  | (2+4)/2 = 3", 
      sep ="|"
    )

我可以使用以下命令从table2中获得第一行的子样本:

I can get the sub sample of the first row from table2 using:

table2[, .SD[1], by=company_id]

但是我不确定如何限制日期，并将其与第一个表格结合起来.

But I am unsure how limit by the date and combine this with the first table.

修改

这会为每个人(而不是公司)生成结果.

This produces the result for each individual but not by company.

table1[, mean_alpha := 
         table2[.SD, on=.(individual_id, date2 <= date), mean(alpha, na.rm = TRUE), by=.EACHI]$V1]


individual_id    date    mean_alpha
1   2018-01-02  1.000000
1   2018-01-04  1.250000
1   2018-01-05  1.166667
2   2018-01-02  1.500000
2   2018-01-05  2.333333

推荐答案

这是另一种可能的方法:

Here is another possible approach:

#ensure that order is correct before using the most recent for each company
setorder(table2, individual_id, company_id, date2)

table1[, mean_alpha := 
    #perform non-equi join
    table2[table1, on=.(individual_id, date2<=date), 
        #for each row of table1,
        by=.EACHI,
        #get most recent alpha by company_id and average the alphas
        mean(.SD[, last(alpha), by=.(company_id)]$V1)]$V1
    ]

输出:

   individual_id       date mean_alpha
1:             1 2018-01-02       1.00
2:             1 2018-01-04       1.50
3:             1 2018-01-05       1.25
4:             2 2018-01-02       1.50
5:             2 2018-01-05       3.00

数据:

library(data.table)
table1 <- fread(
    "individual_id | date       
       1             |  2018-01-02
       1             |  2018-01-04
       1             |  2018-01-05
       2             |  2018-01-02
       2             |  2018-01-05", 
    sep ="|"
)
table1[, date := as.IDate(date)]
table2 <- fread(
    "individual_id | date2       | company_id | alpha
       1             |  2018-01-02 |     62       |  1     
       1             |  2018-01-04 |     62       |  1.5 
       1             |  2018-01-05 |     63       |  1   
       2             |  2018-01-01 |     71       |  2     
       2             |  2018-01-02 |     74       |  1   
       2             |  2018-01-05 |     74       |  4",
    sep = "|"
)
table2[, date2 := as.IDate(date2)]

这篇关于从data.table查找中按组平均第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！