Match start of list to timestamp

Question

I have data which I have identified rolling sums (using rollapply function), and then identified the max rolling sum for a given timeframe. The rollapply function creates a new vector as expected and works perfectly. Now, I have tried figuring out how to match the max sum, with a timestamp in the main data frame. I have tried adding the rollapply vector as a new column but it is 'n' rows short, dependent on the width of rollapply. If anyone has suggestions as to how I can match the max value of totals to another column, I would be greatly appreciative. Working example below

Script

test_data <- read_excel("test.data.xlsx", col_names = TRUE)

totals <- rollapply(test_data$Var, FUN = sum, width = 3)

Dataframe

Row    Time      Var
1      11:29:00  16
2      11:29:01  15
3      11:29:02  50
4      11:29:03  4
5      11:29:04  38
6      11:29:05  22
7      11:29:06  21
8      11:29:07  31
9      11:29:08  12
10     11:29:09  50
11     11:29:10  1
12     11:29:11  38
13     11:29:12  42
14     11:29:13  18
15     11:29:14  50

Output

totals
[1] 81 69 92 64 81 74 64 93 63 89 81 98 110

max_totals <- max(totals)
 
max_totals = 110

In the example provided above, I'd like to find out where the rolled sum for max_totals starts in the time column.

Can you give a small but complete example and show expected output based on that? Say 10-15 rows with rolling window width of 3? — Ronak Shah, Aug 17 '20 at 07:21
Have added the working example. Hopefully it shows what I am trying to achieve. Thanks — youngtred, Aug 17 '20 at 07:48

score 1 · Accepted Answer · answered Aug 17 '20 at 08:07

If you want to know the position where the sum of maximum sequence starts, it is better to use rollsum with align = 'left' and with fill = NA you will have the length same as your dataframe which you can add as a column.

test_data$totals <- zoo::rollsum(test_data$Var, 3, fill = NA, align = 'left')
test_data$totals
#[1]  81  69  92  64  81  74  64  93  63  89  81  98 110  NA  NA
which.max(test_data$totals)
#[1] 13

So row number 13 is where the maximum sequence starts.

This works a treat. Many thanks. – youngtred Aug 17 '20 at 10:46 — youngtred, Aug 17 '20 at 10:46

Match start of list to timestamp

1 Answers1