在 R 中:如何在两个日期之间按组对变量求和 [英] In R: how to sum a variable by group between two dates
问题描述
我有两个数据框(DF1 和 DF2):
I have two data frames (DF1 and DF2):
(1) DF1 包含关于个人级别的信息，即 11 年(2000-2011 年)中嵌套在 30 个单元中的 10.000 个人的信息.它包含四个变量:
(1) DF1 contains information on individual-level, i.e. on 10.000 individuals nested in 30 units across 11 years (2000-2011). It contains four variables:
- 个人"(每个人的数字 ID；范围为 1-10.000)
- 单位"(每个单位的数字 id；范围为 1-30)
- date1"(日期格式的日期，即2000-01-01等；范围从2000-01-01到2010-12-31)
- 日期 2"(日期 1"+ 1 年)
(2) DF2 包含关于unit-level 的信息，即在同一时间段(2000-2011 年)与 DF1 中相同的 30 个单位，并进一步包含一个数字变量(x"):
(2) DF2 contains information on unit-level, i.e. on the same 30 units as in DF1 across the same time period (2000-2011) and further contains a numeric variable ("x"):
- 单位"(每个单位的数字 id；范围为 1-30)
- 日期"(日期格式的日期，即2000-01-01等；范围从2000-01-01到2011-12-31)
- x"(数值型变量，范围从 0 到 200)
我想创建一个新变量(newvar")，它为每个单位"的每个个体"提供从date1"(DF1)到date2"(DF2)的x"(DF2)总和).这意味着我想将这个新变量添加到 DF1.
I would like to create new variable ("newvar") that gives me for each "individual" per "unit" the sum of "x" (DF2) counting from "date1" (DF1) to "date2" (DF2). This means that I would like to add this new variable to DF1.
例如，如果 "unit"=1 中的 "individual"=1 有 "date1"=2000-01-01 和 "date2"=2001-01-01，并且在 DF2 中 "unit"=1 有三个观察值在date1"到date2"(即2000-01-01到2001-01-01)的时间段x"=1、x"=2和x"=3，那么我想添加一个在 "unit"=1 "newvar"=6 中给出 "individual"=1 的新变量.
For instance, if "individual"=1 in "unit"=1 has "date1"=2000-01-01 and "date2"=2001-01-01, and in DF2 "unit"=1 has three observations in the time period "date1" to "date2" (i.e. 2000-01-01 to 2001-01-01) with "x"=1, "x"=2 and "x"=3, then I would like add a new variable that gives for "individual"=1 in "unit"=1 "newvar"=6.
我假设我需要在 R 中使用 for 循环并且一直在使用以下代码:
I assume that I need to use a for loop in R and have been using the following code:
for(i in length(DF1)){
DF1$newvar[i] <-sum(DF2$x[which(DF1$date == DF1$date1[i] &
DF1$date == DF1P$date1[i] &
DF2$unit == DF1P$unit[i]),])
}
但得到错误信息:
Error in DF2$x[which(DF2$date == : incorrect number of dimensions
任何关于如何创建此变量的想法都将不胜感激！
Any ideas of how to create this variable would be tremendously appreciated!
这里是一个小例子以及预期的输出，为了简单起见，使用一个单位:
Here is a small example as well as the expected output, using one unit for the sake of simplicity:
假设 DF1 如下所示:
Assume DF1 looks as follows:
individual unit date1 date2
1 1 2000-01-01 2001-01-01
2 1 2000-02-02 2001-02-02
3 1 2000-03-03 2000-03-03
4 1 2000-04-04 2000-04-04
5 1 2000-12-31 2001-12-31
(...)
996 1 2010-01-01 2011-01-01
997 1 2010-02-15 2011-02-15
998 1 2010-03-05 2011-03-05
999 1 2010-04-10 2011-04-10
1000 1 2010-12-27 2011-12-27
1001 2 2000-01-01 2001-01-01
1002 2 2000-02-02 2001-02-02
1003 2 2000-03-03 2000-03-03
1004 2 2000-04-04 2000-04-04
1005 2 2000-12-31 2001-12-31
(...)
1996 2 2010-01-01 2011-01-01
1997 2 2010-02-15 2011-02-15
1998 2 2010-03-05 2011-03-05
1999 2 2010-04-10 2011-04-10
2000 2 2010-12-027 2011-12-27
(...)
3000 34 2000-02-02 2002-02-02
3001 34 2000-05-05 2001-05-05
3002 34 2000-06-06 2001-06-06
3003 34 2000-07-07 2001-07-07
3004 34 2000-11-11 2001-11-11
(...)
9996 34 2010-02-06 2011-02-06
9997 34 2010-05-05 2011-05-05
9998 34 2010-09-09 2011-09-09
9999 34 2010-09-25 2011-09-25
10000 34 2010-10-15 2011-10-15
假设 DF2 如下所示:
Assume DF2 looks as follows:
unit date x
1 2000-01-01 1
1 2000-05-01 2
1 2000-12-01 3
1 2001-01-02 10
1 2001-07-05 20
1 2001-12-31 30
(...)
2 2010-05-05 1
2 2010-07-01 1
2 2010-08-09 1
3 (...)
这是我希望 DF1 在运行代码后的样子:
This is what I would like DF1 to look like after running the code:
individual unit date1 date2 newvar
1 1 2000-01-01 2001-01-01 6
2 1 2000-02-02 2001-02-02 16
3 1 2000-03-03 2001-03-03 15
4 1 2000-04-04 2001-04-04 15
5 1 2000-12-31 2001-12-31 60
(...)
996 1 2010-01-01 2011-01-01 3
997 1 2010-02-15 2011-02-15 2
998 1 2010-03-05 2011-03-05 2
999 1 2010-04-10 2011-04-10 2
1000 1 2010-12-27 2011-12-27 0
(...)
但是，我不能简单地汇总:想象一下，在 DF1 中，每个单元"在 2000 年至 2011 年期间每年都有数百人.而 DF2 对 2000 年至 2011 年期间的每个单元都有许多观察结果.
However, I cannot simply aggregate: Imagine that in DF1 each "unit" has several hundreds of individuals for each year between 2000 and 2011. And DF2 has many observations for each unit across the years 2000-2011.
推荐答案
我们可以使用data.table
library(data.table)
setDT(DF1)
setDT(DF2)
DF1[DF2[, .(newvar = sum(x)), .(unit, individual = cumsum(date %in% DF1$date1))],
newvar := newvar, on = .(individual, unit)]
DF1
# individual unit date1 date2 newvar
#1: 1 1 2000-01-01 2001-01-01 6
#2: 2 1 2001-01-02 2002-01-02 60
<小时>
或者我们可以使用非等连接
Or we can use a non-equi join
DF1[DF2[DF1, sum(x), on = .(unit, date >= date1, date <= date2),
by = .EACHI], newvar := V1, on = .(unit, date1=date)]
DF1
# individual unit date1 date2 newvar
#1: 1 1 2000-01-01 2001-01-01 6
#2: 2 1 2001-01-02 2002-01-02 60
这篇关于在 R 中:如何在两个日期之间按组对变量求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
