在`df1`中添加一个新变量(标准差)，用于依赖于`df2`中的多个行，条件为`Datetime`和其他两个变量

Question

我有一个数据帧df1，它总结了不同的人df$Person在一小时的时间间隔df1$DateTime。

此外，我还有另一个数据框df2，它在Data_Type列中记录了这些人在一段时间内所做的关于“花在电话上的时间”或“花在购买上的钱”的信息。在Value列中，显示了花在电话上的分钟数或花在这些特定时间上的钱。

举个例子：

df1<- data.frame(DateTime=c("2016-09-27 11:00:00","2016-09-27 11:00:00","2016-09-27 12:00:00","2016-09-27 12:00:00","2016-09-27 13:00:00","2016-09-27 13:00:00"),
                 Person= c(11,12,11,12,11,12))

df2<- data.frame(DateTime= c("2016-09-27 11:03:40","2016-09-27 11:07:40","2016-09-27 11:34:53","2016-09-27 11:48:32","2016-09-27 12:01:40","2016-09-27 12:09:40","2016-09-27 12:21:40","2016-09-27 12:29:40","2016-09-27 12:35:40","2016-09-27 12:41:40","2016-09-27 12:53:26","2016-09-27 13:05:40","2016-09-27 13:24:14","2016-09-27 13:32:50","2016-09-27 13:47:19"),
                 Person= c(11,11,12,11,12,11,11,11,11,12,12,12,11,12,11),
                 Data_Type=c("Call","Call","Call","Call","Purchase","Call","Call","Call","Call","Purchase","Call","Call","Call","Call","Purchase"),
                 Value=c(2.7,5.4,3.2,1.7,300,4.6,2.3,5.1,2.9,100,0.6,6.2,1.8,7.6,380))

> df1
             DateTime Person
1 2016-09-27 11:00:00     11
2 2016-09-27 11:00:00     12
3 2016-09-27 12:00:00     11
4 2016-09-27 12:00:00     12
5 2016-09-27 13:00:00     11
6 2016-09-27 13:00:00     12

> df2
              DateTime Person Data_Type Value
1  2016-09-27 11:03:40     11      Call   2.7
2  2016-09-27 11:07:40     11      Call   5.4
3  2016-09-27 11:34:53     12      Call   3.2
4  2016-09-27 11:48:32     11      Call   1.7
5  2016-09-27 12:01:40     12  Purchase 300.0
6  2016-09-27 12:09:40     11      Call   4.6
7  2016-09-27 12:21:40     11      Call   2.3
8  2016-09-27 12:29:40     11      Call   5.1
9  2016-09-27 12:35:40     11      Call   2.9
10 2016-09-27 12:41:40     12  Purchase 100.0
11 2016-09-27 12:53:26     12      Call   0.6
12 2016-09-27 13:05:40     12      Call   6.2
13 2016-09-27 13:24:14     11      Call   1.8
14 2016-09-27 13:32:50     12      Call   7.6
15 2016-09-27 13:47:19     11  Purchase 380.0

我想在df1中添加两个新变量，它们根据人员和指定的一小时间隔汇总Calls和Purchases的标准偏差。

我想得到这个(也许我在计算sd时弄错了)：

> df1
             DateTime Person   sdCalls sdPurchases
1 2016-09-27 11:00:00     11 1.9139836          NA
2 2016-09-27 11:00:00     12 0.0000000          NA
3 2016-09-27 12:00:00     11 1.3375973          NA
4 2016-09-27 12:00:00     12 0.0000000    141.4214
5 2016-09-27 13:00:00     11 0.0000000      0.0000
6 2016-09-27 13:00:00     12 0.9899495          NA

有人知道怎么做吗？

Answer 1

一种选择是在第二个数据集中使用'DateTime‘列，并将on与'Person'，'DateTime’子集'Value‘连接起来，该子集对应于'Data_Type’中的'Call'，'Purchase‘，以获得sd

library(lubridate)
library(data.table)
setDT(df1)[, DateTime := ymd_hms(DateTime)]
setDT(df2)[, dt_floor := floor_date(ymd_hms(DateTime), unit = "hour")]
df2[df1, .(sdsCalls = sd(Value[Data_Type == "Call"]), 
  sdPurchases = sd(Value[Data_Type == 'Purchase'])),
          on = .(Person, dt_floor = DateTime), by = .EACHI]