Sum a part of a time series between set end points

Question

I have a time series (xts) of rain gage data and I would like to be able to sum all the rain amounts between a beginning and end time point from a list. And then make a new data frame that is StormNumber and TotalRain over that time

> head(RainGage)
                    Rain_mm
2019-07-01 00:00:00       0
2019-07-01 00:15:00       0
2019-07-01 00:30:00       0
2019-07-01 00:45:00       0
2019-07-01 01:00:00       0
2019-07-01 01:15:00       0

head(StormTimes)
  StormNumber       RainStartTime         RainEndTime
1           1 2019-07-21 20:00:00 2019-07-22 04:45:00
2           2 2019-07-22 11:30:00 2019-07-22 23:45:00
3           3 2019-07-11 09:15:00 2019-07-11 19:00:00
4           4 2019-05-29 17:00:00 2019-05-29 20:45:00
5           5 2019-06-27 14:30:00 2019-06-27 17:15:00
6           6 2019-07-11 06:15:00 2019-07-11 09:00:00

I have this code that I got from the SO community when I was trying to do something similar in the past (but extract data rather than sum it). However, I have no idea how it works so I am struggling to adapt it to this situation.

do.call(rbind, Map(function(x, y) RainGage[paste(x, y,  sep="/")], 
               StormTimes$RainStartTime, StormTimes$RainEndTime)

Jean-Paul Jean-Paul · Accepted Answer · 2020-01-17T14:59:44

In this case I would suggest just to write your own function and then use apply to achieve what you want, for example:

dates <- c('2019-07-01 00:00:00', '2019-07-01 00:15:00',
           '2019-07-01 00:30:00', '2019-07-01 00:45:00',
           '2019-07-01 01:00:00', '2019-07-01 01:15:00')
dates <- as.POSIXct(strptime(dates, '%Y-%m-%d %H:%M:%S'))
mm <- c(0, 10, 10, 20, 0, 0)
rain <- data.frame(dates, mm)

number <- c(1,2)
start  <- c('2019-07-01 00:00:00','2019-07-01 00:18:00')
start  <- as.POSIXct(strptime(start, '%Y-%m-%d %H:%M:%S'))
end    <- c('2019-07-01 00:17:00','2019-07-01 01:20:00')
end    <- as.POSIXct(strptime(end, '%Y-%m-%d %H:%M:%S'))
storms <- data.frame(number, start, end)

# Sum of rain
f = function(x, output) {
    # Get storm number
    number = x[1]
    # Get starting moment
    start = x[2]
    # Get ending moment
    end = x[3]
    # Calculate sum
    output <- sum(rain[rain$dates >= start & rain$dates < end, 'mm'])
}

# Apply function to each row of the dataframe
storms$rain <- apply(storms, 1, f)
print(storms)

This yields:

   number                start                  end  rain
1       1  2019-07-01 00:00:00  2019-07-01 00:17:00    10
2       2  2019-07-01 00:18:00  2019-07-01 01:20:00    30

So a column rain in storms now holds the sum of rain$mm, which is what you're after.

Hope that helps you out!

Sum a part of a time series between set end points

1 Answers