CODEWITHSUNDEEP
rwindowaggregatesubset
squishy
squishy

Reputation: 489

Aggregate values in data frame based on an array of start and end dates - R

Example data: 

    Date_End   <- c("1999-08-30","1999-09-07","1999-09-20","1999-09-27","1999-10-04","1999-10-12")
    Date_Start <- c("1999-08-24" ,"1999-08-30" ,"1999-09-13" ,"1999-09-20" ,"1999-09-27" ,"1999-10-04") 
    as.Date(Date_Start, "%Y-%m-%d" )
    as.Date(Date_End, "%Y-%m-%d" )
    df1 <- data.frame(Date_Start,Date_End)  
    c1 <- data.frame(seq(as.Date('1999-08-24'), as.Date('1999-10-12'), by = 1))
    c2 <- sample(100, size = nrow(c1), replace = TRUE)
    df2 <- data.frame(c2,c1)
    names(df2) <- c("unit","date")
    df2 <- zoo(df2)

I have an array of start and end dates in df1 and a time series in df2. I would like to use aggregate functions (mainly sum) so that I have the total sum of unit in df2 for the period spanning each line of df1. As an example, yielding something like this: 

Date_Start  Date_End    sum(unit)
8/24/1999   8/30/1999   282
8/30/1999   9/7/1999    269
9/13/1999   9/20/1999   464
9/20/1999   9/27/1999   308
9/27/1999   10/4/1999   408
10/4/1999   10/12/1999  353

I have tried using both the window function:

window(df2,start = df1$Date_Start, end = df1$Date_End)

And creating a sequence followed by indexing:

seq_a <- seq(as.Date(df1$Date_Start), as.Date(df1$Date_End), by = 1) test <- df2[seq_a] sum(test)

However with seq, you can only have one start and end:

Error in seq.Date(as.Date(df1$Date_Start), as.Date(df1$Date_End), by = 1) : 
  'from' must be of length 1

Help appreciated!

Upvotes: 0

Views: 220

Answers (2)

mlegge
mlegge

Reputation: 6913

This solution cannot use df2 as a zoo object, but it may still be useful to you:

Date_End   <- as.Date(c("1999-08-30","1999-09-07","1999-09-20","1999-09-27","1999-10-04","1999-10-12"))
Date_Start <- as.Date(c("1999-08-24" ,"1999-08-30" ,"1999-09-13" ,"1999-09-20" ,"1999-09-27" ,"1999-10-04")) 
df1 <- data.frame(Date_Start,Date_End)  
c1 <- seq(as.Date('1999-08-24'), as.Date('1999-10-12'), by = 1)
c2 <- sample(100, size = length(c1), replace = TRUE)
df2 <- data.frame(unit = c2, date = c1)

library(sqldf)
> sqldf("select Date_Start, Date_End, sum(unit) as units 
      from df1, 
           df2 
      where df1.Date_Start <= df2.date 
      and df2.date <= df1.Date_end 
      group by Date_Start")
Date_Start   Date_End units
1 1999-08-24 1999-08-30   258
2 1999-08-30 1999-09-07   493
3 1999-09-13 1999-09-20   423
4 1999-09-20 1999-09-27   432
5 1999-09-27 1999-10-04   433
6 1999-10-04 1999-10-12   584

I edited some of your code, including making Date_Start and Date_End date objects and c1 a vector instead of data.frame.

P.S. Using cases with underscores is not recommended, here is a style guide.

Upvotes: 2

Mist
Mist

Reputation: 1948

Probably should use a function instead of a loop but for a quick and dirty you can do something like this:

Date_End   <- c("1999-08-30","1999-09-07","1999-09-20","1999-09-27","1999-10-04","1999-10-12")
Date_Start <- c("1999-08-24" ,"1999-08-30" ,"1999-09-13" ,"1999-09-20" ,"1999-09-27" ,"1999-10-04") 
Date_Start <- as.Date(Date_Start, "%Y-%m-%d" )
Date_End   <- as.Date(Date_End, "%Y-%m-%d" )
df1 <- data.frame(Date_Start,Date_End)
c1 <- data.frame(seq(as.Date('1999-08-24'), as.Date('1999-10-12'), by = 1))
c2 <- sample(100, size = nrow(c1), replace = TRUE)
df2 <- data.frame(c2,c1)
names(df2) <- c("unit","date")

for (i in 1:nrow(df1)) {
  df1$sum[i] <- sum(df2$unit[df2$date > df1$Date_Start[i] & df2$date < df1$Date_End[i]])
}

Note I modified lines 3 and 4 of your code too.

Upvotes: 2

Related Questions