In R: how to sum a variable by group between two dates

1
votes

I have two data frames (DF1 and DF2):

(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:

  1. "individual" (numeric id for each individual; ranging from 1-10.000)
  2. "unit" (numeric id for each unit; ranging from 1-30)
  3. "date1" (a date in date format, i.e. 2000-01-01, etc; ranging from 2000-01-01 to 2010-12-31)
  4. "date2" ("Date1" + 1 year)

(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"):

  1. "unit" (numeric id for each unit; ranging from 1-30)
  2. "date" (a date in date format, i.e. 2000-01-01, etc; ranging from 2000-01-01 to 2011-12-31)
  3. "x" (a numeric variable, ranging from 0 to 200)

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.

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.

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]),])

}

but get the error message:

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:

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

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         (...)

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
    (...)

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.

2
rdatefor-loopsum
Please show a small reproducible example and expected output – akrun
I didn't check the code, but the error message you get because you are treating a vector (DF2$x) as two dimensional, when it has only 1 dimension. You need to delete the comma before your last paranthesis: ,]) – Bea
Thank you @Bea ! The error message disappears when deleting the comma, so that is great. Unfortunately, the code does not produce correct sums, i.e. it gives the value 0 for all observations in "newvar" in DF1. – Gret-D
Thank you, @akrun! I added a small example with expected output - I hope this is what you were thinking of? I am new to this, and so I try my best to provide the information needed... – Gret-D

2 Answers

2
votes

We can use 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
2
votes

You were almost there, I just modified slightly your for loop, and also made sure that the date variables are considered as such:

DF1$date1 = as.Date(DF1$date1,"%Y-%m-%d")
DF1$date2 = as.Date(DF1$date2,"%Y-%m-%d")
DF2$date = as.Date(DF2$date,"%Y-%m-%d")

for(i in 1:nrow(DF1)){
  DF1$newvar[i] <-sum(DF2$x[which(DF2$unit == DF1$unit[i] & 
                                  DF2$date>= DF1$date1[i] &
                                  DF2$date<= DF1$date2[i])]) 
}

The problem was, that you were asking DF2$date to be simultaneously == DF1$date1 & DF1$date2. And also, length(DF1) gives you the number of columns. To have the number of rows you can either use nrow(DF1), or dim(DF1)[1].