get sum of consecutive day values

Question

I have large dataset as follows:

Date       rain code
2009-04-01  0.0 0 
2009-04-02  0.0 0 
2009-04-03  0.0 0 
2009-04-04  0.7 1 
2009-04-05 54.2 1  
2009-04-06  0.0 0 
2009-04-07  0.0 0 
2009-04-08  0.0 0 
2009-04-09  0.0 0 
2009-04-10  0.0 0 
2009-04-11  0.0 0 
2009-04-12  5.3 1  
2009-04-13 10.1 1  
2009-04-14  6.0 1  
2009-04-15  8.7 1  
2009-04-16  0.0 0 
2009-04-17  0.0 0 
2009-04-18  0.0 0 
2009-04-19  0.0 0 
2009-04-20  0.0 0 
2009-04-21  0.0 0 
2009-04-22  0.0 0 
2009-04-23  0.0 0 
2009-04-24  0.0 0 
2009-04-25  4.3 1  
2009-04-26 42.2 1  
2009-04-27 45.6 1  
2009-04-28 12.6 1  
2009-04-29  6.2 1  
2009-04-30  1.0 1  

I am trying to calculate sum of consecutive values of rain when the code is "1" and I need to have sum of them separately. For example I want to get sum of rain values from 2009-04-12 to 2009-04-15. So I am trying to find way to define when the code is equal 1 and there are consecutive rain values I get sum of them.

Any help on the above problem would be greatly appreciated.

Answer 1

One straightforward solution is to use rle. But I suspect there might be more "elegant" solutions out there.

# assuming dd is your data.frame
dd.rle <- rle(dd$code)
# get start pos of each consecutive 1's
start  <- (cumsum(dd.rle$lengths) - dd.rle$lengths + 1)[dd.rle$values == 1]
# how long do each 1's extend?
ival   <- dd.rle$lengths[dd.rle$values == 1]
# using these two, compute the sum
apply(as.matrix(seq_along(start)), 1, function(idx) {
    sum(dd$rain[start[idx]:(start[idx]+ival[idx]-1)])
})

# [1]  54.9  30.1 111.9

Edit: An even simpler method with rle and tapply.

dd.rle <- rle(dd$code)
# get the length of each consecutive 1's
ival <- dd.rle$lengths[dd.rle$values == 1]
# using lengths, construct a `factor` with levels = length(ival)
levl  <- factor(rep(seq_along(ival), ival))
# use these levels to extract `rain[code == 1]` and compute sum
tapply(dd$rain[dd$code == 1], levl, sum)

#    1     2     3 
# 54.9  30.1 111.9 

Answer 2

Following is vectorized way of getting the desired result.

df <- read.table(textConnection("Date       rain code\n2009-04-01  0.0 0\n2009-04-02  0.0 0\n2009-04-03  0.0 0\n2009-04-04  0.7 1\n2009-04-05 54.2 1\n2009-04-06  0.0 0\n2009-04-07  0.0 0\n2009-04-08  0.0 0\n2009-04-09  0.0 0\n2009-04-10  0.0 0\n2009-04-11  0.0 0\n2009-04-12  5.3 1\n2009-04-13 10.1 1\n2009-04-14  6.0 1\n2009-04-15  8.7 1\n2009-04-16  0.0 0\n2009-04-17  0.0 0\n2009-04-18  0.0 0\n2009-04-19  0.0 0\n2009-04-20  0.0 0\n2009-04-21  0.0 0\n2009-04-22  0.0 0\n2009-04-23  0.0 0\n2009-04-24  0.0 0\n2009-04-25  4.3 1\n2009-04-26 42.2 1\n2009-04-27 45.6 1\n2009-04-28 12.6 1\n2009-04-29  6.2 1\n2009-04-30  1.0 1"), 
    header = TRUE)

df$cumsum <- cumsum(df$rain)
df$diff <- c(diff(df$code), 0)
df$result <- rep(NA, nrow(df))

if (nrow(df[df$diff == -1, ]) == nrow(df[df$diff == 1, ])) {
    result <- df[df$diff == -1, "cumsum"] - df[df$diff == 1, "cumsum"]
    df[df$diff == -1, "result"] <- result
} else {
    result <- c(df[df$diff == -1, "cumsum"], df[nrow(df), "cumsum"]) - df[df$diff == 1, "cumsum"]
    df[df$diff == -1, "result"] <- result[1:length(result) - 1]
    df[nrow(df), "result"] <- result[length(result)]
}

df
##          Date rain code cumsum diff result
## 1  2009-04-01  0.0    0    0.0    0     NA
## 2  2009-04-02  0.0    0    0.0    0     NA
## 3  2009-04-03  0.0    0    0.0    1     NA
## 4  2009-04-04  0.7    1    0.7    0     NA
## 5  2009-04-05 54.2    1   54.9   -1   54.9
## 6  2009-04-06  0.0    0   54.9    0     NA
## 7  2009-04-07  0.0    0   54.9    0     NA
## 8  2009-04-08  0.0    0   54.9    0     NA
## 9  2009-04-09  0.0    0   54.9    0     NA
## 10 2009-04-10  0.0    0   54.9    0     NA
## 11 2009-04-11  0.0    0   54.9    1     NA
## 12 2009-04-12  5.3    1   60.2    0     NA
## 13 2009-04-13 10.1    1   70.3    0     NA
## 14 2009-04-14  6.0    1   76.3    0     NA
## 15 2009-04-15  8.7    1   85.0   -1   30.1
## 16 2009-04-16  0.0    0   85.0    0     NA
## 17 2009-04-17  0.0    0   85.0    0     NA
## 18 2009-04-18  0.0    0   85.0    0     NA
## 19 2009-04-19  0.0    0   85.0    0     NA
## 20 2009-04-20  0.0    0   85.0    0     NA
## 21 2009-04-21  0.0    0   85.0    0     NA
## 22 2009-04-22  0.0    0   85.0    0     NA
## 23 2009-04-23  0.0    0   85.0    0     NA
## 24 2009-04-24  0.0    0   85.0    1     NA
## 25 2009-04-25  4.3    1   89.3    0     NA
## 26 2009-04-26 42.2    1  131.5    0     NA
## 27 2009-04-27 45.6    1  177.1    0     NA
## 28 2009-04-28 12.6    1  189.7    0     NA
## 29 2009-04-29  6.2    1  195.9    0     NA
## 30 2009-04-30  1.0    1  196.9    0  111.9